postgresql триггеры и прочее

tokuchu

У меня в таблице лежат события с датой. Я хочу сохранять их в "архивных" таблицах при удалении. Т.е. когда я удаляю запись, то она отправляется в архивную таблицу соответствующего года. Т.е. если в таблице log время у записи помечено 2010-м годом, то она должна попасть в таблицу log_2010 при удалении.
В результате долгих потугов родился вот такой монстрик:
create or replace function archive_deleted returns trigger as $$

declare

  year integer;

  t_src text;

  t_dst text;

begin

  year := extract(year from OLD."when");

  t_src := TG_TABLE_SCHEMA||'.'||TG_TABLE_NAME;

  t_dst := t_src||'_'||year;

  perform opt_create_table(t_src, t_dst);

  create temp table tmp as select old.*;

  execute 'insert into '||t_dst||' select * from tmp';

  drop table tmp;

  return OLD;

end;

$$ language plpgsql;

Оно работает, но если удалять много, то получаю ошибку:
WARNING:  out of shared memory

CONTEXT:  SQL statement "drop table tmp"

PL/pgSQL function "archive_deleted" line 12 at SQL statement

ERROR:  out of shared memory

HINT:  You might need to increase max_locks_per_transaction.

CONTEXT:  SQL statement "drop table tmp"

PL/pgSQL function "archive_deleted" line 12 at SQL statement

Т.е. я так понимаю, что его смущает то, что создаётся много таблиц за транзакцию и не важно, что они удаляются. Есть у кого идеи как это можно исправить?

Dasar

а зачем тебе временные таблицы?

tokuchu

а зачем тебе временные таблицы?
Чтобы вот так сделать:
create temp table tmp as select old.*;

execute 'insert into '||t_dst||' select * from tmp';

Если это можно сделать как-то по-другому, то говори. У меня просто не получилось. Если old в разных формах подсовывать в execute, то не получается. :(

Dasar

У меня просто не получилось.
не получается именно: в генеренном запросе использовать *.old?
если запрос негенеренный, то ок?

Dasar

часто, кстати, применяется вариант вида:
удаленные записи перекидываются в таблицу буфер
крутится job(процесс и т.д.) по таймеру(событиям, постоянно или еще как-то который берет порцию из буфера и ее обрабатывает, затем следующую порцию и т.д.
решение хорошо тем, что оно универсальное и легко масштабируется
соответственно твоя проблема в этом варианте уйдет

tokuchu

если запрос не генеренный, то ок?
Ну вот этот запрос ведь не генерённый:
create temp table tmp as select old.*;
:)
А в генерённый надо что-то хитрое, наверное сказать. В общем то, что я пробовал у меня не получилось. Сейчас ещё попробую разные комбинации.

Dasar

Ну вот этот запрос ведь не генерённый:
тут нет "insert into" в только что созданную таблицу
согласен, что притянуто, но практика показывает, что лучше проверить

tokuchu

часто, кстати, применяется вариант вида:
удаленные записи перекидываются в таблицу буфер
крутится job(процесс и т.д.) по таймеру(событиям, постоянно или еще как-то который берет порцию из буфера и ее обрабатывает, затем следующую порцию и т.д.
решение хорошо тем, что оно универсальное и легко масштабируется
соответственно твоя проблема в этом варианте уйдет
А чем оно универсальнее и более масштабируемое, чем моё?
На самом деле у меня несколько таких таблиц и мне придётся несколько буферов делать и их обрабатывать отдельно. Да и удалять я буду нечасто. Просто хотел автоматизировать себе работу, чтобы руками всё не делать.

tokuchu

тут нет "insert into" в только что созданную таблицу
согласен, что притянуто, но практика показывает, что лучше проверить
чего?

tokuchu

Вот например если сделать:
execute 'insert into '||t_dst||' values '||row(old.*);

То будет вот такая ошибка:
DELETE FROM test.log where a=1;

ERROR:  column "2011-02-01 00:00:00" does not exist

LINE 1: insert into test.log_2011 values ("2011-02-01 00:00:00",1)

                                          ^

QUERY:  insert into test.log_2011 values ("2011-02-01 00:00:00",1)

CONTEXT:  PL/pgSQL function "archive_deleted" line 10 at EXECUTE statement

Т.е. в сгенерённой командре строка оказывается в двойных кавычках. Что странно как-то.

Dasar

А чем оно универсальнее и более масштабируемое, чем моё?
масштабируемее - потому что пофигу сколько будет удаляться строк: 1 или миллион, решение все равно будет работать.
в твоем случае, мы видим - что от кол-ва строк зависит: решение работает или нет

Dasar

ну а просто: insert into test.log_2011 from select *.old работает? если удалять записи за 2011

tokuchu

масштабируемее - потому что пофигу сколько будет удаляться строк: 1 или миллион, решение все равно будет работать.
в твоем случае, мы видим - что от кол-ва строк зависит: решение работает или нет
Ну я бы не сказал, что оно работает. Это можно считать не работает. Собственно поэтому его и надо исправить. Тогда не будет проблем с "масштабированием". Мне как раз миллионы строк и надо удалять. :)

tokuchu

ну а просто: insert into test.log_2011 from select *.old работает? если удалять записи за 2011
Точно сейчас не вспомню, но вроде как да. Ведь со временной таблицей это работает.

Dasar

Точно сейчас не вспомню, но вроде как да. Ведь со временной таблицей это работает
так ты все-таки найди точную грань когда с одной стороны у тебя еще работает, а с другой уже не работает.
действительно ли insert into test.log_2011 from select *.old у тебя работает,
а execute 'insert into test.log_2011 from select *.old' уже нет

tokuchu

действительно ли insert into test.log_2011 from select *.old у тебя работает,
Так я же говорю, что со временной таблицей работает.
а execute 'insert into test.log_2011 from select *.old' уже нет
Тут не грань, а пропасть уже. Такое не работает, т.к. внутри execute, похоже, оно уже не знает про old. В ощибках оно не разворачивается, его тип не упоминается, просто ругается на него как на слово. Поэтому old должен быть неквотирован, но как-то корректно преобразован в строку.

Dasar

Так я же говорю, что со временной таблицей работает.
тебе лень проверить?
вдруг он тоже ругнется что он не понимает что такое .old, и ты вдруг, например, вспомнишь, что в postgree в insert-е select после from-а надо брать в скобки

tokuchu

тебе лень проверить?
вдруг он тоже ругнется что он не понимает что такое .old, и ты вдруг, например, вспомнишь, что в postgree в insert-е select после from-а надо брать в скобки
Не то, чтобы лень. Просто я уверен, что будет абсолютно аналогично как с временной таблицей.
Ну ладно, проверил сейчас. Всё как я и говорю.

Dasar

это не оно?
http://archives.postgresql.org/pgsql-general/2009-09/msg0117...
они, кстати, столкнулись с этим на той же задаче, что и ты

tokuchu

это не оно?
http://archives.postgresql.org/pgsql-general/2009-09/msg0117...
они, кстати, столкнулись с этим на той же задаче, что и ты
Не совсем понял что там. Они какую-то совсем ебанутую конструкцию используют. :) Типа отквотированная неправильно запись приведённая к типу, а потом от неё звёздочку. Но у меня проблема в том, что я не могу привести к типу, т.к. его у меня нет.

Dasar

Но у меня проблема в том, что я не могу привести к типу, т.к. его у меня нет.
таблица - это разве не тип?

tokuchu

Они какую-то совсем ебанутую конструкцию используют. :) Типа отквотированная неправильно запись приведённая к типу, а потом от неё звёздочку. Но у меня проблема в том, что я не могу привести к типу, т.к. его у меня нет.
А нет, получилось привести к таблице. :)
Т.е. это блядские двойные кавычки если приводить обратно к типу, то они пережуются уже нормально. Вот такой результат.
execute 'insert into '||t_dst||' select ('||quote_literal(OLD.*)||'::'||t_dst||').*';

Т.е. OLD приводится к тексту с двойными кавычками, далее это квотируется в строку, потом уже во внутренней команде эта строка преобразовывается обратно в строку для данной таблицы и из неё берутся поля для селекта. Ужоснах! :)

tokuchu

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

Dasar

Ужоснах! :)
если у тебя в таблицах нет хитрых типов колонок и значения не очень длинные, то для твоей задачи должно прокатить
зы
только проверь на всякий случай, что там происходит, если в значении встречаются всякие разные кавычки и какие-нибудь страшные уникодные символы

tokuchu

если у тебя в таблицах нет хитрых типов колонок и значения не очень длинные, то для твоей задачи должно прокатить
зы
только проверь на всякий случай, что там происходит, если в значении встречаются всякие разные кавычки и какие-нибудь страшные уникодные символы
А почему думаешь, что могут помешать. Оно же само их квотирует и расквотирует?
Уже запустил удаляться 6 миллионов строк. :)

Dasar

А почему думаешь, что могут помешать. Оно же само их квотирует и расквотирует?
потому что в теории обычно все замечательно, а на практике кто-нибудь где-нибудь что-нибудь не доделал (как. например, old/new внутри execute)

tokuchu

(как. например, old/new внутри execute)
Ну там уже другой контекст полечается. Так что может быть правильно не видно.
А так вообще строчки у меня все похожие и если на одних отработает, то и на других будет нормально.

hprt

сделай явную вставку типа 


...

insert into year2009 (...)

select old.* where old.year = 2009



insert into year2010 (...)

select old.* where old.year = 2010

PS А в постгре нет нормальных триггеров, чтоб вешать такое не на каждую строку, а на событие удаления? Ну, чтоб еще данные были доступны...

tokuchu

сделай явную вставку типа
Не понял что там ты написал. Т.е. куда ты это предлагаешь засунуть.
Но я не хочу расписывать каждый год отдельно. Так бы я и руками бы удалил бы в таблицу. А мне лень. :)
PS А в постгре нет нормальных триггеров, чтоб вешать такое не на каждую строку, а на событие удаления? Ну, чтоб еще данные были доступны...
Есть построчные триггеры, есть общие. Но я тоже не понимаю чем мне общий поможет. Т.к. там могут быть разные строчки. И кроме того этот триггер before delete, т.е. данные ещё доступны, если я понимаю про что ты.

hprt

1. На каждый год записать свою вставку. Я понимаю, что тебе лень, но годов не так и много + в конце концов скрипт для этого дела можно сгенерить на имеющиеся года + на 150 лет вперед, если собираешься все это время поддерживать. На скорость это почти не повлияет, зато избавит тебя от создания временных таблиц, с которыми у тебя косяк
2. В оракле есть триггеры собственно на событие, а есть for each row. Насколько мне известно, доступа к вставляемым данным в общих триггерах нет, поэтому приходится использовать построчные, и при больших объемах наступает всеобщий ппц. В мсскл нет построчных триггеров, данные доступны в псевдотаблицах inserted/deleted - срабатывает один раз на событие. Если такое есть в постгре - то надо делать так. Плюс если постгре как оракл поддерживает множественную вставку (один инсерт - много таблиц то вообще шикарно.
3. А тебе вообще нужны триггеры? Может, стоит сначала разложить данные по таблицам, а потом удалять? Или если стоит задача архивирования, тупо партиционировать таблицу?

tokuchu

1. На каждый год записать свою вставку. Я понимаю, что тебе лень, но годов не так и много + в конце концов скрипт для этого дела можно сгенерить на имеющиеся года + на 150 лет вперед, если собираешься все это время поддерживать. На скорость это почти не повлияет,
Ну я не хотел так делать. Иначе бы этот вопрос не возник. Написать прямые запросы и скритп не составило бы труда. Но это не цель этого треда.
зато избавит тебя от создания временных таблиц, с которыми у тебя косяк
Ну если ты до конца прочитал, то я от временной таблицы избавился.
2. В оракле есть триггеры собственно на событие, а есть for each row. Насколько мне известно, доступа к вставляемым данным в общих триггерах нет, поэтому приходится использовать построчные, и при больших объемах наступает всеобщий ппц. В мсскл нет построчных триггеров, данные доступны в псевдотаблицах inserted/deleted - срабатывает один раз на событие. Если такое есть в постгре - то надо делать так. Плюс если постгре как оракл поддерживает множественную вставку (один инсерт - много таблиц то вообще шикарно.
Сейчас точно не скажу что там доступно с общим триггером. Но там придётся скрипт намного сложнее писать, т.к. надо сначала года выбрать какие там есть, потом генерировать для каждого команду. "Пипеца" на своих объёмах не наблюдаю сейчас с построчным триггером.
3. А тебе вообще нужны триггеры? Может, стоит сначала разложить данные по таблицам, а потом удалять?
Я уже писал что я хочу сделать. Не хочу делать вручную. Я хотел сделать эту штуку, чтобы о ней потом можно было тупо забыть и она сама работала как надо.
Или если стоит задача архивирования, тупо партиционировать таблицу?
Ну мне не особо надо это объединять в одно. Но собственно что-то вроде партиционирования и делается в результате. При партиционировании ведь тоже надо будет как-то по таблицам раскладывать. А там либо расписывать для каждого года отдельно, либо что-то подобное писать.
Оставить комментарий
Имя или ник:
Комментарий: