granicus.if.org Git - postgresql/blob - src/test/regress/expected/truncate.out

   1 -- Test basic TRUNCATE functionality.
   2 CREATE TABLE truncate_a (col1 integer primary key);
   3 INSERT INTO truncate_a VALUES (1);
   4 INSERT INTO truncate_a VALUES (2);
   5 SELECT * FROM truncate_a;
   6  col1
   7 ------
   8     1
   9     2
  10 (2 rows)
  11
  12 -- Roll truncate back
  13 BEGIN;
  14 TRUNCATE truncate_a;
  15 ROLLBACK;
  16 SELECT * FROM truncate_a;
  17  col1
  18 ------
  19     1
  20     2
  21 (2 rows)
  22
  23 -- Commit the truncate this time
  24 BEGIN;
  25 TRUNCATE truncate_a;
  26 COMMIT;
  27 SELECT * FROM truncate_a;
  28  col1
  29 ------
  30 (0 rows)
  31
  32 -- Test foreign-key checks
  33 CREATE TABLE trunc_b (a int REFERENCES truncate_a);
  34 CREATE TABLE trunc_c (a serial PRIMARY KEY);
  35 CREATE TABLE trunc_d (a int REFERENCES trunc_c);
  36 CREATE TABLE trunc_e (a int REFERENCES truncate_a, b int REFERENCES trunc_c);
  37 TRUNCATE TABLE truncate_a;              -- fail
  38 ERROR:  cannot truncate a table referenced in a foreign key constraint
  39 DETAIL:  Table "trunc_b" references "truncate_a".
  40 HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
  41 TRUNCATE TABLE truncate_a,trunc_b;              -- fail
  42 ERROR:  cannot truncate a table referenced in a foreign key constraint
  43 DETAIL:  Table "trunc_e" references "truncate_a".
  44 HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
  45 TRUNCATE TABLE truncate_a,trunc_b,trunc_e;      -- ok
  46 TRUNCATE TABLE truncate_a,trunc_e;              -- fail
  47 ERROR:  cannot truncate a table referenced in a foreign key constraint
  48 DETAIL:  Table "trunc_b" references "truncate_a".
  49 HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
  50 TRUNCATE TABLE trunc_c;         -- fail
  51 ERROR:  cannot truncate a table referenced in a foreign key constraint
  52 DETAIL:  Table "trunc_d" references "trunc_c".
  53 HINT:  Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
  54 TRUNCATE TABLE trunc_c,trunc_d;         -- fail
  55 ERROR:  cannot truncate a table referenced in a foreign key constraint
  56 DETAIL:  Table "trunc_e" references "trunc_c".
  57 HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
  58 TRUNCATE TABLE trunc_c,trunc_d,trunc_e; -- ok
  59 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a;      -- fail
  60 ERROR:  cannot truncate a table referenced in a foreign key constraint
  61 DETAIL:  Table "trunc_b" references "truncate_a".
  62 HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
  63 TRUNCATE TABLE trunc_c,trunc_d,trunc_e,truncate_a,trunc_b;      -- ok
  64 TRUNCATE TABLE truncate_a RESTRICT; -- fail
  65 ERROR:  cannot truncate a table referenced in a foreign key constraint
  66 DETAIL:  Table "trunc_b" references "truncate_a".
  67 HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
  68 TRUNCATE TABLE truncate_a CASCADE;  -- ok
  69 NOTICE:  truncate cascades to table "trunc_b"
  70 NOTICE:  truncate cascades to table "trunc_e"
  71 -- circular references
  72 ALTER TABLE truncate_a ADD FOREIGN KEY (col1) REFERENCES trunc_c;
  73 -- Add some data to verify that truncating actually works ...
  74 INSERT INTO trunc_c VALUES (1);
  75 INSERT INTO truncate_a VALUES (1);
  76 INSERT INTO trunc_b VALUES (1);
  77 INSERT INTO trunc_d VALUES (1);
  78 INSERT INTO trunc_e VALUES (1,1);
  79 TRUNCATE TABLE trunc_c;
  80 ERROR:  cannot truncate a table referenced in a foreign key constraint
  81 DETAIL:  Table "truncate_a" references "trunc_c".
  82 HINT:  Truncate table "truncate_a" at the same time, or use TRUNCATE ... CASCADE.
  83 TRUNCATE TABLE trunc_c,truncate_a;
  84 ERROR:  cannot truncate a table referenced in a foreign key constraint
  85 DETAIL:  Table "trunc_d" references "trunc_c".
  86 HINT:  Truncate table "trunc_d" at the same time, or use TRUNCATE ... CASCADE.
  87 TRUNCATE TABLE trunc_c,truncate_a,trunc_d;
  88 ERROR:  cannot truncate a table referenced in a foreign key constraint
  89 DETAIL:  Table "trunc_e" references "trunc_c".
  90 HINT:  Truncate table "trunc_e" at the same time, or use TRUNCATE ... CASCADE.
  91 TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e;
  92 ERROR:  cannot truncate a table referenced in a foreign key constraint
  93 DETAIL:  Table "trunc_b" references "truncate_a".
  94 HINT:  Truncate table "trunc_b" at the same time, or use TRUNCATE ... CASCADE.
  95 TRUNCATE TABLE trunc_c,truncate_a,trunc_d,trunc_e,trunc_b;
  96 -- Verify that truncating did actually work
  97 SELECT * FROM truncate_a
  98    UNION ALL
  99  SELECT * FROM trunc_c
 100    UNION ALL
 101  SELECT * FROM trunc_b
 102    UNION ALL
 103  SELECT * FROM trunc_d;
 104  col1
 105 ------
 106 (0 rows)
 107
 108 SELECT * FROM trunc_e;
 109  a | b
 110 ---+---
 111 (0 rows)
 112
 113 -- Add data again to test TRUNCATE ... CASCADE
 114 INSERT INTO trunc_c VALUES (1);
 115 INSERT INTO truncate_a VALUES (1);
 116 INSERT INTO trunc_b VALUES (1);
 117 INSERT INTO trunc_d VALUES (1);
 118 INSERT INTO trunc_e VALUES (1,1);
 119 TRUNCATE TABLE trunc_c CASCADE;  -- ok
 120 NOTICE:  truncate cascades to table "truncate_a"
 121 NOTICE:  truncate cascades to table "trunc_d"
 122 NOTICE:  truncate cascades to table "trunc_e"
 123 NOTICE:  truncate cascades to table "trunc_b"
 124 SELECT * FROM truncate_a
 125    UNION ALL
 126  SELECT * FROM trunc_c
 127    UNION ALL
 128  SELECT * FROM trunc_b
 129    UNION ALL
 130  SELECT * FROM trunc_d;
 131  col1
 132 ------
 133 (0 rows)
 134
 135 SELECT * FROM trunc_e;
 136  a | b
 137 ---+---
 138 (0 rows)
 139
 140 DROP TABLE truncate_a,trunc_c,trunc_b,trunc_d,trunc_e CASCADE;
 141 -- Test TRUNCATE with inheritance
 142 CREATE TABLE trunc_f (col1 integer primary key);
 143 INSERT INTO trunc_f VALUES (1);
 144 INSERT INTO trunc_f VALUES (2);
 145 CREATE TABLE trunc_fa (col2a text) INHERITS (trunc_f);
 146 INSERT INTO trunc_fa VALUES (3, 'three');
 147 CREATE TABLE trunc_fb (col2b int) INHERITS (trunc_f);
 148 INSERT INTO trunc_fb VALUES (4, 444);
 149 CREATE TABLE trunc_faa (col3 text) INHERITS (trunc_fa);
 150 INSERT INTO trunc_faa VALUES (5, 'five', 'FIVE');
 151 BEGIN;
 152 SELECT * FROM trunc_f;
 153  col1
 154 ------
 155     1
 156     2
 157     3
 158     4
 159     5
 160 (5 rows)
 161
 162 TRUNCATE trunc_f;
 163 SELECT * FROM trunc_f;
 164  col1
 165 ------
 166 (0 rows)
 167
 168 ROLLBACK;
 169 BEGIN;
 170 SELECT * FROM trunc_f;
 171  col1
 172 ------
 173     1
 174     2
 175     3
 176     4
 177     5
 178 (5 rows)
 179
 180 TRUNCATE ONLY trunc_f;
 181 SELECT * FROM trunc_f;
 182  col1
 183 ------
 184     3
 185     4
 186     5
 187 (3 rows)
 188
 189 ROLLBACK;
 190 BEGIN;
 191 SELECT * FROM trunc_f;
 192  col1
 193 ------
 194     1
 195     2
 196     3
 197     4
 198     5
 199 (5 rows)
 200
 201 SELECT * FROM trunc_fa;
 202  col1 | col2a
 203 ------+-------
 204     3 | three
 205     5 | five
 206 (2 rows)
 207
 208 SELECT * FROM trunc_faa;
 209  col1 | col2a | col3
 210 ------+-------+------
 211     5 | five  | FIVE
 212 (1 row)
 213
 214 TRUNCATE ONLY trunc_fb, ONLY trunc_fa;
 215 SELECT * FROM trunc_f;
 216  col1
 217 ------
 218     1
 219     2
 220     5
 221 (3 rows)
 222
 223 SELECT * FROM trunc_fa;
 224  col1 | col2a
 225 ------+-------
 226     5 | five
 227 (1 row)
 228
 229 SELECT * FROM trunc_faa;
 230  col1 | col2a | col3
 231 ------+-------+------
 232     5 | five  | FIVE
 233 (1 row)
 234
 235 ROLLBACK;
 236 BEGIN;
 237 SELECT * FROM trunc_f;
 238  col1
 239 ------
 240     1
 241     2
 242     3
 243     4
 244     5
 245 (5 rows)
 246
 247 SELECT * FROM trunc_fa;
 248  col1 | col2a
 249 ------+-------
 250     3 | three
 251     5 | five
 252 (2 rows)
 253
 254 SELECT * FROM trunc_faa;
 255  col1 | col2a | col3
 256 ------+-------+------
 257     5 | five  | FIVE
 258 (1 row)
 259
 260 TRUNCATE ONLY trunc_fb, trunc_fa;
 261 SELECT * FROM trunc_f;
 262  col1
 263 ------
 264     1
 265     2
 266 (2 rows)
 267
 268 SELECT * FROM trunc_fa;
 269  col1 | col2a
 270 ------+-------
 271 (0 rows)
 272
 273 SELECT * FROM trunc_faa;
 274  col1 | col2a | col3
 275 ------+-------+------
 276 (0 rows)
 277
 278 ROLLBACK;
 279 DROP TABLE trunc_f CASCADE;
 280 NOTICE:  drop cascades to 3 other objects
 281 DETAIL:  drop cascades to table trunc_fa
 282 drop cascades to table trunc_faa
 283 drop cascades to table trunc_fb
 284 -- Test ON TRUNCATE triggers
 285 CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);
 286 CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text,
 287         tgargv text, tgtable name, rowcount bigint);
 288 CREATE FUNCTION trunctrigger() RETURNS trigger as $$
 289 declare c bigint;
 290 begin
 291     execute 'select count(*) from ' || quote_ident(tg_table_name) into c;
 292     insert into trunc_trigger_log values
 293       (TG_OP, TG_LEVEL, TG_WHEN, TG_ARGV[0], tg_table_name, c);
 294     return null;
 295 end;
 296 $$ LANGUAGE plpgsql;
 297 -- basic before trigger
 298 INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux');
 299 CREATE TRIGGER t
 300 BEFORE TRUNCATE ON trunc_trigger_test
 301 FOR EACH STATEMENT
 302 EXECUTE PROCEDURE trunctrigger('before trigger truncate');
 303 SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
 304  Row count in test table
 305 -------------------------
 306                        2
 307 (1 row)
 308
 309 SELECT * FROM trunc_trigger_log;
 310  tgop | tglevel | tgwhen | tgargv | tgtable | rowcount
 311 ------+---------+--------+--------+---------+----------
 312 (0 rows)
 313
 314 TRUNCATE trunc_trigger_test;
 315 SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
 316  Row count in test table
 317 -------------------------
 318                        0
 319 (1 row)
 320
 321 SELECT * FROM trunc_trigger_log;
 322    tgop   |  tglevel  | tgwhen |         tgargv          |      tgtable       | rowcount
 323 ----------+-----------+--------+-------------------------+--------------------+----------
 324  TRUNCATE | STATEMENT | BEFORE | before trigger truncate | trunc_trigger_test |        2
 325 (1 row)
 326
 327 DROP TRIGGER t ON trunc_trigger_test;
 328 truncate trunc_trigger_log;
 329 -- same test with an after trigger
 330 INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux');
 331 CREATE TRIGGER tt
 332 AFTER TRUNCATE ON trunc_trigger_test
 333 FOR EACH STATEMENT
 334 EXECUTE PROCEDURE trunctrigger('after trigger truncate');
 335 SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
 336  Row count in test table
 337 -------------------------
 338                        2
 339 (1 row)
 340
 341 SELECT * FROM trunc_trigger_log;
 342  tgop | tglevel | tgwhen | tgargv | tgtable | rowcount
 343 ------+---------+--------+--------+---------+----------
 344 (0 rows)
 345
 346 TRUNCATE trunc_trigger_test;
 347 SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
 348  Row count in test table
 349 -------------------------
 350                        0
 351 (1 row)
 352
 353 SELECT * FROM trunc_trigger_log;
 354    tgop   |  tglevel  | tgwhen |         tgargv         |      tgtable       | rowcount
 355 ----------+-----------+--------+------------------------+--------------------+----------
 356  TRUNCATE | STATEMENT | AFTER  | after trigger truncate | trunc_trigger_test |        0
 357 (1 row)
 358
 359 DROP TABLE trunc_trigger_test;
 360 DROP TABLE trunc_trigger_log;
 361 DROP FUNCTION trunctrigger();
 362 -- test TRUNCATE ... RESTART IDENTITY
 363 CREATE SEQUENCE truncate_a_id1 START WITH 33;
 364 CREATE TABLE truncate_a (id serial,
 365                          id1 integer default nextval('truncate_a_id1'));
 366 ALTER SEQUENCE truncate_a_id1 OWNED BY truncate_a.id1;
 367 INSERT INTO truncate_a DEFAULT VALUES;
 368 INSERT INTO truncate_a DEFAULT VALUES;
 369 SELECT * FROM truncate_a;
 370  id | id1
 371 ----+-----
 372   1 |  33
 373   2 |  34
 374 (2 rows)
 375
 376 TRUNCATE truncate_a;
 377 INSERT INTO truncate_a DEFAULT VALUES;
 378 INSERT INTO truncate_a DEFAULT VALUES;
 379 SELECT * FROM truncate_a;
 380  id | id1
 381 ----+-----
 382   3 |  35
 383   4 |  36
 384 (2 rows)
 385
 386 TRUNCATE truncate_a RESTART IDENTITY;
 387 INSERT INTO truncate_a DEFAULT VALUES;
 388 INSERT INTO truncate_a DEFAULT VALUES;
 389 SELECT * FROM truncate_a;
 390  id | id1
 391 ----+-----
 392   1 |  33
 393   2 |  34
 394 (2 rows)
 395
 396 CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));
 397 INSERT INTO truncate_b DEFAULT VALUES;
 398 INSERT INTO truncate_b DEFAULT VALUES;
 399 SELECT * FROM truncate_b;
 400  id
 401 ----
 402  44
 403  45
 404 (2 rows)
 405
 406 TRUNCATE truncate_b;
 407 INSERT INTO truncate_b DEFAULT VALUES;
 408 INSERT INTO truncate_b DEFAULT VALUES;
 409 SELECT * FROM truncate_b;
 410  id
 411 ----
 412  46
 413  47
 414 (2 rows)
 415
 416 TRUNCATE truncate_b RESTART IDENTITY;
 417 INSERT INTO truncate_b DEFAULT VALUES;
 418 INSERT INTO truncate_b DEFAULT VALUES;
 419 SELECT * FROM truncate_b;
 420  id
 421 ----
 422  44
 423  45
 424 (2 rows)
 425
 426 -- check rollback of a RESTART IDENTITY operation
 427 BEGIN;
 428 TRUNCATE truncate_a RESTART IDENTITY;
 429 INSERT INTO truncate_a DEFAULT VALUES;
 430 SELECT * FROM truncate_a;
 431  id | id1
 432 ----+-----
 433   1 |  33
 434 (1 row)
 435
 436 ROLLBACK;
 437 INSERT INTO truncate_a DEFAULT VALUES;
 438 INSERT INTO truncate_a DEFAULT VALUES;
 439 SELECT * FROM truncate_a;
 440  id | id1
 441 ----+-----
 442   1 |  33
 443   2 |  34
 444   3 |  35
 445   4 |  36
 446 (4 rows)
 447
 448 DROP TABLE truncate_a;
 449 SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
 450 ERROR:  relation "truncate_a_id1" does not exist
 451 LINE 1: SELECT nextval('truncate_a_id1');
 452                        ^
 453 -- partitioned table
 454 CREATE TABLE truncparted (a int, b char) PARTITION BY LIST (a);
 455 -- error, can't truncate a partitioned table
 456 TRUNCATE ONLY truncparted;
 457 ERROR:  cannot truncate only a partitioned table
 458 HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
 459 CREATE TABLE truncparted1 PARTITION OF truncparted FOR VALUES IN (1);
 460 INSERT INTO truncparted VALUES (1, 'a');
 461 -- error, must truncate partitions
 462 TRUNCATE ONLY truncparted;
 463 ERROR:  cannot truncate only a partitioned table
 464 HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
 465 TRUNCATE truncparted;
 466 DROP TABLE truncparted;
 467 -- foreign key on partitioned table: partition key is referencing column.
 468 -- Make sure truncate did execute on all tables
 469 CREATE FUNCTION tp_ins_data() RETURNS void LANGUAGE plpgsql AS $$
 470   BEGIN
 471         INSERT INTO truncprim VALUES (1), (100), (150);
 472         INSERT INTO truncpart VALUES (1), (100), (150);
 473   END
 474 $$;
 475 CREATE FUNCTION tp_chk_data(OUT pktb regclass, OUT pkval int, OUT fktb regclass, OUT fkval int)
 476   RETURNS SETOF record LANGUAGE plpgsql AS $$
 477   BEGIN
 478     RETURN QUERY SELECT
 479       pk.tableoid::regclass, pk.a, fk.tableoid::regclass, fk.a
 480     FROM truncprim pk FULL JOIN truncpart fk USING (a)
 481     ORDER BY 2, 4;
 482   END
 483 $$;
 484 CREATE TABLE truncprim (a int PRIMARY KEY);
 485 CREATE TABLE truncpart (a int REFERENCES truncprim)
 486   PARTITION BY RANGE (a);
 487 CREATE TABLE truncpart_1 PARTITION OF truncpart FOR VALUES FROM (0) TO (100);
 488 CREATE TABLE truncpart_2 PARTITION OF truncpart FOR VALUES FROM (100) TO (200)
 489   PARTITION BY RANGE (a);
 490 CREATE TABLE truncpart_2_1 PARTITION OF truncpart_2 FOR VALUES FROM (100) TO (150);
 491 CREATE TABLE truncpart_2_d PARTITION OF truncpart_2 DEFAULT;
 492 TRUNCATE TABLE truncprim;       -- should fail
 493 ERROR:  cannot truncate a table referenced in a foreign key constraint
 494 DETAIL:  Table "truncpart" references "truncprim".
 495 HINT:  Truncate table "truncpart" at the same time, or use TRUNCATE ... CASCADE.
 496 select tp_ins_data();
 497  tp_ins_data
 498 -------------
 499
 500 (1 row)
 501
 502 -- should truncate everything
 503 TRUNCATE TABLE truncprim, truncpart;
 504 select * from tp_chk_data();
 505  pktb | pkval | fktb | fkval
 506 ------+-------+------+-------
 507 (0 rows)
 508
 509 select tp_ins_data();
 510  tp_ins_data
 511 -------------
 512
 513 (1 row)
 514
 515 -- should truncate everything
 516 TRUNCATE TABLE truncprim CASCADE;
 517 NOTICE:  truncate cascades to table "truncpart"
 518 NOTICE:  truncate cascades to table "truncpart_1"
 519 NOTICE:  truncate cascades to table "truncpart_2"
 520 NOTICE:  truncate cascades to table "truncpart_2_1"
 521 NOTICE:  truncate cascades to table "truncpart_2_d"
 522 SELECT * FROM tp_chk_data();
 523  pktb | pkval | fktb | fkval
 524 ------+-------+------+-------
 525 (0 rows)
 526
 527 SELECT tp_ins_data();
 528  tp_ins_data
 529 -------------
 530
 531 (1 row)
 532
 533 -- should truncate all partitions
 534 TRUNCATE TABLE truncpart;
 535 SELECT * FROM tp_chk_data();
 536    pktb    | pkval | fktb | fkval
 537 -----------+-------+------+-------
 538  truncprim |     1 |      |
 539  truncprim |   100 |      |
 540  truncprim |   150 |      |
 541 (3 rows)
 542
 543 DROP TABLE truncprim, truncpart;
 544 DROP FUNCTION tp_ins_data(), tp_chk_data();