using index or check in ALTER TABLE SET NOT NULL
Sergei Kornilov <sk@zsrv.org>
From: Sergei Kornilov <sk@zsrv.org>
To: pgsql-hackers@postgresql.org
Date: 2017-11-28T18:59:00Z
Lists: pgsql-hackers
Attachments
- alter_table_set_not_null_by_index_or_check_v1.patch (text/x-diff) patch v1
Hello I write patch to speed up ALTER TABLE SET NOT NULL by check existed check constraints or indexes. Huge phase 3 with verify table data will be skipped if table has valid check constraint cover "alteredfield IS NOT NULL" condition or by SPI query if found index with compatible condition or regular amsearchnulls index on processed field. Patch based on current master branch, i believe it has no platform-dependent code, of course code compiled and pass tests locally. Tell me please, what i forgot or make incorrectly. Implementation notes: I use existed PartConstraintImpliedByRelConstraint method to check relation constraints. But i rename original method to static ConstraintImpliedByRelConstraint (because method now used not only in partitions) and leave PartConstraintImpliedByRelConstraint as proxy to not change public API. I found it difficult to do index scan and choose index with lower costs if found many suitable indexes. Is it acceptable to use SPI here? Related archive discussions: https://www.postgresql.org/message-id/flat/530C10CF.4020101%40strangersgate.com https://www.postgresql.org/message-id/flat/CAASwCXdAK55BzuOy_FtYj2zQWg26PriDKL5pRoWiyFJe0eg-Hg%40mail.gmail.com Thanks!
Commits
-
Avoid using INFO elevel for what are fundamentally debug messages.
- db438318997b 13.0 landed
- 121e3ceed6d9 12.0 landed
-
Revert setting client_min_messages to 'debug1' in new tests.
- 5655565c077c 12.0 landed
-
Allow ALTER TABLE .. SET NOT NULL to skip provably unnecessary scans.
- bbb96c3704c0 12.0 landed
-
Improve predtest.c's internal docs, and enhance its functionality a bit.
- 5748f3a0aa7c 11.0 cited