Обсуждение: BUG #13920: pg_try_advisory_xact_lock bigint trouble
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.
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 <javascript:;> > 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. David J.
On Monday, February 8, 2016, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Friday, February 5, 2016, <mtakvel@gmail.com > <javascript:_e(%7B%7D,'cvml','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.
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
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
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
Thanks Tomcas, that the answer for my problem. Greate thanks! On Fri, Mar 4, 2016 at 10:15 PM, Thomas Munro <thomas.munro@enterprisedb.com > wrote: > 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 > -- Thanks! Valeriy