Re: Does Postgres Object-Relational Syntax follow Standard?

Hi.

I was having a discussion with people at work about the Postgres object-relational syntax. The question came up: does this follow an SQL standard? Or is it rather a Postgres-only feature with different RDBMSs doing it differently?

I tried some quick checks on Google and Wikipedia but couldn't find clear answers. There seem to be different SQL standards (for example SQL 2003, SQL 2008), but from what I can see, these don't cover object-relational syntax.

-Will

On 10/17/2012 05:00 AM, Will Rutherdale (rutherw) wrote:
> Hi.
>
> I was having a discussion with people at work about the Postgres object-relational syntax.

What syntax specifically? Do you mean table inheritance and SELECT ONLY ?

--
Craig Ringer

On Tue, Oct 16, 2012 at 2:00 PM, Will Rutherdale (rutherw) <
rutherw(at)cisco(dot)com> wrote:

> Hi.
>
> I was having a discussion with people at work about the Postgres
> object-relational syntax. The question came up: does this follow an SQL
> standard? Or is it rather a Postgres-only feature with different RDBMSs
> doing it differently?
>
> I tried some quick checks on Google and Wikipedia but couldn't find clear
> answers. There seem to be different SQL standards (for example SQL 2003,
> SQL 2008), but from what I can see, these don't cover object-relational
> syntax.
>

Not really. The one area the standard discusses in this area, namely
single inheritance for structured data types, is not supported by
PostgreSQL. As far as I can tell, the SQL 2003 standard followed more or
less the approach Illustra (which began as a Pg fork but had a totally
independent SQL implementation) but limited it to structured data types
only and thus avoided issues like jagged rows (which survive in Informix,
but pose practical programming challenges and therefore have never been
supported on PostgreSQL). The one area that is supported is CREATE TABLE
foo OF TYPE bar; but that's pretty anemic support if you ask me.

In my view, while there are rough edges, the PostgreSQL approach is richer
than the SQL 2003 approach, and I suspect the reason for the lack of SQL
2003 UNDER supertype support is that there hasn't been sufficient demand to
justify implementing it. This isn't a commonly used feature of Oracle or
DB2.....

I would say that while there are some object-relational aspects to SQL
2003, the overlap between those and the PostgreSQL model is non-existent
for practical purposes. I have actually really come to like the PostgreSQL
model.

Best Wishes,
Chris Travers

> -----Original Message-----
> From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
> Sent: 16 October 2012 21:27
> To: Will Rutherdale (rutherw)
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Does Postgres Object-Relational Syntax follow
> Standard?
>
> On 10/17/2012 05:00 AM, Will Rutherdale (rutherw) wrote:
> > Hi.
> >
> > I was having a discussion with people at work about the Postgres object-
> relational syntax.
>
> What syntax specifically? Do you mean table inheritance and SELECT ONLY ?

Just the basic Postgres Object notation, such as CREATE TABLE ... INHERITS, along with related syntax one might use with it. The ONLY keyword would be part of that.

People looking at Postgres might reasonably ask, what does its Object support do, and does it follow a standard.

-Will

From: Chris Travers [mailto:chris(dot)travers(at)gmail(dot)com]
Sent: 16 October 2012 22:37
To: Will Rutherdale (rutherw)
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Does Postgres Object-Relational Syntax follow Standard?

On Tue, Oct 16, 2012 at 2:00 PM, Will Rutherdale (rutherw) <rutherw(at)cisco(dot)com> wrote:
Hi.

Not really.  The one area the standard discusses in this area, namely single inheritance for structured data types, is not supported by PostgreSQL. As far as I can tell, the SQL 2003 standard followed more or less the approach Illustra (which began as  a Pg fork but had a totally independent SQL implementation) but limited it to structured data types only and thus avoided issues like jagged rows (which survive in Informix, but pose practical programming challenges and therefore have never been supported on PostgreSQL).  The one area that is supported is CREATE TABLE foo OF TYPE bar; but that's pretty anemic support if you ask me.

In my view, while there are rough edges, the PostgreSQL approach is richer than the SQL 2003 approach, and I suspect the reason for the lack of SQL 2003 UNDER supertype support is that there hasn't been sufficient demand to justify implementing it.  This isn't a commonly used feature of Oracle or DB2.....

I would say that while there are some object-relational aspects to SQL 2003, the overlap between those and the PostgreSQL model is non-existent for practical purposes.  I have actually really come to like the PostgreSQL model. 

Best Wishes,
Chris Travers

Thanks.

It sounds like Object-relational is decently supported by Postgres but there isn't much of a standard to follow.

Do you know whether there are any efficiency issues in Object-relational? For example, has anyone benchmarked whether queries on an INHERITS table are more or less efficient than the equivalent using a JOIN and conventional table design?

-Will