How to create logical replication slot with NOEXPORT_SNAPSHOT in jdbc

[Igor Polishchuk <ora4dba@xxxxxxxxx>]

Hello Everybody.

We are trying to use logical decoding for detecting database changes.

However, when we create a replication slot, the data processing pauses if there are still transactions running from before the slot creation. If I understand correctly, the slot is waiting for creating a consistent snapshot and is blocked by the long transactions.

In our application, we don't need it, as we only want to see if some tables were modified. Is it possible to create a  logical replication slot with  NOEXPORT_SNAPSHOT option using jdbc?

This is a feature of  feature of the Streaming Replication Protocol described in https://www.postgresql.org/docs/current/static/logicaldecoding-explanation.html#id-1.8.14.8.5

It is just not clear how to pass this option. We are using Postgres 10.4, jbc

changelog for 42.2.2

This is a groovy method for creating the replication slot:

    @CompileStatic(TypeCheckingMode.SKIP)
    private void createReplicationSlot( PGConnection replConnection) {

        def slotName = getSlotName()
        Sql sql = new Sql(dataSource)

        def exists = sql.firstRow("select * from pg_replication_slots where slot_name = '${slotName}'".toString())

        if (exists)
            log.info "replication slot ${slotName} detected and ${exists?.active ? 'active' : 'not active'}"
        else
            log.info "replication slot ${slotName} not detected"

        if (!exists) {
            replConnection.getReplicationAPI()
                    .createReplicationSlot()
                    .logical()
                    .withSlotName(slotName)
                    .withOutputPlugin("wal2json")
                    .make();

            log.info "created replication slot ${slotName}"
        }

--

Thanks

Igor Polishchuk