Thread: AW: Re: OID wraparound: summary and proposal
> 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
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
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.
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
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
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
"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
> > > 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