Re: [ADMIN] Error in creating the backend query
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-ow...@postgresql.org [mailto:pgsql-admin- ow...@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:t...@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 k...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
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 Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Error in creating the backend query
I tried it with both on and off, and it did not make a difference. -Original Message- From: Alvaro Herrera [mailto:alvhe...@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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Error in creating the backend query
Alvaro Herrera alvhe...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Error in creating the backend query
Benjamin Krajmalnik wrote: I tried it with both on and off, and it did not make a difference. Please show a complete example. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Error in creating the backend query
; 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:t...@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 alvhe...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] Error in creating the backend query
Benjamin Krajmalnik k...@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
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:t...@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 k...@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
Re: [ADMIN] Error in creating the backend query
Benjamin Krajmalnik k...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
