При пейджинге с сортировкой в PostgresDataService одни и те же записи попадают в несколько страниц, а другие ни в одну 🚀

[mao29]

Описание ошибки

При пейджинге с сортировкой по полю, в котором есть повторяющиеся значения, некоторые записи попадают на несколько страниц, а некоторые ни на одну. Это связано с неуникальностью результатов отправляемого запроса и является официально задокументированным поведением PostgreSQL: https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset

Ожидаемое поведение

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

Шаги воспроизведения

1. Создать и заполнить большую таблицу с полем, в котором могут быть повторяющиеся значения.
2. Построить LCS, вычитывающий данные постранично и сортирующий их по этому полю
3. Пронаблюдать появление некоторых записей на нескольких страницах

Конфигурация

БД: PostgreSQL
ORM: 5.0.0, 5.1

Пути решения

При генерации SQL-запроса в PostgresDataService для LCS, в котором заданы параметры пейджинга, добавлять в параметры сортировки последним параметром STORMMainObjectKey.
Попутно рассмотреть возможность отказа от row_number(), поскольку в PostgreSQL в нем нет необходимости.

Исходный код

Проект на GitHub: https://github.com/Flexberry/NewPlatform.Flexberry.ORM
Ветка в которую нужен будет PR: develop-v5.0.1 (от неё делаем ветку feature-67-...) и develop
Файл: https://github.com/Flexberry/NewPlatform.Flexberry.ORM/blob/develop/ICSSoft.STORMNET.Business.PostgresDataService/PostgresDataService.cs#L851

Тесты

Предлагается реализовать интеграционный тест, который будет сохранять в БД несколько записей с заранее заданными ключами и повторяющимися значениями в дополнительном поле, по которому будет осуществляться сортировка, и проверять, что при постраничной вычитке каждая запись попадает на страницу в соответствии со своим ключом.

Примерная оценка трудоёмкости

8ч

Скриншоты, полезные ссылки

• https://stackoverflow.com/questions/13580826/postgresql-repeating-rows-from-limit-offset - пример ошибочного поведения при пейджинге
• https://www.postgresql.org/docs/9.6/queries-limit.html - документация про операторы пейджинга в PostgreSQL для принятия решения о выпиливании row_number()
• https://stackoverflow.com/questions/3125571/offset-vs-row-number - вопрос про сравнение offset и row_number в постгресе
• Запросить ссылку на реализацию соответствующего метода на проекте Соцзащита

[bratchikov]

#12

[GaryaevSS]

На проекте СпортивноеПрикамье возникла следующая проблема с быстродействием:
Лукап с автокомплитом генерирует постраничный запрос так, что он выполняется 45сек (вместо условных 400мс).

Конфигурация:
PostgreSQL 10.12
Mono JIT compiler version 4.6.2
NewPlatform.Flexberry.ORM 5.0.2

Данные
Целевая таблица (справочник) содержит 2+ миллиона записей
В таблице специально под этот запрос навешали покрывающий индекс fias_address_pk (Address, PrimaryKey)

Пример сгенерированного sql запроса
SELECT __PrimaryKey ,Address ,Postalcode ,STORMMainObjectKey ,STORMNETDATAOBJECTTYPE FROM ( SELECT __PrimaryKey ,Address ,Postalcode ,STORMMainObjectKey ,STORMNETDATAOBJECTTYPE FROM ( SELECT FIAS0.primaryKey as __PrimaryKey ,FIAS0.Address as Address ,FIAS0.Postalcode as Postalcode ,FIAS0.primaryKey as STORMMainObjectKey ,cast(0 as numeric) as STORMNETDATAOBJECTTYPE FROM FIAS FIAS0 ) STORMGENERATEDQUERY WHERE UPPER( Address ) like '%ГОР%' ORDER BY Address asc ) rn ORDER BY Address asc ,STORMMainObjectKey OFFSET 0 LIMIT 10

План выполнения сгенерированного запроса:

Теперь тот же запрос, но без предварительной сортировки во внутреннем подзапросе:
EXPLAIN ANALYZE SELECT __PrimaryKey ,Address ,Postalcode ,STORMMainObjectKey ,STORMNETDATAOBJECTTYPE FROM ( SELECT __PrimaryKey ,Address ,Postalcode ,STORMMainObjectKey ,STORMNETDATAOBJECTTYPE FROM ( SELECT FIAS0.primaryKey as __PrimaryKey ,FIAS0.Address as Address ,FIAS0.Postalcode as Postalcode ,FIAS0.primaryKey as STORMMainObjectKey ,cast(0 as numeric) as STORMNETDATAOBJECTTYPE FROM FIAS FIAS0 ) STORMGENERATEDQUERY WHERE UPPER( Address ) like '%ГОР%' ) rn ORDER BY Address asc ,STORMMainObjectKey OFFSET 0 LIMIT 10

План выполнения:

Адекватным по быстродействию также являлся бы случай, когда сортировка во внутреннем и внешнем запросах была бы одинаковой:

В связи с этим вопрос-предложение:
Может быть не стоит в постраничном запросе использовать сортировку во внутреннем подзапросе? Или делать ее идентичной той, что на выходе получается?

[NicholasNoise]

g=posts&t=4787#post20825

[bratchikov]

Нужно проверить все ли проблемы, описанные тут были успешно преодолены в текущей версии и если да, то закрыть issue.