Forum OpenACS Development: OpenACS 4.0 Guidelines (aD questions)
In the OpenACS 4.0 Guidelines, there are a couple of aD questions, including
- what's with the disabled constraints, followed by index creation, followed by constraint reenablement?
Please note...I'm NOT an aD employee (nor have I ever been, though I almost was...), but just looking at that, I can guess. Sounds like the SQL was generated a DDL creation tool. I use ERwin and SQL*Navigator at work, and get those kinds of disabled/mucked with/re- enabled constraints sometimes. It could be one of those, or even a perl script for all I know (there's supposedly a pretty cool DDL:Oracle module that I haven't tried, for example). But that's what it looks like to me...
were asking, in essence, if there was some Secret Magical Oracle-World
Trick going down that we'd have to worry about in Postgres. We were
sitting in an aD conference table with our respective laptops reading
datamodels like crazy and pulling out stuff that we didn't immediately
understand into a list that later got stuck into that document.
Anyway, the eventual conclusion was that this was something we could
just ignore in our porting efforts, and thus far we've not been bitten
by any problems that would prove this assumption wrong.
I should filter through that document one more time and pull out some
of the things that aren't relevant at present...
You've made me curious, though ... if any aD'ers in the know read
this, was some sort of tool being used that generated these?
"Enabling a primary key or unique key constraint automatically creates a unique index to enforce the constraint. This index is dropped if the constraint is subsequently disabled, causing Oracle to rebuild the index every time the constraint is enabled. To avoid this behavior, create new primary key and unique key constraints initially disabled. Then create nonunique indexes or use existing nonunique indexes to enforce the constraints. "
the constraint is disabled then reenabled is when
acs-objects-create.sql is run.
I suppose someone may've thought it would be useful for cases where
you copy in tons of data directly into the objects table. In this
case, though, the cost of dropping and rebuilding the index isn't
really that big a deal.
The code certainly doesn't hurt, but unless the ACS were to start
disabling and reenabling the unique constraint on a frequent basis it
doesn't help, either.
It may actually slow things down a bit. When a unique index is used
to enforce the constraint, Oracle can throw a constraint error based
on whether or not the insertion into the index succeeds. Using a
non-unique index to enforce the constraint means that the check has to
be done on the index after insertion. Of course, the insertion code
may flag dupe entries anyway specifically to optimize this case. But
then again it may not.