new relkind for view

Applied.

NB: I will be on vacation from 1-Sep to 5-Sep

On the patches list I sent the following:
-----------------------------------------

This patch implements a different "relkind"
for views. Views are now have a "relkind" of
RELKIND_VIEW instead of RELKIND_RELATION.

Also, views no longer have actual heap storage
files.

The follow changes were made

1. CREATE VIEW sets the new relkind

2. The executor complains if a DELETE or
INSERT references a view.

3. DROP RULE complains if an attempt is made
to delete a view SELECT rule.

4. CREATE RULE "_RETmytable" AS ON SELECT TO mytable DO INSTEAD ...
1. checks to make sure mytable is empty.
2. sets the relkind to RELKIND_VIEW.
3. deletes the heap storage files.

5. LOCK myview is not allowed. :)

6. the regression test type_sanity was changed to
account for the new relkind value.

7. CREATE INDEX ON myview ... is not allowed.

8. VACUUM myview is not allowed.
VACUUM automatically skips views when do the entire
database.

9. TRUNCATE myview is not allowed.

THINGS LEFT TO THINK ABOUT

o pg_views

o pg_dump

o pgsql (\d \dv)

o Do we really want to be able to inherit from views?

o Is 'DROP TABLE myview' OK?

--
Mark Hollomon
mhh@mindspring.com

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

At 00:49 12/09/00 -0400, Bruce Momjian wrote:

o pg_dump

I've got to fix up a few things in pg_dump, so I'll try to do this as well...

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

2. The executor complains if a DELETE or
INSERT references a view.

I think this is for new todo items:
create insert, update and delete rules for simple one table views
change elog for complex view ins|upd|del to "cannot {ins|upd|del}
[into|from] complex view without an on {ins|upd|del} rule"
add the functionality for "with check option" clause of create view

Andreas

In a continuing effort to make pg_dump produce valid SQL where possible, I
would like to move away from the 'Create Table'/'Create Rule' method of
defining views, and actually dump the 'Create View' command.

This seems quite do-able, but before I start I thought I would ask if there
were any reasons people could think of for not doing this?

The approach will be:

- when getting table info, also call pg_get_viewdef (which returns 'not a
view' for non-view relations).

- When dumping rules, ignore all 'view rules'.

- Dump the 'Create View' statement in oid order as per normal.

It would be really nice if there was a simple way of detecting view rules
that was analagous to relkind. Is there a reason why this has not been
done? Has it been done?

Maybe the code that checks the rule name to see if it is a view could be
put in the backend?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner <pjw@rhyme.com.au> writes:

- when getting table info, also call pg_get_viewdef (which returns 'not a
view' for non-view relations).

Huh? Just use the relkind to detect views.

regards, tom lane

At 11:45 14/09/00 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

- when getting table info, also call pg_get_viewdef (which returns 'not a
view' for non-view relations).

Huh? Just use the relkind to detect views.

Sorry; what the above means is that I will get the views when I get the
tables, and call 'pg_get_viewdef' in all cases. I will check relkind when I
come to dump the 'table'.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

On Tue, Sep 12, 2000 at 09:42:41AM +0200, Zeugswetter Andreas SB wrote:

add the functionality for "with check option" clause of create view

I'm not familiar with this. What does it do?

--
Mark Hollomon
mhh@mindspring.com

add the functionality for "with check option" clause of

create view

I'm not familiar with this. What does it do?

It checks on view insert or update, that the resulting tuple will still
be seen through this view (it satisfies the view's where restriction).
If not, the insert or update is not allowed.

Andreas

Added to TODO>

[ Charset ISO-8859-1 unsupported, converting... ]

2. The executor complains if a DELETE or
INSERT references a view.

I think this is for new todo items:
create insert, update and delete rules for simple one table views
change elog for complex view ins|upd|del to "cannot {ins|upd|del}
[into|from] complex view without an on {ins|upd|del} rule"
add the functionality for "with check option" clause of create view

Andreas

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO>

I think this is for new todo items:
create insert, update and delete rules for simple one table views
change elog for complex view ins|upd|del to "cannot {ins|upd|del}
[into|from] complex view without an on {ins|upd|del} rule"
add the functionality for "with check option" clause of create view

The second of these three items is done already (in the rewriter,
not the executor):

regression=# create view vv1 as select * from int4_tbl;
CREATE
regression=# insert into vv1 values (33);
ERROR: Cannot insert into a view without an appropriate rule

regards, tom lane

TODO updated.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Added to TODO>

I think this is for new todo items:
create insert, update and delete rules for simple one table views
change elog for complex view ins|upd|del to "cannot {ins|upd|del}
[into|from] complex view without an on {ins|upd|del} rule"
add the functionality for "with check option" clause of create view

The second of these three items is done already (in the rewriter,
not the executor):

regression=# create view vv1 as select * from int4_tbl;
CREATE
regression=# insert into vv1 values (33);
ERROR: Cannot insert into a view without an appropriate rule

regards, tom lane

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@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