GollyJer's questions

Esta é uma versão simplificada da minha pergunta anterior . Eu removi a complexidade de muitos para muitos e ainda impasses. Isso acontece com menos frequência, mas ainda acontece. ?

A situação...
Tenho uma tweettabela e uma das colunas recebe uma array[]::text[]das urls.
Há uma função de gatilho na tabela que insere as urls em uma url_startingtabela.

A tabela url_starting se parece com isso.

CREATE TABLE public.url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

E a tabela de tweets, o gatilho se parece com isso.

CREATE OR REPLACE FUNCTION public.create_tweet_relationships()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    INSERT INTO url_starting (url) 
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING;

    RETURN NULL;
END
$function$;

Às vezes eu recebo erros de deadlock como este.

deadlock detected
DETAIL:  Process 4540 waits for ShareLock on transaction 4709; blocked by process 4531.
Process 4531 waits for ShareLock on transaction 4710; blocked by process 4540.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (2314,101) in relation "url_starting"
SQL statement "INSERT INTO url_starting (url) 
    SELECT DISTINCT UNNEST(NEW.urls)
    ORDER BY 1
    ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

Tiros no escuro... ?‍♂️

Isso pode ser causado pelo UNNEST? Estou fazendo algo errado na sintaxe?

Por que o erro diz em relação "url_starting" quando não há relacionamento na tabela?

Existem milhares de tweets comprometidos com o banco de dados simultaneamente. Não importa se configurado corretamente, não é?

Estou tendo um problema com deadlocks em uma inserção de muitos para muitos e estou muito fora do meu alcance neste momento.

Tenho uma tweettabela que recebe milhares de registros por segundo. Uma das colunas é um array[]::text[]tipo PostgreSQL com urls de zero a muitos na matriz. Parece {www.blah.com, www.blah2.com}.

O que estou tentando realizar a partir de um gatilho na tweettabela é criar uma entrada em uma urls_startingtabela e, em seguida, adicionar o relacionamento tweet/url_starting em um arquivo tweet_x_url_starting.

_{Observação lateral: A url_startingtabela está vinculada a uma url_endingtabela onde residem os caminhos de URL totalmente resolvidos.}

O problema que enfrento são os impasses e não sei mais o que tentar.
Eu fui em uma farra de aprendizado Erwin Brandstetter . (se você está por aí cara... OBRIGADO! ?)

1. Como implementar um relacionamento muitos-para-muitos no PostgreSQL?
2. Deadlock com INSERTs de várias linhas apesar de ON CONFLICT NÃO FAZER NADA
3. Postgres ATUALIZAÇÃO … LIMITE 1(skip locked help)

Eu tentei adicionar ORDER BY para ordens determinísticas e estáveis ​​e FOR UPDATE SKIP LOCKED, mas não tenho certeza se estou fazendo nada disso corretamente.

Aqui está a estrutura. Usando PostgreSQL 10.5 .

CREATE TABLE tweet(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    twitter_id text NOT NULL,
    created_at timestamp NOT NULL,
    content text NOT NULL,
    urls text[],
    CONSTRAINT tweet_pk PRIMARY KEY (id)
);

CREATE TABLE url_starting(
    id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    url text NOT NULL,
    CONSTRAINT url_starting_pk PRIMARY KEY (id),
    CONSTRAINT url_starting_ak_1 UNIQUE (url)
);

CREATE TABLE tweet_x_url_starting(
    id_tweet integer NOT NULL,
    id_url_starting integer NOT NULL,
    CONSTRAINT tweet_x_url_starting_pk PRIMARY KEY (id_tweet,id_url_starting)

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT tweet_fk FOREIGN KEY (id_tweet)
REFERENCES tweet (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE tweet_x_url_starting ADD CONSTRAINT url_starting_fk FOREIGN KEY (id_url_starting)
REFERENCES url_starting (id) MATCH FULL
ON DELETE CASCADE ON UPDATE CASCADE;

Aqui está o tweetgatilho da tabela.

CREATE TRIGGER create_tweet_relationships
    AFTER INSERT OR UPDATE
    ON tweet
    FOR EACH ROW
    EXECUTE PROCEDURE create_tweet_relationships();

E por fim, a função.

CREATE FUNCTION create_tweet_relationships ()
    RETURNS trigger
    LANGUAGE plpgsql
    VOLATILE 
    CALLED ON NULL INPUT
    SECURITY INVOKER
    COST 1
    AS $$
BEGIN
    IF (NEW.urls IS NOT NULL) AND cardinality(NEW.urls::TEXT[]) > 0 THEN
        WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
            ORDER BY 1
          ON CONFLICT (url) DO UPDATE
            SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
            ORDER BY 1, 2
            FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING;
    END IF;

    RETURN NULL;
END
$$;

Eu joguei cegamente as coisas que li na função sem sucesso.

O erro se parece com isso.

deadlock detected
DETAIL:  Process 11281 waits for ShareLock on transaction 1317; blocked by process 11278.
Process 11278 waits for ShareLock on transaction 1316; blocked by process 11281.
HINT:  See server log for query details.
CONTEXT:  while inserting index tuple (494,33) in relation "url_starting"
SQL statement "WITH tmp_url AS (
          INSERT INTO url_starting (url)
          SELECT UNNEST(NEW.urls)
      ORDER BY 1
          ON CONFLICT (url) DO UPDATE
              SET url = EXCLUDED.url
          RETURNING id
        )
        INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
        SELECT NEW.id, id
            FROM tmp_url
    ORDER BY 1, 2
        FOR UPDATE SKIP LOCKED
        ON CONFLICT DO NOTHING"
PL/pgSQL function create_tweet_relationships() line 12 at SQL statement

Error causing transaction rollback (deadlocks, serialization failures, etc).

Como posso parar os impasses? Obrigado! ?

Minha intuição é NULLque vai ocupar menos espaço do que {}quando usado como valor padrão de uma ARRAY[]coluna no PostgreSQL.

Estou correcto?