Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 254 insertions, 0 deletions

diff --git a/src/test/regress/expected/amutils.out b/src/test/regress/expected/amutils.out
new file mode 100644
index 0000000..7ab6113
--- /dev/null
+++ b/src/test/regress/expected/amutils.out
@@ -0,0 +1,254 @@
+--
+-- Test index AM property-reporting functions
+--
+select prop,
+       pg_indexam_has_property(a.oid, prop) as "AM",
+       pg_index_has_property('onek_hundred'::regclass, prop) as "Index",
+       pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as "Column"
+  from pg_am a,
+       unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
+                    'orderable', 'distance_orderable', 'returnable',
+                    'search_array', 'search_nulls',
+                    'clusterable', 'index_scan', 'bitmap_scan',
+                    'backward_scan',
+                    'can_order', 'can_unique', 'can_multi_col',
+                    'can_exclude', 'can_include',
+                    'bogus']::text[])
+         with ordinality as u(prop,ord)
+ where a.amname = 'btree'
+ order by ord;
+        prop        | AM | Index | Column 
+--------------------+----+-------+--------
+ asc                |    |       | t
+ desc               |    |       | f
+ nulls_first        |    |       | f
+ nulls_last         |    |       | t
+ orderable          |    |       | t
+ distance_orderable |    |       | f
+ returnable         |    |       | t
+ search_array       |    |       | t
+ search_nulls       |    |       | t
+ clusterable        |    | t     | 
+ index_scan         |    | t     | 
+ bitmap_scan        |    | t     | 
+ backward_scan      |    | t     | 
+ can_order          | t  |       | 
+ can_unique         | t  |       | 
+ can_multi_col      | t  |       | 
+ can_exclude        | t  |       | 
+ can_include        | t  |       | 
+ bogus              |    |       | 
+(19 rows)
+
+select prop,
+       pg_indexam_has_property(a.oid, prop) as "AM",
+       pg_index_has_property('gcircleind'::regclass, prop) as "Index",
+       pg_index_column_has_property('gcircleind'::regclass, 1, prop) as "Column"
+  from pg_am a,
+       unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
+                    'orderable', 'distance_orderable', 'returnable',
+                    'search_array', 'search_nulls',
+                    'clusterable', 'index_scan', 'bitmap_scan',
+                    'backward_scan',
+                    'can_order', 'can_unique', 'can_multi_col',
+                    'can_exclude', 'can_include',
+                    'bogus']::text[])
+         with ordinality as u(prop,ord)
+ where a.amname = 'gist'
+ order by ord;
+        prop        | AM | Index | Column 
+--------------------+----+-------+--------
+ asc                |    |       | f
+ desc               |    |       | f
+ nulls_first        |    |       | f
+ nulls_last         |    |       | f
+ orderable          |    |       | f
+ distance_orderable |    |       | t
+ returnable         |    |       | f
+ search_array       |    |       | f
+ search_nulls       |    |       | t
+ clusterable        |    | t     | 
+ index_scan         |    | t     | 
+ bitmap_scan        |    | t     | 
+ backward_scan      |    | f     | 
+ can_order          | f  |       | 
+ can_unique         | f  |       | 
+ can_multi_col      | t  |       | 
+ can_exclude        | t  |       | 
+ can_include        | t  |       | 
+ bogus              |    |       | 
+(19 rows)
+
+select prop,
+       pg_index_column_has_property('onek_hundred'::regclass, 1, prop) as btree,
+       pg_index_column_has_property('hash_i4_index'::regclass, 1, prop) as hash,
+       pg_index_column_has_property('gcircleind'::regclass, 1, prop) as gist,
+       pg_index_column_has_property('sp_radix_ind'::regclass, 1, prop) as spgist_radix,
+       pg_index_column_has_property('sp_quad_ind'::regclass, 1, prop) as spgist_quad,
+       pg_index_column_has_property('botharrayidx'::regclass, 1, prop) as gin,
+       pg_index_column_has_property('brinidx'::regclass, 1, prop) as brin
+  from unnest(array['asc', 'desc', 'nulls_first', 'nulls_last',
+                    'orderable', 'distance_orderable', 'returnable',
+                    'search_array', 'search_nulls',
+                    'bogus']::text[])
+         with ordinality as u(prop,ord)
+ order by ord;
+        prop        | btree | hash | gist | spgist_radix | spgist_quad | gin | brin 
+--------------------+-------+------+------+--------------+-------------+-----+------
+ asc                | t     | f    | f    | f            | f           | f   | f
+ desc               | f     | f    | f    | f            | f           | f   | f
+ nulls_first        | f     | f    | f    | f            | f           | f   | f
+ nulls_last         | t     | f    | f    | f            | f           | f   | f
+ orderable          | t     | f    | f    | f            | f           | f   | f
+ distance_orderable | f     | f    | t    | f            | t           | f   | f
+ returnable         | t     | f    | f    | t            | t           | f   | f
+ search_array       | t     | f    | f    | f            | f           | f   | f
+ search_nulls       | t     | f    | t    | t            | t           | f   | t
+ bogus              |       |      |      |              |             |     | 
+(10 rows)
+
+select prop,
+       pg_index_has_property('onek_hundred'::regclass, prop) as btree,
+       pg_index_has_property('hash_i4_index'::regclass, prop) as hash,
+       pg_index_has_property('gcircleind'::regclass, prop) as gist,
+       pg_index_has_property('sp_radix_ind'::regclass, prop) as spgist,
+       pg_index_has_property('botharrayidx'::regclass, prop) as gin,
+       pg_index_has_property('brinidx'::regclass, prop) as brin
+  from unnest(array['clusterable', 'index_scan', 'bitmap_scan',
+                    'backward_scan',
+                    'bogus']::text[])
+         with ordinality as u(prop,ord)
+ order by ord;
+     prop      | btree | hash | gist | spgist | gin | brin 
+---------------+-------+------+------+--------+-----+------
+ clusterable   | t     | f    | t    | f      | f   | f
+ index_scan    | t     | t    | t    | t      | f   | f
+ bitmap_scan   | t     | t    | t    | t      | t   | t
+ backward_scan | t     | t    | f    | f      | f   | f
+ bogus         |       |      |      |        |     | 
+(5 rows)
+
+select amname, prop, pg_indexam_has_property(a.oid, prop) as p
+  from pg_am a,
+       unnest(array['can_order', 'can_unique', 'can_multi_col',
+                    'can_exclude', 'can_include', 'bogus']::text[])
+         with ordinality as u(prop,ord)
+ where amtype = 'i'
+ order by amname, ord;
+ amname |     prop      | p 
+--------+---------------+---
+ brin   | can_order     | f
+ brin   | can_unique    | f
+ brin   | can_multi_col | t
+ brin   | can_exclude   | f
+ brin   | can_include   | f
+ brin   | bogus         | 
+ btree  | can_order     | t
+ btree  | can_unique    | t
+ btree  | can_multi_col | t
+ btree  | can_exclude   | t
+ btree  | can_include   | t
+ btree  | bogus         | 
+ gin    | can_order     | f
+ gin    | can_unique    | f
+ gin    | can_multi_col | t
+ gin    | can_exclude   | f
+ gin    | can_include   | f
+ gin    | bogus         | 
+ gist   | can_order     | f
+ gist   | can_unique    | f
+ gist   | can_multi_col | t
+ gist   | can_exclude   | t
+ gist   | can_include   | t
+ gist   | bogus         | 
+ hash   | can_order     | f
+ hash   | can_unique    | f
+ hash   | can_multi_col | f
+ hash   | can_exclude   | t
+ hash   | can_include   | f
+ hash   | bogus         | 
+ spgist | can_order     | f
+ spgist | can_unique    | f
+ spgist | can_multi_col | f
+ spgist | can_exclude   | t
+ spgist | can_include   | t
+ spgist | bogus         | 
+(36 rows)
+
+--
+-- additional checks for pg_index_column_has_property
+--
+CREATE TEMP TABLE foo (f1 int, f2 int, f3 int, f4 int);
+CREATE INDEX fooindex ON foo (f1 desc, f2 asc, f3 nulls first, f4 nulls last);
+select col, prop, pg_index_column_has_property(o, col, prop)
+  from (values ('fooindex'::regclass)) v1(o),
+       (values (1,'orderable'),(2,'asc'),(3,'desc'),
+               (4,'nulls_first'),(5,'nulls_last'),
+               (6, 'bogus')) v2(idx,prop),
+       generate_series(1,4) col
+ order by col, idx;
+ col |    prop     | pg_index_column_has_property 
+-----+-------------+------------------------------
+   1 | orderable   | t
+   1 | asc         | f
+   1 | desc        | t
+   1 | nulls_first | t
+   1 | nulls_last  | f
+   1 | bogus       | 
+   2 | orderable   | t
+   2 | asc         | t
+   2 | desc        | f
+   2 | nulls_first | f
+   2 | nulls_last  | t
+   2 | bogus       | 
+   3 | orderable   | t
+   3 | asc         | t
+   3 | desc        | f
+   3 | nulls_first | t
+   3 | nulls_last  | f
+   3 | bogus       | 
+   4 | orderable   | t
+   4 | asc         | t
+   4 | desc        | f
+   4 | nulls_first | f
+   4 | nulls_last  | t
+   4 | bogus       | 
+(24 rows)
+
+CREATE INDEX foocover ON foo (f1) INCLUDE (f2,f3);
+select col, prop, pg_index_column_has_property(o, col, prop)
+  from (values ('foocover'::regclass)) v1(o),
+       (values (1,'orderable'),(2,'asc'),(3,'desc'),
+               (4,'nulls_first'),(5,'nulls_last'),
+               (6,'distance_orderable'),(7,'returnable'),
+               (8, 'bogus')) v2(idx,prop),
+       generate_series(1,3) col
+ order by col, idx;
+ col |        prop        | pg_index_column_has_property 
+-----+--------------------+------------------------------
+   1 | orderable          | t
+   1 | asc                | t
+   1 | desc               | f
+   1 | nulls_first        | f
+   1 | nulls_last         | t
+   1 | distance_orderable | f
+   1 | returnable         | t
+   1 | bogus              | 
+   2 | orderable          | f
+   2 | asc                | 
+   2 | desc               | 
+   2 | nulls_first        | 
+   2 | nulls_last         | 
+   2 | distance_orderable | f
+   2 | returnable         | t
+   2 | bogus              | 
+   3 | orderable          | f
+   3 | asc                | 
+   3 | desc               | 
+   3 | nulls_first        | 
+   3 | nulls_last         | 
+   3 | distance_orderable | f
+   3 | returnable         | t
+   3 | bogus              | 
+(24 rows)
+