pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBug with dump/restore when using UNION and ORDER BY in views
6+ messages / 4 participants
[nested] [flat]
* Bug with dump/restore when using UNION and ORDER BY in views
@ 2002-08-03 21:11 Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Kristian Eide @ 2002-08-03 21:11 UTC (permalink / raw)
To: pgsql-sql
There seems to be a bug when dumping a view which is a UNION of selects, one
of which has an ORDER BY. A pair of paranthesises around the select is
missing, and this cause a subsequent restore to fail. This is quite annoying
as the backup file must be manually edited before it can be restored, and I
would really appreciate a solution in a future version of Postgre.
This problem is still present in 7.2.1.
Example:
CREATE TABLE t (id INT);
CREATE VIEW v AS (SELECT id FROM t AS t1 ORDER BY id) UNION SELECT id FROM t
AS t2;
After a dump it looks like this:
CREATE VIEW "v" as SELECT t1.id FROM t t1 ORDER BY t1.id UNION SELECT t2.id
FROM t t2;
Which is not accepted by postgre.
Regards,
Kristian
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug with dump/restore when using UNION and ORDER BY in views
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
@ 2002-08-04 03:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:24 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Christopher Kings-Lynne <chriskl@familyhealth.com.au>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-08-04 03:06 UTC (permalink / raw)
To: Kristian Eide <kreide@online.no>; +Cc: pgsql-sql
"Kristian Eide" <kreide@online.no> writes:
> There seems to be a bug when dumping a view which is a UNION of selects, one
> of which has an ORDER BY. A pair of paranthesises around the select is
> missing, and this cause a subsequent restore to fail.
Yeah. This is fixed in current sources, and I back-patched it into
the REL7_2 branch, but current plans don't seem to include a 7.2.2
release --- we'll be going straight to 7.3 beta instead.
If you're sufficiently annoyed to want to install a source patch
locally, see rev 1.89.2.1 at
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/ruleutils.c
Note this is a server-side patch; the bug is not in pg_dump.
So using a 7.3 pg_dump against your existing server won't help.
You might want to apply the back-patch just so that you can
dump when the time comes to go to 7.3...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug with dump/restore when using UNION and ORDER BY in views
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-04 05:24 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-04 05:30 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Christopher Kings-Lynne @ 2002-08-04 05:24 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Yeah. This is fixed in current sources, and I back-patched it into
> the REL7_2 branch, but current plans don't seem to include a 7.2.2
> release --- we'll be going straight to 7.3 beta instead.
Is it worth doing a 7.2.2 patch that will dump people's foreign keys as
ALTER TABLE/ADD FOREIGN KEY instead of a bunch of CREATE CONSTRAINT
TRIGGERs, so that they actually become constraints in 7.3?
Also Tom - did you check if you can CREATE CONSTRAINT TRIGGER on a dropped
column - I think I neglected to look at that in the patch I submitted
originally.
Chris
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug with dump/restore when using UNION and ORDER BY in views
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:24 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2002-08-04 05:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:54 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2002-08-04 05:30 UTC (permalink / raw)
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: pgsql-sql
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
>> Yeah. This is fixed in current sources, and I back-patched it into
>> the REL7_2 branch, but current plans don't seem to include a 7.2.2
>> release --- we'll be going straight to 7.3 beta instead.
> Is it worth doing a 7.2.2 patch that will dump people's foreign keys as
> ALTER TABLE/ADD FOREIGN KEY instead of a bunch of CREATE CONSTRAINT
> TRIGGERs, so that they actually become constraints in 7.3?
I don't think it's any easier to do that as a backpatch than as new
functionality in 7.3 pg_dump. As far as the general issue of a 7.2.2
release goes, I'm personally in favor of one --- there are several
important bugfixes in the 7.2 branch right now --- but I don't do the
gruntwork for patch releases, so I can't complain too much.
> Also Tom - did you check if you can CREATE CONSTRAINT TRIGGER on a dropped
> column - I think I neglected to look at that in the patch I submitted
> originally.
I'm pretty sure that won't get past the ATTNAME cache patches, but try
it...
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug with dump/restore when using UNION and ORDER BY in views
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:24 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-04 05:30 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
@ 2002-08-04 05:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-08-04 06:07 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Bruce Momjian @ 2002-08-04 05:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-sql
What is in the 7.2.X CVS that we would want to release?
---------------------------------------------------------------------------
Tom Lane wrote:
> "Christopher Kings-Lynne" <chriskl@familyhealth.com.au> writes:
> >> Yeah. This is fixed in current sources, and I back-patched it into
> >> the REL7_2 branch, but current plans don't seem to include a 7.2.2
> >> release --- we'll be going straight to 7.3 beta instead.
>
> > Is it worth doing a 7.2.2 patch that will dump people's foreign keys as
> > ALTER TABLE/ADD FOREIGN KEY instead of a bunch of CREATE CONSTRAINT
> > TRIGGERs, so that they actually become constraints in 7.3?
>
> I don't think it's any easier to do that as a backpatch than as new
> functionality in 7.3 pg_dump. As far as the general issue of a 7.2.2
> release goes, I'm personally in favor of one --- there are several
> important bugfixes in the 7.2 branch right now --- but I don't do the
> gruntwork for patch releases, so I can't complain too much.
>
> > Also Tom - did you check if you can CREATE CONSTRAINT TRIGGER on a dropped
> > column - I think I neglected to look at that in the patch I submitted
> > originally.
>
> I'm pretty sure that won't get past the ATTNAME cache patches, but try
> it...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
--
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Bug with dump/restore when using UNION and ORDER BY in views
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:24 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-04 05:30 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:54 ` Re: Bug with dump/restore when using UNION and ORDER BY in views Bruce Momjian <pgman@candle.pha.pa.us>
@ 2002-08-04 06:07 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2002-08-04 06:07 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; pgsql-sql
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What is in the 7.2.X CVS that we would want to release?
CVS logs show the following as post-7.2.1 changes in REL7_2_STABLE branch.
Draw your own conclusions ...
regards, tom lane
2002-06-15 14:38 tgl
* src/backend/utils/adt/ruleutils.c (REL7_2_STABLE): Back-patch 7.3
fix to fully parenthesize UNION/EXCEPT/INTERSECT queries in
ruleutils output. The previous partial parenthesization was a hack
to get around grammar restrictions that have since been fixed; and
as Sam O'Connor pointed out, there were cases where it didn't work.
2002-06-07 17:53 momjian
* contrib/intarray/: _int.c, _int.sql.in (REL7_2_STABLE): Please
apply attached patch to contrib/intarray (7.2, 7.3).
Fixed bug with '=' operator for gist__int_ops and
define '=' operator for gist__intbig_ops opclass.
Now '=' operator is consistent with standard 'array' type.
Thanks Achilleus Mantzios for bug report and suggestion.
Oleg Bartunov
2002-05-28 11:40 tgl
* src/bin/pg_dump/pg_dump.c (REL7_2_STABLE): Repair incorrect
dumping of user-defined aggregate with null initcond. (Already
fixed in current, but need a patch for 7.2.2.)
2002-05-28 11:25 tgl
* contrib/rtree_gist/rtree_gist.c (REL7_2_STABLE): Fix a bug with
building rtree_gist indexes. Patch from Teodor Sigaev.
2002-05-28 11:22 tgl
* src/backend/access/gist/gist.c (REL7_2_STABLE): Repair error with
not adjusting active scans properly after gistSplit. Patch from
Teodor Sigaev.
2002-05-22 13:29 tgl
* src/backend/utils/cache/relcache.c (REL7_2_STABLE): Make
RelationForgetRelation error out if the relcache entry has nonzero
reference count. This avoids leaving dangling pointers around, as
in recent bug report against sequences (bug# 671).
2002-05-21 14:50 tgl
* src/pl/plpgsql/src/gram.y (REL7_2_STABLE): Repair OPEN
cursor(args), which I broke on 11/29/01 with a change to be smarter
about parentheses in read_sql_construct(). Sigh.
2002-05-14 14:16 tgl
* src/backend/utils/adt/geo_ops.c (REL7_2_STABLE): Remove
unnecessary pfree's in geometric operators. At least one of these
is actively dangerous, per bug report from Ewald Geschwinde
14-May-02, and several of the rest look suspicious to me. Since
there is no longer any significant value in retail pfree's in these
functions, just get rid of all of them for safety's sake.
2002-05-13 22:08 ishii
* src/bin/: pg_dump/pg_dump.c, psql/startup.c (REL7_2_STABLE): Fix
bug in pg_dump and psql (to reproduce the bug, just try pg_dump
--nonexistingoption).
per report from sugita@sra.co.jp on Thu, 09 May 2002 11:57:51 +0900
(JST) at pgsql-patches list.
Illegal long options to pg_dump makes core on some systems, since
it lacks the last null sentinel of struct option array.
Attached is a patch made by Mr. Ishida Akio <iakio@pjam.jpweb.net>.
2002-05-05 13:38 tgl
* src/pl/plpgsql/src/pl_funcs.c (REL7_2_STABLE):
plpgsql_dstring_append was broken for long strings.
2002-04-30 21:27 inoue
* src/backend/access/heap/heapam.c (REL7_2_STABLE): Change
heap_get_latest_tid() so that a transaction can see changes made by
the transaction itself.
2002-04-08 02:21 ishii
* src/interfaces/libpq/fe-exec.c (REL7_2_STABLE): A backport patch.
Fix PQescapeBytea/PQunescapeBytea so that they handle bytes >
0x7f. This is necessary for mulibyte character sequences. See
"[HACKERS] PQescapeBytea is not multibyte aware" thread posted
around 2002/04/05 for more details.
2002-04-02 00:12 tgl
* src/backend/commands/vacuum.c (REL7_2_STABLE): Fix CLOG
truncation code to not do the Wrong Thing when there are already
wrapped-around databases. The unvacuumed databases might be fine,
or they might not, but things will definitely not be fine if we
remove the wrong CLOG segments. Per trouble report from Gary
Wolfe, 1-Apr-2002.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2002-08-04 06:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-03 21:11 Bug with dump/restore when using UNION and ORDER BY in views Kristian Eide <kreide@online.no>
2002-08-04 03:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:24 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2002-08-04 05:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-08-04 05:54 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-08-04 06:07 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox