Subject: replication connection and multi-command queries

From: Greg Rychlewski <[email-masked]>
Date: Sun, 23 Jan 2022 18:47:36 -0500

Hi,

I have a situation where I've set up a replication connection and tried to
issue a query with the simple protocol that has multiple commands "SELECT
1; SELECT 2;". I'm receiving a syntax error like this:

%{code: :syntax_error, file: "repl_scanner.l", line: "225", message:
> "syntax error", pg_code: "42601", routine: "replication_yyerror", severity:
> "ERROR", unknown: "ERROR"}

The above is formatted by the driver library I am using, but might have
some useful info.

I was curious: is it by design that replications can't use multiple
commands? I just want to make sure I'm not doing something wrong.

Thanks for your help,
Greg

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Jan 2022 17:16:36 -0700

On Sun, Jan 23, 2022 at 4:47 PM Greg Rychlewski <[email-masked]>
wrote:

> 1; SELECT 2;".

What do you mean by "I've set up a replication connection"?  IIUC they are
not intended for interactive usage, they are intended for system-to-system
data replication.  If you want to issue queries just connect with a "normal
connection".

David J.

---

From: Greg Rychlewski <[email-masked]>
Date: Sun, 23 Jan 2022 19:24:34 -0500

Thanks for your reply. To give more context: I am contributing to a
Postgres driver used by a programming language. We are currently trying to
understand what is legal/not legal to be sent through this connection.

In the documentation it states that the simple query protocol is followed
on these connections, which is why we assumed a multi-command statement
would work. I just wanted to make sure we are not doing anything wrong and
that it is disallowed by design. We can, for instance, send "SELECT 1;" and
receive a result.

On Sun, Jan 23, 2022 at 7:16 PM David G. Johnston <
[email-masked]> wrote:

>> I have a situation where I've set up a replication connection and
>> tried to issue a query with the simple protocol that has multiple commands
>> "SELECT 1; SELECT 2;".
>>
>

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Jan 2022 17:28:36 -0700

> that it is disallowed by design. We can, for instance, send "SELECT 1;" and
Please don't top-post.  The convention on these lists is to inline post.

If you are interacting at the protocol layer you probably should be posting
to -hackers, not -novice.

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 23 Jan 2022 17:37:03 -0700

On Sun, Jan 23, 2022 at 5:28 PM David G. Johnston <
[email-masked]> wrote:

> If you are interacting at the protocol layer you probably should be
> posting to -hackers, not -novice.
>
>
Or a -bug report.  It would be good to show the code you are using to
provoke the error.  But I do see where we document the Simple Query
Protocol and do not qualify it such that it doesn't take a multi-command
message.

David J.

---

From: Greg Rychlewski <[email-masked]>
Date: Sun, 23 Jan 2022 19:43:44 -0500

On Sun, Jan 23, 2022 at 7:37 PM David G. Johnston <
[email-masked]> wrote:

>> On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <
>> [email-masked]> wrote:
>>
>>> In the documentation it states that the simple query protocol is
>>> followed on these connections, which is why we assumed a multi-command
>>> statement would work. I just wanted to make sure we are not doing anything
>>> wrong and that it is disallowed by design. We can, for instance, send
>>> "SELECT 1;" and receive a result.
>>>
>>>>
>>>>

Thank you. I will give those lists a shot. Apologies for the top-posting. I
think I did it properly this time but gmail is a bit weird. Apologies if
this is not inline either.

---

From: Tom Lane <[email-masked]>
Date: Sun, 23 Jan 2022 21:06:41 -0500

> Thank you. I will give those lists a shot. Apologies for the top-posting.

This does seem like a bug, and I concur with David that it doesn't belong
on the novice list anyway.

I noticed that repl_gram.y's make_sqlcmd() tries to skip over the rest
of a plain-SQL command, but stops at the first semicolon.  I'm not quite
sure why that leads to a syntax error, because it looks like the grammar
shouldn't really care what follows the leading keyword.  I took that
out (forcing the loop to run till the lexer returns zero), and this
problem went away, but there are still others.  Notably:

1. This solution requires the repl_scanner.l lexer to be able to lex
everything that can appear in a plain-SQL command, which it is just
a thousand or so lines short of being able to do :-(.  The cheesy
substitute of returning "T_WORD" for "." doesn't work well, for
example

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

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

2. There is something seriously wrong with the error recovery
in repl_gram/repl_scanner, because if I try that repeatedly,
the results change:

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

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

So we need some work there, but you ought to file this as
a bug report or start a -hackers thread; it's way out of
scope here.

			regards, tom lane