Postgres Pro
Downloads
Home   >   mailing lists

Thread: FW: statement failure

FW: statement failure

From
"Little, Douglas"
Date:

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

 cid:image001.jpg@01CABEC8.D4980670  orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com

 

Attachment

Re: FW: statement failure

From
Merlin Moncure
Date:
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 

Re: FW: statement failure

From
"Little, Douglas"
Date:

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 

Re: FW: statement failure

From
Magnus Hagander
Date:
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/