missing semicolon at end of psql files

2012/9/12 Peter Eisentraut <peter_e@gmx.net>

I was surprised to find that psql -f file.sql with a file such as this

select 1;
select 2

executes both commands even though the second one is not terminated.

I realize that this is inconsistently handled throughout the system, for
example libpq APIs don't care about the missing semicolon, but
interactive psql does.

Furthermore, if the query string looks like
";;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;"
the backend will return EmptyQueryResponse.
But according to
http://www.postgresql.org/docs/9.2/static/protocol-flow.html
EmptyQueryResponse means "An empty query string was recognized."
But this is debatable what is meant by "empty query string" --
when strlen(query_string) == 0 or when the query_string does
not contains any SQL command?

But what bothered me about this is that if a file gets truncated by
accident, there could be an unqualified DELETE or something similar at
the end.

Good point. The result can be disastrous.

Comments?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
// Dmitriy.

Peter Eisentraut <peter_e@gmx.net> writes:

I was surprised to find that psql -f file.sql with a file such as this
select 1;
select 2

executes both commands even though the second one is not terminated.

I realize that this is inconsistently handled throughout the system, for
example libpq APIs don't care about the missing semicolon, but
interactive psql does.

But what bothered me about this is that if a file gets truncated by
accident, there could be an unqualified DELETE or something similar at
the end.

Comments?

I'm a little dubious about changing this behavior. As you say, it's
not like we're consistent about requiring semicolons everywhere, and
frankly I think the argument about truncated files is specious.
I've never heard of that actually happening to anybody.

What would you have it do instead? Throw an error? Silently drop
the last command (which would be the same thing, if nobody was paying
attention to the error message)? I think the compatibility lossage
from either would outweigh the benefit.

Now having said that, I've occasionally wished I could *continue*
a command across file boundaries. But I don't think it's worth the
compatibility issues it'd create.

regards, tom lane

On Wed, 2012-09-12 at 17:20 +0300, Heikki Linnakangas wrote:

On 12.09.2012 17:10, Peter Eisentraut wrote:

I was surprised to find that psql -f file.sql with a file such as this

select 1;
select 2

executes both commands even though the second one is not terminated.

I realize that this is inconsistently handled throughout the system, for
example libpq APIs don't care about the missing semicolon, but
interactive psql does.

Even interactive psql doesn't require a semicolon at the end; it's just
that without it, it doesn't know whether you're still adding to the
command or if it's finished. But you can also use \g:

ostgres=# select 1
postgres-# \g
?column?
----------
1
(1 row)

A \g is equivalent to a semicolon, at least as far as saying, execute
this command now.

Note that

postgres=# select 1
postgres-# <press Ctrl-D here>

does not execute the command before quitting.

On Wed, Sep 12, 2012 at 03:20:24PM -0400, Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

I was surprised to find that psql -f file.sql with a file such as this
select 1;
select 2

executes both commands even though the second one is not terminated.

I'm a little dubious about changing this behavior. As you say, it's
not like we're consistent about requiring semicolons everywhere, and
frankly I think the argument about truncated files is specious.
I've never heard of that actually happening to anybody.

What would you have it do instead? Throw an error? Silently drop
the last command (which would be the same thing, if nobody was paying
attention to the error message)? I think the compatibility lossage
from either would outweigh the benefit.

Fully agreed.