Re: partitioning and identity column

On Thu, Feb 15, 2024 at 11:30 PM Alexander Lakhin <exclusion(at)gmail(dot)com> wrote:
>
> 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(at)entry=LW_EXCLUSIVE) at lwlock.c:1169
> 1169 {
> (gdb) bt
> #0 0x000055a8ced20de9 in LWLockAcquire (lock=0x7faec200b900, mode=mode(at)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(at)entry=1261347512, flags=0 '\000',
> num_fpi=num_fpi(at)entry=0, topxid_included=false) at xlog.c:817
> #3 0x000055a8cea1396e in XLogInsert (rmid=rmid(at)entry=11 '\v', info=<optimized out>) at xloginsert.c:524
> #4 0x000055a8ce9c1541 in _bt_insertonpg (rel=0x7faeb8478c98, heaprel=0x7faecf63d378,
> itup_key=itup_key(at)entry=0x55a8d5064678, buf=3210, cbuf=cbuf(at)entry=0, stack=stack(at)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(at)entry=0x7faeb8478c98, itup=<optimized out>,
> itup(at)entry=0x55a8d5064658, checkUnique=<optimized out>, checkUnique(at)entry=UNIQUE_CHECK_YES, indexUnchanged=<optimized out>,
> heapRel=<optimized out>, heapRel(at)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(at)entry=0x55a8d0fc03e8, heapTuple=<optimized out>,
> heapTuple(at)entry=0x55a8d50643c8, updateIndexes=<optimized out>) at indexing.c:170
> #8 0x000055a8cea4172c in CatalogTupleUpdate (heapRel=heapRel(at)entry=0x7faecf63d378, otid=0x55a8d50643cc,
> tup=tup(at)entry=0x55a8d50643c8) at indexing.c:324
> #9 0x000055a8ceb18173 in ATExecSetIdentity (rel=0x7faeab1288a8, colName=colName(at)entry=0x55a8d0fbc2b8 "b",
> def=def(at)entry=0x55a8d1063918, lockmode=lockmode(at)entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8307
> #10 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab127f28, colName=colName(at)entry=0x55a8d0fbc2b8 "b",
> def=def(at)entry=0x55a8d1063918, lockmode=lockmode(at)entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8337
> #11 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab1275a8, colName=colName(at)entry=0x55a8d0fbc2b8 "b",
> def=def(at)entry=0x55a8d1063918, lockmode=lockmode(at)entry=8, recurse=true, recursing=<optimized out>) at tablecmds.c:8337
> #12 0x000055a8ceb18251 in ATExecSetIdentity (rel=0x7faeab126c28, colName=colName(at)entry=0x55a8d0fbc2b8 "b",
> def=def(at)entry=0x55a8d1063918, lockmode=lockmode(at)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.

not just Identity related functions, but many other functions in
tablecmds.c have that problem as I mentioned earlier.

--
Best Wishes,
Ashutosh Bapat