Thread: OID order = INSERT order?
Hi folks,
I am converting a legacy (DBM file backend) app to SQL. The current
storage scheme utlizes ordered list values on objects. To emulate this
I'm just doing multiple INSERTs on a table.
I need to make sure they will come back out in the same order, every
time though.
When I fetch values back from the table, if I 'ORDER BY oid', will I
always get them back in the same order I put them in?
This is happening inside a transaction, if it matters.
- Justin
--
Justin Hawkins
Internode Professional Access
On Fri, Sep 13, 2002 at 10:33:33AM +0930, Justin Hawkins wrote: > > Hi folks, > > I am converting a legacy (DBM file backend) app to SQL. The current > storage scheme utlizes ordered list values on objects. To emulate this > I'm just doing multiple INSERTs on a table. > > I need to make sure they will come back out in the same order, every > time though. > > When I fetch values back from the table, if I 'ORDER BY oid', will I > always get them back in the same order I put them in? > > This is happening inside a transaction, if it matters. Usually yes. In general no. The OID may wrap so than later inserts will appear before earlier inserts. Not to mention that OIDs will soon be optional on tables. If you want to guarentee order, create a serial column and use that. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
Justin Hawkins <justin@internode.com.au> writes:
> When I fetch values back from the table, if I 'ORDER BY oid', will I
> always get them back in the same order I put them in?
Until the OID counter wraps around ...
regards, tom lane
On 13 Sep 2002, Justin Hawkins wrote: > > Hi folks, > > I am converting a legacy (DBM file backend) app to SQL. The current > storage scheme utlizes ordered list values on objects. To emulate this > I'm just doing multiple INSERTs on a table. > > I need to make sure they will come back out in the same order, every > time though. > > When I fetch values back from the table, if I 'ORDER BY oid', will I > always get them back in the same order I put them in? If you update a row, it will then have a different OID since postgresql uses MVCC. do it the right way, make a sequence and attach it to the table.
In 7.3b1, the oids of a row do not change on update according to my experiment. I had thought they did. Is this new with 7.3? elein elein@norcov.com On Monday 16 September 2002 08:43, scott.marlowe wrote: > On 13 Sep 2002, Justin Hawkins wrote: > > Hi folks, > > > > I am converting a legacy (DBM file backend) app to SQL. The current > > storage scheme utlizes ordered list values on objects. To emulate this > > I'm just doing multiple INSERTs on a table. > > > > I need to make sure they will come back out in the same order, every > > time though. > > > > When I fetch values back from the table, if I 'ORDER BY oid', will I > > always get them back in the same order I put them in? > > If you update a row, it will then have a different OID since postgresql > uses MVCC. > > do it the right way, make a sequence and attach it to the table. > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
elein <elein@norcov.com> writes:
> In 7.3b1, the oids of a row do not change on update
> according to my experiment. I had thought they did.
> Is this new with 7.3?
No, same as it ever was.
regards, tom lane
>elein <elein@norcov.com> writes: > > In 7.3b1, the oids of a row do not change on update > > according to my experiment. I had thought they did. > > Is this new with 7.3? > >No, same as it ever was. > > regards, tom lane OIDs only change if you drop from a table and then reinsert the data. Perhaps that is what you were thinking of? Elaine Lindelef
>>>>> "Elaine" == Elaine Lindelef <eel@cognitivity.com> writes:
>> elein <elein@norcov.com> writes:
>> > In 7.3b1, the oids of a row do not change on update according
>> > to my experiment. I had thought they did. Is this new with
>> > 7.3?
>> No, same as it ever was.
Elaine> OIDs only change if you drop from a table and then
Elaine> reinsert the data. Perhaps that is what you were thinking
Elaine> of?
Didn't they (appear to) change long ago in the dark ages when rows had
an epoch attached to them so you were never really updating anything?
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
roland@astrofoto.org Forest Hills, NY 11375
On Tue, 2002-09-24 at 16:42, Roland Roberts wrote: > Didn't they (appear to) change long ago in the dark ages when rows had > an epoch attached to them so you were never really updating anything? afaict we now live in the even darker age when we're still not updating things, but cannot even access the old versions of columns anymore...[1] No, can't remember oids changing. cheers -- vbi [1] I'm *not* complaining. I *like* dark! -- secure email with gpg http://fortytwo.ch/gpg NOTICE: subkey signature! request key 92082481 from keyserver.kjsl.com