Tracking notnull attributes inside Var
notnulls discussion is forked from UniqueKey stuff, you can see the
attachment
for the UnqiueKey introduction. Tom raised his opinion to track the
nullability
inside Var[1][2][3], this thread would start from there based on my
understanding.
Generally tracking the null attributes inside Var would have something like:
struct Var
{
...;
int nullable; // -1 unknown, 0 - not nullable. 1 - nullable
};
and then semantics of Var->nullable must be attached to a RelOptInfo. For
example:
CREATE TABLE t1(a int, b int);
SELECT abs(a) FROM t1 WHERE a > -100;
The var in RelOptInfo->reltarget should have nullable = 0 but the var in
RelOptInfo->baserestrictinfo should have nullable = 1; The beauty of this
are: a). It can distinguish the two situations perfectly b). Whenever we
want
to know the nullable attribute of a Var for an expression, it is super easy
to
know. In summary, we need to maintain the nullable attribute at 2 different
places. one is the before the filters are executed(baserestrictinfo,
joininfo,
ec_list at least). one is after the filters are executed
(RelOptInfo.reltarget
only?)
Come to JoinRel, we still need to maintain the 2 different cases as well.
As for the joinrel.reltarget, currently it looks up the inputrel's
reltarget to
get the Var, so it is easy to inherit from Var->nullable from inputrel, but
we need to consider the new changes introduced by current join,
Like new NOT nullable attributes because of join clauses OR new nullable
attributes because of outer join. Everything looks good for now.
The hard part is RelOptInfo.joininfo & root->eq_classes. All of them uses
the shared RestrictInfo, and it is unclear which Var->nullable should be
used in
them. To not provide a wrong answer, I think we can assume nullable=-1
(unknown)
and let the upper layer decides what to do (do we have known use cases to
use
the nullable attribute here?).
More considerations about this strategy:
1. We might use more memory for different var copies, the only known cases
RelOptInfo->reltarget for now.
2. _equalVar() has more complex semantics: shall we consider nulls or not.
My recent experience reminds me of another interesting use case of UniqueKey
which may reduce the planning time a lot IIUC (Value 3 in then attachment).
Since
PG15 has just been released, I wonder if more people have time to discuss
this topic
again. Do I think the way in the right direction?
[1] https://www.postgresql.org/message-id/1551312.1613142245%40sss.pgh.pa.us
[2]
https://www.postgresql.org/message-id/CAApHDvrRwhWCPKUD5H-EQoezHf%3DfnUUsPgTAnXsEOV8f8SF7XQ%40mail.gmail.com
[3] https://www.postgresql.org/message-id/1664320.1625577290%40sss.pgh.pa.us
--
Best Regards
Andy Fan
uniquekey.README
Description: Binary data
Avoid unecessary MemSet call (src/backend/utils/cache/relcache.c)
Hi hackers, At function load_relcache_init_file, there is an unnecessary function call, to initialize pgstat_info pointer to NULL. MemSet(>pgstat_info, 0, sizeof(rel->pgstat_info)); I think that intention with use of MemSet was: MemSet(>pgstat_info, 0, sizeof(*rel->pgstat_info)); Initialize with sizeof of Struct size, not with sizeof pointer size. But so it breaks. Attached a tiny patch. regards, Ranier Vilela avoid_unecessary_memset_call.patch Description: Binary data
Re: PostgreSQL 15 Beta 1 release announcement draft
On Sat, May 14, 2022 at 02:52:35PM -0400, Jonathan S. Katz wrote: > PostgreSQL 15 is made generally available, thouh some details of the release > can though > a SQL standard command for conditionally perform write operations (`INSERT`, performing > he [`range_agg`](https://www.postgresql.org/docs/15/functions-aggregate.html) The > PostgreSQL system and > [TOAST](https://www.postgresql.org/docs/15/storage-toast.html) > tables, used for storing data that is larger than a single page (8kB), can now > utilize > [index > deduplication](https://www.postgresql.org/docs/15/btree-implementation.html#BTREE-DEDUPLICATION) > and benefit from smaller indexes and faster lookups. IMO this doesn't need to be listed. > `pg_basebackup` client can now also decompress backups that use LZ4 an > Zstandard and > Write-ahead log (WAL) files can now be compressed using both LZ4 an Zstandard and > configuration parameter. Additionally, PostgreSQL 15 also adds the > [`recovery_prefetch`](https://www.postgresql.org/docs/15/runtime-config-wal.html#GUC-RECOVERY-PREFETCH) remove "the" or add "option" ? > PostgreSQL 15 makes it possible to skip applying changes using the > [`ALTER SUBSCRIPTION ... > SKIP`](https://www.postgresql.org/docs/15/sql-altersubscription.html). add "command". > PostgreSQL 15 introduces the > [`jsonlog` format for > logging](https://www.postgresql.org/docs/15/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-JSONLOG). > This allows PostgreSQL logs to be consumed by many programs > that perform structured logging aggregation and analysis. PostgreSQL 15 now by log aggregation? > default logs checkpoints and slow autovacuum operations. > PostgreSQL 15 adds support for > "[security invoker > views](https://www.postgresql.org/docs/15/sql-createview.html)", > which users the privileges of the user executing the query instead of the user uses
PostgreSQL 15 Beta 1 release announcement draft
Hi, Attached is a draft of the release announcement for the PostgreSQL 15 Beta 1 release. The goal of this announcement is to raise awareness around many of the new features appearing in PostgreSQL 15 and to encourage people to test. The success of the PostgreSQL 15 GA depends heavily on people testing during the Beta period! Please review this announcement for feature description accuracy or if there is something omitted that should be highlighted. Note that we cannot highlight everything that is coming in PostgreSQL 15 (that is why we have the release notes), but are aiming to showcase features that are impactful and inspirational. Please provide feedback no later than 2022-05-19 0:00 AoE[1]. Thanks, Jonathan [1] https://en.wikipedia.org/wiki/Anywhere_on_Earth The PostgreSQL Global Development Group announces that the first beta release of PostgreSQL 15 is now [available for download](https://www.postgresql.org/download/). This release contains previews of all features that will be available when PostgreSQL 15 is made generally available, thouh some details of the release can change during the beta period. You can find information about all of the features and changes found in PostgreSQL 15 in the [release notes](https://www.postgresql.org/docs/15/release-15.html): [https://www.postgresql.org/docs/15/release-15.html](https://www.postgresql.org/docs/15/release-15.html) In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 15 in your systems to help us eliminate bugs or other issues that may exist. While we do not advise you to run PostgreSQL 15 Beta 1 in your production environments, we encourage you to find ways to run your typical application workloads against this beta release. Your testing and feedback will help the community ensure that the PostgreSQL 15 release upholds our standards of delivering a stable, reliable release of the world's most advanced open source relational database. Please read more about our [beta testing process](https://www.postgresql.org/developer/beta/) and how you can contribute: [https://www.postgresql.org/developer/beta/](https://www.postgresql.org/developer/beta/) PostgreSQL 15 Feature Highlights ### Developer Experience PostgreSQL 15 adds new features for simplifying and enhancing the developer experience. This release introduces [`MERGE`](https://www.postgresql.org/docs/15/sql-merge.html), a SQL standard command for conditionally perform write operations (`INSERT`, `UPDATE`, or `DELETE`) on data. Prior to this release, the same behavior could be accomplished either using stored procedures or, on a limited-basis, with [`INSERT ... ON CONFLICT`](https://www.postgresql.org/docs/15/sql-insert.html). With PostgreSQL 15, developers can write simple, expressive queries to choose the appropriate data modification action to take. PostgreSQL added support for JSON in 2012 as part of the [9.2 release](https://www.postgresql.org/about/news/postgresql-92-released-1415/). The SQL/JSON standard, published five years later, specified a variety of interfaces for accessing and manipulating JSON data stored in relational databases. PostgreSQL 15 builds on its existing support for the SQL/JSON path language by including more standard [SQL/JSON functions](https://www.postgresql.org/docs/15/functions-json.html#FUNCTIONS-SQLJSON). These include [SQL/JSON constructors](https://www.postgresql.org/docs/15/functions-json.html#FUNCTIONS-SQLJSON-PRODUCING), [query / introspection functions](FUNCTIONS-SQLJSON-QUERYING), and the ability to [convert JSON data into a table](https://www.postgresql.org/docs/15/functions-json.html#FUNCTIONS-JSONTABLE). PostgreSQL 15 adds [more regular expression functions](https://www.postgresql.org/docs/15/functions-matching.html#FUNCTIONS-POSIX-REGEXP), including `regexp_count` , `regexp_instr`, `regexp_like`, and `regexp_substr`. he [`range_agg`](https://www.postgresql.org/docs/15/functions-aggregate.html) function, introduced in PostgreSQL 15 for aggregating [`range` data types](https://www.postgresql.org/docs/15/rangetypes.html) into `multirange` types, now supports aggregating `multirange` types too. ### Performance PostgreSQL 15 continues to build on its performance gains over the past several releases. This release includes a significant speedup for sorting data when sorting over larger data sets. In particular, these are data sets that exceed the `work_mem` parameter. Early benchmarks show that these sorts may see on average an 2x speedup for these workloads on PostgreSQL 15. The performance gains of PostgreSQL 15 extend to a variety of query types. This includes the introduction of parallelization for [`SELECT DISTINCT`](https://www.postgresql.org/docs/15/queries-select-lists.html#QUERIES-DISTINCT) statements and improvements in performance to [window functions](https://www.postgresql.org/docs/15/functions-window.html)
Re: First draft of the PG 15 release notes
On Sat, May 14, 2022 at 10:22:10AM +0530, Amit Kapila wrote: > > I see the point now --- new item: > > > > > > > > > > > > Prevent logical replication of empty transactions (Ajin Cherian, > > Hou Zhijie, Euler Taveira) > > > > > > > > Previously, write transactions would send empty transactions to > > subscribers if subscribed tables were not modified. > > > > > > > > Thanks! I will admit I had a little trouble with the wording of this item. :-) -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: Intermittent buildfarm failures on wrasse
On 2022-Apr-20, Masahiko Sawada wrote:
> > MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) |
> > (proc->statusFlags & PROC_XMIN_FLAGS);
> >
> > Perhaps the latter is more future-proof.
> Copying only xmin-related flags in this way also makes sense to me and
> there is no problem at least for now. A note would be that when we
> introduce a new flag that needs to be copied in the future, we need to
> make sure to add it to PROC_XMIN_FLAGS so it is copied. Otherwise a
> similar issue we fixed by 0f0cfb494004befb0f6e could happen again.
OK, done this way -- patch attached.
Reading the comment I wrote about it, I wonder if flags
PROC_AFFECTS_ALL_HORIZONS and PROC_IN_LOGICAL_DECODING should also be
included. I think the only reason we don't care at this point is that
walsenders (logical or otherwise) do not engage in snapshot copying.
But if we were to implement usage of parallel workers sharing a common
snapshot to do table sync in parallel, then it ISTM it would be
important to copy at least the latter. Not sure there are any cases
were we might care about the former.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
"Every machine is a smoke machine if you operate it wrong enough."
https://twitter.com/libseybieda/status/1541673325781196801
>From 95bd3bf62992987e1d6e078520ff76133248579e Mon Sep 17 00:00:00 2001
From: Alvaro Herrera
Date: Sat, 14 May 2022 16:51:23 +0200
Subject: [PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS
This is a slight, convenient semantics change from what commit
0f0cfb494004 introduced that lets us simplify the coding in the one
place where it is used.
---
src/backend/storage/ipc/procarray.c | 17 +++--
src/include/storage/proc.h | 7 +++
2 files changed, 10 insertions(+), 14 deletions(-)
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index ca22336e35..cd58c5faf0 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -2685,17 +2685,14 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc)
TransactionIdIsNormal(xid) &&
TransactionIdPrecedesOrEquals(xid, xmin))
{
- /* Install xmin */
+ /*
+ * Install xmin and propagate the statusFlags that affect how the
+ * value is interpreted by vacuum.
+ */
MyProc->xmin = TransactionXmin = xmin;
-
- /* walsender cheats by passing proc == MyProc, don't check its flags */
- if (proc != MyProc)
- {
- /* Flags being copied must be valid copy-able flags. */
- Assert((proc->statusFlags & (~PROC_COPYABLE_FLAGS)) == 0);
- MyProc->statusFlags = proc->statusFlags;
- ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
- }
+ MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) |
+ (proc->statusFlags & PROC_XMIN_FLAGS);
+ ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags;
result = true;
}
diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h
index 15be21c00a..2579e619eb 100644
--- a/src/include/storage/proc.h
+++ b/src/include/storage/proc.h
@@ -69,11 +69,10 @@ struct XidCache
(PROC_IN_VACUUM | PROC_IN_SAFE_IC | PROC_VACUUM_FOR_WRAPAROUND)
/*
- * Flags that are valid to copy from another proc, the parallel leader
- * process in practice. Currently, flags that are set during parallel
- * vacuum and parallel index creation are allowed.
+ * Xmin-related flags. Make sure any flags that affect how the process' Xmin
+ * value is interpreted by VACUUM are included here.
*/
-#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC)
+#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC)
/*
* We allow a small number of "weak" relation locks (AccessShareLock,
--
2.30.2
Re: Skipping schema changes in publication
On Fri, May 13, 2022 at 9:37 AM Peter Smith wrote:
>
> On Thu, May 12, 2022 at 2:24 PM vignesh C wrote:
> >
> ...
> > The attached patch has the implementation for "ALTER PUBLICATION
> > pubname RESET". This command will reset the publication to default
> > state which includes resetting the publication options, setting ALL
> > TABLES option to false and dropping the relations and schemas that are
> > associated with the publication.
> >
>
> Please see below my review comments for the v1-0001 (RESET) patch
>
> ==
>
> 1. Commit message
>
> This patch adds a new RESET option to ALTER PUBLICATION which
>
> Wording: "RESET option" -> "RESET clause"
Modified
> ~~~
>
> 2. doc/src/sgml/ref/alter_publication.sgml
>
> +
> + The RESET clause will reset the publication to default
> + state which includes resetting the publication options, setting
> + ALL TABLES option to false
> and drop the
> + relations and schemas that are associated with the publication.
>
>
> 2a. Wording: "to default state" -> "to the default state"
Modified
> 2b. Wording: "and drop the relations..." -> "and dropping all relations..."
Modified
> ~~~
>
> 3. doc/src/sgml/ref/alter_publication.sgml
>
> + invoking user to be a superuser. RESET of publication
> + requires invoking user to be a superuser. To alter the owner, you must
> also
>
> Wording: "requires invoking user" -> "requires the invoking user"
Modified
> ~~~
>
> 4. doc/src/sgml/ref/alter_publication.sgml - Example
>
> @@ -207,6 +220,12 @@ ALTER PUBLICATION sales_publication ADD ALL
> TABLES IN SCHEMA marketing, sales;
> production_publication:
>
> ALTER PUBLICATION production_publication ADD TABLE users,
> departments, ALL TABLES IN SCHEMA production;
> +
> +
> +
> + Resetting the publication production_publication:
> +
> +ALTER PUBLICATION production_publication RESET;
>
> Wording: "Resetting the publication" -> "Reset the publication"
Modified
> ~~~
>
> 5. src/backend/commands/publicationcmds.c
>
> + /* Check and reset the options */
>
> IMO the code can just reset all these options unconditionally. I did
> not see the point to check for existing option values first. I feel
> the simpler code outweighs any negligible performance difference in
> this case.
Modified
> ~~~
>
> 6. src/backend/commands/publicationcmds.c
>
> + /* Check and reset the options */
>
> Somehow it seemed a pity having to hardcode all these default values
> true/false in multiple places; e.g. the same is already hardcoded in
> the parse_publication_options function.
>
> To avoid multiple hard coded bools you could just call the
> parse_publication_options with an empty options list. That would set
> the defaults which you can then use:
> values[Anum_pg_publication_pubinsert - 1] =
> BoolGetDatum(pubactiondefs->insert);
>
> Alternatively, maybe there should be #defines to use instead of having
> the scattered hardcoded bool defaults:
> #define PUBACTION_DEFAULT_INSERT true
> #define PUBACTION_DEFAULT_UPDATE true
> etc
I have used #define for default value and used it in both the functions.
> ~~~
>
> 7. src/include/nodes/parsenodes.h
>
> @@ -4033,7 +4033,8 @@ typedef enum AlterPublicationAction
> {
> AP_AddObjects, /* add objects to publication */
> AP_DropObjects, /* remove objects from publication */
> - AP_SetObjects /* set list of objects */
> + AP_SetObjects, /* set list of objects */
> + AP_ReSetPublication /* reset the publication */
> } AlterPublicationAction;
>
> Unusual case: "AP_ReSetPublication" -> "AP_ResetPublication"
Modified
> ~~~
>
> 8. src/test/regress/sql/publication.sql
>
> 8a.
> +-- Test for RESET PUBLICATION
> SUGGESTED
> +-- Tests for ALTER PUBLICATION ... RESET
Modified
> 8b.
> +-- Verify that 'ALL TABLES' option is reset
> SUGGESTED:
> +-- Verify that 'ALL TABLES' flag is reset
Modified
> 8c.
> +-- Verify that publish option and publish via root option is reset
> SUGGESTED:
> +-- Verify that publish options and publish_via_partition_root option are
> reset
Modified
> 8d.
> +-- Verify that only superuser can execute RESET publication
> SUGGESTED
> +-- Verify that only superuser can reset a publication
Modified
Thanks for the comments, the attached v5 patch has the changes for the
same. Also I have made the changes for SKIP Table based on the new
syntax, the changes for the same are available in
v5-0002-Skip-publishing-the-tables-specified-in-EXCEPT-TA.patch.
Regards,
Vignesh
From b38b5fa76c88c2d2df6abf46a760a9422072c989 Mon Sep 17 00:00:00 2001
From: Vigneshwaran C
Date: Sat, 14 May 2022 13:13:46 +0530
Subject: [PATCH v5 1/2] Add RESET clause to Alter Publication which will reset
the publication with default values.
This patch adds a new RESET clause to ALTER PUBLICATION which will reset
the publication to default state which includes resetting the publication
options, setting ALL TABLES option to false and dropping the relations and
schemas that are associated with the publication.
Usage:
ALTER PUBLICATION pub1
Re: list of TransactionIds
On Sat, May 14, 2022 at 1:57 AM Alvaro Herrera wrote: > > We didn't have any use of TransactionId as members of List, until > RelationSyncEntry->streamed_txns was introduced (464824323e57, pg14). > It's currently implemented as a list of int. This is not wrong at > present, but it may soon be, and I'm sure it rubs some people the wrong > way. > > But is the rubbing way wrong enough to add support for TransactionId in > pg_list.h, including, say, T_XidList? > +1. I don't know if we have a need for this at other places but I feel it is a good idea to make its current use better. -- With Regards, Amit Kapila.
Re: Backends stunk in wait event IPC/MessageQueueInternal
On Fri, 13 May 2022 at 22:08, Robert Haas wrote: > On Fri, May 13, 2022 at 6:16 AM Japin Li wrote: >> The process cannot be terminated by pg_terminate_backend(), although >> it returns true. > > pg_terminate_backend() just sends SIGINT. What I'm wondering is what > happens when the stuck process receives SIGINT. It would be useful, I > think, to check the value of the global variable InterruptHoldoffCount > in the stuck process by attaching to it with gdb. I would also try > running "strace -p $PID" on the stuck process and then try terminating > it again with pg_terminate_backend(). Either the system call in which > it's currently stuck returns and then it makes the same system call > again and hangs again ... or the signal doesn't dislodge it from the > system call in which it's stuck in the first place. It would be useful > to know which of those two things is happening. > > One thing I find a bit curious is that the top of the stack in your > case is ioctl(). And there are no calls to ioctl() anywhere in > latch.c, nor have there ever been. What operating system is this? We > have 4 different versions of WaitEventSetWaitBlock() that call > epoll_wait(), kevent(), poll(), and WaitForMultipleObjects() > respectively. I wonder which of those we're using, and whether one of > those calls is showing up as ioctl() in the stacktrace, or whether > there's some other function being called in here that is somehow > resulting in ioctl() getting called. Thanks for your advice. I will try this on Monday. -- Regrads, Japin Li. ChengDu WenWu Information Technology Co.,Ltd.
Re: Backends stunk in wait event IPC/MessageQueueInternal
On Sat, 14 May 2022 at 11:01, Thomas Munro wrote:
> On Sat, May 14, 2022 at 10:25 AM Thomas Munro wrote:
>> Japin, are you able to reproduce the problem reliably? Did I guess
>> right, that you're on illumos? Does this help? I used
>> defined(__sun__) to select the option, but I don't remember if that's
>> the right way to detect that OS family, could you confirm that, or
>> adjust as required?
>
> Better version. Now you can independently set -DWAIT_USE_{POLL,EPOLL}
> and -DWAIT_USE_{SELF_PIPE,SIGNALFD} for testing, and it picks a
> sensible default.
Sorry for the late reply. My bad! It actually SmartOS, which is based on
illumos.
--
Regrads,
Japin Li.
ChengDu WenWu Information Technology Co.,Ltd.
Re: Rewriting the test of pg_upgrade as a TAP test - take three - remastered set
On Thu, May 12, 2022 at 02:27:30PM +0900, Michael Paquier wrote: > On Tue, May 10, 2022 at 10:32:55PM -0700, Noah Misch wrote: > > On Wed, May 11, 2022 at 10:29:44AM +0900, Michael Paquier wrote: > > > On Mon, May 09, 2022 at 12:18:39PM +0900, Michael Paquier wrote: > > > > All these fixes lead me to the attached patch. > > > > > > I have applied this stuff as of 7dd3ee5, in time for beta1, and closed > > > the open item. One difference is that I've added one backslash > > > surrounding the double quote at the beginning *and* the end of the > > > database name in the patch. However, the original case was different, > > > with: > > > - At the beginning of the database name, one backslash before and > > > after the double quote. > > > - At the end of the database name, two backslaces before the double > > > quote and three after the double quote. Here, you describe differences between test.sh and your rewrite of test.sh. > > Why did you discontinue testing the longstanding test database name? > > I am not sure what you mean here. Here, I requested the rationale for the differences you had just described. You made a choice to stop testing one list of database names and start testing a different list of database names. Why? > Are you saying that the test should > be changed to prefix each database name by "regression", as it was the > case in test.sh? Or do you mean that the backslash/double-quote > business should only apply to the first database name and not the > other two, implying that the new generate_db() in 002_pg_upgrade.pl > had better have a $prefix and a $suffix like it was originally > written? No, I wasn't saying any of those. (Later, I may say one or more of those.)
Re: Multi-Master Logical Replication
On Sat, May 14, 2022 at 12:33 AM Bruce Momjian wrote: > > Uh, without these features, what workload would this help with? > To allow replication among multiple nodes when some of the nodes may have pre-existing data. This work plans to provide simple APIs to achieve that. Now, let me try to explain the difficulties users can face with the existing interface. It is simple to set up replication among various nodes when they don't have any pre-existing data but even in that case if the user operates on the same table at multiple nodes, the replication will lead to an infinite loop and won't proceed. The example in email [1] demonstrates that and the patch in that thread attempts to solve it. I have mentioned that problem because this work will need that patch. Now, let's take a simple case where two nodes have the same table which has some pre-existing data: Node-1: Table t1 (c1 int) has data 1, 2, 3, 4 Node-2: Table t1 (c1 int) has data 5, 6, 7, 8 If we have to set up replication among the above two nodes using existing interfaces, it could be very tricky. Say user performs operations like below: Node-1 #Publication for t1 Create Publication pub1 For Table t1; Node-2 #Publication for t1, Create Publication pub1_2 For Table t1; Node-1: Create Subscription sub1 Connection '' Publication pub1_2; Node-2: Create Subscription sub1_2 Connection '' Publication pub1; After this the data will be something like this: Node-1: 1, 2, 3, 4, 5, 6, 7, 8 Node-2: 1, 2, 3, 4, 5, 6, 7, 8, 5, 6, 7, 8 So, you can see that data on Node-2 (5, 6, 7, 8) is duplicated. In case, table t1 has a unique key, it will lead to a unique key violation and replication won't proceed. Here, I have assumed that we already have functionality for the patch in email [1], otherwise, replication will be an infinite loop replicating the above data again and again. Now one way to achieve this could be that we can ask users to stop all operations on both nodes before starting replication between those and take data dumps of tables from each node they want to replicate and restore them to other nodes. Then use the above commands to set up replication and allow to start operations on those nodes. The other possibility for users could be as below. Assume, we have already created publications as in the above example, and then: Node-2: Create Subscription sub1_2 Connection '' Publication pub1; #Wait for the initial sync of table t1 to finish. Users can ensure that by checking 'srsubstate' in pg_subscription_rel. Node-1: Begin; # Disallow truncates to be published and then truncate the table Alter Publication pub1 Set (publish = 'insert, update, delete'); Truncate t1; Create Subscription sub1 Connection '' Publication pub1_2; Alter Publication pub1 Set (publish = 'insert, update, delete, truncate'); Commit; This will become more complicated when more than two nodes are involved, see the example provided for the three nodes case [2]. Can you think of some other simpler way to achieve the same? If not, I don't think the current way is ideal and even users won't prefer that. I am not telling that the APIs proposed in this thread is the only or best way to achieve the desired purpose but I think we should do something to allow users to easily set up replication among multiple nodes. [1] - https://www.postgresql.org/message-id/CALDaNm0gwjY_4HFxvvty01BOT01q_fJLKQ3pWP9%3D9orqubhjcQ%40mail.gmail.com [2] - https://www.postgresql.org/message-id/CALDaNm3aD3nZ0HWXA8V435AGMvORyR5-mq2FzqQdKQ8CPomB5Q%40mail.gmail.com -- With Regards, Amit Kapila.
Re: JSON Functions and Operators Docs for v15
Not done yet but here's where I'm at. If I'm on the wrong track or
missing things that should be done please let me know.
[sqljson-dox-rework.patch]
Here are a few errors/typos/improvements.
I've added (=copied from the old docs) the CREATE TABLE for the my_films
table so that the more complicated json_table examples can be run easily.
Erik Rijkers
--
Andrew Dunstan
EDB: https://www.enterprisedb.com--- doc/src/sgml/func.sgml.orig 2022-05-14 06:32:28.564537299 +0200
+++ doc/src/sgml/func.sgml 2022-05-14 08:10:05.313313154 +0200
@@ -16287,7 +16287,7 @@
jsonb_set_lax('[{"f1":1,"f2":null},2,null,3]', '{0,f1}', null)
-[{"f1":null,"f2":null},2,null,3]
+[{"f1": null, "f2": null}, 2, null, 3]
jsonb_set_lax('[{"f1":99,"f2":null},2]', '{0,f3}', null, true, 'return_target')
@@ -17682,7 +17682,7 @@
object keys.
-json('{"a" 123, "b":[true,"foo"], "a":"bar"}')
+json('{"a":123, "b":[true,"foo"], "a":"bar"}')
{"a":123, "b":[true,"foo"], "a":"bar"}
@@ -17959,7 +17959,7 @@
json_exists(jsonb '{"a": [1,2,3]}', 'strict $.a[5]' ERROR ON ERROR)
-ERROR: Invalid SQL/JSON subscript
+ERROR: jsonpath array subscript is out of bounds
@@ -17990,11 +17990,11 @@
of the ON EMPTY clause.
-json_value('"123.45"', '$' RETURNING float)
+json_value('"123.45"'::jsonb, '$' RETURNING float)
123.45
-json_value('"03:04 2015-02-01"', '$.datetime("HH24:MI -MM-DD")' RETURNING date)
+json_value('"03:04 2015-02-01"'::jsonb, '$.datetime("HH24:MI -MM-DD")' RETURNING date)
2015-02-01
@@ -18111,7 +18111,7 @@
The input data to query, the JSON path expression defining the query,
- and an optional PASSING clause, which can privide data
+ and an optional PASSING clause, which can provide data
values to the path_expression.
The result of the input data
evaluation is called the row pattern. The row
@@ -18409,6 +18409,31 @@
Examples
+ In these examples the following small table storing some JSON data will be used:
+
+CREATE TABLE my_films ( js jsonb );
+
+INSERT INTO my_films VALUES (
+'{ "favorites" : [
+ { "kind" : "comedy", "films" : [
+ { "title" : "Bananas",
+ "director" : "Woody Allen"},
+ { "title" : "The Dinner Game",
+ "director" : "Francis Veber" } ] },
+ { "kind" : "horror", "films" : [
+ { "title" : "Psycho",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "thriller", "films" : [
+ { "title" : "Vertigo",
+ "director" : "Alfred Hitchcock" } ] },
+ { "kind" : "drama", "films" : [
+ { "title" : "Yojimbo",
+ "director" : "Akira Kurosawa" } ] }
+ ] }');
+
+
+
+
Query the my_films table holding
some JSON data about the films and create a view that
distributes the film genre, title, and director between separate columns:
@@ -18427,7 +18452,7 @@
1 | comedy | Bananas | Woody Allen
1 | comedy | The Dinner Game | Francis Veber
2 | horror | Psycho | Alfred Hitchcock
- 3 | thriller | Vertigo | Hitchcock
+ 3 | thriller | Vertigo | Alfred Hitchcock
4 | drama| Yojimbo | Akira Kurosawa
(5 rows)
