Обсуждение: odd behaviour with serial, non null and partitioned table

Hi Alvaro,

Problem 1
========
#create table tpart (a serial primary key, src varchar) partition by range(a);
CREATE TABLE
#create table t_p4 (a int primary key, src varchar);
CREATE TABLE
#\d tpart
                           Partitioned table "public.tpart"
 Column |       Type        | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
 a      | integer           |           | not null |
nextval('tpart_a_seq'::regclass)
 src    | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0

#\d t_p4;
                     Table "public.t_p4"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 src    | character varying |           |          |
Indexes:
    "t_p4_pkey" PRIMARY KEY, btree (a)

Notice that both tpart and t_p4 have their column 'a' marked NOT NULL resp.

#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('tpart'::regclass, 't_p4'::regclass);
     conname      | contype | conrelid
------------------+---------+----------
 tpart_a_not_null | n       | tpart
 tpart_pkey       | p       | tpart
 t_p4_pkey        | p       | t_p4
(3 rows)

But tparts NOT NULL constraint is recorded in pg_constraint but not
t_p4's. Is this expected?

Both of them have there column a marked not null in pg_attribute
#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('tpart'::regclass, 't_p4'::regclass) and attname =
'a';
 attrelid | attname | attnotnull
----------+---------+------------
 tpart   | a       | t
 t_p4     | a       | t
(2 rows)

From the next set of commands it can be inferred that the NOT NULL
constraint of tpart came because of serial column whereas t_p4's
column a was marked NOT NULL because of primary key. I didn't
investigate the source code.
#create table t_serial(a serial, src varchar);
CREATE TABLE
#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('t_serial'::regclass);
       conname       | contype | conrelid
---------------------+---------+----------
 t_serial_a_not_null | n       | t_serial
(1 row)

#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('t_serial'::regclass) and attname = 'a';
 attrelid | attname | attnotnull
----------+---------+------------
 t_serial | a       | t
(1 row)

Here's what I was trying to do actually.
#alter table tpart attach partition t_p4 for values from (7) to (9);
ERROR:  column "a" in child table must be marked NOT NULL
This is a surprise since t_p4.a is marked as NOT NULL. That happens
because MergeConstraintsIntoExisting() only looks at pg_constraint and
not pg_attribute. Should this function look at pg_attribute as well?

This behaviour is different from PG 14. I chanced to have a PG 14
build and hence tried that. I haven't tried PG 15 though.
#select version();
                                                version
-------------------------------------------------------------------------------------------------------
 PostgreSQL 14.8 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)

#create table tpart (a serial primary key, src varchar) partition by range(a);
CREATE TABLE
#create table t_p4 (a int primary key, src varchar);
CREATE TABLE
#\d tpart
                           Partitioned table "public.tpart"
 Column |       Type        | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
 a      | integer           |           | not null |
nextval('tpart_a_seq'::regclass)
 src    | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 0

#\d t_p4
                     Table "public.t_p4"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 src    | character varying |           |          |
Indexes:
    "t_p4_pkey" PRIMARY KEY, btree (a)

#select conname, contype, conrelid::regclass from pg_constraint where
conrelid in ('tpart'::regclass, 't_p4'::regclass);
  conname   | contype | conrelid
------------+---------+----------
 tpart_pkey | p       | tpart
 t_p4_pkey  | p       | t_p4
(2 rows)
                                                             ^
#select attrelid::regclass, attname, attnotnull from pg_attribute
where attrelid in ('tpart'::regclass, 't_p4'::regclass) and attname =
'a';
 attrelid | attname | attnotnull
----------+---------+------------
 tpart    | a       | t
 t_p4     | a       | t
(2 rows)

#alter table tpart attach partition t_p4 for values from (7) to (9);
ALTER TABLE
postgres@1073836=#\d tpart
                           Partitioned table "public.tpart"
 Column |       Type        | Collation | Nullable |
Default
--------+-------------------+-----------+----------+----------------------------------
 a      | integer           |           | not null |
nextval('tpart_a_seq'::regclass)
 src    | character varying |           |          |
Partition key: RANGE (a)
Indexes:
    "tpart_pkey" PRIMARY KEY, btree (a)
Number of partitions: 1 (Use \d+ to list them.)

#\d t_p4
                     Table "public.t_p4"
 Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
 a      | integer           |           | not null |
 src    | character varying |           |          |
Partition of: tpart FOR VALUES FROM (7) TO (9)
Indexes:
    "t_p4_pkey" PRIMARY KEY, btree (a)

Notice that ALTER TABLE succeeded and t_p4 was attached to tpart as a partition.

Is this backward compatibility break intentional? I haven't followed
NOT NULL constraint thread closely. I might have missed some
discussion.

-- 
Best Wishes,
Ashutosh Bapat

Hello,

On 2023-Oct-17, Ashutosh Bapat wrote:

> Problem 1
> ========
> #create table tpart (a serial primary key, src varchar) partition by range(a);
> CREATE TABLE
> #create table t_p4 (a int primary key, src varchar);
> CREATE TABLE

> But tparts NOT NULL constraint is recorded in pg_constraint but not
> t_p4's. Is this expected?

Yes.  tpart gets it from SERIAL, which implicitly requires a NOT NULL
marker.  If you just say PRIMARY KEY as you did for t_p4, the column
gets marked attnotnull, but there's no explicit NOT NULL constraint.


> Here's what I was trying to do actually.
> #alter table tpart attach partition t_p4 for values from (7) to (9);
> ERROR:  column "a" in child table must be marked NOT NULL
> This is a surprise since t_p4.a is marked as NOT NULL. That happens
> because MergeConstraintsIntoExisting() only looks at pg_constraint and
> not pg_attribute. Should this function look at pg_attribute as well?

Hmm ... well, not that way.  Maybe attaching a partition should cause a
NOT NULL constraint to spawn automatically (we do this in other cases).
There's no need to verify the existing rows for it, since attnotnull is
already checked; but it would mean that if you DETACH the partition, the
constraint would remain, so the table would dump slightly differently
than if you hadn't ATTACHed and DETACHed it.  But that sounds OK to me.

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
Thou shalt check the array bounds of all strings (indeed, all arrays), for
surely where thou typest "foo" someone someday shall type
"supercalifragilisticexpialidocious" (5th Commandment for C programmers)