Re: [GENERAL] Sequence value
You could try
INSERT INTO koe (id, name) SELECT nextval('koe_pkey_id'), 'uusi';
or something like that.
/Roger
Esa Pikkarainen wrote:
So, I want to insert new record and get immediately its sequence
value (serial column) (or OID). I Use iHTML web interface.
Now I have found out that I can give two queries in iHTML like this:
SQL ="SELECT nextval('koe_pkey_id') as val;
INSERT INTO koe (id, name) values (val, 'uusi');"
Now there are some problems. The previous example causes an error:
ERROR: Attribute 'val' not found
Is there a method how to transmit a value from one query to another?
If I try the examples of FAQ:
SQL ="$newSerialID = nextval('koe_pkey_id');
INSERT INTO koe (id, name) values ($newSerialID, 'uusi');"
Or
SQL ="INSERT INTO koe (name) values ('uusi');"
$newSerialID = curr('koe_pkey_id');
I get: ERROR: parser: parse error at or near "$"
And lately (this may be an iHTML problem) if I try:
SQL ="INSERT INTO koe (name) values ('uusi');
SELECT currval('koe_pkey_id') as val;"
It causes no error, but I cannot retrieve the value because the
queries seem not return any recordsets (nothig can be fetched).
Any hints are valuable!
Thanks
Esa Pikkarainen
RE: [GENERAL] Re: Unanswered questions about Postgre
How do you mean "reliable"? This raises an interesting question though. How do I _reliably_ INSERT or UPDATE a row? I can't INSERT - fail - UPDATE as someone might delete the record in between my two statements. I can't BEGIN - INSERT - fail - UPDATE - COMMIT as the failing INSERT will invalidate my transaction (stupid). I can't BEGIN - SELECT FOR UPDATE - INSERT or UPDATE - COMMIT as someone might insert after my SELECT and before my INSERT. Is there a right solution? /Roger -Original Message- From: Tim Kientzle [mailto:[EMAIL PROTECTED]] Sent: den 12 december 2000 02:10 To: PostgreSQL general mailing list Subject: [GENERAL] Re: Unanswered questions about Postgre Of course, people really shouldn't be inserting objects which already exist, ... On the contrary, the best way to test if something already exists is to just try the INSERT and let the database tell you if it's already there. Both faster and more reliable than doing SELECT then INSERT. - Tim
RE: [GENERAL] Re: Unanswered questions about Postgre
No, SELECT FOR UPDATE locks the rows matching the constraint and if no rows match, none are locked. as in (somewhat oracle syntax) BEGIN SELECT 'x' INTO a FROM test WHERE y = 10 FOR UPDATE; UPDATE test SET z = 12; EXCEPTION WHEN no_data_found THEN INSERT INTO test VALUES (10, 12); END; -Original Message- From: Edward Q. Bridges [mailto:[EMAIL PROTECTED]] Sent: den 12 december 2000 17:10 To: PostgreSQL general mailing list; Roger Wernersson Subject: RE: [GENERAL] Re: Unanswered questions about Postgre On Tue, 12 Dec 2000 12:04:46 +0100, Roger Wernersson wrote: I can't BEGIN - SELECT FOR UPDATE - INSERT or UPDATE - COMMIT as someone might insert after my SELECT and before my INSERT. correct me if i'm wrong, but a select for update locks the table for an insert or an update until the end of the transaction. --e-- Is there a right solution? /Roger -Original Message- From: Tim Kientzle [mailto:[EMAIL PROTECTED]] Sent: den 12 december 2000 02:10 To: PostgreSQL general mailing list Subject: [GENERAL] Re: Unanswered questions about Postgre Of course, people really shouldn't be inserting objects which already exist, ... On the contrary, the best way to test if something already exists is to just try the INSERT and let the database tell you if it's already there. Both faster and more reliable than doing SELECT then INSERT. - Tim
[GENERAL] Too much traffic
I'm out of here. This list has too much traffic. Too bad it's not on usenet. /Roger, overwhelmed -Original Message- From: Jon Brace [mailto:[EMAIL PROTECTED]] Sent: den 8 december 2000 11:11 To: [EMAIL PROTECTED] Subject: [GENERAL] Loading tables into Memory In Oracle, there is a method bywhich tables can be loaded into memory as a method of speeding up queries. Is there anyway this can be done in Postgres? Does anyone know a good site outlining general methods of database optimisation? Thanks, - Jonathan Brace Junior Software Engineer Blitz The Net [EMAIL PROTECTED] www.blitzthenet.com
RE: [GENERAL] Clarification
You could of course try Java and JDBC as Java runs everywhere. Then you could run your application on Mac, Windows and Linux as you see fit. /Roger -Original Message- From: Alain Toussaint [mailto:[EMAIL PROTECTED]] Sent: den 6 december 2000 08:10 To: KuroiNeko Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Clarification that too is an option but it would hurt me to have to insert 20K record or something like that with a web browser. H How ODBC driver is supposed to help with that? Upload a CSV or FF file and run bulk insert with COPY. with ODBC: user load the database client app (spreadsheet,small database like access or filemaker) and dump his content on the fat server. without ODBC: user hand off the csv file to the DBA. maybe because it is/was a deal between Apple and HK to bundle some nice sounding speakers with their computer and Apple got a Very good price due to volume. If bundled actually means built-in, then the only thing from H/K there is the name :) no idea at the moment,i don't think it's built in (there's no place to put the speakers in the IMac) but i'll confirm that tomorrow. Alain
RE: [GENERAL] 7.0.3 RPMs?
Will there be an RPM for Alpha? -Original Message- From: Lamar Owen [mailto:[EMAIL PROTECTED]] Sent: Monday, November 13, 2000 6:57 PM To: Matthew Cc: pgsql-general Subject: Re: [GENERAL] 7.0.3 RPMs? Matthew wrote: When are the 7.0.3 RPM's expected to be released? Most of my production servers use the RPM install and I would like to keep it that way, however I would also like to move up to 7.0.3 some time soon. Just wondering... Today. Working on it now. What distribution are you using? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
[GENERAL] Semaphores was: Increasing the number of semaphores on FreeBSD 4.1 (clarification)
Whoa! Shit! I'm working on a system which probably will use ALOT of semaphores and mutexes. Is this the limits, 1000? I was hoping for millions. I have asked around but no-one I have asked seems to know the answer. I'm using pthreads on Linux right now but will probably use HP-UX, AIX, True64 or Solaris when going live. How many semaphores and mutexes can one have, both in theory and practically? /Roger -Original Message- From: Alfred Perlstein [mailto:[EMAIL PROTECTED]] Sent: Friday, November 10, 2000 5:56 AM To: Philip Hallstrom Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] Increasing the number of semaphores on FreeBSD 4.1 (clarification) * Philip Hallstrom [EMAIL PROTECTED] [001109 20:37] wrote: * Philip Hallstrom [EMAIL PROTECTED] [001109 19:12] wrote: Hi - I recently tried to start postmaster (7.0.2) with -B 128 -N 64 and got the "semget failed" error. Looking in the faq[1] it says I need to increase the amount allowed in the kernel. It tells me what I need to do, but my question is what values should I set them to? Is there any way to figure it out based on load, etc? Also, are there detrimental effects to setting them too high? What OS are you using? FreeBSD 4.1 [1]http://postgresql.readysetnet.com/docs/faq-bsdi If it's FreeBSD we've documented the tunables here: http://www.freebsd.org/cgi/cvsweb.cgi/src/sys/i386/conf/NOTES?rev=1.857 The docs will probably also help if you're using BSD/os. Also you don't want to "go nuts" with raising these values, they can cause the kernel to allocate too much memory for these structures and cause problems booting or running your system. Yeah, that's what I've seen... I guess I was wondering if there were any guidelines to raising them.. I mean should I up the defaults by 10? Or up them by a percentage (to keep the relationship), etc... here's what I use: options SHMMAXPGS=512000 options SHMSEG=128 options SEMMNI=40 # /* # of semaphore identifiers */ options SEMMNS=240 # /* # of semaphores in system */ options SEMUME=40 # /* max # of undo entries per process */ options SEMMNU=120 # /* # of undo structures in system */ I have a gig of RAM though. -- -Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]] "I have the heart of a child; I keep it in a jar on my desk."
