[Pgpool-general] function epoch seems to be causing error

[Tatsuo Ishii]

Andre,

It appeared that the timestamp rewrite module assumes that the type of
the column having now() as part of default value is always timestamp
etc. Of course this assumption is not correct(in your case, bigint).
Attached patches check if the type of column is timestamp etc., and if
it's not, do no perform query rewriting.

Ideally we'd better to rewrite such column, I have no idea how to do
it...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Looking into this now. Pgpool-II is not very smart in rewriting
> complex default values. I think you hit the case. Let me think about
> work arounds for this...
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp
> 
> > Hello,
> > I am trying to execute a query containing "epoch" and "date_part" and it seems
> > that it is causing trouble for pgpool 2.3.3 when inside an insert.
> > Directly to a backend it goes well (the web app is stable enough)
> > Attached is the db schema.
> > 
> > Please, what is the best way to isolate the problem?
> > Regards.
> > Andre Felipe
> > 
> > 
> > 
> > 
> > Database error: Invalid SQL: INSERT INTO
> > phpgw_cal(uid,title,owner,priority,is_public,category)
> > values('- at 127.0.0.1','teste',1003,2,1,'')
> > pgsql Error: -1 (ERROR: invalid input syntax for integer: "2010-08-16
> > 14:40:35.379241-03")
> > 
> > File: /var/www/expresso/calendar/inc/class.socalendar_sql.inc.php
> > Line: 942
> > Function: socalendar_::save_event / socalendar::add_entry / bocalendar::update /
> > ExecMethod
> > 
> > Session halted.
> > 
> > 
> > 
> > snippet from the schema:
> > 
> > last_update    | bigint                 | default (date_part('epoch'::text,
> > ('now'::text)::timestamp(3) with time zone) * (1000)::double precision)
> > 
> > 
> > 
> > 
> > 
> _______________________________________________
> Pgpool-general mailing list
> Pgpool-general at pgfoundry.org
> http://pgfoundry.org/mailman/listinfo/pgpool-general
-------------- next part --------------
Index: pool_timestamp.c
===================================================================
RCS file: /cvsroot/pgpool/pgpool-II/pool_timestamp.c,v
retrieving revision 1.7.2.1
diff -c -r1.7.2.1 pool_timestamp.c
*** pool_timestamp.c	10 Aug 2010 08:48:37 -0000	1.7.2.1
--- pool_timestamp.c	17 Aug 2010 04:42:23 -0000
***************
*** 107,113 ****
  static TSRel*
  relcache_lookup(TSRewriteContext *ctx)
  {
! #define ATTRDEFQUERY "SELECT attname, coalesce(d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%', false)" \
  	" FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " \
  	" LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)" \
  	" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid = '%s'::regclass::oid" \
--- 107,119 ----
  static TSRel*
  relcache_lookup(TSRewriteContext *ctx)
  {
! #define ATTRDEFQUERY "SELECT attname, coalesce((d.adsrc = 'now()' OR d.adsrc LIKE '%%''now''::text%%')" \
! 	" AND (a.atttypid = 'timestamp'::regtype::oid OR" \
! 	" a.atttypid = 'timestamp with time zone'::regtype::oid OR" \
! 	" a.atttypid = 'date'::regtype::oid OR" \
! 	" a.atttypid = 'time'::regtype::oid OR" \
! 	" a.atttypid = 'time with time zone'::regtype::oid)" \
!     " , false)" \
  	" FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a " \
  	" LEFT JOIN pg_catalog.pg_attrdef d ON (a.attrelid = d.adrelid AND a.attnum = d.adnum)" \
  	" WHERE c.oid = a.attrelid AND a.attnum >= 1 AND a.attisdropped = 'f' AND c.oid = '%s'::regclass::oid" \