From: Tom Lane <tgl@sss.pgh.pa.us>
To: greg.rychlewski@gmail.com
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17379: Cannot issue multi-command statements using a
 replication connection
In-reply-to: <17379-6a5c6cfb3f1f5e77@postgresql.org>
References: <17379-6a5c6cfb3f1f5e77@postgresql.org>
Comments: In-reply-to PG Bug reporting form <noreply@postgresql.org>
	message dated "Mon, 24 Jan 2022 01:10:49 +0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1874780.1643035952.1@sss.pgh.pa.us>
Date: Mon, 24 Jan 2022 09:52:32 -0500
Message-ID: <1874781.1643035952@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1874781.1643035952%40sss.pgh.pa.us>
Precedence: bulk

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