diff options
Diffstat (limited to 'src/test/modules/spgist_name_ops/expected/spgist_name_ops.out')
| -rw-r--r-- | src/test/modules/spgist_name_ops/expected/spgist_name_ops.out | 120 |
1 files changed, 120 insertions, 0 deletions
diff --git a/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out b/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out new file mode 100644 index 0000000..1ee65ed --- /dev/null +++ b/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out @@ -0,0 +1,120 @@ +create extension spgist_name_ops; +select opcname, amvalidate(opc.oid) +from pg_opclass opc join pg_am am on am.oid = opcmethod +where amname = 'spgist' and opcname = 'name_ops'; + opcname | amvalidate +----------+------------ + name_ops | t +(1 row) + +-- warning expected here +select opcname, amvalidate(opc.oid) +from pg_opclass opc join pg_am am on am.oid = opcmethod +where amname = 'spgist' and opcname = 'name_ops_old'; +INFO: SP-GiST leaf data type text does not match declared type name + opcname | amvalidate +--------------+------------ + name_ops_old | f +(1 row) + +create table t(f1 name, f2 integer, f3 text); +create index on t using spgist(f1) include(f2, f3); +\d+ t_f1_f2_f3_idx + Index "public.t_f1_f2_f3_idx" + Column | Type | Key? | Definition | Storage | Stats target +--------+---------+------+------------+----------+-------------- + f1 | text | yes | f1 | extended | + f2 | integer | no | f2 | plain | + f3 | text | no | f3 | extended | +spgist, for table "public.t" + +insert into t select + proname, + case when length(proname) % 2 = 0 then pronargs else null end, + prosrc from pg_proc; +vacuum analyze t; +explain (costs off) +select * from t + where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p' + order by 1; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Sort + Sort Key: f1 + -> Index Only Scan using t_f1_f2_f3_idx on t + Index Cond: ((f1 > 'binary_upgrade_set_n'::name) AND (f1 < 'binary_upgrade_set_p'::name)) +(4 rows) + +select * from t + where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p' + order by 1; + f1 | f2 | f3 +------------------------------------------------------+----+------------------------------------------------------ + binary_upgrade_set_next_array_pg_type_oid | | binary_upgrade_set_next_array_pg_type_oid + binary_upgrade_set_next_heap_pg_class_oid | | binary_upgrade_set_next_heap_pg_class_oid + binary_upgrade_set_next_heap_relfilenode | 1 | binary_upgrade_set_next_heap_relfilenode + binary_upgrade_set_next_index_pg_class_oid | 1 | binary_upgrade_set_next_index_pg_class_oid + binary_upgrade_set_next_index_relfilenode | | binary_upgrade_set_next_index_relfilenode + binary_upgrade_set_next_multirange_array_pg_type_oid | 1 | binary_upgrade_set_next_multirange_array_pg_type_oid + binary_upgrade_set_next_multirange_pg_type_oid | 1 | binary_upgrade_set_next_multirange_pg_type_oid + binary_upgrade_set_next_pg_authid_oid | | binary_upgrade_set_next_pg_authid_oid + binary_upgrade_set_next_pg_enum_oid | | binary_upgrade_set_next_pg_enum_oid + binary_upgrade_set_next_pg_tablespace_oid | | binary_upgrade_set_next_pg_tablespace_oid + binary_upgrade_set_next_pg_type_oid | | binary_upgrade_set_next_pg_type_oid + binary_upgrade_set_next_toast_pg_class_oid | 1 | binary_upgrade_set_next_toast_pg_class_oid + binary_upgrade_set_next_toast_relfilenode | | binary_upgrade_set_next_toast_relfilenode +(13 rows) + +-- Verify clean failure when INCLUDE'd columns result in overlength tuple +-- The error message details are platform-dependent, so show only SQLSTATE +\set VERBOSITY sqlstate +insert into t values(repeat('xyzzy', 12), 42, repeat('xyzzy', 4000)); +ERROR: 54000 +\set VERBOSITY default +drop index t_f1_f2_f3_idx; +create index on t using spgist(f1 name_ops_old) include(f2, f3); +\d+ t_f1_f2_f3_idx + Index "public.t_f1_f2_f3_idx" + Column | Type | Key? | Definition | Storage | Stats target +--------+---------+------+------------+----------+-------------- + f1 | name | yes | f1 | plain | + f2 | integer | no | f2 | plain | + f3 | text | no | f3 | extended | +spgist, for table "public.t" + +explain (costs off) +select * from t + where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p' + order by 1; + QUERY PLAN +--------------------------------------------------------------------------------------------------- + Sort + Sort Key: f1 + -> Index Only Scan using t_f1_f2_f3_idx on t + Index Cond: ((f1 > 'binary_upgrade_set_n'::name) AND (f1 < 'binary_upgrade_set_p'::name)) +(4 rows) + +select * from t + where f1 > 'binary_upgrade_set_n' and f1 < 'binary_upgrade_set_p' + order by 1; + f1 | f2 | f3 +------------------------------------------------------+----+------------------------------------------------------ + binary_upgrade_set_next_array_pg_type_oid | | binary_upgrade_set_next_array_pg_type_oid + binary_upgrade_set_next_heap_pg_class_oid | | binary_upgrade_set_next_heap_pg_class_oid + binary_upgrade_set_next_heap_relfilenode | 1 | binary_upgrade_set_next_heap_relfilenode + binary_upgrade_set_next_index_pg_class_oid | 1 | binary_upgrade_set_next_index_pg_class_oid + binary_upgrade_set_next_index_relfilenode | | binary_upgrade_set_next_index_relfilenode + binary_upgrade_set_next_multirange_array_pg_type_oid | 1 | binary_upgrade_set_next_multirange_array_pg_type_oid + binary_upgrade_set_next_multirange_pg_type_oid | 1 | binary_upgrade_set_next_multirange_pg_type_oid + binary_upgrade_set_next_pg_authid_oid | | binary_upgrade_set_next_pg_authid_oid + binary_upgrade_set_next_pg_enum_oid | | binary_upgrade_set_next_pg_enum_oid + binary_upgrade_set_next_pg_tablespace_oid | | binary_upgrade_set_next_pg_tablespace_oid + binary_upgrade_set_next_pg_type_oid | | binary_upgrade_set_next_pg_type_oid + binary_upgrade_set_next_toast_pg_class_oid | 1 | binary_upgrade_set_next_toast_pg_class_oid + binary_upgrade_set_next_toast_relfilenode | | binary_upgrade_set_next_toast_relfilenode +(13 rows) + +\set VERBOSITY sqlstate +insert into t values(repeat('xyzzy', 12), 42, repeat('xyzzy', 4000)); +ERROR: 54000 +\set VERBOSITY default |