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_2 partition 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 of a 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 values from(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(dot)rowley(at)2ndquadrant(dot)com]
Sent: Saturday, June 23, 2018 7:19 AM
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 22 June 2018 at 18:28, David Rowley <david(dot)rowley(at)2ndquadrant(dot)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