Обсуждение: BUG #17127: drop column cann't delete from pg_attribute, so it will up to 1600 limits soon

The following bug has been logged on the website:

Bug reference:      17127
Logged by:          Zhou Digoal
Email address:      digoal@126.com
PostgreSQL version: 14beta2
Operating system:   CentOS 7.7 x64
Description:

HI, postgresql drop column cann't delete from pg_attribute, so it will up to
1600 limits soon when add and drop column frequenc.

```

do language plpgsql $$ 
declare 
  sql text := 'create table c ('; 
begin 
  for i in 1..1600 loop  
    sql := sql||'c'||i||' int8,';   
  end loop;    
  sql := rtrim(sql,',')||')'; 
  execute sql;   
end;
$$;


postgres=# alter table c add column  c int8;
ERROR:  54011: tables can have at most 1600 columns
LOCATION:  ATExecAddColumn, tablecmds.c:6573

postgres=# alter table c drop column c1;
ALTER TABLE
postgres=# alter table c add column  c int8;
ERROR:  54011: tables can have at most 1600 columns
LOCATION:  ATExecAddColumn, tablecmds.c:6573

postgres=# vacuum full c;
VACUUM
postgres=# alter table c add column  c int8;
ERROR:  54011: tables can have at most 1600 columns
LOCATION:  ATExecAddColumn, tablecmds.c:6573

postgres=# select attname from pg_attribute where attrelid ='c'::regclass
and attisdropped;
           attname            
------------------------------
 ........pg.dropped.1........
(1 row)
```

best regards, digoal

On Thu, 29 Jul 2021 at 15:05, PG Bug reporting form
<noreply@postgresql.org> wrote:
> HI, postgresql drop column cann't delete from pg_attribute, so it will up to
> 1600 limits soon when add and drop column frequenc.

This is true, but it's not a bug. We just never recycle attnums. To do
that you'd need to manually create another table and do INSERT INTO
new_table SELECT * FROM old_table.

There is a good chance we could do something to get rid of these
during table rewrites such as in VACUUM FULL or CLUSTER, but fixing
that would be classed as a new feature rather than a bug fix.

David

On Thu, 29 Jul 2021 at 16:08, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> On Wed, Jul 28, 2021 at 8:04 PM PG Bug reporting form <noreply@postgresql.org> wrote:
>> HI, postgresql drop column cann't delete from pg_attribute, so it will up to
>> 1600 limits soon when add and drop column frequenc.
>>
>
> Yes, this fact is explicitly documented.  See the last paragraph here:
>
> https://www.postgresql.org/docs/13/limits.html
>
> So, it is not a bug, and, IMO, it is not a limitation worth removing.

I'm not sure if I agree that it's "explicitly documented".  All that
text mentions is that you can't have more than 1600 columns in a table
at once. What the OP is complaining about are that attnums are not
recycled and that if you continually add and drop columns you can run
out well before you have 1600 columns.

David

David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 29 Jul 2021 at 16:08, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> So, it is not a bug, and, IMO, it is not a limitation worth removing.

> I'm not sure if I agree that it's "explicitly documented".  All that
> text mentions is that you can't have more than 1600 columns in a table
> at once. What the OP is complaining about are that attnums are not
> recycled and that if you continually add and drop columns you can run
> out well before you have 1600 columns.

Right, but why is that a usage pattern that is worth the very large
increment in complexity that would be involved in supporting it?

The fundamental reason why we can't just "recycle attnums" is that the
attnums are effectively primary keys, ie permanent unique identifiers,
for columns.  A table's attnums propagate into views on the table,
foreign key data in other tables, etc etc.  So renumbering attnums
would involve updating those other objects and therefore taking
exclusive locks on them, which creates a lot of issues.

It's possible that some of this could be dodged if we ever finish
the project of decoupling logical and physical column positions.
What was being talked about there was having three identifiers
for a column (permanent ID, logical index, physical index).
If we didn't restrict the range of permanent IDs then the problem
goes away, at least till you overrun an int16 or int32.

But anyway, I'm skeptical that there is a use-case here that
justifies a lot of work to fix.  Why is it a good idea to
drop and re-add a column over and over?

            regards, tom lane

On Thu, 29 Jul 2021 at 16:34, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The fundamental reason why we can't just "recycle attnums" is that the
> attnums are effectively primary keys, ie permanent unique identifiers,
> for columns.  A table's attnums propagate into views on the table,
> foreign key data in other tables, etc etc.  So renumbering attnums
> would involve updating those other objects and therefore taking
> exclusive locks on them, which creates a lot of issues.

You're right. I'd not given it too much thought and FWIW, I've no
interest in fixing this personally.  What I'd not considered is that
undropped columns that come after a dropped column would need their
attnums resequenced.  I'd only thought as far as the fact that views
and foreign keys can't reference the dropped columns themselves.

> It's possible that some of this could be dodged if we ever finish
> the project of decoupling logical and physical column positions.
> What was being talked about there was having three identifiers
> for a column (permanent ID, logical index, physical index).
> If we didn't restrict the range of permanent IDs then the problem
> goes away, at least till you overrun an int16 or int32.

That's true.  The foreign keys and views would reference the permanent
ID in that case and we'd have flexibility to remove gaps from the
physical index when rewriting the table.

> But anyway, I'm skeptical that there is a use-case here that
> justifies a lot of work to fix.  Why is it a good idea to
> drop and re-add a column over and over?

I only imagined that it's not impossible to hit this naturally through
schema evolution.  However, I imagine the original post here is not
someone facing this on a production server given that the complaint is
about 14beta2. So it may well be a fabricated problem and not even
worth much more discussion..

David