Hi! As it is seen from the code (toasting.c and further) Toast tables are created immediately when a new relation with the TOASTable column is created. Practically, there could occur the case when Toast table does not exist and we should of course check for that.
TOAST_TUPLE_THRESHOLD is not only one which decides should be value stored externally, this is slightly more complex and less obvious logic: (see heapam.c, heap_prepare_insert()) else if (HeapTupleHasExternal(tup) || tup->t_len > TOAST_TUPLE_THRESHOLD) as you can see here is another condition - HeapTupleHasExternal, which is set in heap_fill_tuple and lower in fill_val, where the infomask bit HEAP_HASEXTERNAL is set. So when I experimented with the TOAST I'd to add a new flag which forced the value to be TOASTed regardless of its size. Also, TOAST_TUPLE_THRESHOLD sets overall tuple size over which it would be considered to be toasted, and has its minimum value that could not be decreased further. In [1] (the Pluggable TOAST) we suggest making this an ontion for Toaster. [1] https://www.postgresql.org/message-id/flat/224711f9-83b7-a307-b17f-4457ab73a...@sigaev.ru On Thu, Sep 15, 2022 at 3:05 AM David Rowley <dgrowle...@gmail.com> wrote: > On Thu, 15 Sept 2022 at 04:04, Aleksander Alekseev > <aleksan...@timescale.com> wrote: > > 1. Forbid setting toast_tuple_target < TOAST_TUPLE_THRESHOLD > > 2. Consider using something like RelationGetToastTupleTarget(rel, > > TOAST_TUPLE_THRESHOLD) in heapam.c:2250, heapam.c:3625 and > > rewriteheap.c:636 and modify the documentation accordingly. > > 3. Add a separate user-defined table setting toast_tuple_threshold > > similar to toast_tuple_target. > > > > Thoughts? > > There was some discussion on this problem in [1]. > > The problem with #2 is that if you look at > heapam_relation_needs_toast_table(), it only decides if the toast > table should be created based on (tuple_length > > TOAST_TUPLE_THRESHOLD). So if you were to change the logic as you > describe for #2 then there might not be a toast table during an > INSERT/UPDATE. > > The only way to fix that would be to ensure that we reconsider if we > should create a toast table or not when someone changes the > toast_tuple_target reloption. That can't be done under > ShareUpdateExclusiveLock, so we'd need to obtain an > AccessExclusiveLock instead when changing the toast_tuple_target > reloption. That might upset some people. > > The general direction of [1] was to just increase the minimum setting > to TOAST_TUPLE_THRESHOLD, but there were some concerns about breaking > pg_dump as we'd have to error if someone does ALTER TABLE to set the > toast_tuple_target reloption lower than the newly defined minimum > value. > > I don't quite follow you on #3. If there's no toast table we can't toast. > > David > > [1] > https://www.postgresql.org/message-id/20190403063759.gf3...@paquier.xyz > > > -- Regards, Nikita Malakhov Postgres Professional https://postgrespro.ru/