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.
>
> 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