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

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

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. >>> >>> This has been supported since 3de241db >>

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 also that the item regarding row

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 86f575948c7, thanks