From: Bruno Wolff III <bruno@wolff.to>
To: Dani Oderbolz <oderbolz@ecologic.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: Change the behaviour of the SERIAL "Type"
Date: Fri, 27 Jun 2003 10:20:31 -0500
Message-ID: <20030627152031.GF15269@wolff.to> (raw)
In-Reply-To: <3EFC5638.5040406@ecologic.de>
References: <FA785BC0-A7EF-11D7-8248-000A957653D6@tercent.net>
<3EFC0413.7090006@ecologic.de>
<20030627142635.GB15269@wolff.to>
<3EFC5638.5040406@ecologic.de>
On Fri, Jun 27, 2003 at 16:35:36 +0200,
Dani Oderbolz <oderbolz@ecologic.de> wrote:
> Well, why not just use the Sequence?
> Is there really such a performance hit when calling a trigger?
I think the big issue is concurrency. Sequences are designed so that
conncurrent uses of the sequence don't block each other. In the trigger
based methods you have to lock the value against concurrent update and
this lock will be held to the end of the transaction. This has the
potential to really hurt your performance.
I may have been misunderstanding what you are trying to do though.
If your problem was that people could update or insert values into
the serial column that might cause other transactions to unexpected
fail (when they try to use a duplicate value - assuming you use a unique
index on the column) then you could use a trigger to prevent updates
on that column and force inserts to always use nextval. If you need
to reload the database at some point, you will need to do something
to keep the triggers from interfering with the reload. This should
be fairly efficient.
I had thought you were concerned about possible gaps in the sequence.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, oderbolz@ecologic.de
Subject: Re: Change the behaviour of the SERIAL "Type"
In-Reply-To: <20030627152031.GF15269@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox