[SQL] Re: abstract data types?
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. > > > > 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 matter of changing the references in pg_inherits f
[HACKERS] wrong query plan in 7.1beta3
There seems to be an optimizer problem in 7.1beta3. The query you can see below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is that an 'index scan' has been changed to a 'seq scan'. Details: CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/.../lib/plpgsql.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL'; CREATE TABLE sd_tabla ( azonosito varchar(100) PRIMARY KEY ); CREATE TABLE sd_grant ( tabla varchar(100) REFERENCES sd_tabla(azonosito), gname varchar(100), sel bool NOT NULL DEFAULT 'f', upd bool NOT NULL DEFAULT 'f', ins bool NOT NULL DEFAULT 'f', del bool NOT NULL DEFAULT 'f', rul bool NOT NULL DEFAULT 'f', PRIMARY KEY (tabla, gname) ); create function or_(bool,bool) returns bool as 'begin return $1 or $2; end;' language 'plpgsql'; create aggregate aggr_or(basetype=bool, sfunc1=or_, stype1=bool,initcond1='f'); create view sd_user_grant as select tabla, usename, aggr_or(sel) as sel,aggr_or(ins) as ins,aggr_or(upd) as upd, aggr_or(del) as del,aggr_or(rul) as rul from sd_grant,pg_user where sd_grant.gname>pg_user.usename::varchar group by tabla,usename; explain select sel,ins,upd,del,rul from sd_user_grant where usename::varchar='1016' and tabla='cikk'; Here you can see the outputs of the EXPLAIN statement: 7.0.2: Aggregate (cost=5.95..5.96 rows=0 width=61) -> Group (cost=5.95..5.95 rows=1 width=61) -> Sort (cost=5.95..5.95 rows=1 width=61) -> Nested Loop (cost=0.00..5.94 rows=1 width=61) -> Seq Scan on pg_shadow (cost=0.00..1.75 rows=1 width=32) -> Index Scan using sd_grant_pkey on sd_grant (cost=0.00..4.08 rows=6 width=29) 7.1beta3: Subquery Scan sd_user_grant (cost=38.68..38.85 rows=1 width=61) -> Aggregate (cost=38.68..38.85 rows=1 width=61) -> Group (cost=38.68..38.73 rows=10 width=61) -> Sort (cost=38.68..38.68 rows=10 width=61) -> Nested Loop (cost=0.00..38.51 rows=10 width=61) -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=32) -> Seq Scan on sd_grant (cost=0.00..20.00 rows=1000 width=29) Unfortunately this query is a frequently used one in our application. So we can't upgrade to 7.1 until this works properly. Please help if there is a way to modify the query or consider it as a bug. TIA, Zoltan -- Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
Re: [HACKERS] wrong query plan in 7.1beta3
Kovacs Zoltan writes: > There seems to be an optimizer problem in 7.1beta3. The query you can see > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is > that an 'index scan' has been changed to a 'seq scan'. Details: > Subquery Scan sd_user_grant (cost=38.68..38.85 rows=1 width=61) > -> Aggregate (cost=38.68..38.85 rows=1 width=61) > -> Group (cost=38.68..38.73 rows=10 width=61) > -> Sort (cost=38.68..38.68 rows=10 width=61) > -> Nested Loop (cost=0.00..38.51 rows=10 width=61) > -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 >width=32) > -> Seq Scan on sd_grant (cost=0.00..20.00 rows=1000 >width=29) You haven't VACUUM ANALYZE'd the sd_grant table. Therefore the row estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) thought to be faster. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [HACKERS] wrong query plan in 7.1beta3
On Sat, 27 Jan 2001, Peter Eisentraut wrote: > Kovacs Zoltan writes: > > > There seems to be an optimizer problem in 7.1beta3. The query you can see > > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is > > that an 'index scan' has been changed to a 'seq scan'. Details: > > > Subquery Scan sd_user_grant (cost=38.68..38.85 rows=1 width=61) > > -> Aggregate (cost=38.68..38.85 rows=1 width=61) > > -> Group (cost=38.68..38.73 rows=10 width=61) > > -> Sort (cost=38.68..38.68 rows=10 width=61) > > -> Nested Loop (cost=0.00..38.51 rows=10 width=61) > > -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 >width=32) > > -> Seq Scan on sd_grant (cost=0.00..20.00 rows=1000 >width=29) > > You haven't VACUUM ANALYZE'd the sd_grant table. Therefore the row > estimate is way off (1000 vs 6) and thus a sequential scan is (correctly) > thought to be faster. > > Thanks, I tried it. tir=# explain select sel,ins,upd,del,rul from sd_user_grant where tabla='cikk' and usename::varchar='1016'; NOTICE: QUERY PLAN: Subquery Scan sd_user_grant (cost=8.00..8.03 rows=1 width=61) -> Aggregate (cost=8.00..8.03 rows=1 width=61) -> Group (cost=8.00..8.01 rows=2 width=61) -> Sort (cost=8.00..8.00 rows=2 width=61) -> Nested Loop (cost=0.00..7.99 rows=2 width=61) -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=32) -> Seq Scan on sd_grant (cost=0.00..3.81 rows=181 width=29) It seems to be a little bit faster, but it's still very-very slow. The 'seq scan' on sd_grant still remained. Zoltan -- Kov\'acs, Zolt\'an [EMAIL PROTECTED] http://www.math.u-szeged.hu/~kovzol ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
[SQL] Re: [HACKERS] wrong query plan in 7.1beta3
Kovacs Zoltan <[EMAIL PROTECTED]> writes: > There seems to be an optimizer problem in 7.1beta3. The query you can see > below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is > that an 'index scan' has been changed to a 'seq scan'. Details: This is fixed in current sources: I get Subquery Scan sd_user_grant (cost=5.16..5.22 rows=1 width=61) -> Aggregate (cost=5.16..5.22 rows=1 width=61) -> Group (cost=5.16..5.18 rows=3 width=61) -> Sort (cost=5.16..5.16 rows=3 width=61) -> Nested Loop (cost=0.00..5.14 rows=3 width=61) -> Seq Scan on pg_shadow (cost=0.00..1.01 rows=1 width=32) -> Index Scan using sd_grant_pkey on sd_grant (cost=0.00..4.07 rows=3 width=29) regards, tom lane
[SQL] Architectural question
Hello everybody. I would like to create a database. It's a structure of about 20 items (tables, views, etc.) Then I would like to make it a template, because I need 7 copies of this structure, possibly a growing number. The only problem is that there are four tables in it, which are general, so it would be the finest to make them common. For example in a database "commondb". But I don't know if I could somehow create a construction like this: SELECT * from sometable WHERE somecolumn IN (SELECT * FROM ::atable) Since now I used Delphi, and I could reference databases as directories in my file system, so it wasn't a problem. Any ideas to solve this? Thanks in advance, Baldvin
[SQL] looping through results of a SELECT
Hello! I'd like to write a function, that makes some calculations (perhaps applies another function) on every row of a result set returned by a SELECT query. I thought writing a WHILE loop would work, but I couldn't assign the individual rows to a variable. Then, I read about the FETCH command, and I tried to use that, but it dies with 'error near CURSOR' error when I try to use the function. I wrote a PL/PGSQL function, obviously. So, my question is: is there an easy way to assign the individual rows of a SELECT result to a variable in a function? Thanks in advance, Thomas Nagy
[SQL] RE: looping through results of a SELECT
Try using aggregate functions. Creating your own aggregate function is fairly easy and can produce the exact results you are looking for. In case this is not good enough, here is an example of some code I used to loop through rows in a table in pl/pgsql CREATE FUNCTION pending_post_transaction(int) RETURNS int AS ' DECLARE pMemberID ALIAS for $1; plrecord; rcint; BEGIN if (pMemberID is null) then RAISE NOTICE ''MemberID is null''; return 0; end if; select count(*) into rc from Pending P where P.MemberID = pMemberID; if (rc > 0) then else RAISE NOTICE ''No rows to process''; return 0; end if; FOR pl IN select * from Pending where MemberID = pMemberID LOOP if (pl.InventoryID > 0) then -- InventoryID is a column in the Pending table ... else RAISE NOTICE "The InventoryID is empty, skipping the update"; return 0; end if; if (pl.SpecialArrangement >= 0) then -- SpecialArrangement is also a column in the Pending table ... end if; END LOOP; return rc; END;' LANGUAGE 'plpgsql'; -Original Message- From: Nagy Tamas [SMTP:[EMAIL PROTECTED]] Sent: Saturday, January 27, 2001 12:16 PM To: [EMAIL PROTECTED] Subject:looping through results of a SELECT Hello! I'd like to write a function, that makes some calculations (perhaps applies another function) on every row of a result set returned by a SELECT query. I thought writing a WHILE loop would work, but I couldn't assign the individual rows to a variable. Then, I read about the FETCH command, and I tried to use that, but it dies with 'error near CURSOR' error when I try to use the function. I wrote a PL/PGSQL function, obviously. So, my question is: is there an easy way to assign the individual rows of a SELECT result to a variable in a function? Thanks in advance, Thomas Nagy
[SQL] Queries against multi-million record tables.
Hello all,
I am in the midst of taking a development DB into production, but the
performance has not been very good so far.
The DB is a decision based system, that currently has queries against tables
with up to 20million records (3GB table sizes), and at this point about a
25GB DB in total. {Later down the road up to 60million records and a DB of
up to 150GB is planned).
As I understand it, Oracle has some product called "parallel query" which
splits the table queried into 10 pieces and then does each one across as
many CPUs as possible, then puts it all back together again.
So my question is... based upon the messages I have read here, it does not
appear that PostgreSQL makes use of multiple CPUs, but only hands the next
query off to the next processor based upon operating system rules.
Therefore, what are some good ways to handle such large amounts of
information using PostgreSQL?
Michael Miyabara-McCaskey
Email: [EMAIL PROTECTED]
Web: http://www.miyabara.com/mykarz/
Mobile: +1 408 504 9014
[SQL] BTP_CHAIN errors fixed?
Hi, I have been using PostgreSQL-7.0.0 and have had the problem that, when searching a btree index that contains large numbers of duplicate keys, Postgres crashes with a BTP_CHAIN error. Now that I have installed 7.1beta3 the problem has seemingly been fixed. Was this problem actually fixed somewhere between 7.0.0 and 7.1beta3, or am I just getting lucky right now (just want to know before I put this into production :-))? Thanks, Mark
Re: [SQL] BTP_CHAIN errors fixed?
Mark Volpe <[EMAIL PROTECTED]> writes: > I have been using PostgreSQL-7.0.0 and have had the problem that, when > searching a btree index that contains large numbers of duplicate keys, > Postgres crashes with a BTP_CHAIN error. Now that I have installed 7.1beta3 > the problem has seemingly been fixed. Was this problem actually fixed > somewhere between 7.0.0 and 7.1beta3, or am I just getting lucky right now Well, it was worked on ;-) ... that whole chunk of code was rewritten. Whether it has new bugs remains to be seen, but the old bugs are gone... regards, tom lane
Re: [SQL] BTP_CHAIN errors fixed?
Tom Lane wrote: > > Mark Volpe <[EMAIL PROTECTED]> writes: > > I have been using PostgreSQL-7.0.0 and have had the problem that, when > > searching a btree index that contains large numbers of duplicate keys, > > Postgres crashes with a BTP_CHAIN error. Now that I have installed 7.1beta3 > > the problem has seemingly been fixed. Was this problem actually fixed > > somewhere between 7.0.0 and 7.1beta3, or am I just getting lucky right now > > Well, it was worked on ;-) ... that whole chunk of code was rewritten. > Whether it has new bugs remains to be seen, but the old bugs are gone... > > regards, tom lane Thanks for the quick response. The new code will get plenty of testing from me!
Re: [SQL] Re: abstract data types?
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!
[SQL] Re: abstract data types?
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
[SQL] current host and dbname info
Hi, I've been searching the docs and been unable to find the answer to this -- is there a way to get the current database server host tcpip address, postmaster port, and database name from a SQL query? I'd like to access those from within a plpgsql function without having to create and populate some sort of identification table. Thanks, Joe
