Re: BUG #13920: pg_try_advisory_xact_lock bigint trouble

Thanks Tomcas, that the answer for my problem. Greate thanks!

On Fri, Mar 4, 2016 at 10:15 PM, Thomas Munro <thomas.munro@enterprisedb.com
> wrote:

> On Tue, Feb 9, 2016 at 10:01 PM, Valeriy A. <mtakvel@gmail.com> wrote:
> > Here my simple example
> >
> > --- SQL Begin
> > create table table1 (
> >     id bigserial PRIMARY KEY
> >     -- other fields);
> >
> > create table table2 (
> >     id bigserial PRIMARY KEY
> >     -- other fields);
> >
> >
> > CREATE FUNCTION do_action_on_table1(keyID bigint ) returns int2 LANGUAGE
> > plpgsql AS $$
> > DECLARE
> >   isLocked boolean;
> > BEGIN
> >   EXECUTE 'SELECT pg_try_advisory_xact_lock($1)' INTO isLocked USING
> keyID;
> >
> >   if isLocked THEN
> >      --SOME action on table1
> >      RETURN 1;
> >   END IF;
> >
> >   RETURN 0;
> > END$$
> >
> > CREATE FUNCTION do_action_on_table2(keyID bigint ) returnS int2
> > LANGUAGE plpgsql
> > AS $$
> > DECLARE
> >   isLocked boolean;
> > BEGIN
> >   EXECUTE 'SELECT pg_try_advisory_xact_lock($1)' INTO isLocked USING
> keyID;
> >
> >   if isLocked THEN
> >      --SOME action on table2
> >      RETURN 1;
> >   END IF;
> >
> >   RETURN 0;
> > END$$
> > -- SQL End
> >
> > In this case if sequences fields has same values then calls of functions
> > will be lock both tables and miss actions.
>
> Have you considered using row locks with SKIP LOCKED instead of
> advisory locks?  Then you don't have to come up with your own scheme
> to map tables and keys to integer space.  Something like this:
>
> CREATE OR REPLACE FUNCTION do_action_on_table1(keyID bigint)
> RETURNS int2 LANGUAGE plpgsql AS
> $$
> BEGIN
>   PERFORM * FROM table1 WHERE id = $1 FOR UPDATE SKIP LOCKED;
>   IF FOUND THEN
>      -- some action on table1
>      RETURN 1;
>   END IF;
>
>   RETURN 0;
> END
> $$;
>
> It seems likely you want to load data out of the row into variables
> for processing, so you could replace the PERFORM statement with a
> SELECT ... INTO ... FOR UPDATE SKIP LOCKED.
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>



--
Thanks!
Valeriy