DataNucleus JIRA is now in read-only mode. Raise any new issues in GitHub against the plugin that it applies to. DataNucleus JIRA will remain for the foreseeable future but will eventually be discontinued
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

Yang ZHONG added a comment - 22/Apr/09 10:36 PM
Almost exact same Test Case as NUCRDBMS-156 except using 1.1.2

Yang ZHONG made changes - 22/Apr/09 10:36 PM
Field Original Value New Value
Attachment 172.zip [ 10920 ]
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.

Andy Jefferson made changes - 23/Apr/09 08:03 AM
Status Open [ 1 ] Resolved [ 5 ]
Resolution Cannot Reproduce [ 5 ]
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 made changes - 24/Apr/09 07:09 AM
Resolution Cannot Reproduce [ 5 ]
Status Resolved [ 5 ] Reopened [ 4 ]
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 ...

Andy Jefferson made changes - 24/Apr/09 07:11 AM
Summary indexed="true" doesn't work for join datanucleus.rdbms.constraintCreateMode="JDO2" and indexed="true" doesn't add indexes to join tables
Priority Major [ 3 ] Minor [ 4 ]
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

Andy Jefferson made changes - 27/Nov/10 07:52 PM
Status Reopened [ 4 ] Resolved [ 5 ]
Fix Version/s 2.2.0.release [ 10932 ]
Resolution Fixed [ 1 ]
Andy Jefferson made changes - 10/Dec/10 07:49 AM
Status Resolved [ 5 ] Closed [ 6 ]