diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/create_am.out | 164 | ||||
| -rw-r--r-- | src/test/regress/expected/opr_sanity.out | 19 | ||||
| -rw-r--r-- | src/test/regress/expected/psql.out | 39 | ||||
| -rw-r--r-- | src/test/regress/expected/sanity_check.out | 7 | ||||
| -rw-r--r-- | src/test/regress/expected/type_sanity.out | 15 | ||||
| -rw-r--r-- | src/test/regress/pg_regress_main.c | 7 | ||||
| -rw-r--r-- | src/test/regress/sql/create_am.sql | 116 | ||||
| -rw-r--r-- | src/test/regress/sql/opr_sanity.sql | 16 | ||||
| -rw-r--r-- | src/test/regress/sql/psql.sql | 15 | ||||
| -rw-r--r-- | src/test/regress/sql/type_sanity.sql | 11 |
10 files changed, 396 insertions, 13 deletions
diff --git a/src/test/regress/expected/create_am.out b/src/test/regress/expected/create_am.out index 47dd885c4e..8a63cedb56 100644 --- a/src/test/regress/expected/create_am.out +++ b/src/test/regress/expected/create_am.out @@ -99,3 +99,167 @@ HINT: Use DROP ... CASCADE to drop the dependent objects too. -- Drop access method cascade DROP ACCESS METHOD gist2 CASCADE; NOTICE: drop cascades to index grect2ind2 +-- +-- Test table access methods +-- +-- Create a heap2 table am handler with heapam handler +CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler; +SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2; + amname | amhandler | amtype +--------+----------------------+-------- + heap | heap_tableam_handler | t + heap2 | heap_tableam_handler | t +(2 rows) + +-- First create tables employing the new AM using USING +-- plain CREATE TABLE +CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2; +INSERT INTO tableam_tbl_heap2 VALUES(1); +SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; + f1 +---- + 1 +(1 row) + +-- CREATE TABLE AS +CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; +SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; + f1 +---- + 1 +(1 row) + +-- SELECT INTO doesn't support USING +SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2; +ERROR: syntax error at or near "USING" +LINE 1: SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tab... + ^ +-- CREATE VIEW doesn't support USING +CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; +ERROR: syntax error at or near "USING" +LINE 1: CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM ... + ^ +-- CREATE SEQUENCE doesn't support USING +CREATE SEQUENCE tableam_seq_heap2 USING heap2; +ERROR: syntax error at or near "USING" +LINE 1: CREATE SEQUENCE tableam_seq_heap2 USING heap2; + ^ +-- CREATE MATERIALIZED VIEW does support USING +CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; +SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; + f1 +---- + 1 +(1 row) + +-- CREATE TABLE .. PARTITION BY doesn't not support USING +CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2; +ERROR: specifying a table access method is not supported on a partitioned table +CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); +-- new partitions will inherit from the current default, rather the partition root +SET default_table_access_method = 'heap'; +CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a'); +SET default_table_access_method = 'heap2'; +CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b'); +RESET default_table_access_method; +-- but the method can be explicitly specified +CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap; +CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2; +-- List all objects in AM +SELECT + pc.relkind, + pa.amname, + CASE WHEN relkind = 't' THEN + (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) + ELSE + relname::regclass::text + END AS relname +FROM pg_class AS pc, + pg_am AS pa +WHERE pa.oid = pc.relam + AND pa.amname = 'heap2' +ORDER BY 3, 1, 2; + relkind | amname | relname +---------+--------+---------------------------------- + r | heap2 | tableam_parted_b_heap2 + r | heap2 | tableam_parted_d_heap2 + r | heap2 | tableam_tblas_heap2 + r | heap2 | tableam_tbl_heap2 + m | heap2 | tableam_tblmv_heap2 + t | heap2 | toast for tableam_parted_b_heap2 + t | heap2 | toast for tableam_parted_d_heap2 +(7 rows) + +-- Show dependencies onto AM - there shouldn't be any for toast +SELECT pg_describe_object(classid,objid,objsubid) AS obj +FROM pg_depend, pg_am +WHERE pg_depend.refclassid = 'pg_am'::regclass + AND pg_am.oid = pg_depend.refobjid + AND pg_am.amname = 'heap2' +ORDER BY classid, objid, objsubid; + obj +--------------------------------------- + table tableam_tbl_heap2 + table tableam_tblas_heap2 + materialized view tableam_tblmv_heap2 + table tableam_parted_b_heap2 + table tableam_parted_d_heap2 +(5 rows) + +-- Second, create objects in the new AM by changing the default AM +BEGIN; +SET LOCAL default_table_access_method = 'heap2'; +-- following tests should all respect the default AM +CREATE TABLE tableam_tbl_heapx(f1 int); +CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx; +SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx; +CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx; +CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a); +CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b'); +-- but an explicitly set AM overrides it +CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap; +-- sequences, views and foreign servers shouldn't have an AM +CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx; +CREATE SEQUENCE tableam_seq_heapx; +CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator; +CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ; +CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2; +-- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws +SELECT + pc.relkind, + pa.amname, + CASE WHEN relkind = 't' THEN + (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) + ELSE + relname::regclass::text + END AS relname +FROM pg_class AS pc + LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam) +WHERE pc.relname LIKE 'tableam_%_heapx' +ORDER BY 3, 1, 2; + relkind | amname | relname +---------+--------+----------------------------- + f | | tableam_fdw_heapx + r | heap2 | tableam_parted_1_heapx + r | heap | tableam_parted_2_heapx + p | | tableam_parted_heapx + S | | tableam_seq_heapx + r | heap2 | tableam_tblas_heapx + r | heap2 | tableam_tbl_heapx + m | heap2 | tableam_tblmv_heapx + r | heap2 | tableam_tblselectinto_heapx + v | | tableam_view_heapx +(10 rows) + +-- don't want to keep those tables, nor the default +ROLLBACK; +-- Drop table access method, which fails as objects depends on it +DROP ACCESS METHOD heap2; +ERROR: cannot drop access method heap2 because other objects depend on it +DETAIL: table tableam_tbl_heap2 depends on access method heap2 +table tableam_tblas_heap2 depends on access method heap2 +materialized view tableam_tblmv_heap2 depends on access method heap2 +table tableam_parted_b_heap2 depends on access method heap2 +table tableam_parted_d_heap2 depends on access method heap2 +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- we intentionally leave the objects created above alive, to verify pg_dump support diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out index ce25ee044a..49a0acc0ee 100644 --- a/src/test/regress/expected/opr_sanity.out +++ b/src/test/regress/expected/opr_sanity.out @@ -1802,11 +1802,24 @@ WHERE p1.amhandler = 0; -----+-------- (0 rows) --- Check for amhandler functions with the wrong signature +-- Check for index amhandler functions with the wrong signature SELECT p1.oid, p1.amname, p2.oid, p2.proname FROM pg_am AS p1, pg_proc AS p2 -WHERE p2.oid = p1.amhandler AND - (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset +WHERE p2.oid = p1.amhandler AND p1.amtype = 'i' AND + (p2.prorettype != 'index_am_handler'::regtype + OR p2.proretset + OR p2.pronargs != 1 + OR p2.proargtypes[0] != 'internal'::regtype); + oid | amname | oid | proname +-----+--------+-----+--------- +(0 rows) + +-- Check for table amhandler functions with the wrong signature +SELECT p1.oid, p1.amname, p2.oid, p2.proname +FROM pg_am AS p1, pg_proc AS p2 +WHERE p2.oid = p1.amhandler AND p1.amtype = 's' AND + (p2.prorettype != 'table_am_handler'::regtype + OR p2.proretset OR p2.pronargs != 1 OR p2.proargtypes[0] != 'internal'::regtype); oid | amname | oid | proname diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out index 775b127121..aa101de906 100644 --- a/src/test/regress/expected/psql.out +++ b/src/test/regress/expected/psql.out @@ -2773,6 +2773,45 @@ Argument data types | numeric Type | func \pset tuples_only false +-- check conditional tableam display +-- Create a heap2 table am handler with heapam handler +CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler; +CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql; +CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap; +\d+ tbl_heap_psql + Table "public.tbl_heap_psql" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+----------+--------------+------------- + f1 | integer | | | | plain | | + f2 | character(100) | | | | extended | | + +\d+ tbl_heap + Table "public.tbl_heap" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+----------+--------------+------------- + f1 | integer | | | | plain | | + f2 | character(100) | | | | extended | | + +\set HIDE_TABLEAM off +\d+ tbl_heap_psql + Table "public.tbl_heap_psql" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+----------+--------------+------------- + f1 | integer | | | | plain | | + f2 | character(100) | | | | extended | | +Access method: heap_psql + +\d+ tbl_heap + Table "public.tbl_heap" + Column | Type | Collation | Nullable | Default | Storage | Stats target | Description +--------+----------------+-----------+----------+---------+----------+--------------+------------- + f1 | integer | | | | plain | | + f2 | character(100) | | | | extended | | +Access method: heap + +\set HIDE_TABLEAM on +DROP TABLE tbl_heap, tbl_heap_psql; +DROP ACCESS METHOD heap_psql; -- test numericlocale (as best we can without control of psql's locale) \pset format aligned \pset expanded off diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index 6cd937eb52..aaaa488b3c 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -186,6 +186,13 @@ sql_sizing|f sql_sizing_profiles|f stud_emp|f student|f +tableam_parted_a_heap2|f +tableam_parted_b_heap2|f +tableam_parted_c_heap2|f +tableam_parted_d_heap2|f +tableam_parted_heap2|f +tableam_tbl_heap2|f +tableam_tblas_heap2|f tbl_include_box|t tbl_include_box_pk|f tbl_include_pk|t diff --git a/src/test/regress/expected/type_sanity.out b/src/test/regress/expected/type_sanity.out index b1419d4bc2..91f17013d6 100644 --- a/src/test/regress/expected/type_sanity.out +++ b/src/test/regress/expected/type_sanity.out @@ -502,11 +502,20 @@ WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR -----+--------- (0 rows) --- Indexes should have an access method, others not. +-- All tables and indexes should have an access method. SELECT p1.oid, p1.relname FROM pg_class as p1 -WHERE (p1.relkind = 'i' AND p1.relam = 0) OR - (p1.relkind != 'i' AND p1.relam != 0); +WHERE p1.relkind NOT IN ('S', 'v', 'f', 'c') and + p1.relam = 0; + oid | relname +-----+--------- +(0 rows) + +-- Conversely, sequences, views, types shouldn't have them +SELECT p1.oid, p1.relname +FROM pg_class as p1 +WHERE p1.relkind IN ('S', 'v', 'f', 'c') and + p1.relam != 0; oid | relname -----+--------- (0 rows) diff --git a/src/test/regress/pg_regress_main.c b/src/test/regress/pg_regress_main.c index f274971be3..f1df7557fa 100644 --- a/src/test/regress/pg_regress_main.c +++ b/src/test/regress/pg_regress_main.c @@ -73,11 +73,16 @@ psql_start_test(const char *testname, } } + /* + * Use HIDE_TABLEAM to hide different AMs to allow to use regression tests + * against different AMs without unnecessary differences. + */ offset += snprintf(psql_cmd + offset, sizeof(psql_cmd) - offset, - "\"%s%spsql\" -X -a -q -d \"%s\" < \"%s\" > \"%s\" 2>&1", + "\"%s%spsql\" -X -a -q -d \"%s\" -v %s < \"%s\" > \"%s\" 2>&1", bindir ? bindir : "", bindir ? "/" : "", dblist->str, + "HIDE_TABLEAM=\"on\"", infile, outfile); if (offset >= sizeof(psql_cmd)) diff --git a/src/test/regress/sql/create_am.sql b/src/test/regress/sql/create_am.sql index 3e0ac104f3..516401ddfe 100644 --- a/src/test/regress/sql/create_am.sql +++ b/src/test/regress/sql/create_am.sql @@ -66,3 +66,119 @@ DROP ACCESS METHOD gist2; -- Drop access method cascade DROP ACCESS METHOD gist2 CASCADE; + + +-- +-- Test table access methods +-- + +-- Create a heap2 table am handler with heapam handler +CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler; + +SELECT amname, amhandler, amtype FROM pg_am where amtype = 't' ORDER BY 1, 2; + +-- First create tables employing the new AM using USING + +-- plain CREATE TABLE +CREATE TABLE tableam_tbl_heap2(f1 int) USING heap2; +INSERT INTO tableam_tbl_heap2 VALUES(1); +SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; + +-- CREATE TABLE AS +CREATE TABLE tableam_tblas_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; +SELECT f1 FROM tableam_tbl_heap2 ORDER BY f1; + +-- SELECT INTO doesn't support USING +SELECT INTO tableam_tblselectinto_heap2 USING heap2 FROM tableam_tbl_heap2; + +-- CREATE VIEW doesn't support USING +CREATE VIEW tableam_view_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; + +-- CREATE SEQUENCE doesn't support USING +CREATE SEQUENCE tableam_seq_heap2 USING heap2; + +-- CREATE MATERIALIZED VIEW does support USING +CREATE MATERIALIZED VIEW tableam_tblmv_heap2 USING heap2 AS SELECT * FROM tableam_tbl_heap2; +SELECT f1 FROM tableam_tblmv_heap2 ORDER BY f1; + +-- CREATE TABLE .. PARTITION BY doesn't not support USING +CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a) USING heap2; + +CREATE TABLE tableam_parted_heap2 (a text, b int) PARTITION BY list (a); +-- new partitions will inherit from the current default, rather the partition root +SET default_table_access_method = 'heap'; +CREATE TABLE tableam_parted_a_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('a'); +SET default_table_access_method = 'heap2'; +CREATE TABLE tableam_parted_b_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('b'); +RESET default_table_access_method; +-- but the method can be explicitly specified +CREATE TABLE tableam_parted_c_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('c') USING heap; +CREATE TABLE tableam_parted_d_heap2 PARTITION OF tableam_parted_heap2 FOR VALUES IN ('d') USING heap2; + +-- List all objects in AM +SELECT + pc.relkind, + pa.amname, + CASE WHEN relkind = 't' THEN + (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) + ELSE + relname::regclass::text + END AS relname +FROM pg_class AS pc, + pg_am AS pa +WHERE pa.oid = pc.relam + AND pa.amname = 'heap2' +ORDER BY 3, 1, 2; + +-- Show dependencies onto AM - there shouldn't be any for toast +SELECT pg_describe_object(classid,objid,objsubid) AS obj +FROM pg_depend, pg_am +WHERE pg_depend.refclassid = 'pg_am'::regclass + AND pg_am.oid = pg_depend.refobjid + AND pg_am.amname = 'heap2' +ORDER BY classid, objid, objsubid; + + +-- Second, create objects in the new AM by changing the default AM +BEGIN; +SET LOCAL default_table_access_method = 'heap2'; + +-- following tests should all respect the default AM +CREATE TABLE tableam_tbl_heapx(f1 int); +CREATE TABLE tableam_tblas_heapx AS SELECT * FROM tableam_tbl_heapx; +SELECT INTO tableam_tblselectinto_heapx FROM tableam_tbl_heapx; +CREATE MATERIALIZED VIEW tableam_tblmv_heapx USING heap2 AS SELECT * FROM tableam_tbl_heapx; +CREATE TABLE tableam_parted_heapx (a text, b int) PARTITION BY list (a); +CREATE TABLE tableam_parted_1_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('a', 'b'); + +-- but an explicitly set AM overrides it +CREATE TABLE tableam_parted_2_heapx PARTITION OF tableam_parted_heapx FOR VALUES IN ('c', 'd') USING heap; + +-- sequences, views and foreign servers shouldn't have an AM +CREATE VIEW tableam_view_heapx AS SELECT * FROM tableam_tbl_heapx; +CREATE SEQUENCE tableam_seq_heapx; +CREATE FOREIGN DATA WRAPPER fdw_heap2 VALIDATOR postgresql_fdw_validator; +CREATE SERVER fs_heap2 FOREIGN DATA WRAPPER fdw_heap2 ; +CREATE FOREIGN table tableam_fdw_heapx () SERVER fs_heap2; + +-- Verify that new AM was used for tables, matviews, but not for sequences, views and fdws +SELECT + pc.relkind, + pa.amname, + CASE WHEN relkind = 't' THEN + (SELECT 'toast for ' || relname::regclass FROM pg_class pcm WHERE pcm.reltoastrelid = pc.oid) + ELSE + relname::regclass::text + END AS relname +FROM pg_class AS pc + LEFT JOIN pg_am AS pa ON (pa.oid = pc.relam) +WHERE pc.relname LIKE 'tableam_%_heapx' +ORDER BY 3, 1, 2; + +-- don't want to keep those tables, nor the default +ROLLBACK; + +-- Drop table access method, which fails as objects depends on it +DROP ACCESS METHOD heap2; + +-- we intentionally leave the objects created above alive, to verify pg_dump support diff --git a/src/test/regress/sql/opr_sanity.sql b/src/test/regress/sql/opr_sanity.sql index e2014fc2b5..1227ef79f0 100644 --- a/src/test/regress/sql/opr_sanity.sql +++ b/src/test/regress/sql/opr_sanity.sql @@ -1201,15 +1201,25 @@ SELECT p1.oid, p1.amname FROM pg_am AS p1 WHERE p1.amhandler = 0; --- Check for amhandler functions with the wrong signature +-- Check for index amhandler functions with the wrong signature SELECT p1.oid, p1.amname, p2.oid, p2.proname FROM pg_am AS p1, pg_proc AS p2 -WHERE p2.oid = p1.amhandler AND - (p2.prorettype != 'index_am_handler'::regtype OR p2.proretset +WHERE p2.oid = p1.amhandler AND p1.amtype = 'i' AND + (p2.prorettype != 'index_am_handler'::regtype + OR p2.proretset OR p2.pronargs != 1 OR p2.proargtypes[0] != 'internal'::regtype); +-- Check for table amhandler functions with the wrong signature + +SELECT p1.oid, p1.amname, p2.oid, p2.proname +FROM pg_am AS p1, pg_proc AS p2 +WHERE p2.oid = p1.amhandler AND p1.amtype = 's' AND + (p2.prorettype != 'table_am_handler'::regtype + OR p2.proretset + OR p2.pronargs != 1 + OR p2.proargtypes[0] != 'internal'::regtype); -- **************** pg_amop **************** diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql index 1bb2a6e16d..fb7d17fc76 100644 --- a/src/test/regress/sql/psql.sql +++ b/src/test/regress/sql/psql.sql @@ -448,6 +448,21 @@ select 1 where false; \df exp \pset tuples_only false +-- check conditional tableam display + +-- Create a heap2 table am handler with heapam handler +CREATE ACCESS METHOD heap_psql TYPE TABLE HANDLER heap_tableam_handler; +CREATE TABLE tbl_heap_psql(f1 int, f2 char(100)) using heap_psql; +CREATE TABLE tbl_heap(f1 int, f2 char(100)) using heap; +\d+ tbl_heap_psql +\d+ tbl_heap +\set HIDE_TABLEAM off +\d+ tbl_heap_psql +\d+ tbl_heap +\set HIDE_TABLEAM on +DROP TABLE tbl_heap, tbl_heap_psql; +DROP ACCESS METHOD heap_psql; + -- test numericlocale (as best we can without control of psql's locale) \pset format aligned diff --git a/src/test/regress/sql/type_sanity.sql b/src/test/regress/sql/type_sanity.sql index f9aeea3214..821337b002 100644 --- a/src/test/regress/sql/type_sanity.sql +++ b/src/test/regress/sql/type_sanity.sql @@ -367,12 +367,17 @@ WHERE relkind NOT IN ('r', 'i', 'S', 't', 'v', 'm', 'c', 'f', 'p') OR relpersistence NOT IN ('p', 'u', 't') OR relreplident NOT IN ('d', 'n', 'f', 'i'); --- Indexes should have an access method, others not. +-- All tables and indexes should have an access method. +SELECT p1.oid, p1.relname +FROM pg_class as p1 +WHERE p1.relkind NOT IN ('S', 'v', 'f', 'c') and + p1.relam = 0; +-- Conversely, sequences, views, types shouldn't have them SELECT p1.oid, p1.relname FROM pg_class as p1 -WHERE (p1.relkind = 'i' AND p1.relam = 0) OR - (p1.relkind != 'i' AND p1.relam != 0); +WHERE p1.relkind IN ('S', 'v', 'f', 'c') and + p1.relam != 0; -- **************** pg_attribute **************** |