public inbox for [email protected]help / color / mirror / Atom feed
[PATCH 1/5] bootstrap: convert Typ to a List* 6+ messages / 4 participants [nested] [flat]
* [PATCH 1/5] bootstrap: convert Typ to a List* @ 2020-11-20 02:48 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Justin Pryzby @ 2020-11-20 02:48 UTC (permalink / raw) --- src/backend/bootstrap/bootstrap.c | 69 ++++++++++++++----------------- 1 file changed, 31 insertions(+), 38 deletions(-) diff --git a/src/backend/bootstrap/bootstrap.c b/src/backend/bootstrap/bootstrap.c index 6f615e6622..18eb62ca47 100644 --- a/src/backend/bootstrap/bootstrap.c +++ b/src/backend/bootstrap/bootstrap.c @@ -159,7 +159,7 @@ struct typmap FormData_pg_type am_typ; }; -static struct typmap **Typ = NULL; +static List *Typ = NIL; /* List of struct typmap* */ static struct typmap *Ap = NULL; static Datum values[MAXATTR]; /* current row's attribute values */ @@ -597,7 +597,7 @@ boot_openrel(char *relname) * pg_type must be filled before any OPEN command is executed, hence we * can now populate the Typ array if we haven't yet. */ - if (Typ == NULL) + if (Typ == NIL) populate_typ_array(); if (boot_reldesc != NULL) @@ -688,7 +688,7 @@ DefineAttr(char *name, char *type, int attnum, int nullness) typeoid = gettype(type); - if (Typ != NULL) + if (Typ != NIL) { attrtypes[attnum]->atttypid = Ap->am_oid; attrtypes[attnum]->attlen = Ap->am_typ.typlen; @@ -877,36 +877,25 @@ populate_typ_array(void) Relation rel; TableScanDesc scan; HeapTuple tup; - int nalloc; - int i; - - Assert(Typ == NULL); - nalloc = 512; - Typ = (struct typmap **) - MemoryContextAlloc(TopMemoryContext, nalloc * sizeof(struct typmap *)); + Assert(Typ == NIL); rel = table_open(TypeRelationId, NoLock); scan = table_beginscan_catalog(rel, 0, NULL); - i = 0; while ((tup = heap_getnext(scan, ForwardScanDirection)) != NULL) { Form_pg_type typForm = (Form_pg_type) GETSTRUCT(tup); + struct typmap *newtyp; + MemoryContext old; - /* make sure there will be room for a trailing NULL pointer */ - if (i >= nalloc - 1) - { - nalloc *= 2; - Typ = (struct typmap **) - repalloc(Typ, nalloc * sizeof(struct typmap *)); - } - Typ[i] = (struct typmap *) - MemoryContextAlloc(TopMemoryContext, sizeof(struct typmap)); - Typ[i]->am_oid = typForm->oid; - memcpy(&(Typ[i]->am_typ), typForm, sizeof(Typ[i]->am_typ)); - i++; + old = MemoryContextSwitchTo(TopMemoryContext); + newtyp = (struct typmap *) palloc(sizeof(struct typmap)); + Typ = lappend(Typ, newtyp); + MemoryContextSwitchTo(old); + + newtyp->am_oid = typForm->oid; + memcpy(&newtyp->am_typ, typForm, sizeof(newtyp->am_typ)); } - Typ[i] = NULL; /* Fill trailing NULL pointer */ table_endscan(scan); table_close(rel, NoLock); } @@ -925,16 +914,17 @@ populate_typ_array(void) static Oid gettype(char *type) { - if (Typ != NULL) + if (Typ != NIL) { - struct typmap **app; + ListCell *lc; - for (app = Typ; *app != NULL; app++) + foreach (lc, Typ) { - if (strncmp(NameStr((*app)->am_typ.typname), type, NAMEDATALEN) == 0) + struct typmap *app = lfirst(lc); + if (strncmp(NameStr(app->am_typ.typname), type, NAMEDATALEN) == 0) { - Ap = *app; - return (*app)->am_oid; + Ap = app; + return app->am_oid; } } } @@ -980,14 +970,17 @@ boot_get_type_io_data(Oid typid, if (Typ != NULL) { /* We have the boot-time contents of pg_type, so use it */ - struct typmap **app; - struct typmap *ap; - - app = Typ; - while (*app && (*app)->am_oid != typid) - ++app; - ap = *app; - if (ap == NULL) + struct typmap *ap = NULL; + ListCell *lc; + + foreach (lc, Typ) + { + ap = lfirst(lc); + if (ap->am_oid == typid) + break; + } + + if (!ap || ap->am_oid != typid) elog(ERROR, "type OID %u not found in Typ list", typid); *typlen = ap->am_typ.typlen; -- 2.26.2 --------------614DDB87AFFED893713AC0E9 Content-Type: text/x-patch; charset=UTF-8; name="0002-Allow-composite-types-in-bootstrap-20210304.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Allow-composite-types-in-bootstrap-20210304.patch" ^ permalink raw reply [nested|flat] 6+ messages in thread
* Make default subscription streaming option as Parallel @ 2024-10-07 05:35 vignesh C <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: vignesh C @ 2024-10-07 05:35 UTC (permalink / raw) To: PostgreSQL Hackers <[email protected]> Hi, By default, currently streaming of in-progress transactions for subscriptions is disabled. All transactions are fully decoded on the publisher before being sent to the subscriber. This approach can lead to increased latency and reduced performance, particularly under heavy load. By default, we could enable the parallel streaming option for subscriptions. By doing this, incoming changes will be directly applied by one of the available parallel apply workers. This method significantly improves the performance of commit operations. I conducted a series of tests using logical replication, comparing SQL execution times with streaming set to both parallel and off. The tests varied the logical_decoding_work_mem setting and included the following scenarios: a) Insert, b) Delete, c) Update, d) rollback 5% records, e) rollback 10% records, f) rollback 20% records, g) rollback 50% records. I have written tap tests for the same, the attached files can be copied to src/test/subscription/t and the logical_decoding_work_mem configuration and streaming option in create subscription command should be changed accordingly before running the tests. The tests were executed 5 times and the average of them was taken. The execution time is in seconds. Insert 5kk records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 37.304 | 69.465 | 46.298 256 KB | 36.327 | 70.671 | 48.597 64 MB | 41.173 | 69.228 | 40.526 Delete 5kk records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 42.322 | 69.404 | 39.021 256 KB | 43.250 | 66.973 | 35.422 64 MB | 44.183 | 67.873 | 34.903 Update 5kk records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 93.953 | 127.691 | 26.422 256 KB | 94.166 | 128.541 | 26.743 64 MB | 93.367 | 134.275 | 30.465 Rollback 05% records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 36.968 | 67.161 | 44.957 256 KB | 38.059 | 68.021 | 44.047 64 MB | 39.431 | 66.878 | 41.041 Rollback 10% records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 35.966 | 63.968 | 43.775 256 KB | 36.597 | 64.836 | 43.554 64 MB | 39.069 | 64.357 | 39.292 Rollback 20% records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 37.616 | 58.903 | 36.139 256 KB | 37.330 | 58.606 | 36.303 64 MB | 38.720 | 60.236 | 35.720 Rollback 50% records Logical Decoding mem | Parallel | off | % Improvement -------------------------------|-------------|---------------|------------------------ 64 KB | 38.999 | 44.776 | 12.902 256 KB | 36.567 | 44.530 | 17.882 64 MB | 38.592 | 45.346 | 14.893 The machine configuration that was used is also attached. The tests demonstrate a significant performance improvement when using the parallel streaming option, insert shows 40-48 %improvement, delete shows 34-39 %improvement, update shows 26-30 %improvement. In the case of rollback the improvement is between 12-44%, the improvement slightly reduces with larger amounts of data being rolled back in this case. If there's a significant amount of data to roll back, the performance of streaming in parallel may be comparable to or slightly lower in some instances. However, this is acceptable since commit operations are generally more frequent than rollback operations. One key point to consider is that the lock on transaction objects will be held for a longer duration when using streaming in parallel. This occurs because the parallel apply worker initiates the transaction as soon as streaming begins, maintaining the lock until the transaction is fully completed. As a result, for long-running transactions, this extended lock can hinder concurrent access that requires a lock. Since there is a significant percentage improvement, we should make the default subscription streaming option parallel. Attached patch has the change for the same. Thoughts? All of these tests were conducted with both the publisher and subscriber on the same host. I will perform additional tests with one of the logical replication nodes on a different host and share the results later. Regards, Vignesh CPU INFO processor : 119 vendor_id : GenuineIntel cpu family : 6 model : 62 model name : Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz stepping : 7 microcode : 0x715 cpu MHz : 1505.957 cache size : 38400 KB physical id : 3 siblings : 30 core id : 14 cpu cores : 15 apicid : 125 initial apicid : 125 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm epb intel_ppin ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5629.54 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: MemTotal: 792237404 kB MemFree: 724051992 kB MemAvailable: 762505368 kB Buffers: 2108 kB Cached: 43885588 kB SwapCached: 0 kB Active: 22276460 kB Inactive: 21761812 kB Active(anon): 1199380 kB Inactive(anon): 4228212 kB Active(file): 21077080 kB Inactive(file): 17533600 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 4194300 kB SwapFree: 4194300 kB Dirty: 0 kB Writeback: 0 kB AnonPages: 150796 kB Mapped: 5283248 kB Shmem: 5277044 kB Slab: 1472084 kB SReclaimable: 1165144 kB SUnreclaim: 306940 kB KernelStack: 17504 kB PageTables: 21540 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 400313000 kB Committed_AS: 86287072 kB VmallocTotal: 34359738367 kB VmallocUsed: 1942092 kB VmallocChunk: 33753397244 kB Percpu: 36352 kB HardwareCorrupted: 0 kB AnonHugePages: 81920 kB CmaTotal: 0 kB CmaFree: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 785756 kB DirectMap2M: 7432192 kB DirectMap1G: 799014912 kB Attachments: [text/plain] cpu_info.txt (1.2K, ../../CALDaNm1=MedhW23NuoePJTmonwsMSp80ddsw+sEJs0GUMC_kqQ@mail.gmail.com/2-cpu_info.txt) download | inline: CPU INFO processor : 119 vendor_id : GenuineIntel cpu family : 6 model : 62 model name : Intel(R) Xeon(R) CPU E7-4890 v2 @ 2.80GHz stepping : 7 microcode : 0x715 cpu MHz : 1505.957 cache size : 38400 KB physical id : 3 siblings : 30 core id : 14 cpu cores : 15 apicid : 125 initial apicid : 125 fpu : yes fpu_exception : yes cpuid level : 13 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm epb intel_ppin ssbd ibrs ibpb stibp tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms xsaveopt dtherm ida arat pln pts md_clear spec_ctrl intel_stibp flush_l1d bogomips : 5629.54 clflush size : 64 cache_alignment : 64 address sizes : 46 bits physical, 48 bits virtual power management: [application/octet-stream] v1-0001-Make-default-value-for-susbcription-streaming-opt.patch (20.9K, ../../CALDaNm1=MedhW23NuoePJTmonwsMSp80ddsw+sEJs0GUMC_kqQ@mail.gmail.com/3-v1-0001-Make-default-value-for-susbcription-streaming-opt.patch) download | inline diff: From e40ad2f41391add02e605979a600f8459956ee86 Mon Sep 17 00:00:00 2001 From: Vignesh C <[email protected]> Date: Wed, 25 Sep 2024 14:42:20 +0530 Subject: [PATCH v1] Make default value for susbcription streaming option to parallel. Make default value for susbcription streaming option to parallel. --- doc/src/sgml/ref/create_subscription.sgml | 19 ++++++++--------- src/backend/commands/subscriptioncmds.c | 2 +- src/bin/pg_dump/pg_dump.c | 2 ++ src/bin/pg_dump/t/002_pg_dump.pl | 6 +++--- src/test/regress/expected/subscription.out | 24 +++++++++++----------- 5 files changed, 27 insertions(+), 26 deletions(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 740b7d9421..2f9b769de2 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -271,9 +271,13 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <listitem> <para> Specifies whether to enable streaming of in-progress transactions - for this subscription. The default value is <literal>off</literal>, - meaning all transactions are fully decoded on the publisher and only - then sent to the subscriber as a whole. + for this subscription. The default value is <literal>parallel</literal>, + meaning incoming changes are directly applied via one of the parallel + apply workers, if available. If no parallel apply worker is free to + handle streaming transactions then the changes are written to + temporary files and applied after the transaction is committed. Note + that if an error happens in a parallel apply worker, the finish LSN + of the remote transaction might not be reported in the server log. </para> <para> @@ -283,13 +287,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl </para> <para> - If set to <literal>parallel</literal>, incoming changes are directly - applied via one of the parallel apply workers, if available. If no - parallel apply worker is free to handle streaming transactions then - the changes are written to temporary files and applied after the - transaction is committed. Note that if an error happens in a - parallel apply worker, the finish LSN of the remote transaction - might not be reported in the server log. + If set to <literal>off</literal>, all transactions are fully decoded + on the publisher and only then sent to the subscriber as a whole. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 02ccc636b8..0a7a618855 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -151,7 +151,7 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, if (IsSet(supported_opts, SUBOPT_BINARY)) opts->binary = false; if (IsSet(supported_opts, SUBOPT_STREAMING)) - opts->streaming = LOGICALREP_STREAM_OFF; + opts->streaming = LOGICALREP_STREAM_PARALLEL; if (IsSet(supported_opts, SUBOPT_TWOPHASE_COMMIT)) opts->twophase = false; if (IsSet(supported_opts, SUBOPT_DISABLE_ON_ERR)) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 130b80775d..047b914a25 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5232,6 +5232,8 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo) appendPQExpBufferStr(query, ", streaming = on"); else if (strcmp(subinfo->substream, "p") == 0) appendPQExpBufferStr(query, ", streaming = parallel"); + else + appendPQExpBufferStr(query, ", streaming = off"); if (strcmp(subinfo->subtwophasestate, two_phase_disabled) != 0) appendPQExpBufferStr(query, ", two_phase = on"); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index ab6c830491..7aec965cc8 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2992,7 +2992,7 @@ my %tests = ( CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 WITH (connect = false);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1');\E + \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1', streaming = parallel);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3003,7 +3003,7 @@ my %tests = ( CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 WITH (connect = false, origin = none);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', origin = none);\E + \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', streaming = parallel, origin = none);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3014,7 +3014,7 @@ my %tests = ( CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 WITH (connect = false, origin = any);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3');\E + \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3', streaming = parallel);\E /xm, like => { %full_runs, section_post_data => 1, }, }, diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 17d48b1685..1443e1d929 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -119,7 +119,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); @@ -127,7 +127,7 @@ ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub3; @@ -148,7 +148,7 @@ ERROR: invalid connection string syntax: missing "=" after "foobar" in connecti List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); @@ -160,7 +160,7 @@ ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (password_required = true); @@ -179,7 +179,7 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 (1 row) -- ok - with lsn = NONE @@ -191,7 +191,7 @@ ERROR: invalid WAL location (LSN): 0/0 List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) BEGIN; @@ -226,7 +226,7 @@ HINT: Available values: local, remote_write, remote_apply, on, off. List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 + regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 (1 row) -- rename back to keep the rest simple @@ -258,7 +258,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (binary = false); @@ -267,7 +267,7 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub; @@ -374,7 +374,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) -- we can alter streaming when two_phase enabled @@ -412,7 +412,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); @@ -420,7 +420,7 @@ ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); -- 2.34.1 [text/plain] memory_info.txt (1.3K, ../../CALDaNm1=MedhW23NuoePJTmonwsMSp80ddsw+sEJs0GUMC_kqQ@mail.gmail.com/4-memory_info.txt) download | inline: MemTotal: 792237404 kB MemFree: 724051992 kB MemAvailable: 762505368 kB Buffers: 2108 kB Cached: 43885588 kB SwapCached: 0 kB Active: 22276460 kB Inactive: 21761812 kB Active(anon): 1199380 kB Inactive(anon): 4228212 kB Active(file): 21077080 kB Inactive(file): 17533600 kB Unevictable: 0 kB Mlocked: 0 kB SwapTotal: 4194300 kB SwapFree: 4194300 kB Dirty: 0 kB Writeback: 0 kB AnonPages: 150796 kB Mapped: 5283248 kB Shmem: 5277044 kB Slab: 1472084 kB SReclaimable: 1165144 kB SUnreclaim: 306940 kB KernelStack: 17504 kB PageTables: 21540 kB NFS_Unstable: 0 kB Bounce: 0 kB WritebackTmp: 0 kB CommitLimit: 400313000 kB Committed_AS: 86287072 kB VmallocTotal: 34359738367 kB VmallocUsed: 1942092 kB VmallocChunk: 33753397244 kB Percpu: 36352 kB HardwareCorrupted: 0 kB AnonHugePages: 81920 kB CmaTotal: 0 kB CmaFree: 0 kB HugePages_Total: 0 HugePages_Free: 0 HugePages_Rsvd: 0 HugePages_Surp: 0 Hugepagesize: 2048 kB DirectMap4k: 785756 kB DirectMap2M: 7432192 kB DirectMap1G: 799014912 kB [application/x-zip-compressed] test_tap_test_scripts.zip (8.6K, ../../CALDaNm1=MedhW23NuoePJTmonwsMSp80ddsw+sEJs0GUMC_kqQ@mail.gmail.com/5-test_tap_test_scripts.zip) download ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Make default subscription streaming option as Parallel @ 2024-10-21 09:06 Amit Kapila <[email protected]> parent: vignesh C <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Amit Kapila @ 2024-10-21 09:06 UTC (permalink / raw) To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Oct 7, 2024 at 11:05 AM vignesh C <[email protected]> wrote: > > The tests demonstrate a significant performance improvement when using > the parallel streaming option, insert shows 40-48 %improvement, delete > shows 34-39 %improvement, update shows 26-30 %improvement. In the case > of rollback the improvement is between 12-44%, the improvement > slightly reduces with larger amounts of data being rolled back in this > case. If there's a significant amount of data to roll back, the > performance of streaming in parallel may be comparable to or slightly > lower in some instances. However, this is acceptable since commit > operations are generally more frequent than rollback operations. > > One key point to consider is that the lock on transaction objects will > be held for a longer duration when using streaming in parallel. This > occurs because the parallel apply worker initiates the transaction as > soon as streaming begins, maintaining the lock until the transaction > is fully completed. As a result, for long-running transactions, this > extended lock can hinder concurrent access that requires a lock. > The longer-running transactions will anyway have a risk of deadlocks or longer waits if there are concurrent operations on the subscribers. However, with parallel apply, there is a risk of deadlock among the leader and parallel workers when the schema in publisher and subscriber is different. Say the subscriber has a unique constraint that the publisher doesn't have. See the comments in this regard atop applyparallelworker.c in the "Locking Considerations" section. Having said that, the apply workers will detect deadlock in such cases and will retry to apply the errored-out transaction. So, there is a self-healing in-built mechanism and in such cases, we anyway have a risk of UNIQUE_KEY conflict ERRORs which in most cases would require manual intervention. > Since there is a significant percentage improvement, we should make > the default subscription streaming option parallel. > This makes sense to me. I think it would be better to add a Note or Warning in the docs for the risk of deadlock when the schema of publisher and subscriber is not the same even though such cases should be less. -- With Regards, Amit Kapila. ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Make default subscription streaming option as Parallel @ 2024-10-21 15:09 vignesh C <[email protected]> parent: Amit Kapila <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: vignesh C @ 2024-10-21 15:09 UTC (permalink / raw) To: Amit Kapila <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, 21 Oct 2024 at 14:36, Amit Kapila <[email protected]> wrote: > > On Mon, Oct 7, 2024 at 11:05 AM vignesh C <[email protected]> wrote: > > > > The tests demonstrate a significant performance improvement when using > > the parallel streaming option, insert shows 40-48 %improvement, delete > > shows 34-39 %improvement, update shows 26-30 %improvement. In the case > > of rollback the improvement is between 12-44%, the improvement > > slightly reduces with larger amounts of data being rolled back in this > > case. If there's a significant amount of data to roll back, the > > performance of streaming in parallel may be comparable to or slightly > > lower in some instances. However, this is acceptable since commit > > operations are generally more frequent than rollback operations. > > > > One key point to consider is that the lock on transaction objects will > > be held for a longer duration when using streaming in parallel. This > > occurs because the parallel apply worker initiates the transaction as > > soon as streaming begins, maintaining the lock until the transaction > > is fully completed. As a result, for long-running transactions, this > > extended lock can hinder concurrent access that requires a lock. > > > > The longer-running transactions will anyway have a risk of deadlocks > or longer waits if there are concurrent operations on the subscribers. > However, with parallel apply, there is a risk of deadlock among the > leader and parallel workers when the schema in publisher and > subscriber is different. Say the subscriber has a unique constraint > that the publisher doesn't have. See the comments in this regard atop > applyparallelworker.c in the "Locking Considerations" section. Having > said that, the apply workers will detect deadlock in such cases and > will retry to apply the errored-out transaction. So, there is a > self-healing in-built mechanism and in such cases, we anyway have a > risk of UNIQUE_KEY conflict ERRORs which in most cases would require > manual intervention. > > > Since there is a significant percentage improvement, we should make > > the default subscription streaming option parallel. > > > > This makes sense to me. I think it would be better to add a Note or > Warning in the docs for the risk of deadlock when the schema of > publisher and subscriber is not the same even though such cases should > be less. Yes this can happen like scenarios below(with deadlock_timeout = 10ms): Publisher: CREATE TABLE t1(c1 int); create publication pub1 for table t1; Subscriber has an addition index on the table: CREATE TABLE t1(c1 int); CREATE UNIQUE INDEX idx1 on t1(c1); Create subscription ...; Publisher: Session1: Begin; INSERT INTO t1 SELECT i FROM generate_series(1, 5000) s(i); Session2: -- Insert the record that is already inserted in session1 INSERT INTO t1 value(1); Session1: Commit; In this case a deadlock will occur. Attached v3 version patch has a caution added for the same. Regards, Vignesh Attachments: [text/x-patch] v3-0001-Make-default-value-for-susbcription-streaming-opt.patch (21.9K, ../../CALDaNm1Wz91NZwMUgypuMNi9BQFVLT4Dg1TtO4zHo3nR+4ZRZA@mail.gmail.com/2-v3-0001-Make-default-value-for-susbcription-streaming-opt.patch) download | inline diff: From a56f565689b8b18573acd873a097a213af6c6722 Mon Sep 17 00:00:00 2001 From: Vignesh C <[email protected]> Date: Wed, 25 Sep 2024 14:42:20 +0530 Subject: [PATCH v3] Make default value for susbcription streaming option to parallel. Currently default value of streaming option is set to false. All transactions are fully decoded on the publisher before being sent to the subscriber. This approach can leads reduced performance, particularly under heavy load. Changing default streaming option to parallel, by doing this, incoming changes will be directly applied by one of the available parallel apply workers. This method significantly improves the performance of commit operations. --- doc/src/sgml/ref/create_subscription.sgml | 27 ++++++++++++++-------- src/backend/commands/subscriptioncmds.c | 2 +- src/bin/pg_dump/pg_dump.c | 2 ++ src/bin/pg_dump/t/002_pg_dump.pl | 10 ++++---- src/test/regress/expected/subscription.out | 24 +++++++++---------- 5 files changed, 37 insertions(+), 28 deletions(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 8a3096e62b..3eab06bd2d 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -271,11 +271,23 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <listitem> <para> Specifies whether to enable streaming of in-progress transactions - for this subscription. The default value is <literal>off</literal>, - meaning all transactions are fully decoded on the publisher and only - then sent to the subscriber as a whole. + for this subscription. The default value is <literal>parallel</literal>, + meaning incoming changes are directly applied via one of the parallel + apply workers, if available. If no parallel apply worker is free to + handle streaming transactions then the changes are written to + temporary files and applied after the transaction is committed. Note + that if an error happens in a parallel apply worker, the finish LSN + of the remote transaction might not be reported in the server log. </para> + <caution> + <para> + There is a risk of deadlock when the schemas of the publisher and + subscriber differ, although such cases are rare. The apply worker + is equipped to automatically retry these transactions. + </para> + </caution> + <para> If set to <literal>on</literal>, the incoming changes are written to temporary files and then applied only after the transaction is @@ -283,13 +295,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl </para> <para> - If set to <literal>parallel</literal>, incoming changes are directly - applied via one of the parallel apply workers, if available. If no - parallel apply worker is free to handle streaming transactions then - the changes are written to temporary files and applied after the - transaction is committed. Note that if an error happens in a - parallel apply worker, the finish LSN of the remote transaction - might not be reported in the server log. + If set to <literal>off</literal>, all transactions are fully decoded + on the publisher and only then sent to the subscriber as a whole. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 02ccc636b8..0a7a618855 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -151,7 +151,7 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, if (IsSet(supported_opts, SUBOPT_BINARY)) opts->binary = false; if (IsSet(supported_opts, SUBOPT_STREAMING)) - opts->streaming = LOGICALREP_STREAM_OFF; + opts->streaming = LOGICALREP_STREAM_PARALLEL; if (IsSet(supported_opts, SUBOPT_TWOPHASE_COMMIT)) opts->twophase = false; if (IsSet(supported_opts, SUBOPT_DISABLE_ON_ERR)) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 1d79865058..19e3d24326 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5248,6 +5248,8 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo) appendPQExpBufferStr(query, ", streaming = on"); else if (strcmp(subinfo->substream, "p") == 0) appendPQExpBufferStr(query, ", streaming = parallel"); + else + appendPQExpBufferStr(query, ", streaming = off"); if (strcmp(subinfo->subtwophasestate, two_phase_disabled) != 0) appendPQExpBufferStr(query, ", two_phase = on"); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 91a4c63744..213904440f 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3002,7 +3002,7 @@ my %tests = ( CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 WITH (connect = false);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1');\E + \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1', streaming = parallel);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3011,9 +3011,9 @@ my %tests = ( create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub2 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 - WITH (connect = false, origin = none);', + WITH (connect = false, origin = none, streaming = off);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', origin = none);\E + \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', streaming = off, origin = none);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3022,9 +3022,9 @@ my %tests = ( create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub3 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 - WITH (connect = false, origin = any);', + WITH (connect = false, origin = any, streaming = on);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3');\E + \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3', streaming = on);\E /xm, like => { %full_runs, section_post_data => 1, }, }, diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 17d48b1685..1443e1d929 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -119,7 +119,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); @@ -127,7 +127,7 @@ ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub3; @@ -148,7 +148,7 @@ ERROR: invalid connection string syntax: missing "=" after "foobar" in connecti List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); @@ -160,7 +160,7 @@ ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (password_required = true); @@ -179,7 +179,7 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 (1 row) -- ok - with lsn = NONE @@ -191,7 +191,7 @@ ERROR: invalid WAL location (LSN): 0/0 List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) BEGIN; @@ -226,7 +226,7 @@ HINT: Available values: local, remote_write, remote_apply, on, off. List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 + regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 (1 row) -- rename back to keep the rest simple @@ -258,7 +258,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (binary = false); @@ -267,7 +267,7 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub; @@ -374,7 +374,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) -- we can alter streaming when two_phase enabled @@ -412,7 +412,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); @@ -420,7 +420,7 @@ ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); -- 2.34.1 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Make default subscription streaming option as Parallel @ 2024-10-22 09:16 Amit Kapila <[email protected]> parent: vignesh C <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Amit Kapila @ 2024-10-22 09:16 UTC (permalink / raw) To: vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> On Mon, Oct 21, 2024 at 8:40 PM vignesh C <[email protected]> wrote: > > Attached v3 version patch has a caution added for the same. > Thanks, the patch looks good to me and I am planning to commit this early next week unless there are objections or any major problems. I have slightly updated the docs and commit message. Few more points to consider: 1. Please ensure that none of the existing tests that use subscriptions with large changes will be impacted due to this change. 2. The pg_createsubscriber utility uses CREATE SUBSCRIPTION statement and after this change, it will enable parallel mode by default which I think is a good idea as users won't need to do that manually after running the tool. Do you see any problem with this? -- With Regards, Amit Kapila. Attachments: [application/octet-stream] v4-0001-Change-the-default-value-of-the-streaming-option-.patch (22.1K, ../../CAA4eK1KoF9qNU4r7UOYv+asisi0eFNqTX8EqX9ba_-Oo5gWt2w@mail.gmail.com/2-v4-0001-Change-the-default-value-of-the-streaming-option-.patch) download | inline diff: From 1659ee78ce6acba3e1dbc7516807fa6f3427a3e8 Mon Sep 17 00:00:00 2001 From: Vignesh C <[email protected]> Date: Wed, 25 Sep 2024 14:42:20 +0530 Subject: [PATCH v5] Change the default value of the streaming option to 'parallel'. Previously the default value of this option was 'off'. The parallel option indicates that the changes in large transactions (greater than logical_decoding_work_mem) are to be applied directly via one of the parallel apply workers, if available. The parallel mode was introduced in 16, but we refrain from enabling it by default to avoid seeing any unpleasant behavior in the existing applications. However we haven't found any such report yet, so this is a good time to enable it by default. Reported-by: Vignesh C Author: Hayato Kuroda, Amit Kapila Discussion: https://postgr.es/m/CALDaNm1=MedhW23NuoePJTmonwsMSp80ddsw+sEJs0GUMC_kqQ@mail.gmail.com --- doc/src/sgml/ref/create_subscription.sgml | 27 ++++++++++++++-------- src/backend/commands/subscriptioncmds.c | 2 +- src/bin/pg_dump/pg_dump.c | 2 ++ src/bin/pg_dump/t/002_pg_dump.pl | 10 ++++---- src/test/regress/expected/subscription.out | 24 +++++++++---------- 5 files changed, 37 insertions(+), 28 deletions(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 8a3096e62b..6cf7d4f9a1 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -271,11 +271,23 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl <listitem> <para> Specifies whether to enable streaming of in-progress transactions - for this subscription. The default value is <literal>off</literal>, - meaning all transactions are fully decoded on the publisher and only - then sent to the subscriber as a whole. + for this subscription. The default value is <literal>parallel</literal>, + meaning incoming changes are directly applied via one of the parallel + apply workers, if available. If no parallel apply worker is free to + handle streaming transactions then the changes are written to + temporary files and applied after the transaction is committed. Note + that if an error happens in a parallel apply worker, the finish LSN + of the remote transaction might not be reported in the server log. </para> + <caution> + <para> + There is a risk of deadlock when the schemas of the publisher and + subscriber differ, although such cases are rare. The apply worker + is equipped to retry these transactions automatically. + </para> + </caution> + <para> If set to <literal>on</literal>, the incoming changes are written to temporary files and then applied only after the transaction is @@ -283,13 +295,8 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl </para> <para> - If set to <literal>parallel</literal>, incoming changes are directly - applied via one of the parallel apply workers, if available. If no - parallel apply worker is free to handle streaming transactions then - the changes are written to temporary files and applied after the - transaction is committed. Note that if an error happens in a - parallel apply worker, the finish LSN of the remote transaction - might not be reported in the server log. + If set to <literal>off</literal>, all transactions are fully decoded + on the publisher and only then sent to the subscriber as a whole. </para> </listitem> </varlistentry> diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 02ccc636b8..0a7a618855 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -151,7 +151,7 @@ parse_subscription_options(ParseState *pstate, List *stmt_options, if (IsSet(supported_opts, SUBOPT_BINARY)) opts->binary = false; if (IsSet(supported_opts, SUBOPT_STREAMING)) - opts->streaming = LOGICALREP_STREAM_OFF; + opts->streaming = LOGICALREP_STREAM_PARALLEL; if (IsSet(supported_opts, SUBOPT_TWOPHASE_COMMIT)) opts->twophase = false; if (IsSet(supported_opts, SUBOPT_DISABLE_ON_ERR)) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 1b47c388ce..d8c6330732 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -5235,6 +5235,8 @@ dumpSubscription(Archive *fout, const SubscriptionInfo *subinfo) appendPQExpBufferStr(query, ", streaming = on"); else if (strcmp(subinfo->substream, "p") == 0) appendPQExpBufferStr(query, ", streaming = parallel"); + else + appendPQExpBufferStr(query, ", streaming = off"); if (strcmp(subinfo->subtwophasestate, two_phase_disabled) != 0) appendPQExpBufferStr(query, ", two_phase = on"); diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index ab6c830491..ac60829d68 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2992,7 +2992,7 @@ my %tests = ( CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 WITH (connect = false);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1');\E + \QCREATE SUBSCRIPTION sub1 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub1', streaming = parallel);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3001,9 +3001,9 @@ my %tests = ( create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub2 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 - WITH (connect = false, origin = none);', + WITH (connect = false, origin = none, streaming = off);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', origin = none);\E + \QCREATE SUBSCRIPTION sub2 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub2', streaming = off, origin = none);\E /xm, like => { %full_runs, section_post_data => 1, }, }, @@ -3012,9 +3012,9 @@ my %tests = ( create_order => 50, create_sql => 'CREATE SUBSCRIPTION sub3 CONNECTION \'dbname=doesnotexist\' PUBLICATION pub1 - WITH (connect = false, origin = any);', + WITH (connect = false, origin = any, streaming = on);', regexp => qr/^ - \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3');\E + \QCREATE SUBSCRIPTION sub3 CONNECTION 'dbname=doesnotexist' PUBLICATION pub1 WITH (connect = false, slot_name = 'sub3', streaming = on);\E /xm, like => { %full_runs, section_post_data => 1, }, }, diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out index 17d48b1685..1443e1d929 100644 --- a/src/test/regress/expected/subscription.out +++ b/src/test/regress/expected/subscription.out @@ -119,7 +119,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); @@ -127,7 +127,7 @@ ALTER SUBSCRIPTION regress_testsub4 SET (origin = any); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub4 | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub3; @@ -148,7 +148,7 @@ ERROR: invalid connection string syntax: missing "=" after "foobar" in connecti List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false); @@ -160,7 +160,7 @@ ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (password_required = true); @@ -179,7 +179,7 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345'); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/12345 (1 row) -- ok - with lsn = NONE @@ -191,7 +191,7 @@ ERROR: invalid WAL location (LSN): 0/0 List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 + regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist2 | 0/0 (1 row) BEGIN; @@ -226,7 +226,7 @@ HINT: Available values: local, remote_write, remote_apply, on, off. List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN ---------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+------------------------------+---------- - regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | off | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 + regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | local | dbname=regress_doesnotexist2 | 0/0 (1 row) -- rename back to keep the rest simple @@ -258,7 +258,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | t | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (binary = false); @@ -267,7 +267,7 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) DROP SUBSCRIPTION regress_testsub; @@ -374,7 +374,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) -- we can alter streaming when two_phase enabled @@ -412,7 +412,7 @@ HINT: To initiate replication, you must manually create the replication slot, e List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); @@ -420,7 +420,7 @@ ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true); List of subscriptions Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Synchronous commit | Conninfo | Skip LSN -----------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------------------+-----------------------------+---------- - regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 + regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | off | dbname=regress_doesnotexist | 0/0 (1 row) ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE); -- 2.28.0.windows.1 ^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Make default subscription streaming option as Parallel @ 2024-10-22 10:54 Hayato Kuroda (Fujitsu) <[email protected]> parent: Amit Kapila <[email protected]> 0 siblings, 0 replies; 6+ messages in thread From: Hayato Kuroda (Fujitsu) @ 2024-10-22 10:54 UTC (permalink / raw) To: 'Amit Kapila' <[email protected]>; vignesh C <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]> Dear Amit, Vignesh, > 1. Please ensure that none of the existing tests that use > subscriptions with large changes will be impacted due to this change. I found at least 022_twophase_cascade.pl should be fixed. The file has a part which tests non-streaming case: ``` # ----------------------- # 2PC NON-STREAMING TESTS # ----------------------- ... $node_B->safe_psql( 'postgres', " CREATE SUBSCRIPTION tap_sub_B CONNECTION '$node_A_connstr application_name=$appname_B' PUBLICATION tap_pub_A WITH (two_phase = on)"); ... ``` I know the streaming actually does not happen because few tuples will be inserted later, but creating as streaming=parallel is bit misleading. I checked other files as well but I couldn't find what we should fix. > 2. The pg_createsubscriber utility uses CREATE SUBSCRIPTION statement > and after this change, it will enable parallel mode by default which I > think is a good idea as users won't need to do that manually after > running the tool. Do you see any problem with this? I also think it is okay. IIUC, there were no specific reasons to create subscriptions with streaming=off, it was chosen because it was a default. I cannot find strong reasons to keep current behavior. Best regards, Hayato Kuroda FUJITSU LIMITED ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-10-22 10:54 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-11-20 02:48 [PATCH 1/5] bootstrap: convert Typ to a List* Justin Pryzby <[email protected]> 2024-10-07 05:35 Make default subscription streaming option as Parallel vignesh C <[email protected]> 2024-10-21 09:06 ` Re: Make default subscription streaming option as Parallel Amit Kapila <[email protected]> 2024-10-21 15:09 ` Re: Make default subscription streaming option as Parallel vignesh C <[email protected]> 2024-10-22 09:16 ` Re: Make default subscription streaming option as Parallel Amit Kapila <[email protected]> 2024-10-22 10:54 ` RE: Make default subscription streaming option as Parallel Hayato Kuroda (Fujitsu) <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox