Hello.
I have two transactions (trans1 and trans2) updating tables T1 and T2 in
the same order, but in a different way.
trans1 creates temp table, copies data from a file and updates tables T1
and T2 from this temp table (using basic UPDATE form). It even commits
changes in between T1 and T2
in relation ""T1""","WITH agg_tmp AS (SELECT ... FROM
trans2_T1_tmp GROUP BY ...), upd AS (UPDATE T1 SET ... FROM agg_tmp s
WHERE ... RETURNING ...) INSERT INTO T1 (...) SELECT ... FROM agg_tmp s
LEFT JOIN upd t ON (...) WHERE ...;
Process 71221:
Best guess you are running into what is described here:
https://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-DEADLOCKS
Both transactions are holding locks on rows in T1 that the other wants
also.
I may be missing something, but I am not sure why it is necessary to run
both
I've also replaced "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt})
{ins_stmt}" to "INSERT INTO .. ON CONFLICT DO UPDATE ...", but no
success - row level deadlocks still occur...
Is there a way to tell Postgres to update rows in a specified order?
Or maybe LOCK TABLE should be used?
Sessions
I've already switched to 'UPSERT', it didn't resolved deadlock issue by
itself... Added LOCK TABLE ... IN EXCLUSIVE MODE; to one session, hope
it will help.
You did not mention what version of Postgres you are using, if it is
9.5+ you have the 'UPSERT' option available instead of using the WIT
"less" is much better for opening huge text files in *nix for reading.
On 07/05/16 15:13, Christofer C. Bell wrote:
On Tue, Jul 5, 2016 at 3:17 PM, Paul Linehan mailto:lineh...@tcd.ie>> wrote:
> a good point, but I would prefer NOT to open a 324GB backup file in a text
> editor. I can h
