Thread: create rule changes table to view ?
I just began to learn rules with 6.5 and notice:
test=> \dt
Database = test+------------------+----------------------------------+----------+| Owner |
Relation | Type |+------------------+----------------------------------+----------+| megera |
access_log | table || megera | hits | table || megera
| junk_qwerty | table
|+------------------+----------------------------------+----------+
test=> create rule log_hits as on update to hits do instead insert into hits values ( NEW.msg_id, 1);
CREATE
test=> \dt
Database = test+------------------+----------------------------------+----------+| Owner |
Relation | Type |+------------------+----------------------------------+----------+| megera |
access_log | table || megera | hits | view? || megera
| junk_qwerty | table
|+------------------+----------------------------------+----------+
Table hits now becomes view ?
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
Can someone comment on this? > I just began to learn rules with 6.5 and notice: > test=> \dt > Database = test > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | megera | access_log | table | > | megera | hits | table | > | megera | junk_qwerty | table | > +------------------+----------------------------------+----------+ > > test=> create rule log_hits as on update to hits do instead insert into hits values ( NEW.msg_id, 1); > CREATE > test=> \dt > Database = test > +------------------+----------------------------------+----------+ > | Owner | Relation | Type | > +------------------+----------------------------------+----------+ > | megera | access_log | table | > | megera | hits | view? | > | megera | junk_qwerty | table | > +------------------+----------------------------------+----------+ > > Table hits now becomes view ? > > > 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
I have noticed and lived with this problem for quite a while. There's nothing in pg_class that tells a table from a view, they're both "relations". Since a view is implemented as in effect an empty table with on select rules on it, psql thinks every table with a rule on it is a view. This is just the output, nothing on the table changes. A fix would be to display both tables and views as "relation". As far as I know there is now 100% deterministic way to tell a table from a view. I think one fine day Jan is going to change that but for now we don't have to worry about it. Peter On Sep 23, Bruce Momjian mentioned: > Can someone comment on this? > > > I just began to learn rules with 6.5 and notice: > > test=> \dt > > Database = test > > +------------------+----------------------------------+----------+ > > | Owner | Relation | Type | > > +------------------+----------------------------------+----------+ > > | megera | access_log | table | > > | megera | hits | table | > > | megera | junk_qwerty | table | > > +------------------+----------------------------------+----------+ > > > > test=> create rule log_hits as on update to hits do instead insert into hits values ( NEW.msg_id, 1); > > CREATE > > test=> \dt > > Database = test > > +------------------+----------------------------------+----------+ > > | Owner | Relation | Type | > > +------------------+----------------------------------+----------+ > > | megera | access_log | table | > > | megera | hits | view? | > > | megera | junk_qwerty | table | > > +------------------+----------------------------------+----------+ > > > > Table hits now becomes view ? > > > > > > 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 > > > > > > > > > -- Peter Eisentraut - peter_e@gmx.net http://yi.org/peter-e
Bruce Momjian ha scritto:
> Can someone comment on this?
This an old question.
psql suppose that table "test" is a view because it checks for pg_class.relhasrules and it prints "view?"
if the value is TRUE and the value is if there's a rule for the table.
The only way to distinguish a table from a view is the pg_get_viewdef.
Some time ago I suggested to use pg_get_viewdef('tablename') to check for views
to print "view or table" instead of "view?".
I made a patch to my psql and it now recognize views perfectly and I can display
only tables using \d and/or only views using \v
Comments.
>
>
> > I just began to learn rules with 6.5 and notice:
> > test=> \dt
> > Database = test
> > +------------------+----------------------------------+----------+
> > | Owner | Relation | Type |
> > +------------------+----------------------------------+----------+
> > | megera | access_log | table |
> > | megera | hits | table |
> > | megera | junk_qwerty | table |
> > +------------------+----------------------------------+----------+
> >
> > test=> create rule log_hits as on update to hits do instead insert into hits values ( NEW.msg_id, 1);
> > CREATE
> > test=> \dt
> > Database = test
> > +------------------+----------------------------------+----------+
> > | Owner | Relation | Type |
> > +------------------+----------------------------------+----------+
> > | megera | access_log | table |
> > | megera | hits | view? |
> > | megera | junk_qwerty | table |
> > +------------------+----------------------------------+----------+
> >
> > Table hits now becomes view ?
> >
> >
> > 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
>
> ************
Peter Eisentraut wrote:
> A fix would be to display both tables and views as "relation". As far as I
> know there is now 100% deterministic way to tell a table from a view. I
> think one fine day Jan is going to change that but for now we don't have
> to worry about it.
There is currently a 100% failsafe way.
Actually, rules ON SELECT are totally restricted to rules
that are INSTEAD, return a targetlist that's exactly the
relations (views) schema and there could only be one single-
action rule on the SELECT event. These checks are performed
during CREATE RULE.
In short: If there's a rule ON SELECT, then the relation MUST
BE A VIEW.
The detail psql is doing wrong is that it treats any rule as
if it is indicating a view. It must look for SELECT rules
only.
And I'm not planning to take out this restriction again.
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) #
> I have noticed and lived with this problem for quite a while. > > There's nothing in pg_class that tells a table from a view, they're both > "relations". Since a view is implemented as in effect an empty table with > on select rules on it, psql thinks every table with a rule on it is a > view. This is just the output, nothing on the table changes. > > A fix would be to display both tables and views as "relation". As far as I > know there is now 100% deterministic way to tell a table from a view. I > think one fine day Jan is going to change that but for now we don't have > to worry about it. OK. Nothing added to TODO list. -- 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