6
-- This test tries to verify that WITHOUT OIDS actually saves space.
7
-- On machines where MAXALIGN is 8, WITHOUT OIDS may or may not save any
8
-- space, depending on the size of the tuple header + null bitmap.
9
-- As of 8.0 we need a 9-bit null bitmap to force the difference to appear.
11
CREATE TABLE wi (i INT,
12
n1 int, n2 int, n3 int, n4 int,
13
n5 int, n6 int, n7 int, n8 int) WITH OIDS;
14
CREATE TABLE wo (i INT,
15
n1 int, n2 int, n3 int, n4 int,
16
n5 int, n6 int, n7 int, n8 int) WITHOUT OIDS;
18
INSERT INTO wi VALUES (1); -- 1
19
INSERT INTO wo SELECT i FROM wi; -- 1
20
INSERT INTO wo SELECT i+1 FROM wi; -- 1+1=2
21
INSERT INTO wi SELECT i+1 FROM wo; -- 1+2=3
22
INSERT INTO wi SELECT i+3 FROM wi; -- 3+3=6
23
INSERT INTO wo SELECT i+2 FROM wi; -- 2+6=8
24
INSERT INTO wo SELECT i+8 FROM wo; -- 8+8=16
25
INSERT INTO wi SELECT i+6 FROM wo; -- 6+16=22
26
INSERT INTO wi SELECT i+22 FROM wi; -- 22+22=44
27
INSERT INTO wo SELECT i+16 FROM wi; -- 16+44=60
28
INSERT INTO wo SELECT i+60 FROM wo; -- 60+60=120
29
INSERT INTO wi SELECT i+44 FROM wo; -- 44+120=164
30
INSERT INTO wi SELECT i+164 FROM wi; -- 164+164=328
31
INSERT INTO wo SELECT i+120 FROM wi; -- 120+328=448
32
INSERT INTO wo SELECT i+448 FROM wo; -- 448+448=896
33
INSERT INTO wi SELECT i+328 FROM wo; -- 328+896=1224
34
INSERT INTO wi SELECT i+1224 FROM wi; -- 1224+1224=2448
35
INSERT INTO wo SELECT i+896 FROM wi; -- 896+2448=3344
36
INSERT INTO wo SELECT i+3344 FROM wo; -- 3344+3344=6688
37
INSERT INTO wi SELECT i+2448 FROM wo; -- 2448+6688=9136
38
INSERT INTO wo SELECT i+6688 FROM wi WHERE i<=2448; -- 6688+2448=9136
40
SELECT count(oid) FROM wi;
42
SELECT count(oid) FROM wo;
47
SELECT min(relpages) < max(relpages), min(reltuples) - max(reltuples)
49
WHERE relname IN ('wi', 'wo');
55
-- WITH / WITHOUT OIDS in CREATE TABLE AS
57
CREATE TABLE create_table_test (
62
COPY create_table_test FROM stdin;
67
CREATE TABLE create_table_test2 WITH OIDS AS
68
SELECT a + b AS c1, a - b AS c2 FROM create_table_test;
70
CREATE TABLE create_table_test3 WITHOUT OIDS AS
71
SELECT a + b AS c1, a - b AS c2 FROM create_table_test;
73
SELECT count(oid) FROM create_table_test2;
75
SELECT count(oid) FROM create_table_test3;
77
DROP TABLE create_table_test;
78
DROP TABLE create_table_test2;
79
DROP TABLE create_table_test3;