Re: problems with foreign keys on partitioned tables

On 2019/01/25 2:18, Alvaro Herrera wrote: > On 2019-Jan-24, Amit Langote wrote: > >> A few hunks of the originally proposed patch are attached here as 0001, >> especially the part which fixes ATAddForeignKeyConstraint to pass the >> correct value of connoninherit to CreateConstraintEntry (which

Re: problems with foreign keys on partitioned tables

On 2019-Jan-24, Amit Langote wrote: > A few hunks of the originally proposed patch are attached here as 0001, > especially the part which fixes ATAddForeignKeyConstraint to pass the > correct value of connoninherit to CreateConstraintEntry (which should be > false for partitioned tables). With

Re: problems with foreign keys on partitioned tables

On 2019-Jan-25, Amit Langote wrote: > On Fri, Jan 25, 2019 at 12:30 AM Amit Langote wrote: > > Doesn't the following performDeletion() at the start of > > ATExecDropConstraint(), through findDependentObject()'s own recursion, > > take care of deleting *all* constraints, including those of? > >

Re: problems with foreign keys on partitioned tables

On Fri, Jan 25, 2019 at 12:30 AM Amit Langote wrote: > > On Fri, Jan 25, 2019 at 12:08 AM Alvaro Herrera > wrote: > > On 2019-Jan-24, Amit Langote wrote: > > > > > Thinking more on this, my proposal to rip dependencies between parent and > > > child constraints altogether to resolve the bug I

Re: problems with foreign keys on partitioned tables

On Fri, Jan 25, 2019 at 12:08 AM Alvaro Herrera wrote: > On 2019-Jan-24, Amit Langote wrote: > > > Thinking more on this, my proposal to rip dependencies between parent and > > child constraints altogether to resolve the bug I initially reported is > > starting to sound a bit overambitious

Re: problems with foreign keys on partitioned tables

Hello On 2019-Jan-24, Amit Langote wrote: > Thinking more on this, my proposal to rip dependencies between parent and > child constraints altogether to resolve the bug I initially reported is > starting to sound a bit overambitious especially considering that we'd > need to back-patch it (the

Re: problems with foreign keys on partitioned tables

Hi, On 2019/01/24 6:13, Alvaro Herrera wrote: > On 2019-Jan-22, Amit Langote wrote: >> Done. See the attached patches for HEAD and PG 11. > > I'm not quite sure I understand why the one in DefineIndex needs the > deps but nothing else does. I fear that you added that one just to > appease the

Re: problems with foreign keys on partitioned tables

On 2019-Jan-22, Amit Langote wrote: > On 2019/01/22 8:30, Alvaro Herrera wrote: > > Hi Amit, > > > > Will you please rebase 0002? Please add your proposed tests cases to > > it, too. > > Done. See the attached patches for HEAD and PG 11. I'm not quite sure I understand why the one in

Re: problems with foreign keys on partitioned tables

On 2019/01/22 8:30, Alvaro Herrera wrote: > Hi Amit, > > Will you please rebase 0002? Please add your proposed tests cases to > it, too. Done. See the attached patches for HEAD and PG 11. Thanks, Amit From 432c4551990d0da1c77b6b9523296b0a2a0a5119 Mon Sep 17 00:00:00 2001 From: amit Date:

Re: problems with foreign keys on partitioned tables

Hi Amit, Will you please rebase 0002? Please add your proposed tests cases to it, too. Thanks, -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: problems with foreign keys on partitioned tables

Pushed now, thanks. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: problems with foreign keys on partitioned tables

On Sat, Jan 19, 2019 at 7:16 AM Alvaro Herrera wrote: > Thanks, this is better. There were a few other things I didn't like, so > I updated it. Mostly, two things: > > 1. I didn't like a seqscan on pg_trigger, so I turned that into an > indexed scan on the constraint OID, and then the other two

Re: problems with foreign keys on partitioned tables

On 2019-Jan-18, Amit Langote wrote: > OK, I agree. I have updated the patch to make things work that way. Thanks, this is better. There were a few other things I didn't like, so I updated it. Mostly, two things: 1. I didn't like a seqscan on pg_trigger, so I turned that into an indexed scan

Re: problems with foreign keys on partitioned tables

On 2019/01/18 7:54, Alvaro Herrera wrote: > On 2019-Jan-09, Amit Langote wrote: > >> 1. Foreign keys of partitions stop working correctly after being detached >> from the parent table > >> This happens because the action triggers defined on the PK relation (pk) >> refers to p as the referencing

Re: problems with foreign keys on partitioned tables

On 2019-Jan-09, Amit Langote wrote: > 1. Foreign keys of partitions stop working correctly after being detached > from the parent table > This happens because the action triggers defined on the PK relation (pk) > refers to p as the referencing relation. On detaching p1 from p, p1's > data is no

Re: problems with foreign keys on partitioned tables

Hi Amit On 2019-Jan-09, Amit Langote wrote: > I noticed a couple of problems with foreign keys on partitioned tables. Ouch, thanks for reporting. I think 0001 needs a bit of a tweak in pg11 to avoid an ABI break -- I intend to study this one and try to push early next week. I'm going to

problems with foreign keys on partitioned tables

Hi, I noticed a couple of problems with foreign keys on partitioned tables. 1. Foreign keys of partitions stop working correctly after being detached from the parent table create table pk (a int primary key); create table p (a int) partition by list (a); create table p1 partition of p

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On Fri, Jun 29, 2018 at 12:06 PM, Alvaro Herrera wrote: > Okay, pushed that way. > > We can redo this if/when we add support for cloning BEFORE row triggers, > which are going to need the trigger link info, I suspect. Yeah, having an explicit representation seems less likely to be fragile.

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jul 5, 2018 at 6:44 AM, Robert Haas wrote: > > Well, as far as I know, it's up to me which parts of your emails I want to > quote in my reply. I did read this part. It did not change my opinion. My > fundamental objection to your proposal is that I think it is too wordy. I > think users

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, Jul 3, 2018 at 12:41 AM Ashutosh Bapat < ashutosh.ba...@enterprisedb.com> wrote: > > rhaas=# drop table foo; > > ERROR: table "foo" does not exist > > HINT: Try dropping a table with a different name that does exist, or > > first create this table before trying to drop it. > > Again a

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, Jul 3, 2018 at 8:19 AM, Robert Haas wrote: > On Mon, Jul 2, 2018 at 1:46 AM, Ashutosh Bapat > wrote: >> This constraint was added to the partitioned table and inherited from >> there. If user wants to drop that constraint for some reason, this >> error message doesn't help. The error

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/07/03 11:49, Robert Haas wrote: > On Mon, Jul 2, 2018 at 1:46 AM, Ashutosh Bapat > wrote: >> This constraint was added to the partitioned table and inherited from >> there. If user wants to drop that constraint for some reason, this >> error message doesn't help. The error message tells

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jul 2, 2018 at 1:46 AM, Ashutosh Bapat wrote: > This constraint was added to the partitioned table and inherited from > there. If user wants to drop that constraint for some reason, this > error message doesn't help. The error message tells why he can't drop > it, but doesn't tell,

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/07/02 14:46, Ashutosh Bapat wrote: > This constraint was added to the partitioned table and inherited from > there. If user wants to drop that constraint for some reason, this > error message doesn't help. The error message tells why he can't drop > it, but doesn't tell, directly or

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Fri, Jun 29, 2018 at 6:35 PM, Tomas Vondra wrote: > > > On 06/29/2018 02:30 PM, Robert Haas wrote: >> >> On Wed, Jun 27, 2018 at 10:26 PM, Amit Langote >> wrote: >>> >>> By the way, picking up on the word "inherited" in the error message shown >>> above, I wonder if you decided against using

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 2018-Jun-29, Amit Langote wrote: > On 2018/06/29 6:23, Peter Eisentraut wrote: > > On 6/28/18 22:52, Alvaro Herrera wrote: > >>> Couldn't psql chase the pg_depend links to find inherited triggers? > >> > >> Yeah, I thought this would be exceedingly ugly, but apparently it's not > >> *that* bad

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 06/29/2018 02:30 PM, Robert Haas wrote: On Wed, Jun 27, 2018 at 10:26 PM, Amit Langote wrote: By the way, picking up on the word "inherited" in the error message shown above, I wonder if you decided against using similar terminology intentionally. Good question. I guess the thinking

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, Jun 27, 2018 at 10:26 PM, Amit Langote wrote: > By the way, picking up on the word "inherited" in the error message shown > above, I wonder if you decided against using similar terminology > intentionally. Good question. > I guess the thinking there is that the terminology being > used

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 2018/06/29 6:23, Peter Eisentraut wrote: > On 6/28/18 22:52, Alvaro Herrera wrote: >>> Couldn't psql chase the pg_depend links to find inherited triggers? >> >> Yeah, I thought this would be exceedingly ugly, but apparently it's not >> *that* bad -- see the attached patch, which relies on the

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 6/28/18 22:52, Alvaro Herrera wrote: >> Couldn't psql chase the pg_depend links to find inherited triggers? > > Yeah, I thought this would be exceedingly ugly, but apparently it's not > *that* bad -- see the attached patch, which relies on the fact that > triggers don't otherwise depend on

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 6/27/18 23:01, Alvaro Herrera wrote: > Another angle is that we're already in beta3 and there may be concerns > about altering catalog definition this late in the cycle. Anybody? > Maybe psql can just match tgisinternal triggers by name, and if one > match occurs then we assume it's a clone

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/28 7:58, Alvaro Herrera wrote: > On 2018-Jun-18, Robert Treat wrote: >> So +1 for thinking we do need to worry about it. I'm not exactly sure >> how we want to expose that info; with \d+ we list various "Partition >> X:" sections, perhaps adding one for "Partition triggers:" would be >>

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-18, Robert Treat wrote: > One of the things I was thinking about while reading this thread is > that the scenario of creating "duplicate" triggers on a table meaning > two triggers doing the same thing is entirely possible now but we > don't really do anything to prevent it, which is

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 2018-Jun-28, David Rowley wrote: > On 28 June 2018 at 09:01, Alvaro Herrera wrote: > > Another angle is that we're already in beta3 and there may be concerns > > about altering catalog definition this late in the cycle. Anybody? > > Maybe psql can just match tgisinternal triggers by name,

Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

On 28 June 2018 at 09:01, Alvaro Herrera wrote: > Another angle is that we're already in beta3 and there may be concerns > about altering catalog definition this late in the cycle. Anybody? > Maybe psql can just match tgisinternal triggers by name, and if one > match occurs then we assume it's a

Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables)

Gmail users: this comes from https://postgr.es/m/20180627191819.6g73wu7ck23fdhv6@alvherre.pgsql On 2018-Jun-27, Alvaro Herrera wrote: > On 2018-Jun-19, Amit Langote wrote: > > > In CreateTrigger(), 86f575948c7 did this. > > > > -values[Anum_pg_trigger_tgisinternal - 1] =

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-19, Amit Langote wrote: > In CreateTrigger(), 86f575948c7 did this. > > -values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal); > +values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || > in_partition); > > I'm not sure why it had to be done, but

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 18, 2018 at 10:29 PM, Alvaro Herrera wrote: > On 2018-Jun-18, Ashutosh Bapat wrote: > >> That's a wrong comparison. Inheritance based partitioning works even >> after declarative partitioning feature is added. So, users can >> continue using inheritance based partitioning if they

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, Jun 19, 2018 at 3:51 AM, Robert Treat wrote: > > So +1 for thinking we do need to worry about it. I'm not exactly sure > how we want to expose that info; with \d+ we list various "Partition > X:" sections, perhaps adding one for "Partition triggers:" would be > enough, although I am

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

Hi. On 2018/06/19 1:59, Alvaro Herrera wrote: > What does worry me a little bit now, reading this discussion, is whether > we've made the triggers in partitions visible enough. We'll have this > problem once we implement BEFORE ROW triggers as proposed, and I think > we already have this problem

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 18, 2018 at 12:59 PM, Alvaro Herrera wrote: > On 2018-Jun-18, Ashutosh Bapat wrote: > >> That's a wrong comparison. Inheritance based partitioning works even >> after declarative partitioning feature is added. So, users can >> continue using inheritance based partitioning if they

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 18, 2018 at 9:59 AM, Alvaro Herrera wrote: > > alvherre=# select tgname, tgrelid::regclass, tgisinternal from pg_trigger; > tgname │ tgrelid │ tgisinternal > ┼─┼── > trig_p │ parent │ f > trig_p │ child │ t > trig_c │ child │ f > (3 filas) > > So

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-18, Ashutosh Bapat wrote: > That's a wrong comparison. Inheritance based partitioning works even > after declarative partitioning feature is added. So, users can > continue using inheritance based partitioning if they don't want to > move to declarative partitioning. That's not true

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 18, 2018 at 1:20 AM, Ashutosh Bapat wrote: > That's a wrong comparison. Inheritance based partitioning works even > after declarative partitioning feature is added. So, users can > continue using inheritance based partitioning if they don't want to > move to declarative partitioning.

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Sat, Jun 16, 2018 at 9:29 AM, Robert Haas wrote: > > By that logic, we should not have suggested that anyone use table > inheritance, because they would have to change their configuration > when we implemented table partitioning. Indeed, switching from table > inheritance to table

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 14, 2018 at 9:45 AM, Ashutosh Bapat wrote: >> It sounds like you want to try to hide from users the fact that they >> can create triggers on the individual partitions. > > No. I never said that in my mails (see [1], [2]) I object to the > explicit suggestion that they can/should

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/14 22:45, Ashutosh Bapat wrote: > On Thu, Jun 14, 2018 at 6:49 PM, Robert Haas wrote: >> It sounds like you want to try to hide from users the fact that they >> can create triggers on the individual partitions. > > No. I never said that in my mails (see [1], [2]) I object to the >

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

> On Jun 14, 2018, at 9:19 AM, Robert Haas wrote: > > anyone who wants a BEFORE trigger has a good reason > for wanting it. I have used before triggers to enforce the immutability of a column. i.e. if (new.member_key != old.member_key) then raise exception 'Unable to change

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 14, 2018 at 6:49 PM, Robert Haas wrote: > On Wed, Jun 6, 2018 at 7:51 AM, Ashutosh Bapat > wrote: >> On Tue, Jun 5, 2018 at 10:38 PM, Alvaro Herrera >> wrote: >> >>> - BEFORE row triggers are not supported >> >> I think this is fine. The existing wording suggests that the user >>

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, Jun 6, 2018 at 7:51 AM, Ashutosh Bapat wrote: > On Tue, Jun 5, 2018 at 10:38 PM, Alvaro Herrera > wrote: > >> - BEFORE row triggers are not supported > > I think this is fine. The existing wording suggests that the user > creates the triggers on the partitioned table, and that will be >

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 4, 2018 at 5:40 PM, Tom Lane wrote: >> I think, in general, that we should try to pick semantics that make a >> partitioned table behave like an unpartitioned table, provided that >> all triggers are defined on the partitioned table itself. > > Well, then we lose the property Alvaro

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 14, 2018 at 1:54 PM, Amit Langote wrote: > On 2018/06/12 22:22, Ashutosh Bapat wrote: >> -- create triggers, user may create different trigger functions one >> for each partition, unless s/he understands that the tables can share >> trigger functions >> create function trig_t1p1()

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/12 22:22, Ashutosh Bapat wrote: > -- create triggers, user may create different trigger functions one > for each partition, unless s/he understands that the tables can share > trigger functions > create function trig_t1p1() returns trigger as $$ begin return new; > end;$$ language

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Sat, Jun 9, 2018 at 3:48 AM, Alvaro Herrera wrote: > On 2018-Jun-08, Alvaro Herrera wrote: > >> Actually, the column order doesn't matter for a trigger function, >> because these don't refer to columns by number but by name. So unless >> users write trigger functions in C and use hardcoded

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-08, Alvaro Herrera wrote: > Actually, the column order doesn't matter for a trigger function, > because these don't refer to columns by number but by name. So unless > users write trigger functions in C and use hardcoded column numbers > (extremely unlikely), I think this is not an

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-07, Ashutosh Bapat wrote: > On Thu, Jun 7, 2018 at 10:58 AM, Amit Langote > wrote: > > I don't understand why you think it's too troublesome to let the users > > know that there is some way to use BR triggers with partitioning. We > > didn't do that for indexes, for example, before

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 7, 2018 at 10:58 AM, Amit Langote wrote: > On 2018/06/07 14:17, Ashutosh Bapat wrote: >>> that is, users can find out about that feature by themselves by >>> trying it out? >> >> I didn't understand that part. >> >> Probably we just say that BEFORE ROW triggers are not supported on a

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/07 14:17, Ashutosh Bapat wrote: >> that is, users can find out about that feature by themselves by >> trying it out? > > I didn't understand that part. > > Probably we just say that BEFORE ROW triggers are not supported on a > partitioned table. It's good enough not to suggest it

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 7, 2018 at 7:51 AM, Amit Langote wrote: > On 2018/06/06 20:51, Ashutosh Bapat wrote: >> The existing wording suggests that the user >> creates the triggers on the partitioned table, and that will be >> supported always, which can lead to problems. > > Do you mean the following wording

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/06 20:51, Ashutosh Bapat wrote: > The existing wording suggests that the user > creates the triggers on the partitioned table, and that will be > supported always, which can lead to problems. Do you mean the following wording "BEFORE ROW triggers, if necessary, must be defined on

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, Jun 5, 2018 at 10:38 PM, Alvaro Herrera wrote: > - BEFORE row triggers are not supported I think this is fine. The existing wording suggests that the user creates the triggers on the partitioned table, and that will be supported always, which can lead to problems. With this

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/06 2:08, Alvaro Herrera wrote: > On 2018-Jun-05, Amit Langote wrote: > >> On 2018/06/05 16:41, Ashutosh Bapat wrote: >>> On Tue, Jun 5, 2018 at 1:07 PM, Amit Langote >>> wrote: On 2018/06/05 1:25, Alvaro Herrera wrote: > In the meantime, my inclination is to fix the

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-05, Amit Langote wrote: > On 2018/06/05 16:41, Ashutosh Bapat wrote: > > On Tue, Jun 5, 2018 at 1:07 PM, Amit Langote > > wrote: > >> On 2018/06/05 1:25, Alvaro Herrera wrote: > >>> In the meantime, my inclination is to fix the documentation to point out > >>> that AFTER triggers are

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/05 16:41, Ashutosh Bapat wrote: > On Tue, Jun 5, 2018 at 1:07 PM, Amit Langote > wrote: >> On 2018/06/05 1:25, Alvaro Herrera wrote: >>> In the meantime, my inclination is to fix the documentation to point out >>> that AFTER triggers are allowed but BEFORE triggers are not. >> >>

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/06/05 1:25, Alvaro Herrera wrote: > In the meantime, my inclination is to fix the documentation to point out > that AFTER triggers are allowed but BEFORE triggers are not. Wasn't that already fixed by bcded2609ade6? We don't say anything about AFTER triggers per se, but the following

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, Jun 5, 2018 at 1:07 PM, Amit Langote wrote: > On 2018/06/05 1:25, Alvaro Herrera wrote: >> In the meantime, my inclination is to fix the documentation to point out >> that AFTER triggers are allowed but BEFORE triggers are not. > > Wasn't that already fixed by bcded2609ade6? > > We don't

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 4, 2018 at 2:40 PM, Tom Lane wrote: > > I think, in general, that we should try to pick semantics that make a > > partitioned table behave like an unpartitioned table, provided that > > all triggers are defined on the partitioned table itself. > > Well, then we lose the property

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

Robert Haas writes: > On Mon, Jun 4, 2018 at 4:50 PM, Tom Lane wrote: >> Perhaps, but I'm having a hard time wrapping my mind around what the >> semantics ought to be. If a trigger on partition A changes the keys >> so that the row shouldn't have gone into A at all, what then? That >> trigger

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 6/4/18 16:50, Tom Lane wrote: > Perhaps, but I'm having a hard time wrapping my mind around what the > semantics ought to be. If a trigger on partition A changes the keys > so that the row shouldn't have gone into A at all, what then? That > trigger should never have fired, eh? The insert

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Mon, Jun 4, 2018 at 1:42 PM, Tom Lane wrote: > Could we solve it by saying that triggers on partitioned tables aren't > allowed to change the partitioning values? (Or at least, not allowed > to change them in a way that changes the target partition.) That seems like a somewhat-unfortunate

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-04, Tom Lane wrote: > Alvaro Herrera writes: > > On 2018-Jun-04, Tom Lane wrote: > >> ... why doesn't the same problem apply to AFTER triggers that are attached > >> to the inheritance parent? > > > With a BEFORE trigger, running the trigger might change the target > > partition,

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

Alvaro Herrera writes: > On 2018-Jun-04, Tom Lane wrote: >> ... why doesn't the same problem apply to AFTER triggers that are attached >> to the inheritance parent? > With a BEFORE trigger, running the trigger might change the target > partition, which has the potential for all kinds of trouble.

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-Jun-04, Tom Lane wrote: > Alvaro Herrera writes: > > This kind of inconsistency is what I wanted to avoid. One of the > > guiding principles here was that a partitioned table behaves just like a > > regular table does; in particular, inserting directly into a partition > > is an

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

Alvaro Herrera writes: > This kind of inconsistency is what I wanted to avoid. One of the > guiding principles here was that a partitioned table behaves just like a > regular table does; in particular, inserting directly into a partition > is an application-level optimization that must behave

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018-May-03, Robert Haas wrote: > The asymmetry doesn't seem horrible to me on its own merits, but it > would lead to some odd behavior: suppose you defined a BEFORE ROW > trigger and an AFTER ROW trigger on the parent, and then inserted one > row into the parent table and a second row

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, May 2, 2018 at 9:17 AM, Ashutosh Bapat wrote: > On Wed, May 2, 2018 at 11:56 AM, Amit Langote > wrote: >> But one could very well argue that BEFORE ROW triggers on the >> parent should run before performing the tuple routing

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 5/2/18 02:26, Amit Langote wrote: > You're right. I think that's what you were also saying on the other > thread, in reply to which I directed you to this thread. I very clearly > missed that BEFORE ROW triggers are still disallowed. fixed -- Peter Eisentraut

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Wed, May 2, 2018 at 11:56 AM, Amit Langote wrote: > But one could very well argue that BEFORE ROW triggers on the > parent should run before performing the tuple routing and not be cloned to > individual partitions, in which case the result would not have been

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/05/02 14:17, Ashutosh Bapat wrote: > On Tue, May 1, 2018 at 5:20 PM, Peter Eisentraut > <peter.eisentr...@2ndquadrant.com> wrote: >> On 4/26/18 05:03, Amit Langote wrote: >>> On 2018/04/26 13:01, David Rowley wrote: >>>> The attached small patch remo

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Tue, May 1, 2018 at 5:20 PM, Peter Eisentraut <peter.eisentr...@2ndquadrant.com> wrote: > On 4/26/18 05:03, Amit Langote wrote: >> On 2018/04/26 13:01, David Rowley wrote: >>> The attached small patch removes the mention that partitioned tables >>> cannot

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/05/01 20:50, Peter Eisentraut wrote: > On 4/26/18 05:03, Amit Langote wrote: >> On 2018/04/26 13:01, David Rowley wrote: >>> The attached small patch removes the mention that partitioned tables >>> cannot have foreign keys defined on them. >>> >&g

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 1 May 2018 at 23:50, Peter Eisentraut wrote: > committed both Thanks! -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 4/26/18 05:03, Amit Langote wrote: > On 2018/04/26 13:01, David Rowley wrote: >> The attached small patch removes the mention that partitioned tables >> cannot have foreign keys defined on them. >> >> This has been supported since 3de241db > > I noticed

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 26 April 2018 at 21:03, Amit Langote wrote: > I noticed also that the item regarding row triggers might be obsolete as > of 86f575948c7, thanks again to Alvaro! So, I updated your patch to take > care of that. Thanks. I walked right past that one. -- David

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On 2018/04/26 13:01, David Rowley wrote: > The attached small patch removes the mention that partitioned tables > cannot have foreign keys defined on them. > > This has been supported since 3de241db I noticed also that the item regarding row triggers might be obsolete as of 86f5759

Remove mention in docs that foreign keys on partitioned tables are not supported

The attached small patch removes the mention that partitioned tables cannot have foreign keys defined on them. This has been supported since 3de241db -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Serv

Re: Foreign keys and partitioned tables

Alvaro Herrera wrote: > After wasting some time trying to resolve > "minor last minute issues", I decided to reduce the scope for now: in > the current patch, it's allowed to have foreign keys in partitioned > tables, but it is not possible to have foreign keys that point t

Re: pgsql: Foreign keys on partitioned tables

On Fri, Apr 6, 2018 at 4:49 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Robert Haas wrote: >> On Wed, Apr 4, 2018 at 1:03 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> >> wrote: >> > Foreign keys on partitioned tables >> > >> &g

Re: pgsql: Foreign keys on partitioned tables

Robert Haas wrote: > On Wed, Apr 4, 2018 at 1:03 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> > wrote: > > Foreign keys on partitioned tables > > > > Author: Álvaro Herrera > > Discussion: > > https://postgr.es/m/20171231194359.cvojcour423ulha4@alvher

Re: pgsql: Foreign keys on partitioned tables

On Wed, Apr 4, 2018 at 1:03 PM, Alvaro Herrera <alvhe...@alvh.no-ip.org> wrote: > Foreign keys on partitioned tables > > Author: Álvaro Herrera > Discussion: https://postgr.es/m/20171231194359.cvojcour423ulha4@alvherre.pgsql > Reviewed-by: Peter Eisentraut The commit mess

Re: Foreign keys and partitioned tables

Robert Haas wrote: > I suspect that this leads to bugs under concurrency, something to do > with crosscheck_snapshot, but I couldn't say exactly what the problem > is off the top of my head. My hope is that partitioning might be > immune on the strength of knowing that any given tuple could

Re: Foreign keys and partitioned tables

Tom Lane wrote: > Alvaro Herrera writes: > > Thanks, pushed. > > This has broken the selinux regression tests, evidently because it > removed ONLY from the emitted FK test queries. While we could change > the expected results, I would first like to hear a defense of why

Re: Foreign keys and partitioned tables

Peter Eisentraut wrote: > On 4/3/18 15:11, Alvaro Herrera wrote: > > 0003 is the main patch, which is a bit changed from v4, notably to cover > > your review comments: > > Looks good now. Thanks, pushed. I added a couple of test cases for ON UPDATE/DELETE and MATCH PARTIAL, after noticing that

Re: Foreign keys and partitioned tables

Robert Haas wrote: > On Sun, Dec 31, 2017 at 2:43 PM, Alvaro Herrera > wrote: > > This patch removes all the ONLY markers from queries in ri_triggers.c. > > That makes the queries work for the new use case, but I haven't figured > > if it breaks things for other use

Re: Foreign keys and partitioned tables

On 4/3/18 15:11, Alvaro Herrera wrote: > 0003 is the main patch, which is a bit changed from v4, notably to cover > your review comments: Looks good now. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Foreign keys and partitioned tables

Peter Eisentraut wrote: > > 0002 is a fixup for a bug in the row triggers patch: I had a restriction > > earlier that triggers declared internal were not cloned, and I seem to > > have lost it in rebase. Reinstate it. > > Hmm, doesn't cause any test changes? Here's a test case: create table t

Re: Foreign keys and partitioned tables

Alvaro Herrera wrote: > While adding some more tests for the "action" part (i.e. updates and > deletes on the referenced table) I came across a bug that was causing > the server to crash ... but it's actually a preexisting bug in an > assert. The fix is in 0001. Yeah, it's a live bug that only

Re: Foreign keys and partitioned tables

, this restriction should perhaps be mentioned in > the documentation additions that we have been discussing. Added a note in ALTER TABLE. > > The first few hunks in ri_triggers.c (the ones that refer to the > pktable) are apparently for the postponed part of the feature, foreign >

Re: Foreign keys and partitioned tables

r to the pktable) are apparently for the postponed part of the feature, foreign keys referencing partitioned tables. So I think those hunks should be dropped from this patch. The removal of the ONLY for the foreign key queries also affects inherited tables, in undesirable ways. For example, consid

Re: Foreign keys and partitioned tables

On 3/31/18 18:21, Alvaro Herrera wrote: > Yeah, I started by putting what I thought was going to be just ALTER > TABLE in that test, then moved to the other file and added what I > thought were more complete tests there and failed to move stuff to > alter_table. Honestly, I think these should

  1   2   >