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(&rel->pgstat_info, 0, sizeof(rel->pgstat_info)); I think that intention with use of MemSet was: MemSet(&rel->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 RESET;
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.)