postgrespro / pgwininstall Goto Github PK
View Code? Open in Web Editor NEWPostgreSQL Windows installer
PostgreSQL Windows installer
В патче pg для работы с 1С наблюдается некорректное поведение при поиске в btree индексе поля типа mvarchar и mchar подстроки, содержащей букву "ё" (возможно, проблема более общая). На определенных наборах данных поиск в индексе не возвращает никаких результатов, хотя поиск по таблице успешно возвращает нужные строки (если скрыть индекс от планировщика тем же plantuner). При этом, если посмотреть индекс через pageinspect, то ключ, указывающий на искомую строку таблицы, в него успешно попадает (но не находится). Проблема проявляется на всех сборках postgresql с патчем 1С из репозитория postgres pro.
Инициализация базы
createdb pg1c
psql -f pg1c.sql.txt -1 pg1c
Ищем в индексе
explain analyze select * from t where val like 'зё%';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on t (cost=0.42..8.44 rows=2268 width=29) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((val >= 'зё'::mvarchar COLLATE "default") AND (val < 'зє'::mvarchar COLLATE "default"))
Filter: (val ~~ 'зё%'::mvarchar)
Heap Fetches: 0
Planning time: 1.251 ms
Execution time: 0.028 ms
(6 строк)
select * from t where val like 'зё%';
val
-----
(0 строк)
Ищем в таблице
load 'plantuner';
set plantuner.disable_index = 't_idx';
explain analyze select * from t where val like 'зё%';
QUERY PLAN
----------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..9072.00 rows=2268 width=29) (actual time=69.260..70.382 rows=2 loops=1)
Filter: (val ~~ 'зё%'::mvarchar)
Rows Removed by Filter: 453519
Planning time: 0.241 ms
Execution time: 70.397 ms
(5 строк)
select * from t where val like 'зё%';
val
------------
Зёма Л. Ф.
Зё Г. В.
(2 строки)
Проверим, что проблема не в like, но сохраняется и для поиска подстроки по диапазону (так переписывается план запроса вида "like" в индексе)
Ищем в индексе
explain analyze select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on t (cost=0.42..8.44 rows=1 width=29) (actual time=0.049..0.049 rows=0 loops=1)
Index Cond: ((val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar))
Heap Fetches: 0
Planning time: 0.170 ms
Execution time: 0.068 ms
(5 строк)
select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
val
-----
(0 строк)
Ищем в таблице
load 'plantuner';
set plantuner.disable_index = 't_idx';
explain analyze select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Seq Scan on t (cost=0.00..10205.80 rows=1 width=29) (actual time=95.307..96.933 rows=2 loops=1)
Filter: ((val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar))
Rows Removed by Filter: 453519
Planning time: 0.084 ms
Execution time: 96.948 ms
(5 строк)
select * from t where (val >= 'зё'::mvarchar) AND (val < 'зє'::mvarchar);
val
------------
Зёма Л. Ф.
Зё Г. В.
(2 строки)
create extension pageinspect;
select g.g as page, i.* from generate_series(1,2823) as g, lateral bt_page_items('t_idx', g.g) as i
where regexp_replace(data,' ','','g') like '%'||right(mvarchar_send('Зё Г. В.')::text, -2)||'%';
page | itemoffset | ctid | itemlen | nulls | vars | data
------+------------+-----------+---------+-------+------+-------------------------------------------------------------------------
822 | 115 | (3402,13) | 32 | f | t | 23 17 04 51 04 20 00 13 04 2e 00 20 00 12 04 2e 00 00 00 00 00 00 00 00
(1 строка)
select * from heap_page_items(get_raw_page('t', 3402)) where lp = 13;
lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid | t_data
----+--------+----------+--------+--------+--------+----------+-----------+-------------+------------+--------+--------+-------+--------------------------------------
13 | 7504 | 1 | 41 | 1018 | 0 | 463 | (3402,13) | 1 | 2306 | 24 | | | \x2317045104200013042e00200012042e00
(1 строка)
Ключ попадает не на ту страницу индекса
is it possible to make a 32bits installer for v14?
Hi,
the official documentation of postgresql 12 state that the build should be done with Windows SDK >= 8.1. In fact state this:
"64-bit PostgreSQL builds are supported with Microsoft Windows SDK version 8.1a to 10 or Visual Studio 2013 and above."
I see that on run.cmd of pgwininstall there is this dos batch code referring to SDK7.1:
IF "%~1"=="1" (
TITLE Building dependencies
IF "%SDK%"=="" SET SDK=SDK71
CMD.EXE /C %ROOT%\build\helpers\dependencies.cmd || GOTO :ERROR
)
Also in the readme.md of the pgwininstall repository says that these points are required:
"Microsoft SDK 7.1 or MSVC2013 for build PostgreSQL"
Also the last releases seems to stop on 11.6 release.
I'm wondering... is pgwininstall compatible with postgresql 12?
Thanks a lot for your work!, this is a very useful proyect to make easy your custom installer for windows.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.