Re: BUG #17127: drop column cann't delete from pg_attribute, so it will up to 1600 limits soon

On Thu, 29 Jul 2021 at 16:34, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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