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

PG Bug reporting form <noreply(at)postgresql(dot)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