Thread: View definition formatting
Hi all, When using pg_get_viewdef(oid), the view definition is returned in a reconstructed form (I assume) with all formatting removed. This is a pain for apps like pgAdmin, that allow the user to edit their views, particularly with very large ones. Would it be possible and sensible to store the original view definition for future use, such as we do for functions? Perhaps a new catalog (pg_source?) could store these and other definitions such as rules for use? Regards, Dave.
Dave Page wrote: > > Hi all, > > When using pg_get_viewdef(oid), the view definition is returned in a > reconstructed form (I assume) with all formatting removed. This is a > pain for apps like pgAdmin, that allow the user to edit their views, > particularly with very large ones. > > Would it be possible and sensible to store the original view definition > for future use, such as we do for functions? Perhaps a new catalog > (pg_source?) could store these and other definitions such as rules for > use? Not too obvious, but this should be covered in the TODO item "Allow RULE recompilation". That is because if the rule/view is broken due to other schema changes, the reconstruction might fail. Thus we need to save a textual form and the original CREATE statement from the user seems logical. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
Jan Wieck <JanWieck@Yahoo.com> writes:
> Dave Page wrote:
>> Would it be possible and sensible to store the original view definition
>> for future use, such as we do for functions? Perhaps a new catalog
>> (pg_source?) could store these and other definitions such as rules for
>> use?
> Not too obvious, but this should be covered in the TODO item "Allow RULE
> recompilation". That is because if the rule/view is broken due to other
> schema changes, the reconstruction might fail.
Given the dependency mechanism in 7.3, it should no longer be possible
to break a rule that way. Of course, there are cases where you'd wish
the rule to *change* not just reject the update.
The major problem with any such proposal is that source-form storage has
its own set of inflexibilities. For example, we can currently allow
renaming of tables and columns that underlie a view, because the stored
form of the view doesn't contain those names and so it doesn't need to
change. If we store source text then we'd have to forbid such renaming
--- or else update the source text, which seems to require parsing,
adjustment of parsed tree, deparsing; which rather defeats the purpose
of Dave's request.
There are even more subtle problems: the source text may be ambiguous
in some way, so that reparsing it today might not generate the identical
intepretation to what you had before. Even "a+b" is ambiguous given
the possibility that user-defined operators could be added, or the
search path changed. Deparsing compensates for this by producing (or
at least trying to produce) a representation that is correct and
unambiguous in the current context.
One reason I'm disillusioned with this idea is that we do take the
trouble to store both source and internal form of column default
expressions, but in practice pg_attrdef.adsrc has fallen into disuse.
That track record doesn't bode well for adding source-form storage of
other things.
regards, tom lane
Thanks for the responses. I guess I'm going to have to write some view formatting code... :-( Oh well, at least it's for a good reason!! Regards, Dave It's rumoured that Tom Lane once said: > Jan Wieck <JanWieck@Yahoo.com> writes: >> Dave Page wrote: >>> Would it be possible and sensible to store the original view >>> definition for future use, such as we do for functions? Perhaps a new >>> catalog (pg_source?) could store these and other definitions such as >>> rules for use? > >> Not too obvious, but this should be covered in the TODO item "Allow >> RULE recompilation". That is because if the rule/view is broken due to >> other schema changes, the reconstruction might fail. > > Given the dependency mechanism in 7.3, it should no longer be possible > to break a rule that way. Of course, there are cases where you'd wish > the rule to *change* not just reject the update. > > The major problem with any such proposal is that source-form storage > has its own set of inflexibilities. For example, we can currently > allow renaming of tables and columns that underlie a view, because the > stored form of the view doesn't contain those names and so it doesn't > need to change. If we store source text then we'd have to forbid such > renaming --- or else update the source text, which seems to require > parsing, adjustment of parsed tree, deparsing; which rather defeats the > purpose of Dave's request. > > There are even more subtle problems: the source text may be ambiguous > in some way, so that reparsing it today might not generate the > identical intepretation to what you had before. Even "a+b" is > ambiguous given the possibility that user-defined operators could be > added, or the search path changed. Deparsing compensates for this by > producing (or at least trying to produce) a representation that is > correct and > unambiguous in the current context. > > One reason I'm disillusioned with this idea is that we do take the > trouble to store both source and internal form of column default > expressions, but in practice pg_attrdef.adsrc has fallen into disuse. > That track record doesn't bode well for adding source-form storage of > other things. > > regards, tom lane
Dave Page writes: > When using pg_get_viewdef(oid), the view definition is returned in a > reconstructed form (I assume) with all formatting removed. This is a > pain for apps like pgAdmin, that allow the user to edit their views, > particularly with very large ones. Perhaps as a workaround you could invent a standard indentation format and format the rules automatically that way, so that users will be able to find everything in the same place automatically after the second edit cycle. -- Peter Eisentraut peter_e@gmx.net
Peter Eisentraut <peter_e@gmx.net> writes:
> Perhaps as a workaround you could invent a standard indentation format and
> format the rules automatically that way, so that users will be able to
> find everything in the same place automatically after the second edit
> cycle.
Perhaps we could make pg_get_ruledef and friends try to prettyprint
their output a little better, instead of duplicating such logic in
various clients (which couldn't do nearly as well at it anyway without
re-parsing the string :-().
Does anyone have code that depends on these functions returning
single-line output? (I suppose the pg_rules and pg_views views might
get a bit ugly, but perhaps psql could be taught to format data with
embedded newlines better than it does now...)
regards, tom lane
On Tue, 2003-04-01 at 14:18, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Perhaps as a workaround you could invent a standard indentation format and > > format the rules automatically that way, so that users will be able to > > find everything in the same place automatically after the second edit > > cycle. > > Perhaps we could make pg_get_ruledef and friends try to prettyprint > their output a little better, instead of duplicating such logic in > various clients (which couldn't do nearly as well at it anyway without > re-parsing the string :-(). > > Does anyone have code that depends on these functions returning > single-line output? (I suppose the pg_rules and pg_views views might > get a bit ugly, but perhaps psql could be taught to format data with > embedded newlines better than it does now...) > Could we turn the prettyprint incantation into a user callable function? We could easily add that to the phpPgAdmin code to call that function when pulling information from pg_views.definition and it's ilk, this giving a nicely formatted output without having to modify the way it is currently stored. Robert Treat
Robert Treat <xzilla@users.sourceforge.net> writes:
> On Tue, 2003-04-01 at 14:18, Tom Lane wrote:
>> Perhaps we could make pg_get_ruledef and friends try to prettyprint
>> their output a little better, instead of duplicating such logic in
>> various clients (which couldn't do nearly as well at it anyway without
>> re-parsing the string :-().
> Could we turn the prettyprint incantation into a user callable function?
I don't think it's reasonable to implement the prettyprint as a separate
function, if that's what you mean. It would have to re-parse the string
which is exactly what I wanted to avoid. pg_get_ruledef itself already
knows what the statement structure is, and would need very little more
logic to do fairly reasonable pretty-printing.
We could make variants of pg_get_ruledef etc. that pretty-print,
while leaving the existing behavior alone, I suppose.
regards, tom lane
On Tue, 2003-04-01 at 14:18, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Perhaps as a workaround you could invent a standard indentation format and > > format the rules automatically that way, so that users will be able to > > find everything in the same place automatically after the second edit > > cycle. > > Perhaps we could make pg_get_ruledef and friends try to prettyprint > their output a little better, instead of duplicating such logic in > various clients (which couldn't do nearly as well at it anyway without > re-parsing the string :-(). > > Does anyone have code that depends on these functions returning > single-line output? (I suppose the pg_rules and pg_views views might > get a bit ugly, but perhaps psql could be taught to format data with > embedded newlines better than it does now...) I do -- but the only thing it does is format the output to look better. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
> Perhaps we could make pg_get_ruledef and friends try to prettyprint > their output a little better, instead of duplicating such logic in > various clients (which couldn't do nearly as well at it anyway without > re-parsing the string :-(). > > Does anyone have code that depends on these functions returning > single-line output? (I suppose the pg_rules and pg_views views might > get a bit ugly, but perhaps psql could be taught to format data with > embedded newlines better than it does now...) Well, phpPgAdmin kind of assumes that it does, however since it's written to a webpage, it will appear as one line anyway. I would need to just convert newlines to <br>s that's all. The other dependency would be the new rule and trigger defs in that patch I sent in for psql recently. (And was committed). Chris
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 01 April 2003 20:59 > To: Robert Treat > Cc: Peter Eisentraut; Dave Page; pgsql-hackers@postgresql.org > Subject: Re: [HACKERS] View definition formatting > > > We could make variants of pg_get_ruledef etc. that > pretty-print, while leaving the existing behavior alone, I suppose. > Hacking those functions never crossed my mind, but it would certainly do what I require, and is probably more within the scope of things I could do myself. *If* I get time and can figure it out, how would people feel about pg_get_pp_viewdef(oid) etc? Regards, Dave.