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;

Thanks for the reply, In the scenario above this will work, but if I add a view as:

create view a_b as SELECT a.id as a_id, b.id as b_id FROM b join a on a.id = b.a_id;

then the -DROP table b;- will fail, unless I drop also a_b view,  or use cascade option. In certain applications, it is easy. In some cases, it will take a lot of time and effort.

Is there a plan to fix this in the comming releases.  Finally, what is the risk of changing the cataloge tables in this case?

Regards 

On Wednesday, December 11, 2013 3:15 PM, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

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