Thread: Can I adjust the oid counter for testing?

Hi

I need to do some testing of pgAdmin on a database with very large oids
(> 4,000,000,000). Is there anyway I can wind the oid counter forward
without having to do a few billion inserts?

I'm on a test system so I can initdb if required.

Thanks, Dave.

"Dave Page" <dpage@vale-housing.co.uk> writes:
> I need to do some testing of pgAdmin on a database with very large oids
> (> 4,000,000,000). Is there anyway I can wind the oid counter forward
> without having to do a few billion inserts?
> I'm on a test system so I can initdb if required.

A clean solution would be to extend pg_resetxlog to have a switch to set
nextOid, parallel to its switch to tweak nextXid.  (I had thought we had
this already, actually, but I'm not seeing it in current sources.)

The difficulty with that, if you are using current CVS, is that I
believe pg_resetxlog is broken at the moment --- Thomas changed the
format of pg_control recently and didn't update pg_resetxlog.

If you want to fix both of those things and submit a patch, it'd save me
some work that needs to get done before 7.3 can go out.

If that all seems like too much work, you could just reach in with a
debugger and set ShmemVariableCache->nextOid in a running system
(be careful that nothing is going on while you do so).  Better set
ShmemVariableCache->oidCount = 0 while you're at it.
        regards, tom lane

Tom Lane wrote:
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > I need to do some testing of pgAdmin on a database with very large oids
> > (> 4,000,000,000). Is there anyway I can wind the oid counter forward
> > without having to do a few billion inserts?
> > I'm on a test system so I can initdb if required.
> 
> A clean solution would be to extend pg_resetxlog to have a switch to set
> nextOid, parallel to its switch to tweak nextXid.  (I had thought we had
> this already, actually, but I'm not seeing it in current sources.)

Yes, I thought we had that too, but I don't see it.

Actually, you can just use COPY WITH OIDS and insert a large oid.  That
will set the counter.  That's how pg_dump does it:CREATE TEMPORARY TABLE pgdump_oid (dummy integer);COPY pgdump_oid
WITHOIDS FROM stdin;143655  0\.
 

This method will only _increase_ the oid.

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026
 

Thanks Tom,

I suspect hacking pg_resetxlog is beyond my capabilities right now, but
if I do get anything I'll post a patch. I'll probably end up using
gdb...

Regards, Dave.

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 17 June 2002 15:00
> To: Dave Page
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Can I adjust the oid counter for testing?
>
>
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > I need to do some testing of pgAdmin on a database with very large
> > oids (> 4,000,000,000). Is there anyway I can wind the oid counter
> > forward without having to do a few billion inserts? I'm on a test
> > system so I can initdb if required.
>
> A clean solution would be to extend pg_resetxlog to have a
> switch to set nextOid, parallel to its switch to tweak
> nextXid.  (I had thought we had this already, actually, but
> I'm not seeing it in current sources.)
>
> The difficulty with that, if you are using current CVS, is
> that I believe pg_resetxlog is broken at the moment ---
> Thomas changed the format of pg_control recently and didn't
> update pg_resetxlog.
>
> If you want to fix both of those things and submit a patch,
> it'd save me some work that needs to get done before 7.3 can go out.
>
> If that all seems like too much work, you could just reach in
> with a debugger and set ShmemVariableCache->nextOid in a
> running system (be careful that nothing is going on while you
> do so).  Better set
> ShmemVariableCache->oidCount = 0 while you're at it.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>