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

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.

On Tue, Feb 9, 2016 at 6:28 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

>
>
> On Monday, February 8, 2016, David G. Johnston <david.g.johnston@gmail.com>
> wrote:
>
>> On Friday, February 5, 2016, <mtakvel@gmail.com> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference:      13920
>>> Logged by:          Valeriy
>>> Email address:      mtakvel@gmail.com
>>> PostgreSQL version: 9.5.0
>>> Operating system:   Ubuntu
>>> Description:
>>>
>>> Hello, I have few high load big tables. My logic calls
>>> pg_try_advisory_xact_lock(bitint) for locking row in current table. As I
>>> see
>>> with bigint param pg_try_advisory_xact_lock lock same ids for all my
>>> tables.
>>> Insthead lock only row in one current table. Looks like this is bug and
>>> will
>>> be cool if you fix it.
>>>
>>>
>> Likely working as designed.  If you wish to provide an example of what
>> you are doing we can probably explain your misunderstanding.  Basically,
>> though, there is nothing about the ID you pass to the advisory lock
>> functions that cause them to be associated with a table.  The ID is simply
>> a number.   You should try the two-key version and associate the first key
>> with the table (probably oid) and the second with the row on that table.
>>
>>
> Though the two-arg uses Integer so maybe not...
>
> You should poprobably explain your use case a bit on the -general list if
> you'd like to discuss alternatives.  But the behavior described is how
> things work right now.
>
>  David J.
>



--
Thanks!
Valeriy