Re: Test to dump and restore objects left behind by regression

2025-08-05 Thread Alvaro Herrera
On 2025-Aug-05, Tom Lane wrote: > Daniel Gustafsson writes: > > Thanks for reviving this. I am +1 on placing this behind PG_TEST_EXTRA as > > was > > discussed upthread. > > +1 here too. Cool, thanks, done. Now we need a volunteer to set up a buildfarm animal with this flag ... -- Álvaro H

Re: Test to dump and restore objects left behind by regression

2025-08-05 Thread Alvaro Herrera
Hello, On 2025-Apr-04, Andres Freund wrote: > FWIW, with cassert and -O2, it's: > > 17: > real0m53.981s > user3m22.837s > sys 3m24.237s > > HEAD: > real1m19.749s > user4m54.526s > sys 4m15.657s > > so this isn't just due to me using

Re: log_min_messages per backend type

2025-07-31 Thread Alvaro Herrera
On 2025-Aug-01, Japin Li wrote: > If we set the log level for all backend types, I don't think a generic log > level is necessary. I don't understand what you mean by this. Can you elaborate? -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/ "If it is not right,

Adding REPACK [concurrently]

2025-07-26 Thread Alvaro Herrera
Hello, Here's a patch to add REPACK and eventually the CONCURRENTLY flag to it. This is coming from [1]. The ultimate goal is to have an in-core tool to allow concurrent table rewrite to get rid of bloat; right now, VACUUM FULL does that, but it's not concurrent. Users have resorted to using the

Re: why there is not VACUUM FULL CONCURRENTLY?

2025-07-22 Thread Alvaro Herrera
Hello I started to read through 0001 and my first reaction is that I would like to make a few more breaking changes. It appears that the current patch tries to keep things unchanged, or to keep some things with the CLUSTER name. I'm going to try and get rid of that. For instance, in the grammar

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-05 Thread Alvaro Herrera
On 2025-Jul-04, Tom Lane wrote: > Alvaro Herrera writes: > > On 2025-Jul-04, Alvaro Herrera wrote: > >> Hmm, crake doesn't like the fact that there's no regnamespace in 9.4. > >> Apparently in version 13 we claim to support back to 9.0. I only tried > &

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-04 Thread Alvaro Herrera
On 2025-Jul-04, Alvaro Herrera wrote: > Hmm, crake doesn't like the fact that there's no regnamespace in 9.4. > Apparently in version 13 we claim to support back to 9.0. I only tried > with an old server version 12. Should be an easy fix ... It goes like this. It's tes

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-04 Thread Alvaro Herrera
Hmm, crake doesn't like the fact that there's no regnamespace in 9.4. Apparently in version 13 we claim to support back to 9.0. I only tried with an old server version 12. Should be an easy fix ... -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-04 Thread Alvaro Herrera
On 2025-Jul-03, Tom Lane wrote: > Alvaro Herrera writes: > > On 2025-Jul-03, Justin Pryzby wrote: > >>> Actually I think we should consider backporting to all live versions > > >> If you don't backpatch it, there's no point. > > > Oh yeah, yo

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-03 Thread Alvaro Herrera
On 2025-Jul-03, Justin Pryzby wrote: > > I think from 18 on, the problem can no longer be recreated, > > > Actually I think we should consider backporting to all live versions > > If you don't backpatch it, there's no point. Oh yeah, you're absolutely right. -- Álvaro Herrera PostgreS

Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" in child table must be marked NOT NULL

2025-07-03 Thread Alvaro Herrera
Hello, We seem to have forgotten about this issue. I think this is even more pressing with the changes to 18 for not-null constraints, though strictly speaking it's always been a problem. Here's an updated patch. I simplified the query (no need for a recursive CTE as far as I can tell) and updat

Re: Psql meta-command conninfo+

2025-06-12 Thread Alvaro Herrera
On 2025-Jun-12, Peter Eisentraut wrote: > This new code uses the term "TLS" where the rest of PostgreSQL, including > the rest of psql, uses the term "SSL". Making this different seems > uselessly confusing. I suggest the attached patch to use "SSL" here as > well. Sure, let's do that for now.

Re: Foreign key validation failure in 18beta1

2025-06-05 Thread Alvaro Herrera
On 2025-Jun-04, jian he wrote: > On Tue, Jun 3, 2025 at 12:14 PM Amul Sul wrote: > > > I found a third approach that requires only a few changes. The key > > idea is to determine the root referenced table and pass it to > > QueueFKConstraintValidation(). We would then enqueue phase 3 > > validati

Re: Foreign key validation failure in 18beta1

2025-06-02 Thread Alvaro Herrera
Hello I find this coding somewhat confusing. Now you have a function "QueueFkConstraintValidation" which may queue a FK queue constraint validation, if the flag "queueValidation" is given, but it may also do something else -- makes no sense IMO. I think it's better to split this function in two;

Re: Foreign key validation failure in 18beta1

2025-05-28 Thread Alvaro Herrera
On 2025-May-28, Tender Wang wrote: > I dided the codes, in QueueFKConstraintValidation(), we add three > newconstraint for the > fk rel, because the pk rel is partition table. > > During phase 3 of AlterTable, in ATRewriteTables(), > call validateForeignKeyConstraint() three times. > The first t

Re: Assert failure in base_yyparse

2025-05-14 Thread Alvaro Herrera
On 2025-May-14, Richard Guo wrote: > Attached is a patch that implements this. It also renames the > internally used option name and updates the error message. LGTM. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/

Re: Assert failure in base_yyparse

2025-05-09 Thread Alvaro Herrera
On 2025-Apr-14, Richard Guo wrote: > It seems what happens is that internally in gram.y (~line 14274), the > DefElem for the not-null option is assigned the name "is_not_null". > As a result, this allows users to explicitly use "is_not_null" as the > option name. However, the value provided for t

Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key

2025-05-08 Thread Alvaro Herrera
On 2025-May-08, Matthew Gabeler-Lee wrote: > My earlier test case, and an adjusted version of it that is closer to > my production application (notably adding ON DELETE CASCADE to the > FK), now passes with 15.13. Thank you, that's a relief to know. -- Álvaro Herrera PostgreSQL Develope

Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key

2025-05-03 Thread Alvaro Herrera
On 2025-May-01, Tender Wang wrote: > Hmm. I didn't get the same conclusion. > Before commit 5914a22f6ea5, the issue reported by Luca could have happened. [...] > You can see from the above test that no error was reported. > But if I revert the commit 614a406b4ff1, above test would report error o

Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key

2025-05-01 Thread Alvaro Herrera
Hello, I've been looking at this bug once again and I think I finally understood what's going on and how to fix it. Ref 1: https://postgr.es/m/20230707175859.17c91538@karst Re: Issue attaching a table to a partitioned table with an auto-referenced foreign key (Guillaume Lelar

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-28 Thread Alvaro Herrera
On 2025-Apr-26, jian he wrote: > I am wondering if we need to change the following comments in getTableAttrs. > > * We track in notnull_islocal whether the constraint was defined directly > * in this table or via an ancestor, for binary upgrade. flagInhAttrs > * might modify this

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-25 Thread Alvaro Herrera
On 2025-Apr-23, Nathan Bossart wrote: > This one was briefly discussed in an RMT meeting. > > On Wed, Apr 09, 2025 at 01:16:20PM +0800, jian he wrote: > > attached patch is for address pg_dump inconsistency > > when parent is "not null not valid" while child is "not null". > > I see an open item

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-24 Thread Alvaro Herrera
On 2025-Apr-09, jian he wrote: > hi. > > attached patch is for address pg_dump inconsistency > when parent is "not null not valid" while child is "not null". Here's my take on this patch. We don't really need the notnull_parent_invalid flag; in flagInhAttrs we can just set "islocal" to convince

Re: not null constraints, again

2025-04-16 Thread Alvaro Herrera
On 2025-Apr-16, Tender Wang wrote: > if (conForm->contype != CONSTRAINT_NOTNULL) > elog(ERROR, "constraint %u is not a not-null constraint", conForm->oid); > > I feel that using conForm->conname is more friendly than oid for users. Yeah, this doesn't really matter because this function would

Re: not null constraints, again

2025-04-16 Thread Alvaro Herrera
Here's another version where I do skip searching for children twice, and rewrote the comments. I also noticed that in child tables we were only looking for pg_attribute.attnotnull, and not whether the constraints had been validated or made inheritable. This seemed a wasted opportunity, so I refac

Re: not null constraints, again

2025-04-15 Thread Alvaro Herrera
On 2025-Apr-15, Tom Lane wrote: > +1. Fundamentally the problem here is that pg_restore needs > > ALTER TABLE ONLY foo ADD PRIMARY KEY > > to not recurse to child tables at all. It is expecting this command > to acquire a lock on foo and nothing else; and it has already taken > care of making

Re: not null constraints, again

2025-04-15 Thread Alvaro Herrera
On 2025-Apr-15, Tender Wang wrote: > I thought further about the lockmode calling find_inheritance_children > in ATPrepAddPrimaryKey. > What we do here? We first get oids of children, then check the if the > column of children has marked not-null, if not, report an error. > No operation here on c

Re: not null constraints, again

2025-04-14 Thread Alvaro Herrera
On 2025-Apr-14, Tom Lane wrote: > The patch I propose there seems to prevent this, but I wonder if we > shouldn't look closer into why it's failing in the first place. > I would not have expected that adding pg_constraint rows implies > stronger locks than what ALTER ADD PRIMARY KEY was using befo

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-10 Thread Alvaro Herrera
On 2025-Apr-07, Tom Lane wrote: > Alvaro Herrera writes: > > I have pushed this after some small additional changes. > > Looks like some of the test cases have issues with locale-dependent > ordering. > > https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=jay&

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-07 Thread Alvaro Herrera
On 2025-Apr-07, jian he wrote: > CREATE TABLE t (a int, b int); > INSERT INTO t VALUES (NULL, 1), (300, 3); > ALTER TABLE t ADD CONSTRAINT nn NOT NULL a NOT VALID; -- ok > ALTER TABLE t add column c float8 default random(); > the last query should not fail. Agreed. > if we want more places use C

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-06 Thread Alvaro Herrera
On 2025-Apr-05, jian he wrote: > hi. > + /* FIXME use CompactAttribute */ > Form_pg_attribute att = TupleDescAttr(relation->rd_att, i - > 1); > if (att->attnotnull && att->attnotnullvalid && > !att->attisdropped) > { >

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-05 Thread Alvaro Herrera
On 2025-Mar-21, jian he wrote: > * if partitioned table have valid not-null, then partition with > invalid not-null can not attach to the partition tree. Correct. > if partitioned table have not valid not-null, we *can* attach a > valid not-null to the partition tree. Also correct. > (inhe

Re: Test to dump and restore objects left behind by regression

2025-04-05 Thread Alvaro Herrera
On 2025-Mar-28, Ashutosh Bapat wrote: > However, it's a very painful process to come up with the schedule and > more painful and error prone to maintain it. It could take many days > to come up with the right schedule which can become inaccurate the > moment next SQL file is added OR an existing f

Re: two occurrences of assign print_notnull within pg_dump.c

2025-04-05 Thread Alvaro Herrera
On 2025-Apr-03, Ashutosh Bapat wrote: > On Thu, Apr 3, 2025 at 4:31 PM jian he wrote: > > > > hi. > > > > in src/bin/pg_dump/pg_dump.c > > within function dumpTableSchema: > > there are two occurrences of: > > print_notnull = (tbinfo->notnull_constrs[j] != NULL && > >

Re: Modern SHA2- based password hashes for pgcrypto

2025-04-05 Thread Alvaro Herrera
Hello, I have pushed this now, hoping it won't explode. Thanks! -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/ "No es bueno caminar con un hombre muerto"

Re: Modern SHA2- based password hashes for pgcrypto

2025-04-05 Thread Alvaro Herrera
Hello, I triggered a run of this on CI on all platforms. It seems to have gone well, so unless I hear complaints, I intend to get this out later today. https://cirrus-ci.com/build/4613871211642880 Thanks, -- Álvaro HerreraBreisgau, Deutschland — https://www.EnterpriseDB.com/

Re: Test to dump and restore objects left behind by regression

2025-04-04 Thread Alvaro Herrera
On 2025-Apr-02, Ashutosh Bapat wrote: > I have closed the CF entry > https://commitfest.postgresql.org/patch/4564/ committed. I will > create another CF entry to park --no-statistics reversal change. That > way, we will know when statistics dump/restore has become stable. No commitfest entry ple

Re: Test to dump and restore objects left behind by regression

2025-04-04 Thread Alvaro Herrera
On 2025-Apr-03, Andres Freund wrote: > I've increased the timeout even further, but I can't say that I am happy about > the slowest test getting even slower. Adding test time in the serially slowest > test is way worse than adding the same time in a concurrent test. Yeah. We discussed strategies

Re: why there is not VACUUM FULL CONCURRENTLY?

2025-04-04 Thread Alvaro Herrera
On 2025-Apr-01, Antonin Houska wrote: > Besides that, it occurred to me that 0005 ("Preserve visibility > information of the concurrent data changes.") will probably introduce > significant overhead. The problem is that the table we're repacking is > treated like a catalog, for reorderbuffer.c to

Re: Modern SHA2- based password hashes for pgcrypto

2025-04-03 Thread Alvaro Herrera
On 2025-Mar-11, Bernd Helmle wrote: > Please find attached v4 of this patch. I added the following changes: > > - Check for non-supported characters in the salt like passlib does. > - Check for reserved tokens when parsing the salt string (i find this > very strict, but it covers the cases Japin

Re: Test to dump and restore objects left behind by regression

2025-04-03 Thread Alvaro Herrera
On 2025-Apr-03, Ashutosh Bapat wrote: > Looks like the problem is in the test itself as pointed out by Jeff in > [1]. PFA patch fixing the test and enabling statistics back. Thanks, pushed. > A note about variable name changes and introduction of new variables. > We run step 2 between 1 and 3 so

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-03 Thread Alvaro Herrera
On 2025-Apr-03, Peter Eisentraut wrote: > It occurred to me that we will also want to have NOT NULL NOT ENFORCED > constraints eventually. As we have discussed elsewhere, the NOT > ENFORCED state is closely related to the NOT VALID state. So that > should probably be considered in the design her

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-02 Thread Alvaro Herrera
On 2025-Apr-02, jian he wrote: > we need special code for handing parent is invalid, child is valid > (table inheritance or partitioning). H. I'm going to focus on this case, which is the simplest one we care about (no multi-level hierarchy, only not null constraint): create table singlepp

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-02 Thread Alvaro Herrera
On 2025-Mar-31, Robert Haas wrote: > It seems like a bad idea to make conislocal and coninhcount have > anything to do with whether the constraint is valid. We need those to > mean what they have traditionally meant just to make the correct > things happen when the constraint is dropped, either di

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-02 Thread Alvaro Herrera
Hello, thanks for the review. On 2025-Apr-02, jian he wrote: > the following are reviews of changes in pg_dump > on v6-0001-NOT-NULL-NOT-VALID.patch > > minor style tweak: > + "CASE WHEN NOT co.convalidated THEN co.oid" > + " ELSE NULL END AS notnull_invalidoid,\n" > > align with surrounding co

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-04-01 Thread Alvaro Herrera
On 2025-Mar-28, jian he wrote: > ATPrepAddPrimaryKey > + if (!conForm->convalidated) > + ereport(ERROR, > + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), > + errmsg("not-null constraint \"%s\" of table \"%s\" has not been validated", > + NameStr(conForm->conname), > + RelationGetRelationN

Re: Test to dump and restore objects left behind by regression

2025-04-01 Thread Alvaro Herrera
On 2025-Apr-01, Ashutosh Bapat wrote: > Just today morning, I found something which looks like another bug in > statistics dump/restore [1]. As Daniel has expressed upthread [2], we > should go ahead and commit the test even if the bug is not fixed. But > in case it creates a lot of noise and make

Re: Test to dump and restore objects left behind by regression

2025-03-31 Thread Alvaro Herrera
On 2025-Mar-31, Daniel Gustafsson wrote: > Given where we are in the cycle, it seems to make sense to stick to using the > schedule we already have rather than invent a new process for generating it, > and work on that for 19? No objections to that. I'll see about getting this committed during m

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-31 Thread Alvaro Herrera
On 2025-Mar-31, jian he wrote: > hi. > in notnull-notvalid.patch > > + if (coninfo->contype == 'c') > + keyword = "CHECK CONSTRAINT"; > + else > + keyword = "INVALID NOT NULL CONSTRAINT"; > we have a new TocEntry->desc kind. Yeah, I wasn't sure that this change made much actual sense. I think i

Re: Reducing memory consumed by RestrictInfo list translations in partitionwise join planning

2025-03-28 Thread Alvaro Herrera
On 2025-Mar-28, David Rowley wrote: > I experimented by applying your v4 along with 0001-0003 of Yuya's v35 > patchset from [2]. See the attached bz2 for my results run on an AMD > Zen2 machine. The CREATE TABLE statement is in the attached script. Eyeballing these results, unless I am misreadin

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-28 Thread Alvaro Herrera
On 2025-Mar-28, jian he wrote: > i think your patch messed up with pg_constraint.conislocal. > for example: > > CREATE TABLE parted (id bigint default 1,id_abc bigint) PARTITION BY LIST > (id); > alter TABLE parted add CONSTRAINT dummy_constr not null id not valid; > CREATE TABLE parted_1 (id bi

Re: Test to dump and restore objects left behind by regression

2025-03-28 Thread Alvaro Herrera
On 2025-Mar-28, Tom Lane wrote: > I think instead of going this direction, we really need to create a > separately-purposed script that simply creates "one of everything" > without doing anything else (except maybe loading a little data). > I believe it'd be a lot easier to remember to add to that

Re: Test to dump and restore objects left behind by regression

2025-03-28 Thread Alvaro Herrera
On 2025-Mar-28, Ashutosh Bapat wrote: > No, that's losing some information like default installation and the > same version. You don't need to preserve such information. This is just a test name. People looking for more details can grep for the name and they will find the comments. -- Álvaro H

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-27 Thread Alvaro Herrera
On 2025-Mar-24, jian he wrote: > hi. > you may like the attached. it's based on your idea: attnotnullvalid. This is quite close to what I was thinking, yeah. I noticed a couple of bugs however, and ended up cleaning up the whole thing. Here's what I have so far. I'm not sure the pg_dump bits a

Re: Test to dump and restore objects left behind by regression

2025-03-27 Thread Alvaro Herrera
On 2025-Mar-27, Ashutosh Bapat wrote: > On Thu, Mar 27, 2025 at 6:01 PM vignesh C wrote: > > Couple of minor thoughts: > > 1) I felt this error message is not conveying the error message correctly: > > + if ($src_node->pg_version != $dst_node->pg_version > > + or defined $src

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

2025-03-25 Thread Alvaro Herrera
Hello On 2025-Mar-25, Peter Eisentraut wrote: > A patch in the NOT ENFORCED constraints patch series proposes to refactor > some of the code added by this patch series ([0] patch v18-0001). I noticed > that the code paths from this patch series do not call > InvokeObjectPostAlterHook() or CacheI

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-24 Thread Alvaro Herrera
On 2025-Mar-24, Robert Haas wrote: > I mean, maybe there's an argument that some changes are more > disruptive than others. For instance, if removing attndims would force > drivers to run extra more complicated queries to learn whether a > certain type is an array type, one could argue that taking

Re: Test to dump and restore objects left behind by regression

2025-03-24 Thread Alvaro Herrera
On 2025-Mar-24, Ashutosh Bapat wrote: > One concern I have with directory format is the dumped database is not > readable. This might make investigating a but identified the test a > bit more complex. Oh, it's readable all right. You just need to use `pg_restore -f-` to read it. No big deal.

Re: Test to dump and restore objects left behind by regression

2025-03-21 Thread Alvaro Herrera
On 2025-Mar-21, Ashutosh Bapat wrote: > I used the same parallelism in pg_restore and pg_dump too. And your > numbers seem to be similar to mine; slightly less than 20% slowdown. > But is that slowdown acceptable? From the earlier discussions, it > seems the answer is No. Haven't heard otherwise.

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-21 Thread Alvaro Herrera
On 2025-Mar-21, Robert Haas wrote: > I don't agree with this conclusion. Uhm. > The 8.3 casting changes were problematic because any piece of SQL > you'd ever written could have problems. Okay, this much I agree with. > This change will only break queries that look at the attnotnull > column.

Re: Test to dump and restore objects left behind by regression

2025-03-21 Thread Alvaro Herrera
I passed PROVE_FLAGS="--timer -v" to get the timings and run under --format=directory. Without new test: ok23400 ms ( 0.00 usr 0.00 sys + 2.84 cusr 1.53 csys = 4.37 CPU) ok23409 ms ( 0.00 usr 0.01 sys + 2.81 cusr 1.53 csys = 4.35 CPU) With new test, under --format=directory: -j2

Re: Test to dump and restore objects left behind by regression

2025-03-21 Thread Alvaro Herrera
On 2025-Mar-21, Ashutosh Bapat wrote: > On Thu, Mar 20, 2025 at 8:37 PM vignesh C wrote: > > Should the copyright be only 2025 in this case: > The patch was posted in 2024 to this mailing list. So we better > protect the copyright since then. I remember a hackers discussion > where a senior mem

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-20 Thread Alvaro Herrera
On 2025-Mar-20, jian he wrote: > > Is it expected that a child may have VALID constraint but parent has > > not valid constraint? > > but the MergeConstraintsIntoExisting logic is when > ALTER TABLE ATTACH PARTITION, > it expects the child table to also have an equivalent constraint > definition o

Re: Test to dump and restore objects left behind by regression

2025-03-20 Thread Alvaro Herrera
On 2025-Mar-20, vignesh C wrote: > Will it help the execution time if we use --jobs in case of pg_dump > and pg_restore wherever supported: As I said in another thread, I think we should enable this test to run without requiring any PG_TEST_EXTRA, because otherwise the only way to know about prob

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-20 Thread Alvaro Herrera
On 2025-Mar-20, jian he wrote: > as you can see the output of `\d+ notnull_tbl1` > That means the pg_attribute.attnotnull definition is changed. That's correct, it changed in that way. I propose for the new docs: > > >attnotnull bool > > >This column h

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-20 Thread Alvaro Herrera
Hello On 2025-Mar-20, Rushabh Lathia wrote: > Attached is another version of the patch (WIP), where I have > introduced a new catalog column, pg_attribute.attinvalidnotnull > (boolean). This column will default to FALSE but will be set to TRUE > when an INVALID NOT NULL constraint is created. Wi

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-17 Thread Alvaro Herrera
On 2025-Mar-17, jian he wrote: > hi. > I played around with it. > > current syntax, we don't need to deal with column constraint grammar. > like the following can fail directly: > create table t0(a int constraint nn not null a not valid); > we only support table constraint cases like: > alter tab

Re: Test to dump and restore objects left behind by regression

2025-03-15 Thread Alvaro Herrera
Hello When running these tests, I encounter this strange diff in the dumps, which seems to be that the locale for type money does not match. I imagine the problem is that the locale is not set correctly when initdb'ing one of them? Grepping the regress_log for initdb, I see this: $ grep -B1 'Ru

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-15 Thread Alvaro Herrera
On 2025-Mar-10, Rushabh Lathia wrote: > I adjusted the set_attnotnull() API and removed the added > queue_validation parameter. Rather, the function start using wqueue > input parameter as a check. > If wqueue is NULL, skip the queue_validation. Attaching patch here, > but not sure how clear it

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-15 Thread Alvaro Herrera
On 2025-Mar-12, Ashutosh Bapat wrote: > If the test passes for you, can you please try the patches at [1] on > top of your patches? Please apply those, set and export environment > variable PG_TEST_EXTRA=regress_dump_test, and run 002_pg_upgrade test? > I intended to do this but can not do it sinc

Re: Test to dump and restore objects left behind by regression

2025-03-13 Thread Alvaro Herrera
Hello On 2025-Mar-13, Ashutosh Bapat wrote: > 1. can you please run the test again and share the dump outputs. They > will be located in a temporary directory with names > src_dump.sql_adjusted and dest_dump..sql_adjusted. Ah, I see the problem :-) The first initdb does this: # Running

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-12 Thread Alvaro Herrera
On 2025-Mar-12, Rushabh Lathia wrote: > Hi Alvaro, > > Here are the latest patches, which includes the regression fix. Thank you. Taking a step back after discussing this with some colleagues, I need to contradict what I said at the start of this thread. There's a worry that changing pg_attrib

Re: Test to dump and restore objects left behind by regression

2025-03-12 Thread Alvaro Herrera
On 2025-Mar-12, Ashutosh Bapat wrote: > Does the test pass for you if you don't apply my patches? Yes. It also passes if I keep PG_TEST_EXTRA empty. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-03-12 Thread Alvaro Herrera
On 2025-Mar-12, Ashutosh Bapat wrote: > The 002_pg_upgrade test passes with and without my patches now. But > then the tests added here do not leave behind any parent-child table. > Previously we have found problems in dumping and restoring constraints > in an inheritance hierarchy. I think the te

Re: refactor AlterDomainAddConstraint (alter domain add constraint)

2025-03-10 Thread Alvaro Herrera
Hello, On 2025-Jan-15, jian he wrote: > we cannot error out AlterDomainAddConstraint for cases like ALTER > DOMAIN ADD CHECK NO INHERIT. > because "NO INHERIT" is actually a separate Constraint Node, and > AlterDomainAddConstraint > can only handle one Constraint node. I had forgotten this threa

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

2025-03-05 Thread Alvaro Herrera
On 2025-Mar-03, Suraj Kharage wrote: > Thanks Alvaro for the review and fixup patch. > > I agree with your changes and merged that into the main patch along with a > couple of other changes. > > Please find attached v6 for further review. Thanks, I have pushed this. I made some changes to the

Re: why there is not VACUUM FULL CONCURRENTLY?

2025-03-03 Thread Alvaro Herrera
On 2025-Feb-26, Antonin Houska wrote: > @@ -403,39 +381,38 @@ cluster_rel(Relation OldHeap, Oid indexOid, > ClusterParams *params) >* would work in most respects, but the index would only get marked as >* indisclustered in the current database, leading to unexpected > behavior >

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

2025-02-28 Thread Alvaro Herrera
On 2025-Feb-21, Suraj Kharage wrote: > Thanks, Alvaro. > > I have revised the patch as per your last update. > Please find attached v5 for further review. Hello I noticed two issues. One is that we are OK to modify a constraint that's defined in our parent, which breaks everything. We can onl

Re: Psql meta-command conninfo+

2025-02-25 Thread Alvaro Herrera
On 2025-Feb-22, Alvaro Herrera wrote: > Also, there's a bunch of "(char *)" casts that are 100% due to > printTableAddCell() taking a char * instead of const char * for the cell > value. That seems a bit silly, we should change that. Ah, but the problem is that most

Re: Psql meta-command conninfo+

2025-02-22 Thread Alvaro Herrera
On 2025-Feb-21, Sami Imseih wrote: > > If we want to include 'role' in this output, what I'd propose is to > > have \conninfo issue "SHOW role", which is accepted by every server > > version. If it fails (say because we're in an aborted transaction), > > just omit that row from the output. > > v

Re: Psql meta-command conninfo+

2025-02-21 Thread Alvaro Herrera
I suggest the attached, which gets 99% there with 10% of the complexity, and has \conninfo (no plus sign) output this: Connection Information Parámetro │ Valor ───┼ Base de Datos │ alvherre Client Use

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-02-21 Thread Alvaro Herrera
On 2025-Feb-21, Alvaro Herrera wrote: > I see a nonrepeatable problem under valgrind which I'm going to look > into. Sorry, pilot error. The pg_upgrade test works fine under valgrind. -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-02-21 Thread Alvaro Herrera
On 2025-Feb-21, Ashutosh Bapat wrote: > If I apply your patches, build binaries, I see failure. I reverted > your patches, built binaries, I don't see failure. I apply your > patches again, built binaries, it fails again. I can't reproduce the problem either. Are you running asserts disabled or

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-02-20 Thread Alvaro Herrera
Hello, Thanks! I noticed a typo 'constrint' in several places; fixed in the attached. I discovered that this sequence, taken from added regression tests, CREATE TABLE notnull_tbl1 (a int); ALTER TABLE notnull_tbl1 ADD CONSTRAINT nn_parent not null a not valid; CREATE TABLE notnull_chld (a int);

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

2025-02-19 Thread Alvaro Herrera
On 2025-Feb-10, Suraj Kharage wrote: > Thanks, Alvaro, for the review. > > I have addressed your comments per the above suggestions in the attached v4 > patch. Okay, thanks. It looks good to me, but I realized a few days ago that this patch affects the same code as the patch from Amul Sul to ch

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

2025-02-13 Thread Alvaro Herrera
On 2025-Feb-12, Sami Imseih wrote: > Greg S. Mullane wrote: > > > I agree fingerprint is the right final word. But "jumble" conveys > > the *process* better than "fingerprinting". I view it as jumbling > > produces an object that can be fingerprinted. > > hmm, "jumble" describes something that i

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

2025-02-12 Thread Alvaro Herrera
On 2025-Feb-12, Julien Rouhaud wrote: > On Wed, Feb 12, 2025 at 01:57:47PM +0100, Alvaro Herrera wrote: > > Anyway, I think that's different. We do support compute_query_id=off as > > a way for a custom module to compute completely different query IDs > > using th

Re: [PATCH] Optionally record Plan IDs to track plan changes for a query

2025-02-12 Thread Alvaro Herrera
On 2025-Feb-12, Julien Rouhaud wrote: > > FWIW, I think options to tweak queryId computation is something that > > should be in core. It was discussed earlier in the context of IN > > list merging; the patch for this currently has the guc for the > > feature in pg_stat_statements, but there was a

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-02-07 Thread Alvaro Herrera
Recursion to child tables is incorrectly trying to locate the constraint by name: create table notnull_tbl1 (a int); alter table notnull_tbl1 add constraint foo not null a not valid; create table notnull_chld (a int); alter table notnull_chld add constraint blah not null a not valid; alter table

Re: Modern SHA2- based password hashes for pgcrypto

2025-02-07 Thread Alvaro Herrera
On 2025-Feb-07, Japin Li wrote: > Since there is no standard, how do we handle this? I prefer to use > the strict mode like passlib. I definitely like that passlib have documented their thought process thoroughly. I think using their strict mode is good on principle, but if we're going to do th

Re: Support NOT VALID / VALIDATE constraint options for named NOT NULL constraints

2025-02-06 Thread Alvaro Herrera
Hello Rushabh, On 2025-Feb-06, Rushabh Lathia wrote: > Commit 14e87ffa5c543b5f30ead7413084c25f7735039f > > added the support for named NOT NULL constraints. We can now support > the NOT VALID/VALID named NOT

Re: Modern SHA2- based password hashes for pgcrypto

2025-02-06 Thread Alvaro Herrera
On 2025-Jan-28, Bernd Helmle wrote: > Python's passlib is very strict when it comes to supported characters > within a salt string. It rejects everything thats not matching '[./0- > 9A-Za-z]'. So when you provide the example above you get The reason it uses these chars is that in their scheme the

Re: Test to dump and restore objects left behind by regression

2025-02-06 Thread Alvaro Herrera
On 2025-Feb-06, Michael Paquier wrote: > On Wed, Feb 05, 2025 at 03:28:04PM +0900, Michael Paquier wrote: > > Hmm. I was reading through the patch and there is something that > > clearly stands out IMO: the new compare_dumps(). It is in Utils.pm, > > and it acts as a wrapper of `diff` with its f

Re: Support for NO INHERIT to INHERIT state change with named NOT NULL constraints

2025-02-04 Thread Alvaro Herrera
On 2025-Jan-13, Suraj Kharage wrote: > Please find attached revised version of patch which added the INHERIT to NO > INHERIT state change for not null constraint. Thanks! I find the doc changes a little odd. First, you seem to have added a [INHERIT/NO INHERIT] flag in the wrong place (line 112)

Re: NOT ENFORCED constraint feature

2025-02-04 Thread Alvaro Herrera
On 2025-Feb-04, Peter Eisentraut wrote: > On 03.02.25 08:50, Alvaro Herrera wrote: > > On 2025-Feb-03, Ashutosh Bapat wrote: > > > > > VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation > > > required, constraint is enforced > > There&

Re: NOT ENFORCED constraint feature

2025-02-03 Thread Alvaro Herrera
On 2025-Feb-03, Ashutosh Bapat wrote: > ``` > If the > constraint is NOT ENFORCED, the database system will > not check the constraint. It is then up to the application code to > ensure that the constraints are satisfied. The database system might > still assume tha

Re: NOT ENFORCED constraint feature

2025-02-02 Thread Alvaro Herrera
On 2025-Feb-03, Ashutosh Bapat wrote: > VALID, NOT ENFORCED changed to VALID, ENFORCED - data validation > required, constraint is enforced There's no such thing as a VALID NOT ENFORCED constraint. It just cannot exist. > NOT VALID, NOT ENFORCED changed to NOT_VALID, ENFORCED - no data > valida

Re: why there is not VACUUM FULL CONCURRENTLY?

2025-02-02 Thread Alvaro Herrera
> From bf2ec8c5d753de340140839f1b061044ec4c1149 Mon Sep 17 00:00:00 2001 > From: Antonin Houska > Date: Mon, 13 Jan 2025 14:29:54 +0100 > Subject: [PATCH 4/8] Add CONCURRENTLY option to both VACUUM FULL and CLUSTER > commands. > @@ -950,8 +1412,46 @@ copy_table_data(Relation NewHeap, Relation

Re: NOT ENFORCED constraint feature

2025-01-31 Thread Alvaro Herrera
On 2025-Jan-31, Ashutosh Bapat wrote: > But if the constraint is NOT VALID and later marked as NOT ENFORCED, > what is expected behaviour while changing it to ENFORCED? I think what you want is a different mode that would be ENFORCED NOT VALID, which would be an extension of the standard, because

Re: why there is not VACUUM FULL CONCURRENTLY?

2025-01-31 Thread Alvaro Herrera
On 2025-Jan-31, Antonin Houska wrote: > Matthias van de Meent wrote: > > First, due to the XLog-based change detection this feature can't work > > for unlogged tables without first changing them to logged (which > > implies first writing the whole table to XLog, to not cause issues on > > any re

  1   2   3   4   5   6   7   8   9   10   >