From: Justin Pryzby <pryzby@telsasoft.com>
To: pgsql-hackers@postgresql.org
Cc: Andres Freund <andres@anarazel.de>
Subject: tableam options for pg_dump/ALTER/LIKE
Date: Tue, 28 Jan 2020 07:33:17 -0600
Message-ID: <20200128133317.GZ13621@telsasoft.com> (raw)
I made these casual comments. If there's any agreement on their merit, it'd be
nice to implement at least the first for v13.
In <20190818193533.GL11185@telsasoft.com>, I 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.
That appears to be a trivial variation on no-tablespace:
/* do nothing in --no-tablespaces mode */
if (ropt->noTablespace)
return;
> . 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 EXCLUDING INDEXES) 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;
Ideally that would allow all at once various combinations of altering
tablespace, changing AM, clustering, and reindexing, like what's discussed
here:
https://www.postgresql.org/message-id/flat/8a8f5f73-00d3-55f8-7583-1375ca8f6a91@postgrespro.ru> . Speaking of which, I think LIKE needs a new option for ACCESS METHOD, which
> is otherwise lost.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: pryzby@telsasoft.com, andres@anarazel.de
Subject: Re: tableam options for pg_dump/ALTER/LIKE
In-Reply-To: <20200128133317.GZ13621@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox