[GENERAL] pg_dump problem with dropped NOT NULL on child table

[Karsten Hilbert]

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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general