Thread: bug with dump sql to recreate view

bug with dump sql to recreate view

From
"Laura Moloney"
Date:
I have 3 servers running postgres. Two are version 7.1.3 and the other is
7.2.3. I had run pgdump on a database on the 7.1.3 system to create a sql
text file of the structure and data. I then ran that sql text file onto  the other
7.1.3 system to create the database there. All SQL ran fine except for one
statement which creates a view.

The sql that created the original view was:
CREATE VIEW INCUNRESOLVEDV1 AS
    SELECT DISTINCT TRACKID FROM INCIDENT
        WHERE STATUS = 'o'
    EXCEPT
    SELECT DISTINCT TRACKID FROM INCIDENT_DETAIL
        WHERE LOWER(RESOLVEDYN) = 'y'
    ;

The sql that was generated from pgdump was:
CREATE VIEW "incunresolvedv1" as
    SELECT DISTINCT incident.trackid FROM incident
        WHERE (incident.status = 'o'::"varchar")
        ORDER BY incident.trackid
    EXCEPT
    SELECT DISTINCT incident_detail.trackid FROM incident_detail
        WHERE (lower((incident_detail.resolvedyn)::text) = 'y'::text)
        ORDER BY incident_detail.trackid;

The error I got when I ran the pgdump sql was:
PostgreSQL said: ERROR: parser: parse error at or near "EXCEPT"
Your query:
CREATE VIEW "incunresolvedv1" as
SELECT DISTINCT incident.trackid FROM incident
WHERE (incident.status = 'o'::"varchar")
ORDER BY incident.trackid
EXCEPT
SELECT DISTINCT incident_detail.trackid FROM incident_detail
WHERE (lower((incident_detail.resolvedyn)::text) = 'y'::text)
ORDER BY incident_detail.trackid

I found that if I removed the order by clauses, then the sql would execute
correctly.

I also tried to run the pgdump sql on our 7.2.3 server to see if it had been
resolved in a newer release, but the same error occurred.

Hope my explanation was clear enough. I can certainly get around it my
running the original SQL, but I thought you might be interested in it if in fact
it was a bug.

Thanks, Laura
Laura Moloney
Theradex
CN5257
Princeton, NJ 08543
(609) 799-7580 (Phone)
(609) 799-4148 (Fax)
LMOLONEY@THERADEX.COM (email)

Re: bug with dump sql to recreate view

From
Stephan Szabo
Date:
On Mon, 7 Apr 2003, Laura Moloney wrote:

> The sql that created the original view was:
> CREATE VIEW INCUNRESOLVEDV1 AS
>     SELECT DISTINCT TRACKID FROM INCIDENT
>         WHERE STATUS = 'o'
>     EXCEPT
>     SELECT DISTINCT TRACKID FROM INCIDENT_DETAIL
>         WHERE LOWER(RESOLVEDYN) = 'y'
>     ;
>
> The sql that was generated from pgdump was:
> CREATE VIEW "incunresolvedv1" as
>     SELECT DISTINCT incident.trackid FROM incident
>         WHERE (incident.status = 'o'::"varchar")
>         ORDER BY incident.trackid
>     EXCEPT
>     SELECT DISTINCT incident_detail.trackid FROM incident_detail
>         WHERE (lower((incident_detail.resolvedyn)::text) = 'y'::text)
>         ORDER BY incident_detail.trackid;
>
> I found that if I removed the order by clauses, then the sql would execute
> correctly.
>
> I also tried to run the pgdump sql on our 7.2.3 server to see if it had been
> resolved in a newer release, but the same error occurred.
>
> Hope my explanation was clear enough. I can certainly get around it my
> running the original SQL, but I thought you might be interested in it if in fact
> it was a bug.

It seems to have been a bug in pg_dump on 7.1.x that dumped an invalid
query.  Current pg_dump dumps the two selects with parentheses around them
which is loadable.