Re: Convert table to view 9.1

salah jubeh wrote:
>> http://www.postgresql.org/docs/current/static/catalog-pg-class.html
>> relhastriggers bool True if table has (or once had) triggers
>
>> This is what is queried when you try to convert the table into a view.
>> So there is no way to convert your table to a view unless you are
>> wiling to tamper with the pg_class.

> I have tried the follwoing and itworks, I need to update also relhasindex
>
> UPDATE pg_class SET relhastriggers = FALSE WHERE oid = 'b'::regclass;
> UPDATE pg_class SET relhasindex = FALSE WHERE oid = 'b'::regclass;
>
> To be honest I do not like to play with catalog tables, so my question would be, what are the reason
> for "(or recently had)" in the case of index, or (or once had) in the case of triggers. I find the
> ability to convert a table to a view an extremly handy in applications were buisnes logic is modelled
> as views. For example, I need to refactor b, but keep it for backward compatability as updatabale
> view.

You are right to be reluctant to tamper with pg_class.

This comment in backend/commands/trigger.c explains why
relhastriggers is left "true":

/*
* We do not bother to try to determine whether any other triggers remain,
* which would be needed in order to decide whether it's safe to clear the
* relation's relhastriggers. (In any case, there might be a concurrent
* process adding new triggers.) Instead, just force a relcache inval to
* make other backends (and this one too!) rebuild their relcache entries.
* There's no great harm in leaving relhastriggers true even if there are
* no triggers left.
*/

So I guess it is just left because nobody cared enough.

What keeps you from creating a copy of b:

CREATE TABLE b_copy(LIKE b EXCLUDING CONSTRAINTS);
DROP TABLE b;
ALTER TABLE b_copy RENAME TO b;

Yours,
Laurenz Albe