Thread: return records in DB load order??

return records in DB load order??

From
"Maksim Likharev"
Date:
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?

Re: return records in DB load order??

From
"Maksim Likharev"
Date:
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
>


Re: return records in DB load order??

From
"Dann Corbit"
Date:
> -----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.

Re: return records in DB load order??

From
"Maksim Likharev"
Date:
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

Re: return records in DB load order??

From
weigelt@metux.de
Date:
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/

Re: return records in DB load order??

From
"Maksim Likharev"
Date:
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

Re: return records in DB load order??

From
Lincoln Yeoh
Date:
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.


Re: return records in DB load order??

From
Bruno Wolff III
Date:
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.