Altering a parent table in Postgresql 8.4 breaks child table defaults -

the problem: in postgresql, if table temp_person_two inherits fromtemp_person, default column values on child table ignored if parent table altered.

how replicate:

first, create table , child table. child table should have 1 column has 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 trigger on parent table copies data child table (i know appears bad design, minimal test case show problem).

create function temp_person_insert() returns trigger language plpgsql ' begin insert temp_person_two values ( new.* ); return null; end; ';  create trigger temp_person_insert_trigger     before insert on temp_person     each row     execute procedure temp_person_insert(); 

then insert data parent , select data child. data should correct.

insert temp_person (name) values ('ovid'); select * temp_person_two;  person_id | name | has_default -----------+------+-------------          1 | ovid | en (1 row ) 

finally, alter parent table adding new, unrelated column. attempt insert data , watch "not-null constraint" violation occur:

alter table temp_person add column foo text; insert temp_person(name) values ('corinna'); error:  null value in column "has_default" violates not-null constraint context:  sql statement "insert temp_person_two values (  $1 .* )" pl/pgsql function "temp_person_insert" line 2 @ sql statement 

my version:

testing=# select version();                                                 version -------------------------------------------------------------------------------------------------------  postgresql 8.4.17 on x86_64-pc-linux-gnu, compiled gcc gcc-4.4.real (debian 4.4.5-8) 4.4.5, 64-bit (1 row) 

it's there way 9.3, it's going tricky fix, , i'm not sure if it's undesirable behaviour rather bug.

the constraint still there, @ column-order.

                                  table "pg_temp_2.temp_person"   column   |       type        |                            modifiers                             -----------+-------------------+-----------------------------------------------------------------  person_id | integer           | not null default nextval('temp_person_person_id_seq'::regclass)  name      | character varying |  number of child tables: 1 (use \d+ list them.)                                    table "pg_temp_2.temp_person_two"    column    |         type         |                            modifiers                             -------------+----------------------+-----------------------------------------------------------------  person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)  name        | character varying    |   has_default | character varying(4) | not null default 'en'::character varying inherits: temp_person  alter table                                   table "pg_temp_2.temp_person_two"    column    |         type         |                            modifiers                             -------------+----------------------+-----------------------------------------------------------------  person_id   | integer              | not null default nextval('temp_person_person_id_seq'::regclass)  name        | character varying    |   has_default | character varying(4) | not null default 'en'::character varying  foo         | text                 |  inherits: temp_person 

it works in first example because doing:

insert temp_person_two (person_id,name) values (person_id, name) 

but new column added in child table - @ end! end with

insert temp_person_two (person_id,name,has_default) values (person_id, name, foo) 

rather hoped for:

insert temp_person_two (person_id,name,foo)... 

so - what's correct behaviour here? if postgresql shuffled columns in child table break code. if doesn't, can break code. happens, don't think first option do-able without substantial pg code changes, it's unlikely in medium term.

moral of story: explicitly list insert column-names.

could take while hand. know languages regexes? ;-)