On Wed, Jan 13, 2016 at 03:32:12PM -0500, Tom Lane wrote: > Karsten Hilbert <Karsten.Hilbert@xxxxxxx> 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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
