Search Postgresql Archives

Re: Altering parent table breaks child table defaults

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


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




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux