Allow table AMs to define their own reloptions
Hi,
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]/messages/by-id/CAFF0-CG4KZHdtYHMsonWiXNzj16gWZpduXAn8yF7pDDub+GQMg@mail.gmail.com. Others
attempts were posted here [1]/messages/by-id/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel@j-davis.com and here [3]/messages/by-id/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao@hashdata.cn.
[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
--
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'
--
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.cnPlease 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].
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.cnPlease 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.cnPlease 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.cnPlease 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 ofTAM
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 haveaccess
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 alreadyhave
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 haveaccess
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 alreadyhave
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.listI 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.listI 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 TABLEHowever, 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.cnPlease 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.listcheers
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
Hi,
Both of Zsolt's reports are real bugs, fixed in v5. Replying to both
threads 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 v4, 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, not a limitation: heap's own relation_toast_am
callback, heapam_relation_toast_am(), returns rel->rd_rel->relam,
which is dummy_table_am's own oid rather than heap's since it
inherited that callback unchanged along with the rest of heap's
routine. That made its TOAST table a dummy_table_am relation too,
and building its index failed in heap_getnext(), which requires a
real heap relation. Fixed 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?
Yes, that call passes amoptions = NULL
explicitly, which forces heap_reloptions() regardless of the
relation's actual access method, so the result really is
StdRdOptions.
I think the behaviour is right, though: it means autovacuum's own
scheduling only ever sees the standard autovacuum_* names, never an
AM's own amoptions. 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
rebased patch attached.
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
On 2026-08-28 Fr 3:43 PM, Andrew Dunstan wrote:
rebased patch attached.
Attached is v7. Changes since v6:
- ALTER TABLE ... SET ACCESS METHOD skipped reloption revalidation when
the new AM has no option parser of its own, on the theory that falling
back to the heap parser meant nothing could have changed. But the
old AM may have accepted options heap doesn't know, so e.g. switching a
dummy_table_am table back to heap with option_int still set silently
kept an option the next relcache load couldn't interpret. Validate
unconditionally, with a test covering that direction.
- DefineRelation validated a partition's reloptions against the wrong AM
when the partitioned parent has no AM of its own (relam = 0): the
validation lookup stopped at get_rel_relam(parent) while the actual
relation creation falls back to default_table_access_method. With a
non-heap default AM, PARTITION OF ... WITH (am_specific_option) was
spuriously rejected. Apply the same fallback, with a test.
- dummy_table_am registered autovacuum_enabled as a bool reloption; core
defines it as ternary. Also rewrote the comments explaining why every
standard option must be registered (the has_std_options_prefix
contract), which were more confusing than helpful.
cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com
Hi Hackers,
While testing dummy_table_am (src/test/modules/dummy_table_am/), I noticed
the README doesn't match the code
README says
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)
- "option_int" (integer)
- "option_real" (real)
- "option_bool" (boolean)
- "option_enum" (enum, one|two)
Standard heap options such as parallel_workers, autovacuum_*, and
toast_tuple_target are intentionally NOT accepted.
But create_reloptions_table() in dummy_table_am.c registers 8 standard
options: fillfactor, toast_tuple_target, parallel_workers,
vacuum_index_cleanup,
vacuum_truncate, vacuum_max_eager_freeze_failure_rate,
autovacuum_enabled, and user_catalog_table
Do we need to update documentation as per create_reloptions_table()?
Thanks & Best Regards,
Ajit
On Mon, 31 Aug 2026 at 17:57, Andrew Dunstan <andrew@dunslane.net> wrote:
Show quoted text
On 2026-08-28 Fr 3:43 PM, Andrew Dunstan wrote:
rebased patch attached.
Attached is v7. Changes since v6:
- ALTER TABLE ... SET ACCESS METHOD skipped reloption revalidation when
the new AM has no option parser of its own, on the theory that falling
back to the heap parser meant nothing could have changed. But the
old AM may have accepted options heap doesn't know, so e.g. switching a
dummy_table_am table back to heap with option_int still set silently
kept an option the next relcache load couldn't interpret. Validate
unconditionally, with a test covering that direction.
- DefineRelation validated a partition's reloptions against the wrong AM
when the partitioned parent has no AM of its own (relam = 0): the
validation lookup stopped at get_rel_relam(parent) while the actual
relation creation falls back to default_table_access_method. With a
non-heap default AM, PARTITION OF ... WITH (am_specific_option) was
spuriously rejected. Apply the same fallback, with a test.
- dummy_table_am registered autovacuum_enabled as a bool reloption; core
defines it as ternary. Also rewrote the comments explaining why every
standard option must be registered (the has_std_options_prefix
contract), which were more confusing than helpful.cheers
andrew
--
Andrew Dunstan
EDB: https://www.enterprisedb.com