Thread: Altering parent table breaks child table defaults

Altering parent table breaks child table defaults

From

Ovid

Date:

07 October 2013, 19:49:24

Hi all,

This problem has also been posted to Stack Overflow. http://stackoverflow.com/questions/19227920/altering-a-parent-table-in-postgresql-8-4-breaks-child-table-defaults

The problem: In Postgresql 8.4 (not tested on other versions), if table `temp_person_two` inherits from`temp_person`, default column values on the child table are ignored if the *parent* table is altered.

How to replicate (these don't need to be temporary tables. I made them temporary for your cut-n-paste convenience):

First, create table and a child table. The child table should have one column that has a default value.

CREATE TEMPORARY TABLE temp_person (

person_id SERIAL,

name VARCHAR

);

CREATE TEMPORARY TABLE temp_person_two (

has_default character varying(4) DEFAULT 'en'::character varying NOT NULL

) INHERITS (temp_person);

Next, create a trigger on the parent table that copies its data to the child table (I know this appears like bad design, but this is a minimal test case to show the problem).

CREATE FUNCTION temp_person_insert() RETURNS trigger

LANGUAGE plpgsql

AS '

BEGIN

INSERT INTO temp_person_two VALUES ( NEW.* );

RETURN NULL;

END;

CREATE TRIGGER temp_person_insert_trigger

BEFORE INSERT ON temp_person

FOR EACH ROW

EXECUTE PROCEDURE temp_person_insert();

Then insert data into parent and select data from child. The data should be correct.

INSERT INTO temp_person (name) VALUES ('ovid');

SELECT * FROM temp_person_two;

person_id | name | has_default

-----------+------+-------------

1 | ovid | en

(1 row )

Finally, alter the parent table by adding a new, unrelated column. Attempt to insert data and watch a "not-null constraint" violation occur:

ALTER TABLE temp_person ADD column foo text;

INSERT INTO temp_person(name) VALUES ('Corinna');

ERROR: null value in column "has_default" violates not-null constraint

CONTEXT: SQL statement "INSERT INTO temp_person_two VALUES ( $1 .* )"

PL/pgSQL function "temp_person_insert" line 2 at SQL statement

My version:

testing=# select version();

version

-------------------------------------------------------------------------------------------------------

PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

(1 row)

Cheers,

Ovid

--
IT consulting, training, international recruiting
http://www.allaroundtheworld.fr/.
Buy my book! - http://bit.ly/beginning_perl
Live and work overseas - http://www.overseas-exile.com/

Re: Altering parent table breaks child table defaults

From

Adrian Klaver

Date:

08 October 2013, 01:02:13

On 10/07/2013 07:51 AM, Ovid wrote:
> Hi all,
>
> This problem has also been posted to Stack
> Overflow.
http://stackoverflow.com/questions/19227920/altering-a-parent-table-in-postgresql-8-4-breaks-child-table-defaults
>
> The problem: In Postgresql 8.4 (not tested on other versions), if table
> `temp_person_two` inherits from`temp_person`, default column values on
> the child table are ignored if the *parent* table is altered.
>
> How to replicate (these don't need to be temporary tables. I made them
> temporary for your cut-n-paste convenience):
>
> First, create table and a child table. The child table should have one
> column that has a default value.
>
>      CREATE TEMPORARY TABLE temp_person (
>          person_id SERIAL,
>          name      VARCHAR
>      );
>
>      CREATE TEMPORARY TABLE temp_person_two (
>          has_default character varying(4) DEFAULT 'en'::character
> varying NOT NULL
>      ) INHERITS (temp_person);
>
> Next, create a trigger on the parent table that copies its data to the
> child table (I know this appears like bad design, but this is a minimal
> test case to show the problem).
>
>      CREATE FUNCTION temp_person_insert() RETURNS trigger
>      LANGUAGE plpgsql
>      AS '
>      BEGIN
>      INSERT INTO temp_person_two VALUES ( NEW.* );
>      RETURN NULL;
>      END;
>      ';
>
>      CREATE TRIGGER temp_person_insert_trigger
>          BEFORE INSERT ON temp_person
>          FOR EACH ROW
>          EXECUTE PROCEDURE temp_person_insert();
>
> Then insert data into parent and select data from child. The data should
> be correct.
>
>      INSERT INTO temp_person (name) VALUES ('ovid');
>      SELECT * FROM temp_person_two;
>       person_id | name | has_default
>      -----------+------+-------------
>               1 | ovid | en
>      (1 row )
>
>
> Finally, alter the parent table by adding a new, unrelated column.
> Attempt to insert data and watch a "not-null constraint" violation occur:
>
>      ALTER TABLE temp_person ADD column foo text;
>      INSERT INTO temp_person(name) VALUES ('Corinna');
>      ERROR:  null value in column "has_default" violates not-null constraint
>      CONTEXT:  SQL statement "INSERT INTO temp_person_two VALUES (  $1 .* )"
>      PL/pgSQL function "temp_person_insert" line 2 at SQL statement

What happens if you do?:

INSERT INTO temp_person(name, has_default) VALUES ('Corinna', DEFAULT)


My guess is the problem is the expansion of NEW.* is leading to
VALUES('Corina', NULL)

>
> My version:
>
>      testing=# select version();
>                                                      version
>
> -------------------------------------------------------------------------------------------------------
>       PostgreSQL 8.4.17 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
>      (1 row)
>
> Cheers,
> Ovid
> --
> IT consulting, training, international recruiting
>         http://www.allaroundtheworld.fr/.
> Buy my book! - http://bit.ly/beginning_perl
> Live and work overseas - http://www.overseas-exile.com/


--
Adrian Klaver
adrian.klaver@gmail.com