Repository navigation
CREATE EXTENSION test_factory_pgtap fails inside BEGIN/COMMIT block #13
Description
Problem
Installing test_factory_pgtap inside a BEGIN/COMMIT transaction block causes COMMIT to fail. This breaks any install script that wraps multiple CREATE EXTENSION calls in a single transaction.
Root Cause
test_factory_pgtap creates a TEMP TABLE ... ON COMMIT DROP during extension installation. When PostgreSQL processes COMMIT, it auto-drops that temp table. However, the table is registered as owned by the extension, so PostgreSQL cannot drop it as part of the commit without removing the extension — causing the commit to fail with an error.
Minimal Reproduction
BEGIN;
CREATE EXTENSION test_factory_pgtap CASCADE;
COMMIT;
-- ERROR: cannot drop table <name> because extension test_factory_pgtap requires it
-- (or similar error preventing the commit)Working workaround:
-- Install BEFORE the transaction block
CREATE EXTENSION test_factory_pgtap CASCADE;
BEGIN;
-- ... rest of install script ...
COMMIT;Expected Behavior
CREATE EXTENSION test_factory_pgtap should be safe to run inside a transaction block, like any standard extension.
Fix
The extension should explicitly DROP TABLE the temp table at the end of installation rather than relying on ON COMMIT DROP. This removes the transaction-scoped lifetime dependency, making the extension safe to install inside a BEGIN/COMMIT block.
Activity
jnasbyupgrade commentedon Jul 14, 2026
Resolving as part of the test_factory pgxntool-sync +
test/install/+ fix work.Root cause is the install-time
CREATE TEMP TABLE original_role ON COMMIT DROPused to save the caller's role — an extension-owned temp object, which is inherently fragile (also causes a name-collision failure if the caller already has anoriginal_roletemp table). The specific commit-time failure reported here has been masked since commit 0a51bf7 (2025-06, predates these issues) by an explicitDROP TABLEbefore commit, so it no longer reproduces on current code.The proper fix removes the temp table entirely: save the caller's role via
set_config('test_factory.original_role', current_user, true)and restore it withSET ROLEat end of script — no extension-owned temp object, noON COMMIT DROP, no collision. This is being done on top of the pgxntool sync (#19), switching the install test to pgxntool's committedtest/install/path. Closing per maintainer direction.Squashed 'pgxntool/' changes from bed3604..eb84bc6
Squashed 'pgxntool/' changes from bed3604..eb84bc6
Drop the original_role temp table from install (#12/#13) (#20)
Squashed 'pgxntool/' changes from bed3604..b67064b
Squashed 'pgxntool/' changes from bed3604..e80485e