Re: Incomprehensible behaviour of a foreign key.

Sorry, I forgot to mention that this had no effect when it came to stopping the
error message.


On Sun, 20 Jul 2003, Nigel J. Andrews wrote:

> 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,
MinLen0, 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.

I forgot to mention that this had no effect when it came to stopping the error
message.


>
> 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