Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / PostgreSQL Новый топик    Ответить
 Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1298
Добрый день всем.

В оракле 12 есть 4 вида документов - для примера возьмем один
create table DK of xmltype store as binary xml;
--create one virtual column with value extracted from xml
alter table DK add (DKID NUMBER AS (EXTRACTVALUE(OBJECT_VALUE ,'/DKID/@ID'))) ;
create unique index DK_UKC on DK(DKID);

Есть pkg1.xmlrefresh(rec.DKID) пишущая xml в object_value
(SQL хитрый запрос по обычным таблицам )

ctx_ddl.create_section_group('DkXMLGR','XML_SECTION_GROUP');
ctx_ddl.add_attr_section('DKXMLGR','origin','DKID@ORIGIN');
ctx_ddl.add_field_section('DKXMLGR','text','BODY');
... и еще 20+

Есть XML индекс
create index DKXML_CTX on DKXML(OBJECT_VALUE) indextype is CTXSYS.CONTEXT
PARAMETERS('LEXER OISXML_LX WORDLIST OISXML_WL STOPLIST OISXML_SL filter CTXSYS.NULL_FILTER section group DKXMLGR');

написан пакет с функциями поиска которые и дергаются в app
типа : select * from table (PKG1.f_find(0,30,'/',sys.ku$_vcnt('TEST/text'),'user1',sys.ku$_vcnt('firewal1','firewal2'));
выдает таблицу ид док , статус , дата найденных доков (по хитрым условиям)

Я Не автор но вносил небольшие изменения (когда нужно было добавлять поиск по новым атрибутам) Не меняя логики
Работает в принципе хорошо и быстро

Стоит вопрос переноса этого дела на Postgres Azure
и пока у меня большие проблемы с общим подходом
Если кто-то делал что-то похожее или есть идеи пишите.
Я немного полазил в Postgres там тоже есть полнотекстовый поиск но практически уверен что той функциональснти не хватит

1?) Generated columns появились в postgres 12 (его пока у меня нет )
можно ли его вытянуть с xml поля и по нему сделать уникальный индекс

2?) Ссылки на толковые статьи по полнотекстовому поиску в Postgres велком (гуглом я польовался )

Ну или общие идеи тоже приветствюутся
12 фев 21, 20:31    [22280257]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 1246
Гулин Федор
1?) Generated columns появились в postgres 12 (его пока у меня нет )
можно ли его вытянуть с xml поля и по нему сделать уникальный индекс

Функциональные индексы (в т.ч. уникальные) по любому immutable выражению (в том числе по пользовательским хранимкам) в postgresql есть давным давно.
Потому с generated columns никто и не торопился - за ненадобностью.

Напишите не что у вас там в оракле, а какова сама задача. Возможно, она решается просто другими методами. Например, что xml вам вообще совсем не нужен, а просто был удобным костылём для оракла - я так понял, что у вас на входе "SQL хитрый запрос по обычным таблицам", на выходе "выдает таблицу", а xml посередине - внутренняя деталь реализации.
13 фев 21, 17:34    [22280506]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1298
@Melkij
Конечная задача написать набор функций такого вида :
Select * from table (PKG1.f_find(0,30,'/',sys.ku$_vcnt('TEST/text'),'user1',sys.ku$_vcnt('firewal1','firewal2'));
выдает таблицу ид док , статус , дата найденных доков (по хитрым условиям)
F_FIND( N_SKIP NUMBER, N_TOP NUMBER, P_DELIM VARCHAR2, ...) RETURN Tp_COLumns_IDS PIPELINED ;
.. PKG1.F_FINDCREAT_ON(sysdate-2,30,'/',SYS.KU$_VCNT('test/text','34512/clientid'),'user2',0,SYS.KU$_VCNT('firewal3','firewal4'));
...

чтобы их можно было вызывать для поиска документов с бакенда (там есть функции с параметрами дата от, дата по и т.д )

понятно пакетов нет и передавать массив надо не через sys.ku$_vcnt( а по другому (по моему там есть array))
Насчет xml - да возможно он и не нужен будет - в оракле по нему строился полнотекстовый поиск.
Насчет логики :
Атрибуты документа {attr1 , attr2 , attr3 , ... attrn }
Входят в разные фильтры поиска { Filter1 , ... FilterrN } в различных заранее известных сочетаниях (тот самый хитрый SQL)
Filter1 : { User_Sent , User_CC , Use_BCC , User_Boss ... }
Filter2 : { User_Sent , User_CC }
Фактически XML полученный в оракле это есть расширенный исходный документ
с бОльшим кол-вом тэгов чем исходных атрибутов
по которому сторится оракл. полнотектовый индекс по Различным тэгам.

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

Пока я хочу понять можно ли попробовать прикрутить Postgres text search к-й вроде есть
14 фев 21, 18:49    [22280817]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1298
Попробую переформулировать вопрос
Как переписать функции F_FIND*( в Postgres

т.е мои идеи пока своядтся к доп. таблицам
Sec_user : {dkid , user_id}
Sec_words {dkid , type_word , user_id} ...
Sec_date {dkid , dat_begin , dat_end }
и генерации динамического SQL по ним для разных случаев
почитал - вроде есть возможнсоть выдавать в функции динам. скл
RETURN QUERY EXECUTE ''
https://carto.com/help/working-with-data/sql-stored-procedures/

Идея вообщем понятна
Есть фильтр на юзеров - клеить таблицу Sec_user и т.д ..
пока думаю - в оригинале был 1 xml для 1 документа -
тут у меня получается миниму 3-4 доп. таблицы с атрибутами
Опять же перформанс с динамическим SQL это еще та загадка и проблема.
В одном случае будет быстро - в другом тормоза

В SQL server я бы делал через последтовательность временных таблиц ( #Tmp1, #Tmp2 , #Tmp3 )
(между ними можно и лог всунутьт (чтобы видет перфоманс) и доп. индексы на #Tmp создать )
чтобы не создавать один мегасложный запрос - а можно ли в postgres не ясно - вряд ли.

Вообщем идеи КАК можно приветсвуются
22 фев 21, 13:06    [22284706]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Гулин Федор,

Если у вас задача хранения произвольных неизвестной структуры документов в формате key-value наборов
и поиска по ним
то я бы смотрел в сторону
или
hstore https://www.postgresql.org/docs/13/hstore.html

или более модно-молодежно json(b)
https://www.postgresql.org/docs/13/functions-json.html

и то и другое более менее можно подпереть GIN индексами

Но я бы рекомендовал нормализовать структуру и вынести общие для всех документов поля в колонки а зоопарк уже оставить в json(b) или hstore/

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
22 фев 21, 15:27    [22284771]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Гулин Федор
Member

Откуда: МИНСК
Сообщений: 1298
Maxim Boguk,
СПАСИБО за ответ
почитаю мат. часть
а где и как можно посмотерть
поддерижваются ли
jsonb и hstore
в Azure Postgres 10(11) ?
22 фев 21, 17:01    [22284801]     Ответить | Цитировать Сообщить модератору
 Re: Миграция XML index с Оракла 12 на Postgres Azure 10 (11)  [new]
Maxim Boguk
Member

Откуда: Melbourne, Австралия
Сообщений: 4581
Гулин Федор
Maxim Boguk,
СПАСИБО за ответ
почитаю мат. часть
а где и как можно посмотерть
поддерижваются ли
jsonb и hstore
в Azure Postgres 10(11) ?


вот уж не знаю... json(b) и работа с ним в какой то форме на 11 версии уже была.
Доступен ли hstore не знаю
я такими сервисами не пользуюсь... дорого и неудобно.

--
Maxim Boguk
лучшая поддержка PostgreSQL: dataegret.ru
22 фев 21, 17:10    [22284804]     Ответить | Цитировать Сообщить модератору
Все форумы / PostgreSQL Ответить