Re: Differences between = ANY and IN?

Maciek Sakrejda writes: > My colleague's patch changes SQL generated from Ruby expressions like > `where(id: [1, 2])` . This is currently translated to roughly `WHERE > id IN (1, 2)` and would be changed to `id = ANY('{1,2}')`. > As far as we know, the expressions are equivalent, but we wanted

Re: Making aggregate deserialization (and WAL receive) functions slightly faster

On Sun, 12 Feb 2023 at 23:43, David Rowley wrote: > > On Sun, 12 Feb 2023 at 19:39, Tom Lane wrote: > > It could maybe be okay if we added the capability for StringInfoData > > to understand (and enforce) that its "data" buffer is read-only. > > However, that'd add overhead to every existing

Differences between = ANY and IN?

Hello, My colleague has been working on submitting a patch [1] to the Ruby Rails framework to address some of the problems discussed in [2]. Regardless of whether that change lands, the change in Rails would be useful since people will be running Postgres versions without this patch for a while.

Re: Add support for AT LOCAL

On 9/29/23 09:27, Michael Paquier wrote: On Sat, Sep 23, 2023 at 12:54:01AM +0200, Vik Fearing wrote: On 9/22/23 23:46, cary huang wrote: I think this feature can be a useful addition in dealing with time zones. I have applied and tried out the patch, The feature works as described and seems

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Tue, Oct 3, 2023 at 9:58 AM Bharath Rupireddy wrote: > > On Fri, Sep 29, 2023 at 5:27 PM Hayato Kuroda (Fujitsu) > wrote: > > > > Yeah, the approach enforces developers to check the decodability. > > But the benefit seems smaller than required efforts for it because the > > function > >

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Fri, Sep 29, 2023 at 5:27 PM Hayato Kuroda (Fujitsu) wrote: > > Yeah, the approach enforces developers to check the decodability. > But the benefit seems smaller than required efforts for it because the > function > would be used only by pg_upgrade. Could you tell me if you have another use

Re: Fixup some more appendStringInfo misusages

On Tue, 3 Oct 2023 at 11:24, David Rowley wrote: > This is along the same lines as 8b26769bc, f736e188c, 110d81728 and 8abc13a88. I've pushed the patch to fix the misusages of the functions. David

[PGDOCS] Inconsistent linkends to "monitoring" views.

I noticed one or two "monitoring" links and linkends that are slightly inconsistent from all the others. ~~~ >From "Dynamic Statistics Views" pg_stat_activity, linkend="monitoring-pg-stat-activity-view" ==> ok pg_stat_replication, linkend="monitoring-pg-stat-replication-view" ==> ok

Re: Invalidate the subscription worker in cases where a user loses their superuser status

Some review comments for v5. == src/backend/catalog/pg_subscription.c 1. GetSubscription - comment + /* Get superuser for subscription owner */ + sub->ownersuperuser = superuser_arg(sub->owner); + The comment doesn't seem very good. SUGGESTION /* Is the subscription owner a superuser? */

Re: Allow deleting enumerated values from an existing enumerated data type

On 10/2/23 20:07, Dagfinn Ilmari Mannsåker wrote: Vik Fearing writes: No one except you has said anything about this patch. I think it would be good to commit it, wordsmithing aside. FWIW I'm +1 on this patch, Thanks. and with Tom on dropping the "yet". To me it makes it sound like we

Re: CHECK Constraint Deferrable

Vik Fearing writes: > On 10/2/23 21:25, Tom Lane wrote: >> Sorry for not weighing in on this before, but ... is this a feature >> we want at all? > For standards conformance, I vote yes. Only if we can actually implement it in a defensible way, which this patch is far short of accomplishing.

Re: CHECK Constraint Deferrable

På fredag 07. juli 2023 kl. 13:50:44, skrev Dilip Kumar mailto:dilipbal...@gmail.com>>: On Wed, Jul 5, 2023 at 3:08 PM Himanshu Upadhyaya wrote: > > Hi, > > Currently, there is no support for CHECK constraint DEFERRABLE in a create table statement. > SQL standard specifies that CHECK

Re: Replace (stat())[7] in TAP tests with -s

On Mon, Oct 02, 2023 at 12:44:59PM +0100, Dagfinn Ilmari Mannsåker wrote: > I approve of removing use of the list form of stat, it's a horrible API. Agreed, I've appied the suggestion to use -s, like we do anywhere else. > If we weren't already using -s everywhere else, I would prefer >

Re: pg*.dll and *.pdb files in psqlODBC have no version numbers

On Mon, Oct 02, 2023 at 02:28:58PM +, Mark Hill wrote: > A colleague noticed that the following files in the psqlODBC MSI for Windows > have no version numbers: > pgenlist.dll > pgenlista.dll > pgxalib.dll > pgenlist.pdb > pgenlista.pdb > psqlodbc30a.pdb > psqlodbc35w.pdb > > Does anyone

Re: Does anyone ever use OPTIMIZER_DEBUG?

On Fri, 29 Sept 2023 at 10:59, Tom Lane wrote: > We could also discuss keeping the "tracing" aspect of it, but > replacing debug_print_rel with pprint(rel), which'd still allow > removal of all the "DEBUG SUPPORT" stuff at the bottom of allpaths.c. > That's pretty much all of the

Re: CHECK Constraint Deferrable

On 10/2/23 21:25, Tom Lane wrote: Himanshu Upadhyaya writes: V3 patch attached. Sorry for not weighing in on this before, but ... is this a feature we want at all? For standards conformance, I vote yes. We are very clear in the existing docs that CHECK conditions must be immutable [1],

Fixup some more appendStringInfo misusages

The attached v1-0001 patch adjusts some code in stringinfo.h to find misusages of the appendStringInfo functions. I don't intend to commit that, but I do intend to commit the patch for the new misusages that it found, which is also attached. This is along the same lines as 8b26769bc, f736e188c,

Re: [PGDOCS] change function linkend to refer to a more relevant target

On Sat, Sep 30, 2023 at 12:04 AM Daniel Gustafsson wrote: > > > On 29 Sep 2023, at 10:54, Daniel Gustafsson wrote: > > > >> On 29 Sep 2023, at 06:51, Peter Smith wrote: > > > >> I found a link in the docs that referred to the stats "views" section, > >> instead of the more relevant (IMO) stats

Re: Pre-proposal: unicode normalized text

On Tue, Sep 12, 2023 at 03:47:10PM -0700, Jeff Davis wrote: > One of the frustrations with using the "C" locale (or any deterministic > locale) is that the following returns false: > > SELECT 'á' = 'á'; -- false > > because those are the unicode sequences U&'\0061\0301' and U&'\00E1', >

Re: Eager page freeze criteria clarification

On Mon, Oct 2, 2023 at 11:37 AM Peter Geoghegan wrote: > If no vacuuming against pgbench_accounts is strictly better than some > vacuuming (unless it's just to advance relfrozenxid, which can't be > avoided), then to what extent is Melanie's freezing patch obligated to > not make the situation

Re: Various small doc improvements; plpgsql, schemas, permissions, oidvector

On Sun, 1 Oct 2023 18:18:07 -0500 "Karl O. Pinc" wrote: Version 6 Added: v6-0015-Trigger-authors-need-not-worry-about-parallelism.patch Can't say if this is an awesome idea or not. (Might have saved me time.) Read the commit message for a justification. > > On Mon, 25 Sep 2023 23:37:44

Re: CHECK Constraint Deferrable

On Mon, Oct 2, 2023 at 12:25 PM Tom Lane wrote: > Himanshu Upadhyaya writes: > > V3 patch attached. > > Sorry for not weighing in on this before, but ... is this a feature > we want at all? We are very clear in the existing docs that CHECK > conditions must be immutable [1], and that's not

Re: pg16: XX000: could not find pathkey item to sort

On Tue, 19 Sept 2023 at 23:45, Richard Guo wrote: > My first thought about the fix is that we artificially add resjunk > target entries to parse->targetList for the ordered aggregates' > arguments that are ORDER BY expressions, as attached. While this can > fix the given query, it would cause

Re: Pre-proposal: unicode normalized text

On Mon, Oct 2, 2023 at 3:42 PM Peter Eisentraut wrote: > I think a better direction here would be to work toward making > nondeterministic collations usable on the global/database level and then > encouraging users to use those. It seems to me that this overlooks one of the major points of

Re: CHECK Constraint Deferrable

Himanshu Upadhyaya writes: > V3 patch attached. Sorry for not weighing in on this before, but ... is this a feature we want at all? We are very clear in the existing docs that CHECK conditions must be immutable [1], and that's not something we can easily relax because if they are not then it's

Re: [DOCS] HOT - correct claim about indexes not referencing old line pointers

On Sat, Sep 30, 2023 at 1:05 AM Peter Geoghegan wrote: > > This is why I discovered it: it says "indexes do not reference their > > page item identifiers", which is manifestly not true when talking > > about the root item, and in fact would defeat the whole purpose of HOT > > (at least in a

Commitfest 2023-09 has finished

As September has ended, I have closed commitfest 2023-09. The status before I started moving patches over was Status summary: Needs review: 189. Waiting on Author: 30. Ready for Committer: 28. Committed: 68. Moved to next CF: 1. Returned with Feedback: 16. Rejected: 1. Withdrawn: 5. Total:

Re: On login trigger: take three

Sorry to have gone dark on this for a long time after having been asked for my input back in March. I'm not having a great time trying to keep up with email, and the threads getting split up makes it a lot worse for me. On Fri, Sep 29, 2023 at 6:15 AM Daniel Gustafsson wrote: > Running the same

Re: Allow deleting enumerated values from an existing enumerated data type

Vik Fearing writes: > On 9/29/23 03:17, Tom Lane wrote: >> Vik Fearing writes: >>> On 9/28/23 20:46, Tom Lane wrote: We went through all these points years ago when the enum feature was first developed, as I recall. Nobody thought that the ability to remove an enum value was

Re: Show various offset arrays for heap WAL records

On 04/09/2023 23:02, Melanie Plageman wrote: I might phrase the last bit as "neither the description functions nor the output format should be considered part of a stable API" +Guidelines for rmgrdesc output format += I noticed you used === for both headings

Re: Allow deleting enumerated values from an existing enumerated data type

On 9/29/23 03:17, Tom Lane wrote: Vik Fearing writes: On 9/28/23 20:46, Tom Lane wrote: We went through all these points years ago when the enum feature was first developed, as I recall. Nobody thought that the ability to remove an enum value was worth the amount of complexity it'd entail.

Re: commitfest app down for repairs

On Sat, Sep 30, 2023 at 9:31 AM Joe Conway wrote: > We restored to a backup from one day prior. We will take a look at what > changed in between, but it might be up to folks to redo some things. > > A cooling off period was added to the commitfest app for new community > accounts, similar to what

Re: [PATCH] Clarify the behavior of the system when approaching XID wraparound

On Mon, Oct 2, 2023 at 11:52 AM Pavel Borisov wrote: > FWIW I think the patch is still in good shape and worth to be committed. I'm also pretty happy with these patches and would like to see at least 0001 and 0002 committed, and probably 0003 as well. I am, however, -1 on back-patching. Perhaps

Re: bgwriter doesn't flush WAL stats

The first patch, to flush the bgwriter's WAL stats to the stats collector, seems like a straightforward bug fix, so committed and backpatched that. Thank you! I didn't look at the second patch. -- Heikki Linnakangas Neon (https://neon.tech)

Re: Eliminate redundant tuple visibility check in vacuum

On Sat, Sep 30, 2023 at 1:02 PM Andres Freund wrote: > The only thought I have is that it might be worth to amend the comment in > lazy_scan_prune() to mention that such a tuple won't need to be frozen, > because it was visible to another session when vacuum started. I revised the comment a bit,

Re: Eager page freeze criteria clarification

On Mon, Oct 2, 2023 at 6:26 AM Robert Haas wrote: > I think it's true that the fact that pgbench does what pgbench does > makes us think more about that workload than about some other, equally > plausible workload. It's the test we have, so we end up running it a > lot. If we had some other test,

Re: CHECK Constraint Deferrable

On Mon, Oct 2, 2023 at 8:31 PM Himanshu Upadhyaya < upadhyaya.himan...@gmail.com> wrote: V3 patch attached. > -- Regards, Himanshu Upadhyaya EnterpriseDB: http://www.enterprisedb.com v3-0001-Implementation-of-CHECK-Constraint-to-make-it.patch Description: Binary data

Re: CHECK Constraint Deferrable

On Tue, Sep 12, 2023 at 2:56 PM vignesh C wrote: > On Thu, 7 Sept 2023 at 17:26, Himanshu Upadhyaya > wrote: > > > > Attached is v2 of the patch, rebased against the latest HEAD. > > Thanks for working on this, few comments: > 1) "CREATE TABLE check_constr_tbl (i int CHECK(i<>0) DEFERRABLE, t >

pg*.dll and *.pdb files in psqlODBC have no version numbers

I posted this question to pgsql-gene...@lists.postgrsql.org last week but on one has responded so posting here now. We download the ODBC source from http://ftp.postgresql.org and build it on-site, 13.02. in this case.

Re: Trigger violates foreign key constraint

On Mon, 2023-10-02 at 09:49 -0400, Tom Lane wrote: > Laurenz Albe writes: > > CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN > > NULL; END;'; > > CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION > > silly(); > > > The trigger function cancels

Re: Trigger violates foreign key constraint

Laurenz Albe writes: > CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN > NULL; END;'; > CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION > silly(); > The trigger function cancels the cascaded delete on "child", and we are left > with > a row

[PATCH] Fix memory leak in memoize for numeric key

Hello, all! I found a query which consumes a lot of memory and triggers OOM killer. Memory leak occurs in memoize node for numeric key. Version postgresql is 14.9. The problem is very similar https://www.postgresql.org/message-id/17844-d2f6f9e75a622...@postgresql.org I attached to the backend

Implementing LRU cache for postgresql extension

Hi I am trying to implement a LRU cache in postgres extension. Please find the markdown file for more details. Looking forward to hearing from you. I am trying to write an LRU cache which can hold any type of data item in c for a postgresql extension. Everything is fine except that I am relying on

Re: Making the subquery alias optional in the FROM clause

Dean Rasheed writes: > On Mon, 2 Oct 2023 at 01:01, Tom Lane wrote: >> Yeah, that's exposing more of the implementation than we really want. > Note that this isn't a new issue, specific to unnamed subqueries. The > same thing happens for unnamed joins: True, and we've had few complaints about

Re: Eager page freeze criteria clarification

On Fri, Sep 29, 2023 at 8:50 PM Peter Geoghegan wrote: > While pgbench makes a fine stress-test, for the most part its workload > is highly unrealistic. And yet we seem to think that it's just about > the most important benchmark of all. If we're not willing to get over > even small regressions

Re: Remove ParallelReadyList and worker_spi_state from typedefs.list

On Sat, 30 Sept 2023 at 21:44, Tom Lane wrote: > > vignesh C writes: > > Remove ParallelReadyList and worker_spi_state from typedefs.list, > > these structures have been removed as part of an earlier commits, > > ParallelReadyList was removed as part of > >

Re: [PATCH] Clarify the behavior of the system when approaching XID wraparound

Hi! On Mon, 2 Oct 2023 at 03:34, Peter Geoghegan wrote: > > On Sun, Oct 1, 2023 at 11:46 AM Peter Eisentraut wrote: > > What is the status of this patch discussion now? It had been set as > > Ready for Committer for some months. Do these recent discussions > > invalidate that? Does it need

Re: Replace (stat())[7] in TAP tests with -s

"Drouvot, Bertrand" writes: > Hi hackers, > > Please find attached a tiny patch to $SUBJECT. > > It: > > - provides more consistency to the way we get files size in TAP tests > - seems more elegant that relying on a hardcoded result position I approve of removing use of the list form of stat,

Re: Trigger violates foreign key constraint

Perhaps it would be enough to run "RI_FKey_noaction_del" after "RI_FKey_cascade_del", although that would impact the performance. Yours, Laurenz Albe

Re: wal recycling problem

Hi, ## Fabrice Chapuis (fabrice636...@gmail.com): > on the other hand there are 2 slots for logical replication which display > status extended. I don't understand why given that the confirmed_flush_lsn > field that is up to date. The restart_lsn remains frozen, for what reason? There you have

Replace (stat())[7] in TAP tests with -s

Hi hackers, Please find attached a tiny patch to $SUBJECT. It: - provides more consistency to the way we get files size in TAP tests - seems more elegant that relying on a hardcoded result position Regards, -- Bertrand Drouvot PostgreSQL Contributors Team RDS Open Source Databases Amazon

Re: Add a new BGWORKER_BYPASS_ROLELOGINCHECK flag

Hi, On 10/2/23 10:17 AM, Michael Paquier wrote: On Mon, Oct 02, 2023 at 10:01:04AM +0200, Drouvot, Bertrand wrote: I think that would make sense to have more flexibility in the worker_spi module. I think that could be done in a dedicated patch though. I think it makes more sense to have the

Re: Making the subquery alias optional in the FROM clause

On Mon, 2 Oct 2023 at 01:01, Tom Lane wrote: > > Erwin Brandstetter writes: > > On Mon, 2 Oct 2023 at 00:33, Dean Rasheed wrote: > >> The only place that exposes the eref's made-up relation name is the > >> existing query deparsing code in ruleutils.c, which uniquifies it and > >> generates SQL

Re: Pre-proposal: unicode normalized text

On 13.09.23 00:47, Jeff Davis wrote: The idea is to have a new data type, say "UTEXT", that normalizes the input so that it can have an improved notion of equality while still using memcmp(). I think a new type like this would obviously be suboptimal because it's nonstandard and most people

Re: TAP tests for psql \g piped into program

On 29/03/2023 21:39, Daniel Verite wrote: Peter Eisentraut wrote: So for your patch, I would just do the path adjustment ad hoc in-line. It's just one additional line. Here's the patch updated that way. Committed, thanks! -- Heikki Linnakangas Neon (https://neon.tech)

Re: pg_stat_get_activity(): integer overflow due to (int) * (int) for MemoryContextAllocHuge()

On Fri, Sep 29, 2023 at 4:00 AM Michael Paquier wrote: > > On Thu, Sep 28, 2023 at 11:01:14AM +0200, Jakub Wartak wrote: > > v3 attached. I had a problem coming out with a better error message, > > so suggestions are welcome. The cast still needs to be present as per > > above suggestion as 3GB

Re: Add a new BGWORKER_BYPASS_ROLELOGINCHECK flag

On Mon, Oct 02, 2023 at 10:01:04AM +0200, Drouvot, Bertrand wrote: > I think that would make sense to have more flexibility in the worker_spi > module. I think that could be done in a dedicated patch though. I > think it makes more sense to have the current patch "focusing" on > this new flag

Re: Add a new BGWORKER_BYPASS_ROLELOGINCHECK flag

Hi, On 9/29/23 8:19 AM, Michael Paquier wrote: On Thu, Sep 28, 2023 at 02:37:02PM +0200, Drouvot, Bertrand wrote: This patch allows the role provided in BackgroundWorkerInitializeConnection() and BackgroundWorkerInitializeConnectionByOid() to lack login authorization. Interesting. Yes,

Clean up some pg_dump tests

Following [0], I did a broader analysis of some dubious or nonsensical like/unlike combinations in the pg_dump tests. This includes 1) Remove useless entries from "unlike" lists. Runs that are not listed in "like" don't need to be excluded in "unlike". 2) Ensure there is always a "like"

Trigger violates foreign key constraint

CREATE TABLE parent (id integer PRIMARY KEY); CREATE TABLE child (id integer REFERENCES parent ON DELETE CASCADE); CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN NULL; END;'; CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION silly(); INSERT

Re: Synchronizing slots from primary to standby

Hi, On 9/29/23 1:33 PM, Amit Kapila wrote: On Thu, Sep 28, 2023 at 6:31 PM Drouvot, Bertrand wrote: I think that standby_slot_names could be used to do some filtering (means for which standby(s) we don't want the logical replication on the primary to go ahead and for which standby(s) one