Thread:
Hi,
Somehow I got duplicate values in a column covered by a unique key.
CREATE TABLE myschema.vocabulary
(
phrase_id integer NOT NULL DEFAULT nextval('onto_oper.vocabulary_phraseid_seq'::regclass),
phrase text COLLATE pg_catalog."default" NOT NULL,
is_to_sync boolean NOT NULL DEFAULT true,
last_modified timestamp without time zone NOT NULL DEFAULT timezone('utc'::text, now()),
CONSTRAINT pk_vocabulary PRIMARY KEY (phrase_id),
CONSTRAINT vocabulary_phrase_key UNIQUE (phrase)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Today I noticed a strange behaviour in a regularly executed routine. The problem seemed to be absolutely irrelevant:
ERROR: duplicate key value violates unique constraint "vocabulary_phrase_key"
DETAIL: Key (phrase)=(Ã…sa) already exists.
CONTEXT: SQL statement "update myschema.vocabulary set is_to_sync = false where is_to_sync = true"
It was unclear how an update of an independent column could cause such an error, but I started searching duplicate entries. GROUP BY on phrase column did not give any results HAVING count(*) > 1. Then I copied the data into another table, without indices. This gave the answer there is a duplicated value 'Åsa' (I guess the same could be done by switching off index scan).
From the copy I found the IDs of problem entries: 5850963 and 29883128.
With best wishes,
Vladimir
Somehow I got duplicate values in a column covered by a unique key.
CREATE TABLE myschema.vocabulary
(
phrase_id integer NOT NULL DEFAULT nextval('onto_oper.vocabulary_phraseid_seq'::regclass),
phrase text COLLATE pg_catalog."default" NOT NULL,
is_to_sync boolean NOT NULL DEFAULT true,
last_modified timestamp without time zone NOT NULL DEFAULT timezone('utc'::text, now()),
CONSTRAINT pk_vocabulary PRIMARY KEY (phrase_id),
CONSTRAINT vocabulary_phrase_key UNIQUE (phrase)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
Today I noticed a strange behaviour in a regularly executed routine. The problem seemed to be absolutely irrelevant:
ERROR: duplicate key value violates unique constraint "vocabulary_phrase_key"
DETAIL: Key (phrase)=(Ã…sa) already exists.
CONTEXT: SQL statement "update myschema.vocabulary set is_to_sync = false where is_to_sync = true"
It was unclear how an update of an independent column could cause such an error, but I started searching duplicate entries. GROUP BY on phrase column did not give any results HAVING count(*) > 1. Then I copied the data into another table, without indices. This gave the answer there is a duplicated value 'Åsa' (I guess the same could be done by switching off index scan).
From the copy I found the IDs of problem entries: 5850963 and 29883128.
Some funny queries:
select
(select phrase from myschema.vocabulary where phrase_id = 5850963) =
(select phrase from myschema.vocabulary where phrase_id = 29883128)
Gives 'true'.
select phrase_id from myschema.vocabulary
where phrase = (select phrase from myschema.vocabulary where phrase_id = 5850963)
Gives 29883128.
Is it some misconfiguration problem in my database? Since I see some non-ASCII Unicode characters, I suppose it might be encoding conversion problem. I inspected both values to the best of my abilities and found them equal byte-to byte.
My version() is PostgreSQL 10.10 (Ubuntu 10.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
select
(select phrase from myschema.vocabulary where phrase_id = 5850963) =
(select phrase from myschema.vocabulary where phrase_id = 29883128)
Gives 'true'.
select phrase_id from myschema.vocabulary
where phrase = (select phrase from myschema.vocabulary where phrase_id = 5850963)
Gives 29883128.
Is it some misconfiguration problem in my database? Since I see some non-ASCII Unicode characters, I suppose it might be encoding conversion problem. I inspected both values to the best of my abilities and found them equal byte-to byte.
My version() is PostgreSQL 10.10 (Ubuntu 10.10-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
With best wishes,
Vladimir
On Tue, Sep 24, 2019 at 1:55 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
> I inspected both values to the best of my abilities and found them equal byte-to byte.
I would try running contrib/amcheck, which should be able to confirm
index corruption, and give you a specific complaint. You may then be
able to inspect the exact index page with the problem using
contrib/pageinspect. Something like this ought to do it on Postgres
10:
CREATE EXTENSION IF NOT EXISTS amcheck
SELECT bt_index_check('vocabulary_phrase_key');
If that doesn't show any errors, then perhaps try this:
SELECT bt_index_parent_check('vocabulary_phrase_key');
Let us know what you see. We may be able to offer additional guidance,
depending on how that goes.
--
Peter Geoghegan
Peter, thank you for you response.
bt_index_check() shows this:
ERROR: item order invariant violated for index "vocabulary_phrase_key"
bt_index_check() shows this:
ERROR: item order invariant violated for index "vocabulary_phrase_key"
DETAIL: Lower index tid=(29915,116) (points to index tid=(97262,1)) higher index tid=(29915,117) (points to index tid=(118496,1)) page lsn=5A8/6AA20EC8.
вт, 24 сент. 2019 г. в 14:04, Peter Geoghegan <pg@bowt.ie>:
>
> On Tue, Sep 24, 2019 at 1:55 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
> I would try running contrib/amcheck, which should be able to confirm
> index corruption, and give you a specific complaint. You may then be
> able to inspect the exact index page with the problem using
> contrib/pageinspect. Something like this ought to do it on Postgres
> 10:
>
> CREATE EXTENSION IF NOT EXISTS amcheck
> SELECT bt_index_check('vocabulary_phrase_key');
>
SQL state: XX002
--
--
Vladimir
вт, 24 сент. 2019 г. в 14:04, Peter Geoghegan <pg@bowt.ie>:
>
> On Tue, Sep 24, 2019 at 1:55 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
> I would try running contrib/amcheck, which should be able to confirm
> index corruption, and give you a specific complaint. You may then be
> able to inspect the exact index page with the problem using
> contrib/pageinspect. Something like this ought to do it on Postgres
> 10:
>
> CREATE EXTENSION IF NOT EXISTS amcheck
> SELECT bt_index_check('vocabulary_phrase_key');
>
On Tue, Sep 24, 2019 at 5:28 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
> bt_index_check() shows this:
>
> ERROR: item order invariant violated for index "vocabulary_phrase_key"
> DETAIL: Lower index tid=(29915,116) (points to index tid=(97262,1)) higher index tid=(29915,117) (points to index
tid=(118496,1))page lsn=5A8/6AA20EC8.
> SQL state: XX002
That's slightly inconvenient, because it's an internal page -- there
is no heap TID that you can use to look at the row in the table
directly. You're going to have to look at the index page directly, and
try to make sense of pageinspect's hex representation of the data.
The following will show details of the pair of tuples that are in the
wrong order here:
CREATE EXTENSION pageinspect;
SELECT * FROM bt_page_items('vocabulary_phrase_key', 29915) WHERE
itemoffset in (116, 117);
What does that look like?
I think that the problem here is likely to have something to do with
OS collations -- this isn't a Postgres bug in all likelihood.
--
Peter Geoghegan
bt_page_items() returns two rows:
116 (97262,1) 88 false true
9b 31 39 36 38 e5 b9 b4 e3 83 a1 e3 82 ad e3 82
116 (97262,1) 88 false true
9b 31 39 36 38 e5 b9 b4 e3 83 a1 e3 82 ad e3 82
b7 e3 82 b3 e3 82 b7 e3 83 86 e3 82 a3 e3 83 bc
e3 82 aa e3 83 aa e3 83 b3 e3 83 94 e3 83 83 e3
82 af e3 81 ae e3 83 8f e3 83 b3 e3 82 ac e3 83
aa e3 83 bc e9 81 b8 e6 89 8b e5 9b a3 00 00 00
117 (118496,1) 80 false true
89 31 39 36 38 e5 b9 b4 e3 82 b0 e3 83 ab e3 83
117 (118496,1) 80 false true
89 31 39 36 38 e5 b9 b4 e3 82 b0 e3 83 ab e3 83
8e e3 83 bc e3 83 96 e3 83 ab e3 82 aa e3 83 aa
e3 83 b3 e3 83 94 e3 83 83 e3 82 af e3 81 ae e3
83 a2 e3 83 ad e3 83 83 e3 82 b3 e9 81 b8 e6 89
8b e5 9b a3 00 00 00 00
This does not make much sense to me to be honest...
Please let me know what to check on OS level.
--
Vladimir
вт, 24 сент. 2019 г. в 17:35, Peter Geoghegan <pg@bowt.ie>:
>
>
> That's slightly inconvenient, because it's an internal page -- there
> is no heap TID that you can use to look at the row in the table
> directly. You're going to have to look at the index page directly, and
> try to make sense of pageinspect's hex representation of the data.
>
> The following will show details of the pair of tuples that are in the
> wrong order here:
>
> CREATE EXTENSION pageinspect;
> SELECT * FROM bt_page_items('vocabulary_phrase_key', 29915) WHERE
> itemoffset in (116, 117);
>
> What does that look like?
>
> I think that the problem here is likely to have something to do with
> OS collations -- this isn't a Postgres bug in all likelihood.
This does not make much sense to me to be honest...
Please let me know what to check on OS level.
--
Vladimir
вт, 24 сент. 2019 г. в 17:35, Peter Geoghegan <pg@bowt.ie>:
>
>
> That's slightly inconvenient, because it's an internal page -- there
> is no heap TID that you can use to look at the row in the table
> directly. You're going to have to look at the index page directly, and
> try to make sense of pageinspect's hex representation of the data.
>
> The following will show details of the pair of tuples that are in the
> wrong order here:
>
> CREATE EXTENSION pageinspect;
> SELECT * FROM bt_page_items('vocabulary_phrase_key', 29915) WHERE
> itemoffset in (116, 117);
>
> What does that look like?
>
> I think that the problem here is likely to have something to do with
> OS collations -- this isn't a Postgres bug in all likelihood.
On Tue, Sep 24, 2019 at 6:25 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote: > bt_page_items() returns two rows: > This does not make much sense to me to be honest... It doesn't look like UTF-8, but FWIW "31 39 36 38" is 1968 in ASCII (and every other encoding supported by Postgres). That's probably the first part of the string in each case. What do you mean about encoding conversion? It is rather unlikely that a bad client application would be able to do this kind of damage. If you're using UTF-8 as your database encoding, then Postgres tends to reject malformed strings when validated on input. Even if a malformed string is accepted into the database, it is only malformed to your application -- that shouldn't cause this kind of index corruption. -- Peter Geoghegan
I meant a probable conversion step to some normalized Unicode representation, as some characters in Unicode may be represented in more than one way (e.g. NFD). But I did not have any strong evidence to support it, that was just a wild guess.
How to investigate it further? What was the cause of index corruption? How to prevent it in the future?
вт, 24 сент. 2019 г. в 18:48, Peter Geoghegan <pg@bowt.ie>:
On Tue, Sep 24, 2019 at 6:25 PM Vladimir Ryabtsev <greatvovan@gmail.com> wrote:
> bt_page_items() returns two rows:
> This does not make much sense to me to be honest...
It doesn't look like UTF-8, but FWIW "31 39 36 38" is 1968 in ASCII
(and every other encoding supported by Postgres). That's probably the
first part of the string in each case.
What do you mean about encoding conversion? It is rather unlikely that
a bad client application would be able to do this kind of damage. If
you're using UTF-8 as your database encoding, then Postgres tends to
reject malformed strings when validated on input. Even if a malformed
string is accepted into the database, it is only malformed to your
application -- that shouldn't cause this kind of index corruption.
--
Peter Geoghegan