Re: SUBTRANS: Minimizing calls to SubTransSetParent()
Hi Tomas, Le mar. 29 nov. 2022 à 14:06, Tomas Vondra a écrit : > > On 11/29/22 13:49, Simon Riggs wrote: > > On Thu, 17 Nov 2022 at 17:29, Simon Riggs > > wrote: > > > >> (yes, the last line shows x10 performance patched, that is not a typo) > > > > New version of patch, now just a one-line patch! > > > > Results show it's still a good win for XidInMVCCSnapshot(). > > > > I'm a bit confused - for which workload/benchmark are there results? > It's generally a good idea to share the scripts used to run the test and > not just a chart. The scripts have been attached to this thread with the initial performance results. Anyway, re-sending those (including a minor fix). -- Julien Tachoires EDB subtrans-benchmark.tar.gz Description: GNU Zip compressed data
Re: SUBTRANS: Minimizing calls to SubTransSetParent()
Hi, Le mar. 1 nov. 2022 à 09:39, Dilip Kumar a écrit : > > On Fri, Oct 28, 2022 at 10:55 PM Julien Tachoires wrote: > > > > Hi, > > > > Le lun. 26 sept. 2022 à 15:57, Simon Riggs > > a écrit : > > > > > > On Fri, 16 Sept 2022 at 13:20, Simon Riggs > > > wrote: > > > > > > > > Thanks for the review. > > > > > > > > v10 attached > > > > > > v11 attached, corrected for recent commit > > > 14ff44f80c09718d43d853363941457f5468cc03. > > > > Please find below the performance tests results I have produced for this > > patch. > > Attaching some charts and the scripts used to reproduce these tests. > > > > 1. Assumption > > > > The number of sub-transaction issued by only one long running > > transaction may affect global TPS throughput if the number of > > sub-transaction exceeds 64 (sub-overflow) > > > > 2. Testing scenario > > > > Based on pgbench, 2 different types of DB activity are applied concurrently: > > - 1 long running transaction, including N sub-transactions > > - X pgbench clients running read-only workload > > > > Tests are executed with a varying number of sub-transactions: from 0 to 128 > > Key metric is the TPS rate reported by pgbench runs in read-only mode > > > > Tests are executed against > > - HEAD (14a737) > > - HEAD (14a737) + 002_minimize_calls_to_SubTransSetParent.v11.patch > > > > 3. Long transaction anatomy > > > > Two different long transactions are tested because they don't have the > > exact same impact on performance. > > > > Transaction number 1 includes one UPDATE affecting each row of > > pgbench_accounts, plus an additional UPDATE affecting only one row but > > executed in its own rollbacked sub-transaction: > > BEGIN; > > SAVEPOINT s1; > > SAVEPOINT s2; > > -- ... > > SAVEPOINT sN - 1; > > UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid > 0; > > SAVEPOINT sN; > > UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = 12345; > > ROLLBACK TO SAVEPOINT sN; > > -- sleeping until the end of the test > > ROLLBACK; > > > > Transaction 2 includes one UPDATE affecting each row of pgbench_accounts: > > BEGIN; > > SAVEPOINT s1; > > SAVEPOINT s2; > > -- ... > > SAVEPOINT sN; > > UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid > 0; > > -- sleeping until the end of the test > > ROLLBACK; > > > > 4. Test results with transaction 1 > > > > TPS vs number of sub-transaction > > > > nsubx HEAD patched > > > > 0 441109 439474 > > 8 439045 438103 > > 16 439123 436993 > > 24 436269 434194 > > 32 439707 437429 > > 40 439997 437220 > > 48 439388 437422 > > 56 439409 437210 > > 64 439748 437366 > > 7292869 434448 > > 8066577 434100 > > 8861243 434255 > > 9657016 434419 > > 10452132 434917 > > 11249181 433755 > > 12046581 434044 > > 12844067 434268 > > > > Perf profiling on HEAD with 80 sub-transactions: > > Overhead Symbol > > 51.26% [.] LWLockAttemptLock > > 24.59% [.] LWLockRelease > >0.36% [.] base_yyparse > >0.35% [.] PinBuffer > >0.34% [.] AllocSetAlloc > >0.33% [.] hash_search_with_hash_value > >0.22% [.] LWLockAcquire > >0.20% [.] UnpinBuffer > >0.15% [.] SimpleLruReadPage_ReadOnly > >0.15% [.] _bt_compare > > > > Perf profiling on patched with 80 sub-transactions: > > Overhead Symbol > > 2.64% [.] AllocSetAlloc > > 2.09% [.] base_yyparse > > 1.76% [.] hash_search_with_hash_value > > 1.62% [.] LWLockAttemptLock > > 1.26% [.] MemoryContextAllocZeroAligned > > 0.93% [.] _bt_compare > > 0.92% [.] expression_tree_walker_impl.part.4 > > 0.84% [.] SearchCatCache1 > > 0.79% [.] palloc > > 0.64% [.] core_yylex > > > > 5. Test results with transaction 2 > > > > nsubx HEAD patched > > > > 0 440145 443816 > > 8 438867 443081 > > 16 438634 441786 > > 24 436406 440187 > > 32 439203 442447 > > 40 439819 443574 > > 48 439314 442941 > > 56 439801 443736 > > 64 439074 441970 > > 72 439833 444132 > > 80 148737 439941 > > 88 413714 443343 > >
Compress ReorderBuffer spill files using LZ4
Hi, When the content of a large transaction (size exceeding logical_decoding_work_mem) and its sub-transactions has to be reordered during logical decoding, then, all the changes are written on disk in temporary files located in pg_replslot/. Decoding very large transactions by multiple replication slots can lead to disk space saturation and high I/O utilization. When compiled with LZ4 support (--with-lz4), this patch enables data compression/decompression of these temporary files. Each transaction change that must be written on disk (ReorderBufferDiskChange) is now compressed and encapsulated in a new structure. 3 different compression strategies are implemented: 1. LZ4 streaming compression is the preferred one and works efficiently for small individual changes. 2. LZ4 regular compression when the changes are too large for using the streaming API. 3. No compression when compression fails, the change is then stored not compressed. When not using compression, the following case generates 1590MB of spill files: CREATE TABLE t (i INTEGER PRIMARY KEY, t TEXT); INSERT INTO t SELECT i, 'Hello number n°'||i::TEXT FROM generate_series(1, 1000) as i; With LZ4 compression, it creates 653MB of spill files: 58.9% less disk space usage. Open items: 1. The spill_bytes column from pg_stat_get_replication_slot() still returns plain data size, not the compressed data size. Should we expose the compressed data size when compression occurs? 2. Do we want a GUC to switch compression on/off? Regards, JT v1-0001-Compress-ReorderBuffer-spill-files-using-LZ4.patch Description: Binary data
Re: Compress ReorderBuffer spill files using LZ4
Le jeu. 6 juin 2024 à 04:13, Amit Kapila a écrit : > > On Thu, Jun 6, 2024 at 4:28 PM Julien Tachoires wrote: > > > > When the content of a large transaction (size exceeding > > logical_decoding_work_mem) and its sub-transactions has to be > > reordered during logical decoding, then, all the changes are written > > on disk in temporary files located in pg_replslot/. > > Decoding very large transactions by multiple replication slots can > > lead to disk space saturation and high I/O utilization. > > > > Why can't one use 'streaming' option to send changes to the client > once it reaches the configured limit of 'logical_decoding_work_mem'? That's right, setting subscription's option 'streaming' to 'on' moves the problem away from the publisher to the subscribers. This patch tries to improve the default situation when 'streaming' is set to 'off'. > > 2. Do we want a GUC to switch compression on/off? > > > > It depends on the overhead of decoding. Did you try to measure the > decoding overhead of decompression when reading compressed files? Quick benchmarking executed on my laptop shows 1% overhead. Table DDL: CREATE TABLE t (i INTEGER PRIMARY KEY, t TEXT); Data generated with: INSERT INTO t SELECT i, 'Text number n°'||i::TEXT FROM generate_series(1, 1000) as i; Restoration duration measured using timestamps of log messages: "DEBUG: restored / changes from disk" HEAD: 25.54s, 25.94s, 25.516s, 26.267s, 26.11s / avg=25.874s Patch: 26.872s, 26.311s, 25.753s, 26.003, 25.843s / avg=26.156s Regards, JT
Re: Compress ReorderBuffer spill files using LZ4
Le jeu. 6 juin 2024 à 06:40, Amit Kapila a écrit : > > On Thu, Jun 6, 2024 at 6:22 PM Julien Tachoires wrote: > > > > Le jeu. 6 juin 2024 à 04:13, Amit Kapila a écrit : > > > > > > On Thu, Jun 6, 2024 at 4:28 PM Julien Tachoires wrote: > > > > > > > > When the content of a large transaction (size exceeding > > > > logical_decoding_work_mem) and its sub-transactions has to be > > > > reordered during logical decoding, then, all the changes are written > > > > on disk in temporary files located in pg_replslot/. > > > > Decoding very large transactions by multiple replication slots can > > > > lead to disk space saturation and high I/O utilization. > > > > > > > > > > Why can't one use 'streaming' option to send changes to the client > > > once it reaches the configured limit of 'logical_decoding_work_mem'? > > > > That's right, setting subscription's option 'streaming' to 'on' moves > > the problem away from the publisher to the subscribers. This patch > > tries to improve the default situation when 'streaming' is set to > > 'off'. > > > > Can we think of changing the default to 'parallel'? BTW, it would be > better to use 'parallel' for the 'streaming' option, if the workload > has large transactions. Is there a reason to use a default value in > this case? You're certainly right, if using the streaming API helps to avoid bad situations and there is no downside, it could be used by default. > > > > 2. Do we want a GUC to switch compression on/off? > > > > > > > > > > It depends on the overhead of decoding. Did you try to measure the > > > decoding overhead of decompression when reading compressed files? > > > > Quick benchmarking executed on my laptop shows 1% overhead. > > > > Thanks. We probably need different types of data (say random data in > bytea column, etc.) for this. Yes, good idea, will run new tests in that sense. Thank you! Regards, JT
Re: Compress ReorderBuffer spill files using LZ4
Le jeu. 6 juin 2024 à 07:24, Alvaro Herrera a écrit : > > On 2024-Jun-06, Amit Kapila wrote: > > > On Thu, Jun 6, 2024 at 4:28 PM Julien Tachoires wrote: > > > > > > When the content of a large transaction (size exceeding > > > logical_decoding_work_mem) and its sub-transactions has to be > > > reordered during logical decoding, then, all the changes are written > > > on disk in temporary files located in pg_replslot/. > > > Decoding very large transactions by multiple replication slots can > > > lead to disk space saturation and high I/O utilization. > > I like the general idea of compressing the output of logical decoding. > It's not so clear to me that we only want to do so for spilling to disk; > for instance, if the two nodes communicate over a slow network, it may > even be beneficial to compress when streaming, so to this question: > > > Why can't one use 'streaming' option to send changes to the client > > once it reaches the configured limit of 'logical_decoding_work_mem'? > > I would say that streaming doesn't necessarily have to mean we don't > want compression, because for some users it might be beneficial. Interesting idea, will try to evaluate how to compress/decompress data transiting via streaming and how good the compression ratio would be. > I think a GUC would be a good idea. Also, what if for whatever reason > you want a different compression algorithm or different compression > parameters? Looking at the existing compression UI we offer in > pg_basebackup, perhaps you could add something like this: > > compress_logical_decoding = none > compress_logical_decoding = lz4:42 > compress_logical_decoding = spill-zstd:99 > > "none" says to never use compression (perhaps should be the default), > "lz4:42" says to use lz4 with parameters 42 on both spilling and > streaming, and "spill-zstd:99" says to use Zstd with parameter 99 but > only for spilling to disk. I agree, if the server was compiled with support of multiple compression libraries, users should be able to choose which one they want to use. > (I don't mean to say that you should implement Zstd compression with > this patch, only that you should choose the implementation so that > adding Zstd support (or whatever) later is just a matter of adding some > branches here and there. With the current #ifdef you propose, it's hard > to do that. Maybe separate the parts that depend on the specific > algorithm to algorithm-agnostic functions.) Makes sense, will rework this patch in that way. Thank you! Regards, JT
Re: Compress ReorderBuffer spill files using LZ4
Le ven. 7 juin 2024 à 05:59, Tomas Vondra a écrit : > > On 6/6/24 12:58, Julien Tachoires wrote: > > ... > > > > When compiled with LZ4 support (--with-lz4), this patch enables data > > compression/decompression of these temporary files. Each transaction > > change that must be written on disk (ReorderBufferDiskChange) is now > > compressed and encapsulated in a new structure. > > > > I'm a bit confused, but why tie this to having lz4? Why shouldn't this > be supported even for pglz, or whatever algorithms we add in the future? That's right, reworking this patch in that sense. Regards, JT
Re: Compress ReorderBuffer spill files using LZ4
Le lun. 15 juil. 2024 à 12:28, Tomas Vondra a écrit : > > On 7/15/24 20:50, Julien Tachoires wrote: > > The last patch of this set is still in WIP, it adds the machinery > > required for setting the compression methods as a subscription option: > > CREATE SUBSCRIPTION ... WITH (spill_compression = ...); > > I think there is a major problem with this approach: the logical > > decoding context is tied to one replication slot, but multiple > > subscriptions can use the same replication slot. How should this work > > if 2 subscriptions want to use the same replication slot but different > > compression methods? > > > > Do we really support multiple subscriptions sharing the same slot? I > don't think we do, but maybe I'm missing something. You are right, it's not supported, the following error is raised in this case: ERROR: replication slot "sub1" is active for PID 51735 I was distracted by the fact that nothing prevents the configuration of multiple subscriptions sharing the same replication slot. Thanks, JT
Re: Compress ReorderBuffer spill files using LZ4
Hi Tomas, Le lun. 23 sept. 2024 à 18:13, Tomas Vondra a écrit : > > Hi, > > I've spent a bit more time on this, mostly running tests to get a better > idea of the practical benefits. Thank you for your code review and testing! > Firstly, I think there's a bug in ReorderBufferCompress() - it's legal > for pglz_compress() to return -1. This can happen if the data is not > compressible, and would not fit into the output buffer. The code can't > just do elog(ERROR) in this case, it needs to handle that by storing the > raw data. The attached fixup patch makes this work for me - I'm not > claiming this is the best way to handle this, but it works. > > FWIW I find it strange the tests included in the patch did not trigger > this. That probably means the tests are not quite sufficient. > > > Now, to the testing. Attached are two scripts, testing different cases: > > test-columns.sh - Table with a variable number of 'float8' columns. > > test-toast.sh - Table with a single text column. > > The script always sets up a publication/subscription on two instances, > generates certain amount of data (~1GB for columns, ~3.2GB for TOAST), > waits for it to be replicated to the replica, and measures how much data > was spilled to disk with the different compression methods (off, pglz > and lz4). There's a couple more metrics, but that's irrelevant here. It would be interesting to run the same tests with zstd: in my early testing I found that zstd was able to provide a better compression ratio than lz4, but seemed to use more CPU resources/is slower. > For the "column" test, it looks like this (this is in MB): > > rowscolumnsdistributionoff pglzlz4 > > 10 1000compressible778 20 9 > random778 778 16 > > 100 100compressible916 116 62 > random916 916 67 > > It's very clear that for the "compressible" data (which just copies the > same value into all columns), both pglz and lz4 can significantly reduce > the amount of data. For 1000 columns it's 780MB -> 20MB/9MB, for 100 > columns it's a bit less efficient, but still good. > > For the "random" data (where every column gets a random value, but rows > are copied), it's a very different story - pglz does not help at all, > while lz4 still massively reduces the amount of spilled data. > > I think the explanation is very simple - for pglz, we compress each row > on it's own, there's no concept of streaming/context. If a row is > compressible, it works fine, but when the row gets random, pglz can't > compress it at all. For lz4, this does not matter, because with the > streaming mode it still sees that rows are just repeated, and so can > compress them efficiently. That's correct. > For TOAST test, the results look like this: > > distribution repeatstoast offpglz lz4 > === > compressible 1lz4 14 2 1 > pglz 40 4 3 > 1000lz4 32 16 9 > pglz 54 17 10 > - > random 1lz4330533053157 > pglz 330533053157 > 1000lz4316631621580 > pglz 333433261745 >-- >random2 1lz4330533053157 > pglz 330533053158 > 1000lz4316031563010 > pglz 333433263172 > > The "repeats" value means how long the string is - it's the number of > "md5" hashes added to the string. The number of rows is calculated to > keep the total amount of data the same. The "toast" column tracks what > compression was used for TOAST, I was wondering if it matters. > > This time there are three data distributions - compressible means that > each TOAST value is nicely compressible, "random" means each value is > random (not compressible), but the rows are just copy of the same value > (so on the whole there's a lot of redundancy). And "random2" means each > row is random and unique (so not compressible at all). > > The table shows that with compressible TOAST values, compressing the > spill file is rather useless. The reason is that ReorderBufferCompress > is handling raw TOAST data, which is already compressed. Yes, it may > further reduce the amount of data, but it's negligible when co
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]. 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+g...@mail.gmail.com
[3]
https://www.postgresql.org/message-id/flat/AMUA1wBBBxfc3tKRLLdU64rb.1.1683276279979.Hmail.wuhao%40hashdata.cn
--
Julien Tachoires
>From 8968bb1cf92e373523377c79ff42e76dc9fc20ed Mon Sep 17 00:00:00 2001
From: Julien Tachoires
Date: Sat, 1 Mar 2025 17:59:49 +0100
Subject: [PATCH 1/2] Allow table AMs to define their own reloptions
With the help of the new routine 'relation_options', table access
methods can now define their own reloptions.
These options 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 from the former
TAM can be dropped (if not supported by the new TAM) via:
DROP option, or, updated via: SET option 'value'.
Before this commit, tables using different TAMs than heap were able
to use heap's reloptions (fillfactor, toast_tuple_target, etc...).
Now, this is not the case anymore: if the TAM needs to have access
to settings similar to heap ones, they must explicitly define them.
This work is directly derived from SadhuPrasad's patch named:
v4-0001-PATCH-V4-Per-table-storage-parameters-for-TableAM.patch
---
doc/src/sgml/ref/alter_table.sgml| 13 +-
doc/src/sgml/ref/create_table.sgml | 3 +-
src/backend/access/common/reloptions.c | 66 -
src/backend/access/heap/heapam_handler.c | 2 +
src/backend/commands/foreigncmds.c | 2 +-
src/backend/commands/tablecmds.c | 180 ---
src/backend/parser/gram.y| 9 ++
src/backend/postmaster/autovacuum.c | 18 ++-
src/backend/utils/cache/relcache.c | 11 +-
src/include/access/reloptions.h | 6 +-
src/include/access/tableam.h | 10 ++
src/include/commands/defrem.h| 1 +
12 files changed, 286 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8e56b8e59b0..e38200e20d2 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -76,7 +76,7 @@ ALTER TABLE [ IF EXISTS ] name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
-SET ACCESS METHOD { new_access_method | DEFAULT }
+SET ACCESS METHOD { new_access_method | DEFAULT } [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter [= value] [, ... ] )
@@ -734,7 +734,7 @@ WITH ( MODULUS numeric_literal, REM
-SET ACCESS METHOD
+SET ACCESS METHOD { new_access_method | DEFAULT } [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
This form changes the access method of the table by rewriting it
@@ -752,6 +752,15 @@ WITH ( MODULUS numeric_literal, REM
causing future partitions to default to
default_table_access_method.
+
+ Specifying OPTIONS allows to change options for
+ the table when changing
Re: 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]
> https://www.postgresql.org/message-id/flat/429fb58fa3218221bb17c7bf9e70e1aa6cfc6b5d.camel%40j-davis.com
> [2]
> https://www.postgresql.org/message-id/flat/caff0-cg4kzhdtyhmsonwixnzj16gwzpduxan8yf7pddub+g...@mail.gmail.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'
--
Julien Tachoires
>From 4b20842ae509f6c330c48e6792fd4c966e3f Mon Sep 17 00:00:00 2001
From: Julien Tachoires
Date: Sat, 1 Mar 2025 17:59:49 +0100
Subject: [PATCH 1/2] Allow table AMs to define their own reloptions
With the help of the new routine 'relation_options', table access
methods can now define their own reloptions.
These options 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 from the former
table AM can be dropped (if not supported by the new table AM) via:
DROP option, or, updated via: SET option 'value'.
Before this commit, tables using different table AMs than heap were
able to use heap's reloptions (fillfactor, toast_tuple_target,
etc...). Now, this is not the case anymore: if the table AM needs
to have access to settings similar to heap ones, they must
explicitly define them.
This work is directly derived from SadhuPrasad's patch named:
v4-0001-PATCH-V4-Per-table-storage-parameters-for-TableAM.patch
---
doc/src/sgml/ref/alter_table.sgml| 13 +-
doc/src/sgml/ref/create_table.sgml | 3 +-
src/backend/access/common/reloptions.c | 66 -
src/backend/access/heap/heapam_handler.c | 2 +
src/backend/commands/foreigncmds.c | 2 +-
src/backend/commands/tablecmds.c | 180 ---
src/backend/parser/gram.y| 9 ++
src/backend/postmaster/autovacuum.c | 18 ++-
src/backend/utils/cache/relcache.c | 11 +-
src/include/access/reloptions.h | 6 +-
src/include/access/tableam.h | 10 ++
src/include/commands/defrem.h| 1 +
12 files changed, 286 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 8e56b8e59b0..e38200e20d2 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -76,7 +76,7 @@ ALTER TABLE [ IF EXISTS ] name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
-SET ACCESS METHOD { new_access_method | DEFAULT }
+SET ACCESS METHOD { new_access_method | DEFAULT } [ OPTIONS ( [ ADD | SET | DROP ] option ['value'] [, ... ] ) ]
SET TABLESPACE new_tablespace
SET { LOGGED | UNLOGGED }
SET ( storage_parameter [= value] [, ... ] )
@@ -734,7 +734,7 @@ WITH ( MODULUS numeric_literal, REM
-SET ACCESS METHOD
+SET ACCESS METHOD { new_access_method | DEFAULT } [ OPTIONS ( [ ADD | SET |
Re: Allow table AMs to define their own reloptions
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] https://postgr.es/m/flat/3766675.7eaCOWfIcx%40thinkpad-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]: https://www.postgresql.org/message-id/1823308.yXV3o4JbTB%40thinkpad-pgpro -- Julien Tachoires
Re: Allow table AMs to define their own reloptions
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+g...@mail.gmail.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.
--
Julien Tachoires
>From ceb99fc9cb49eb5bca7ef35dd2afc767b5d2abf1 Mon Sep 17 00:00:00 2001
From: Julien Tachoires
Date: Sat, 1 Mar 2025 17:59:49 +0100
Subject: [PATCH 1/2] Allow table AMs to define their own reloptions
With the help of the new routine 'relation_options', table access
methods can now define their own reloptions.
These options 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 from the former
table AM can be dropped (if not supported by the new table AM) via:
DROP option, or, updated via: SET option 'value'.
Before this commit, tables using different table AMs than heap were
able to use heap's reloptions (fillfactor, toast_tuple_target,
etc...). Now, this is not the case anymore: if the table AM needs
to have access to settings similar to heap ones, they must
explicitly define them.
This work is directly derived from SadhuPrasad's patch named:
v4-0001-PATCH-V4-Per-table-storage-parameters-for-TableAM.patch
---
doc/src/sgml/ref/alter_table.sgml| 13 +-
doc/src/sgml/ref/create_table.sgml | 3 +-
src/backend/access/common/reloptions.c | 66 -
src/backend/access/heap/heapam_handler.c | 2 +
src/backend/commands/foreigncmds.c | 2 +-
src/backend/commands/tablecmds.c | 180 ---
src/backend/parser/gram.y| 9 ++
src/backend/postmaster/autovacuum.c | 18 ++-
src/backend/utils/cache/relcache.c | 11 +-
src/include/access/reloptions.h | 6 +-
src/include/access/tableam.h | 10 ++
src/include/commands/defrem.h| 1 +
12 files changed, 286 insertions(+), 35 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 11d1bc7dbe1..1b4dd023877 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -77,7 +77,7 @@ ALTER TABLE [ IF EXISTS ] name
CLUSTER ON index_name
SET WITHOUT CLUSTER
SET WITHOUT OIDS
-SET ACCESS METHOD { new_access_method | DEFAULT }
+SET ACCESS METHOD { new_access_method | DEFAULT } [ OPTIONS ( [ ADD | SET | DROP ] option ['value'
Re: Allow table AMs to define their own reloptions
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+g...@mail.gmail.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. -- Julien Tachoires >From e61a0ef687b19edc676e0cc70f9057ad844c192f Mon Sep 17 00:00:00 2001 From: Julien Tachoires Date: Sat, 1 Mar 2025 17:59:49 +0100 Subject: [PATCH 1/2] Allow table AMs to define their own reloptions With the help of the new routine 'relation_options', table access methods can now define their own reloptions. These options 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 from the former table AM can be dropped (if not supported by the new table AM) via: DROP option, or, updated via: SET option 'value'. Before this commit, tables using different table AMs than heap were able to use heap's reloptions (fillfactor, toast_tuple_target, etc...). Now, this is not the case anymore: if the table AM needs to have access to settings similar to heap ones, they must explicitly define them. This work is directly derived from SadhuPrasad's patch named: v4-0001-PATCH-V4-Per-table-storage-parameters-for-TableAM.patch --- doc/src/sgml/ref/alter_table.sgml| 13 +- doc/src/sgml/ref/create_table.sgml | 3 +- src/backend/access/common/reloptions.c | 66 - src/backend/access/heap/heapam_handler.c | 2 + src/backend/commands/foreigncmds.c | 2 +- src/backend/commands/tablecmds.c | 180 --- src/backend/parser/gram.y| 9 ++ src/backend/postmaster/autovacuum.c | 18 ++- src/backend/utils/cache/relcache.c | 11 +- src/include/access/reloptions.h | 6 +- src/include/access/tableam.h | 10 ++ src/include/commands/defrem.h| 1 + 12 files changed, 286 insertions(+), 35 deletions(-) diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index d63f3a621ac..e567aad4774 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter
