Thread: Error in creating the backend query
PostgreSQL 8.3.7 running on FreeBSD.
The following query:
update tblksalerts
set cleartime = x_cleartime,
laststatusid = x_statusid,
lastreplytext = x_replytxt,
lasttesttime = x_testtime,
lasteventsource = x_eventsource,
lasteventid = x_eventid,
replyval = x_replyval,
trend = mytrend,
alertoccurrence = testcount
where ksalertssysid = alertsrecord.ksalertssysid;
being issued via a pl/pgsql stored procedure is being translated by the backend as follows:
update tblksalerts set cleartime = '2009-07-29 10:49:50'::TIMESTAMP, laststatusid = 7::INTEGER, lastreplytext = '0'::VARCHAR, lasttesttime = '2009-07-29 10:49:50'::TIMESTAMP, lasteventsource is NULL::VARCHAR, lasteventid is NULL::INTEGER, replyval = 0::REAL, trend = ''::varchar ( 1 ), alertoccurrence = 0::integer where ksalertssysid = 1737078
and is generating a syntax error: syntax error at or near "is" at character 192
Please note the “lasteventsource is null” instead of “lasteventsource = null” which is being generated when the value of x_eventsource is null.
We have implemented a workaround by setting the value to a blank string, but I believe this is a problem nonetheless.
Benjamin Krajmalnik wrote: > update tblksalerts set cleartime = '2009-07-29 10:49:50'::TIMESTAMP, > laststatusid = 7::INTEGER, lastreplytext = '0'::VARCHAR, lasttesttime = > '2009-07-29 10:49:50'::TIMESTAMP, lasteventsource is NULL::VARCHAR, > lasteventid is NULL::INTEGER, replyval = 0::REAL, trend = ''::varchar ( > 1 ), alertoccurrence = 0::integer where ksalertssysid = 1737078 > Please note the "lasteventsource is null" instead of "lasteventsource = > null" which is being generated when the value of x_eventsource is null. Do you have transform_null_equals set? -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I tried it with both on and off, and it did not make a difference. > -----Original Message----- > From: Alvaro Herrera [mailto:alvherre@commandprompt.com] > Sent: Wednesday, July 29, 2009 3:20 PM > To: Benjamin Krajmalnik > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Error in creating the backend query > > Benjamin Krajmalnik wrote: > > > update tblksalerts set cleartime = '2009-07-29 10:49:50'::TIMESTAMP, > > laststatusid = 7::INTEGER, lastreplytext = '0'::VARCHAR, lasttesttime > = > > '2009-07-29 10:49:50'::TIMESTAMP, lasteventsource is NULL::VARCHAR, > > lasteventid is NULL::INTEGER, replyval = 0::REAL, trend = ''::varchar > ( > > 1 ), alertoccurrence = 0::integer where ksalertssysid = 1737078 > > > Please note the "lasteventsource is null" instead of "lasteventsource > = > > null" which is being generated when the value of x_eventsource is > null. > > Do you have transform_null_equals set? > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Benjamin Krajmalnik wrote:
>> Please note the "lasteventsource is null" instead of "lasteventsource =
>> null" which is being generated when the value of x_eventsource is null.
> Do you have transform_null_equals set?
Even if he did, that wouldn't affect the source form of the query.
Insertion of explicit constant values and casts like that isn't
something plpgsql would do on its own either. My bet is that this has
got nothing to do with plpgsql, and in fact the query was generated
client-side using some rather ill-designed parameter substitution code.
If this can actually be reproduced in bare plpgsql, I would like to see
a complete test case.
regards, tom lane
Benjamin Krajmalnik wrote: > I tried it with both on and off, and it did not make a difference. Please show a complete example. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Below is the full stored procedure.
The error as captured in pg_log is as follows:
2009-07-29 11:19:01 MDT 172.20.1.33ERROR: syntax error at or near "is"
at character 192
2009-07-29 11:19:01 MDT 172.20.1.33STATEMENT: update tblksalerts set
cleartime = '2009-07-29 10:49:50'::TIMESTAMP, laststatusid = 7::INTEGER,
lastreplytext = '0'::VARCHAR, lasttesttime = '2009-07-29
10:49:50'::TIMESTAMP, lasteventsource is NULL::VARCHAR, lasteventid is
NULL::INTEGER, replyval = 0::REAL, trend = ''::varchar ( 1 ),
alertoccurrence = 0::integer where ksalertssysid = 1737060
The piece of code executing is:
update tblksalerts
set cleartime = x_cleartime,
laststatusid = x_statusid,
lastreplytext = x_replytxt,
lasttesttime = x_testtime,
lasteventsource = x_eventsource,
lasteventid = myeventid,
replyval = x_replyval,
trend = mytrend,
alertoccurrence = testcount
where ksalertssysid = alertsrecord.ksalertssysid;
and as you can see, every column is being cast.
------------------------------------------------------------------------
----------
-- Function: fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer)
-- DROP FUNCTION fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer);
CREATE OR REPLACE FUNCTION fn_dtaalerts(x_testhash character varying,
x_testtime timestamp without time zone, x_replytxt character varying,
x_statusid integer, x_eventsource character varying, x_eventid integer,
x_replyval real, x_eventlog character varying, x_counter integer,
x_cleartime timestamp without time zone, x_lasttesttime timestamp
without time zone, x_laststatusid integer, x_lastreplytxt character
varying, x_lasteventsource character varying, x_lasteventid integer)
RETURNS void AS
$BODY$
/* function to insert or update alerts from dta*/
declare
alertsrecord record;
r_testrecord record;
myresellerid integer;
mytrend varchar(1);
testcount integer;
oldstatusid integer;
mydisplayname varchar(50);
myeventid integer;
myeventsource varchar(100);
begin
select into r_testrecord
laststatus,
accountno,
priority,
ksdevicessysid,
kstestssysid
from tblkstests
where testhash=x_testhash;
select into mydisplayname displayname from tblksdevices where
ksdevicessysid=r_testrecord.ksdevicessysid;
if x_eventsource is null then
myeventsource = '';
end if;
if x_eventid is null then
myeventid = 0;
end if;
if not exists (select 1 from tblkseventexclusion where
eventid=myeventid and eventsource=myeventsource
and accountno = r_testrecord.accountno and
displayname=mydisplayname) then
-- Parse out test name for event log
-- insert alerts
select into myresellerid resellerid from tblksaccounts where
accountno = r_testrecord.accountno limit 1;
if not exists (select 1 from tblksalerts where kstestssysid =
r_testrecord.kstestssysid
and cleartime is null order by testtime desc limit 1) then
insert into tblksalerts ( testtime, statusid, replytxt,
priority, accountno,
eventsource, eventid, replyval, kstestssysid, eventlog,
resellerid)
values (x_lasttesttime, x_laststatusid, x_lastreplytxt,
r_testrecord.priority,
r_testrecord.accountno, x_lasteventsource,
x_lasteventid, x_replyval,
r_testrecord.kstestssysid, x_eventlog, myresellerid);
end if;
select into alertsrecord ksalertssysid,
testtime,
statusid,
cleartime,
eventsource,
lasteventsource,
eventid,
replyval,
alertoccurrence
from tblksalerts
where kstestssysid = r_testrecord.kstestssysid
order by testtime desc
limit 1;
mytrend := null;
if x_replyval > alertsrecord.replyval then
mytrend := 'U';
elsif x_replyval < alertsrecord.replyval then
mytrend := 'D';
end if;
-- calculate alert occurrence for Event Logs
if x_eventlog is not null then
if alertsrecord.alertoccurrence is null then
testcount = x_counter;
else
testcount = alertsrecord.alertoccurrence+x_counter;
end if;
else
testcount = 0;
end if;
oldstatusid = alertsrecord.statusid;
if oldstatusid = 99 and x_statusid=8 then
oldstatusid = 8;
end if;
if alertsrecord.cleartime is null then
if (x_cleartime is not null) and x_eventlog is null then
update tblksalerts
set cleartime = x_cleartime,
laststatusid = x_statusid,
lastreplytext = x_replytxt,
lasttesttime = x_testtime,
lasteventsource = x_eventsource,
lasteventid = myeventid,
replyval = x_replyval,
trend = mytrend,
alertoccurrence = testcount
where ksalertssysid = alertsrecord.ksalertssysid;
else
update tblksalerts
set laststatusid = x_statusid,
lastreplytext = x_replytxt,
lasttesttime = x_testtime,
lasteventsource = x_eventsource,
lasteventid = myeventid,
replyval = x_replyval,
trend = mytrend,
alertoccurrence = testcount,
statusid = oldstatusid
where ksalertssysid = alertsrecord.ksalertssysid;
end if;
end if;
end if;
end;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION fn_dtaalerts(character varying, timestamp without time
zone, character varying, integer, character varying, integer, real,
character varying, integer, timestamp without time zone, timestamp
without time zone, integer, character varying, character varying,
integer) OWNER TO postgres;
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Wednesday, July 29, 2009 3:31 PM
> To: Alvaro Herrera
> Cc: Benjamin Krajmalnik; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Error in creating the backend query
>
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Benjamin Krajmalnik wrote:
> >> Please note the "lasteventsource is null" instead of
> "lasteventsource =
> >> null" which is being generated when the value of x_eventsource is
> null.
>
> > Do you have transform_null_equals set?
>
> Even if he did, that wouldn't affect the source form of the query.
> Insertion of explicit constant values and casts like that isn't
> something plpgsql would do on its own either. My bet is that this has
> got nothing to do with plpgsql, and in fact the query was generated
> client-side using some rather ill-designed parameter substitution
code.
>
> If this can actually be reproduced in bare plpgsql, I would like to
see
> a complete test case.
>
> regards, tom lane
"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> Below is the full stored procedure.
All I can do is repeat that plpgsql does not behave that way. It never
has AFAIR, and it most definitely doesn't in any version new enough to
recognize the COST option to CREATE FUNCTION (ie, 8.3 and up). In fact,
I don't believe that commands executed in a plpgsql function will get
logged at all in 8.3 or later --- they are not according to my tests.
Perhaps you are running some largely hand-hacked local version of plpgsql?
Perhaps you're just confused about what's generating the log entry?
regards, tom lane
I wish that were the case. I am running 8.3.7 built from the FreeBSD ports. All insertions and updates to that table (or any others) are done exclusively through that (or other) stored procedures. We only use ad-hoc queries for selecting data for presentation purposes. Our code does not cast any column to its type. Just to make sure that this was indeed the source, we went ahead and typecast one of the assigned values, and the generated code had a "double cast", such as Column = 'value'::VARCHAR::varchar So the plpgsql stored procedure is definitely the source. We have worked around this by setting the variable to a blank string if the value passed to the stored procedure is a null value, but there definitely appears to be an issue in there. > -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: Wednesday, July 29, 2009 4:43 PM > To: Benjamin Krajmalnik > Cc: Alvaro Herrera; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Error in creating the backend query > > "Benjamin Krajmalnik" <kraj@illumen.com> writes: > > Below is the full stored procedure. > > All I can do is repeat that plpgsql does not behave that way. It never > has AFAIR, and it most definitely doesn't in any version new enough to > recognize the COST option to CREATE FUNCTION (ie, 8.3 and up). In > fact, > I don't believe that commands executed in a plpgsql function will get > logged at all in 8.3 or later --- they are not according to my tests. > > Perhaps you are running some largely hand-hacked local version of > plpgsql? > Perhaps you're just confused about what's generating the log entry? > > regards, tom lane
"Benjamin Krajmalnik" <kraj@illumen.com> writes:
> So the plpgsql stored procedure is definitely the source.
If you want to convince me of that you need to provide a *self
contained* demonstration. An out-of-context procedure definition
isn't helpful because I can't test it.
regards, tom lane
I believe you may be right - need to do some more testing. Apparently the error which I saw in the log was generated while debugging the stored procedure using SQL Studio for PostgreSQL. I guess it has its own interpreter for plpgsql and must be typecasting while executing each row. We can consider this closed unless I see it occur again. Sorry for the false alarm. > -----Original Message----- > From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin- > owner@postgresql.org] On Behalf Of Benjamin Krajmalnik > Sent: Wednesday, July 29, 2009 5:00 PM > To: Tom Lane > Cc: Alvaro Herrera; pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Error in creating the backend query > > I wish that were the case. > I am running 8.3.7 built from the FreeBSD ports. > All insertions and updates to that table (or any others) are done > exclusively through that (or other) stored procedures. > We only use ad-hoc queries for selecting data for presentation > purposes. > Our code does not cast any column to its type. > Just to make sure that this was indeed the source, we went ahead and > typecast one of the assigned values, and the generated code had a > "double cast", such as > > Column = 'value'::VARCHAR::varchar > > So the plpgsql stored procedure is definitely the source. > We have worked around this by setting the variable to a blank string if > the value passed to the stored procedure is a null value, but there > definitely appears to be an issue in there. > > > > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: Wednesday, July 29, 2009 4:43 PM > > To: Benjamin Krajmalnik > > Cc: Alvaro Herrera; pgsql-admin@postgresql.org > > Subject: Re: [ADMIN] Error in creating the backend query > > > > "Benjamin Krajmalnik" <kraj@illumen.com> writes: > > > Below is the full stored procedure. > > > > All I can do is repeat that plpgsql does not behave that way. It > never > > has AFAIR, and it most definitely doesn't in any version new enough > to > > recognize the COST option to CREATE FUNCTION (ie, 8.3 and up). In > > fact, > > I don't believe that commands executed in a plpgsql function will get > > logged at all in 8.3 or later --- they are not according to my tests. > > > > Perhaps you are running some largely hand-hacked local version of > > plpgsql? > > Perhaps you're just confused about what's generating the log entry? > > > > regards, tom lane > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin