Thread: Bug(?) in pg_get_ruledef()
Hi,
I try dump (via pg_dump) my database, but if I write dumped data back to DB,
views (as select on ingerit table) not work.
The bug is in routine pg_get_ruledef(pg_rewrite.rulename), which _not_
discern between select on standard table and select on inderit table.
Select on inherit table is "SELECT * FROM table*", but pg_get_ruledef()
return this view definition without asterisk: "SELECT * FROM table".
See example:
-----------
abil=> create table mother_tab (aaa int);
CREATE
abil=> create table son () inherits(mother_tab);
CREATE
abil=> create view v_mother as select * from mother_tab*;
CREATE
abil=> insert into son values (111);
INSERT 4946878 1
abil=> select * from v_mother;
aaa
---
111
(1 row)
abil=> SELECT pg_get_ruledef(pg_rewrite.rulename) FROM pg_rewrite WHERE
rulename ='_RETv_mother';
CREATE RULE "_RETv_mother" AS ON SELECT TO "v_mother" DO INSTEAD SELECT
"mother_tab"."aaa" FROM "mother_tab";
(1 row) ^^^^^^^^^^^^ right is "mother_tab*"
-----
Is it but?
(It is probably fatal bug if somebody backup batabase via pg_dump and views
from dump is unavailable.)
Karel Z.
------------------------------------------------------------------------------
<zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
------------------------------------------------------------------------------
...and cathedral dilapidate
Hi,
my first question was without answer, I try it again:
IMHO is a problem with the routine pg_get_ruledef(), this routine is used in
any query in the pg_dump for view dumping. But the pg_get_ruledef() not discern
contrast between view rules defined as 'select * table' and rules defined as
'select * table*' (the query should be run over all classes in the
inheritance hierarchy).
Is it a bug or a limitation? (The pg_dump is unworkable for a views tables
runnig over the inheritance hierarchy?) Problem example:--------------- abil=> create table mother_tab (aaa
int);CREATEabil=>create table son () inherits(mother_tab);CREATEabil=> create view v_mother as select * from
mother_tab*;CREATEabil=>insert into son values (111);INSERT 4946878 1abil=> select * from v_mother;aaa---111(1
row)abil=>SELECT pg_get_ruledef(pg_rewrite.rulename) FROM pg_rewrite WHERErulename ='_RETv_mother';
CREATE RULE "_RETv_mother" AS ON SELECT TO "v_mother" DO INSTEAD SELECT"mother_tab"."aaa" FROM "mother_tab";(1 row)
^^^^^^^^^^^^ but right is "mother_tab*"
---Any comments? (Please)
Karel Z.
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in pg_get_ruledef())
From
wieck@debis.com (Jan Wieck)
Date:
>
>
>
> Hi,
>
> my first question was without answer, I try it again:
>
> IMHO is a problem with the routine pg_get_ruledef(), this routine is used in
> any query in the pg_dump for view dumping. But the pg_get_ruledef() not discern
> contrast between view rules defined as 'select * table' and rules defined as
> 'select * table*' (the query should be run over all classes in the
> inheritance hierarchy).
>
> Is it a bug or a limitation? (The pg_dump is unworkable for a views tables
> runnig over the inheritance hierarchy?)
Surely a bug!
Unfortunately I'm too busy at the moment to tackle it down.
The location where the inheritance is ignored is
src/backend/utils/adt/ruleutils.c
or a similar name - you'll find that file - it's the source
where that damned pg_get_ruledef() is defined. If you can
loacate and fix the problem therein depends on how familiar
you are with interpreting querytrees. At some place the table
name is printed, but I don't know if it is possible to tell
from the data at hand if it is an inheritance. Maybe another
catalog lookup is required there.
Oh man, this little 'piece of magic' (as someone else called
it) was only intended to demonstrate that it is POSSIBLE AT
ALL to translate a querytree back into it's original SQL
statement. Why the hell did I assist in making use of it in
pg_dump?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in pg_get_ruledef())
From
Karel Zak - Zakkr
Date:
On Fri, 29 Oct 1999, Jan Wieck wrote: > > Is it a bug or a limitation? (The pg_dump is unworkable for a views tables > > runnig over the inheritance hierarchy?) > > Surely a bug! > > Unfortunately I'm too busy at the moment to tackle it down. > The location where the inheritance is ignored is > > src/backend/utils/adt/ruleutils.c > > or a similar name - you'll find that file - it's the source > where that damned pg_get_ruledef() is defined. If you can > loacate and fix the problem therein depends on how familiar > you are with interpreting querytrees. At some place the table > name is printed, but I don't know if it is possible to tell > from the data at hand if it is an inheritance. Maybe another > catalog lookup is required there. Well, I try see to the source and fix it. > Oh man, this little 'piece of magic' (as someone else called But, more good details make very good PosgreSQL :-)) > it) was only intended to demonstrate that it is POSSIBLE AT > ALL to translate a querytree back into it's original SQL > statement. Why the hell did I assist in making use of it in > pg_dump? If exist handle, why not open the door? Pg_dump is backup util which allow dump _all_ definition and data, we need it right if we allow it. (I use pg_dump only for data backup.) Thank Jan! Karel Z.
Zakkr <zakkr@zf.jcu.cz> writes:
> But the pg_get_ruledef() not discern contrast between view rules
> defined as 'select * table' and rules defined as 'select * table*'
> (the query should be run over all classes in the inheritance
> hierarchy).
> Is it a bug or a limitation?
Sounds like a bug to me too. The fix is probably just a small addition
of code, but I haven't had time to look into it.
regards, tom lane
wieck@debis.com (Jan Wieck) writes:
> Oh man, this little 'piece of magic' (as someone else called
> it) was only intended to demonstrate that it is POSSIBLE AT
> ALL to translate a querytree back into it's original SQL
> statement. Why the hell did I assist in making use of it in
> pg_dump?
Because it solved a necessary problem. Don't beat yourself up about
it...
regards, tom lane
On Fri, 29 Oct 1999, Tom Lane wrote:
> Zakkr <zakkr@zf.jcu.cz> writes:
> > But the pg_get_ruledef() not discern contrast between view rules
> > defined as 'select * table' and rules defined as 'select * table*'
> > (the query should be run over all classes in the inheritance
> > hierarchy).
>
> > Is it a bug or a limitation?
>
> Sounds like a bug to me too. The fix is probably just a small addition
> of code, but I haven't had time to look into it.
>
> regards, tom lane
Yes, I fix this bug. Here is my patch (for /src/backend/utils/adt/ruleutils)
for it:
*** ruleutils.c.org Mon Sep 6 00:55:28 1999
--- ruleutils.c Sun Sep 31 13:37:42 1999
***************
*** 968,971 ****
--- 968,973 ---- strcat(buf, "\""); strcat(buf, rte->relname);
+ if (rte->inh)
+ strcat(buf, "*"); strcat(buf, "\""); if (strcmp(rte->relname,
rte->refname)!= 0)
***************
*** 973,976 ****
--- 975,980 ---- strcat(buf, " \""); strcat(buf, rte->refname);
+ if (rte->inh)
+ strcat(buf, "*"); strcat(buf, "\""); }
Add we (Jan or Tom) this code to PostgreSQL source main? (Pease).
Karel
------------------------------------------------------------------------------
Karel Zak <zakkr@zf.jcu.cz> http://home.zf.jcu.cz/~zakkr/
Kim Project: http://home.zf.jcu.cz/~zakkr/kim/ (process manager)
FTP: ftp://ftp2.zf.jcu.cz/users/zakkr/ (C/ncurses/PgSQL)
------------------------------------------------------------------------------
Karel Zak - Zakkr <zakkr@zf.jcu.cz> writes:
> *** ruleutils.c.org Mon Sep 6 00:55:28 1999
> --- ruleutils.c Sun Sep 31 13:37:42 1999
> ***************
> *** 968,971 ****
> --- 968,973 ----
> strcat(buf, "\"");
> strcat(buf, rte->relname);
> + if (rte->inh)
> + strcat(buf, "*");
> strcat(buf, "\"");
> if (strcmp(rte->relname, rte->refname) != 0)
> ***************
> *** 973,976 ****
> --- 975,980 ----
> strcat(buf, " \"");
> strcat(buf, rte->refname);
> + if (rte->inh)
> + strcat(buf, "*");
> strcat(buf, "\"");
> }
> Add we (Jan or Tom) this code to PostgreSQL source main? (Pease).
That looks about like the right thing to do, but I wonder whether the
"*" doesn't need to go *outside* the quote marks around the table name?
Seems like it would be taken as a name character if inside...
regards, tom lane
On Sun, 31 Oct 1999, Tom Lane wrote:
>
> That looks about like the right thing to do, but I wonder whether the
> "*" doesn't need to go *outside* the quote marks around the table name?
> Seems like it would be taken as a name character if inside...
grrr! - it is (my) novice's idiocy...
Sorry Tom, I forget that between the quote is the table name.. next time I
first test & check my patches :-) Now is it good? (I test it this time.)
Karel
*** ruleutils.c.org Mon Sep 6 00:55:28 1999
--- ruleutils.c Mon Nov 1 09:26:03 1999
***************
*** 969,972 ****
--- 969,974 ---- strcat(buf, rte->relname); strcat(buf, "\"");
+ if (rte->inh)
+ strcat(buf, "*"); if (strcmp(rte->relname, rte->refname) != 0) {
***************
*** 974,977 ****
--- 976,981 ---- strcat(buf, rte->refname); strcat(buf, "\"");
+ if (rte->inh)
+ strcat(buf, "*"); } }
Karel Zak - Zakkr <zakkr@zf.jcu.cz> writes:
> *** ruleutils.c.org Mon Sep 6 00:55:28 1999
> --- ruleutils.c Mon Nov 1 09:26:03 1999
> ***************
> *** 969,972 ****
> --- 969,974 ----
> strcat(buf, rte->relname);
> strcat(buf, "\"");
> + if (rte->inh)
> + strcat(buf, "*");
> if (strcmp(rte->relname, rte->refname) != 0)
> {
I applied this part --- I don't think adding a second '*' after the
refname is correct.
regards, tom lane