Thread: table row type and query-specified row type do not match
Hello, Perhaps this issue has been resolved in higher sub-versions or in 9.1? In summary, the issue revolves around the data type of a column being changed, but the data type in a dependent rule on anothertable does not. Does the data type have to be embedded in the rule? Sorry if the issue has already been discussed. Googling it didn't seem to turn up anything. Thank you for a great product anyway! Bill MacArthur Postgres version: PostgreSQL 9.0.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-50),64-bit OS version: CentOS release 5.7 (Final) network=# create table test.a (id bigint); CREATE TABLE network=# create table test.b (id integer); CREATE TABLE ^ network=# create rule testb_delete AS on delete to test.b DO INSERT INTO test.a (id) VALUES (old.id); CREATE RULE network=# insert into test.b (id) values (123); INSERT 0 1 network=# delete from test.b; DELETE 1 network=# select * from test.a; id ----- 123 (1 row) network=# alter table test.a alter column id type integer; ALTER TABLE network=# insert into test.b (id) values (12399); INSERT 0 1 network=# delete from test.b; ERROR: table row type and query-specified row type do not match DETAIL: Table has type integer at ordinal position 1, but query expects bigint.
Bill MacArthur <webmaster@dhs-club.com> writes: > Perhaps this issue has been resolved in higher sub-versions or in 9.1? No, seems to be just the same in HEAD :-( > In summary, the issue revolves around the data type of a column being changed, but the data type in a dependent rule onanother table does not. Does the data type have to be embedded in the rule? Yeah, it does, or at least the implications of not doing so would amount to a ground-up redesign, as well as moving a lot of cycles out of rule creation and into every rule use. What I would have expected to happen is for the ALTER TABLE to throw an error telling you it couldn't cope with updating the rule, and that you'd need to fix that manually. There is such a test involving views; not sure why it's not catching this rule. regards, tom lane
I wrote: > What I would have expected to happen is for the ALTER TABLE to throw an > error telling you it couldn't cope with updating the rule, and that > you'd need to fix that manually. There is such a test involving views; > not sure why it's not catching this rule. FYI, I've committed a patch that will cause 9.2 and later releases to report an error for this case. regards, tom lane