Обсуждение: ON COMMIT options for non temporary tables
Hi
For my work with Postgres 11.5, I needed functionality that unlogged tables are automatically dropped at the commit time, but I found that ON COMMIT option is only supported with temporary table.
I would like to understand reasons why this option is limited to temporary tables? Is there any problem by making this option available for other type of tables such as unlogged tables?
--
Thanks
Prafulla
On Thu, 2019-11-21 at 21:23 +0530, P V Tekawade wrote: > For my work with Postgres 11.5, I needed functionality that unlogged tables > are automatically dropped at the commit time, but I found that ON COMMIT > option is only supported with temporary table. > I would like to understand reasons why this option is limited to temporary tables? > Is there any problem by making this option available for other type of tables > such as unlogged tables? How should that work for tables other than temporary tables? Should COMMIT lock if somebody else accesses the table? What keeps you from explicitly dropping the table right before you commit? Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Thu, Nov 21, 2019 at 05:13:31PM +0100, Laurenz Albe wrote: > How should that work for tables other than temporary tables? > Should COMMIT lock if somebody else accesses the table? Postgres does not support read uncommitted, so the table would not be visible to other sessions until the transaction that created it is committed. Anyway, the goal is to use the table just within a transaction without having it produce WAL, right? > What keeps you from explicitly dropping the table right before > you commit? Yeah, that would be the most simple. Another idea I can think of here is to use a combination of event trigger and deferred triggered on a dummy constraint: register the table creation using the event trigger on CREATE TABLE, and then drop it using the deferred trigger. Using a DROP TABLE before the commit or once your transaction is done with this table is much more simple of course :) -- Michael