Re: [pgsql-admin] "Soft-hitting" the 1600 column limit

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: [pgsql-admin] "Soft-hitting" the 1600 column limit
Дата
Msg-id 21523.1528311427@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: [pgsql-admin] "Soft-hitting" the 1600 column limit  (Wells Oliver <wells.oliver@gmail.com>)
Список pgsql-admin
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


В списке pgsql-admin по дате отправления:

Предыдущее
От: "Moradhassel, Kavian"
Дата:
Сообщение: Re: [**EXTERNAL**] Re: [pgsql-admin] "Soft-hitting" the 1600 columnlimit
Следующее
От: Ron
Дата:
Сообщение: Re: [**EXTERNAL**] Re: [pgsql-admin] "Soft-hitting" the 1600 columnlimit