Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

e: att = TupleDescAttr(RelationGetDescr(partRel), attmap->attnums[conkey[i] - 1] - 1); We should use confkey[i] -1 not conkey[i] - 1; In this case, the length of attmap is 2, conkey= {2,3}, confkey={1,2}, attmap->attnums[conkey[1] -1] will trigger memory invalid

Re: execute prepared statement passing parameter expression with COLLATE clause

QUERY PLAN - Seq Scan on pktable (cost=0.00..27.00 rows=7 width=32) Filter: (x = 'Å'::text COLLATE ignore_accents) (2 rows) The filter expr in the two queries is different. And I debug the texteq; the collid is also different. So the result of the two queries is different. I don't look execute more in details. -- Thanks, Tender Wang

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

jian he 于2024年10月29日周二 14:15写道： > On Thu, Oct 24, 2024 at 3:01 PM Tender Wang wrote: > > > > I feel that it's hard only to use one struct(for example, X), which just > calls equal(X, expr) > > can check both the expression match and the collation match. > >

Fix a typo in the comment of gistdoinsert()

Hi, I found a typo in the comment of gistdoinsert() when I read the codes. See the attached patch. -- Thanks, Tender Wang From 9c614b424418d451fb375294733b0cdc3740a1b7 Mon Sep 17 00:00:00 2001 From: Tender Wang Date: Mon, 4 Nov 2024 22:28:24 +0800 Subject: [PATCH] Fix a typo in the comment of

Remove an obsolete comment in gistinsert()

emoved together. By the way, after commit 5edb24a, the comments in gistbuildCallback() was removed. So I think we can now remove the obsolete comment from gistinsert(). -- Thanks, Tender Wang 0001-Remove-an-obsolete-comment-in-gistinsert.patch Description: Binary data

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

d_att->natts of pt is 2. It will not go into tp1 because tp1 is a partition of pt. But after detach, the rel->rd_att->natts of tp1 is 1, so "ERROR: invalid attribute number 2" will report. CREATE TABLE tp1... will ignore the dropped column of parent, so the natts of tp1 is 1, but its parent is 2. -- Thanks, Tender Wang

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Alvaro Herrera 于2024年10月25日周五 16:30写道： > On 2024-Oct-25, Tender Wang wrote: > > > Thanks for reporting. I can reproduce this memory invalid access on HEAD. > > After debuging codes, I found the root cause. > > In DetachPartitionFinalize(), below code: > > att =

Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

me, I was unsure if it was the correct fix. Until I find this thread issue, I think we should do it this way. In the attached patch, I include this thread test and [1] test case. I have two questions in my head: 1. Does partition pruning logic still check the collation match with this patch.

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Alvaro Herrera 于2024年10月28日周一 17:16写道： > On 2024-Oct-27, Tender Wang wrote: > > > BTW, while reviewing the v2 patch, I found the parentConTup in > > foreach(cell, fks) block > > didn't need it anymore. We can remove the related codes. > > True. Done so in thi

Re: [BUG] Fix DETACH with FK pointing to a partitioned table fails

Alvaro Herrera 于2024年10月27日周日 05:47写道： > On 2024-Oct-25, Alvaro Herrera wrote: > > > On 2024-Oct-25, Tender Wang wrote: > > > > > Thanks for reporting. I can reproduce this memory invalid access on > HEAD. > > > After debuging codes, I found the root c

Re: Remove a unnecessary backslash in CopyFrom

this is not in a macro. > I've attached a patch. > Yeah, agree. The patch LGTM. -- Thanks, Tender Wang

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Tender Wang 于2024年10月23日周三 21:48写道： > Hi all, > > > I think the root cause of this thread and [1] are same. We don't use the > Partition Key collation but column's > collation to fill the RelOptInfo partexprs field in > set_baserel_partition_key_exprs(). >

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Amit Langote 于2024年11月1日周五 15:27写道： > Hi, > > On Wed, Oct 23, 2024 at 10:48 PM Tender Wang wrote: > > I think the root cause of this thread and [1] are same. We don't use > the Partition Key collation but column's > > collation to fill

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

x27;s not > the same as partcoll. > Me, too. > > Attached 0002 is what I came up with. One thing that's different from > what Jian proposed is that match_expr_to_partition_keys() returns -1 > Agree. (expr not matched to any key) when the collation is also not matched > instead of using a separate output parameter for that. > In have_partkey_equi_join() ... if (exprs_known_equal(root, expr1, expr2, btree_opfamily)) { Oid partcoll2 = rel1->part_scheme->partcollation[ipk]; I think we should use rel2 here, not rel1. -- Thanks, Tender Wang

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

wise grouping is not the same as when > enable_partitionwise_aggregate is off. > LGTM -- Thanks, Tender Wang

Re: not null constraints, again

wording might need work. > The indexing test case in regress failed with v4 patch. alter table only idxpart add primary key (a); -- fail, no not-null constraint -ERROR: column a of table idxpart0 is not marked not null +ERROR: column "a" of table "idxpart0" is not marked NOT NULL It seemed the error message forgot to change. -- Thanks, Tender Wang https://www.openpie.com/

Re: not null constraints, again

his made me realize that there is a more general problem, to > wit, that pg_dump is not dumping not-null constraint names correctly -- > sometimes they just not dumped, which is Not Good. I'll have to look > into that once more. > > > (Also: there are still a few additional test stanzas in regress/ that > ought to be removed; also, I haven't re-tested sepgsql, so it's probably > broken ATM.) > > -- > Álvaro Herrera PostgreSQL Developer — > https://www.EnterpriseDB.com/ > -- Thanks, Tender Wang

Re: not null constraints, again

create table cc3() inherits(pp1,cc1,cc2); > > alter table pp1 alter f1 set not null; > ERROR: tuple already updated by self > I guess some place needs call CommandCounterIncrement(). -- Thanks, Tender Wang

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Amit Langote 于2024年10月24日周四 14:33写道： > Hi, > > On Thu, Oct 24, 2024 at 1:46 PM Tender Wang wrote: > > Tender Wang 于2024年10月23日周三 21:48写道： > >> > >> Hi all, > >> > >> I find another issue as $SUBJECT when I work on [1]. > > > > W

Re: Wrong result when enable_partitionwise_join is on if collation of PartitionKey and Column is different.

Tender Wang 于2024年10月23日周三 21:48写道： > Hi all, > > I find another issue as $SUBJECT when I work on [1]. > When I continue to work on this, I find below issue. But I'm not sure whether it is a bug. postgres=# create table part_index(a text primary key) partition by list (

Unsafe access BufferDescriptors array in BufferGetLSNAtomic()

local or shared buffer. If the buffer is local, then buffer < 0; it will be cast to uint32 when passed to GetBufferDescriptor(). This may be unsafe, although no someone reports the problem. I tweak a few codes; see the attached patch. Any thoughts? -- Thanks, Tender Wang 0001-Fix-unsaf

Re: Remove an obsolete comment in gistinsert()

Michael Paquier 于2024年11月7日周四 14:11写道： > On Thu, Nov 07, 2024 at 10:57:08AM +0800, Tender Wang wrote: > > Thanks for reviewing this. I have added it to the 2015-01 commitfest. > > Right. I don't quite see why this comment would apply anymore, and > the commit you are

Re: Remove an obsolete comment in gistinsert()

NULL /* size is currently bogus */, isnull); > + values, isnull, true /* size is currently bogus */); > ``` > > I checked the rest of gistFormTuple() calls and also looked for other > comments like this. There seems to be only one call like this to fix. > Thanks for reviewing this. I have added it to the 2015-01 commitfest. -- Thanks, Tender Wang

Re: Remove an unnecessary check on semijoin_target_ok() on postgres_fdw.c

Alexander Pyhalov 于2024年11月29日周五 00:02写道： > Tender Wang писал(а) 2024-10-09 10:26: > > Hi, > >When I debug FDW join pushdown codes, I found below codes in > > semijoin_target_ok(): > > if (bms_is_member(var->varno, innerrel->relids) && > >

Re: Tweak some codes format in gist.c

Andrey M. Borodin 于2024年11月15日周五 17:57写道： > > > > On 15 Nov 2024, at 14:48, Tender Wang wrote: > > > > Hi, > > > > I found some inconsistent code format in gist.c. > > See the attached. > > I’d suggest to combine such changes with some real co

Tweak some codes format in gist.c

Hi, I found some inconsistent code format in gist.c. See the attached. -- Thanks, Tender Wang 0001-Fix-some-codes-format-in-gist.c.patch Description: Binary data

Re: Unsafe access BufferDescriptors array in BufferGetLSNAtomic()

andled. > Thanks for reviewing. The BufferGetLSNAtomic() with this patch looks not complex. I think no need more explanation here. > Best regards, > [Xuneng] > > The new status of this patch is: Waiting on Author > I change the status to Ready for commiter -- Thanks, Tender Wang

Re: Tweak some codes format in gist.c

t; There is one more in trgm.h, though it's OK as-is, as well. > -- > Michael > Thanks for pushing. -- Thanks, Tender Wang

Re: Adjust tuples estimate for appendrels

ded > * because some places assume rel->tuples is valid for any baserel. > */ > > I wonder if this assumption still holds today. If we create an empty > table, analyze it, and then use it in a query, the table will have > rel->tuples set to zero and rel->rows set to one, which doesn't cause > any issues today. > > [1] > https://postgr.es/m/cambws4-ocromekmtvdh3rbmuajqaqdk0qi4k6zouvponmwz...@mail.gmail.com > > Thanks > Richard > -- Thanks, Tender Wang

Re: Removing unneeded self joins

Alexander Korotkov 于2025年4月6日周日 19:50写道： > On Sun, Apr 6, 2025 at 2:42 PM Tender Wang wrote: > > Daniel Gustafsson 于2025年4月6日周日 19:23写道： > >> > >> > On 6 Apr 2025, at 06:02, Tender Wang wrote: > >> > >> > I find t

Re: Removing unneeded self joins

Daniel Gustafsson 于2025年4月6日周日 19:23写道： > > On 6 Apr 2025, at 06:02, Tender Wang wrote: > > > I find that the postgresql.conf.sample file doesn't contain > enable_self_join_elimination guc. > > If this is necessary, please see the attached patch. > > The GUC

Re: Removing unneeded self joins

Hi, I find that the postgresql.conf.sample file doesn't contain enable_self_join_elimination guc. If this is necessary, please see the attached patch. -- Thanks, Tender Wang From f27c99aebbd07d4008173492c7913749b149b540 Mon Sep 17 00:00:00 2001 From: Tender Wang Date: Sun, 6 Apr 2025 11:

Re: Remove an unnecessary check on semijoin_target_ok() on postgres_fdw.c

Alexander Korotkov 于2025年3月25日周二 18:57写道： > On Fri, Nov 29, 2024 at 3:39 AM Tender Wang wrote: > > Alexander Pyhalov 于2024年11月29日周五 00:02写道： > >> > >> Tender Wang писал(а) 2024-10-09 10:26: > >> > Hi, > >> >When I de

Re: Reduce "Var IS [NOT] NULL" quals during constant folding

ms is zero-based set containing attnums of NOT NULL * columns. But in get_relation_notnullatts(): rte->notnullattnums = bms_add_member(rte->notnullattnums, i + 1); The notnullattnums seem to be 1-based. -- Thanks, Tender Wang

Re: Consistently use macro HeapTupleIsValid to check the validity of tuples in tablecmds.c

patterns mean the same thing. > OK, makes sense. -- Thanks, Tender Wang

Re: Typo in comment for pgstat_database_flush_cb()

Etsuro Fujita 于2025年3月30日周日 18:28写道： > Another thing I noticed is $SUBJECT: I think “if lock could not > immediately acquired” should be “if lock could not be immediately > acquired”. Attached is a patch for that. > Agree. The patch looks good to me. -- Thanks, Tender Wang

Re: [PATCH] Fix potential overflow in binary search mid calculation

flow, we should use the alternative calculation method: > > uint16 mid = min + (max - min) / 2; > > This approach ensures that (max - min) does not exceed 65535, preventing > the addition from overflowing while still correctly computing the middle > index. > Patch > > A patch implementing this fix is attached. > -- Thanks, Tender Wang

Re: bug when apply fast default mechanism for adding new column over domain with default value

CommandCounterIncrement(). So let's add CommandCounterIncrement() after calling StoreAttrMissingVal(). -- Thanks, Tender Wang From ee2bc290cab83daf0ebecf9f29cc23539ba19741 Mon Sep 17 00:00:00 2001 From: Tender Wang Date: Wed, 2 Apr 2025 09:52:22 +0800 Subject: [PATCH] Fix &quo

Consistently use macro HeapTupleIsValid to check the validity of tuples in tablecmds.c

the second formats to the third one, e.g., using HeapTupleIsValid. BTW, I searched the other files, some files also have different ways to check the validity of tuples. But the attached patch only focuses on tablecmds.c Any thoughts? -- Thanks, Tender Wang From

Re: not null constraints, again

's not, we should fix it. BTW, the document doesn't state the order in which the commands are executed when users specify more than one manipulation in a single ALTER TABLE command. -- Thanks, Tender Wang

Re: not null constraints, again

t; 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. Others look good for me. -- Thanks, Tender Wang

Typos in the comment for the estimate_multivariate_ndistinct()

tch for that. -- Thanks, Tender Wang From 55ac0d72303b7ea431d58cbbc242dc5215e13102 Mon Sep 17 00:00:00 2001 From: Tender Wang Date: Mon, 14 Apr 2025 21:28:53 +0800 Subject: [PATCH] Fix a typo --- src/backend/utils/adt/selfuncs.c | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --gi

Re: not null constraints, again

x7ffc0e561eb0) at postmaster.c:3580 #24 0x561cd92afeb1 in ServerLoop () at postmaster.c:1702 #25 0x561cd92af7a2 in PostmasterMain (argc=3, argv=0x561cf1cffb00) at postmaster.c:1400 #26 0x561cd914cf06 in main (argc=3, argv=0x561cf1cffb00) at main.c:227 The alter table session do check its children not-null constraint using lockmod=8, and the copy session do get partition_qual to lock parent using lockmode =1. I wonder if we have to use the same lockmode for checking children's not-null constraint. -- Thanks, Tender Wang

Re: MergeJoin beats HashJoin in the case of multiple hash clauses

Tender Wang 于2025年4月14日周一 14:17写道： > Hi, > > While I debug hashjoin codes, in estimate_multivariate_bucketsize(), I > find that > the list_copy(hashclauses) below is unnecessary if we have a single join > clause. > > List *clauses = list_copy(hashclauses); > ..

Re: Typos in the comment for the estimate_multivariate_ndistinct()

Tender Wang 于2025年4月14日周一 21:34写道： > Hi, > > While reading the estimate_multivariate_ndistinct(), > I think "If a match it found, *varinfos is > * updated to remove the list of matched varinfos" > should be "If a match is found, *varinfos is > * updated to

Re: not null constraints, again

Alvaro Herrera 于2025年4月16日周三 03:12写道： > 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 o

Re: MergeJoin beats HashJoin in the case of multiple hash clauses

some overhead of function calls and memory copies. Any thoughts? -- Thanks, Tender Wang From d4a29af25ef276a145b46ecc16d631909fb1891a Mon Sep 17 00:00:00 2001 From: Tender Wang Date: Mon, 14 Apr 2025 13:53:15 +0800 Subject: [PATCH] Adjust the place of list_copy() in

Re: Typos in the comment for the estimate_multivariate_ndistinct()

Daniel Gustafsson 于2025年4月16日周三 22:20写道： > > On 14 Apr 2025, at 15:34, Tender Wang wrote: > > > > Hi, > > > > While reading the estimate_multivariate_ndistinct(), > > I think "If a match it found, *varinfos is > > * updated to remove the list of

Re: not null constraints, again

Tender Wang 于2025年4月15日周二 11:20写道： > > > Tom Lane 于2025年4月15日周二 05:39写道： > >> Alvaro Herrera writes: >> > On 2025-Apr-14, Tom Lane wrote: >> >> I would not have expected that adding pg_constraint rows implies >> >> stronger locks than what

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

= (1, 1); CREATE TABLE CREATE TABLE INSERT 0 2 ERROR: update or delete on table "test_1" violates foreign key constraint "test_id_1_parent_id_2_fkey1" on table "test" DETAIL: Key (id_1, id_2)=(1, 1) is still referenced from table "test". > Anyway, if people have a chance to give this a look, it would be > helpful. > It's midnight in my time zone. I will look at this tomorrow. -- Thanks, Tender Wang

Re: Unsafe access BufferDescriptors array in BufferGetLSNAtomic()

he code consistent > > > across branches, and this avoids incorrect access patterns when > > > copy-pasting this code. > > > > Hmm, fair point. I will backpatch-through 13. > > Done. > Thanks for pushing. -- Thanks, Tender Wang

Re: Anti join confusion

Richard Guo 于2025年3月3日周一 15:34写道： > On Wed, Feb 26, 2025 at 7:09 PM Tender Wang wrote: > > Recently, I found Greenplum implement pull-up NOT IN subquery. They have > the below comments in their codes: > > > > We normalize NOT subqueries using the following axioms: >

Re: Question about duplicate JSONTYPE_JSON check

sponding value in the > new enum) instead? > The below else branch has code if (tcategory == JSONTYPE_CAST). I guess here the second JSONTYPE_JSON may just be removed. @Amit Langote please check out this. -- Thanks, Tender Wang

Re: generic plans and "initial" pruning

Amit Langote 于2025年2月23日周日 16:36写道： > On Sun, Feb 23, 2025 at 2:03 AM Tender Wang wrote: > > Alexander Lakhin 于2025年2月22日周六 23:00写道： > >> 21.02.2025 05:40, Amit Langote wrote: > >> > >> I pushed the final piece yesterday. > >> > >> &g

Re: generic plans and "initial" pruning

uneinfo created by create_append_plan() is put into the head of global list. So we first process it in ExecDoInitialPruning(). Then error reports because we only contain 1 in estate->es_unpruned_relids. -- Thanks, Tender Wang

Re: Anti join confusion

join2 t2 where T2.ID = 1000); > QUERY PLAN > > > Seq Scan on join1 t1 (cost=4.45..38673.19 rows=630150 width=8) >Filter: (NOT (ANY (id = (hashed SubPlan 1).col1))) >SubPlan 1 > -> Index Only Scan using idx_j2 on join2 t2 (cost=0.42..4.44 rows=1 > width=4) >Index Cond: (id = 1000) > (5 rows) > > Planner now doesn't support pulling up the "NOT IN" sublink. The "NOT IN" sublink will be transformed into SubPlan. -- Thanks, Tender Wang

Re: Anti join confusion

nt, or if > there are other options to consider. > > Recently, I found Greenplum implement pull-up NOT IN subquery. They have the below comments in their codes: We normalize NOT subqueries using the following axioms: * * val NOT IN (subq) => val <> ALL (subq) Richard, do you have an impression about this? -- Thanks, Tender Wang

Re: Add mention in docs about locking all partitions for generic plans

something here it's only > v17 and earlier. > In the case of [1], we still have AccessShareLock on entity_2, even though it is pruned during initial partition pruning. This seems to contradict what you said. "This is no longer true in master" . [1] https://postgr.es/m/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000...@eu-west-1.amazonses.com -- Thanks, Tender Wang

Re: Add mention in docs about locking all partitions for generic plans

David Rowley 于2025年3月24日周一 16:50写道： > On Mon, 24 Mar 2025 at 19:50, Tender Wang wrote: > > > > David Rowley 于2025年3月24日周一 05:28写道： > >> This is no longer true in master, so if we do something here it's only > >> v17 and earlier. > > > > In

Re: Add semi-join pushdown to postgres_fdw

https://www.postgresql.org/message-id/flat/CAHewXN=8aW4hd_W71F7Ua4+_w0=bppuvvtebfbf6g0nusxl...@mail.gmail.com -- Thanks, Tender Wang

Re: bug when apply fast default mechanism for adding new column over domain with default value

Tom Lane 于2025年4月2日周三 23:15写道： > Tender Wang writes: > > So let's add CommandCounterIncrement() after calling > StoreAttrMissingVal(). > > Pushed with minor editorialization. I thought for a bit about whether > the CCI call should be inside StoreAttrMissingVal; b

Re: MERGE issues around inheritance

igned. I try to pass resultRelInfo to ExecInsert, the inherited INSERTed rows are returned by RETURNING. But some test cases in regression failed. -- Thanks, Tender Wang

Re: MERGE issues around inheritance

Tender Wang 于2025年5月25日周日 19:17写道： > > > Álvaro Herrera 于2025年5月24日周六 17:11写道： > >> On 2025-May-21, Andres Freund wrote: >> >> > Hi, >> > >> > In [1] I added some verification to projection building, to check if the >> > tupleslo

Re: MERGE issues around inheritance

Dean Rasheed 于2025年5月26日周一 18:41写道： > On Mon, 26 May 2025 at 07:46, Tender Wang wrote: > > > > Hi Dean, > > > > "it is possible for the parent to be excluded from the > > plan and so all of the entries in the resultRelInfo array may be for > >

Re: MERGE issues around inheritance

Dean Rasheed 于2025年5月26日周一 04:10写道： > On Sun, 25 May 2025 at 13:41, Dean Rasheed > wrote: > > > > On Sun, 25 May 2025 at 13:06, Tender Wang wrote: > > > > > > For a partitioned table, we must pass rootResultRelInfo to > ExecInsert(). I added the check bef

Re: MERGE issues around inheritance

Dean Rasheed 于2025年5月26日周一 04:10写道： > On Sun, 25 May 2025 at 13:41, Dean Rasheed > wrote: > > > > On Sun, 25 May 2025 at 13:06, Tender Wang wrote: > > > > > > For a partitioned table, we must pass rootResultRelInfo to > ExecInsert(). I added the check bef

Re: MERGE issues around inheritance

stResultRel may equal (==) to rootRelation, > in that case, we don't need to call map_variable_attnos? > > + if (rootRelInfo != mtstate->resultRelInfo && + rootRelInfo->ri_RelationDesc->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && + (mtstate->mt_merge_subcommands & MERGE_INSERT) != 0) Above if already does the check. -- Thanks, Tender Wang

<    1   2