Okay, here's a follow up to my previous messages "ACK table corrupted,
unique index violated."

I've been trying to clean up the corruptions that i mentioned earlier.  I
felt most comfortable shutting down all my application servers, restarting
postgres, doing a dump of my database and rebuilding it with a pginit and
complete reload.  So far so good.  I went to fix one of the corrupted tables
and i have another strange experience.  I'm still looking into other
possibilities such as a hardware failure; but i thought this might be
interesting or helpful in the context of my previous post:  Basically the
table with duplicate oid/id now has unique oid from the relead, so I'm going
to delete the duplicate rows and recreate the unique index on the identity
column.

basement=# select count(*),developer_aka_id from developer_aka group by
developer_aka_id having count(*) <> 1;
 count | developer_aka_id
-------+------------------
     2 |             9789
     2 |            10025
     2 |            40869
(3 rows)

basement=# select oid,* from developer_aka where developer_aka_id in
(9789,10025,40869);
  oid  | developer_id | developer_aka_id |    first_name     | last_name
-------+--------------+------------------+-------------------+-----------
 48390 |         1916 |             9789 | Chris             | Smith
 48402 |        35682 |            40869 | Donald "Squirral" | Fisk
 48425 |         4209 |            10025 | Mike              | Glosecki
 48426 |         1916 |             9789 | Chris             | Smith
 48427 |        35682 |            40869 | Donald "Squirral" | Fisk
 48428 |         4209 |            10025 | Mike              | Glosecki
(6 rows)

basement=# delete from developer_aka where oid in (48390,48402,48425);
DELETE 3
basement=# select count(*),developer_aka_id from developer_aka group by
developer_aka_id having count(*) <> 1;
 count | developer_aka_id
-------+------------------
(0 rows)

basement=# create unique index developer_aka_pkey on
developer_aka(developer_aka_id);
CREATE
basement=# VACUUM ANALYZE developer_aka;
ERROR:  Cannot insert a duplicate key into unique index developer_aka_pkey



Wed, 12 Dec 2001 11:30:47 -0700
"Brian Hirt" <bhirt@mobygames.com>
problems with table corruption continued
Tom & All:

I've been looking into this problem some more and I've been able to
consistantly reproduce the error.  I've testing it on two different
machines; both running 7.1.3 on a i686-pc-linux-gnu configuration.  One
machine is RH7.2 and the other is RH6.2.

I still haven't been able to reproduce the problem with corrupted
indexes/tables (NUMBER OF TUPLES IS NOT THE SAME AS HEAP -- duplicate rows
with same oid & pkey); but I'm hopefull that these two problems are related.
Also, i wanted to inform you that the same two tables became corrupted on
12-15-2001 after my 12-12-2001 reload).

I've attached three files, a typescript, and two sql files.  I found that if
the commands were combined into a single file and run in a single pgsql
session, the error does not occur -- so it's important to follow the
commands exactly like they are in the typescript.  If for some reason the
errors aren't reproducable on your machines, let me know and we will try to
find out what's unique about my setup.

Thanks.

----- Original Message -----
From: "Brian Hirt" <bhirt@mobygames.com>
To: "Postgres Hackers" <pgsql-hackers@postgresql.org>
Cc: "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Wednesday, December 12, 2001 11:30 AM
Subject: [HACKERS] problems with table corruption continued


> Okay, here's a follow up to my previous messages "ACK table corrupted,
> unique index violated."
>
> I've been trying to clean up the corruptions that i mentioned earlier.  I
> felt most comfortable shutting down all my application servers, restarting
> postgres, doing a dump of my database and rebuilding it with a pginit and
> complete reload.  So far so good.  I went to fix one of the corrupted
tables
> and i have another strange experience.  I'm still looking into other
> possibilities such as a hardware failure; but i thought this might be
> interesting or helpful in the context of my previous post:  Basically the
> table with duplicate oid/id now has unique oid from the relead, so I'm
going
> to delete the duplicate rows and recreate the unique index on the identity
> column.
>
> basement=# select count(*),developer_aka_id from developer_aka group by
> developer_aka_id having count(*) <> 1;
>  count | developer_aka_id
> -------+------------------
>      2 |             9789
>      2 |            10025
>      2 |            40869
> (3 rows)
>
> basement=# select oid,* from developer_aka where developer_aka_id in
> (9789,10025,40869);
>   oid  | developer_id | developer_aka_id |    first_name     | last_name
> -------+--------------+------------------+-------------------+-----------
>  48390 |         1916 |             9789 | Chris             | Smith
>  48402 |        35682 |            40869 | Donald "Squirral" | Fisk
>  48425 |         4209 |            10025 | Mike              | Glosecki
>  48426 |         1916 |             9789 | Chris             | Smith
>  48427 |        35682 |            40869 | Donald "Squirral" | Fisk
>  48428 |         4209 |            10025 | Mike              | Glosecki
> (6 rows)
>
> basement=# delete from developer_aka where oid in (48390,48402,48425);
> DELETE 3
> basement=# select count(*),developer_aka_id from developer_aka group by
> developer_aka_id having count(*) <> 1;
>  count | developer_aka_id
> -------+------------------
> (0 rows)
>
> basement=# create unique index developer_aka_pkey on
> developer_aka(developer_aka_id);
> CREATE
> basement=# VACUUM ANALYZE developer_aka;
> ERROR:  Cannot insert a duplicate key into unique index developer_aka_pkey
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Tue, 18 Dec 2001 08:53:59 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued
Great,  I'm also trying to create a reproducable test case for the original
problem i reported with duplicate rows/oids/pkeys.  Maybe both problems are
a result of the same bug; i don't know.

BTW, if you remove the index that is based on the pgpsql function
"developer_aka_seach_idx" the problem is not reproducable.  But you've
probably figured that out by now.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>; "Brian A Hirt"
<bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 10:52 AM
Subject: Re: [HACKERS] problems with table corruption continued


> "Brian Hirt" <bhirt@mobygames.com> writes:
> > I've attached three files, a typescript, and two sql files.  I found
that if
> > the commands were combined into a single file and run in a single pgsql
> > session, the error does not occur -- so it's important to follow the
> > commands exactly like they are in the typescript.  If for some reason
the
> > errors aren't reproducable on your machines, let me know and we will try
to
> > find out what's unique about my setup.
>
> Indeed, it reproduces just fine here --- not only that, but I get an
> Assert failure when I try it in current sources.
>
> Many, many thanks!  I shall start digging forthwith...
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>


Tue, 18 Dec 2001 09:49:20 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued 
Tom,

I'm glad you found the cause.  I already deleted the index a few days back
after I strongly suspected that they were related to the problem.  The
reason i created the index was to help locate a record based on the name of
a person with an index lookup instead of a sequential scan on a table with
several hundred thousand rows.

I was trying to avoid adding additional computed fields to the tables and
maintaining them with triggers, indexing and searching on them.  The index
function seemed like an elegant solution to the problem; although at the
time I was completely unaware of 'iscachable';

Do you think this might also explain the following errors i was seeing?

NOTICE:  Cannot rename init file
/moby/pgsql/base/156130/pg_internal.init.19833 to
/moby/pgsql/base/156130/pg_internal.init: No such file or directory

--brian

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Vadim Mikheev" <vmikheev@sectorbase.com>; "Postgres Hackers"
<pgsql-hackers@postgresql.org>; "Brian A Hirt" <bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 11:48 AM
Subject: Re: [HACKERS] problems with table corruption continued


> "Brian Hirt" <bhirt@mobygames.com> writes:
> > [ example case that creates a duplicate tuple ]
>
> Got it.  The problem arises in this section of vacuum.c, near the bottom
> of repair_frag:
>
>                 if (!(tuple.t_data->t_infomask & HEAP_XMIN_COMMITTED))
>                 {
>                     if ((TransactionId) tuple.t_data->t_cmin != myXID)
>                         elog(ERROR, "Invalid XID in t_cmin (3)");
>                     if (tuple.t_data->t_infomask & HEAP_MOVED_OFF)
>                     {
>                         itemid->lp_flags &= ~LP_USED;
>                         num_tuples++;
>                     }
>                     else
>                         elog(ERROR, "HEAP_MOVED_OFF was expected (2)");
>                 }
>
> This is trying to get rid of the original copy of a tuple that's been
> moved to another page.  The problem is that your index function causes a
> table scan, which means that by the time control gets here, someone else
> has looked at this tuple and marked it good --- so the initial test of
> HEAP_XMIN_COMMITTED fails, and the tuple is never removed!
>
> I would say that it's incorrect for vacuum.c to assume that
> HEAP_XMIN_COMMITTED can't become set on HEAP_MOVED_OFF/HEAP_MOVED_IN
> tuples during the course of vacuum's processing; after all, the xmin
> definitely does refer to a committed xact, and we can't realistically
> assume that we know what processing will be induced by user-defined
> index functions.  Vadim, what do you think?  How should we fix this?
>
> In the meantime, Brian, I think you ought to get rid of your index
> CREATE  INDEX "developer_aka_search_idx" on "developer_aka" using btree
( developer_aka_search_name ("developer_aka_id") "varchar_ops" );
> I do not think this index is well-defined: an index function ought
> to have the property that its output depends solely on its input and
> cannot change over time.  This function cannot make that claim.
> (In 7.2, you can't even create the index unless you mark the function
> iscachable, which is really a lie.)  I'm not even real sure what you
> expect the index to do for you...
>
> I do not know whether this effect explains all the reports of duplicate
> tuples we've seen in the last few weeks.  We need to ask whether the
> other complainants were using index functions that tried to do table
> scans.
>
> regards, tom lane
>


Tue, 18 Dec 2001 10:35:22 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued 
Yes, both tables had similiar functions, and the corruption was limited to
only those two tables.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>; "Brian A Hirt"
<bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 11:53 AM
Subject: Re: [HACKERS] problems with table corruption continued


> "Brian Hirt" <bhirt@mobygames.com> writes:
> > Great,  I'm also trying to create a reproducable test case for the
original
> > problem i reported with duplicate rows/oids/pkeys.  Maybe both problems
are
> > a result of the same bug; i don't know.
>
> Were the duplicate rows all in tables that had functional indexes based
> on functions similar to developer_aka_search_name?  The problem we're
> seeing here seems to be due to VACUUM not being able to cope with the
> side effects of the SELECT inside the index function.
>
> regards, tom lane
>


Tue, 18 Dec 2001 10:36:28 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued 
"Brian Hirt" <bhirt@mobygames.com> writes:
> I've attached three files, a typescript, and two sql files.  I found that if
> the commands were combined into a single file and run in a single pgsql
> session, the error does not occur -- so it's important to follow the
> commands exactly like they are in the typescript.  If for some reason the
> errors aren't reproducable on your machines, let me know and we will try to
> find out what's unique about my setup.

Indeed, it reproduces just fine here --- not only that, but I get an
Assert failure when I try it in current sources.

Many, many thanks!  I shall start digging forthwith...

			regards, tom lane

Tue, 18 Dec 2001 12:52:28 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problems with table corruption continued 
Tom,

I probably could have written the function like you suggest in this email,
and i probably will look into doing so.   This was the first time I tried
creating an index function, and one of the few times i've used plpgsql.  In
general i have very little experience with doing this kind of stuff in
postgres (i try to stick to standard SQL as much as possible) and it looks
like i've stumbled onto this problem because of a bad design decision on my
part and a lack of understanding of how index functions work.

Thanks for the suggestion.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>; "Brian A Hirt"
<bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 12:20 PM
Subject: Re: [HACKERS] problems with table corruption continued


> "Brian Hirt" <bhirt@mobygames.com> writes:
> > I was trying to avoid adding additional computed fields to the tables
and
> > maintaining them with triggers, indexing and searching on them.  The
index
> > function seemed like an elegant solution to the problem
>
> Understood, but can you write the index function in a way that avoids
> having it do a SELECT to get at data that it hasn't been passed?  I'm
> wondering if you can't define the function as just
> f(first_name, last_name) = upper(first_name || ' ' || last_name)
> and create the index on f(first_name, last_name).  You haven't shown us
> the queries you expect the index to be helpful for, so maybe this is not
> workable...
>
> regards, tom lane
>


Tue, 18 Dec 2001 10:58:24 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued 
Well, when my application starts, about 100 backend database connections
start up at the same time so this fits in with the circumstance you
describe.  However, I'm just using a standard ext2 filesystem on 2.2 linux
kernel.

It's good to know that this error should be harmless.

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Brian Hirt" <bhirt@mobygames.com>
Cc: "Postgres Hackers" <pgsql-hackers@postgresql.org>; "Brian A Hirt"
<bhirt@berkhirt.com>
Sent: Tuesday, December 18, 2001 12:32 PM
Subject: Re: [HACKERS] problems with table corruption continued


> "Brian Hirt" <bhirt@mobygames.com> writes:
> > Do you think this might also explain the following errors i was seeing?
>
> > NOTICE:  Cannot rename init file
> > /moby/pgsql/base/156130/pg_internal.init.19833 to
> > /moby/pgsql/base/156130/pg_internal.init: No such file or directory
>
> No, that error is not coming from anywhere near VACUUM; it's from
> relcache startup (see write_irels in src/backend/utils/cache/relcache.c).
> The rename source file has just been created in the very same routine,
> so it's difficult to see how one would get a "No such file" failure from
> rename().
>
> It is possible that several backends create temp init files at the
> same time and all try to rename their own temp files into place as
> the pg_internal.init file.  However, that should work: the rename
> man page says
>
>      The rename() system call causes the source file to be renamed to
>      target.  If target exists, it is first removed.  Both source and
>      target must be of the same type (that is, either directories or
>      nondirectories), and must reside on the same file system.
>
>      If target can be created or if it existed before the call, rename()
>      guarantees that an instance of target will exist, even if the system
>      crashes in the midst of the operation.
>
> so we should end up with the extra copies deleted and just one init file
> remaining after the slowest backend renames its copy into place.
>
> Do you by chance have your database directory mounted via NFS, or some
> other strange filesystem where the normal semantics of concurrent rename
> might not work quite right?
>
> FWIW, I believe this condition is pretty harmless, but it would be nice
> to understand exactly why you're seeing the message.
>
> regards, tom lane
>


Tue, 18 Dec 2001 11:07:26 -0700
"Brian Hirt" <bhirt@mobygames.com>
Re: problems with table corruption continued 
"Brian Hirt" <bhirt@mobygames.com> writes:
> [ example case that creates a duplicate tuple ]

Got it.  The problem arises in this section of vacuum.c, near the bottom
of repair_frag:

                if (!(tuple.t_data->t_infomask & HEAP_XMIN_COMMITTED))
                {
                    if ((TransactionId) tuple.t_data->t_cmin != myXID)
                        elog(ERROR, "Invalid XID in t_cmin (3)");
                    if (tuple.t_data->t_infomask & HEAP_MOVED_OFF)
                    {
                        itemid->lp_flags &= ~LP_USED;
                        num_tuples++;
                    }
                    else
                        elog(ERROR, "HEAP_MOVED_OFF was expected (2)");
                }

This is trying to get rid of the original copy of a tuple that's been
moved to another page.  The problem is that your index function causes a
table scan, which means that by the time control gets here, someone else
has looked at this tuple and marked it good --- so the initial test of
HEAP_XMIN_COMMITTED fails, and the tuple is never removed!

I would say that it's incorrect for vacuum.c to assume that
HEAP_XMIN_COMMITTED can't become set on HEAP_MOVED_OFF/HEAP_MOVED_IN
tuples during the course of vacuum's processing; after all, the xmin
definitely does refer to a committed xact, and we can't realistically
assume that we know what processing will be induced by user-defined
index functions.  Vadim, what do you think?  How should we fix this?

In the meantime, Brian, I think you ought to get rid of your index
CREATE  INDEX "developer_aka_search_idx" on "developer_aka" using btree ( developer_aka_search_name ("developer_aka_id") "varchar_ops" );
I do not think this index is well-defined: an index function ought
to have the property that its output depends solely on its input and
cannot change over time.  This function cannot make that claim.
(In 7.2, you can't even create the index unless you mark the function
iscachable, which is really a lie.)  I'm not even real sure what you
expect the index to do for you...

I do not know whether this effect explains all the reports of duplicate
tuples we've seen in the last few weeks.  We need to ask whether the
other complainants were using index functions that tried to do table
scans.

			regards, tom lane

Tue, 18 Dec 2001 13:48:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problems with table corruption continued 
"Brian Hirt" <bhirt@mobygames.com> writes:
> Great,  I'm also trying to create a reproducable test case for the original
> problem i reported with duplicate rows/oids/pkeys.  Maybe both problems are
> a result of the same bug; i don't know.

Were the duplicate rows all in tables that had functional indexes based
on functions similar to developer_aka_search_name?  The problem we're
seeing here seems to be due to VACUUM not being able to cope with the
side effects of the SELECT inside the index function.

			regards, tom lane

Tue, 18 Dec 2001 13:53:37 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problems with table corruption continued 
"Brian Hirt" <bhirt@mobygames.com> writes:
> I was trying to avoid adding additional computed fields to the tables and
> maintaining them with triggers, indexing and searching on them.  The index
> function seemed like an elegant solution to the problem

Understood, but can you write the index function in a way that avoids
having it do a SELECT to get at data that it hasn't been passed?  I'm
wondering if you can't define the function as just
	f(first_name, last_name) = upper(first_name || ' ' || last_name)
and create the index on f(first_name, last_name).  You haven't shown us
the queries you expect the index to be helpful for, so maybe this is not
workable...

			regards, tom lane

Tue, 18 Dec 2001 14:20:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problems with table corruption continued 
"Brian Hirt" <bhirt@mobygames.com> writes:
> Do you think this might also explain the following errors i was seeing?

> NOTICE:  Cannot rename init file
> /moby/pgsql/base/156130/pg_internal.init.19833 to
> /moby/pgsql/base/156130/pg_internal.init: No such file or directory

No, that error is not coming from anywhere near VACUUM; it's from
relcache startup (see write_irels in src/backend/utils/cache/relcache.c).
The rename source file has just been created in the very same routine,
so it's difficult to see how one would get a "No such file" failure from
rename().

It is possible that several backends create temp init files at the
same time and all try to rename their own temp files into place as
the pg_internal.init file.  However, that should work: the rename
man page says

     The rename() system call causes the source file to be renamed to
     target.  If target exists, it is first removed.  Both source and
     target must be of the same type (that is, either directories or
     nondirectories), and must reside on the same file system.

     If target can be created or if it existed before the call, rename()
     guarantees that an instance of target will exist, even if the system
     crashes in the midst of the operation.

so we should end up with the extra copies deleted and just one init file
remaining after the slowest backend renames its copy into place.

Do you by chance have your database directory mounted via NFS, or some
other strange filesystem where the normal semantics of concurrent rename
might not work quite right?

FWIW, I believe this condition is pretty harmless, but it would be nice
to understand exactly why you're seeing the message.

			regards, tom lane

Tue, 18 Dec 2001 14:32:05 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: problems with table corruption continued 