Repeatedly breaking indexes

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello,

I have encountered serious problem with PostgreSQL 7.3.4, the same
with previous version 7.3.2 (installed from RPMs postgresql-7.3.4-1PGDG)
running on dual processor machine. Maybe dual processors cause described
problems but I suppose it should work on N-processor machine. Redhat 8.0
is running on this machine.

I have a DB with 50 tables, following three are the most
problematic: zone, qmail_account, qmail_dot (scheme at the end of this
mail).

* zone has a unique key (name)
* qmail_account has column domain which refers to zone(name)
qmail_account has a unique key (username,domain)
* qmail_dot has columns username,domain which refers to
qmail_account(username,domain)
qmail_dot has a unique key (username,domain,extension)
* there are preinsert/preupdate triggers on tables which DON'T modify
indexed columns

There are more RI/FK constraints, see scheme.

- -----
'LIKE' / '=' PROBLEM

Several times I came to the situation when I was not able to insert a
record to the table zone because of unique index.

But when I tried to select the record "where name='domain'" I got no
result. When i used "where name LIKE 'domain'" I got it. LIKE is without
wild chars %.

After "update zone set name='domain' where name like 'domain'" I was able
to select it with '='.

So these operators acts differently on the same indexes.

- -----
SESSION PROBLEM

Another problem is similar to the previous. My coleague tried to select
domain from the zone table and got no result, so he inserted the record.
Yes, this time the insert succeeded. Then he selected the record with '='
and he saw his newly inserted record.
I told his that I already inserted this domain and I did select with '='
and saw my record. When I used 'like' I saw both records.
When he used 'like' he still saw only his record. He closed the session
and open new, then he saw both records with 'like'.

After playing with it I used REINDEX TABLE {zone,qmail_account,qmail_dot}
FORCE and problem was fixed. But for a while only. After some time the
indexes broke again on table qmail_account and qmail was complaining about
missing accounts, but I saw the record with the EXACTLY same select query.
After closing session and opening new one I didn't see it as well :-(
After reindex everything is fine.

- -----
Duplicates on unique index

The last problem with indexes I encountered is that when I have unique
index sometime it's possible to have duplicates. I don't know when it
happens, but unlike in previous problem this time I see both record using
'=' operator.

- ----
tables

CREATE TABLE zone (
id SERIAL,
contractid int not null REFERENCES contract (id),
name varchar(100) not null unique DEFAULT '',
public_mail boolean not null DEFAULT '0',
public_web boolean not null DEFAULT '0',
ns1id int not null REFERENCES server (id) DEFAULT '0',
ns2id int REFERENCES server (id) DEFAULT '0',
ns3id int REFERENCES server (id) DEFAULT '0',
mx0id int not null REFERENCES server (id) DEFAULT '0',
mx10id int REFERENCES server (id) DEFAULT '0',
mx100id int REFERENCES server (id) DEFAULT '0',
expire date null,
extra text null DEFAULT '',
comment varchar(200) null DEFAULT '',
PRIMARY KEY(id)
);

CREATE INDEX zone_contractid_idx ON zone (contractid);

CREATE TABLE qmail_account (
id SERIAL,
contractid int not null REFERENCES contract (id),
username varchar(50),
domain varchar(100) not null REFERENCES zone (name) ON UPDATE
CASCADE,
email varchar(255),
password varchar(50),
passwordenc boolean DEFAULT 'n',
uid int DEFAULT '8',
gid int DEFAULT '11',
home varchar(100),
hardquota int DEFAULT '50',
valid_from timestamp null DEFAULT 'now()',
valid_to timestamp null,
enabled boolean DEFAULT '1',
use_dot_qmail boolean DEFAULT '0',
lastlogin timestamp,
lastdelivery timestamp,
comment varchar(200) null DEFAULT '',
PRIMARY KEY(id)
);

CREATE INDEX qmail_account_contractid_idx ON qmail_account (contractid);
CREATE INDEX qmail_account_username_idx ON qmail_account (username);
CREATE UNIQUE INDEX qmail_account_email ON qmail_account (email);
CREATE UNIQUE INDEX qmail_account_email_idx ON qmail_account (username,domain);

CREATE TABLE qmail_dot (
id SERIAL,
username varchar(50),
domain varchar(100) not null REFERENCES zone (name) ON UPDATE
CASCADE,
extension varchar(64),
dotqmail text,
FOREIGN KEY(username,domain) REFERENCES
qmail_account(username,domain) ON UPDATE CASCADE ON
DELETE CASCADE,
PRIMARY KEY(id)
);

CREATE UNIQUE INDEX qmail_dot_key ON qmail_dot (username,domain,extension);

- --

Martin Edlman
Fortech s.r.o, Litomysl
Public PGP key: http://edas.visaci.cz/#keys

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQE/pjYCoFlEKJy9loQRApIXAJ0VTkKloaYVM9vF9Ai6rgv7+oiEvwCfRW/l
4oLXCT46Gd9SwHCLd+eZsLs=
=7yx5
-----END PGP SIGNATURE-----