Обсуждение: Safe operations?
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?
Thanks
Samuel
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 CrossOn 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 CrossTim 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:
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