Re: pg_advisory_lock problem

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(at)gmail(dot)com>:

> On Mon, Aug 11, 2014 at 9:49 AM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> > Rémi Cura <remi(dot)cura(at)gmail(dot)com> wrote:
> >
> >> 2014-08-11 5:33 GMT+02:00 John R Pierce <pierce(at)hogranch(dot)com>:
> >
> >>> 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
>