Обсуждение: Convert table to view 9.1

salah jubeh wrote:

> ERROR:  could not convert table "b" to a view because it has triggers
> HINT:  In particular, the table cannot be involved in any foreign key relationships.
> 
> ********** Error **********
> 
> ERROR: could not convert table "b" to a view because it has triggers
> SQL state: 55000
> Hint: In particular, the table cannot be involved in any foreign key relationships.
> 
> 
> Scenario:
> 
> create table a (id int primary key);
> create table b (id int primary key, a_id int references a (id));
> 
> insert into  a values (1);
> insert into  b values (1,1);
> 
> create table c AS SELECT * FROM b;
> 
> TRUNCATE b;
> ALTER TABLE b DROP CONSTRAINT b_a_id_fkey;
> ALTER TABLE b DROP CONSTRAINT b_pkey;
> ALTER TABLE b ALTER COLUMN id DROP NOT NULL;
> 
> CREATE RULE "_RETURN" AS ON SELECT TO b DO INSTEAD SELECT * FROM C;

SELECT relhastriggers FROM pg_class WHERE oid = 'b'::regclass;

 relhastriggers
----------------
 t
(1 row)

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.

Yours,
Laurenz Albe

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

salah jubeh <s_jubeh@yahoo.com> writes:
> create table a (id int primary key);
> create table b (id int primary key, a_id int references a (id));

> insert into� a values (1);
> insert into� b values (1,1);

> create table c AS SELECT * FROM b;

> TRUNCATE b;
> ALTER TABLE b DROP CONSTRAINT b_a_id_fkey;
> ALTER TABLE b DROP CONSTRAINT b_pkey;
> ALTER TABLE b ALTER COLUMN id DROP NOT NULL;
> �
> CREATE RULE "_RETURN" AS ON SELECT TO b DO INSTEAD SELECT * FROM C;

Patient: Doctor, it hurts when I do this.
Doctor: So, don't do that.

Why would you think this is a good thing to do?  Why not just rename
table b to c, and then create the view as b?

(For context, it's not even considered a supported operation to
manually create _RETURN rules like that.  Any arbitrary restrictions
we might put on transforming tables to views are perfectly legitimate
IMHO, because the only case we care about supporting is pg_dump's
usage of this hack to break circular dependencies between views.
And in that case, the "table" never had any table-only features.)

            regards, tom lane