Блог

Главная - Вопросы по программированию - идентификационные номера для повторного использования postgresql

идентификационные номера для повторного использования postgresql

#postgresql

#postgresql

Вопрос:

У меня есть таблица следующего вида

 |GroupID | UserID |
--------------------
|1       | 1      |
|1       | 2      |
|1       | 3      |
|2       | 1      |
|2       | 2      |
|3       | 20     |
|3       | 30     |
|5       | 200    |
|5       | 100    |

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

Пользователи имеют возможность покинуть группу и создать новую.

Когда все пользователи покидают группу, в моей таблице больше нет этого groupId.

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

Я хотел бы переработать идентификационные номера групп таким образом, чтобы при вставке новой записи, если группа 4 не используется (как в приведенном выше случае), она присваивалась.

Комментарии:

1. Эти идентификаторы являются суррогатными ключами. Никто не должен их видеть / заботиться об их значениях. Просто оставьте их в покое. Если вы хотите что-то отобразить и вам не нравятся «group345876» или «user9013758235», добавьте поддержку отображаемых имен, удобочитаемых человеком.

2. Моя проблема в том, что 9223372036854775807 звучит как большое число, пока не пройдет несколько лет.

3. Просто перестаньте беспокоиться. Если у вас будет 1 миллиард пользователей, и они будут создавать новый идентификатор каждую секунду каждого часа каждого дня, на исчерпание идентификаторов уйдет почти 300 лет. Если моя математика верна.

Ответ №1:

Есть веские причины не делать этого, но в PostgreSQL это довольно просто. Этот метод — использование generate_series() для поиска пробелов в последовательности — полезен и в других контекстах.

 WITH group_id_range AS (
    SELECT generate_series((SELECT MIN(group_id) FROM groups), 
                           (SELECT MAX(group_id) FROM groups)) group_id
)
SELECT min(gir.group_id)
FROM group_id_range gir
LEFT JOIN groups g ON (gir.group_id = g.group_id)
WHERE g.group_id IS NULL;

Этот запрос вернет NULL, если в таблице «группы» нет пробелов или если в ней вообще нет строк. Если вы хотите использовать это для возврата следующего идентификационного номера группы независимо от состояния таблицы «группы», используйте это вместо этого.

 WITH group_id_range AS (
    SELECT generate_series(
                      (COALESCE((SELECT MIN(group_id) FROM groups), 1)), 
                      (COALESCE((SELECT MAX(group_id) FROM groups), 1))
                  ) group_id
)
SELECT COALESCE(min(gir.group_id), (SELECT MAX(group_id) 1 FROM groups))
FROM group_id_range gir
LEFT JOIN groups g ON (gir.group_id = g.group_id)
WHERE g.group_id IS NULL;