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

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