[YSQL] After executing the `insert into` statement, server closed the connection unexpectedly.

[ycybfhb]

Jira Link: DB-11885

Description

Basic Information

Version:

PostgreSQL 11.2-YB-2.21.1.0-b0 on x86_64-pc-linux-gnu, compiled by clang version 17.0.6 (https://github.com/yugabyte/llvm-project.git 9b881774e40024e901fc6f3d313607b071c08631), 64-bit

Deploy:

version: '2'

volumes:
  yb-single-data-1:
  yb-tserver-single-data-1:

services:  
  yb-single:
      image: yugabytedb/yugabyte:latest
      container_name: yb-single-n1
      volumes:
      - yb-single-data-1:/mnt/single
      command: [ "/home/yugabyte/bin/yb-master",
                "--fs_data_dirs=/mnt/single",
                "--master_addresses=yb-single-n1:7100",
                "--rpc_bind_addresses=yb-single-n1:7100",
                "--replication_factor=1"]
      ports:
      - "7001:7000"
      networks:
        yb-net:
          ipv4_address: 10.1.3.31
      environment:
        SERVICE_7000_NAME: yb-master

  yb-tserver-single:
      image: yugabytedb/yugabyte:latest
      container_name: yb-tserver-single-n1
      volumes:
      - yb-tserver-single-data-1:/mnt/tserver
      command: [ "/home/yugabyte/bin/yb-tserver",
                "--fs_data_dirs=/mnt/tserver",
                "--enable_ysql",
                "--rpc_bind_addresses=yb-tserver-single-n1:9100",
                "--tserver_master_addrs=yb-single-n1:7100"]
      environment:
        SERVICE_5433_NAME: ysql
        SERVICE_9042_NAME: ycql
        SERVICE_6379_NAME: yedis
        SERVICE_9000_NAME: yb-tserver
      depends_on:
      - yb-single
      networks:
        yb-net:
          ipv4_address: 10.1.3.41

networks:
  yb-net:
    driver: bridge
    enable_ipv6: false
    ipam:
      config:
        - subnet: 10.1.3.0/24
          gateway: 10.1.3.1

Reproduce

Firstly, connect to the database via the psql command.

Secondly, execute the statements in init.sql to create the tables.

Finally, when executing the statements in error.sql, a crash occurred, and the server closed the connection.

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: Succeeded.
psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1), server 11.2-YB-2.21.1.0-b0)

init.sql: init.sql.txt error.sql: error.sql.txt

Error Log

postgresql-2024-06-21_084101.log

I0621 10:58:11.556192  3818 mem_tracker.cc:264] Creating root MemTracker with garbage collection threshold 5242880 bytes
I0621 10:58:11.556284  3818 mem_tracker.cc:268] Root memory limit is 459554178457
I0621 10:58:11.557289  3818 thread_pool.cc:178] Starting thread pool { name: pggate_ybclient max_workers: 1024 }
I0621 10:58:11.558246  3818 pg_client.cc:363] Using TServer host_port: 10.1.3.41:9100
I0621 10:58:11.559984  3818 pg_client.cc:376] Session id 217: Session id acquired. Postgres backend pid: 3818
I0621 10:58:18.001340  3818 backoff_waiter.cc:130] Database 17922 is not ready in Yugabyte shared memory - started
I0621 10:58:18.001910  3818 backoff_waiter.cc:133] Database 17922 is not ready in Yugabyte shared memory - completed: OK
I0621 10:58:29.760111  3833 mem_tracker.cc:264] Creating root MemTracker with garbage collection threshold 5242880 bytes
I0621 10:58:29.760212  3833 mem_tracker.cc:268] Root memory limit is 459554178457
I0621 10:58:29.761363  3833 thread_pool.cc:178] Starting thread pool { name: pggate_ybclient max_workers: 1024 }
I0621 10:58:29.762223  3833 pg_client.cc:363] Using TServer host_port: 10.1.3.41:9100
I0621 10:58:29.764024  3833 pg_client.cc:376] Session id 218: Session id acquired. Postgres backend pid: 3833
2024-06-21 10:58:30.341 UTC [156] WARNING:  server process (PID 3818) was terminated by signal 11: Segmentation fault
2024-06-21 10:58:30.341 UTC [156] DETAIL:  Failed process was running: insert into t_xcqkmbf (c_iu, c_pzwyjdh2km, c_rs3fozyg64, c_z95vg_cl63, c_jz84ed84, c_jtrwg, c_yifdi8lcon, c_lsoylg) values 

    (coalesce(case when (EXISTS (
        select  
            ref_4.c3 as c0, 
            ref_4.c1 as c1, 
            ref_4.c1 as c2, 
            cast( (cast(ref_4.c2 as int8) <= cast(ref_3.c_i5x8zkfy as int8)) as boolean) as c3, 
            ref_4.c0 as c4, 
            ref_3.c_tq8u2ws as c5, 
            ref_3.c_mvxevd as c6
          from 
            (t__zkzk94_5 as ref_3
              left outer join t_y as ref_4
              on ((-9 in (
                  ref_4.c3, ref_4.c2))))
          where (ref_3.c_tq8u2ws > ( 
            select  
                  ref_3.c_tq8u2ws as c0
                from 
                  t_y as ref_5
                where ((select 1)
                     <= ( 
                  select  
                        1))

              order by c0 limit 1)))) then pg_catalog.network_cmp(
        cast(cast(null as inet) as inet), 
        cast(cast(null as inet) as inet)) else case when (pg_catalog.boollt(
            cast(true as boolean), 
            c

About Us

We are the BASS team from the School of Cyber Science and Technology at Beihang University. Our main focus is on system software security, operating systems, and program analysis research, as well as the development of automated program testing frameworks for detecting software defects. Using our self-developed database vulnerability testing tool, we have identified the above-mentioned vulnerabilities in Yugabyte that may lead to database crashes.

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• [X] I confirm this issue does not contain any sensitive information.

[ddorian]

Thank you for reporting @ycybfhb , I was able to reproduce on 2.21.1.0.

Just download the files: init.sql: init.sql.txt error.sql: error.sql.txt

And run:

./bin/ysqlsh -h 127.0.1.1 -d test0 -f init.sql.txt

./bin/ysqlsh -h 127.0.1.1 -d test0 -f error.sql.txt
ysqlsh:error.sql.txt:79: ERROR:  tupdesc reference 0x48c7d9f2038 is not owned by resource owner Portal

[karthik-ramanathan-3006]

Thank you for reporting this issue @ycybfhb.

Here's a minimal example that reproduces the issue on latest master:

-- Setup
CREATE TABLE mock (i INT PRIMARY KEY);

-- Query
INSERT INTO mock VALUES (case when (pg_catalog.boollt(true::boolean, EXISTS(select 1)) in (select true)) then 10 else -10 end), (15);

The EXPLAIN plan produced by the query:

                          QUERY PLAN
-------------------------------------------------------------------
 Insert on mock  (cost=0.01..0.04 rows=2 width=4)
   InitPlan 1 (returns $0)
     ->  Result  (cost=0.00..0.01 rows=1 width=0)
   ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4)
(4 rows)

Stacktrace (line numbers may be invalid):

* thread #1, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x7f7f7f7f7f7f7f7f)
  * frame #0: 0x0000000102f9be30 postgres`castNodeImpl(type=T_TupleTableSlot, ptr=0x7f7f7f7f7f7f7f7f) at nodes.h:609:2
    frame #1: 0x0000000102f9bca4 postgres`ExecResetTupleTable(tupleTable=0x00000001040aed30, shouldFree=false) at execTuples.c:200:26
    frame #2: 0x0000000102f895a4 postgres`ExecEndPlan(planstate=0x0000000115373000, estate=0x00000001040ae120) at execMain.c:1646:2
    frame #3: 0x0000000102f894c8 postgres`standard_ExecutorEnd(queryDesc=0x000000010b7f2d20) at execMain.c:495:2
    frame #4: 0x0000000103deea54 pg_stat_statements.so`pgss_ExecutorEnd(queryDesc=0x000000010b7f2d20) at pg_stat_statements.c:1388:3
    frame #5: 0x0000000103e366fc yb_pg_metrics.so`ybpgm_ExecutorEnd(queryDesc=0x000000010b7f2d20) at yb_pg_metrics.c:693:7

[karthik-ramanathan-3006]

RCA: This was an issue with lifecycle management of TupleTableSlots used in SubPlans (CASE WHEN(..) ... END) that were used in conjunction with a ValueScan (inserting multiple rows). The issue was present in vanilla postgres 11.2 (the version YugabyteDB is currently based on) and fixed in a subsequent minor release of postgres 11.

I have imported and tested the fix in YugabyteDB. The fix will be made available in all currently-supported releases of YugabyteDB.

Thank you again for reporting the issue!