Re: database design with temporary tables

On 8/29/21 2:26 PM, Adrian Klaver wrote:
> The pool is maintained, the individual connections(sessions) come and
> go. Otherwise there would be no point to having a pool. Every time the
> connection(session) is closed the temporary table disappears.

Yes, you're right. And that is very convenient. I also prefer temporary
tables with ON COMMIT DELETE ROWS so that I don't have leftovers between
transactions. There is also pgtt extension by Giles Darold which
emulates Oracle global temporary tables:

https://github.com/darold/pgtt#use-of-the-extension

I have never particularly liked Oracle's implementation of temporary
tables, local or global,  so I have no problems with questions like
that. BTW, I have compared Oracle's implementation with other
implementations here:

https://dbwhisperer.wordpress.com/2019/03/18/private-temporary-tables-oracle-18c/

I could have used Postgres instead of the SQL Server, the comparison
would read the same, except for the "#" character which denotes
temporary table in SQL Server.

Regards

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com