PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID16293
PG Version11.7
OSUbuntu 16.04
Opened2020-03-10 21:13:24+00
Reported byDaniel WM
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      16293
Logged by:          Daniel WM
Email address:      (redacted)
PostgreSQL version: 11.7
Operating system:   Ubuntu 16.04
Description:        

I was getting this error each time I ran a series of autoamted tests in a CI
server:
```
2020-03-06 23:32:57,051 WARN  main c.z.h.p.ProxyConnection - HikariPool-2 -
Connection org.postgresql.jdbc.PgConnection@42e3ede4 marked as broken
because of SQLSTATE(08006), ErrorCode(0) {}
 org.postgresql.util.PSQLException: An I/O error occurred while sending to
the backend.
    at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:335)
    at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
    at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
    at
org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
```

I looked at `dmesg` and I saw this:

    [1383242.997083] postgres[7998]: segfault at 100000048 ip
000055c587913e4b sp 00007fffa492e6f0 error 4 in
postgres[55c587424000+72d000]

Using `gdb` I got this backtrace:
```
Core was generated by `postgres: REDACTED REDACTED 127.0.0.1(49990) INSERT  
                       '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x000055c587913e4b in pfree ()
(gdb) bt
#0  0x000055c587913e4b in pfree ()
#1  0x000055c587687475 in ExecSetSlotDescriptor ()
#2  0x000055c58767eb61 in ExecConstraints ()
#3  0x000055c5876a0efc in ?? ()
#4  0x000055c5876a2085 in ?? ()
#5  0x000055c58767cd1b in standard_ExecutorRun ()
#6  0x000055c5877d22e5 in ?? ()
#7  0x000055c5877d2538 in ?? ()
#8  0x000055c5877d2855 in ?? ()
#9  0x000055c5877d3427 in PortalRun ()
#10 0x000055c5877cfeec in PostgresMain ()
#11 0x000055c5874ddd37 in ?? ()
#12 0x000055c58775a882 in PostmasterMain ()
#13 0x000055c5874df0e5 in main ()
```

My full version of Postgres is: `PostgreSQL 11.7 (Ubuntu 11.7-1.pgdg16.04+1)
on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.12)
5.4.0 20160609, 64-bit`

I finally found the issue was that one of the tests was trying to insert a
row on a partitioned table, but there was no partition where to put that
row, so it had to go to the default partition. But, the default partition
had a constraint that disallowed that row, so the row couldn't be inserted
and Postgres ended with a segfault.

I enabled query logging and managed to find the offending "insert":

    insert into "myschema"."mytable" ("custcode", "custcar", "custdob",
"closed") values ('a33113f2-930c-47de-95a6-b9e07650468a', 'hellow world',
'2020-02-02 01:00:00+00:00', 'f')

That is a partitioned table on the "custdob" column, with these
partitions:

```
\d+ mytable
                                                           Table
"myschema.mytable"
   Column   |           Type           | Collation | Nullable |             
  Default                 | Storage  | Stats target | Description 
------------+--------------------------+-----------+----------+----------------------------------------+----------+--------------+-------------
 id         | bigint                   |           | not null |
nextval('mytable_id_seq'::regclass)    | plain    |              | 
 custcode   | uuid                     |           | not null |             
                          | plain    |              | 
 custcar    | character varying        |           | not null |             
                          | extended |              | 
 custdob    | timestamp with time zone |           | not null |             
                          | plain    |              | 
 closed     | boolean                  |           | not null | false       
                          | plain    |              | 
Partition key: RANGE (custdob)
Partitions: mytable_201902_partition FOR VALUES FROM ('2019-02-01
00:00:00+00') TO ('2019-03-01 00:00:00+00'),
            mytable_201903_partition FOR VALUES FROM ('2019-03-01
00:00:00+00') TO ('2019-04-01 00:00:00+00'),
            mytable_201908_partition FOR VALUES FROM ('2019-08-02
00:00:00+00') TO ('2019-09-01 00:00:00+00'),
            mytable_202003_partition FOR VALUES FROM ('2020-03-01
00:00:00+00') TO ('2020-04-01 00:00:00+00'),
            mytable_202004_partition FOR VALUES FROM ('2020-04-01
00:00:00+00') TO ('2020-05-01 00:00:00+00'),
            mytable_000000_partition DEFAULT
```

Notice the INSERT wants to insert in February's partition, but that
partition is missing in my CI server, so it should insert the row in the
DEFAULT partition. The issue is, the DEFAULT partition has this
constraint:

```
"mytable_partition_check" CHECK (custdob < '2019-08-02
00:00:00+00'::timestamp with time zone)
```

So Postgres seems to be getting into a bug because it can't insert a record
for February while that constraint is in there. If I drop this constraint
and re-issue the offending INSERT, it works this time.

I first reported this issue in SO:
https://stackoverflow.com/questions/60573071/postgres-segfault-and-returns-sqlstate-08006

Messages

DateAuthorSubject
2020-03-10 21:13:24+00PG Bug reporting formBUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-10 22:19:51+00Tom LaneRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-10 23:04:48+00Daniel WMRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-14 20:43:32+00Daniel WMRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-14 21:21:04+00Tom LaneRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-14 21:26:57+00Andres FreundRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-15 00:35:41+00Andres FreundRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-17 10:49:43+00Amit LangoteRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-22 06:52:17+00Andres FreundRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-23 18:53:30+00Andres FreundRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-23 22:05:02+00Andres FreundRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-31 06:30:25+00Amit LangoteRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006
2020-03-31 09:37:56+00Amit LangoteRe: BUG #16293: postgres segfaults and returns SQLSTATE 08006