Subject: Re: Concurrency problem
From: Dag Gullberg <dag.gullberg@telia.com>
To: pgsql-sql@postgresql.org
In-Reply-To: <3318.1096823750@sss.pgh.pa.us>
References: <1096801131.5238.32.camel@hemma> <3318.1096823750@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Organization: 
Message-Id: <1096889735.4305.239.camel@hemma>
Mime-Version: 1.0
Date: 04 Oct 2004 13:35:35 +0200
Content-Transfer-Encoding: 8bit

Hi Tom,


	Yes, I have several clients connecting to the db, using the same
username, doing the same things, pretty much.

	Please educate me: 
When a table is accessed, is there an entry that is updated in
pg_catalog.pg_tables (or somewhere else) in such a fashion that the MVCC
cannot handle it and that consecutive selects from other clients will
cause concurrency errors? Are these entries not "locked" while accessed?

What's more in your response is very clear and it is amazing how easy it
is to slip into singel user reasoning, if not careful,  when writing
code for a multi user environment. Its a blunder, pure and simple.  I
stand corrected (and wiser). Thanks! 

/Dag

(apologies for sending this to you private address first)

sön 2004-10-03 klockan 19.15 skrev Tom Lane:
> Dag Gullberg <dag.gullberg@telia.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.