Re: TOAST condition for column size

On Tue, Jan 19, 2021 at 5:18 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> On Mon, Jan 18, 2021 at 7:53 PM torikoshia <torikoshia(at)oss(dot)nttdata(dot)com> wrote:
> >
> > Hi,
> >
> > When I created a table consisting of 400 VARCHAR columns and tried
> > to INSERT a record which rows were all the same size, there were
> > cases where I got an error due to exceeding the size limit per
> > row.
> >
> > =# -- create a table consisting of 400 VARCHAR columns
> > =# CREATE TABLE t1 (c1 VARCHAR(100),
> > c2 VARCHAR(100),
> > ...
> > c400 VARCHAR(100));
> >
> > =# -- insert one record which rows are all 20 bytes
> > =# INSERT INTO t1 VALUES (repeat('a', 20),
> > repeat('a', 20),
> > ...
> > repeat('a', 20));
> > ERROR: row is too big: size 8424, maximum size 8160
> >
> > What is interesting is that it failed only when the size of each
> > column was 20~23 bytes, as shown below.
> >
> > size of each column | result
> > -------------------------------
> > 18 bytes | success
> > 19 bytes | success
> > 20 bytes | failure
> > 21 bytes | failure
> > 22 bytes | failure
> > 23 bytes | failure
> > 24 bytes | success
> > 25 bytes | success
> >
> >
> > When the size of each column was 19 bytes or less, it succeeds
> > because the row size is within a page size.
> > When the size of each column was 24 bytes or more, it also
> > succeeds because columns are TOASTed and the row size is reduced
> > to less than one page size.
> > OTOH, when it's more than 19 bytes and less than 24 bytes,
> > columns aren't TOASTed because it doesn't meet the condition of
> > the following if statement.
> >
> > --src/backend/access/table/toast_helper.c
> >
> > toast_tuple_find_biggest_attribute(ToastTupleContext *ttc,
> > bool for_compression, bool check_main)
> > ...(snip)...
> > int32 biggest_size = MAXALIGN(TOAST_POINTER_SIZE);
> > ...(snip)...
> > if (ttc->ttc_attr[i].tai_size > biggest_size) // <- here
> > {
> > biggest_attno = i;
> > biggest_size = ttc->ttc_attr[i].tai_size;
> > }
> >
> >
> > Since TOAST_POINTER_SIZE is 18 bytes but
> > MAXALIGN(TOAST_POINTER_SIZE) is 24 bytes, columns are not TOASTed
> > until its size becomes larger than 24 bytes.
> >
> > I confirmed these sizes in my environment but AFAIU they would be
> > the same size in any environment.
> >
> > So, as a result of adjusting the alignment, 20~23 bytes seems to
> > fail.
> >
> > I wonder if it might be better not to adjust the alignment here
> > as an attached patch because it succeeded in inserting 20~23
> > bytes records.
> > Or is there reasons to add the alignment here?
> >
> > I understand that TOAST is not effective for small data and it's
> > not recommended to create a table containing hundreds of columns,
> > but I think cases that can be successful should be successful.
> >
> > Any thoughts?
>
> How this can be correct? because while forming the tuple you might
> need the alignment.
>

Won't it be safe because we don't align individual attrs of type
varchar where length is less than equal to 127?

--
With Regards,
Amit Kapila.