BUG #15597: possible bug in amcheck/amcheck_next (or corrupted index?)

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: kunert(at)cms(dot)hu-berlin(dot)de
Subject: BUG #15597: possible bug in amcheck/amcheck_next (or corrupted index?)
Date: 2019-01-17 13:37:43
Message-ID: 15597-294e5d3e7f01c407@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15597
Logged by: Andreas Kunert
Email address: kunert(at)cms(dot)hu-berlin(dot)de
PostgreSQL version: 11.1
Operating system: Ubuntu 16.04.5 LTS
Description:

Hello,

I observed the following behavior in Postgres 9.6.?/10.6/11.1 running under
Debian/Debian/Ubuntu on three different servers:

CREATE TABLE foo
(
a integer,
b character(255),
c character(255),
d character(255),
e character(25),
g date,
h date,
i date,
j character(255),
k character(255),
m character(10),
n character(255),
o character(1),
p character(4),
q character(1),
r integer,
t character(255),
u character(50),
v character(100)
);
CREATE INDEX i_foo ON foo USING btree (b COLLATE pg_catalog."default", c
COLLATE pg_catalog."default");

select oid, relname from pg_class where relname like 'i_foo';
-- result is e.g. 12345

select bt_index_check(12345, true);
-- result: everything ok

INSERT INTO foo(a,b,c,d,e,g,h,i,j,k,m,n,o,p,q,r,t,u,v) VALUES ('1', 'b',
'c', 'd', 'e', '2000-01-01', '2000-01-01', NULL, 'j', 'k', 'm', 'n', 'o',
'p', 'q', '2', 't', 'u', 'v');
-- result: ok

select bt_index_check(12345, true);
-- result: ERROR: heap tuple (0,1) from table "foo" lacks matching index
tuple within index "i_foo"

reindex table foo;
select bt_index_check(12345, true);
-- result: everything ok again, but after adding another row:

INSERT INTO foo(a,b,c,d,e,g,h,i,j,k,m,n,o,p,q,r,t,u,v) VALUES ('1', 'b',
'c', 'd', 'e', '2000-01-01', '2000-01-01', NULL, 'j', 'k', 'm', 'n', 'o',
'p', 'q', '2', 't', 'u', 'v');

select bt_index_check(12345, true);
-- result: ERROR: heap tuple (0,2) from table "foo" lacks matching index
tuple within index "i_foo"

Despite the error message I suspect the index being ok since I can find the
aforementioned tuples by using it:

explain select * from foo where b='b' and c='c'
-- result: Index Scan using i_foo on foo...
select * from foo where b='b' and c='c'
-- result: 2 rows

I tried to simplify the example table as much as possible - if I remove more
columns or reduce some of the char(n) lengths, the error does not appear.

Moreover I hope you can reproduce the behavior.

Regards,
Andreas

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sandeep Thakkar 2019-01-17 15:11:18 Re: Installation issue
Previous Message Jadhav, Manadeep (BHGE) 2019-01-17 12:55:29 Issue while non-english character in userid and password