Обсуждение: 7.2.1: pg_dump of UNIONed VIEWs broken
PostgreSQL 7.2.1's pg_dump prduces bad SQL for VIEW's.
The SQL for CREATE VIEW as output by pg_dump generates an error, because the
appropriate nesting of SELECTs with parentheses is missing:
ERROR: parser: parse error at or near "SELECT"
Original SQL:
CREATE VIEW "agents" as (
(
SELECT DISTINCT
employees.contact_id
FROM
employees
ORDER BY
employees.contact_id
)
UNION
(
SELECT DISTINCT
contractors.contact_id
FROM
contractors
ORDER BY
contractors.contact_id
)
);
As output by pg_dump:
CREATE VIEW "agents" as SELECT DISTINCT employees.contact_id FROM employees
ORDER BY employees.contact_id UNION SELECT DISTINCT contractors.contact_id
FROM contractors ORDER BY contractors.contact_id;
As you can see, the nesting is entirely lost.
Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------
Ian Morgan <imorgan@webcon.net> writes:
> The SQL for CREATE VIEW as output by pg_dump generates an error, because the
> appropriate nesting of SELECTs with parentheses is missing:
Hmm. You shouldn't *need* parentheses in most cases ...
> CREATE VIEW "agents" as (
> (
> SELECT DISTINCT
> employees.contact_id
> FROM
> employees
> ORDER BY
> employees.contact_id
> )
> UNION
> (
> SELECT DISTINCT
> contractors.contact_id
> FROM
> contractors
> ORDER BY
> contractors.contact_id
> )
> );
although I suppose this is a counterexample. Would it help any to point
out that ORDER BY inside an arm of a UNION is a complete waste of
cycles? That's probably why it didn't occur to anyone to test this.
BTW, the SELECT DISTINCTs are also a waste of cycles, since UNION will
do that anyway.
regards, tom lane
On Mon, 22 Apr 2002, Tom Lane wrote:
> Ian Morgan <imorgan@webcon.net> writes:
> > The SQL for CREATE VIEW as output by pg_dump generates an error, because the
> > appropriate nesting of SELECTs with parentheses is missing:
>
> Hmm. You shouldn't *need* parentheses in most cases ...
>
> > CREATE VIEW "agents" as (
> > (
> > SELECT DISTINCT
> > employees.contact_id
> > FROM
> > employees
> > ORDER BY
> > employees.contact_id
> > )
> > UNION
> > (
> > SELECT DISTINCT
> > contractors.contact_id
> > FROM
> > contractors
> > ORDER BY
> > contractors.contact_id
> > )
> > );
>
> although I suppose this is a counterexample. Would it help any to point
> out that ORDER BY inside an arm of a UNION is a complete waste of
> cycles? That's probably why it didn't occur to anyone to test this.
>
> BTW, the SELECT DISTINCTs are also a waste of cycles, since UNION will
> do that anyway.
Interesting observations. I knew the ORDER BYs were irrelevant, but hadn't
gotten around to removing them. The DISTINCTs, on the other hand were a
throwback to some old queries, and I completely missed that the UNION would
do that for me.. hehe.. oops.
Anyhow, rewriting the select:
Works:
SELECT employees.contact_id FROM employees
UNION
SELECT contractors.contact_id FROM contractors;
Does not work:
SELECT employees.contact_id FROM employees ORDER BY employees.contact_id
UNION
SELECT contractors.contact_id FROM contractors ORDER BY contractors.contact_id;
Even if adding ORDER BY to a UNION is really sub-optimal SQL, shouldn't
pg_dump (and even psql's \d <viewname>) produce SQL that actually works for
any query that was originally accepted when creating the VIEW?
Otherwise, maybe having ORDER BY in a VIEW's SELECT should be made illegal?
Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------
Ian Morgan <imorgan@webcon.net> writes:
> Even if adding ORDER BY to a UNION is really sub-optimal SQL, shouldn't
> pg_dump (and even psql's \d <viewname>) produce SQL that actually
> works
I didn't say it wasn't a bug ;-) ... just pointing out that fixing it
doesn't seem very high priority. I'll do something about it next time
I have occasion to touch ruleutils.c.
regards, tom lane
Can someone phrase a TODO item for me? --------------------------------------------------------------------------- Ian Morgan wrote: > PostgreSQL 7.2.1's pg_dump prduces bad SQL for VIEW's. > > The SQL for CREATE VIEW as output by pg_dump generates an error, because the > appropriate nesting of SELECTs with parentheses is missing: > ERROR: parser: parse error at or near "SELECT" > > > Original SQL: > > CREATE VIEW "agents" as ( > ( > SELECT DISTINCT > employees.contact_id > FROM > employees > ORDER BY > employees.contact_id > ) > UNION > ( > SELECT DISTINCT > contractors.contact_id > FROM > contractors > ORDER BY > contractors.contact_id > ) > ); > > > As output by pg_dump: > > CREATE VIEW "agents" as SELECT DISTINCT employees.contact_id FROM employees > ORDER BY employees.contact_id UNION SELECT DISTINCT contractors.contact_id > FROM contractors ORDER BY contractors.contact_id; > > As you can see, the nesting is entirely lost. > > Regards, > Ian Morgan > -- > ------------------------------------------------------------------- > Ian E. Morgan Vice President & C.O.O. Webcon, Inc. > imorgan@webcon.net PGP: #2DA40D07 www.webcon.net > * Customized Linux network solutions for your business * > ------------------------------------------------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Tue, 23 Apr 2002, Bruce Momjian wrote:
> Can someone phrase a TODO item for me?
How about:
Fix pg_get_viewdef() to output fully nested selects
Regards,
Ian Morgan
--
-------------------------------------------------------------------
Ian E. Morgan Vice President & C.O.O. Webcon, Inc.
imorgan@webcon.net PGP: #2DA40D07 www.webcon.net
* Customized Linux network solutions for your business *
-------------------------------------------------------------------
> Ian Morgan wrote:
> > PostgreSQL 7.2.1's pg_dump prduces bad SQL for VIEW's.
> >
> > The SQL for CREATE VIEW as output by pg_dump generates an error, because the
> > appropriate nesting of SELECTs with parentheses is missing:
> > ERROR: parser: parse error at or near "SELECT"
> >
> >
> > Original SQL:
> >
> > CREATE VIEW "agents" as (
> > (
> > SELECT DISTINCT
> > employees.contact_id
> > FROM
> > employees
> > ORDER BY
> > employees.contact_id
> > )
> > UNION
> > (
> > SELECT DISTINCT
> > contractors.contact_id
> > FROM
> > contractors
> > ORDER BY
> > contractors.contact_id
> > )
> > );
> >
> >
> > As output by pg_dump:
> >
> > CREATE VIEW "agents" as SELECT DISTINCT employees.contact_id FROM employees
> > ORDER BY employees.contact_id UNION SELECT DISTINCT contractors.contact_id
> > FROM contractors ORDER BY contractors.contact_id;
> >
> > As you can see, the nesting is entirely lost.
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Can someone phrase a TODO item for me?
It's already done (not committed yet though).
regards, tom lane