Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C7A3E9FB1DF for ; Sun, 1 Apr 2007 02:09:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 29327-01 for ; Sun, 1 Apr 2007 02:09:08 -0300 (ADT) X-Greylist: delayed 00:59:59.827427 by SQLgrey-1.7.4 Received: from relais.videotron.ca (relais.videotron.ca [24.201.245.36]) by postgresql.org (Postfix) with ESMTP id 7086C9FB272 for ; Sun, 1 Apr 2007 02:01:29 -0300 (ADT) Received: from tedy ([70.82.131.58]) by VL-MH-MR001.ip.videotron.ca (Sun Java System Messaging Server 6.2-2.05 (built Apr 28 2005)) with ESMTP id <0JFS005NDWIEDV60@VL-MH-MR001.ip.videotron.ca> for pgsql-sql@postgresql.org; Sun, 01 Apr 2007 00:01:28 -0400 (EDT) Date: Sun, 01 Apr 2007 00:01:23 -0400 From: Daniel CAUNE Subject: Sequence vs Serial To: pgsql-sql@postgresql.org Message-id: <000001c77412$6a6e10e0$0b00a8c0@tedy> MIME-version: 1.0 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2900.3028 X-Mailer: Microsoft Office Outlook 11 Content-type: multipart/alternative; boundary="Boundary_(ID_W9ClEY/flRvruyqKGrnj+w)" Thread-index: Acd0EmnfOR4MOk6MTV6FyEsfUELEeg== X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/1 X-Sequence-Number: 28001 This is a multi-part message in MIME format. --Boundary_(ID_W9ClEY/flRvruyqKGrnj+w) Content-type: text/plain; charset=us-ascii Content-transfer-encoding: 7BIT Hi, I was wondering when it is better to choose sequence, and when it is better to use serial. The serial type is a sequence with default parameters (http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SE RIAL). Actually, I never use serial - I prefer sequence for some reasons that I explain later in this electronic mail - but I may be wrong. It's never late to learn! There is not that much difference between using sequence or serial when the current value needs to be returned (which is most of my cases). Using a sequence [1]: SELECT INTO v_mytable_id nextval('seq_mytable_id'); INSERT INTO mytable(id, mycolumn1, mycolumn2) VALUES (v_mytable_id, v_mycolumn1, v_mycolumn2); RETURN v_mytable_id; Using a serial: INSERT INTO mytable(mycolumn1, mycolumn2) VALUES (v_mycolumn1, v_mycolumn2); SELECT INTO v_mytable_id currval('mytable_id_seq'); RETURN v_mytable_id; I would say that these two code snippets have equivalent performance, if sequence seq_mytable_id has been created with default parameters. The reason why I generally prefer sequence over serial is that it is possible to "tune" sequence, which it seems to not be possible while using serial. For instance, the sequence allows defining a cache: the optional clause CACHE specifies how many sequence numbers are to be preallocated and stored in memory for faster access. When inserting a lot of entities in a few times, that should make a big difference! Now, when is it better to use serial? Serial is easier to define and to use. I would say that serial could be used to insert entities in dimension tables (such as a table that references countries, and where a unique identifier must be defined), a table which data are inserted when the operational system is setup. However, using serial for fact tables (such as a table that stores player actions in an MMO game) seems to not be that much relevant. What are your experiences? Regards, [1] It is possible to use another form, similar to serial: CREATE SEQUENCE seq_mytable_id; CREATE TABLE v_mytable(id int nextval('seq_mytable_id'), mycolumn1 int , mycolumn2 int); Then it is possible to use the sequence as follows: INSERT INTO mytable(mycolumn1, mycolumn2) VALUES (v_mycolumn1, v_mycolumn2); SELECT INTO v_mytable_id currval('seq_mytable_id'); RETURN v_mytable_id; -- Daniel --Boundary_(ID_W9ClEY/flRvruyqKGrnj+w) Content-type: text/html; charset=us-ascii Content-transfer-encoding: 7BIT

Hi,

 

I was wondering when it is better to choose sequence, and when it is better to use serial.  The serial type is a sequence with default parameters (http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-SERIAL).  Actually, I never use serial – I prefer sequence for some reasons that I explain later in this electronic mail – but I may be wrong.  It’s never late to learn!

 

There is not that much difference between using sequence or serial when the current value needs to be returned (which is most of my cases).

 

Using a sequence [1]:

 

SELECT INTO v_mytable_id nextval('seq_mytable_id');

 

    INSERT INTO mytable(id, mycolumn1, mycolumn2)

      VALUES (v_mytable_id, v_mycolumn1, v_mycolumn2);

 

    RETURN v_mytable_id;

 

 

Using a serial:

 

    INSERT INTO mytable(mycolumn1, mycolumn2)

      VALUES (v_mycolumn1, v_mycolumn2);

 

SELECT INTO v_mytable_id currval('mytable_id_seq');

 

RETURN v_mytable_id;

 

 

I would say that these two code snippets have equivalent performance, if sequence seq_mytable_id has been created with default parameters.

 

The reason why I generally prefer sequence over serial is that it is possible to “tune” sequence, which it seems to not be possible while using serial.  For instance, the sequence allows defining a cache: the optional clause CACHE specifies how many sequence numbers are to be preallocated and stored in memory for faster access.  When inserting a lot of entities in a few times, that should make a big difference!

 

Now, when is it better to use serial?  Serial is easier to define and to use.  I would say that serial could be used to insert entities in dimension tables (such as a table that references countries, and where a unique identifier must be defined), a table which data are inserted when the operational system is setup.  However, using serial for fact tables (such as a table that stores player actions in an MMO game) seems to not be that much relevant.

 

What are your experiences?

 

Regards,

 

 

 

[1] It is possible to use another form, similar to serial:

 

CREATE SEQUENCE seq_mytable_id;

 

CREATE TABLE v_mytable(id int nextval('seq_mytable_id'), mycolumn1 int , mycolumn2 int);

 

Then it is possible to use the sequence as follows:

 

    INSERT INTO mytable(mycolumn1, mycolumn2)

      VALUES (v_mycolumn1, v_mycolumn2);

 

SELECT INTO v_mytable_id currval('seq_mytable_id');

 

    RETURN v_mytable_id;

 

--

Daniel

--Boundary_(ID_W9ClEY/flRvruyqKGrnj+w)--