pg.ddx.io  pgsql-committers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
pgsql: Catalog NOT NULL constraints
3+ messages / 2 participants
[nested] [flat]

Commits from this thread:
  2023-04-11 4c8a1b4769  Fix uninitialized variable in transformTableLikeClause()
* pgsql: Catalog NOT NULL constraints
@ 2023-04-07 18:09 Alvaro Herrera <alvherre@alvh.no-ip.org>
  2023-04-11 10:45 ` Re: pgsql: Catalog NOT NULL constraints David Rowley <dgrowleyml@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Alvaro Herrera @ 2023-04-07 18:09 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Catalog NOT NULL constraints

We now create pg_constaint rows for NOT NULL constraints with
contype='n'.

We propagate these constraints during operations such as adding
inheritance relationships, creating and attaching partitions, creating
tables LIKE other tables.  We mostly follow the well-known rules of
conislocal and coninhcount that we have for CHECK constraints, with some
adaptations; for example, as opposed to CHECK constraints, we don't
match NOT NULL ones by name when descending a hierarchy to alter it;
instead we match by column number.  This means we don't require the
constraint names to be identical across a hierarchy.

For now, we omit them from system catalogs.  Maybe this is worth
reconsidering.  We don't support NOT VALID nor DEFERRABLE clauses
either; these can be added as separate features later (this patch is
already large and complicated enough.)

This has been very long in the making.  The first patch was written by
Bernd Helmle in 2010 to add a new pg_constraint.contype value ('n'),
which I (Álvaro) then hijacked in 2011 and 2012, until that one was
killed by the realization that we ought to use contype='c' instead:
manufactured CHECK constraints.  However, later SQL standard
development, as well as nonobvious emergent properties of that design
(mostly, failure to distinguish them from "normal" CHECK constraints as
well as the performance implication of having to test the CHECK
expression) led us to reconsider this choice, so now the current
implementation uses contype='n' again.

In 2016 Vitaly Burovoy also worked on this feature[1] but found no
consensus for his proposed approach, which was claimed to be closer to
the letter of the standard, requiring additional pg_attribute columns to
track the OID of the NOT NULL constraint for that column.
[1] https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com

Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Author: Bernd Helmle <mailings@oopsware.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>

Discussion: https://postgr.es/m/CACA0E642A0267EDA387AF2B%40%5B172.26.14.62%5D
Discussion: https://postgr.es/m/AANLkTinLXMOEMz+0J29tf1POokKi4XDkWJ6-DDR9BKgU@mail.gmail.com
Discussion: https://postgr.es/m/20110707213401.GA27098@alvh.no-ip.org
Discussion: https://postgr.es/m/1343682669-sup-2532@alvh.no-ip.org
Discussion: https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com
Discussion: https://postgr.es/m/20220817181249.q7qvj3okywctra3c@alvherre.pgsql

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/e056c557aef4006c3dfbf8a4b94b7ae88eb9fd67

Modified Files
--------------
doc/src/sgml/catalogs.sgml                         |    1 +
doc/src/sgml/ref/alter_table.sgml                  |   14 +-
doc/src/sgml/ref/create_table.sgml                 |    8 +-
src/backend/catalog/heap.c                         |  491 ++++++--
src/backend/catalog/pg_constraint.c                |   97 ++
src/backend/commands/tablecmds.c                   | 1326 +++++++++++++++-----
src/backend/nodes/outfuncs.c                       |    4 +
src/backend/nodes/readfuncs.c                      |    8 +-
src/backend/optimizer/util/plancat.c               |    2 +
src/backend/parser/gram.y                          |   13 +
src/backend/parser/parse_utilcmd.c                 |  206 ++-
src/backend/utils/adt/ruleutils.c                  |   14 +
src/bin/pg_dump/common.c                           |   15 +-
src/bin/pg_dump/pg_backup_archiver.c               |    2 +
src/bin/pg_dump/pg_dump.c                          |  209 ++-
src/bin/pg_dump/pg_dump.h                          |    2 +-
src/bin/pg_dump/t/002_pg_dump.pl                   |    6 +-
src/include/catalog/catversion.h                   |    2 +-
src/include/catalog/heap.h                         |    7 +-
src/include/catalog/pg_constraint.h                |   11 +-
src/include/commands/tablecmds.h                   |    2 +
src/include/nodes/parsenodes.h                     |   14 +-
.../test_ddl_deparse/expected/alter_table.out      |   18 +-
.../test_ddl_deparse/expected/create_table.out     |   25 +-
.../modules/test_ddl_deparse/test_ddl_deparse.c    |    4 +
src/test/regress/expected/alter_table.out          |   50 +-
src/test/regress/expected/cluster.out              |    7 +-
src/test/regress/expected/constraints.out          |  114 ++
src/test/regress/expected/create_table.out         |   27 +-
src/test/regress/expected/event_trigger.out        |    2 +
src/test/regress/expected/foreign_data.out         |   11 +-
src/test/regress/expected/foreign_key.out          |   16 +-
src/test/regress/expected/indexing.out             |   41 +-
src/test/regress/expected/inherit.out              |  405 ++++++
src/test/regress/expected/replica_identity.out     |   13 +
src/test/regress/parallel_schedule                 |    3 +-
src/test/regress/sql/alter_table.sql               |   26 +-
src/test/regress/sql/constraints.sql               |   43 +
src/test/regress/sql/create_table.sql              |    6 +-
src/test/regress/sql/indexing.sql                  |    8 +-
src/test/regress/sql/inherit.sql                   |  208 +++
src/test/regress/sql/replica_identity.sql          |   12 +
42 files changed, 2868 insertions(+), 625 deletions(-)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: pgsql: Catalog NOT NULL constraints
  2023-04-07 18:09 pgsql: Catalog NOT NULL constraints Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-04-11 10:45 ` David Rowley <dgrowleyml@gmail.com>
  0 siblings, 0 replies; 3+ messages in thread

From: David Rowley @ 2023-04-11 10:45 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-committers@lists.postgresql.org

On Sat, 8 Apr 2023 at 06:09, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> Catalog NOT NULL constraints

I think transformTableLikeClause() contains a small issue because
process_notnull_constraints is not set to false by default.

Per valgrind, it looks like the variable can be uninitialised by the
time it's referenced around line 1221.

David

Attachments:

init_process_notnull_constraints_to_false.patch (application/octet-stream, 576B) download diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 15d8f3a1a0..ea0fe6f811 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1041,7 +1041,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla AclResult aclresult; char *comment; ParseCallbackState pcbstate; - bool process_notnull_constraints; + bool process_notnull_constraints = false; setup_parser_errposition_callback(&pcbstate, cxt->pstate, table_like_clause->relation->location);
^ permalink raw reply [nested|flat] 3+ messages in thread

* pgsql: Catalog not-null constraints
@ 2023-08-25 11:32 Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 0 replies; 3+ messages in thread

From: Alvaro Herrera @ 2023-08-25 11:32 UTC (permalink / raw)
  To: pgsql-committers@lists.postgresql.org

Catalog not-null constraints

We now create contype='n' pg_constraint rows for not-null constraints.

We propagate these constraints to other tables during operations such as
adding inheritance relationships, creating and attaching partitions and
creating tables LIKE other tables.  We also spawn not-null constraints
for inheritance child tables when their parents have primary keys.
These related constraints mostly follow the well-known rules of
conislocal and coninhcount that we have for CHECK constraints, with some
adaptations: for example, as opposed to CHECK constraints, we don't
match not-null ones by name when descending a hierarchy to alter it,
instead matching by column name that they apply to.  This means we don't
require the constraint names to be identical across a hierarchy.

For now, we omit them for system catalogs.  Maybe this is worth
reconsidering.  We don't support NOT VALID nor DEFERRABLE clauses
either; these can be added as separate features later (this patch is
already large and complicated enough.)

psql shows these constraints in \d+.

pg_dump requires some ad-hoc hacks, particularly when dumping a primary
key.  We now create one "throwaway" not-null constraint for each column
in the PK together with the CREATE TABLE command, and once the PK is
created, all those throwaway constraints are removed.  This avoids
having to check each tuple for nullness when the dump restores the
primary key creation.

pg_upgrading from an older release requires a somewhat brittle procedure
to create a constraint state that matches what would be created if the
database were being created fresh in Postgres 17.  I have tested all the
scenarios I could think of, and it works correctly as far as I can tell,
but I could have neglected weird cases.

This patch has been very long in the making.  The first patch was
written by Bernd Helmle in 2010 to add a new pg_constraint.contype value
('n'), which I (Álvaro) then hijacked in 2011 and 2012, until that one
was killed by the realization that we ought to use contype='c' instead:
manufactured CHECK constraints.  However, later SQL standard
development, as well as nonobvious emergent properties of that design
(mostly, failure to distinguish them from "normal" CHECK constraints as
well as the performance implication of having to test the CHECK
expression) led us to reconsider this choice, so now the current
implementation uses contype='n' again.  During Postgres 16 this had
already been introduced by commit e056c557aef4, but there were some
problems mainly with the pg_upgrade procedure that couldn't be fixed in
reasonable time, so it was reverted.

In 2016 Vitaly Burovoy also worked on this feature[1] but found no
consensus for his proposed approach, which was claimed to be closer to
the letter of the standard, requiring an additional pg_attribute column
to track the OID of the not-null constraint for that column.
[1] https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com

Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Author: Bernd Helmle <mailings@oopsware.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/b0e96f311985bceba79825214f8e43f65afa653a

Modified Files
--------------
contrib/sepgsql/expected/alter.out                 |    3 -
contrib/sepgsql/expected/ddl.out                   |    2 +
contrib/test_decoding/expected/ddl.out             |   12 +
doc/src/sgml/catalogs.sgml                         |   11 +-
doc/src/sgml/ddl.sgml                              |   55 +-
doc/src/sgml/ref/alter_table.sgml                  |   11 +-
doc/src/sgml/ref/create_table.sgml                 |    8 +-
src/backend/catalog/heap.c                         |  529 +++++--
src/backend/catalog/pg_constraint.c                |  286 ++++
src/backend/commands/tablecmds.c                   | 1595 ++++++++++++++------
src/backend/nodes/outfuncs.c                       |    5 +
src/backend/nodes/readfuncs.c                      |    9 +-
src/backend/optimizer/util/plancat.c               |    2 +
src/backend/parser/gram.y                          |   19 +-
src/backend/parser/parse_utilcmd.c                 |  277 +++-
src/backend/utils/adt/ruleutils.c                  |   14 +
src/backend/utils/cache/relcache.c                 |   34 +-
src/bin/pg_dump/common.c                           |   18 +-
src/bin/pg_dump/pg_dump.c                          |  292 +++-
src/bin/pg_dump/pg_dump.h                          |    9 +-
src/bin/pg_dump/t/002_pg_dump.pl                   |   10 +-
src/bin/psql/describe.c                            |   44 +
src/include/catalog/catversion.h                   |    2 +-
src/include/catalog/heap.h                         |    8 +-
src/include/catalog/pg_constraint.h                |   14 +-
src/include/nodes/parsenodes.h                     |   16 +-
.../test_ddl_deparse/expected/alter_table.out      |   18 +-
.../test_ddl_deparse/expected/create_table.out     |   26 +-
.../modules/test_ddl_deparse/test_ddl_deparse.c    |    6 +-
src/test/regress/expected/alter_table.out          |   62 +-
src/test/regress/expected/cluster.out              |    7 +-
src/test/regress/expected/constraints.out          |  252 ++++
src/test/regress/expected/create_table.out         |   41 +-
src/test/regress/expected/create_table_like.out    |   10 +
src/test/regress/expected/event_trigger.out        |    2 +
src/test/regress/expected/foreign_data.out         |  108 +-
src/test/regress/expected/foreign_key.out          |   16 +-
src/test/regress/expected/generated.out            |    2 +
src/test/regress/expected/identity.out             |    4 +
src/test/regress/expected/indexing.out             |   41 +-
src/test/regress/expected/inherit.out              |  442 ++++++
src/test/regress/expected/publication.out          |    6 +
src/test/regress/expected/replica_identity.out     |   24 +
src/test/regress/expected/rowsecurity.out          |    2 +
src/test/regress/sql/alter_table.sql               |   24 +-
src/test/regress/sql/constraints.sql               |  102 ++
src/test/regress/sql/create_table.sql              |    6 +-
src/test/regress/sql/indexing.sql                  |    8 +-
src/test/regress/sql/inherit.sql                   |  183 +++
src/test/regress/sql/replica_identity.sql          |   15 +
50 files changed, 3928 insertions(+), 764 deletions(-)



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2023-08-25 11:32 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-07 18:09 pgsql: Catalog NOT NULL constraints Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-04-11 10:45 ` David Rowley <dgrowleyml@gmail.com>
2023-08-25 11:32 pgsql: Catalog not-null constraints Alvaro Herrera <alvherre@alvh.no-ip.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox