pg_migrator progress

You've moved fast on this!

#ifdef NOT_USED
/* XXX do we need this at all? */
/*
* Assuming OIDs are only used in system tables, there is no need to
* restore the OID counter because we have not transfered any OIDs
* from the old system.
*/
prepStatus(&ctx, "Setting next oid for new cluster");
exec_prog(&ctx, true, "%s/pg_resetxlog -o %u %s 1>/dev/null",
ctx.m_newbindir, ctx.m_oldctrldata.chkpnt_nxtoid, ctx.m_newpgdata);
check_ok(&ctx);
#endif

It's certainly not impossible for someone to be using OIDs on user
tables, is it?

I mean, I'm not, but...

...Robert

Bruce Momjian wrote:

I have completed all the outstanding pg_migratory TODO items.

I still have more work to do in cleanup and testing, but if people want
to look at my progress, now is a good time.

Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're
going to mess with pg_clog?

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Robert Haas <robertmhaas@gmail.com> writes:

/* XXX do we need this at all? */
/*
* Assuming OIDs are only used in system tables, there is no need to
* restore the OID counter because we have not transfered any OIDs
* from the old system.
*/

It's certainly not impossible for someone to be using OIDs on user
tables, is it?

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

Well it was a time bomb but it wasn't necessarily about to go off... He may
very well know how close or far he is from oid wraparound and have contingency
plans in place.

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Gregory Stark <stark@enterprisedb.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*. In the second place, if he does have such runs, the claim that
he can't possibly have dealt with OID wraparound before seems pretty
untenable --- he's obviously been eating lots of OIDs.

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Gregory Stark <stark@enterprisedb.com> writes:

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*.

Really? Wouldn't all it take be a single large COPY loading data into a table
with one or more columns receiving large data which need to be toasted?

In the second place, if he does have such runs, the claim that he can't
possibly have dealt with OID wraparound before seems pretty untenable ---
he's obviously been eating lots of OIDs.

Well there's a pretty wide margin between millions and 4 billion. I suppose
you could say it would only be a one-time cost (or a few separate one-time
costs until the oid counter passed the old value). So a few minutes after
doing an in-place upgrade while the oid counter skimmed past all the existing
values would be bearable.

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

Yeah, if it was massive amounts of code I could see arguing that it's not
justified, but given that it's about the same degree of complexity either way
it seems clear to me that it's better to do it than not to do it.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

On Wednesday 18 February 2009 10:47:25 Tom Lane wrote:

Gregory Stark <stark@enterprisedb.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*. In the second place, if he does have such runs, the claim that
he can't possibly have dealt with OID wraparound before seems pretty
untenable --- he's obviously been eating lots of OIDs.

Yeah, but its not just lots... it's lots and lots of lots. Sure, I have
multi-billion row _tables_ now, but I know I ran systems for years "back in
the day" when we used oids in user tables, and they never made it to oid
wraparound terratory, because they just didn't churn through that much data.

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

+1 for doing this, otherwise we need some strong warnings in the migrator docs
about this case imho.

--
Robert Treat
Conjecture: http://www.xzilla.net
Consulting: http://www.omniti.com

Alvaro Herrera wrote:

Bruce Momjian wrote:

I have completed all the outstanding pg_migratory TODO items.

I still have more work to do in cleanup and testing, but if people want
to look at my progress, now is a good time.

Hmm, don't you need to change the Xid counter (pg_resetxlog) if you're
going to mess with pg_clog?

Yes, that happens in copy_clog_xlog_xid().

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Robert Treat wrote:

On Wednesday 18 February 2009 10:47:25 Tom Lane wrote:

Gregory Stark <stark@enterprisedb.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*. In the second place, if he does have such runs, the claim that
he can't possibly have dealt with OID wraparound before seems pretty
untenable --- he's obviously been eating lots of OIDs.

Yeah, but its not just lots... it's lots and lots of lots. Sure, I have
multi-billion row _tables_ now, but I know I ran systems for years "back in
the day" when we used oids in user tables, and they never made it to oid
wraparound terratory, because they just didn't churn through that much data.

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

+1 for doing this, otherwise we need some strong warnings in the migrator docs
about this case imho.

One compromise is outputting the pg_resetxlog command to the terminal,
and suggesting they run it only if they need to.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

One compromise is outputting the pg_resetxlog command to the terminal,
and suggesting they run it only if they need to.

Er, what? pg_resetxlog is certainly not optional in this process.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

One compromise is outputting the pg_resetxlog command to the terminal,
and suggesting they run it only if they need to.

Er, what? pg_resetxlog is certainly not optional in this process.

The oid setting part is.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

Er, what? pg_resetxlog is certainly not optional in this process.

The oid setting part is.

Yeah, but if you have to run it anyway it certainly isn't going to be
any more work to make it set the OID counter too.

regards, tom lane

Gregory Stark wrote:

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

Yeah, if it was massive amounts of code I could see arguing that it's not
justified, but given that it's about the same degree of complexity either way
it seems clear to me that it's better to do it than not to do it.

Agreed, done.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Tom Lane wrote:

Gregory Stark <stark@enterprisedb.com> writes:

Tom Lane <tgl@sss.pgh.pa.us> writes:

No, but this would just be the same situation that prevails after
OID-counter wraparound, so I don't see a compelling need for us to
change the OID counter in the new DB. If the user has done the Proper
Things (ie, made unique indexes on his OIDs) then it won't matter.
If he didn't, his old DB was a time bomb anyway.

Also I wonder about the performance of skipping over thousands or even
millions of OIDs for something like a toast table.

I think that argument is a red herring. In the first place, it's
unlikely that there'd be a huge run of consecutive OIDs *in the same
table*. In the second place, if he does have such runs, the claim that
he can't possibly have dealt with OID wraparound before seems pretty
untenable --- he's obviously been eating lots of OIDs.

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

FYI, I decided against restoring the oid counter because it might
collide with an oid assigned during pg_migrator schema creation.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

FYI, I decided against restoring the oid counter because it might
collide with an oid assigned during pg_migrator schema creation.

That argument seems pretty empty --- why is it more likely than a
collision happening if you *don't* restore the oid counter? And why
would it matter anyway? We fixed the problems with oid collisions
some time ago.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

But having said that, there isn't any real harm in fixing the OID
counter to match what it was. You need to run pg_resetxlog to set the
WAL position and XID counter anyway, and it can set the OID counter too.

FYI, I decided against restoring the oid counter because it might
collide with an oid assigned during pg_migrator schema creation.

That argument seems pretty empty --- why is it more likely than a
collision happening if you *don't* restore the oid counter? And why

We don't transfer any system-level oids so there is no chance of a
collision against system tables.

would it matter anyway? We fixed the problems with oid collisions
some time ago.

Oh, we handled that; OK, old code restored.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +