--
-- MISC
--
--
-- BTREE
--
UPDATE onek
SET unique1 = onek.unique1 + 1;
UPDATE onek
SET unique1 = onek.unique1 - 1;
--
-- BTREE partial
--
-- UPDATE onek2
-- SET unique1 = onek2.unique1 + 1;
--UPDATE onek2
-- SET unique1 = onek2.unique1 - 1;
--
-- BTREE shutting out non-functional updates
--
-- the following two tests seem to take a long time on some
-- systems. This non-func update stuff needs to be examined
-- more closely. - jolly (2/22/96)
--
UPDATE tmp
SET stringu1 = reverse_name(onek.stringu1)
FROM onek
WHERE onek.stringu1 = 'JBAAAA' and
onek.stringu1 = tmp.stringu1;
UPDATE tmp
SET stringu1 = reverse_name(onek2.stringu1)
FROM onek2
WHERE onek2.stringu1 = 'JCAAAA' and
onek2.stringu1 = tmp.stringu1;
DROP TABLE tmp;
--UPDATE person*
-- SET age = age + 1;
--UPDATE person*
-- SET age = age + 3
-- WHERE name = 'linda';
--
-- copy
--
COPY onek TO '@abs_builddir@/results/onek.data';
DELETE FROM onek;
COPY onek FROM '@abs_builddir@/results/onek.data';
SELECT unique1 FROM onek WHERE unique1 < 2 ORDER BY unique1;
unique1
---------
0
1
(2 rows)
DELETE FROM onek2;
COPY onek2 FROM '@abs_builddir@/results/onek.data';
SELECT unique1 FROM onek2 WHERE unique1 < 2 ORDER BY unique1;
unique1
---------
0
1
(2 rows)
COPY BINARY stud_emp TO '@abs_builddir@/results/stud_emp.data';
DELETE FROM stud_emp;
COPY BINARY stud_emp FROM '@abs_builddir@/results/stud_emp.data';
SELECT * FROM stud_emp ORDER BY 1,2;
name | age | location | salary | manager | gpa | percent
-------+-----+------------+--------+---------+-----+---------
cim | 30 | (10.5,4.7) | 400 | | 3.4 |
jeff | 23 | (8,7.7) | 600 | sharon | 3.5 |
linda | 19 | (0.9,6.1) | 100 | | 2.9 |
(3 rows)
-- COPY aggtest FROM stdin;
-- 56 7.8
-- 100 99.097
-- 0 0.09561
-- 42 324.78
-- .
-- COPY aggtest TO stdout;
--
-- inheritance stress test
--
SELECT * FROM a_star* ORDER BY 1,2;
class | a
-------+---
a | 1
a | 2
a |
(3 rows)
SELECT *
FROM b_star* x
WHERE x.b = text 'bumble' or x.a < 3;
b | class | a
--------+-------+---
bumble | b |
(1 row)
SELECT class, a
FROM c_star* x
WHERE x.c ~ text 'hi' ORDER BY 1,2;
class | a
-------+---
c | 5
c |
(2 rows)
SELECT class, b, c
FROM d_star* x
WHERE x.a < 100 ORDER BY 1,2,3;
class | b | c
-------+---------+------------
d | fumble |
d | grumble | hi sunita
d | rumble |
d | stumble | hi koko
d | | hi avi
d | | hi kristin
d | |
d | |
(8 rows)
SELECT class, c FROM e_star* x WHERE x.c NOTNULL ORDER BY 1,2;
class | c
-------+-------------
e | hi bob
e | hi carol
e | hi elisa
e | hi michelle
(4 rows)
SELECT * FROM f_star* x WHERE x.c ISNULL ORDER BY 1,2;
ERROR: could not identify an ordering operator for type polygon
LINE 1: SELECT * FROM f_star* x WHERE x.c ISNULL ORDER BY 1,2;
^
HINT: Use an explicit ordering operator or modify the query.
-- grouping and aggregation on inherited sets have been busted in the past...
SELECT sum(a) FROM a_star*;
sum
-----
3
(1 row)
SELECT class, sum(a) FROM a_star* GROUP BY class ORDER BY class;
class | sum
-------+-----
a | 3
(1 row)
ALTER TABLE f_star RENAME COLUMN f TO ff;
ALTER TABLE e_star* RENAME COLUMN e TO ee;
ALTER TABLE d_star* RENAME COLUMN d TO dd;
ALTER TABLE c_star* RENAME COLUMN c TO cc;
ALTER TABLE b_star* RENAME COLUMN b TO bb;
ALTER TABLE a_star* RENAME COLUMN a TO aa;
SELECT class, aa
FROM a_star* x
WHERE aa ISNULL ORDER BY 1,2;
class | aa
-------+----
a |
(1 row)
-- As of Postgres 7.1, ALTER implicitly recurses,
-- so this should be same as ALTER a_star*
ALTER TABLE a_star RENAME COLUMN aa TO foo;
SELECT class, foo
FROM a_star* x
WHERE x.foo >= 2 ORDER BY 1,2;
class | foo
-------+-----
a | 2
(1 row)
ALTER TABLE a_star RENAME COLUMN foo TO aa;
SELECT *
from a_star*
WHERE aa < 1000 ORDER BY 1,2;
class | aa
-------+----
a | 1
a | 2
(2 rows)
ALTER TABLE f_star ADD COLUMN f int4;
UPDATE f_star SET f = 10;
ALTER TABLE e_star* ADD COLUMN e int4;
--UPDATE e_star* SET e = 42;
SELECT * FROM e_star* ORDER BY 1,2,3,4;
ee | c | class | a | e
----+-------------+-------+----+---
-4 | | e | |
-3 | hi michelle | e | |
-2 | | e | 17 |
-1 | hi carol | e | 15 |
| hi bob | e | 16 |
| hi elisa | e | |
| | e | 18 |
(7 rows)
ALTER TABLE a_star* ADD COLUMN a text;
-- That ALTER TABLE should have added TOAST tables.
SELECT relname, reltoastrelid <> 0 AS has_toast_table
FROM pg_class
WHERE oid::regclass IN ('a_star', 'c_star')
ORDER BY 1;
relname | has_toast_table
---------+-----------------
a_star | t
c_star | f
(2 rows)
--UPDATE b_star*
-- SET a = text 'gazpacho'
-- WHERE aa > 4;
SELECT class, aa, a FROM a_star* ORDER BY 1,2;
class | aa | a
-------+----+---
a | 1 |
a | 2 |
a | |
(3 rows)
--
-- versions
--
--
-- postquel functions
--
--
-- mike does post_hacking,
-- joe and sally play basketball, and
-- everyone else does nothing.
--
SELECT p.name, name(p.hobbies) FROM ONLY person p ORDER BY 1,2;
name | name
-------+-------------
joe | basketball
mike | posthacking
sally | basketball
(3 rows)
--
-- as above, but jeff also does post_hacking.
--
SELECT p.name, name(p.hobbies) FROM person* p ORDER BY 1,2;
name | name
-------+-------------
joe | basketball
mike | posthacking
sally | basketball
(3 rows)
--
-- the next two queries demonstrate how functions generate bogus duplicates.
-- this is a "feature" ..
--
SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
ORDER BY 1,2;
name | name
-------------+---------------
basketball | hightops
posthacking | advil
posthacking | peet's coffee
skywalking | guts
(4 rows)
SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r ORDER BY 1,2;
name | name
-------------+---------------
basketball | hightops
basketball | hightops
posthacking | advil
posthacking | peet's coffee
skywalking | guts
(5 rows)
--
-- mike needs advil and peet's coffee,
-- joe and sally need hightops, and
-- everyone else is fine.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p ORDER BY 1,2,3;
name | name | name
-------+-------------+---------------
joe | basketball | hightops
mike | posthacking | advil
mike | posthacking | peet's coffee
sally | basketball | hightops
(4 rows)
--
-- as above, but jeff needs advil and peet's coffee as well.
--
SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p ORDER BY 1,2,3;
name | name | name
-------+-------------+---------------
joe | basketball | hightops
mike | posthacking | advil
mike | posthacking | peet's coffee
sally | basketball | hightops
(4 rows)
--
-- just like the last two, but make sure that the target list fixup and
-- unflattening is being done correctly.
--
SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p ORDER BY 1,2,3;
name | name | name
---------------+-------+-------------
advil | mike | posthacking
hightops | joe | basketball
hightops | sally | basketball
peet's coffee | mike | posthacking
(4 rows)
SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p ORDER BY 1,2,3;
name | name | name
---------------+-------+-------------
advil | mike | posthacking
hightops | joe | basketball
hightops | sally | basketball
peet's coffee | mike | posthacking
(4 rows)
SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p ORDER BY 1,2,3;
name | name | name
---------------+-------------+-------
advil | posthacking | mike
hightops | basketball | joe
hightops | basketball | sally
peet's coffee | posthacking | mike
(4 rows)
SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p ORDER BY 1,2,3;
name | name | name
---------------+-------------+-------
advil | posthacking | mike
hightops | basketball | joe
hightops | basketball | sally
peet's coffee | posthacking | mike
(4 rows)
SELECT user_relns() AS user_relns
ORDER BY user_relns;
user_relns
----------------------------
a_star
aggtest
array_index_op_test
array_op_test
autovacuum_partition_table
b_star
base_tab_000
base_type_tab_000
bprime
bt_f8_heap
bt_i4_heap
bt_name_heap
bt_txt_heap
c_star
d_star
dept
e_star
emp
equipment_r
f_star
fast_emp4000
hash_f8_heap
hash_i4_heap
hash_name_heap
hash_txt_heap
hobbies_r
ihighway
int8_tbl
iportaltest
onek
onek2
person
pg_cudesc_17913
pg_cudesc_part_17759
pg_cudesc_part_17760
pg_cudesc_part_17761
pg_cudesc_part_17762
pg_cudesc_part_17763
pg_cudesc_part_17764
pg_cudesc_part_17765
pg_cudesc_part_17766
pg_cudesc_part_17767
pg_cudesc_part_17768
pg_cudesc_part_17769
pg_delta_17913
pg_delta_part_17759
pg_delta_part_17760
pg_delta_part_17761
pg_delta_part_17762
pg_delta_part_17763
pg_delta_part_17764
pg_delta_part_17765
pg_delta_part_17766
pg_delta_part_17767
pg_delta_part_17768
pg_delta_part_17769
ramp
real_city
road
shighway
slow_emp4000
src
stud_emp
student
tenk1
tenk2
test_tsvector
tmp_asset_max_belong
tmp_cust_asset_sum_1
toyemp
(70 rows)
SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
name
------
guts
(1 row)
SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
name
------
guts
(1 row)
SELECT name(equipment_named_ambiguous_2b(text 'skywalking')) ORDER BY 1;
name
---------------
advil
guts
hightops
peet's coffee
(4 rows)
SELECT person as hobbies_by_name from hobbies_r where name = 'basketball' order by 1;
hobbies_by_name
-----------------
joe
sally
(2 rows)
SELECT name, overpaid(emp.*) FROM emp ORDER BY 1,2;
name | overpaid
--------+----------
bill | t
sam | t
sharon | t
(3 rows)
--
-- Try a few cases with SQL-spec row constructor expressions
--
SELECT * FROM equipment(ROW('skywalking', 'mer'));
name | hobby
------+------------
guts | skywalking
(1 row)
SELECT name(equipment(ROW('skywalking', 'mer')));
name
------
guts
(1 row)
SELECT *, name(equipment(h.*)) FROM hobbies_r h ORDER BY 1,2,3;
name | person | name
-------------+--------+---------------
basketball | joe | hightops
basketball | sally | hightops
posthacking | mike | advil
posthacking | mike | peet's coffee
skywalking | | guts
(5 rows)
SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h ORDER BY 1,2,3;
name | person | name
-------------+--------+---------------
basketball | joe | hightops
basketball | sally | hightops
posthacking | mike | advil
posthacking | mike | peet's coffee
skywalking | | guts
(5 rows)
--
-- check that old-style C functions work properly with TOASTed values
--
create table oldstyle_test(i int4, t text);
insert into oldstyle_test values(null,null);
insert into oldstyle_test values(0,'12');
insert into oldstyle_test values(1000,'12');
insert into oldstyle_test values(0, repeat('x', 50000));
select i, length(t), octet_length(t), oldstyle_length(i,t) from oldstyle_test ORDER BY 1,2,3;
i | length | octet_length | oldstyle_length
------+--------+--------------+-----------------
0 | 2 | 2 | 822333
0 | 50000 | 50000 | 581
1000 | 2 | 2 | 823333
| | |
(4 rows)
drop table oldstyle_test;
select pg_terminate_backend(9999999999);
WARNING: PID 9999999999 is not a gaussdb server thread
CONTEXT: referenced column: pg_terminate_backend
pg_terminate_backend
----------------------
f
(1 row)
select pg_terminate_session(0, 0);
WARNING: PID 0 is not a gaussdb server thread
CONTEXT: referenced column: pg_terminate_session
pg_terminate_session
----------------------
f
(1 row)
select pg_terminate_session(9999999999, 99999999999998);
pg_terminate_session
----------------------
f
(1 row)
select pg_terminate_session(9999999999, 0);
ERROR: Invalid session id
CONTEXT: referenced column: pg_terminate_session
select pg_partition_filenode(9999999999);
ERROR: OID out of range
CONTEXT: referenced column: pg_partition_filenode
--
-- functional joins
--
--
-- instance rules
--
--
-- rewrite rules
--
---
--- clean connection
---
create database clcodb1;
\c clcodb1;
create user clcodb1zz with sysadmin identified by '1234@abcd';
create table t1(id int);
insert into t1 values (0),(1),(2),(3);
create database clcodb2;
\c clcodb2;
create table t1(id int);
insert into t1 values (0),(1),(2),(3);
\c regression;
\! @gsqldir@/gsql -dclcodb1 -Uclcodb1zz -W1234@abcd -p @portstring@ -c 'select * from t1; select pg_sleep(10)' 2>&1 1>/dev/null &
\! @gsqldir@/gsql -dclcodb2 -p @portstring@ -c 'select * from t1; select pg_sleep(5)' 2>&1 1>/dev/null &
\! @gsqldir@/gsql -dclcodb1 -p @portstring@ -c 'select * from t1; select pg_sleep(5)' 2>&1 1>/dev/null &
select pg_sleep(2);
pg_sleep
----------
(1 row)
clean connection to all force for database clcodb1 to user clcodb1zz;
ERROR: Un-support feature
DETAIL: The distributed capability is not supported currently.
select pg_sleep(8);
pg_sleep
----------
(1 row)
drop database clcodb1;
drop database clcodb2;
drop user clcodb1zz;