Обсуждение: 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.

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