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

[Martijn Grendelman]

Hi Tatsuo,

>> 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:-)

I may have found someone, who is willing and able to produce a patch, if
it is not too difficult to implement.

Are there any corner cases or pitfalls we should look out for?

Best regards,
Martijn.