Re: POSS. FEATURE REQ: "Dynamic" Views

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> "Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> >> How is this different from materialized views, which is already on the
> >> TODO list?
>
> > The idea behind the DYNAMIC VIEW is that if you made a DDL change in the
> > table it could be reflected in the view. So for example, if you defined
> > a view as SELECT * FROM table; and then added a field to the table that
> > field would also show up in the view.
>
> But why exactly is this a good idea? It seems like an absolutely
> horrible idea to me. It is oft-repeated advice that you don't use
> "SELECT *" ever in production programming, because your applications
> will break as soon as any columns are added (or removed, even if they
> don't make any use of those columns). The proposed dynamic view
> facility would move that instability of results right into the views.

Just because something is oft-repeated doesn't make it good advice. I am
convinced that advice originates in the fact that many databases handled
"select *" very poorly. These other databases often had limitations like
having it produce errors or even incorrect results if the underlying table was
changed.

From a programming aesthetics point of view it's downright important to use
it. Not using it forces the programmer to distribute knowledge about columns
and how they will be used throughout many more layers of programming than
otherwise necessary. If

Far from breaking as soon as columns are added or removed, the use of select *
insulates the application from the changes. I can add a column to my front-end
templates without having to modify every layer below it. Or can add a column
to a database and use it immediately in the front-end without modifying every
layer in between.

> What's more, I cannot see any benefit to be gained over just issuing
> the expanded query directly. You couldn't layer a normal view over
> a dynamic view (not having any idea what columns it'll return), nor
> even a prepared statement, because those things nail down specific
> result columns too. So it's just an awkwardly expressed form of
> query macro that can only be used in interactively-issued commands.

I think we have two different ideas of what we're talking about. I'm talking
about absolutely normal views. They can be used in the same ways and behave
the same as normal views.

I'm just suggesting adding a command that would do exactly the same thing as
having the user issue a "CREATE OR REPLACE VIEW" with the exact same
definition text as originally used.

The point here is to give a user an out who would otherwise be completely
stuck. If he didn't save the original view definition text he has to now
reverse engineer what was intended from the reconstructed view definition that
pg_dump gives which isn't always obvious.

> I think the burden of proof is on the proponents of this idea to show
> that it's sensible, and it doesn't deserve to be in TODO just because
> one or two people think it'd be nice.

I think, given the confusion shown by myself and this other user, that the
evidence is there that the spec behaviour violates the principle of least
surprise and warrants warnings. I think just about any time these warnings
would be fire there's a better than 50% chance the programmer is about to be
bitten by a nasty surprise.

The "alter view recompile" is the thing one or two people think would be nice.
I can offer a use case for "alter view recompile" that might be somewhat more
convincing than hand waving:

Consider the case of someone who has a large growing table with log records.
He wants to periodically rotate it out and start a fresh table. Much like what
logrotate does for files. Now any view on that table will follow the renamed
table instead of using the fresh new table. If the user doesn't keep around a
complete DDL definition for the table he can't even fix the problem robustly.
He has to try to reconstruct all the views and hopefully get their definitions
right.

In fact the situation is quite similar to the situation with daemons that
don't reopen their log files regularly. In those cases however those daemons
invariably support reopening their log files on some even like kill -HUP. This
is precisely because restarting the daemon is intrusive and error prone, just
as having to reconstruct the view definitions from scratch would be.

--
greg