BUG #2171: Differences compiling plpgsql in ecpg and psql
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@ece.cmu.edu wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Attachments:
/pgpatches/ecpgtext/plainDownload+23-23
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@dunslane.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@dunslane.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Here is an updated patch. It fixes things in the places it should be
fixed by handling \n at the time we double-quote, and double single
quotes at the place we are building the $$ string.
I am going to apply this to HEAD. It is a fix, but I am worried it
might affect ecpg users who might have worked-around these problems and
this might break their work-around. I am looking for opinions on
applying this to back releases.
---------------------------------------------------------------------------
Andrew Klosterman wrote:
On Wed, 1 Feb 2006, 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.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 lineIt 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@ece.cmu.edu wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073--Andrew J. Klosterman
andrew5@ece.cmu.edu
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Attachments:
/pgpatches/ecpgtext/plainDownload+54-37
Import Notes
Reply to msg id not found: Pine.LNX.4.53L-ECE.CMU.EDU.0602021041290.21760@blossom.pdl.cmu.edu | Resolved by subject fallback
As to my opinion on affecting any work-arounds: I got around this issue
by not line wrapping my long strings in "EXEC SQL ..." statements that
ecpg processes.
I would edit the plpgsql function that I was writing in an external
editor, make sure it installed by copy&paste into the psql window, and
then doctoring the quotes and removing the line breaks so that ecpg would
accept the function and the compiled C code would install it. This made
for some crufty looking code in my editor, but it worked.
If the patched code takes care of eating line breaks then it shouldn't
make any difference to my all-on-one-line solution. Not yet having tried
the patched code, I can only assume that I could now "beautify" my code by
inserting line breaks and do away with the external editor and copy&paste
procedures. :-)
If anyone has heard of other work-arounds for the situation, I'm happy to
hear about them and offer an opinion!
On Fri, 3 Feb 2006, Bruce Momjian wrote:
<snip>
I am going to apply this to HEAD. It is a fix, but I am worried it
might affect ecpg users who might have worked-around these problems and
this might break their work-around. I am looking for opinions on
applying this to back releases.---------------------------------------------------------------------------
Andrew Klosterman wrote:
On Wed, 1 Feb 2006, 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.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 lineIt 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@ece.cmu.edu wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073--Andrew J. Klosterman
andrew5@ece.cmu.edu-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
--Andrew J. Klosterman
andrew5@ece.cmu.edu
Attachments:
/pgpatches/ecpgtext/plain; CHARSET=US-ASCIIDownload+54-37
Andrew Klosterman wrote:
As to my opinion on affecting any work-arounds: I got around this issue
by not line wrapping my long strings in "EXEC SQL ..." statements that
ecpg processes.I would edit the plpgsql function that I was writing in an external
editor, make sure it installed by copy&paste into the psql window, and
then doctoring the quotes and removing the line breaks so that ecpg would
accept the function and the compiled C code would install it. This made
for some crufty looking code in my editor, but it worked.If the patched code takes care of eating line breaks then it shouldn't
make any difference to my all-on-one-line solution. Not yet having tried
the patched code, I can only assume that I could now "beautify" my code by
inserting line breaks and do away with the external editor and copy&paste
procedures. :-)If anyone has heard of other work-arounds for the situation, I'm happy to
hear about them and offer an opinion!
Yep, that's about right. The new code is not going to auto-wrap your
existing function, but it will preserve line wraps you use in ecpg
SQl code.
---------------------------------------------------------------------------
On Fri, 3 Feb 2006, Bruce Momjian wrote:
<snip>I am going to apply this to HEAD. It is a fix, but I am worried it
might affect ecpg users who might have worked-around these problems and
this might break their work-around. I am looking for opinions on
applying this to back releases.---------------------------------------------------------------------------
Andrew Klosterman wrote:
On Wed, 1 Feb 2006, 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.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 lineIt 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@ece.cmu.edu wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073--Andrew J. Klosterman
andrew5@ece.cmu.edu-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073--Andrew J. Klosterman
andrew5@ece.cmu.edu
Content-Description:
Index: src/interfaces/ecpg/preproc/output.c =================================================================== RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/output.c,v retrieving revision 1.12 diff -c -c -r1.12 output.c *** src/interfaces/ecpg/preproc/output.c 13 Oct 2004 01:25:13 -0000 1.12 --- src/interfaces/ecpg/preproc/output.c 3 Feb 2006 05:33:38 -0000 *************** *** 2,7 **** --- 2,9 ----#include "extern.h"
+ static void ouput_escaped_str(char *cmd); + void output_line_number(void) { *************** *** 10,30 **** }void
! output_simple_statement(char *cmd)
{
! int i,
! j = strlen(cmd);;
!
! /* output this char by char as we have to filter '\"' */
! for (i = 0; i < j; i++)
! {
! if (cmd[i] != '"')
! fputc(cmd[i], yyout);
! else
! fputs("\\\"", yyout);
! }
output_line_number();
! free(cmd);
}/* --- 12,22 ---- }void
! output_simple_statement(char *stmt)
{
! ouput_escaped_str(stmt);
output_line_number();
! free(stmt);
}/*
***************
*** 106,125 ****
void
output_statement(char *stmt, int mode, char *con)
{
- int i,
- j = strlen(stmt);
-
fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL");
!
! /* output this char by char as we have to filter '\"' */
! for (i = 0; i < j; i++)
! {
! if (stmt[i] != '"')
! fputc(stmt[i], yyout);
! else
! fputs("\\\"", yyout);
! }
!
fputs("\", ", yyout);/* dump variables to C file */ --- 98,105 ---- void output_statement(char *stmt, int mode, char *con) { fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL"); ! ouput_escaped_str(stmt); fputs("\", ", yyout);/* dump variables to C file */ *************** *** 135,137 **** --- 115,135 ---- if (connection != NULL) free(connection); } + + + static void + ouput_escaped_str(char *str) + { + int i, len = strlen(str); + + /* output this char by char as we have to filter " and \n */ + for (i = 0; i < len; i++) + { + if (str[i] == '"') + fputs("\\\"", yyout); + else if (str[i] == '\n') + fputs("\\n\\\n", yyout); + else + fputc(str[i], yyout); + } + } Index: src/interfaces/ecpg/preproc/pgc.l =================================================================== RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/pgc.l,v retrieving revision 1.140 diff -c -c -r1.140 pgc.l *** src/interfaces/ecpg/preproc/pgc.l 2 Feb 2006 03:51:41 -0000 1.140 --- src/interfaces/ecpg/preproc/pgc.l 3 Feb 2006 05:33:38 -0000 *************** *** 152,158 **** dolq_cont [A-Za-z\200-\377_0-9] dolqdelim \$({dolq_start}{dolq_cont}*)?\$ dolqfailed \${dolq_start}{dolq_cont}* ! dolqinside [^$]+/* Double quote * Allows embedded spaces and other special characters into identifiers. --- 152,158 ---- dolq_cont [A-Za-z\200-\377_0-9] dolqdelim \$({dolq_start}{dolq_cont}*)?\$ dolqfailed \${dolq_start}{dolq_cont}* ! dolqinside [^$']+/* Double quote * Allows embedded spaces and other special characters into identifiers. *************** *** 476,482 **** <xdolq>{dolqinside} { addlit(yytext, yyleng); } <xdolq>{dolqfailed} { addlit(yytext, yyleng); } <xdolq>. { ! /* This is only needed for $ inside the quoted text */ addlitchar(yytext[0]); } <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); } --- 476,485 ---- <xdolq>{dolqinside} { addlit(yytext, yyleng); } <xdolq>{dolqfailed} { addlit(yytext, yyleng); } <xdolq>. { ! /* $$ is implemented as a single-quoted string, so double it? */ ! if (yytext[0] == '\'') ! addlitchar(yytext[0]); ! /* single quote or dollar sign */ addlitchar(yytext[0]); } <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); }
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Applied to CVS HEAD. No backpatching.
---------------------------------------------------------------------------
Bruce Momjian wrote:
Here is an updated patch. It fixes things in the places it should be
fixed by handling \n at the time we double-quote, and double single
quotes at the place we are building the $$ string.I am going to apply this to HEAD. It is a fix, but I am worried it
might affect ecpg users who might have worked-around these problems and
this might break their work-around. I am looking for opinions on
applying this to back releases.---------------------------------------------------------------------------
Andrew Klosterman wrote:
On Wed, 1 Feb 2006, 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.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 lineIt 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@ece.cmu.edu wrote:
The following bug has been logged online:
Bug reference: 2171
Logged by:
Email address: andrew5@ece.cmu.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@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073--Andrew J. Klosterman
andrew5@ece.cmu.edu-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: src/interfaces/ecpg/preproc/output.c =================================================================== RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/output.c,v retrieving revision 1.12 diff -c -c -r1.12 output.c *** src/interfaces/ecpg/preproc/output.c 13 Oct 2004 01:25:13 -0000 1.12 --- src/interfaces/ecpg/preproc/output.c 3 Feb 2006 05:33:38 -0000 *************** *** 2,7 **** --- 2,9 ----#include "extern.h"
+ static void ouput_escaped_str(char *cmd); + void output_line_number(void) { *************** *** 10,30 **** }void
! output_simple_statement(char *cmd)
{
! int i,
! j = strlen(cmd);;
!
! /* output this char by char as we have to filter '\"' */
! for (i = 0; i < j; i++)
! {
! if (cmd[i] != '"')
! fputc(cmd[i], yyout);
! else
! fputs("\\\"", yyout);
! }
output_line_number();
! free(cmd);
}/* --- 12,22 ---- }void
! output_simple_statement(char *stmt)
{
! ouput_escaped_str(stmt);
output_line_number();
! free(stmt);
}/*
***************
*** 106,125 ****
void
output_statement(char *stmt, int mode, char *con)
{
- int i,
- j = strlen(stmt);
-
fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL");
!
! /* output this char by char as we have to filter '\"' */
! for (i = 0; i < j; i++)
! {
! if (stmt[i] != '"')
! fputc(stmt[i], yyout);
! else
! fputs("\\\"", yyout);
! }
!
fputs("\", ", yyout);/* dump variables to C file */ --- 98,105 ---- void output_statement(char *stmt, int mode, char *con) { fprintf(yyout, "{ ECPGdo(__LINE__, %d, %d, %s, \"", compat, force_indicator, con ? con : "NULL"); ! ouput_escaped_str(stmt); fputs("\", ", yyout);/* dump variables to C file */ *************** *** 135,137 **** --- 115,135 ---- if (connection != NULL) free(connection); } + + + static void + ouput_escaped_str(char *str) + { + int i, len = strlen(str); + + /* output this char by char as we have to filter " and \n */ + for (i = 0; i < len; i++) + { + if (str[i] == '"') + fputs("\\\"", yyout); + else if (str[i] == '\n') + fputs("\\n\\\n", yyout); + else + fputc(str[i], yyout); + } + } Index: src/interfaces/ecpg/preproc/pgc.l =================================================================== RCS file: /cvsroot/pgsql/src/interfaces/ecpg/preproc/pgc.l,v retrieving revision 1.140 diff -c -c -r1.140 pgc.l *** src/interfaces/ecpg/preproc/pgc.l 2 Feb 2006 03:51:41 -0000 1.140 --- src/interfaces/ecpg/preproc/pgc.l 3 Feb 2006 05:33:38 -0000 *************** *** 152,158 **** dolq_cont [A-Za-z\200-\377_0-9] dolqdelim \$({dolq_start}{dolq_cont}*)?\$ dolqfailed \${dolq_start}{dolq_cont}* ! dolqinside [^$]+/* Double quote * Allows embedded spaces and other special characters into identifiers. --- 152,158 ---- dolq_cont [A-Za-z\200-\377_0-9] dolqdelim \$({dolq_start}{dolq_cont}*)?\$ dolqfailed \${dolq_start}{dolq_cont}* ! dolqinside [^$']+/* Double quote * Allows embedded spaces and other special characters into identifiers. *************** *** 476,482 **** <xdolq>{dolqinside} { addlit(yytext, yyleng); } <xdolq>{dolqfailed} { addlit(yytext, yyleng); } <xdolq>. { ! /* This is only needed for $ inside the quoted text */ addlitchar(yytext[0]); } <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); } --- 476,485 ---- <xdolq>{dolqinside} { addlit(yytext, yyleng); } <xdolq>{dolqfailed} { addlit(yytext, yyleng); } <xdolq>. { ! /* $$ is implemented as a single-quoted string, so double it? */ ! if (yytext[0] == '\'') ! addlitchar(yytext[0]); ! /* single quote or dollar sign */ addlitchar(yytext[0]); } <xdolq><<EOF>> { yyerror("unterminated dollar-quoted string"); }
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.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@jabber.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Actually 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@jabber.org
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!