366 stories
·
1 follower

Peter Eisentraut: More robust collations with ICU support in PostgreSQL 10

1 Share

In this article, I want to introduce the ICU support in PostgreSQL, which I have worked on for PostgreSQL version 10, to appear later this year.

Sorting

Sorting is an important functionality of a database system. First, users generally want to see data sorted. Any query result that contains more than one row and is destined for end-user consumption will probably want to be sorted, just for a better user experience. Second, a lot of the internal functionality of a database system depends on sorting data or having sorted data available. B-tree indexes are an obvious example. BRIN indexes have knowledge of order. Range partitioning has to compare values. Merge joins depend on sorted input. The idea that is common to these different techniques is that, roughly speaking, if you have sorted data and you know what you are looking for, it makes it much faster to locate the place where it should be found.

There are two important aspects to sorting. One is the sorting algorithm. This is a standard topic in computer science, and a lot of work has gone into PostgreSQL over the years to refine the various sorting algorithms and methods, but that’s not what I will write about. The other is deciding in what order things should be, which is what we call collation. In many cases, that choice is obvious. 1 comes before 2. FALSE comes before TRUE … well, someone just arbitrarily decided that one. A usually comes before B. But when it comes to natural language text, things get interesting. There are many different ways to order text, and the actual methods to collate text strings are more complicated than might be apparent. Different languages prefer different sort orders, but even within a language, there can be variations for different applications. And there are details to worry about, such as what to do about whitespace, punctuation, case differences, diacritic marks, and so on. Look up the Unicode Collation Algorithm for more insight into this.

Before the ICU feature was committed, all this functionality what facilitated by the C library in the operating system. PostgreSQL basically just passes strings to strcmp(), strcoll(), and the like and works with the result. The C libraries in the various operating systems implement the various collation variants and nuances mentioned above to different levels of functionality and quality, so PostgreSQL can do what your operating system can do.

Changing collations

Problems start if the operating system ever needs to change a collation it provides. Why would they want to do that? It could be that the previous collation was wrong and had to be fixed. Maybe a new standard for a language was published and the collation is to be updated for that. Maybe the internal representation of collation and string data was changed for performance reasons or because it was necessary to implement additional functionality. For many programs, this is not an issue. You might just see a slightly differently ordered output, if you notice a difference at all. For a database system, however, this is a major problem. As described above, PostgreSQL stores sorted data in indexes and other places and relies on the sort order to be correct. If the sort order is not correct, an index lookup might not find data that is actually there. Or a write to an index will write to a different place. Or data is written to or read from the wrong partition. This can lead to erroneously duplicate data or the appearance of data loss because data is not where it is looked for. In other words, it can lead to data corruption and (apparent) data loss.

Unfortunately, there was not much we could do about it so far. Operating systems update their collations whenever they feel like it, perhaps as part of an upgrade to their C library package. There is no way to find out about this in a reasonable way, or than perhaps by inspecting the update packages in detail. And even then, will you reject an important update of your C library because you noticed that the collation in some locale you are not using was changed? It was a very uncomfortable situation.

Enter ICU

So where does ICU come in? ICU, International Components for Unicode, is a library that provides internationalization and localization facilities, including collation. So in that respect, it is an alternative to using the facilities in the standard C library. The nice thing is that ICU explicitly provides some guarantees about the stability of collations:

  • A collation will not be changed in an incompatible way as part of a minor release update.
  • A collation has a version, which can be inspected, and when a collation changes in an incompatible way, the version changes.

For users of PostgreSQL, this will mean in practice:

  • Routine operating system package updates will not interfere with the validity of sorted data. Since a postgres binary is linked to a particular major version of libicu, routine operating system package upgrades will not end up with postgres being linked to a new major version of libicu, as long as a) you don’t update the PostgreSQL packages, or b) the PostgreSQL packages are still linked to the same major version of ICU as before. Packagers will need to be careful to maintain this properly, but that shouldn’t be too problematic in practice.
  • When major package and operating system upgrades do change the version of a collation, we have a way to detect that and warn the user. Right now we just warn and offer some guidelines and tools to fix things, but in the future we might refine and automate this further.

(To make this more explicit for packagers: In a stable branch of your operating system, you should not change the major ICU version that a given PostgreSQL package set is linked with.)

Using ICU

To be able to use this, PostgreSQL needs to be built explicitly with ICU support. When building from source, use ./configure --with-icu along with other desired options. We expect most major binary packages to offer this by default as well. When this is done, ICU-based collations are offered alongside the libc-based collations that previous releases offered. (So building with ICU support does not remove libc collation support; the two exist together.) Check the documentation for details on how to select an ICU-based collation versus a libc-based one. For example, if you had previously specified

CREATE TABLE ... (... x text COLLATE "en_US" ...)

you might now do

CREATE TABLE ... (... x text COLLATE "en-x-icu" ...)

This should give you roughly the same user-visible behavior as before, except that your database will be more future-proof when it comes to upgrading. (On Linux/glibc, the sort order should be mostly the same, but there could be small differences in some details. If, however, you are using an operating system whose C library does not support Unicode collation at all, such as macOS or older versions of FreeBSD, then this will be a major change — for the better.)

Currently, ICU support is only available for explicitly specified collations. The default collation in a database is still always provided by the C library. Addressing this is a future project.

If you upgrade such a database by pg_upgrade for example to a new PostgreSQL installation that is linked with a newer major version of ICU that has changed the collation version of that collation you are using, then you will get a warning and will have to fix up for example any indexes that depend on the collation. Instructions for this are also in the documentation.

Abbreviated keys

So this change will provide some very important improvements for long-term robustness of a database system. But ICU is also an improvement over the system C library in other areas.

For example, PostgreSQL B-trees can store what are called abbreviated keys to improve performance and storage. For text string data types, with the standard C library, we would compute these abbreviated keys using the strxfrm() function. However, we have learned that many C libraries have a variety of bugs and misbehaviors that make this approach not reliable. So the abbreviated keys optimization is currently disabled for string data types. With ICU, we can use the equivalent API calls and compute abbreviated keys in what we believe is a reliable and stable way. So there are possible performance improvements from this move as well.

More collations

Apart from these internal improvements of robustness and performance, there is also some new user-facing functionality.

For some languages, more than one sort order might be relevant in practice. (This might get you started.) One example is that for German, there is a standard sort order that is used for most purposes and a “phone book” sort order that is used for lists of names. The standard C library only provides one of those variants (probably the first one). But if you want to write an application that properly sorts, say, both product names and customer names, you need to be able to use both.

For example, the example from the German Wikipedia can now be reproduced with PostgreSQL:

CREATE TABLE names (name text);

INSERT INTO names
    VALUES ('Göbel'), ('Goethe'), ('Goldmann'), ('Göthe'), ('Götz');

=> SELECT name FROM names ORDER BY name COLLATE "de-u-co-standard-x-icu";
   name
----------
 Göbel
 Goethe
 Goldmann
 Göthe
 Götz

=> SELECT name FROM names ORDER BY name COLLATE "de-u-co-phonebk-x-icu";
   name
----------
 Göbel
 Goethe
 Göthe
 Götz
 Goldmann

=> SELECT name FROM names ORDER BY name COLLATE "de-AT-u-co-phonebk-x-icu";
   name
----------
 Goethe
 Goldmann
 Göbel
 Göthe
 Götz

(With glibc, COLLATE "de_DE" and COLLATE "de_AT" indeed return the first order.)

One interesting way to combine several features might be to use domains to model the above mentioned difference between product names and customer names:

CREATE DOMAIN product_name AS text COLLATE "de-u-co-standard-x-icu";
CREATE DOMAIN person_name AS text COLLATE "de-u-co-phonebk-x-icu";

(This is just an example. Of course you can also attach those COLLATE clauses to column definitions directly or use them in queries.)

Even more collations

Finally, and this is clearly what the world had been waiting for, there is now a way to properly sort emojis. This is essential to ensure that all your cat faces are in the proper order. Compare

=# SELECT chr(x) FROM generate_series(x'1F634'::int, x'1F644'::int) AS _(x)
       ORDER BY chr(x) COLLATE "und-x-icu";
 chr
-----
 😴
 😵
 😶
 😷
 😸
 😹
 😺
 😻
 😼
 😽
 😾
 😿
 🙀
 🙁
 🙂
 🙃
 🙄

with

=# SELECT chr(x) FROM generate_series(x'1F634'::int, x'1F644'::int) AS _(x)
       ORDER BY chr(x) COLLATE "und-u-co-emoji-x-icu";
 chr
-----
 🙂
 😶
 🙄
 😴
 🙃
 🙁
 😵
 😷
 😺
 😸
 😹
 😻
 😼
 😽
 🙀
 😿
 😾

Yes, there is actually a standard about this.

More to come

This is just the beginning. ICU offers a lot of functionality in this area that we are not exposing through PostgreSQL yet. There are options for case-insensitive sorting, accent-insensitive sorting, and totally customizing a collation. Look for those in future PostgreSQL releases.

Read the whole story
internetionals
7 days ago
reply
Netherlands
Share this story
Delete

Vasilis Ventirozos: Reusing an old master (as slave) after a switchover

1 Share
Todays blogpost is old news but it's not very well known and it has to do with how to add an old master after a slave has been promoted. Fujii Masao explained the situation in his patch back in the day.

So in todays post i will be demonstrating a combination of replication slots for retaining the writes on a promoted slave and how to re-introduce an old master to the replication.

Say that we have a master-slave setup, stop the master gracefully and create and activate a replica slot on the slave :

monkey=# SELECT * FROM pg_create_physical_replication_slot('this_is_a_replica_slot');
       slot_name        | xlog_position
------------------------+---------------
 this_is_a_replica_slot |
(1 row)

postgres@bf9823730feb:~$ pg_receivexlog -D . -S this_is_a_replica_slot -v -h 10.0.0.3 -U repuser
pg_receivexlog: starting log streaming at 0/4000000 (timeline 1) ^C
pg_receivexlog: received interrupt signal, exiting
pg_receivexlog: not renaming "000000010000000000000004.partial", segment is not complete
pg_receivexlog: received interrupt signal, exiting


This way all the changes after the slave promotion will be retained. Now , lets promote the slave and check the replication slot status.

postgres@bf9823730feb:~$ pg_ctl promote
server promoting

postgres@bf9823730feb:~$ psql -c "SELECT slot_name, database,active,  pg_xlog_location_diff(pg_current_xlog_insert_location(), restart_lsn) AS retained_bytes FROM pg_replication_slots;" postgres
       slot_name        | database | active | retained_bytes
------------------------+----------+--------+----------------
 this_is_a_replica_slot |          | f      |           9056

As you can see , the new master is now retaining all writes. Now, to the old master, lets make a recovery.conf that looks like this :

standby_mode = 'on'
primary_conninfo = 'user=repuser host=10.0.0.3 port=5432 application_name=a_slave'
trigger_file = '/home/postgres/pgdata/finish.recovery'
primary_slot_name = 'this_is_a_replica_slot'
recovery_target_timeline = 'latest'

where host is obviously the new master. 
Start the old master and you should see something similar to:

LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/5000098
LOG:  database system is ready to accept read only connections
LOG:  invalid record length at 0/5000098: wanted 24, got 0
LOG:  fetching timeline history file for timeline 2 from primary server
LOG:  started streaming WAL from primary at 0/5000000 on timeline 1
LOG:  replication terminated by primary server
DETAIL:  End of WAL reached on timeline 1 at 0/5000098.
LOG:  new target timeline is 2
LOG:  restarted WAL streaming at 0/5000000 on timeline 2
LOG:  redo starts at 0/5000098


This would also work if you had archiving enabled instead of replication slots. Given that you have plenty of available disk space on the new master, it should be fine to keep the old master down for any reasonable amount of time and re-introduce it to the replication without issues.

The only 2 things that you have to make sure is to shutdown postgres on old-master gracefully and that the slave has caught up before the promotion.


Thanks for reading.




Read the whole story
internetionals
10 days ago
reply
Netherlands
Share this story
Delete

Waiting for PostgreSQL 10 – Implement multivariate n-distinct coefficients

1 Share
I missed it completely, but on 24th of March 2017, Alvaro Herrera committed patch: Implement multivariate n-distinct coefficients Add support for explicitly declared statistic objects (CREATE STATISTICS), allowing collection of statistics on more complex combinations that individual table columns. Companion commands DROP STATISTICS and ALTER STATISTICS ... OWNER TO / SET SCHEMA / RENAME are […]
Read the whole story
internetionals
10 days ago
reply
Netherlands
Share this story
Delete

Holly Orr: Installing pgDevOps

1 Share

Install in 6 easy steps

Never used the BigSQL PostgreSQL Distributions before? Follow these 5 easy steps. Already have BigSQL pgc installed? Jump to step #3.

  1. Read this short introduction to BigSQL’s package and update manager (pgc).

  2. Install pgc via command line to a sandbox:

    MAC / Linux:

    python -c "$(curl -fsSL http://s3.amazonaws.com/pgcentral/install.py)" 
    

    Windows:

    @powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://s3.amazonaws.com/pgcentral/install.ps1'))"
    
  3. Get into the product home directory by navigating via command line to the bigsql directory. Windows users don’t prefix the pgc command with ./ as shown in the below examples:

    cd bigsql
    
  4. Using the pgc command line tool, run the update command to get the lastest distribution:

    ./pgc update
    
  5. Then run the install, init, and start commands on pgdevops:

    ./pgc install pgdevops
    ./pgc init pgdevops
    ./pgc start pgdevops
    
  6. Open pgDevOps in browser:

    localhost:8051
    

What you get:

Easily build PostgreSQL development environments:

Eliminate wait times by giving developers the power to standup precompiled PostgreSQL versions on Windows, Linux, and Mac.

One-click PostgreSQL component installs:

In the package manager dashboard choose from an extensive list of community projects to extend your PostgreSQL capabilities.

Monitor your PostgreSQL databases:

Intuitive graphs, metrics, and one-click access to logfiles make it easy to monitor the implications of changes to code, queries, and infrastructure.

The plProfiler Console creates performance profiles of PL/pgSQL functions and stored procedures and increase efficiency of your code.

The pgBadger Console gives you advanced log analysis features.

Want to learn more about the DevOps philosphy behind BigSQL’s pgDevOps development? Read DevOps: Can’t we just all get along?

Got questions or suggestions? Get in touch with us at BigSQL Feedback.

Read the whole story
internetionals
11 days ago
reply
Netherlands
Share this story
Delete

Michael Paquier: Postgres 10 highlight - Incompatible changes

1 Share

Postgres 10 will be released in a couple of months, with its beta 1 to be out very soon, and for this release many small, incompatible changes have been introduced during its development to help with long-term maintenance. The full list of items is present in the release notes, and here are the changes that can have an impact on any applications relying of what PostgreSQL has provided up to now.

Here are the changes related to the on-disk format of the data folders.

  • pg_xlog has been renamed to pg_wal. This has primarily consequences for any application handling with backup and restore, though the change to get the update right should be straight-forward.
  • pg_clog has been renamed to pg_xact.
  • Default value for log_directory has been changed from “pg_log” to “log”. Most applications likely set this parameter to an absolute path anyway, hopefully pointing to a different partition. Or not.

The idea behind the first two items is to protect users always tempted to perform a “rm -rf *log” in a data folder if the partition where data is present gets full to free up some space. This would result in a corrupted cluster.

Some commands have changes in option names, as well as default behavior changes. Some commands are removed.

  • pg_basebackup has changed the default value of –xlog-method to “stream”, and “none” can be used to get the past behavior. It has been proven that users like being able to rely on self-contained, consistent backups. The option -x has been removed as well, being replaced by “-X fetch”.
  • pg_ctl not waits for all its subcommands to wait for completion by default. Note that if your application has relied on “start” mode leaving immediately when starting an instance to start recovery, pg_ctl would wait also until the server has reached a consistent state. Better to be careful about that. “stop” has been always using the wait mode.
  • createlang and droplang are no more. RIP.

From the system-side of things, many things are piling up:

  • Cleartext password support has been removed, trying for example to create a role with UNENCRYPTED PASSWORD will return an error.
  • Any application using hash indexes need to reindex them after an upgrade. WAL-logging support for hash indexes has been added, and many enhauncements have been done as well. Since the introduction of streaming replication, hash indexes have not been that popular, but compared to btree hash indexes are an advantage when doing equal operations on columns with a high cardinality as less data pages need to be fetched to look for an index entry.
  • Support for version-0 functions has been removed. Most systems should be using version-1 anyway today.
  • Support for protocol version 1 is removed.

From the configuration side, there are three incompatible changes:

  • min_parallel_relation_size is replaced by min_parallel_table_scan_size and min_parallel_index_scan_size to control better paralle queries.
  • password_encryption has been changed from a boolean switch to an enum, with support for “md5” and “scram-sha-256”. Note that this is in line with removal of cleartext passwords and addition of support for SCRAM-SHA-256 as a new hashing mechanism for passwords.
  • sql_inheritance has been removed.

On top of that, another bigger change has happened with the removal of the term “xlog” in system functions and binaries, replaced by “wal”.

  • Functions like pg_current_xlog_lsn are renamed to pg_current_wal_lsn. So if you maintain a monitoring script be careful that it would break.
  • pg_receivexlog is renamed to pg_receivewal.
  • pg_basebackup’s –xlog-method is renamed to –wal-method.

Finally the version number has changed, switching from a 3-digit numbering to a 2-digit numbering. By working on C extensions, things don’t change much except that PG_VERSION_NUM now has 6 digits. This is actually something that may break applications relying on the old version string format fetched for example by the SQL-level function version(). So be very careful about that as well.

Read the whole story
internetionals
11 days ago
reply
Netherlands
Share this story
Delete

Bruce Momjian: Postgres Window Magic

1 Share

Having given the first presentation of my new window function talk, Postgres Window Magic, I have now put the slides online.

Read the whole story
internetionals
15 days ago
reply
Netherlands
Share this story
Delete
Next Page of Stories