Hi! Karsten Düsterloh 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! > > pgpool: > - INSERT with id ...68 > - INSERT without id > - INSERT with id ...70 > > node0: > - INSERT with id ...68 > - INSERT without id => 69 > - INSERT with id ...70 > - INSERT with id ...71 > > node1: > - INSERT with id ...68 > - INSERT with id ...70 > - INSERT with id ...71 > - INSERT without id => ERROR! > > This is rather worrisome - any ideas what might have caused that?
The culprit, as I found out now, is the *client's* psql version. While our PostgeSQL servers are running 8.x, our clients still use a wild bunch of potentially old psql versions (with 7.0.3, 7.3.4, 8.0.1 and 8.3.14 being the most common ones). Given the table create table x(id SERIAL, val int); and the query insert into x(val) values(0); the resulting PostgreSQL query sequence is different depending on the client's psql version: psql 7.x: > 2011-05-10 10:39:34.358 CEST [25507] LOG: duration: 0.415 ms statement: > INSERT INTO xxx(val) values(0); psql 8.x: > 2011-05-10 10:38:47.036 CEST [24423] LOG: duration: 0.042 ms statement: > BEGIN > 2011-05-10 10:38:47.038 CEST [24423] LOG: duration: 0.045 ms statement: > LOCK TABLE "xxx" IN SHARE ROW EXCLUSIVE MODE > 2011-05-10 10:38:47.038 CEST [24423] LOG: duration: 0.162 ms statement: > INSERT INTO xxx(val) values(0); > 2011-05-10 10:38:47.041 CEST [24423] LOG: duration: 0.312 ms statement: > COMMIT The missing table lock with psql 7.x will allow for SERIAL usage confusion if multiple complex queries touch the same table. We were going to remove the 7.x clients anyway, so that's not a particular problem, but I think the issue should be noted in the manual for replication/loadbalancing as well — it only mentions parallel mode being problematic with < 7.4. Yours, Karsten _______________________________________________ Pgpool-general mailing list [email protected] http://pgfoundry.org/mailman/listinfo/pgpool-general
