
> Some other databases have the notion of a ROWID which uniquely
identifies a row
> within a table. OID can be used for that, but it means if you use it,
you must
> limit the size of your whole database system.

Imho that is getting it all wrong. OID is *not* a suitable substitute
for other 
db's ROWID.

If you take a few extra precautions then you can use XTID in PostgreSQL
instead of other's ROWID.

We often hear, that it is safer to use ROWID in Oracle and Informix than
in 
PostgreSQL. It is only true that the risc of getting at the wrong record
is 
lower. Are you going to take chances when manipulating rows ? NO !
Thus any sensible program working on ROWID's will have builtin
precautions,
like locking the table, or using additional where quals.

I am still of the opinion, that we should invent an alias ROWID at the
SQL level
for the current XTID. I do not think that it matters what datatype this
ROWID is,
an arbitrary string like xtid is sufficient, it does not need to be an
integer.

Andreas

Mon, 6 Aug 2001 08:38:03 +0200
"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at>
AW: Re: OID wraparound: summary and proposal
I think you are focusing too much on "ROWID" and not enough on OID. The issue
at hand is OID. It is a PostgreSQL cluster wide limitation. As data storage
decreases in price, the likelihood of people running into this limitation
increases. I have run into OID problems in my curent project. Geez, 40G 7200
RPM drives are $120, amazing.

Tom has proposed being able to remove the OID from tables, to preserve this
resource. I originally thought this was a good idea, but there are tools and
utilities others may want to use in the future that require OIDs, thus they
would have to be re-written or abandoned altogether.

It seems to me, I guess and others too, that the OID mechanism should be on a
per table basis. That way OIDs are much more likely to be unique, and TRUNCATE
on a table should reset it's OID counter to zero.


Zeugswetter Andreas SB SD wrote:
> 
> > Some other databases have the notion of a ROWID which uniquely
> identifies a row
> > within a table. OID can be used for that, but it means if you use it,
> you must
> > limit the size of your whole database system.
> 
> Imho that is getting it all wrong. OID is *not* a suitable substitute
> for other
> db's ROWID.
> 
> If you take a few extra precautions then you can use XTID in PostgreSQL
> instead of other's ROWID.
> 
> We often hear, that it is safer to use ROWID in Oracle and Informix than
> in
> PostgreSQL. It is only true that the risc of getting at the wrong record
> is
> lower. Are you going to take chances when manipulating rows ? NO !
> Thus any sensible program working on ROWID's will have builtin
> precautions,
> like locking the table, or using additional where quals.
> 
> I am still of the opinion, that we should invent an alias ROWID at the
> SQL level
> for the current XTID. I do not think that it matters what datatype this
> ROWID is,
> an arbitrary string like xtid is sufficient, it does not need to be an
> integer.
> 
> Andreas

-- 
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com

Mon, 06 Aug 2001 07:17:17 -0400
mlw <markw@mohawksoft.com>
Re: AW: Re: OID wraparound: summary and proposal
On Mon, 6 Aug 2001, mlw wrote:

> I think you are focusing too much on "ROWID" and not enough on OID. The issue
> at hand is OID. It is a PostgreSQL cluster wide limitation. As data storage
> decreases in price, the likelihood of people running into this limitation
> increases. I have run into OID problems in my curent project. Geez, 40G 7200
> RPM drives are $120, amazing.
Possibly you were using OIDs for what they weren't intended  ;)

> Tom has proposed being able to remove the OID from tables, to preserve
> this resource. I originally thought this was a good idea, but there
> are tools and utilities others may want to use in the future that
> require OIDs, thus they would have to be re-written or abandoned
> altogether.
What are these tools?

> It seems to me, I guess and others too, that the OID mechanism should be on a
> per table basis. That way OIDs are much more likely to be unique, and TRUNCATE
> on a table should reset it's OID counter to zero.
I disagree. OID as it is now is a mandatory SERIAL that is added to every
table. Most tables don't need such a field, those which do, well, they can
keep it as it is now (global per-database), or, if you want per-table
sequence, just create a SERIAL field explicitly.


Mon, 6 Aug 2001 08:29:05 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: AW: Re: OID wraparound: summary and proposal

Somehow I guess I created a misunderstanding. I don't really care about
ROWID. I care that OID is a 32 bit number. The notion that each table could
have its own "OID" similar to a ROWID could be an intermediate solution. I
have flip-flopped a couple times about whether or not the OID being able to
be eliminated from some tables is a good idea. Some code depends on the
OID.

I have hit OID problems personally. To be honest I think it can be a huge
problem. As I have said, 40G disks are under $100. Just a few years ago a
40G storage system would have cost $20K-$30K. BIG databases are being
created today, which wouldn't have been funded just a few years ago.  At my
company we have an aggregated database of 3 distinctly large databases, and
hit a bug in large OID numbers in 7.0.3.

The way I see it there are 4 options for the OID:
(1) Keep OID handling as it is. I think everyone agrees that this is not an
option.
(2) Allow the ability to have tables without OIDs. This is a source of
debate.
(3) Allow tables to have their own notion of an OID. This is harder to do,
and also a source of debate.
(4) Make OIDs 64 or 128 bit. (there are platform issues.)



> > Some other databases have the notion of a ROWID which uniquely
> identifies a row
> > within a table. OID can be used for that, but it means if you use it,
> you must
> > limit the size of your whole database system.
>
> Imho that is getting it all wrong. OID is *not* a suitable substitute
> for other
> db's ROWID.
>
> If you take a few extra precautions then you can use XTID in PostgreSQL
> instead of other's ROWID.
>
> We often hear, that it is safer to use ROWID in Oracle and Informix than
> in
> PostgreSQL. It is only true that the risc of getting at the wrong record
> is
> lower. Are you going to take chances when manipulating rows ? NO !
> Thus any sensible program working on ROWID's will have builtin
> precautions,
> like locking the table, or using additional where quals.
>
> I am still of the opinion, that we should invent an alias ROWID at the
> SQL level
> for the current XTID. I do not think that it matters what datatype this
> ROWID is,
> an arbitrary string like xtid is sufficient, it does not need to be an
> integer.
>
> Andreas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


Mon, 13 Aug 2001 10:17:25 -0400
mlw <markw@mohawksoft.com>
Re: AW: Re: OID wraparound: summary and proposal
Hello, I'm having a problem vacuum a table and I didn't see an answer using
the fts engine.

I have two questions:

1) Is this a big problem, can it be fixed, do I have to dump / restore this
table?
2) I found this problem from my nightly cron driven vacuum -a -z.  When it
hits this error the entire vacuumdb process stops immediately thus skipping
any remaining databases.  Should it do this?  Or should it continue on and
vacuum the other databases?

Here is the error:

cms_beau=# vacuum hits;  (It works without the analyze phase of backup.)
VACUUM
cms_beau=# VACUUM verbose analyze hits;
NOTICE:  --Relation hits--
NOTICE:  Pages 8389: Changed 0, reaped 2, Empty 0, New 0; Tup 834575: Vac 0,
Keep/VTL 4/4, Crash 0, UnUsed 6, MinLen 52, MaxLen 121; Re-using:
Free/Avail. Space 376/64; EndEmpty/Avail. Pages 0/1. CPU 0.34s/0.05u sec.
NOTICE:  Index hits_id_key: Pages 1831; Tuples 834575: Deleted 0. CPU
0.11s/0.56u sec.
NOTICE:  Rel hits: Pages: 8389 --> 8389; Tuple(s) moved: 0. CPU 0.00s/0.00u
sec.
NOTICE:  --Relation pg_toast_6742393--
NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
NOTICE:  Index pg_toast_6742393_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
NOTICE:  Analyzing...
ERROR:  MemoryContextAlloc: invalid request size 4294079565
cms_beau=#

Additional information:

sort_mem = 16384
shared_buffers = 8192

cms_beau=# select version();
                           version
-------------------------------------------------------------
 PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96
(1 row)

cms_beau=# \d hits
                                      Table "hits"
  Attribute  |           Type           |                   Modifier
-------------+--------------------------+-----------------------------------
------------
 id          | integer                  | not null default
nextval('hits_id_seq'::text)
 operator_id | integer                  |
 connected   | timestamp with time zone | default 'now'
 page        | text                     |
Index: hits_id_key

cms_beau=# select count(*) from hits;
 count
--------
 834539
(1 row)


Please let me know if there is any other information you need.

Thank you much,

Matt O'Connor


Mon, 13 Aug 2001 11:08:09 -0500
"Matthew T. O'Connor" <matthew@zeut.net>
Help with Vacuum Failure
mlw wrote:

> The way I see it there are 4 options for the OID:
[snip]
> (2) Allow the ability to have tables without OIDs. This is a source of
> debate.

I think Tom Lane has already committed some patches to allow for this.
So, I think you should be able to try this from the latest CVS. (Tom?)

Neil

-- 
Neil Padgett
Red Hat Canada Ltd.                       E-Mail:  npadgett@redhat.com
2323 Yonge Street, Suite #300, 
Toronto, ON  M4P 2C9

Mon, 13 Aug 2001 14:04:57 -0400
Neil Padgett <npadgett@redhat.com>
Re: Re: AW: Re: OID wraparound: summary and proposal
mlw <markw@mohawksoft.com> writes:

> Somehow I guess I created a misunderstanding. I don't really care about
> ROWID. I care that OID is a 32 bit number. The notion that each table could
> have its own "OID" similar to a ROWID could be an intermediate solution. I
> have flip-flopped a couple times about whether or not the OID being able to
> be eliminated from some tables is a good idea. Some code depends on the
> OID.

See below...

> The way I see it there are 4 options for the OID:

> (2) Allow the ability to have tables without OIDs. This is a source of
> debate.

If we do this, and default OIDs to "on", honestly, where's the
problem?  If the DBA does nothing, things work as before (with
potential OID wraparound issues).  If you want to avoid/minimize the
issues, turn off OIDs on your large tables, and write/fix your code to
cope.

> (3) Allow tables to have their own notion of an OID. This is harder to do,
> and also a source of debate.
> (4) Make OIDs 64 or 128 bit. (there are platform issues.)

(5) [this was suggested earlier] Create separate spaces for "system"
and "user" OIDs.  This requires a similar mechanism to (3), but may be 
somewhat easier.

-Doug
-- 
Free Dmitry Sklyarov! 
http://www.freesklyarov.org/ 

We will return to our regularly scheduled signature shortly.

13 Aug 2001 14:07:58 -0400
Doug McNaught <doug@wireboard.com>
Re: Re: AW: Re: OID wraparound: summary and proposal
Neil Padgett <npadgett@redhat.com> writes:
> mlw wrote:
>> The way I see it there are 4 options for the OID:
> [snip]
>> (2) Allow the ability to have tables without OIDs. This is a source of
>> debate.

> I think Tom Lane has already committed some patches to allow for this.
> So, I think you should be able to try this from the latest CVS. (Tom?)

Yes, it's done and in CVS.  I think this is orthogonal to the other
proposals: whatever we want to do with OID, it's a useful feature to
be able to suppress them for tables that you're sure don't need one.

I thought the discussion had more or less concluded that separate-OID-
generator-per-table was the next step to take.  That won't get done in
time for 7.2, though.

			regards, tom lane

Mon, 13 Aug 2001 14:56:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Re: AW: Re: OID wraparound: summary and proposal 
"Matthew T. O'Connor" <matthew@zeut.net> writes:
> cms_beau=# vacuum hits;  (It works without the analyze phase of backup.)
> VACUUM
> cms_beau=# VACUUM verbose analyze hits;
> NOTICE:  --Relation hits--
> NOTICE:  Pages 8389: Changed 0, reaped 2, Empty 0, New 0; Tup 834575: Vac 0,
> Keep/VTL 4/4, Crash 0, UnUsed 6, MinLen 52, MaxLen 121; Re-using:
> Free/Avail. Space 376/64; EndEmpty/Avail. Pages 0/1. CPU 0.34s/0.05u sec.
> NOTICE:  Index hits_id_key: Pages 1831; Tuples 834575: Deleted 0. CPU
> 0.11s/0.56u sec.
> NOTICE:  Rel hits: Pages: 8389 --> 8389; Tuple(s) moved: 0. CPU 0.00s/0.00u
> sec.
> NOTICE:  --Relation pg_toast_6742393--
> NOTICE:  Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0,
> Keep/VTL 0/0, Crash 0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail.
> Space 0/0; EndEmpty/Avail. Pages 0/0. CPU 0.00s/0.00u sec.
> NOTICE:  Index pg_toast_6742393_idx: Pages 1; Tuples 0. CPU 0.00s/0.00u sec.
> NOTICE:  Analyzing...
> ERROR:  MemoryContextAlloc: invalid request size 4294079565
> cms_beau=#

This looks like you have corrupted data in your table --- specifically,
a variable-length value with a bogus length word.  If so, you'll get a
similar error during any attempt to access the particular value or row
that's corrupted.  A quick check of this theory is to try to pg_dump
the table --- if it fails with the same sort of error, then you have
a problem.

>  PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC 2.96

2.96?  AFAICT 2.95.3 is the latest official release of GCC.

			regards, tom lane

Tue, 14 Aug 2001 11:12:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Help with Vacuum Failure 
>
> > The way I see it there are 4 options for the OID:
>

What about a vacuum analyze for the database that renumbers theOIDs
back at some baseline?  There is still a limitation on the total number
of active rows in the database (0.5 * 2^32), but at least we wouldn't
have this timebomb.

Dale Johnson




Tue, 21 Aug 2001 18:35:18 -0700
Dale Johnson <dale@zembu.com>
Re: AW: Re: OID wraparound: summary and proposal