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(dot)munro(at)enterprisedb(dot)com
> wrote:

> On Tue, Feb 9, 2016 at 10:01 PM, Valeriy A. <mtakvel(at)gmail(dot)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