Safe operations?

On 08/12/2018 05:41 PM, Samuel Williams wrote:

I wish the documentation would include performance details, i.e. this
operation is O(N) or O(1) relative to the number of rows.

I found renaming a table was okay.

How about renaming a column? Is it O(1) or proportional to the amount of
data?

Is there any documentation about this?

https://www.postgresql.org/docs/10/static/sql-altertable.html

"RENAME

The RENAME forms change the name of a table (or an index, sequence,
view, materialized view, or foreign table), the name of an individual
column in a table, or the name of a constraint of the table. There is no
effect on the stored data.
"

Thanks
Samuel

--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 13 Aug 2018 at 11:24, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/12/2018 05:41 PM, Samuel Williams wrote:

I wish the documentation would include performance details, i.e. this
operation is O(N) or O(1) relative to the number of rows.

I found renaming a table was okay.

How about renaming a column? Is it O(1) or proportional to the amount of
data?

Is there any documentation about this?

https://www.postgresql.org/docs/10/static/sql-altertable.html

"RENAME

The RENAME forms change the name of a table (or an index, sequence,
view, materialized view, or foreign table), the name of an individual
column in a table, or the name of a constraint of the table. There is no
effect on the stored data.
"

Just wondering - what about the case when the column being renamed is also
referenced in an index or check constraint? (I would guess you cannot
rename a column used in a check constraint without first removing it, but
for an index, would this result in the index being rebuilt (or do you have
to take care of that manually or are such references abstracted such that
the column name "text" is irrelevant tot he actual structure of the
index?).

--
Adrian Klaver
adrian.klaver@aklaver.com

--
regards,

Tim

--
Tim Cross

On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross <theophilusx@gmail.com> wrote:

On Mon, 13 Aug 2018 at 11:24, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/12/2018 05:41 PM, Samuel Williams wrote:

I wish the documentation would include performance details, i.e. this
operation is O(N) or O(1) relative to the number of rows.

I found renaming a table was okay.

How about renaming a column? Is it O(1) or proportional to the amount

of

data?

Is there any documentation about this?

https://www.postgresql.org/docs/10/static/sql-altertable.html

"RENAME

The RENAME forms change the name of a table (or an index, sequence,
view, materialized view, or foreign table), the name of an individual
column in a table, or the name of a constraint of the table. There is no
effect on the stored data.
"

Just wondering - what about the case when the column being renamed is
also referenced in an index or check constraint? (I would guess you cannot
rename a column used in a check constraint without first removing it, but
for an index, would this result in the index being rebuilt (or do you have
to take care of that manually or are such references abstracted such that
the column name "text" is irrelevant tot he actual structure of the
index?).

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.

Olivier

On Mon, 13 Aug 2018 at 12:23, Olivier Gautherot <olivier@gautherot.net>
wrote:

On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross <theophilusx@gmail.com> wrote:

On Mon, 13 Aug 2018 at 11:24, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 08/12/2018 05:41 PM, Samuel Williams wrote:

I wish the documentation would include performance details, i.e. this
operation is O(N) or O(1) relative to the number of rows.

I found renaming a table was okay.

How about renaming a column? Is it O(1) or proportional to the amount

of

data?

Is there any documentation about this?

https://www.postgresql.org/docs/10/static/sql-altertable.html

"RENAME

The RENAME forms change the name of a table (or an index, sequence,
view, materialized view, or foreign table), the name of an individual
column in a table, or the name of a constraint of the table. There is no
effect on the stored data.
"

Just wondering - what about the case when the column being renamed is
also referenced in an index or check constraint? (I would guess you cannot
rename a column used in a check constraint without first removing it, but
for an index, would this result in the index being rebuilt (or do you have
to take care of that manually or are such references abstracted such that
the column name "text" is irrelevant tot he actual structure of the
index?).

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.

Olivier

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.
--
regards,

Tim

--
Tim Cross

Tim Cross <theophilusx@gmail.com> writes:

On Mon, 13 Aug 2018 at 12:23, Olivier Gautherot <olivier@gautherot.net>

On Sun, Aug 12, 2018 at 11:06 PM, Tim Cross <theophilusx@gmail.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

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@sss.pgh.pa.us> wrote: