[Pgpool-hackers] now() handling, feature request

[Tatsuo Ishii]

> Hi,
> 
> I am experimenting with Pgpool-II, and in particular with tables that have
> some form of 'now()' as their default value. I am running Pgpool-II 3.0.3
> in replication mode and I have pgpool-regclass installed. The backends are
> PostgreSQL 8.4.8. I am neither a PostgreSQL- nor a Pgpool guru.
> 
>>From looking at the code (pool_timestamp.c in particular), I understand
> that Pgpool uses the query in ATTRDEFQUERY or ATTRDEFQUERY2 to find out if
> timestamp replacement should take place on a query. If it should, the
> value of 'now()' is obtainted from one backend, and substituted for all
> backends.
> 
> Now please consider the following table, which contains timestamps without
> time zone information, all translated to UTC.
> 
> pooltest=# \d table1
>                             Table "public.table1"
>  Col |            Type             |        Modifiers
> --------+-----------------------------+--------------------------------------
>  t   | timestamp without time zone | default timezone('utc'::text, now())
> 
> There are two issues with this:
> - No substitution takes place, because the ATTRDEFQUERY doesn't match this
> column
> - If substitution WOULD take place, substituting 'now()' would be
> incorrect, because it would be a timestamp in the local time zone, instead
> of UTC, which would be 2 hours off in my case.

Yes, pgpool-II has an issue with this particular case. Thanks for
pointing it out.

> Would it be possible to take vale of pg_catalog.pg_attrdef.adsrc (in this
> example it would be 'timezone('utc'::text, now())', but I guess it could
> be anything as long as it contains 'now()'), and use that to substitute
> the timestamp value in what is sent to the backends ?

I think your idea is doable. What we have todo is, extract the default
value ("timezone('utc'::text, now())" in your case) from the system
catalog, parse it and rewrite it. However this is not a trivial work
for me and will take a while to develop it unless someone else comes
up with patches:-)
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp