Thread: Bug with dump/restore when using UNION and ORDER BY in views
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
"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
Re: Bug with dump/restore when using UNION and ORDER BY in views
From
"Christopher Kings-Lynne"
Date:
> 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
"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
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, Pennsylvania19026
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.3fix to fully parenthesize UNION/EXCEPT/INTERSECT
queriesinruleutils output. The previous partial parenthesization was a hackto get around grammar restrictions that
havesince been fixed; andas 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): Pleaseapply 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 '='
operatoris 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 incorrectdumping of user-defined aggregate with null initcond.
(Alreadyfixedin 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 withbuilding rtree_gist indexes. Patch from Teodor
Sigaev.
2002-05-28 11:22 tgl
* src/backend/access/gist/gist.c (REL7_2_STABLE): Repair error withnot adjusting active scans properly after gistSplit.
Patch fromTeodor Sigaev.
2002-05-22 13:29 tgl
* src/backend/utils/cache/relcache.c (REL7_2_STABLE): MakeRelationForgetRelation error out if the relcache entry has
nonzeroreferencecount. This avoids leaving dangling pointers around, asin recent bug report against sequences (bug#
671).
2002-05-21 14:50 tgl
* src/pl/plpgsql/src/gram.y (REL7_2_STABLE): Repair OPENcursor(args), which I broke on 11/29/01 with a change to be
smarteraboutparentheses in read_sql_construct(). Sigh.
2002-05-14 14:16 tgl
* src/backend/utils/adt/geo_ops.c (REL7_2_STABLE): Removeunnecessary pfree's in geometric operators. At least one of
theseisactively dangerous, per bug report from Ewald Geschwinde14-May-02, and several of the rest look suspicious to
me. Sincethere is no longer any significant value in retail pfree's in thesefunctions, just get rid of all of them for
safety'ssake.
2002-05-13 22:08 ishii
* src/bin/: pg_dump/pg_dump.c, psql/startup.c (REL7_2_STABLE): Fixbug in pg_dump and psql (to reproduce the bug, just
trypg_dump--nonexistingoption).per report from sugita@sra.co.jp on Thu, 09 May 2002 11:57:51 +0900(JST) at
pgsql-patcheslist.Illegal long options to pg_dump makes core on some systems, sinceit lacks the last null sentinel of
structoption 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): Changeheap_get_latest_tid() so that a transaction can see changes
madebythe 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
handlebytes >0x7f. This is necessary for mulibyte character sequences. See"[HACKERS] PQescapeBytea is not multibyte
aware"thread postedaround 2002/04/05 for more details.
2002-04-02 00:12 tgl
* src/backend/commands/vacuum.c (REL7_2_STABLE): Fix CLOGtruncation code to not do the Wrong Thing when there are
alreadywrapped-arounddatabases. The unvacuumed databases might be fine,or they might not, but things will definitely
notbe fine if weremove the wrong CLOG segments. Per trouble report from GaryWolfe, 1-Apr-2002.