Как узнать, изменилась ли запись в Postgres

У меня есть вопрос типа "upsert"... но я хочу высказать его, потому что он немного отличается от всего, что я читал в stackoverflow.

Основная проблема.

Я работаю над переходом с mysql на PostgreSQL 9.1.5 (размещенный на Heroku). В связи с этим мне нужно каждый день импортировать несколько CSV-файлов. Некоторые данные являются информацией о продажах и почти гарантированно являются новыми и должны быть вставлены. Но другие части данных почти гарантированно будут одинаковыми. Например, файлы csv (обратите внимание на множественное число) будут содержать информацию о POS (точке продажи). Это редко меняется (и, скорее всего, только через дополнения). Затем идет информация о продукте. Товаров около 10 000 (подавляющее большинство останется без изменений, но возможны как дополнения, так и обновления).

Последний элемент (но важный) заключается в том, что у меня есть требование предоставить контрольный журнал/информацию для любого данного элемента. Например, если я добавляю новую запись POS, мне нужно иметь возможность отследить ее до файла, в котором она была найдена. Если я изменяю код UPC или описание продукта, мне нужно иметь возможность отследить ее обратно. к импорту (и файлу), откуда пришло изменение.

Решение, которое я обдумываю.

Поскольку данные предоставляются мне через CSV, я работаю над идеей, что COPY будет лучшим/самым быстрым способом. Структура данных в файлах не совсем та, что у меня в базе данных (т.е. конечный пункт назначения). Итак, я копирую их в таблицы промежуточной схемы, которые соответствуют CSV (примечание: одна схема для каждого источника данных). Таблицы в промежуточных схемах будут иметь триггеры перед вставкой строки. Эти триггеры могут решить, что делать с данными (вставить, обновить или игнорировать).

Для таблиц, которые, скорее всего, будут содержать новые данные, он попытается вставить их первыми. Если запись уже есть, то она вернет NULL (и остановит вставку в промежуточную таблицу). Для таблиц, которые редко изменяются, он будет запрашивать таблицу и проверять, найдена ли запись. Если это так, то мне нужен способ увидеть, изменилось ли какое-либо из полей. (потому что помните, мне нужно показать, что запись была изменена путем импорта x из файла y). Я, очевидно, могу просто создать код и протестировать каждый столбец. Но искал что-то более «красноречивое» и более удобное в обслуживании.

В каком-то смысле я комбинирую систему импорта с системой контрольного журнала. Итак, изучая журналы аудита, я просмотрел следующую статью wiki.postgresql.org. Кажется, что hstore может быть хорошим способом получения изменений (и возможностью легко игнорировать некоторые столбцы в таблице, которые не важны, например, «last_modified»)

Я примерно на 90% уверен, что все это будет работать... Я создал несколько тестовых таблиц и т. д. и поэкспериментировал с ними.

Мой вопрос?

Это лучший, более удобный способ выполнения этой задачи по поиску, возможно, 3 записей из 10 КБ, которые требуют изменения в базе данных. Я, конечно, мог бы написать скрипт на Python (или что-то еще), который читает файл и пытается выяснить, что делать с каждой записью, но это кажется ужасно неэффективным и приведет к большому количеству циклов.

Несколько заключительных вещей:

  1. У меня нет контроля над входными файлами. Я был бы рад, если бы они прислали мне только дельты, но они этого не делают, и это полностью вне моего контроля или влияния.
  2. Система растет, и, вероятно, будут добавлены новые источники данных, которые значительно увеличат объем обрабатываемых данных (поэтому я стараюсь поддерживать эффективность)
  3. Я знаю, что это нехороший, простой вопрос SO (например, «как отсортировать список в python»), но я считаю, что одна из замечательных особенностей SO заключается в том, что вы можете задавать трудные вопросы, и люди будут делиться своими мыслями о том, как они думают лучший способ решить это.

file-io postgresql upsert postgresql-9.1 postgresql-copy
person David S    schedule 19.09.2012    source источник
comment
Два (последних) вопроса: 1) у вас есть удаления или ввод инкрементальный? 2) могут ли поставщики данных гарантировать стабильные ключи (без обновлений ключей)?   -  person wildplasser    schedule 21.09.2012
comment
Это зависит от источника данных и типа данных. Это определенно ситуация, когда мне, вероятно, следует занять оборонительную позицию при обработке данных и быть готовым практически ко всему. Тем не менее, я думаю, что у меня могут быть удаления (но редко), и я считаю, что ключи должны быть стабильными (другими словами, идентификатор записи POS должен оставаться одинаковым между загрузками).   -  person David S    schedule 21.09.2012

Ответы (1)


arrow_upward
7
arrow_downward

У меня много подобных операций. Я делаю COPY для временных промежуточных таблиц:

CREATE TEMP TABLE target_tmp AS
SELECT * FROM target_tbl LIMIT 0;  -- only copy structure, no data

COPY target_tmp FROM '/path/to/target.csv';

Для производительности запустите ANALYZE - temp. таблицы не анализируются автоочисткой!

ANALYZE target_tmp;

Также для повышения производительности можно даже создать один или два индекса во временной таблице или добавить первичный ключ, если данные позволяют это сделать.

ALTER TABLE ADD CONSTRAINT target_tmp_pkey PRIMARY KEY(target_id);

Вам не нужна производительность для небольшого импорта.

Затем используйте полный набор команд SQL для переваривания новых данных.
Например, если первичный ключ целевой таблицы — target_id ..

Может DELETE чего больше нет?

DELETE FROM target_tbl t
WHERE NOT EXISTS (
   SELECT 1 FROM target_tmp t1
   WHERE  t1.target_id = t.target_id
);

Затем UPDATE то, что уже есть:

UPDATE target_tbl t
SET    col1 = t1.col1
FROM   target_tmp t1
WHERE  t.target_id = t1.target_id

Чтобы избежать пустых ОБНОВЛЕНИЙ, просто добавьте:

...
AND    col1 IS DISTINCT FROM t1.col1; -- repeat for relevant columns

Или, если актуальна вся строка:

...
AND    t IS DISTINCT FROM t1;         -- check the whole row

Тогда INSERT что нового:

INSERT INTO target_tbl(target_id, col1)
SELECT t1.target_id, t1.col1
FROM   target_tmp t1
LEFT   JOIN target_tbl t USING (target_id)
WHERE  t.target_id IS NULL;

Очистите, если ваш сеанс продолжается (временные таблицы автоматически удаляются в конце сеанса):

DROP TABLE target_tmp;

Или используйте ON COMMIT DROP или аналогичный с CREATE TEMP TABLE.
Код не тестировался, но должен работать в любой современной версии PostgreSQL, за исключением опечаток.

person Erwin Brandstetter    schedule 19.09.2012
comment
Спасибо за ответ. Хороший пример/деталь; Я уверен, что это поможет некоторым людям. И это очень близко к тому, что мне нужно сделать. Единственное требование, которое меня беспокоит, - это отслеживание идентификатора пакета импорта, который фактически изменил запись. Другими словами, я не хочу всегда сразу перезаписывать/обновлять запись, если она существует - только обновлять, если есть изменения. Я полагаю, что мог. А затем просто создайте триггер обновления в таблице и выясните, действительно ли запись изменилась... если да, то добавьте запись контрольного журнала. Кажется разумным? Есть лучший способ? - person David S; 20.09.2012
comment
@DavidS: Это может быть проще, чем вы ожидаете. Немного добавил в раздел UPDATE. В любом случае, почти всегда рекомендуется исключать пустые обновления. Если вам нужен контрольный журнал, я бы посоветовал вам скопировать устаревшие версии (плюс отметку времени) в таблицу истории до того, как вы DELETE / UPDATE. - person Erwin Brandstetter; 20.09.2012
comment
хорошие предложения и спасибо за обновление ответа. Для целей аудита вы предпочитаете копировать в архивную таблицу (полная копия данных + метка времени), а не просто использовать hstore(x.*) и сохранять ее в текстовом поле в таблице типа audit_trail/history? Главное преимущество архивной таблицы в том, что ее легко запрашивать. Основным преимуществом подхода hstore кажется гибкость, если ваша схема изменится в будущем. Я знаю, что это немного не по теме и, вероятно, может быть собственным вопросом, но любопытно, что вы думаете. Спасибо! - person David S; 20.09.2012
comment
@DavidS: Ну, это немного не по теме и действительно зависит от ваших требований. Моим первым импульсом было бы пойти с копией таблицы + временной меткой, потому что это просто, и вы можете использовать все те же запросы для архива и живой таблицы. - person Erwin Brandstetter; 20.09.2012
comment
Избегание обновлений, если ничего не изменится, позволит избежать большого количества ненужных записей (+ грязные строки + триггеры, + очистка). Временные метки + история являются первым выбором. Я не думаю, что временные метки хорошо сочетаются с конструкцией is отличной от .... - person wildplasser; 20.09.2012
comment
@wildplasser - договорились о временных метках. Но я думаю, что вам просто нужно включить каждый столбец, который имеет отношение к делу (как я думаю, Эрвин пытался указать). Я все еще заинтригован триггерной системой для обновлений, потому что кажется, что вы могли бы почти справиться с этим с помощью общего решения и просто использовать TG_ARGV для каждой таблицы для столбцов, которые вы хотите игнорировать. Другими словами, (hstore(NEW.*)-hstore(OLD.*))-TG_ARGV[0]).. если различий нет, то вернуть null; - person David S; 20.09.2012