Forking this thread
https://www.postgresql.org/message-id/20181227132417.xe3oagawina7775b%40alvherre.pgsql
On Wed, Dec 26, 2018 at 01:09:39PM -0500, Robert Haas wrote:
> ALTER TABLE already has a lot of logic that is oriented towards being
> able to do multiple things at the same time. If we added CLUSTER,
> VACUUM FULL, and REINDEX to that set, then you could, say, change a
> data type, cluster, and change tablespaces all in a single SQL
> command.
On Thu, Dec 27, 2018 at 10:24:17AM -0300, Alvaro Herrera wrote:
> I think it would be valuable to have those ALTER TABLE variants that rewrite
> the table do so using the cluster order, if there is one, instead of the heap
> order, which is what it does today.
That's a neat idea.
I haven't yet fit all of ALTERs processing logic in my head ... but there's an
issue that ALTER (unlike CLUSTER) needs to deal with column type promotion, so
the indices may need to be dropped and recreated. The table rewrite happens
AFTER dropping indices (and all other processing), but the clustered index
can't be scanned if it's just been dropped. I handled that by using a
tuplesort, same as heapam_relation_copy_for_cluster.
Experimental patch attached. With clustered ALTER:
template1=# DROP TABLE t; CREATE TABLE t AS SELECT generate_series(1,999)i; CREATE INDEX ON t(i DESC); ALTER TABLE t
CLUSTERON t_i_idx; ALTER TABLE t ALTER i TYPE bigint; SELECT * FROM t LIMIT 9;
DROP TABLE
SELECT 999
CREATE INDEX
ALTER TABLE
ALTER TABLE
i
-----
999
998
997
996
995
994
993
992
991
(9 rows)
0001 patch is stolen from the nearby thread:
https://www.postgresql.org/message-id/flat/20200207143935.GP403%40telsasoft.com
It doesn't make much sense for ALTER to use a clustered index when rewriting a
table, if doesn't also go to the effort to preserve the cluster property when
rebuilding its indices.
0002 patch is included and not squished with 0003 to show the original
implementation using an index scan (by not dropping indices on the old table,
and breaking various things), and the evolution to tuplesort.
Note, this doesn't use clustered order when rewriting only due to tablespace
change. Alter currently does an AM specific block copy without looking at
tuples. But I think it'd be possible to use tuplesort and copy if desired.