From: Tom Lane <tgl@sss.pgh.pa.us>
To: Wells Oliver <wells.oliver@gmail.com>
Cc: nunks <nunks.lol@gmail.com>
Cc: Scott Ribe <scott_ribe@elevated-dev.com>
Cc: pgsql-admin <pgsql-admin@postgresql.org>
Subject: Re: [pgsql-admin] "Soft-hitting" the 1600 column limit
Date: Wed, 06 Jun 2018 14:57:07 -0400
Message-ID: <21523.1528311427@sss.pgh.pa.us> (raw)
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>
Wells Oliver <wells.oliver@gmail.com> writes:
> Is there a pointer to some deeper explanation of this? It's news to me and
> kind of fascinating that dropped columns don't disappear. I did this stupid
> test, which obviously failed:
> mydb=# create table wells.foo (col1 text, col2 text);
> CREATE TABLE
> mydb=# insert into wells.foo values ('a','b'),('c','d');
> INSERT 0 2
> mydb=# alter table wells.foo drop column col2;
> ALTER TABLE
> mydb=# 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, what
> 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=# create table wells.foo (col1 text, col2 text);
CREATE TABLE
regression=# select attname, attnum, attisdropped from pg_attribute where attrelid = 'wells.foo'::regclass and attnum > 0;
attname | attnum | attisdropped
---------+--------+--------------
col1 | 1 | f
col2 | 2 | f
(2 rows)
regression=# alter table wells.foo drop column col2;
ALTER TABLE
regression=# select attname, attnum, attisdropped from pg_attribute where attrelid = '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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, wells.oliver@gmail.com, nunks.lol@gmail.com, scott_ribe@elevated-dev.com
Subject: Re: [pgsql-admin] "Soft-hitting" the 1600 column limit
In-Reply-To: <21523.1528311427@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox