Thread: Postgresql 7.1.3 not thread safe

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


Your name        :    Alain Picard
Your email address    :       Alain.Picard@memetrics.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 | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbâbbbbbbbbbbbbbbbbbbbbbbb
  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

Alain Picard <Alain.Picard@memetrics.com> writes:
> Behold!

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

> -> returns:

>    id   |                               data
> --------+------------------------------------------------------------------
>  253084 | bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbâbbbbbbbbbbbbbbbbbbbbbbb
>   55068 | áaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
> (2 rows)

I would say that you have either faulty RAM or a faulty disk drive.
Given the apparent dependence on running more than one backend at a
time, faulty RAM is perhaps more likely (the bad chip could be in
RAM locations that don't get used unless lots of processes are running).
The apparent single-bit nature of the fault also points to a bad RAM
location; disks tend to drop multiple words or whole sectors, not
single bits.  But dropped bits during I/O transfers shouldn't be ruled
out completely.

Have you run any hardware diagnostics lately?

            regards, tom lane

Thanks to Tom Lane and to Jan-Benedict Glaw for suggesting
a RAM problem; this has now been confirmed.

I'm relieved that Postgresql does not have what would have
been a very fundamental problem.  That's the first time I
find a "bug" which is actually a hardware fault.  I guess
sometimes the computer is wrong after all.

Thanks for the excellent feedback!
--
            Alain Picard
            Memetrics