SEO

The following bug has been logged online:

Bug reference: 1814
Logged by: Ian Burrell
Email address: ianburrell@gmail.com
PostgreSQL version: 7.4.6
Operating system: RHEL 3 x86_64
Description: Cancelling a CLUSTER changes the OID counter
Details:

Cancelling a CLUSTER is causing the OID counter to jump forwards. In the
test below, it goes from 108 million to 4286 million (close to 2^32).

We recently wrapped the OID counter.

vodlive=# insert into foo values (1) ;
INSERT 108817614 1
Time: 0.675 ms
vodlive=# select oid, * from foo ;
oid | bar
-----------+-----
108817614 | 1

vodlive=# CLUSTER idx_daily_by_cs_ti_wk_wk_cs_ti ON
daily_xtns_by_cable_sys_title_wk ;
Cancel request sent
ERROR: canceling query due to user request
vodlive=# abort ;
ROLLBACK

vodlive=# insert into foo values (1) ;
INSERT 4286822632 1
Time: 0.475 ms
vodlive=# select oid, * from foo ;
oid | bar
------------+-----
4286822632 | 1
(1 row)

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"Ian Burrell" <ianburrell@gmail.com> writes:
> Cancelling a CLUSTER is causing the OID counter to jump forwards. In the
> test below, it goes from 108 million to 4286 million (close to 2^32).

[ scratches head... ] Cannot duplicate this here. Does anyone else
see it?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"Ian Burrell" <ianburrell@gmail.com> writes:
> Cancelling a CLUSTER is causing the OID counter to jump forwards. In the
> test below, it goes from 108 million to 4286 million (close to 2^32).

> We recently wrapped the OID counter.

Uh, does the same thing happen if you *don't* cancel it?

It looks to me like this could possibly happen due to CheckMaxObjectId()
being applied to each OID found in the existing table.

CheckMaxObjectId was always a kluge, and I'm not sure that it still has
any redeeming social value at all. Can anyone think of a good reason
to keep it?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Ian Burrell <ianburrell@gmail.com> writes:
> On 8/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It looks to me like this could possibly happen due to CheckMaxObjectId()
>> being applied to each OID found in the existing table.
>>
>> CheckMaxObjectId was always a kluge, and I'm not sure that it still has
>> any redeeming social value at all. Can anyone think of a good reason
>> to keep it?

> From looking in the code, I am pretty sure CheckMaxObjectId is the
> culprit. It sets the nextOID to the oid in the row if the
> assigned_oid is greater than the nextOID.

Yeah. This is closely related to my recent speculations about putting
in a more direct defense against duplicate OIDs:

http://archives.postgresql.org/pgsql...8/msg00074.php

I think if we did that, particularly in the general any-unique-OID-index
form suggested here

http://archives.postgresql.org/pgsql...8/msg00247.php

then we could feel justified in simply discarding CheckMaxObjectId.
We would then have a mechanism that guaranteed OID uniqueness on a
per-table basis, but not at all on a cluster-wide basis, which is
the mindset that CheckMaxObjectId comes from. In environments where
databases live long enough to have OID wraparound, CheckMaxObjectId
is worse than useless --- it creates uniqueness problems rather than
avoiding them, because it tends to force the OID counter to hover near
the high end of the range.

Comments?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: 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

On 8/8/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Uh, does the same thing happen if you *don't* cancel it?
>

Yes. In that case, it change the OID counter to the maximum OID in
the table if the OID counter was less than the maximum. This is only
a problem when the OID counter has wrapped because until then there
are no OID higher than the counter. I have verified it with a couple
of different tables different maximum OID; the counter went from 28
million, to 690 million, to 4286 million, to 4294 million.

> It looks to me like this could possibly happen due to CheckMaxObjectId()
> being applied to each OID found in the existing table.
>
> CheckMaxObjectId was always a kluge, and I'm not sure that it still has
> any redeeming social value at all. Can anyone think of a good reason
> to keep it?
>

From looking in the code, I am pretty sure CheckMaxObjectId is the
culprit. It sets the nextOID to the oid in the row if the
assigned_oid is greater than the nextOID.

We are using PostgreSQL 7.4.6 but it looks like the same code is in 8.0.3.

- Ian

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match