Re: [GENERAL] Great Bridge benchmark results for Postgres, 4 others
Ned Lilly wrote: Oh, Dan, I'm not that clever... ;-) But I *can* tell you that the market leading proprietary RDBMS products we tested were not IBM, Informix, or Sybase. That's very helpful. Can you also tell us if Proprietry 1 or Proprietry 2 was definitely NOT MS-SQL Server? Regards, Ned Dan Browning wrote: Can you tell us what version of the (ahem) unnamed proprietary products you used? :-). For example if you used version 8i of an unnamed proprietry product, that might be informative :-). Oh, but even if you can't tell us what version was used, I'm sure you could tell us that story about the monster you saw last week. But which monster was it? Was it the monster that ATE EYEs? And I remember you once said there was a second monster, could you describe it as well?
Re: [GENERAL] Great Bridge benchmark results for Postgres, 4 others
Ned Lilly wrote: Er... let me put it this way. Proprietary 2 prefers to run on Windows NT. The performance is so bad it must be MS-Access :-). Chris Bitmead wrote: That's very helpful. Can you also tell us if Proprietry 1 or Proprietry 2 was definitely NOT MS-SQL Server?
Re: [GENERAL] hierarchy select question?
Hi, Originally postgres had a "recursive select" to handle cases like this. Some syntax like... retrieve* (notice the "*") which meant keep executing until you can't anymore, and using an appropriate where clause it would decend tree-like structures. This feature disappeared somewhere along the way. There is I think a similar concept in SQL or SQL-99 which needs to be (re)implemented sometime, but I don't think there's an easy way right now. There was some talk on the hackers list recently about how to implement parent child comments in discussion forums, but I'm not sure if a nice solution came along. Some people seemed confident that there was a way. [EMAIL PROTECTED] wrote: Dear all, I would like to define threads in message system for replies to message but if I define too many level, I am afraid I have problem in the select... Say, I have define 3 levels: 1 / \ 2 3 /\\ 4 5 6 It means message 2 is a reply to 1. 4 is a further follow-up of 2...etc In table format, I would present it with Table A ParentId ChildId 1 2 1 3 2 4 2 5 3 6 I think I can issue the command to join table A 3 times to give the following result 1st2nd 3rd Row11 2 4 Row21 2 5 Row31 3 6 But can I show a. which level each node belongs b. no. of child nodes it has altogether in one "select" sql and if the level exceeds 3, how can I do it? Many thanks. Best regards, Boris
Re: [GENERAL] Replication options in Postgres
I guess if you don't do deletes then something like selecting all the records with an oid greater than the last replication cycle would find the most recent additions. Erich wrote: I am setting up a system that processes transactions, and it needs to be highly reliable. Once a transaction happens, it can never be lost. This means that there needs to be real-time off-site replication of data. I'm wondering what's the best way to do this. One thing that might simplify this system is that I _never_ use UPDATE or DELETE. The only thing I ever do with the database is INSERT. So this might make replication a little easier. I think I have a few possibilities: 1. In my PHP code, I have functions like inserttransaction(values...). I could just modify inserttransaction() so that it runs the same query (the INSERT) on two or more DB servers. This would probably work ok. 2. I could write triggers for all my tables, so that when there is an INSERT, the trigger does the same INSERT on the other server. Any ideas for an efficient way to do this? 3. Any other tricks? I don't need mirroring. There will be one master and one or more slaves, and the only thing the slaves will do is store backup data. The most important thing is that I can't lose a single transaction. Thanks, e
Re: [GENERAL] How to alter the size of a column
Michael Talbot-Wilson wrote: I want to alter the size of a column, say from char(40) to char(80), but it seem that the ALTER does not support such operation, nor does it support column removing. How can I do for this ? I would also like to know how to do both of these things. I'm not aware of an easy way of doing it. But you can dump your schema and data separately. Manually edit your schema. Reload the schema then reload the data.
Re: [GENERAL] 4 billion record limit?
The Versant ODBMS uses 48 bit oids, and if you do the math I think you'll find that should last you forever. (It uses an additional 16 bits to identify the database, but that's another story.). Any complex scheme to solve this seems like a waste of time. In a couple of years when you are likely to be running out, you'll probably be upgrading your computer to a 64bit one with a newer version of postgres, and then the problem will disappear. brad wrote: THe implications of the OIDs not wrapping are immense. We have some extremely active databases that will easily reach this limit in two or three years. For some applications, such as ecommerce, dumping then reinserting the rows is not an option for large databases due to the 24 hours nature of their work. This is a much more complex problem than it would at first seem as the "tripping up" over old records with low OIDs still presents a problem, yet if the system is changed to try and manage a list of available OIDs, it will then hit performance problems. Simply waiting for 64bit numbers is rather inelegant and also presumes usage parameters for the database... remember Bill Gates saying that he couldn't foresee any usage for more than 64MB of RAM? Besides which, PostgreSQL is the best DB around... there's a high standard to maintain! Some initial ideas: a) The system remains working the way that it does until a configurable cutoff point (% of OIDs remaining), at which point the DBA can either switch on some sort of OID "garbage collection" and take the associated performance hit, or dump the data and reinsert it to pack the OIDs. GARBAGE COLLECTION: b) The system could wrap around, keeping an internal pointer of where it is in the OID chain. It could scan the OIDs sequentially finding the first free OID. It coudl then store that position as the new start point for the next time an OID is needed. c) An OID compression utility could be writen that doesn't require bringing the DB down (but will obviously have a performance it). As running this utilty would be a known impact, provision could be made, or the knock budgeted for and accounted for while not bringing the entire DB to a halt. d) OIDs themselves could form a list. The OID system as it stands now could be the "default mode" but if the OIDs run out, then a new OID list is started. This means the OID usage would then change to work along the same lines that the Intel memory adressing works. Personally I hate it, but it does work. e) OIDs could be scrapped in favour of some other system. Well, that's my two pence worth. Brad Paul Caskey wrote: Tom Lane wrote: Chris Bitmead [EMAIL PROTECTED] writes: Paul Caskey wrote: 1. This implies a hard limit of 4 billion records on a server, right? Basically, yes. It's only a hard limit if your application assumes OIDs are unique. If you don't assume that, then I think it's not a big problem. It's possible (though obviously not especially likely) that you might get OID collisions in the system tables after an OID-counter wraparound. This implies they do wrap around. So they are reused? Chris said no, but you're saying yes. (Maybe they wrap around "by accident", by adding one to MAXINT, which will give zero on an unsigned int, I believe. Will the system choke on zero? Has anyone tested this wraparound?) I will not have 4 billion records in one table or even one database. But on a large server with many databases, it is conceivable to have 4 billion records on one machine. With a lot of insert/delete activity, over a few years, it is certainly conceivable to have 4 billion inserts. If the oids don't wrap, I have a problem. I can ignore it for a long time, but it will loom, like Y2K. :-) Even if they do wrap, if I have some old records lying around with a low OIDs, they will trip me up. Like you said, these are "the outer limits", but I'm thinking ahead. Someone suggested in private that I pg_dump/restore all my data to "repack" the oids which start around 1700 on a fresh database. Thanks for that idea. Also thanks, Tom, for the sanity check Re: terabytes of data with 4 billion records. It's still possible, especially in coming years. It would be a big feather in PG's cap to "fully support" 64-bit platforms such as IRIX and Solaris (finally) and, coming soon to a theater near you, Linux on IA-64. -- Paul Caskey [EMAIL PROTECTED] 505-255-1999 New Mexico Software 5041 Indian School NE Albuquerque, NM 87110 --
Re: [HACKERS] Re: [GENERAL] PRIMARY KEY INHERITANCE (fwd)
Stephan Szabo wrote: Of course I had to be half asleep when I wrote the second paragraph of my response, since I totally missed he was using a serial. The rest still applies though... As an aside to Chris, what interactions do you expect between the OO stuff you've been working on and foreign key references? I'm going to have to muck around with the trigger code to move to storing oids of tables and attributes rather than names, so I thought it might make sense to at least think about possible future interactions. As a rule, anything that applies to a base class should also apply to the sub-class automatically. For some things you may want to have the option of excluding it, by something like the ONLY syntax of select, but 99% of the time everything should just apply to sub-classes. Storing oids of attributes sounds like a problem in this context because it may make it hard to relate these to sub-classes. I do really think that the system catalogs should be re-arranged so that attributes have two parts - the parts that are specific to that class, and the parts that also apply to sub-classes. For example the type and the length should probably apply to sub-classes. The attnum and the name should probably be individual to each class in the hierarchy. (The name should be individual to support subclass renaming to avoid naming conflicts, like in the draft SQL3 and Eiffel). If it is in two parts then using the oid of the common part would make it easy for your purposes.
Re: [HACKERS] Re: [GENERAL] PRIMARY KEY INHERITANCE (fwd)
Tom Lane wrote: Chris Bitmead [EMAIL PROTECTED] writes: ... The attnum and the name should probably be individual to each class in the hierarchy. (The name should be individual to support subclass renaming to avoid naming conflicts, like in the draft SQL3 and Eiffel). If it is in two parts then using the oid of the common part would make it easy for your purposes. This bothers me. Seems like you are saying that a subclass's column might not match the parent's by *either* name or column position, but nonetheless the system will know that this subclass column is the same as that parent column. No doubt we could implement that by relying on OIDs of pg_attribute rows, but just because it's implementable doesn't make it a good idea. I submit that this is too confusing to be of any practical use. There should be a *user-visible* connection between parent and child column, not some magic under-the-hood connection. IMHO it ought to be the column name. When you multiple inherit from unrelated base classes you need a conflict resolution mechanism. That's why it can't be the name. The SQL3 draft recognised this. Many programming languages deal with this issue without undue confusion. To provide mapping to these programming languages such a conflict resolution mechanism becomes necessary.
Re: [GENERAL] MOD
MOD is the remainder after division.
MOD(10, 3) = 1
MOD(11, 3) = 2
MOD(12, 3) = 0
MOD(13, 3) = 1
etc.
Tyler Wood wrote:
Hello,
I'm updating an existing postgres database, and using
perl with dbi to
access it.
Everything works fine,
I'm just not sure what this MOD command means.
Not asking you to figure it out in this context,
but just what does MOD do?
$sqh = $dbh-prepare("select
name,namelink,address,city,state,zip,email
from company where MOD(nextscreen,2)=1 order
by $sort_selection;");
$sqh-execute();
thank you,
Tyler Wood
[EMAIL PROTECTED]
__
Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere!
http://mail.yahoo.com/
Re: [GENERAL] Object oriented features - MISSING
Hi, This has been broken in postgres for many years. But I have fixed it recently in current CVS. So you'll either have to wait for the next release or else risk a development version. Another option is to go to patches archive and manually apply the patch to 7.0.2 which wouldn't be that hard. Chris Bitmead. Felipe Alvarez Harnecker wrote: Hi, Postgresistas I'm running 7.0.2 in a Debian system, and a have this problem: SELECT * FROM some_base_table* works fine, but UPDATE some_base_table* SET a = b WHERE some_condition and DELETE FROM some_base_table* WHERE some_condition dosen't even parse: the parser says parser: error at or near "*" The question is Is this a bug in Postgres? or just my instalation? Thanks. PS. I really like the Object oriented features ( at leas as describe in the docs ). It's a bad thing tha those dosen't work Regards. -- __ Felipe Alvarez Harnecker. QlSoftware. Tel. 09.874.60.17 e-mail: [EMAIL PROTECTED] Potenciado por Ql/Linux http://www.qlsoft.cl/ __
[GENERAL] Re: [HACKERS] proposed improvements to PostgreSQL license
Good point. But the USA is the demon spawning ground for lawyers, and is at the leading edge of aggressive new legal territory. Actually that is the exact reason you _don't_ want to be based in the USA. Do you really want Postgres to be breaking new ground in the courts? The USA is at the leading edge of lame new legislation. If the postgresql licence is locked into Virginia law forever, (because any licence change will be forever), you are subject to that law forever no matter how stupid it may get. For that reason I don't think you should be naming a jurisdiction. You don't know what that jurisdiction may do in the future. Now any normal corporation in this event could just change their licence to jurisdiction B which has more favourable laws. Open source can't change the licence ever unless you assign the rights to every bit of submitted code like RMS insists on for GNU code. If you must pick a jurisdiction pick Australia. We are *much* less litigious. :-) Actually, pick Sealand. They have no laws and no courts.
Re: [GENERAL] responses to licensing discussion
Philip Warner wrote: At 14:38 5/07/00 +1000, Chris Bitmead wrote: Then what happens if I fork the project and remove all these printf's from the code? Then I'd guess that the organization that removed them becomes liable. That's why they're there. Putting aside that I don't think anybody is liable anyway... I could fork postgres, then sit on pgsql-patches applying them all as they come along, and go around claiming that my postgres is the "one true". Tenuous I know, but then the whole idea of getting sued by someone you have no contract with is pretty tenuous.
Re: [HACKERS] Re: [GENERAL] Revised Copyright: is this morepalatable?
Philip Warner wrote: My legal advice is that, assuming they knew it was a BSD project, they can't take it out of PostgreSQL. But you could, for example, stop Microsoft using your compression code in one of their products. The new license removes this right from you. Why wouldn't MS be able to take the code and use it while abiding by its terms and conditions?
Re: [GENERAL] Join with other database's table
I am developing WWW site that is serviced in 4 difference language, english, chinese, japanese, korean. I allocated one database for one language. Why?
Re: [GENERAL] Join with other database's table
Sungchul Park wrote: Because I want to use same table name. I mean... For example, I have a BBS code. That code store data into 'pubbbs' table. As you know I need 4 tables for one service. Yes, I can use 4 different table name. As 'pubbbs_en', 'pubbbs_jp', 'pubbbs_kr', 'pubbbs_cn'. But I will make much more services and there will be too many table in one database. Too many in what way? Personally I think the whole idea of having 4 tables that store the same kind of data will lead you to problems later on. I think I'd have a language attribute in the one table. (Could you understand? I am not well english speaker.) I like simple and it looks simple way to allocate datebase for each language. I believed this schema is not special one. Many DBMSs are support this, even MySQL.
Re: [GENERAL]
Emmanuel Motchane wrote: Hi, I am trying to figure out how to restrict user access to a database to a few defined functions, so that users could use the database (and update it) but only through a set of procedures, written for exemple in C and using the Server Programming interface. If this is some kind of security measure, it won't work because in the extreme case anybody can just open a socket and send the appropriate protocol down it (like you can with any client server database). Otherwise I suggest you ask your user's politely or see if the postgres grant permissions can do what you want.
[GENERAL] Re: [HACKERS] Postgresql OO Patch
While SQL3 talks about trees and leaf rows, it's not implemented like that, so all this worrying about digging down trees and leafs is all a bit mute. "Robert B. Easter" wrote: If it were allowed, you might have to specify the level to dig to in the tree. The rows are shared among supertable and subtables. One row in a leaf table has subrows in all its supertables up the tree. If you do a "SELECT * FROM supertable*" (for example, if you were to redefine table* to mean select heterogeneous rows), what row will you get for a row that exists in a leaf? The same row is in all tables between supertable and the leaf. I suppose it would be necessary to have the query check each row and see how far down the tree it goes, or the system keeps track of that and returns the row-type from the table that inserted it. OR, there could be some extra specifier like "SELECT * FROM supertable DIGGING TO LEVEL 3". In this case, it would only look down into the tree to 3 levels below supertable and you'd never get row-types that are down lower than level 3. Anyhow, I still don't think returning multple row-types is going to happen, not that I have any authority one way or the other! :-) -- Robert B. Easter [EMAIL PROTECTED] -- Chris Bitmead mailto:[EMAIL PROTECTED] http://www.techphoto.org - Photography News, Stuff that Matters
Re: [GENERAL] Re: [HACKERS] Postgresql OO Patch
Mike Mascari wrote: At a minimum, it seems to me, the backend must support the concept of multiple tuples with different attributes at the relation level since concurrency and rollback-ability of ALTER TABLE ADD COLUMN will cause two concurrent transactions to see a single relation with different attributes. It doesn't seem a large leap to support this concept for OO purposes from "leaf" to "base". For "base" to "leaf" type queries, wouldn't it be acceptable to return the base attributes only, as long as the equivalent of run-time type information could be had from the OID? How are you going to be able to go shape.display() and have it work for a triangle, if the triangle's apex's weren't retrieved?
[GENERAL] Re: [HACKERS] Proposed Changes to PostgreSQL
Peter Eisentraut wrote:
A lot of people use inheritance to create "consistent schemas", that is,
they empty create base tables, such as "address" which are inherited by
tables such as customer, vendor, office, etc.
This is a really bad idea. You could never have both a postal address
AND
a home address for example. I thought the original postgres supported
this
by having
CREATE TABLE ADDRESS (...)
CREATE TABLE PERSON(add ADDRESS).
Anyway, this is what Oracle and others can do these days, and this is
the right
thing.
Anyway, an idea I had would be to reimplement inheritance based on joins,
since this is what the "pure relational" solution would be anyway. When I
create a table B that is based on A, all the system does is create the
table B as usual and store a note "I inherit from A". Any row you insert
into B also creates a row in A, and the row in B contains an oid pointer
to it.
This is a really stu^H^H^H bad idea. I have hierarchies 5 levels deep
with
multiple inheritance, and I
don't want to do a 10 way join just to retrieve an object.
This is why RDBMS's performance sucks so incredibly badly on some
applications.
an ODBMS can perform 100x as fast in these cases just because of what
you
are proposing.
Thus a select on B performs a join on A.oid and B.row_in_A_pointer.
A select on A just returns all the rows in A, no extras needed. A delete
on B deletes the row in B and in A. A delete in A would cascade to B. Both
of this can be gotten for free with foreign keys. Adding a column to A
just adds the column to A, all other tables get the new column magically
and in the right order. Same with dropping columns, etc.
In short, this approach solves all inheritance problems at once and does
so without adding any extra kludges besides the "I inherited from" field,
which is static, plus the necessary transformations necessary in the
parser. The drawback is of course that a select from an inherited table
would always incur a join, perhaps some optimizing could be done in this
direction. But the bottom line is that the compatibility issue looms big.
-Peter
On Thu, 3 Feb 2000, Chris Bitmead wrote:
Hi,
I've been spending a lot of time lately with gdb and tracing the
back-end seeing if I can understand it enough to make some changes.
I'm starting to actually understand a lot of stuff, so in order
to have some possibility of having my changes accepted, I want to
discuss
them here first. Based on that, I'm going to hopefully make an attempt
at implementation. I have a patch for one of these changes already
if I get the go ahead.
THESE CHANGES DON'T AFFECT YOU IF YOU DON'T USE INHERITANCE.
Speak now about these changes or please, forever hold your peace. Of
course you can comment later if I screw up implementation.
The proposed changes are
1) An imaginary field in every tuple that tells you the class it came
from.
This is useful when you select from table* and want to know which
relation the object actually came from. It wouldn't be stored on disk,
and like oid it wouldn't be displayed when you do SELECT *. The field
would be called classname. So you could have...
SELECT p.classname, p.name FROM person p;
person | Fred
student | Bill
employee | Jim
person | Chris
If you want to know the exact behaviour it is as if every table in the
database had done to it...
ALTER TABLE foo ADD COLUMN classname TEXT;
UPDATE foo SET classname='foo';
Of course this is not how it would be implemented. It is just
reference for how it will appear to work. BTW, this idea was also
in the original berkeley design notes.
2) Changing the sense of the default for getting inherited tuples.
Currently you only get inherited tuples if you specify "tablename*".
This would be changed so that you get all sub-class tuples too by
default unless you specify "ONLY tablename". There are several
rationale for this. Firstly this is what Illustra/Informix have
implemented. Secondly, I believe it is more logical from an OO
perspective as well as giving a more useful default. If a politician
IS a person and I say SELECT * from person, then logically I should
see all the politicians because they are people too (so they claim
:). Thirdly, there are a whole range of SQL statements that should
probably be disallowed without including sub-classes. e.g. an ALTER
TABLE ADD COLUMN that does not include sub-classes is almost certainly
undesirable. It seems ashame to have to resort to non-standard SQL
with the "*" syntax in this case when it is really your only
choice. Basicly, wanting ONLY a classname is a far more unusual
choice, and leaving off the "*" is a common error. Fourthly, it seems
out of character for the SQL language to have this single character
operator. The SQL style is to use wordy descripti
Re: [GENERAL] Proposed Changes to PostgreSQL
Bruce Momjian wrote: So the field is created on the fly to show what table it came from. Seems like a good idea, though implementing another usually-invisible column will be tough. What problems do you forsee? However, because it is not really a column like the oid is a column, it should be ok. Of course, internally it is relid. 2) Changing the sense of the default for getting inherited tuples. Currently you only get inherited tuples if you specify "tablename*". Sounds fine to me. Just realize you are taking on a long-overdue but big job here. I already have a patch for this one. The change is a few pretty simple changes to gram.y. 3) The ability to return different types of rows from a SELECT. This is to allow implementation of ODBMS functionality where a query could be required to instantiate objects of differing types with differing attributes. This bothers me. We return relational data, showing the same number of columns and types for every query. I don't think we want to change that, even for OO. What aspects bother you? This is the fundamental important thing about object databases. It's also something that I'm always wanting to do when generating web pages. I have web links like http://foo.com/page?id=123. I want to retrieve the webpage object (which is an inheritance hierarchy) of id=123 which may represent a web page of different types. Then process appropriately for different objects. i.e. typical OO polymorphism. How are you going to return that info the the client side? Well the backend - frontend protocol that used to be able to return tuples of different types would be put back in. Also the berkerly postgres docs had other scenarios where different tuples could be returned. One is you could have a field of type postquel called say EMP.hobbies which had a value of "retrieve HOBBIES.all where...", and then "retrieve EMP.hobbies would return tuples of different types of hobbies.
Re: [GENERAL] Proposed Changes to PostgreSQL
Bruce Momjian wrote: I already have a patch for this one. The change is a few pretty simple changes to gram.y. OK, you will have to canvas the general list to make sure this does not break things for people, though our inheritance system needs an overhaul badly. This is already CCed to the general list. I fear it is totally against the way our API works. How does someone see how many columns in the returned row? A new API PQnfieldsv(PQresult, tupnum) or some such. Yikes. Strange. Strange for C code perhaps. Very useful for constructing real objects in OO application code framework. Can we just return nulls for the empty fields? Well, I think we should probably distinguish between a field that is null, and a field that simply doesn't exist. How many new API calls are required? Perhaps just the one. (above).
Re: [SQL] Re: [GENERAL] Proposed Changes to PostgreSQL
Mathijs Brands wrote: On Wed, Feb 02, 2000 at 09:57:48PM -0500, Bruce Momjian allegedly wrote: 3) The ability to return different types of rows from a SELECT. This is to allow implementation of ODBMS functionality where a query could be required to instantiate objects of differing types with differing attributes. This bothers me. We return relational data, showing the same number of columns and types for every query. I don't think we want to change that, even for OO. What aspects bother you? This is the fundamental important thing about object databases. I fear it is totally against the way our API works. How does someone see how many columns in the returned row? This would probably break applications written in PHP and Perl (and possibly others) that have their queryresults returned to them in a numerically indexed array (index by offset). If this behaviour could be turned off, than it shouldn't be a problem. It wouldn't affect them because the current APIs would continue to return the same base-level columns. You would only get access to the extra columns with a new API.
Re: [GENERAL] Prog to generate table structure ...
You need to look at the system tables. Like pg_class and the other pg_* tables. pg_dump will output CREATE statements to re-create the database. I'm not sure that's what you want though. Marzullo Laurent wrote: Hello, is there a prog to generate C file describing table structure of a database. (Something like dclgen under Ingres...) If not, I would like to know how to get information about type of each row of a table to write this kind of prog. (i.e. Where is this explain ?) -- +--+ | Marzullo Laurent | | Analyste-programmeur | | SEMA-GROUP Belgium S.A. | | Tél: (32) 2 333 52 43| | e-mail: [EMAIL PROTECTED] | +--+
Re: [GENERAL] pg-dump -- primary Key
Dan Wilson wrote: Yes, I am aware that the primary key does not really mean anything except implicitly making it a unique key, but it's supposed to be there for compatibility and it's not even in the dump. Someone mentioned recently that primary key enforces nulls as unique whereas unique index doesn't. It's not a huge deal, but I was wondering if there was reasoning behind it of which I am unaware. -Dan -- Chris Bitmead mailto:[EMAIL PROTECTED] http://www.techphoto.org - Photography News, Stuff that Matters
[GENERAL] inheritance
I've been reading up on what Informix and Oracle provide in the way of object support. In particular I noticed that in Informix when you SELECT on a table it by default includes all the objects of sub-classes. In other words the "*" is postgres terms is always there by default. If you just want that class only you have to say ONLY(tablename). To me this is a much better idea. In any proper OO application you would be using the "*" in postgres 99% of the time - that being the whole point of OO. Does any consideration want to be given to making the same change while there's not too many people using the inheritance feature? I realise breaking compatibility is bad, but I think this is the Right Thing. When you say "SELECT * FROM animal" it's reasonable that you be returned all elephants. To not return them is pretty strange for the uninitiated. The other thing Informix does is automatically propagate all attributes including indexes, constraints, pretty much everything to sub-classes. Again.. I think this is the right thing. Any thoughts? As for Oracle 8i, as far as I can tell it provides no support for inheritance whatsoever. The docs themselves say "Oracle doesn't support inheritance". It's a bit rich really to call it Oracle "object" in any shape or form. -- Chris Bitmead mailto:[EMAIL PROTECTED]
Re: [GENERAL] Multi-threading on PostgreSQL?
I think you are missing something. Threads and processes, to a great extent are 6 of one and half a dozen of the other. Postgres uses multiple processes accessing a shared memory area. A multithreaded database would use multiple threads accessing a shared memory area. In most OSes, threads and processes are treated very much the same way. Threads and processes don't necessarily have to "wait". Both threads and processes use locks. A good multi-process implementation will beat a bad threaded one every time. A threaded implementation can be more efficient, specifically when you've got hundreds of simultaneous connections, but we're not talking about some giant leap here. That's not to say Sybase isn't faster than PostgreSQL (or slower for that matter. I wouldn't know). But I'd advise against putting any stock into whether it's threaded or not. As always, do your own testing. Tiago Hermans wrote: Hi, I am really impressed with the features of PostgreSQL but am wondering if we should use this product for our project. We have tried several databases (MySQL, Sybase ASE, Sybase ASA) and we are now looking at PostgreSQL before releasing our service on the Internet. My concern comes from the fact that there's no multi-threading support in PostgreSQL and since we expect many customers to be connected at the same time on multiple connections, I am afraid the time to perform the SQL statements will be much longer, since they'll all be queued one after the other. Therefore, if one query takes a long time, all the other queries have to wait behind before being executed. Am I missing something or are my assumptions correct? Also, does PostgreSQL supports international characters? Thank you for any info this. Tiago Hermans [EMAIL PROTECTED]
Re: [GENERAL] Parallelizing PostgreSQL for Cluster
Dustin Sallings wrote:
On Thu, 3 Jun 1999, The Hermit Hacker wrote:
# One of the long-term projects that PostgreSQL, Inc is planning on
# working on is exactly this, unless someone jumps at it before we get a
# chance to...
It should be a trivial change, right? :)
Will it make it into 6.5?
:)
# On Thu, 3 Jun 1999, Laurence Liew wrote:
#
# Hi!
#
# Is anyone looking at making postgreSQL scalable across a cluster of PCs?
# That is, we have postgreSQL exuting queries which can be parallelised across
# a cluster, either something along Informix's method of fragmenting the
# tables across multiple disks, or Oracle's method of using a shared disk
# (global file)architecture.
#
# It would be interesting to have PostrgreSQL sitting on a Beowulf cluster
# giving Informix XPS or Oracle OPS a run for their money :-)
#
# Thanks for any info.
#
# Laurence
#
#
#
#
#
# Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
# Systems Administrator @ hub.org
# primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
#
#
#
--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings [EMAIL PROTECTED]
|Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L___ I hope the answer won't upset her.
Re: [GENERAL] Upgrading from 6.3-6.4.2/6.5b1 possible
I've seen this problem too in 6.5 beta. I don't have a solution, but I'd just like to add my voice to say that this problem is real. Actually, I did have a kind of solution. If you dump proper insert statements into the dump and then run every insert in a separate process. Ugly but workable if the number isn't too great. while read A do echo "$A" | psql databasename done dumpfilename Gregory Maxwell wrote: AHH! The pgsql v6.3 backend has begun crashing horribly on me since I've added a few more database backed webpages (with messages like 'backend cache invalidation...').. I figured that upgrading to 6.4.2 would solve my problems.. But I can't upgrade! Things I've tried: * Using old pg_dumpall -z file and psql -e template1 file (after install and initdb) * Using the pg_dumpall from the new version. * Using pg_upgrade * Rereading the docs dozens of times * having a friend try it. * all of the above with both 6.4.2 and 6.5beta Every time I start loading, it chokes up on the dump output and falls into a constant stream of parse errors and messages like "PQsendQuery() -- query is too long. Maximum length is 8191" Argh!!! I've got websites constantly crashing and coustomers ready to revolt, what can I do???
Re: [GENERAL] PL/pgSQL - mailng list
It's extremely straight-forward..
$q = $dbh-prepare("SELECT x,y,z from b where c = ?");
$q-execute("foo");
while (($x, $y, $z) = $q-fetchrow())) {
#stuff
}
$q = $dbh-prepare("INSERT INTO a(x,y,z) values(?,?,?);
$q-execute($x,$y,$z);
Mike Haberman wrote:
I too need more info on PL/pgSQL than the docs supply.
Is it possible for people to mail me any examples of PL/pgSQL. I
just need to see examples of the syntax of the various control
sturctures, variable assignment, and the like.
From simple to very complex, send anything that might be helpful.
thanks a ton
mike haberman
[EMAIL PROTECTED]
On Sat, 22 May 1999, Andy Lewis wrote:
How can we tell you more if we don't know what you know? It's
usually pretty safe to just go ahead and ask the question. You'll be
redirected if you've posted to the wrong place.
// I'd just plain would like to know more about PL/pgSQL.
//
// Andy
//
// On Sat, 22 May 1999, Piotr Stelmaszyk wrote:
//
// I'd like to get to know where should I post messages concerning
// PL/pgSQL.
//
//
// //
// /- Piotr Stelmaszyk | Student of Computer Science ---/
// /-- | at Poznan University of Technology /
// /- mailto:[EMAIL PROTECTED] -/
// /- mailto:[EMAIL PROTECTED] ---/
// //
//
//
//
//
//
//
--
Principal Member Technical Staff, beyond.comThe world is watching America,
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings [EMAIL PROTECTED]
|Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L__ and America is watching TV. __
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:[EMAIL PROTECTED]
Re: [GENERAL] RH 6.0 and PostgreSQL
Did you perhaps previously build postgres yourself, and now maybe Redhat has overwritten the configuration? Ricardo Peres wrote: Hello, After upgrading to RedHat 6.0, PostgreSQL no longer works... Whenever I try to start the postmaster daemon, using /erc/rc.d/init.d/postmaster script, I receive a message about postgresql not finding the data directory. I checked /var/lib/pgsql, and in fact it does not contain anything! Am I missing something? Thanks in advance! PS-BTW, please e-mail-me directly, as I am not subscribed to this list! - Ricardo Peres E-mail: [EMAIL PROTECTED] ICQ UIN: 708926 TM: 0931 9459192 Departamento de Engenharia Informática Universidade de Coimbra PORTUGAL - -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:[EMAIL PROTECTED]
Re: [GENERAL] Weird datetime problem
It's not a timezone problem is it? Did the dates get re-loaded correctly? Andy Lewis wrote: I have 2 tables both of which have a datetime field and both have an identifier of date_submitted. I've been using(via PHP) a query of the following for a month or so with no problem: select count(*) from mytable1 where date_submitted 'today'::datetime and select count(*) from mytable2 where date_submitted 'today'::datetime Both queries were working fine until I dumped the data in mytable2 to file and reloaded it. Now I can enter a date and not get the rows that I put in today(or any other day). The date_submitted has a btree index on it. I've even dropped and created a new index and that still hasn't helped. I still come up with 0 rows found. All I'm trying to do is get the number of rows submitted since midnight. Oh, yeah, Postgres 6.4.2, PHP 3.0.7, Linux Slackware 2.0.35, Dual Pent 450's. Anyone have any ideas? Thanks in advance. Andy
Re: [GENERAL] virtual fields ( proxy execution ?? )
You can set up a trigger to be activated on any event such as UPDATE.
The trigger calls a function which may be a C function. The trigger can
take arguments. So I would say if you wrote a C function which
execve()'s its arguments that would be what you want.
I agree that it sounds useful enough that such a function should perhaps
be a standard part of postgres. Perhaps if you implement it you could
donate it to the core code. (unless there's already one there I don't
know about).
paulmoody wrote:
Hi
Err maybe that was a bad example. How about this ... I have ( not really )
a large number of programmable power supplies connected to my remote Linux box
where the details about each supply are stored in a database.( eg 100 supplies
= 100 records ). One of the fields in each record might be called voltage. If
I query a particular field then the current voltage of the supply is returned.
If I write to that same field then that particular supply would have its
voltage adjusted to the written value. This would allow me to manipulate the
power supply parameters using SQL and would be a powerful ( no pun ) tool for
doing all sorts of things in response to alarms etc ( including sending email
).
Thanks again.
Chris Bitmead wrote:
I'm a bit vague on what you mean. Are you saying you want to be able to
say to postgres...
INSERT INTO message(to,subject,body) VALUES ('[EMAIL PROTECTED]', 'Hello',
'How\'s it going');
and instead of storing a message into postgres, it will send a mail
message to fred?
Then you want to say...
SELECT to, subject, body FROM messages;
And have a list of mail messages be returned, not from postgres tables,
but from your UNIX mailbox?
Just want to be clear on what you are asking.
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:[EMAIL PROTECTED]
Re: [GENERAL] postgreSQL for storing a database of documents
darold wrote: I'm using Large Object to store images and displayed them on the fly. I never found any problems and it is very quiet. "When Postgre starts using large objects it is going to make a lot of noise." ??? How many images are you storing? -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:[EMAIL PROTECTED]
Re: [GENERAL] postgreSQL for storing a database of documents
Dustin Sallings wrote: On Tue, 4 May 1999, Raphael Finkel wrote: # Is PostgreSQL appropriate for a database of documents? I see several # potential problems. # # 1. Tuples are limited to 8KB. I assume even the "text" type is limited # that way. But my documents may be longer than that. This isn't a problem, I store all of my photographs in postgres. How?
Re: [GENERAL] LIMIT QUESTION
Only PostgreSQL 6.5 Beta supports LIMIT. I've an elementary question. What's wrong with the following : dbtest= select * from testusers limit 10; ERROR: parser: parse error at or near "10" dbtest= select version(); version - PostgreSQL 6.4.2 on i586-pc-linux-gnu, compiled by gcc 2.7.2. (1 row) the testuser table has about 1000 rows. Please comment. Thanks
Re: [GENERAL] advice on buying sun hardware to run postgres
Dustin Sallings wrote: Works != works as well as SCSI. I've yet to find an example where IDE works as well as SCSI in real life (vs. benchmarks). My real life scenarios rarely involve telling a machine to be still so we can do a disk read, then again for a disk write. Modern operating systems don't ask the disk to do something and then just wait for the answer. That's what interrupts are for. Anyway, modern disks have caches. Slow, however more robust than the same in Linux. Linux achieves a lot of speed by throwing away safety nets. Sometimes, these safety nets are important. Never lost a file to Linux in 5 years. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:[EMAIL PROTECTED]
Re: [GENERAL] advice on buying sun hardware to run postgres
Maarten Boekhold wrote: Never lost a file to Linux in 5 years. Haha, just lost my home directory this weekend. But then again, I was in win98 when my rabbit chewed through a 220V cable and the whole room went black. Might just have something to do with it (but on the other hand, win98 shouldn't even be touching that disk, there's only linux partitions on it). You think you're hard done by. Think about the rabbit! -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:[EMAIL PROTECTED]
Re: [GENERAL] Object-oriented stuff and postgres
Umm. I need to know the type of the _object_, not the types of the attributes contained therein. José Soares wrote: --retrieve column information... SELECT a.attnum, a.attname, t.typname, a.attlen, a.atttypmod, a.attnotnull, a.atthasdef FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'comuni' and a.attnum 0 and a.attrelid = c.oid and a.atttypid = t.oid ORDER BY attnum ; attnum|attname |typname|attlen|atttypmod|attnotnull|atthasdef --+--+---+--+-+--+- 1|istat |bpchar |-1| 10|t |f 2|nome |bpchar |-1| 54|t |f 3|provincia |bpchar |-1|6|f |f 4|codice_fiscale|bpchar |-1|8|f |f 5|cap |bpchar |-1|9|f |f 6|regione |bpchar |-1|7|f |f 7|distretto |bpchar |-1|8|f |f (7 rows) José Chris Bitmead ha scritto: What's the best way to do this in postgres? (basicly finding the type of objects). I want to run a web site with different types of content - question and answers, stories etc. I propose an object hierarchy... webobject (title, body) question inherits webobject story (image) inherits (webobject). The idea being you could have a search screen that searches questions AND stories with the one SELECT query. But then each result would have a link to examine the body of the search result. But different types of objects would have different URLs to display that content. So basicly I need to know the type of objects returned. I am loath to store the object type inside the object because it is wasteful. PG obviously already knows the type of objects, the question is how to get at that info. -- Chris Bitmead http://www.bigfoot.com/~chris.bitmead mailto:[EMAIL PROTECTED]
