From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
cc: nunks <nunks.lol@gmail.com>, Scott Ribe <scott_ribe@elevated-dev.com>,
        pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [pgsql-admin] "Soft-hitting" the 1600 column limit
In-reply-to: 
 <CAOC+FBX6JLtcz-vO7K1rB-qmn23MyVgHya5xgcYLX-yUYoM_xw@mail.gmail.com>
References: 
 <CACq6szQbQDG6_mThyH32=6J0e99-KdqOrhZMm9QwvGoVFzNbBw@mail.gmail.com>
 <CAOC+FBXehj4X2DMfytnPKHRWrWWZjq1G9OfUXsMop5Tex7hrvg@mail.gmail.com>
 <3C61E858-5CA7-405D-B32A-B4C89D1AC62E@elevated-dev.com>
 <CACq6szR7Q--tw67ZJL4-T59oAXCLXgmCDFVh=FunY3N4HCDLXg@mail.gmail.com>
 <CAOC+FBX6JLtcz-vO7K1rB-qmn23MyVgHya5xgcYLX-yUYoM_xw@mail.gmail.com>
Comments: In-reply-to Wells Oliver <wells.oliver@gmail.com>
	message dated "Wed, 06 Jun 2018 11:43:22 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <21522.1528311427.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 06 Jun 2018 14:57:07 -0400
Message-ID: <21523.1528311427@sss.pgh.pa.us>
Precedence: bulk

Wells Oliver <wells.oliver@gmail.com> writes:
> Is there a pointer to some deeper explanation of this? It's news to me a=
nd
> kind of fascinating that dropped columns don't disappear. I did this stu=
pid
> test, which obviously failed:

> mydb=3D# create table wells.foo (col1 text, col2 text);
> CREATE TABLE
> mydb=3D# insert into wells.foo values ('a','b'),('c','d');
> INSERT 0 2
> mydb=3D# alter table wells.foo drop column col2;
> ALTER TABLE
> mydb=3D# insert into wells.foo (col1,col2) values('a','b');
> ERROR:  column "col2" of relation "foo" does not exist
> LINE 1: insert into wells.foo (col1,col2) values('a','b');

> Just curious then, in what meaningful way do dropped columns persist, wh=
at
> are the reasons?

I don't recall if it's documented explicitly in any user-facing places,
but poking into the source code or the system catalogs will show you what
happens:

regression=3D# create table wells.foo (col1 text, col2 text);
CREATE TABLE
regression=3D# select attname, attnum, attisdropped from pg_attribute wher=
e attrelid =3D 'wells.foo'::regclass and attnum > 0;
 attname | attnum | attisdropped =

---------+--------+--------------
 col1    |      1 | f
 col2    |      2 | f
(2 rows)

regression=3D# alter table wells.foo drop column col2;
ALTER TABLE
regression=3D# select attname, attnum, attisdropped from pg_attribute wher=
e attrelid =3D 'wells.foo'::regclass and attnum > 0;
           attname            | attnum | attisdropped =

------------------------------+--------+--------------
 col1                         |      1 | f
 ........pg.dropped.2........ |      2 | t
(2 rows)

Most SQL operations ignore "attisdropped" entries in pg_attribute,
which is why those seem to be hidden.  But they're still valid as
far as the physical representation of the table is concerned.

As for why it's like this, the most obvious practical benefit is that it
makes ALTER TABLE DROP COLUMN cheap: we just have to change that one
entry in pg_attribute, not rewrite the entire table to physically remove
the column from each table row.

There are also some more-theoretical benefits involving having a stable
identifier (a/k/a primary key) for a column.  While that could be done
in different ways, the way we do it is that attrelid (the table's OID)
plus attnum is the unique identifier for a column.

			regards, tom lane