pg_upgrade rebuild_tsvector_tables.sql includes child table columns
When pg_upgrade generates a rebuild_tsvector_tables.sql script to
rewrite tsvector columns, it includes ALTER TABLE commands for child
tables that cannot be altered independently from their parents:
psql:rebuild_tsvector_tables.sql:673: ERROR: cannot alter inherited column "xxx"
This isn't a problem unless you run the script in single-transaction
mode, because the commands will just fail and the work has already
been done in the parent table.
It's probably not worth fixing this, given that this only applies to
upgrades from 8.3, but I wanted to get it into the archives. If we
ever need to do something like this again, we should make it smarter.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Nov 13, 2013 at 03:27:14PM -0500, Peter Eisentraut wrote:
When pg_upgrade generates a rebuild_tsvector_tables.sql script to
rewrite tsvector columns, it includes ALTER TABLE commands for child
tables that cannot be altered independently from their parents:psql:rebuild_tsvector_tables.sql:673: ERROR: cannot alter inherited column "xxx"
This isn't a problem unless you run the script in single-transaction
mode, because the commands will just fail and the work has already
been done in the parent table.It's probably not worth fixing this, given that this only applies to
upgrades from 8.3, but I wanted to get it into the archives. If we
ever need to do something like this again, we should make it smarter.
Good catch. I have applied the attached patch to head so if we ever use
this code for more complex cases, it will work properly for child
tables. Thanks.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
Attachments:
pg_upgrade.difftext/x-diff; charset=us-asciiDownload
diff --git a/contrib/pg_upgrade/version_old_8_3.c b/contrib/pg_upgrade/version_old_8_3.c
new file mode 100644
index 84a47ee..e8438b6
*** a/contrib/pg_upgrade/version_old_8_3.c
--- b/contrib/pg_upgrade/version_old_8_3.c
*************** old_8_3_rebuild_tsvector_tables(ClusterI
*** 325,330 ****
--- 325,332 ----
"WHERE c.relkind = 'r' AND "
" c.oid = a.attrelid AND "
" NOT a.attisdropped AND "
+ /* child attribute changes are processed by the parent */
+ " a.attinhcount = 0 AND "
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND "
" c.relnamespace = n.oid AND "
/* exclude possible orphaned temp tables */
*************** old_8_3_rebuild_tsvector_tables(ClusterI
*** 346,351 ****
--- 348,355 ----
"WHERE c.relkind = 'r' AND " \
" c.oid = a.attrelid AND " \
" NOT a.attisdropped AND " \
+ /* child attribute changes are processed by the parent */ \
+ " a.attinhcount = 0 AND " \
" a.atttypid = 'pg_catalog.tsvector'::pg_catalog.regtype AND " \
" c.relnamespace = n.oid AND " \
" n.nspname !~ '^pg_' AND " \