Postgres Pro
Downloads
Home   >   mailing lists

Re: pg_advisory_lock problem - Mailing list pgsql-general

From Rémi Cura
Subject Re: pg_advisory_lock problem
Date
Msg-id CAJvUf_tae2zvVXr_KSZprw2KvGkKahbx7s6vc5=oN-6LiuzdLA@mail.gmail.com
Whole thread Raw
In response to Re: pg_advisory_lock problem  (John R Pierce <pierce@hogranch.com>)
Responses Re: pg_advisory_lock problem  (Kevin Grittner <kgrittn@ymail.com>)
List pgsql-general
Tree view



2014-08-11 5:33 GMT+02:00 John R Pierce <pierce@hogranch.com>:

---------
PERFORM pg_advisory_lock(#1 , 2 or 3 depending on the table#,id of the
stuff I want to upsert) ;
WITH stuff_to_upsert ()
,updating AS (update returning id)
,inserting AS (insert if not updated)
PERFORM pg_advisory_unlock(same as above).
--------


ah, you're releasing the lock before the insert is committed, since this is all within a function call, its entirely within a single transaction.



--
john r pierce                                      37N 122W
somewhere on the middle of the left coast




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


Hey,
thanks your two !

Oh no I hoped it was something fixable
, because I don't think I can fix this transaction problem.
I guess because the big query is called in one plpgsql function it will be in one transaction
,so even if I resorted to plpython in the upserting functions to issue commit it shouldn't work.
The only way would be to transform the big query plpgsqp function into another pl function,
but I can't really afford it :-(

Damn, it is really hard to use a table with 2 different session at the same time!

Thanks for your answers, and for the much needed typo correction ;-)

Cheers,
Rémi-C

pgsql-general by date:

Previous
From: John R Pierce
Date:
Subject: Re: pg_advisory_lock problem
Next
From: Jov
Date:
Subject: wired problem for a 9.1 slave：receive wal but do not replay it？