Re: ALTER TABLE SET ACCESS METHOD on partitioned tables - Mailing list pgsql-hackers

From Michael Paquier
Subject Re: ALTER TABLE SET ACCESS METHOD on partitioned tables
Date
Msg-id Zh4JLSvvtQgBJZkZ@paquier.xyz
Whole thread Raw
In response to Re: ALTER TABLE SET ACCESS METHOD on partitioned tables  (Michael Paquier <michael@paquier.xyz>)
Responses Re: ALTER TABLE SET ACCESS METHOD on partitioned tables
List pgsql-hackers
On Mon, Apr 15, 2024 at 10:46:00AM +0900, Michael Paquier wrote:
> There is no need for a catalog here to trigger the failure, and it
> would have happened as long as a foreign table is used.  The problem
> introduced in 374c7a229042 fixed by e2395cdbe83a comes from a thinko
> on my side, my apologies for that and the delay in replying.  Thanks
> for the extra fix done in 13b3b62746ec, Alvaro.

While doing more tests with this feature, among other things, I've
spotted an incorrect behavior with dump/restore with the handling of
the GUC default_table_access_method when it comes to partitions.
Imagine the following in database "a":
CREATE TABLE parent_tab (id int) PARTITION BY RANGE (id);
CREATE TABLE parent_tab_2 (id int) PARTITION BY RANGE (id) USING heap;
CREATE TABLE parent_tab_3 (id int) PARTITION BY RANGE (id);

This leads to the following in pg_class:
=# SELECT relname, relam FROM pg_class WHERE oid > 16000;
   relname    | relam
--------------+-------
 parent_tab   |     0
 parent_tab_2 |     2
 parent_tab_3 |     0
(3 rows)

Now, let's do the following:
$ createdb b
$ pg_dump | psql b
$ psql b
=# SELECT relname, relam FROM pg_class WHERE oid > 16000;
   relname    | relam
--------------+-------
 parent_tab   |     0
 parent_tab_2 |     0
 parent_tab_3 |     0
(3 rows)

And parent_tab_2 would now rely on the default GUC when creating new
partitions rather than enforce heap.

It seems to me that we are going to extend the GUC
default_table_access_method with a "default" mode to be able to force
relam to 0 and make a difference with the non-0 case, in the same way
as ALTER TABLE SET ACCESS METHOD DEFAULT.  The thing is that, like
tablespaces, we have to rely on a GUC and not a USING clause to be
able to handle --no-table-access-method.

An interesting point comes to what we should do for
default_table_access_method set to "default" when dealing with
something else than a partitioned table, where an error may be
adapted.  Still, I'm wondering if there are more flavors I lack
imagination for.  This requires more careful design work.

Perhaps somebody has a good idea?
--
Michael

Attachment

pgsql-hackers by date:

Previous
From: Amul Sul
Date:
Subject: Re: Add bump memory context type and use it for tuplesorts
Next
From: Michael Paquier
Date:
Subject: Re: ALTER TABLE SET ACCESS METHOD on partitioned tables