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