X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id AFEAE310203 for ; Fri, 27 Jun 2003 14:23:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 78854-07 for ; Fri, 27 Jun 2003 11:23:05 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 953C3310225 for ; Fri, 27 Jun 2003 11:23:04 -0300 (ADT) Received: (qmail 15481 invoked by uid 500); 27 Jun 2003 14:26:35 -0000 Date: Fri, 27 Jun 2003 09:26:35 -0500 From: Bruno Wolff III To: Dani Oderbolz Cc: Randall Lucas , pgsql-sql@postgresql.org Subject: Re: Change the behaviour of the SERIAL "Type" Message-ID: <20030627142635.GB15269@wolff.to> Mail-Followup-To: Dani Oderbolz , Randall Lucas , pgsql-sql@postgresql.org References: <3EFC0413.7090006@ecologic.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <3EFC0413.7090006@ecologic.de> User-Agent: Mutt/1.5.4i X-Archive-Number: 200306/315 X-Sequence-Number: 14073 On Fri, Jun 27, 2003 at 10:45:07 +0200, Dani Oderbolz 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