Re: [BUGS] BUG #2171: Differences compiling plpgsql in

The following bug has been logged online:

Bug reference: 2171
Logged by:
Email address: andrew5(at)ece(dot)cmu(dot)edu
PostgreSQL version: 8.1.2
Operating system: Linux (Debian)
Description: Differences compiling plpgsql in ecpg and psql
Details:

There appear to be parsing problems with ecpg. The following example
program shows code snippets that allow for the successful creation of a
function (CREATE FUNCTION) only using two different syntaxes: one when
entered through psql, and another when compiling with ecpg.

The expectation (and hints from the documentation) indicate that the exact
same method of defining a function should succeed in both cases, but such is
not the case.

Different quoting and line-wrap behavior is observed between psql and ecpg.

(Thanks for the attention, I hope this is useful!)

BEGIN CODE---------------
/* This file is bug.pgc. */
/* Compile as shown:
ecpg bug.pgc -o bug.c
gcc -c -g -std=c99 -I/usr/local/pgsql/include -L/usr/local/pgsql/lib
bug.c -o bug.o
gcc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lecpg bug.o -o bug
*/
/* Run as: ./bug */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

int main(int argc, char* argv[]) {

EXEC SQL CONNECT TO DEFAULT;

EXEC SQL SET AUTOCOMMIT TO ON;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;

EXEC SQL CREATE TABLE My_Table ( Item1 int, Item2 text );

/* Documentation appears to indicate that only single quotes (') are
needed, but this will not ecpg-compile without double-single ('')
quotes. When entered through psql, only the single quotes (')
are needed. */
/* doc/html/sql-syntax.html#SQL-SYNTAX-DOLLAR-QUOTING: "It is
particularly useful when representing string constants inside
other constants, as is often needed in procedural function
definitions." */
/* doc/html/sql-createfunction.html: "Without dollar quoting, any
single quotes or backslashes in the function definition must be
escaped by doubling them." */

/* Documentation appears to indicate that the body of the funtion
can be extended across multiple lines in the input file (this
file) but it will not compile (ecpg) without keeping the function
body on one line. Multiple line input works through psql, but
not here.*/
//bad ecpg,good psql: EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS
trigger
//bad ecpg,good psql: AS $My_Table_Check$
//bad ecpg,good psql: BEGIN RAISE NOTICE 'TG_NAME=%, TG WHEN=%', TG_NAME,
TG_WHEN;
//bad ecpg,good psql: RETURN NEW;
//bad ecpg,good psql: END;
//bad ecpg,good psql: $My_Table_Check$
//bad ecpg,good psql: LANGUAGE 'plpgsql';
EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS trigger
AS $My_Table_Check$ BEGIN RAISE NOTICE ''TG_NAME=%, TG WHEN=%'',
TG_NAME, TG_WHEN; RETURN NEW; END; $My_Table_Check$
LANGUAGE 'plpgsql';

EXEC SQL CREATE TRIGGER My_Table_Check_Trigger
BEFORE INSERT
ON My_Table
FOR EACH ROW
EXECUTE PROCEDURE My_Table_Check();

EXEC SQL INSERT INTO My_Table VALUES (1234, 'Some random text');
EXEC SQL INSERT INTO My_Table VALUES (5678, 'The Quick Brown');

EXEC SQL DROP TRIGGER My_Table_Check_Trigger ON My_Table;
EXEC SQL DROP FUNCTION My_Table_Check();
EXEC SQL DROP TABLE My_Table;

EXEC SQL DISCONNECT ALL;

return 0;
}

END CODE------------------

I have researched your report, and you are right, there are two ecpg
bugs here. First, dollar quoting uses single-quotes internally to do
the quoting, but it does not double any single-quotes in the
dollar-quoted string.

Second, when a dollar quoted string or single-quoted string spans
multiple lines, ecpg does not escape the newline that is part of the
string. Some compilers will accept an unescaped newline in a string,
while others will not:

$ gcc -pedantic -c -g -Wall tst1.c
tst1.c:5: warning: string constant runs past end of line

It isn't standard so I think we need to replace newline in a string with
"\n\".

Attached is a patch which fixes both of these issues. This changes ecpg
behavior so I am thinking this patch would only appear in 8.2.

I am unclear if I fixed the \r case properly.

---------------------------------------------------------------------------

andrew5(at)ece(dot)cmu(dot)edu wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2171
> Logged by:
> Email address: andrew5(at)ece(dot)cmu(dot)edu
> PostgreSQL version: 8.1.2
> Operating system: Linux (Debian)
> Description: Differences compiling plpgsql in ecpg and psql
> Details:
>
> There appear to be parsing problems with ecpg. The following example
> program shows code snippets that allow for the successful creation of a
> function (CREATE FUNCTION) only using two different syntaxes: one when
> entered through psql, and another when compiling with ecpg.
>
> The expectation (and hints from the documentation) indicate that the exact
> same method of defining a function should succeed in both cases, but such is
> not the case.
>
> Different quoting and line-wrap behavior is observed between psql and ecpg.
>
> (Thanks for the attention, I hope this is useful!)
>
> BEGIN CODE---------------
> /* This file is bug.pgc. */
> /* Compile as shown:
> ecpg bug.pgc -o bug.c
> gcc -c -g -std=c99 -I/usr/local/pgsql/include -L/usr/local/pgsql/lib
> bug.c -o bug.o
> gcc -I/usr/local/pgsql/include -L/usr/local/pgsql/lib -lecpg bug.o -o bug
> */
> /* Run as: ./bug */
> #include <stdio.h>
> #include <stdlib.h>
> #include <string.h>
>
> int main(int argc, char* argv[]) {
>
> EXEC SQL CONNECT TO DEFAULT;
>
> EXEC SQL SET AUTOCOMMIT TO ON;
> EXEC SQL WHENEVER SQLWARNING SQLPRINT;
> EXEC SQL WHENEVER SQLERROR SQLPRINT;
>
> EXEC SQL CREATE TABLE My_Table ( Item1 int, Item2 text );
>
> /* Documentation appears to indicate that only single quotes (') are
> needed, but this will not ecpg-compile without double-single ('')
> quotes. When entered through psql, only the single quotes (')
> are needed. */
> /* doc/html/sql-syntax.html#SQL-SYNTAX-DOLLAR-QUOTING: "It is
> particularly useful when representing string constants inside
> other constants, as is often needed in procedural function
> definitions." */
> /* doc/html/sql-createfunction.html: "Without dollar quoting, any
> single quotes or backslashes in the function definition must be
> escaped by doubling them." */
>
> /* Documentation appears to indicate that the body of the funtion
> can be extended across multiple lines in the input file (this
> file) but it will not compile (ecpg) without keeping the function
> body on one line. Multiple line input works through psql, but
> not here.*/
> //bad ecpg,good psql: EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS
> trigger
> //bad ecpg,good psql: AS $My_Table_Check$
> //bad ecpg,good psql: BEGIN RAISE NOTICE 'TG_NAME=%, TG WHEN=%', TG_NAME,
> TG_WHEN;
> //bad ecpg,good psql: RETURN NEW;
> //bad ecpg,good psql: END;
> //bad ecpg,good psql: $My_Table_Check$
> //bad ecpg,good psql: LANGUAGE 'plpgsql';
> EXEC SQL CREATE FUNCTION My_Table_Check() RETURNS trigger
> AS $My_Table_Check$ BEGIN RAISE NOTICE ''TG_NAME=%, TG WHEN=%'',
> TG_NAME, TG_WHEN; RETURN NEW; END; $My_Table_Check$
> LANGUAGE 'plpgsql';
>
> EXEC SQL CREATE TRIGGER My_Table_Check_Trigger
> BEFORE INSERT
> ON My_Table
> FOR EACH ROW
> EXECUTE PROCEDURE My_Table_Check();
>
> EXEC SQL INSERT INTO My_Table VALUES (1234, 'Some random text');
> EXEC SQL INSERT INTO My_Table VALUES (5678, 'The Quick Brown');
>
> EXEC SQL DROP TRIGGER My_Table_Check_Trigger ON My_Table;
> EXEC SQL DROP FUNCTION My_Table_Check();
> EXEC SQL DROP TABLE My_Table;
>
> EXEC SQL DISCONNECT ALL;
>
> return 0;
> }
>
> END CODE------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, 2006-02-01 at 23:48 -0500, Bruce Momjian wrote:
> I have researched your report, and you are right, there are two ecpg
> bugs here. First, dollar quoting uses single-quotes internally to do
> the quoting, but it does not double any single-quotes in the
> dollar-quoted string.
>

As a matter of curiosity, why does ecpg handle dollar quoting like that?
psql, for example, happily just passes a dollar quoted string through to
the backend, without any need to convert it to a conventionally quoted
string.

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> As a matter of curiosity, why does ecpg handle dollar quoting like that?
> psql, for example, happily just passes a dollar quoted string through to
> the backend, without any need to convert it to a conventionally quoted
> string.

Doesn't ecpg have to convert string literals into C string constants?

regards, tom lane

On Thu, 2006-02-02 at 16:41 -0500, Tom Lane wrote:
> Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> > As a matter of curiosity, why does ecpg handle dollar quoting like that?
> > psql, for example, happily just passes a dollar quoted string through to
> > the backend, without any need to convert it to a conventionally quoted
> > string.
>
> Doesn't ecpg have to convert string literals into C string constants?
>

I believe so. But doesn't it do that by surrounding them with double
quotes, and escaping embedded double quotes (and now newlines) ? The
backend still needs to see what it will accept as a quote delimiter,
surely.

cheers

andrew

Andrew Dunstan wrote:
> On Wed, 2006-02-01 at 23:48 -0500, Bruce Momjian wrote:
> > I have researched your report, and you are right, there are two ecpg
> > bugs here. First, dollar quoting uses single-quotes internally to do
> > the quoting, but it does not double any single-quotes in the
> > dollar-quoted string.
> >
>
> As a matter of curiosity, why does ecpg handle dollar quoting like that?
> psql, for example, happily just passes a dollar quoted string through to
> the backend, without any need to convert it to a conventionally quoted
> string.

ecpg actually parses the statements so it can do things like make
variable substitutions. The $$ and '' strings are actually passed to
preproc.y as SCONST. parser/scan.l does the same thing, though it
doesn't need to single-quote it and pass it up to eventually be output
in C.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Wed, Feb 01, 2006 at 11:48:45PM -0500, Bruce Momjian wrote:
> I have researched your report, and you are right, there are two ecpg
> bugs here. First, dollar quoting uses single-quotes internally to do
> the quoting, but it does not double any single-quotes in the
> dollar-quoted string.

Actually ecpg should not translate dollar quoting at all. I'm going to
fix this. Dollar quotes will then be send to the backend with
translation and the backend takes care of the rest.

> Second, when a dollar quoted string or single-quoted string spans
> multiple lines, ecpg does not escape the newline that is part of the
> string. Some compilers will accept an unescaped newline in a string,
> while others will not:

Not sure, but there appears to be a bug in the routine that outputs a
statement. I will change that one as well and would ask you to just
check again. It seems to me that this also fixes your problem. I tried
with the one test case in this email and it seems to work. But then I'm
currently travelling and do not have that much spare time to test.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

Michael Meskes wrote:
> On Wed, Feb 01, 2006 at 11:48:45PM -0500, Bruce Momjian wrote:
> > I have researched your report, and you are right, there are two ecpg
> > bugs here. First, dollar quoting uses single-quotes internally to do
> > the quoting, but it does not double any single-quotes in the
> > dollar-quoted string.
>
> Actually ecpg should not translate dollar quoting at all. I'm going to
> fix this. Dollar quotes will then be send to the backend with
> translation and the backend takes care of the rest.

Well, CVS version before I modified it had "dolq" stuff in the lexer to
handle dollar quotes and pass it as SCONST to the parser.

> > Second, when a dollar quoted string or single-quoted string spans
> > multiple lines, ecpg does not escape the newline that is part of the
> > string. Some compilers will accept an unescaped newline in a string,
> > while others will not:
>
> Not sure, but there appears to be a bug in the routine that outputs a
> statement. I will change that one as well and would ask you to just
> check again. It seems to me that this also fixes your problem. I tried
> with the one test case in this email and it seems to work. But then I'm
> currently travelling and do not have that much spare time to test.

The problem is that output.c does:

printf("abc
def");

While some compilers are OK with that, others are not. I changed it to
output:

printf("abc\n\
def");

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> > Actually ecpg should not translate dollar quoting at all. I'm going to
> > fix this. Dollar quotes will then be send to the backend with
> > translation and the backend takes care of the rest.
>
> Well, CVS version before I modified it had "dolq" stuff in the lexer to
> handle dollar quotes and pass it as SCONST to the parser.

Yes, I know. This was buggy from the get go on IMO. :-)

> > Not sure, but there appears to be a bug in the routine that outputs a
> > statement. I will change that one as well and would ask you to just
> > check again. It seems to me that this also fixes your problem. I tried
> > with the one test case in this email and it seems to work. But then I'm
> > currently travelling and do not have that much spare time to test.
>
> The problem is that output.c does:
>
> printf("abc
> def");
>
> While some compilers are OK with that, others are not. I changed it to
> output:
>
> printf("abc\n\
> def");

Okay, will look into this again.

Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: meskes(at)jabber(dot)org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!