GollyJer's questions

这是我先前问题的简化版本。我消除了多对多的复杂性，但仍然存在死锁。它发生的频率较低，但仍然会发生。?

情况...
我有一张tweet表，其中一列接收到一个array[]::text[]url。
表上有一个触发器函数，可以将 url 插入url_starting表中。

url_starting表如下所示。

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)
);

推文表触发器看起来像这样。

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$;

有时我会遇到这样的死锁错误。

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).

在黑暗中拍摄......?‍♂️

这可能是由 UNNEST 引起的吗？我在语法上做错了什么吗？

当表中没有关系时， 为什么错误说关系“url_starting” ？

有数千条推文同时提交到数据库。如果设置正确应该没关系，不是吗？

我在多对多插入时遇到了死锁问题，并且在这一点上远远超出了我的范围。

我有一个tweet每秒接收数千条记录的表。其中一列是 PostgreSQLarray[]::text[]类型，在数组中具有零对多的 url。它看起来像{www.blah.com, www.blah2.com}。

我试图从表上的触发器中完成的是在tweet表中创建一个条目，urls_starting然后在tweet_x_url_starting.

_{旁注：该url_starting表链接到url_ending完全解析的 url 路径所在的表。}

我面临的问题是死锁，我不知道还能尝试什么。
我继续进行Erwin Brandstetter学习狂欢。（如果你在外面，伙计......谢谢！?）

1. 如何在 PostgreSQL 中实现多对多关系？
2. 尽管 ON CONFLICT DO NOTHING 导致多行 INSERT 死锁
3. Postgres 更新……限制 1(skip locked help)

我尝试为确定性、稳定的订单和 FOR UPDATE SKIP LOCKED 添加 ORDER BY，但我不确定我是否正确地执行了任何操作。

这是结构。使用 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;

这是tweet表触发器。

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

最后，功能。

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
$$;

我盲目地将我读到的东西扔到函数中，但没有成功。

错误看起来像这样。

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).

我怎样才能停止死锁？谢谢！?

我的直觉是比在 PostgreSQL 中用作列的默认值时NULL占用更少的空间。{}ARRAY[]

我对么？