Handle PGRES_COPY_BOTH in psql for logical replication?

On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote:

Hello Hackers,

At the moment one can open up a replication connection using psql and use
all of the commands available on this special type of connection, except
for START_REPLICATION:

$ psql -d "dbname=test replication=database"
psql (9.4.1)
Type "help" for help.

test=# IDENTIFY_SYSTEM;
systemid | timeline | xlogpos | dbname
---------------------+----------+-----------+---------
6146106447402521313 | 1 | 0/76422B0 | bw_test
(1 row)

test=# START_REPLICATION SLOT test LOGICAL 0/0;
unexpected PQresultStatus: 8
test=#

This is due to PGRES_COPY_BOTH not being handled in psql. With
introduction of logical replication I think it makes sense to support this
mode, for the sake of (semi-)interactive testing from psql.

How would that look like? It's just binary data you'd get back? And what
would be the feedback messages and such look like, and what would be the
content?

For example, one could try replication from different wal positions,
different output plugins and/or plugin options, w/o re-establishing the
connection, interactively by editing the command directly in psql prompt,
as opposed to using pg_recvlogical and editing the command line switches.

You can mostly use the sql functions for that.

Regards,

Andres

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jun 5, 2015 at 9:26 AM, Andres Freund <andres@anarazel.de> wrote:

On 2015-06-05 09:20:41 +0200, Shulgin, Oleksandr wrote:

Hello Hackers,

At the moment one can open up a replication connection using psql and use
all of the commands available on this special type of connection, except
for START_REPLICATION:

$ psql -d "dbname=test replication=database"
psql (9.4.1)
Type "help" for help.

test=# IDENTIFY_SYSTEM;
systemid | timeline | xlogpos | dbname
---------------------+----------+-----------+---------
6146106447402521313 | 1 | 0/76422B0 | bw_test
(1 row)

test=# START_REPLICATION SLOT test LOGICAL 0/0;
unexpected PQresultStatus: 8
test=#

This is due to PGRES_COPY_BOTH not being handled in psql. With
introduction of logical replication I think it makes sense to support

this

mode, for the sake of (semi-)interactive testing from psql.

How would that look like? It's just binary data you'd get back? And what
would be the feedback messages and such look like, and what would be the
content?

It would be whatever the logical decoding plugin sends. Makes perfect
sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal. And you
could even use binary with psql's \o, if you want (though usefulness is
less apparent to me in this case).

Since my intent is to just use it for testing the look of text logical
output, I'm not planning any support for feedback messages in this case.

For example, one could try replication from different wal positions,

different output plugins and/or plugin options, w/o re-establishing the
connection, interactively by editing the command directly in psql prompt,
as opposed to using pg_recvlogical and editing the command line switches.

You can mostly use the sql functions for that.

Maybe I'm missing something, which functions do you have in mind exactly?

--
Alex

On 2015-06-05 09:43:45 +0200, Shulgin, Oleksandr wrote:

How would that look like? It's just binary data you'd get back? And what
would be the feedback messages and such look like, and what would be the
content?

It would be whatever the logical decoding plugin sends. Makes perfect
sense for OUTPUT_PLUGIN_TEXTUAL_OUTPUT to see it on the terminal. And you
could even use binary with psql's \o, if you want (though usefulness is
less apparent to me in this case).

Hm. I severely doubt that that's a useful thing to do.

For example, one could try replication from different wal positions,

different output plugins and/or plugin options, w/o re-establishing the
connection, interactively by editing the command directly in psql prompt,
as opposed to using pg_recvlogical and editing the command line switches.

You can mostly use the sql functions for that.

Maybe I'm missing something, which functions do you have in mind exactly?

pg_logical_slot_get_changes() etc?

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Jun 5, 2015 at 10:22 AM, Andres Freund <andres@anarazel.de> wrote:

Maybe I'm missing something, which functions do you have in mind exactly?

pg_logical_slot_get_changes() etc?

Oh, totally forgot about these. However there are two significant
differences between using the functions and using START_REPLICATION command:

1. With get/peek_changes one cannot specify start_lsn. A parameter upto_lsn
is supported instead.
2. The functions return when either of the upto_* limits is reached or
there are no more data to decode, while with internal command it should
wait for more data until interrupted by user.

Anyway, using pg_recvlogical is perfectly fine by me, it's just psql can
pass the command, but is not ready to handle the request. Maybe just
having are more sensible error message for PGRES_COPY_BOTH is the way to go.

--
Alex