Re: BUG #13920: pg_try_advisory_xact_lock bigint trouble

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(dot)g(dot)johnston(at)gmail(dot)com> wrote:

>
>
> On Monday, February 8, 2016, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
>
>> On Friday, February 5, 2016, <mtakvel(at)gmail(dot)com> wrote:
>>
>>> The following bug has been logged on the website:
>>>
>>> Bug reference: 13920
>>> Logged by: Valeriy
>>> Email address: mtakvel(at)gmail(dot)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