Postgresql 7.1.3 not thread safe

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Alain Picard
Your email address : Alain(dot)Picard(at)memetrics(dot)com

System Configuration
---------------------
Architecture (example: Intel Pentium) :

bash-2.05$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 8
model name : Pentium III (Coppermine)
stepping : 6
cpu MHz : 801.848
cache size : 256 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 2
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 sep mtrr pge mca cmov pat pse36 mmx fxsr sse
bogomips : 1599.07

Operating System :

bash-2.05$ uname -a
Linux possum 2.4.7-10 #1 Thu Sep 6 17:27:27 EDT 2001 i686 unknown

[A plain-vanilla redhat 7.2 system]

PostgreSQL version (example: PostgreSQL-7.1.3): PostgreSQL-7.1.3

Compiler used (example: gcc 2.95.2) :
Unknown; as distributed by the redhat RPM.

Please enter a FULL description of your problem:
------------------------------------------------

Dear Postgresql developers,

I have an application which talks to postgresql 7.1.3 running
on linux, redhat 7.2. via unixODBC 2.2.0.

If I create a table like this:

create table test_table (
id integer not null,
data varchar(64) not null check (data ~ '^[ab]*$')
);

And then run one copy of the test application which writes into the
table 10,000 values like
"aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa"

in process 1, and 10,000 values like

"bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"

in process 2, I would expect to only have one of the above two values
like the above in the DB at the end of the run.

Behold!

----------------------------------------------------------------
xos_visitor_db-#

select * from test_table
where data !='bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
and
data != 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa';

-> returns:

id | data
--------+------------------------------------------------------------------
253084 | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
55068 | aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
(2 rows)

----------------------------------------------------------------
Note the 2 rows (out of 20,000) which have non- us-ascii characters in
them.

This contrived example is the simplest form I can reproduce the
problem to. My actual application is written in lisp, and uses
unixODBC to talk to postgresql, though I believe the above problem
should be reproducible using any client. I might write a perl
script using the DBD stuff to test this. If I can reproduce the
problem there, I'll forward the script.

This problem seems to only occur under very heavy load when multiple
DB connections are writing to the same table, each carefully within the
context of a transaction.

cheers,

--
Alain Picard
Memetrics