In order to make your issue reports as actionable as possible, please provide the following information, depending on the issue type.

Bug report

Further testing the fix for DBZ-6092 I found that it does not really work since ignoring the transactions with pid not equal to the current connection pid is not enough. The reason for that I suspect is autoCommit = false for "Debezium General" connection since pg_stat_activity shows it as "idle in transaction" while loading the information about the database schema + the fact that PostgresReplicationConnection tries to re-init the replication slot when it starts streaming and uses a newly created connection for that. The result of this is that we can not fall back to restart_lsn since we are checking for any entries with 'idle in transaction' in pg_stat_activity.

What Debezium connector do you use and what version?

Postgres connector version 1.9.5

What is the connector configuration?

<Your answer>

What is the captured database version and mode of depoyment?

(E.g. on-premises, with a specific cloud provider, etc.)

Postgres 12

What behaviour do you expect?

<Your answer>

What behaviour do you see?

Debezium tries to fetch the replication slots from Postgres and filter out the slots that do not have confirmed_flush_lsn set initially retrying and waiting for potential long-running transactions to be committed as described in DBZ-862 and then fails with an exception when the amount of retries is exhausted. After that the retrying repeats.

Do you see the same behaviour using the latest relesead Debezium version?

(Ideally, also verify with latest Alpha/Beta/CR version)

<Your answer>

Do you have the connector logs, ideally from start till finish?

(You might be asked later to provide DEBUG/TRACE level log)

<Your answer>

How to reproduce the issue using our tutorial deployment?

<Your answer>

Feature request or enhancement

For feature requests or enhancements, provide this information, please:

Which use case/requirement will be addressed by the proposed feature?

<Your answer>

Implementation ideas (optional)

A naive suggestion would be to ignore at least 'Debezium General' connection(or maybe all Debezium connections?) entries in pg_stat_activity when it's idle in transaction by simply modifying the query adding "' and application_name != 'Debezium General'".