This error occurs when attempting to create a PostgreSQL logical replication subscription within a transaction block. PostgreSQL requires CREATE SUBSCRIPTION to run in autocommit mode when creating replication slots.
This error is a PostgreSQL implementation restriction that prevents CREATE SUBSCRIPTION commands from running inside explicit transaction blocks (BEGIN...COMMIT). When you create a subscription with the default settings, PostgreSQL automatically creates a replication slot on the publisher database. This replication slot creation is a special operation that cannot be safely rolled back, which is why it must execute outside of a transaction block. This restriction ensures that the replication slot is either created successfully or not created at all, without the possibility of leaving the system in an inconsistent state if the transaction were to be rolled back. It's an important safeguard for maintaining data integrity in logical replication setups.
If you're in an interactive psql session or have an open transaction, commit or rollback first:
-- If you have an open transaction
COMMIT;
-- Or rollback if needed
ROLLBACK;This ensures you're starting from autocommit mode where CREATE SUBSCRIPTION can run successfully.
Execute the CREATE SUBSCRIPTION command directly without wrapping it in a transaction:
-- This should run successfully outside a transaction
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_host dbname=mydb user=replica_user password=secret'
PUBLICATION my_publication;In psql, autocommit is enabled by default, so this will work as-is. For programmatic access, ensure your database client has autocommit enabled.
For scenarios where you need more control or must work within transaction constraints, create the replication slot manually first:
On the publisher database:
-- Create the replication slot manually
SELECT pg_create_logical_replication_slot('my_subscription', 'pgoutput');On the subscriber database:
-- Now create the subscription without creating a slot
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_host dbname=mydb user=replica_user password=secret'
PUBLICATION my_publication
WITH (create_slot = false, slot_name = 'my_subscription');This two-step approach works around the transaction restriction by separating slot creation from subscription creation.
If using database migration tools, configure them to run CREATE SUBSCRIPTION separately:
For Flyway migrations:
-- Add at the top of your migration file
-- flyway:executeInTransaction=false
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_host dbname=mydb'
PUBLICATION my_publication;For Liquibase:
<changeSet id="create-subscription" author="you" runInTransaction="false">
<sql>
CREATE SUBSCRIPTION my_subscription
CONNECTION 'host=publisher_host dbname=mydb'
PUBLICATION my_publication;
</sql>
</changeSet>For custom scripts:
Execute subscription commands in separate database connections or ensure autocommit is enabled before running them.
After successfully creating the subscription, verify it's working:
-- Check subscription status
SELECT * FROM pg_subscription WHERE subname = 'my_subscription';
-- Check replication slot on publisher
SELECT * FROM pg_replication_slots WHERE slot_name = 'my_subscription';
-- Monitor subscription state
SELECT * FROM pg_stat_subscription WHERE subname = 'my_subscription';The subscription should show as active and the replication slot should be visible on the publisher.
Transaction Isolation and Replication:
The CREATE SUBSCRIPTION transaction restriction exists because replication slots are WAL-based resources that persist beyond transaction boundaries. Creating a slot allocates WAL resources and begins tracking the logical replication position. Rolling back such an operation would create inconsistencies in the replication system.
Same-Database Replication Caveat:
When creating a subscription that connects to the same database cluster (replicating between databases on the same PostgreSQL instance), the CREATE SUBSCRIPTION command will hang if it tries to create the replication slot in the same command. This is because the command would create a deadlock situation. Always use the two-step approach (create slot separately with create_slot = false) for same-cluster replication.
Programmatic Subscription Management:
When creating subscriptions programmatically, ensure your database driver's autocommit is enabled:
- Python (psycopg2/psycopg3): Set autocommit = True on the connection
- Node.js (pg): Connection is in autocommit by default unless you call BEGIN
- Java (JDBC): Call connection.setAutoCommit(true) before executing
- Go (pgx): Use Exec() directly without beginning a transaction
Future PostgreSQL Versions:
The PostgreSQL documentation notes this is "an implementation restriction that might be lifted in a future release." However, as of PostgreSQL 18 (2025), the restriction remains in place. Always design replication setup scripts with this limitation in mind.
DROP SUBSCRIPTION Considerations:
While DROP SUBSCRIPTION can also face similar restrictions, it has additional options. Using DROP SUBSCRIPTION name (slot_name = NONE) allows you to drop the subscription metadata without affecting the replication slot, which can then be dropped separately.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL