Re: BUG #17379: Cannot issue multi-command statements using a replication connection

PG Bug reporting form <noreply@postgresql.org> writes:
> When I issue the following multi-command query on a replication connection I
> receive a syntax error:

> $psql "dbname=postgres replication=database" -c "select 1;select 2;" 
> ERROR:  syntax error

As I mentioned on the pgsql-novice thread, I think the proximate cause
of this is that repl_gram.y's make_sqlcmd() tries to skip to the end
of the SQL statement, but for some reason it is coded to stop at a
semicolon.  It needs to eat the whole rest of the string,
unconditionally.

It gets worse though.  repl_scanner.l is not built to lex everything
the core scanner can (and I don't think we want to require it to).
But this approach to consuming non-replication commands requires it
to be able to do so.  It's not very hard to find cases that break it,
for example

$ psql "dbname=postgres replication=database"
psql (15devel)
Type "help" for help.

postgres=# select $x$ " $x$;
ERROR:  unterminated quoted string

Of course that happens because repl_scanner.l doesn't know about
dollar-quoting, so it tries to process the ", which it mis-recognizes
as the start of a quoted string.  We probably want to shut down the
lexer as soon as we realize it's a non-replication command, instead
of asking it to lex to the end of the string.

Still worse, if you repeat that a few times, you find the behavior
is unstable:

postgres=# select $x$ " $x$;
 ?column? 
----------
  " 
(1 row)

postgres=# select $x$ " $x$;
ERROR:  unterminated quoted string
postgres=# select $x$ " $x$;
 ?column? 
----------
  " 
(1 row)

postgres=# select $x$ " $x$;
ERROR:  unterminated quoted string

I've not traced the reason for that in detail, but I bet it is
because there is static state in repl_scanner.l that doesn't
get cleaned up after elog(ERROR).

Oh, and another thing:

postgres=# /* foo */ select 42;
ERROR:  syntax error

Presuming that all SQL statements start with a keyword has
its problems.

This sort of half-baked implementation was probably fine when
the replication protocol was first designed, but if we're going
to claim that clients can issue arbitrary SQL, it needs upgrading.

            regards, tom lane