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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: greg(dot)rychlewski(at)gmail(dot)com
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #17379: Cannot issue multi-command statements using a replication connection
Date: 2022-01-24 14:52:32
Message-ID: 1874781.1643035952@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexander Lakhin 2022-01-24 18:00:00 Re: BUG #17355: Server crashes on ExecReScanForeignScan in postgres_fdw when accessing foreign partition
Previous Message B Ganesh Kishan 2022-01-24 05:55:11 RE: BUG #17375: RECOVERY TARGET TIME RESTORE IS FAILING TO START SERVER