обертывание команд postgresql в транзакцию: усечение или удаление или добавление/объединение

Я использую следующие команды ниже в postgresql 9.1.3 для перемещения данных из временной промежуточной таблицы в таблицу, используемую в веб-приложении (геосервере), все в той же базе данных. Затем удалить временную таблицу.

  1. ОБРЕЗАТЬ table_foo;

  2. ВСТАВИТЬ В table_foo

    ВЫБЕРИТЕ * ИЗ table_temp;

  3. УДАЛИТЬ ТАБЛИЦУ table_temp;

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

  • Каков наилучший способ запуска этих команд в транзакции?
  • Целесообразно ли создавать функцию или писать UPSERT/MERGE и т. д. в CTE?
  • Было бы лучше УДАЛИТЬ все строки, а затем выполнить массовую ВСТАВКУ из временной таблицы вместо TRUNCATE?
  • В postgres что бы разрешить откат TRUNCATE или DELETE?
  • Временная таблица доставляется ежедневно через сценарий ETL в arcpy. Как я могу автоматизировать части усечения/удаления/массовой вставки в postgres?
  • Я готов использовать PL/pgsql, PL/python (или рекомендуемый py для postgres)

В настоящее время я вручную выполняю команды sql после импорта временной промежуточной таблицы в мою БД.


postgresql common-table-expression truncate plpgsql plpython
person EnzovsJacques    schedule 27.11.2012    source источник

Ответы (1)


arrow_upward
4
arrow_downward

И truncate, и delete можно откатить (что четко описано в мануале).

truncate - в силу своей природы - имеет некоторые странности относительно видимости.

Подробнее см. в руководстве: http://www.postgresql.org/docs/current/static/sql-truncate.html (предупреждение внизу)

Если ваше приложение может мириться с тем фактом, что table_foo "пусто" во время этого процесса, truncate, вероятно, лучше (снова см. объяснение в большом красном поле в руководстве). Если вы не хотите, чтобы приложение заметило, вам нужно использовать delete

Чтобы запустить эти операторы в транзакции, просто поместите их в один:

begin transaction;
delete from table_foo;
insert into ....
drop table_temp;
commit;

Делаете ли вы это в функции или нет, зависит от вас.

truncate/insert будет быстрее (чем delete/insert), так как это сводит к минимуму количество генерируемых WAL.

person a_horse_with_no_name    schedule 27.11.2012
comment
Спасибо за предложения. Это отлично сработало. Я выбираю DELETE FROM vs TRUNCATE из-за безопасности MVCC. Есть ли у вас какие-либо предложения или опыт использования python для вызова команд psql. Я использую pyodbc и помещаю транзакцию в строку запроса python. Хотя я слышал об использовании psycopg2. - person EnzovsJacques; 30.11.2012
comment
@EnzovsJacques: извините, я не использую Python. - person a_horse_with_no_name; 30.11.2012