Horton
Menu

ALTER TABLE SET ACCESS METHOD on partitioned tables

Soumyadeep Chakraborty <soumyadeep2007@gmail.com>

View thread
From: Soumyadeep Chakraborty <soumyadeep2007@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Justin Pryzby <pryzby@telsasoft.com>, Michael Paquier <michael@paquier.xyz>, Ashwin Agrawal <ashwinstar@gmail.com>, vanjared@vmware.com
Date: 2022-05-18T00:10:27Z
Lists: pgsql-hackers

Attachments

Hello,

This is a fresh thread to continue the discussion on ALTER TABLE SET
ACCESS METHOD when applied to partition roots, as requested.

Current behavior (HEAD):

CREATE TABLE am_partitioned(x INT, y INT)
   PARTITION BY hash (x);
ALTER TABLE am_partitioned SET ACCESS METHOD heap2;
ERROR:  cannot change access method of a partitioned table

Potential behavior options:

(A) Don't recurse to existing children and ensure that the new am gets
inherited by any new children. (ALTER TABLE SET TABLESPACE behavior)

(B) Recurse to existing children and modify their am. Also, ensure that
any new children inherit the new am.

A patch [1] was introduced earlier by Justin to implement
(A). v1-0001-Allow-ATSETAM-on-partition-roots.patch contains a rebase
of that patch against latest HEAD, with minor updates on comments and
some additional test coverage.

I think that (B) is necessary for partition hierarchies with a high
number of partitions. One typical use case in Greenplum, for
instance, is to convert heap tables containing cold data to append-only
storage at the root or subroot level of partition hierarchies consisting
of thousands of partitions. Asking users to ALTER individual partitions
is cumbersome and error-prone.

Furthermore, I believe that (B) should be the default and (A) can be
chosen by using the ONLY clause. This would give us the best of both
worlds and would make the use of ONLY consistent. The patch
v1-0002-Make-ATSETAM-recurse-by-default.patch achieves that.

Thoughts?

Regards,
Soumyadeep (VMware)


[1]
https://www.postgresql.org/message-id/20210308010707.GA29832%40telsasoft.com

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add information about access method for partitioned relations in \dP+

  2. Fix dumps of partitioned tables with table AMs

  3. Don't use the pg_am system catalog in new test

  4. ALTER TABLE: rework determination of access method ID

  5. Allow specifying an access method for partitioned tables

  6. Review wording on tablespaces w.r.t. partitioned tables

  7. Add support for DEFAULT in ALTER TABLE .. SET ACCESS METHOD

  8. Update comment of AlterTableCmd->name in parsenodes.h