Обсуждение: pg_dump problem with dropped NOT NULL on child table
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
;
Resulting in (as expected):
postgres@hermes:/tmp$ psql -d test
Ausgabeformat ist „wrapped“.
psql (9.5.0, Server 9.4.5)
Geben Sie „help“ für Hilfe ein.
test=# \d parent
Tabelle „public.parent“
Spalte | Typ | Attribute
--------------------+---------+-----------
not_null_in_parent | integer | not null
Anzahl Kindtabellen: 1 (Mit \d+ alle anzeigen.)
test=# \d child
Tabelle „public.child“
Spalte | Typ | Attribute
--------------------+---------+-----------
not_null_in_parent | integer |
Erbt von: parent
But getting dumped as (note the re-appearing NOT NULL
constraint on child):
--------------------------------------------------
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:
--
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:
--
COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- Name: parent; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE parent (
not_null_in_parent integer NOT NULL
);
ALTER TABLE parent OWNER TO postgres;
--
-- Name: child; Type: TABLE; Schema: public; Owner: postgres; Tablespace:
--
CREATE TABLE child (
)
INHERITS (parent);
ALTER TABLE child OWNER TO postgres;
--
-- Data for Name: child; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY child (not_null_in_parent) FROM stdin;
\.
--
-- Data for Name: parent; Type: TABLE DATA; Schema: public; Owner: postgres
--
COPY parent (not_null_in_parent) FROM stdin;
\.
--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
--
-- PostgreSQL database dump complete
--
--------------------------------------------------
Is this a bug or am I doing things I shouldn't hope work ?
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
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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
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 http://www.postgresql.org/message-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
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
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
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