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@xxxxxxxxxxxxx> wrote:
Tim Cross <theophilusx@xxxxxxxxx> writes:
> On Mon, 13 Aug 2018 at 12:23, Olivier Gautherot <olivier@xxxxxxxxxxxxx>
>> On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross <theophilusx@xxxxxxxxx> 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
