Thread: replication connection and multi-command queries
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
On Sun, Jan 23, 2022 at 4:47 PM Greg Rychlewski <greg.rychlewski@gmail.com> 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;".
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.
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 <david.g.johnston@gmail.com> wrote:
On Sun, Jan 23, 2022 at 4:47 PM Greg Rychlewski <greg.rychlewski@gmail.com> 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;".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.
On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <greg.rychlewski@gmail.com> wrote:
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.
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.
On Sun, Jan 23, 2022 at 5:28 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <greg.rychlewski@gmail.com> wrote: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.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.
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.
On Sun, Jan 23, 2022 at 7:37 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sun, Jan 23, 2022 at 5:28 PM David G. Johnston <david.g.johnston@gmail.com> wrote:On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <greg.rychlewski@gmail.com> wrote: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.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.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.
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.
Greg Rychlewski <greg.rychlewski@gmail.com> writes:
> On Sun, Jan 23, 2022 at 7:37 PM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>> 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.
> 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