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.
signature.asc
Description: This is a digitally signed message part
