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)
postgres=# create extension tmptblbug;
ERROR: cannot drop table foo because extension tmptblbug requires it
TIP: You can drop extension tmptblbug instead.
Re: BUG #15061: Cannot use TEMP TABLE ON COMMIT DROP in extension
On Mon, Feb 12, 2018 at 05:35:56PM +0000, PG Bug reporting form wrote:
> Seen on 10.2 and git HEAD.
> 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
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.