История версий геоданных. нужны советы по Postgres+PostGIS

[ttestt]

Идея такова.
Пусть у нас есть таблица с пространственными данными и одним аттрибутом (для простоты)
Тоесть имеем в постгресе таблицу с полями :
gid serial PRIMARY KEY,
"№" varchar(128),
'the_geom'
все это может быть запихнуто в ПГ при помощи shp2pgsql из шейпфайла, главное что опишу на примере этих полей. вообще поля естественно будут ваши, с вашего shp файла.

Нам нужно создать в ПГ дополнительный счетчик, который будет задействован для определения уникальности объекта или группы объектов, сделаем так
CREATE SEQUENCE thenameofmytable_nuid START 1;

И добавим к этим полям
"the_pid" int4 DEFAULT nextval('thenameofmytable_nuid'), - наш неуникальный идентификатор для объекта
"the_login" text DEFAULT user, - кто создал запись
"the_time" timestamp without time zone DEFAULT now(), - время создания (изменения) записи

далее
создаем вид
CREATE OR REPLACE VIEW "thenameofmytable_ptv" AS
SELECT gid, "№", the_pid, the_geom
FROM "thenameofmytable";


назначаем собственника оригинальной таблицы (для опытов - необязательно, наверное)
ALTER TABLE "thenameofmytable" OWNER TO postgres;

правило чтения из таблицы
CREATE OR REPLACE RULE "_RETURN"AS ON SELECT TO "thenameofmytable_ptv" DO INSTEAD
SELECT gid, "№", the_pid, the_geom
FROM "thenameofmytable"
WHERE (the_pid, the_time) IN ( SELECT the_pid, max(the_time) FROM "thenameofmytable" GROUP BY the_pid);
тоесть вернутся по запросу только самые новые (по полю времени) из групп собранных по the_pid

правило добавления записи в таблицу
CREATE OR REPLACE RULE "_INSERT"AS ON INSERT TO "thenameofmytable_ptv" DO INSTEAD
INSERT INTO "thenameofmytable" ( "№", the_geom, the_pid, the_login, the_time)
VALUES ( new."№", new.the_geom, nextval('thenameofmytable_nuid'), user, CURRENT_TIMESTAMP);
новой записи - новый неуникальный идентификатор. туда же имя создателя, и текущее время.

правило на изменение данных
CREATE OR REPLACE RULE "_UPDATE"AS ON UPDATE TO "thenameofmytable_ptv" DO INSTEAD
INSERT INTO "thenameofmytable" ( "№", the_geom, the_pid, the_login, the_time)
VALUES (new."№", new.the_geom, old.the_pid, user, CURRENT_TIMESTAMP);
вместо изменения - создаем запись с новыми данными но старым неуникальным идентификатором.

правило на удаление. пока не проработано. делает "лишь бы что-то", но чтобы можно было отличить удаленные записи.
CREATE OR REPLACE RULE "_DELETE"AS ON DELETE TO "thenameofmytable_ptv" DO INSTEAD
INSERT INTO "thenameofmytable" ( "№", old.the_geom, old.the_pid, 'object.deleted', CURRENT_TIMESTAMP);
собственно заготовка. создает копию записи со старыми данными но имя пользователя меняет на 'object.deleted'

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

У кого какие мысли возникают?
Цель - получить таблицу и карту, состояние которой можно определить на любой момент времени.

Идей насчет удаления две :
1) удаление реально удаляет запись. если у этого геообъекта есть история, автоматом появится предыдущая запись вместо этой.
2) устанавливается флаг "удален", объект исчезает с карты (нужно наверное переписать запрос _RETURN
3) полностью удаляется объект со всей историей. но это наверное зверство, и лишнее.

Также есть подозрение что запрос _RETURN неоптимален.

[ttestt]

Кстати, о плюсах...
Редактировать геотаблицу в ПГ, используя квантум, можно с нескольких рабочих мест (распараллелить работу).
При этом потерь данных вроде нет.

И о минусах.
В таком виде (с историей), если два пользователя начали редактирование одного объекта, изменили что-то, а потом сохранили с интервалом в 10 секунд, например, то отобразятся изменения того, кто сохранил позже. имхо. Но изменения первого хотябы _останутся_ в истории, в случае работы на простой таблице, думаю, изменения первого будут потеряны совсем.
Сортировка по времени, наверное, не сильно хорошо и надежно для определения новизны. Возможно, нужно еще один счетчик. Потому как время можно перевести (скорректировать).

[Voltron]

Сортировка по времени, наверное, не сильно хорошо и надежно для определения новизны. Возможно, нужно еще один счетчик. Потому как время можно перевести (скорректировать).

Если все правильно путаю, то токены вроде CURRENT_TIMESTAMP, CURRENT_DATE и прочие вставляют в таблицы время сервера, и модификация времени на рабочей станции на них влияния не оказывает, хоть на сто лет вперед или назад переводи. А возможности корректировать дату/время на сервере у юзеров по определению быть не должно. Так что, ИМХО, метка времени вполне подходит для определения новизны.

[ttestt]

Сортировка по времени, наверное, не сильно хорошо и надежно для определения новизны. Возможно, нужно еще один счетчик. Потому как время можно перевести (скорректировать).

Если все правильно путаю, то токены вроде CURRENT_TIMESTAMP, CURRENT_DATE и прочие вставляют в таблицы время сервера, и модификация времени на рабочей станции на них влияния не оказывает, хоть на сто лет вперед или назад переводи. А возможности корректировать дату/время на сервере у юзеров по определению быть не должно. Так что, ИМХО, метка времени вполне подходит для определения новизны.

Все верно.
Но именно про время сервера и речь
У меня вот включена автокоррекция времени через инет. Сервер обновил время - поехала и сортировка (вероятность такого совпадения ничтожна, но растет по мере увеличения кол-ва пользователей)
Еще не исключен вариант, когда сервер БД для нескольких рабстанций будет установлен на самой мощной либо на самой слабоиспользуемой рабстанции.

[stopa85]

IMHO, кроме current_timestamp нужно добавить булево поле "эта запись актуальна" - is_actual.
При обновлении объекта спредыдущей записи значение этого поля ставиться в false.
Это дает:

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

2. Запрос вида SELECT * FROM table WHERE is_actual=true, выглядит эффективнее запроса с подзапросом.
Запрос SELECT the_pid, max(the_time) FROM "thenameofmytable" GROUP BY the_pid скорее всего будет шерстить всю таблицу.

3. Можно сделать table space по полю is_actual (технология когда записи таблицы, удовлетворяющие условию, физически размещается отдельно от остальной части таблицы). Table space (как-то оно по другому называется ...) Может очень положительно сказаться на производительности - ведь не актуальные сведения используются только для восстановления истории.

Развивая мысль на случай удаления записи, нужно добавить третье состояние флага:
1 - неактульная версия,
2 - удаленная версия, всегда актуальна,
3 - актуальная версия, объект неудален.

Естественно, если кто-то пытается вставить "актуальную" версию удаленного объекта - нужно делать откат транзакции.

Я бы как-то так делал.

[stopa85]

Вторую идею модно назвать "журнал транзакций". Она мне нравиться намного больше.

Создаете табличку в которую пишите все изменения, включая их дату.
Придется написать процедуры отката существующего слоя на заданную дату и/или вообще в другой слой. Он-лайн просмотра истории не получиться, но зато тормозов при работе с "распухшей" таблицей не огребете точно, да и как средство хранения истории за несколько лет - самое то.

[Dorofeev]

Кстати и в ArcSde история организованна отдельной таблицей для каждого фич класса

[stopa85]

ttestt, чего вы хотите добиться хранением истории?

Вам нужна возможность оперативно просматривать старую версию карты (в пределах нескольких дней, часов)?
Иметь возможность откатиться на несколько дней или месяцев назад?

[ttestt]

ttestt, чего вы хотите добиться хранением истории?

Вам нужна возможность оперативно просматривать старую версию карты (в пределах нескольких дней, часов)?
Иметь возможность откатиться на несколько дней или месяцев назад?

Иметь возможность запросить состояние графики и данных на любой момент времени.

Откат, я думаю, явление которое если и нужно то в исключительно редких случаях, когда пользователь, к примеру, удалит всё с карты. Поэтому такие вещи (откаты) можно выполнять руками.

[stopa85]

Иметь возможность запросить состояние графики и данных на любой момент времени.

Тогда я думаю, что путь по которому Вы идете верный. Свои мысли и замечая я высказал в своем первом посте.

Откат, я думаю, явление которое если и нужно то в исключительно редких случаях, когда пользователь, к примеру, удалит всё с карты. Поэтому такие вещи (откаты) можно выполнять руками.

Откат можно делать и в другую таблицу (почему бы не использовать временную таблицу - temporary table, как это делают продукты "1с:Предприятие"?) или выполнять на копии данных - и уже их просматривать....

Так что оба подхода имеют свои достоинства и недостатки и "теоретически" реализуют одинаковый функционал.

[ttestt]

Тогда я думаю, что путь по которому Вы идете верный. Свои мысли и замечая я высказал в своем первом посте.

Откат можно делать и в другую таблицу (почему бы не использовать временную таблицу - temporary table, как это делают продукты "1с:Предприятие"?) или выполнять на копии данных - и уже их просматривать....

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

Так что оба подхода имеют свои достоинства и недостатки и "теоретически" реализуют одинаковый функционал.

Осталось только выбрать наиболее удобный
В конце концов, даже ежедневные бэкапы решат проблему. Только появится вопрос об объеме бэкапных данных.

А про "table space" поподробнее можно?
Если вам интересна данная тема и полезна для работы, и если не затруднит сильно, то с примером.