[Pgpool-general] PgPool install, Part Deux

[Marc G. Fournier]

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 . scrappy at hub.org                              MSN . scrappy at hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664