Обсуждение: FW: statement failure
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
orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com
Вложения
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.
both dev and prod are 8.2.15
Version string PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 3 2012 20:28:56
can you tell me what change in 8.3 maybe Greenplum incorporated some of the changes in their build.
Thanks
From: Merlin Moncure [mailto:mmoncure@gmail.com]
Sent: Wednesday, March 06, 2013 9:47 AM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] FW: statement failure
On Wed, Mar 6, 2013 at 9:38 AM, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> wrote:
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.
can you double check pg version on both dev and prod. My guess is that prod is 8.3+ -- casting rules were tightened with 8.3.
merlin
On Thu, Mar 7, 2013 at 5:33 PM, Little, Douglas <DOUGLAS.LITTLE@orbitz.com> wrote: > both dev and prod are 8.2.15 > > Version string PostgreSQL 8.2.15 (Greenplum Database 4.2.2.4 build 1) on > x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct 3 > 2012 20:28:56 > > > > can you tell me what change in 8.3 maybe Greenplum incorporated some of the > changes in their build. Since they're not actually 8.2, you should probably ask Greenplum about the differences - I'm sure their support people will know. What Merlin was referring to is the removal of implicit casts - it's listed as the very first point under migration to 8.3 back in the old release notes (which can still be found as a chapter in the 9.2 release notes for example, even though 8.3 is fully unsupported by now) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/