Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtCoi-00BJsc-F7 for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Oct 2023 20:06:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qtCog-008Sw3-Fv for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Oct 2023 20:06:55 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtCog-008Svs-2y for pgsql-bugs@lists.postgresql.org; Wed, 18 Oct 2023 20:06:54 +0000 Received: from mail-wm1-x32e.google.com ([2a00:1450:4864:20::32e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qtCod-0018wM-UA for pgsql-bugs@lists.postgresql.org; Wed, 18 Oct 2023 20:06:53 +0000 Received: by mail-wm1-x32e.google.com with SMTP id 5b1f17b1804b1-40776b1ff73so45724475e9.2 for ; Wed, 18 Oct 2023 13:06:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1697659609; x=1698264409; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:from:to:cc:subject:date :message-id:reply-to; bh=QaXBrBBiGRaj2WPL+WGbCa5FVWFlAttUhQ1+7kMKoFU=; b=YVd0eA+UcBzusrlD9SI3jZO8Iwi4BpYMd6DjuYAmV0STqdYHEq38P8rvVB3uxYPvpU y41yc4zGU9RpaVvk78ErWM3FeEK5zBlu1h5JFJdHXlk0iBGx8tkDXiwupWCslnUUd/lU Ntex9F2TcDleay6CnEbJcyfQKyFFt6affZcCdhokBM6ewv6L2N2KO6SnuovgHua/o//K QsbKe/Qaw7Eo+cDHDcl73bDDT8ZR9MVgQsAAaJZvPOa/9zaPBZm982XrEU19P1DTO6dJ NtjuQqlBlZmwclMzXEjlPDp8cPDvellGhDrn060m7Rme6ItP7Yav5NTxlicW6bAJMwFX 266g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1697659609; x=1698264409; h=mime-version:user-agent:content-transfer-encoding:references :in-reply-to:date:to:from:subject:message-id:x-gm-message-state:from :to:cc:subject:date:message-id:reply-to; bh=QaXBrBBiGRaj2WPL+WGbCa5FVWFlAttUhQ1+7kMKoFU=; b=ehX4FFDmvw8SjgTMEB5u9dXTfpAAnVU778rfAhUQCzsBEIowsI8QKE9jx5UDeww5U0 MA1QvWC+mOvaIV9pIe4Khpv97X9bdUVxSM13cYR+Uz6Y9TEs/5OdoRUK5RGKy3pf++zX KpYYIf0NK3y5Ig6zt0Trc6XGnA2swy7U+Qd+Jo/Uu1HaihlRdgy9e13pJvFy641VSYD+ 1MDU4En2ZRVMW89W5k+GHbT2HVOH3g7s/K7FRyGQqh4SlzHXeNQwQ+9GkZGuqdst8yPr TbdlF2hnIdlB0Z5vkj3555wPBTK9c0B4dilqOwb/FqgcPE6zFYx6ky4ETuECnjpns4VX he9w== X-Gm-Message-State: AOJu0Yzny5l2tfJesMaC5jTB+jpV6PbrPr/x9XTtorK0kYbjvNJwQI62 X84NnB6naetMG7hC7Rbm4RXNG8UO/THDqL8istMBiA== X-Google-Smtp-Source: AGHT+IHFjhSeIW0c/l9kMyPGUBG0GcAvzLIlymMWJDTZUt9jPsYbW3PdvQtbjZx/6XOXl5sGJf3OWw== X-Received: by 2002:a05:600c:3104:b0:406:3fda:962c with SMTP id g4-20020a05600c310400b004063fda962cmr302561wmo.31.1697659609476; Wed, 18 Oct 2023 13:06:49 -0700 (PDT) Received: from localhost.localdomain ([2001:871:260:ccee:ff5d:296a:ef88:dd9e]) by smtp.gmail.com with ESMTPSA id n18-20020a05600c4f9200b004060f0a0fd5sm2516481wmq.13.2023.10.18.13.06.49 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Wed, 18 Oct 2023 13:06:49 -0700 (PDT) Message-ID: <28d214b20fbcc95b6e24fa647f19982e6c0ed19f.camel@cybertec.at> Subject: Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. From: Laurenz Albe To: asotolongo@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 18 Oct 2023 22:06:48 +0200 In-Reply-To: <18161-6f5840d311f0a100@postgresql.org> References: <18161-6f5840d311f0a100@postgresql.org> Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Wed, 2023-10-18 at 17:43 +0000, PG Bug reporting form wrote: > PostgreSQL version: 15.4 >=20 > example=3D# CREATE TABLE example (i serial , j text); > CREATE TABLE > example=3D# alter table example add column i_new bigint; > ALTER TABLE > example=3D# alter table example alter column i drop default ; > ALTER TABLE > example=3D# alter table example alter column i_new set default > nextval('example_i_seq'::regclass); > ALTER TABLE > example=3D# alter table example drop column i; > ERROR:=C2=A0 cannot drop column i of table example because other objects = depend > on it > DETAIL:=C2=A0 default value for column i_new of table example depends on = sequence example_i_seq > HINT:=C2=A0 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