test_factory_pgtap extension fails to install on PostgreSQL 17: ON COMMIT DROP conflicts with extension ownership

[jnasbyupgrade]

Bug Description

CREATE EXTENSION test_factory_pgtap always fails on PostgreSQL 17 (and likely other versions) due to a conflict between ON COMMIT DROP and PostgreSQL's extension ownership tracking.

Root Cause

test_factory_pgtap--0.1.0.sql begins with:

CREATE TEMP TABLE original_role ON COMMIT DROP AS SELECT current_user AS original_role;

When CREATE EXTENSION runs this SQL, PostgreSQL records the temp table as extension-owned. At commit time, ON COMMIT DROP fires and attempts to drop the table — but PostgreSQL refuses because the extension owns it:

ERROR:  cannot drop table original_role because extension test_factory_pgtap requires it
HINT:  You can drop extension test_factory_pgtap instead.

This causes CREATE EXTENSION test_factory_pgtap to always fail (the transaction is rolled back), making the extension completely uninstallable.

Steps to Reproduce

CREATE EXTENSION test_factory_pgtap CASCADE;
-- ERROR:  cannot drop table original_role because extension test_factory_pgtap requires it
-- HINT:  You can drop extension test_factory_pgtap instead.

Expected Behavior

Extension installs successfully.

Affected Versions

• PostgreSQL 17 (confirmed)
• Likely all PostgreSQL versions that enforce extension ownership on objects created during CREATE EXTENSION

Suggested Fix

Remove ON COMMIT DROP from the temp table creation:

-- Before (broken):
CREATE TEMP TABLE original_role ON COMMIT DROP AS SELECT current_user AS original_role;

-- After (fixed):
CREATE TEMP TABLE original_role AS SELECT current_user AS original_role;

The table is only used during extension installation to restore the original role at the end of the script. It will be cleaned up automatically at session end (which is fine for a temp table used only during installation).

Additionally, the explicit DROP TABLE pg_temp.original_role; at line 39 of the script should be kept or also removed — it currently runs before the ON COMMIT DROP would fire, so it was the intended cleanup mechanism. With ON COMMIT DROP removed, the explicit DROP TABLE at the end of the script remains the correct approach and can stay as-is.

File Reference

sql/test_factory_pgtap--0.1.0.sql, lines 2 and 39.