Alvaro Herrera writes:
> So it looks like we should suggest to save the output of the query,
> execute each DROP followed by each ADD, and if the latter fails, fix the
> violations and retry the ADD.
Right. I'll make it so -- thanks for doing the legwork on
creating the query!
On 2024-Nov-08, Tom Lane wrote:
> Hmm ... interestingly, if I run this in HEAD's regression database,
> I get
>
> constrained table | constraint | references
> ---+---+-
> clstr_tst | clstr_tst_con | clstr_tst_s
> (1 row)
Eeek.
> So it look
Alvaro Herrera writes:
>> Perhaps one more task for me is to figure out a way to get a list of all
>> the constraints that are broken because of this ... let me see if I can
>> figure that out.
> It's gotta be something like this,
> SELECT conrelid::regclass AS "constrained table",
>conn
On 2024-Nov-06, Alvaro Herrera wrote:
> Perhaps one more task for me is to figure out a way to get a list of all
> the constraints that are broken because of this ... let me see if I can
> figure that out.
It's gotta be something like this,
SELECT conrelid::regclass AS "constrained table",
On 2024-Nov-05, Tom Lane wrote:
> I'm trying to write release notes for commits 53af9491a et al,
> and it seems to me that we need to explain how to get out of
> the mess that would be left behind by the old DETACH code.
> There's no hint about that in the commit message :-(
> Clearly, if you hav
I'm trying to write release notes for commits 53af9491a et al,
and it seems to me that we need to explain how to get out of
the mess that would be left behind by the old DETACH code.
There's no hint about that in the commit message :-(
Clearly, if you have now-inconsistent data, there's little
hel
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 this v3.
>
> I noticed another problem h
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 this v3.
I noticed another problem here: we're grabbing the wrong lock type on
the reference
Alvaro Herrera 于2024年10月25日周五 23:14写道：
> On 2024-Oct-25, Alexander Lakhin wrote:
>
> > I've also discovered another anomaly with a similar setup, but it's not
> > related to 53af9491a.
>
> Hmm, it may well be a preexisting problem, but I do think it involves
> the same code. As far as I can tell
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 cause.
> > > In DetachPartitionFinalize(), be
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 cause.
> > In DetachPartitionFinalize(), below code:
> > att = TupleDescAttr(RelationGetDescr(part
On 2024-Oct-25, Tender Wang wrote:
> When I debug codes, I find that the way to access AttrMap almost uses
> "attrmp_ptr->attnums[offset]."
> The codes now usually don't check if the offset is out of bounds, which
> seems unsafe.
> Can we wrap an access function? For example:
> inline AttrNumber(a
On 2024-Oct-25, Alexander Lakhin wrote:
> I've also discovered another anomaly with a similar setup, but it's not
> related to 53af9491a.
Hmm, it may well be a preexisting problem, but I do think it involves
the same code. As far as I can tell, the value "2" here
> This script ends up with:
> E
Hello Alvaro and Tender Wang,
24.10.2024 17:00, Alexander Lakhin wrote:
Please look at a new anomaly introduced with 53af9491a. When running the
following script:
CREATE TABLE t (a int, b int, PRIMARY KEY (a, b));
CREATE TABLE pt (a int, b int, FOREIGN KEY (a, b) REFERENCES t(a, b))
PARTITION B
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 = TupleDescAttr(RelationGetDescr(par
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 = TupleDescAttr(RelationGetDescr(partRel),
>attmap->attn
Hello Alvaro,
22.10.2024 17:32, Alvaro Herrera wrote:
Yeah. I pushed these patches finally, thanks!
Please look at a new anomaly introduced with 53af9491a. When running the
following script:
CREATE TABLE t (a int, b int, PRIMARY KEY (a, b));
CREATE TABLE pt (a int, b int, FOREIGN KEY (a, b) R
Alexander Lakhin 于2024年10月24日周四 22:00写道：
> Hello Alvaro,
>
> 22.10.2024 17:32, Alvaro Herrera wrote:
> > Yeah. I pushed these patches finally, thanks!
>
> Please look at a new anomaly introduced with 53af9491a. When running the
> following script:
> CREATE TABLE t (a int, b int, PRIMARY KEY (a,
Alexander Lakhin 于2024年10月24日周四 22:00写道：
> Hello Alvaro,
>
> 22.10.2024 17:32, Alvaro Herrera wrote:
> > Yeah. I pushed these patches finally, thanks!
>
> Please look at a new anomaly introduced with 53af9491a. When running the
> following script:
> CREATE TABLE t (a int, b int, PRIMARY KEY (a,
On Tue, 22 Oct 2024 16:32:33 +0200
Alvaro Herrera wrote:
> On 2024-Oct-22, Jehan-Guillaume de Rorthais wrote:
>
> > On Fri, 18 Oct 2024 16:50:59 +0200
> > Alvaro Herrera wrote:
>
> > > For branch 14, I opted to make it delete the constraints on detach.
> > > This isn't ideal but unless someb
On Mon, 21 Oct 2024 23:52:18 +0200
Alvaro Herrera wrote:
> On 2024-Oct-21, Tender Wang wrote:
>
> > I suspect that we don't need the below if
> > statement anymore.
> > /*
> > * If the referenced side is partitioned (which we know because our
> > * parent's constraint points to a different relat
On 2024-Oct-22, Jehan-Guillaume de Rorthais wrote:
> On Fri, 18 Oct 2024 16:50:59 +0200
> Alvaro Herrera wrote:
> > For branch 14, I opted to make it delete the constraints on detach.
> > This isn't ideal but unless somebody wants to spend a lot more time on
> > this, it seems the best we can do
On Fri, 18 Oct 2024 16:50:59 +0200
Alvaro Herrera wrote:
> On 2024-Sep-26, Jehan-Guillaume de Rorthais wrote:
>
> > REL_14_STABLE backport doesn't seem trivial, so I'll wait for some
> > feedback, review & decision before going further down in backpatching.
>
> Hi, thanks for these patches. I
Alvaro Herrera 于2024年10月22日周二 05:52写道：
> On 2024-Oct-21, Tender Wang wrote:
>
> > I suspect that we don't need the below if
> > statement anymore.
> > /*
> > * If the referenced side is partitioned (which we know because our
> > * parent's constraint points to a different relation than ours) then
On 2024-Oct-21, Tender Wang wrote:
> I suspect that we don't need the below if
> statement anymore.
> /*
> * If the referenced side is partitioned (which we know because our
> * parent's constraint points to a different relation than ours) then
> * we must, in addition to the above, create pg_cons
Alvaro Herrera 于2024年10月18日周五 22:52写道：
> On 2024-Sep-26, Jehan-Guillaume de Rorthais wrote:
>
> > REL_14_STABLE backport doesn't seem trivial, so I'll wait for some
> > feedback, review & decision before going further down in backpatching.
>
> Hi, thanks for these patches. I have made some edits
On Wed, 25 Sep 2024 16:14:07 +0200
Jehan-Guillaume de Rorthais wrote:
> On Wed, 25 Sep 2024 14:42:40 +0200
> Jehan-Guillaume de Rorthais wrote:
>
> > On Thu, 5 Sep 2024 00:57:28 +0200
> > Jehan-Guillaume de Rorthais wrote:
> […]
> > >
> > > Please, find in attachment a patch implementing th
On Wed, 25 Sep 2024 14:42:40 +0200
Jehan-Guillaume de Rorthais wrote:
> On Thu, 5 Sep 2024 00:57:28 +0200
> Jehan-Guillaume de Rorthais wrote:
[…]
> >
> > Please, find in attachment a patch implementing this idea.
>
> Please, find in attachment a set of patch based on the previous one.
Plea
On Thu, 5 Sep 2024 00:57:28 +0200
Jehan-Guillaume de Rorthais wrote:
> On Mon, 2 Sep 2024 23:01:47 +0200
> Jehan-Guillaume de Rorthais wrote:
>
> […]
>
> > My proposal was to clean everything related to the old FK and use some
> > existing code path to create a fresh and cleaner one. This
Alvaro Herrera 于2024年8月8日周四 06:50写道：
> On 2024-Jul-26, Tender Wang wrote:
>
> > Junwang Zhao 于2024年7月26日周五 14:57写道：
> >
> > > There is a bug report[0] Tender comments might be the same issue as
> > > this one, but I tried Alvaro's and mine patch, neither could solve
> > > that problem, I did not
On Mon, 2 Sep 2024 23:01:47 +0200
Jehan-Guillaume de Rorthais wrote:
[…]
> My proposal was to clean everything related to the old FK and use some
> existing code path to create a fresh and cleaner one. This requires some
> refactoring in existing code, but we would win a common path of cod
Jehan-Guillaume de Rorthais 于2024年9月3日周二 17:26写道：
> Hi Tender,
>
> On Tue, 3 Sep 2024 10:16:44 +0800
> Tender Wang wrote:
>
> > Jehan-Guillaume de Rorthais 于2024年9月3日周二 05:02写道：
> […]
> > > * Constraint & trigger catalog cleanup [1] (this thread)
> > > * FK broken after DETACH [2]
> > > * Maint
Hi Tender,
On Tue, 3 Sep 2024 10:16:44 +0800
Tender Wang wrote:
> Jehan-Guillaume de Rorthais 于2024年9月3日周二 05:02写道：
[…]
> > * Constraint & trigger catalog cleanup [1] (this thread)
> > * FK broken after DETACH [2]
> > * Maintenance consideration about self referencing FK between partitions
> >
Jehan-Guillaume de Rorthais 于2024年9月3日周二 05:02写道：
> Hi,
>
> On Tue, 20 Aug 2024 23:09:27 -0400
> Alvaro Herrera wrote:
>
> > On 2024-Aug-20, Jehan-Guillaume de Rorthais wrote:
> >
> > > I'm back on this issue as well. I start poking at this patch to review
> it,
> > > test it, challenge it and t
Hi,
On Tue, 20 Aug 2024 23:09:27 -0400
Alvaro Herrera wrote:
> On 2024-Aug-20, Jehan-Guillaume de Rorthais wrote:
>
> > I'm back on this issue as well. I start poking at this patch to review it,
> > test it, challenge it and then report here.
> >
> > I'll try to check if some other issues migh
Alvaro Herrera 于2024年8月23日周五 02:41写道：
> On 2024-Aug-22, Tender Wang wrote:
>
> > I apply the v14 patch on branch REL_14_STABLE. I run this thread issue
> and I
> > find below error.
> > [...]
> > ERROR: cache lookup failed for constraint 16400
> >
> > I haven't look into details to find out wher
On 2024-Aug-22, Tender Wang wrote:
> I apply the v14 patch on branch REL_14_STABLE. I run this thread issue and I
> find below error.
> [...]
> ERROR: cache lookup failed for constraint 16400
>
> I haven't look into details to find out where cause above error.
Right, we try to drop the constrai
Tender Wang 于2024年8月22日周四 11:19写道：
>
>
> Alvaro Herrera 于2024年8月22日周四 06:00写道：
>
>> On 2024-Aug-19, Alvaro Herrera wrote:
>>
>> > I haven't pushed it yet, mostly because of being unsure about not doing
>> > anything for the oldest branches (14 and back).
>>
>> Last night, after much mulling on t
Alvaro Herrera 于2024年8月22日周四 06:00写道：
> On 2024-Aug-19, Alvaro Herrera wrote:
>
> > I haven't pushed it yet, mostly because of being unsure about not doing
> > anything for the oldest branches (14 and back).
>
> Last night, after much mulling on this, it occurred to me that one easy
> way out of
On 2024-Aug-19, Alvaro Herrera wrote:
> I haven't pushed it yet, mostly because of being unsure about not doing
> anything for the oldest branches (14 and back).
Last night, after much mulling on this, it occurred to me that one easy
way out of this problem for the old branches, without having to
On 2024-Aug-20, Jehan-Guillaume de Rorthais wrote:
> I'm back on this issue as well. I start poking at this patch to review it,
> test it, challenge it and then report here.
>
> I'll try to check if some other issues might have lost/forgot on they way as
> well.
Thanks, much appreciated, looking
On Wed, 7 Aug 2024 18:50:10 -0400
Alvaro Herrera wrote:
> On 2024-Jul-26, Tender Wang wrote:
>
> > Junwang Zhao 于2024年7月26日周五 14:57写道：
> >
> > > There is a bug report[0] Tender comments might be the same issue as
> > > this one, but I tried Alvaro's and mine patch, neither could solve
> > > th
Alvaro Herrera 于2024年8月20日周二 10:25写道：
> On 2024-Aug-20, Tender Wang wrote:
>
> > > As I understand, this fix needs to be applied all the way back to 12,
> > > because the overall functionality is that old. However, in branches 14
> > > and back, the patch doesn't apply cleanly, because of the ch
On 2024-Aug-20, Tender Wang wrote:
> > As I understand, this fix needs to be applied all the way back to 12,
> > because the overall functionality is that old. However, in branches 14
> > and back, the patch doesn't apply cleanly, because of the changes we
> > made in commit f4566345cf40 :-( I'm
Alvaro Herrera 于2024年8月8日周四 06:50写道：
> On 2024-Jul-26, Tender Wang wrote:
>
> > Junwang Zhao 于2024年7月26日周五 14:57写道：
> >
> > > There is a bug report[0] Tender comments might be the same issue as
> > > this one, but I tried Alvaro's and mine patch, neither could solve
> > > that problem, I did not
Alvaro Herrera 于2024年8月8日周四 06:50写道：
> On 2024-Jul-26, Tender Wang wrote:
>
> > Junwang Zhao 于2024年7月26日周五 14:57写道：
> >
> > > There is a bug report[0] Tender comments might be the same issue as
> > > this one, but I tried Alvaro's and mine patch, neither could solve
> > > that problem, I did not
Alvaro Herrera 于2024年8月8日周四 06:50写道：
> On 2024-Jul-26, Tender Wang wrote:
>
> > Junwang Zhao 于2024年7月26日周五 14:57写道：
> >
> > > There is a bug report[0] Tender comments might be the same issue as
> > > this one, but I tried Alvaro's and mine patch, neither could solve
> > > that problem, I did not
On 2024-Jul-26, Tender Wang wrote:
> Junwang Zhao 于2024年7月26日周五 14:57写道：
>
> > There is a bug report[0] Tender comments might be the same issue as
> > this one, but I tried Alvaro's and mine patch, neither could solve
> > that problem, I did not tried Tender's earlier patch thought. I post
> > t
Junwang Zhao 于2024年7月26日周五 14:57写道：
>
> There is a bug report[0] Tender comments might be the same
> issue as this one, but I tried Alvaro's and mine patch, neither
> could solve that problem, I did not tried Tender's earlier patch
> thought. I post the test script below in case you are intereste
On 2024-Jul-26, Junwang Zhao wrote:
> There is a bug report[0] Tender comments might be the same
> issue as this one, but I tried Alvaro's and mine patch, neither
> could solve that problem, I did not tried Tender's earlier patch
> thought. I post the test script below in case you are interested.
On Fri, Jul 26, 2024 at 2:36 PM Junwang Zhao wrote:
>
> On Mon, Jul 22, 2024 at 1:52 PM Tender Wang wrote:
> >
> >
> >
> > Alvaro Herrera 于2024年7月19日周五 21:18写道：
> >>
> >> Hello,
> >>
> >> I think the fix for the check triggers should be as the attached. Very
> >> close to what you did, but you
On Mon, Jul 22, 2024 at 1:52 PM Tender Wang wrote:
>
>
>
> Alvaro Herrera 于2024年7月19日周五 21:18写道：
>>
>> Hello,
>>
>> I think the fix for the check triggers should be as the attached. Very
>> close to what you did, but you were skipping some operations that needed
>> to be kept. AFAICS this patch
Alvaro Herrera 于2024年7月19日周五 21:18写道：
>
> I find this pair of queries useful; they show which constraints exist
> and which triggers belong to each. We need to make the constraints and
> triggers match after a detach right as things would be if the
> just-detached partition were an individual ta
Alvaro Herrera 于2024年7月19日周五 21:18写道：
> Hello,
>
> I think the fix for the check triggers should be as the attached. Very
> close to what you did, but you were skipping some operations that needed
> to be kept. AFAICS this patch works correctly for the posted cases.
>
After applying the attach
Hello,
I think the fix for the check triggers should be as the attached. Very
close to what you did, but you were skipping some operations that needed
to be kept. AFAICS this patch works correctly for the posted cases.
I haven't looked at the action triggers yet; I think we need to create
one t
Hi Alvaro,
Recently, Alexander reported the same issue on [1]. And before that,
another same issue was reported on [2].
So I try to re-work those issues. In my last email on this thread, I said
that
"
I slightly modified the previous patch，but I didn't add test case, because
I found another issu
Hi Alvaro,
I re-analyzed this issue, and here is my analysis process.
step 1: CREATE TABLE p ( id bigint PRIMARY KEY )
PARTITION BY list (id);
step2: CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
step3: CREATE TABLE r_1 (
id bigint PRIMARY KEY,
p_id bigint NOT NULL,
FOREIGN
On 2023-Oct-25, tender wang wrote:
> Hi
>Is there any conclusion to this issue?
None yet. I intend to work on this at some point, hopefully soon.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Hi
Is there any conclusion to this issue?
Jehan-Guillaume de Rorthais 于2023年8月10日周四 23:03写道：
> On Thu, 3 Aug 2023 11:02:43 +0200
> Alvaro Herrera wrote:
>
> > On 2023-Aug-03, tender wang wrote:
> >
> > > I think old "sub-FK" should not be dropped, that will be violates
> foreign
> > > key c
On Thu, 3 Aug 2023 11:02:43 +0200
Alvaro Herrera wrote:
> On 2023-Aug-03, tender wang wrote:
>
> > I think old "sub-FK" should not be dropped, that will be violates foreign
> > key constraint.
>
> Yeah, I've been playing more with the patch and it is definitely not
> doing the right things.
On 2023-Aug-07, tender wang wrote:
> The foreign key still works even though partition was detached. Is this
> behavior expected?
Well, there's no reason for it not to, right? For example, if you
detach a partition and then attach it again, you don't have to scan the
partition on attach, because
The foreign key still works even though partition was detached. Is this
behavior expected?
I can't find the answer in the document. If it is expected behavior ,
please ignore the bug I reported a few days ago.
tender wang 于2023年8月4日周五 17:04写道：
> Oversight the DetachPartitionFinalize(), I found a
Oversight the DetachPartitionFinalize() again, I found the root cause why
'r_p_id_fkey' wat not removed.
DetachPartitionFinalize() call the GetParentedForeignKeyRefs() func to get
tuple from pg_constraint that will be delete but failed.
according to the comments, the GetParentedForeignKeyRefs() f
Oversight the DetachPartitionFinalize(), I found another bug below:
postgres=# CREATE TABLE p ( id bigint PRIMARY KEY ) PARTITION BY list (id);
CREATE TABLE
postgres=# CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE
postgres=# CREATE TABLE r_1 (
postgres(# id bigint PRIMARY K
I think the code to determine that fk of a partition is inherited or not is
not enough.
For example, in this case, foreign key r_1_p_id_fkey1 is not inherited
from parent.
If conform->conparentid(in DetachPartitionFinalize func) is valid, we
should recheck confrelid(pg_constraint) field.
I try t
On 2023-Aug-03, tender wang wrote:
> I think old "sub-FK" should not be dropped, that will be violates foreign
> key constraint.
Yeah, I've been playing more with the patch and it is definitely not
doing the right things. Just eyeballing the contents of pg_trigger and
pg_constraint for partitio
I think old "sub-FK" should not be dropped, that will be violates foreign
key constraint. For example :
postgres=# insert into r values(1,1);
INSERT 0 1
postgres=# ALTER TABLE r DETACH PARTITION r_1;
ALTER TABLE
postgres=# delete from p_1 where id = 1;
DELETE 1
postgres=# select * from r_1;
id |
On 2023-Jul-05, Jehan-Guillaume de Rorthais wrote:
> ALTER TABLE r ATTACH PARTITION r_1 FOR VALUES IN (1);
>
> The old sub-FKs (below 18289) created in this table to enforce the action
> triggers on referenced partitions are not deleted when the table becomes a
> partition. Because of this, we
Hi,
(patch proposal below).
Consider a table with a FK pointing to a partitioned table.
CREATE TABLE p ( id bigint PRIMARY KEY )
PARTITION BY list (id);
CREATE TABLE p_1 PARTITION OF p FOR VALUES IN (1);
CREATE TABLE r_1 (
id bigint PRIMARY KEY,
p_id bigint NOT NULL,
FOR
69 matches
Mail list logo
