Support CREATE ACCESS METHOD

This enables external code to create access methods.  This is useful so
that extensions can add their own access methods which can be formally
tracked for dependencies, so that DROP operates correctly.  Also, having
explicit support makes pg_dump work correctly.

Currently only index AMs are supported, but we expect different types to
be added in the future.

Authors: Alexander Korotkov, Petr Jelínek
Reviewed-By: Teodor Sigaev, Petr Jelínek, Jim Nasby
Commitfest-URL: https://commitfest.postgresql.org/9/353/
Discussion: https://www.postgresql.org/message-id/CAPpHfdsXwZmojm6Dx+TJnpYk27kT4o7Ri6X_4OSWcByu1Rm+VA@mail.gmail.com

7 files changed, 196 insertions, 5 deletions

diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out
new file mode 100644
index 00000000000..47d6024610d
--- /dev/null
+++ b/src/test/regress/expected/create_am.out
@@ -0,0 +1,108 @@
+--
+-- Create access method tests
+--
+-- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
+CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
+-- Drop old index on fast_emp4000
+DROP INDEX grect2ind;
+-- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist
+CREATE INDEX grect2ind ON fast_emp4000 USING gist2 (home_base);
+ERROR:  data type box has no default operator class for access method "gist2"
+HINT:  You must specify an operator class for the index or define a default operator class for the data type.
+-- Make operator class for boxes using gist2
+CREATE OPERATOR CLASS box_ops DEFAULT
+	FOR TYPE box USING gist2 AS
+	OPERATOR 1	<<,
+	OPERATOR 2	&<,
+	OPERATOR 3	&&,
+	OPERATOR 4	&>,
+	OPERATOR 5	>>,
+	OPERATOR 6	~=,
+	OPERATOR 7	@>,
+	OPERATOR 8	<@,
+	OPERATOR 9	&<|,
+	OPERATOR 10	<<|,
+	OPERATOR 11	|>>,
+	OPERATOR 12	|&>,
+	OPERATOR 13	~,
+	OPERATOR 14	@,
+	FUNCTION 1	gist_box_consistent(internal, box, smallint, oid, internal),
+	FUNCTION 2	gist_box_union(internal, internal),
+	FUNCTION 3	gist_box_compress(internal),
+	FUNCTION 4	gist_box_decompress(internal),
+	FUNCTION 5	gist_box_penalty(internal, internal, internal),
+	FUNCTION 6	gist_box_picksplit(internal, internal),
+	FUNCTION 7	gist_box_same(box, box, internal),
+	FUNCTION 9	gist_box_fetch(internal);
+-- Create gist2 index on fast_emp4000
+CREATE INDEX grect2ind ON fast_emp4000 USING gist2 (home_base);
+-- Now check the results from plain indexscan
+SET enable_seqscan = OFF;
+SET enable_indexscan = ON;
+SET enable_bitmapscan = OFF;
+EXPLAIN (COSTS OFF)
+SELECT * FROM fast_emp4000
+    WHERE home_base @ '(200,200),(2000,1000)'::box
+    ORDER BY (home_base[0])[0];
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Sort
+   Sort Key: ((home_base[0])[0])
+   ->  Index Only Scan using grect2ind on fast_emp4000
+         Index Cond: (home_base @ '(2000,1000),(200,200)'::box)
+(4 rows)
+
+SELECT * FROM fast_emp4000
+    WHERE home_base @ '(200,200),(2000,1000)'::box
+    ORDER BY (home_base[0])[0];
+       home_base       
+-----------------------
+ (337,455),(240,359)
+ (1444,403),(1346,344)
+(2 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
+                         QUERY PLAN                          
+-------------------------------------------------------------
+ Aggregate
+   ->  Index Only Scan using grect2ind on fast_emp4000
+         Index Cond: (home_base && '(1000,1000),(0,0)'::box)
+(3 rows)
+
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
+ count 
+-------
+     2
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Aggregate
+   ->  Index Only Scan using grect2ind on fast_emp4000
+         Index Cond: (home_base IS NULL)
+(3 rows)
+
+SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
+ count 
+-------
+   278
+(1 row)
+
+-- Try to drop access method: fail because of depending objects
+DROP ACCESS METHOD gist2;
+ERROR:  cannot drop access method gist2 because other objects depend on it
+DETAIL:  operator class box_ops for access method gist2 depends on access method gist2
+index grect2ind depends on operator class box_ops for access method gist2
+HINT:  Use DROP ... CASCADE to drop the dependent objects too.
+-- Drop access method cascade
+DROP ACCESS METHOD gist2 CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to operator class box_ops for access method gist2
+drop cascades to index grect2ind
+-- Reset optimizer options
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 75751bebeff..5310c956feb 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -325,6 +325,10 @@ SELECT pg_get_object_address('event trigger', '{one}', '{}');
 ERROR:  event trigger "one" does not exist
 SELECT pg_get_object_address('event trigger', '{one,two}', '{}');
 ERROR:  event trigger name cannot be qualified
+SELECT pg_get_object_address('access method', '{one}', '{}');
+ERROR:  access method "one" does not exist
+SELECT pg_get_object_address('access method', '{one,two}', '{}');
+ERROR:  access method name cannot be qualified
 -- test successful cases
 WITH objects (type, name, args) AS (VALUES
 				('table', '{addr_nsp, gentable}'::text[], '{}'::text[]),
@@ -373,7 +377,8 @@ WITH objects (type, name, args) AS (VALUES
 				-- extension
 				-- event trigger
 				('policy', '{addr_nsp, gentable, genpol}', '{}'),
-				('transform', '{int}', '{sql}')
+				('transform', '{int}', '{sql}'),
+				('access method', '{btree}', '{}')
         )
 SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)).*,
 	-- test roundtrip through pg_identify_object_as_address
@@ -405,6 +410,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)).*,
  server                    |            | addr_fserv        | addr_fserv                                                           | t
  user mapping              |            |                   | regtest_addr_user on server integer                                  | t
  foreign-data wrapper      |            | addr_fdw          | addr_fdw                                                             | t
+ access method             |            | btree             | btree                                                                | t
  operator of access method |            |                   | operator 1 (integer, integer) of pg_catalog.integer_ops USING btree  | t
  function of access method |            |                   | function 2 (integer, integer) of pg_catalog.integer_ops USING btree  | t
  default value             |            |                   | for addr_nsp.gentable.b                                              | t
@@ -426,7 +432,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)).*,
  text search parser        | addr_nsp   | addr_ts_prs       | addr_nsp.addr_ts_prs                                                 | t
  text search configuration | addr_nsp   | addr_ts_conf      | addr_nsp.addr_ts_conf                                                | t
  text search template      | addr_nsp   | addr_ts_temp      | addr_nsp.addr_ts_temp                                                | t
-(41 rows)
+(42 rows)
 
 ---
 --- Cleanup resources
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index eb0bc88ef1f..2c5be4bae4b 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -44,7 +44,7 @@ e_star|f
 emp|f
 equipment_r|f
 f_star|f
-fast_emp4000|t
+fast_emp4000|f
 float4_tbl|f
 float8_tbl|f
 func_index_heap|t
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index bec03165a73..8be4b831a1e 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -60,7 +60,7 @@ test: create_index create_view
 # ----------
 # Another group of parallel tests
 # ----------
-test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes
+test: create_aggregate create_function_3 create_cast constraints triggers inherit create_table_like typed_table vacuum drop_if_exists updatable_views rolenames roleattributes create_am
 
 # ----------
 # sanity_check does a vacuum, affecting the sort order of SELECT *
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 7e9b319b0f7..1de3da8bac0 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -75,6 +75,7 @@ test: drop_if_exists
 test: updatable_views
 test: rolenames
 test: roleattributes
+test: create_am
 test: sanity_check
 test: errors
 test: select
diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql
new file mode 100644
index 00000000000..e2051c5fcd3
--- /dev/null
+++ b/src/test/regress/sql/create_am.sql
@@ -0,0 +1,73 @@
+--
+-- Create access method tests
+--
+
+-- Make gist2 over gisthandler. In fact, it would be a synonym to gist.
+CREATE ACCESS METHOD gist2 TYPE INDEX HANDLER gisthandler;
+
+-- Drop old index on fast_emp4000
+DROP INDEX grect2ind;
+
+-- Try to create gist2 index on fast_emp4000: fail because opclass doesn't exist
+CREATE INDEX grect2ind ON fast_emp4000 USING gist2 (home_base);
+
+-- Make operator class for boxes using gist2
+CREATE OPERATOR CLASS box_ops DEFAULT
+	FOR TYPE box USING gist2 AS
+	OPERATOR 1	<<,
+	OPERATOR 2	&<,
+	OPERATOR 3	&&,
+	OPERATOR 4	&>,
+	OPERATOR 5	>>,
+	OPERATOR 6	~=,
+	OPERATOR 7	@>,
+	OPERATOR 8	<@,
+	OPERATOR 9	&<|,
+	OPERATOR 10	<<|,
+	OPERATOR 11	|>>,
+	OPERATOR 12	|&>,
+	OPERATOR 13	~,
+	OPERATOR 14	@,
+	FUNCTION 1	gist_box_consistent(internal, box, smallint, oid, internal),
+	FUNCTION 2	gist_box_union(internal, internal),
+	FUNCTION 3	gist_box_compress(internal),
+	FUNCTION 4	gist_box_decompress(internal),
+	FUNCTION 5	gist_box_penalty(internal, internal, internal),
+	FUNCTION 6	gist_box_picksplit(internal, internal),
+	FUNCTION 7	gist_box_same(box, box, internal),
+	FUNCTION 9	gist_box_fetch(internal);
+
+-- Create gist2 index on fast_emp4000
+CREATE INDEX grect2ind ON fast_emp4000 USING gist2 (home_base);
+
+-- Now check the results from plain indexscan
+SET enable_seqscan = OFF;
+SET enable_indexscan = ON;
+SET enable_bitmapscan = OFF;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM fast_emp4000
+    WHERE home_base @ '(200,200),(2000,1000)'::box
+    ORDER BY (home_base[0])[0];
+SELECT * FROM fast_emp4000
+    WHERE home_base @ '(200,200),(2000,1000)'::box
+    ORDER BY (home_base[0])[0];
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
+SELECT count(*) FROM fast_emp4000 WHERE home_base && '(1000,1000,0,0)'::box;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
+SELECT count(*) FROM fast_emp4000 WHERE home_base IS NULL;
+
+-- Try to drop access method: fail because of depending objects
+DROP ACCESS METHOD gist2;
+
+-- Drop access method cascade
+DROP ACCESS METHOD gist2 CASCADE;
+
+-- Reset optimizer options
+RESET enable_seqscan;
+RESET enable_indexscan;
+RESET enable_bitmapscan;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 68e7cb0eb3a..b5662349bcd 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -117,6 +117,8 @@ SELECT pg_get_object_address('extension', '{one}', '{}');
 SELECT pg_get_object_address('extension', '{one,two}', '{}');
 SELECT pg_get_object_address('event trigger', '{one}', '{}');
 SELECT pg_get_object_address('event trigger', '{one,two}', '{}');
+SELECT pg_get_object_address('access method', '{one}', '{}');
+SELECT pg_get_object_address('access method', '{one,two}', '{}');
 
 -- test successful cases
 WITH objects (type, name, args) AS (VALUES
@@ -166,7 +168,8 @@ WITH objects (type, name, args) AS (VALUES
 				-- extension
 				-- event trigger
 				('policy', '{addr_nsp, gentable, genpol}', '{}'),
-				('transform', '{int}', '{sql}')
+				('transform', '{int}', '{sql}'),
+				('access method', '{btree}', '{}')
         )
 SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.subobjid)).*,
 	-- test roundtrip through pg_identify_object_as_address