Как вставить много строк тегов на основе входного массива?
У меня есть таблица сообщений, таблица хэштегов и таблица, связывающая сообщения с хэштегами, например:
CREATE TABLE posts(
id SERIAL PRIMARY KEY,
post_data VARCHAR(128) NOT NULL
);
CREATE TABLE hashtags (
id SERIAL PRIMARY KEY,
value VARCHAR(128) NOT NULL
);
CREATE TABLE post_hashtags(
id SERIAL PRIMARY KEY,
post_id INTEGER NOT NULL REFERENCES posts(id),
hashtag_id INTEGER NOT NULL REFERENCES hashtag(id)
);
INSERT INTO posts(post_data) VALUES ('post1');
INSERT INTO hashtags (value) VALUES ('hashtag1'), ('hashtag2'), ('hashtag3');
-- At this point I might want to add links between post1 and existing hashtags as well as possibly new ones
Всякий раз, когда пользователь публикует сообщение, используя несколько хэш-тегов, я хочу:
- Создайте новую строку в
postsи получите идентификатор. - Создайте новые строки в
hashtagsдля несуществующих хэштегов и получите их идентификаторы. - Для каждого хэштега создайте строку
post_hashtagsс идентификаторами публикации и хэштега.
Прямо сейчас я могу справиться с этим на стороне сервера, но это, очевидно, ужасная производительность: вставить сообщение и получить идентификатор; для каждого хештега вставьте в hashtags, если его нет, получите идентификатор; затем вставьте в post_hashtags. Множество вызовов к БД, которые, я думаю, можно было бы упростить, но на данный момент мне не хватает навыков SQL.
Хорошо, я добавил код SQL, который можно запустить для настройки состояния. Меня будет интересовать любой запрос, независимо от версии Postgres. Как я уже упоминал в посте, у меня есть только серверный код, но пока нет SQL.
Столбец id в post_hashtags не обязателен; однако для (post_id, hashtag_id) должно быть уникальное ограничение, чтобы исключить избыточные строки.
Ответы 1
Вы можете сделать это безопасно и эффективно с помощью одного запроса, используя изменяющие данные CTE и UPSERT для тегов.
Конечно, вам нужно ограничение UNIQUE или PRIMARY KEY для hashtags (value). (И у вас должен быть один на post_hashtags (post_id, hashtag_id).)
WITH input(post_data, tags) AS ( -- provide single data row with array of tags
VALUES ('post2', '{hashtag1, hashtag2, hashtag4}'::text[]) -- single post!
)
, tag_set AS ( -- unnest tags - may be empty/missing (?)
SELECT unnest(i.tags) AS value
FROM input i
)
, ins_p AS (
INSERT INTO posts (post_data)
SELECT i.post_data
FROM input i
RETURNING id AS post_id
)
, ins_h AS (
INSERT INTO hashtags (value)
SELECT t.value
FROM tag_set t
WHERE NOT EXISTS (SELECT FROM hashtags h WHERE h.value = t.value) -- optional to avoid burning lots of serial IDs
ON CONFLICT (value) DO NOTHING
RETURNING id AS hashtag_id
)
INSERT INTO post_hashtags
( post_id, hashtag_id)
SELECT p.post_id, t.hashtag_id
FROM ins_p p
CROSS JOIN ( -- only if actual tags were entered
TABLE ins_h -- new tags
UNION ALL
SELECT h.id AS hastag_id -- pre-existing tags
FROM tag_set t
JOIN hashtags h USING (value)
) t
RETURNING *;
Это безопасно даже при большой нагрузке на одновременную запись.
Остаются два возможных крайних случая:
Параллельная транзакция может создать тот же новый хэштег, но затем откатиться, что приведет к отсутствию записи в таблице
hashtagsдля этого запроса, что приведет к возникновению исключения.Ранее существовавший хэштег может быть удален в ходе параллельной транзакции между его поиском здесь и вставкой в
post_hashtags. Опять же, вызывая исключение. Крайне маловероятно, но возможно.
Вы можете просто повторно запустить этот запрос, если возникнет одна из этих двух маловероятных проблем.
Или вы охватываете оба крайних случая в одном запросе. Я не стал заходить так далеко. Инструкции и подробное объяснение смотрите здесь:
Связанный:
По крайней мере, предоставьте правильные определения таблиц, вашу версию Postgres и образец ввода. Кроме того, все, что было до сих пор, даже если это не работает.