BUG #5604: Setting NOT NULL on inherited column turns to real column in dump

The following bug has been logged online:

Bug reference: 5604
Logged by: Jon Erdman (aka StuckMojo)
Email address: postgresql(at)thewickedtribe(dot)net
PostgreSQL version: Tested 9.0, 8.3
Operating system: Ubuntu Lucid 10.04
Description: Setting NOT NULL on inherited column turns to real
column in dump
Details:

I think the fix here is relatively simple: make NOT NULL on an inherited
column dump as an ALTER TABLE.

If you set NOT NULL on an inherited column in a child table, then drop the
column from the parent, it's gone from both and all is well.

However, if you dump and restore the db, then drop the parent column, the
inherited column remains in the child table. This is a result of the NOT
NULL dumping as a column create in the child, which I assume then shadows
the inherited column after restore.

This example illustrates the issue nicely I think:

jon(at)stuck-64:~$ cat bug.sh
createdb foo
psql -X foo <<EOF
begin;
create table parent (i int, j int);
create table child_1 (k int) inherits (parent);
create table child_2 (k int) inherits (parent);
alter table child_2 alter column j set not null;
commit;
begin;
alter table parent drop column j;
\d child_1
\d child_2
rollback;
EOF
createdb bar
pg_dump --schema-only foo
pg_dump foo | psql -X bar
psql -X bar <<EOF
begin;
alter table parent drop column j;
commit;
\d child_1
\d child_2
EOF
dropdb foo
dropdb bar

jon(at)stuck-64:~$ sh bug.sh
BEGIN
CREATE TABLE
CREATE TABLE
CREATE TABLE
ALTER TABLE
COMMIT
BEGIN
ALTER TABLE
Table "public.child_1"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
k | integer |
Inherits: parent

Table "public.child_2"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
k | integer |
Inherits: parent

ROLLBACK
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;

ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

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 (
i integer,
j integer
);

ALTER TABLE public.parent OWNER TO postgres;

--
-- Name: child_1; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

--

CREATE TABLE child_1 (
k integer
)
INHERITS (parent);

ALTER TABLE public.child_1 OWNER TO postgres;

--
-- Name: child_2; Type: TABLE; Schema: public; Owner: postgres; Tablespace:

--

CREATE TABLE child_2 (
j integer NOT NULL,
k integer
)
INHERITS (parent);

ALTER TABLE public.child_2 OWNER TO postgres;

--
-- 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
--

SET
SET
SET
SET
SET
SET
CREATE LANGUAGE
ALTER LANGUAGE
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
REVOKE
REVOKE
GRANT
GRANT
BEGIN
ALTER TABLE
COMMIT
Table "public.child_1"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
k | integer |
Inherits: parent

Table "public.child_2"
Column | Type | Modifiers
--------+---------+-----------
i | integer |
j | integer | not null
k | integer |
Inherits: parent