Now I know that you all believe that postgres only has problems due to
bad programming, but I'm getting another problem that I can't figure out
in 6.5.3

[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: db_geocrawler

db_geocrawler=> vacuum analyze;
ERROR:  cannot find attribute 1 of relation pg_attrdef


This is causing geocrawler.com to be totally fubar at this point.

Any ideas? Do I have to recover from the last backup?

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Sun, 09 Jul 2000 19:26:57 -0700
Tim Perdue <tperdue@valinux.com>
more corruption
On Sun, 9 Jul 2000, Tim Perdue wrote:

> Now I know that you all believe that postgres only has problems due to
> bad programming, but I'm getting another problem that I can't figure out
> in 6.5.3
> 
> [PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
> 
>    type \? for help on slash commands
>    type \q to quit
>    type \g or terminate with semicolon to execute query
>  You are currently connected to the database: db_geocrawler
> 
> db_geocrawler=> vacuum analyze;
> ERROR:  cannot find attribute 1 of relation pg_attrdef
> 
> 
> This is causing geocrawler.com to be totally fubar at this point.
> 
> Any ideas? Do I have to recover from the last backup?

just a quick thought ... have you tried shutting down and restrating the
postmaster?  basically, "reset" the shared memory?  v7.x handles
corruptions like that alot cleaner, but previous versions caused odd
results if shared memory got corrupted ...



Mon, 10 Jul 2000 00:13:13 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: more corruption
The Hermit Hacker wrote:
> just a quick thought ... have you tried shutting down and restrating the
> postmaster?  basically, "reset" the shared memory?  v7.x handles
> corruptions like that alot cleaner, but previous versions caused odd
> results if shared memory got corrupted ...

Well, I've rebooted twice. In fact, it was a hard lock that caused the
problems. When the machine was brought back up, the db was foobar.

I'm doing something really really evil to avoid losing the last days'
data:

-I created a new db
-used the old db schema to create all new blank tables
-copied the physical table files from the old data directory into the
new database directory
-currently vacuuming the new db - nothing is barfing yet
-now hopefully I can create my indexes and be back in business

Tim

-- 
Founder - PHPBuilder.com / Geocrawler.com
Lead Developer - SourceForge
VA Linux Systems
408-542-5723

Sun, 09 Jul 2000 20:26:33 -0700
Tim Perdue <tperdue@valinux.com>
Re: more corruption
You have recreated what pg_upgrade does.  It is for upgrading system
tables.  If only your system tables were hosed, you are fine now.


> The Hermit Hacker wrote:
> > just a quick thought ... have you tried shutting down and restrating the
> > postmaster?  basically, "reset" the shared memory?  v7.x handles
> > corruptions like that alot cleaner, but previous versions caused odd
> > results if shared memory got corrupted ...
> 
> Well, I've rebooted twice. In fact, it was a hard lock that caused the
> problems. When the machine was brought back up, the db was foobar.
> 
> I'm doing something really really evil to avoid losing the last days'
> data:
> 
> -I created a new db
> -used the old db schema to create all new blank tables
> -copied the physical table files from the old data directory into the
> new database directory
> -currently vacuuming the new db - nothing is barfing yet
> -now hopefully I can create my indexes and be back in business
> 
> Tim
> 
> -- 
> Founder - PHPBuilder.com / Geocrawler.com
> Lead Developer - SourceForge
> VA Linux Systems
> 408-542-5723
> 


-- 
  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, Pennsylvania 19026

Sun, 9 Jul 2000 23:36:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: more corruption
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> You have recreated what pg_upgrade does.  It is for upgrading system
> tables.  If only your system tables were hosed, you are fine now.

Er, not unless he did exactly the right fancy footwork with pg_log and
vacuum.  Or have you forgotten how tricky it was to get pg_upgrade to
work reliably?

			regards, tom lane

Mon, 10 Jul 2000 01:14:37 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: more corruption 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > You have recreated what pg_upgrade does.  It is for upgrading system
> > tables.  If only your system tables were hosed, you are fine now.
> 
> Er, not unless he did exactly the right fancy footwork with pg_log and
> vacuum.  Or have you forgotten how tricky it was to get pg_upgrade to
> work reliably?

Yes, I had forgotten.  The new table file names will make pg_upgrade
useless in the future.

-- 
  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, Pennsylvania 19026

Mon, 10 Jul 2000 09:11:48 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: more corruption
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Yes, I had forgotten.  The new table file names will make pg_upgrade
> useless in the future.

Hmm ... that's an implication I hadn't thought about.  I wonder how much
work it would be to get pg_upgrade to rename table files.  Be a shame to
throw pg_upgrade away after all the sweat we put into making it work ;-)

			regards, tom lane

Mon, 10 Jul 2000 10:32:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: more corruption 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, I had forgotten.  The new table file names will make pg_upgrade
> > useless in the future.
> 
> Hmm ... that's an implication I hadn't thought about.  I wonder how much
> work it would be to get pg_upgrade to rename table files.  Be a shame to
> throw pg_upgrade away after all the sweat we put into making it work ;-)

Seems impossible.  The physical file names are not dumped by pg_dump, so
there is really no way to re-assocate the files with the table names. 
Looks like a lost cause.

-- 
  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, Pennsylvania 19026

Mon, 10 Jul 2000 14:57:11 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: more corruption
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Yes, I had forgotten.  The new table file names will make pg_upgrade
> > useless in the future.
> 
> Hmm ... that's an implication I hadn't thought about.  I wonder how much
> work it would be to get pg_upgrade to rename table files.  Be a shame to
> throw pg_upgrade away after all the sweat we put into making it work ;-)

I guess we could throw the physical file into a comment, and somehow
read that in pg_upgrade, but it seems too error-prone.  I am sure Vadim
will come up with something to break pg_upgrade soon anyway.  It is a
nifty feature while we have it.


-- 
  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, Pennsylvania 19026

Mon, 10 Jul 2000 14:58:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: more corruption
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>>> Yes, I had forgotten.  The new table file names will make pg_upgrade
>>>> useless in the future.
>> 
>> Hmm ... that's an implication I hadn't thought about.  I wonder how much
>> work it would be to get pg_upgrade to rename table files.  Be a shame to
>> throw pg_upgrade away after all the sweat we put into making it work ;-)

> Seems impossible.  The physical file names are not dumped by pg_dump, so
> there is really no way to re-assocate the files with the table names. 
> Looks like a lost cause.

Well, we'd need to modify the pg_dump format so that the OIDs of the
tables are recorded, but given that it doesn't seem impossible.

I suppose tablespaces might complicate the situation to the point where
it wasn't worth the trouble, though.

Given Vadim's plans for WAL and smgr changes, at least the next two
version updates likely won't be updatable with pg_upgrade anyway.
However, we've seen a couple of times recently when pg_upgrade was
useful as a recovery tool for system-table corruption, and that's why
I'm unhappy about the prospect of just discarding it...

			regards, tom lane

Mon, 10 Jul 2000 18:08:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: more corruption 
At 18:08 10/07/00 -0400, Tom Lane wrote:
>
>Well, we'd need to modify the pg_dump format so that the OIDs of the
>tables are recorded, but given that it doesn't seem impossible.

Already are (in text it's in the comments, and in the other formats it's
part of the data in the TOC.


----------------------------------------------------------------
Philip Warner                    |     __---_____
Albatross Consulting Pty. Ltd.   |----/       -  \
(A.C.N. 008 659 498)             |          /(@)   ______---_
Tel: (+61) 0500 83 82 81         |                 _________  \
Fax: (+61) 0500 83 82 82         |                 ___________ |
Http://www.rhyme.com.au          |                /           \|
                                 |    --________--
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/

Tue, 11 Jul 2000 08:28:18 +1000
Philip Warner <pjw@rhyme.com.au>
Re: more corruption 
> Given Vadim's plans for WAL and smgr changes, at least the next two
> version updates likely won't be updatable with pg_upgrade anyway.
> However, we've seen a couple of times recently when pg_upgrade was
> useful as a recovery tool for system-table corruption, and that's why
> I'm unhappy about the prospect of just discarding it...

Agreed.  I can see some cases where several types of recovery will be
harder in the new system.

-- 
  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, Pennsylvania 19026

Mon, 10 Jul 2000 18:38:35 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: more corruption