vacuum can't find clog

This is on a linux box, postgres 8.0.3 (yes, I know it's out of date but it was a test setup which seems to become
live and I have not had time to upgrade the thing) which runs a daily process to crunch some logs and do some
postGIS processing on them.

Tonight I got this message from my program:

Skipping the copy to disk
Done at Sat Jul  1 02:32:37 2006
Starting vacuum at Sat Jul  1 02:32:41 2006
Can't do the final vacuum and stats! ERROR:  could not access status of transaction 1081340514
SQL is <VACUUM ANALYZE;>

Running a vacuum verbose in the afflicted database got this complaint:
DETAIL:  0 dead row versions cannot be removed yet.
There were 19 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "pg_catalog.pg_operator"
INFO:  "pg_operator": scanned 14 of 14 pages, containing 651 live rows and 0 dead rows; 651 rows in sample,
651 estimated total rows
INFO:  vacuuming "public.weekly_log_data"
ERROR:  could not access status of transaction 1081340514
DETAIL:  could not open file "/data/postgres/psql8_test/pg_clog/0407": No such file or directory

Sure enough, there is no such beast:
[postgres <at> pogo cmnd_logs]$ ls -lt /data/postgres/psql8_test/pg_clog/
total 1716
-rw-------    1 postgres postgres   155648 Jul  1 02:36 0007
-rw-------    1 postgres postgres   262144 Jun 15 02:09 0006
-rw-------    1 postgres postgres   262144 Apr 12 05:27 0005

Re: vacuum can't find clog

"Gregory S. Williamson" <gsw <at> globexplorer.com> writes:
> INFO:  vacuuming "public.weekly_log_data"
> ERROR:  could not access status of transaction 1081340514
> DETAIL:  could not open file "/data/postgres/psql8_test/pg_clog/0407": No such file or directory

This is a data-corruption problem, ie, something has stomped on the xmin
or xmax field of a tuple.  (It may or may not be relevant that the high
order half of that number looks like ASCII "s <at> " ...)  Since xmin/xmax
are the first fields looked at that can be checked with any specificity,
"could not access status of transaction" is a common symptom even when
most of a page has been trashed 

I don't recall whether we've fixed any bugs post-8.0.3 that might
explain this.  My experience is that if you inquire in any detail
you tend to find symptoms that look more like OS- or hardware-level
problems than anything that looks like it could have come from a
Postgres bug ... for instance, I recall finding a block of mail-message
text smack in the middle of a Postgres file in one case.

If you care to investigate exactly what happened, you could break out
pg_filedump and other implements of destruction and see if you can
determine exactly what's bogus in that table.  You could probably
also recover whatever data hasn't been overwritten, but the extent
of damage is impossible to guess at this point.

Otherwise, you might just consider this a wake-up call to update your
Postgres and kernel, and maybe run some memory and disk testing.

			regards, tom lane

Re: reinstalling postgres on windows

Hi Dragan,

On Windows 2000/XP/2003, run lusrmgr.msc (or right-click My Computer, Manage
- then Local Users and Groups) - find the postgres account and delete it (or
right-click and Set Password to change it.)

Regards,

Andy

> -----Original Message-----
> From: pgsql-admin-owner <at> postgresql.org [mailto:pgsql-admin-
> owner <at> postgresql.org] On Behalf Of Dragan Matic
> Sent: 30 June 2006 7:47 am
> To: pgsql-admin-owner <at> postgresql.org
> Subject: [ADMIN] reinstalling postgres on windows
> 
> During the installation process installer creates postgresql account
> name needed for windows service start. I didn't give it a password and
> installer has put some random password for the account. After that I
> uninstalled postgres, and now when I want to install it again, I can't
> use postgres account name for service installation since I don't know
> the password. Where can I delete this postgres account?
> 
> Thanks in advance,
> 
> Dragan Matic
> 
> ---------------------------(end of broadcast)--------------------------
> -

FW: 2 instances

Re: FW: 2 instances

Re: FW: 2 instances

Wrap (obfuscate) code

Good afternoon.

I'm not sure if this is the correct list, so please forgive me if it's not.

I was wondering if there's a mechanism (or if not, a workaround) to
obfuscate server code (PL/PgSQL), a la Oracle's PL/SQL Wrap Utility:

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/c_wrap.htm#LNPLS016

Thanks for your time.

Regards,

Rodrigo

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo <at> postgresql.org so that your
       message can get through to the mailing list cleanly

Re: Wrap (obfuscate) code

On 7/4/06, Rodrigo De Leon <rdeleonp <at> gmail.com> wrote:
> I was wondering if there's a mechanism (or if not, a workaround) to
> obfuscate server code (PL/PgSQL), a la Oracle's PL/SQL Wrap Utility:

No, there is no such utility for PostgreSQL.  And, even if there were,
it wouldn't actually stop someone from reverse engineering it quite
easily as the source code to PL/pgSQL itself is readily available.

--

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 2nd Floor            | jharris <at> enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/

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

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

Re: Wrap (obfuscate) code

On 7/4/06, Jonah H. Harris <jonah.harris <at> gmail.com> wrote:
> No, there is no such utility for PostgreSQL.  And, even if there were,
> it wouldn't actually stop someone from reverse engineering it quite
> easily as the source code to PL/pgSQL itself is readily available.

I see. Thanks for the reply, both to you and Mr. Momjian.

Regards,

Rodrigo

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

space not reclaimed after repeated full vacuums