Re: Re-create dependent views on ALTER TABLE ALTER COLUMN ... TYPE?

ash wrote:
>
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> >
> > We don't store dependency information for function bodies, so there's
> > no way to do this except by reparsing everything in sight.

> OK, forget functions, I now realize it's not feasible to consider.
>
> Can we get back to re-defining views at least?

Hi Alex,

I think it's reasonable to try and fix the problems for views (and other
objects -- there are other things that can depend on table definitions;
composite types come to mind) and ignore functions bodies, since you can
already get into trouble by using ALTER TABLE today and it's known to be
an unsolvable problem.

Now -- do we need to do anything about tables used as return types or
argument types for functions?

alvherre=# create table qux (a int, b text);
CREATE TABLE
alvherre=# create or replace function test_qux(a qux) returns void language plpgsql as $$ begin raise notice 'the qux we got is %', $1; end; $$;
CREATE FUNCTION
alvherre=# insert into qux values (1, 'one');
INSERT 0 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one)
a | b | test_qux
---+-----+----------
1 | one |
(1 fila)

alvherre=# alter table qux add column c timestamptz;
ALTER TABLE
alvherre=# update qux set c = now();
UPDATE 1
alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,)
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Notice how the NOTICE has a final comma, meaning the tuple descriptor is
aware that there is a third column -- but the value in the table is not
null per the UPDATE, so the fact that there's nothing after the comma
means this is not being handled correctly. If I close the session and
start a fresh one, the result is saner:

alvherre=# select * from qux, test_qux(qux.*);
NOTICE: the qux we got is (1,one,"2014-05-28 12:08:28.210895-04")
a | b | c | test_qux
---+-----+-------------------------------+----------
1 | one | 2014-05-28 12:08:28.210895-04 |
(1 fila)

Maybe we're missing a function cache invalidation or something like
that.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services