Thread: Why does the PL/pgSQL compiler do this?
<div dir="ltr"><span style="font-size:12.8px">Here is the complete function, but all you need to look at is the exceptionblock. (I didn't write this code) :-) I will ask the question after the code.</span><div style="font-size:12.8px"><br/></div><div style="font-size:12.8px"><p class="MsoNormal"><font face="monospace, monospace"size="1">CREATE OR REPLACE FUNCTION etl_app.detl_tx_pull_client_<wbr />stat(</font><p class="MsoNormal"><font face="monospace,monospace" size="1"> p_start_date character varying,</font><p class="MsoNormal"><font face="monospace,monospace" size="1"> p_end_date character varying)</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> RETURNS boolean AS</font><p class="MsoNormal"><font face="monospace, monospace" size="1">$BODY$</font><pclass="MsoNormal"><font face="monospace, monospace" size="1">DECLARE</font><p class="MsoNormal"><fontface="monospace, monospace" size="1"> COUNT INTEGER;</font><p class="MsoNormal"><font face="monospace,monospace" size="1"> SOURCE RECORD;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> v_check_count INTEGER;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">BEGIN</font><pclass="MsoNormal"><font face="monospace, monospace" size="1"> COUNT := 0;</font><p class="MsoNormal"><fontface="monospace, monospace" size="1"> </font><p class="MsoNormal"><font face="monospace, monospace"size="1"> SELECT count(*) into v_check_count</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> FROM fs_QSN_APP.tx_pull_client_stat</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> WHERE updateddate >= TO_DATE(p_start_date,'DD-MON-<wbr />YY HH24:MI:SS') AND updateddate <=TO_DATE(p_end_date,'DD-MON-YY HH24:MI:SS');</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> </font><pclass="MsoNormal"><font face="monospace, monospace" size="1"> IF v_check_count > 0 then</font><pclass="MsoNormal"><font face="monospace, monospace" size="1"> RAISE INFO 'Rows detected=%', v_check_count;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> DELETE FROM QSN_APP.tx_pull_client_stat;</font><pclass="MsoNormal"><font face="monospace, monospace" size="1"> RAISE INFO'Done Deleting tx_pull_client_stat';</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> INSERT INTO QSN_APP.tx_pull_client_stat (PULL_STAT_KEY,<wbr />COUNTRYCODE2TPOSTALCOORDINATE,<wbr/>POSTALCODE2TPOSTALCOORDINATE,<wbr />SERVICE2TX_SERVICE_CATALOG,<wbr />MATCH_RATE,REVENUE_AMT,LAST_<wbr/>CALCULATED_DATE,KEY2TX_<wbr />CRITERIA_TREE,CREATEDDATE,<wbr />CREATEDBYT2USER,UPDATEDDATE,<wbr/>UPDATEDBY2TUSER)</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> select PULL_STAT_KEY,<wbr />COUNTRYCODE2TPOSTALCOORDINATE,<wbr />POSTALCODE2TPOSTALCOORDINATE,<wbr/>SERVICE2TX_SERVICE_CATALOG,<wbr />MATCH_RATE,REVENUE_AMT,LAST_<wbr />CALCULATED_DATE,KEY2TX_<wbr/>CRITERIA_TREE,CREATEDDATE,<wbr />CREATEDBYT2USER,UPDATEDDATE,<wbr />UPDATEDBY2TUSER</font><pclass="MsoNormal"><font face="monospace, monospace" size="1"> FROM fs_QSN_APP.tx_pull_client_<wbr/>stat;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> RAISEINFO 'Done Inserting tx_pull_client_stat';</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> END IF;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> </font><p class="MsoNormal"><fontface="monospace, monospace" size="1"> RETURN TRUE;</font><p class="MsoNormal"><font face="monospace,monospace" size="1">EXCEPTION WHEN OTHERS THEN</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> ROLLBACK;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> RETURNFALSE;</font><p class="MsoNormal"><font face="monospace, monospace" size="1">END;</font><p class="MsoNormal"><fontface="monospace, monospace" size="1">$BODY$</font><p class="MsoNormal"><font face="monospace, monospace"size="1"> LANGUAGE plpgsql VOLATILE</font><p class="MsoNormal"><font face="monospace, monospace" size="1"> COST100;</font><p class="MsoNormal"><font face="monospace, monospace" size="1"><br /></font><p class="MsoNormal"><font face="georgia,serif" size="1">So, here is the question. Why does the compiler not catch:</font><p class="MsoNormal"><fontface="georgia, serif" size="1">1) ROLLBACK; is not a valid PL/pgSQL command</font><p class="MsoNormal"><fontface="georgia, serif" size="1">2) ROLLBACK; and RETURN FALSE; can never be reached</font><p class="MsoNormal"><fontface="georgia, serif" size="1"><br /></font><p class="MsoNormal"><font face="georgia, serif" size="1">Again,my question is about the compiler, not about wrongness of the error handling code. </font><p class="MsoNormal"><fontface="georgia, serif" size="1">I understand that as far as fixing the error handling is concerned,the correct thing to do would be to remove the EXCEPTION block all together and let any errors be propagated upthe call stack.</font><p class="MsoNormal"><font face="georgia, serif" size="1"><br /></font><p class="MsoNormal"><fontface="georgia, serif" size="1">This code is what happens when you let an Oracle PL/SQL programmertry his hand at PL/pgSQL. ;-)</font></div></div>
Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
R
eading section 41.10.2 at the linked page should answer this part.
2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.
David J.
Cool, thanks David, I'll give it a read.
On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
Reading section 41.10.2 at the linked page should answer this part.2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.David J.
I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce the same error since it is not valid in the LANGUAGE plpgsql. I understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same thing.
On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com> wrote:
Cool, thanks David, I'll give it a read.On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:Here is the complete function, but all you need to look at is the exception block. (I didn't write this code) :-) I will ask the question after the code.[...]RETURN TRUE;
EXCEPTION WHEN OTHERS THEN
RAISE EXCEPTION '% %', SQLERRM, SQLSTATE;
ROLLBACK;
RETURN FALSE;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
So, here is the question. Why does the compiler not catch:
1) ROLLBACK; is not a valid PL/pgSQL command
Reading section 41.10.2 at the linked page should answer this part.2) ROLLBACK; and RETURN FALSE; can never be reached
Similar to the above - though "static analysis" is yet a step beyond even what the syntax checking skipping covered above would reveal.David J.
On 10/31/2016 04:32 PM, Michael Moore wrote: > I'm still a bit confused. If I replace the ROLLBACK; command with > ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce > the same error since it is not valid in the LANGUAGE plpgsql. I > understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same > thing. I am guessing this: https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html " A disadvantage is that errors in a specific expression or command cannot be detected until that part of the function is reached in execution. (Trivial syntax errors will be detected during the initial parsing pass, but anything deeper will not be detected until execution.)" ROLLBACK might actually be valid at some point, ELEPHANT will not so it caught in the trivial error stage. > > On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>> wrote: > > Cool, thanks David, I'll give it a read. > > > On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston > <david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com>> wrote: > > On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore > <michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com>>wrote: > > Here is the complete function, but all you need to look at > is the exception block. (I didn't write this code) :-) I > will ask the question after the code. > [...] > > RETURN TRUE; > > EXCEPTION WHEN OTHERS THEN > > RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; > > ROLLBACK; > > RETURN FALSE; > > END; > > $BODY$ > > LANGUAGE plpgsql VOLATILE > > COST 100; > > > So, here is the question. Why does the compiler not catch: > > 1) ROLLBACK; is not a valid PL/pgSQL command > > > R > eading section 41.10.2 at the linked page should answer this part. > > https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html> > > > 2) ROLLBACK; and RETURN FALSE; can never be reached > > > > Similar to the above - though "static analysis" is yet a step > beyond even what the syntax checking skipping covered above > would reveal. > > David J. > > > -- Adrian Klaver adrian.klaver@aklaver.com
<div dir="ltr">Thanks Adrian, but is ROLLBACK <b><u>ever</u></b> possible in PL/pgSQL? My understanding is, "No".</div><divclass="gmail_extra"><br /><div class="gmail_quote">On Mon, Oct 31, 2016 at 4:38 PM, Adrian Klaver <span dir="ltr"><<ahref="mailto:adrian.klaver@aklaver.com" target="_blank">adrian.klaver@aklaver.com</a>></span> wrote:<br/><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span class="">On10/31/2016 04:32 PM, Michael Moore wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"> I'm still a bit confused. If I replace the ROLLBACK; command with<br />ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; produce<br /> the same error since it is not valid in theLANGUAGE plpgsql. I<br /> understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not the same<br /> thing.<br /></blockquote><br/></span> I am guessing this:<br /><br /><a href="https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html"rel="noreferrer" target="_blank">https://www.postgresql.org/doc<wbr/>s/9.5/static/plpgsql-implement<wbr />ation.html</a><br /> " A disadvantageis that errors in a specific expression or command cannot be detected until that part of the function is reachedin execution. (Trivial syntax errors will be detected during the initial parsing pass, but anything deeper will notbe detected until execution.)"<br /><br /> ROLLBACK might actually be valid at some point, ELEPHANT will not so it caughtin the trivial error stage.<br /><br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #cccsolid;padding-left:1ex"><span class=""><br /> On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore <<a href="mailto:michaeljmoore@gmail.com"target="_blank">michaeljmoore@gmail.com</a><br /></span><span class=""> <mailto:<ahref="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.co<wbr />m</a>>> wrote:<br /><br/> Cool, thanks David, I'll give it a read.<br /><br /><br /> On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston<br/></span><span class=""> <<a href="mailto:david.g.johnston@gmail.com" target="_blank">david.g.johnston@gmail.com</a><mailto:<a href="mailto:david.g.johnston@gmail.com" target="_blank">david.g.johnston@gmail<wbr/>.com</a>>> wrote:<br /><br /> On Mon, Oct 31, 2016 at 3:13 PM,Michael Moore<br /></span> <<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.com</a><mailto:<a href="mailto:michaeljmoore@gmail.com" target="_blank">michaeljmoore@gmail.co<wbr/>m</a>>>wrote:<span class=""><br /><br /> Here is the completefunction, but all you need to look at<br /> is the exception block. (I didn't write this code) :-) I<br/> will ask the question after the code.<br /> [...]<br /><br /> RETURNTRUE;<br /><br /> EXCEPTION WHEN OTHERS THEN<br /><br /> RAISE EXCEPTION '% %', SQLERRM,SQLSTATE;<br /><br /> ROLLBACK;<br /><br /> RETURN FALSE;<br /><br /> END;<br /><br /> $BODY$<br /><br /> LANGUAGE plpgsql VOLATILE<br /><br /> COST 100;<br /><br /><br /> So, here is the question. Why does the compiler not catch:<br /><br /> 1) ROLLBACK; is not a valid PL/pgSQL command<br /><br /><br /> R<br /> eading section 41.10.2 at thelinked page should answer this part.<br /><br /> <a href="https://www.postgresql.org/docs/current/static/plpgsql-implementation.html"rel="noreferrer" target="_blank">https://www.postgresql.org/doc<wbr/>s/current/static/plpgsql-imple<wbr />mentation.html</a><br /> <<a href="https://www.postgresql.org/docs/current/static/plpgsql-implementation.html" rel="noreferrer" target="_blank">https://www.postgresql.org/do<wbr/>cs/current/static/plpgsql-impl<wbr />ementation.html</a>><br /><br/><br /> 2) ROLLBACK; and RETURN FALSE; can never be reached<br /><br /><br /><br /> Similar tothe above - though "static analysis" is yet a step<br /> beyond even what the syntax checking skipping coveredabove<br /> would reveal.<br /><br /> David J.<br /><br /><br /><br /></span></blockquote><spanclass="HOEnZb"><font color="#888888"><br /><br /> -- <br /> Adrian Klaver<br /><a href="mailto:adrian.klaver@aklaver.com"target="_blank">adrian.klaver@aklaver.com</a><br /></font></span></blockquote></div><br/></div>
On 10/31/2016 06:09 PM, Michael Moore wrote: > Thanks Adrian, but is ROLLBACK *_ever_* possible in PL/pgSQL? My > understanding is, "No". Well not directly. This is where the memory faded. As I understand it pl/pgsql uses savepoints under the hood for: https://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING When trying to figure this out in the past I found: RollbackAndReleaseCurrentSubTransaction(); in pl_exec.c So you are correct. > > On Mon, Oct 31, 2016 at 4:38 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote: > > On 10/31/2016 04:32 PM, Michael Moore wrote: > > I'm still a bit confused. If I replace the ROLLBACK; command with > ELEPHANT; the result is a syntax error. Why doesn't ROLLBACK; > produce > the same error since it is not valid in the LANGUAGE plpgsql. I > understand that "ROLLBACK TO SAVEPOINT" IS valid. But it's not > the same > thing. > > > I am guessing this: > > https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/9.5/static/plpgsql-implementation.html> > " A disadvantage is that errors in a specific expression or command > cannot be detected until that part of the function is reached in > execution. (Trivial syntax errors will be detected during the > initial parsing pass, but anything deeper will not be detected until > execution.)" > > ROLLBACK might actually be valid at some point, ELEPHANT will not so > it caught in the trivial error stage. > > > On Mon, Oct 31, 2016 at 3:55 PM, Michael Moore > <michaeljmoore@gmail.com <mailto:michaeljmoore@gmail.com> > <mailto:michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>>> wrote: > > Cool, thanks David, I'll give it a read. > > > On Mon, Oct 31, 2016 at 3:24 PM, David G. Johnston > <david.g.johnston@gmail.com > <mailto:david.g.johnston@gmail.com> > <mailto:david.g.johnston@gmail.com > <mailto:david.g.johnston@gmail.com>>> wrote: > > On Mon, Oct 31, 2016 at 3:13 PM, Michael Moore > <michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com> <mailto:michaeljmoore@gmail.com > <mailto:michaeljmoore@gmail.com>>>wrote: > > Here is the complete function, but all you need to > look at > is the exception block. (I didn't write this code) > :-) I > will ask the question after the code. > [...] > > RETURN TRUE; > > EXCEPTION WHEN OTHERS THEN > > RAISE EXCEPTION '% %', SQLERRM, SQLSTATE; > > ROLLBACK; > > RETURN FALSE; > > END; > > $BODY$ > > LANGUAGE plpgsql VOLATILE > > COST 100; > > > So, here is the question. Why does the compiler not > catch: > > 1) ROLLBACK; is not a valid PL/pgSQL command > > > R > eading section 41.10.2 at the linked page should > answer this part. > > > https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html> > > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html > <https://www.postgresql.org/docs/current/static/plpgsql-implementation.html>> > > > 2) ROLLBACK; and RETURN FALSE; can never be reached > > > > Similar to the above - though "static analysis" is yet a > step > beyond even what the syntax checking skipping covered above > would reveal. > > David J. > > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > -- Adrian Klaver adrian.klaver@aklaver.com
Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
Hi Tom,
understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.
Thanks everybody for the enlightening conversation!
Regards,
Mike
On Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
2016-11-01 18:36 GMT+01:00 Michael Moore <michaeljmoore@gmail.com>:
Hi Tom,understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.
This issue can be checked simply by plpgsql_check in next version.
Regards
Pavel
Thanks everybody for the enlightening conversation!Regards,MikeOn Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane
2016-11-01 18:43 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:
2016-11-01 18:36 GMT+01:00 Michael Moore <michaeljmoore@gmail.com>:Hi Tom,understood. If anybody needs a tiny bit of motivation to write that extra bit of code which would complain at compile time: I sense that there are a lot of shops like mine who would love to get off of Oracle due to the cost. That means you'll have guys like myself who are well versed on Oracle's PL/SQL trying to write PL/pgSQL functions. If ROLLBACK; were to cause a syntax error, it would immediately tell guys like myself that we are missing a key concept of how PL/pgSQL works. Saving the error until run time makes look for other reasons that ROLLBACK; might not be working. I know it's a trivial point, but just putting it out there.This issue can be checked simply by plpgsql_check in next version.
tested by https://github.com/okbob/plpgsql_check/commit/1d3d2f5c853077b9b11a2aad9ad8986c06bee0af commit.
Regards
Pavel
RegardsPavelThanks everybody for the enlightening conversation!Regards,MikeOn Mon, Oct 31, 2016 at 7:38 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Michael Moore <michaeljmoore@gmail.com> writes:
> I'm still a bit confused. If I replace the ROLLBACK; command with ELEPHANT;
> the result is a syntax error. Why doesn't ROLLBACK; produce the same error
> since it is not valid in the LANGUAGE plpgsql.
That's a runtime error so far as plpgsql is concerned, because it relies
on the SPI layer to throw the error. It might be practical to complain
about it at compile time, but it would be some extra code that nobody's
written.
regards, tom lane