Table renaming does not propagate to views

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Table renaming does not propagate to views

Jordan Deitch-2
Hi Hackers -

It appears only the oid of the relations used in a view are captured in the view definition, not the relations' name itself. The effect this has is that relation renaming does not propagate to views. I would like to assert that they should.

The user has stated their intention in the view to reference a particular relation by name, not by oid, and so materializing the oid is defective behavior.

The following procedure demonstrates:

---------------------------
begin;
create table public.test (id varchar(255));
insert into public.test(id) values ('12/12/2009');


create table public.test_new (id varchar(50));
insert into public.test_new(id) select * from test;

create view public.test_v as select count(*) from test;

select * from public.test_v; <------ returns 1

alter table public.test rename to test_depricated;
alter table public.test_new rename to test;

insert into public.test(id) values ('12/12/2010');


select * from public.test_v; <------ (erroneously) returns 1


rollback;
---------------------------

Thanks!


--
Jordan Deitch
https://id.rsa.pub/

Reply | Threaded
Open this post in threaded view
|

Re: Table renaming does not propagate to views

Tom Lane-2
Jordan Deitch <[hidden email]> writes:
> It appears only the oid of the relations used in a view are captured in the view definition, not the relations' name itself. The effect this has is that relation renaming does not propagate to views. I would like to assert that they should.

> The user has stated their intention in the view to reference a particular relation by name, not by oid, and so materializing the oid is defective behavior.

[ shrug... ]  There's a considerably larger body of people who think
the current behavior is correct; moreover, we've got a couple decades
of backwards compatibility to consider.  I think your odds of convincing
us to change this are nil.

Unfortunately, since the SQL standard lacks any renaming functionality,
it's hard to settle this sort of thing by appealing to outside authority.
However, if they did add RENAME, I bet that they'd make it work as we have
it, because otherwise the standard's notions of dependency and cascaded
drops make no sense.  If we did things as you suggest, then in the
interval between the two renames, what does the view mean?

    create view public.test_v as select count(*) from test;
    alter table public.test rename to test_depricated;

    select * from public.test_v;    -- what should happen here?

    alter table public.test_new rename to test;

It's pretty clear from the spec's definition of DROP that they don't
intend to allow you to take away any referenced table of a view while
still having the view in existence.  With the reference-by-OID behavior,
RENAME doesn't create an issue for that, but with reference-by-name it
would.

Perhaps you can get the behavior you want by executing dynamic SQL
instead of using a view.

                        regards, tom lane