Re: "object references" and renaming was: Why Does UPDATE Take So Long?

2008/10/1 Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>:
> On Wed, 01 Oct 2008 08:32:16 -0600
> Bill Thoen <bthoen(at)gisnet(dot)com> wrote:
>
>> CREATE TABLE farm2 (LIKE farms);
>> INSERT INTO farm2 (farm_id, fips_cd, farm_nbr, prog_year) SELECT
>> farm_id, fips_cd, farm_nbr, '2007' FROM farms;
>> DROP TABLE farms;

this will fail if there are FK references to farms table.

>> ALTER TABLE farm2 RENAME TO farms;
>> CREATE UNIQUE INDEX farms_id_key ON farms(farm_id);
>> CREATE UNIQUE INDEX farms_fips_nbr_key ON farms(fips_cd,farm_nbr);
>
> Is this kind of stuff going to affect any reference to the farm
> table? eg. inside functions, triggers etc?

no, not in functions source.
only FK references will be affected. FK triggers are handled internally.
I don't know if any other kind of object references are handled this way.

> what if:
> create table farm_rel (
> farm_id [sometype] references farm(farm_id) on delete cascade,
> ...
> );
>
> and I
>
> alter table farm rename to farm_t;
> alter table farm2 rename to farm;
> drop table farm_t;

well, check it :) I did:

filip(at)filip=# \d farm_rel
Table "public.farm_rel"
Column | Type | Modifiers
---------+---------+-----------
farm_id | integer |
Foreign-key constraints:
"farm_rel_farm_id_fkey" FOREIGN KEY (farm_id) REFERENCES
farm_t(farm_id) ON DELETE CASCADE

filip(at)filip=# drop table farm_t;
NOTICE: constraint farm_rel_farm_id_fkey on table farm_rel depends on
table farm_t
ERROR: cannot drop table farm_t because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

>
> or similar situations...
>
> where could I incur in troubles using RENAME (for tables, columns
> etc...)?

if you reference renamed objects from, say, pl/pgsql function source,
it can effect in broken code.

filip(at)filip=# create function get_farm_id() returns int as $$SELECT
farm_id from farm limit 1$$ language sql;
CREATE FUNCTION
filip(at)filip=# alter table farm rename to farm_t;
ALTER TABLE
filip(at)filip=# select get_farm_id();
ERROR: relation "farm" does not exist
CONTEXT: SQL function "get_farm_id" during startup

OTOH, your rename trick will work for such functions :)

--
Filip Rembiałkowski