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

On Mon, May 26, 2014 at 06:25:09PM +0400, ash wrote:
> Hi Hackers,
> 
> This came up recently on general list (and I've just hit the same issue today):
>   http://www.postgresql.org/message-id/CAB7nPqTLmMn1LTb5WE0v0dO57iP0U73yKwzbZytAXDF1CAWLZg@mail.gmail.com
> 
> Why couldn't postgres re-create the dependent views automatically?  I
> mean it knows which views depend on the altered column and keeps the
> view definition, no?

Also worth considering: functions which take any part of the view as a
parameter.

Not, IMHO, worth considering, are functions like this:

CREATE OR REPLACE FUNCTION make_task_impossible_for_alex()
RETURNS int8
LANGUAGE plpgsql
AS $$   DECLARE       foo text[] := ARRAY['list','of','views','here'];   BEGIN       EXECUTE 'SELECT COUNT(*) FROM %',
foo[floor(random()*array_upper(foo,1))];  END;
 
$$;

That counts pretty strictly as pilot error, not least because it makes
things like you want to write not just hard, but impossible.

> Would a patch likely to be accepted?  How hard do you feel this might be
> to implement?

In the general case, impossible.  In most sane cases, mostly a matter
of chasing down dependencies, which is harder than it first appears,
as anyone who's worked on that part of pg_dump can tell you.

> Any caveat

Locking.  Given that, you'd want this behavior only with CASCADE, per
Stephen's response.

> that defeats the purpose of such feature?

Probably not.  I'd certainly like to have the feature :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate