Re: [GENERAL] SQL-question: returning the id of an insert querry
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Martijn van Oosterhout wrote:
After you've done the insert on the address table, you can use
currval('address_id_seq') (or equivalent) to get the ID. Ofcourse you have
to have used nextval() for the original insert.
Hope this helps,
..going to try it. Thanks
Andreas Fromm
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.1 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQE/rkMwPkvkZVZzNY0RAnajAJ0ePCTi/UODhGAxOs5NuptZAT0tUgCgpNAz
Oqh8rM934O3SRRzv4Mh9S4I=
=E71z
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [Fwd: [LIH]OpenOffice.org 1.1 - PostgreSQL connectivity]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Shridhar Daithankar wrote: Hi all, I hope this is useful for people using open office regularly. Can we get this posted on techdocs? Regards Shridhar Original Message -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Was fooling around a bit and managed to get OpenOffice.org 1.1 and PostgreSQL to talk to each other. Have documented my experiences in a small HOWTO kind of document. If you're interested in reading it the Linuxdoc SGML source is available at: http://kandalaya.org/download/oo-pgsql.sgml A single-page HTML conversion is also available at the same place for online reading: http://kandalaya.org/download/oo-pgsql.html Feedback welcome. Regards, - -- Raju ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Nice article. What I liked most with OOo is the ability to quiqly dessign Forms to enter Data. Take a look at AutoPilot-Forms. You can even design embeded forms ( Forms which disply Data from different tables). There are some usfull articles abaut that on the OOo-db project site. Regards Andreas Fromm -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/pil5PkvkZVZzNY0RAlgRAJ4s+8OIlKivHGEv0A4AEzw4tmA6/ACgsvBP QaVbXoXrfGYd4FF0uDgZe9s= =ICOt -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] poor cpu utilization on dual cpu box
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 It is probably not really a pg issue that you don't get your CPUs fully used. I would guess that one CPU saturates the memory bus or the disk bandwidth depending on where your data is. This is the typical behavior for memory bounded applications where you don't get the full performanco out of your box and the only thing you can do is to improve your application to reuse data which is in the caches, which is something you probably won't be able to do, due to the nature of databse applications. Regards Andreas Fromm Simon Sadedin wrote: Folks, Im hoping someone can give me some pointers to resolving an issue with postgres and its ability to utilize multiple CPUs effectively. The issue is that no matter how much query load we throw at our server it seems almost impossible to get it to utilize more than 50% cpu on a dual-cpu box. For a single connection we can use all of one CPU, but multiple connections fail to increase the overall utilization (although they do cause it to spread across CPUs). The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading disabled) running a fairly standard Redhat 9 distribution. We are using postgres on this platform with a moderate sized data set (some hundreds of megs of data). The tests perform no updates and simply hit the server with a single large complex query via a multithreaded java/jdbc client. To avoid network distortion we run the client on the localhost (its cpu load is minimal). We are running with shared buffers large enough to hold the entire database and sort memory of 64m, should easily be enough to prevent sorting to disk. At this point Ive tried everything I can think of to diagnose this - checking the pg_locks table indicates that even under heavy load there are no ungranted locks, so it would appear not to be a locking issue. Vmstat/iostat show no excessive figures for network or io waits. The only outlandish figure is that context switches which spike up to 250,000/sec (seems large). By all indications, postgres is waiting internally as if it is somehow singlethreaded. However the documentation clearly indicates this should not be so. Can anyone give me some pointers as to why postgres would be doing this? Is postgres really multi-process capable or are the processes ultimately waiting on each other to run queries or access shared memory? On a second note, has anyone got some tips on how to profile postgres in this kind of situation? I have tried using gprof, but because postgres spawns its processes dynamically I always end up profiling the postmaster (not very useful). Thanking in advance for any help! Cheers, Simon. Do you Yahoo!? The New Yahoo! Shopping http://shopping.yahoo.com/?__yltc=s%3A15443%2Cd%3A22708228%2Cslk%3Atext%2Csec%3Amail - with improved product search -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/lqjpPkvkZVZzNY0RAtw/AKDHhIQSAhOVN/+OMIjeChpwky80BwCgqSOu MhEDKjU23Rb4TONykGzM8wQ= =IRJU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG tools
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Openoffice has a nice formbuilder which can be used to create Frontends to any DB accisble by odbc. It claims to be as good as access, but with the beneffit of all the power of Postgres or any other db engine. There are some docs accessible from the OpenOffice-Databse website G Lam wrote: Hi, I am new to postgresql. I have done some small applications with MS Access. Are there some PG tools out there that can build applications like MS Access does out there? Thanks Gary ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster - -- Andreas Fromm -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQE/jT+pPkvkZVZzNY0RAuIFAJ9Kpssft5AXPCAqPjUiCvBAKhvzvwCgsiDe hFaoSsx19uAO5ZhX7KMlVeM= =+x5L -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] MPI interface
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ramkrishna Chakrabarty wrote: | Hi, | | Is there a MPI interface to PostgreSQL ? Has anybody used. | If yes could please send me some links about the same. | | Regards | RC | What do you mean with a MPI interface? Do you want to access a DB from a mpi-program? If thats wht you want, you can have each thread establsih a connection to the DB and issue the transactions. You don't need any special mpi-pg stuff for this. Regards Andreas -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) Comment: Using GnuPG with Debian - http://enigmail.mozdev.org iD8DBQE/c/FfPkvkZVZzNY0RAoytAJ4pCPBra/9/n98Xj4QE13rOKGGEEgCguw1T BVqeBcjkJUDTVL1KTKODdyg= =5X6J -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] inserting via on insert rule
Peter Eisentraut wrote: Andreas Fromm writes: I was thinking of defining a view users over persons which would let me retrive the list of useres. But How would I implement the rule for insertiung users? I tryed the following but NEW is not known where I want to use it: CREATE VIEW users AS SELECT * FROM persons WHERE is_user(person.id) = TRUE; CREATE RULE insert_on_users AS ON INSERT TO users DO INSTEAD INSERT INTO persons SELECT * FROM NEW; You can write ... DO INSTEAD INSERT INTO persons VALUES (NEW.col1, NEW.col2, ...); Yes, but the advantage of the select would be that I could do a SELECT .. FROM .. WHERE , or how can I perform a checking of the data before insertion? Andreas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] deleting referenced data
Bruno Wolff III wrote: On Wed, Aug 27, 2003 at 00:09:34 +0200, Andreas Fromm [EMAIL PROTECTED] wrote: Bruno Wolff III wrote: On Tue, Aug 26, 2003 at 18:34:04 +0100, Richard Huxton [EMAIL PROTECTED] wrote: I don't think that is what he wants. He seems to want to go in the other direction. That is going to require custom triggers and probably adding a reference count column to data-tables. This is what I want to do, but my question is how can I easely now if there is someone referencing a certain record. Is it just possible by searching every table that could reference it or is there an other way of doing it without adding an reference count column? You can use a reference count in the row you are interested in. You would need to create triggers on all referncing tables to update the reference count as necessary. Also as pointed out previously, you want to use serialized isolation (and handle retries) or exclusively lock the table (and worry about deadlocks and a concurrency bottleneck) if you use this approach. You could do a scan of all possible referencing tables whenever you update any of the referencing tables. However, this approach has pretty much the same problems as using a refernce count and will probably be slower. As the newbe I am, I don't understand much abaut serialized isolation, db-locks and so on. Do I need to care abaut all these considerng that the database will only be accessed by one user at a time, and the hole db will not have many entries? Are there basic dessign considerations that should be taken, or is it something that can be added in the future? As I don't have much time to invest in this project, I will have to come up with some other solution. Maybe something like a 'garbage collection' routine, that runs over the db from time to time and delets all unreferenced records. -- Andreas Fromm ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] deleting referenced data
Hi, I have The following situation: CREATE TABLE data1 ( id SERIAL PRIMARY KEY, data INTEGER ); CREATE TABLE data2 ( id SERIAL PRIMARY KEY, data INTEGER ); CREATE TABLE data3 ( id SERIAL PRIMARY KEY, data INTEGER ); CREATE TABLE relations ( id SERIAL PRIMARY KEY, data1 INTEGER REFERENCES data1, data2 INTEGER REFERENCES data2, data3 INTEGER REFERENCES data3 ); When I delete a record of the table realtions I also want to delete the record on the data-tables if there is no other record referencing them. How can I do this? Regards Andreas Fromm ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
