Re: Safe operations?

Thanks everyone for your prompt help. It sounds like a rename operation is
almost never an issue unless you literally had millions of indexes. Thanks
for all the follow on questions and answers, it was most helpful and
interesting to learn a bit more about PG internals.

On Mon, 13 Aug 2018 at 12:01, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Tim Cross <theophilusx(at)gmail(dot)com> writes:
> > On Mon, 13 Aug 2018 at 12:23, Olivier Gautherot <olivier(at)gautherot(dot)net>
> >> On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross <theophilusx(at)gmail(dot)com>
> wrote:
> >>> Just wondering - what about the case when the column being renamed is
> >>> also referenced in an index or check constraint?
>
> >> Tim, as far as I know, names are only an attribute tagged to an OID.
> >> Internal relations are though these OIDs, not names, so renaming a
> column
> >> is really one-shot. Names are mainly a more convenient way of referring
> to
> >> objects.
>
> > thanks Olivier, that is what I suspected and your explanation fits with
> my
> > mental model. I had assumed table/column names are convenience for humans
> > and that the system would use OIDs etc for internal references.
>
> Right, catalog internal references are all via OIDs or column numbers,
> so that the only thing the system thinks it needs to do is update the
> "name" field in a single catalog row. (A problem with this is that
> user-defined function bodies are stored as text; so you may well have
> to run around and fix your functions by hand. But that doesn't
> contribute to the cost of the RENAME operation per se.)
>
> Getting back to Samuel's original question, the reason we don't try
> to document performance issues like this is that there are just too
> many moving parts. Yeah, the update of the catalog row should be
> more or less O(1), and then the required updates of the catalog's
> indexes will be more or less O(log N) (N being the number of rows
> in that catalog). But in practice the main constraint is often the
> need to obtain locks on the relevant database objects, and that's
> really hard to give a prediction for.
>
> regards, tom lane
>