Thread: pgsql: Parameter toast_tuple_target controls TOAST for new rows
Parameter toast_tuple_target controls TOAST for new rows Specifies the point at which we try to move long column values into TOAST tables. No effect on existing rows. Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com Author: Simon Riggs <simon@2ndQudrant.com> Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndQuadrant.com> Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 2 +- doc/src/sgml/ref/create_table.sgml | 21 +++++++++++++++ src/backend/access/common/reloptions.c | 12 +++++++++ src/backend/access/heap/tuptoaster.c | 2 +- src/include/utils/rel.h | 9 +++++++ src/test/regress/expected/strings.out | 47 ++++++++++++++++++++++++++++++++++ src/test/regress/sql/strings.sql | 19 ++++++++++++++ 7 files changed, 110 insertions(+), 2 deletions(-)
On 19 November 2017 at 17:52, Simon Riggs <simon@2ndquadrant.com> wrote: > Parameter toast_tuple_target controls TOAST for new rows > > Specifies the point at which we try to move long column values > into TOAST tables. > > No effect on existing rows. > > Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com > > Author: Simon Riggs <simon@2ndQudrant.com> > Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndQuadrant.com> > > Branch > ------ > master > > Details > ------- > https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897 > > Modified Files > -------------- > doc/src/sgml/ref/alter_table.sgml | 2 +- > doc/src/sgml/ref/create_table.sgml | 21 +++++++++++++++ > src/backend/access/common/reloptions.c | 12 +++++++++ > src/backend/access/heap/tuptoaster.c | 2 +- > src/include/utils/rel.h | 9 +++++++ > src/test/regress/expected/strings.out | 47 ++++++++++++++++++++++++++++++++++ > src/test/regress/sql/strings.sql | 19 ++++++++++++++ > 7 files changed, 110 insertions(+), 2 deletions(-) Am investigating the few buildfarm failures -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2017-11-19 19:08:48 -0500, Simon Riggs wrote: > On 19 November 2017 at 17:52, Simon Riggs <simon@2ndquadrant.com> wrote: > > Parameter toast_tuple_target controls TOAST for new rows > > > > Specifies the point at which we try to move long column values > > into TOAST tables. > > > > No effect on existing rows. > > > > Discussion: https://postgr.es/m/CANP8+jKsVmw6CX6YP9z7zqkTzcKV1+Uzr3XjKcZW=2Ya00OyQQ@mail.gmail.com > > > > Author: Simon Riggs <simon@2ndQudrant.com> > > Reviewed-by: Andrew Dunstan <andrew.dunstan@2ndQuadrant.com> > > > > Branch > > ------ > > master > > > > Details > > ------- > > https://git.postgresql.org/pg/commitdiff/c2513365a0a85e77d3c21adb92fe12cfbe0d1897 > > > > Modified Files > > -------------- > > doc/src/sgml/ref/alter_table.sgml | 2 +- > > doc/src/sgml/ref/create_table.sgml | 21 +++++++++++++++ > > src/backend/access/common/reloptions.c | 12 +++++++++ > > src/backend/access/heap/tuptoaster.c | 2 +- > > src/include/utils/rel.h | 9 +++++++ > > src/test/regress/expected/strings.out | 47 ++++++++++++++++++++++++++++++++++ > > src/test/regress/sql/strings.sql | 19 ++++++++++++++ > > 7 files changed, 110 insertions(+), 2 deletions(-) > > Am investigating the few buildfarm failures The tests look very sensitive to differences in tuple size due to different alignment requirements. Dependant on what MAXALIGN (and some others) is the number of tuples fitting on a page will differ. Greetings, Andres Freund
Andres Freund <andres@anarazel.de> writes:
> On 2017-11-19 19:08:48 -0500, Simon Riggs wrote:
>> Am investigating the few buildfarm failures
> The tests look very sensitive to differences in tuple size due to
> different alignment requirements. Dependant on what MAXALIGN (and some
> others) is the number of tuples fitting on a page will differ.
"Few" buildfarm failures? It's probably going to fail on every 32-bit host.
TBH, I would just remove those test cases. Even if they were stable
across platforms, they don't directly prove anything at all about
whether the feature does what it's supposed to.
(Also, scaling the results to blocksize seems unlikely to help in
passing on different BLCKSZ configurations...)
regards, tom lane
On 2017-11-19 19:49:01 -0500, Tom Lane wrote: > Andres Freund <andres@anarazel.de> writes: > > On 2017-11-19 19:08:48 -0500, Simon Riggs wrote: > >> Am investigating the few buildfarm failures > > > The tests look very sensitive to differences in tuple size due to > > different alignment requirements. Dependant on what MAXALIGN (and some > > others) is the number of tuples fitting on a page will differ. > > "Few" buildfarm failures? It's probably going to fail on every 32-bit host. > > TBH, I would just remove those test cases. Even if they were stable > across platforms, they don't directly prove anything at all about > whether the feature does what it's supposed to. > > (Also, scaling the results to blocksize seems unlikely to help in > passing on different BLCKSZ configurations...) I think it might make sense to rewrite the tests so it doesn't output any of the sizes, but instead just compares the size of tables with different thresholds. That should be fairly reliable. Greetings, Andres Freund
On 19 November 2017 at 19:49, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andres Freund <andres@anarazel.de> writes: >> On 2017-11-19 19:08:48 -0500, Simon Riggs wrote: >>> Am investigating the few buildfarm failures > >> The tests look very sensitive to differences in tuple size due to >> different alignment requirements. Dependant on what MAXALIGN (and some >> others) is the number of tuples fitting on a page will differ. > > "Few" buildfarm failures? It's probably going to fail on every 32-bit host. > > TBH, I would just remove those test cases. Even if they were stable > across platforms, they don't directly prove anything at all about > whether the feature does what it's supposed to. > > (Also, scaling the results to blocksize seems unlikely to help in > passing on different BLCKSZ configurations...) I just committed a change while this email arrived. I think what I've done is essentially this. The original test was more about demonstrating effectiveness. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Andres Freund <andres@anarazel.de> writes:
> On 2017-11-19 19:49:01 -0500, Tom Lane wrote:
>> TBH, I would just remove those test cases. Even if they were stable
>> across platforms, they don't directly prove anything at all about
>> whether the feature does what it's supposed to.
> I think it might make sense to rewrite the tests so it doesn't output
> any of the sizes, but instead just compares the size of tables with
> different thresholds. That should be fairly reliable.
Hm, what I'd try after a bit of thought is to stuff the same data
into two different tables with different settings chosen to make it
TOAST or not, and then just test the toast tables for zero or nonzero
size. Setting STORAGE = EXTERNAL on the data column to disable
compression would help make things more stable, too.
regards, tom lane
On 19 November 2017 at 19:52, Andres Freund <andres@anarazel.de> wrote: > On 2017-11-19 19:49:01 -0500, Tom Lane wrote: >> Andres Freund <andres@anarazel.de> writes: >> > On 2017-11-19 19:08:48 -0500, Simon Riggs wrote: >> >> Am investigating the few buildfarm failures >> >> > The tests look very sensitive to differences in tuple size due to >> > different alignment requirements. Dependant on what MAXALIGN (and some >> > others) is the number of tuples fitting on a page will differ. >> >> "Few" buildfarm failures? It's probably going to fail on every 32-bit host. >> >> TBH, I would just remove those test cases. Even if they were stable >> across platforms, they don't directly prove anything at all about >> whether the feature does what it's supposed to. >> >> (Also, scaling the results to blocksize seems unlikely to help in >> passing on different BLCKSZ configurations...) > > I think it might make sense to rewrite the tests so it doesn't output > any of the sizes, but instead just compares the size of tables with > different thresholds. That should be fairly reliable. Thanks for your input. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 19 November 2017 at 20:30, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Andres Freund <andres@anarazel.de> writes: >> On 2017-11-19 19:49:01 -0500, Tom Lane wrote: >>> TBH, I would just remove those test cases. Even if they were stable >>> across platforms, they don't directly prove anything at all about >>> whether the feature does what it's supposed to. > >> I think it might make sense to rewrite the tests so it doesn't output >> any of the sizes, but instead just compares the size of tables with >> different thresholds. That should be fairly reliable. > > Hm, what I'd try after a bit of thought is to stuff the same data > into two different tables with different settings chosen to make it > TOAST or not, and then just test the toast tables for zero or nonzero > size. Roughly that. > Setting STORAGE = EXTERNAL on the data column to disable > compression would help make things more stable, too. That was already set at line 350 -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services