Thread: 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@thewickedtribe.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@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@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

On Fri, Aug 6, 2010 at 12:31 AM, Jon Erdman (aka StuckMojo)
<postgresql@thewickedtribe.net> wrote:
> Description: =A0 =A0 =A0 =A0Setting NOT NULL on inherited column turns to=
 real
> column in dump
>
> 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.

That sucks.  I have a feeling it's going to be hard to fix properly
without this patch:

https://commitfest.postgresql.org/action/patch_view?id=3D312

I don't think your proposed fix will work because the NOT NULL-ness
could be either inherited or not inherited.  The column could even be
inherited from multiple parents, some of which have a NOT NULL
constraint and others of which do not.  Consider:

create table top1 (a int not null);
create table top2 (a int);
create table bottom () inherits (top1, top2);
alter table bottom no inherit <something>;

If <something> =3D top1, then bottom.a should now allow nulls, but if
<something> =3D top2, then it should still be not null.  Unfortunately,
we don't do enough bookkeeping right now to distinguish those cases.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company