[pgpool-general: 278] Re: Timestamp problem again

[Bartosz Rebeś]

Wiadomość napisana przez Tatsuo Ishii w dniu 2012-02-05, o godz. 01:39:

>> My previous problem was easily solved with pgpool_regclass (thanks, Tatsuo), but now Iʼve encountered a bit more complex one.
>> We have some tables with triggers, which update timestamp column. Because they are executed on backend side, timestamps are getting out of sync. Is there any solution applicable on pgpool side, so we wonʼt have to give up using triggers?
> 
> Can you elaborate on what the trigger is doing?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp


Sorry for refreshing the old tread, unfortunatelly I wasn’t able to answer earlier.

The easiest way to reproduce the problem is as follows:
1. Create table and trigger
# CREATE TABLE foo (trigger BOOLEAN, time TIME);

# CREATE FUNCTION bar() RETURNS TRIGGER AS
$BODY$
BEGIN
 IF NEW.trigger = 't' THEN
   NEW.time := NOW();
 END IF;
 RETURN NEW;
END
$BODY$
LANGUAGE plpgsql VOLATILE;

# CREATE TRIGGER bar BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE bar();

2. Test:
# INSERT INTO foo VALUES('f', NOW());
This will work correctly.

# UPDATE foo SET trigger='t';
This will result in backend desynchronisation.


-- 
Regards,
Bartosz Rebeś

Developer at DynaCloud
E-mail: b.rebes at dynacloud.com
Mobile.: +31 647 111 631

DynaCloud 
Web: www.dynacloud.com
Email: info at dynacloud.com
Tel.: +31 (0) 538 200 921