Subject: create table like: ACCESS METHOD

From: Justin Pryzby <[email-masked]>
Date: Wed, 9 Dec 2020 14:13:29 -0600

I thought this was a good idea, but didn't hear back when I raised it before.

Failing to preserve access method is arguably a bug, reminiscent of CREATE
STATISTICS and 5564c1181.  But maybe it's not important to backpatch a fix in
this case, since access methods are still evolving.

https://www.postgresql.org/message-id/[email-masked]
On Sun, Aug 18, 2019 at 02:35:33PM -0500, Justin Pryzby wrote:
>  . What do you think about pg_restore --no-tableam; similar to
>    --no-tablespaces, it would allow restoring a table to a different AM:
>    PGOPTIONS='-c default_table_access_method=zedstore' pg_restore --no-tableam ./pg_dump.dat -d postgres
>    Otherwise, the dump says "SET default_table_access_method=heap", which
>    overrides any value from PGOPTIONS and precludes restoring to new AM.
...
>  . it'd be nice if there was an ALTER TABLE SET ACCESS METHOD, to allow
>    migrating data.  Otherwise I think the alternative is:
>       begin; lock t;
>       CREATE TABLE new_t LIKE (t INCLUDING ALL) USING (zedstore);
>       INSERT INTO new_t SELECT * FROM t;
>       for index; do CREATE INDEX...; done
>       DROP t; RENAME new_t (and all its indices). attach/inherit, etc.
>       commit;
>
>  . Speaking of which, I think LIKE needs a new option for ACCESS METHOD, which
>    is otherwise lost.

---

From: Michael Paquier <[email-masked]>
Date: Fri, 25 Dec 2020 15:41:46 +0900

> this case, since access methods are still evolving.

Interesting.  Access methods for tables are released for more than one
year now, so my take about a backpatch is that this boat has already
sailed.  This may give a reason to actually not introduce this
feature.

+   CREATE_TABLE_LIKE_ACCESSMETHOD = 1 << 0,
Nit: wouldn't this be better as ACCESS_METHOD?

 -- fail, as partitioned tables don't allow NO INHERIT constraints
-CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL)
+CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL EXCLUDING ACCESS METHOD)
   PARTITION BY LIST (a);
This diff means that you are introducing an incompatible change by
forcing any application using CREATE TABLE LIKE for a partitioned
table to exclude access methods.  This is not acceptable, and it may
be better to just ignore this clause instead in this context.

This patch should have more tests.  Something could be added in
create_am.sql where there is a fake heap2 as table AM.

+         <para>
+          The table's access method will be copied.  By default, the
+          <literal>default_table_access_method</literal> is used.
+         </para>
Second sentence sounds a bit strange by combining "the" and a GUC
name.  I would just write "Default is default_table_a_m".
--
Michael

---

From: Justin Pryzby <[email-masked]>
Date: Tue, 29 Dec 2020 17:08:05 -0600

> sailed.  This may give a reason to actually not introduce this

Are you saying that since v12/13 didn't preserve the access method, it might be
preferred to never do it ?  I think it's reasonable to to not change v12/13 but
the behavior seems like an omission going forward.  It's not so important right
now, since AMs aren't widely used.

This might be important for a few cases I can think of easily:

If an readonly AM doesn't support DDL, and table needs to be rebuilt, we'd
handle that by creating a new table LIKE the existing table, preserving its AM,
and then INSERT into it.  Like for column type promotion.  That's much better
than querying amname FROM pg_class JOIN relam.

ALTER TABLE..ATTACH PARTITION requires a less strong lock than CREATE
TABLE..PARTITION OF, so it's nice to be able to CREATE TABLE LIKE.

To use an alternate AM for historic data, we'd CREATE TABLE LIKE an existing,
populated table before inserting into it.  This would support re-creating on a
new AM, or re-creating on the same AM, say, to get rid of dropped columns, or
to re-arrange columns.  

> be better to just ignore this clause instead in this context.

Ok.  This means that 
CREATE TABLE (LIKE x INCLUDING ACCESS METHOD) PARTITION BY ...
silently ignores the INCLUDING AM.  Is that ok ?  I think the alternative is
for INCLUDING to be "ternary" options, defaulting to UNSET=0, when it's ok to
ignore options in contexts where they're not useful.
Maybe we'd need to specially handle INCLUDING ALL, to make options
"soft"/implied rather than explicit.

> create_am.sql where there is a fake heap2 as table AM.

Yes, I had already done that locally.

-- 
Justin

---

From: Simon Riggs <[email-masked]>
Date: Wed, 30 Dec 2020 12:33:56 +0000

On Tue, 29 Dec 2020 at 23:08, Justin Pryzby <[email-masked]> wrote:
> preferred to never do it ?  I think it's reasonable to not change v12/13 but

Omitting copying the AM seems like a bug during
  CREATE TABLE likeamlike(LIKE likeam INCLUDING ALL);
But this does allow you to specify the TableAM by using
default_table_access_method, and to use
  CREATE TABLE likeamlike(LIKE likeam INCLUDING ALL) USING (heapdup);
if you wish to set the AM explicitly, so I don't see this as needing backpatch.

Which means that the emphasis for the earlier functionality was
towards one "preferred AM" rather than using multiple AMs at same
time. Allowing this change in later releases makes sense.

Please make sure this is marked as an incompatibility in the release notes.

> Yes, I had already done that locally.

There are no tests for the new functionality, please could you add some?

-- 
Simon Riggs                http://www.EnterpriseDB.com/