Обсуждение: Inheritance

On 5/22/16 1:37 AM, Jan Johansson wrote:
>  - Allow single (behavior) inheritance (model here is quite a few modern
> languages, such as C#, D, ...)
>  - Allow multiple declarative inheritance (interface like, the
> inheritance almost works like this today though)
>
> If, with these restrictions (or maybe only the first), do you think that
> it will simplify implementation and make it more feature complete?

I think you'll need to be a bit more specific to elicit a response. What 
exactly are you proposing to change?
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On Mon, May 23, 2016 at 10:21 AM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 5/22/16 1:37 AM, Jan Johansson wrote:
>>
>>  - Allow single (behavior) inheritance (model here is quite a few modern
>> languages, such as C#, D, ...)
>>  - Allow multiple declarative inheritance (interface like, the
>> inheritance almost works like this today though)
>>
>> If, with these restrictions (or maybe only the first), do you think that
>> it will simplify implementation and make it more feature complete?
>
> I think you'll need to be a bit more specific to elicit a response. What
> exactly are you proposing to change?

I would guess OP is complaining about what everyone complains about.
What people want is for tables to have a base set of shared columns by
a varying set of derived type dependent columns.  Constraints on the
shared columns will be enforced on all the derived columns.

Postgres doesn't work that way, and the documentation disclaims this:
"Note: Although inheritance is frequently useful, it has not been
integrated with unique constraints or foreign keys, which limits its
usefulness. See Section 5.8 for more detail."

Personally, I don't think this will ever be fixed.  The reason why it
doesn't work is due to some foundational implementation decisions that
would have to be revisited.

This feature was very much a product of the time, at the height of the
"Object Relational" fad.  The trend for postgres has been in the exact
opposite direction, towards the SQL standard.  Further complicating
matters, inheritance has been repurposed to be the foundation for
table partitioning, making heavy changes problematic.

The classic SQL approach to the problem, establishing a base table
plus a type and derived tables with a pkey:pkey link isn't a very bad
one from a data modelling perspective and serialization to the
application is increasingly going to be handled by json going forward
as opposed to hacking the postgres type system.  This really reduces
the value proposition of heavy changes to the inheritance features.
If there was consensus on that point, I suppose the way forward is
some documentation restructuring, starting with removing the
increasingly baroque tutorial (trivia: the tutorial was the old manual
at one point).

merlin

On 5/23/16 11:05 AM, Merlin Moncure wrote:
> Postgres doesn't work that way, and the documentation disclaims this:
> "Note: Although inheritance is frequently useful, it has not been
> integrated with unique constraints or foreign keys, which limits its
> usefulness. See Section 5.8 for more detail."
>
> Personally, I don't think this will ever be fixed.  The reason why it
> doesn't work is due to some foundational implementation decisions that
> would have to be revisited.

If the complaint is really about FKs/UNIQUE (and really AFAIK it's only 
UNIQUE that's the problem), then I agree: it should be addressed. It's a 
major impediment to partitioning (and generic inheritance).

> This feature was very much a product of the time, at the height of the
> "Object Relational" fad.  The trend for postgres has been in the exact
> opposite direction, towards the SQL standard.  Further complicating
> matters, inheritance has been repurposed to be the foundation for
> table partitioning, making heavy changes problematic.

I don't see why partitioning complicates fixing these issues. ISTM it's 
the exact same complaint for both inheritance and partitioning.

I also disagree about PK:PK FK's between a bunch of completely 
independent tables being a good way to model this stuff. It doubles the 
complexity of every query against a child table and doesn't perform 
nearly as well, because your data locality goes down the tubes.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 5/23/16 11:05 AM, Merlin Moncure wrote:
>> This feature was very much a product of the time, at the height of the
>> "Object Relational" fad.  The trend for postgres has been in the exact
>> opposite direction, towards the SQL standard.  Further complicating
>> matters, inheritance has been repurposed to be the foundation for
>> table partitioning, making heavy changes problematic.

> I don't see why partitioning complicates fixing these issues. ISTM it's 
> the exact same complaint for both inheritance and partitioning.

My feeling about it is that we need to provide a partitioning feature
that doesn't rely on the current notion of inheritance at all.  We've
heard from multiple users who want to use large numbers of partitions,
enough that simply having a separate relcache entry for each partition
would be a performance problem, never mind the current approach to
planning queries over inheritance trees.  So the partitions need to be
objects much simpler than full-fledged tables.

If we had that, and encouraged people to migrate simple partitioning
use-cases to it, that might take off enough pressure that we could
afford to consider more-complicated inheritance schemes rather than
treating inheritance as an unfortunate legacy design.  But we're
some years away from being able to do that.
        regards, tom lane

On 05/23/2016 03:05 PM, Tom Lane wrote:
> Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
>> I don't see why partitioning complicates fixing these issues. ISTM it's
>> the exact same complaint for both inheritance and partitioning.
>
> My feeling about it is that we need to provide a partitioning feature
> that doesn't rely on the current notion of inheritance at all.  We've
> heard from multiple users who want to use large numbers of partitions,
> enough that simply having a separate relcache entry for each partition
> would be a performance problem, never mind the current approach to
> planning queries over inheritance trees.  So the partitions need to be
> objects much simpler than full-fledged tables.

I wonder if it wouldn't make sense to define a partition as a list of
segments within a single table that represent the partition?

But then again, maybe we need to start with a clear notion of what
problems people are trying to solve when they use partitions. At least
some of the historic reasons are no longer valid.

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

Joe Conway <mail@joeconway.com> writes:
> But then again, maybe we need to start with a clear notion of what
> problems people are trying to solve when they use partitions. At least
> some of the historic reasons are no longer valid.

That's true.  Just because people want to have a gazillion partitions
doesn't necessarily mean it's a good design that we need to support well.
Some investigation would be a smart use of time.
        regards, tom lane

Tom Lane wrote:

> My feeling about it is that we need to provide a partitioning feature
> that doesn't rely on the current notion of inheritance at all.  We've
> heard from multiple users who want to use large numbers of partitions,
> enough that simply having a separate relcache entry for each partition
> would be a performance problem, never mind the current approach to
> planning queries over inheritance trees.  So the partitions need to be
> objects much simpler than full-fledged tables.

Sorry to hijack the thread, but I agree on this, and I'm worried that
the patch being floated for partitioning may paint us on a corner from
which it may be difficult to get out.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 5/23/16 10:30 PM, Craig Ringer wrote:
> I find it notable that no popular ORM has bothered adopting PostgreSQL's
> inheritance features, and instead just use big left joins or repeated
> SELECTs to implement parent/child relationships, with foreign keys
> enforcing constraints.

Since when do we consider ORMs to be an example of how to do good 
database design?

In this case, I'm sure no ORM uses the feature because either the ORM's 
authors have no clue it exists (except maybe for partitioning) or 
because we're the only mainstream database that has it.

> I consider inheritance mostly useless without the ability to have UNIQUE
> indexes that span a parent relation and all its children. You can use
> them for partitioning only by sacrificing a bunch of integrity
> protection or creating messy chains of FKs between individual partitions.

There's ways around this issue, but I agree that it's a very unhappy 
situation right now.

> I'd rather like to quietly deprecate inheritance and eventually remove
> it once we have real partitioning and some time has passed...

IMO that's a very unfortunate attitude to have for the "Worlds Most 
Advanced Open Source Database". Now that OO programming isn't treated as 
a magic bullet it's proven to be an extremely powerful tool, especially 
when used to encapsulate complex data. The 
ndarray->Series->DataFrame->Panel inheritance tree is a great example of 
this. Feature-wise, we need to be doing *more* of this kind of work, not 
less. Lack of support for OO paradigms was one of the drivers for NoSQL.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

There is one aspect of inheritance support which was not mentioned: 
polymorphic queries.
Actually polymorphism is the fundamental feature of OOP, without it 
there is no behavioral inheritance and inheritance can be considered 
just as "syntax sugar" for sharing some common subset of attributes 
between tables.

The main problem with supporting polymorphic queries is that SQL query 
returns set of tuples, not set of objects.
So there is no nice way to return both instances of based and derived 
tables. There are several alternatives
(for example return joined set of attributes in all derived tables, 
leaving missed as NULLs) but none of them is good.

There is more specific problem - polymorphic function calls, see:

http://postgresql.nabble.com/Polymorphic-function-calls-td5784772.html

which in principle can be solved... But looks like there is no such 
intention.

I do not know how much people will be interested in normal OO support 
from DBMS.
Right now PostgreQSL inheritance is used for different purposes: 
partitioning, FDW-based replication or sharding, ...
But IMHO it is mostly because current inheritance implementation just 
not make it possible to use it for OOP.
As far as I know before there were a lot of discussions whether arrays, 
json and other non-traditional for RDBMS types is needed.
But right now them are quite popular and frequently used. The same thing 
can happen with OO support: it will be popular if provide necessary 
functionality.

Right now OO DBMS applications are mostly using ORMs and them are not 
using PostgreSQL inheritance.
Mostly because ORMs are used to be more flexible in providing different 
approaches for implementing inheritance (for example in most cases 
placing all derived classes in single table works better than approach 
based on appending results of several queries).
Another reason is that ORM usually support more than one DBMS, and since 
most of them have no built-in support for inheritance,
ORM has to implement inheritance itself.
But I do not think that presence of ORM excludes necessity to have 
internal support of OO in DBMS.



On 24.05.2016 16:51, Jim Nasby wrote:
> On 5/23/16 10:30 PM, Craig Ringer wrote:
>> I find it notable that no popular ORM has bothered adopting PostgreSQL's
>> inheritance features, and instead just use big left joins or repeated
>> SELECTs to implement parent/child relationships, with foreign keys
>> enforcing constraints.
>
> Since when do we consider ORMs to be an example of how to do good 
> database design?
>
> In this case, I'm sure no ORM uses the feature because either the 
> ORM's authors have no clue it exists (except maybe for partitioning) 
> or because we're the only mainstream database that has it.
>
>> I consider inheritance mostly useless without the ability to have UNIQUE
>> indexes that span a parent relation and all its children. You can use
>> them for partitioning only by sacrificing a bunch of integrity
>> protection or creating messy chains of FKs between individual 
>> partitions.
>
> There's ways around this issue, but I agree that it's a very unhappy 
> situation right now.
>
>> I'd rather like to quietly deprecate inheritance and eventually remove
>> it once we have real partitioning and some time has passed...
>
> IMO that's a very unfortunate attitude to have for the "Worlds Most 
> Advanced Open Source Database". Now that OO programming isn't treated 
> as a magic bullet it's proven to be an extremely powerful tool, 
> especially when used to encapsulate complex data. The 
> ndarray->Series->DataFrame->Panel inheritance tree is a great example 
> of this. Feature-wise, we need to be doing *more* of this kind of 
> work, not less. Lack of support for OO paradigms was one of the 
> drivers for NoSQL.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On Tue, May 24, 2016 at 9:47 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
> On 24 May 2016 at 22:45, Konstantin Knizhnik <k.knizhnik@postgrespro.ru>
> wrote:
>>
>> There is one aspect of inheritance support which was not mentioned:
>> polymorphic queries.
>> Actually polymorphism is the fundamental feature of OOP, without it there
>> is no behavioral inheritance and inheritance can be considered just as
>> "syntax sugar" for sharing some common subset of attributes between tables.
>>
>> The main problem with supporting polymorphic queries is that SQL query
>> returns set of tuples, not set of objects.
>> So there is no nice way to return both instances of based and derived
>> tables. There are several alternatives
>> (for example return joined set of attributes in all derived tables,
>> leaving missed as NULLs) but none of them is good.
>
>
> Exactly. We have a sort-of-object-ish storage option, but none of the
> surrounding stuff to make it useful for actual OO / object-relational work.
>
> The "joined set of attributes" approach is exactly what ORMs already do, and
> many direct implementations of the same idea will use too. So we'd offer no
> advantage over what they already do in a way that works with multiple
> DBMSes, except we might be able to do it faster. Maybe.
>
> The lack of polymorphism is critical. It's not really usefully OO but it
> costs you important relational features if you use it. We have some very
> limited polymorphism in the sense that you can query the parent table and
> see rows in child tables, but you only get the subset of cols that exists at
> that level of the heirarchy.
>
> One thing I'd like to explore one day is a nice, user-friendly way to
> express "SELECT this row and the corresponding sets of rows from [these
> tables and their children in turn] as a structured object". Right now users
> have to write series of LEFT JOINs and de-duplicate the left-hand sides. Or
> do multiple queries (n+1 selects), possibly expensively with repeated join
> work involved. Or they have to write pretty baroque queries to construct a
> jsonb object with jsonb_agg with multiple levels of group-by in
> subqueries-in-from. We should be able to do this for them, so they can say
>
> SELECTOBJECT customer
>   CHILD JOIN invoice ON (customer.customer_id = invoice.customer_id AND
> invoice_date > $1)
>   CHILD JOIN invoiceline USING (invoice_id)
>   CHILD JOIN address USING (customer_id)
> WHERE customer.in_debt_collections;
>
> instead of the current nested mess of aggregation and subqueries needed,
> like:
>
> SELECT
>     to_jsonb(customer) || (
>       SELECT jsonb_build_object('invoices', jsonb_agg(invoice_obj))
>       FROM (
>         SELECT to_jsonb(invoice) || jsonb_build_object('invoice_lines',
> jsonb_agg(invoice_line))
>         FROM invoice
>         LEFT OUTER JOIN invoice_line ON (invoice.invoice_id =
> invoice_line.invoice_id)
>         WHERE invoice.customer_id = customer.customer_id AND invoice_date >=
> current_date
>         GROUP BY invoice.invoice_id
>       ) invoice_obj
>     ) || (
>       SELECT jsonb_build_object('addresses', jsonb_agg(address))
>       FROM address
>       WHERE address.customer_id = customer.customer_id
>     )
> FROM customer
> WHERE customer.in_debt_collections

Well, I don't know. There's a lot of ways to write that type of thing.
Personally, I tend to prefer to delay the serialization to json as
long as possible (although it's sometimes unavoidable) because it
keeps the query cleaner.  I also sometimes use the array() subquery
syntax for sake of brevity, but this query could be restructured to
use proper aggregation on all levels if you're concerned about
performance (this query would tend to underperform yours for very
large compositions because of the second subquery scan vs the hash
join OTOH, it's a faster serialization model).  I didn't test the
syntax, but you get the idea.

SELECT to_json(q)
FROM
( SELECT   c.*,   array(     SELECT       i.*,       array(         SELECT il         FROM invoice_line il
WHEREil.invoice_id = i.invoice_id       ) AS invoice_lines     FROM invoice i     WHERE i.customer_id = c.customer_id
ANDinvoice_date >= current_date   ) AS invoices FROM customer c WHERE c.in_debt_collections
 
) q

The point is this: the postgresql type system is flexible enough that
you can do arbitrary constructions pretty easy and the situation has
been one of continuous improvement over the last several releases.  It
isn't perfect, but json enhancements FWICT have made syntactical
approaches to the problem a dead end; json gets the job done is less
likely to cause problems with the SQL standard down the road.  For the
same set of reasons I no longer use crosstab.

In the 15+ years I've been watching postgres inheritance has gone
precisely nowhere, and there other ways to do the things it can do
that also supply a much broader range of use cases.  Plus, I'm biased:
I happen to think the 90's OO style of inheritance is pretty dumb :-).

merlin

On 5/24/16 8:52 PM, Craig Ringer wrote:> Absolutely, and I use OO heavily. But a half-assed "object relational">
featurein the database that only kind-of works isn't OO, and it's> generally less useful than using existing
relational-to-oomodelling> techniques like FK-related 1:1 child tables for specialisation.
 

How is it less useful than that? To me, the FK "solution" is the 
absolute worst of everything: you still have all the separate child 
tables that you must explicitly query *and* you have to get all the 
joins correct as well. And hope it doesn't have horrible performance.

> Note that there was enough enthusiasm to adopt whole new database
> engines, but not enough to use PostgreSQL's existing features for that.
> Partly because they suck. In particular, people looking for this tend to
> want to be able to create new subtypes without having to mess around
> making schema changes and modelling everything.

Which is a decision people have come to regret, because then your 
codebase somehow has to deal with 38 different versions of what a 
"customer" is.

> There's much more future in improving document-structured storage like
> jsonb, and possibly extending in future toward hybrid storage with some
> normal cols and some dynamic cols, than with Pg's
> pseudo-object-relational inheritance feature.

I don't see why we can't do both. There's many cases where more 
flexibility in what output tuples look like would be very valuable. The 
JSON features are one aspect; crosstab is another.

Postgres is well past the point where our relational features are the 
big selling point. It's now about scale, an incredibly robust storage 
engine, and all the extensiblity opportunities. We've moved from being 
an RDBMS to being a "Data Platform". Improving our OO capabilities just 
continues that.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

On 5/25/16 8:19 PM, Craig Ringer wrote:
>     Postgres is well past the point where our relational features are
>     the big selling point. It's now about scale, an incredibly robust
>     storage engine, and all the extensiblity opportunities. We've moved
>     from being an RDBMS to being a "Data Platform". Improving our OO
>     capabilities just continues that.
>
>
> Right. I'm just not convinced table inheritance is a useful part of that
> picture.

In it's current state it's certainly not the best tool in the toolbox, 
but IMHO there's still plenty of room for the ability to support 
restricted polymorphism, because of all the benefits of not allowing 
willy-nilly stuff in the schema. There's certainly other cases (ie: 
per-customer custom fields) where willy-nilly is what you actually need.

And certainly things that reduce table modification pain for *any* 
operations are most welcome! I think allowing queued backgrounded 
processes would be a huge win there. If we had real stored procedures 
(IE: ability to control transaction state) and a modest background 
scheduler then it wouldn't be hard to build that.
-- 
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461