Thread: PG 9.3.x: Dropping a table column breaks restore of dump if table is used as return type of a function used in a view
PG 9.3.x: Dropping a table column breaks restore of dump if table is used as return type of a function used in a view
From
Marc Schablewski
Date:
Hi, we think we might have discovered a problem in the way dropped columns are handled in PostgreSQL 9.3 when using a table as a return type in a function that itself is used in a from clause of a view and dumping the database containing this view. As you can see in the example below, we created a function that returns a set of rows from a table. This function is used in a view that selects each row the function returns. PostgreSQL seems to generate additional column information for the result type of our function when parsing the view definition, which you can see when doing a \d on it. test=> \d+ testv ... View definition: SELECT t.sss1 FROM testt() t(sss1, sss2); Now, if you drop a column in the table and do a describe on the view again, the dropped column gets replaced by a dummy column "<>". test=> \d+ testv ... View definition: SELECT t.sss1 FROM testt() t(sss1, "<>"); This also occurs when you drop the column _before_ creating the function and view! You also cannot remove this dummy column by dropping and recreating the function or view. Up to this point, it's no big deal. You can still use the view as normal. The trouble starts, when you take a dump of that database. As expected, the structure of the table is dumped without the dropped column, but the view definition still contains the dummy column and will produce an error while restoring the dump, because the column count does not match. This happens in both the SQL and the custom dump format of pg_dump. I can't tell if this is a problem with dropped columns still being visible in certain circumstances or a problem in pg_dump which should ignore those columns, but doesn't. So far, the only solution we can think of is to drop and recreate the table without the dropped columns. We tested this on various versions of PostgreSQL (9.3.4, 9.3.2, 9.1.13 and 9.2.6), both on Windows 7 (64bit) and Linux (SuSE and Ubuntu), but only 9.3.x seems to be affected. The Linux versions where compiled from source. On Windows we used the one-click installer. In PostgreSQL 9.2 and older, the dummy column didn't appear in the generated column list to cause any trouble. Kind regards, Marc Schablewski Example script to reproduce the behaviour: create table test (sss1 varchar, sss2 varchar); alter table test drop column sss2; drop function if exists testt(); CREATE OR REPLACE FUNCTION testt() RETURNS setof test AS $body$ declare rec1 test%ROWTYPE; begin for rec1 in (select * from test) loop return next rec1; end loop; return; end; $body$ LANGUAGE 'plpgsql'; drop view if exists testv; create view testv as select t.* from testt() t; \d+ testv