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.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
> 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
ONDELETE RESTRICT 
        "clin_root_item_fk_episode_fkey" FOREIGN KEY (fk_episode) REFERENCES clin.episode(pk) ON UPDATE CASCADE ON
DELETERESTRICT 
    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_episodeIS 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
EACHROW 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
IMMEDIATEFOR 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-clinicalitem 

    :-)

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