Re: Can you make a simple view non-updatable?

[Ryan Murphy]

On Fri, Jun 8, 2018 at 8:27 AM, Adrian Klaver 
wrote:

>
> Using INSTEAD OF trigger?:
>

Yep, that's one way.

Re: Can you make a simple view non-updatable?

[Ryan Murphy]

> maybe it is time to overhaul the security concept.
>

I could see how I could revoke permissions from, say, all users that aren't
superusers to INSERT or UPDATE certain views.  However, if possible it
would be nice to get an error message about the VIEW not being updatable,
rather than a user access error, which could be misleading.

When I try to insert into a non-updatable VIEWs, I get this message:

ERROR:  cannot insert into view "test_view"
DETAIL:  Views containing GROUP BY are not automatically updatable.
HINT:  To enable inserting into the view, provide an INSTEAD OF INSERT
trigger or an unconditional ON INSERT DO INSTEAD rule.

It would be great to see something like this when trying to insert into a
simple VIEW that I had made non-updatable:

ERROR:  cannot insert into view "test_view2"
DETAIL:  This view has manually been made non-updatable.

Can you make a simple view non-updatable?

[Ryan Murphy]

Hello.

I enjoy using VIEWs.  Often my views are updatable, either automatically
(due to being a simple 1-table view, or due to a TRIGGER).  Sometimes they
are meant to be just read-only.

Is there any way to set a VIEW to be read-only -- specifically, can I do
this for a view that is automatically updatable due to being simple?

The reason I want this:  It will help me encode into my schema the
distinction between views that are supposed to behave like full-fledged
"subtypes" of a larger relation and need to be updatable, vs those that are
merely a report / literally just a "view".

Thanks!
Ryan

Re: Is there a way to create a functional index that tables tableoid column as an arg?

[Ryan Murphy]

Hi David!  Thanks for the reply.

> tableoid might be an exception to
> this, but it does not really seem like a useful column to index,
> giving it would be indexing the same value for each record in the
> table.

Unless you're using inheritance - then tableoid may vary.  That's the case
I'm interested in.


> -- get all animals that are persons
> select ... from animal where tableoid in (select
> get_inherited_tables('person'::regclass);
>
> -- get all animals that are not persons
> select ... from animal where tableoid not in (select
> get_inherited_tables('person'::regclass);
>
>
That's a great idea.  I'll try it!


> Just be careful around search_paths and your use of regclass. In this
> case, if "animal" was not in the first schema in search_path, but
> someone created another table called "person" that was in the first
> schema listed in search_path, then the query would not do what you
> want. You might want to consider prefixing the input parameter into
> get_inherited_tables with the schema name too.
>

Good point.

Thanks again!
Ryan

Is there a way to create a functional index that tables tableoid column as an arg?

[Ryan Murphy]

Hello Postgressers,

I am using table inheritance and have e.g. the following tables:

create table animal ( ... );
create table dog ( ... ) inherits (animal);
create table cat ( ... ) inherits (animal);
create table person ( ... ) inherits (animal);
create table musician ( ... ) inherits (person);
create table politician ( ... ) inherits (person);

Now I have a query that gets all the "animal"'s except for those that are
"person"'s.

select * from only animal

won't cut it, because it leaves out the dogs and cats.

select *, tableoid::regclass relname from animal
where relname != 'person'::regclass

also won't cut it because it leaves out the musicians and politicians.

So I have created an immutable function is_a_kind_of(tbl regclass,
parent_tbl regclass) that returns true iff tbl is identical with, or
directly or indirectly inherits from, parent_tbl.  For example:

is_a_kind_of('person','person') => true
is_a_kind_of('person','animal') => true
is_a_kind_of('musician','person') => true
is_a_kind_of('animal','person') => false
is_a_kind_of('dog','person') => false

No problems so far.  Now my query works:

select *,tableoid from "animal"
where not is_a_kind_of(tableoid::regclass::text, 'person')

This query is somewhat slow though - I'd like to index the is_a_kind_of()
call.  And Postgres supports functional indexes!  So I try:

create index animal_is_person on animal (
is_a_kind_of(tableoid::regclass, 'person') );

ERROR:  index creation on system columns is not supported

I see that this is because "tableoid" is a system column. Does anyone know
any workaround for this?  So close yet so far away!

Thanks!
Ryan

Downsides of liberally using CREATE TEMP TABLE ... ON COMMIT DROP

[Ryan Murphy]

Hello,

I heard at a PostgreSQL talk that you should not liberally create temp
tables in the course of frequently-used functions etc, because (roughly)
you're using up some of the same resources that you for your regular tables.

Is this true?  Is there an important reason not to have e.g. a plpgsql
function that uses a temp table?  What are the specific problems if I do
this?  Is the problem ameliorated if I add ON COMMIT DROP?

Best,
Ryan