pg_dump problem with dropped NOT NULL on child table - Mailing list pgsql-general

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