From sharmad@im.eth.net Fri Jan 12 16:21:21 2001 Received: from im.eth.net (mail.uthplanet.com [202.9.136.18]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0CGLKp54790 for ; Fri, 12 Jan 2001 11:21:21 -0500 (EST) (envelope-from sharmad@im.eth.net) Received: from SharmadNaik ([61.11.9.217]) by im.eth.net with Microsoft SMTPSVC(5.5.1877.117.11); Fri, 12 Jan 2001 21:46:21 +0530 Message-ID: <004c01c07cb1$2e266e20$d9090b3d@SharmadNaik> From: "Sharmad Naik" To: "PGSQL-SQL" Subject: Serials Date: Fri, 12 Jan 2001 21:31:58 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003D_01C07CDF.17B716C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200101/118 X-Sequence-Number: 779 This is a multi-part message in MIME format. ------=_NextPart_000_003D_01C07CDF.17B716C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable What's the purpose of Serials in Data types of postgres ?=20 TIA=20 Sharmad ------=_NextPart_000_003D_01C07CDF.17B716C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
What's the purpose of Serials in Data type= s of=20 postgres ?
TIA
Sharmad
------=_NextPart_000_003D_01C07CDF.17B716C0-- From josh@agliodbs.com Fri Jan 12 16:35:54 2001 Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0CGZrp56926 for ; Fri, 12 Jan 2001 11:35:54 -0500 (EST) (envelope-from josh@agliodbs.com) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.3) with HTTP id 1176339 for ; Fri, 12 Jan 2001 08:35:49 -0800 From: "Josh Berkus" Subject: Re: Serials To: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.3 Date: Fri, 12 Jan 2001 08:35:49 -0800 Message-ID: In-Reply-To: <004c01c07cb1$2e266e20$d9090b3d@SharmadNaik> MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200101/119 X-Sequence-Number: 780 Mr. Naik, > What's the purpose of Serials in Data types of postgres ? > TIA > Sharmad This topic is more than adequately covered in both the online documentation and Bruce Momjian's book. If language or other problems are preventing you from finding the correct reference, please reply and I'll give you a pointer. Othervwise, RTFM. -Josh Berkus From tswan-lst@ics.olemiss.edu Mon Jan 15 00:25:10 2001 Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f0F0PAp91507 for ; Sun, 14 Jan 2001 19:25:10 -0500 (EST) (envelope-from pgsql-sql-owner@postgresql.org) Received: from tangent.ics.olemiss.edu (tangent.ics.olemiss.edu [130.74.31.3]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0CM2kp88408 for ; Fri, 12 Jan 2001 17:02:46 -0500 (EST) (envelope-from tswan-lst@ics.olemiss.edu) Received: from Betelgeuse.ics.olemiss.edu (wsp000771wss.ics.olemiss.edu [130.74.31.251]) by tangent.ics.olemiss.edu (8.9.3/8.8.7) with ESMTP id QAA19251; Fri, 12 Jan 2001 16:02:43 -0600 Message-Id: <5.0.2.1.0.20010112150114.01a2a948@tangent.ics.olemiss.edu> X-Sender: tswan-lst@tangent.ics.olemiss.edu X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Fri, 12 Jan 2001 16:02:42 -0600 To: "Josh Berkus" , pgsql-sql@postgresql.org From: Thomas Swan Subject: Re: Serials In-Reply-To: References: <004c01c07cb1$2e266e20$d9090b3d@SharmadNaik> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed X-Archive-Number: 200101/136 X-Sequence-Number: 797 At 1/12/2001 10:35 AM, Josh Berkus wrote: >Mr. Naik, > > > What's the purpose of Serials in Data types of postgres ? > > TIA > > Sharmad SERIAL is the same as makeing a column and then setting the default value to a sequence. If you insert and don't specify the column that is of type serial it will have a new unique value on each insert. The datatype in a serial column is an int4. >This topic is more than adequately covered in both the >online documentation and Bruce Momjian's book. If language >or other problems are preventing you from finding the >correct reference, please reply and I'll give you a pointer. >Othervwise, RTFM. These things are explained with little effort. Granted sometimes these are easily answered by consulting documentation, but sometimes all anyone is looking for is a simple, quick answer. Thomas From grant@conprojan.com.au Sat Mar 24 05:55:16 2001 Received: from webster.conprojan.com.au (raw130515-1.gw.connect.com.au [202.21.11.98]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2O5tFw57930 for ; Sat, 24 Mar 2001 00:55:16 -0500 (EST) (envelope-from grant@conprojan.com.au) Received: (qmail 21951 invoked by uid 523); 24 Mar 2001 05:55:09 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 24 Mar 2001 05:55:09 -0000 Date: Sat, 24 Mar 2001 15:55:09 +1000 (EST) From: Grant To: pgsql-sql@postgresql.org Subject: Serials. Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200103/472 X-Sequence-Number: 2141 Please see below for my table schema. I have two questions. (1) Why is a sequence limited to 2147483647, it seems very small? (2) If I reset the sequence, then try another insert. It will not insert anything until it cycles through all sequences and finds an unused one. It will give the following error each time it tries to insert a row with a used sequence: PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique index releases_pkey How can I possibly get around this issue so that I can be sure to always have a free id without getting the error above? Thankyou. -- TABLE -- =# \d bookings Table "bookings" Attribute | Type | Modifier --------------+-----------+--------------------------------------------------- id | integer | not null default nextval('bookings_id_seq'::text) added | timestamp | not null client_id | smallint | not null booking_time | timestamp | not null duration | float4 | not null notes | text | not null Index: bookings_pkey =# From dev@archonet.com Sat Mar 24 14:04:46 2001 Received: from www-uk-05.digiserve.com ([195.97.231.11]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2OE4kw51718 for ; Sat, 24 Mar 2001 09:04:46 -0500 (EST) (envelope-from dev@archonet.com) Received: from mainbox.archonet.com (mwynhau.demon.co.uk [193.237.186.96]) by www-uk-05.digiserve.com (8.9.1/8.9.1) with ESMTP id OAA26808; Sat, 24 Mar 2001 14:05:15 GMT Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id 6EACD2A68A; Sat, 24 Mar 2001 11:34:45 +0000 (GMT) Message-ID: <3ABC8631.8041704F@archonet.com> Date: Sat, 24 Mar 2001 11:34:09 +0000 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Grant Cc: pgsql-sql@postgresql.org Subject: Re: Serials. References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200103/475 X-Sequence-Number: 2144 Grant wrote: > > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? That's 2 billion(ish) - the largest signed 32 bit integer. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Well - you've told PG to only allow unique id values and then reset the sequence that generates its values. It's doing pretty much what you'd expect, I'd say. How fast are you inserting these bookings? According to my calculations that's a sustained rate of 68 inserts/sec over a whole year. If you just want unique records, put the primary key over id,added and let the id_seq cycle. If you want more than 2 billion unique id values you'll need to combine the sequence with another value - see my postgresql notes at techdocs.postgresql.org and perhaps look into the plpgsql cookbook (www.brasileiro.net from memory) - Richard Huxton From darcy@druid.net Sat Mar 24 12:58:25 2001 Received: from druid.net (druid.net [216.126.72.98]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2OCwKw59666 for ; Sat, 24 Mar 2001 07:58:25 -0500 (EST) (envelope-from darcy@druid.net) Received: by druid.net (Postfix, from userid 1000) id 906311A62; Sat, 24 Mar 2001 07:57:50 -0500 (EST) Subject: Re: Serials. In-Reply-To: "from Grant at Mar 24, 2001 03:55:09 pm" To: Grant Date: Sat, 24 Mar 2001 07:57:50 -0500 (EST) Cc: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL78 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII Message-Id: <20010324125750.906311A62@druid.net> From: darcy@druid.net (D'Arcy J.M. Cain) X-Archive-Number: 200103/474 X-Sequence-Number: 2143 Thus spake Grant > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? Yikes! What are you counting? :-) The value 2147483647 is the largest value that can fit into an int. It is equal to 0x7fffffff in hex. If you add one to that you get -2147483648. I suppose the number could be doubled by using an unsigned int for the serial type. It should never be negative anyway. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Well, there are a lot of questions to ask before answering that one. What is the reason for resetting the sequence? Do you actually have values in that range and are hitting a ceiling. Are there a lot of holes in your sequence? The short ansqwer is to use max and setval to reset your index above the highest number but if holes are the problem then that won't help. You may need to run a script that renumbers down but don't forget to renumber any tables that reference it. Referential integrity constraints may also get in your way. Hey, this is PostgreSQL. If you don't like the way that nextval works then just create your own function with the behaviour that you need. You can keep your own list of numbers and fill holes and all sorts of things. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. From mathijs@ilse.nl Sat Mar 24 13:50:11 2001 Received: from serv1.ilse.nl (serv1.ilse.nl [212.189.131.11]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2ODo3w40182 for ; Sat, 24 Mar 2001 08:50:11 -0500 (EST) (envelope-from mathijs@serv0.ilse.nl) Received: from serv0.ilse.nl (intra.ilse.nl [10.1.8.249]) by serv1.ilse.nl (8.11.0/8.10.2) with ESMTP id f2ODnHX86254; Sat, 24 Mar 2001 14:49:17 +0100 (MET) Received: (from mathijs@localhost) by serv0.ilse.nl (8.11.1/8.11.1) id f2ODnEj37375; Sat, 24 Mar 2001 14:49:14 +0100 (CET) (envelope-from mathijs) Date: Sat, 24 Mar 2001 14:49:14 +0100 From: Mathijs Brands To: Grant Cc: pgsql-sql@postgresql.org Subject: Re: Serials. Message-ID: <20010324144914.A37141@ilse.nl> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from grant@conprojan.com.au on Sat, Mar 24, 2001 at 03:55:09PM +1000 X-Unexpected-Header: The Spanish Inquisition X-Archive-Number: 200103/475 X-Sequence-Number: 2146 On Sat, Mar 24, 2001 at 03:55:09PM +1000, Grant allegedly wrote: > Please see below for my table schema. I have two questions. > > (1) Why is a sequence limited to 2147483647, it seems very small? This is the maximum value a signed integer (32-bit) can contain. If this really is a problem for you, then it might be a good idea to look into another database; one specifically suited for huge databases, such as NCR Terabase. To put things in perspective: if you have a table with more than two billion records each about 50 bytes in size you would have more than (!) 100 GB of data... I don't think PostgreSQL is meant for those enormous amounts of data, although it may still work porperly. Most larger commercial database offerings (Oracle and Sybase come to mind) will still work well, but once you're database contains a few terabytes of data you're just asking for problems. There is a very good chance that you can spread the data accross several database, increasing performance and maintainability. Of course, writing your own serial implementation is not that difficult. If you use 64-bit integers (int8), you should have plenty of space :) Search the mailinglist archive for some clues, since there was a discussion about this (implementing a sequence that doesn't have holes) one or two days ago. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey > > How can I possibly get around this issue so that I can be sure to always > have a free id without getting the error above? Get the maximum primary key value from the table and start the sequence from the number following it. I hope this helps, Mathijs -- $_='while(read+STDIN,$_,2048){$a=29;$c=142;if((@a=unx"C*",$_)[20]&48){$h=5; $_=unxb24,join"",@b=map{xB8,unxb8,chr($_^$a[--$h+84])}@ARGV;s/...$/1$&/;$d= unxV,xb25,$_;$b=73;$e=256|(ord$b[4])<<9|ord$b[3];$d=$d>>8^($f=($t=255)&($d >>12^$d>>4^$d^$d/8))<<17,$e=$e>>8^($t&($g=($q=$e>>14&7^$e)^$q*8^$q<<6))<<9 ,$_=(map{$_%16or$t^=$c^=($m=(11,10,116,100,11,122,20,100)[$_/16%8])&110;$t ^=(72,@z=(64,72,$a^=12*($_%16-2?0:$m&17)),$b^=$_%64?12:0,@z)[$_%8]}(16..271)) [$_]^(($h>>=8)+=$f+(~$g&$t))for@a[128..$#a]}print+x"C*",@a}';s/x/pack+/g;eval From peter_e@gmx.net Sat Mar 24 14:20:09 2001 Received: from mailout00.sul.t-online.com (mailout00.sul.t-online.com [194.25.134.16]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2OEK8w56451 for ; Sat, 24 Mar 2001 09:20:09 -0500 (EST) (envelope-from peter_e@gmx.net) Received: from fwd00.sul.t-online.com by mailout00.sul.t-online.com with smtp id 14gotO-0002zm-01; Sat, 24 Mar 2001 15:19:46 +0100 Received: from peter.localdomain (520083510237-0001@[217.80.146.15]) by fmrl00.sul.t-online.com with esmtp id 14got9-0dGh1cC; Sat, 24 Mar 2001 15:19:31 +0100 Date: Sat, 24 Mar 2001 15:29:49 +0100 (CET) From: Peter Eisentraut To: Grant cc: Subject: Re: Serials. In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200103/476 X-Sequence-Number: 2147 Grant writes: > (1) Why is a sequence limited to 2147483647, it seems very small? Because that's what a four-byte signed integer takes. No one has stepped forward to implement 8-byte sequence counters, yet. > (2) If I reset the sequence, then try another insert. It will not insert > anything until it cycles through all sequences and finds an unused one. It > will give the following error each time it tries to insert a row with a > used sequence: > > PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique > index releases_pkey Why did you reset the sequence in the first place? You should probably set it back to where it was (using setval()). Sequences simply return incrementing numbers, they don't fill holes or have any constraint-avoiding logic. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/ From grant@conprojan.com.au Sat Mar 24 21:56:18 2001 Received: from webster.conprojan.com.au (raw130515-1.gw.connect.com.au [202.21.11.98]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2OLuFw19692 for ; Sat, 24 Mar 2001 16:56:18 -0500 (EST) (envelope-from grant@conprojan.com.au) Received: (qmail 26169 invoked by uid 523); 24 Mar 2001 21:56:06 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 24 Mar 2001 21:56:06 -0000 Date: Sun, 25 Mar 2001 07:56:06 +1000 (EST) From: Grant To: "D'Arcy J.M. Cain" cc: pgsql-sql@postgresql.org Subject: Re: Serials. In-Reply-To: <20010324125750.906311A62@druid.net> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200103/481 X-Sequence-Number: 2152 > > (1) Why is a sequence limited to 2147483647, it seems very small? > > Yikes! What are you counting? :-) I have a message board. Where users can send each other messages. I doubt I will ever get 2147483647 messages, but I want to make sure I never get an error where the message isn't sent. I guess the best option is to create a function that changes all the ids to the lowest values possible and then set the sequence to the maximum value + 1. Thanks everyone for your help. From danlyke@flutterby.com Sun Mar 25 02:40:04 2001 Received: from mail.flutterby.com (www.flutterby.com [209.140.49.28]) by mail.postgresql.org (8.11.3/8.11.1) with ESMTP id f2P2e3w43658 for ; Sat, 24 Mar 2001 21:40:04 -0500 (EST) (envelope-from danlyke@flutterby.com) Received: from wynand.flutterby.com (c1294852-a.marin1.sfba.home.com [65.11.14.11]) by mail.flutterby.com (8.9.3/8.9.3/Debian/GNU) with ESMTP id SAA31177 for ; Sat, 24 Mar 2001 18:44:10 -0800 Received: (from danlyke@localhost) by wynand.flutterby.com (8.9.3/8.9.3/Debian 8.9.3-21) id SAA00962; Sat, 24 Mar 2001 18:28:08 -0800 From: Dan Lyke MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Message-ID: <15037.22456.492634.743275@wynand.flutterby.com> Date: Sat, 24 Mar 2001 18:28:08 -0800 (PST) To: pgsql-sql@postgresql.org Subject: Re: Serials. In-Reply-To: References: <20010324125750.906311A62@druid.net> X-Mailer: VM 6.72 under 21.1 (patch 10) "Capitol Reef" XEmacs Lucid X-Archive-Number: 200103/485 X-Sequence-Number: 2156 Grant writes: > I have a message board. Where users can send each other messages. I > doubt I will ever get 2147483647 messages, but I want to make sure I > never get an error where the message isn't sent. Think about loads. If your users are going to be posting 10 messages/second, that's 864000 messages per day, you won't wrap for nearly 7 years. I've got a pretty heavy mail load, including spam I probably get 300 messages/day, weekends are lighter, so if you've got a bunch of weenies who are subscribed to a gazillion mailing lists you're talking three thousand users for six and a half years. A little light if you're planning on being the next Hotmail (A test account set up there gets 70 spams/day without my ever publishing the address), but for your average mid-range discussion forum you're probably good for a while. I doubt that, say, Salon's TableTalk forum gets even 10k new messages per day. > I guess the best option is to create a function that changes all the > ids to the lowest values possible and then set the sequence to the > maximum value + 1. Better idea, if one of your deletion criteria is going to be age, would just be to wrap the IDs. Dan From grant@conprojan.com.au Mon Mar 26 00:05:58 2001 Received: from webster.conprojan.com.au (raw130515-1.gw.connect.com.au [202.21.11.98]) by mail.postgresql.org (8.11.3/8.11.1) with SMTP id f2Q05tw82305 for ; Sun, 25 Mar 2001 19:05:58 -0500 (EST) (envelope-from grant@conprojan.com.au) Received: (qmail 29658 invoked by uid 523); 25 Mar 2001 23:58:57 -0000 Received: from localhost (sendmail-bs@127.0.0.1) by localhost with SMTP; 25 Mar 2001 23:58:57 -0000 Date: Mon, 26 Mar 2001 09:58:57 +1000 (EST) From: Grant To: Dan Lyke cc: pgsql-sql@postgresql.org Subject: Re: Serials. In-Reply-To: <15037.22456.492634.743275@wynand.flutterby.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200103/499 X-Sequence-Number: 2170 > Grant writes: > > I have a message board. Where users can send each other messages. I > > doubt I will ever get 2147483647 messages, but I want to make sure I > > never get an error where the message isn't sent. > > Think about loads. If your users are going to be posting 10 > messages/second, that's 864000 messages per day, you won't wrap for > nearly 7 years. I've got a pretty heavy mail load, including spam I > probably get 300 messages/day, weekends are lighter, so if you've got > a bunch of weenies who are subscribed to a gazillion mailing lists > you're talking three thousand users for six and a half years. > > A little light if you're planning on being the next Hotmail (A test > account set up there gets 70 spams/day without my ever publishing the > address), but for your average mid-range discussion forum you're > probably good for a while. I doubt that, say, Salon's TableTalk forum > gets even 10k new messages per day. I understand what you're saying. However it's not the amount of messages per day. I have cycle set on the sequence so that when it reaches the limit it will start back at 1 again. If however some users still have messages in their accounts that have used random ids from 1 onwards postgresql will produce the error that it's trying to insert a duplicate id that already exists, so in theory I want the system to run for infinity. I hope this makes sense. Thankyou for your time.