Re: Concurrency problem

Dag Gullberg <dag(dot)gullberg(at)telia(dot)com> writes:
> Warning: pg_query(): Query failed: ERROR: tuple concurrently updated
> CONTEXT: PL/pgSQL function "get_rights" line 5 at SQL statement in
> /home/site/PHP/db_func.php on line 301

> Code of get_rights:
> GRANT SELECT ON c.users TO
> adm,w3;
> SELECT rights INTO result
> FROM c.users
> WHERE usr_id=uid;

> REVOKE ALL ON c.users FROM w3,adm;
> RETURN result;

Do you have a bunch of clients doing this in parallel? If so, the
problem is probably coming from two instances of the function trying to
update the catalog entry for c.users at the same time. The approach
is fundamentally broken anyhow, because there is nothing stopping
client 2 from revoking the rights in between client 1 doing his GRANT
and his SELECT.

The right way to do what you seem to want (allow only this function to
access the table) is not to flap the rights settings back and forth
like that. Grant SELECT rights to a specific userid and make the
function be owned by that userid and be labeled SECURITY DEFINER.

regards, tom lane