Thread: Dumping of views -- done!
Hi all Is it too late to add a feature to pg_dump for 6.4?? I just spent most of the day learning pg_dump and modifing it so it would dump views also. This is the first time I have ever contributed any code changes, so I'm not sure of how to submit it. The diff's and a readme as a tgz file are attached. Thanks Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
Attachment
> Hi all > > Is it too late to add a feature to pg_dump for 6.4?? > > I just spent most of the day learning pg_dump and modifing it so it would > dump views also. > > This is the first time I have ever contributed any code changes, so I'm > not sure of how to submit it. > > The diff's and a readme as a tgz file are attached. > > Thanks > Terry Mackintosh <terry@terrym.com> http://www.terrym.com > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. I though we dumped views already. If not, I am inclinded to apply it, but want to see what others say. I thought Jan was working on this, or was that just rewrite rules? Are you using his code? I know there is an item on the Open Items list that talks about this: have psql dump out rules text with new function Is that what this is? -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hi Bruce On Sat, 3 Oct 1998, Bruce Momjian wrote: > > Hi all > > > > Is it too late to add a feature to pg_dump for 6.4?? > I though we dumped views already. If not, I am inclinded to apply it, OH, ... well, if it does, I sure would like to know how, I missed it. > but want to see what others say. I thought Jan was working on this, or > was that just rewrite rules? Are you using his code? I know there is I tryed to stay as much in style with the code that was there as possible. > an item on the Open Items list that talks about this: > > have psql dump out rules text with new function > Is that what this is? I'm not sure that is the same, what my change does is add: CREATE VIEW viewname AS select clause......; to the dump file. I did notice that if used with '-z' it also puts the revoke grant statments there too. Not my doing, just nice. At this time there are no provisions (by me any way) to dump any associated update/delete rules on the view. Unless, like -z above, it already works? To the best of my knowlage this feature does not yet exist, if any one else is working on it, I don't know. > -- > Bruce Momjian | http://www.op.net/~candle > maillist@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > Have a great day Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
Hi Bruce
On Sat, 3 Oct 1998, Bruce Momjian wrote:
> I though we dumped views already. If not, I am inclinded to apply it,
Your comment gave me cause to go read the pg_dump man page again, here is
what it has to say:
rules and views
pg_dump does not understand user-defined rules and
views and will fail to dump them properly. (This
is due to the fact that rules are stored as plans
in the catalogs and not textually)
Now, I did not (yet) do any thing about rules, only views.
Have a great day
Terry Mackintosh <terry@terrym.com> http://www.terrym.com
sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
-------------------------------------------------------------------
Success Is A Choice ... book by Rick Patino, get it, read it!
On Sat, 3 Oct 1998, Bruce Momjian wrote: > > Hi all > > > > Is it too late to add a feature to pg_dump for 6.4?? > > > > I just spent most of the day learning pg_dump and modifing it so it would > > dump views also. > > > > This is the first time I have ever contributed any code changes, so I'm > > not sure of how to submit it. > > > > The diff's and a readme as a tgz file are attached. > > > > Thanks > > Terry Mackintosh <terry@terrym.com> http://www.terrym.com > > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. > > I though we dumped views already. If not, I am inclinded to apply it, > but want to see what others say. As this does not affect, in any way, shape or form, the backend...I'd be inclined to say go for it also... Marc G. Fournier scrappy@hub.org Systems Administrator @ hub.org scrappy@{postgresql|isc}.org ICQ#7615664
Terry Mackintosh wrote:
>
> Hi Bruce
>
> On Sat, 3 Oct 1998, Bruce Momjian wrote:
>
> > > Hi all
> > >
> > > Is it too late to add a feature to pg_dump for 6.4??
>
> > I though we dumped views already. If not, I am inclinded to apply it,
>
> OH, ... well, if it does, I sure would like to know how, I missed it.
>
No we didn't. I just mentioned that it would be possible with
the new rule utility functions.
> > but want to see what others say. I thought Jan was working on this, or
> > was that just rewrite rules? Are you using his code? I know there is
>
> I tryed to stay as much in style with the code that was there as possible.
>
Took a look at the patch. Terry's using the new system view
pg_views for it. But...
> > an item on the Open Items list that talks about this:
> >
> > have psql dump out rules text with new function
> > Is that what this is?
>
> I'm not sure that is the same, what my change does is add:
> CREATE VIEW viewname AS select clause......;
> to the dump file.
>
> I did notice that if used with '-z' it also puts the revoke grant
> statments there too. Not my doing, just nice.
>
> At this time there are no provisions (by me any way) to dump any
> associated update/delete rules on the view. Unless, like -z above, it
> already works?
>
> To the best of my knowlage this feature does not yet exist, if any one
> else is working on it, I don't know.
Not me. But I think I should assist since I'm one of the
rulegurus here on the list.
IMHO the better way would be to dump views as regular CREATE
TABLE statements and just omit the data (views have none).
After all tables are restored and all functions, operators
and operator classes are created, we should dump rules using
the pg_rules view.
The checks in pg_dump if a table is a view rely on the
existence of a rule named _RETtablename. I'll add another
check to rewriteDefine.c making sure that a rewrite rule ON
SELECT follows this convention (rules ON SELECT are now
restricted to view rules at all so it would make sense for
me).
I'll add double quotes around identifiers in the output of
pg_get_ruledef() and pg_get_viewdef().
Terry, would you mind to implement dumping of views again in
the above mentioned way? This would cover rewrite rules at
all. A good place to dump rules would be where triggers get
dumped (if they are dumped at all). Dumping all rules would
automagically turn the formerly created tables into views.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Hi Jan On Mon, 5 Oct 1998, Jan Wieck wrote: > Took a look at the patch. Terry's using the new system view > pg_views for it. But... > > IMHO the better way would be to dump views as regular CREATE > TABLE statements and just omit the data (views have none). > After all tables are restored and all functions, operators > and operator classes are created, we should dump rules using > the pg_rules view. > Well, if you see need to do it differant, you most certainly know more about it then me, so sure. I am confused though .... How is "CREATE TABLE ...." going to become "CREATE VIEW ....."???? Note also, that the way I did it kept in mind that some day we will have outer joins, at which time most of the code I put in getTables() can come out, and just make a very minor change to the main query to get the view info all at one time. If there is a cleaner way to do it though, then that is the way to do it, I just do not understand what it is you said. I am lacking time today to really think about it, but will take time tonight maybe to look at pg_rules and such. Have a great day Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
> Hi all > > Is it too late to add a feature to pg_dump for 6.4?? > > I just spent most of the day learning pg_dump and modifing it so it would > dump views also. > > This is the first time I have ever contributed any code changes, so I'm > not sure of how to submit it. > > The diff's and a readme as a tgz file are attached. Applied, and I have updated the manual pages, and the sgml docs. Yoo, whooo. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> Applied, and I have updated the manual pages, and the sgml docs. Yoo,
> whooo.
That sgml is pretty exciting, eh? Now calm down and get back to work :)
- Tom
> > Applied, and I have updated the manual pages, and the sgml docs. Yoo, > > whooo. > > That sgml is pretty exciting, eh? Now calm down and get back to work :) I actually have a hardcover SGML book, called "Readme.1st: SGML for Writers and Editors". I have not really looked through it, but it was only $7. It is a Prentice Hall book, 1996. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
> I actually have a hardcover SGML book, called "Readme.1st: SGML for > Writers and Editors". Without having seen the book, I will make the comment that the SGML books I've run into (not very many, but that won't stop me from generalizing :) tend to tell you what you could do with SGML, rather than telling you how to do something with the particular implementation we are using (DocBook). The fellow who wrote and maintains the "Modular Style Sheets", Norm Walsh, is in the middle of writing a book (Nutshell? can't remember) on DocBook, and presumably his style sheets. The most useful single information source I've found so far, other than stealing markup from other parts of the documentation, is the DocBook element dictionary at the O'Reilly site: http://www.ora.com/homepages/dtdparse/docbook/3.0/ I hope this is mentioned in our docguide... - Tom
I haven't seen any comments on this yet...am tempted to add it in, considering we have another 2 weeks before release, and it doesn't affect the backend... Anyone want to comment? If I hear nothing by, say, Monday morning, I'll apply these... On Sat, 3 Oct 1998, Terry Mackintosh wrote: > Hi all > > Is it too late to add a feature to pg_dump for 6.4?? > > I just spent most of the day learning pg_dump and modifing it so it would > dump views also. > > This is the first time I have ever contributed any code changes, so I'm > not sure of how to submit it. > > The diff's and a readme as a tgz file are attached. > > Thanks > Terry Mackintosh <terry@terrym.com> http://www.terrym.com > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. > > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 > ------------------------------------------------------------------- > Success Is A Choice ... book by Rick Patino, get it, read it! > Marc G. Fournier scrappy@hub.org Systems Administrator @ hub.org scrappy@{postgresql|isc}.org ICQ#7615664
Hi Marc On Mon, 12 Oct 1998, Marc G. Fournier wrote: > > I haven't seen any comments on this yet...am tempted to add it in, > considering we have another 2 weeks before release, and it doesn't affect > the backend... Hugh?-) There were lots, any way it prompted Jan to do some major work that did this and much more, so it is all done as far as I know. Thanks for asking though, have a great night. > Anyone want to comment? If I hear nothing by, say, Monday morning, I'll > apply these... > > On Sat, 3 Oct 1998, Terry Mackintosh wrote: > > > Hi all > > > > Is it too late to add a feature to pg_dump for 6.4?? Terry Mackintosh <terry@terrym.com> http://www.terrym.com sysadmin/owner Please! No MIME encoded or HTML mail, unless needed. Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3 ------------------------------------------------------------------- Success Is A Choice ... book by Rick Patino, get it, read it!
>
>
> I haven't seen any comments on this yet...am tempted to add it in,
> considering we have another 2 weeks before release, and it doesn't affect
> the backend...
>
> Anyone want to comment? If I hear nothing by, say, Monday morning, I'll
> apply these...
>
Stop - don't apply.
Dumping of views is already done in the current CVS. Pg_dump
now outputs CREATE TABLE for view relations too, but no data.
At the end of the schema (after data for real tables has been
filled in) it outputs commands to recreate ALL rewrite rules,
silently turning the tables into the views they where at dump
time.
>
>
>
>
> On Sat, 3 Oct 1998, Terry Mackintosh wrote:
>
> > Hi all
> >
> > Is it too late to add a feature to pg_dump for 6.4??
> >
> > I just spent most of the day learning pg_dump and modifing it so it would
> > dump views also.
> >
> > This is the first time I have ever contributed any code changes, so I'm
> > not sure of how to submit it.
> >
> > The diff's and a readme as a tgz file are attached.
> >
> > Thanks
> > Terry Mackintosh <terry@terrym.com> http://www.terrym.com
> > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
> >
> > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
> > -------------------------------------------------------------------
> > Success Is A Choice ... book by Rick Patino, get it, read it!
> >
>
> Marc G. Fournier scrappy@hub.org
> Systems Administrator @ hub.org
> scrappy@{postgresql|isc}.org ICQ#7615664
>
>
>
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
On Tue, 13 Oct 1998, Jan Wieck wrote:
> >
> >
> > I haven't seen any comments on this yet...am tempted to add it in,
> > considering we have another 2 weeks before release, and it doesn't affect
> > the backend...
> >
> > Anyone want to comment? If I hear nothing by, say, Monday morning, I'll
> > apply these...
> >
>
> Stop - don't apply.
>
> Dumping of views is already done in the current CVS. Pg_dump
> now outputs CREATE TABLE for view relations too, but no data.
> At the end of the schema (after data for real tables has been
> filled in) it outputs commands to recreate ALL rewrite rules,
> silently turning the tables into the views they where at dump
> time.
D'oh...I hate losing a hard drive :( catching up takes soooo long
to do :(
>
> >
> >
> >
> >
> > On Sat, 3 Oct 1998, Terry Mackintosh wrote:
> >
> > > Hi all
> > >
> > > Is it too late to add a feature to pg_dump for 6.4??
> > >
> > > I just spent most of the day learning pg_dump and modifing it so it would
> > > dump views also.
> > >
> > > This is the first time I have ever contributed any code changes, so I'm
> > > not sure of how to submit it.
> > >
> > > The diff's and a readme as a tgz file are attached.
> > >
> > > Thanks
> > > Terry Mackintosh <terry@terrym.com> http://www.terrym.com
> > > sysadmin/owner Please! No MIME encoded or HTML mail, unless needed.
> > >
> > > Proudly powered by R H Linux 4.2, Apache 1.3, PHP 3, PostgreSQL 6.3
> > > -------------------------------------------------------------------
> > > Success Is A Choice ... book by Rick Patino, get it, read it!
> > >
> >
> > Marc G. Fournier scrappy@hub.org
> > Systems Administrator @ hub.org
> > scrappy@{postgresql|isc}.org ICQ#7615664
> >
> >
> >
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #======================================== jwieck@debis.com (Jan Wieck) #
>
>
Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664