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?
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
On 07/02/2016 11:38 AM, trafdev wrote:
Yes, you are right about sessions.
Here is the case from the server log:
"deadlock detected","Process 2588 waits for ShareLock on transaction
1939192; blocked by process 16399. Process 16399 waits for ShareLock on
transaction 1939195; blocked by process
Yes, you are right about sessions.
Here is the case from the server log:
"deadlock detected","Process 2588 waits for ShareLock on transaction
1939192; blocked by process 16399. Process 16399 waits for ShareLock on
transaction 1939195; blocked by process 2588.
Process 2588:
UPDATE T1
SET
On 07/02/2016 09:54 AM, trafdev wrote:
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
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
On Fri, Jul 1, 2016 at 7:15 PM, Sfiligoi, Igor wrote:
> OK. Will change our query generation code to not use the view.
> (I have tried the LEFT JOIN approach, but it just does not seem to perform.)
> PS: Here are the numbers for the real production query (will not provide
On Thu, Jun 30, 2016 at 09:16:49AM -0500, Merlin Moncure wrote:
> It's not really necessary to create version down scripts. In five
> years of managing complex database environments we've never had to
> roll a version back and likely never will; in the event of a disaster
> it's probably better