Re: [GENERAL] Getting value of SERIAL column after insert from libpq?

Someone else had a good idea with the fsync thing, but for the inserts, you
also might want to double-check that you are using a transaction, as that
speeds up transactions considerably.  To start a transaction use the SQL
command "BEGIN WORK;" and then use "COMMIT WORK;" after you have finished
your inserts.  I don't think you can do a select inside a transaction,
(although I've never tried) so you may have to open up a second connection
to the database and find the value your sequence was assigned by SELECTing
the oid returned instead of by using curval()  I believe instructions on
this are in the FAQ.  If not, drop me a lien and I'll look for the PHP3
code I use to do it, it should be fairly close to how you do it in C.

At 12:49 PM 12/22/99, Mark Alliban wrote:
>Thanks for the help, it works great!
>
>However, there is a problem with performance.
>I am moving from MySQL to Postgres, and to test performance I am inserting a
>large row (30 fields) into a table from my C program. I am running this
>program 50 times, and timing the results. The MySQL version of the program
>took 0.75 seconds to execute 50 times, but the Postgres version takes 22-25
>seconds. A similar test with a simple select takes 3.5 seconds on Postgres
>but 0.8 on MySQL. Postgres undoubtably has more features and is better for
>my app than MySQL, but are these performance values normal?
>
>All my program does is read the query from a text file, open the database
>connection, perform the query, output currval('seqence_name') or the query
>results to a text file, and close the connection. This is how my app needs
>to work.
>
>Thanks,
>Mark.
>
> >> Hi,
> >>
> >> I have written a C program to insert a row into a table with a
> >> SERIAL column.
> >>
> >> Is there a way of returning the inserted value for this column
> >> to my program? I.e. if there are rows with the serial column
> >> for 1,2,3,4 and 5, and I insert a row, my program needs to be
> >> told "6" for the new serial. There may be many instances of the
> >> program running simultaneously so I can't do a "select max..."
> >> or "select last_value..." workaround because by the time the
> >> select is done, there may have been other rows inserted so the
> >> last_value would be wrong. Also the program needs to be table-name
> >> and column-name independent so that it can work for ANY insert
> >> query into a table with a SERIAL column.
> >
> >Answer is that currval('seqence_name') will return your last sequence
> >number, even if another session has assigned a sequence number since
> >your nextval() call.
>
>
>
>************