Thread: Something smells in this schema...

    I had postgres start blocking all it's UPDATEs on a production
database today, when an engineer added the following two tables,
among other things.  We've had to restore from backup, and the
interesting thing is that when we re-add these tables, things
break again.
Version:  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2

(we were planning on going to beta4 in another week, and have done
some testing.  This problem doesn't seem to occur on the engineer's
machine, which is already at beta4)
My first thought was the index on the boolean field in the time_cards,
which I could have sworn has caused me problems before.  Anyone else see
anything wrong?


-- time_tasks.schema
create table time_tasks ( name char(2) primary key, title text, description text
);

insert into time_tasks (name, title) values ('CO', 'Communication');
insert into time_tasks (name, title) values ('DB', 'Debug');
.
.
.



-- time_cards.schema
create table time_cards ( id serial, open bool not null default 't', accounted bool not null default 'f',
 uid int4 not null, task char(2) not null, project int4, component text,
 time_start int4, time_stop int4, total_minutes int4,
 notes text
);
create index time_cards_open_pkey on time_cards (open);
create index time_cards_uid_pkey on time_cards (uid);

-- 
Adam Haberlach            |A cat spends her life conflicted between a
adam@newsnipple.com       |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500    '00 >^<      |profound desire to avoid getting wet.

Adam Haberlach <adam@newsnipple.com> writes:
>     I had postgres start blocking all it's UPDATEs on a production
> database today, when an engineer added the following two tables,
> among other things.  We've had to restore from backup, and the
> interesting thing is that when we re-add these tables, things
> break again.

"blocking"?  Please define symptoms more precisely.

>     My first thought was the index on the boolean field in the time_cards,
> which I could have sworn has caused me problems before.  Anyone else see
> anything wrong?

Pre-7.1 versions do have problems with large numbers of equal keys in
a btree index, which is more or less the definition of an index on
boolean.  I'm dubious that such an index is of any value anyway ...
        regards, tom lane

On Fri, Feb 16, 2001 at 01:02:24PM -0500, Tom Lane wrote:
> Adam Haberlach <adam@newsnipple.com> writes:
> >     I had postgres start blocking all it's UPDATEs on a production
> > database today, when an engineer added the following two tables,
> > among other things.  We've had to restore from backup, and the
> > interesting thing is that when we re-add these tables, things
> > break again.
> 
> "blocking"?  Please define symptoms more precisely.
The postgres process stalls.  According to ps, it's it is attempting
an UPDATE.  I think it times out eventually (I was in disaster-recovery
mode this morning, and not always waiting around for these things.  :)

> >     My first thought was the index on the boolean field in the time_cards,
> > which I could have sworn has caused me problems before.  Anyone else see
> > anything wrong?
> 
> Pre-7.1 versions do have problems with large numbers of equal keys in
> a btree index, which is more or less the definition of an index on
> boolean.  I'm dubious that such an index is of any value anyway ...
Ok--I'll check this.  Thanks for the incredibly fast response--my
favorite thing about PostgreSQL is the fact that I can post to a mailing
list and get clued answers from real developers, usually within hours
if not minutes.

-- 
Adam Haberlach            |A cat spends her life conflicted between a
adam@newsnipple.com       |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500    '00 >^<      |profound desire to avoid getting wet.