From: | Anthony Sotolongo <asotolongo(at)gmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. |
Date: | 2023-10-18 20:14:53 |
Message-ID: | CAASDfF3mXRoNZjQr60oPOMhi0p36wvFPkVi9-=NmFLUTSNdQ+w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi Laurenz, thank for your explanation, I understand now what I was
missing, the process of: ALTER SEQUENCE sequence_name OWNED BY
table_column_name;
now all make sense
Good point
Thanks again
El mié, 18 de oct. de 2023 5:06 p. m., Laurenz Albe <
laurenz(dot)albe(at)cybertec(dot)at> escribió:
> On Wed, 2023-10-18 at 17:43 +0000, PG Bug reporting form wrote:
> > PostgreSQL version: 15.4
> >
> > example=# CREATE TABLE example (i serial , j text);
> > CREATE TABLE
> > example=# alter table example add column i_new bigint;
> > ALTER TABLE
> > example=# alter table example alter column i drop default ;
> > ALTER TABLE
> > example=# alter table example alter column i_new set default
> > nextval('example_i_seq'::regclass);
> > ALTER TABLE
> > example=# alter table example drop column i;
> > ERROR: cannot drop column i of table example because other objects
> depend
> > on it
> > DETAIL: default value for column i_new of table example depends on
> sequence example_i_seq
> > HINT: Use DROP ... CASCADE to drop the dependent objects too.
>
> That is working as intended.
>
> If you create a "serial" column, PostgreSQL adds a dependency, just like
> this statement would:
>
> ALTER SEQUENCE example_i_seq OWNED BY example.i;
>
> That dependency makes sure that the sequence is automatically deleted when
> you drop the column. That relationship is not broken if you change the
> default value or use the sequence elsewhere.
>
> You never created the sequence explicitly, so you should consider it an
> implementation details of "serial", just like the column default.
> Manually changing the default or using the sequence for something else
> messes with that on a lower level.
>
> It is easy to remove the dependency:
>
> ALTER SEQUENCE example_i_seq OWNED BY NONE;
>
> Consider using the more advanced and standard conforming alternative
> of identity columns. You will still find ways to mess with the underlying
> sequence, but there is no column default you can change, and the sequence
> name is not visible in the output of "\d", so you are less likely to fall
> into this trap.
>
> Yours,
> Laurenz Albe
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2023-10-18 20:15:58 | Re: BUG #18160: first create table show "ERROR: permission denied for schema public", next create table works |
Previous Message | Stephen Frost | 2023-10-18 20:11:59 | Re: pg_dump needs SELECT privileges on irrelevant extension table |