On 2/12/2007 8:18 AM, David Brain wrote: > Hi, > > Jan Wieck wrote: >> On 2/9/2007 12:21 PM, David Brain wrote: >>> What failed was a _separate_ application that was importing data into >>> the master database, this application was using a non-superuser >>> account to insert data into one of the tables in the replication set, >>> and it was this process that saw the 'permission denied for sequence >>> sl_action_seq' error. >> >> The only time such an import process is supposed to access the >> sl_action_seq would be inside of the log trigger, which is supposed to >> be owned by the Slony database superuser and the underlying function >> used to be security definer. So the question is, how did your >> application end up accessing that sequence different from that path? > > Yes, I think I have a theory as to what may have happened, and come up > with a potential solution. It would be useful to know if this sounds > reasonable. > > The application that was inserting into the DB was populating two tables > one with an foreign key into the other, it was doing this by making an > insert into the parent table - using nexval(seqname) then populating the > child table with a lastval(). So my guess, and it is somewhat of a > guess as to what happened is that while this application worked fine > with no additional triggers in place, with the addition of the Slony > triggers it caused the call to lastval() to access the wrong sequence. > It would seem that the fix for this is to use curval(seqname) rather > than lastval() in the inserting application.
Calling lastval() instead of currval() is not safe against concurrent clients to begin with, so your application might end up using the wrong values for the referenced parent row. However, unless you've stumbled across a bug in Postgres itself, there is nothing there that would cause any of the code to access a sequence different from the one your application specifies in the call. Can you find out either from your application log or from the postmaster log what the actual SQL statement was, that received that error? Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== [EMAIL PROTECTED] # _______________________________________________ Slony1-general mailing list [email protected] http://gborg.postgresql.org/mailman/listinfo/slony1-general
