Re: partitioning and identity column - Mailing list pgsql-hackers

From Alexander Lakhin
Subject Re: partitioning and identity column
Date
Msg-id 59af4296-e407-d556-c50a-853a7660dd60@gmail.com
Whole thread Raw
In response to Re: partitioning and identity column  (Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>)
Responses Re: partitioning and identity column
List pgsql-hackers
Hello Ashutosh,

24.01.2024 09:34, Ashutosh Bapat wrote:
>
>>> There's another thing I found. The file isn't using
>>> check_stack_depth() in the function which traverse inheritance
>>> hierarchies. This isn't just a problem of the identity related
>>> function but most of the functions in that file. Do you think it's
>>> worth fixing it?
>> I suppose the number of inheritance levels is usually not a problem for
>> stack depth?
>>
> Practically it should not. I would rethink the application design if
> it requires so many inheritance or partition levels. But functions in
> optimizer like try_partitionwise_join() and set_append_rel_size() call
>
> /* Guard against stack overflow due to overly deep inheritance tree. */
> check_stack_depth();
>
> I am fine if we want to skip this.

I've managed to reach stack overflow inside ATExecSetIdentity() with
the following script:
(echo "CREATE TABLE tp0 (a int PRIMARY KEY,
         b int GENERATED ALWAYS AS IDENTITY) PARTITION BY RANGE (a);";
for ((i=1;i<=80000;i++)); do
   echo "CREATE TABLE tp$i PARTITION OF tp$(( $i - 1 ))
          FOR VALUES FROM ($i) TO (1000000) PARTITION BY RANGE (a);";
done;
echo "ALTER TABLE tp0 ALTER COLUMN b SET GENERATED BY DEFAULT;") | psql >psql.log

(with max_locks_per_transaction = 400 in the config)

It runs about 15 minutes for me and ends with:
Program terminated with signal SIGSEGV, Segmentation fault.

#0  0x000055a8ced20de9 in LWLockAcquire (lock=0x7faec200b900, mode=mode@entry=LW_EXCLUSIVE) at lwlock.c:1169
1169    {
(gdb) bt
#0  0x000055a8ced20de9 in LWLockAcquire (lock=0x7faec200b900, mode=mode@entry=LW_EXCLUSIVE) at lwlock.c:1169
#1  0x000055a8cea0342d in WALInsertLockAcquire () at xlog.c:1389
#2  XLogInsertRecord (rdata=0x55a8cf1ccee8 <hdr_rdt>, fpw_lsn=fpw_lsn@entry=1261347512, flags=0 '\000', 
num_fpi=num_fpi@entry=0, topxid_included=false) at xlog.c:817
#3  0x000055a8cea1396e in XLogInsert (rmid=rmid@entry=11 '\v', info=<optimized out>) at xloginsert.c:524
#4  0x000055a8ce9c1541 in _bt_insertonpg (rel=0x7faeb8478c98, heaprel=0x7faecf63d378, 
itup_key=itup_key@entry=0x55a8d5064678, buf=3210, cbuf=cbuf@entry=0, stack=stack@entry=0x55a8d1063d08, 
itup=0x55a8d5064658, itemsz=16,
     newitemoff=<optimized out>, postingoff=0, split_only_page=<optimized out>) at nbtinsert.c:1389
#5  0x000055a8ce9bf9a7 in _bt_doinsert (rel=<optimized out>, rel@entry=0x7faeb8478c98, itup=<optimized out>, 
itup@entry=0x55a8d5064658, checkUnique=<optimized out>, checkUnique@entry=UNIQUE_CHECK_YES, indexUnchanged=<optimized
out>,
     heapRel=<optimized out>, heapRel@entry=0x7faecf63d378) at nbtinsert.c:260
#6  0x000055a8ce9c92ad in btinsert (rel=0x7faeb8478c98, values=<optimized out>, isnull=<optimized out>, 
ht_ctid=0x55a8d50643cc, heapRel=0x7faecf63d378, checkUnique=UNIQUE_CHECK_YES, indexUnchanged=<optimized out>,
     indexInfo=<optimized out>) at nbtree.c:205
#7  0x000055a8cea41391 in CatalogIndexInsert (indstate=indstate@entry=0x55a8d0fc03e8, heapTuple=<optimized out>, 
heapTuple@entry=0x55a8d50643c8, updateIndexes=<optimized out>) at indexing.c:170
#8  0x000055a8cea4172c in CatalogTupleUpdate (heapRel=heapRel@entry=0x7faecf63d378, otid=0x55a8d50643cc, 
tup=tup@entry=0x55a8d50643c8) at indexing.c:324
#9  0x000055a8ceb18173 in ATExecSetIdentity (rel=0x7faeab1288a8, colName=colName@entry=0x55a8d0fbc2b8 "b", 
def=def@entry=0x55a8d1063918, lockmode=lockmode@entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8307
#10 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab127f28, colName=colName@entry=0x55a8d0fbc2b8 "b", 
def=def@entry=0x55a8d1063918, lockmode=lockmode@entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8337
#11 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab1275a8, colName=colName@entry=0x55a8d0fbc2b8 "b", 
def=def@entry=0x55a8d1063918, lockmode=lockmode@entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8337
#12 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab126c28, colName=colName@entry=0x55a8d0fbc2b8 "b", 
def=def@entry=0x55a8d1063918, lockmode=lockmode@entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8337
...

Functions ATExecAddIdentity() and ATExecDropIdentity() are recursive too,
so I think they can be exploited as well.

Best regards,
Alexander



pgsql-hackers by date:

Previous
From: Peter Geoghegan
Date:
Subject: Re: index prefetching
Next
From: Corey Huinker
Date:
Subject: Re: Document efficient self-joins / UPDATE LIMIT techniques.