Обсуждение: pg_upgrading to 10.1 corrupts (varchar,varchar) UNIQUE indices
I forgot to mention the real problem: the mentioned unique constraint didn't work and allowed duplicate rows to get inserted into the table until the duplicates were manually removed the the index was rebuilt.
On Fri, Dec 22, 2017 at 11:56 AM, rihad <rihad@mail.ru> wrote: > I forgot to mention the real problem: the mentioned unique constraint didn't > work and allowed duplicate rows to get inserted into the table until the > duplicates were manually removed the the index was rebuilt. You should run amcheck functions on both environments, against all indexes, to see where the inconsistency arose, and to isolate any other inconsistencies that may have been missed. While amcheck is available from contrib in Postgres 10, you can get a version that will work on other versions through OS packages for most major Linux distributions. See: https://github.com/petergeoghegan/amcheck Note also that only this external version has the "heapallindexed" check. -- Peter Geoghegan
On 12/22/2017 05:32 PM, Peter Geoghegan wrote: > On Fri, Dec 22, 2017 at 11:56 AM, rihad <rihad@mail.ru> wrote: >> I forgot to mention the real problem: the mentioned unique constraint didn't >> work and allowed duplicate rows to get inserted into the table until the >> duplicates were manually removed the the index was rebuilt. > You should run amcheck functions on both environments, against all > indexes, to see where the inconsistency arose, and to isolate any > other inconsistencies that may have been missed. While amcheck is > available from contrib in Postgres 10, you can get a version that will > work on other versions through OS packages for most major Linux > distributions. See: > > https://github.com/petergeoghegan/amcheck > > Note also that only this external version has the "heapallindexed" check. > Hm, interesting. It doesn't look like FreeBSD ports include the amcheck extension, but I could still use the versions bundled with postgres 10.1-contrib. Also, the version included doesn't allow a second boolean argument. I first ran it on a reindexed index, which didn't show any problems, as expected. Then I ran it on an unfixed broken index. foo=# create extension amcheck; CREATE EXTENSION foo=# select bt_index_check('index_translations_on_locale_and_key'); bt_index_check ---------------- (1 row) foo=# select bt_index_check('index_users_on_email_and_type'); ERROR: item order invariant violated for index "index_users_on_email_and_type" DETAIL: Lower index tid=(3,25) (points to index tid=(26,1)) higher index tid=(3,26) (points to index tid=(27,1)) page lsn=0/0.
On 12/22/2017 07:01 PM, rihad wrote: > [snip] > foo=# select bt_index_check('index_users_on_email_and_type'); > ERROR: item order invariant violated for index > "index_users_on_email_and_type" > DETAIL: Lower index tid=(3,25) (points to index tid=(26,1)) higher > index tid=(3,26) (points to index tid=(27,1)) page lsn=0/0. > Interestingly, another (varchar,varchar) index on a completely different table doesn't have the problem because it probably doesn't contain any non-ascii symbols. gateway_reference | character varying(255) | | | gateway_name | character varying | | | foo=# select bt_index_check('index_transactions_on_gateway_name_and_gateway_reference'); bt_index_check ---------------- (1 row)
I wonder why the errors don't show up under 9.6.6 and only started appearing after the pg_upgrade to 10.1? Both severs naturally used the same OS environment.
I have no idea. Maybe something to do with not actually using the same collation provider?
The freebsd package contains modifications to make icu work, or at least did. Possibly, you just stopped using the same collation provider, without even realizing it, because they switched over to a new provider without accounting for pg_upgrade. If so, then that's a bug in the package. This is a total speculation, but makes a certain amount of sense to me.
--
Peter Geoghegan
(Sent from my phone)
Peter Geoghegan
(Sent from my phone)
On 12/22/2017 07:43 PM, Peter Geoghegan wrote: > I have no idea. Maybe something to do with not actually using the same > collation provider? > > The freebsd package contains modifications to make icu work, or at > least did. Possibly, you just stopped using the same collation > provider, without even realizing it, because they switched over to a > new provider without accounting for pg_upgrade. If so, then that's a > bug in the package. This is a total speculation, but makes a certain > amount of sense to me. Exactly! 9.6.6 port options include ICU: OPTIONS_FILE_UNSET+=DEBUG OPTIONS_FILE_UNSET+=DTRACE OPTIONS_FILE_UNSET+=GSSAPI OPTIONS_FILE_SET+=ICU OPTIONS_FILE_SET+=INTDATE OPTIONS_FILE_UNSET+=LDAP OPTIONS_FILE_UNSET+=NLS OPTIONS_FILE_UNSET+=OPTIMIZED_CFLAGS OPTIONS_FILE_UNSET+=PAM OPTIONS_FILE_UNSET+=SSL OPTIONS_FILE_SET+=TZDATA OPTIONS_FILE_SET+=XML OPTIONS_FILE_UNSET+=MIT_KRB5 OPTIONS_FILE_UNSET+=HEIMDAL_KRB5 10.1 options don't: OPTIONS_FILE_UNSET+=DEBUG OPTIONS_FILE_UNSET+=DTRACE OPTIONS_FILE_UNSET+=GSSAPI OPTIONS_FILE_SET+=INTDATE OPTIONS_FILE_UNSET+=LDAP OPTIONS_FILE_UNSET+=NLS OPTIONS_FILE_UNSET+=OPTIMIZED_CFLAGS OPTIONS_FILE_UNSET+=PAM OPTIONS_FILE_UNSET+=SSL OPTIONS_FILE_SET+=TZDATA OPTIONS_FILE_SET+=XML Thank you and others who have responded so much for your tips!
Please report this as a bug to the freebsd package maintainer.
--
Peter Geoghegan
(Sent from my phone)
Peter Geoghegan
(Sent from my phone)
On 12/22/2017 08:07 PM, Peter Geoghegan wrote: > Please report this as a bug to the freebsd package maintainer. You mean they would have to bring back ICU support as an option? Ok, I'll try to report the problem.
On Fri, Dec 22, 2017 at 4:16 PM, rihad <rihad@mail.ru> wrote: > You mean they would have to bring back ICU support as an option? Ok, I'll > try to report the problem. The FreeBSD package previously contained a hacked-in ICU library, meaning that the version of Postgres shipped with FreeBSD was technically a fork, in order to compensate for the previous lack of collations/locales in FreeBSD's libc. Now that FreeBSD has built-in libc collations, and now that ICU is available as an option (a fully recognized alternative "collation provider") in Postgres 10, it would make sense for FreeBSD to "unfork", which is what I assume happened here. As I said, it's not that much of a stretch to suppose that pg_upgrade was overlooked, because at least everything else would work fine. The way that ICU was supported (which presumably changed with the "unfork") is going to break Postgres assumptions about the immutability of OS collations. IIRC, the forked code more or less pretended that ICU functions were the actual OS libc collation functions (which are assumed immutable). If I have everything right here, this is more or less equivalent to a total behavioral change in OS collations, even though the OS collation behavior apparently did not change. I'm currently feeling too lazy to check that I guessed right about all of this, but somebody should look into it. -- Peter Geoghegan
Here's the prompt response I got from the maintainer of FreeBSD package, girgen@
-------- Forwarded Message --------
Subject: | Re: pg_upgrading to 10.1 breaks some indices maybe due to changing providers for ICU |
---|---|
Date: | Fri, 22 Dec 2017 17:44:41 +0100 |
From: | Palle Girgensohn <girgen@pingpong.net> |
To: | rihad <rihad@mail.ru> |
Yes that is a knogen problem. I ahould document it better. The Built in support for icu is not the same as the patch. For upgrade to work and indeed for expected old behaviour of icu to work, the patch still needs to be applied for pg10. This work has not been done yet. Palle > 22 dec. 2017 kl. 17:22 skrev rihad <rihad@mail.ru>: > > Hi, please see my original post describing the problem: https://www.postgresql.org/message-id/e6f53eeb-ef46-3095-cd34-88550904fa02%40mail.ru > > and a reply for a possible cause: https://www.postgresql.org/message-id/e6f53eeb-ef46-3095-cd34-88550904fa02%40mail.ru > .
Here's the prompt response I got from the maintainer of FreeBSD package, girgen@
-------- Forwarded Message --------
Subject: | Re: pg_upgrading to 10.1 breaks some indices maybe due to changing providers for ICU |
---|---|
Date: | Fri, 22 Dec 2017 17:44:41 +0100 |
From: | Palle Girgensohn <girgen@pingpong.net> |
To: | rihad <rihad@mail.ru> |
Yes that is a knogen problem. I ahould document it better. The Built in support for icu is not the same as the patch. For upgrade to work and indeed for expected old behaviour of icu to work, the patch still needs to be applied for pg10. This work has not been done yet. Palle > 22 dec. 2017 kl. 17:22 skrev rihad <rihad@mail.ru>: > > Hi, please see my original post describing the problem: https://www.postgresql.org/message-id/e6f53eeb-ef46-3095-cd34-88550904fa02%40mail.ru > > and a reply for a possible cause: https://www.postgresql.org/message-id/e6f53eeb-ef46-3095-cd34-88550904fa02%40mail.ru > .