Code review for ON COMMIT patch. Make the actual on-commit action happen

before commit, not after :-( --- the original coding is not only unsafe
if an error occurs while it's processing, but it generates an invalid
sequence of WAL entries.  Resurrect 7.2 logic for deleting items when
no longer needed.  Use an enum instead of random macros.  Editorialize
on names used for routines and constants.  Teach backend/nodes routines
about new field in CreateTable struct.  Add a regression test.

2 files changed, 97 insertions, 14 deletions

diff --git a/src/test/regress/expected/temp.out b/src/test/regress/expected/temp.out
index 1800b9ebe56..75d758d36e3 100644
--- a/src/test/regress/expected/temp.out
+++ b/src/test/regress/expected/temp.out
@@ -5,21 +5,31 @@
 -- test temp table/index masking
 CREATE TABLE temptest(col int);
 CREATE INDEX i_temptest ON temptest(col);
-CREATE TEMP TABLE temptest(col int);
-CREATE INDEX i_temptest ON temptest(col);
+CREATE TEMP TABLE temptest(tcol int);
+CREATE INDEX i_temptest ON temptest(tcol);
+SELECT * FROM temptest;
+ tcol 
+------
+(0 rows)
+
 DROP INDEX i_temptest;
 DROP TABLE temptest;
+SELECT * FROM temptest;
+ col 
+-----
+(0 rows)
+
 DROP INDEX i_temptest;
 DROP TABLE temptest;
 -- test temp table selects
 CREATE TABLE temptest(col int);
 INSERT INTO temptest VALUES (1);
-CREATE TEMP TABLE temptest(col int);
-INSERT INTO temptest VALUES (2);
+CREATE TEMP TABLE temptest(tcol float);
+INSERT INTO temptest VALUES (2.1);
 SELECT * FROM temptest;
- col 
------
-   2
+ tcol 
+------
+  2.1
 (1 row)
 
 DROP TABLE temptest;
@@ -30,9 +40,46 @@ SELECT * FROM temptest;
 (1 row)
 
 DROP TABLE temptest;
-CREATE TEMP TABLE temptest(col int);
 -- test temp table deletion
+CREATE TEMP TABLE temptest(col int);
 \c regression
 SET autocommit TO 'on';
 SELECT * FROM temptest;
 ERROR:  Relation "temptest" does not exist
+-- Test ON COMMIT DELETE ROWS
+CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
+BEGIN;
+INSERT INTO temptest VALUES (1);
+INSERT INTO temptest VALUES (2);
+SELECT * FROM temptest;
+ col 
+-----
+   1
+   2
+(2 rows)
+
+COMMIT;
+SELECT * FROM temptest;
+ col 
+-----
+(0 rows)
+
+DROP TABLE temptest;
+-- Test ON COMMIT DROP
+BEGIN;
+CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
+INSERT INTO temptest VALUES (1);
+INSERT INTO temptest VALUES (2);
+SELECT * FROM temptest;
+ col 
+-----
+   1
+   2
+(2 rows)
+
+COMMIT;
+SELECT * FROM temptest;
+ERROR:  Relation "temptest" does not exist
+-- ON COMMIT is only allowed for TEMP
+CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;
+ERROR:  ON COMMIT can only be used on TEMP tables
diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql
index 6f45b2eaac7..5ebd13c83cc 100644
--- a/src/test/regress/sql/temp.sql
+++ b/src/test/regress/sql/temp.sql
@@ -9,14 +9,18 @@ CREATE TABLE temptest(col int);
 
 CREATE INDEX i_temptest ON temptest(col);
 
-CREATE TEMP TABLE temptest(col int);
+CREATE TEMP TABLE temptest(tcol int);
 
-CREATE INDEX i_temptest ON temptest(col);
+CREATE INDEX i_temptest ON temptest(tcol);
+
+SELECT * FROM temptest;
 
 DROP INDEX i_temptest;
 
 DROP TABLE temptest;
 
+SELECT * FROM temptest;
+
 DROP INDEX i_temptest;
 
 DROP TABLE temptest;
@@ -27,9 +31,9 @@ CREATE TABLE temptest(col int);
 
 INSERT INTO temptest VALUES (1);
 
-CREATE TEMP TABLE temptest(col int);
+CREATE TEMP TABLE temptest(tcol float);
 
-INSERT INTO temptest VALUES (2);
+INSERT INTO temptest VALUES (2.1);
 
 SELECT * FROM temptest;
 
@@ -39,12 +43,44 @@ SELECT * FROM temptest;
 
 DROP TABLE temptest;
 
-CREATE TEMP TABLE temptest(col int);
-
 -- test temp table deletion
 
+CREATE TEMP TABLE temptest(col int);
+
 \c regression
 SET autocommit TO 'on';
 
 SELECT * FROM temptest;
 
+-- Test ON COMMIT DELETE ROWS
+
+CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS;
+
+BEGIN;
+INSERT INTO temptest VALUES (1);
+INSERT INTO temptest VALUES (2);
+
+SELECT * FROM temptest;
+COMMIT;
+
+SELECT * FROM temptest;
+
+DROP TABLE temptest;
+
+-- Test ON COMMIT DROP
+
+BEGIN;
+
+CREATE TEMP TABLE temptest(col int) ON COMMIT DROP;
+
+INSERT INTO temptest VALUES (1);
+INSERT INTO temptest VALUES (2);
+
+SELECT * FROM temptest;
+COMMIT;
+
+SELECT * FROM temptest;
+
+-- ON COMMIT is only allowed for TEMP
+
+CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS;