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
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
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
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.
- 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.
- 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 (
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 (
(NULL::text, NULL::timestamp, NULL::text, NULL::text, NULL::text),
, upserted_tweets AS (
INSERT INTO tweet (twitter_id, created_at, contents, search_hits)
SELECT twitter_id, created_at, contents, search_hits
ORDER BY 1
ON CONFLICT DO NOTHING
RETURNING id, twitter_id
, upserted_tweets_with_urls AS (
SELECT id, urls
JOIN cte_data USING (twitter_id)
, unique_urls AS (
SELECT DISTINCT UNNEST(urls) url
, new_urls AS (
RIGHT JOIN unique_urls USING (url)
WHERE id IS NULL
ORDER BY 1
, inserted_urls AS (
INSERT INTO url_starting (url)
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. 🎉