db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Watts <...@cliftonfarm.org>
Subject Re: "Conglomerate could not be created"?
Date Fri, 01 Jun 2012 13:14:17 GMT
On Fri, 2012-06-01 at 15:19 +0300, John English wrote:
> I'm having trouble with the following error: "Conglomerate could not be
> created". It happens when I do this:
> 
>    INSERT INTO resource_usage (resid,itemid,itemtype)
>      (SELECT resid,?,? FROM resource_usage
>       WHERE itemid=?
>       AND   itemtype=?
>       AND   NOT EXISTS (SELECT resid FROM resource_usage
>                         WHERE itemid=? AND itemtype=?));
> 
> The first two and last two parameters are the same; the idea is that an
> item uses a set of resources, and I want to copy the item from one table
> where it's being edited to another where it is publicly visible. To do
> this I need to duplicate the set of resource IDs so that they appear
> for the item being edited (which has an ID and an item type of 'temp')
> and the released version (another ID and type 'final'), but without
> creating duplicate rows if the same resources were already listed
> prior to the editing process. For example:
> 
>    INSERT INTO resource_usage (resid,itemid,itemtype)
>      (SELECT resid,55,'final' FROM resource_usage
>       WHERE itemid=11
>       AND   itemtype='temp'
>       AND   NOT EXISTS (SELECT resid FROM resource_usage
>                         WHERE itemid=55 AND itemtype='final'));
> 
> The resource_usage table is defined like this:
> 
>    CREATE TABLE resource_usage (
>      resid       INTEGER       NOT NULL,
>      itemid      INTEGER       NOT NULL,
>      itemtype    VARCHAR(16)   NOT NULL,
>      CONSTRAINT resusage_1     FOREIGN KEY (resid)
>                                REFERENCES resources(id)
>                                ON DELETE CASCADE,
>      CONSTRAINT resusage_2     UNIQUE(resid,itemid,itemtype)
>    );
> 
> So to create a new item I insert a copy of the item from the editing
> table into the final destinationtable, then try to do the above insert
> on resource_usage for the newly created item, and get "Conglomerate
> could not be created".
> 
> If I stop after copying the item to the public table but before the
> insert above and then try each of the selects in turn manually, I get
> this:
> 
>    SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final';
>             -- 0 rows selected
> 
>    SELECT resid,55,'final' FROM resource_usage
>       WHERE itemid=11
>       AND   itemtype='temp'
>       AND   NOT EXISTS (SELECT resid FROM resource_usage
>                         WHERE itemid=55 AND itemtype='final');
>             -- 4 rows selected:
> RESID 	| ITEMID | ITEMTYPE
> --------+--------+-------
> 29      | 11     | temp
> 30      | 11     | temp
> 31      | 11     | temp
> 32      | 11     | temp
> 
>    SELECT resid,55,'final' FROM resource_usage
>       WHERE itemid=11
>       AND   itemtype='temp'
>       AND   NOT EXISTS (SELECT resid FROM resource_usage
>                         WHERE itemid=55 AND itemtype='final');
>             -- 4 rows selected:
> RESID 	| 2 	| 3
> --------+-------+-------
> 29      | 55    | final
> 30      | 55    | final
> 31      | 55    | final
> 32      | 55    | final
> 
> On the other hand I can do the insert manually:
> 
>    INSERT INTO resource_usage (resid,itemid,itemtype) VALUES
>       (29,55,'final'),(30,55,'final'),(31,55,'final'),(32,55,'final');
>             -- 4 rows inserted
> 
> Can anybody throw any light on what's going on here please?
> 

Don't have an exact answer but perhaps this will help.  According to the
description of the SYSCONGLOMERATES table, "A conglomerate is a unit of
storage and is either a table or an index."

There may be additional exceptions in the chain that might reveal more
contextual information.  Try iterating over & displaying the
SQLException.getNextException() chain as well as each exception's
getCause() chain.



Mime
View raw message