Обсуждение: return records in DB load order??
Hi, if I'd like to return records in DB load order, will it be enough to say SELECT ..... ORDER BY oid? basically I assume that lowest oid will be for early records?
Is isn't suppose to be unique? -----Original Message----- From: Dennis Gearon [mailto:gearond@cvc.net] Sent: Friday, June 13, 2003 1:30 PM To: Maksim Likharev Subject: Re: [GENERAL] return records in DB load order?? unless the OID rolls over, which can and does happen to bigger databases. Maksim Likharev wrote: > Hi, > if I'd like to return records in DB load order, will it be enough to say > > > SELECT ..... > ORDER BY oid? > > basically I assume that lowest oid will be for early records? > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> -----Original Message----- > From: Maksim Likharev [mailto:mlikharev@aurigin.com] > Sent: Friday, June 13, 2003 1:33 PM > To: gearond@cvc.net > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] return records in DB load order?? > > > Is isn't suppose to be unique? What happens when you have 2^32 + 1 rows in a table? A 4 byte OID cannot contain it.
I am pretty much understand what going to happen with int4 when it hit 2^32 + 1, but what going to happen with PostgreSQL and that that particular table if all rows are alive? -----Original Message----- From: Dann Corbit [mailto:DCorbit@connx.com] Sent: Friday, June 13, 2003 1:37 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] return records in DB load order?? > -----Original Message----- > From: Maksim Likharev [mailto:mlikharev@aurigin.com] > Sent: Friday, June 13, 2003 1:33 PM > To: gearond@cvc.net > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] return records in DB load order?? > > > Is isn't suppose to be unique? What happens when you have 2^32 + 1 rows in a table? A 4 byte OID cannot contain it. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
On Fri, Jun 13, 2003 at 01:36:46PM -0700, Dann Corbit wrote: <snip> > What happens when you have 2^32 + 1 rows in a table? A 4 byte OID > cannot contain it. Isnt the oid type now 64 bits wide ? cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/
It is 32 bit, but discussion slept into different direction, so really I have freshly loaded DB where present set of 2 tables, one [to] many, where many has a primary record, of cause without indication that is primary but ancient knowledge exists that primary record was first during a DB load. In order to fix that, I just want to use oid as indication of first in order, and I guess that's will work. -----Original Message----- From: weigelt@metux.de [mailto:weigelt@metux.de] Sent: Friday, June 13, 2003 1:49 PM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] return records in DB load order?? On Fri, Jun 13, 2003 at 01:36:46PM -0700, Dann Corbit wrote: <snip> > What happens when you have 2^32 + 1 rows in a table? A 4 byte OID > cannot contain it. Isnt the oid type now 64 bits wide ? cu -- --------------------------------------------------------------------- Enrico Weigelt == metux ITS Webhosting ab 5 EUR/Monat. UUCP, rawIP und vieles mehr. phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: contact@metux.de cellphone: +49 174 7066481 smsgate: sms.weigelt@metux.de --------------------------------------------------------------------- Diese Mail wurde mit UUCP versandt. http://www.metux.de/uucp/ ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
At 12:35 PM 6/13/2003 -0700, Maksim Likharev wrote: >Hi, >if I'd like to return records in DB load order, will it be enough to say > > >SELECT ..... >ORDER BY oid? > >basically I assume that lowest oid will be for early records? How about using the "serial" type instead? OID is a bit too close to postgresql internals. Unless you are really "very good friends" with Postgresql I suggest you don't use it if possible. Link.
On Sat, Jun 14, 2003 at 11:00:43 +0800, Lincoln Yeoh <lyeoh@pop.jaring.my> wrote: > At 12:35 PM 6/13/2003 -0700, Maksim Likharev wrote: > > >Hi, > >if I'd like to return records in DB load order, will it be enough to say > > > > > >SELECT ..... > >ORDER BY oid? > > > >basically I assume that lowest oid will be for early records? > > How about using the "serial" type instead? This is a bad idea. Sequences aren't gaurenteed to be monotonicly increasing over time except within a single session. (And of course that nobody is using setval.) If you make sure that sequence values are given out to backends one at a time, this will mostly work. You still have to worry about what is meant by DB load order. Is that when a transaction starts, ends or the time a particular row is written in the middle of a transaction. If it is transaction start time, you can use now(). If it is when the row is written, then you can use timeofday(). I don't think there is a good way to get end of transaction time. I think to get close to that you would want to use timeofday() and write the record just before committing the transaction.