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

Поиск
Список
Период
Сортировка
От Marc Schablewski
Тема 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
Дата
Msg-id 53C93986.80904@clickware.de
обсуждение исходный текст
Список pgsql-bugs
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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: eshkinkot@gmail.com
Дата:
Сообщение: BUG #10989: log_statement = ddl does not log ddl statements from stored functions
Следующее
От: andrew.pennebaker@gmail.com
Дата:
Сообщение: BUG #10991: psql -c ignores my pager settings in ~/.psqlrc