Re: [GENERAL] Stored procedure version control

2016-07-02 Thread Karsten Hilbert
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 t

Re: --EXTERNAL--Re: [GENERAL] PSQL does not remove obvious useless joins

2016-07-02 Thread Kevin Grittner
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 > details): > Original

[GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread trafdev
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

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread Adrian Klaver
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

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread trafdev
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

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread Adrian Klaver
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 25

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread trafdev
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

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

2016-07-02 Thread trafdev
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