RE: Speeding up INSERTs and UPDATEs to partitioned tables

Hi,

I tried to benchmark with v1-0001-Speed-up-INSERT-and-UPDATE-on-partitioned-tables.patch, but when I create the second
partition,server process get segmentation fault.
 

I don't know the cause, but it seems that an incorrect value is set to partdesc->boundinfo.

(gdb) p partdesc->boundinfo[0]
$6 = {strategy = 0 '\000', ndatums = 2139062142, datums = 0x7f7f7f7f7f7f7f7f, kind = 0x7f7f7f7f7f7f7f7f, indexes =
0x7f7f7f7f7f7f7f7f,null_index = 2139062143, default_index = 2139062143}
 


$ psql postgres
psql (11beta2)
Type "help" for help.

postgres=# create table a(i int) partition by range(i);
CREATE TABLE
postgres=# create table a_1 partition of a for values from(1) to (200);
CREATE TABLE
postgres=# create table a_2 partition of a for values from(200) to (400);
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.
!> 

2018-07-05 14:02:52.405 JST [60250] LOG:  server process (PID 60272) was terminated by signal 11: Segmentation fault
2018-07-05 14:02:52.405 JST [60250] DETAIL:  Failed process was running: create table a_2 partition of a for values
from(200)to (400);
 

(gdb) bt
#0  0x0000000000596e52 in get_default_oid_from_partdesc (partdesc=0x259e928) at partition.c:269
#1  0x0000000000677355 in DefineRelation (stmt=0x259e610, relkind=114 'r', ownerId=10, typaddress=0x0,
queryString=0x24d58b8"create table a_2 partition of a for values from(200) to (400);") at tablecmds.c:832
 
#2  0x00000000008b6893 in ProcessUtilitySlow (pstate=0x259e4f8, pstmt=0x24d67d8, queryString=0x24d58b8 "create table
a_2partition of a for values from(200) to (400);", context=PROCESS_UTILITY_TOPLEVEL, 
 
    params=0x0, queryEnv=0x0, dest=0x24d6ac8, completionTag=0x7ffc05932330 "") at utility.c:1000
#3  0x00000000008b66c2 in standard_ProcessUtility (pstmt=0x24d67d8, queryString=0x24d58b8 "create table a_2 partition
ofa for values from(200) to (400);", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, 
 
    queryEnv=0x0, dest=0x24d6ac8, completionTag=0x7ffc05932330 "") at utility.c:920
#4  0x00000000008b583b in ProcessUtility (pstmt=0x24d67d8, queryString=0x24d58b8 "create table a_2 partition of a for
valuesfrom(200) to (400);", context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, 
 
    dest=0x24d6ac8, completionTag=0x7ffc05932330 "") at utility.c:360
#5  0x00000000008b482c in PortalRunUtility (portal=0x253af38, pstmt=0x24d67d8, isTopLevel=true, setHoldSnapshot=false,
dest=0x24d6ac8,completionTag=0x7ffc05932330 "") at pquery.c:1178
 
#6  0x00000000008b4a45 in PortalRunMulti (portal=0x253af38, isTopLevel=true, setHoldSnapshot=false, dest=0x24d6ac8,
altdest=0x24d6ac8,completionTag=0x7ffc05932330 "") at pquery.c:1324
 
#7  0x00000000008b3f7d in PortalRun (portal=0x253af38, count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x24d6ac8,altdest=0x24d6ac8, completionTag=0x7ffc05932330 "") at pquery.c:799
 
#8  0x00000000008adf16 in exec_simple_query (query_string=0x24d58b8 "create table a_2 partition of a for values
from(200)to (400);") at postgres.c:1122
 
#9  0x00000000008b21a5 in PostgresMain (argc=1, argv=0x24ff5b0, dbname=0x24ff410 "postgres", username=0x24d2358
"symfo")at postgres.c:4153
 
#10 0x00000000008113f4 in BackendRun (port=0x24f73f0) at postmaster.c:4361
#11 0x0000000000810b67 in BackendStartup (port=0x24f73f0) at postmaster.c:4033
#12 0x000000000080d0ed in ServerLoop () at postmaster.c:1706
#13 0x000000000080c9a3 in PostmasterMain (argc=1, argv=0x24d0310) at postmaster.c:1379
#14 0x0000000000737392 in main (argc=1, argv=0x24d0310) at main.c:228

(gdb) disassemble 
Dump of assembler code for function get_default_oid_from_partdesc:
   0x0000000000596e0a <+0>:     push   %rbp
   0x0000000000596e0b <+1>:     mov    %rsp,%rbp
   0x0000000000596e0e <+4>:     mov    %rdi,-0x8(%rbp)
   0x0000000000596e12 <+8>:     cmpq   $0x0,-0x8(%rbp)
   0x0000000000596e17 <+13>:    je     0x596e56 <get_default_oid_from_partdesc+76>
   0x0000000000596e19 <+15>:    mov    -0x8(%rbp),%rax
   0x0000000000596e1d <+19>:    mov    0x10(%rax),%rax
   0x0000000000596e21 <+23>:    test   %rax,%rax
   0x0000000000596e24 <+26>:    je     0x596e56 <get_default_oid_from_partdesc+76>
   0x0000000000596e26 <+28>:    mov    -0x8(%rbp),%rax
   0x0000000000596e2a <+32>:    mov    0x10(%rax),%rax
   0x0000000000596e2e <+36>:    mov    0x24(%rax),%eax
   0x0000000000596e31 <+39>:    cmp    $0xffffffff,%eax
   0x0000000000596e34 <+42>:    je     0x596e56 <get_default_oid_from_partdesc+76>
   0x0000000000596e36 <+44>:    mov    -0x8(%rbp),%rax
   0x0000000000596e3a <+48>:    mov    0x8(%rax),%rdx
   0x0000000000596e3e <+52>:    mov    -0x8(%rbp),%rax
   0x0000000000596e42 <+56>:    mov    0x10(%rax),%rax
   0x0000000000596e46 <+60>:    mov    0x24(%rax),%eax
   0x0000000000596e49 <+63>:    cltq   
   0x0000000000596e4b <+65>:    shl    $0x2,%rax
   0x0000000000596e4f <+69>:    add    %rdx,%rax
=> 0x0000000000596e52 <+72>:    mov    (%rax),%eax
   0x0000000000596e54 <+74>:    jmp    0x596e5b <get_default_oid_from_partdesc+81>
   0x0000000000596e56 <+76>:    mov    $0x0,%eax
   0x0000000000596e5b <+81>:    pop    %rbp
   0x0000000000596e5c <+82>:    retq   
End of assembler dump.

(gdb) i r
rax            0x20057e77c      8595695484
rbx            0x72     114
rcx            0x7f50ce90e0e8   139985039712488
rdx            0x259e980        39446912
rsi            0x7f50ce90e0a8   139985039712424
rdi            0x259e928        39446824
rbp            0x7ffc05931890   0x7ffc05931890
rsp            0x7ffc05931890   0x7ffc05931890
r8             0x7ffc059317bf   140720402012095
r9             0x0      0
r10            0x6b     107
r11            0x7f50cdbc3f10   139985025777424
r12            0x70     112
r13            0x0      0
r14            0x0      0
r15            0x0      0
rip            0x596e52 0x596e52 <get_default_oid_from_partdesc+72>
eflags         0x10202  [ IF RF ]
cs             0x33     51
ss             0x2b     43
ds             0x0      0
es             0x0      0
fs             0x0      0
gs             0x0      0

(gdb) list *0x596e52
0x596e52 is in get_default_oid_from_partdesc (partition.c:269).
264     Oid
265     get_default_oid_from_partdesc(PartitionDesc partdesc)
266     {
267             if (partdesc && partdesc->boundinfo &&
268                     partition_bound_has_default(partdesc->boundinfo))
269                     return partdesc->oids[partdesc->boundinfo->default_index];
270 
271             return InvalidOid;
272     }
273

regards,
-----Original Message-----
From: David Rowley [mailto:david.rowley@2ndquadrant.com] 
Sent: Saturday, June 23, 2018 7:19 AM
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 22 June 2018 at 18:28, David Rowley <david.rowley@2ndquadrant.com> wrote:
> I've written fixes for items 1-6 above.
>
> I did:
>
> 1. Use an array instead of a List.
> 2. Don't do this loop. palloc0() the partitions array instead. Let 
> UPDATE add whatever subplans exist to the zeroed array.
> 3. Track what we initialize in a gapless array and cleanup just those 
> ones. Make this array small and increase it only when we need more 
> space.
> 4. Only allocate the map array when we need to store a map.
> 5. Work that out in relcache beforehand.
> 6. ditto

I've added this to the July 'fest:

https://commitfest.postgresql.org/18/1690/

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services