W. A. Sanchez wrote:
> Hi! We have a java program accessing a postgresql 7.1.3 db
> through a connection pool. this morning when i checked the
> db, a number of recent records were missing. however, when i
> did a search on the pgdata/base files, the recent records
> were still there and also in the pg_xlog file but i can't see
> them using psql. Is there a way to fix this like rebuilding the
> pg_xlog perhaps?
>
> Thanks.
>
>
The reason you can find the data but not see the row is that PostgreSQL uses
a nonoverwriteing storage manager. This means that when some one updates a
row the original row isn't overwriten, but rather that a new row is
inserted. There are various fields to tell who can see what row, as if the
update where part of a transaction, later parts of the transaction could see
the updated row, but other traansactions would see the original until the
first row committed (see the manual about MVCC). This is also the case with
deletes. Therefore if someone has deleted your row, the data would still be
there until you vacuumed. pg_xlog is the WAL (write ahead log) files and
shouldn't be fiddled with. These ensure that even if the db where to suffer
powerdown any commited transactions would be committed and there couldn't be
a problem with a partly written row in a table.
I also heard of a problem with non superusers running vacuum, but I think it
just affects 7.2.1 & 7.2.2. What could happen there is that the bits that
tell wether a transaction is commited or not could be prematurley removed,
therefore leading to a row being thought to not be committed.
hmmm... I do seem to have gone on a bit sorry
hth,
- Stuart