FW: statement failure - Mailing list pgsql-general

Hi,

I have a dev and prod Greenplum system (4.2.2.4) that is based on PG 8.2.15

I have a function that’s been working fine on dev, but when implemented in prod had a syntax error.

I was easily able to fix by casting, but I can’t see why the message didn’t show up on our dev machine.

I’ve reviewed the guk settings and can’t find anything relevant.

Any ideas what might be different and causing problems.

Thanks in advance

ERROR:  column "logtransaction" is of type integer but expression is of type text

LINE 1: ...abase,loguser,loghost,logsession, logsessiontime, logtransac...

                                                             ^

HINT:  You will need to rewrite or cast the expression.

QUERY:  insert into dba_work.table_usage_log(logtime, logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction, logseverity, logstate, logdebug) select logtime, logdatabase,loguser,loghost,logsession, logsessiontime, logtransaction, logseverity, logstate, logdebug from dba_work.gp_log_master_ext where logmessage ilike '%select%' and logmessage ilike '%from%' and logmessage not ILIKE 'execute%' and logmessage not ILIKE '%gp_%' and logmessage not ILIKE '%pg_%' and loguser not in ('gpadmin','gp_php_read','gpmon','ods','dlittle') and date_trunc('day',logtime) =  $1

CONTEXT:  PL/pgSQL function "table_usage_analyzer" line 31 at SQL statement

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com