Обсуждение: "relation deleted while in use" encountered with Postgresql 8.0.8

Hi,

I'm using a python script w/ the PyGreSQL library to insert 1 billion rows into a database table for an experiment (performing a commit every 10K rows). My script failed at about 170M rows with the following exception:

  File "/usr/lib64/python2.3/site-packages/pgdb.py", line 163, in execute
    self.executemany(operation, (params,))
  File "/usr/lib64/python2.3/site-packages/pgdb.py", line 185, in executemany
    raise DatabaseError, "error '%s' in '%s'" % ( msg, sql )
pg.DatabaseError: error 'ERROR:  relation 184577 deleted while still in use
' in 'INSERT INTO nfs_files_10 (mxid, fhInode, fhGen, fhSnapId, fhFlags, name, parentInode, parentGen, parentSnapId, parentFlags, extension, type, atime, mtime, fileSize, owner, generation) VALUES (10, 120, 927370846, 0, 0, 'gummy0.txt', 1204041, 927370729, 0, 0, 'txt', 0, 1112147234, 1112147234, 40960, NULL, 2);'


After this error, my database table no longer exists and appeared to have been dropped, although my script was doing only INSERT statements. Any ideas on what might be causing this and/or if this a known issue and possible solutions would be greatly appreciated.

thanks,
Gautam

On Thu, Nov 29, 2007 at 02:44:25PM -0800, Gautam Sampathkumar wrote:
> Hi,
>
> I'm using a python script w/ the PyGreSQL library to insert 1 billion rows
> into a database table for an experiment (performing a commit every 10K
> rows). My script failed at about 170M rows with the following exception:

You don't indicate your version so it could be XID wraparound, but
you'd have to be running a pretty old version to run into that still...

> After this error, my database table no longer exists and appeared to have
> been dropped, although my script was doing only INSERT statements. Any ideas
> on what might be causing this and/or if this a known issue and possible
> solutions would be greatly appreciated.

Do you run VACUUM regularly?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
>  -- John F Kennedy

On Thursday 29 November 2007 2:44 pm, Gautam Sampathkumar wrote:
> Hi,
>
> I'm using a python script w/ the PyGreSQL library to insert 1 billion rows
> into a database table for an experiment (performing a commit every 10K
> rows). My script failed at about 170M rows with the following exception:
>
>   File "/usr/lib64/python2.3/site-packages/pgdb.py", line 163, in execute
>     self.executemany(operation, (params,))
>   File "/usr/lib64/python2.3/site-packages/pgdb.py", line 185, in
> executemany
>     raise DatabaseError, "error '%s' in '%s'" % ( msg, sql )
> pg.DatabaseError: error 'ERROR:  relation 184577 deleted while still in use
> ' in 'INSERT INTO nfs_files_10 (mxid, fhInode, fhGen, fhSnapId, fhFlags,
> name, parentInode, parentGen, parentSnapId, parentFlags, extension, type,
> atime, mtime, fileSize, owner, generation) VALUES (10, 120, 927370846, 0,
> 0, 'gummy0.txt', 1204041, 927370729, 0, 0, 'txt', 0, 1112147234,
> 1112147234, 40960, NULL, 2);'
>
>
> After this error, my database table no longer exists and appeared to have
> been dropped, although my script was doing only INSERT statements. Any
> ideas on what might be causing this and/or if this a known issue and
> possible solutions would be greatly appreciated.
>
> thanks,
> Gautam
Is it possible to show the python script?
Also were you using the logging functions in Postgres?
If so what does the log file show?
Was another application/person accessing the database at the same time?

--
Adrian Klaver
aklaver@comcast.net

On Nov 29, 2007 4:44 PM, Gautam Sampathkumar <gsampathkumar@gmail.com> wrote:
> Hi,
>
> I'm using a python script w/ the PyGreSQL library to insert 1 billion rows
> into a database table for an experiment (performing a commit every 10K
> rows). My script failed at about 170M rows with the following exception:
>
>    File "/usr/lib64/python2.3/site-packages/pgdb.py", line 163, in execute
>      self.executemany(operation, (params,))
>    File "/usr/lib64/python2.3/site-packages/pgdb.py", line 185, in
> executemany
>      raise DatabaseError, "error '%s' in '%s'" % ( msg, sql )
>  pg.DatabaseError: error 'ERROR:  relation 184577 deleted while still in use
>  ' in 'INSERT INTO nfs_files_10 (mxid, fhInode, fhGen, fhSnapId, fhFlags,
> name, parentInode, parentGen, parentSnapId, parentFlags, extension, type,
> atime, mtime, fileSize, owner, generation) VALUES (10, 120, 927370846, 0, 0,
> 'gummy0.txt', 1204041, 927370729, 0, 0, 'txt', 0, 1112147234, 1112147234,
> 40960, NULL, 2);'
>
>
> After this error, my database table no longer exists and appeared to have
> been dropped, although my script was doing only INSERT statements. Any ideas
> on what might be causing this and/or if this a known issue and possible
> solutions would be greatly appreciated.

When you hear hoofbeats, don't think zebra...  This is not a common problem.

Is this repeatable reliably?  Are there other people connected to this
database?  Are you logging queries?  What, if anything, have you done
to make your postgresql installation "unique" from a default one,
including any monitoring scripts and things like that?