Date: Fri, 27 Jun 2003 10:20:31 -0500
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"
Message-ID: <20030627152031.GF15269@wolff.to>
Mail-Followup-To: Dani Oderbolz <oderbolz@ecologic.de>,
	pgsql-sql@postgresql.org
References: <FA785BC0-A7EF-11D7-8248-000A957653D6@tercent.net>
	<3EFC0413.7090006@ecologic.de> <20030627142635.GB15269@wolff.to>
	<3EFC5638.5040406@ecologic.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <3EFC5638.5040406@ecologic.de>
User-Agent: Mutt/1.5.4i

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.