Re: pg_advisory_lock problem

[Rémi Cura <remi.cura@xxxxxxxxx>]

Hey,

as you (both?) suggested it works using advisory lock used at the beginning and end of the transaction. This way there is no upsert at all if the element is locked? (I used general advisory lockbut in the same way as transactionnal lock)

The issue in this case is simple : I have to use about 100k advisory locks, which is a big memory requirement for my hardware :-(

Merlin I'm afraid I don't understand what is "vanilla" LOCK TABLE.

I can't really use a lock table because each query upsert sequentially into 3 tables, doing lots of computing between.

I use parallel query to compute faster (load dividing). I guess it would be very slow with about 8 parallel queries with locks.

I should test this lock approach to be sure.

Thanks both of you !

Cheers,
Rémi-C

2014-08-11 17:51 GMT+02:00 Merlin Moncure <mmoncure@xxxxxxxxx>:

On Mon, Aug 11, 2014 at 9:49 AM, Kevin Grittner <kgrittn@xxxxxxxxx> wrote:
> Rémi Cura <remi.cura@xxxxxxxxx> wrote:
>
>> 2014-08-11 5:33 GMT+02:00 John R Pierce <pierce@xxxxxxxxxxxx>:
>
>>> 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.
>
>> Oh no I hoped it was something fixable
>
> Well, it might be.  Try using a transactional advisory lock and
> letting it expire at the end of the transaction, rather than
> explicitly releasing it before the transaction commits.  Depending
> on some other details, that might get it to do what you want.

Better to use vanilla LOCK TABLE statement in my opinion for this purpose.

merlin