On Sun, 20 Jul 2003, Tom Lane wrote:
> "Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> > **** Attempt the delete ...
> > **** ...and watch the empty table from the start cause a ref. int. failure!
>
> Bizarre. If the database is not too huge, I would ask you to please
> make a tarball backup of the whole $PGDATA directory (while the
> postmaster is stopped of course) before you go any further. That way
> we can get back to this state if needed for bug investigation.
>
> With backup in hand, please try "VACUUM FULL VERBOSE site_membership"
> and see what it has to say about rows in site_membership. If it shows
> that any were deleted, is the problem fixed?
>
> regards, tom lane
>
Test script output up to the transaction start:
**** Start by showing the problem table is empt _before_ the transaction starts
select * from site_membership;
id | site_id | group_id
----+---------+----------
(0 rows)
psql:/tmp/aa2.sql:8: INFO: --Relation ttacms.site_membership--
psql:/tmp/aa2.sql:8: INFO: Pages 1: Changed 0, reaped 1, Empty 0, New 0; Tup 0: Vac 82, Keep/VTL 0/0, UnUsed 0, MinLen
0,MaxLen 0; Re-using: Free/Avail. Space 7844/0; EndEmpty/Avail. Pages 1/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:/tmp/aa2.sql:8: INFO: Index site_membership_pkey: Pages 2; Tuples 0: Deleted 82.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
psql:/tmp/aa2.sql:8: INFO: Rel site_membership: Pages: 1 --> 0.
VACUUM
...
The table had 82 tuples in it until a deleted them with delete from
site_membership earlier in the session.
The db is about 100MB I'd guess but it's not really important to make the back
up as this problem is happening in a script that loads from a dump and does
some stuff to move the data into another schema and the problem consistently
arises during this process.
--
Nigel J. Andrews