Re: ON COMMIT DROP unstable behaviour

On Wed, Oct 18, 2017 at 1:12 PM, Ian R. Campbell <
ian(dot)campbell(at)thepathcentral(dot)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.​