URL: https://www.opennet.me/cgi-bin/openforum/vsluhboard.cgi
Форум: vsluhforumID9
Нить номер: 8924
[ Назад ]
Исходное сообщение
"Сдвинуть и уплотнить 'серийные номера' строк в Postgresql."
Отправлено Af. , 11-Ноя-10 21:17 
База непрерывно собирает данные, периодически очищаю таблицу от ненужного старого. У каждой строки, в период времени между чистками, есть уникальный порядковый номер. Номера растут и после очистки нужно бы сдвинуть серийные номера к началу нумерации. При этом убирая "зазоры" в нумерации, зазоры появляются из-за удалений во время чистки.

Как эффективнее сделать это?

Мануал Postgresql в разделе "8.1.4. Serial Types" описывает способ создания уникальных серийных номеров для каждой строки в базе.
http://www.postgresql.org/files/documentation/pdf/8.4/postgr...
Например, вот так:
CREATE TABLE tblnm ( sn SERIAL UNIQUE , event_day TIMESTAMP, event_description VARCHAR ) ;
Если есть более подходящий способ нумерации - могу способ из мануала заменить на другой.

Сделал вот такой пример, модель того как у меня всё это могло бы работать:

-- очистка примера
DROP TABLE tblnm ;
DROP TABLE tblnm_buffer ;
-- создать таблицу, со столбцом 'sn' для серийных номеров
CREATE TABLE tblnm ( sn SERIAL UNIQUE , event_day timestamp ) ;
-- заполнить данными три строки примера
INSERT INTO tblnm ( event_day ) VALUES ( timestamp '2010-10-22' ) ;
INSERT INTO tblnm ( event_day ) VALUES ( timestamp '2010-10-22' ) ;
INSERT INTO tblnm ( event_day ) VALUES ( timestamp '2010-10-30' ) ;
-- три записи имеют порядковые/серийные номера
SELECT * FROM tblnm ;
-- очистка
DELETE FROM tblnm WHERE event_day < timestamp '2010-10-30' ;
-- Сдвиг/сброс номеров с отбросом ненужных данных.
-- Номера будут сдвинуты, но порядок нумерации нарушится.
-- Допустимо, но некрасиво.
-- Причина: SQL в ответе на запрос не гарантирует
-- хронологический порядок строк и, да, порядок бывает другой.
BEGIN ;
    -- переименовать таблицу, создать пустую таблицу
    ALTER TABLE tblnm RENAME TO tblnm_buffer ;
    CREATE TABLE tblnm ( sn SERIAL UNIQUE , event_day timestamp ) ;
    -- перенести оставщееся из таблицы старой в новую
    INSERT INTO tblnm ( event_day ) SELECT event_day FROM tblnm_buffer;
END ;
-- Из старой таблицы запись с номером 3
-- перешла в новую таблицу и новый номер = 1.
-- Т.е. остались только нужные данные и
-- номера сдвинуты к началу нумерации,
-- нумерация оставщихся снова с единицы.
SELECT * FROM tblnm ;

Можно сдвиг и уплотнение нумерации, выполнение блока BEGIN-END оптимизировать, ускорить?

Содержание
Сообщения в этом обсуждении
"Сдвинуть и уплотнить 'серийные номера' строк в Postgresql."
Отправлено pavel_simple , 11-Ноя-10 21:46 
>[оверквотинг удален]
>     INSERT INTO tblnm ( event_day ) SELECT event_day
> FROM tblnm_buffer;
> END ;
> -- Из старой таблицы запись с номером 3
> -- перешла в новую таблицу и новый номер = 1.
> -- Т.е. остались только нужные данные и
> -- номера сдвинуты к началу нумерации,
> -- нумерация оставщихся снова с единицы.
> SELECT * FROM tblnm ;
> Можно сдвиг и уплотнение нумерации, выполнение блока BEGIN-END оптимизировать, ускорить?

убираете sequence с таблицы, если это поле является primary key (что чаще всего), удаляете primary key, убиваете сам sequece.

ставите на таблицу те данные которые быть должны
создаёте новый seq со стартовым +1 от максимального в таблице
привязываете seq
привязываете primary,если необходимо.

постановка задачи -- изврат

"Сдвинуть и уплотнить 'серийные номера' строк в Postgresql."
Отправлено Af. , 12-Ноя-10 09:50 
Спасибо. Действительно ведь:
BEGIN ;
   ALTER TABLE tblnm DROP COLUMN sn ;
   ALTER TABLE tblnm ADD COLUMN sn SERIAL UNIQUE ;
END;

Использование типа serial unique приводит к автоматическому выполнению нужных операций с последовательностями. Как я понимаю.

"Сдвинуть и уплотнить 'серийные номера' строк в Postgresql."
Отправлено gibbon , 12-Ноя-10 13:00 
> Спасибо. Действительно ведь:
> 
BEGIN ;
>    ALTER TABLE tblnm DROP COLUMN sn ;
>    ALTER TABLE tblnm ADD COLUMN sn SERIAL UNIQUE ;
> END;

> Использование типа serial unique приводит к автоматическому выполнению нужных операций
> с последовательностями. Как я понимаю.

А установить новый текущий номер для последовательности это слишком сложно?
SELECT pg_catalog.setval('ефиду_seq', 12345, true);

"Сдвинуть и уплотнить 'серийные номера' строк в Postgresql."
Отправлено pavel_simple , 12-Ноя-10 13:35 
>> Спасибо. Действительно ведь:
>> 
BEGIN ;
>>    ALTER TABLE tblnm DROP COLUMN sn ;
>>    ALTER TABLE tblnm ADD COLUMN sn SERIAL UNIQUE ;
>> END;

>> Использование типа serial unique приводит к автоматическому выполнению нужных операций
>> с последовательностями. Как я понимаю.
> А установить новый текущий номер для последовательности это слишком сложно?
> SELECT pg_catalog.setval('ефиду_seq', 12345, true);

тогда у него при попытке записи случится неприятное -- "поле с таким номером уже существует"
а так-то да -- seq отредактировал и рад.