Why is this seemingly simple insert trigger function causing deadlocks?

Question

This is a simplified version of my prior question. I removed the many-to-many complexity and still deadlocks. It happens WAY less frequently, but still happens. 😕

The situation...
I have a tweet table and one of the columns receives an array[]::text[] of urls.
There is a trigger function on the table that inserts the urls into a url_starting table.

The url_starting table looks like this.

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

And tweet table the trigger looks like this.

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

Sometimes I get deadlock errors like this.

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

---

Shots in the dark... 🤷‍♂️

Could this be caused by UNNEST? Am I doing something wrong in the syntax?

Why does the error say in relation "url_starting" when there is no relationship in the table?

There are thousands of tweets committing to the database simultaneously. It shouldn't matter if set up correctly, should it?

Answer 1

Under heavy concurrent write access, the defense against deadlocks is to process rows in consistent order in all writing queries.

You may have sorted rows properly in all your writing queries to the table tweet- did you? (I know about that table from your related question.)

And you are obviously sorting rows to be "upserted" in url_starting in the trigger function.

That should do it for transactions inserting a single row in tweet with multiple URLs to be upserted in url_starting.

But while inserting multiple rows to tweet, each with an arbitrary array of URLs, rows to be upserted in url_starting are still in inconsistent order for the transaction. URLs are only sorted per row in tweet, not for the whole transaction (or even command). You would need to unnest all URLs of the same INSERT, sort them consistently, and then upsert url_starting. This cannot be achieved with a trigger FOR EACH ROW. I don't think you can solve this at all with your current approach while inserting multiple rows with arbitrary arrays of URLs. Concurrent write access to both tables is inherently conflicting.

Single-row inserts to tweet should be fine (each in its own transaction) - though possibly substantially more expensive. Maybe you can get rid of the trigger completely and reorganize the workflow (with data-modifying CTEs): write a sorted list of URLs to url_starting, then write to tweet ...

Arrays are often problematic in a relational table design. Full normalization might be another approach - replacing the array column with a related table. Not sure if that's the way ...

Or you keep your transactions as short and fast as possible, making deadlocks the rare exception, and prepare your application to retry in case of an error.

Answer 2

When Erwin mentioned in his answer...

But while inserting multiple rows to tweet, each with an arbitrary array of URLs, rows to be upserted in url_starting are still in inconsistent order for the transaction. URLs are only sorted per row in tweet, not for the whole transaction (or even command).

Flashbulbs went off in my head. Duh! I'm not sorting all the items being upserted in url_starting.

Maybe you can get rid of the trigger completely and reorganize the workflow (with data-modifying CTEs): write a sorted list of URLs to url_starting, then write to tweet ...

And so I did. I went into my python code and refactored. Instead of simply sending batches of tweets to the tweet table it now sends the tweets and upserts all the urls (sorting them first) via a data-modifying CTE.

Great! The code ran for a minute without any issues and then 💥💥💥!
THE SAME ERROR! And then a few more times. They were fewer than the trigger, but not by much. 😤

The one thing I still couldn't put my finger on was why the error mentioned an "index tuple".

while inserting index tuple (2314,101) in relation "url_starting"

Then I remembered 2 things.

1. Whenever using INSERT INTO with ON CONFLICT DO NOTHING the primary key is incremented for each attempt, even if the entry was skipped via DO NOTHING, leaving gaps in the auto-incrementing pattern.
2. PostgreSQL uses an index tuple for it's internal tracking. From the docs...

   A final identifier type used by the system is tid, or tuple identifier (row identifier). This is the data type of the system column ctid. A tuple ID is a pair (block number, tuple index within block) that identifies the physical location of the row within its table.

With this in mind I thought... maybe the race condition is happening on this internal indexing system because of the thousands of concurrent duplicates being processed/skipped at a time?

That thought led to a test where I filter out the duplicates before trying to add anything to the url_upsert table. I wanted no chance of an internal race condition by eliminating the creation of skipped/wasted internal tuple ids (tid/ctid).

That thought resulted in this query (sent via the execute_many() function in psycopg2). 👇

WITH cte_data (twitter_id, created_at, contents, search_hits, urls) AS (
    VALUES
    (NULL::text, NULL::timestamp, NULL::text, NULL::text[], NULL::text[]),
    %s
    OFFSET 1
)
, upserted_tweets AS (
    INSERT INTO tweet (twitter_id, created_at, contents, search_hits)
        SELECT twitter_id, created_at, contents, search_hits
        FROM cte_data
        ORDER BY 1
    ON CONFLICT DO NOTHING
    RETURNING id, twitter_id
    )
, upserted_tweets_with_urls AS (
    SELECT id, urls
    FROM upserted_tweets
    JOIN cte_data USING (twitter_id)
)
, unique_urls AS (
    SELECT DISTINCT UNNEST(urls) url
    FROM cte_data
)
, new_urls AS (
    SELECT url
    FROM url_starting
    RIGHT JOIN unique_urls USING (url)
    WHERE id IS NULL
    ORDER BY 1
)
, inserted_urls AS (
    INSERT INTO url_starting (url)
        TABLE new_urls
    ON CONFLICT DO NOTHING
    RETURNING id, url
)
INSERT INTO tweet_x_url_starting (id_tweet, id_url_starting)
    SELECT ut.id, iu.id
    FROM upserted_tweets_with_urls ut
    JOIN inserted_urls iu
        ON (iu.url = ANY (ut.urls))
    ORDER BY 1, 2
ON CONFLICT DO NOTHING;

This bad boy ran for about 30 minutes with no errors then 💥💥💥! NEW ERROR! 🤦‍♂️

ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Luckily this time it wasn't a big deal. I'll take one every 30 minutes instead of one every 30 seconds.

I'd LOVE to eliminate all errors by completely understanding the problem and fixing it. But, for now, I can live with an error every 30 minutes or so and rerun that batch. 🙂

Strikethrough! The above is no longer true. I removed the ON CONFLICT DO UPDATE from the upserted_tweets table. It seems the ctid issue shows up there as well.

Luckily I don't really need to update anything so this is really just one giant insert.

It now runs with 10+ concurrent connections each adding thousands of entries to the database simultaneously. 🎉