Обсуждение: pg_dump --pretty-print-views
Hi,
At the company I work for, we've been splitting dumps into separate
files and diffing them for a while now. By far the biggest problem we
had was with views: pg_dump by default dumps views on one line, in a
format which maximizes compatibility. Now this has several problems for
our use case:
1) The one-line equivalent of a 200-line view is completely impossible
to read.
2) If there's a difference between the two dumped view definitions,
it takes a long time to find where and what exactly it is.
3) For some reason some expressions are dumped differently depending
on how exactly they are written, cluttering the diff with false
positives.
While we can do the actual splitting of objects from a -Fc dump
relatively easily, we can't fix the view definitions after they've been
dumped. So I'm proposing a --pretty-print-views setting to pg_dump
(patch attached).
Any feedback is welcome.
Regards,
Marko Tiikkaja
Вложения
On Thu, Jan 10, 2013 at 01:23:10PM +0100, Marko Tiikkaja wrote: > Hi, > > At the company I work for, we've been splitting dumps into separate > files and diffing them for a while now. By far the biggest problem > we had was with views: pg_dump by default dumps views on one line, > in a format which maximizes compatibility. Now this has several > problems for our use case: > > 1) The one-line equivalent of a 200-line view is completely impossible > to read. > 2) If there's a difference between the two dumped view definitions, > it takes a long time to find where and what exactly it is. > 3) For some reason some expressions are dumped differently depending > on how exactly they are written, cluttering the diff with false > positives. > > While we can do the actual splitting of objects from a -Fc dump > relatively easily, we can't fix the view definitions after they've > been dumped. So I'm proposing a --pretty-print-views setting to > pg_dump (patch attached). > > Any feedback is welcome. Why not make this the new default? That way, new users will have the benefit, and people with tools or processes that depend on the old behavior can still have it. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
* David Fetter (david@fetter.org) wrote:
> On Thu, Jan 10, 2013 at 01:23:10PM +0100, Marko Tiikkaja wrote:
> > Any feedback is welcome.
>
> Why not make this the new default? That way, new users will have the
> benefit, and people with tools or processes that depend on the old
> behavior can still have it.
I tend to agree with this, I've never really liked having the view def
all on one massive line.
Well, I'll caveat that with this- being able to grep -C2 and pull the
views and the view definitions has been nice on occation, but you can
pull independent objects out with pg_restore from a -Fc dump and, in
general, I think the advantage of having the view definition look
reasonable in the dump is more than being able to do tricks on views
(but not tables, etc anyway..) with grep.
Thanks,
Stephen
On 01/10/2013 07:23 AM, Marko Tiikkaja wrote: > Hi, > > At the company I work for, we've been splitting dumps into separate > files and diffing them for a while now. By far the biggest problem we > had was with views: pg_dump by default dumps views on one line, in a > format which maximizes compatibility. Now this has several problems > for our use case: > > 1) The one-line equivalent of a 200-line view is completely impossible > to read. > 2) If there's a difference between the two dumped view definitions, > it takes a long time to find where and what exactly it is. > 3) For some reason some expressions are dumped differently depending > on how exactly they are written, cluttering the diff with false > positives. > > While we can do the actual splitting of objects from a -Fc dump > relatively easily, we can't fix the view definitions after they've > been dumped. So I'm proposing a --pretty-print-views setting to > pg_dump (patch attached). > > For versions >= 9.2 it would be better to allow passing in a pretty-print value, like 80 or 0, instead of just passing 'true'. The new line-wrapping that the integer argument triggers is much more readable than the supposedly pretty value that 'true' provides. cheers andrew
On 1/10/13 3:22 PM, Andrew Dunstan wrote: > For versions >= 9.2 it would be better to allow passing in a > pretty-print value, like 80 or 0, instead of just passing 'true'. The > new line-wrapping that the integer argument triggers is much more > readable than the supposedly pretty value that 'true' provides. Ooh, I had no idea we exposed that in SQL. I like this idea. Regards, Marko Tiikkaja
Marko Tiikkaja <pgmail@joh.to> writes:
> While we can do the actual splitting of objects from a -Fc dump
> relatively easily, we can't fix the view definitions after they've been
> dumped. So I'm proposing a --pretty-print-views setting to pg_dump
> (patch attached).
-1. The reason that pg_dump does not pretty-print things is that
it's unsafe; there is no real guarantee that the view will reload as
intended, because it's under-parenthesized. (Even if we were sure
it would reload safely into current code, which I'm not, what of
future versions that could have different operator precedences?)
I don't think we should offer a foot-gun option like this at all,
and as for making it the default, not bloody likely.
I think your schema-diffing needs would be better served by a tool
specifically directed at that problem; which pg_dump is not, but
I believe there are some out there.
regards, tom lane
On Thu, Jan 10, 2013 at 11:21:13AM -0500, Tom Lane wrote: > Marko Tiikkaja <pgmail@joh.to> writes: > > While we can do the actual splitting of objects from a -Fc dump > > relatively easily, we can't fix the view definitions after they've > > been dumped. So I'm proposing a --pretty-print-views setting to > > pg_dump (patch attached). > > -1. The reason that pg_dump does not pretty-print things is that > it's unsafe; there is no real guarantee that the view will reload as > intended, because it's under-parenthesized. (Even if we were sure > it would reload safely into current code, which I'm not, what of > future versions that could have different operator precedences?) Under what circumstances do pretty-printed views not reload? It seems to me that such circumstances would be pretty_print() bugs by definition. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
David Fetter <david@fetter.org> writes:
> On Thu, Jan 10, 2013 at 11:21:13AM -0500, Tom Lane wrote:
>> -1. The reason that pg_dump does not pretty-print things is that
>> it's unsafe; there is no real guarantee that the view will reload as
>> intended, because it's under-parenthesized. (Even if we were sure
>> it would reload safely into current code, which I'm not, what of
>> future versions that could have different operator precedences?)
> Under what circumstances do pretty-printed views not reload? It seems
> to me that such circumstances would be pretty_print() bugs by
> definition.
It would not be a bug, particularly not in the case of a subsequent
release with different operator precedence. pg_dump's charter is to be
safe. Pretty-printing's charter is to look nice. These goals are not
compatible. If they were, we'd never have made a separate pretty
printing mode at all.
Now, we could consider changing the "safe" mode so that it tries to
provide nice whitespace/line breaks while not risking removal of
parentheses. But that would be a totally different patch, and I'm
not sure how much it would address Marko's desires anyway.
regards, tom lane
On 01/10/2013 12:09 PM, Tom Lane wrote: > Now, we could consider changing the "safe" mode so that it tries to > provide nice whitespace/line breaks while not risking removal of > parentheses. But that would be a totally different patch, and I'm > not sure how much it would address Marko's desires anyway. I think there's a very good case for breaking the nexus between PRETTYFLAG_PAREN and PRETTYFLAG_INDENT+line wrapping for views. Only PRETTYFLAG_PAREN affects the safety issue. The others are just about white space in safe places. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> I think there's a very good case for breaking the nexus between
> PRETTYFLAG_PAREN and PRETTYFLAG_INDENT+line wrapping for views. Only
> PRETTYFLAG_PAREN affects the safety issue. The others are just about
> white space in safe places.
What I was actually thinking about was turning on indent/linewrapping
all the time (ie, no change on pg_dump's side, just hack ruleutils).
If we believe it's safe, why not just do it? It's the paren-removal
that pg_dump can't tolerate.
regards, tom lane
On 01/10/2013 12:35 PM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> I think there's a very good case for breaking the nexus between >> PRETTYFLAG_PAREN and PRETTYFLAG_INDENT+line wrapping for views. Only >> PRETTYFLAG_PAREN affects the safety issue. The others are just about >> white space in safe places. > What I was actually thinking about was turning on indent/linewrapping > all the time (ie, no change on pg_dump's side, just hack ruleutils). > If we believe it's safe, why not just do it? It's the paren-removal > that pg_dump can't tolerate. > > Works for me. cheers andrew
On Thu, Jan 10, 2013 at 11:07 PM, Andrew Dunstan <andrew@dunslane.net> wrote:
Works for me.
On 01/10/2013 12:35 PM, Tom Lane wrote:Andrew Dunstan <andrew@dunslane.net> writes:I think there's a very good case for breaking the nexus betweenWhat I was actually thinking about was turning on indent/linewrapping
PRETTYFLAG_PAREN and PRETTYFLAG_INDENT+line wrapping for views. Only
PRETTYFLAG_PAREN affects the safety issue. The others are just about
white space in safe places.
all the time (ie, no change on pg_dump's side, just hack ruleutils).
If we believe it's safe, why not just do it? It's the paren-removal
that pg_dump can't tolerate.
Nice idea.
Marko, just hack ruleutils some thing like this:
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 266cec5..a46f588 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -511,8 +511,9 @@ pg_get_viewdef(PG_FUNCTION_ARGS)
{
/* By OID */
Oid viewoid = PG_GETARG_OID(0);
+ int prettyFlags = PRETTYFLAG_INDENT;
- PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, 0, -1)));
+ PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT)));
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 266cec5..a46f588 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -511,8 +511,9 @@ pg_get_viewdef(PG_FUNCTION_ARGS)
{
/* By OID */
Oid viewoid = PG_GETARG_OID(0);
+ int prettyFlags = PRETTYFLAG_INDENT;
- PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, 0, -1)));
+ PG_RETURN_TEXT_P(string_to_text(pg_get_viewdef_worker(viewoid, prettyFlags, WRAP_COLUMN_DEFAULT)));
}
Thanks
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
On Fri, 18 Jan 2013 07:46:21 +0100, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote: > Nice idea. > > Marko, just hack ruleutils some thing like this: Here's a patch attempting to do just that. The actual code changes were trivial, the patch is mostly just regression tests. As for the docs, I wasn't entirely happy with what they say about pg_get_viewdef(), but it didn't look like they needed to be changed by this either. Regards, Marko Tiikkaja
Вложения
<div dir="ltr">Hi <span class="" style="style">Marko</span>,<br /><div class="gmail_extra"><br /></div><div class="gmail_extra">Icould not apply the patch with git apply, but able to apply it by patch -p1 command.<br /><br /></div><divclass="gmail_extra"> However, will you please justify the changes done in "<span class="" style="style">xml</span>.out"? I guess they are not needed. <br />You might need to configure your sources with <span class=""style="style">libxml</span>.<br /><br /></div><div class="gmail_extra">Also, I am not sure about putting "WRAP_COLUMN_DEFAULT"by default. I will keep that in code <span class="" style="style">committors</span> plate. Rest of thecode changes looks good to me.<br /><br /></div><div class="gmail_extra">Thanks<br /><br /><div class="gmail_quote">OnSun, Jan 27, 2013 at 6:23 PM, <span class="" style="style">Marko</span> <span class="" style="style">Tiikkaja</span><span dir="ltr"><<a href="mailto:pgmail@joh.to" target="_blank"><span class="" style="style">pgmail</span>@<spanclass="" style="style">joh</span>.to</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div class="im">OnFri, 18 Jan 2013 07:46:21 +0100, Jeevan Chalke <<a href="mailto:jeevan.chalke@enterprisedb.com" target="_blank">jeevan.chalke@enterprisedb.<u></u>com</a>>wrote:<br /><blockquote class="gmail_quote" style="margin:0px0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> Nice idea.<br /><br /> Marko, justhack ruleutils some thing like this:<br /></blockquote><br /></div> Here's a patch attempting to do just that.<br /><br/> The actual code changes were trivial, the patch is mostly just regression tests.<br /><br /> As for the docs, I wasn'tentirely happy with what they say about pg_get_viewdef(), but it didn't look like they needed to be changed by thiseither.<br /><br /><br /> Regards,<br /> Marko Tiikkaja</blockquote></div><br /><br clear="all" /><br />-- <br /><spanclass="" style="style">Jeevan</span> B <span class="" style="style">Chalke</span><br />Senior Software Engineer, R&D<br/><span class="" style="style">EnterpriseDB</span> Corporation<br /> The Enterprise <span class="" style="style">PostgreSQL</span>Company<br /><br />Phone: +91 20 30589500<br /><br />Website: <a href="http://www.enterprisedb.com"target="_blank">www.<span class="" style="style">enterprisedb</span>.com</a><br /><spanclass="" style="style">EnterpriseDB</span> Blog: <a href="http://blogs.enterprisedb.com/" target="_blank">http://blogs.<spanclass="" style="style">enterprisedb</span>.com/</a><br /> Follow us on Twitter: <a href="http://www.twitter.com/enterprisedb"target="_blank">http://www.twitter.com/<span class="" style="style">enterprisedb</span></a><br/><br />This e-mail message (and any attachment) is intended for the use of the individualor entity to whom it is addressed. This message contains information from <span class="" style="style">EnterpriseDB</span>Corporation that may be privileged, confidential, or exempt from disclosure under applicablelaw. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination,distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have receivedthis e-mail in error, please notify the sender immediately by reply e-mail and delete this message. </div></div>
On 1/28/13 12:14 PM, Jeevan Chalke wrote: > I could not apply the patch with git apply, but able to apply it by patch > -p1 command. IME that's normal for patches that went through filterdiff. I do: git diff |filterdiff --format=context to re-format the patches to the context diff preferred on the mailing list. Maybe if I somehow told git to produce context diff it would work.. > However, will you please justify the changes done in "xml.out" ? I guess > they are not needed. > You might need to configure your sources with libxml. If you look very carefully, the pretty-printing version adds one space at the very beginning of the output. :-) > Also, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. I will > keep that in code committors plate. Rest of the code changes looks good to > me. Thanks for reviewing! Regards, Marko Tiikkaja
On 28/01/13 12:31, Marko Tiikkaja wrote: > On 1/28/13 12:14 PM, Jeevan Chalke wrote: >> I could not apply the patch with git apply, but able to apply it by patch >> -p1 command. > > IME that's normal for patches that went through filterdiff. I do: git > diff |filterdiff --format=context to re-format the patches to the > context diff preferred on the mailing list. Maybe if I somehow told git > to produce context diff it would work.. Try this, worked for me: http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git Cheers, Jan
Hi Marko,
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
On Mon, Jan 28, 2013 at 5:01 PM, Marko Tiikkaja <pgmail@joh.to> wrote:
On 1/28/13 12:14 PM, Jeevan Chalke wrote:IME that's normal for patches that went through filterdiff. I do: git diff |filterdiff --format=context to re-format the patches to the context diff preferred on the mailing list. Maybe if I somehow told git to produce context diff it would work..I could not apply the patch with git apply, but able to apply it by patch
-p1 command.If you look very carefully, the pretty-printing version adds one space at the very beginning of the output. :-)However, will you please justify the changes done in "xml.out" ? I guess
they are not needed.
You might need to configure your sources with libxml.
That's fine. I am not at all pointing that to you. Have a look at this:
*** a/src/test/regress/expected/xml.out
--- b/src/test/regress/expected/xml.out
***************
*** 3,82 **** CREATE TABLE xmltest (
data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
INSERT INTO xmltest VALUES (2, '<value>two</value>');
INSERT INTO xmltest VALUES (3, '<wrong');
! ERROR: invalid XML content
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
! DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
! <wrong
! ^
.
.
.
--- 3,84 ----
data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ^
+ DETAIL: This functionality requires the server to be built with libxml support.
+ HINT: You need to rebuild PostgreSQL using --with-libxml.
INSERT INTO xmltest VALUES (2, '<value>two</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (2, '<value>two</value>');
+ ^
+ DETAIL: This functionality requires the server to be built with libxml support.
+ HINT: You need to rebuild PostgreSQL using --with-libxml.
*** a/src/test/regress/expected/xml.out
--- b/src/test/regress/expected/xml.out
***************
*** 3,82 **** CREATE TABLE xmltest (
data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
INSERT INTO xmltest VALUES (2, '<value>two</value>');
INSERT INTO xmltest VALUES (3, '<wrong');
! ERROR: invalid XML content
LINE 1: INSERT INTO xmltest VALUES (3, '<wrong');
^
! DETAIL: line 1: Couldn't find end of Start Tag wrong line 1
! <wrong
! ^
.
.
.
--- 3,84 ----
data xml
);
INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (1, '<value>one</value>');
+ ^
+ DETAIL: This functionality requires the server to be built with libxml support.
+ HINT: You need to rebuild PostgreSQL using --with-libxml.
INSERT INTO xmltest VALUES (2, '<value>two</value>');
+ ERROR: unsupported XML feature
+ LINE 1: INSERT INTO xmltest VALUES (2, '<value>two</value>');
+ ^
+ DETAIL: This functionality requires the server to be built with libxml support.
+ HINT: You need to rebuild PostgreSQL using --with-libxml.
These changes are not at all required.
Follow the hint.
In other way, if I apply your patch and run make check I get regression failure for xml.out.
Please check.
Thanks
Thanks for reviewing!Also, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. I will
keep that in code committors plate. Rest of the code changes looks good to
me.
Regards,
Marko Tiikkaja
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
On 1/29/13 10:18 AM, Jeevan Chalke wrote: > > That's fine. I am not at all pointing that to you. Have a look at this: Ugh.. I'm sorry, I don't understand how this happened. I manually looked through all the changes, but somehow this slipped through. Will have a look this evening. Regards, Marko Tiikkaja
On Tue, 29 Jan 2013 10:18:51 +0100, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote: > That's fine. I am not at all pointing that to you. Have a look at this: Here's the third version of this patch, hopefully this time without any problems. I looked through the patch and it looked OK, but I did that last time too so I wouldn't trust myself on that one. Regards, Marko Tiikkaja
Вложения
Hi Marko,
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
On Wed, Jan 30, 2013 at 2:07 AM, Marko Tiikkaja <pgmail@joh.to> wrote:
On Tue, 29 Jan 2013 10:18:51 +0100, Jeevan Chalke <jeevan.chalke@enterprisedb.com> wrote:Here's the third version of this patch, hopefully this time without any problems. I looked through the patch and it looked OK, but I did that last time too so I wouldn't trust myself on that one.That's fine. I am not at all pointing that to you. Have a look at this:
Looks good this time.
Will mark "Ready for committor"
However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In my opinion we should put that by default but other people may object so I will keep that in code committors plate.
However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In my opinion we should put that by default but other people may object so I will keep that in code committors plate.
Thanks
Regards,
Marko Tiikkaja
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
On 1/30/13 7:52 AM, Jeevan Chalke wrote: > Looks good this time. > > Will mark "Ready for committor" Thanks for reviewing it more carefully than I did! :-) And my apologies for the confusion earlier. > However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In > my opinion we should put that by default but other people may object so I > will keep that in code committors plate. I have no opinion on this to one way or the other, so I'm fine with waiting for someone else (possibly the committer) to decide this. Regards, Marko Tiikkaja
Marko Tiikkaja <pgmail@joh.to> writes:
> On 1/30/13 7:52 AM, Jeevan Chalke wrote:
>> However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In
>> my opinion we should put that by default but other people may object so I
>> will keep that in code committors plate.
> I have no opinion on this to one way or the other, so I'm fine with
> waiting for someone else (possibly the committer) to decide this.
FWIW, I'd vote for not enabling that by default --- it's basically an
unwarranted assumption about how wide people's terminal windows are.
regards, tom lane
On 01/30/2013 09:58 AM, Tom Lane wrote: > Marko Tiikkaja <pgmail@joh.to> writes: >> On 1/30/13 7:52 AM, Jeevan Chalke wrote: >>> However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In >>> my opinion we should put that by default but other people may object so I >>> will keep that in code committors plate. >> I have no opinion on this to one way or the other, so I'm fine with >> waiting for someone else (possibly the committer) to decide this. > FWIW, I'd vote for not enabling that by default --- it's basically an > unwarranted assumption about how wide people's terminal windows are. > > I'm not exactly sure what you're arguing for. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> On 01/30/2013 09:58 AM, Tom Lane wrote:
>> Marko Tiikkaja <pgmail@joh.to> writes:
>>> On 1/30/13 7:52 AM, Jeevan Chalke wrote:
>>>> However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In
>>>> my opinion we should put that by default but other people may object so I
>>>> will keep that in code committors plate.
>> FWIW, I'd vote for not enabling that by default --- it's basically an
>> unwarranted assumption about how wide people's terminal windows are.
> I'm not exactly sure what you're arguing for.
Maybe I'm confused - I thought the alternative Jeevan was talking about
was that we enable PRETTY_INDENT processing by default, but not
wrapColumn processing.
regards, tom lane
On 01/30/2013 11:03 AM, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: >> On 01/30/2013 09:58 AM, Tom Lane wrote: >>> Marko Tiikkaja <pgmail@joh.to> writes: >>>> On 1/30/13 7:52 AM, Jeevan Chalke wrote: >>>>> However, I am not sure about putting "WRAP_COLUMN_DEFAULT" by default. In >>>>> my opinion we should put that by default but other people may object so I >>>>> will keep that in code committors plate. >>> FWIW, I'd vote for not enabling that by default --- it's basically an >>> unwarranted assumption about how wide people's terminal windows are. >> I'm not exactly sure what you're arguing for. > Maybe I'm confused - I thought the alternative Jeevan was talking about > was that we enable PRETTY_INDENT processing by default, but not > wrapColumn processing. > > Well, we could actually set the wrap value to 0, which would mean always wrap. That wouldn't be making any assumption about the user's terminal window size ;-) Here are two examples, one of which is with exactly Marko's patch which doesn't change the wrap value (i.e. leaves it at -1, meaning no wrap) , the other sets it at 0. The example is a view that is the same as information_schema.columns. Personally I find the wrapped case MUCH more readable. I guess anything is an advance, but turning on PRETTY_INDENT without turning on some level of target wrapping seems like a half-done job. cheers andrew
Вложения
Andrew Dunstan <andrew@dunslane.net> writes:
> On 01/30/2013 11:03 AM, Tom Lane wrote:
>>>> FWIW, I'd vote for not enabling that by default --- it's basically an
>>>> unwarranted assumption about how wide people's terminal windows are.
> Well, we could actually set the wrap value to 0, which would mean always
> wrap. That wouldn't be making any assumption about the user's terminal
> window size ;-)
I could live with that. I rather wonder actually if WRAP_COLUMN_DEFAULT
shouldn't be removed in favor of using 0 as the default. 80-column
windows may be our coding standard for Postgres, but that doesn't mean
they're not pretty passe elsewhere.
regards, tom lane
Andrew Dunstan <andrew@dunslane.net> writes: > Well, we could actually set the wrap value to 0, which would mean always > wrap. That wouldn't be making any assumption about the user's terminal > window size ;-) +1 > Personally I find the wrapped case MUCH more readable. I guess anything is > an advance, but turning on PRETTY_INDENT without turning on some level of > target wrapping seems like a half-done job. Minor gripe: the CASE WHEN THEN indenting is not ideal: CASE WHEN (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull)) THEN 'NO'::text ELSE 'YES'::text END)::information_schema.yes_or_noAS is_nullable, I think the following is easier to read: CASE WHEN (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull)) THEN 'NO'::text ELSE 'YES'::text END)::information_schema.yes_or_noAS is_nullable, But I realise it's not exactly what's being talked about in this thread… Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
On Thu, Jan 31, 2013 at 4:40 PM, Dimitri Fontaine <dimitri@2ndquadrant.fr> wrote:
+1
Andrew Dunstan <andrew@dunslane.net> writes:+1
> Well, we could actually set the wrap value to 0, which would mean always
> wrap. That wouldn't be making any assumption about the user's terminal
> window size ;-)
+1
After looking at both the SQL, I too like wrapped case.
Minor gripe: the CASE WHEN THEN indenting is not ideal:
> Personally I find the wrapped case MUCH more readable. I guess anything is
> an advance, but turning on PRETTY_INDENT without turning on some level of
> target wrapping seems like a half-done job.
CASE
WHEN (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull)) THEN 'NO'::text
ELSE 'YES'::text
END)::information_schema.yes_or_no AS is_nullable,
I think the following is easier to read:
CASE
WHEN (a.attnotnull OR ((t.typtype = 'd'::"char") AND t.typnotnull))
THEN 'NO'::text
ELSE 'YES'::text
END)::information_schema.yes_or_no AS is_nullable,
But I realise it's not exactly what's being talked about in this thread…
Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
--
Jeevan B Chalke
Senior Software Engineer, R&D
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
Phone: +91 20 30589500
Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb
This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
"Marko Tiikkaja" <pgmail@joh.to> writes:
> Here's the third version of this patch, hopefully this time without any
> problems. I looked through the patch and it looked OK, but I did that
> last time too so I wouldn't trust myself on that one.
Applied with corrections.
The xml expected output was still wrong - to do that part right, you
need to update xml.out with an xml-enabled build and xml_1.out with a
non-xml-enabled build.
Also, it seemed to me that the patch didn't go far enough, in that it
only touched pg_get_viewdef and not the sister functions. pg_dump would
certainly want pg_get_ruledef to have the same behavior, and in general
the documentation seems to me to be clear that all these functions have
similar pretty-print-vs-not behavior. As committed, the pretty_bool
argument only affects PRETTY_PAREN processing for all of them.
I also went ahead and set the default wrap column to zero rather than
the former 79, since it seemed clear that people like that behavior
better.
regards, tom lane
On 2/3/13 10:06 PM, Tom Lane wrote: > Applied with corrections. Thank you. > The xml expected output was still wrong - to do that part right, you > need to update xml.out with an xml-enabled build and xml_1.out with a > non-xml-enabled build. Ahh. That explains a lot. Regards, Marko Tiikkaja
On Sun, Feb 3, 2013 at 4:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Marko Tiikkaja" <pgmail@joh.to> writes:Applied with corrections.
> Here's the third version of this patch, hopefully this time without any
> problems. I looked through the patch and it looked OK, but I did that
> last time too so I wouldn't trust myself on that one.
The xml expected output was still wrong - to do that part right, you
need to update xml.out with an xml-enabled build and xml_1.out with a
non-xml-enabled build.
Also, it seemed to me that the patch didn't go far enough, in that it
only touched pg_get_viewdef and not the sister functions. pg_dump would
certainly want pg_get_ruledef to have the same behavior, and in general
the documentation seems to me to be clear that all these functions have
similar pretty-print-vs-not behavior. As committed, the pretty_bool
argument only affects PRETTY_PAREN processing for all of them.
I also went ahead and set the default wrap column to zero rather than
the former 79, since it seemed clear that people like that behavior
better.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Was this ever committed into core? Apologies, I'm not very familiar with looking through the commit history of the source code and I don't see anything about this option or pretty-print outputs in the pg_dump/restore docs for 9.3. Had someone asking me about this feature for pg_extractor
https://github.com/omniti-labs/pg_extractor/issues/28
On 4/29/14 4:29 PM, Keith Fiske wrote: > Was this ever committed into core? Apologies, I'm not very familiar with > looking through the commit history of the source code and I don't see > anything about this option or pretty-print outputs in the pg_dump/restore > docs for 9.3. Had someone asking me about this feature for pg_extractor No, the idea was rejected. Regards, Marko Tiikkaja
Keith Fiske <keith@omniti.com> writes:
> On Sun, Feb 3, 2013 at 4:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Applied with corrections.
> Was this ever committed into core? Apologies, I'm not very familiar with
> looking through the commit history of the source code and I don't see
> anything about this option or pretty-print outputs in the pg_dump/restore
> docs for 9.3. Had someone asking me about this feature for pg_extractor
Yeah, if I say "applied" that means I committed it. Here's the commit
log entry:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [62e666400] 2013-02-03 15:56:45 -0500
Perform line wrapping and indenting by default in ruleutils.c. This patch changes pg_get_viewdef() and allied
functionsso that PRETTY_INDENT processing is always enabled. Per discussion, only the PRETTY_PAREN processing
(thatis, stripping of "unnecessary" parentheses) poses any real forward-compatibility risk, so we may as well make
dump output look as nice as we safely can. Also, set the default wrap length to zero (i.e, wrap after each SELECT
or FROM list item), since there's no very principled argument for the former default of 80-column wrapping, and most
peopleseem to agree this way looks better. Marko Tiikkaja, reviewed by Jeevan Chalke, further hacking by Tom
Lane
As per the branch annotation, this is in 9.3 and up.
regards, tom lane
On Tue, Apr 29, 2014 at 11:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Keith Fiske <keith@omniti.com> writes:>> Applied with corrections.
> On Sun, Feb 3, 2013 at 4:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:Yeah, if I say "applied" that means I committed it. Here's the commit
> Was this ever committed into core? Apologies, I'm not very familiar with
> looking through the commit history of the source code and I don't see
> anything about this option or pretty-print outputs in the pg_dump/restore
> docs for 9.3. Had someone asking me about this feature for pg_extractor
log entry:
Author: Tom Lane <tgl@sss.pgh.pa.us>
Branch: master Release: REL9_3_BR [62e666400] 2013-02-03 15:56:45 -0500
Perform line wrapping and indenting by default in ruleutils.c.
This patch changes pg_get_viewdef() and allied functions so that
PRETTY_INDENT processing is always enabled. Per discussion, only the
PRETTY_PAREN processing (that is, stripping of "unnecessary" parentheses)
poses any real forward-compatibility risk, so we may as well make dump
output look as nice as we safely can.
Also, set the default wrap length to zero (i.e, wrap after each SELECT
or FROM list item), since there's no very principled argument for the
former default of 80-column wrapping, and most people seem to agree this
way looks better.
Marko Tiikkaja, reviewed by Jeevan Chalke, further hacking by Tom Lane
As per the branch annotation, this is in 9.3 and up.
regards, tom lane
Great! Thanks, Tom
Huh, I had assumed this was old behaviour. I didn't realize this was new with 9.3. Considering the thread "pg_get_viewdefs() indentation considered harmful" I'm beginning to think this was a regression. It results in some dump files being unnecessarily large and the pg_dump consuming too much memory and crashing. Tom liked my suggestion of doing the indentation modulo 40. There are plenty of other suggestions that can work too, giving up on indentation after an indent of 40, switching to an indent distance of 1 if it's more than 10 levels deep, and so on. I think it doesn't really matter which we choose but we have to do something. And given this is new behaviour in 9.3 perhaps it should be backported too.
Greg Stark <stark@mit.edu> writes:
> Huh, I had assumed this was old behaviour. I didn't realize this was
> new with 9.3.
> Considering the thread "pg_get_viewdefs() indentation considered
> harmful" I'm beginning to think this was a regression. It results in
> some dump files being unnecessarily large and the pg_dump consuming
> too much memory and crashing.
> Tom liked my suggestion of doing the indentation modulo 40. There are
> plenty of other suggestions that can work too, giving up on
> indentation after an indent of 40, switching to an indent distance of
> 1 if it's more than 10 levels deep, and so on. I think it doesn't
> really matter which we choose but we have to do something. And given
> this is new behaviour in 9.3 perhaps it should be backported too.
I'm still a bit skeptical about this being a catastrophic problem in
practice ... but anyway, I thought there were two somewhat different
proposals on the table in that thread:
1. Arrange for "x UNION y UNION z ..." to put all the UNION arms at
the same indentation level.
2. Change the indentation rules globally, in one or another fashion
as Greg mentions above, to prevent ruleutils from ever prepending
silly amounts of whitespace.
These are not mutually exclusive, and I think we should do both.
But it seems we're hung up on exactly which flavor of #2 to do.
I would argue that rules such as "reduce the indent step once we're too
far over" don't fix the problem, just postpone it. If we're taking this
complaint seriously at all then there needs to be a guaranteed maximum
indent distance, one way or another. So I'd go for either a modulo-N
rule or a hard limit, with some preference for the modulo-N approach.
Yeah, it does sound silly at first, but I think it'd preserve
readability better than just capping the indent.
regards, tom lane
I wrote:
> I'm still a bit skeptical about this being a catastrophic problem in
> practice ... but anyway, I thought there were two somewhat different
> proposals on the table in that thread:
> 1. Arrange for "x UNION y UNION z ..." to put all the UNION arms at
> the same indentation level.
> 2. Change the indentation rules globally, in one or another fashion
> as Greg mentions above, to prevent ruleutils from ever prepending
> silly amounts of whitespace.
> These are not mutually exclusive, and I think we should do both.
Here's a draft patch tackling point 1. This gets rid of a whole lot
of parenthesization, as well as indentation, for simple UNION lists.
You can see the results in the changed regression test outputs.
There are still a few oddities in the printout format, such as the
forced space between ( and SELECT at the start of a subquery;
but changing that would require touching more than just
get_setop_query(), and I'm not too sure what else would be affected,
so I let it be.
While I was testing this I noticed that there's something thoroughly
busted about the indentation of outer JOIN constructs, too --- you
can see this in some of the regression test queries that are touched
by this patch, where the JOINs are actually outdented to the left of
their FROM clause in the unpatched output. (They'd be outdented in
the new output, too, if negative indentation were possible...)
That seems like material for a separate patch though.
Comments?
regards, tom lane
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..73e09ae 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
*************** get_setop_query(Node *setOp, Query *quer
*** 4713,4754 ****
else if (IsA(setOp, SetOperationStmt))
{
SetOperationStmt *op = (SetOperationStmt *) setOp;
!
! if (PRETTY_INDENT(context))
! {
! context->indentLevel += PRETTYINDENT_STD;
! appendStringInfoSpaces(buf, PRETTYINDENT_STD);
! }
/*
! * We force parens whenever nesting two SetOperationStmts. There are
! * some cases in which parens are needed around a leaf query too, but
! * those are more easily handled at the next level down (see code
! * above).
*/
! need_paren = !IsA(op->larg, RangeTblRef);
if (need_paren)
appendStringInfoChar(buf, '(');
get_setop_query(op->larg, query, context, resultDesc);
- if (need_paren)
- appendStringInfoChar(buf, ')');
! if (!PRETTY_INDENT(context))
appendStringInfoChar(buf, ' ');
switch (op->op)
{
case SETOP_UNION:
! appendContextKeyword(context, "UNION ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
case SETOP_INTERSECT:
! appendContextKeyword(context, "INTERSECT ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
case SETOP_EXCEPT:
! appendContextKeyword(context, "EXCEPT ",
! -PRETTYINDENT_STD, PRETTYINDENT_STD, 0);
break;
default:
elog(ERROR, "unrecognized set op: %d",
--- 4713,4767 ----
else if (IsA(setOp, SetOperationStmt))
{
SetOperationStmt *op = (SetOperationStmt *) setOp;
! int subindent;
/*
! * We force parens when nesting two SetOperationStmts, except when the
! * lefthand input is another setop of the same kind. Syntactically,
! * we could omit parens in rather more cases, but it seems best to use
! * parens to flag cases where the setop operator changes.
! *
! * There are some cases in which parens are needed around a leaf query
! * too, but those are more easily handled at the next level down (see
! * code above).
*/
! if (IsA(op->larg, SetOperationStmt))
! {
! SetOperationStmt *lop = (SetOperationStmt *) op->larg;
!
! if (op->op == lop->op && op->all == lop->all)
! need_paren = false;
! else
! need_paren = true;
! }
! else
! need_paren = false;
if (need_paren)
+ {
appendStringInfoChar(buf, '(');
+ appendContextKeyword(context, "", PRETTYINDENT_STD, 0, 0);
+ }
+
get_setop_query(op->larg, query, context, resultDesc);
! if (need_paren)
! appendContextKeyword(context, ") ", -PRETTYINDENT_STD, 0, 0);
! else if (PRETTY_INDENT(context))
! appendContextKeyword(context, "", 0, 0, 0);
! else
appendStringInfoChar(buf, ' ');
+
switch (op->op)
{
case SETOP_UNION:
! appendStringInfoString(buf, "UNION ");
break;
case SETOP_INTERSECT:
! appendStringInfoString(buf, "INTERSECT ");
break;
case SETOP_EXCEPT:
! appendStringInfoString(buf, "EXCEPT ");
break;
default:
elog(ERROR, "unrecognized set op: %d",
*************** get_setop_query(Node *setOp, Query *quer
*** 4757,4775 ****
if (op->all)
appendStringInfoString(buf, "ALL ");
- if (PRETTY_INDENT(context))
- appendContextKeyword(context, "", 0, 0, 0);
-
need_paren = !IsA(op->rarg, RangeTblRef);
if (need_paren)
appendStringInfoChar(buf, '(');
get_setop_query(op->rarg, query, context, resultDesc);
- if (need_paren)
- appendStringInfoChar(buf, ')');
-
if (PRETTY_INDENT(context))
! context->indentLevel -= PRETTYINDENT_STD;
}
else
{
--- 4770,4791 ----
if (op->all)
appendStringInfoString(buf, "ALL ");
need_paren = !IsA(op->rarg, RangeTblRef);
if (need_paren)
+ {
appendStringInfoChar(buf, '(');
+ subindent = PRETTYINDENT_STD;
+ }
+ else
+ subindent = 0;
+ if (PRETTY_INDENT(context))
+ appendContextKeyword(context, "", subindent, 0, 0);
get_setop_query(op->rarg, query, context, resultDesc);
if (PRETTY_INDENT(context))
! context->indentLevel -= subindent;
! if (need_paren)
! appendContextKeyword(context, ")", 0, 0, 0);
}
else
{
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..bf3c187 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** select * from (values(1,2,3,4,5)) v(a,b,
*** 1090,1112 ****
union all
select * from tt7 full join tt8 using (x), tt8 tt8x;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! tt8x.x_1 AS d, +
! tt8x.z AS e +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
--- 1090,1112 ----
union all
select * from tt7 full join tt8 using (x), tt8 tt8x;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! tt8x.x_1 AS d, +
! tt8x.z AS e +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
*************** select * from
*** 1116,1140 ****
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x);
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
--- 1116,1140 ----
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x);
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
*************** select * from
*** 1145,1172 ****
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ------------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f, +
! tt8y.z AS g +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
--- 1145,1172 ----
tt7 full join tt8 using (x),
tt7 tt7x full join tt8 tt8x using (x) full join tt8 tt8y using (x);
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ----------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f, +
! tt8y.z AS g +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
*************** alter table tt7 add column z int;
*** 1175,1245 ****
alter table tt7 drop column zz;
alter table tt8 add column z2 int;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! tt8x.x_1 AS d, +
! tt8x.z AS e +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ------------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f, +
! tt8y.z AS g +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
--- 1175,1245 ----
alter table tt7 drop column zz;
alter table tt8 add column z2 int;
select pg_get_viewdef('vv2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (1,2,3,4,5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! tt8x.x_1 AS d, +
! tt8x.z AS e +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f +
! FROM ( VALUES (1,2,3,4,5,6)) v(a, b, c, x, e, f)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
! pg_get_viewdef
! ----------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.x, +
! v.e, +
! v.f, +
! v.g +
! FROM ( VALUES (1,2,3,4,5,6,7)) v(a, b, c, x, e, f, g)+
! UNION ALL +
! SELECT x AS a, +
! tt7.y AS b, +
! tt8.z AS c, +
! x_1 AS x, +
! tt7x.y AS e, +
! tt8x.z AS f, +
! tt8y.z AS g +
! FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
*************** select * from (values(now(),2,3,now(),5)
*** 1252,1274 ****
union all
select * from tt7a left join tt8a using (x), tt8a tt8ax;
select pg_get_viewdef('vv2a', true);
! pg_get_viewdef
! ----------------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7a.y AS b, +
! tt8a.z AS c, +
! tt8ax.x_1 AS d, +
! tt8ax.z AS e +
! FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1252,1274 ----
union all
select * from tt7a left join tt8a using (x), tt8a tt8ax;
select pg_get_viewdef('vv2a', true);
! pg_get_viewdef
! --------------------------------------------------------
! SELECT v.a, +
! v.b, +
! v.c, +
! v.d, +
! v.e +
! FROM ( VALUES (now(),2,3,now(),5)) v(a, b, c, d, e)+
! UNION ALL +
! SELECT x AS a, +
! tt7a.y AS b, +
! tt8a.z AS c, +
! tt8ax.x_1 AS d, +
! tt8ax.z AS e +
! FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
diff --git a/src/test/regress/expected/matview.out b/src/test/regress/expected/matview.out
index daf3b9e..ddac97b 100644
*** a/src/test/regress/expected/matview.out
--- b/src/test/regress/expected/matview.out
*************** CREATE VIEW v_test2 AS SELECT moo, 2*moo
*** 346,358 ****
moo | integer | | plain |
?column? | integer | | plain |
View definition:
! SELECT v_test1.moo,
! 2 * v_test1.moo
! FROM v_test1
UNION ALL
! SELECT v_test1.moo,
! 3 * v_test1.moo
! FROM v_test1;
CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
\d+ mv_test2
--- 346,358 ----
moo | integer | | plain |
?column? | integer | | plain |
View definition:
! SELECT v_test1.moo,
! 2 * v_test1.moo
! FROM v_test1
UNION ALL
! SELECT v_test1.moo,
! 3 * v_test1.moo
! FROM v_test1;
CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM v_test2 UNION ALL SELECT moo, 3*moo FROM v_test2;
\d+ mv_test2
*************** CREATE MATERIALIZED VIEW mv_test2 AS SEL
*** 362,374 ****
moo | integer | | plain | |
?column? | integer | | plain | |
View definition:
! SELECT v_test2.moo,
! 2 * v_test2.moo
! FROM v_test2
UNION ALL
! SELECT v_test2.moo,
! 3 * v_test2.moo
! FROM v_test2;
CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
--- 362,374 ----
moo | integer | | plain | |
?column? | integer | | plain | |
View definition:
! SELECT v_test2.moo,
! 2 * v_test2.moo
! FROM v_test2
UNION ALL
! SELECT v_test2.moo,
! 3 * v_test2.moo
! FROM v_test2;
CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo = 12345;
SELECT relispopulated FROM pg_class WHERE oid = 'mv_test3'::regclass;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..eb6066c 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_rules| SELECT n.nspname AS schemaname
*** 1401,1567 ****
JOIN pg_class c ON ((c.oid = r.ev_class)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
! pg_seclabels| ( ( ( ( ( ( ( ( ( SELECT
l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (rel.relkind =
'r'::"char")THEN 'table'::text
! WHEN (rel.relkind =
'v'::"char")THEN 'view'::text
! WHEN (rel.relkind =
'm'::"char")THEN 'materialized view'::text
! WHEN (rel.relkind =
'S'::"char")THEN 'sequence'::text
! WHEN (rel.relkind =
'f'::"char")THEN 'foreign table'::text
! ELSE NULL::text
! END AS objtype,
! rel.relnamespace AS objnamespace,
! CASE
! WHEN
pg_table_is_visible(rel.oid)THEN quote_ident((rel.relname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((rel.relname)::text))
! END AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace
=nsp.oid)))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'column'::text AS objtype,
! rel.relnamespace AS objnamespace,
! ((
! CASE
! WHEN
pg_table_is_visible(rel.oid)THEN quote_ident((rel.relname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((rel.relname)::text))
! END || '.'::text) ||
(att.attname)::text)AS objname,
! l.provider,
! l.label
! FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid =
att.attrelid)AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace =
nsp.oid)))
! WHERE (l.objsubid <> 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (pro.proisagg = true) THEN
'aggregate'::text
! WHEN (pro.proisagg = false) THEN
'function'::text
! ELSE NULL::text
! END AS objtype,
! pro.pronamespace AS objnamespace,
! (((
! CASE
! WHEN
pg_function_is_visible(pro.oid)THEN quote_ident((pro.proname)::text)
! ELSE
((quote_ident((nsp.nspname)::text)|| '.'::text) || quote_ident((pro.proname)::text))
! END || '('::text) ||
pg_get_function_arguments(pro.oid))|| ')'::text) AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (typ.typtype = 'd'::"char") THEN
'domain'::text
! ELSE 'type'::text
! END AS objtype,
! typ.typnamespace AS objnamespace,
! CASE
! WHEN pg_type_is_visible(typ.oid) THEN
quote_ident((typ.typname)::text)
! ELSE ((quote_ident((nsp.nspname)::text)
||'.'::text) || quote_ident((typ.typname)::text))
! END AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND
(l.objoid= typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'large object'::text AS objtype,
! NULL::oid AS objnamespace,
! (l.objoid)::text AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'language'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((lan.lanname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid =
lan.oid))))
! WHERE (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'schema'::text AS objtype,
! nsp.oid AS objnamespace,
! quote_ident((nsp.nspname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'event trigger'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((evt.evtname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'database'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((dat.datname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'tablespace'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((spc.spcname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'role'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((rol.rolname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
--- 1401,1567 ----
JOIN pg_class c ON ((c.oid = r.ev_class)))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
! pg_seclabels| SELECT l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (rel.relkind = 'r'::"char") THEN 'table'::text
! WHEN (rel.relkind = 'v'::"char") THEN 'view'::text
! WHEN (rel.relkind = 'm'::"char") THEN 'materialized view'::text
! WHEN (rel.relkind = 'S'::"char") THEN 'sequence'::text
! WHEN (rel.relkind = 'f'::"char") THEN 'foreign table'::text
! ELSE NULL::text
! END AS objtype,
! rel.relnamespace AS objnamespace,
! CASE
! WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
! END AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'column'::text AS objtype,
! rel.relnamespace AS objnamespace,
! ((
! CASE
! WHEN pg_table_is_visible(rel.oid) THEN quote_ident((rel.relname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((rel.relname)::text))
! END || '.'::text) || (att.attname)::text) AS objname,
! l.provider,
! l.label
! FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid = rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid = att.attrelid) AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace = nsp.oid)))
! WHERE (l.objsubid <> 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (pro.proisagg = true) THEN 'aggregate'::text
! WHEN (pro.proisagg = false) THEN 'function'::text
! ELSE NULL::text
! END AS objtype,
! pro.pronamespace AS objnamespace,
! (((
! CASE
! WHEN pg_function_is_visible(pro.oid) THEN quote_ident((pro.proname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((pro.proname)::text))
! END || '('::text) || pg_get_function_arguments(pro.oid)) || ')'::text) AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid = pro.tableoid) AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace = nsp.oid)))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! CASE
! WHEN (typ.typtype = 'd'::"char") THEN 'domain'::text
! ELSE 'type'::text
! END AS objtype,
! typ.typnamespace AS objnamespace,
! CASE
! WHEN pg_type_is_visible(typ.oid) THEN quote_ident((typ.typname)::text)
! ELSE ((quote_ident((nsp.nspname)::text) || '.'::text) || quote_ident((typ.typname)::text))
! END AS objname,
! l.provider,
! l.label
! FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND (l.objoid = typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'large object'::text AS objtype,
! NULL::oid AS objnamespace,
! (l.objoid)::text AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND (l.objsubid = 0))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'language'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((lan.lanname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid = lan.oid))))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'schema'::text AS objtype,
! nsp.oid AS objnamespace,
! quote_ident((nsp.nspname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid = nsp.oid))))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! l.objsubid,
! 'event trigger'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((evt.evtname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0)
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'database'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((dat.datname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid))))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'tablespace'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((spc.spcname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid))))
! UNION ALL
! SELECT l.objoid,
! l.classoid,
! 0 AS objsubid,
! 'role'::text AS objtype,
! NULL::oid AS objnamespace,
! quote_ident((rol.rolname)::text) AS objname,
! l.provider,
! l.label
! FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index d76ef4e..06b372b 100644
*** a/src/test/regress/expected/with.out
--- b/src/test/regress/expected/with.out
*************** SELECT * FROM vsubdepartment ORDER BY na
*** 300,347 ****
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
! pg_get_viewdef
! -------------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE (department.name = 'A'::text)+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE (d.parent_department = sd.id)+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! -----------------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE department.name = 'A'::text+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE d.parent_department = sd.id+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
--- 300,347 ----
-- Check reverse listing
SELECT pg_get_viewdef('vsubdepartment'::regclass);
! pg_get_viewdef
! -----------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE (department.name = 'A'::text)+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE (d.parent_department = sd.id)+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
SELECT pg_get_viewdef('vsubdepartment'::regclass, true);
! pg_get_viewdef
! ---------------------------------------------
! WITH RECURSIVE subdepartment AS ( +
! SELECT department.id, +
! department.parent_department, +
! department.name +
! FROM department +
! WHERE department.name = 'A'::text+
! UNION ALL +
! SELECT d.id, +
! d.parent_department, +
! d.name +
! FROM department d, +
! subdepartment sd +
! WHERE d.parent_department = sd.id+
! ) +
! SELECT subdepartment.id, +
! subdepartment.parent_department, +
! subdepartment.name +
FROM subdepartment;
(1 row)
*************** SELECT sum(n) FROM t;
*** 360,370 ****
sum | bigint | | plain |
View definition:
WITH RECURSIVE t(n) AS (
! VALUES (1)
UNION ALL
! SELECT t_1.n + 1
! FROM t t_1
! WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
--- 360,370 ----
sum | bigint | | plain |
View definition:
WITH RECURSIVE t(n) AS (
! VALUES (1)
UNION ALL
! SELECT t_1.n + 1
! FROM t t_1
! WHERE t_1.n < 100
)
SELECT sum(t.n) AS sum
FROM t;
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Here's a draft patch tackling point 1. This gets rid of a whole lot
> of parenthesization, as well as indentation, for simple UNION lists.
> You can see the results in the changed regression test outputs.
[...]
> Comments?
+10000.
Thanks,
Stephen
I wrote:
> While I was testing this I noticed that there's something thoroughly
> busted about the indentation of outer JOIN constructs, too --- you
> can see this in some of the regression test queries that are touched
> by this patch, where the JOINs are actually outdented to the left of
> their FROM clause in the unpatched output. (They'd be outdented in
> the new output, too, if negative indentation were possible...)
> That seems like material for a separate patch though.
I poked into that too, and found that it seems to have been busted from
the very beginning. There's a unmatched subtraction of
PRETTYINDENT_JOIN_ON from the indentLevel, which I suppose must be an
accidental leftover from some previous version of the logic. And the code
is also trying to outdent JOIN clauses further than the corresponding
FROM, which seems unintuitive to me, even when the FROM is indented far
enough to make it possible (which it generally isn't in simple views).
Attached are two separate versions of a repair patch. The first one
causes JOIN clauses to be indented the same as their parent FROM.
The second one moves them over an additional 5 spaces. I find the
second layout more sensible, but it does result in significantly
more changes in the regression test outputs, as you can see.
Comments?
regards, tom lane
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..f6bf2c3 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 68,75 ****
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 13
! #define PRETTYINDENT_JOIN_ON (PRETTYINDENT_JOIN-PRETTYINDENT_STD)
#define PRETTYINDENT_VAR 4
/* Pretty flags */
--- 68,74 ----
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 2
#define PRETTYINDENT_VAR 4
/* Pretty flags */
*************** get_from_clause_item(Node *jtnode, Query
*** 8378,8404 ****
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 1);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
default:
elog(ERROR, "unrecognized join type: %d",
--- 8377,8408 ----
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
default:
elog(ERROR, "unrecognized join type: %d",
*************** get_from_clause_item(Node *jtnode, Query
*** 8411,8418 ****
if (need_paren_on_right)
appendStringInfoChar(buf, ')');
- context->indentLevel -= PRETTYINDENT_JOIN_ON;
-
if (j->usingClause)
{
ListCell *lc;
--- 8415,8420 ----
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..3835f12 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** create table tt5 (a int, b int);
*** 1035,1084 ****
create table tt6 (c int, d int);
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc, dd);
(1 row)
alter table tt5 add column c int;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! -----------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc, dd);
(1 row)
alter table tt5 add column cc int;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! -----------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
(1 row)
alter table tt5 drop column c;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
--- 1035,1084 ----
create table tt6 (c int, d int);
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! ---------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc, dd);
(1 row)
alter table tt5 add column c int;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! ------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc, dd);
(1 row)
alter table tt5 add column cc int;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
(1 row)
alter table tt5 drop column c;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! ---------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
*************** select pg_get_viewdef('vv2', true);
*** 1105,1112 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
--- 1105,1112 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
*************** select pg_get_viewdef('vv3', true);
*** 1133,1141 ****
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
create view vv4 as
--- 1133,1141 ----
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
create view vv4 as
*************** select pg_get_viewdef('vv4', true);
*** 1164,1173 ****
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
alter table tt7 add column zz int;
--- 1164,1173 ----
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
alter table tt7 add column zz int;
*************** select pg_get_viewdef('vv2', true);
*** 1190,1197 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
--- 1190,1197 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
*************** select pg_get_viewdef('vv3', true);
*** 1212,1220 ****
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
--- 1212,1220 ----
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
*************** select pg_get_viewdef('vv4', true);
*** 1237,1246 ****
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
--- 1237,1246 ----
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
*************** select pg_get_viewdef('vv2a', true);
*** 1267,1274 ****
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1267,1274 ----
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..976a7b2 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_seclabels| ( ( (
*** 1420,1428 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace
=nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1420,1428 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid
=rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1437,1446 ****
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid =
att.attrelid)AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace =
nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1437,1446 ----
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid
=rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON
(((rel.oid= att.attrelid) AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1459,1467 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1459,1467 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON
((pro.pronamespace= nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1478,1486 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND
(l.objoid= typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1478,1486 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid)
AND(l.objoid = typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1491,1498 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1491,1498 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid =
lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1503,1510 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid =
lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1503,1510 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND
(l.objoid= lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1515,1522 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1515,1522 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1527,1534 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1527,1534 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid =
evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1539,1545 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1539,1545 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1550,1556 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1550,1556 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** UNION ALL
*** 1561,1567 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
--- 1561,1567 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index ea7b8c5..27db21e 100644
*** a/src/backend/utils/adt/ruleutils.c
--- b/src/backend/utils/adt/ruleutils.c
***************
*** 68,75 ****
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 13
! #define PRETTYINDENT_JOIN_ON (PRETTYINDENT_JOIN-PRETTYINDENT_STD)
#define PRETTYINDENT_VAR 4
/* Pretty flags */
--- 68,74 ----
/* Indent counts */
#define PRETTYINDENT_STD 8
! #define PRETTYINDENT_JOIN 7
#define PRETTYINDENT_VAR 4
/* Pretty flags */
*************** get_from_clause_item(Node *jtnode, Query
*** 8378,8404 ****
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 1);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_JOIN,
! PRETTYINDENT_JOIN, 2);
break;
default:
elog(ERROR, "unrecognized join type: %d",
--- 8377,8408 ----
case JOIN_INNER:
if (j->quals)
appendContextKeyword(context, " JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
else
appendContextKeyword(context, " CROSS JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_LEFT:
appendContextKeyword(context, " LEFT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_FULL:
appendContextKeyword(context, " FULL JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
case JOIN_RIGHT:
appendContextKeyword(context, " RIGHT JOIN ",
! -PRETTYINDENT_STD,
! PRETTYINDENT_STD,
! PRETTYINDENT_JOIN);
break;
default:
elog(ERROR, "unrecognized join type: %d",
*************** get_from_clause_item(Node *jtnode, Query
*** 8411,8418 ****
if (need_paren_on_right)
appendStringInfoChar(buf, ')');
- context->indentLevel -= PRETTYINDENT_JOIN_ON;
-
if (j->usingClause)
{
ListCell *lc;
--- 8415,8420 ----
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index f6db582..d29d0c1 100644
*** a/src/test/regress/expected/create_view.out
--- b/src/test/regress/expected/create_view.out
*************** create view v2 as select * from tt2 join
*** 706,1064 ****
create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j;
create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b);
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! ---------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! ------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! --------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ---------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 USING (b, c) +
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! -----------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c) +
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 add column d int;
alter table tt2 add column e int;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! ---------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! ------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! --------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ---------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 USING (b, c) +
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! -----------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c) +
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 rename c to d;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! -----------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! --------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! ----------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ----------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ----------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 add column c int;
alter table tt3 add column e int;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! -------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! ---------------------------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ---------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
! JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 drop column d;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! -------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! ------------------------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
! JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ------------------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
create table tt5 (a int, b int);
create table tt6 (c int, d int);
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc, dd);
(1 row)
alter table tt5 add column c int;
select pg_get_viewdef('vv1', true);
- pg_get_viewdef
- -----------------------------------------
- SELECT j.aa, +
- j.bb, +
- j.cc, +
- j.dd +
- FROM (tt5 +
- CROSS JOIN tt6) j(aa, bb, c, cc, dd);
- (1 row)
-
- alter table tt5 add column cc int;
- select pg_get_viewdef('vv1', true);
pg_get_viewdef
-----------------------------------------------
SELECT j.aa, +
--- 706,1052 ----
create view v2a as select * from (tt2 join tt3 using (b,c) join tt4 using (b)) j;
create view v3 as select * from tt2 join tt3 using (b,c) full join tt4 using (b);
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! --------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! -----------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! -------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! --------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 USING (b, c) +
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ----------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c) +
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 add column d int;
alter table tt2 add column e int;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! --------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! -----------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! -------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! --------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 USING (b, c) +
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ----------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 USING (b, c) +
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 rename c to d;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! ----------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! -------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)) j;
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! ---------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! ---------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! JOIN tt4 USING (b)) j;
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! ---------------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt3 add column c int;
alter table tt3 add column e int;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! ------------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! --------------------------------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, d, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! --------------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
! JOIN tt4 USING (b)) j(b, c, a, d, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
alter table tt2 drop column d;
select pg_get_viewdef('v1', true);
! pg_get_viewdef
! ------------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c);
(1 row)
select pg_get_viewdef('v1a', true);
! pg_get_viewdef
! -----------------------------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)) j(b, c, a, e, ax, c_1, e_1);
(1 row)
select pg_get_viewdef('v2', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT tt2.b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! JOIN tt4 USING (b);
(1 row)
select pg_get_viewdef('v2a', true);
! pg_get_viewdef
! -----------------------------------------------------------------
! SELECT j.b, +
! j.c, +
! j.a, +
! j.ax, +
! j.ay, +
! j.q +
! FROM (tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c) +
! JOIN tt4 USING (b)) j(b, c, a, e, ax, c_1, e_1, ay, q);
(1 row)
select pg_get_viewdef('v3', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT b, +
! tt3.c, +
! tt2.a, +
! tt3.ax, +
! tt4.ay, +
! tt4.q +
! FROM tt2 +
! JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c)+
! FULL JOIN tt4 USING (b);
(1 row)
create table tt5 (a int, b int);
create table tt6 (c int, d int);
create view vv1 as select * from (tt5 cross join tt6) j(aa,bb,cc,dd);
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc, dd);
(1 row)
alter table tt5 add column c int;
select pg_get_viewdef('vv1', true);
pg_get_viewdef
-----------------------------------------------
SELECT j.aa, +
*************** select pg_get_viewdef('vv1', true);
*** 1066,1084 ****
j.cc, +
j.dd +
FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
(1 row)
alter table tt5 drop column c;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
--- 1054,1084 ----
j.cc, +
j.dd +
FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc, dd);
! (1 row)
!
! alter table tt5 add column cc int;
! select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! -----------------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, c, cc_1, cc, dd);
(1 row)
alter table tt5 drop column c;
select pg_get_viewdef('vv1', true);
! pg_get_viewdef
! --------------------------------------------------
! SELECT j.aa, +
! j.bb, +
! j.cc, +
! j.dd +
! FROM (tt5 +
! CROSS JOIN tt6) j(aa, bb, cc_1, cc, dd);
(1 row)
-- Unnamed FULL JOIN USING is lots of fun too
*************** select pg_get_viewdef('vv2', true);
*** 1105,1112 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
--- 1105,1112 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z);
(1 row)
create view vv3 as
*************** select pg_get_viewdef('vv3', true);
*** 1133,1141 ****
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
create view vv4 as
--- 1133,1141 ----
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1);
(1 row)
create view vv4 as
*************** select pg_get_viewdef('vv4', true);
*** 1164,1173 ****
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
alter table tt7 add column zz int;
--- 1164,1173 ----
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y) +
! FULL JOIN tt8 tt8x(x_1, z) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z) USING (x_1);
(1 row)
alter table tt7 add column zz int;
*************** select pg_get_viewdef('vv2', true);
*** 1190,1197 ****
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
--- 1190,1197 ----
tt8x.x_1 AS d, +
tt8x.z AS e +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt8 tt8x(x_1, z, z2);
(1 row)
select pg_get_viewdef('vv3', true);
*************** select pg_get_viewdef('vv3', true);
*** 1212,1220 ****
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
--- 1212,1220 ----
tt7x.y AS e, +
tt8x.z AS f +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1);
(1 row)
select pg_get_viewdef('vv4', true);
*************** select pg_get_viewdef('vv4', true);
*** 1237,1246 ****
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
--- 1237,1246 ----
tt8x.z AS f, +
tt8y.z AS g +
FROM tt7 +
! FULL JOIN tt8 USING (x), +
! tt7 tt7x(x_1, y, z) +
! FULL JOIN tt8 tt8x(x_1, z, z2) USING (x_1) +
! FULL JOIN tt8 tt8y(x_1, z, z2) USING (x_1);
(1 row)
-- Implicit coercions in a JOIN USING create issues similar to FULL JOIN
*************** select pg_get_viewdef('vv2a', true);
*** 1267,1274 ****
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
--- 1267,1274 ----
tt8ax.x_1 AS d, +
tt8ax.z AS e +
FROM tt7a +
! LEFT JOIN tt8a USING (x), +
! tt8a tt8ax(x_1, z);
(1 row)
--
*************** create table tt9 (x int, xx int, y int);
*** 1278,1301 ****
create table tt10 (x int, z int);
create view vv5 as select x,y,z from tt9 join tt10 using(x);
select pg_get_viewdef('vv5', true);
! pg_get_viewdef
! -------------------------
! SELECT tt9.x, +
! tt9.y, +
! tt10.z +
! FROM tt9 +
! JOIN tt10 USING (x);
(1 row)
alter table tt9 drop column xx;
select pg_get_viewdef('vv5', true);
! pg_get_viewdef
! -------------------------
! SELECT tt9.x, +
! tt9.y, +
! tt10.z +
! FROM tt9 +
! JOIN tt10 USING (x);
(1 row)
-- clean up all the random objects we made above
--- 1278,1301 ----
create table tt10 (x int, z int);
create view vv5 as select x,y,z from tt9 join tt10 using(x);
select pg_get_viewdef('vv5', true);
! pg_get_viewdef
! ------------------------------
! SELECT tt9.x, +
! tt9.y, +
! tt10.z +
! FROM tt9 +
! JOIN tt10 USING (x);
(1 row)
alter table tt9 drop column xx;
select pg_get_viewdef('vv5', true);
! pg_get_viewdef
! ------------------------------
! SELECT tt9.x, +
! tt9.y, +
! tt10.z +
! FROM tt9 +
! JOIN tt10 USING (x);
(1 row)
-- clean up all the random objects we made above
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 9d40510..cb20b6b 100644
*** a/src/test/regress/expected/rangefuncs.out
--- b/src/test/regress/expected/rangefuncs.out
*************** select * from vw_ord;
*** 85,98 ****
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! -------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN foot(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
--- 85,98 ----
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! ------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN foot(1) WITH ORDINALITY z(a, b, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
*************** select * from vw_ord;
*** 112,127 ****
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! ---------------------------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.c, +
! z.d, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN ROWS FROM(foot(1), foot(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
--- 112,127 ----
(1 row)
select definition from pg_views where viewname='vw_ord';
! definition
! --------------------------------------------------------------------------------------------------
! SELECT v.n, +
! z.a, +
! z.b, +
! z.c, +
! z.d, +
! z.ord +
! FROM (( VALUES (1)) v(n) +
! JOIN ROWS FROM(foot(1), foot(2)) WITH ORDINALITY z(a, b, c, d, ord) ON ((v.n = z.ord)));
(1 row)
drop view vw_ord;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 6c51d0d..da5c836 100644
*** a/src/test/regress/expected/rules.out
--- b/src/test/regress/expected/rules.out
*************** pg_available_extension_versions| SELECT
*** 1294,1306 ****
e.requires,
e.comment
FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment)
! LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
pg_available_extensions| SELECT e.name,
e.default_version,
x.extversion AS installed_version,
e.comment
FROM (pg_available_extensions() e(name, default_version, comment)
! LEFT JOIN pg_extension x ON ((e.name = x.extname)));
pg_cursors| SELECT c.name,
c.statement,
c.is_holdable,
--- 1294,1306 ----
e.requires,
e.comment
FROM (pg_available_extension_versions() e(name, version, superuser, relocatable, schema, requires, comment)
! LEFT JOIN pg_extension x ON (((e.name = x.extname) AND (e.version = x.extversion))));
pg_available_extensions| SELECT e.name,
e.default_version,
x.extversion AS installed_version,
e.comment
FROM (pg_available_extensions() e(name, default_version, comment)
! LEFT JOIN pg_extension x ON ((e.name = x.extname)));
pg_cursors| SELECT c.name,
c.statement,
c.is_holdable,
*************** pg_indexes| SELECT n.nspname AS schemana
*** 1321,1330 ****
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM ((((pg_index x
! JOIN pg_class c ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char"));
pg_locks| SELECT l.locktype,
l.database,
--- 1321,1330 ----
t.spcname AS tablespace,
pg_get_indexdef(i.oid) AS indexdef
FROM ((((pg_index x
! JOIN pg_class c ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = i.reltablespace)))
WHERE ((c.relkind = ANY (ARRAY['r'::"char", 'm'::"char"])) AND (i.relkind = 'i'::"char"));
pg_locks| SELECT l.locktype,
l.database,
*************** pg_matviews| SELECT n.nspname AS scheman
*** 1350,1357 ****
c.relispopulated AS ispopulated,
pg_get_viewdef(c.oid) AS definition
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
pg_prepared_statements| SELECT p.name,
p.statement,
--- 1350,1357 ----
c.relispopulated AS ispopulated,
pg_get_viewdef(c.oid) AS definition
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'm'::"char");
pg_prepared_statements| SELECT p.name,
p.statement,
*************** pg_prepared_xacts| SELECT p.transaction,
*** 1365,1372 ****
u.rolname AS owner,
d.datname AS database
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
! LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
! LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_replication_slots| SELECT l.slot_name,
l.plugin,
l.slot_type,
--- 1365,1372 ----
u.rolname AS owner,
d.datname AS database
FROM ((pg_prepared_xact() p(transaction, gid, prepared, ownerid, dbid)
! LEFT JOIN pg_authid u ON ((p.ownerid = u.oid)))
! LEFT JOIN pg_database d ON ((p.dbid = d.oid)));
pg_replication_slots| SELECT l.slot_name,
l.plugin,
l.slot_type,
*************** pg_replication_slots| SELECT l.slot_name
*** 1377,1383 ****
l.catalog_xmin,
l.restart_lsn
FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, active, xmin, catalog_xmin, restart_lsn)
! LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
pg_roles| SELECT pg_authid.rolname,
pg_authid.rolsuper,
pg_authid.rolinherit,
--- 1377,1383 ----
l.catalog_xmin,
l.restart_lsn
FROM (pg_get_replication_slots() l(slot_name, plugin, slot_type, datoid, active, xmin, catalog_xmin, restart_lsn)
! LEFT JOIN pg_database d ON ((l.datoid = d.oid)));
pg_roles| SELECT pg_authid.rolname,
pg_authid.rolsuper,
pg_authid.rolinherit,
*************** pg_roles| SELECT pg_authid.rolname,
*** 1392,1405 ****
s.setconfig AS rolconfig,
pg_authid.oid
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules| SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
pg_get_ruledef(r.oid) AS definition
FROM ((pg_rewrite r
! JOIN pg_class c ON ((c.oid = r.ev_class)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels| ( ( ( ( ( ( ( ( ( SELECT
l.objoid,
l.classoid,
--- 1392,1405 ----
s.setconfig AS rolconfig,
pg_authid.oid
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))));
pg_rules| SELECT n.nspname AS schemaname,
c.relname AS tablename,
r.rulename,
pg_get_ruledef(r.oid) AS definition
FROM ((pg_rewrite r
! JOIN pg_class c ON ((c.oid = r.ev_class)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (r.rulename <> '_RETURN'::name);
pg_seclabels| ( ( ( ( ( ( ( ( ( SELECT
l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1420,1428 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON ((rel.relnamespace
=nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1420,1428 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_class rel ON
(((l.classoid= rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid = 0)
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1437,1446 ****
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON (((l.classoid =
rel.tableoid)AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON (((rel.oid =
att.attrelid)AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON ((rel.relnamespace =
nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1437,1446 ----
l.provider,
l.label
FROM (((pg_seclabel l
! JOIN pg_class rel ON
(((l.classoid= rel.tableoid) AND (l.objoid = rel.oid))))
! JOIN pg_attribute att ON
(((rel.oid= att.attrelid) AND (l.objsubid = att.attnum))))
! JOIN pg_namespace nsp ON
((rel.relnamespace= nsp.oid)))
! WHERE (l.objsubid <> 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1459,1467 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON ((pro.pronamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1459,1467 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_proc pro ON (((l.classoid =
pro.tableoid)AND (l.objoid = pro.oid))))
! JOIN pg_namespace nsp ON
((pro.pronamespace= nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1478,1486 ****
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid = typ.tableoid) AND
(l.objoid= typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace = nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1478,1486 ----
l.provider,
l.label
FROM ((pg_seclabel l
! JOIN pg_type typ ON (((l.classoid =
typ.tableoid)AND (l.objoid = typ.oid))))
! JOIN pg_namespace nsp ON ((typ.typnamespace =
nsp.oid)))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1491,1498 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid = lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1491,1498 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_largeobject_metadata lom ON ((l.objoid =
lom.oid)))
! WHERE ((l.classoid = ('pg_largeobject'::regclass)::oid) AND
(l.objsubid= 0)))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1503,1510 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND (l.objoid =
lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1503,1510 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_language lan ON (((l.classoid = lan.tableoid) AND
(l.objoid= lan.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1515,1522 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid =
nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1515,1522 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_namespace nsp ON (((l.classoid = nsp.tableoid) AND (l.objoid
=nsp.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1527,1534 ****
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1527,1534 ----
l.provider,
l.label
FROM (pg_seclabel l
! JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid =
evt.oid))))
! WHERE (l.objsubid = 0))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1539,1545 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1539,1545 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_database dat ON (((l.classoid = dat.tableoid) AND (l.objoid = dat.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** pg_seclabels| ( ( (
*** 1550,1556 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
--- 1550,1556 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_tablespace spc ON (((l.classoid = spc.tableoid) AND (l.objoid = spc.oid)))))
UNION ALL
SELECT l.objoid,
l.classoid,
*************** UNION ALL
*** 1561,1567 ****
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
--- 1561,1567 ----
l.provider,
l.label
FROM (pg_shseclabel l
! JOIN pg_authid rol ON (((l.classoid = rol.tableoid) AND (l.objoid = rol.oid))));
pg_settings| SELECT a.name,
a.setting,
a.unit,
*************** pg_shadow| SELECT pg_authid.rolname AS u
*** 1589,1595 ****
(pg_authid.rolvaliduntil)::abstime AS valuntil,
s.setconfig AS useconfig
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
WHERE pg_authid.rolcanlogin;
pg_stat_activity| SELECT s.datid,
d.datname,
--- 1589,1595 ----
(pg_authid.rolvaliduntil)::abstime AS valuntil,
s.setconfig AS useconfig
FROM (pg_authid
! LEFT JOIN pg_db_role_setting s ON (((pg_authid.oid = s.setrole) AND (s.setdatabase = (0)::oid))))
WHERE pg_authid.rolcanlogin;
pg_stat_activity| SELECT s.datid,
d.datname,
*************** pg_stat_all_indexes| SELECT c.oid AS rel
*** 1622,1630 ****
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_stat_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1622,1630 ----
pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_stat_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_stat_all_tables| SELECT c.oid AS reli
*** 1649,1656 ****
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_archiver| SELECT s.archived_count,
--- 1649,1656 ----
pg_stat_get_analyze_count(c.oid) AS analyze_count,
pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_archiver| SELECT s.archived_count,
*************** pg_stat_user_functions| SELECT p.oid AS
*** 1761,1767 ****
pg_stat_get_function_total_time(p.oid) AS total_time,
pg_stat_get_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
--- 1761,1767 ----
pg_stat_get_function_total_time(p.oid) AS total_time,
pg_stat_get_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_function_calls(p.oid) IS NOT NULL));
pg_stat_user_indexes| SELECT pg_stat_all_indexes.relid,
pg_stat_all_indexes.indexrelid,
*************** pg_stat_xact_all_tables| SELECT c.oid AS
*** 1809,1816 ****
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid,
--- 1809,1816 ----
pg_stat_get_xact_tuples_deleted(c.oid) AS n_tup_del,
pg_stat_get_xact_tuples_hot_updated(c.oid) AS n_tup_hot_upd
FROM ((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname;
pg_stat_xact_sys_tables| SELECT pg_stat_xact_all_tables.relid,
*************** pg_stat_xact_user_functions| SELECT p.oi
*** 1833,1839 ****
pg_stat_get_xact_function_total_time(p.oid) AS total_time,
pg_stat_get_xact_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid,
pg_stat_xact_all_tables.schemaname,
--- 1833,1839 ----
pg_stat_get_xact_function_total_time(p.oid) AS total_time,
pg_stat_get_xact_function_self_time(p.oid) AS self_time
FROM (pg_proc p
! LEFT JOIN pg_namespace n ON ((n.oid = p.pronamespace)))
WHERE ((p.prolang <> (12)::oid) AND (pg_stat_get_xact_function_calls(p.oid) IS NOT NULL));
pg_stat_xact_user_tables| SELECT pg_stat_xact_all_tables.relid,
pg_stat_xact_all_tables.schemaname,
*************** pg_statio_all_indexes| SELECT c.oid AS r
*** 1856,1864 ****
(pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_statio_all_sequences| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1856,1864 ----
(pg_stat_get_blocks_fetched(i.oid) - pg_stat_get_blocks_hit(i.oid)) AS idx_blks_read,
pg_stat_get_blocks_hit(i.oid) AS idx_blks_hit
FROM (((pg_class c
! JOIN pg_index x ON ((c.oid = x.indrelid)))
! JOIN pg_class i ON ((i.oid = x.indexrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]));
pg_statio_all_sequences| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_statio_all_sequences| SELECT c.oid AS
*** 1866,1872 ****
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
pg_stat_get_blocks_hit(c.oid) AS blks_hit
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
pg_statio_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
--- 1866,1872 ----
(pg_stat_get_blocks_fetched(c.oid) - pg_stat_get_blocks_hit(c.oid)) AS blks_read,
pg_stat_get_blocks_hit(c.oid) AS blks_hit
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'S'::"char");
pg_statio_all_tables| SELECT c.oid AS relid,
n.nspname AS schemaname,
*************** pg_statio_all_tables| SELECT c.oid AS re
*** 1880,1889 ****
(sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS
tidx_blks_read,
(sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
FROM ((((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
! LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
--- 1880,1889 ----
(sum((pg_stat_get_blocks_fetched(x.indexrelid) - pg_stat_get_blocks_hit(x.indexrelid))))::bigint AS
tidx_blks_read,
(sum(pg_stat_get_blocks_hit(x.indexrelid)))::bigint AS tidx_blks_hit
FROM ((((pg_class c
! LEFT JOIN pg_index i ON ((c.oid = i.indrelid)))
! LEFT JOIN pg_class t ON ((c.reltoastrelid = t.oid)))
! LEFT JOIN pg_index x ON ((t.oid = x.indrelid)))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]))
GROUP BY c.oid, n.nspname, c.relname, t.oid, x.indrelid;
pg_statio_sys_indexes| SELECT pg_statio_all_indexes.relid,
*************** pg_stats| SELECT n.nspname AS schemaname
*** 2008,2016 ****
ELSE NULL::real[]
END AS elem_count_histogram
FROM (((pg_statistic s
! JOIN pg_class c ON ((c.oid = s.starelid)))
! JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
--- 2008,2016 ----
ELSE NULL::real[]
END AS elem_count_histogram
FROM (((pg_statistic s
! JOIN pg_class c ON ((c.oid = s.starelid)))
! JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text));
pg_tables| SELECT n.nspname AS schemaname,
c.relname AS tablename,
*************** pg_tables| SELECT n.nspname AS schemanam
*** 2020,2027 ****
c.relhasrules AS hasrules,
c.relhastriggers AS hastriggers
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev,
pg_timezone_abbrevs.utc_offset,
--- 2020,2027 ----
c.relhasrules AS hasrules,
c.relhastriggers AS hastriggers
FROM ((pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
! LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))
WHERE (c.relkind = 'r'::"char");
pg_timezone_abbrevs| SELECT pg_timezone_abbrevs.abbrev,
pg_timezone_abbrevs.utc_offset,
*************** pg_user_mappings| SELECT u.oid AS umid,
*** 2055,2068 ****
ELSE NULL::text[]
END AS umoptions
FROM ((pg_user_mapping u
! LEFT JOIN pg_authid a ON ((a.oid = u.umuser)))
! JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views| SELECT n.nspname AS schemaname,
c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner,
pg_get_viewdef(c.oid) AS definition
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'v'::"char");
rtest_v1| SELECT rtest_t1.a,
rtest_t1.b
--- 2055,2068 ----
ELSE NULL::text[]
END AS umoptions
FROM ((pg_user_mapping u
! LEFT JOIN pg_authid a ON ((a.oid = u.umuser)))
! JOIN pg_foreign_server s ON ((u.umserver = s.oid)));
pg_views| SELECT n.nspname AS schemaname,
c.relname AS viewname,
pg_get_userbyid(c.relowner) AS viewowner,
pg_get_viewdef(c.oid) AS definition
FROM (pg_class c
! LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
WHERE (c.relkind = 'v'::"char");
rtest_v1| SELECT rtest_t1.a,
rtest_t1.b
On Wed, Apr 30, 2014 at 6:33 AM, Stephen Frost <sfrost@snowman.net> wrote: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> Here's a draft patch tackling point 1. This gets rid of a whole lot >> of parenthesization, as well as indentation, for simple UNION lists. >> You can see the results in the changed regression test outputs. > [...] >> Comments? > > +10000. +1. Output is far easier to read. -- Michael