On Thu, 9 Jul 2009, Tatsuo Ishii wrote:
Okay, doing some more thinking of whwat you state here ... what about
stuff like:
CREATE TABLE test ( id serial, data text );
IF there are two connections to the databases (through pgpoool) and both
issue:
INSERT INTO test ( data ) VALUES ( 'conn1' );
INSERT INTO test ( data ) VALUES ( 'conn2' );
INSERT INTO test ( data ) VALUES ( 'conn3' );
at the same time ... is it possible that backend 1 will have:
1, conn1
2, conn2
3, conn3
while backend 2 has:
1, conn2
2, conn1
3, conn3
Or does pgpool pass them back sequentially so that both sides will have it
in the same order?
Basically, with your example, it is easy to see how now() could be a
problem, as if there is a slight delay on backend2 over backend1, the
dates could / would drift some ... but what about stuff like serial keys?
Tatsuo?
As of pgpool-II 2.2 or higher, pgpool autmatically issues LOCK TABLE
when you try to execute INSERT against a table which has SERIAL column
to avoid the situation you pointed out if "insert_lock" directive in
pgpool.conf is true.
You could observe the LOCK TABLE command generated by pgpool by
looking at PostgreSQL query log.
So, the only major burn here is that 'default now()'? Since pgpool can't
"see" that ... :( doing a quick grep of the schema, the MAIA backend
looks to be clean for this:
# pg_dump -U pgsql --schema-only maia | grep -i now
file_type character varying(20) DEFAULT 'Unknown'::character varying
NOT NULL
And the main daemon looks to be modifiable easily enough, as there are
only two INSERTS that use NOW():
# grep -i "now()" amavisd-maia
$insert = "INSERT INTO maia_mail (received_date, size,
sender_email, envelope_to, subject, contents) VALUES (NOW(),?,?,?,?,?)";
$insert = "INSERT INTO maia_mail (id, received_date, size,
sender_email, envelope_to, subject, contents) VALUES (?,NOW(),?,?,?,?,?)";
So this just might work ... and hopefully those 'NOW()' statements explain
why things got out of sync the last time ... ? *cross fingers*
Thanks ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email . [email protected] MSN . [email protected]
Yahoo . yscrappy Skype: hub.org ICQ . 7615664
_______________________________________________
Pgpool-general mailing list
[email protected]
http://pgfoundry.org/mailman/listinfo/pgpool-general
