Re: Allow table AMs to define their own reloptions

On Thu, 16 Jul 2026 at 20:02, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
>
> On Thu, Jul 16, 2026 at 9:37 AM Rafia Sabih <rafia(dot)pghackers(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>
>>>
>>> On 2025-05-26 Mo 7:06 AM, Julien Tachoires wrote:
>>> > On Sat, Mar 29, 2025 at 08:46:01AM +0100, Julien Tachoires wrote:
>>> >> On Sun, Mar 02, 2025 at 02:23:54PM +0100, Julien Tachoires wrote:
>>> >>> On Sun, Mar 02, 2025 at 09:56:41AM +0100, Julien Tachoires wrote:
>>> >>>> With the help of the new TAM routine 'relation_options', table
>>> access
>>> >>>> methods can with this patch define their own reloptions
>>> >>>> parser/validator.
>>> >>>>
>>> >>>> These reloptions can be set via the following commands:
>>> >>>> 1. CREATE TABLE ... USING table_am
>>> >>>> WITH (option1='value1', option2='value2');
>>> >>>> 2. ALTER TABLE ...
>>> >>>> SET (option1 'value1', option2 'value2');
>>> >>>> 3. ALTER TABLE ... SET ACCESS METHOD table_am
>>> >>>> OPTIONS (option1 'value1', option2 'value2');
>>> >>>>
>>> >>>> When changing table's access method, the settings inherited from the
>>> >>>> former TAM can be dropped (if not supported by the new TAM) via:
>>> DROP
>>> >>>> option, or, updated via: SET option 'value'.
>>> >>>>
>>> >>>> Currently, tables using different TAMs than heap are able to use
>>> heap's
>>> >>>> reloptions (fillfactor, toast_tuple_target, etc...). With this patch
>>> >>>> applied, this is not the case anymore: if the TAM needs to have
>>> access
>>> >>>> to similar settings to heap ones, they have to explicitly define
>>> them.
>>> >>>>
>>> >>>> The 2nd patch file includes a new test module 'dummy_table_am' which
>>> >>>> implements a dummy table access method utilized to exercise TAM
>>> >>>> reloptions. This test module is strongly based on what we already
>>> have
>>> >>>> in 'dummy_index_am'. 'dummy_table_am' provides a complete example
>>> of TAM
>>> >>>> reloptions definition.
>>> >>>>
>>> >>>> This work is directly derived from SadhuPrasad's patch here [2].
>>> Others
>>> >>>> attempts were posted here [1] and here [3].
>>> >>>>
>>> >>>> [1]
>>> https://www.postgresql.org/message-id/flat/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel%40j-davis.com
>>> >>>> [2]
>>> https://www.postgresql.org/message-id/flat/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg(at)mail(dot)gmail(dot)com
>>> >>>> [3]
>>> https://www.postgresql.org/message-id/flat/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao%40hashdata.cn
>>> >>> Please find a new version including minor fixes: 'TAM' terms are
>>> >>> replaced by 'table AM'
>>> >> Please find a new rebased version.
>>> > New rebased version.
>>> >
>>>
>>>
>>> This thread seems to have gone dormant, sadly. Here's a fresh attempt I
>>> made (with assistance from Claude), before I was aware of the existence
>>> of this and other efforts. I think it meets most of the previous
>>> objections, and is in line with what we do for Index AMs.
>>>
>>> I was testing this patch and found this issue,
>> CREATE EXTENSION dummy_table_am;
>> CREATE TABLE t (a int) USING dummy_table_am WITH (option_int = 42);
>> ALTER TABLE t SET ACCESS METHOD heap;
>> SELECT reloptions FROM pg_class WHERE oid = 't'::regclass;
>> reloptions
>> -----------------
>> {option_int=42}
>> (1 row)
>> ALTER TABLE t SET (fillfactor = 50);
>> 2026-07-16 19:01:36.060 IST [95058] ERROR: unrecognized parameter
>> "option_int"
>> 2026-07-16 19:01:36.060 IST [95058] STATEMENT: ALTER TABLE t SET
>> (fillfactor = 50);
>> ERROR: unrecognized parameter "option_int"
>>
>> Basically, the option is left behind after changing the tableam to heap,
>> I think that isn't intentional.
>>
>>>
>>>
>>
>
>
> Thanks for reviewing. Good catch!
>
> Basically the code had an early exit in the validation code that shouldn't
> have been there.
>
> v2 attached with a fix for that, plus a regression test covering this
> direction (dummy_table_am -> heap with a still-set AM-specific option),
> which I verified fails against v1 and passes with the fix. I also
> folded the two independent access-method-resolution code paths in
> DefineRelation into one and added the new test module's typedefs to
> typedefs.list
>
> I can confirm that on the new patch, the alter command gives error and the
one with the RESET does the job,
ALTER TABLE t SET ACCESS METHOD heap;
2026-07-17 12:55:04.244 IST [55465] ERROR: unrecognized parameter
"option_int"
2026-07-17 12:55:04.244 IST [55465] STATEMENT: ALTER TABLE t SET ACCESS
METHOD heap;
ALTER TABLE t SET ACCESS METHOD heap, RESET(option_int);
ALTER TABLE

However, I encountered another issues now, this time with partitioning,
CREATE TABLE parted(a int) PARTITION BY RANGE(a);
CREATE TABLE parted_p1 PARTITION OF parted for values from (1) to (10) WITH
(option_int=50);
2026-07-20 12:20:54.137
IST [55465] ERROR: unrecognized parameter "option_int"
2026-07-20 12:20:54.137 IST [55465] STATEMENT: CREATE TABLE parted_p1
PARTITION OF parted for values from (1) to (10) WITH (option_int=50);
ERROR: unrecognized parameter "option_int"
So basically partitioned table is not able to read the new reloption.

cheers
>
> andrew
>
>

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH