Thread: missing semicolon at end of psql files

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?

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.