> Karsten D$(D+d(Bsterloh wrote: >> We're running pgpool-II 2.3.3 with two Postgresql 8.3.11 nodes. >> Certain tables sport an 'id' field, tied to a SEQUENCE as the default >> value. INSERT queries for these tables come in two flavours: >> - read next id from SEQUENCE and INSERT with that id explicitly set >> - INSERT without setting the id, let Postgresql set it automatically >> >> Two weeks ago, out second node got degenerated for a "duplicate key" >> backend mismatch violation, because the order of INSERTs was different >> on the backend nodes! > > This happened again last night. > While the actual order of INSERTs into the table 'zaehler' was ok this > time, I also had a look at the actual calls to nextval etc., which > resulted in this layout: > > pgpool: > - INSERT with id ..11 (from client A) > - INSERT without id (from client B) > - select nextval (from client A) > - INSERT with id ..13 (from client A) > > node0: > - INSERT with id ..11 > - INSERT without id > - select nextval > - SELECT count(*) > FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c > WHERE d.adrelid = c.oid > AND d.adsrc ~ 'nextval' > AND c.relname = 'zaehler' > (where does this query come from? pgpool?)
Yes, pgpool issues this query to check if the table has SERIAL column or not. > - INSERT with id ..13 > > node1: > - INSERT with id ..11 > - select nextval > - INSERT without id > - INSERT with id ..13 > > So, again, the relevant queries are not passed to the nodes in the same > order, which obviously results in havoc. The reason why you see this is, you don't lock the target table to properly serialize access order between different sessions. pgpool guarantees the SQL order is same among servers in *a* session but does not among sessions. To synchronize the SQL order among sessions LOCK is nessessary. You could use insert_lock or could issume explicit LOCK by yourself. > Should I check the pgpool/postgresql for certain settings? Which? > Or is this a known bug? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
