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

[Martijn Grendelman]

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.

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 ?

As I am not really a C programmer, and not very familiar with Pgpool
internals, I have no idea what the implications would be.

Best regards,
Martijn.