Thread: BUG #18674: Partitioned table doesn't depend on access method it uses

The following bug has been logged on the website:

Bug reference:      18674
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 17.0
Operating system:   Ubuntu 22.04
Description:

The following script:
CREATE ACCESS METHOD ham TYPE TABLE HANDLER heap_tableam_handler;
CREATE TABLE pt (a int) PARTITION BY LIST (a) USING ham;
DROP ACCESS METHOD ham;

ends successfully, with the access method dropped, though then:
CREATE TABLE tp1 PARTITION OF pt FOR VALUES IN (1);

fails with
ERROR:  cache lookup failed for access method 16385

Whilst
CREATE TABLE t (a int) USING ham;
DROP ACCESS METHOD ham;

fails with
ERROR:  cannot drop access method ham because other objects depend on it
DETAIL:  table t depends on access method ham
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Reproduced starting from 374c7a229.

Hello Michael and Kirill,

28.10.2024 10:23, Michael Paquier wrote:
> The path of adding the dependency between an AM and a table/matview is
> heap_create_with_catalog() when these are defined.  We can just expand
> that for partitioned tables and simply fix the problem.  Attached is a
> patch to do that, with tests around create_am.sql based on USING
> checking the contents of pg_depend.

0001-Fix-dependency....patch works for me, but while testing, I've also
observed another questionable behavior:
CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
SET default_table_access_method = heap2;
CREATE TABLE pt (a int) PARTITION BY LIST (a);
\d+ pt

doesn't show heap2 as access method for the table.

Shouldn't default_table_access_method affect partitioned tables?

Best regards.
Alexander

On Mon, Oct 28, 2024 at 4:00 PM Alexander Lakhin <exclusion@gmail.com> wrote:
>
> Hello Michael and Kirill,
>
> 28.10.2024 10:23, Michael Paquier wrote:
> > The path of adding the dependency between an AM and a table/matview is
> > heap_create_with_catalog() when these are defined.  We can just expand
> > that for partitioned tables and simply fix the problem.  Attached is a
> > patch to do that, with tests around create_am.sql based on USING
> > checking the contents of pg_depend.
>
> 0001-Fix-dependency....patch works for me, but while testing, I've also
> observed another questionable behavior:
> CREATE ACCESS METHOD heap2 TYPE TABLE HANDLER heap_tableam_handler;
> SET default_table_access_method = heap2;
> CREATE TABLE pt (a int) PARTITION BY LIST (a);
> \d+ pt
>
> doesn't show heap2 as access method for the table.
>
> Shouldn't default_table_access_method affect partitioned tables?

I think `default_table_access_method` should not affect partitioned tables,
the partition's access_method will be default_table_access_method as
wanted.

But if it affects partitioned tables, then the partitions will always
inherit the access method of the partitioned table.

You can try the following if you want it to affect partitioned tables:

diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1ccc80087c..434e82ab14 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -973,7 +973,7 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
                        accessMethodId =
get_rel_relam(linitial_oid(inheritOids));
                }

-               if (RELKIND_HAS_TABLE_AM(relkind) &&
!OidIsValid(accessMethodId))
+               if (!OidIsValid(accessMethodId))
                        accessMethodId =
get_table_am_oid(default_table_access_method, false);
        }

>
> Best regards.
> Alexander
>
>


--
Regards
Junwang Zhao