?

Log in

No account? Create an account

...

или внеочередные заметки


SQL/JSON standard-2016 conformance for PostgreSQL, Oracle, SQL Server and MySQL
trekking, Himalaya
obartunov
Updated 21-03-2017:
Refresh data for MySQL ( 8.0.4 version).
SQL-commands, we used for checking comformance

Updated 10-07-2017:
Short version of comparison table.


We compared SQL/JSON Standard-2016 conformance in the latest versions of the major relational databases and it is clearly seen from the table below, that PostgreSQL support is the best ! Nikita Glukhov and I have started this project a year ago, a couple of months after the Standard was published. Our initial intention was to have it in PG 10, but community afraid of the size of the patch. A year of development, reading the Standard, a lot of chatting, were really helpful to us and we confirmed now, that SQL/JSON standard is really useful and our implementation is solid.



Read more...Collapse )
Tags: , ,

sql/json: House example
trekking, Himalaya
obartunov
This is a technical post to illustrate the house data set example, which I use in sqljson documentation.

Tags: , ,

SQL/JSON documentation
trekking, Himalaya
obartunov
Draft documentation for SQL/JSON implementation in PostgreSQL is available
https://github.com/obartunov/sqljsondoc/blob/master/README.jsonpath.md

Now I need your recommendation about a structure of documentation.
Tags: ,

SQL/JSON on android phone
trekking, Himalaya
obartunov
Update:

We updated packages:
http://www.sai.msu.su/~megera/postgres/files/postgresql-sqljson_11.0-dev-2_aarch64.deb
http://www.sai.msu.su/~megera/postgres/files/postgresql-sqljson_11.0-dev-2_arm.deb
They now automatically initialize the data directory, start server and load demo table 'house.sql'. Also, the history file .psql_history automatically created, so you can play with queries (use arrow up and down keys).

===========================================================================================


Nikita Glukhov has managed to compile the postgresql(master branch) + sql/json patches for Termux, so now it's possible to play with sql/json on android phone, using deb-file postgresql-sqljson_11.0-dev-0_aarch64.deb.

You can download the sample database to play with examples in Jsonpath Introduction.



In case you need more details:

Read more...Collapse )

SQL/JSON in PostgreSQL (WIP).
trekking, Himalaya
obartunov
I have started writing document about upcoming SQL/JSON support in PostgreSQL. Currently, I mostly concentrated on JSONPATH. Read "Jsonpath introduction" on github. Consider it as WIP, more info will be added.

My intention is to make the standard more understandable for user. I appreciate any help.


Nepali language for PostgreSQL
trekking, Himalaya
obartunov
Many years ago I and Teodor added devanagari script support (slides 33-35) to PostgreSQL . We planned to add snowball stemmer for nepali language, but that was never done.



Recently, I revived the project and contacted Ingroj Shrestha from Nepali NLP group, who kindly agreed to work on snowball stemmer for nepali language and rather quickly produced the first version of stemmer (Ingroj Shrestha and Shreeya Singh Dhakal), which I used to add nepali support to full text search in master branch of PostgreSQL (default configuration, stop words), see nepali.patch. See, how it works:

select ts_lexize('nepali_stem', 'अँगअँकाउछन्');
 ts_lexize
-----------
 {अँगअँकाउ}
(1 row)

select to_tsvector('nepali','PostgreSQL संसारको सबैभन्दा उन्नत खुला स्रोत डाटास हो');
                                 to_tsvector
-----------------------------------------------------------------------------
 'postgresql':1 'उन्नत':4 'खुला':5 'डाटास':7 'संसार':2 'सबैभन्':3 'स्रोत':6 'हो':8
(1 row)


I intend to submit this patch for PG 11.

Update: Arthur Zakirov added Hunspell dictionary for nepali language.
Update: Nepali FTS configuration was committed to PG 12


Read more...Collapse )
Tags: , ,

Безвременье
trekking, Himalaya
obartunov
Нашел старые записи примерно 1983 года, времен аспирантуры.

"Безвремененное племя,
безвремененный народ,
в безвремененное время,
безвремененно живет".

Такое было время.
Tags: ,

Using plantuner for debugging indexes in PostgreSQL
trekking, Himalaya
obartunov
Many years ago we developed Plantuner to facilitate the process of debugging indexes - we wanted to specify which index out of bunch of them to use. The trick is to hide all indexes and uncover for optimizer the specific one.

Read more...Collapse )

Free json datasets
trekking, Himalaya
obartunov
This is a technical post about json[b] datasets I used in presentations (http://www.sai.msu.su/~megera/postgres/talks/). We will make them available in http://sqlfiddle.postgrespro.ru/#!21/,
which one can use to play with our SQL/JSON (SQL-2016 standard) implementation in PostgreSQL.

Read more...Collapse )

ICU в постгресе
trekking, Himalaya
obartunov
Решил проверить, как работает ICU в постгресе под маком и линуксом. В частности, меня интересовала возможность использования abbreviated keys, которую включили в 10-ке как раз вместе с поддержкой ICU. Если кратко, то CREATE INDEX под MAC OS X High Sierra для текста с кириллицей ускорился в 23 раза, а под Linux всего 1.4. Для английского текста под маком ускорение было около 10 раз.

Read more...Collapse )

collprovider показывает источник локали - 'c' - libc, 'i' - icu.

Замечание 2: Пост Гейгана про то, что можно сделать с ICU - https://goo.gl/Y4KsWe

Вывод такой, что основной выигрыш от использования ICU - это стабильная локаль. Хорошо бы кто-нибудь прогнал скрипт под Windows.
Tags: ,