1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
-- Test basic TRUNCATE functionality.
CREATE TABLE truncate_a (col1 integer primary key);
INSERT INTO truncate_a VALUES (1);
INSERT INTO truncate_a VALUES (2);
SELECT * FROM truncate_a;
col1
------
1
2
(2 rows)
-- Roll truncate back
BEGIN;
ROLLBACK;
SELECT * FROM truncate_a;
col1
------
1
2
(2 rows)
-- Commit the truncate this time
BEGIN;
COMMIT;
CREATE TABLE trunc_c (a serial PRIMARY KEY);
-- Add some data to verify that truncating actually works ...
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
-- Add data again to test TRUNCATE ... CASCADE
INSERT INTO trunc_c VALUES (1);
INSERT INTO truncate_a VALUES (1);
-- Test TRUNCATE with inheritance
CREATE TABLE trunc_f (col1 integer primary key);
INSERT INTO trunc_f VALUES (1);
INSERT INTO trunc_f VALUES (2);
BEGIN;
ROLLBACK;
BEGIN;
ROLLBACK;
BEGIN;
ROLLBACK;
BEGIN;
SELECT * FROM trunc_f;
col1
------
1
2
(2 rows)
ROLLBACK;
-- Test ON TRUNCATE triggers
CREATE TABLE trunc_trigger_test (f1 int, f2 text, f3 text);
CREATE TABLE trunc_trigger_log (tgop text, tglevel text, tgwhen text,
tgargv text, tgtable name, rowcount bigint);
-- basic before trigger
INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux');
SELECT count(*) as "Row count in test table" FROM trunc_trigger_test;
Row count in test table
-------------------------
2
(1 row)
SELECT * FROM trunc_trigger_log;
tgop | tglevel | tgwhen | tgargv | tgtable | rowcount
------+---------+--------+--------+---------+----------
(0 rows)
-- same test with an after trigger
INSERT INTO trunc_trigger_test VALUES(1, 'foo', 'bar'), (2, 'baz', 'quux');
SELECT * FROM trunc_trigger_log;
tgop | tglevel | tgwhen | tgargv | tgtable | rowcount
------+---------+--------+--------+---------+----------
(0 rows)
DROP TABLE trunc_trigger_test;
DROP TABLE trunc_trigger_log;
CREATE TABLE truncate_a (id serial,
id1 integer default nextval('truncate_a_id1'));
-- check rollback of a RESTART IDENTITY operation
BEGIN;
ROLLBACK;
DROP TABLE truncate_a;
SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
ERROR: relation "truncate_a_id1" does not exist
LINE 1: SELECT nextval('truncate_a_id1');
^
CREATE TABLE truncprim (a int PRIMARY KEY);
|
