I'm using the EXPORT_SNAPSHOT
option documented here when creating a postgres logical replication slot. The documentation states that 'export, which is the default, will export the snapshot for use in other sessions. This option can't be used inside a transaction...'
However, when I try to use the snapshot exported here from another transaction with the SET TRANSACTION SNAPSHOT
command I keep getting the invalid snapshot identifier
error.
Isn't it contradictory that there's an export snapshot option which explicitly states that it shouldn't be used inside a transaction(thus we cannot control when it commits) but we cannot use the exported snapshot in another transaction(precisely because we cannot control when the create_replication_slot commits and that snapshot is probably already purged by the time we try to use it)?
ps: What I want to achieve is to use the exported snapshot to copy table data prior to creating a replication slot before actually starting the logical replication.
So here is what I had made working, just FYI:
create_replication_slot
command cannot be inside a transaction. The exported snapshot has "repeatable read" isolation level, seems no way to change. Hold the connection without executing any other command.SET TRANSACTION SNAPSHOT <snapshot_name>
, which you got from the above create_replication_slot
command.