pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLine length in pl/pgsql function
8+ messages / 3 participants
[nested] [flat]
* Line length in pl/pgsql function
@ 2004-03-17 00:47 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 2 replies; 8+ messages in thread
From: David Olbersen @ 2004-03-17 00:47 UTC (permalink / raw)
To: pgsql-sql
Hello all.
I was minding my business, writing a nice long pl/pgsql function and all was well. I tried creating the function (using \i <file-with-definition>) and started getting funny errors.
I figured out eventually that the problem seems due to line length in a construct of: FOR result IN <SELECT ...> LOOP
Here's the "clean" code that doesn't work:
FOR result IN
SELECT
initcap( c.name ) AS category,
initcap( l.language ) AS language,
count(*) AS howmany
FROM
urlinfo u JOIN ratings_by_serial r USING (id)
JOIN categories c USING (cid)
JOIN languages l USING (lang_id)
WHERE u.ratedon BETWEEN startDate AND endDate
GROUP BY category, language
ORDER BY category, language
LOOP
RETURN NEXT result;
END LOOP;
But, if I scrunch up most of it to one line:
FOR result IN SELECT initcap( c.name ) AS category, initcap( l.language ) AS language, count(*) AS howmany FROM urlinfo u JOIN ratings_by_serial r USING (id) JOIN categories c USING (cid) JOIN languages l USING (lang_id) WHERE u.ratedon BETWEEN startDate AND endDate GROUP BY category, language ORDER BY category, language LOOP
RETURN NEXT result;
END LOOP;
... it loads this part just fine. This was after about an hour of hair-pulling and log reading. Currently running PostgreSQL 7.3.4 on FreeBSD 4.5-STABLE. I've read through the release notes for the more recent versions (7.3.5, 7.3.6, 7.4.1, 7.4.2) but didn't see anything like this mentioned. Perhaps a fix for this is part of another fix?
Any thoughts on who I should start poking about a solution? Is there possibly a configuration setting I can change? Upgrading is an option, but I'd rather not go there just yet if I can avoid it.
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 05:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Olbersen <DOlbersen@stbernard.com>
1 sibling, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-03-17 05:35 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-sql
"David Olbersen" <DOlbersen@stbernard.com> writes:
> I was minding my business, writing a nice long pl/pgsql function and all was well. I tried creating the function (using \i <file-with-definition>) and started getting funny errors.
> I figured out eventually that the problem seems due to line length in a construct of: FOR result IN <SELECT ...> LOOP
Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but
I don't know of any problems since then. Could you submit a *complete*
test case, rather than making us guess the details?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 09:27 Richard Huxton <dev@archonet.com>
parent: David Olbersen <DOlbersen@stbernard.com>
1 sibling, 0 replies; 8+ messages in thread
From: Richard Huxton @ 2004-03-17 09:27 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; pgsql-sql
On Wednesday 17 March 2004 00:47, David Olbersen wrote:
>
> Here's the "clean" code that doesn't work:
>
> FOR result IN
> SELECT
> initcap( c.name ) AS category,
> initcap( l.language ) AS language,
> count(*) AS howmany
[etc]
I don't suppose your actual code has any comments in it? I stumbled across an
obscure parser issue in plpgsql regarding comments/line-endings and I think
that was somewhere in 7.3.x
Might be worth a quick look in the -bugs list archives and see if anything
looks like your problem.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 19:50 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 1 reply; 8+ messages in thread
From: David Olbersen @ 2004-03-17 19:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> Hmm ... plpgsql had some string-length issues as recently as 7.2.2, but
> I don't know of any problems since then. Could you submit a *complete*
> test case, rather than making us guess the details?
Sure. I didn't want to dump a huge email to have somebody say "Addressed in 7.3.5" :)
PostgreSQL version...: 7.3.4
Compiled with........: gcc 2.95.3
Architecture.........: Intel Pentium III
Operating System.....: FreeBSD 4.5-STABLE
Reproduction
--------------
1) Connect to the database using psql
2) Attempt to create a return type and function using:
\i test_ratedby_category_lang.plsql
The errors from psql are attached as psql-errors.txt
The file with the function is attached as test_ratedby_category_lang.plsql
The postgresql.conf and current postmaster.opts are attached as well.
I can send syslog output as well, if desired, at any debug level (I don't know what's appropriate).
I tried reproducing the bug before I composed this message and it didn't break in the same way as yesterday. That's not very helpful -- but it did still break with the same error message (as seen in psql-errors.txt) but my syslog output was different. I can attach both if that's helpful.
Any more info needed?
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
Attachments:
psql-errors.txt (text/plain, 1.9K) download
psql:test_ratedby_category_lang.plsql:1: ERROR: Relation 'ratedby_return_set' already exists
psql:test_ratedby_category_lang.plsql:101: ERROR: parser: parse error at or near "t" at character 3613
psql:test_ratedby_category_lang.plsql:102: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:118: ERROR: parser: parse error at or near "ELSIF" at character 13
psql:test_ratedby_category_lang.plsql:119: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:135: ERROR: parser: parse error at or near "ELSE" at character 13
psql:test_ratedby_category_lang.plsql:136: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:137: ERROR: parser: parse error at or near "IF" at character 17
psql:test_ratedby_category_lang.plsql:156: ERROR: parser: parse error at or near "ELSE" at character 9
psql:test_ratedby_category_lang.plsql:157: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:174: ERROR: parser: parse error at or near "ELSIF" at character 13
psql:test_ratedby_category_lang.plsql:175: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:193: ERROR: parser: parse error at or near "ELSE" at character 13
psql:test_ratedby_category_lang.plsql:194: ERROR: parser: parse error at or near "LOOP" at character 21
psql:test_ratedby_category_lang.plsql:195: ERROR: parser: parse error at or near "IF" at character 17
psql:test_ratedby_category_lang.plsql:196: ERROR: parser: parse error at or near "IF" at character 13
psql:test_ratedby_category_lang.plsql:198: ERROR: parser: parse error at or near "RETURN" at character 9
psql:test_ratedby_category_lang.plsql:199: WARNING: COMMIT: no transaction in progress
COMMIT
psql:test_ratedby_category_lang.plsql:200: ERROR: parser: parse error at or near "' LANGUAGE '" at character 1
* Re: Line length in pl/pgsql function
@ 2004-03-17 19:52 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Olbersen @ 2004-03-17 19:52 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
Richard Huxton wrote:
> On Wednesday 17 March 2004 00:47, David Olbersen wrote:
> >
> > Here's the "clean" code that doesn't work:
> >
> > FOR result IN
> > SELECT
> > initcap( c.name ) AS category,
> > initcap( l.language ) AS language,
> > count(*) AS howmany [etc]
>
> I don't suppose your actual code has any comments in it? I stumbled across an
> obscure parser issue in plpgsql regarding comments/line-endings and I think
> that was somewhere in 7.3.x
That was the actual code. There are comments, but they're one line above that one, and on their own line. Everything is developed using Vim on FreeBSD, so line-endings shouldn't be a problem, should they?
> Might be worth a quick look in the -bugs list archives and see if anything
> looks like your problem.
Didn't see anything on a quick look.
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 20:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2004-03-17 20:03 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; +Cc: pgsql-sql
"David Olbersen" <DOlbersen@stbernard.com> writes:
> The errors from psql are attached as psql-errors.txt
> The file with the function is attached as =
> test_ratedby_category_lang.plsql
> The postgresql.conf and current postmaster.opts are attached as well.
Hate to tell you this, but it's just pilot error. You've got comments
like these embedded in the plpgsql function:
ELSIF cat = ''none'' THEN
-- none,none = don't show the languages, or categories (whaaat?)
FOR result IN
That quote in "don't" has to be doubled. Remember this whole thing is a
giant string literal as far as the outer CREATE FUNCTION syntax is
concerned. The fact that the quote is within a comment in terms of the
plpgsql syntax doesn't mean a thing to the outer string-literal parser.
This'll all get a *lot* easier in 7.5 when you can use dollar-quoting
instead of having to double quote marks in function bodies ...
regards, tom lane
PS: note to hackers: Fabien's new error localization code does a pretty
decent job of fingering the problem. When I loaded this test file into
CVS tip I got
psql:test_ratedby_category_lang.plsql:95: ERROR: syntax error at or near "t" at character 3419
psql:test_ratedby_category_lang.plsql:95: LINE 81: -- none,everything = don't show the language...
psql:test_ratedby_category_lang.plsql:95: ^
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 20:07 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Olbersen @ 2004-03-17 20:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Tom Lane wrote:
> Hate to tell you this, but it's just pilot error. You've got comments
> like these embedded in the plpgsql function:
>
> ELSIF cat = ''none'' THEN
> -- none,none = don't show the languages, or categories
> (whaaat?) FOR result IN
>
> That quote in "don't" has to be doubled. Remember this whole thing is a
> giant string literal as far as the outer CREATE FUNCTION syntax is
> concerned. The fact that the quote is within a comment in terms of the
> plpgsql syntax doesn't mean a thing to the outer string-literal parser.
*sigh* I'd rather have pilot error than having to wait for a patch :)
It's funny, I use Vim with syntax highlighting to catch this sort of thing. 99% of the time it does, I guess this is that other 1%.
> PS: note to hackers: Fabien's new error localization code does a pretty
> decent job of fingering the problem. When I loaded this test file into
> CVS tip I got
>
> psql:test_ratedby_category_lang.plsql:95: ERROR: syntax error at or near "t"
> at character 3419 psql:test_ratedby_category_lang.plsql:95: LINE 81:
> -- none,everything = don't show the language...
> psql:test_ratedby_category_lang.plsql:95:
> ^
That would have been handy!
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Line length in pl/pgsql function
@ 2004-03-17 20:37 David Olbersen <DOlbersen@stbernard.com>
0 siblings, 0 replies; 8+ messages in thread
From: David Olbersen @ 2004-03-17 20:37 UTC (permalink / raw)
To: David Olbersen <DOlbersen@stbernard.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
David Olbersen wrote:
> *sigh* I'd rather have pilot error than having to wait for a patch :)
Hmmm, that doesn't look right in retrospect. What I meant to say was
THANK YOU TOM!
--
David Olbersen
iGuard Engineer
St. Bernard Software
15015 Avenue of Sciences
San Diego, CA 92127
x2152
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-03-17 20:37 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-17 00:47 Line length in pl/pgsql function David Olbersen <DOlbersen@stbernard.com>
2004-03-17 05:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-17 09:27 ` Richard Huxton <dev@archonet.com>
2004-03-17 19:50 Re: Line length in pl/pgsql function David Olbersen <DOlbersen@stbernard.com>
2004-03-17 20:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-17 19:52 Re: Line length in pl/pgsql function David Olbersen <DOlbersen@stbernard.com>
2004-03-17 20:07 Re: Line length in pl/pgsql function David Olbersen <DOlbersen@stbernard.com>
2004-03-17 20:37 Re: Line length in pl/pgsql function David Olbersen <DOlbersen@stbernard.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox