Re: catalog corruption bug - Mailing list pgsql-hackers

On Wed, 21 Dec 2005, Tom Lane wrote:

> Jeremy Drake <pgsql@jdrake.com> writes:
> > We have encountered a very nasty but apparently rare bug which appears to
> > result in catalog corruption.
>
> How much of this can you reproduce on 8.1.1?  We've fixed a few issues
> already.

We did not see this problem for a while.  I upgraded the second gentoo box
to show this problem to 8.1.1 basically as soon as the ebuild for it was
out.  It just started acting up today (but we have not stressed it for a
while).  It appears to be acting similarly (although corruption which
persisted into other backends has not appeared).

Here is the error message I currently get on 8.1.1 (names have been
changed):

DBD::Pg::st execute failed: ERROR:  type "push_temp" already exists
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE push_temp (val text) ON
COMMIT DROP"
PL/pgSQL function "push_func" line 6 at SQL statement
DBD::Pg::st execute failed: ERROR:  type "push_temp" already exists
CONTEXT:  SQL statement "CREATE TEMPORARY TABLE push_temp (val text) ON
COMMIT DROP"
PL/pgSQL function "push_func" line 6 at SQL statement

postgres=# select version();                                                                version

------------------------------------------------------------------------------------------------------------------------------------------PostgreSQL
8.1.1on x86_64-pc-linux-gnu, compiled by GCC
 
x86_64-pc-linux-gnu-gcc (GCC) 3.4.4 (Gentoo 3.4.4-r1, ssp-3.4.4-1.0,
pie-8.7.8)
(1 row)

>
> > This was built from the gentoo ebuild version 8.1.0
>
> I'd be even more interested if you can reproduce it on a non-gentoo
> machine.  Gentoo is not noted for stability.

This one was also on Gentoo, this time ebuild version 8.1.1.  They are
applying a couple patches it looks like, one of which looks like it just
changes some makefile stuff around, and the other appears to add support
for the SH platform in s_lock.h.

Unfortunately, I don't have any non-gentoo boxes around which are on a par
with these two hardware-wise.  Also, I think my test cases I tried to come
up with were most likely wrong.  This code which is currently croaking is
basically amounting to 9 processes calling functions which do SELECT,
INSERT, SELECT FOR UPDATE, DELETE, and UPDATE, as well as CREATE TEMP
TABLE ... ON COMMIT DROP.  ON COMMIT DROP is the only kind of temp table
that this code uses.

I could probably try to re-arrange the code in such a way that I can send
it, if that would be helpful (although I wouldn't want to waste the effort
if it wouldn't be helpful).

Also, what do you figure are the chances of that plperl locale problem
causing this?  I would guess pretty slim seeing as I am only using ASCII
for my schemas, and all of my databases are SQL_ASCII.  I am calling
plperl functions in both of the projects which are breaking...  Also, if I
run the command 'locale' all of the things it prints out are either empty
or "POSIX"

-- 
Take your dying with some seriousness, however.  Laughing on the way to
your execution is not generally understood by less advanced life forms,
and they'll call you crazy.    -- "Messiah's Handbook: Reminders for the Advanced Soul"