BUG #15061: Cannot use TEMP TABLE ON COMMIT DROP in extension
The following bug has been logged on the website:
Bug reference: 15061
Logged by: Christoph Berg
Email address: myon@debian.org
PostgreSQL version: 10.2
Operating system: Debian
Description:
For upgrading the data tables in my "unit" extension, I want to create
temporary tables to hold data before it is merged into the real tables.
Unfortunately it doesn't let me create the TEMP tables as ON COMMIT DROP:
$ cat tmptblbug.control
default_version = '1'
$ cat tmptblbug--1.sql
create temp table foo (id int) on commit drop;
$ cat Makefile
EXTENSION = tmptblbug
DATA = tmptblbug--1.sql
PG_CONFIG = pg_config
PGXS = $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
postgres=# create extension tmptblbug;
ERROR: cannot drop table foo because extension tmptblbug requires it
TIP: You can drop extension tmptblbug instead.
Real-world use case:
https://github.com/ChristophBerg/postgresql-unit/blob/master/unit--6.sql.in#L60-L83
Seen on 10.2 and git HEAD.
Workarounds:
1) Explicitly drop the table within the .control file
2) Don't use ON COMMIT DROP (but that leaks the temp tables to the invoking
session)
(Docs mention the possibility to create temp objects within the .control
file, so it's definitely meant to be supported.)
Thanks,
Christoph
On Mon, Feb 12, 2018 at 05:35:56PM +0000, PG Bug reporting form wrote:
Seen on 10.2 and git HEAD.
Workarounds:
1) Explicitly drop the table within the .control file
2) Don't use ON COMMIT DROP (but that leaks the temp tables to the invoking
session)
Even dropping the dependency link between the temporary relation and its
extension is tricky because the ON COMMIT DROP happens within a hook
at transaction commit after create_extension and CurrentExtensionObject
are reset. One dirty trick I can think of here is to directly delete
the dependency within pg_depend, say in your extension script:
create temp table aa (a int);
delete from pg_depend where objid = 'aa'::regclass and refclassid = 'pg_extension'::regclass;
This makes sure the extension is able to drop the object correctly.
Have you actually thought about using unlogged tables? Keeping around
relation definitions is not a big deal usually for upgrade scenarios,
and temporary tables generate WAL, so if you move your data with a
two-step process you never want to generate the same WAL data twice,
making the upgrade perform faster.
--
Michael
Re: Michael Paquier 2018-02-13 <20180213052732.GF2915@paquier.xyz>
1) Explicitly drop the table within the .control file
2) Don't use ON COMMIT DROP (but that leaks the temp tables to the invoking
session)One dirty trick I can think of here is to directly delete
the dependency within pg_depend, say in your extension script:
create temp table aa (a int);
delete from pg_depend where objid = 'aa'::regclass and refclassid = 'pg_extension'::regclass;
"drop table aa" is much easier and cleaner anyway.
Have you actually thought about using unlogged tables? Keeping around
relation definitions is not a big deal usually for upgrade scenarios,
and temporary tables generate WAL, so if you move your data with a
two-step process you never want to generate the same WAL data twice,
making the upgrade perform faster.
The table is only about 3000 rows, so speed is not a concern.
Christoph