I've been working on a brute force method of saving view and function
source when changes to an underlying object force that object to be
dropped. But I think there is a way for Postgres to handle this that
wouldn't be too hard to implement and would be extremely useful for us
users.

Problem: We need to change the last_name column of the people table from
varchar(30) to varchar(50). You issue the alter table command only to be
reminded that the people table is quite popular. It will likely be used
in dozens of views and many of those views will have dependent views,
and lets say there are some dependent functions too. And you have to
drop all of them if you want to alter your column. Once they are dropped
you can alter your column and then start digging through your source
code repository to rebuild all of those views and functions that you
just dropped.

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;

Oracle invalidates objects without warning. But maybe we could keep the
current behavior and add an invalidate option.

ALTER TABLE people ALTER last_name VARCHAR(50);
-- Throw exception can not alter table with dependents

ALTER TABLE people ALTER last_name VARCHAR(50) INVALIDATE;
-- Alters column and invalidates any dependent objects

Is this a viable option?

Scott Bailey

Search Discussions

  • Takahiro Itagaki at Apr 30, 2010 at 8:04 am

    Scott Bailey 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
  • Robert Haas at Apr 30, 2010 at 12:08 pm

    On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey 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
  • Merlin Moncure at Apr 30, 2010 at 3:21 pm

    On Fri, Apr 30, 2010 at 8:08 AM, Robert Haas wrote:
    On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey 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
  • Scott Bailey at May 3, 2010 at 4:27 am

    Robert Haas wrote:
    On Fri, Apr 30, 2010 at 3:33 AM, Scott Bailey 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
    I've been using the "source" in information_schema.views rather than
    storing the original source. Oracle does indeed store the original
    source code for your objects. I don't know what they use to recompile.
    But my inclination is that they use the original source. If you alter a
    table/column name I believe it will invalidate any dependent views which
    will need manually edited before they will compile successfully.

    As for Oracle's approach being stupid and not user friendly, OK, maybe
    they could automatically try to recompile. But even a manual process is
    better than no help at all.

    Scott
  • Tom Lane at Apr 30, 2010 at 2:38 pm

    Scott Bailey 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
  • Robert Haas at May 2, 2010 at 3:20 am

    On Fri, Apr 30, 2010 at 10:38 AM, Tom Lane wrote:
    Scott Bailey <artacus@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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 30, '10 at 7:33a
activeMay 3, '10 at 4:27a
posts7
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase