[HACKERS] 6.5 cvs: views doesn't survives after pg_dump (fwd)

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 -------------

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, Pennsylvania 19026