Re: pg_advisory_lock problem

2014-08-11 5:33 GMT+02:00 John R Pierce <pierce(at)hogranch(dot)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(at)postgresql(dot)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