From mauricio@cristorei.com.br Fri Jan  10 00:48:04 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 9EC61476493
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 19:48:04 -0500 (EST)
Received: from cr1.cristorei.net.br (host1.cristorei.com.br [200.162.160.50])
	by postgresql.org (Postfix) with ESMTP id 5C6B947643B
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 19:48:03 -0500 (EST)
Received: from mauricio1 ([200.162.160.203] unverified) by
	cr1.cristorei.net.br with Microsoft SMTPSVC(5.0.2195.4905);
	Thu, 9 Jan 2003 22:48:04 -0200
Message-ID: <010901c2b841$f429f290$830101c0@mauricio1>
From: =?iso-8859-1?Q?Maur=EDcio_Sessue_Otta?= <mauricio@cristorei.com.br>
To: <pgsql-sql@postgresql.org>
Subject: Getting sequence value after inserting many rows at a time
Date: Thu, 9 Jan 2003 22:48:11 -0200
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0106_01C2B831.3003D120"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2720.3000
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-OriginalArrivalTime: 10 Jan 2003 00:48:04.0626 (UTC)
	FILETIME=[EF41FF20:01C2B841]
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/143
X-Sequence-Number: 11008

This is a multi-part message in MIME format.

------=_NextPart_000_0106_01C2B831.3003D120
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,

I have a PHP script that do something like this:

INSERT INTO table_with_sequence (field1, field2...., fieldn)=20
SELECT field1, field2, ..., fieldn FROM table,table=20
WHERE condition, condition

My doubt:
Will the rows just inserted in "table_with_sequence" always be sequencial?
(won't it happen to have X rows from this INSERT, 1 rows from another INSERT
in the middle and the rest rows of the first INSERT?)

How can I safely get the first value the INSERT "generated" for=20
the sequence?

[]'s Mauricio

------=_NextPart_000_0106_01C2B831.3003D120
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2722.900" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I have a PHP script that do something like=
=20
this:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>INSERT INTO table_with_sequence (field1,=
=20
field2...., fieldn) </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>SELECT field1, field2, ..., fieldn FROM ta=
ble,table=20
</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>WHERE condition, condition</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>My doubt:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Will the rows just inserted in=20
"table_with_sequence" always be sequencial?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>(won't it happen to have&nbsp;X rows from =
this=20
INSERT, 1 rows from another INSERT</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>in the middle and the rest rows of the fir=
st=20
INSERT?)</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>How can I safely get the first value the=
=20
INSERT&nbsp;"generated" for </FONT></DIV>
<DIV><FONT face=3DArial size=3D2>the sequence?</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>[]'s Mauricio</FONT></DIV></BODY></HTML>

------=_NextPart_000_0106_01C2B831.3003D120--




From bruno@wolff.to Fri Jan  10 01:13:12 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id 6406D475D6E
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 20:13:12 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id ADFE5475D17
	for <pgsql-sql@postgresql.org>; Thu,  9 Jan 2003 20:13:11 -0500 (EST)
Received: (qmail 22294 invoked by uid 500); 10 Jan 2003 01:25:15 -0000
Date: Thu, 9 Jan 2003 19:25:15 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: =?iso-8859-1?Q?Maur=EDcio?= Sessue Otta <mauricio@cristorei.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting sequence value after inserting many rows at a time
Message-ID: <20030110012515.GA22248@wolff.to>
Mail-Followup-To: =?iso-8859-1?Q?Maur=EDcio?= Sessue Otta
	<mauricio@cristorei.com.br>, pgsql-sql@postgresql.org
References: <010901c2b841$f429f290$830101c0@mauricio1>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <010901c2b841$f429f290$830101c0@mauricio1>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/144
X-Sequence-Number: 11009

On Thu, Jan 09, 2003 at 22:48:11 -0200,
  Maurício Sessue Otta <mauricio@cristorei.com.br> wrote:
> Hi,
> 
> I have a PHP script that do something like this:
> 
> INSERT INTO table_with_sequence (field1, field2...., fieldn) 
> SELECT field1, field2, ..., fieldn FROM table,table 
> WHERE condition, condition
> 
> My doubt:
> Will the rows just inserted in "table_with_sequence" always be sequencial?
> (won't it happen to have X rows from this INSERT, 1 rows from another INSERT
> in the middle and the rest rows of the first INSERT?)
> 
> How can I safely get the first value the INSERT "generated" for 
> the sequence?

You might want to reconsider the design. You might want to use another
serial column to indicate the grouping. This will allow you to pick out
records inserted as a group.



From bruno@wolff.to Sat Jan  11 18:33:07 2003
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id BE20E475C98
	for <pgsql-sql@postgresql.org>; Sat, 11 Jan 2003 13:33:07 -0500 (EST)
Received: from wolff.to (wolff.to [66.93.249.74])
	by postgresql.org (Postfix) with SMTP id 11514475BA0
	for <pgsql-sql@postgresql.org>; Sat, 11 Jan 2003 13:33:07 -0500 (EST)
Received: (qmail 12204 invoked by uid 500); 11 Jan 2003 18:45:11 -0000
Date: Sat, 11 Jan 2003 12:45:11 -0600
From: Bruno Wolff III <bruno@wolff.to>
To: =?iso-8859-1?Q?Maur=EDcio?= Sessue Otta <mauricio@cristorei.com.br>
Cc: pgsql-sql@postgresql.org
Subject: Re: Getting sequence value after inserting many rows at a time
Message-ID: <20030111184511.GB12098@wolff.to>
Mail-Followup-To: =?iso-8859-1?Q?Maur=EDcio?= Sessue Otta
	<mauricio@cristorei.com.br>, pgsql-sql@postgresql.org
References: <20030111182547.GA12098@wolff.to>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <010901c2b841$f429f290$830101c0@mauricio1>
User-Agent: Mutt/1.3.25i
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/160
X-Sequence-Number: 11025


On Sat, Jan 11, 2003 at 12:25:47 -0600,
  Maurício Sessue Otta <mauricio@cristorei.com.br> wrote:
> Hi,
> 
> I have a PHP script that do something like 
> this:
> 
> INSERT INTO table_with_sequence (field1, 
> field2...., fieldn) 
> SELECT field1, field2, ..., fieldn FROM table,table 
> 
> WHERE condition, condition
> 
> My doubt:
> Will the rows just inserted in 
> "table_with_sequence" always be sequencial?
> (won't it happen to have X rows from this 
> INSERT, 1 rows from another INSERT
> in the middle and the rest rows of the first 
> INSERT?)
> 
> How can I safely get the first value the 
> INSERT "generated" for 
> the sequence?

I wanted to expound on my previous answer some more.

In general sequences are designed to provide an efficient way to generate
unique ids. If you are trying to get any more than uniqness out of them
you need to be careful. And in being careful, you may need to do things
that will make them less efficient.

In particular there can be gaps in sequences and they only have a loose
correlation with time.

I suspect that what you are trying to do is label a group in your example.
This is based on the insert not have a guarenteed order and your concern
about other transactions using sequence numbers between the lowest and
highest numbers used in a specif transaction.

My suggestion is to instead use one sequence number for the whole group.
A sketched example follows:

INSERT INTO newtable (groupid, field1, field2...., fieldn) 
  SELECT a.groupid, b.field1, b.field2, ..., b.fieldn
    FROM (SELECT nextval('groupid_seq') as groupid) a,
      (SELECT * FROM oldtable WHERE condition, condition) b

The groupid will be the same for each row inserted by a single instance
of this insert statement. Following sql statements can use
currval('groupid_seq') to get the value used for this insert statement.

Doing things this way will probably make other parts of what you are doing
easier. In particular getting records from a specific group will be much
simpler if they all have the same groupid as opposed to all groupids within
a specific range (that will somehow need to be tracked).