CODEWITHSUNDEEP
postgresql
Ovid
Ovid

Reputation: 11677

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 the child table are ignored if the parent table is altered.

How to replicate:

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

Upvotes: 2

Views: 2343

Answers (2)

Richard Huxton
Richard Huxton

Reputation: 22972

It's there all the way to 9.3, but it's going to be tricky to fix, and I'm not sure if it's just undesirable behaviour rather than a bug.

The constraint is still there, but look at the 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+ to 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 your first example because you are effectively doing:

INSERT INTO temp_person_two (person_id,name)
VALUES (person_id, name)

BUT look where your new column is added in the child table - at the end! So you end up with

INSERT INTO temp_person_two (person_id,name,has_default)
VALUES (person_id, name, foo)

rather than what you hoped for:

INSERT INTO temp_person_two (person_id,name,foo)...

So - what's the correct behaviour here? If PostgreSQL shuffled the columns in the child table that could break code. If it doesn't, that can also break code. As it happens, I don't think the first option is do-able without substantial PG code changes, so it's unlikely to do that in the medium term.

Moral of the story: explicitly list your INSERT column-names.

Could take a while by hand. You know any languages with regexes? ;-)

Upvotes: 2

ilmari
ilmari

Reputation: 301

It's not a bug. NEW.* expands to the values of each column in the new row, so you're doing INSERT INTO temp_person_two VALUES ( NEW.person_id, NEW.name, NEW.foo ), the last of which is indeed NULL if you didn't specify it (and wrong if you did).

I'm surprised it even works before you added the new column, since the number of values doesn't match the number of fields in the child table. Presumably it assumes the default for missing trailing values.

Upvotes: 0

Related Questions