SQL Spec Compliance Questions

Folks,

Just got this "do we support it" questionnaire from a signficant commercial
entity vaguely interested in supporting PostrgreSQL. Since I'm often foggy
on the differences between the SQL99 and SQL92 spec definitions of things, I
thought I'd post it for feedback here:

1) SQL-92 SELECT, INSERT, UPDATE, DELETE
2) SQL-92 CREATE/DROP SCHEMA/TABLE
3) SQL-92 INFORMATION_SCHEMA or SQL/CLI or ODBC driver with metadata functions
4) SQL-99 CREATE TRIGGER/DROP TRIGGER
5) SQL-99 BLOB, CLOB Data Types up to 1MB
6) SQL-99 Distinct Types
7) SQL-99 Structured Types
8) SQL-99 Functions, Methods, Procedures
9) SQL-99 Collection Types
10) SQL-99 Typed tables and views
11) SQL-99 Recursion
12) SQL/CLI or ODBC driver supports asynchronous statement execution
13) SQL/CLI or ODBC driver Supports all required SQL features
14) Transaction management functions—begin transaction, commit, abort
15) Wire protocol documentation and software

My answers:

1) Yes
2) yes
3) Yes -- INFORMATION_SCHEMA
4) Yes, unless SQL99 has some wierd twist on triggers.
6), 7) Not sure what these are.
8) Functions, yes, and in PostgreSQL functions are capable of acting as
procedures.
9) ???
10) Also not sure
11) In development, expected within the next two versions. Currently we have
non-SQL-standard recursion by several methods.
12) No
13) Not sure.
14) Yes
15) I think so.

Feedback, please!

--
-Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh(at)agliodbs(dot)com> writes:
> Just got this "do we support it" questionnaire from a signficant commercial
> entity vaguely interested in supporting PostrgreSQL.

This looks more like an underling with a checklist than a serious
inquiry. Can you get them to specify particular capabilities that they
need? In sufficient detail that we could actually answer?

regards, tom lane

Tom,

> This looks more like an underling with a checklist than a serious
> inquiry. Can you get them to specify particular capabilities that they
> need? In sufficient detail that we could actually answer?

I'll ask, but I'm not sure that I can. The particular company is very
bureaucratic, and I doubt the person who asked me has any control over the
questionnaire.

--
-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh(at)agliodbs(dot)com
and data management solutions (415) 565-7293
for law firms, small businesses fax 651-9224
and non-profit organizations. San Francisco

Tom,

> This looks more like an underling with a checklist than a serious
> inquiry. Can you get them to specify particular capabilities that they
> need? In sufficient detail that we could actually answer?

Ok, talked with him. They are trying to plan OO-->DB mapping in 3
programming languages for a large project. Large enough that they would
cost out implementing these SQL99 features for us if they like PostgreSQL
otherwise. But they *do* need to complete the checklist for each candidate
database system.

So ... can anyone more familiar with SQL99 than me give some feedback?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:

> 6) SQL-99 Distinct Types
> 7) SQL-99 Structured Types

> 9) SQL-99 Collection Types

> 10) SQL-99 Typed tables and views

> My answers:
>
> 6), 7) Not sure what these are.
Here's the section in SQL99:

4.8 User-defined types
A user-defined type is a schema object, identified by a
<user-defined type name>. The definition of a user-defined type
specifies a number of components, including in particular a list of
attribute definitions. Although the attribute definitions are said
to define the representation of the userdefined type, in fact they
implicitly define certain functions (observers and mutators) that
are part of the interface of the user-defined type; physical
representations of user-defined type values are
implementation-dependent.

The representation of a user-defined type is expressed either as a
single data type (some predefined data type, called the source
type), in which case the user-defined type is said to be a distinct
type, or as a list of attribute definitions, in which case it is
said to be a structured type.

So if I read that correctly, they are user defined types, that are
either scalar (distinct) or composite (structured) -- so I'd say yes.

> 9) ???

From SQL99:

4.11 Collection types
A collection is a composite value comprising zero or more elements
each a value of some data type DT. If the elements of some
collection C are values of DT, then C is said to be a collection of
DT. The number of elements in C is the cardinality of C. The term
‘‘element’’ is not further defined in this part of ISO/IEC 9075.
The term ‘‘collection’’ is generic, encompassing various types (of
collection) in connection with each of which, individually, this
part of ISO/IEC 9075 defines primitive type constructors and
operators. This part of ISO/IEC 9075 supports one collection type,
arrays.

We are not yet fully compliant with SQL99 arrays, but not too far off
either, I think. We have some extensions to SQL99 behavior, that would
require breaking backward compatibility in order to do away with them.
For example, SQL99 arrays *always* start with a lower bound of 1, if I
read the spec correctly. Also multidimensional arrays in SQL99 are
"arrays of arrays", which is not quite the same as our multidimensional
arrays.

> 10) Also not sure

SQL99:

4.16.2 Referenceable tables, subtables, and supertables
A table BT whose row type is derived from a structured type ST is
called a typed table. Only a base table or a view can be a typed
table. A typed table has columns corresponding, in name and
declared type, to every attribute of ST and one other column REFC
that is the self-referencing column of BT; let REFCN be the
<column name> of REFC. The declared type of REFC is necessarily
REF(ST) and the nullability characteristic of REFC is known not
nullable. If BT is a base table, then the table constraint
‘‘UNIQUE(REFCN)’’ is implicit in the definition of BT. A typed
table is called a referenceable table. A self-referencing column
cannot be updated. Its value is determined during the insertion
of a row into the referenceable table. The value of a
system-generated selfreferencing column and a derived
self-referencing column is automatically generated when the row
is inserted into the referenceable table. The value of a
user-generated self-referencing column is supplied as part of the
candidate row to be inserted into the referenceable table.

I really don't quite understand this, but I don't think we have it ;-)

HTH,

Joe

Joe,

Thanks for your help!

> 4.16.2 Referenceable tables, subtables, and supertables
> A table BT whose row type is derived from a structured type ST is
> called a typed table. Only a base table or a view can be a typed
> table. A typed table has columns corresponding, in name and
> declared type, to every attribute of ST and one other column REFC
> that is the self-referencing column of BT; let REFCN be the
>
> I really don't quite understand this, but I don't think we have it ;-)

Ye Gods and Little Fishes!!

Was the SQL99 Committee smoking crack, or what? What the heck is that
*for*?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
>>4.16.2 Referenceable tables, subtables, and supertables
>> A table BT whose row type is derived from a structured type ST is
>> called a typed table. Only a base table or a view can be a typed
>> table. A typed table has columns corresponding, in name and
>> declared type, to every attribute of ST and one other column REFC
>> that is the self-referencing column of BT; let REFCN be the
>>
>>I really don't quite understand this, but I don't think we have it ;-)
> Was the SQL99 Committee smoking crack, or what? What the heck is that
> *for*?

After re-reading it, I think it is related to (or at least similar to)
the work Tom is currently doing to allow composite types as table
attributes.

Joe

Joe,

> After re-reading it, I think it is related to (or at least similar to)
> the work Tom is currently doing to allow composite types as table
> attributes.

That's what I thought at first as well, and told the requestor that we would
support them in the next two versions. But reading that paragraph makes me
think that the type is somehow supposed to contain metadata or summary data
for the table itself.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Joe Conway <mail(at)joeconway(dot)com> writes:
>>> 4.16.2 Referenceable tables, subtables, and supertables
>>> A table BT whose row type is derived from a structured type ST is
>>> called a typed table. Only a base table or a view can be a typed
>>> table. A typed table has columns corresponding, in name and
>>> declared type, to every attribute of ST and one other column REFC
>>> that is the self-referencing column of BT; let REFCN be the

> After re-reading it, I think it is related to (or at least similar to)
> the work Tom is currently doing to allow composite types as table
> attributes.

The "structured type" stuff seems closely related, but I do not
understand the business about a "self-referencing column". I have a
feeling that it might be a mutant version of our notion of inheritance
...

regards, tom lane

Josh Berkus wrote:
> > 4.16.2 Referenceable tables, subtables, and supertables
> > A table BT whose row type is derived from a structured type
> > ST is called a typed table. Only a base table or a view can be a
> > typed table. A typed table has columns corresponding, in name and
> > declared type, to every attribute of ST and one other column REFC
> > that is the self-referencing column of BT; let REFCN be the
> >
> > I really don't quite understand this, but I don't think we have it
> > ;-)
>
> Ye Gods and Little Fishes!!
>
> Was the SQL99 Committee smoking crack, or what? What the heck is
> that *for*?

Object/relational mapping?

Josh Berkus wrote:
> Just got this "do we support it" questionnaire from a signficant
> commercial entity vaguely interested in supporting PostrgreSQL.
> Since I'm often foggy on the differences between the SQL99 and SQL92
> spec definitions of things, I thought I'd post it for feedback here:

The list of supported features can be found here:
http://www.postgresql.org/docs/7.4/static/features.html

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joe Conway <mail(at)joeconway(dot)com> writes:
> >>> 4.16.2 Referenceable tables, subtables, and supertables
> >>> A table BT whose row type is derived from a structured type ST is
> >>> called a typed table. Only a base table or a view can be a typed
> >>> table. A typed table has columns corresponding, in name and
> >>> declared type, to every attribute of ST and one other column REFC
> >>> that is the self-referencing column of BT; let REFCN be the
>
> > After re-reading it, I think it is related to (or at least similar to)
> > the work Tom is currently doing to allow composite types as table
> > attributes.
>
> The "structured type" stuff seems closely related, but I do not
> understand the business about a "self-referencing column". I have a
> feeling that it might be a mutant version of our notion of inheritance
> ...

hmm, for some moments I thought on things like the OID attribute
(on "system generated attrs")

Who we could ask to explain that ?!

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

* Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> Josh Berkus wrote:
> > > 4.16.2 Referenceable tables, subtables, and supertables
> > > A table BT whose row type is derived from a structured type
> > > ST is called a typed table. Only a base table or a view can be a
> > > typed table. A typed table has columns corresponding, in name and
> > > declared type, to every attribute of ST and one other column REFC
> > > that is the self-referencing column of BT; let REFCN be the
> > >
> > > I really don't quite understand this, but I don't think we have it
> > > ;-)
> >
> > Ye Gods and Little Fishes!!
> >
> > Was the SQL99 Committee smoking crack, or what? What the heck is
> > that *for*?
>
> Object/relational mapping?
hmm. any example for that stuff ?

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

I missed the first part of this question.
But perhaps I can explain the answer if I heard
the whole question. Both Illustra (postgres) and Informix
implemented typed tables.

--elein
============================================================
elein(at)varlena(dot)com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.

On Thu, Jun 03, 2004 at 07:19:06PM +0200, Enrico Weigelt wrote:
> * Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Joe Conway <mail(at)joeconway(dot)com> writes:
> > >>> 4.16.2 Referenceable tables, subtables, and supertables
> > >>> A table BT whose row type is derived from a structured type ST is
> > >>> called a typed table. Only a base table or a view can be a typed
> > >>> table. A typed table has columns corresponding, in name and
> > >>> declared type, to every attribute of ST and one other column REFC
> > >>> that is the self-referencing column of BT; let REFCN be the
> >
> > > After re-reading it, I think it is related to (or at least similar to)
> > > the work Tom is currently doing to allow composite types as table
> > > attributes.
> >
> > The "structured type" stuff seems closely related, but I do not
> > understand the business about a "self-referencing column". I have a
> > feeling that it might be a mutant version of our notion of inheritance
> > ...
>
> hmm, for some moments I thought on things like the OID attribute
> (on "system generated attrs")
>
> Who we could ask to explain that ?!
>
> cu
> --
> ---------------------------------------------------------------------
> Enrico Weigelt == metux IT service
>
> phone: +49 36207 519931 www: http://www.metux.de/
> fax: +49 36207 519932 email: contact(at)metux(dot)de
> cellphone: +49 174 7066481
> ---------------------------------------------------------------------
> -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
> ---------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly

* elein <elein(at)varlena(dot)com> wrote:

Hi,

> I missed the first part of this question.
> But perhaps I can explain the answer if I heard
> the whole question. Both Illustra (postgres) and Informix
> implemented typed tables.
What exactly does 'typed tables' mean ?
Someone here on the list (dont remember who it was ...), sayd
something about derived tables. This works fine w/ psql.

cu
--
---------------------------------------------------------------------
Enrico Weigelt == metux IT service

phone: +49 36207 519931 www: http://www.metux.de/
fax: +49 36207 519932 email: contact(at)metux(dot)de
cellphone: +49 174 7066481
---------------------------------------------------------------------
-- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
---------------------------------------------------------------------

A typed table is an type which happens to be
a table. They are also known as composite types
or row types.

We have these.

CREATE TYPE deptavgs AS ( yr int, mon int, minsal int, maxsal int, avgsalary int8);

In PostgreSQL you can use these table types in order
to return tuples from plpgsql functions.

CREATE or REPLACE FUNCTION avgdept() RETURNS deptavgs AS
'
DECLARE
r deptavgs%rowtype;

...
return r;
...

This should also allow you to do a:
create table foo as deptavgs;

If I recall correctly, PostgreSQL does not
support this syntax. But I'm not sure.

The concept is only tricky when you distinguish
between a row which is like a 1 dimensional array
and a table which is 2 dimensional. The row returning
functions return a row at a time.

There is also the concept of an unnamed row type
which is the temporary type of a result of a select.

In both Illustra and Informix IUS, the row type
was treated as a first class SQL type in that you
could create tables containing tables. PostgreSQL
does not support this.

create table dept_aggs (
deptid integer,
salavgs deptavgs,
...
);

The elements of the salavgs column are accessible
with the following syntax. (My memory may be a bit
faulty on this one.)

dep_aggs == table
dep_aggs.deptid == table.column
dep_aggs.salavgs == table.table
dep_aggs.salavgs.minsal == table.column

You could do a
select salvags.* from dep_aggs where dep_aggs.dept_id = 1;

The return values would be of the type deptavgs.

I'm sure this is more than what was asked. I reviewed
a review of the SQL2003 standards with regards to PostgreSQL
in General Bits Issue #71 and #73.
http://www.varlena.com/GeneralBits/71
http://www.varlena.com/GeneralBits/72

--elein
============================================================
elein(at)varlena(dot)com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.

On Thu, Jun 03, 2004 at 08:45:27PM +0200, Enrico Weigelt wrote:
> * elein <elein(at)varlena(dot)com> wrote:
>
> Hi,
>
> > I missed the first part of this question.
> > But perhaps I can explain the answer if I heard
> > the whole question. Both Illustra (postgres) and Informix
> > implemented typed tables.
> What exactly does 'typed tables' mean ?
> Someone here on the list (dont remember who it was ...), sayd
> something about derived tables. This works fine w/ psql.
>
>
> cu
> --
> ---------------------------------------------------------------------
> Enrico Weigelt == metux IT service
>
> phone: +49 36207 519931 www: http://www.metux.de/
> fax: +49 36207 519932 email: contact(at)metux(dot)de
> cellphone: +49 174 7066481
> ---------------------------------------------------------------------
> -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops --
> ---------------------------------------------------------------------
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Elein,

> A typed table is an type which happens to be
> a table. They are also known as composite types
> or row types.

Well, Tom was working on this for spec compliance. I don't know if he
completed it.

Of course, it doesn't answer the two corrolary questions:

1) Why would one want a typed table?

2) Aren't typed tables a big violation of relational database design?

--
Josh Berkus
Aglio Database Solutions
San Francisco

You would want a typed table if data particular
to a row took that form. Where you would want
something like a 2-D array which was *queryable*.

You could achieve the same effect by using a foreign
key.

In postgres and Illustra, references were possible.
That is, storing the OID (REF) of a table in a column of another
table achieving the same effect. SQL syntax support
was needed for the REF type. I believe this was removed
or suppressed in PostgreSQL.

It could be a violation of relational database design
if used improperly. Codd's rules say nothing about
nested structures. If the data in the column.table
was not unique to the row in question, then it would
violate the normalization rules. But this is true
of any data. Data is data.

An ORDBMS is type blind where ever possible. That means
a table in a column is just data. The only thing that
makes it different is the additional SQL syntax to support access.

elein

On Thu, Jun 03, 2004 at 04:09:51PM -0700, Josh Berkus wrote:
> Elein,
>
> > A typed table is an type which happens to be
> > a table. They are also known as composite types
> > or row types.
>
> Well, Tom was working on this for spec compliance. I don't know if he
> completed it.
>
> Of course, it doesn't answer the two corrolary questions:
>
> 1) Why would one want a typed table?
>
> 2) Aren't typed tables a big violation of relational database design?
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco

Elein,

> In postgres and Illustra, references were possible.
> That is, storing the OID (REF) of a table in a column of another
> table achieving the same effect. SQL syntax support
> was needed for the REF type. I believe this was removed
> or suppressed in PostgreSQL.

Yeah, I'd like to see a return of table references as FKs. It would allow me
to eliminate all of these rather non-relational integer-based surrogate keys.

I don't think our OIDs would be the way to go on this, given the problems
already discussed with PG OIDs. I kinda like SyBase's solution, where they
have a system-acessable hash key of the data in the row.

--
-Josh Berkus
Aglio Database Solutions
San Francisco