Re: Incomprehensible behaviour of a foreign key.

On Sun, 20 Jul 2003, Tom Lane wrote:

> "Nigel J. Andrews" <nandrews(at)investsystems(dot)co(dot)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