[GENERAL] PHPBuilder article --
Off of Slashdot: http://www.phpbuilder.com/columns/tim20001112.php3 is an article that is quite favorable to PostgreSQL, about PostgreSQL vs. MySQL on Sourceforge's database. Across-the-board positive. --titus
Re: [GENERAL] storing large amounts of text
- I'm sure this has come up before because it just logically seems like - others would have run up against this too. Is it possible to store a - large amount of text in a row/field? I want to store articles in the - database along with information about them, sort of a content databasing - system. Can anyone reccommend a good way to go about doing this in - Postgres? Any and all help greatly appreciated. I'm doing this for a genome project; you need to look into large object functionality if you want to store anything larger than 8kb in PostgreSQL. This is pretty inconvenient to do, because it's slow to search (I had to write my own search routine; it's at http://www.idyll.org/~t/www-tools/ along with some other stuff for AOLserver/large objects) and you have to use a special program to dump the large objects (again, contact me, or refer to the archives). Several people have recommended that you store the files themselves on your file system and have just the indices be kept in the database. That should work as well, and I'll actually be looking into it today. cheers, --titus
[GENERAL] error message help?
Can anyone tell me what the following two messages mean? They are from 6.5.3. thanks, --titus NOTICE: equal: don't know whether nodes of type 719 are equal NOTICE: LockReleaseAll: xid loop detected, giving up -- Titus Brown, [EMAIL PROTECTED]
Re: [GENERAL] To BLOB Or Not To BLOB
- It seems that the issue with large objects is "Why do you want the info in a - database?" To organize them, of course. - It seems to me that the point of a database is its ability to order and - relate data. If you want to retrieve the "large-ish text files" based on - their content then I think you need to have the files in the database so - they can be searched. However, if you are going to retrieve the files based - on something about them that is stored as a separate attribute, such as - their title, then I think that storing the files as large objects is - unnecessary. Partly I would like to retrieve them based on their content, and it is why I wrote (badly) a small extension that searches large objects for text strings. - If you have the option to "[do] as pointers t those files" then I think you - are not intending to use any of Postgres's features beyond its ability to - store and retrieve data. Surely the file system itself is 'better' at this - regardless of how stable Postgres LO's are. Yes, but the file system is external to the database. A view that I am beginning to appreciate the more that I use databases like PG and Oracle is that all of the relations within a database should be internal. Want to reference a title? Reference to the table of titles. Want to reference a bunch of data? Point to the bunch of data. If I have to use the large object interface to do this, then I will. This way, you can let the database worry about organizing and storing the data, and all you need to do is worry about backing that database up -- not about making sure that the permissions on files are right, or that the directory structure remains the same, or the DB remains synced to the contents of the files... This is also why the FOREIGN KEY functionality that e.g. MySQL completely lacks and PostgreSQL is going to have as of 7.0 is so important - maintaining referential integrity. Here are two specific applications for which I am using large objects, one in Oracle and one in PG, both involving online databases, both using the ArsDigita Community System. First of all, I managed the online paper submission and review process for a conference using Oracle. For this, it was very convenient to be able to grab submissions and stuff them into the database, from which they could be referenced by review etc. Moreover, because of the geographical distribution of the review committee, the ability to grab the papers without giving access to the machine by shell or by FTP (which is generally a security problem) was excellent. Finally, separate revisions of the papers could be kept separate using the obvious internal mechanisms of the database. The second application is one I'm working on right now, a genome database project for the sea urchin. For this, we need to be able to archive arbitrarily large amounts of file-oriented data by (for example) sequence name, and we want to give the option to search it as well. The only really convenient way to do this is to store it all in the database. (Note that it's not terribly *fast* to do it this way ;). I would be happy to discuss this more if anyone has suggestions for alternatives -- I'm not wedded to the approach, but it seemed to be the obvious one. cheers, --titus
[t-ishii@sra.co.jp: Re: [GENERAL] Minor hack to support LO string searches]
This points out a need that I haven't really seen addressed -- a PG software contrib archive. Is there such a thing? Are there any objections to starting such a thing? cheers, --titus - Forwarded message from Tatsuo Ishii [EMAIL PROTECTED] - To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Minor hack to support LO string searches In-Reply-To: [EMAIL PROTECTED] X-Mailer: Mew version 1.94 on Emacs 19.34 / Mule 2.3 (SUETSUMUHANA) Date: Mon, 10 Apr 2000 22:56:15 +0900 From: Tatsuo Ishii [EMAIL PROTECTED] X-Dispatcher: imput version 990905(IM130) X-Mailing-List: [EMAIL PROTECTED] Precedence: bulk Hi, all, at http://www.idyll.org/~t/www-tools/ you'll find an add-on function to PostgreSQL 6.5.3 that allows one to do string searches through large objects. It's not a terribly good hack, but it works ;). I do plan on extending this add-on to support regular expressions Real Soon. I already have such functions + operator definitions. I could post them if there is enough interest. -- Tatsuo Ishii - End forwarded message -
[GENERAL] Minor hack to support LO string searches
Hi, all, at http://www.idyll.org/~t/www-tools/ you'll find an add-on function to PostgreSQL 6.5.3 that allows one to do string searches through large objects. It's not a terribly good hack, but it works ;). I do plan on extending this add-on to support regular expressions Real Soon. There are a couple of other software hacks on the same page, including one that lets you retrieve large objects through the AOLserver database driver for PostgreSQL. cheers, --titus -- Titus Brown, [EMAIL PROTECTED]
[GENERAL] 6.5.3: Two 'INHERITS' problems.
Hi, folks. I'm having some trouble with inheritance in 6.5.3; first off, PRIMARY KEY attributes don't seem to be inherited. For example, CREATE TABLE test (i integer primary key); CREATE TABLE test2 (j integer) INHERITS(test); then INSERT INTO test VALUES (1); INSERT INTO test VALUES (1); correctly returns an error, while INSERT INTO test VALUES (1); INSERT INTO test2 VALUES (1,1); does not. Is this proper behavior?? If so, how can I guarantee that 'i' will be a primary (unique) key for the entire inheritance hierarchy? Secondly, create table superparent ( super_member_1INTEGER ); create table subchild ( child_member_2 INTEGER ) INHERITS (superparent); insert into subchild VALUES (1,1); select * from subchild; -- should return (1, 1) select * from superparent; -- should return null select * from superparent*; -- should return (1) test inheritance: -- add a member specifically to superparent: alter table superparent add member_2 INTEGER; -- add a member to superparent + children alter table superparent* add super_member_3 INTEGER; -- SELECT * : get good behavior from subchild, good behavior from -- superparent, and bad (random??) behavior from superparent*: select * from subchild; select * from superparent; select * from superparent*; -- lesson: either (a) don't add members specifically to superparent, or (b) have some other check on the query results! Selecting everything from superparent* returns a nonsense value for 'member_2', which was added only to superparent and not to the full hierarchy. Again, is this proper behavior?? How can one guard against it? Upgrading to PG 7 is not yet an option - I'm using pgACS, which doesn't yet working with 7 fully AFAIK. If upgrading is the only solution, I'll put up with it 'til I can use 7 ;). Thanks, --titus P.S. Pointers to appropriate documentation would be appreciated; I've read everything I can find... -- Titus Brown, [EMAIL PROTECTED]
