Re: BUG #13920: pg_try_advisory_xact_lock bigint trouble - Mailing list pgsql-bugs

On Tue, Feb 9, 2016 at 3:01 AM, 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

The problenm is
you can do it via:
create sequence lock_seq;
create domain lockid bigint default nextval('lock_seq');

create table table1 (
    id lockid PRIMARY KEY
    -- other fields);

create table table2 (
    id lockid PRIMARY KEY
    -- other fields);

...

merlin