From: | Sam O'Connor <sam(at)panviva(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | pg_dump fails for views with UNION and SELECT DISTINCT |
Date: | 2002-06-14 01:44:18 |
Message-ID: | 1024019058.20981.29.camel@moby |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
If I create a view like this:
CREATE VIEW v AS
SELECT i FROM a
UNION
SELECT DISTINCT i FROM b
It functions as expected but it causes pg_dump to produce
bad output. "ORDER BY b.i" is added to the view definition.
On restore this causes:
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
Full setup and steps taken are below.
Regards,
Sam
My setup:
Debian woody.
Linux 2.4.18.
PostgreSQL 7.2.1
./configure --with-maxbackends=64 --with-gnu-ld --enable-odbc
--enable-syslog
What I did:
# cat > test.sql << EOF
SELECT version();
CREATE TABLE foo (i int);
CREATE TABLE bar (i int);
CREATE VIEW this_is_ok
AS SELECT i
FROM foo
UNION
SELECT i FROM bar;
CREATE VIEW this_causes_a_broken_dump
AS SELECT i
FROM foo
UNION
SELECT DISTINCT i FROM bar;
CREATE VIEW this_causes_a_broken_dump_too
AS SELECT i
FROM foo
UNION
SELECT DISTINCT i FROM bar x;
EOF
# createdb test
CREATE DATABASE
# psql test < test.sql
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
CREATE
CREATE
CREATE
CREATE
CREATE
# pg_dump test
-- snip --
CREATE VIEW "this_is_ok" as SELECT foo.i FROM foo UNION SELECT bar.i
FROM bar;
-- snip --
CREATE VIEW "this_causes_a_broken_dump" as SELECT foo.i FROM foo UNION
SELECT DISTINCT bar.i FROM bar ORDER BY bar.i;
-- snip --
CREATE VIEW "this_causes_a_broken_dump_too" as SELECT foo.i FROM foo
UNION SELECT DISTINCT x.i FROM bar x ORDER BY x.i;
-- snip --
# createdb test2
CREATE DATABASE
# pg_dump test | psql test2
CREATE
CREATE
CREATE
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
ERROR: Relation "x" does not exist
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2002-06-14 04:42:52 | Re: PgLargeObject bug |
Previous Message | Stephan Szabo | 2002-06-13 21:02:38 | Re: Problems with select chaining using INTERSECT |