Обсуждение: Re: [HACKERS] Declarative partitioning vs. sql_inheritance
On Thu, Dec 15, 2016 at 10:40 AM, Dmitry Ivanov <d.ivanov@postgrespro.ru> wrote: > Hi everyone, > > Looks like "sql_inheritance" GUC is affecting partitioned tables: > > explain (costs off) select * from test; > QUERY PLAN ------------------------------ > Append > -> Seq Scan on test > -> Seq Scan on test_1 > -> Seq Scan on test_2 > -> Seq Scan on test_1_1 > -> Seq Scan on test_1_2 > -> Seq Scan on test_1_1_1 > -> Seq Scan on test_1_2_1 > (8 rows) > > > set sql_inheritance = off; > > > explain (costs off) select * from test; > QUERY PLAN ------------------ > Seq Scan on test > (1 row) > > > I might be wrong, but IMO this should not happen. Queries involving update, > delete etc on partitioned tables are basically broken. Moreover, there's no > point in performing such operations on a parent table that's supposed to be > empty at all times. An earlier version of Amit's patches tried to handle this by forcing sql_inheritance on for partitioned tables, but it wasn't well-implemented and I don't see the point anyway. Sure, turning off sql_inheritance off for partitioned tables produces stupid results. But turning off sql_inheritance for inheritance hierarchies also produces stupid results. If we were going to do anything about this, my vote would be to remove sql_inheritance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> An earlier version of Amit's patches tried to handle this by forcing
> sql_inheritance on for partitioned tables, but it wasn't
> well-implemented and I don't see the point anyway. Sure, turning off
> sql_inheritance off for partitioned tables produces stupid results.
> But turning off sql_inheritance for inheritance hierarchies also
> produces stupid results. If we were going to do anything about this,
> my vote would be to remove sql_inheritance.
+1. If memory serves, we invented that GUC as a backwards-compatibility
hack, because once upon a time the default behavior was equivalent to
sql_inheritance = off. But that was a long time ago; a bit of digging
in the git history suggests we changed it in 2000. It's hard to believe
that anybody still relies on being able to turn it off.
regards, tom lane
On Fri, Dec 16, 2016 at 11:05:21AM -0500, Robert Haas wrote: > On Thu, Dec 15, 2016 at 10:40 AM, Dmitry Ivanov <d.ivanov@postgrespro.ru> wrote: > > Hi everyone, > > > > Looks like "sql_inheritance" GUC is affecting partitioned tables: > > > > [breaks literally everything] > > > > I might be wrong, but IMO this should not happen. Queries involving update, > > delete etc on partitioned tables are basically broken. Moreover, there's no > > point in performing such operations on a parent table that's supposed to be > > empty at all times. > > An earlier version of Amit's patches tried to handle this by forcing > sql_inheritance on for partitioned tables, but it wasn't > well-implemented and I don't see the point anyway. Sure, turning > off sql_inheritance off for partitioned tables produces stupid > results. But turning off sql_inheritance for inheritance > hierarchies also produces stupid results. If we were going to do > anything about this, my vote would be to remove sql_inheritance. +1 It occurs to me this probably isn't the only GUC that's basically just a foot gun at this point. Is 10 a good time to sweep and clear them? Best, David. -- David Fetter <david(at)fetter(dot)org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Dec 16, 2016 at 2:34 PM, David Fetter <david@fetter.org> wrote: > It occurs to me this probably isn't the only GUC that's basically just > a foot gun at this point. > > Is 10 a good time to sweep and clear them? We never make any progress trying to do these things "in bulk". If you think there are other GUCs that need to be removed, start a thread for each one, or closely related group, and let's talk about it on the merits. On this thread, let's just decide whether or not to remove sql_inheritance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 12/16/16 11:05 AM, Robert Haas wrote: > If we were going to do anything about this, > my vote would be to remove sql_inheritance. Go for it. Let's also remove the table* syntax then. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 12/16/16 11:05 AM, Robert Haas wrote: > If we were going to do anything about this, > my vote would be to remove sql_inheritance. +1. This option is long past the intended shelf life. -- -David david@pgmasters.net
Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> On 12/16/16 11:05 AM, Robert Haas wrote:
>> If we were going to do anything about this,
>> my vote would be to remove sql_inheritance.
> Go for it.
> Let's also remove the table* syntax then.
Meh --- that might break existing queries, to what purpose?
We certainly shouldn't remove query syntax without a deprecation period.
I'm less concerned about that for GUCs.
regards, tom lane
On Fri, Dec 16, 2016 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >> On 12/16/16 11:05 AM, Robert Haas wrote: >>> If we were going to do anything about this, >>> my vote would be to remove sql_inheritance. > >> Go for it. > >> Let's also remove the table* syntax then. > > Meh --- that might break existing queries, to what purpose? > > We certainly shouldn't remove query syntax without a deprecation period. > I'm less concerned about that for GUCs. I agree. Patch attached, just removing the GUC and a fairly minimal amount of the supporting infrastructure. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Вложения
On 2016/12/17 10:40, Robert Haas wrote: > On Fri, Dec 16, 2016 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: >>> On 12/16/16 11:05 AM, Robert Haas wrote: >>>> If we were going to do anything about this, >>>> my vote would be to remove sql_inheritance. >> >>> Go for it. >> >>> Let's also remove the table* syntax then. >> >> Meh --- that might break existing queries, to what purpose? >> >> We certainly shouldn't remove query syntax without a deprecation period. >> I'm less concerned about that for GUCs. > > I agree. Patch attached, just removing the GUC and a fairly minimal > amount of the supporting infrastructure. +1 to removing the sql_inheritance GUC. The patch looks good to me. Thanks, Amit
Robert Haas wrote: > On Fri, Dec 16, 2016 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes: > >> On 12/16/16 11:05 AM, Robert Haas wrote: > >>> If we were going to do anything about this, > >>> my vote would be to remove sql_inheritance. > > > >> Go for it. > > > >> Let's also remove the table* syntax then. > > > > Meh --- that might break existing queries, to what purpose? > > > > We certainly shouldn't remove query syntax without a deprecation period. > > I'm less concerned about that for GUCs. > > I agree. Patch attached, just removing the GUC and a fairly minimal > amount of the supporting infrastructure. Any particular reason not to change inhOpt to be a simple boolean, and remove the enum? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Mon, Dec 19, 2016 at 11:48 AM, Alvaro Herrera <alvherre@2ndquadrant.com> wrote: > Any particular reason not to change inhOpt to be a simple boolean, and > remove the enum? No, no particular reason. I thought about it, but I didn't really see any advantage in getting rid of the typedef. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Mon, Dec 19, 2016 at 12:25 AM, Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> wrote: >> I agree. Patch attached, just removing the GUC and a fairly minimal >> amount of the supporting infrastructure. > > +1 to removing the sql_inheritance GUC. The patch looks good to me. Great, committed. I realize just now that I forgot to credit anyone as a reviewer, but hopefully nobody's going to mind that too much considering this is a purely mechanical patch I wrote in 20 minutes. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> Great, committed. I realize just now that I forgot to credit anyone
> as a reviewer, but hopefully nobody's going to mind that too much
> considering this is a purely mechanical patch I wrote in 20 minutes.
Do you have any particular objection to taking the next step of removing
enum InhOption in favor of making inhOpt a bool? It seems to me that
stuff like
- bool recurse = interpretInhOption(rv->inhOpt);
+ bool recurse = (rv->inhOpt == INH_YES);
just begs the question of why it's not simply
bool recurse = rv->inh;
Certainly a reader who did not know the history would be confused at
the useless-looking complexity.
regards, tom lane
On Fri, Dec 23, 2016 at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Robert Haas <robertmhaas@gmail.com> writes: >> Great, committed. I realize just now that I forgot to credit anyone >> as a reviewer, but hopefully nobody's going to mind that too much >> considering this is a purely mechanical patch I wrote in 20 minutes. > > Do you have any particular objection to taking the next step of removing > enum InhOption in favor of making inhOpt a bool? It seems to me that > stuff like > > - bool recurse = interpretInhOption(rv->inhOpt); > + bool recurse = (rv->inhOpt == INH_YES); > > just begs the question of why it's not simply > > bool recurse = rv->inh; > > Certainly a reader who did not know the history would be confused at > the useless-looking complexity. No, not really. I don't feel like it's an improvement, but you and Alvaro obviously do, so have at it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
> On Fri, Dec 23, 2016 at 11:59 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Do you have any particular objection to taking the next step of removing
>> enum InhOption in favor of making inhOpt a bool?
> No, not really. I don't feel like it's an improvement, but you and
> Alvaro obviously do, so have at it.
OK, will do.
regards, tom lane