Message-ID: <efdbe967d39597898f1730803e34798cf5016562.camel@cybertec.at>
Subject: Re: Default Value Retention After Dropping Default
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Marcelo Fernandes <marcefern7@gmail.com>, 
	pgsql-general@lists.postgresql.org
Date: Mon, 24 Feb 2025 12:50:35 +0100
In-Reply-To: <CAM2F1VNZNeruO2N+_-Fq3JJ7bto35kOzomoN5A2dmstAWLi5tQ@mail.gmail.com>
References: 
	<CAM2F1VNZNeruO2N+_-Fq3JJ7bto35kOzomoN5A2dmstAWLi5tQ@mail.gmail.com>
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/efdbe967d39597898f1730803e34798cf5016562.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-02-24 at 20:56 +1300, Marcelo Fernandes wrote:
> I am experiencing an interesting behavior in PostgreSQL and would like to=
 seek
> some clarification.
>=20
> In the following snippet, I first add a column with a default value, then=
 drop
> that default. However, when I query the table, the column still retains t=
he
> dropped default for existing rows:
>=20
> =C2=A0 SET client_min_messages=3Ddebug1;
>=20
> =C2=A0 DROP TABLE IF EXISTS foo CASCADE;
> =C2=A0 CREATE TABLE foo (id SERIAL PRIMARY KEY);
>=20
> =C2=A0 INSERT INTO foo (id) SELECT generate_series(1, 10000);
>=20
> =C2=A0 ALTER TABLE foo ADD COLUMN bar varchar(255) NOT NULL DEFAULT 'defa=
ult';
> =C2=A0 ALTER TABLE foo ALTER COLUMN bar DROP DEFAULT;
>=20
> =C2=A0 SELECT * from foo order by id desc limit 5;
> =C2=A0 --=C2=A0=C2=A0 id=C2=A0=C2=A0 |=C2=A0=C2=A0 bar
> =C2=A0 -- -------+---------
> =C2=A0 --=C2=A0 10000 | default
> =C2=A0 --=C2=A0=C2=A0 9999 | default
> =C2=A0 --=C2=A0=C2=A0 9998 | default
> =C2=A0 --=C2=A0=C2=A0 9997 | default
> =C2=A0 --=C2=A0=C2=A0 9996 | default
>=20
> In this example, even after dropping the default value from the bar colum=
n, the
> rows that were previously inserted (prior to dropping the default) still =
show
> 'default' as their value in the bar column.
>=20
> It does not see that the table has been rewritten or rescanned, otherwise=
 the
> debug1 messages would be triggered.
>=20
> Can anyone explain how PostgreSQL "knows about" the default value that ha=
s just
> been dropped and what is happened under the scenes? I am keen on a deep
> understanding on how Postgres achieves this.

The "missing value" is stored in pg_attribute.admissingval:

SELECT attmissingval
FROM pg_attribute
WHERE attrelid =3D 'foo'::regclass
  AND attname =3D 'bar';

 attmissingval=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=
=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90
 {default}
(1 row)

That value is used for all rows that don't yet physically have the column.

Yours,
Laurenz Albe

--=20

*E-Mail Disclaimer*
Der Inhalt dieser E-Mail ist ausschliesslich fuer den=20
bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat=
=20
dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,=20
dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder=
=20
Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich=
=20
in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.

*CONFIDENTIALITY NOTICE & DISCLAIMER
*This message and any attachment are=20
confidential and may be privileged or otherwise protected from disclosure=
=20
and solely for the use of the person(s) or entity to whom it is intended.=
=20
If you have received this message in error and are not the intended=20
recipient, please notify the sender immediately and delete this message and=
=20
any attachment from your system. If you are not the intended recipient, be=
=20
advised that any use of this message is prohibited and may be unlawful, and=
=20
you must not copy this message or attachment or disclose the contents to=20
any other person.