public inbox for [email protected]
help / color / mirror / Atom feedreplication connection and multi-command queries
7+ messages / 3 participants
[nested] [flat]
* replication connection and multi-command queries
@ 2022-01-23 23:47 Greg Rychlewski <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Rychlewski @ 2022-01-23 23:47 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 00:16 David G. Johnston <[email protected]>
parent: Greg Rychlewski <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-01-24 00:16 UTC (permalink / raw)
To: Greg Rychlewski <[email protected]>; +Cc: pgsql-novice <[email protected]>
On Sun, Jan 23, 2022 at 4:47 PM Greg Rychlewski <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 00:24 Greg Rychlewski <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Rychlewski @ 2022-01-24 00:24 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-novice <[email protected]>
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 protected]> wrote:
> On Sun, Jan 23, 2022 at 4:47 PM Greg Rychlewski <[email protected]>
> 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.
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 00:28 David G. Johnston <[email protected]>
parent: Greg Rychlewski <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-01-24 00:28 UTC (permalink / raw)
To: Greg Rychlewski <[email protected]>; +Cc: pgsql-novice <[email protected]>
On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <[email protected]>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 00:37 David G. Johnston <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2022-01-24 00:37 UTC (permalink / raw)
To: Greg Rychlewski <[email protected]>; +Cc: pgsql-novice <[email protected]>
On Sun, Jan 23, 2022 at 5:28 PM David G. Johnston <
[email protected]> wrote:
> On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 00:43 Greg Rychlewski <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Rychlewski @ 2022-01-24 00:43 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-novice <[email protected]>
On Sun, Jan 23, 2022 at 7:37 PM David G. Johnston <
[email protected]> wrote:
> On Sun, Jan 23, 2022 at 5:28 PM David G. Johnston <
> [email protected]> wrote:
>
>> On Sun, Jan 23, 2022 at 5:24 PM Greg Rychlewski <
>> [email protected]> 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: replication connection and multi-command queries
@ 2022-01-24 02:06 Tom Lane <[email protected]>
parent: Greg Rychlewski <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2022-01-24 02:06 UTC (permalink / raw)
To: Greg Rychlewski <[email protected]>; +Cc: David G. Johnston <[email protected]>; pgsql-novice <[email protected]>
Greg Rychlewski <[email protected]> writes:
> On Sun, Jan 23, 2022 at 7:37 PM David G. Johnston <
> [email protected]> 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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-01-24 02:06 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-01-23 23:47 replication connection and multi-command queries Greg Rychlewski <[email protected]>
2022-01-24 00:16 ` David G. Johnston <[email protected]>
2022-01-24 00:24 ` Greg Rychlewski <[email protected]>
2022-01-24 00:28 ` David G. Johnston <[email protected]>
2022-01-24 00:37 ` David G. Johnston <[email protected]>
2022-01-24 00:43 ` Greg Rychlewski <[email protected]>
2022-01-24 02:06 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox