logical replication resiliency

[Istvan Soos]

I have a database with about 1 TB of data, mostly inserts, little
updates and deletes, and I want to setup 2 logical replication for it.
What should be my expectation with the replication?

1. What is the time-window that either of the nodes can be offline?
- Will it survive if the node doing the publishing is offline for a
few hours and then goes back online?
- Will it survive if the subscriber node is offline for a few hours
and then goes back online?

2. If the replication is aborted for whatever reason, is there a way
to do it incrementally, or will it re-sync the 1TB at every such
event?

3. What alternative tools are out there to e.g. periodically sync from
one db to another?

Thanks,
  Istvan

How to cleanup transaction after statement_timeout aborts a query?

[Istvan Soos]

I'm one of the developers of the Dart-language Postgresql client
package. I am working on adding a feature that may set the
statement_timeout value before a session or a query as the client
requests it, however, I'm stuck with the following error:

setup:
CREATE TABLE t (id INT PRIMARY KEY);
INSERT INTO t (id) values (1);

client-1:
BEGIN;
SELECT * FROM t WHERE id=1 FOR UPDATE;


client-2:
BEGIN;
SET statement_timeout TO 1000;
SELECT * FROM t WHERE id=1 FOR UPDATE;


After that any query I send through client-2 will get me the following error:

Severity.error 25P02: current transaction is aborted, commands ignored
until end of transaction block

Not even ROLLBACK or COMMIT is working. It is the same for both simple
and extended query protocol. Does the client need to send a non-query
message to cleanup the transaction state? Or is this connection now
gone for good?

Thanks,
  Istvan

Re: How to cleanup transaction after statement_timeout aborts a query?

[Istvan Soos]

On Sun, Sep 8, 2024 at 1:19 PM Laurenz Albe  wrote:
> ROLLBACK and COMMIT are working: they end the transaction.

I have this reproduction test, and ROLLBACK does fail:
https://github.com/isoos/postgresql-dart/pull/363/files#diff-4547e49b04ec8280fb8f4f1ebf695b77f9a2d9a4ac9bcfd685bcd570a46baa80R122

I've checked and nothing else is sent on the protocol, yet, for the
rollback statement it gets the 25P02 error.

> It is the atomicity guarantee of database transactions: either all statements
> succeed, or all fail.

Yeah, I thought so, that's why I'm struggling to see what's missing.

> To handle the failure of a statement while allowing the transaction to 
> proceed,
> you can use savepoints.  But be warned: don't even think of setting a 
> savepoint
> before each statement.  That would affect statement performance severely.

As the writer of the client library, I don't have the luxury of
telling users they need to change their way, hence I'm looking for any
pointer on the protocol level.

Thanks,
  Istvan

Re: How to cleanup transaction after statement_timeout aborts a query?

[Istvan Soos]

On Sun, Sep 8, 2024 at 3:41 PM Laurenz Albe  wrote:
> You must be doing something wrong then, because it works:

And you are right, I was doing something wrong:

The combination of the change and the library internals triggered a
SET statement_timeout TO 3000; before the ROLLBACK was sent, and the
error message I got was not clear on which statement triggered it.

I'm sorry for the noise.

Btw. Is there any guideline on how to write the client libraries or
recommendations/tests that a client library should follow?

Thanks,
  Istvan

Re: A table lock inside a transaction depends on query protocol being used?

[Istvan Soos]

On Wed, Nov 20, 2024 at 7:57 PM Tom Lane  wrote:
> but I wonder if you could be failing to close out the SELECT statement before 
> issuing ALTER.

Thanks! This led me to check some debug details, and in fact we don't
close the portal, only the statement. (Besides a bug in the debug
log...)

Now I know what to fix :).

Thanks,
  Istvan

A table lock inside a transaction depends on query protocol being used?

[Istvan Soos]

Hi,

I'm developing the Dart client for Postgresql, which is using the
extended query protocol by default. I have received a report which
shows a difference in locking behavior inside a transaction, depending
which protocol we are using.

My main question: is this something that is implemented in a wrong way
in the Dart client? Or is it something that is intrinsic to Postgresql
server? How should the user who found this go ahead?

The following minimal reproduction case can be used locally:

Setup:
CREATE TABLE a (
  a_id INTEGER PRIMARY KEY NOT NULL,
  a_other_id INTEGER NOT NULL
);
CREATE TABLE b (other_id INTEGER PRIMARY KEY NOT NULL);

BEGIN;
SELECT * FROM a;
ALTER TABLE a ADD CONSTRAINT fk_other FOREIGN KEY (a_other_id)
REFERENCES b(other_id);

At which point we get '55006: cannot ALTER TABLE "a" because it is
being used by active queries in this session'. It makes sense,
however, if we change the SELECT to simple query protocol, the error
is not present and the transaction completes.

Internal inside the Dart client, this is the rough message flow debug
for the extended protocol:

[292da4d4][out] Aggregated [Parse SELECT * FROM a;, Instance of 'SyncMessage']
[292da4d4][in] Parse Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'BindMessage', Instance of
'DescribeMessage', Instance of 'ExecuteMessage', Instance of
'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] Instance of 'RowDescriptionMessage'
[292da4d4][in] CommandCompleteMessage(0 affected rows)
[292da4d4][in] ReadyForQueryMessage(state = T)
[292da4d4][out] Aggregated [Instance of 'CloseMessage', Instance of
'SyncMessage']
[out] Aggregated [Instance of 'CloseMessage', Instance of 'SyncMessage']
[292da4d4][in] Bind Complete Message
[292da4d4][in] ReadyForQueryMessage(state = T)

And for the simple protocol:

[3f02e699][out] Query: SELECT * FROM a;
[3f02e699][in] Instance of 'RowDescriptionMessage'
[3f02e699][in] CommandCompleteMessage(0 affected rows)
[3f02e699][in] ReadyForQueryMessage(state = T)

Thank you,
  Istvan