а если PostgreSQL не при чем, то уже все?)

А, ясно. Я подумал, что я что-то пропустил. ;)

файлы клиенту как файлы надо возвращать?

Всем привет. У меня такая задача - сделать web API, для получения файлов до 10 гиг. И делать по файлу поиск. Напримел сколько раз встречается указанное слово в файле. Использую Sinatra (для веб апи на ruby). (и Redis+Sidekiq) Вопрос: - Какие лучшие практики для решения таких задач? - Как лучше делать поиск по большим файлам? - Как сохранять большие файлы?

Текстовые файлы по 10 гиг? И что же там у тебя?

Оффтоп, просто в инете ничего не выдает. Есть ли чат для фотолюбителей?

это уже другой вопрос. (ну например много томов Война и Мир :))

Всем привет. У меня такая задача - сделать web API, для получения файлов до 10 гиг. И делать по файлу поиск. Напримел сколько раз встречается указанное слово в файле. Использую Sinatra (для веб апи на ruby). (и Redis+Sidekiq) Вопрос: - Какие лучшие практики для решения таких задач? - Как лучше делать поиск по большим файлам? - Как сохранять большие файлы?

что подразумевается под поиском? поиск по названию, по контенту, по главе, нужен ли нечеткий, etc., etc.

@Sashka_S варианты: хранить файлы как файлы на файловой системе файлсервера. в хранилке "путь к файлу и имя файла" - идентифицирует документ, все его метаданные например в реляционной базе. FTS прикручивать сбоку. хранить файлы как чанки размазанные по кластеру. ну типа хадуп. вот кстати https://events.yandex.ru/lib/talks/524/

@Komzpa

уже потер

Добрый день, как возможно получить ID вставленных строк? p = plpy.execute('INSERT INTO tables VALUES ....')

INSERT INTO tables VALUES ... RETURNING id

INSERT INTO tables VALUES ... RETURNING id

спасибо)

@Sashka_S варианты: хранить файлы как файлы на файловой системе файлсервера. в хранилке "путь к файлу и имя файла" - идентифицирует документ, все его метаданные например в реляционной базе. FTS прикручивать сбоку. хранить файлы как чанки размазанные по кластеру. ну типа хадуп. вот кстати https://events.yandex.ru/lib/talks/524/

Спасибо за линк!

Да, это выдаётся в EXPLAIN (ANALYZE, ?BUFFERS?).

Проверил уменьшение work_mem ниже определённого порога приводит к резкому появлению lossy Heap Blocks (помимо exact), а также к появлению Rows Removed by Index Recheck. Причём, количество Heap Block начинает делиться поровну между exact и lossy. Само время выполнения увеличивается примерно в 4 раза. Как я понял, в режиме exact вообще не выполняется recheck. Значение work_mem, ниже которого появляется lossy Heap Blocks, зависит от отбираемого в where объёма данных. Другого изменения грубости битовой карты я не обнаружил.

Проверил уменьшение work_mem ниже определённого порога приводит к резкому появлению lossy Heap Blocks (помимо exact), а также к появлению Rows Removed by Index Recheck. Причём, количество Heap Block начинает делиться поровну между exact и lossy. Само время выполнения увеличивается примерно в 4 раза. Как я понял, в режиме exact вообще не выполняется recheck. Значение work_mem, ниже которого появляется lossy Heap Blocks, зависит от отбираемого в where объёма данных. Другого изменения грубости битовой карты я не обнаружил.

Ну так это оно и есть, а чего вы ожидали?

Ожидал хоть чего-то, спасибо за наводку.

Ожидал хоть чего-то, спасибо за наводку.

Эээ... а чего ещё вообще можно ожидать?

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

Кстати, производительность запроса с OR в таком exact-режиме получилась в 2 раза выше, чем с union.

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

Я не понимаю, как. Вроде bitmap либо влезает в work_mem, либо нет...

Кстати, производительность запроса с OR в таком exact-режиме получилась в 2 раза выше, чем с union.

Какого запроса? Можете показать запросы, планы и \d. ?

create table tst as (select hashint4(s) as i, hashint4(s+1) as j from generate_series (1, 268435456, 1) s); create index tst_i on tst using btree(i); create index tst_j on tst using btree(j); select * from tst where i between 1 and 60000000 or j between 1 and 60000000; Как есть, work_mem=70MB: https://explain.depesz.com/s/N0PI Как есть, work_mem=69MB: https://explain.depesz.com/s/3adh Переписать в uinon all: https://explain.depesz.com/s/bEHp Переписать в uinon: https://explain.depesz.com/s/Lju3

Робяты, вопрос по существу. Вызываю postgres из Go. Собственно разницы какой язык нет. Если в Go не вызывать функцию явного старта транзакции, то считается, что транзакция открывается и коммитится после исполнения команды автоматически. Вопрос. А если делать это явно, то это будет 3 вызова СУБД с клиента или их и сейчас три. Просто интересно, может для неявных транзакций в PG есть какой то флаг при отправке команды, что уменьшает 3 вызова до 1го.

create table tst as (select hashint4(s) as i, hashint4(s+1) as j from generate_series (1, 268435456, 1) s); create index tst_i on tst using btree(i); create index tst_j on tst using btree(j); select * from tst where i between 1 and 60000000 or j between 1 and 60000000; Как есть, work_mem=70MB: https://explain.depesz.com/s/N0PI Как есть, work_mem=69MB: https://explain.depesz.com/s/3adh Переписать в uinon all: https://explain.depesz.com/s/bEHp Переписать в uinon: https://explain.depesz.com/s/Lju3

Спасибо! Сейчас попробую посмотреть...

Робяты, вопрос по существу. Вызываю postgres из Go. Собственно разницы какой язык нет. Если в Go не вызывать функцию явного старта транзакции, то считается, что транзакция открывается и коммитится после исполнения команды автоматически. Вопрос. А если делать это явно, то это будет 3 вызова СУБД с клиента или их и сейчас три. Просто интересно, может для неявных транзакций в PG есть какой то флаг при отправке команды, что уменьшает 3 вызова до 1го.

Есть режим автокоммит почти во всех СУБД.

Каждый оператор -- отдельная транзакция, сама коммитится (есил ошибки нет естественно)

https://postgrespro.ru/docs/postgrespro/10/ecpg-sql-set-autocommit

Запрос (roundtrip на сервер) будет один при этом

create table tst as (select hashint4(s) as i, hashint4(s+1) as j from generate_series (1, 268435456, 1) s); create index tst_i on tst using btree(i); create index tst_j on tst using btree(j); select * from tst where i between 1 and 60000000 or j between 1 and 60000000; Как есть, work_mem=70MB: https://explain.depesz.com/s/N0PI Как есть, work_mem=69MB: https://explain.depesz.com/s/3adh Переписать в uinon all: https://explain.depesz.com/s/bEHp Переписать в uinon: https://explain.depesz.com/s/Lju3

Подождите, а ведь это было всё в контексте совсем другого запроса... нет? Там было что-то про выборку одной записи с приоритетом, кажется...

Да, было вот что: SELECT *, CASE WHEN chat_id=‘1111111’ THEN 1 ELSE 2 END AS pr FROM users WHERE chat_id = '111111' OR username = 'alex' ORDER BY pr LIMIT 1;

Робяты, вопрос по существу. Вызываю postgres из Go. Собственно разницы какой язык нет. Если в Go не вызывать функцию явного старта транзакции, то считается, что транзакция открывается и коммитится после исполнения команды автоматически. Вопрос. А если делать это явно, то это будет 3 вызова СУБД с клиента или их и сейчас три. Просто интересно, может для неявных транзакций в PG есть какой то флаг при отправке команды, что уменьшает 3 вызова до 1го.

По умолчанию в PostgreSQL autocommit. При этом, естественно, roundtrip-ов в три раза меньше по сравнению с отдельными BEGIN; SELECT ... ; COMMIT; А вот что там использует Go, лучше проверить (да хотя бы посмотрев в логах PostgreSQL при log_statement = all).

Спасибо!

так и предполагал конечно,что раундтрипов меньше при автокоммите :(

Ну не обязательно

Почему спрашиваю. Оптимистичную блокировку через обновления поля row_version делаю. Раньше передавал в условие было типа UPDATE persons SET name =$1, row_version = row_version + 1 WHERE ID = $2 AND row_version = $3 RETURNING row_version;

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

поэтому решил обернуть все в транзакцию и если при RETURNING row_version больше на 2 и выше (в идеале должна быть выше на 1), значит запись была изменена в другой транзакции. И тогда текущую можно откатывать и предупреждать пользователя.

поэтому решил обернуть все в транзакцию и если при RETURNING row_version больше на 2 и выше (в идеале должна быть выше на 1), значит запись была изменена в другой транзакции. И тогда текущую можно откатывать и предупреждать пользователя.

Ну можно же просто проверить, сколько записей обновилось, А тразнакцию "откатит" оно само (просто на самом деле ничего не изменит твой UPDATE)

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

Ну а что ж тебе откатывать, если её не произошло? Ты только сообщение дашь, что была ошибка, и заново всё начнёшь...

откатывать, но быть уверенным что запись с заданным ID в таблице есть.

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

Да оптимистичная блокировка вообще очень простая штука очень ограниченного применения. Т.е. если вы получаете "UPDATE 0" — произошла ошибка, и всё тут.

скорей всего за 1 update не выйдет узнать, что строка могла быть удалена

Вот меня это и печалит. А три раундтрипа, не знаю какой даст оверхед. Надо будет эксперимент провести.

может вам подойдет merge(upsert)?

какая должна быть логика в случае если данные были удалены в 1ой сессии, а во 2ой хотелось их апдейтить

А зачем вам что-то узнавать? В любом случае, если в ответ на UPDATE вы получаете, что обновлено ноль записей — это ошибка, и всё.

Ярослав, там ведь в чем дело. Ага, запись обновлена в другой сессии. Тогда уведомляем пользователя и предлагаем варианты: 1) пофиг пляшем, записываем поверх нашу версию (и тогда в след улетевшем UPDATE пропадает условие на сверку row_version) или 2) считайка мне то что есть в БД. Гляну, что изменили.

Ярослав, там ведь в чем дело. Ага, запись обновлена в другой сессии. Тогда уведомляем пользователя и предлагаем варианты: 1) пофиг пляшем, записываем поверх нашу версию (и тогда в след улетевшем UPDATE пропадает условие на сверку row_version) или 2) считайка мне то что есть в БД. Гляну, что изменили.

Вот первого варианта не дожно быть (и вы уже сами видите, почему), только второй. (Я поэтому и писал, что это штука очень ограниченного применения.) В какой ситуации вам кажется "нормальным" первый вариант?

Прямо с козырей зашли :) да, согласен 1 вариант редкий. Помню использовали, на пред проектах, но в каких кейсах - вопрос.

Прямо с козырей зашли :) да, согласен 1 вариант редкий. Помню использовали, на пред проектах, но в каких кейсах - вопрос.

Дело в том, что это "чистый" lost update. :(

ERROR: S client not available

Прямо с козырей зашли :) да, согласен 1 вариант редкий. Помню использовали, на пред проектах, но в каких кейсах - вопрос.

Кстати, только заметил: > UPDATE persons SET name =$1, row_version = row_version + 1 WHERE ID = $2 AND row_version = $3 RETURNING row_version; Это вообще что? ;) OCC, по-хорошему, делается не так. Вы должны либо брать row_version из какой-то sequence (при каждом UPDATE), либо вообще использовать "классический" метод с сохранением и указанием в последущем UPDATE всех полей записи. Иначе эта шутка подходит только для UPDATE. :(

версия обычно берется из записи, которую селектили до этого, дальше идет апдейт любого поля(зачем все?) & version += 1 where version = oldversion

Where version=$3 это как раз, то что получили раннее при select

версия обычно берется из записи, которую селектили до этого, дальше идет апдейт любого поля(зачем все?) & version += 1 where version = oldversion

Нет, минуточку. :) Что такое "обычно"? А инициализируется row version при создании записи как?

0 или 1

не нужен никакой сиквенс

пользователь всегда открывает перед редактированием?) иначе это кто-то шалит в бд напрямую

Нет, минуточку. :) Что такое "обычно"? А инициализируется row version при создании записи как?

пользователь всегда открывает перед редактированием?) иначе это кто-то шалит в бд напрямую

Да, всегда. Надо ведь поля проинициализировать

В форме

0 или 1

Так это работает только для PK, основанного на sequence... Смотрите: INSERT INTO users(id, name, birth_date, version) VALUES (1, 'Вася', '2012-01-23', 1); Далее, первая часть OCC, клиент 1 (выбирает запись и "уходит"): SELECT * FROM users WHERE id = 1; — запоминаем: Id = 1 и rowversion = 1 Тем временем, другая транзакция: BEGIN; DELETE FROM users WHERE id = 1; — ошиблись, это был не Вася INSERT INTO users(id, name, birth_date, version) VALUES (1, 'Петя', '2008-01-23', 1); COMMIT; Возвращается клиент 1, чтобы обновить Васину дату рождения (использует сохранённые значения): UPDATE users SET birth_date = '2000-08-08' WHERE id = 1 AND rowversion = 1; — UPDATE 1 Всё успешно, изменили дату рождения... только вот не того человека. Если пример кажется надуманным, представьте, что это строки заказа, и кто-то считывает и хочет поменять первую, а тем временем кто-то другой её удаляет и вставляет совсем другую...

версия обычно берется из записи, которую селектили до этого, дальше идет апдейт любого поля(зачем все?) & version += 1 where version = oldversion

> дальше идет апдейт любого поля(зачем все?) Я имел в виду, что в WHERE этого UPDATE указываются все поля (тогда rowversion вообще не нужен), неточно выразился, извините.

зачем будут удалять запись с васей, а не просто update? почему есть явная привязка к id? если будут инсертить вновь, то sequence.nextval и будет ошибка в первой сессии т.к. rows_updated = 0? Предположим, что там не id, а уникальный номер заказа, то тогда возвращаемся к первому вопросу)

зачем будут удалять запись с васей, а не просто update? почему есть явная привязка к id? если будут инсертить вновь, то sequence.nextval и будет ошибка в первой сессии т.к. rows_updated = 0? Предположим, что там не id, а уникальный номер заказа, то тогда возвращаемся к первому вопросу)

> зачем будут удалять запись с васей Потому что мне так захотелось. Я что, не имею права менять данные удобным мне способом? ;) > почему есть явная привязка к id? Ну, это какой-нибудь табельный номер в каком-то бумажном документе, например. > если будут инсертить вновь, то sequence.nextval и будет ошибка в первой сессии т.к. rows_updated = 0? Так вы же писали, что инициализируется 0 или 1? > Предположим, что там не id, а уникальный номер заказа, то тогда возвращаемся к первому вопросу) Строки заказа. Т.е. 1, 2, 3...

> Так вы же писали, что инициализируется 0 или 1? я к тому, что той записи уже не будет, т.к её удалили и ей присвоился новый ид => rows_updated = 0 => error(запись изменена) > Потому что мне так захотелось. Я что, не имею права менять данные удобным мне способом? ;) при желании можно ещё и чексумму вычислять, если уж так хочется. Тогда не будет апдейта Васи вместо Пети. Если хочется что-то сломать, то это всегда можно сделать)

> Так вы же писали, что инициализируется 0 или 1? я к тому, что той записи уже не будет, т.к её удалили и ей присвоился новый ид => rows_updated = 0 => error(запись изменена) > Потому что мне так захотелось. Я что, не имею права менять данные удобным мне способом? ;) при желании можно ещё и чексумму вычислять, если уж так хочется. Тогда не будет апдейта Васи вместо Пети. Если хочется что-то сломать, то это всегда можно сделать)

> я к тому, что той записи уже не будет Да, но это работает только в том случае, если ключ таблицы использует sequence. А если нет — не работает. Это всё, что я хотел сказать. :) > при желании можно ещё и чексумму вычислять, если уж так хочется. Тогда не будет апдейта Васи вместо Пети. С некоторой вероятностью. ;) > Если хочется что-то сломать, то это всегда можно сделать) Я что-то не вижу, каким образом можно сломать схему с sequence (или ключом на основании sequence). Как это сделать?

> Я что-то не вижу, каким образом можно сломать схему с sequence (или ключом на основании sequence). Как это сделать? А какая была ваша схема? использовать sequence для генерации чисел в row_version? Я предпочел бы использовать время последнего апдейта, чем вам не естественный sequence

Есть ли что-то поинтереснее с примерами по Plpython, нежели офф. документация?

> Я что-то не вижу, каким образом можно сломать схему с sequence (или ключом на основании sequence). Как это сделать? А какая была ваша схема? использовать sequence для генерации чисел в row_version? Я предпочел бы использовать время последнего апдейта, чем вам не естественный sequence

Да, sequence либо в rowversion, либо в PRIMARY KEY таблицы (естественно, запретить указывать то и другое вручную, правами доступа + триггерами, или ALWAYS GENERATED).

Юнит тестирование скриптов баз данных https://habr.com/post/417569/ https://tsqlt.org/

Гарантирует ли постгрес то, что при вставке множества значений Insert into table (...) values (...), (...)... returning "Id"; Он вернет id вставленных в том порядке, в котором они перечислены?

порядок без order by никто не гарантирует

@Komzpa речь же про инсерт. какой там order by?

оборачиваешь в cte, делаешь из него select и там order by