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 205CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44)); 206 207INSERT INTO truncate_b DEFAULT VALUES; 208INSERT INTO truncate_b DEFAULT VALUES; 209SELECT * FROM truncate_b; 210 211TRUNCATE truncate_b; 212 213INSERT INTO truncate_b DEFAULT VALUES; 214INSERT INTO truncate_b DEFAULT VALUES; 215SELECT * FROM truncate_b; 216 217TRUNCATE truncate_b RESTART IDENTITY; 218 219INSERT INTO truncate_b DEFAULT VALUES; 220INSERT INTO truncate_b DEFAULT VALUES; 221SELECT * FROM truncate_b; 222 223-- check rollback of a RESTART IDENTITY operation 224BEGIN; 225TRUNCATE truncate_a RESTART IDENTITY; 226INSERT INTO truncate_a DEFAULT VALUES; 227SELECT * FROM truncate_a; 228ROLLBACK; 229INSERT INTO truncate_a DEFAULT VALUES; 230INSERT INTO truncate_a DEFAULT VALUES; 231SELECT * FROM truncate_a; 232 233DROP TABLE truncate_a; 234 235SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped 236 237-- partitioned table 238CREATE TABLE truncparted (a int, b char) PARTITION BY LIST (a); 239-- error, can't truncate a partitioned table 240TRUNCATE ONLY truncparted; 241CREATE TABLE truncparted1 PARTITION OF truncparted FOR VALUES IN (1); 242INSERT INTO truncparted VALUES (1, 'a'); 243-- error, must truncate partitions 244TRUNCATE ONLY truncparted; 245TRUNCATE truncparted; 246DROP TABLE truncparted; 247 248-- foreign key on partitioned table: partition key is referencing column. 249-- Make sure truncate did execute on all tables 250CREATE FUNCTION tp_ins_data() RETURNS void LANGUAGE plpgsql AS $$ 251 BEGIN 252 INSERT INTO truncprim VALUES (1), (100), (150); 253 INSERT INTO truncpart VALUES (1), (100), (150); 254 END 255$$; 256CREATE FUNCTION tp_chk_data(OUT pktb regclass, OUT pkval int, OUT fktb regclass, OUT fkval int) 257 RETURNS SETOF record LANGUAGE plpgsql AS $$ 258 BEGIN 259 RETURN QUERY SELECT 260 pk.tableoid::regclass, pk.a, fk.tableoid::regclass, fk.a 261 FROM truncprim pk FULL JOIN truncpart fk USING (a) 262 ORDER BY 2, 4; 263 END 264$$; 265CREATE TABLE truncprim (a int PRIMARY KEY); 266CREATE TABLE truncpart (a int REFERENCES truncprim) 267 PARTITION BY RANGE (a); 268CREATE TABLE truncpart_1 PARTITION OF truncpart FOR VALUES FROM (0) TO (100); 269CREATE TABLE truncpart_2 PARTITION OF truncpart FOR VALUES FROM (100) TO (200) 270 PARTITION BY RANGE (a); 271CREATE TABLE truncpart_2_1 PARTITION OF truncpart_2 FOR VALUES FROM (100) TO (150); 272CREATE TABLE truncpart_2_d PARTITION OF truncpart_2 DEFAULT; 273 274TRUNCATE TABLE truncprim; -- should fail 275 276select tp_ins_data(); 277-- should truncate everything 278TRUNCATE TABLE truncprim, truncpart; 279select * from tp_chk_data(); 280 281select tp_ins_data(); 282-- should truncate everything 283TRUNCATE TABLE truncprim CASCADE; 284SELECT * FROM tp_chk_data(); 285 286SELECT tp_ins_data(); 287-- should truncate all partitions 288TRUNCATE TABLE truncpart; 289SELECT * FROM tp_chk_data(); 290DROP TABLE truncprim, truncpart; 291DROP FUNCTION tp_ins_data(), tp_chk_data(); 292 293-- test cascade when referencing a partitioned table 294CREATE TABLE trunc_a (a INT PRIMARY KEY) PARTITION BY RANGE (a); 295CREATE TABLE trunc_a1 PARTITION OF trunc_a FOR VALUES FROM (0) TO (10); 296CREATE TABLE trunc_a2 PARTITION OF trunc_a FOR VALUES FROM (10) TO (20) 297 PARTITION BY RANGE (a); 298CREATE TABLE trunc_a21 PARTITION OF trunc_a2 FOR VALUES FROM (10) TO (12); 299CREATE TABLE trunc_a22 PARTITION OF trunc_a2 FOR VALUES FROM (12) TO (16); 300CREATE TABLE trunc_a2d PARTITION OF trunc_a2 DEFAULT; 301CREATE TABLE trunc_a3 PARTITION OF trunc_a FOR VALUES FROM (20) TO (30); 302INSERT INTO trunc_a VALUES (0), (5), (10), (15), (20), (25); 303 304-- truncate a partition cascading to a table 305CREATE TABLE ref_b ( 306 b INT PRIMARY KEY, 307 a INT REFERENCES trunc_a(a) ON DELETE CASCADE 308); 309INSERT INTO ref_b VALUES (10, 0), (50, 5), (100, 10), (150, 15); 310 311TRUNCATE TABLE trunc_a1 CASCADE; 312SELECT a FROM ref_b; 313 314DROP TABLE ref_b; 315 316-- truncate a partition cascading to a partitioned table 317CREATE TABLE ref_c ( 318 c INT PRIMARY KEY, 319 a INT REFERENCES trunc_a(a) ON DELETE CASCADE 320) PARTITION BY RANGE (c); 321CREATE TABLE ref_c1 PARTITION OF ref_c FOR VALUES FROM (100) TO (200); 322CREATE TABLE ref_c2 PARTITION OF ref_c FOR VALUES FROM (200) TO (300); 323INSERT INTO ref_c VALUES (100, 10), (150, 15), (200, 20), (250, 25); 324 325TRUNCATE TABLE trunc_a21 CASCADE; 326SELECT a as "from table ref_c" FROM ref_c; 327SELECT a as "from table trunc_a" FROM trunc_a ORDER BY a; 328 329DROP TABLE trunc_a, ref_c; 330