On 2019-Apr-10, Alvaro Herrera wrote: > This is because ruleutils.c attaches a TABLESPACE clause when asked to > dump an index definition; and tablecmds.c uses ruleutils to deparse the > index definition into something that can be replayed via CREATE INDEX > commands (or ALTER TABLE ADD CONSTRAINT UNIQUE/PRIMARY KEY, if that's > the case.)
Found a "solution" to this -- namely, to set the GUC default_tablespace to the empty string temporarily, and teach ruleutils.c to attach TABLESPACE clauses on index/constraint definitions only if they are not in the database tablespace. That makes everything works correctly. (I did have to patch psql to show tablespace for partitioned indexes.) However, because the tablespace to use for an index is determined at phase 3 execution time (i.e. inside DefineIndex), look what happens in certain weird cases: create tablespace foo location '/tmp/foo'; set default_tablespace to foo; alter table t add unique (b) ; create index on t (a); at this point, the indexes for "a" and "b" is in tablespace foo, which is correct because that's the default tablespace. However, if we do a type change *and add an index in the same command*, then that index ends up in the wrong tablespace (namely the database tablespace instead of default_tablespace): alter table t alter a type bigint, add unique (c); I'm not seeing any good way to fix this; I need the default tablespace reset to only affect the index creations caused by the rewrite, but not the ones used by different commands. I suppose forbidding ADD CONSTRAINT subcommands together with ALTER COLUMN SET DATA TYPE would not fly very far. (I'm not sure that's complete either: if you change datatype so that a toast table is created, perhaps this action will affect the location of said new toast table, also.) -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
