Thread: abstract data types?
Hi all,
I'm sure this has become somewhat of a FAQ recently, but I couldn't
find any reference to casting composite types in the mailing lists.
I'm trying to figure out what support PostgreSQL offers for SQL99
abstract data types.
PostgreSQL version is cvs from about a week ago.
Given the following relations:
test=> \d c_adtattr Table "c_adtattr"
Attribute | Type | Modifier
-----------+------+----------
attr1 | text |
attr2 | text |
test=> \d c_adtparent Table "c_adtparent"
Attribute | Type | Modifier
-----------+-----------+----------
basetype | text |
adtattr | c_adtattr |
OK, now try and insert a tuple into the relation with the composite
attribute:
test=> INSERT INTO c_adtparent values ('basetype','{"adtr1a1","adtr1a2"}');
ERROR: Attribute 'adtattr' is of type 'c_adtattr' but expression is of
type 'unknown' You will need to rewrite or cast the expression
Is this use of the '{" delimiters correct? I took it from the array
docs, which was only reference that I could find to anything like
inserting values into composite types.
OK, try something stupid (cast as pg_type.typname):
test=> INSERT INTO c_adtparent values ('basetype', CAST
('{"adtr1a1","adtr1a2"}' AS c_adtattr));
ERROR: pg_atoi: error in "{"adtr1a1","adtr1a2"}": can't parse
"{"adtr1a1","adtr1a2"}"
OK, try insert into individual attributes:
test=> INSERT INTO c_adtparent (basetype, adtattr.attr1, adtattr.attr2)
VALUES ('basetype', CAST ('adtr1a1') AS text, CAST ('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
OK, try double dot syntax from SQL99:
test=> INSERT INTO c_adtparent (basetype, adtattr..attr1,
adtattr..attr2) VALUES ('basetype', CAST ('adtr1a1') AS text, CAST
('adtr1a2') AS text);
ERROR: parser: parse error at or near "."
So far, so bad. Am I doing something really stupid with the syntax,
should I be using a different approach, or does the support not yet
exist? If it's just my stupidity, can someone please give me some
pointers to exactly where I should RTFM?
OK, so why am I attempting this lunacy? I am interested in assisting
with the development of a data store for GIS. Looks like most of the
mooted schemas will involve the creation of large numbers of tables of
identical type. Each of these in turn contains possibly repeating
identical conceptual structures. So to me it made sense, rather than
producing application specific code to manage these, to see if support
could be added to the backend DBMS, so that other applications (e.g.
CAD) could make use of the functionality.
TIA for any assistance.
cheers,
John
--
----------------------------------------------------------------------
john reid e-mail john_reid@uow.edu.au
uproot your questions from their ground and the dangling roots will be
seen. more questions! -mentat zensufi
apply standard disclaimers as desired...
----------------------------------------------------------------------
John Reid <jgreid@uow.edu.au> writes:
> I'm trying to figure out what support PostgreSQL offers for SQL99
> abstract data types.
None, I fear. The stuff you are fooling with is leftover from the old
PostQuel language. Most of it is suffering from bit rot, because the
developers' focus has been on SQL92 compliance for the last six or seven
years. I hadn't realized that SQL99 had caught up to PostQuel in this
area ;-). Sounds like we will have to dust off some of that stuff and
get it working again. No promises about timeframe, unless someone
steps up to the plate to do the work...
regards, tom lane
On Sat, 20 Jan 2001, Tom Lane wrote: > None, I fear. The stuff you are fooling with is leftover from the old > PostQuel language. Most of it is suffering from bit rot, because the > developers' focus has been on SQL92 compliance for the last six or seven > years. I hadn't realized that SQL99 had caught up to PostQuel in this > area ;-). Sounds like we will have to dust off some of that stuff and > get it working again. No promises about timeframe, unless someone > steps up to the plate to do the work... What goes around comes around. :-) -- Brett http://www.chapelperilous.net/~bmccoy/ --------------------------------------------------------------------------- mixed emotions:Watching a bus-load of lawyers plunge off a cliff.With five empty seats.
Jim, > > I'm trying to figure out what support PostgreSQL > offers for SQL99 > > abstract data types. I'm a little curious why what you're attempting couldn't be done with two columns rather than inventing your own data type. As somebody who often rescues databases gone bad, composite data types have not earned a warm place in my heart ... -Josh Berkus
Hi Tom, listers, Thanks for the info. > On Sat, 20 Jan 2001, Tom Lane wrote: > > >> None, I fear. The stuff you are fooling with is leftover from the old >> PostQuel language. Most of it is suffering from bit rot, because the >> developers' focus has been on SQL92 compliance for the last six or seven >> years. > Damn! Not what I wanted to hear :-( >> I hadn't realized that SQL99 had caught up to PostQuel in this >> area ;-). > FWIW, this is actually one of the primary reasons that I became interested in PostgreSQL, before I even knew about SQL3/SQL99. Seems like such a cool idea :-) >> Sounds like we will have to dust off some of that stuff and >> get it working again. No promises about timeframe, unless someone >> steps up to the plate to do the work... > OK, what few coding skills I had are so rusty I'm pretty much back to square one, but I would like to help out where possible (Docs maybe?). Then again, might as well jump in the deep end, and have a look to see what needs doing anyway :-) Can you please give me some pointers as to where I should look in the docs and code to see how classes are currently handled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creatingclasses hides * Anything else that I should be aware of! For the moment I guess I don't need to worry about the parser, just how the operations related to the classes (both system and user) work/are implemented. Correct? > What goes around comes around. :-) And hits you in the back of the head just when you least expect it ... cheers, John ---------------------------------------------------------------------- john reid e-mail john_reid@uow.edu.au uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... ----------------------------------------------------------------------
Hi Josh et al, Sorry for the tardy reply, and thanks for your comments. Any suggestions or pointers on robust database design will be greatly appreciated. Josh Berkus wrote: > Jim, > > >>> I'm trying to figure out what support PostgreSQL >> >> offers for SQL99 >> >>> abstract data types. >> > I'm a little curious why what you're attempting couldn't be > done with two columns rather than inventing your own data > type. As somebody who often rescues databases gone bad, > composite data types have not earned a warm place in my > heart ... > > -Josh Berkus What we are attempting is the storage of vector data for a geographical (or spatial) information system in a database. We hope to base the implementation on the upcoming standard from the ISO TC/211 committee. More information can be found at http://FMaps.sourceforge.net/ - the webpages need a major revamp so the best place to look for current developments is in the mailing list archive. A good source of info can be found at http://gdal.velocet.ca/projects/osvecdb/index.html, especially relevant is the comparison of the SQL/MM, OGC, and ISO TC/211 standards (http://gdal.velocet.ca/projects/osvecdb/comp-mm-ogc-tc211.pdf ). To answer your question, it is a bit hard to say at the moment as the design schema for our project has only just been started. The draft versions of the ISO standard that I have seen use an object oriented data model, so to me it makes sense to try and keep the database schema as close as possible to this (minimise data impedance). Briefly, at its' simplest the schema will probably use a two tier approach. Tier 0ne ------ The original data stored in the most flexible way that we can think of, with associated metadata tables. Tier Two ------- These will effectively be persistent views on the T1 tables structured for efficient access by client applications. OK, as far I know no such beast as a persistent view exists in the SQL standards, but that is probably the best way to describe what I have in mind. Using views as currently implemented in PostgreSQL would probably not be viable as it is likely that, if multiple spatial reference systems are defined on a area of interest, reprojection of the geometry objects would be a performance killer. cheers, John
Mr. Reid, > To answer your question, it is a bit hard to say at the moment as the > design schema for our project has only just been started. The draft > versions of the ISO standard that I have seen use an object oriented > data model, so to me it makes sense to try and keep the database schema > as close as possible to this (minimise data impedance). > > Briefly, at its' simplest the schema will probably use a two tier approach. <snip> Let me preface this by saying that I know squat-all about building geometric databases. My background is in db's for accounting, billing, scheduling, and fundraising. Given that .., over the last 3 months, I have become a believer in C.J. Date and Fabian Pascal, who point out quite a few ways that object-oriented and relational approaches to data problems *cannot* be made to reconcile. See http://www.firstsql.com/dbdebunk for some examples of their objections. Of course, Date and Pascal reject Object Oriented approaches entirely, something I'm not ready to do ... but I do see that trying to build a database accessable to both a range of OODB tools and relationally compliant is not achievable. -Josh Berkus -- ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco
Hi Josh, all, Thanks for your comments. My 2c worth: Josh Berkus wrote: > Mr. Reid, > > >> To answer your question, it is a bit hard to say at the moment as the >> design schema for our project has only just been started. The draft >> versions of the ISO standard that I have seen use an object oriented >> data model, so to me it makes sense to try and keep the database schema >> as close as possible to this (minimise data impedance). >> >> Briefly, at its' simplest the schema will probably use a two tier approach. > > <snip> > > Let me preface this by saying that I know squat-all about building > geometric databases. My background is in db's for accounting, billing, > scheduling, and fundraising. Yes, definitely a very different beastie (as my aching head is proving). The more I learn about spatial information systems, the more I come to the conclusion that I know squat about them as well. As far as the relationship between the schemas for financial and spatial information systems goes, a book I have (on OO database management) goes so far as to say "that relational database systems do not adequately support these so-called non-standard applications." From the research that I have done, by far the best DBMS for these applications is Informix. Funny about that, having Postgres in its' ancestory :-) Unfortunately I can't speak from personal experience - I don't have any access to it, as at uni we are a Oracle/MS SQL Server/mySQL shop, and from my preliminary investigations none of these seem to cut it for this task as far as I am concerned :-( > Given that .., over the last 3 months, I have become a believer in C.J. > Date and Fabian Pascal, who point out quite a few ways that > object-oriented and relational approaches to data problems *cannot* be > made to reconcile. See http://www.firstsql.com/dbdebunk for some > examples of their objections. Interesting. This is a really cool site. Thanks. However I don't see how you draw the conclusion from what I have read on this site "that object-oriented and relational approaches to data problems *cannot* be made to reconcile." C.J. Date here seems to be arguing more about the semantics employed in UML modelling, Pascal more about the quality of database design. This site does give me the urge to read up on set theory - I've forgotten what little I once knew. In [DAT00] (Section 25.1 pg 863) Date states "we need do nothing to the relational model in order to achieve object functionality in relational systems - nothing, that is, except implement it, fully and properly, which most of today's systems have so signally failed to do." He mentions in the prelude to that statement (in a discussion of the incorporation of "proper data type support into the relational model") that "object-orientation" involves: 1. Proper data type support 2. Type inheritance (actually, he considers this as being part of 1.) He then states that "the support is already there [in the relational model -jgr], in the shape of domains (which we prefer to call types anyway)." > Of course, Date and Pascal reject Object Oriented approaches entirely, > something I'm not ready to do ... Hmmm, from what I've read I don't see it that way. My current understanding is that "we acknowledge the desirability of supporting certain features that are commonly regarded as aspects of object orientation. However, we believe that the features in question are orthogonal to (i.e. independent of) the relational model ..." ([DD00] Chapter 1, pg 6). Interesting, I just noticed the statement "is truly relational (unlike SQL)."! > but I do see that trying to build a > database accessable to both a range of OODB tools and relationally > compliant is not achievable. Sorry, disagree strongly here. My interest in PostgreSQL was sparked when I first came across a link to Postgres in a list of object-oriented databases. From a quick look at the docs (I think the ones I first looked at were for v6.5 or an even earlier version than that) I could see the potential for the enhanced data type support, that at the time I believed was essential for a GIS (or SIS) - or at least would be if the programmer's, or even more so the maintainer's, sanity was to be preserved. Actually, at the time I thought ADT style type support was already fully implemented. A little knowledge can be a dangerous thing, especially when mixed with a lack of sleep ;-) As far as I can tell, PostgreSQL has most, if not all, of the building blocks to supply support for abstract data types already in place. Whoever thought up the system catalogs (as well) was one very smart individual. Salutations, whoever you are! These are some of the potential problems for implementing abstract data types that I can see so far: * Inheritance is currently implemented at the relation level, rather than the type level. Is this simply a matterof changing the references in pg_inherits from pg_class.oid to pg_type.oid? Or would this cause major breakagesin other parts of the code? * The existing "CREATE TABLE tablename AS" syntax is incompatible (or needs tobe modified to comply) with the SQL99 syntax of "CREATE TABLE tablename AS typename"; * Code for creating a compositeattribute member currently implements them as a oid referencing a seperate table. According to Date thisis probably "not a Good Thing" (see [DAT00] Section 25.2 pg 865) - in this case relvar = object class rather thanhis preferred domain = object class. I assume the methods necessary to read and write complex attributes would be similar in nature to those employed for table access - correct? Oh, well. Back to tracing how procedures are called from the system catalogs I guess. From a previous post of mine: "Can you please give me some pointers as to where I should look in the docs and code to see how classes are currentlyhandled. I'm thinking specifically of: * How (and where) the access methods for class tuples are implemented and called. * Where the code for creating classes hides * Anything else that I should be awareof! For the moment I guess I don't need to worry about the parser, just how the operations related tothe classes (both system and user) work/are implemented. Correct?" Any help people can give me would be much appreciated. I'm already feeling a little lost. I hope people don't mind if I ask a lot of dumb questions over the next few weeks :-) Is this the appropriate list, or should I move over to hackers? Cheers, John Where I'm getting my info from: Book [Dat00] Author: Date, C.J. Title: An Introduction to Database Systems Publisher: Addison Wesley Longman Date: 2000 Edition: 7th Book [DD00] Author: Date, C.J.; Darwen, Hugh Title: Foundation for Future Database Systems : the Third Manifesto Publisher: Addison Wesley Date: 2000 Edition: 2nd Book [SB99] Author: Stonebraker, Michael; Brown, Paul Title: Object-Relational DBMSs : Tracking the Next Great Wave Publisher: Morgan Kaufmann Date: 1999 Edition: 2nd Book [For99] Author: Fortier, Paul Title: SQL3 Implementing the SQL Foundation Standard Publisher: McGraw Hill Date: 1999 ---------------------------------------------------------------------- john reid e-mail john_reid@uow.edu.au uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... ----------------------------------------------------------------------
John, > Thanks for your comments. My 2c worth: That was at least $1.50 worth. Teach me to speak 'off the cuff' on this list ... > As > far as the > relationship between the schemas for financial and > spatial information > systems goes, a book I have (on OO database management) > goes so far as > to say "that relational database systems do not > adequately support these > so-called non-standard applications." I'd agree with you, I'm afraid. Most of the "spatial database projects" I've been familiar with involved either: a) completely custom software, or b) *lots* of RAM and processing power, or c) both. > Unfortunately I can't speak from personal > experience - I > don't have any access to it, as at uni we are a Oracle/MS > SQL > Server/mySQL shop, and from my preliminary investigations > none of these > seem to cut it for this task as far as I am concerned :-( A definite No for 2 of the above. MySQL was built to be fast and light, with a minimal feature set. As a semi-certified MS SQL Admin, I can tell you that MS SQL Server isn't up to anything better than a *simple* accounting database. Oracle, on the other hand, claims to do anything. They really have no geometic support? > Interesting. This is a really cool site. Thanks. However > I don't see how > you draw the conclusion from what I have read on this > site "that > object-oriented and relational approaches to data > problems *cannot* be > made to reconcile." C.J. Date here seems to be arguing > more about the > semantics employed in UML modelling, Pascal more about > the quality of > database design. This site does give me the urge to read > up on set > theory - I've forgotten what little I once knew. You're right, that's what's currently on the site. I'm basing my opinion more on the earlier writings of Pascal ... and porbably on my own expereinces. Of course, we could ask him. > In [DAT00] (Section 25.1 pg 863) Date states "we need do > nothing to the > relational model in order to achieve object functionality > in relational > systems - nothing, that is, except implement it, fully > and properly, > which most of today's systems have so signally failed to > do." Yeah. Few systems bother even to fully implement the SQL standard fully ... and SQL 99 was as much a product of politics in the computer industry as logic. For example, I agree with Pascal & Date that BLOBs are a bad idea, and a violation of relational priniciples (being data that cannot be stores as a value in a column in a relation). One need only look at the terrible and persistent implementation problems for BLOB support in various platforms for proof of this. > He then states that "the support is already there [in the > relational > model -jgr], in the shape of domains (which we prefer to > call types > anyway)." > Yeah. Real DOMAIN and TYPE support (which are really two diffetent things, a Domain being a specification for a more general Type) in Postgres would be teriffic. How about it, Tom, Stephen? > Chapter 1, pg 6). Interesting, I just noticed the > statement "is truly > relational (unlike SQL)."! Yes -- see my comments above. Market pressues and politics have caused the ISO to abandon relational standards in formulating the SQL standard in many areas. > Sorry, disagree strongly here. Ok. I'm probably just biased, anyway, from being burned by DB tools claiming both OO and SQL-relational support. > As far as I can tell, PostgreSQL has most, if not all, of > the building > blocks to supply support for abstract data types already > in place. > Whoever thought up the system catalogs (as well) was one > very smart > individual. Salutations, whoever you are! I'd definitely stand back and applaud any effort to support this. When I first started with PostgreSQL, I thought it was a really nifty idea, until I tried to build a database on it. Puls I soon discovered that nifty ideas do not a payment-processing database make :-( > Any help people can give me would be much appreciated. > I'm already > feeling a little lost. I hope people don't mind if I ask > a lot of dumb > questions over the next few weeks :-) Is this the > appropriate list, or > should I move over to hackers? You should probably cross-post. This list is the place to see if a number of other developers are interested in the functionality you propose (yes), hackers is probably the place to ask how to make the actual changes. I can't help. Heck, I can't even get 7.1 beta to run on an alternate port. -Josh Berkus P.S. BTW, John, I'm thrilled to get a discussion of issues, going here in addition to the how-tos!
Hi again, Josh Berkus wrote: > John, > > > Thanks for your comments. My 2c worth: > > That was at least $1.50 worth. Teach me to speak 'off the > cuff' on this list ... Just because I went out and brought a stack of books doesn't mean that I actually know anything ;-) > > As > > far as the > > relationship between the schemas for financial and > > spatial information > > systems goes, a book I have (on OO database management) > > goes so far as > > to say "that relational database systems do not > > adequately support these > > so-called non-standard applications." > > I'd agree with you, I'm afraid. Most of the "spatial > database projects" I've been familiar with involved either: > a) completely custom software, or b) *lots* of RAM and > processing power, or c) both. These are some of the things that have me scared - actually these considerations are main reason that I was was thinking of a two-tier approach. The data "views" the applications would access directly would be optimised for performance, the underlying store for flexible storage and data integrity. I figure big disks are a lot cheaper than a dirty great machine. Especially if I can use IDE RAID and run on otherwise throwaway hardware - we don't need 100% uptime, just need to make sure we don't loose the base data. The ability to get it all running again in several hours would be a definite plus as well! > > Unfortunately I can't speak from personal > > experience - I > > don't have any access to it, as at uni we are a Oracle/MS > > SQL > > Server/mySQL shop, and from my preliminary investigations > > none of these > > seem to cut it for this task as far as I am concerned :-( > > A definite No for 2 of the above. MySQL was built to be > fast and light, with a minimal feature set. As a > semi-certified MS SQL Admin, I can tell you that MS SQL > Server isn't up to anything better than a *simple* > accounting database. Oracle, on the other hand, claims to > do anything. They really have no geometic support? Oracle does have geometric support (Spatial Data Cartridge I think it's called). My main concern after reading the Oracle8 technical reference was the underlying fundamentals. From what I could see, Oracle seems to have just slapped an object-relational type syntax over the original relational engine. IIRC, all datatypes were still rigidly structured i.e. fixed length arrays support only, no variable length data types etc. For any application trying to model the vagaries of the "real" world, I feel that this can only lead to tears (or a tendency for DBA's to go insane) sooner or later - probably sooner. BTW, if any insomniacs out there are looking for a cure, try reading the O8TR manual. I can recall falling asleep after about 1 page just after consuming about 3 cups of strong coffee - which would normally have me bouncing off ceilings :-) > > Interesting. This is a really cool site. Thanks. However > > I don't see how > > you draw the conclusion from what I have read on this > > site "that > > object-oriented and relational approaches to data > > problems *cannot* be > > made to reconcile." C.J. Date here seems to be arguing > > more about the > > semantics employed in UML modelling, Pascal more about > > the quality of > > database design. This site does give me the urge to read > > up on set > > theory - I've forgotten what little I once knew. > > You're right, that's what's currently on the site. I'm > basing my opinion more on the earlier writings of Pascal ... > and porbably on my own expereinces. Of course, we could ask > him. > > > In [DAT00] (Section 25.1 pg 863) Date states "we need do > > nothing to the > > relational model in order to achieve object functionality > > in relational > > systems - nothing, that is, except implement it, fully > > and properly, > > which most of today's systems have so signally failed to > > do." > > Yeah. Few systems bother even to fully implement the SQL > standard fully ... and SQL 99 was as much a product of > politics in the computer industry as logic. > > For example, I agree with Pascal & Date that BLOBs are a bad > idea, and a violation of relational priniciples (being data > that cannot be stores as a value in a column in a relation). > One need only look at the terrible and persistent > implementation problems for BLOB support in various > platforms for proof of this. > > > > He then states that "the support is already there [in the > > relational > > model -jgr], in the shape of domains (which we prefer to > > call types > > anyway)." > > > > Yeah. Real DOMAIN and TYPE support (which are really two > diffetent things, a Domain being a specification for a more > general Type) in Postgres would be teriffic. Also, IIRC, OO types have methods, domains have only values. I'm not 100% sure on what distinction is made between them in SQL99, whether domains are included in the spec or whether this concept is covered instead by the create distinct type statement. No book to check at the moment :-) > How about it, > Tom, Stephen? Just an idea :-) I'm no cvs guru, but isn't it possible to define seperate branches within the same cvs tree? That way, anyone crazy enough to get involved in this could experiment without running the risk of breaking the MAIN branch, while still keeping up with the latest changes to the code in other areas. Then, when ready, any required changes could be merged back into the main tree. I think some approach similar to this is probably a "Good Thing", as some changes will probably be necessary in the core of the system, resulting in a significant risk of major breakages that we probably don't want to subject other developers to. > > Chapter 1, pg 6). Interesting, I just noticed the > > statement "is truly > > relational (unlike SQL)."! > > Yes -- see my comments above. Market pressues and politics > have caused the ISO to abandon relational standards in > formulating the SQL standard in many areas. > > > Sorry, disagree strongly here. > > Ok. I'm probably just biased, anyway, from being burned by > DB tools claiming both OO and SQL-relational support. > > > As far as I can tell, PostgreSQL has most, if not all, of > > the building > > blocks to supply support for abstract data types already > > in place. > > Whoever thought up the system catalogs (as well) was one > > very smart > > individual. Salutations, whoever you are! > > I'd definitely stand back and applaud any effort to support > this. When I first started with PostgreSQL, I thought it > was a really nifty idea, until I tried to build a database > on it. Puls I soon discovered that nifty ideas do not a > payment-processing database make :-( Naivety is such a wonderful thing. I speak from experience. Now to get bitter, cynical and twisted :-) > > Any help people can give me would be much appreciated. > > I'm already > > feeling a little lost. I hope people don't mind if I ask > > a lot of dumb > > questions over the next few weeks :-) Is this the > > appropriate list, or > > should I move over to hackers? > > You should probably cross-post. This list is the place to > see if a number of other developers are interested in the > functionality you propose (yes), hackers is probably the > place to ask how to make the actual changes. > > I can't help. Heck, I can't even get 7.1 beta to run on an > alternate port. > > -Josh Berkus > > P.S. BTW, John, I'm thrilled to get a discussion of issues, > going here in addition to the how-tos! Cool. However, I'm just not up to the stage of asking the how-to's yet! cheers, John -- ---------------------------------------------------------------------- john reid e-mail john_reid@uow.edu.au technical officer room G02, building 41 school of geosciences phone +61 02 4221 3963 university of wollongong fax +61 02 4221 4250 uproot your questions from their ground and the dangling roots will be seen. more questions! -mentat zensufi apply standard disclaimers as desired... ----------------------------------------------------------------------