From: Bruno Wolff III <bruno@wolff.to>
To: Dani Oderbolz <oderbolz@ecologic.de>
Cc: Randall Lucas <rlucas@tercent.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Change the behaviour of the SERIAL "Type"
Date: Fri, 27 Jun 2003 09:26:35 -0500
Message-ID: <20030627142635.GB15269@wolff.to> (raw)
In-Reply-To: <3EFC0413.7090006@ecologic.de>
References: <FA785BC0-A7EF-11D7-8248-000A957653D6@tercent.net>
<3EFC0413.7090006@ecologic.de>
On Fri, Jun 27, 2003 at 10:45:07 +0200,
Dani Oderbolz <oderbolz@ecologic.de> wrote:
>
> Yea, fine, but I propose a different (deeper) approach.
> Why does SERIAL only enforce a DEFAULT?
Because it is faster.
> This is not an exact imitation of an autoincrement, as a DEFAULT can be
> overwritten.
There are probably other differences as well, since serial only provides
a way to get unique values. If you want more meaning than that you
have to be careful.
> In my oppinion, SERIAL should implicitly create a Trigger on the table,
> which then
> handles this transparently.
> Would that be difficult?
It shouldn't be too difficult to write some triggers that make something
closer to autoincrement. It probably won't work very well if there are
lots of concurrent updates though. You can either lock the table with
the column exclusively and then find the largest value and then use
that value plus one. Don't use max for this. Make an index on the
autoincrement column and use order by and limit 1 to get the largest
value. The other option is to keep the sequence value in other table.
You can use select for update to update it. You will want to vacuum
this table often enough that it will stay on one page.
> (I am already writing a Procedure which gets all the info needed out of
> the Catalog,
> but my problem is that I need some dynamic statements in there...)
>
> Cheers, Dani
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
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, rlucas@tercent.net
Subject: Re: Change the behaviour of the SERIAL "Type"
In-Reply-To: <20030627142635.GB15269@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