Skip to content

2.25.0.0-b281

2.25.0.0-b281: [#24399] YSQL: Support NULLS NOT DISTINCT on unique index
@arpang arpang tagged this 09 Nov 08:06 
Summary:
This revision adds support for UNIQUE NULLS NOT DISTINCT in YB. A unique index created in this mode will treat all nulls as equivalent. This feature was introduced in PG by the commit 94aa7cc5f707712f592885995a28e018c7c80488 and subsequently disabled by YB commit f0d98a62f8b93e0f1f91ca99cf9148c02ffe5058.

Till now, nulls were treated as distinct in a UNIQUE index in PG/YB. But in DocDB, nulls are not distinct (null == null). To workaround this disparity, an extra key column, `kYBUniqueIdxKeySuffix`, was introduced for unique indexes. This attribute is populated with the baseybctid (base relation's ybctid corresponding to the row being indexed) if any of the index key columns are null - so that a unique DocKey is created.

Since DocDB natively treats all nulls as equivalent, setting this additional key column to NULL is sufficient to support unique indexes in NULLS NOT DISTINCT mode.

An alternate approach would have to just drop the kYBUniqueIdxKeySuffix column for this case, which would have saved one byte per index row in DocDB, but setting it to null keeps the DocKey schema consistent across both the modes (null-are-distinct and null-not-distinct) of unique indexes, this has some minor advantages:
- less and easy-to-understand code changes
- if we support a mode switch in the future, it will be relatively more straightforward.
Jira: DB-13308

Test Plan:
   ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressPgConstraints'
   ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressPgMisc'
   ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressInsertOnConflict'
   ./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressForeignKey'

Reviewers: kramanathan, tnayak

Reviewed By: kramanathan, tnayak

Subscribers: smishra, ybase, yql

Differential Revision: https://phorge.dev.yugabyte.com/D39058
Assets 2
Loading