Allow table AMs to define their own reloptions

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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.cn

Please find a new version including minor fixes: 'TAM' terms are
replaced by 'table AM'

--
Julien Tachoires

02.03.2025 16:23, Julien Tachoires пишет:

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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.cn

Please find a new version including minor fixes: 'TAM' terms are
replaced by 'table AM'

Good day, Julien.

Your forgot another one attempt discussion with patch [1]/messages/by-id/flat/3766675.7eaCOWfIcx@thinkpad-pgpro with alive
commitfest entry [2]https://commitfest.postgresql.org/patch/4688/

[1]: /messages/by-id/flat/3766675.7eaCOWfIcx@thinkpad-pgpro
[2]: https://commitfest.postgresql.org/patch/4688/

-------
regards
Yura Sokolov aka funny-falcon

Hi Yura,

On Sun, Mar 02, 2025 at 06:20:07PM +0300, Yura Sokolov wrote:

Your forgot another one attempt discussion with patch [1] with alive
commitfest entry [2]

[1] /messages/by-id/flat/3766675.7eaCOWfIcx@thinkpad-pgpro
[2] https://commitfest.postgresql.org/patch/4688/

Thank you. After taking a look at the patch itself and the email thread,
it seems this patch does not add custom reloptions to table AMs, see
[1]: /messages/by-id/1823308.yXV3o4JbTB@thinkpad-pgpro

[1]: /messages/by-id/1823308.yXV3o4JbTB@thinkpad-pgpro

--
Julien Tachoires

04.03.2025 09:16, Julien Tachoires пишет:

Hi Yura,

On Sun, Mar 02, 2025 at 06:20:07PM +0300, Yura Sokolov wrote:

Your forgot another one attempt discussion with patch [1] with alive
commitfest entry [2]

[1] /messages/by-id/flat/3766675.7eaCOWfIcx@thinkpad-pgpro
[2] https://commitfest.postgresql.org/patch/4688/

Thank you. After taking a look at the patch itself and the email thread,
it seems this patch does not add custom reloptions to table AMs, see
[1].

[1]: /messages/by-id/1823308.yXV3o4JbTB@thinkpad-pgpro

But intention of this patch is to make adding options easier, as author says:

But new option engine will make adding custom options for table AM more
easy task, as main goal of this patch is to simplify adding options
everywhere they needed. And yes, adding custom table AM options is one of
my next goals, as soon as this patch is commit.

I believe, adding generic way to options extension is better way than
targeting specific options. Don't you?

I believe Nikolay's patch needs review and support. As you're interesting
to the problem, may you at least look at his suggestion and analyze it from
the point of view of your task?

-------
regards
Yura Sokolov aka funny-falcon

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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.cn

Please find a new version including minor fixes: 'TAM' terms are
replaced by 'table AM'

Please find a new rebased version.

--
Julien Tachoires

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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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.

--
Julien Tachoires

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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan <andrew@dunslane.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]

/messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com

[2]

/messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com

[3]

/messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com

--
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH

On Thu, Jul 16, 2026 at 9:37 AM Rafia Sabih <rafia.pghackers@gmail.com>
wrote:

On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan <andrew@dunslane.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]

/messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com

[2]

/messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com

[3]

/messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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

cheers

andrew

On Thu, 16 Jul 2026 at 20:02, Andrew Dunstan <andrew@dunslane.net> wrote:

On Thu, Jul 16, 2026 at 9:37 AM Rafia Sabih <rafia.pghackers@gmail.com>
wrote:

On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan <andrew@dunslane.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]

/messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com

[2]

/messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com

[3]

/messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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

On 2026-07-20 Mo 2:52 AM, Rafia Sabih wrote:

On Thu, 16 Jul 2026 at 20:02, Andrew Dunstan <andrew@dunslane.net> wrote:

On Thu, Jul 16, 2026 at 9:37 AM Rafia Sabih
<rafia.pghackers@gmail.com> wrote:

On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan
<andrew@dunslane.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]

/messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com

[2]

/messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com

[3]

/messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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.

I think this is a test issue rather than a patch issue. Neither
statement names an access method: "parted" is a plain partitioned table
with no USING, so it falls back to default_table_access_method, i.e.
heap; parted_p1 doesn't override that either. heap has no idea what
option_int is, so the error is correct -- dummy_table_am was never in
the picture for either table.

If I add USING dummy_table_am to the parent, it works as expected:

    CREATE TABLE parted(a int) PARTITION BY RANGE(a) USING dummy_table_am;
    CREATE TABLE parted_p1 PARTITION OF parted FOR VALUES FROM (1) TO (10)
        WITH (option_int=50);
    -- CREATE TABLE, no error; reloptions = {option_int=50}

cheers

andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

Hello!

+dummy_table_am behaves like a heap table but accepts a different
+set of reloptions:
+
+  - "fillfactor"     (inherited from the core heap registration via
+                      add_reloption_to_kind)

+bool
+RelationHasStdRdOptions(Relation relation)
+{
+	if (relation->rd_options == NULL)
+		return false;
+	if (relation->rd_tableam == NULL)
+		return false;
+	return relation->rd_tableam->amoptions == NULL;
+}

dummy_table_am seems to accept but ignore fillfactor options with this, which based on the documentation above seem unintended?

CREATE TABLE heap_ff10 (a int) WITH (fillfactor=10);
CREATE TABLE dummy_ff10 (a int) USING dummy_table_am WITH (fillfactor=10);

INSERT INTO heap_ff10 SELECT generate_series(1,200000);
INSERT INTO dummy_ff10 SELECT generate_series(1,200000);

SELECT relname, relpages,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('heap_ff10','dummy_ff10')
ORDER BY relname;
relname | relpages | size
------------+----------+---------
dummy_ff10 | 885 | 7080 kB
heap_ff10 | 9091 | 71 MB
(2 rows)

Also it isn't critical for the current tests, but it doesn't seem to support text columns, so that contradicts the generic behaves like heap claim a bit:

CREATE TABLE t_txt (a int, b text) USING dummy_table_am;
ERROR: only heap AM is supported

Hi Andrew,

On Thu, Jul 16, 2026 at 10:33 PM Andrew Dunstan <andrew@dunslane.net> wrote:

On Thu, Jul 16, 2026 at 9:37 AM Rafia Sabih <rafia.pghackers@gmail.com> wrote:

On Thu, 4 Jun 2026 at 04:32, Andrew Dunstan <andrew@dunslane.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] /messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com
[2] /messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com
[3] /messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.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

cheers

andrew

I checked the usage of extractRelOptions, and found that in
extract_autovac_opts, it unconditionally interprets the result as
StdRdOptions, the patch doesn't seem to consider that, is that
intentional?

--
Regards
Junwang Zhao

On Sat, Aug 15, 2026 at 5:46 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

+dummy_table_am behaves like a heap table but accepts a different
+set of reloptions:
+
+  - "fillfactor"     (inherited from the core heap registration via
+                      add_reloption_to_kind)

+bool
+RelationHasStdRdOptions(Relation relation)
+{
+       if (relation->rd_options == NULL)
+               return false;
+       if (relation->rd_tableam == NULL)
+               return false;
+       return relation->rd_tableam->amoptions == NULL;
+}

dummy_table_am seems to accept but ignore fillfactor options with
this, which based on the documentation above seem unintended?

CREATE TABLE heap_ff10 (a int) WITH (fillfactor=10);
CREATE TABLE dummy_ff10 (a int) USING dummy_table_am WITH (fillfactor=10);

INSERT INTO heap_ff10 SELECT generate_series(1,200000);
INSERT INTO dummy_ff10 SELECT generate_series(1,200000);

SELECT relname, relpages,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('heap_ff10','dummy_ff10')
ORDER BY relname;
relname | relpages | size
------------+----------+---------
dummy_ff10 | 885 | 7080 kB
heap_ff10 | 9091 | 71 MB
(2 rows)

I think this might be due to the fact that dummy_table_am doesn't
handle the `fillfactor` itself, since it's just a test module, I think
it's ok.

Also it isn't critical for the current tests, but it doesn't seem to
support text columns, so that contradicts the generic behaves like
heap claim a bit:

CREATE TABLE t_txt (a int, b text) USING dummy_table_am;
ERROR: only heap AM is supported

--
Regards
Junwang Zhao

On 2026-08-14 Fr 5:46 PM, Zsolt Parragi wrote:

Hello!

+dummy_table_am behaves like a heap table but accepts a different
+set of reloptions:
+
+  - "fillfactor"     (inherited from the core heap registration via
+                      add_reloption_to_kind)

+bool
+RelationHasStdRdOptions(Relation relation)
+{
+	if (relation->rd_options == NULL)
+		return false;
+	if (relation->rd_tableam == NULL)
+		return false;
+	return relation->rd_tableam->amoptions == NULL;
+}

dummy_table_am seems to accept but ignore fillfactor options with
this, which based on the documentation above seem unintended?

CREATE TABLE heap_ff10 (a int) WITH (fillfactor=10);
CREATE TABLE dummy_ff10 (a int) USING dummy_table_am WITH (fillfactor=10);

INSERT INTO heap_ff10 SELECT generate_series(1,200000);
INSERT INTO dummy_ff10 SELECT generate_series(1,200000);

SELECT relname, relpages,
pg_size_pretty(pg_relation_size(oid)) AS size
FROM pg_class
WHERE relname IN ('heap_ff10','dummy_ff10')
ORDER BY relname;
relname | relpages | size
------------+----------+---------
dummy_ff10 | 885 | 7080 kB
heap_ff10 | 9091 | 71 MB
(2 rows)

Also it isn't critical for the current tests, but it doesn't seem to
support text columns, so that contradicts the generic behaves like
heap claim a bit:

CREATE TABLE t_txt (a int, b text) USING dummy_table_am;
ERROR: only heap AM is supported

Both of your reports are real bugs, fixed in v5. Replying to both your
email and Junwang's together.

[Zsolt] dummy_table_am seems to accept but ignore fillfactor
options [...]
heap_ff10 | 9091 | 71 MB
dummy_ff10 | 885 | 7080 kB

Confirmed, and not just fillfactor. Four macros/call sites read
StdRdOptions fields straight out of rd_options behind a check,
RelationHasStdRdOptions(), that only asked "does this AM have a
custom amoptions at all" rather than "is this field actually there"
-- blocking exactly the case add_reloption_to_kind() exists for.
Same problem for toast_tuple_target, parallel_workers,
vacuum_index_cleanup, vacuum_truncate,
vacuum_max_eager_freeze_failure_rate, and autovacuum_enabled.

Fixed with TableAmRoutine.has_std_options_prefix: an AM sets it when
its amoptions struct embeds a full StdRdOptions as its first member,
and RelationHasStdRdOptions() trusts that. dummy_table_am now embeds
StdRdOptions and registers all seven other fields too, not just
fillfactor, so each gets its real default instead of zero. Re-ran
your repro:

    dummy_ff10 | 9091 | 71 MB
    heap_ff10  | 9091 | 71 MB

Matches now. Added a regression case that fails on pre-v5, passes on v5.

[Zsolt] doesn't seem to support text columns [...]
ERROR: only heap AM is supported
[Junwang] I think it's ok, it's just a test module

Also a real bug. Fixed (after some -hackers discussion) by having
dummy_table_am override relation_toast_am directly. Text columns work
now.

[Junwang] extract_autovac_opts... unconditionally interprets the
result as StdRdOptions, is that intentional?

It is intentional.

I think the behaviour is right: it means autovacuum's own
scheduling only ever sees the standard autovacuum_* names, never an
AM's own amoptions. (And making it AM-aware would cost a catalog lookup
on every relation in autovacuum's periodic scan, for a capability
nothing needs.) Documented that constraint in tableam.sgml rather
than leave it to be rediscovered.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com