pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedDouble newline bug with DBD::Pg: Where best to fix?
10+ messages / 5 participants
[nested] [flat]
* Double newline bug with DBD::Pg: Where best to fix?
@ 2001-02-27 19:25 Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Dan Lyke @ 2001-02-27 19:25 UTC (permalink / raw)
To: pgsql-interfaces
If, using DBI with DBD::Pg, you call
$dbh->do('INSERT INTO xyz(stuff) VALUES('
$dbh->quote("a string with \n\n a two line break").')');
You'll get a string with a single line break back, because DBD::Pg
doesn't escape newlines into '\n'.
This is consistent with the interface via psql:
test=# insert into xyz(stuff) values('a string with
test'#
test'# a two line break');
test=# select stuff from xyz;
stuff
----------------------------------
a string with
a two line break
(1 row)
(As you can see there are also some interesting issues with leading
spaces going on here).
I don't have a copy of the SQL standard in front of me, so I'm not
sure whether this needs to be fixed in the PostgreSQL parser (seems
like the logical place, but we're into minutae of compatibility) or
DBD::Pg (the easy place, but maybe this is a hack that's going to have
to go in every interface).
I also haven't tested with putting the string inside the
$sth->execute(...) call, and haven't dug through enough of the DBI
code to know how exactly that gets passed to the underlying driver.
Anyway, I've got over 4000 records that need to get fixed by hand
because of this, and want to make sure nobody else gets bitten, and
would like to implement a solution that doesn't require me remembering
to patch things every time I upgrade.
Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
@ 2001-02-27 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:47 ` Re: Double newline bug with DBD::Pg: Where best to fix? ivan <ivan-pgsql-interfaces@420.am>
0 siblings, 2 replies; 10+ messages in thread
From: Tom Lane @ 2001-02-27 20:21 UTC (permalink / raw)
To: Dan Lyke <danlyke@flutterby.com>; +Cc: pgsql-interfaces@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
Dan Lyke <danlyke@flutterby.com> writes:
> If, using DBI with DBD::Pg, you call
> $dbh->do('INSERT INTO xyz(stuff) VALUES('
> $dbh->quote("a string with \n\n a two line break").')');
> You'll get a string with a single line break back, because DBD::Pg
> doesn't escape newlines into '\n'.
There shouldn't be any need to convert newlines into \n in quoted
strings; either way should work.
> This is consistent with the interface via psql:
> test=# insert into xyz(stuff) values('a string with
> test'#
> test'# a two line break');
> test=# select stuff from xyz;
> stuff
> ----------------------------------
> a string with
> a two line break
> (1 row)
> (As you can see there are also some interesting issues with leading
> spaces going on here).
I would call this a clear bug in psql. I cannot replicate the loss of
leading whitespace in current sources, but I do see the loss of the
empty line. Turning on debug logging shows that the empty line has
been stripped by psql, not the backend --- it's not there in the query
that arrives at the backend.
I tried a similar experiment in pgtclsh and didn't see any loss of
blank lines, so the problem seems to be specific to psql. Your
observation in DBI must be an independent bug in either DBD::Pg or
DBI itself.
> I don't have a copy of the SQL standard in front of me, so I'm not
> sure whether this needs to be fixed in the PostgreSQL parser (seems
> like the logical place, but we're into minutae of compatibility) or
> DBD::Pg (the easy place, but maybe this is a hack that's going to have
> to go in every interface).
The Postgres parser is not broken, AFAICT. If you can find where this
is happening in the DBI/DBD path, please submit a patch.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-27 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:39 ` Re: Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-28 17:11 ` Re: Double newline bug with DBD::Pg: Where best to fix? Peter Eisentraut <peter_e@gmx.net>
1 sibling, 2 replies; 10+ messages in thread
From: Tom Lane @ 2001-02-27 20:27 UTC (permalink / raw)
To: Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces@postgresql.org, Peter Eisentraut <peter_e@gmx.net>
>> test=# insert into xyz(stuff) values('a string with
>> test'#
>> test'# a two line break');
>> test=# select stuff from xyz;
>> stuff
>> ----------------------------------
>> a string with
>> a two line break
>> (1 row)
>> (As you can see there are also some interesting issues with leading
>> spaces going on here).
On closer look, there is no leading-space issue. Psql is formatting the
column with a leading space as it usually does, and the newline-space
in the string is just being echoed. Compare:
regression=# select 'a string without a newline';
?column?
----------------------------
a string without a newline
(1 row)
regression=# select 'a string\nwith a newline';
?column?
-------------------------
a string
with a newline
(1 row)
regression=# select 'a string\n with a newline';
?column?
--------------------------
a string
with a newline
(1 row)
regression=# select 'a string
regression'# with a newline';
?column?
-------------------------
a string
with a newline
(1 row)
regression=# select 'a string
regression'# with a newline';
?column?
--------------------------
a string
with a newline
(1 row)
That's all fine IMHO. But this isn't:
regression=# select 'a string
regression'#
regression'# with a newline';
?column?
-------------------------
a string
with a newline
(1 row)
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-27 20:39 ` Dan Lyke <danlyke@flutterby.com>
1 sibling, 0 replies; 10+ messages in thread
From: Dan Lyke @ 2001-02-27 20:39 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Peter Eisentraut <peter_e@gmx.net>
Tom Lane writes:
> On closer look, there is no leading-space issue. Psql is formatting the
> column with a leading space as it usually does, and the newline-space
> in the string is just being echoed. Compare:
Yep, you're completely right.
And in fact a little more testing (and pulling my head out of my butt)
indicates that there's no problem at all with DBD::Pg, it's all with
psql and the fact that I was using $dbh->quote to write a script which
got piped into psql.
Dang it, that's twice I've made a stupid foul-up on my end with
potential PostgreSQL bugs.
Anyway, we've at least established that there's a bug in psql. I can
patch my one script to work around it, if I get into the psql source
I'll try to drop a fix on y'all, although given my recent attention to
detail that might not be a good idea...
Dan
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-28 17:11 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-28 17:32 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 17:42 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 2 replies; 10+ messages in thread
From: Peter Eisentraut @ 2001-02-28 17:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces
Tom Lane writes:
> That's all fine IMHO. But this isn't:
>
> regression=# select 'a string
> regression'#
> regression'# with a newline';
> ?column?
> -------------------------
> a string
> with a newline
> (1 row)
Well, *somebody* once figured that he could optimize away whitespace...
Not so. Here's a patch that gets this case right:
inline patch 1: mainloop.c, mainloop.c 2001/02/28 16:57:31 (+2/-2 lines) download inline-1.patch
Index: mainloop.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/mainloop.c,v
retrieving revision 1.35
diff -u -r1.35 mainloop.c
--- mainloop.c 2001/02/10 02:31:28 1.35
+++ mainloop.c 2001/02/28 16:57:31
@@ -249,7 +249,7 @@
pset.lineno++;
/* nothing left on line? then ignore */
- if (line[0] == '\0')
+ if (line[0] == '\0' && !in_quote)
{
free(line);
continue;
@@ -510,7 +510,7 @@
/* Put the rest of the line in the query buffer. */
- if (line[query_start + strspn(line + query_start, " \t\n\r")] != '\0')
+ if (in_quote || line[query_start + strspn(line + query_start, " \t\n\r")] != '\0')
{
if (query_buf->len > 0)
appendPQExpBufferChar(query_buf, '\n');
===snip
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 17:11 ` Re: Double newline bug with DBD::Pg: Where best to fix? Peter Eisentraut <peter_e@gmx.net>
@ 2001-02-28 17:32 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 18:06 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-02-28 17:32 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces
> Tom Lane writes:
>
> > That's all fine IMHO. But this isn't:
> >
> > regression=# select 'a string
> > regression'#
> > regression'# with a newline';
> > ?column?
> > -------------------------
> > a string
> > with a newline
> > (1 row)
>
> Well, *somebody* once figured that he could optimize away whitespace...
> Not so. Here's a patch that gets this case right:
Looks like it was me:
revision 1.2
date: 1999/11/04 23:14:29; author: momjian; state: Exp; lines: +343 -306
psql cleanup
Peter, this was just after you installed these files. My guess is that
something stopped working, and I got in there and tried to fix it. I
have attached the entire diff I applied at that time.
I believe your fix is correct. Empty lines are significant inside
quotes. What amazes me is that this code has been this way for over a
year and no one found the problem. The bug appears in 7.0.
>
> Index: mainloop.c
> ===================================================================
> RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/mainloop.c,v
> retrieving revision 1.35
> diff -u -r1.35 mainloop.c
> --- mainloop.c 2001/02/10 02:31:28 1.35
> +++ mainloop.c 2001/02/28 16:57:31
> @@ -249,7 +249,7 @@
> pset.lineno++;
>
> /* nothing left on line? then ignore */
> - if (line[0] == '\0')
> + if (line[0] == '\0' && !in_quote)
> {
> free(line);
> continue;
> @@ -510,7 +510,7 @@
>
>
> /* Put the rest of the line in the query buffer. */
> - if (line[query_start + strspn(line + query_start, " \t\n\r")] != '\0')
> + if (in_quote || line[query_start + strspn(line + query_start, " \t\n\r")] != '\0')
> {
> if (query_buf->len > 0)
> appendPQExpBufferChar(query_buf, '\n');
> ===snip
>
> Both of these whitespace-away-optimizing checks could probably be removed
> completely, but I'm not sure whether there isn't some case that relies on
> it. I'm gonna stare at it for a few hours and then check it in.
>
> --
> Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
>
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Attachments:
/bjm/diff (text/plain, 18.8K) download
Index: mainloop.c
===================================================================
RCS file: /home/projects/pgsql/cvsroot/pgsql/src/bin/psql/mainloop.c,v
retrieving revision 1.1
retrieving revision 1.2
diff -c -r1.1 -r1.2
*** mainloop.c 1999/11/04 21:56:02 1.1
--- mainloop.c 1999/11/04 23:14:29 1.2
***************
*** 26,368 ****
int
MainLoop(PsqlSettings *pset, FILE *source)
{
! PQExpBuffer query_buf; /* buffer for query being accumulated */
! char *line; /* current line of input */
! char *xcomment; /* start of extended comment */
! int len; /* length of the line */
! int successResult = EXIT_SUCCESS;
! backslashResult slashCmdStatus;
!
! bool eof = false; /* end of our command input? */
! bool success;
! char in_quote; /* == 0 for no in_quote */
! bool was_bslash; /* backslash */
! int paren_level;
! unsigned int query_start;
!
! int i, prevlen, thislen;
!
! /* Save the prior command source */
! FILE *prev_cmd_source;
! bool prev_cmd_interactive;
!
! bool die_on_error;
! const char *interpol_char;
!
!
! /* Save old settings */
! prev_cmd_source = pset->cur_cmd_source;
! prev_cmd_interactive = pset->cur_cmd_interactive;
!
! /* Establish new source */
! pset->cur_cmd_source = source;
! pset->cur_cmd_interactive = ((source == stdin) && !pset->notty);
!
!
! query_buf = createPQExpBuffer();
! if (!query_buf) {
! perror("createPQExpBuffer");
! exit(EXIT_FAILURE);
! }
!
! xcomment = NULL;
! in_quote = 0;
! paren_level = 0;
! slashCmdStatus = CMD_UNKNOWN; /* set default */
!
!
! /* main loop to get queries and execute them */
! while (!eof)
! {
! if (slashCmdStatus == CMD_NEWEDIT)
{
! /*
! * just returned from editing the line? then just copy to the
! * input buffer
! */
! line = strdup(query_buf->data);
! resetPQExpBuffer(query_buf);
! /* reset parsing state since we are rescanning whole query */
! xcomment = NULL;
! in_quote = 0;
! paren_level = 0;
}
! else
{
! /*
! * otherwise, set interactive prompt if necessary
! * and get another line
! */
! if (pset->cur_cmd_interactive)
! {
! int prompt_status;
!
! if (in_quote && in_quote == '\'')
! prompt_status = PROMPT_SINGLEQUOTE;
! else if (in_quote && in_quote == '"')
! prompt_status= PROMPT_DOUBLEQUOTE;
! else if (xcomment != NULL)
! prompt_status = PROMPT_COMMENT;
! else if (query_buf->len > 0)
! prompt_status = PROMPT_CONTINUE;
else
! prompt_status = PROMPT_READY;
! line = gets_interactive(get_prompt(pset, prompt_status));
! }
! else
! line = gets_fromFile(source);
! }
! /* Setting these will not have effect until next line */
! die_on_error = GetVariableBool(pset->vars, "die_on_error");
! interpol_char = GetVariable(pset->vars, "sql_interpol");;
!
!
! /*
! * query_buf holds query already accumulated. line is the malloc'd
! * new line of input (note it must be freed before looping around!)
! * query_start is the next command start location within the line.
! */
!
! /* No more input. Time to quit, or \i done */
! if (line == NULL || (!pset->cur_cmd_interactive && *line == '\0'))
! {
! if (GetVariableBool(pset->vars, "echo") && !GetVariableBool(pset->vars, "quiet"))
! puts("EOF");
! eof = true;
! continue;
! }
! /* not currently inside an extended comment? */
! if (xcomment)
! xcomment = line;
! /* strip trailing backslashes, they don't have a clear meaning */
! while (1) {
! char * cp = strrchr(line, '\\');
! if (cp && (*(cp + 1) == '\0'))
! *cp = '\0';
! else
! break;
! }
-
- /* echo back if input is from file and flag is set */
- if (!pset->cur_cmd_interactive && GetVariableBool(pset->vars, "echo"))
- fprintf(stderr, "%s\n", line);
-
-
- /* interpolate variables into SQL */
- len = strlen(line);
- thislen = PQmblen(line);
-
- for (i = 0; line[i]; i += (thislen = PQmblen(&line[i])) ) {
- if (interpol_char && interpol_char[0] != '\0' && interpol_char[0] == line[i]) {
- size_t in_length, out_length;
- const char * value;
- char * new;
- bool closer; /* did we have a closing delimiter or just an end of line? */
-
- in_length = strcspn(&line[i+thislen], interpol_char);
- closer = line[i + thislen + in_length] == line[i];
- line[i + thislen + in_length] = '\0';
- value = interpolate_var(&line[i + thislen], pset);
- out_length = strlen(value);
-
- new = malloc(len + out_length - (in_length + (closer ? 2 : 1)) + 1);
- if (!new) {
- perror("malloc");
- exit(EXIT_FAILURE);
- }
-
- new[0] = '\0';
- strncat(new, line, i);
- strcat(new, value);
- if (closer)
- strcat(new, line + i + 2 + in_length);
! free(line);
! line = new;
! i += out_length;
! }
! }
! /* nothing left on line? then ignore */
! if (line[0] == '\0') {
! free(line);
! continue;
! }
! slashCmdStatus = CMD_UNKNOWN;
! len = strlen(line);
! query_start = 0;
! /*
! * Parse line, looking for command separators.
! *
! * The current character is at line[i], the prior character at
! * line[i - prevlen], the next character at line[i + thislen].
! */
! prevlen = 0;
! thislen = (len > 0) ? PQmblen(line) : 0;
#define ADVANCE_1 (prevlen = thislen, i += thislen, thislen = PQmblen(line+i))
! success = true;
! for (i = 0; i < len; ADVANCE_1) {
! if (!success && die_on_error)
! break;
!
!
! /* was the previous character a backslash? */
! if (i > 0 && line[i - prevlen] == '\\')
! was_bslash = true;
! else
! was_bslash = false;
!
!
! /* in quote? */
! if (in_quote) {
! /* end of quote */
! if (line[i] == in_quote && !was_bslash)
! in_quote = '\0';
! }
!
! /* start of quote */
! else if (line[i] == '\'' || line[i] == '"')
! in_quote = line[i];
!
! /* in extended comment? */
! else if (xcomment != NULL) {
! if (line[i] == '*' && line[i + thislen] == '/') {
! xcomment = NULL;
! ADVANCE_1;
! }
! }
!
! /* start of extended comment? */
! else if (line[i] == '/' && line[i + thislen] == '*') {
! xcomment = &line[i];
! ADVANCE_1;
! }
!
! /* single-line comment? truncate line */
! else if ((line[i] == '-' && line[i + thislen] == '-') ||
! (line[i] == '/' && line[i + thislen] == '/'))
! {
! line[i] = '\0'; /* remove comment */
! break;
! }
!
! /* count nested parentheses */
! else if (line[i] == '(')
! paren_level++;
!
! else if (line[i] == ')' && paren_level > 0)
! paren_level--;
!
! /* semicolon? then send query */
! else if (line[i] == ';' && !was_bslash && paren_level==0) {
! line[i] = '\0';
! /* is there anything else on the line? */
! if (line[query_start + strspn(line + query_start, " \t")]!='\0') {
! /* insert a cosmetic newline, if this is not the first line in the buffer */
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! /* append the line to the query buffer */
! appendPQExpBufferStr(query_buf, line + query_start);
! }
!
! /* execute query */
! success = SendQuery(pset, query_buf->data);
!
! resetPQExpBuffer(query_buf);
! query_start = i + thislen;
! }
!
! /* backslash command */
! else if (was_bslash) {
! const char * end_of_cmd = NULL;
!
! line[i - prevlen] = '\0'; /* overwrites backslash */
!
! /* is there anything else on the line? */
! if (line[query_start + strspn(line + query_start, " \t")]!='\0') {
! /* insert a cosmetic newline, if this is not the first line in the buffer */
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! /* append the line to the query buffer */
! appendPQExpBufferStr(query_buf, line + query_start);
! }
!
! /* handle backslash command */
!
! slashCmdStatus = HandleSlashCmds(pset, &line[i], query_buf, &end_of_cmd);
!
! success = slashCmdStatus != CMD_ERROR;
!
! if (slashCmdStatus == CMD_SEND) {
! success = SendQuery(pset, query_buf->data);
! resetPQExpBuffer(query_buf);
! query_start = i + thislen;
! }
!
! /* is there anything left after the backslash command? */
! if (end_of_cmd) {
! i += end_of_cmd - &line[i];
! query_start = i;
}
- else
- break;
- }
- }
! if (!success && die_on_error && !pset->cur_cmd_interactive) {
! successResult = EXIT_USER;
! break;
! }
! if (slashCmdStatus == CMD_TERMINATE) {
! successResult = EXIT_SUCCESS;
! break;
! }
! /* Put the rest of the line in the query buffer. */
! if (line[query_start + strspn(line + query_start, " \t")]!='\0') {
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! appendPQExpBufferStr(query_buf, line + query_start);
! }
! free(line);
! /* In single line mode, send off the query if any */
! if (query_buf->data[0] != '\0' && GetVariableBool(pset->vars, "singleline")) {
! success = SendQuery(pset, query_buf->data);
! resetPQExpBuffer(query_buf);
! }
!
! /* Have we lost the db connection? */
! if (pset->db == NULL && !pset->cur_cmd_interactive) {
! successResult = EXIT_BADCONN;
! break;
! }
! } /* while */
! destroyPQExpBuffer(query_buf);
! pset->cur_cmd_source = prev_cmd_source;
! pset->cur_cmd_interactive = prev_cmd_interactive;
! return successResult;
} /* MainLoop() */
-
--- 26,405 ----
int
MainLoop(PsqlSettings *pset, FILE *source)
{
! PQExpBuffer query_buf; /* buffer for query being accumulated */
! char *line; /* current line of input */
! char *xcomment; /* start of extended comment */
! int len; /* length of the line */
! int successResult = EXIT_SUCCESS;
! backslashResult slashCmdStatus;
!
! bool eof = false; /* end of our command input? */
! bool success;
! char in_quote; /* == 0 for no in_quote */
! bool was_bslash; /* backslash */
! int paren_level;
! unsigned int query_start;
!
! int i,
! prevlen,
! thislen;
!
! /* Save the prior command source */
! FILE *prev_cmd_source;
! bool prev_cmd_interactive;
!
! bool die_on_error;
! const char *interpol_char;
!
!
! /* Save old settings */
! prev_cmd_source = pset->cur_cmd_source;
! prev_cmd_interactive = pset->cur_cmd_interactive;
!
! /* Establish new source */
! pset->cur_cmd_source = source;
! pset->cur_cmd_interactive = ((source == stdin) && !pset->notty);
!
!
! query_buf = createPQExpBuffer();
! if (!query_buf)
{
! perror("createPQExpBuffer");
! exit(EXIT_FAILURE);
}
!
! xcomment = NULL;
! in_quote = 0;
! paren_level = 0;
! slashCmdStatus = CMD_UNKNOWN; /* set default */
!
!
! /* main loop to get queries and execute them */
! while (!eof)
{
! if (slashCmdStatus == CMD_NEWEDIT)
! {
!
! /*
! * just returned from editing the line? then just copy to the
! * input buffer
! */
! line = strdup(query_buf->data);
! resetPQExpBuffer(query_buf);
! /* reset parsing state since we are rescanning whole query */
! xcomment = NULL;
! in_quote = 0;
! paren_level = 0;
! }
else
! {
! /*
! * otherwise, set interactive prompt if necessary and get
! * another line
! */
! if (pset->cur_cmd_interactive)
! {
! int prompt_status;
!
! if (in_quote && in_quote == '\'')
! prompt_status = PROMPT_SINGLEQUOTE;
! else if (in_quote && in_quote == '"')
! prompt_status = PROMPT_DOUBLEQUOTE;
! else if (xcomment != NULL)
! prompt_status = PROMPT_COMMENT;
! else if (query_buf->len > 0)
! prompt_status = PROMPT_CONTINUE;
! else
! prompt_status = PROMPT_READY;
!
! line = gets_interactive(get_prompt(pset, prompt_status));
! }
! else
! line = gets_fromFile(source);
! }
! /* Setting these will not have effect until next line */
! die_on_error = GetVariableBool(pset->vars, "die_on_error");
! interpol_char = GetVariable(pset->vars, "sql_interpol");;
!
!
! /*
! * query_buf holds query already accumulated. line is the
! * malloc'd new line of input (note it must be freed before
! * looping around!) query_start is the next command start location
! * within the line.
! */
!
! /* No more input. Time to quit, or \i done */
! if (line == NULL || (!pset->cur_cmd_interactive && *line == '\0'))
! {
! if (GetVariableBool(pset->vars, "echo") && !GetVariableBool(pset->vars, "quiet"))
! puts("EOF");
! eof = true;
! continue;
! }
! /* not currently inside an extended comment? */
! if (xcomment)
! xcomment = line;
! /* strip trailing backslashes, they don't have a clear meaning */
! while (1)
! {
! char *cp = strrchr(line, '\\');
!
! if (cp && (*(cp + 1) == '\0'))
! *cp = '\0';
! else
! break;
! }
! /* echo back if input is from file and flag is set */
! if (!pset->cur_cmd_interactive && GetVariableBool(pset->vars, "echo"))
! fprintf(stderr, "%s\n", line);
!
!
! /* interpolate variables into SQL */
! len = strlen(line);
! thislen = PQmblen(line);
!
! for (i = 0; line[i]; i += (thislen = PQmblen(&line[i])))
! {
! if (interpol_char && interpol_char[0] != '\0' && interpol_char[0] == line[i])
! {
! size_t in_length,
! out_length;
! const char *value;
! char *new;
! bool closer; /* did we have a closing delimiter
! * or just an end of line? */
!
! in_length = strcspn(&line[i + thislen], interpol_char);
! closer = line[i + thislen + in_length] == line[i];
! line[i + thislen + in_length] = '\0';
! value = interpolate_var(&line[i + thislen], pset);
! out_length = strlen(value);
!
! new = malloc(len + out_length - (in_length + (closer ? 2 : 1)) + 1);
! if (!new)
! {
! perror("malloc");
! exit(EXIT_FAILURE);
! }
!
! new[0] = '\0';
! strncat(new, line, i);
! strcat(new, value);
! if (closer)
! strcat(new, line + i + 2 + in_length);
!
! free(line);
! line = new;
! i += out_length;
! }
! }
! /* nothing left on line? then ignore */
! if (line[0] == '\0')
! {
! free(line);
! continue;
! }
! slashCmdStatus = CMD_UNKNOWN;
! len = strlen(line);
! query_start = 0;
! /*
! * Parse line, looking for command separators.
! *
! * The current character is at line[i], the prior character at line[i
! * - prevlen], the next character at line[i + thislen].
! */
! prevlen = 0;
! thislen = (len > 0) ? PQmblen(line) : 0;
#define ADVANCE_1 (prevlen = thislen, i += thislen, thislen = PQmblen(line+i))
! success = true;
! for (i = 0; i < len; ADVANCE_1)
! {
! if (!success && die_on_error)
! break;
!
!
! /* was the previous character a backslash? */
! if (i > 0 && line[i - prevlen] == '\\')
! was_bslash = true;
! else
! was_bslash = false;
!
!
! /* in quote? */
! if (in_quote)
! {
! /* end of quote */
! if (line[i] == in_quote && !was_bslash)
! in_quote = '\0';
! }
!
! /* start of quote */
! else if (line[i] == '\'' || line[i] == '"')
! in_quote = line[i];
!
! /* in extended comment? */
! else if (xcomment != NULL)
! {
! if (line[i] == '*' && line[i + thislen] == '/')
! {
! xcomment = NULL;
! ADVANCE_1;
! }
! }
!
! /* start of extended comment? */
! else if (line[i] == '/' && line[i + thislen] == '*')
! {
! xcomment = &line[i];
! ADVANCE_1;
! }
!
! /* single-line comment? truncate line */
! else if ((line[i] == '-' && line[i + thislen] == '-') ||
! (line[i] == '/' && line[i + thislen] == '/'))
! {
! line[i] = '\0'; /* remove comment */
! break;
! }
!
! /* count nested parentheses */
! else if (line[i] == '(')
! paren_level++;
!
! else if (line[i] == ')' && paren_level > 0)
! paren_level--;
!
! /* semicolon? then send query */
! else if (line[i] == ';' && !was_bslash && paren_level == 0)
! {
! line[i] = '\0';
! /* is there anything else on the line? */
! if (line[query_start + strspn(line + query_start, " \t")] != '\0')
! {
!
! /*
! * insert a cosmetic newline, if this is not the first
! * line in the buffer
! */
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! /* append the line to the query buffer */
! appendPQExpBufferStr(query_buf, line + query_start);
! }
!
! /* execute query */
! success = SendQuery(pset, query_buf->data);
!
! resetPQExpBuffer(query_buf);
! query_start = i + thislen;
! }
!
! /* backslash command */
! else if (was_bslash)
! {
! const char *end_of_cmd = NULL;
!
! line[i - prevlen] = '\0'; /* overwrites backslash */
!
! /* is there anything else on the line? */
! if (line[query_start + strspn(line + query_start, " \t")] != '\0')
! {
!
! /*
! * insert a cosmetic newline, if this is not the first
! * line in the buffer
! */
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! /* append the line to the query buffer */
! appendPQExpBufferStr(query_buf, line + query_start);
! }
!
! /* handle backslash command */
!
! slashCmdStatus = HandleSlashCmds(pset, &line[i], query_buf, &end_of_cmd);
!
! success = slashCmdStatus != CMD_ERROR;
!
! if (slashCmdStatus == CMD_SEND)
! {
! success = SendQuery(pset, query_buf->data);
! resetPQExpBuffer(query_buf);
! query_start = i + thislen;
! }
!
! /* is there anything left after the backslash command? */
! if (end_of_cmd)
! {
! i += end_of_cmd - &line[i];
! query_start = i;
! }
! else
! break;
! }
}
! if (!success && die_on_error && !pset->cur_cmd_interactive)
! {
! successResult = EXIT_USER;
! break;
! }
! if (slashCmdStatus == CMD_TERMINATE)
! {
! successResult = EXIT_SUCCESS;
! break;
! }
! /* Put the rest of the line in the query buffer. */
! if (line[query_start + strspn(line + query_start, " \t")] != '\0')
! {
! if (query_buf->len > 0)
! appendPQExpBufferChar(query_buf, '\n');
! appendPQExpBufferStr(query_buf, line + query_start);
! }
! free(line);
! /* In single line mode, send off the query if any */
! if (query_buf->data[0] != '\0' && GetVariableBool(pset->vars, "singleline"))
! {
! success = SendQuery(pset, query_buf->data);
! resetPQExpBuffer(query_buf);
! }
!
! /* Have we lost the db connection? */
! if (pset->db == NULL && !pset->cur_cmd_interactive)
! {
! successResult = EXIT_BADCONN;
! break;
! }
! } /* while */
! destroyPQExpBuffer(query_buf);
! pset->cur_cmd_source = prev_cmd_source;
! pset->cur_cmd_interactive = prev_cmd_interactive;
! return successResult;
} /* MainLoop() */
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 17:11 ` Re: Double newline bug with DBD::Pg: Where best to fix? Peter Eisentraut <peter_e@gmx.net>
2001-02-28 17:32 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-02-28 18:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 19:02 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 2001-02-28 18:06 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Tom Lane <tgl@sss.pgh.pa.us>; Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces
> > Tom Lane writes:
> >
> > > That's all fine IMHO. But this isn't:
> > >
> > > regression=# select 'a string
> > > regression'#
> > > regression'# with a newline';
> > > ?column?
> > > -------------------------
> > > a string
> > > with a newline
> > > (1 row)
> >
> > Well, *somebody* once figured that he could optimize away whitespace...
> > Not so. Here's a patch that gets this case right:
>
Peter, I have thought about this some more. Seems we either add the
'quote' check, or remove the optimizations completely. Because we have
to change it one way or the other for 7.1, it seems we should just
remove the tests totally. Neither of us understand why they are even in
the code.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 17:11 ` Re: Double newline bug with DBD::Pg: Where best to fix? Peter Eisentraut <peter_e@gmx.net>
2001-02-28 17:32 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 18:06 ` Re: Double newline bug with DBD::Pg: Where best to fix? Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-02-28 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2001-02-28 19:02 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Peter Eisentraut <peter_e@gmx.net>; Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Peter, I have thought about this some more. Seems we either add the
> 'quote' check, or remove the optimizations completely. Because we have
> to change it one way or the other for 7.1, it seems we should just
> remove the tests totally.
It's kind of handy that, for example, you can hit return a few times
without causing psql to think that something's been put into the query
buffer. I'm worried that removing this code entirely might lead to
problems like failure to recognize backslash commands preceded by
whitespace.
Peter's patch looks like the most conservative fix to me ...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 17:11 ` Re: Double newline bug with DBD::Pg: Where best to fix? Peter Eisentraut <peter_e@gmx.net>
@ 2001-02-28 17:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 2001-02-28 17:42 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Dan Lyke <danlyke@flutterby.com>; pgsql-interfaces
> Both of these whitespace-away-optimizing checks could probably be removed
> completely, but I'm not sure whether there isn't some case that relies on
> it. I'm gonna stare at it for a few hours and then check it in.
Not sure why the optimizations are there either, though I thought I put
them in there myself.
However, now that I look at the diff, the tests were there in your
initial 1.1 version too, I just moved them. I see no problem removing
the tests, though at this late date, maybe the removal should happen in
7.2. Not sure.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Double newline bug with DBD::Pg: Where best to fix?
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Re: Double newline bug with DBD::Pg: Where best to fix? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-02-27 20:47 ` ivan <ivan-pgsql-interfaces@420.am>
1 sibling, 0 replies; 10+ messages in thread
From: ivan @ 2001-02-27 20:47 UTC (permalink / raw)
To: pgsql-interfaces; +Cc: Dan Lyke <danlyke@flutterby.com>
On Tue, Feb 27, 2001 at 03:21:58PM -0500, Tom Lane wrote:
> Dan Lyke <danlyke@flutterby.com> writes:
> > If, using DBI with DBD::Pg, you call
>
> > $dbh->do('INSERT INTO xyz(stuff) VALUES('
> > $dbh->quote("a string with \n\n a two line break").')');
>
> > You'll get a string with a single line break back, because DBD::Pg
> > doesn't escape newlines into '\n'.
>
> There shouldn't be any need to convert newlines into \n in quoted
> strings; either way should work.
>
> > This is consistent with the interface via psql:
>
> > test=# insert into xyz(stuff) values('a string with
> > test'#
> > test'# a two line break');
> > test=# select stuff from xyz;
> > stuff
> > ----------------------------------
> > a string with
> > a two line break
> > (1 row)
>
> > (As you can see there are also some interesting issues with leading
> > spaces going on here).
>
> I would call this a clear bug in psql. I cannot replicate the loss of
> leading whitespace in current sources, but I do see the loss of the
> empty line. Turning on debug logging shows that the empty line has
> been stripped by psql, not the backend --- it's not there in the query
> that arrives at the backend.
>
> I tried a similar experiment in pgtclsh and didn't see any loss of
> blank lines, so the problem seems to be specific to psql. Your
> observation in DBI must be an independent bug in either DBD::Pg or
> DBI itself.
>
> > I don't have a copy of the SQL standard in front of me, so I'm not
> > sure whether this needs to be fixed in the PostgreSQL parser (seems
> > like the logical place, but we're into minutae of compatibility) or
> > DBD::Pg (the easy place, but maybe this is a hack that's going to have
> > to go in every interface).
>
> The Postgres parser is not broken, AFAICT. If you can find where this
> is happening in the DBI/DBD path, please submit a patch.
One of my cow orkers sent me a patch for this; it sounds like we tickled
the same bug you did. See below.
The other (unrelated) problem I'm having with DBD::Pg is that you can't
insert more than 64k of data using placeholders, i.e.
$sth = $dbh->prepare("INSERT INTO table VALUES ( ?, ?, ? )");
$sth->execute($a, $b, $c);
will *segfault* if $a $b or $c is larger than 64k.
--
meow
_ivan
inline patch 1: ./dbdimp.c Fri Feb 23 15:17:37 2001 (+27/-2 lines) download inline-1.patch
--- /tmp/dbd-pg/DBD-Pg-0.95/dbdimp.c Mon Jul 10 10:47:51 2000
+++ ./dbdimp.c Fri Feb 23 15:17:37 2001
@@ -634,9 +634,22 @@
if (in_literal) {
/* check if literal ends but keep quotes in literal */
+ if (dbis->debug) {
+ fprintf(DBILOGFP, "*src = %c, in_literal = %c, *(src+1) = %c\n", *src, in_literal, *(src+1));
+ }
+#if 1
+ if (*src == in_literal) {
+ if (*(src+1) == in_literal) {
+ *dest++ = *src++;
+ } else {
+ in_literal = 0;
+ }
+ }
+#else
if (*src == in_literal && *(src-1) != '\\') {
in_literal = 0;
- }
+ }
+#endif
*dest++ = *src++;
continue;
}
@@ -1022,9 +1035,21 @@
if (in_literal) {
/* check if literal ends but keep quotes in literal */
+ if (dbis->debug) {
+ fprintf(DBILOGFP, "*src = %c, in_literal = %c, *(src+1) = %c\n", *src, in_literal, *(src+1));
+ }
+ if (*src == in_literal) {
+ if (*(src+1) == in_literal) {
+ *dest++ = *src++;
+ } else {
+ in_literal = 0;
+ }
+ }
+#if 0
if (*src == in_literal && *(src-1) != '\\') {
in_literal = 0;
- }
+ }
+#endif
*dest++ = *src++;
continue;
}
end of thread, other threads:[~2001-02-28 19:02 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-27 19:25 Double newline bug with DBD::Pg: Where best to fix? Dan Lyke <danlyke@flutterby.com>
2001-02-27 20:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-27 20:39 ` Dan Lyke <danlyke@flutterby.com>
2001-02-28 17:11 ` Peter Eisentraut <peter_e@gmx.net>
2001-02-28 17:32 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 18:06 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-28 19:02 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-02-28 17:42 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-02-27 20:47 ` ivan <ivan-pgsql-interfaces@420.am>
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