Thread: ALTER TABLE table RENAME COLUMN x TO y
PostgreSQL version 7.3.3, GCC 2.96, Redhat 7.2 When issuing the following type of command: ALTER TABLE table RENAME COLUMN x TO y The column name change is not cascading through to RULEs on a VIEW. For example I had a column named "id_security" in TABLE "tbl_valrule" and I had a RULE on a view that stated: CREATE RULE rul_i03 AS ON INSERT TO vu_tbl_valrule DO INSTEAD INSERT INTO tbl_valrule (id_security, id_valmonthend, n_lagdays ) VALUES (new.id_security, new.id_valmonthend, new.n_lagdays ); After issuing the following command: ALTER TABLE tbl_valrule RENAME COLUMN id_security TO id_seclass; The Rule stated above never changed. I noted that the underlying SELECT rule (rule name _RETURN) for the view changed by replacing the column named "id_security" to "id_seclass AS id_security". Regards Donald Fraser Ps. The way I checked that the Rule had changed was by issuing the following command. SELECT r.rulename, pg_get_ruledef(r.oid) AS definition FROM pg_class AS c, pg_rewrite AS r WHERE r.ev_class = c.oid AND c.relname = 'vu_tbl_valrule' ORDER BY r.rulename
"Donald Fraser" <demolish@cwgsy.net> writes:
> When issuing the following type of command:
> ALTER TABLE table RENAME COLUMN x TO y
> The column name change is not cascading through to RULEs on a VIEW.
More specifically, INSERTs and UPDATEs contained in rules don't have
their target column names adjusted. This is because the "resname"
fields in their targetlists contain the original column names, and
those fields are actually looked at to determine the target columns.
I think this behavior is vestigial, and we could both simplify the code
and make it RENAME-proof by using just the "resno" fields to determine
the target columns. "resname" would then have just one purpose: to
carry the "AS" alias of targetlist entries in SELECTs. There is already
code in ruleutils.c to allow "resname" to be overridden by the current
column name of a view (thus handling RENAME applied to the view itself),
and I don't think "resname" is user-visible in any other way.
Anyone see a problem with this plan?
I regard this as something we should fix for 7.4, mainly because if you
use --enable-cassert then the backend actually dumps core when trying to
execute the outdated rule (there are Asserts in there that notice the
resname mismatch).
regards, tom lane
Tom Lane wrote: > "Donald Fraser" <demolish@cwgsy.net> writes: > > When issuing the following type of command: > > ALTER TABLE table RENAME COLUMN x TO y > > The column name change is not cascading through to RULEs on a VIEW. > > More specifically, INSERTs and UPDATEs contained in rules don't have > their target column names adjusted. This is because the "resname" > fields in their targetlists contain the original column names, and > those fields are actually looked at to determine the target columns. > > I think this behavior is vestigial, and we could both simplify the code > and make it RENAME-proof by using just the "resno" fields to determine > the target columns. "resname" would then have just one purpose: to > carry the "AS" alias of targetlist entries in SELECTs. There is already > code in ruleutils.c to allow "resname" to be overridden by the current > column name of a view (thus handling RENAME applied to the view itself), > and I don't think "resname" is user-visible in any other way. > > Anyone see a problem with this plan? > > I regard this as something we should fix for 7.4, mainly because if you Oh, man, you are reaching with that one, but I like it. :-) > use --enable-cassert then the backend actually dumps core when trying to > execute the outdated rule (there are Asserts in there that notice the > resname mismatch). -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073