Если сделать alter table ... drop column ..., сама колонка

фактически не удаляется, а только скрывается, за счёт чего не требуется перезапись таблицы, а в новых кортежах проставляется NULL. С этим связано ограничение PostgreSQL на максимальное число колонок в таблице (в которое входят также удалённые колонки). Есть ли способ окончательно удалить колонку (с перезаписью всей таблицы)? Например, после выполнения VACUUM FULL удалённые колонки полностью удаляются или остаются и продолжают заполняться NULL-ами?

Create table as select?

Сам спросил - сам нашел ответ. Похоже, до сих пор единственный вариант - создать новую таблицу (что может быть больно, если это какой-то центральный справочник в системе и на него ссылаются 100500 других таблиц), согласно https://stackoverflow.com/a/15700213/15168194

Тоже к такому мнению склонялся)

Интересно, по какой причине до сих пор не сделана очистка от удалённых полей? Мешает то, что операция станет небезопасной с точки зрения MVCC или есть какие-то другие причины?

pg_repack

"до сих пор". Интересно, а известны СУБД в которых это по-другому? Зачем тратить драгоценное время, да ещё наверное хотите транзакционное, на перераспределение свободного пространства... без явной на то команды....

я не хочу чтобы это делалось автоматом. Но я хочу иметь средство, позволяющее начать жизнь заново, если за период существования проекта у меня в таблицу добавилось и удалилось по паре сотен полей (потому как сейчас это приведёт к тому, что каждая запись потащит за собой маску null-овых полей для всех удалённых атрибутов). Сейчас это можно сделать только через пересоздание таблицы (по сути - костыль, заменяющий отсутствующий функционал), хотя логичнее было бы такое делать при почти любой операции, которая вынуждено перезаписывает всю таблицу с эсклюзивной блокировкой (т.к., такое удаление полей потенциально уменьшит объём хранящихся данных).

pg_repack это делает без лока всей таблицы. Правда в кейсе с Alter table я не уверен что канает. Вообще если в одной таблице пара сотен полей я бы уже задумался над тем что "что то пошло не так"... Но я бы, например, не хотел чтобы таблицы реорганизовывались "на лету" и "без моего на то ведома"... А особенно чтобы если не дай бог мне нужна операция которая ставит эксклюзивный лок на всю таблицу во время этой операции постгрес что-то реорганизовывал, освобождал, перемещал, чесал за ухом...

Ну vacuum full мог бы и справится 😂

Этого расстрелять! :)

Ага👍

Нет, ни pg_repack, ни vacuum full тут не поможет - смотрите пример от Николая Самхвалова: https://gitlab.com/gitlab-org/gitlab/-/issues/205232#note_287215942

1. ни pg_repack, ни vacuum full тут не помогут - смотрите пример от Николая Самохвалова: https://gitlab.com/gitlab-org/gitlab/-/issues/205232#note_287215942 2. мне пока неочевидно зачем поддерживать дропнутые столбцы для вставленных строк после дропа 3. в целом, концепция дропа только изменением метаданных мне понятна - у оракла, например, есть схожая вещь: alter table ... set unused (columns), которая делает почти так же: происходит изменение только метаданных, а уже существующие строки не затрагивает, но новые строки при этом не содержат этих столбцов. А для полного удаления уже используется отдельная команда alter table ... drop unused columns (со своими нюансами). 4. Учитывая подход pg_repack схожий с online redefinition от оракла (dbms_redefinition), я думаю, в pg_repack быстро реализуют и эту фичу, ну или в reorg или новом каком-нибудь форке. Пока же, думаю не так сложно взять этот же подход и реализовать его вручную на триггерах.

Есть универсальный метод убеждения разработчиков в важности такой фичи: деньги.

Ну он там пишет что и vacuum full не помогает... это похоже на какой то косяк вероятно ещё в какой то конкретной версии. set unused норм подход, не знаю кому и зачем он нужен но в оракле куча таких фич :). Но точно этого нельзя делать просто при обычном alter table... как выше человек писал. Это тайминг, а alter table может просто ORM вызывать при миграциях...

Боюсь, со своей стороны смогу только попробовать разобраться и сделать патч. Не такая это фича, чтобы можно было кого-то с бюджетом убедить в её важности, но обидно что текущая реализация прописывается в ограничениях СУБД.

Такой патч подразумевает изменение формата хранения. Т.е. для обновления нужно все данные перелопатить. И теперь, внимание, интересное: все пользователи при обновлении будут вынуждены столкнуться с этой процедурой, потому-что исчезающе малому числу людей требуется удалять и создавать новые колонки в таблицах [в каких-то непотребных количествах].

зачем менять формат хранения? Формат остаётся тот же, просто будет операция, которая физически удалит удалённые колонки (это принципиально не меняет формат, просто в новых физических полей меняется (как и при добавлении колонки, только в этом случае колонка удаляется). После выполнения этой операции, необходимо будет обновить внутренние структуры, используемые для разбора полей в кортеже.

VACUUM FULL всё равно их перелопачивает и "пусть весь мир подождёт".

На pg_repack, да.

А такой патч в pg_repack примут? Учитывая то, что сейчас там специально обрабатываются удалённые поля (создаётся таблица включающие эти поля, затем они дропаются), подозреваю, что были причины так делать? https://github.com/reorg/pg_repack/blob/ebc8a425fcabc06d62a1c9b51fbc3a5f460b2baa/lib/pg_repack.sql.in#L127

С opt-in — запросто.

+100500

Вот когда поймёте почему сделано именно так то сразу придёт понимание что избавиться от дропнутых полей будет оооочень геморойно.

Так почему сделано именно так?

потому что pg_repack физически подменяет табличные и индексные файлы, он не занимется переименовыванием relations. Если же подменять саму таблицу с помощью переименовывания то возникает куча проблем с внешними ключами.

git blame выводит на " Fixed database corruption when target tables have dropped columns, and there are views or functions depending on columns after dropped ones.".

Причем внешние ключи на дропнутых столбцах...

Столбцы тут не при чем. Есть таблица А которую мы ребилдим и Б которая ссылается на А. Создать таблицу А_НОВАЯ легко, но подсунуть её вместо старой А так чтобы вся остальная схема в базе сохранилась (включая foreign key Б->А) гемор.

Это не фундаментальная проблема, просто ошибки проектирования. И рано или поздно, но их исправлять надо, тк это лишние накладные расходы по поддержке несуществующих полей. Начать можно с метаданных например как в оракле: добавить логический id столбца к уже имеющемуся физическому. Реализовать unused и invisible columns на уровне метаданных. Исправить проблему с линками. Затем исправить чтение и запись, чтобы хотя бы в новых строках не иметь дропнутых столбцов и читать это без ошибок

> Это не фундаментальная проблема, просто ошибки проектирования. Потому что Вы так сказали? ;) IMHO, это называется "компромисс", и в этом случае он адекватный. > И рано или поздно, но их исправлять надо, тк это лишние накладные расходы по поддержке несуществующих полей. А у альтернативного способа накладные расходы по поддержке существующих полей куда выше, нет? > Начать можно с метаданных например как в оракле: Ещё нам не хватало всякую чушь с legacy RDBMS сдирать. ;) > добавить логический id столбца к уже имеющемуся физическому. Это overhead. > Реализовать unused и invisible columns на уровне метаданных. И это overhead. > Исправить проблему с линками. А это — неэффективное приложение усилий ("исправление" очень крайнего случая, причём, возможно, с усложнением кода). Patches welcome, впрочем. > чтобы хотя бы в новых строках не иметь дропнутых столбцов и читать это без ошибок Там их и так нет, я правильно помню?

Не то, чтобы проблем. Задач. Так-то что там? Менять pg_catalog.pg_ : attribute, attrdef, class, constraint, depend, description, partitioned_table, policy, proc, rewrite, seclabel, shdepend, statistic, statistic_ext, trigger, type. (это я для 12). Сложности предвижу только с теми, у кого надо парзить nodeTree: attrdef, class, constraint, policy, partitioned_table, proc, rewrite, trigger, type. Да и то — главное найти в этом nodeTree что-то вроде attnum, и поначалу можно и просто отказываться преобразовывать такие таблицы.

Компромисс, потому что забыли про дроп?) фактически write-only сущность. А оверхед вариантов легко сравнить и результат достаточно очевиден: в текущем варианте он на каждой строчной операции. >их и так нет. Это не правда, с точки зрения физического хранения - это хранение null

> Компромисс, потому что забыли про дроп?) Нет, потому, что я перечислил в своём ответе — никто ничего не забывал. > результат достаточно очевиден: в текущем варианте он на каждой строчной операции. И в новом будет, только ещё больше, нет (я уже написал, в каких местах)? > Это не правда, с точки зрения физического хранения - это хранение null Понятно, я подумал, что Вы про что-то другое.

Тебе надо — ты и исправляй. В своём форке, понятно. У людей, которые реально используют postgres проблем с этим нет. Я, если что, смогу подрихтовать количество через pg_dump или slony — но, скорее всего, никогда и не упрусь в эту проблему.

>Нет, потому, что я перечислил в своём ответе — никто ничего не забывал. спорно, реального дропа так и нет. лишь поздний воркэраунд на метаданных >И в новом будет, только ещё больше, нет (я уже написал, в каких местах)? для меня ответ очевиден, но тут спорить бессмысленно, тк пока сравнивать просто нечего

Ну, в нулл битмапе-то есть.

забавно, а загуглить? Или хотя бы прочитать полностью мою ссылку, где Николай Самохвалов отвечает?

> спорно, реального дропа так и нет. Потому что он никому не нужен, может быть? А если нужен — то где их патчи или деньги? ;) > лишь поздний воркэраунд на метаданных Это только Ваше мнение, что это "воркэраунд". Это работает, и никому (кроме тех, кто любит заниматься идиотским крохоборством) не мешает жить. ;) > для меня ответ очевиден, но тут спорить бессмысленно, А для меня очевидно обратное. Т.е. я не понимаю, как добавление overhead может сделать процесс быстрее. Но сравнивать, конечно, нечего.

> Потому что он никому не нужен, может быть? А если нужен — то где их патчи или деньги? ;) Потому что есть ограничения на время и финансы и в таких условиях, либо нищеброды не могущие оплатить, либо чуть богаче и с большим временем, но решают более критичные на данный момент проблемы 🤷 многим было бы удобнее иметь 2-3 разные машины, но покупают одну, или я бы хотел иметь дома в 10 любимых странах, но покупаю один 🤷

> Это работает, и никому (кроме тех, кто любит заниматься идиотским крохоборством) не мешает жить. ;) ну не умрут, конечно, да и без субд не умрут. Но вот, например, по ссылке гитлабу мешают...

> Потому что есть ограничения на время и финансы и в таких условиях, либо нищеброды не могущие оплатить, > либо чуть богаче и с большим временем, но решают более критичные на данный момент проблемы Т.е. приходим к тому, что это "благие пожелания" или "мечтания", IMHO. ;) > многим было бы удобнее иметь 2-3 разные машины, но покупают одну, Я пишу совершенно не об этом. А о соотношении усилий и результата.

Без СУБД, как раз, многие "умрут". Ну это если говорить о практике, а не о мечтаниях об "идеале". ;) > Но вот, например, по ссылке гитлабу мешают... А по какой именно ссылке (обсуждение-то было длинное, не хочется просматривать)?

Прочитал. Ни одного слова от Николая, относящегося к моему ответу, я там не нашёл. Зато нашёл чёткое подтверждение своим словам от представителя gitlab: проблемы нет дажэ на гигантском сетапе, плюс оно иногда чистится при логической репликацыи.

Andreas Brandl @abrandl · 1 year ago Maintainer Summary: - Dropped columns count towards the column limit as expected - On GitLab.com, we still have lots of room here (see #205232 (comment 287156439)) - VACUUM FULL and pg_repack are not fixing the problem - A full table re-create (with data copied between tables) fixes the problem - Logical replication - perhaps we'll use it for the GitLab.com upgrade - resets the problem for GitLab.com

в целом, для меня это не проблема, так что "пусть их". А с учетом таких операций как в гитлаб, я бы просто пересмотрел подход

Да-да, я именно про этот коммент от Andreas Brandl, где он говорит, что проблемы нет и всё к тому жэ периодически сбрасывается.

вы странно переводите: он говорит что это "проблема", и говорит что "возможно будут" делать логическую репликациюдля обхода этой проблемы при апгрейде.

Прямо-таки реальная проблема у людей, о да: Currently on GitLab.com - number of dropped vs number of total (existing+dropped) columns per table, TOP 20 tables by total count: relname | dropped | total ------------------------------------------+---------+------- application_settings | 34 | 255 Т.е. они просто опасаются / исследуют, а реальной проблемы у них и близко нет, я так понял (но все комментарии я читать не хочу).