pg_dump problem with dropped NOT NULL on child table

On 01/13/2016 11:38 AM, Karsten Hilbert wrote:

Please consider the following:

- Debian
- server is PG 9.4.5
- client (psql/pg_dump/libpq5) is 9.5

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

--------------------------------------------------

Is this a bug or am I doing things I shouldn't hope work ?

The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique, primary key, and foreign key constraints) are not inherited."

I noticed this during a recent 9.4 -> 9.5 pg_upgradecluster
attempt with actual data in "child" violating-upon-restore
the newly created NOT NULL constraint on "child" when COPYing.

Thanks,
Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

Please consider the following:

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Is this a bug or am I doing things I shouldn't hope work ?

You should not expect this to work; sooner or later we will make
the backend reject it. See
/messages/by-id/21633.1448383428@sss.pgh.pa.us

Alvaro or someone had a WIP patch to track NOT NULL constraints in
pg_constraint, which is the bookkeeping we'd need to deal with this
sort of thing properly. I'm not sure what the status of it is.

In the meantime, you could get the effect you want if the parent
were marked with CHECK (not_null_in_parent IS NOT NULL) NO INHERIT.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 13, 2016 at 12:10:15PM -0800, Adrian Klaver wrote:

On 01/13/2016 11:38 AM, Karsten Hilbert wrote:

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Is this a bug or am I doing things I shouldn't hope work ?

The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints (unique,
primary key, and foreign key constraints) are not inherited."

Hello Adrian, thanks for chipping in. I am aware of the above
paragraph. In fact, it made me choose the inheritance
approach to the problem at hand in the first place :-)

Note though that, usually, inheriting is a one-time act --
such as during child table creation. What stays behind is the
legacy - which can be changed (DROP NOT NULL).

I was, then, surprised by the fact that the pg_dump /
pg_restore cycle did not "faithfully" reproduce the child
table. That made me question my ways.

Maybe I shouldn't have been surprised because PG inheritance
doesn't end at table creation time (child and parent are
still linked through data even in the future).

Meatspace inheritance is more like

CREATE TABLE pseudo_child_table AS SELECT FROM pseudo_parent_table ...

While PG inheritance is a bit more like view-on-steroids.

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Jan 13, 2016 at 03:32:12PM -0500, Tom Lane wrote:

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Is this a bug or am I doing things I shouldn't hope work ?

You should not expect this to work; sooner or later we will make
the backend reject it. See
/messages/by-id/21633.1448383428@sss.pgh.pa.us

Thanks Tom, that about pins it down for me.

In the meantime, you could get the effect you want if the parent
were marked with CHECK (not_null_in_parent IS NOT NULL) NO INHERIT.

The NO INHERIT won't do because this is, again, part of a
larger scheme of things:

The GNUmed EMR uses a common parent table for all tables
holding clinical data:
Table "clin.clin_root_item"
Column | Type | Modifiers | Storage | Stats target | Description
---------------+--------------------------+-----------------------------------------------------------------------+----------+--------------+-------------------------------------------------------------------------------------------------
pk_audit | integer | not null default nextval('audit.audit_fields_pk_audit_seq'::regclass) | plain | |
row_version | integer | not null default 0 | plain | |
modified_when | timestamp with time zone | not null default now() | plain | |
modified_by | name | not null default "current_user"() | plain | |
pk_item | integer | not null default nextval('clin.clin_root_item_pk_item_seq'::regclass) | plain | | the primary key, not named "id" or "pk" as usual since child +
| | | | | tables will have "id"/"pk"-named primary keys already and +
| | | | | we would get duplicate columns while inheriting from this +
| | | | | table
clin_when | timestamp with time zone | not null default now() | plain | | when this clinical item became known, can be different from +
| | | | | when it was entered into the system (= audit.audit_fields.modified_when)
fk_encounter | integer | not null | plain | | the encounter this item belongs to
fk_episode | integer | not null | plain | | the episode this item belongs to
narrative | text | | extended | | each clinical item by default inherits a free text field for clinical narrative
soap_cat | text | | extended | | each clinical item must be either one of the S, O, A, P, U +
| | | | | categories or NULL to indicate a non-clinical item, U meaning Unspecified-but-clinical
Indexes:
"clin_root_item_pkey" PRIMARY KEY, btree (pk_item)
"idx_cri_encounter" btree (fk_encounter)
"idx_cri_episode" btree (fk_episode)
Check constraints:
"clin_root_item_sane_soap_cat" CHECK (soap_cat IS NULL OR (lower(soap_cat) = ANY (ARRAY['s'::text, 'o'::text, 'a'::text, 'p'::text, 'u'::text])))
Foreign-key constraints:
"clin_root_item_fk_encounter_fkey" FOREIGN KEY (fk_encounter) REFERENCES clin.encounter(pk) ON UPDATE CASCADE ON DELETE RESTRICT
"clin_root_item_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON DELETE RESTRICT
Rules:
clin_ritem_no_del AS
ON DELETE TO clin.clin_root_item DO INSTEAD SELECT clin.f_protect_clin_root_item() AS f_protect_clin_root_item
clin_ritem_no_ins AS
ON INSERT TO clin.clin_root_item DO INSTEAD SELECT clin.f_protect_clin_root_item() AS f_protect_clin_root_item
Triggers:
tr_sanity_check_enc_epi_ins_upd BEFORE INSERT OR UPDATE ON clin.clin_root_item FOR EACH ROW WHEN (new.fk_episode IS NOT NULL) EXECUTE PROCEDURE clin.trf_sanity_check_enc_epi_ins_upd('fk_encounter', 'fk_episode')
zzz_tr_announce_clin_clin_root_item_del AFTER DELETE ON clin.clin_root_item DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_del('operation=DELETE::table=clin.clin_root_item::PK name=pk_item', 'select $1.pk_item', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
zzz_tr_announce_clin_clin_root_item_ins_upd AFTER INSERT OR UPDATE ON clin.clin_root_item DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE gm.trf_announce_table_ins_upd('table=clin.clin_root_item::PK name=pk_item', 'select $1.pk_item', 'select fk_patient from clin.encounter where pk = $1.fk_encounter limit 1')
Inherits: audit.audit_fields
Child tables: au.referral,
clin.allergy,
clin.clin_aux_note,
clin.clin_narrative,
clin.family_history,
clin.form_instances,
clin.hospital_stay,
clin.lab_request,
clin.procedure,
clin.substance_intake,
clin.test_result,
clin.vaccination

1) note how this inherits from the audit schema base table
discussed a few days ago

2) please don't chastize me on the

soap_cat: each *clinical item* must be either one of the S, O, A, P, U categories or NULL to indicate a NON-clinical item

:-)

3) I am well aware that child tables of this will have
_three_ single-column, integer candidates for a
primary key:

pk_audit / pk_item / pk_whatever_child_table

:-))

4) I shouldn't have listened to users, or rather use a
pseudo-episode-of-care for storing _some_ items
in clin.substance_intake (users did not want to
link substance *abuse* to an episode of care) such
that I don't have to DROP NOT NULL on fk_episode
in clin.substance_intake

I shall go fix my schema.

(other suggestions to improve the above welcome)

Thanks,
Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 01/14/2016 12:59 AM, Karsten Hilbert wrote:

On Wed, Jan 13, 2016 at 12:10:15PM -0800, Adrian Klaver wrote:

On 01/13/2016 11:38 AM, Karsten Hilbert wrote:

create table parent (
not_null_in_parent integer not null
);

create table child() inherits (parent);
alter table child
alter column not_null_in_parent
drop not null
;

Is this a bug or am I doing things I shouldn't hope work ?

The latter if I am following the below correctly:

http://www.postgresql.org/docs/9.5/static/ddl-inherit.html

"All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints (unique,
primary key, and foreign key constraints) are not inherited."

Hello Adrian, thanks for chipping in. I am aware of the above
paragraph. In fact, it made me choose the inheritance
approach to the problem at hand in the first place :-)

Note though that, usually, inheriting is a one-time act --
such as during child table creation. What stays behind is the
legacy - which can be changed (DROP NOT NULL).

I was, then, surprised by the fact that the pg_dump /
pg_restore cycle did not "faithfully" reproduce the child
table. That made me question my ways.

Maybe I shouldn't have been surprised because PG inheritance
doesn't end at table creation time (child and parent are
still linked through data even in the future).

Actually more than that:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

INHERITS ( parent_table [, ... ] )

Use of INHERITS creates a persistent relationship between the new child
table and its parent table(s). Schema modifications to the parent(s)
normally propagate to children as well, and by default the data of the
child table is included in scans of the parent(s).

Meatspace inheritance is more like

CREATE TABLE pseudo_child_table AS SELECT FROM pseudo_parent_table ...

While PG inheritance is a bit more like view-on-steroids.

Thanks,
Karsten

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general