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 commits
changes in between T1 and T2 updates to reduce locks.
trans2 creates two temp tables (T1_tmp and T2_tmp), copies data from
files and updates T1 and T2 using [WITH ... AS ... RETURNING] approach.
Unexciting rows should be inserted, existing updated (sum-ed with values
from temp tables). Both T1 and T2 must be updated in the same transaction.
There are no any FKs anywhere in these tables.
trans1:
DROP TABLE IF EXISTS trans1_T_tmp;
CREATE TABLE trans1_T_tmp (...);
COMMIT
COPY from FILE into trans1_T_tmp;
BEGIN
UPDATE T1
SET ...
FROM trans1_T_tmp
WHERE ...
COMMIT
BEGIN
UPDATE T2
SET ...
FROM (SELECT ... FROM trans1_T_tmp)
WHERE ...
DROP TABLE trans1_T_tmp;
COMMIT
trans2:
BEGIN
CREATE TABLE trans2_T1_tmp (...);
COPY from FILE into trans2_T1_tmp;
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 ...;
DROP TABLE trans2_T1_tmp;
CREATE TABLE trans2_T2_tmp (...);
COPY from FILE into trans2_T2_tmp;
WITH agg_tmp AS (SELECT ... FROM trans2_T2_tmp GROUP BY ...), upd AS
(UPDATE T2 SET ... FROM agg_tmp s WHERE ... RETURNING ...) INSERT INTO
T2 (...) SELECT ... FROM agg_tmp s LEFT JOIN upd t ON (...) WHERE ...;
DROP TABLE trans2_T2_tmp;
COMMIT
By an unknown [for me] reason trans1 and trans2 often produce deadlocks...
Could you explain what's the reason for that and how to avoid them?
And is there exist a better replacement for WITH ... AS ... RETURNING ?
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 WITH
construct:
https://www.postgresql.org/docs/9.5/static/sql-insert.html
ON CONFLICT Clause
https://www.depesz.com/2015/05/10/waiting-for-9-5-add-support-for-insert-on-conflict-do-nothingupdate/
If not you might to take a look at:
http://stackoverflow.com/questions/17267417/how-to-upsert-merge-insert-on-duplicate-update-in-postgres
Bulk upsert with lock
Thanks!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
