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.a...@wien.gv.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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
