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 I

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" li

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, there

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 (whil

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 i

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: 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 wo

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: 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 cur

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 We

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 i

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: 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: [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 mo

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 > > 9bfd44bbde4261181bf94738f3b041c629c65a7

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 in

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 t

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

[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

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 i

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

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. A

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 >

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: 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: 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: 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: [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 t

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: 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: 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

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 wort

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 p

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: 33

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 old-to

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 u

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 Jeff's

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 Ass

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 some

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 -050

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 wor

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', > respec

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

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, 1

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], an

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 maintenance-requi

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 know

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 > File::sta

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 constraint

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: 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: 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? */

[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 pg_stat_wal_

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

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: [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 > > would

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 pr

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: 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 use-

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 to

Re: CHECK Constraint Deferrable

On Thu, Sep 14, 2023 at 9:57 AM vignesh C wrote: > 2) I was not sure, if the error message change was intentional: > 2a) > In Head: > CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRABLE) SERVER s1; > ERROR: misplaced DEFERRABLE clause > LINE 1: CREATE FOREIGN TABLE t9(a int CHECK(a<>0) DEFERRAB

Re: pg_stat_statements and "IN" conditions

I've also tried the patch and I see the same results as Jakub, which make sense to me. I did have issues getting it to apply, though: `git am` complains about a conflict, though patch itself was able to apply it.

Re: Differences between = ANY and IN?

Great, thanks for the guidance!

Re: Fail hard if xlogreader.c fails on out-of-memory

On Thu, Sep 28, 2023 at 09:36:37AM +0900, Michael Paquier wrote: > If none, I propose to apply the patch to switch to palloc() instead of > palloc_extended(NO_OOM) in this code around the beginning of next > week, down to 12. Done down to 12 as of 6b18b3fe2c2f, then. -- Michael signature.asc Des

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

On Tue, 3 Oct 2023 at 06:09, Peter Smith wrote: > > 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 see

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

On Tue, Oct 3, 2023 at 5:42 PM vignesh C wrote: > > Thanks for the comments, the attached v6 version patch has the changes > for the same. > v6 LGTM. == Kind Regards, Peter Smith. Fujitsu Australia