Thread: [HACKERS] 6.5 cvs: views doesn't survives after pg_dump (fwd)
I just checked the problem with views using current cvs and it's
stell here.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---------- Forwarded message ----------
Date: Thu, 13 May 1999 19:46:50 +0400 (MSD)
From: Oleg Bartunov <oleg@sai.msu.su>
To: pgsql-hackers@postgreSQL.org
Subject: [HACKERS] 6.5 cvs: views doesn't survives after pg_dump
After dumping (by pg_dump) and restoring views becomes a tables
Here is a simple scenario:
1. createdb tview
2. create table t1 (a int4, b int4); create view v1 as select a from t1;
3. pg_dump -z tview > tview.dump
4. destroydb tview
createdb tview
5. psql -e tview < tview.dump
............................
QUERY: COPY "t1" FROM stdin;
CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1";
QUERY: CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1";
ERROR: parser: parse error at or near "do"
EOF
6. psql tview
tview=> \dt
Database = tview+------------------+----------------------------------+----------+| Owner |
Relation | Type |+------------------+----------------------------------+----------+| megera |
t1 | table || megera | v1 | table
|+------------------+----------------------------------+----------+
tview=>
view t1 now becomes table v1 !
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> > After dumping (by pg_dump) and restoring views becomes a tables > The problem is that views are dumped with anm extraneous "WHERE" > ............................ > QUERY: COPY "t1" FROM stdin; > CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1"; > QUERY: CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1"; ...................................................++++++ > ERROR: parser: parse error at or near "do" > EOF Which causes this error and the rule (View) is not Created. I don't know how the where clause gets in there but if you edit the dump before restoring all is OK. Keith.
Jan,
Have you any ideas on this?
We get a rule output by pg_dump like :-
CREATE RULE "_RETsongs" AS ON SELECT TO "songs" WHERE DO INSTEAD SELECT "t"."artist", "t"."song", "t"."trackno",
"d"."cdname" FROM "disks" "d", "tracks" "t" WHERE "d"."diskid" = "t"."diskid";
from a view defined like so:-
CREATE VIEW songs AS SELECT t.artist, t.song, t.trackno, d.cdname FROM disks d, tracks t WHERE d.diskid = t.diskid;
Note the WHERE keyword in line 3 of the rule define.
>From "./src/backend/utils/adt/ruleutils.c" line 662 of 1814
/* If the rule has an event qualification, add it */ if (ev_qual == NULL) ev_qual = "";
if (strlen(ev_qual) > 0) { Node *qual; Query *query; QryHier
qh;
.
. strcat(buf, " WHERE "); strcat(buf, get_rule_expr(&qh, 0, qual, TRUE)); }
strcat(buf, " DO ");
/* The INSTEAD keyword (if so) */ if (is_instead) strcat(buf, "INSTEAD ");
We put the WHERE in if strlen(ev_qual) > 0
I've not yet followed this back any further.
Keith.
------------ Begin Forwarded Message -------------
X-Authentication-Warning: hub.org: majordom set sender to
owner-pgsql-hackers@postgreSQL.org using -f
Date: Fri, 21 May 1999 22:34:50 +0100 (BST)
From: Keith Parks <emkxp01@mtcc.demon.co.uk>
Subject: Re: [HACKERS] 6.5 cvs: views doesn't survives after pg_dump (fwd)
To: pgsql-hackers@postgreSQL.org, oleg@sai.msu.su
MIME-Version: 1.0
Content-MD5: 34XqWKKsmVlyonlE1gsMzw==
Oleg Bartunov < oleg@sai.msu.su>
> After dumping (by pg_dump) and restoring views becomes a tables
>
The problem is that views are dumped with anm extraneous "WHERE"
> ............................
> QUERY: COPY "t1" FROM stdin;
> CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM
"t1";
> QUERY: CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a"
FROM "t1";
...................................................++++++
> ERROR: parser: parse error at or near "do"
> EOF
Which causes this error and the rule (View) is not Created.
I don't know how the where clause gets in there but if you
edit the dump before restoring all is OK.
Keith.
------------- End Forwarded Message -------------
Re: [HACKERS] 6.5 cvs: views doesn't survives after pg_dump (fwd)
From
jwieck@debis.com (Jan Wieck)
Date:
>
> Jan,
>
> Have you any ideas on this?
Yepp
>
> We get a rule output by pg_dump like :-
>
> CREATE RULE "_RETsongs" AS
> ON SELECT TO "songs"
> WHERE DO INSTEAD
> SELECT "t"."artist", "t"."song", "t"."trackno", "d"."cdname"
> FROM "disks" "d", "tracks" "t"
> WHERE "d"."diskid" = "t"."diskid";
>
> from a view defined like so:-
>
> CREATE VIEW songs AS
> SELECT t.artist, t.song, t.trackno, d.cdname
> FROM disks d, tracks t
> WHERE d.diskid = t.diskid;
>
> Note the WHERE keyword in line 3 of the rule define.
>
> >From "./src/backend/utils/adt/ruleutils.c" line 662 of 1814
>
> /* If the rule has an event qualification, add it */
> if (ev_qual == NULL)
> ev_qual = "";
> if (strlen(ev_qual) > 0)
> {
> Node *qual;
> Query *query;
> QryHier qh;
> .
> .
That's exactly the location AFAICS. The problem was
introduced when the storage of rules changed in that the
event qualification is now stored as "<>" (the output of the
node print functions for NULL) instead of a NULL attribute.
I'll fix it soon - thanks.
>
> We put the WHERE in if strlen(ev_qual) > 0
>
> I've not yet followed this back any further.
>
> Keith.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
Looks like this was fixed in 6.5. > I just checked the problem with views using current cvs and it's > stell here. > > Regards, > > Oleg > > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > ---------- Forwarded message ---------- > Date: Thu, 13 May 1999 19:46:50 +0400 (MSD) > From: Oleg Bartunov <oleg@sai.msu.su> > To: pgsql-hackers@postgreSQL.org > Subject: [HACKERS] 6.5 cvs: views doesn't survives after pg_dump > > After dumping (by pg_dump) and restoring views becomes a tables > > Here is a simple scenario: > 1. createdb tview > > 2. create table t1 (a int4, b int4); > create view v1 as select a from t1; > > 3. pg_dump -z tview > tview.dump > 4. destroydb tview > > createdb tview > > 5. psql -e tview < tview.dump > ............................ > QUERY: COPY "t1" FROM stdin; > CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1"; > QUERY: CREATE RULE "_RETv1" AS ON SELECT TO "v1" WHERE DO INSTEAD SELECT "a" FROM "t1"; > ERROR: parser: parse error at or near "do" > EOF > > 6. psql tview > > tview=> \dt > Database = tview > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | megera | t1 | table | > | megera | v1 | table | > +------------------+----------------------------------+----------+ > > tview=> > > view t1 now becomes table v1 ! > > Regards, > > Oleg > _____________________________________________________________ > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > Sternberg Astronomical Institute, Moscow University (Russia) > Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > -- Bruce Momjian | http://www.op.net/~candle maillist@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