pg_dump problem with dropped NOT NULL on child table

[Karsten Hilbert <Karsten.Hilbert@xxxxxxx>]

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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general