Forum OpenACS Development: acs_objects_context_object_un

Collapse
1: acs_objects_context_object_un
Posted by Malte Sussdorff on 06/14/06 02:08 PM
Maybe we (myself and colleagues) are missing a major point, but acs_objects has these two unique constraints:

"acs_objects_pk" PRIMARY KEY, btree (object_id)
"acs_objects_context_object_un" UNIQUE, btree (context_id, object_id)

As the second one is a reason why quite some transactions are failing, I was wondering how this could happen. And then I continued wondering under which situations this constraint could be violated and came up with the following szenarios:

NONE

As object_id is a primary key, it automatically implies "unique". And any combination with a unique key is unique by definition (as you cannot have two times the same object_id in the acs_objects table, therefore you cannot have the same object_id/context_id combination either).

Before I make a change to this fundamental table, I would like to ask for the validity of my findings.

Collapse
2: Re: acs_objects_context_object_un (response to 1)
Posted by Dave Bauer on 06/14/06 02:55 PM
Malte,

What exactly would you change?

The unique constraint is because of an index on (context_id,object_id).

Or rather the other way around.

"
-- The unique constriant about will force create of this index...
-- create index acs_objects_context_object_idx onacs_objects (context_id, object_id);
"

Point is, we can't change this, and I can't imagine why you would want to. What sort of transactions are failing due to this constraint? An example would be helpful.

Collapse
3: Re: Re: acs_objects_context_object_un (response to 2)
Posted by Malte Sussdorff on 06/14/06 03:44 PM
The problem is described in https://openacs.org/forums/message-view?message%5fid=447214.

What I would change is to create the index and drop the unique constraint, as this seems to me to be the propper thing to do. But Dave explained that an index on (object_id,context_id) will create a unique constraint.

Now my question would be, why is that the case. But maybe I should ask over at postgresql. Any way how this could be detected? I mean if the database is creating a unique constraint, shouldn't this show up in \d ?

Collapse
4: Re: acs_objects_context_object_un (response to 1)
Posted by Nis Jørgensen on 06/14/06 07:06 PM
I believe you are shooting the messenger here ... my guess is that PG checks the constraints in an unspecified order , so if you try to insert an object using an existing object_id, sometimes you will get an error from this constraint, sometimes from the pk one.

In fact, when I said that we are seeing "the same problem", I forgot to check whether it was the exact same constraint being violated - and indeed it wasn't.

It is consistent with the behavior I observe that the only difference between having a UNIQUE index and a UNIQUE constraint is in how you create/drop them, and how they show up in listings.

Now, I don't know whether we actually need a unique index. Normally this would be used for a foreign key, but I don't see any in my installation. As you point out, the constraint is logically redundant, so a simple index would make for less clutter.

/Nis