Re: [GENERAL] Joins and links
Leon wrote: > Why? There will be no such field as "record number", the only > place where it can exist is the field which references another > table. I can quite share your feeling about wrongness of > physical-oriented things in abstract tables, but don't > plain old indices deal with physical record numbers? We could > do the same - hide the value stored in such field and only > offer the user ability to use it in queries without knowing > the value. Leon, In my understanding, pointer based approaches like you are recommending have been implemented in several prototype objected oriented databases. They have been shown to be orders of magnitude slower than set oriented techniques,thus many OO databases are implemented as wrappers over relational systems! In general, the best way to handle stuff like this for reports is to cashe small tables which are joined (like product lookups) in memory to make the queries run much faster. To do this, your design has to be smart, by seperating those tuples which are "active" products from those "inactive" products so that the database can cashe the active records and not the inactive records. Perhaps something like: 1. CREATE VIEW PRODUCT AS ( SELECT * FROM PRODUCT_ACTIVE_CASHED UNION ALL SELECT * FROM PRODUCT_INACTIVE); 2. SELECT ORDER_NO, PRODUCT_NAME FROM ORDER_LINE, PRODUCT WHERE PRODUCT.PRODUCT = ORDER_LINE.PRODUCT and ORDER_LINE.ORDER = 120; Would be a general like solution, where orders with active products are brought up quickly since the join is done in memory, but orders with inactive products take much longer, since the query on the active table is a cashe miss, leaving a disk access on the inactive table. Perhaps there are several other nicer ways do to this, from my understanding a HASH based cashe could allow frequently accesed tuples to be cahsed in memory? ... anyway, I'm no expert. A more traditional method (which I use all the time), is to have canned reports that are pre-generated using common conditions. These are then saved on a web server and updated daily. It is a bit less accurate, but often for 99% of the purposes, day old information is just fine Hope this helps! ;) Clark
Re: [GENERAL] The value returned by autoinc ?
Silvio Emanuel Barbosa de Macedo wrote: > If there is an insert between my INSERT and SELECT, won't the counter be > increased ? The only way I can understand this is the transaction locks > inserts... so, in fact there could not exist another insert... It should be a counter attached to your session, so there wouldn't be any problems, since you can only have one thread in each session. Clark
Re: [GENERAL] Negating the list of selected rows of a join
Manuel Lemos wrote: > > I want to list the rows of a table with a text field whose values do not > exist in a similar field of another table. Basically what I want to get > is negated results of a join. Ulf Mehlig wrote: >SELECT name FROM table_a >WHERE name NOT IN (SELECT name FROM table_b); Clark Evans wrote: > SELECT table_a.name, table_a.age > FROM table_a > WHERE NOT EXISTS ( > SELECT 'x' > FROM table_b >WHERE table_b.name = table_a.name >); I'm not sure about how well PostgreSQL handles these two. I'd try them both with your data set. If table_b is small (less than a few thousand rows) then Ulf's approach would work best. However, if table_b is large (more than a thousand) then I think the other approach may work better if table_b.name is indexed. Clark
Re: [GENERAL] PostgreSQL EndTransactionBlock and not inprogress/abort state
Clark Evans wrote: > > Manuel Lemos wrote: > > > > Hello, > > > > I am trying to use transactions with PostgreSQL with the normal sequence > > > > BEGIN > > one or more SELECT/INSERT/UPDATE/DELETE queries > > COMMIT > > END > > > > But I am getting the following message just like if I haven't started a > > transaction when I commit. > > > > EndTransactionBlock and not inprogress/abort state > > Oops! forgot the semicolon. *whap* > BEGIN; > > COMMIT; > > > BEGIN; > > ROLLBACK; > BTW, are you putting on the "END" beacuse you are in a PL/pgsql block? If so, I'm not sure if transactions can work inside this language. Hmm. Clark
Re: [GENERAL] PostgreSQL EndTransactionBlock and not inprogress/abort state
Manuel Lemos wrote: > > Hello, > > I am trying to use transactions with PostgreSQL with the normal sequence > > BEGIN > one or more SELECT/INSERT/UPDATE/DELETE queries > COMMIT > END > > But I am getting the following message just like if I haven't started a > transaction when I commit. > > EndTransactionBlock and not inprogress/abort state > I could be wrong, but the END isn't necessary. BEGIN COMMIT; BEGIN ROLLBACK; Hope this helps, Clark
Re: [GENERAL] Negating the list of selected rows of a join
Manuel Lemos wrote: > How can I make a query that works the way I want all the time, even for the > case when table_b is empty? SELECT table_a.name, table_a.age FROM table_a WHERE NOT EXISTS ( SELECT 'x' FROM table_b WHERE table_b.name = table_a.name ); Hope this will do the trick. :) Clark Evans
Re: [GENERAL] Calcuate percentage.
It's crude and not very efficient, but here is a solution: CREATE TABLE temp ( var INT2 ); INSERT INTO temp VALUES (1); etc. CREATE FUNCTION temp_row_count() RETURNS FLOAT AS 'SELECT COUNT(*)::FLOAT AS result FROM temp' LANGUAGE 'sql'; SELECT var, COUNT(*)::FLOAT / temp_row_count() AS pct FROM temp GROUP BY var; Hope this helps, Clark P.S. In oracle, I'd use a sub-query: SELECT var, COUNT(*) / total_count FROM temp, ( SELECT COUNT(*) AS total_count FROM temp ) GROUP BY var; Sze Yuen Wong wrote: > > Hi, > > I need to calcuate the percentage from my table: > > var1 > -- > 1 > 1 > 1 > 2 > 2 > 3 > > Desire result: > > var1 | percentage > > 1 |50% > 2 |33% > 3 |17% > > === > > Any clue? > > Please help. > > Sze Wong > > _ > DO YOU YAHOO!? > Get your free @yahoo.com address at http://mail.yahoo.com
Re: [GENERAL] Replication of databases (esp. postgres)
Thomas Antepoth wrote: > > Michael, > > As Dustin pointed out, he solved it by merging a unique database > id with a sequence into a unique primary key. > If the newly inserted record gets the database id of database 2 > it will later not be recognized belonging to the relation by a > "... where db_id=this_relations_host and recid=this_relations_sequence" > on every host. You concatinate them. They become a 'single value'. Do not treat them as seperate things, you merge them together to generate the uniqueness. You _never_ seperate them, i.e., you _never_ change the db_id. > If the newly inserted record gets the database id of database 1 > it will be surely regocnized, but the sequence number of the > record may collide with a pre existing record in database 1 > when replicated. Nope. This is never a problem. This is *exactly* what the concatination gives you. > Multiple updates to a record of the same relation on different > non connected databases are another problem not solved by > this mechanism. Correct. If the data moves... you need a pointer telling it where it moved to. When something moves (a rare event in my case), you go back and update all of the pointers in the 'local-cashe' copy of the tuple. > Every solution which i can imagine relies on a master, which > receives temporary data on inserts, creates unique record ids > and re exports the data to the slaves. Nope. You can only query the local-cashe. If you want to update it, you need to 'transfer' the ownership of the object (tuple) from one database to the other. _Then_ you can update the local copy. > A slave will create in his local database a record id which is > known to be "not part of all databases". e.g. all numbers below > 10 are temporary. YIKES! I would not do this it won't scale, and besides that it's messy! *evil grin* > Then it exports this data to the master and deletes the temporary > data, when the master acknowledged his import. Why delete it? If you just edited it, chances are you may want to query it. Sure.. you might get 'old' data, but for relatively static data this is not a problem. A replication service can go around propigating updates during off-peak times. > The master creates unique record ids among all temporary data > and reexports the data to all his slaves. > > But what to do, if there are several local masters? You have a 'chain of responsibility', the Design Pattern book will help with this one. > > t++ Best, Clark Evans
Re: [GENERAL] Replication of databases (esp. postgres)
Thomas Antepoth wrote: > > But what to do, if there are several local masters? I may have interpreted the problem incorrectly, but here is a similar problem I had... Say I have 5 plants in a large company. Each of these plants has a variety of machinery and tools which have detailed maintance records. It dosn't make sence to have one centralized database, so you have 5 seperate ones. Each database with it's own copy of the machines deployed at the plant. Now comes the kicker. About 3-5 times a year there is a major migration of equipment between the plants. Plant A no longer needs such-and-such a tool, so the tool moves to plant B. The problem is, the history for that peice of equipment is tied to several, some times a hundred or more different production processes, and/or related machinery. Also, primary key conflicts cause hudge problems. To solve this problem, I switched from a numeric key to an alpha numeric key. I assigned a "birthplace" to each machine... i.e., where it was first deployed. Each birthplace had it's alpha key, e.g., plant 'A', 'B', etc. and the new object would be entered using the birth place key followed by a unique sequence or 'birth number' at that particular 'birth place'. The result of concatination, is a globally unique key that can move with the equipment from place to place. Each peice of equipment also has it's current location. If when you query the peice of equipment in the local database, and it's not the current location, then you are not allowed to update that 'archive' copy of the data. To make any changes, you must go over via remote database link to the database which 'owns' the equipment. This seemed to solve the problem. Hope this helps. Clark
Re: Subject: Re: [GENERAL] A book for PgSQL? A need? yes? no?
Stephan Doliov wrote: > The efforts of the docs teams has been tremendous and valuable beyond > description. The existing docs are the right place to start. It's > certainly worth it however to expand some sections, create new ones, and > edit existing ones. From my point of view, the docs give one most all the > information one needs to work postgres/have postgres work for one, but > they are not to the point yet where spending several days with them makes > one feel comfortable using the postgres system. It's kind of like a user > interface issue. The docs aren't super friendly yet. Perhaps a "professional writer" is needed. If people put up advertisements at your local college, perhaps a few english majors (*very* valueable people) would be interested in helping out with the documentation. It would look good on their resume and would get them "published". On a side note, you could also advertise for Jouralism majors to put out a monthly PostgreSQL newsletter! Thoughts? Clark
[GENERAL] Re: [NOVICE] A book for PgSQL? A need? yes? no?
Rich Shepard wrote: > > So, what I do is print out the docs, punch holes in them and put them > in a loose-leaf binder. Those folks who want to make the extra effort put > their docs out in .pdf format and I print them from Acrobat. Except that > my laser printer doesn't do duplex printing, it works just fine. Ya. I stopped doing this beacuse the looseleaf binders always loose papers and click when they get older... having it bound is much nicer. Also, getting most printers to do duplex is a pain. You can print out one side, then flip it over and reverse print on the other side with ghostscript, but my printer always jams when I do this. It ends up costing me 2-4 hours in time Having it printed on two sides is infinitely better. And having someone else punch the holes in it for you and bind it up is even nicer. :) Clark
Re: Subject: Re: [GENERAL] A book for PgSQL? A need? yes? no?
The Hermit Hacker wrote: > On Thu, 11 Feb 1999, dustin sallings wrote: > > On Fri, 12 Feb 1999, The Hermit Hacker wrote: > > //Like, [EMAIL PROTECTED] :) How much of what is already > > // done by the Docs Team pertinent to all this, and, if not, why not? > > // If our existing manuals don't make good documentation, then why not, > > // and shouldn't those be fixed too? > > > > It's not that the docs are bad, it's just that they're not on a > > shelf. :) > > My point was that why re-create the wheel...why not bundle *those* up and > improve upon what is already there? Yes. Thomas put a ton of work into the documentation. Two things: A) The current work is already using DocBook, which is what O'Rilley would like to see. This would be along term solution. B) Untill a book is published by O'Rilley, there are lots of "print-on-demand" places that will do cardboard/thin binding relatively inexpesively if we go with a volume of say, 100 books. To do this, we would have to pay for the printing, shipping/handling, and numerous other annoying costs. To do this, you would want to take a collection, etc. There is a company on the web (my friend had this done) that will print out books pre-paid in blocks of 50 and register them with Amazon and Borders, etc. It ends up being relatively expensive though for small volume ($25/book up-front) and by the time Amazon tacks on a 100% profit + shipping and handling the cost is close to $60.00 per book. C) Another option, is just use the local Kinko's. It cost me almost $50 to have Kinko's print my copy for me with holes double sided. For that, we could easily setup a cgi script where you typed in your address, and it sent your order to the nearest Kinko's as a PDF file...
[GENERAL] Using As with Inheritance?
I have a parallel inheritance going on, so I was wondering if there was a way to re-name a derived column? This would make my design clearer. - CREATE TABLE B ( NAME VARCHAR(10) ); CREATE TABLE C ( ... ) INHERITS(B); CREATE TABLE X ( A VARCHAR(10), B VARCHAR(10), CONSTRAINT FOREIGN KEY (B) REFERENCES B(OID) ); CREATE TABLE Y ( B AS C, /* Syntatic Sugar */ D VARCHAR(10), CONSTRAINT FOREIGN KEY (C) REFERENCES C(OID) ) INHERITS(X) Here, I've added the syntax "AS" to show that column A in table X, is called B in the derived table Y. Thank you for your thoughts. :) Clark Evans
[GENERAL] ODMG.ORG
Just a general note. How close are PostgreSQL's object extensions to those at the ODMG? How hard would it be to write complant interface? Anyone working on it? See: http://www.odmg.org/ Clark
[GENERAL] OID
I'm designing a database schema and have questions about OID. First, I assume that OID are system assigned? [yes] Second, Can I use OID as a primary key? [no] Third, Is the OID invariant under an import/export process? [no] Fourth, Can I create table with an OID type for referencing another table? [no] I puy my assumed answers in brackets. Please let me know if I am mistaken. Thanks! Clark
Re: [GENERAL] 88, CREATE FUNCTION ON TABLE
Jose',
Thank you for your response.
> --COBOL level 88 using functions---
>
> drop function current_client(text);
> create function current_client(text) returns bool as '
> DECLARE
> status ALIAS for $1;
> BEGIN
> RETURN status IN (''A'',''a'',''P'',''p'');
> END;
> ' language 'plpgsql';
>
> drop function active_client(text);
> create function active_client(text) returns bool as '
> DECLARE
> status ALIAS for $1;
> BEGIN
> RETURN status IN (''A'',''a'');
> END;
> ' language 'plpgsql';
>
> select * from customer
> where active_client(customer_status);
>
> select * from customer
> where not active_client(customer_status);
I guess what I was wondering... perhaps plpgsql
could have an "automatic" local variable, say
"current_row" so that the "text" would not have
to be passed in... Thus
create function current_client returns bool as '
BEGIN
RETURN current_row.status IN (''A'',''a'',''P'',''p'');
END;
' language 'plpgsql';
Then the select could look like this:
select * from customer where active_client;
Thoughts?
:) Clark
Re: [GENERAL] Why PostgreSQL is better than other commerial softwares?
Albert Chen wrote: > I tell my advisor to try PostgreSQL, but he said: > "Why PostgreSQL is better than other commercial softwares? > If you could give me ten reasons, and I will give it a try." I'll give you one reason: Equal access to the source code. This allows you to better learn from it and also allows you to help debug it. I've been a professional Oracle programmer for almost 6 years now. I'm moving to PostgreSQL When I have a problem with this database, I can pull out the code and fix it, extend it, etc. When I have a problem with Oracle, I have to pay thousands in "support" charges (you can't afford to go production without being on a silver or gold support plan...) for me to help them solve their problem .. without the benifit of the source code. *sigh* Then when we are done, they implement the suggestions I gave them and sell the improvements back to me!! No more. I've had it. You don't need 10 reasons. Independence is the only reason any reasonable person requires. Good luck, :) Clark
[GENERAL] Row Level Locking, On-line Recovery
Anyone working on Row level locking and on-line recovery? I have some ideas (and questions) in this area. Also, I was wondering if there is a "transaction server" project, i.e., a server that helps provide a consistent client interface to both the database and application servers. Thank you! Clark
[GENERAL] XML Integration
Is there anyone working on XML integration? Services include: a) Import/Export to XML (DTD defines schema) b) Access (through stored procedures) to XML (PSAX anyone?) b) Emulated tables (that direct queries to XML queries)? c) Other fun XML items. :) Clark
Re: [GENERAL] Introduction: Accounting/Bookeeping Project
Thank you all who responded to my last e-mail. It's wonderful to have such a responsive group of forward thinking people to work with. :) My last post was made prematurely with less thought than what was required. I won't have my "act" together for another month or two... but when I do you I will make another post to this list. So as to not leave you hanging, my project is really two distinct things that are interwoven: a) A new software ownership model. I call it "communitysoftware". It is a balance between free software and proprietary software where the open source, non monopolistic aspects are available, while maintaining the ability to be paid for ones work by charging for commercial use. b) A new bookkeeping model. Bookkeeping records the financial "story" of a company. Current bookkeeping systems record this story in first person (from the company's perspective). This bookkeeping project will record the story in third person (from the community's perspective). The resulting business environment is one where large numbers of independent contractors can cooperate/compete, while having the advantages of uniform record keeping. Currently the bookkeeping overhead of multiple first person systems prevents groups of independent contractors from collaborating effectively -- leaving large contracts for large companies who can leverage uniform bookkeeping practices across a project. These two models are interwoven since (a) requires (b). My business associate, Dan Palanza, is doing a similar type of business ownership model at the local community level. It was he who introduced me the new bookkeeping concepts and opened my eyes to a different way of doing business. Thus the new bookkeeping model will be applied to not only the software world, but in a small town environment in Falmouth, MA. Both of these concepts are in their infancy, and it will be very challenging to bring them into maturity, hence the only thing that I can promise is that it will be an interesting trip for all involved. Once again, give me at least another month to write this up and get organized to the point where I can reach out in a more organized fashion. In the interim, if you could point me to existing bookkeeping/accounting models and software, this would be great. :) Clark
[GENERAL] Introduction: Accounting/Bookeeping Project
Hello. My name is Clark Evans. I'm starting a project to create an "accounting/bookeeping" project using PostgreSQL. I am one of the founders of jos.org and became frustrated with the management of a distributed development effort. I have six month funding starting January to develop a network accounting system. I hope to apply the resulting bookkeeping system to the difficult challenge of managing a large internet development effort. Questions: a) Is there any such project using PostgreSQL? b) If not, anyone want to join? I have over 5 years of solid database experience and an NSF/SBIR grant to execute this project full time. Your time will _not_ be wasted. I staff up in February, so take the next month to think it over and ask me questions. Your time against the project will be tracked and when the project is sold to its community of users, you will be able to recover your time invested with real dollars. I especially need a designer with corporate accounting experience, a PostgreSQL expert, a cgi/gui developer, and a java gui developer. Please reply personally in confidence. Thank you! Clark Evans
