Re: Postgres crashes at memcopy() after upgrade to PG 13.

Hi,

On Sun, Mar 14, 2021 at 10:17 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com>
wrote:

> [Dropping pgsql-general@ from the CC, because cross-posting triggers
> moderation; sorry I didn't notice that on my first reply]
>
> On Mon, Mar 15, 2021 at 2:05 PM Avinash Kumar
> <avinash(dot)vallarapu(at)gmail(dot)com> wrote:
> > On Sun, Mar 14, 2021 at 10:01 PM Avinash Kumar <
> avinash(dot)vallarapu(at)gmail(dot)com> wrote:
> >> Also the datatype is bigint
>
> Ok. Collation changes are the most common cause of index problems
> when upgrading OSes, but here we can rule that out if your index is on
> bigint. So it seems like this is some other kind of corruption in
> your database, or a bug in the deduplication code.
>
I suspect the same.
When i tried to perform a pg_filedump to see the entry of the ID in the
index, it was strange that the entry did not exist in the Index. But, the
SELECT using an Index only scan was still working okay. I have chosen the
start and end page perfectly and there should not be any mistake there.

Following may be helpful to understand what I meant.

I have renamed the table and index names before adding it here.

=# select pg_size_pretty(pg_relation_size('idx_id_mtime')) as size,
relpages from pg_class where relname = 'idx_id_mtime';
size | relpages
-------+----------
71 MB | 8439

=# select pg_relation_filepath('idx_id_mtime');
pg_relation_filepath
----------------------
base/16404/346644309

=# \d+ idx_id_mtime
Index "public.idx_id_mtime"
Column | Type | Key? | Definition | Storage | Stats
target
-----------+--------------------------+------+------------+---------+--------------
sometable_id | bigint | yes | sometable_id | plain |
mtime | timestamp with time zone | yes | mtime | plain |
btree, for table "public.sometable"

$ pg_filedump -R 1 8439 -D bigint,timestamp
/flash/berta13/base/16404/346644309 > 12345.txt

$ cat 12345.txt | grep -w 70334
--> No Output.

We don't see the entry for the ID : 70334 in the output of pg_filedump.
*But, the SELECT statement is still using the same Index. *

=*# EXPLAIN select * from sometable where sometable_id = 70334;
QUERY PLAN

--------------------------------------------------------------------------------
Index Scan using idx_id_mtime on sometable (cost=0.43..2.45 rows=1
width=869)
Index Cond: (sometable_id = 70334)
(2 rows)

=*# EXPLAIN ANALYZE select * from sometable where sometable_id = 70334;
QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_id_mtime on sometable (cost=0.43..2.45 rows=1
width=869) (actual time=0.166..0.168 rows=1 loops=1)
Index Cond: (sometable_id = 70334)
Planning Time: 0.154 ms
Execution Time: 0.195 ms
(4 rows)

=*# update sometable set sometable_id = 70334 where sometable_id = 70334;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>

Now, let us see the next ID. Here, the entry is visible in the output of
pg_filedump.

$ cat 12345.txt | grep -w 10819
COPY: 10819 2018-03-21 15:16:41.202277

The update still fails with the same error.

=*# update sometable set sometable_id = 10819 where sometable_id = 10819;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!?>