From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: "David G. Johnston" <david.g.johnston@gmail.com>,
        =?UTF-8?B?5b635ZOl?= <digoal@126.com>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17127: drop column cann't delete from pg_attribute,
 so it will up to 1600 limits soon
In-reply-to: 
 <CAApHDvqnWUmQ5YAPtMwxZM4eH-4NbcLY22zvTQhbXLY4xk3-Yg@mail.gmail.com>
References: <17127-f973fb9d41bae9ca@postgresql.org>
 <CAKFQuwYVbVSTYR2pAc4tDsBMzh6gjHkNehia0NyFHorGSBh3FQ@mail.gmail.com>
 <CAApHDvqnWUmQ5YAPtMwxZM4eH-4NbcLY22zvTQhbXLY4xk3-Yg@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 29 Jul 2021 16:14:49 +1200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <683331.1627533172.1@sss.pgh.pa.us>
Date: Thu, 29 Jul 2021 00:32:52 -0400
Message-ID: <683332.1627533172@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/683332.1627533172%40sss.pgh.pa.us>
Precedence: bulk

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