Re: [GENERAL] 'tuple concurrently updated' error when granting permissions

[Jason Daly]

Thanks Tom et al,
I appreciate the explanation.
I am certainly more appreciative of what is going on behind the scenes now
when I see 'tuple concurrently updated' errors.

I couldn't initially find a way to grant select on all present *and
future*tables in a schema, which is why we took the approach we did.
Now I have
discovered 'ALTER DEFAULT PRIVILEGES' which will allow me to remove these
concurrent permission grants - I only need to exec one ALTER DEFAULT
PRIVILEGES command when the schema is created.

Much obliged,
Jason



On Sun, Mar 2, 2014 at 6:09 AM, Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 02/27/2014 06:54 PM, Jason Daly wrote:
> >> When I attempt to execute the non-query "GRANT SELECT ON TABLE
> >> . TO ", I occasionally encounter an exception from
> >> postgresql, 'tuple concurrently updated'.
>
> > My guess this is covered here:
> > http://rhaas.blogspot.com/2013/07/mvcc-catalog-access.html
>
> While MVCC catalog scans are a necessary step towards making this kind of
> thing safe, they aren't by any means sufficient.  It'd be necessary to add
> some kind of locking scheme if you want to avoid "tuple concurrently
> updated" errors.  This is not really any different from the situation
> where two transactions both want to update the same row in a user table:
> unless the application takes extra steps to serialize the updates, you're
> going to get "tuple concurrently updated" errors.
>
> We do have such locking for DDL on tables/indexes, but the theory in the
> past has been that it's not worth the trouble for objects represented by
> single catalog rows, such as functions or roles.  You can't corrupt the
> database with concurrent updates on such a row, you'll just get a "tuple
> concurrently updated" error from all but the first-to-arrive update.
> So the benefit-to-work ratio hasn't been high enough to motivate anyone
> to work on it.  Possibly this will change sometime in the future, but
> I wouldn't hold my breath waiting.
>
> In the meantime, you could consider using an application-managed advisory
> lock if you really need such grants to work transparently.
>
> However, might I suggest that you're doing it wrong?  If you have enough
> traffic on permission grants for a single table that concurrent updates
> are a real hazard, it would likely be much better to invent a group role
> that holds a relatively static set of rights, and implement the everyday
> permissions changes by granting or revoking membership in the group role.
> Not only is this using SQL permissions the way they were meant to be used,
> but it should remove the concurrent-updates problem, because role
> memberships are represented by distinct rows in pg_auth_members.  Also,
> our implementation isn't terribly efficient for cases where lots and lots
> of roles have separate permissions to a single object --- I think the acl
> arrays are just searched linearly, and in any case performance would
> certainly go south once they got big enough to require toasting.
>
> regards, tom lane
>

Re: [GENERAL] 'tuple concurrently updated' error when granting permissions

[Tom Lane]

Adrian Klaver  writes:
> On 02/27/2014 06:54 PM, Jason Daly wrote:
>> When I attempt to execute the non-query "GRANT SELECT ON TABLE
>> . TO ", I occasionally encounter an exception from
>> postgresql, 'tuple concurrently updated'.

> My guess this is covered here:
> http://rhaas.blogspot.com/2013/07/mvcc-catalog-access.html

While MVCC catalog scans are a necessary step towards making this kind of
thing safe, they aren't by any means sufficient.  It'd be necessary to add
some kind of locking scheme if you want to avoid "tuple concurrently
updated" errors.  This is not really any different from the situation
where two transactions both want to update the same row in a user table:
unless the application takes extra steps to serialize the updates, you're
going to get "tuple concurrently updated" errors.

We do have such locking for DDL on tables/indexes, but the theory in the
past has been that it's not worth the trouble for objects represented by
single catalog rows, such as functions or roles.  You can't corrupt the
database with concurrent updates on such a row, you'll just get a "tuple
concurrently updated" error from all but the first-to-arrive update.
So the benefit-to-work ratio hasn't been high enough to motivate anyone
to work on it.  Possibly this will change sometime in the future, but
I wouldn't hold my breath waiting.

In the meantime, you could consider using an application-managed advisory
lock if you really need such grants to work transparently.

However, might I suggest that you're doing it wrong?  If you have enough
traffic on permission grants for a single table that concurrent updates
are a real hazard, it would likely be much better to invent a group role
that holds a relatively static set of rights, and implement the everyday
permissions changes by granting or revoking membership in the group role.
Not only is this using SQL permissions the way they were meant to be used,
but it should remove the concurrent-updates problem, because role
memberships are represented by distinct rows in pg_auth_members.  Also,
our implementation isn't terribly efficient for cases where lots and lots
of roles have separate permissions to a single object --- I think the acl
arrays are just searched linearly, and in any case performance would
certainly go south once they got big enough to require toasting.

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] 'tuple concurrently updated' error when granting permissions

[Adrian Klaver]

On 02/27/2014 06:54 PM, Jason Daly wrote:

Hi,
When I attempt to execute the non-query "GRANT SELECT ON TABLE
. TO ", I occasionally encounter an exception from
postgresql, 'tuple concurrently updated'.

I think the tuple it is referring to is the role.

Our app is multi-threaded, and it is very possible for more than one
thread to be attempting to grant permissions to the same role at the
same time. (the situation is that we are creating tables on the fly, and
each time we create a new table we have to grant permissions to a role
so it can read from it).

In my view, it is a bug that postgresql cannot handle multiple
concurrent grants to a role.

We're using the latest version of postgresql (9.3) in the context of an
AWS RDS.

Should I enter a bug report, or can someone convince me that we should
single-thread this part of our app (or work around it somehow)?



My guess this is covered here:

http://rhaas.blogspot.com/2013/07/mvcc-catalog-access.html

In the meantime, I would say single thread is the order of the day.



Please respond to jasond...@trimblegeospatial.com


-Jason




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] 'tuple concurrently updated' error when granting permissions

[Jason Daly]

Hi,
When I attempt to execute the non-query "GRANT SELECT ON TABLE
. TO ", I occasionally encounter an exception from
postgresql, 'tuple concurrently updated'.

I think the tuple it is referring to is the role.

Our app is multi-threaded, and it is very possible for more than one thread
to be attempting to grant permissions to the same role at the same time.
(the situation is that we are creating tables on the fly, and each time we
create a new table we have to grant permissions to a role so it can read
from it).

In my view, it is a bug that postgresql cannot handle multiple concurrent
grants to a role.

We're using the latest version of postgresql (9.3) in the context of an AWS
RDS.

Should I enter a bug report, or can someone convince me that we should
single-thread this part of our app (or work around it somehow)?

Please respond to jasond...@trimblegeospatial.com

-Jason

Re: [GENERAL] tuple concurrently updated

[Andrus]

That's a bit interesting ... what PG version is this exactly?


"PostgreSQL 8.1.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 
(mingw-special)"



What else might be touching that table at the same time?


Maybe some other client executes same or other command at same time in same 
database.
templsabi table is created, dropped and updated frequently by a number of 
clients concurrently.



Do you have autovacuum enabled?


Yes.

Andrus. 



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] tuple concurrently updated

[Tom Lane]

"Andrus" <[EMAIL PROTECTED]> writes:
> My application receives the folllowing error sometimes.
> 7/XX000:Error while executing the query;ERROR:  tuple concurrently updated
> CONTEXT:  SQL statement "DROP TABLE templsabi"PL/pgSQL function "drop_table" 
> line 2 at execute statement

That's a bit interesting ... what PG version is this exactly?  What else
might be touching that table at the same time?  Do you have autovacuum
enabled?

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

[GENERAL] tuple concurrently updated

[Andrus]

My application receives the folllowing error sometimes.

Any idea how to fix ?

Andrus.

7/XX000:Error while executing the query;ERROR:  tuple concurrently updated

CONTEXT:  SQL statement "DROP TABLE templsabi"PL/pgSQL function "drop_table" 
line 2 at execute statement


SELECT drop_table('templsabi');CREATE TABLE templsabi AS
   select
 toode,
 osak as sihtyksus,
 partii,
 laosumma,

 1 as lj, 0 as st, 0 as vm,
 0 as kaubasumma,
 kogus,
 0 as kogpak,
 CAST('' AS CHAR(10)) as yksus,
 CAST('' AS CHAR(1)) as doktyyp
   from HETKESEI
   where true
AND osak LIKE 'TARTU%' ESCAPE '!'

AND toode='DIS123103'
;SELECT * FROM templsabi

drop_table is defined as

CREATE OR REPLACE FUNCTION drop_table(TEXT)
 RETURNS VOID STRICT LANGUAGE plpgsql AS $$
BEGIN
   EXECUTE 'DROP TABLE ' || $1;
   EXCEPTION WHEN UNDEFINED_TABLE THEN
RETURN;
END;
$$;


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match