

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


Wed, 27 Oct 1999 12:37:44 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
Bug(?) in pg_get_ruledef()


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);
 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)
                          ^^^^^^^^^^^^
 			but right is "mother_tab*"

---
 
 Any comments? (Please)

							Karel Z.


Fri, 29 Oct 1999 11:23:54 +0200 (CEST)
Zakkr <zakkr@zf.jcu.cz>
view vs. inheritance hierarchy (was: Bug(?) in pg_get_ruledef())
>
>
>
> 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) #



Fri, 29 Oct 1999 14:55:24 +0200 (MET DST)
wieck@debis.com (Jan Wieck)
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in
	pg_get_ruledef())


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.		



Fri, 29 Oct 1999 15:23:21 +0200 (CEST)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in
	pg_get_ruledef())
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

Fri, 29 Oct 1999 09:47:44 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in
	pg_get_ruledef())
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

Fri, 29 Oct 1999 10:25:28 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] view vs. inheritance hierarchy (was: Bug(?) in
	pg_get_ruledef())




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


Sun, 31 Oct 1999 15:26:51 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Patch -  Re: [HACKERS] view vs. inheritance hierarchy  
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

Sun, 31 Oct 1999 11:59:58 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Patch - Re: [HACKERS] view vs. inheritance hierarchy 



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, "*");
  				}
  			}


Mon, 1 Nov 1999 10:09:51 +0100 (CET)
Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Re: Patch - Re: [HACKERS] view vs. inheritance hierarchy 
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

Mon, 01 Nov 1999 09:52:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Patch - Re: [HACKERS] view vs. inheritance hierarchy 