pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Error on CLUSTER command
7+ messages / 3 participants
[nested] [flat]

* Error on CLUSTER command
@ 2008-02-08 15:59 Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Mark Steben @ 2008-02-08 15:59 UTC (permalink / raw)
  To: pgsql-admin

Hello listers,

 

I am getting the following error when I attempt to CLUSTER on a table:

 

 mavmail=# cluster mailer_queue;

ERROR:  could not create relation "pg_temp_752992924": File exists

 

The 752992924 is the oid of one of the indexes of this table (not the
clustering index)

 

This apparently is the result of a previous cluster operation that had not
cleaned up properly.

Where can I find this file?  There are several tables that have this
problem.  Do I need to

Do a database REINDEX or some other type of system maintenance?

 

Again we are running 7.4.5.  8.2.5 scheduled for implementation later this
month or early March.

 

Mark Steben

Senior Database Administrator
@utoRevenueT 
A Dominion Enterprises Company
480 Pleasant Street
Suite B200
Lee, MA 01238
413-243-4800 Home Office 
413-243-4809 Corporate Fax

msteben <blocked::mailto:name@autorevenue.com> @autorevenue.com

Visit our new website at 
 <blocked::http://www.autorevenue.com/; www.autorevenue.com

 

IMPORTANT: The information contained in this e-mail message is confidential
and is intended only for the named addressee(s). If the reader of this
e-mail message is not the intended recipient (or the individual responsible
for the delivery of this e-mail message to the intended recipient), please
be advised that any re-use, dissemination, distribution or copying of this
e-mail message is prohibited.  If you have received this e-mail message in
error, please reply to the sender that you have received this e-mail message
in error and then delete it.  Thank you.

Come Visit Us at NADA!  Booth #5735N 
February 9th - February 12th 
Moscone Center in San Francisco, CA 

 

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
@ 2008-02-08 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 16:25   ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2008-02-08 16:19 UTC (permalink / raw)
  To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin

"Mark Steben" <msteben@autorevenue.com> writes:
> I am getting the following error when I attempt to CLUSTER on a table:
>  mavmail=# cluster mailer_queue;
> ERROR:  could not create relation "pg_temp_752992924": File exists

If you repeat the command, does the error persist?  Does the number
stay the same?

> Again we are running 7.4.5.

Tsk tsk.  Why not 7.4.19, or at least some reasonably recent 7.4.x
update?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-08 16:25   ` Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:35     ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: Mark Steben @ 2008-02-08 16:25 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

The number may change pointing to another index or stay the same.
The error persists if I repeat 3 or 4 times.  Usually after the
4th or 5th time the cluster is successful.

What is the risk of performing an upgrade to 7.4.19?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Friday, February 08, 2008 11:19 AM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Error on CLUSTER command 

"Mark Steben" <msteben@autorevenue.com> writes:
> I am getting the following error when I attempt to CLUSTER on a table:
>  mavmail=# cluster mailer_queue;
> ERROR:  could not create relation "pg_temp_752992924": File exists

If you repeat the command, does the error persist?  Does the number
stay the same?

> Again we are running 7.4.5.

Tsk tsk.  Why not 7.4.19, or at least some reasonably recent 7.4.x
update?

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 16:25   ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
@ 2008-02-08 16:35     ` Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 19:37       ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2008-02-08 16:35 UTC (permalink / raw)
  To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin

"Mark Steben" <msteben@autorevenue.com> writes:
> The number may change pointing to another index or stay the same.
> The error persists if I repeat 3 or 4 times.  Usually after the
> 4th or 5th time the cluster is successful.

Hm.  I'd guess that the installation is old enough that the OID counter
has wrapped around, and is currently in a region that is pretty densely
populated with existing tables.  (You could check that out by looking
into the database directory.)  7.4.x doesn't have the logic that was
added later to skip over filenames that are already in use; instead
you just get an error if there's a collision.

I forget whether 7.4's pg_resetxlog has an option to set the OID
counter, but if it does then a temporary fix is to move the OID counter
to some less densely populated range.

> What is the risk of performing an upgrade to 7.4.19?

Low, but it won't make this particular problem go away.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 16:25   ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:35     ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-02-08 19:37       ` Mark Steben <msteben@autorevenue.com>
  2008-02-08 19:49         ` Re: Error on CLUSTER command Scott Marlowe <scott.marlowe@gmail.com>
  2008-02-08 20:56         ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: Mark Steben @ 2008-02-08 19:37 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin

The doc says to do an initdb and reload the data whenever you run
pg_resetxlog.  Do you concur with that even if we are only resetting the OID
counter?

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
Sent: Friday, February 08, 2008 11:36 AM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Error on CLUSTER command 

"Mark Steben" <msteben@autorevenue.com> writes:
> The number may change pointing to another index or stay the same.
> The error persists if I repeat 3 or 4 times.  Usually after the
> 4th or 5th time the cluster is successful.

Hm.  I'd guess that the installation is old enough that the OID counter
has wrapped around, and is currently in a region that is pretty densely
populated with existing tables.  (You could check that out by looking
into the database directory.)  7.4.x doesn't have the logic that was
added later to skip over filenames that are already in use; instead
you just get an error if there's a collision.

I forget whether 7.4's pg_resetxlog has an option to set the OID
counter, but if it does then a temporary fix is to move the OID counter
to some less densely populated range.

> What is the risk of performing an upgrade to 7.4.19?

Low, but it won't make this particular problem go away.

			regards, tom lane




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 16:25   ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:35     ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 19:37       ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
@ 2008-02-08 19:49         ` Scott Marlowe <scott.marlowe@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Scott Marlowe @ 2008-02-08 19:49 UTC (permalink / raw)
  To: Mark Steben <msteben@autorevenue.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin

On Feb 8, 2008 1:37 PM, Mark Steben <msteben@autorevenue.com> wrote:
> The doc says to do an initdb and reload the data whenever you run
> pg_resetxlog.  Do you concur with that even if we are only resetting the OID
> counter?

On 7.4 I'd say you'd be better off dumping and reloading all your data
than playing with pg_resetxlog on a live database.  Might as well
update to 7.4.19 while you're at it.

I'd run a dump restore to a test machine running 7.4.19 first to make
sure it will go well, or get another server that can handle the load,
or make a new 7.4.19 instance on the same machine and transfer to
that.  But if it's a production database I wouldn't run pg_resetxlog
on it myself.



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Error on CLUSTER command
  2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:19 ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 16:25   ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
  2008-02-08 16:35     ` Re: Error on CLUSTER command Tom Lane <tgl@sss.pgh.pa.us>
  2008-02-08 19:37       ` Re: Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
@ 2008-02-08 20:56         ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 7+ messages in thread

From: Tom Lane @ 2008-02-08 20:56 UTC (permalink / raw)
  To: Mark Steben <msteben@autorevenue.com>; +Cc: pgsql-admin

"Mark Steben" <msteben@autorevenue.com> writes:
> The doc says to do an initdb and reload the data whenever you run
> pg_resetxlog.  Do you concur with that even if we are only resetting the OID
> counter?

The docs are thinking of the case where you are trying to recover from
database corruption.  If you just use it to change the OID counter after
a clean shutdown, I don't see a problem.

Not that it wouldn't be a good idea to take a backup first, just
in case...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2008-02-08 20:56 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-08 15:59 Error on CLUSTER command Mark Steben <msteben@autorevenue.com>
2008-02-08 16:19 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-08 16:25   ` Mark Steben <msteben@autorevenue.com>
2008-02-08 16:35     ` Tom Lane <tgl@sss.pgh.pa.us>
2008-02-08 19:37       ` Mark Steben <msteben@autorevenue.com>
2008-02-08 19:49         ` Scott Marlowe <scott.marlowe@gmail.com>
2008-02-08 20:56         ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox