Invalidating dependent views and functions
in [PgSql]
|
From: Takahiro Itagaki on 30 Apr 2010 04:04 Scott Bailey <artacus(a)comcast.net> wrote: > Problem: We need to change the last_name column of the people table from > varchar(30) to varchar(50). > Proposal: Add an invalid flag to pg_class. Your example is one of the simplest cases, but there are other complex usages. For example, shrinking varchar length, altering indexed columns, CREATE FUNCTION RETURNS altered_table_type, and so on. Can your proposal solve all (or almost all) use-cases? I think we need to have such flag fields for each catalog tables if we support invalid status. > ALTER TABLE people ALTER last_name VARCHAR(50) INVALIDATE; > -- Alters column and invalidates any dependent objects IMHO, I don't like the "invalid" flags. If we can recompile objects later, why don't we recomple them at the same time? ALTER TABLE people ALTER last_name TYPE varchar(50) CASCADE; -- Alters column and *recompile* any dependent objects However, dependent objects are not only in the database, but also in the client applications. That's why we allow CREATE OR REPLACE VIEW only to add columns, but disallow to modify existing columns. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 30 Apr 2010 08:08 On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey <artacus(a)comcast.net> wrote: > Proposal: Add an invalid flag to pg_class. Invalid objects would be ignored > when doing dependency checks for DDL statements. And an exception would be > thrown when an invalid object is called. > > This is similar to what Oracle does. And most Oracle tools have find and > compile invalid objects with a statement like: > ALTER VIEW foo RECOMPILE; > ALTER PACKAGE bar RECOMPILE BODY; Keep in mind that our implementation is apparently quite different from Oracle's. Of course I have no idea what they do under the hood, but we don't even store the original text of the view. Instead, we store a parsed version of the view text that refers to the target objects logically rather than by name. That has some advantages; for example, you can rename a column in some other table that the view uses, and nothing breaks. You can rename a whole table that is used by the view, and nothing breaks. Even if we added storage for the text of the view, recompiling it might result in some fairly astonishing behavior - you might suddenly be referring to tables or columns that were quite different from the ones you originally targeted, if the old ones were renamed out of the way and new, eponymous ones were added. I'm familiar with the view-dependency-hell problem you mention, having fought with it (succesfully, I'm pleased to say, using a big Perl script to manage things - and also - obligatory dig here - to work around our lack of support for CREATE IF NOT EXISTS) on many occasions, but I don't have any brilliant ideas about how to solve it. I would like to eventually support ALTER VIEW ... DROP COLUMN; note that we do now support ADDING columns to a view using CREATE OR REPLACE as long as all the new ones are at the end. But neither of those things is going to help with a case like yours, when you want to change the type of the column. I'm not really sure what to do about that case. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 30 Apr 2010 10:38 Scott Bailey <artacus(a)comcast.net> writes: > Proposal: Add an invalid flag to pg_class. Invalid objects would be > ignored when doing dependency checks for DDL statements. And an > exception would be thrown when an invalid object is called. IMO, the way Oracle does this pretty much sucks, and shouldn't be emulated. If they know how to recompile the view, why don't they just do it? What you describe is about as user-unfriendly as it gets. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 30 Apr 2010 11:21 On Fri, Apr 30, 2010 at 8:08 AM, Robert Haas <robertmhaas(a)gmail.com> wrote: > On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey <artacus(a)comcast.net> wrote: >> Proposal: Add an invalid flag to pg_class. Invalid objects would be ignored >> when doing dependency checks for DDL statements. And an exception would be >> thrown when an invalid object is called. >> >> This is similar to what Oracle does. And most Oracle tools have find and >> compile invalid objects with a statement like: >> ALTER VIEW foo RECOMPILE; >> ALTER PACKAGE bar RECOMPILE BODY; > > Keep in mind that our implementation is apparently quite different > from Oracle's. Of course I have no idea what they do under the hood, > but we don't even store the original text of the view. Instead, we > store a parsed version of the view text that refers to the target > objects logically rather than by name. That has some advantages; for > example, you can rename a column in some other table that the view > uses, and nothing breaks. You can rename a whole table that is used > by the view, and nothing breaks. Even if we added storage for the > text of the view, recompiling it might result in some fairly > astonishing behavior - you might suddenly be referring to tables or > columns that were quite different from the ones you originally > targeted, if the old ones were renamed out of the way and new, > eponymous ones were added. > > I'm familiar with the view-dependency-hell problem you mention, having > fought with it (succesfully, I'm pleased to say, using a big Perl > script to manage things - and also - obligatory dig here - to work > around our lack of support for CREATE IF NOT EXISTS) on many > occasions, but I don't have any brilliant ideas about how to solve it. > I would like to eventually support ALTER VIEW ... DROP COLUMN; note > that we do now support ADDING columns to a view using CREATE OR > REPLACE as long as all the new ones are at the end. But neither of > those things is going to help with a case like yours, when you want to > change the type of the column. I'm not really sure what to do about > that case. We discussed keeping view sources for invalidation purposes in depth earlier. The main takeaway was that recompiling view sources simply doesn't work: if your view definition is: 'select * from table', the recompile would add fields to the view which SQL (unfortunately) expressly forbids. This is maybe solvable, but complicated. aside: I've been lobbying for (somefoo).* to NOT do this, that is, that is allow it to pick up extra fields on somefoo as they appear, with not so great results so far. I happen to think that the way functions are invalidated right now based on table changes actually work pretty well. Plans are invalidated appropriately and functions are dropped if you suffer major argument changes. Before thinking about improving this, you have to grapple with (for starters) the mess of interactions with search_path and function definitions. IOW, functions not getting planned until they are used is a nice property. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 1 May 2010 23:20 On Fri, Apr 30, 2010 at 10:38 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Scott Bailey <artacus(a)comcast.net> writes: >> Proposal: Add an invalid flag to pg_class. Invalid objects would be >> ignored when doing dependency checks for DDL statements. And an >> exception would be thrown when an invalid object is called. > > IMO, the way Oracle does this pretty much sucks, and shouldn't be > emulated. If they know how to recompile the view, why don't they > just do it? What you describe is about as user-unfriendly as it > gets. I agree that the way Oracle does it pretty much sucks. On the other hand, I also sympathize with the OP's difficulties in managing a large nest of views. I'm not really sure what can be done to improve the situation, but it would be nice to come up with some better ideas. It would almost be nice if there were a way to do ALTER TABLE ... ALTER COLUMN ... TYPE ... CASCADE and have it trickle down into the dependent views, but that might be too much black magic (or just too hard to implement). Still, I don't really have a better idea. ....Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
|
Next
|
Last
Pages: 1 2 Prev: [HACKERS] Invalidating dependent views and functions Next: [HACKERS] Patch for PKST timezone |