diff options
Diffstat (limited to 'src/test/regress/input/misc.source')
-rw-r--r-- | src/test/regress/input/misc.source | 547 |
1 files changed, 0 insertions, 547 deletions
diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source index e397f247b70..74d2d84bc6e 100644 --- a/src/test/regress/input/misc.source +++ b/src/test/regress/input/misc.source @@ -93,553 +93,6 @@ SELECT count(*) FROM onek where oidrand(onek.oid, 10); -- --- transaction blocks --- -BEGIN; - -SELECT * - INTO TABLE xacttest - FROM aggtest; - -INSERT INTO xacttest (a, b) VALUES (777, 777.777); - -END; - --- should retrieve one value-- -SELECT a FROM xacttest WHERE a > 100; - - -BEGIN; - -CREATE TABLE disappear (a int4); - -DELETE FROM aggtest; - --- should be empty -SELECT * FROM aggtest; - -ABORT; - --- should not exist -SELECT oid FROM pg_class WHERE relname = 'disappear'; - --- should have members again -SELECT * FROM aggtest; - - --- --- portal manipulation --- -BEGIN; - -DECLARE foo1 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo2 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo3 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo4 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo5 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo6 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo7 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo8 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo9 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo10 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo11 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo12 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo13 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo14 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo15 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo16 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo17 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo18 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo19 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo20 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo21 CURSOR FOR SELECT * FROM tenk1; - -DECLARE foo22 CURSOR FOR SELECT * FROM tenk2; - -DECLARE foo23 CURSOR FOR SELECT * FROM tenk1; - -FETCH 1 in foo1; - -FETCH 2 in foo2; - -FETCH 3 in foo3; - -FETCH 4 in foo4; - -FETCH 5 in foo5; - -FETCH 6 in foo6; - -FETCH 7 in foo7; - -FETCH 8 in foo8; - -FETCH 9 in foo9; - -FETCH 10 in foo10; - -FETCH 11 in foo11; - -FETCH 12 in foo12; - -FETCH 13 in foo13; - -FETCH 14 in foo14; - -FETCH 15 in foo15; - -FETCH 16 in foo16; - -FETCH 17 in foo17; - -FETCH 18 in foo18; - -FETCH 19 in foo19; - -FETCH 20 in foo20; - -FETCH 21 in foo21; - -FETCH 22 in foo22; - -FETCH 23 in foo23; - -FETCH backward 1 in foo23; - -FETCH backward 2 in foo22; - -FETCH backward 3 in foo21; - -FETCH backward 4 in foo20; - -FETCH backward 5 in foo19; - -FETCH backward 6 in foo18; - -FETCH backward 7 in foo17; - -FETCH backward 8 in foo16; - -FETCH backward 9 in foo15; - -FETCH backward 10 in foo14; - -FETCH backward 11 in foo13; - -FETCH backward 12 in foo12; - -FETCH backward 13 in foo11; - -FETCH backward 14 in foo10; - -FETCH backward 15 in foo9; - -FETCH backward 16 in foo8; - -FETCH backward 17 in foo7; - -FETCH backward 18 in foo6; - -FETCH backward 19 in foo5; - -FETCH backward 20 in foo4; - -FETCH backward 21 in foo3; - -FETCH backward 22 in foo2; - -FETCH backward 23 in foo1; - -CLOSE foo1; - -CLOSE foo2; - -CLOSE foo3; - -CLOSE foo4; - -CLOSE foo5; - -CLOSE foo6; - -CLOSE foo7; - -CLOSE foo8; - -CLOSE foo9; - -CLOSE foo10; - -CLOSE foo11; - -CLOSE foo12; - -end; - -EXTEND INDEX onek2_u1_prtl WHERE onek2.unique1 <= 60; - -BEGIN; - -DECLARE foo13 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 50; - -DECLARE foo14 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 51; - -DECLARE foo15 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 52; - -DECLARE foo16 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 53; - -DECLARE foo17 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 54; - -DECLARE foo18 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 55; - -DECLARE foo19 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 56; - -DECLARE foo20 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 57; - -DECLARE foo21 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 58; - -DECLARE foo22 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 59; - -DECLARE foo23 CURSOR FOR - SELECT * FROM onek WHERE unique1 = 60; - -DECLARE foo24 CURSOR FOR - SELECT * FROM onek2 WHERE unique1 = 50; - -DECLARE foo25 CURSOR FOR - SELECT * FROM onek2 WHERE unique1 = 60; - -FETCH all in foo13; - -FETCH all in foo14; - -FETCH all in foo15; - -FETCH all in foo16; - -FETCH all in foo17; - -FETCH all in foo18; - -FETCH all in foo19; - -FETCH all in foo20; - -FETCH all in foo21; - -FETCH all in foo22; - -FETCH all in foo23; - -FETCH all in foo24; - -FETCH all in foo25; - -CLOSE foo13; - -CLOSE foo14; - -CLOSE foo15; - -CLOSE foo16; - -CLOSE foo17; - -CLOSE foo18; - -CLOSE foo19; - -CLOSE foo20; - -CLOSE foo21; - -CLOSE foo22; - -CLOSE foo23; - -CLOSE foo24; - -CLOSE foo25; - -END; - - --- --- PURGE --- --- we did two updates on each of these 10K tables up above. we should --- therefore go from 10002 tuples (two of which are not visible without --- using a time qual) to 10000. --- --- vacuuming here also tests whether or not the hash index compaction --- code works; this used to be commented out because the hash AM would --- miss deleting a bunch of index tuples, which caused big problems when --- you dereferenced the tids and found garbage.. --- --- absolute time -PURGE hash_f8_heap BEFORE 'now'; - -SELECT count(*) AS has_10002 FROM hash_f8_heap[,] h; - -VACUUM hash_f8_heap; - -SELECT count(*) AS has_10000 FROM hash_f8_heap[,] h; - --- relative time -PURGE hash_i4_heap AFTER '@ 1 second ago'; - -SELECT count(*) AS has_10002 FROM hash_i4_heap[,] h; - -VACUUM hash_i4_heap; - -SELECT count(*) AS has_10000 FROM hash_i4_heap[,] h; - - --- --- add attribute --- -CREATE TABLE temp (initial int4); - -ALTER TABLE temp ADD COLUMN a int4; - -ALTER TABLE temp ADD COLUMN b char16; - -ALTER TABLE temp ADD COLUMN c text; - -ALTER TABLE temp ADD COLUMN d float8; - -ALTER TABLE temp ADD COLUMN e float4; - -ALTER TABLE temp ADD COLUMN f int2; - -ALTER TABLE temp ADD COLUMN g polygon; - -ALTER TABLE temp ADD COLUMN h abstime; - -ALTER TABLE temp ADD COLUMN i char; - -ALTER TABLE temp ADD COLUMN j abstime[]; - -ALTER TABLE temp ADD COLUMN k dt; - -ALTER TABLE temp ADD COLUMN l tid; - -ALTER TABLE temp ADD COLUMN m xid; - -ALTER TABLE temp ADD COLUMN n oid8; - ---ALTER TABLE temp ADD COLUMN o lock; -ALTER TABLE temp ADD COLUMN p smgr; - -ALTER TABLE temp ADD COLUMN q point; - -ALTER TABLE temp ADD COLUMN r lseg; - -ALTER TABLE temp ADD COLUMN s path; - -ALTER TABLE temp ADD COLUMN t box; - -ALTER TABLE temp ADD COLUMN u tinterval; - -ALTER TABLE temp ADD COLUMN v oidint4; - -ALTER TABLE temp ADD COLUMN w oidname; - -ALTER TABLE temp ADD COLUMN x float8[]; - -ALTER TABLE temp ADD COLUMN y float4[]; - -ALTER TABLE temp ADD COLUMN z int2[]; - -INSERT INTO temp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, - v, w, x, y, z) - VALUES (4, 'char16', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', - 314159, '(1,1)', 512, - '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]', - '1/3', '1,char16', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); - -SELECT * FROM temp; - -DROP TABLE temp; - --- the wolf bug - schema mods caused inconsistent row descriptors -CREATE TABLE temp ( - initial int4 -) ARCHIVE = light; - -ALTER TABLE temp ADD COLUMN a int4; - -ALTER TABLE temp ADD COLUMN b char16; - -ALTER TABLE temp ADD COLUMN c text; - -ALTER TABLE temp ADD COLUMN d float8; - -ALTER TABLE temp ADD COLUMN e float4; - -ALTER TABLE temp ADD COLUMN f int2; - -ALTER TABLE temp ADD COLUMN g polygon; - -ALTER TABLE temp ADD COLUMN h abstime; - -ALTER TABLE temp ADD COLUMN i char; - -ALTER TABLE temp ADD COLUMN j abstime[]; - -ALTER TABLE temp ADD COLUMN k dt; - -ALTER TABLE temp ADD COLUMN l tid; - -ALTER TABLE temp ADD COLUMN m xid; - -ALTER TABLE temp ADD COLUMN n oid8; - ---ALTER TABLE temp ADD COLUMN o lock; -ALTER TABLE temp ADD COLUMN p smgr; - -ALTER TABLE temp ADD COLUMN q point; - -ALTER TABLE temp ADD COLUMN r lseg; - -ALTER TABLE temp ADD COLUMN s path; - -ALTER TABLE temp ADD COLUMN t box; - -ALTER TABLE temp ADD COLUMN u tinterval; - -ALTER TABLE temp ADD COLUMN v oidint4; - -ALTER TABLE temp ADD COLUMN w oidname; - -ALTER TABLE temp ADD COLUMN x float8[]; - -ALTER TABLE temp ADD COLUMN y float4[]; - -ALTER TABLE temp ADD COLUMN z int2[]; - -INSERT INTO temp (a, b, c, d, e, f, g, h, i, j, k, l, m, n, p, q, r, s, t, u, - v, w, x, y, z) - VALUES (4, 'char16', 'text', 4.1, 4.1, 2, '(4.1,4.1,3.1,3.1)', - 'Mon May 1 00:30:30 1995', 'c', '{Mon May 1 00:30:30 1995, Monday Aug 24 14:43:07 1992, epoch}', - 314159, '(1,1)', 512, - '1 2 3 4 5 6 7 8', 'magnetic disk', '(1.1,1.1)', '(4.1,4.1,3.1,3.1)', - '(0,2,4.1,4.1,3.1,3.1)', '(4.1,4.1,3.1,3.1)', '["current" "infinity"]', - '1/3', '1,char16', '{1.0,2.0,3.0,4.0}', '{1.0,2.0,3.0,4.0}', '{1,2,3,4}'); - -SELECT * FROM temp[,]; - -DROP TABLE temp; - - --- --- rename - --- should preserve indices --- -ALTER TABLE tenk1 RENAME TO ten_k; - --- 20 values, sorted -SELECT unique1 FROM ten_k WHERE unique1 < 20; - --- 20 values, sorted -SELECT unique2 FROM ten_k WHERE unique2 < 20; - --- 100 values, sorted -SELECT hundred FROM ten_k WHERE hundred = 50; - -ALTER TABLE ten_k RENAME TO tenk1; - --- 5 values, sorted -SELECT unique1 FROM tenk1 WHERE unique1 < 5; - - --- --- VIEW queries --- --- test the views defined in create.source --- -SELECT * from street; - -SELECT * from iexit; - -SELECT * from toyemp where name='sharon'; - - - - --- --- AGGREGATES --- -SELECT avg(four) AS avg_1 FROM onek; - -SELECT avg(a) AS avg_49 FROM aggtest WHERE a < 100; - -SELECT avg(b) AS avg_107_943 FROM aggtest; - -SELECT avg(gpa) AS avg_3_4 FROM student; - - -SELECT sum(four) AS sum_1500 FROM onek; - -SELECT sum(a) AS sum_198 FROM aggtest; - -SELECT sum(b) AS avg_431_773 FROM aggtest; - -SELECT sum(gpa) AS avg_6_8 FROM student; - - -SELECT max(four) AS max_3 FROM onek; - -SELECT max(a) AS max_100 FROM aggtest; - -SELECT max(aggtest.b) AS max_324_78 FROM aggtest; - -SELECT max(student.gpa) AS max_3_7 FROM student; - - -SELECT count(four) AS cnt_1000 FROM onek; - - -SELECT newavg(four) AS avg_1 FROM onek; - -SELECT newsum(four) AS sum_1500 FROM onek; - -SELECT newcnt(four) AS cnt_1000 FROM onek; - - --- -- inheritance stress test -- SELECT * FROM a_star*; |