решать естественно можно разными способами

pgadmin или ems sql manager

pgadmin скорее

вот интересное задание на постгрес собеседование - есть довольно большая постгрес база, в которой каким-то образом появились полные дубликаты записей (в том числе одинаковые id) нужно от них избавиться

Ха. У меня есть такая бд на проде. ? Аналитики до сих пор не могут решить какие из 2х записей лишняя.

pgadmin скорее

для отладки в pgadmin надо сервер настраивать, для ems manager не нужно, но он платный

вот интересное задание на постгрес собеседование - есть довольно большая постгрес база, в которой каким-то образом появились полные дубликаты записей (в том числе одинаковые id) нужно от них избавиться

Был баг в версии 9.2, когда строки дублировались даже при наличии уникального индекса. Вот это была задачка )

по-моему, единственный рабочий вариант сейчас pldebugger + pgadmin

Спасибо, буду гуглить

Был баг в версии 9.2, когда строки дублировались даже при наличии уникального индекса. Вот это была задачка )

Это вот вопрос, как возникают такие записи. MVCC, видимость строки, снимки, ну и вот это фсйо...

Это вот вопрос, как возникают такие записи. MVCC, видимость строки, снимки, ну и вот это фсйо...

А ещё админ плохой в регионе, который держит бд и приложение на одном серваке. И когда приложение кушает чуть больше памяти бд падает. И он не собирается это менять.

Вот причина дублей

вот интересное задание на постгрес собеседование - есть довольно большая постгрес база, в которой каким-то образом появились полные дубликаты записей (в том числе одинаковые id) нужно от них избавиться

есть доступ до alter table?

есть доступ до alter table?

есть полный доступ к бд если это интересует

и да, как вариант - завести столбец с индексом, но это показывает что человек не знает про ctid

вообще правильного ответа нет, просто смотрят какие способы человек предлагает

и да, как вариант - завести столбец с индексом, но это показывает что человек не знает про ctid

ctid - хм... это же что-то по поподу версий строки. Я не прав?

ну я бы (при текущих знаниях): а) создал бы доп поле (например unique_ID) в таблице с foreign на PK той же таблицы б) написал бы Проуедуру (или UPDATE если получится) на определение дубликатов(group by по правилам) и обновление поля uique_ID

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

Хотя полные дубликаты - это идентичные записи же.

ctid - хм... это же что-то по поподу версий строки. Я не прав?

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

ну я бы (при текущих знаниях): а) создал бы доп поле (например unique_ID) в таблице с foreign на PK той же таблицы б) написал бы Проуедуру (или UPDATE если получится) на определение дубликатов(group by по правилам) и обновление поля uique_ID

Ну пока такой в общем вариант при "сомнительной" уникальности, когда не уверен что все верно соберется

хотя нет в обоих случаях так сделал бы. Можно ще конечно сделать абсолютную копию таблицы с добавленным уникальным индексом и добавлять по правилу INSERT ON CONFLICT DO NOTHING

Но меня больше беспокоит PK

А если он где-то связан?

тогда бездумно собирать дубли нельзя и перегонять из таблицы в таблицу не получится

Ну пока такой в общем вариант при "сомнительной" уникальности, когда не уверен что все верно соберется

вариантов много, можно тупо сделать дамп таблицы и восстановить уже без дублей

вариантов много, можно тупо сделать дамп таблицы и восстановить уже без дублей

Вот такой вариант в голову не пришёл. :)

Есть ещё хитрый вариант ? Заюзать запросы в PG, а обработку написать на бэкенде :)

Спасива вам мне запис dbf??

Ребят, подскажите пожалуйста в каком направлении двигаться из таблицы повытягивать записи с максимальным Value, сгруппированные по external_id, сравнивая только те значения, в которых timestamp'ы не старее от самой верхней(новой) записи в текущей группе по времени чем на 30 дней external_id value timestamp ------------- ------- ------------------- 1 10 2016-10-01 1 5 2016-09-01 3 8 2016-10-01 3 10 2016-09-01 3 12 2016-08-01в итоге что бы из этой таблицы вышло external_id value timestamp ------------- ------- ------------------- 1 10 2016-10-01 3 10 2016-09-01

select external_id, max(value), max(timestamp) from table group by external_id having timestamp >= ...; не? а, не, сорри

Там логика чет больно сложная

целый день думаю

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

а если вложенный запрос написать

?

Оконные функции вам должны помочь

select external_id, max(value), max(timestamp) from table group by external_id having timestamp >= ...; не? а, не, сорри

то есть сначала вычислить самые верхние как здесь написано. А в запросе выше - оринетироваться на значения этой таблицы

то есть сначала вычислить самые верхние как здесь написано. А в запросе выше - оринетироваться на значения этой таблицы

тогда having не нужен, правильно?

ну как бы да. 1 сек

точнее час

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

create table test as select (random()*5)::int as external_id, (random()*100)::int as value, '2017-01-01'::date + interval '1 day' * (random()*355)::int as "timestamp" from generate_series(1, 1000) t; with prepared as ( select external_id, value, "timestamp", max("timestamp") over(partition by external_id) - interval '30 days' as max_timestamp from test group by external_id, value, "timestamp" ) select external_id, max(value) from prepared where "timestamp" > max_timestamp group by 1;

Если я правильно понял условие.

Мне кажется да, секунду, я попробую, но в любом случае спасибо за потраченное время

with test (external_id, value, timestamp) as ( values (1,10,'2016-10-01'::timestamp), (1,5,'2016-09-01'::timestamp), (3,8,'2016-10-01'::timestamp), (3,10,'2016-09-01'::timestamp), (3,12,'2016-08-01'::timestamp) ), max_stamp as ( select external_id, max(timestamp) as timestamp from test group by external_id ), max_value as ( select r.external_id, max(r.value) as value from ( select t.external_id, t.value from test as t join max_stamp as g using (external_id) where t.timestamp between (g.timestamp - interval '30 days') and g.timestamp ) as r group by r.external_id ) select external_id, value, timestamp from test where (external_id, value) = any ( select external_id, value from max_value );

Уважаемые программисты, подскажите, где можно найти dba postgres и программист тыщ так за 160-180? И чтоб на удаленку не сбежал? Такие существуют?

а dba postgres или программист?

2 вакансии, не могу закрыть

Уважаемые программисты, подскажите, где можно найти dba postgres и программист тыщ так за 160-180? И чтоб на удаленку не сбежал? Такие существуют?

территориально где?

Москва, Калужская

Москва, Калужская

какие ограничения по вакансии?

Манго )

"хочу всё и сразу"

Ну, полагаю, только 18+))). Главное для dba, чтоб администрировал постгрес.

"хочу всё и сразу"

)))

Ага, Андрей, Манго. На хх мало спецов, их вообще мало

Ну, полагаю, только 18+))). Главное для dba, чтоб администрировал постгрес.

на описание "хотелок" от нанимателя посмотреть можно?

Приложите ссылки на сами вакансии с хх.ру, пожалуйста

В манго найти адекватного человека, и чтобы не сбежал? Я хочу это видеть!

Пару лет назад отправлял им резюм, получил отлуп. Полез в интернеты читать, что за компания. Порадовался, что получил отлуп.

а че за манго?

очевидно https://www.mango-office.ru/

Блин, вот вы шустрые, мои запросы медленно работают))). Оптимизация нужна))). Https://hh.ru/vacancy/20608668

Сейчас вторую скину.

Всем привет! Подскажите пожалуйста как разрулить ситуацию с разворачиванием json по колонкам Исходные данные такие: CREATE TYPE mkey AS ( mtype int4, mid int4 ); CREATE TABLE tbl ( field_1 varchar, field_2 int4, mkeys json ); INSERT INTO tbl VALUES (1,1,'[{"mtype":1, "mid":11},{"mtype":1, "mid":22}]'::json); —------------------- Нужно получить строки таблицы "сJOINненые"" со значениями массива из колонки mkeys::json Те нужно получить вот так: field_1 | field_2 | mtype | mid —-------+---------+-------+---— 1 | 1 | 1 | 11 1 | 1 | 1 | 22 Пробую: SELECT * FROM tbl a LEFT JOIN json_array_elements(a.mkeys) ON TRUE; field_1 | field_2 | mkeys | value —-------+---------+-----------------------------------------------+---------------------— 1 | 1 | [{"mtype":1, "mid":11},{"mtype":1, "mid":22}] | {"mtype":1, "mid":11} 1 | 1 | [{"mtype":1, "mid":11},{"mtype":1, "mid":22}] | {"mtype":1, "mid":22} Далее пробую "разложить" по колонкам SELECT * FROM tbl a LEFT JOIN json_populate_record(null::mkey, json_array_elements(a.mkeys)) ON TRUE; но получаю ошибку ( [Err] ERROR: set-valued function called in context that cannot accept a set

ERROR: S client not available

Черт, вакансии dba нет в открытом доступе. Кому интересно, напишите в личку почту, скину. Обещаю никогда не спамить. Спасибо)))

Всем привет! Подскажите пожалуйста как разрулить ситуацию с разворачиванием json по колонкам Исходные данные такие: CREATE TYPE mkey AS ( mtype int4, mid int4 ); CREATE TABLE tbl ( field_1 varchar, field_2 int4, mkeys json ); INSERT INTO tbl VALUES (1,1,'[{"mtype":1, "mid":11},{"mtype":1, "mid":22}]'::json); —------------------- Нужно получить строки таблицы "сJOINненые"" со значениями массива из колонки mkeys::json Те нужно получить вот так: field_1 | field_2 | mtype | mid —-------+---------+-------+---— 1 | 1 | 1 | 11 1 | 1 | 1 | 22 Пробую: SELECT * FROM tbl a LEFT JOIN json_array_elements(a.mkeys) ON TRUE; field_1 | field_2 | mkeys | value —-------+---------+-----------------------------------------------+---------------------— 1 | 1 | [{"mtype":1, "mid":11},{"mtype":1, "mid":22}] | {"mtype":1, "mid":11} 1 | 1 | [{"mtype":1, "mid":11},{"mtype":1, "mid":22}] | {"mtype":1, "mid":22} Далее пробую "разложить" по колонкам SELECT * FROM tbl a LEFT JOIN json_populate_record(null::mkey, json_array_elements(a.mkeys)) ON TRUE; но получаю ошибку ( [Err] ERROR: set-valued function called in context that cannot accept a set

сделайте сначала cte с отдельным set из json_array_elements, потом уже выборку с populate

сделайте сначала cte с отдельным set из json_array_elements, потом уже выборку с populate

Спасибо, но не совсем понятно что нужно сделать )

Спасибо, но не совсем понятно что нужно сделать )

сейчас пример напишу

сейчас пример напишу

спасибо

спасибо

немного напутал, в вашем случае можно через recordset: WITH tbl (field1, field2, mkeys) AS (VALUES ('1', 1,'[{"mtype":1, "mid":11},{"mtype":1, "mid":22}]'::JSON)) SELECT tbl.field1, tbl.field2, r.* FROM tbl, json_to_recordset (tbl.mkeys) AS r (mtype INT, mid INT)

немного напутал, в вашем случае можно через recordset: WITH tbl (field1, field2, mkeys) AS (VALUES ('1', 1,'[{"mtype":1, "mid":11},{"mtype":1, "mid":22}]'::JSON)) SELECT tbl.field1, tbl.field2, r.* FROM tbl, json_to_recordset (tbl.mkeys) AS r (mtype INT, mid INT)

это работает! еще раз спасибо

это работает! еще раз спасибо

или json_populate_recordset (null::mkey, mkeys) вместо json_to_recordset (mkeys) AS ...

привет, а никто не знает как pg large object хранит? в relation также? https://momjian.us/main/writings/pgsql/aw_pgsql_book/node95.html

в смысле если я создам table (name CHAR(30), image OID) для image будет использоваться один толстый relation как toast?

да с сжатием, как toast

да с сжатием, как toast

Точно? ПО-моему внешние файлы... могу ошибаться, но все же

Именно поэтому и bytea vs oid

Хм .. а можно кто подсказать, можно как-то выяснить реальное количество памяти, которое использует postgres?

Точно? ПО-моему внешние файлы... могу ошибаться, но все же

postgres=# INSERT INTO data VALUES ('big', lo_import('/test.file.big')); INSERT 0 1 postgres=# INSERT INTO data VALUES ('big2', lo_import('/test.file.big')); INSERT 0 1 postgres=# /test.file.big - 1Gb который хорошо жметься postgres=# INSERT INTO data VALUES ('big3', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big4', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big5', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big6', lo_import('/test.file.big')); INSERT 0 1 postgres=# [root@localhost ~]# ls -lah /var/lib/pgsql/9.6/data/base/13275/2613* -rw-------. 1 postgres postgres 1.0G Jul 13 08:40 /var/lib/pgsql/9.6/data/base/13275/2613 -rw-------. 1 postgres postgres 421M Jul 13 08:42 /var/lib/pgsql/9.6/data/base/13275/2613.1 -rw-------. 1 postgres postgres 384K Jul 13 08:42 /var/lib/pgsql/9.6/data/base/13275/2613_fsm -rw-------. 1 postgres postgres 0 Jul 13 08:29 /var/lib/pgsql/9.6/data/base/13275/2613_vm

postgres=# INSERT INTO data VALUES ('big', lo_import('/test.file.big')); INSERT 0 1 postgres=# INSERT INTO data VALUES ('big2', lo_import('/test.file.big')); INSERT 0 1 postgres=# /test.file.big - 1Gb который хорошо жметься postgres=# INSERT INTO data VALUES ('big3', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big4', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big5', lo_import('/test.file.big')); INSERT 0 1 postgres=# postgres=# INSERT INTO data VALUES ('big6', lo_import('/test.file.big')); INSERT 0 1 postgres=# [root@localhost ~]# ls -lah /var/lib/pgsql/9.6/data/base/13275/2613* -rw-------. 1 postgres postgres 1.0G Jul 13 08:40 /var/lib/pgsql/9.6/data/base/13275/2613 -rw-------. 1 postgres postgres 421M Jul 13 08:42 /var/lib/pgsql/9.6/data/base/13275/2613.1 -rw-------. 1 postgres postgres 384K Jul 13 08:42 /var/lib/pgsql/9.6/data/base/13275/2613_fsm -rw-------. 1 postgres postgres 0 Jul 13 08:29 /var/lib/pgsql/9.6/data/base/13275/2613_vm

Да, не файлы, но и не TOAST. В pg_largeobject хранится

но жмет

накой

кто просил

кто просил

Че, правда жмет? Однако

Хм .. а можно кто подсказать, можно как-то выяснить реальное количество памяти, которое использует postgres?

Максимум - grep VmPeak /proc/PID/status

он наверно про разделяемую беспокоиться

Хм .. а можно кто подсказать, можно как-то выяснить реальное количество памяти, которое использует postgres?

а вы для чего спрашиваете? беспокоит "большой" чекпоинтер?

а вы для чего спрашиваете? беспокоит "большой" чекпоинтер?

Я довольно слабо знаком с системой логики работы памяти в линуксе, а у нас тут возникли проблемки. Вот у меня вроде реальной памяти 32Г, а Commit Limit всего 19 и не могу понять, куда делать остальная память) Пока предполагаю, что все укатилось в shared + mapped, но фиг его знает. Ну и интересно, как понять, сколько всего постгрес сьел.

Да, не файлы, но и не TOAST. В pg_largeobject хранится

угу, отвратительно, это один relation в котором все lo храняться

хотел бы побить на кучку не гиганстких relation

Да, не файлы, но и не TOAST. В pg_largeobject хранится

postgres=# \d+ pg_largeobject Table "pg_catalog.pg_largeobject" Column | Type | Modifiers | Storage | Stats target | Description —------+---------+-----------+----------+--------------+------------- loid | oid | not null | plain | | pageno | integer | not null | plain | | data | bytea | not null | extended | | Indexes: "pg_largeobject_loid_pn_index" UNIQUE, btree (loid, pageno) блин, тотже bytea

Я довольно слабо знаком с системой логики работы памяти в линуксе, а у нас тут возникли проблемки. Вот у меня вроде реальной памяти 32Г, а Commit Limit всего 19 и не могу понять, куда делать остальная память) Пока предполагаю, что все укатилось в shared + mapped, но фиг его знает. Ну и интересно, как понять, сколько всего постгрес сьел.

а покажите /proc/meminfo ?

а покажите /proc/meminfo ?

https://pastebin.com/3tNa236H

https://pastebin.com/3tNa236H

»> (6489688.0 * 1024) / 1024 / 1024 / 1024 6.1890487670898438 6Gb shared buffers

остальное - обычная выделенная память, которую можно посчитать по процессам