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

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.

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.

Are you running with fsync() disabled? That is the single
largest bottleneck for PostgreSQL performance and is
disabled by default in MySQL, except on NT (since NT has a
tendency to crash). You can disable fsync() with the
postmaster -o -F option, to pass the option to the backend.
See the postmaster and postgres man pages for more info.

Hope that helps,

Mike Mascari

On Wed, 22 Dec 1999, 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.

yeouch, that doesnt sound normal... especially seeing that ive been
inserting ~100 rows, doing a convert, cp'ing a file, _and_ checking for
referential integrity in ~40-45 seconds... and the app has the normal perl
overhead.

make sure you've turned fsync off ( -F opt to postmaster iirc ).

> 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?

depends on the select.  if its not hitting indexes ( doing a full table
scan ), things get slow.  try doing an explain of the query and make sure
you have indexes on most (if not all) of the columns in the WHERE clause.

> [SNIP]

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
"I've learned that you cannot make someone love you.
 All you can do is stalk them and hope they panic and give in."

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.
>
>
>
>************

Thought some would like to see a little quatitative data on the impact of running
with the -F flag...

Test case:  Using psql via stdin to load 5,238 inserts of the following form:

    INSERT INTO exchange_rate
    ( from_currency_id, to_currency_id, rate, start_time)
    VALUES
    (150,164, 6.01500000, '09/04/1999');

Platform:  RH6.1, 2.2.12-20smp, dual P3 600Mhz, 1 Gb RAM, 3 10K rpm scsi drives
in software raid.

Without -F, it loaded in 228 seconds (~23 inserts/sec).
With -F, it loaded in 10 seconds (~524 inserts/sec).

2200% boost...

Remember this performance boost comes at the expense of increased risk of data
loss in the event of a system crash.

Cheers,
Ed Loehr