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

On Wed, 1 Oct 2008 22:19:29 +0200
"Filip Rembiałkowski" <plk(dot)zuber(at)gmail(dot)com> wrote:

> > Is this kind of stuff going to affect any reference to the farm
> > table? eg. inside functions, triggers etc?

> no, not in functions source.

I've read somewhere that create *or replace* should be used exactly
because internally postgresql reference functions by id.
dropping and recreating a function will change the id.
Or was I daydreaming and I have memories of another DB?

I think that postgresql store plpgsql functions as "text". I don't
know if it caches plans, "compile" the function somehow etc...
So I was wondering if renaming stuff referenced in a function may
have some unexpected effect.

> only FK references will be affected. FK triggers are handled
> internally. I don't know if any other kind of object references

handled internally?

> 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:

Well I wrote the example not to let someone check for me... ;) just
to give an example of a "renaming" situation where I should be
cautious...
What else could be renamed other than tables and columns?
triggers...?
I'd expect renaming columns follow the same pattern of renaming
tables.

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

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

I think the problem arise from dependencies following the name and
dependencies following the object (table).
It seems that in these cases what is chosen is due to the difficulty
to do otherwise.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it