Re: pg_dump problem with dropped NOT NULL on child table

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

> Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)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
> http://www.postgresql.org/message-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