1-- Test basic TRUNCATE functionality. 2CREATE TABLE truncate_a (col1 integer primary key); 3INSERT INTO truncate_a VALUES (1); 4INSERT INTO truncate_a VALUES (2); 5SELECT * FROM truncate_a; 6-- Roll truncate back 7BEGIN; 8TRUNCATE truncate_a; 9ROLLBACK; 10SELECT * FROM truncate_a; 11-- Commit the truncate this time 12BEGIN; 13TRUNCATE truncate_a; 14COMMIT; 15SELECT * FROM truncate_a; 16 17-- Test foreign-key checks 18CREATE TABLE trunc_b (a int REFERENCES truncate_a); 19CREATE TABLE trunc_c (a serial PRIMARY KEY); 20CREATE TABLE trunc_d (a int REFERENCES trunc_c); 21CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c); 22 23TRUNCATE TABLE truncate_a; -- fail 24TRUNCATE TABLE truncate_a,trunc_b; -- fail 25TRUNCATE TABLE truncate_a,trunc_b,trunc_e; -- ok 26TRUNCATE TABLE truncate_a,trunc_e; -- fail 27TRUNCATE TABLE trunc_c; -- fail 28TRUNCATE TABLE trunc_c,trunc_d; -- fail 29TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok 30TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a; -- fail 31TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b; -- ok 32 33TRUNCATE TABLE truncate_a RESTRICT; -- fail 34TRUNCATE TABLE truncate_a CASCADE; -- ok 35 36-- circular references 37ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c; 38 39-- Add some data to verify that truncating actually works ... 40INSERT INTO trunc_c VALUES (1); 41INSERT INTO truncate_a VALUES (1); 42INSERT INTO trunc_b VALUES (1); 43INSERT INTO trunc_d VALUES (1); 44INSERT INTO trunc_e VALUES (1,1); 45TRUNCATE TABLE trunc_c; 46TRUNCATE TABLE trunc_c,truncate_a; 47TRUNCATE TABLE trunc_c,truncate_a,trunc_d; 48TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e; 49TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b; 50 51-- Verify that truncating did actually work 52SELECT * FROM truncate_a 53 UNION ALL 54 SELECT * FROM trunc_c 55 UNION ALL 56 SELECT * FROM trunc_b 57 UNION ALL 58 SELECT * FROM trunc_d; 59SELECT * FROM trunc_e; 60 61-- Add data again to test TRUNCATE ... CASCADE 62INSERT INTO trunc_c VALUES (1); 63INSERT INTO truncate_a VALUES (1); 64INSERT INTO trunc_b VALUES (1); 65INSERT INTO trunc_d VALUES (1); 66INSERT INTO trunc_e VALUES (1,1); 67 68TRUNCATE TABLE trunc_c CASCADE; -- ok 69 70SELECT * FROM truncate_a 71 UNION ALL 72 SELECT * FROM trunc_c 73 UNION ALL 74 SELECT * FROM trunc_b 75 UNION ALL 76 SELECT * FROM trunc_d; 77SELECT * FROM trunc_e; 78 79DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE; 80 81-- Test TRUNCATE with inheritance 82 83CREATE TABLE trunc_f (col1 integer primary key); 84INSERT INTO trunc_f VALUES (1); 85INSERT INTO trunc_f VALUES (2); 86 87CREATE TABLE trunc_fa (col2a text) INHERITS (trunc_f); 88INSERT INTO trunc_fa VALUES (3, 'three'); 89 90CREATE TABLE trunc_fb (col2b int) INHERITS (trunc_f); 91INSERT INTO trunc_fb VALUES (4, 444); 92 93CREATE TABLE trunc_faa (col3 text) INHERITS (trunc_fa); 94INSERT INTO trunc_faa VALUES (5, 'five', 'FIVE'); 95 96BEGIN; 97SELECT * FROM trunc_f; 98TRUNCATE trunc_f; 99SELECT * FROM trunc_f; 100ROLLBACK; 101 102BEGIN; 103SELECT * FROM trunc_f; 104TRUNCATE ONLY trunc_f; 105SELECT * FROM trunc_f; 106ROLLBACK; 107 108BEGIN; 109SELECT * FROM trunc_f; 110SELECT * FROM trunc_fa; 111SELECT * FROM trunc_faa; 112TRUNCATE ONLY trunc_fb, ONLY trunc_fa; 113SELECT * FROM trunc_f; 114SELECT * FROM trunc_fa; 115SELECT * FROM trunc_faa; 116ROLLBACK; 117 118BEGIN; 119SELECT * FROM trunc_f; 120SELECT * FROM trunc_fa; 121SELECT * FROM trunc_faa; 122TRUNCATE ONLY trunc_fb, trunc_fa; 123SELECT * FROM trunc_f; 124SELECT * FROM trunc_fa; 125SELECT * FROM trunc_faa; 126ROLLBACK; 127 128DROP TABLE trunc_f CASCADE; 129 130-- Test ON TRUNCATE triggers 131 132CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text); 133CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text, 134 tgargv text, tgtable name, rowcount bigint); 135 136CREATE FUNCTION trunctrigger() RETURNS trigger as $$ 137declare c bigint; 138begin 139 execute 'select count(*) from ' || quote_ident(tg_table_name) into c; 140 insert into trunc_trigger_log values 141 (TG_OP, TG_LEVEL, TG_WHEN, TG_ARGV[0], tg_table_name, c); 142 return null; 143end; 144$$ LANGUAGE plpgsql; 145 146-- basic before trigger 147INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux'); 148 149CREATE TRIGGER t 150BEFORE TRUNCATE ON trunc_trigger_test 151FOR EACH STATEMENT 152EXECUTE PROCEDURE trunctrigger('before trigger truncate'); 153 154SELECT count(*) as "Row count in test table" FROM trunc_trigger_test; 155SELECT * FROM trunc_trigger_log; 156TRUNCATE trunc_trigger_test; 157SELECT count(*) as "Row count in test table" FROM trunc_trigger_test; 158SELECT * FROM trunc_trigger_log; 159 160DROP TRIGGER t ON trunc_trigger_test; 161 162truncate trunc_trigger_log; 163 164-- same test with an after trigger 165INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux'); 166 167CREATE TRIGGER tt 168AFTER TRUNCATE ON trunc_trigger_test 169FOR EACH STATEMENT 170EXECUTE PROCEDURE trunctrigger('after trigger truncate'); 171 172SELECT count(*) as "Row count in test table" FROM trunc_trigger_test; 173SELECT * FROM trunc_trigger_log; 174TRUNCATE trunc_trigger_test; 175SELECT count(*) as "Row count in test table" FROM trunc_trigger_test; 176SELECT * FROM trunc_trigger_log; 177 178DROP TABLE trunc_trigger_test; 179DROP TABLE trunc_trigger_log; 180 181DROP FUNCTION trunctrigger(); 182 183-- test TRUNCATE ... RESTART IDENTITY 184CREATE SEQUENCE truncate_a_id1 START WITH 33; 185CREATE TABLE truncate_a (id serial, 186 id1 integer default nextval('truncate_a_id1')); 187ALTER SEQUENCE truncate_a_id1 OWNED BY truncate_a.id1; 188 189INSERT INTO truncate_a DEFAULT VALUES; 190INSERT INTO truncate_a DEFAULT VALUES; 191SELECT * FROM truncate_a; 192 193TRUNCATE truncate_a; 194 195INSERT INTO truncate_a DEFAULT VALUES; 196INSERT INTO truncate_a DEFAULT VALUES; 197SELECT * FROM truncate_a; 198 199TRUNCATE truncate_a RESTART IDENTITY; 200 201INSERT INTO truncate_a DEFAULT VALUES; 202INSERT INTO truncate_a DEFAULT VALUES; 203SELECT * FROM truncate_a; 204 205-- check rollback of a RESTART IDENTITY operation 206BEGIN; 207TRUNCATE truncate_a RESTART IDENTITY; 208INSERT INTO truncate_a DEFAULT VALUES; 209SELECT * FROM truncate_a; 210ROLLBACK; 211INSERT INTO truncate_a DEFAULT VALUES; 212INSERT INTO truncate_a DEFAULT VALUES; 213SELECT * FROM truncate_a; 214 215DROP TABLE truncate_a; 216 217SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped 218