Apologies for the re-post. I noted scripts in the original post had their line feeds messed up. Below are the fixed ones,
Script 1,
ROWS=100000
function test() {
SQL=$1
echo $SQL
createdb test
psql test -qc "CREATE TABLE t(x) AS SELECT generate_series(1,$ROWS); CREATE INDEX ON t(x);"
psql test -c "SELECT pg_create_logical_replication_slot('test_slot', 'test_decoding')" > /dev/null
echo "BEGIN; $(seq -f "$SQL" $ROWS) COMMIT;" | psql test -q
time $(psql test -c "SELECT COUNT(*) FROM pg_logical_slot_get_changes('test_slot', NULL, NULL)" > /dev/null)
echo
psql test -c "SELECT pg_drop_replication_slot('test_slot')" > /dev/null
dropdb test
}
UPDATE="UPDATE t SET x=x-1 WHERE x=%0.f"
test "$UPDATE;"
test "SAVEPOINT s; $UPDATE; RELEASE SAVEPOINT s;"
Script 2,
CREATE_TABLES=`seq -f "CREATE TABLE t_%.0f (id int);" 10000`
# Create tables in separate transactions; pg_logical_slot_get_changes() takes 0.3s
createdb test1
psql test1 -c "SELECT pg_create_logical_replication_slot('test_slot_1', 'test_decoding')"
time echo $CREATE_TABLES | psql test1 -q
time psql test1 -c "SELECT COUNT(*) FROM pg_logical_slot_get_changes('test_slot_1', NULL, NULL)"
# Create tables in one transaction; pg_logical_slot_get_changes() takes 210s
createdb test2
psql test2 -c "SELECT pg_create_logical_replication_slot('test_slot_2', 'test_decoding')"
time echo "BEGIN; $CREATE_TABLES COMMIT;" | psql test2 -q
time psql test2 -c "SELECT COUNT(*) FROM pg_logical_slot_get_changes('test_slot_2', NULL, NULL)"
# Clean up
psql test1 -c "select pg_drop_replication_slot('test_slot_1');"
dropdb test1
psql test2 -c "select pg_drop_replication_slot('test_slot_2');"
dropdb test2
