Re: tableam vs. TOAST

Hi All,

While testing the Toast patch(PG+v7 patch) I found below server crash.
System configuration:
VCPUs: 4, RAM: 8GB, Storage: 320GB

This issue is not frequently reproducible, we need to repeat the same
testcase multiple times.

CREATE OR REPLACE FUNCTION toast_chunks_cnt_func(p1 IN text)
RETURNS int AS $$
DECLARE
chunks_cnt int;
v_tbl text;
BEGIN
SELECT reltoastrelid::regclass INTO v_tbl FROM pg_class WHERE RELNAME =
p1;
EXECUTE 'SELECT count(*) FROM ' || v_tbl::regclass INTO chunks_cnt;
RETURN chunks_cnt;
END; $$ LANGUAGE PLPGSQL;

-- Server crash after multiple run of below testcase
-- ------------------------------------------------------------------------
CHECKPOINT;
CREATE TABLE toast_tab (c1 text);
\d+ toast_tab
-- ALTER table column c1 for storage as "EXTERNAL" to make sure that the
column value is pushed to the TOAST table but not COMPRESSED.
ALTER TABLE toast_tab ALTER COLUMN c1 SET STORAGE EXTERNAL;
\d+ toast_tab
\timing
INSERT INTO toast_tab
( select repeat('a', 200000)
from generate_series(1,40000) x);
\timing
SELECT reltoastrelid::regclass FROM pg_class WHERE RELNAME = 'toast_tab';
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

CHECKPOINT;
\timing
UPDATE toast_tab SET c1 = UPPER(c1);
\timing
SELECT toast_chunks_cnt_func('toast_tab') "Number of chunks";
SELECT pg_column_size(t1.*) FROM toast_tab t1 limit 1;
SELECT DISTINCT SUBSTR(c1, 90000,10) FROM toast_tab;

DROP TABLE toast_tab;
-- ------------------------------------------------------------------------

-- Stacktrace as below:
[centos(at)host-192-168-1-249 bin]$ gdb -q -c data2/core.3151 postgres
Reading symbols from
/home/centos/PG/PGsrc/postgresql/inst/bin/postgres...done.
[New LWP 3151]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `postgres: checkpointer
'.
Program terminated with signal 6, Aborted.
#0 0x00007f2267d33207 in raise () from /lib64/libc.so.6
Missing separate debuginfos, use: debuginfo-install
glibc-2.17-260.el7_6.5.x86_64 keyutils-libs-1.5.8-3.el7.x86_64
krb5-libs-1.15.1-37.el7_6.x86_64 libcom_err-1.42.9-13.el7.x86_64
libselinux-2.5-14.1.el7.x86_64 openssl-libs-1.0.2k-16.el7_6.1.x86_64
pcre-8.32-17.el7.x86_64 zlib-1.2.7-18.el7.x86_64
(gdb) bt
#0 0x00007f2267d33207 in raise () from /lib64/libc.so.6
#1 0x00007f2267d348f8 in abort () from /lib64/libc.so.6
#2 0x0000000000eb3a80 in errfinish (dummy=0) at elog.c:552
#3 0x0000000000c26530 in ProcessSyncRequests () at sync.c:393
#4 0x0000000000bbbc57 in CheckPointBuffers (flags=256) at bufmgr.c:2589
#5 0x0000000000604634 in CheckPointGuts (checkPointRedo=51448358328,
flags=256) at xlog.c:8992
#6 0x0000000000603b5e in CreateCheckPoint (flags=256) at xlog.c:8781
#7 0x0000000000aed8fa in CheckpointerMain () at checkpointer.c:481
#8 0x00000000006240de in AuxiliaryProcessMain (argc=2,
argv=0x7ffe887c0880) at bootstrap.c:461
#9 0x0000000000b0e834 in StartChildProcess (type=CheckpointerProcess) at
postmaster.c:5414
#10 0x0000000000b09283 in reaper (postgres_signal_arg=17) at
postmaster.c:2995
#11 <signal handler called>
#12 0x00007f2267df1f53 in __select_nocancel () from /lib64/libc.so.6
#13 0x0000000000b05000 in ServerLoop () at postmaster.c:1682
#14 0x0000000000b0457b in PostmasterMain (argc=5, argv=0x349bce0) at
postmaster.c:1391
#15 0x0000000000971c9f in main (argc=5, argv=0x349bce0) at main.c:210
(gdb)

On Sat, Oct 5, 2019 at 12:03 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> On Fri, Sep 6, 2019 at 10:59 AM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> > On Thu, Sep 5, 2019 at 4:07 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > > Yea, makes sense to me.
> >
> > OK, done. Here's the remaining patches again, with a slight update to
> > the renaming patch (now 0002). In the last version, I renamed
> > toast_insert_or_update to heap_toast_insert_or_update but did not
> > rename toast_delete to heap_toast_delete. Actually, I'm not seeing
> > any particular reason not to go ahead and push the renaming patch at
> > this point also.
>
> And, hearing no objections, done.
>
> Here's the last patch back, rebased over that renaming. Although I
> think that Andres (and Tom) are probably right that there's room for
> improvement here, I currently don't see a way around the issues I
> wrote about in
> http://postgr.es/m/CA+Tgmoa0zFcaCpOJCsSpOLLGpzTVfSyvcVB-USS8YoKzMO51Yw@mail.gmail.com
> -- so not quite sure where to go next. Hopefully Andres or someone
> else will give me a quick whack with the cluebat if I'm missing
> something obvious.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

--

With Regards,

Prabhat Kumar Sahu
Skype ID: prabhat.sahu1984
EnterpriseDB Software India Pvt. Ltd.

The Postgres Database Company