Thread: 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
Attachment
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