Thread: Bug?: Update on ancestor for a row of a child

Hello (mainly developer) folks!

Probably Kevin really found a bug.

When I saw his words in $50, I immediately started to look around his
problem... You probably don't think that as a student here, in Hungary I
live half a month for $50 :-))))

So I simplified his given schema as much as I needed, and found out
what exactly the problem is.

(Kevin, if you badly need a workaround, I can give you one,
but quite an ugly one.)

The problem is: when updating a row in an ancestor table,
which is really belongs to a child, there's something wrong
with the CHECK system.

Here's the simple schema, producing the error:

----------------

drop table child;
drop table ancestor;

create table ancestor ( node_id int4, a int4
);

create table child ( b int4 NOT NULL DEFAULT 0          CHECK ( b = 0 OR b = 1)
) inherits (ancestor);

insert into ancestor values (3,4);
insert into child values (5,6,1);

update ancestor set a=8 where node_id=5;

----------------

If one leaves out the CHECK condition, the UPDATE
works just fine, and __the final result meets the
check condition__.

So it seems to me that the system
1. either tries to check the CHECK condition of the child on the  ancestor
2. or only uses a wrong time to check against it.




Best regards,
Baldvin

> The problem is: when updating a row in an ancestor table,
> which is really belongs to a child, there's something wrong
> with the CHECK system.

Well, I believe you found one minor problem.  The bigger one is still
lurking in the shadows though.  To duplicate it, take my previous schema,
and add 
       lastlog         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,           

to the users table, between pass_hash and logged_in.

After doing so, you'll find that postgres actually crashes when you try
to insert a vote into the uservote table.  That's the one that has me
looking at the costs involved with migrating to Oracle.

-Kevin

Kevin,

> After doing so, you'll find that postgres actually crashes when you
> try
> to insert a vote into the uservote table.  That's the one that has me
> looking at the costs involved with migrating to Oracle.

And you think that Oracle is entirely free of bugs?  ;-)

At least here, you can get a message to the actual database developers.

Still, I understand your frustration.

-Josh


______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

> And you think that Oracle is entirely free of bugs?  ;-)

Yes, but they'd be exciting technology-oriented e-business enabled bugs!

> Still, I understand your frustration.   

Thanks...  It's just frustrating that the bug is on something so basic,
which makes it both hard to code around and hard for me to delve into the
postgres source and fix.  I spent a few hours tracing source before finally
conceding the point that it takes more than a few hours to understand
postgres internals well enough to fix a major bug.

For development purposes I've just removed all the CHECK constraints 
from my child tables, and I'm hoping some genius will solve the problem
by the time I'm looking to deploy.

-Kevin Way