ON COMMIT DROP unstable behaviour

On Wed, Oct 18, 2017 at 1:12 PM, Ian R. Campbell <
ian.campbell@thepathcentral.com> wrote:

Hi

I'm using PG 10, pgAdmin 4, and PGMaestro 17.8.0.1.

I have a function that creates a temp table marked ON COMMIT DROP. The
function performs read-only ops on the DB apart from using the temp table.

I observed the temp table sometimes fails to drop when executing the
function. I am able to query its contents. Multiple calls to the function
simply increases the temp table's contents.

I narrowed it down to the following scenario:

1) Remove the ON COMMIT DROP
2) Compile
3) Execute "SELECT * FROM func(1)"... temp table visible as expected
4) Put back the ON COMMIT DROP
5) Compile
6) Execute... temp table visible and grows with each call to the function

*Dropping all connections to the DB, reconnecting, and executing the
function (without modification) will result in expected behavior: temp
table drops.*

​Given that this is unlikely to be a PostgreSQL bug I would say providing a
definitive answer will require you to provide a self-contained test case.
I would suggest you forgo the whole "compile" thing, though, as its
probably complicating things - and a realistic use case for temporary
tables does not involve "removing and putting back" ON COMMIT DROP.

The conclusion/return of a function does not cause a "Commit" to happen.
Either auto-commit is in effect (and so when the statement ends so does the
transaction) or you must manually issue a "commit". "Dropping all
connections to the db" serves the same purpose.

David J.​