using index or check in ALTER TABLE SET NOT NULL

From: Sergei Kornilov <sk(at)zsrv(dot)org>
To: pgsql-hackers(at)postgresql(dot)org
Subject: using index or check in ALTER TABLE SET NOT NULL
Date: 2017-11-28 18:59:00
Message-ID: 81911511895540@web58j.yandex.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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!

Attachment Content-Type Size
alter_table_set_not_null_by_index_or_check_v1.patch text/x-diff 7.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Mark Dilger 2017-11-28 19:07:24 Re: Updated macOS start scripts
Previous Message Peter Eisentraut 2017-11-28 18:33:59 Re: [HACKERS] Transaction control in procedures