Issue Details (XML | Word | Printable)

Key: NUCRDBMS-172
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Minor Minor
Assignee: Unassigned
Reporter: Yang ZHONG
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
DataNucleus Store RDBMS

datanucleus.rdbms.constraintCreateMode="JDO2" and indexed="true" doesn't add indexes to join tables

Created: 22/Apr/09 10:35 PM   Updated: 10/Dec/10 07:49 AM   Resolved: 27/Nov/10 07:52 PM
Component/s: SchemaTool
Affects Version/s: 1.1.2
Fix Version/s: 2.2.0.release

File Attachments: 1. Zip Archive 172.zip (2 kB)

Environment: Linux, Java 5, PostGreSQL 8.3.7, DB2 9.1.0.2

Datastore: IBM DB2, PostgreSQL
Severity: Production


 Description  « Hide
Run attached "r"(SchemaTool) and should have seen index for "COLUMN".
This's a continuation of
HTTP://WWW.DataNucleus.org/servlet/jira/browse/NUCRDBMS-156

Sort Order: Ascending order - Click to sort in descending order
Yang ZHONG added a comment - 22/Apr/09 10:36 PM
Almost exact same Test Case as NUCRDBMS-156 except using 1.1.2

Andy Jefferson added a comment - 23/Apr/09 08:03 AM
Shows no issue. Log from using it with Postgresql
07:59:16,911 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE TABLE "A"
(
    "A_ID" int8 NOT NULL,
    "JDOLOCKX" int8 NOT NULL,
    "JDOCLASSX" varchar(255) NOT NULL,
    PRIMARY KEY ("A_ID")
)
07:59:16,922 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE TABLE "B"
(
    "B_ID" int8 NOT NULL,
    "I" int4 NOT NULL,
    PRIMARY KEY ("B_ID")
)
07:59:16,933 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE TABLE "JOIN"
(
    "PK__JDOIDX" int8 NOT NULL,
    "COLUMN" int8,
    "JDOORDERX" int4 NOT NULL,
    PRIMARY KEY ("PK__JDOIDX","JDOORDERX")
)
07:59:16,942 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE INDEX "A_N49" ON "A" ("JDOLOCKX")
07:59:16,944 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE INDEX "A_N50" ON "A" ("JDOCLASSX")
07:59:16,951 (main) DEBUG [DataNucleus.Datastore.Schema] - ALTER TABLE "JOIN" ADD CONSTRAINT "JOIN_FK1" FOREIGN KEY ("PK__JDOIDX") REFERENCES "A" ("A_ID")
INITIALLY DEFERRED
07:59:16,955 (main) DEBUG [DataNucleus.Datastore.Schema] - ALTER TABLE "JOIN" ADD CONSTRAINT "JOIN_FK2" FOREIGN KEY ("COLUMN") REFERENCES "B" ("B_ID") INITIALLY DEFERRED
07:59:16,958 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE INDEX "JOIN_N49" ON "JOIN" ("PK__JDOIDX")
07:59:16,960 (main) DEBUG [DataNucleus.Datastore.Schema] - CREATE INDEX "JOIN_N50" ON "JOIN" ("COLUMN")

which bearing in mind the metadata is

    <package name="org.datanucleus.test">
        <class name="A" table="A">
            <version strategy="version-number" column="JDOLOCKX" indexed="true"/>
            <inheritance>
                <discriminator strategy="class-name" column="JDOCLASSX" indexed="true"/>
            </inheritance>
            <field name="b" table="join">
                <order column="JDOORDERX"/>
                <join column="PK__JDOIDX"/>
                <element column="COLUMN" indexed="true"/>
            </field>
        </class>
        <class name="B" table="B">
            <field name="i"/>
        </class>
    </package>

means that the schema is correct and there is an index on COLUMN in JOIN.

Yang ZHONG added a comment - 23/Apr/09 08:03 PM
Thanks to Andy for looking into this.

However, the problem still exists. SchemaTool generates DDL, what it logs does not necessarily end up in the generated DDL This's the DDL I got against PostGreSQL 8.3.7 and "COLUMN" index is missing:

------------------------------------------------------------------
-- DataNucleus SchemaTool (version 1.1.2) ran at 22/04/2009 15:16:41
------------------------------------------------------------------
-- Complete schema required for the following classes:-
-- org.datanucleus.test.A
-- org.datanucleus.test.B
--
-- Table "JOIN" for join relationship
CREATE TABLE "JOIN"
(
    "PK__JDOIDX" int8 NOT NULL,
    "COLUMN" int8 NULL,
    "JDOORDERX" int4 NOT NULL,
    PRIMARY KEY ("PK__JDOIDX","JDOORDERX")
);

-- Table "B" for classes [org.datanucleus.test.B]
CREATE TABLE "B"
(
    "B_ID" int8 NOT NULL,
    "I" int4 NOT NULL,
    PRIMARY KEY ("B_ID")
);

-- Table "A" for classes [org.datanucleus.test.A]
CREATE TABLE "A"
(
    "A_ID" int8 NOT NULL,
    "JDOLOCKX" int8 NOT NULL,
    "JDOCLASSX" varchar(256) NOT NULL,
    PRIMARY KEY ("A_ID")
);

-- Constraints for table "JOIN"

-- Constraints for table "B" for class(es) [org.datanucleus.test.B]

-- Constraints for table "A" for class(es) [org.datanucleus.test.A]
CREATE INDEX "A_N49" ON "A" ("JDOCLASSX");


Actually, lots of constraints showed up in Andy's logs are missing, e.g.

CREATE INDEX "A_N49" ON "A" ("JDOLOCKX")
ALTER TABLE "JOIN" ADD CONSTRAINT "JOIN_FK1" FOREIGN KEY ("PK__JDOIDX") REFERENCES "A" ("A_ID") INITIALLY DEFERRED
ALTER TABLE "JOIN" ADD CONSTRAINT "JOIN_FK2" FOREIGN KEY ("COLUMN") REFERENCES "B" ("B_ID") INITIALLY DEFERRED
CREATE INDEX "JOIN_N49" ON "JOIN" ("PK__JDOIDX")
CREATE INDEX "JOIN_N50" ON "JOIN" ("COLUMN")

Yang ZHONG added a comment - 23/Apr/09 08:13 PM
On second thought, the attached Test Case configures "datanucleus.rdbms.constraintCreateMode=JDO2", that probably can explain my DDL doesn't have Andy's FOREIGN KEYs. Still, "COLUMN" index should have been in generated DDL.

Thanks.

Andy Jefferson added a comment - 24/Apr/09 07:11 AM
Reopened, but workarounds exist (omit that PM setting) and downgraded to low priority since we have more important things to do right now. Obviously IBM could invest resource in providing a fix ...

Yang ZHONG added a comment - 24/Apr/09 06:47 PM
Unfortunately, the default "constraintCreateMode" doesn't work for us since the additional constraints don't work for us (maybe another issue, will try to collect evidence in order to compose a Test Case). Therefore no work-around yet...

Andy Jefferson added a comment - 27/Nov/10 07:52 PM
I get no such missing constraints with current code