Thread: getImportedKeys returns only one key
hello all,
i have the following relations defined:
create table users (
UserID serial primary key,
UserName varchar(255) unique not null,
...);
create table Organizations (
OrgID serial primary key,
OrgName varchar(255) unique not null,
...);
create table UserOrgs (
OrgID int not null references organizations(orgid),
UserID int not null references users(userid),
primary key (userid,orgid)
);
and the following bit of code:
Connection cn = DriverManager.getConnection(url, user, pass);
DatabaseMetaData dbmd = cn.getMetaData();
ResultSet rs = dbmd.getImportedKeys(null, null, "userorgs");
while (rs.next()) {
out.println(rs.getString("PKTABLE_NAME"));
}
...which prints only "organizations". In other tests I've repeatedly found
that the ResultSet returned by getImportedKeys never contains more than one
row. I've tested this with the latest stable jdbc driver, the latest
development driver, and with a driver compiled from cvs. I'm currently running
postgresql-7.1.3 on the server. Can anyone give me a hint as to what i'm doing
wrong, or is this a problem with the driver?
Thanks,
Ian Wehrman
--
Labfire, Inc.
Seamless Technical Solutions
http://labfire.com/
Ian,
This must be a 7.1.3 issue, recent messages on the list suggest that it
works fine on 7.2 databases.
Dave
On Sat, 2002-06-01 at 14:12, Ian Wehrman wrote:
> hello all,
> i have the following relations defined:
>
> create table users (
> UserID serial primary key,
> UserName varchar(255) unique not null,
> ...);
>
> create table Organizations (
> OrgID serial primary key,
> OrgName varchar(255) unique not null,
> ...);
>
> create table UserOrgs (
> OrgID int not null references organizations(orgid),
> UserID int not null references users(userid),
> primary key (userid,orgid)
> );
>
>
> and the following bit of code:
>
> Connection cn = DriverManager.getConnection(url, user, pass);
> DatabaseMetaData dbmd = cn.getMetaData();
> ResultSet rs = dbmd.getImportedKeys(null, null, "userorgs");
> while (rs.next()) {
> out.println(rs.getString("PKTABLE_NAME"));
> }
>
> ...which prints only "organizations". In other tests I've repeatedly found
> that the ResultSet returned by getImportedKeys never contains more than one
> row. I've tested this with the latest stable jdbc driver, the latest
> development driver, and with a driver compiled from cvs. I'm currently running
> postgresql-7.1.3 on the server. Can anyone give me a hint as to what i'm doing
> wrong, or is this a problem with the driver?
>
> Thanks,
> Ian Wehrman
>
> --
> Labfire, Inc.
> Seamless Technical Solutions
> http://labfire.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
Ian,
I tried my test code on a 7.1.3 db and it worked fine, one thin I did
notice is that I am specifically creating foreign key constraints. I
don't have time right now to test to see if that makes a difference, but
if you can; let me know
here's an idea how I defined my tables
JDBC2Tests.createTable( con1, "users", "id int4 primary key,
people_id int4, policy_id int4,"+
"CONSTRAINT people FOREIGN KEY
(people_id) references people(id),"+
"constraint policy FOREIGN KEY
(policy_id) references policy(id)" );
Dave
On Sat, 2002-06-01 at 14:12, Ian Wehrman wrote:
> hello all,
> i have the following relations defined:
>
> create table users (
> UserID serial primary key,
> UserName varchar(255) unique not null,
> ...);
>
> create table Organizations (
> OrgID serial primary key,
> OrgName varchar(255) unique not null,
> ...);
>
> create table UserOrgs (
> OrgID int not null references organizations(orgid),
> UserID int not null references users(userid),
> primary key (userid,orgid)
> );
>
>
> and the following bit of code:
>
> Connection cn = DriverManager.getConnection(url, user, pass);
> DatabaseMetaData dbmd = cn.getMetaData();
> ResultSet rs = dbmd.getImportedKeys(null, null, "userorgs");
> while (rs.next()) {
> out.println(rs.getString("PKTABLE_NAME"));
> }
>
> ...which prints only "organizations". In other tests I've repeatedly found
> that the ResultSet returned by getImportedKeys never contains more than one
> row. I've tested this with the latest stable jdbc driver, the latest
> development driver, and with a driver compiled from cvs. I'm currently running
> postgresql-7.1.3 on the server. Can anyone give me a hint as to what i'm doing
> wrong, or is this a problem with the driver?
>
> Thanks,
> Ian Wehrman
>
> --
> Labfire, Inc.
> Seamless Technical Solutions
> http://labfire.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
Hi Dave, I tried Ian's test last night on Cygwin/PostgreSQL 7.2.1 (with the bundled jar), and it still only returns one row... thisis without explicitly naming the constraints (just uses the references clause). The actual query the driver performs returns four rows (an update/delete rule or trigger each for organizations and users),but I'm not sure where the JDBC code is falling down at this stage or what it is basing an imported key as being...the code for getImportedExportedKeys() looks like the spot, and it sounds like it may be because they are '<unnamed>',but I haven't read through the entire code for the method yet. I've attached the query the JDBC driver performsbelow. Cheers, Joe > I tried my test code on a 7.1.3 db and it worked fine, one thin I did > notice is that I am specifically creating foreign key constraints. I > don't have time right now to test to see if that makes a difference, but > if you can; let me know > > > here's an idea how I defined my tables > > JDBC2Tests.createTable( con1, "users", "id int4 > primary key, > people_id int4, policy_id int4,"+ > "CONSTRAINT people FOREIGN KEY > (people_id) references people(id),"+ > "constraint policy FOREIGN KEY > (policy_id) references policy(id)" ); FK query: SELECT c.relname,c2.relname,t.tgconstrname,ic.relname,t.tgdeferrable,t.tginitdeferred,t.tgnargs,t.tgargs,p.proname FROM pg_triggert,pg_class c,pg_class c2,pg_class ic,pg_proc p, pg_index i WHERE t.tgrelid=c.oid AND t.tgconstrrelid=c2.oid ANDt.tgfoid=p.oid AND tgisconstraint AND c2.relname='userorgs' AND i.indrelid=c.oid AND i.indexrelid=ic.oid AND i.indisprimaryORDER BY c.relname, c2.relname
Dave Cramer <Dave@micro-automation.net> wrote: > This must be a 7.1.3 issue, recent messages on the list suggest that it > works fine on 7.2 databases. Dave, This morning I upgraded to 7.2.1, and the behavior remains the same. I've read the recent messages on the list, which is why I'm confused... Are there regression tests in place to check this functionality? Your help is greatly appreciated. Thanks, Ian Wehrman -- Labfire, Inc. Seamless Technical Solutions http://labfire.com/
Dave Cramer <Dave@micro-automation.net> wrote:
> I tried my test code on a 7.1.3 db and it worked fine, one thin I did
> notice is that I am specifically creating foreign key constraints. I
> don't have time right now to test to see if that makes a difference, but
> if you can; let me know
Dave,
Thanks for your help. I did a bit more testing with this hint, and I think
I've narrowed the problem down further. I can verify that your tests work as
expected, getImportedKeys shows all FOREIGN KEYs that are explicitly created.
The difference, I suspect, is that the FORIEGN KEY attribute is a table
constraint, while the syntax i used created a column constraint. I'm not
entirely sure what the difference is, I only glean this from the psql help for
"create table." So, you're right that table constraints are working fine, but
it does seem that only the last column constraint is showing up in
getImportedKeys(). Try adding the following to your test harness, and I
think you'll see that only one of the constraints show up:
JDBC2Tests.createTable(con1, "users", "id int4 primary key, " +
"people_id int4 references people(id), " +
"policy_id int4 references policy(id)");
I can verify (with "\d tablename") that creating either table or column
constraints properly creates the triggers (of the form
RI_ConstraintTrigger_xxxxx), so it seems like these should be returned by
getImportedKeys(). Thanks for helping me find a workaround though, and let me
know if I can help test any futher.
Thanks,
Ian Wehrman
--
Labfire, Inc.
Seamless Technical Solutions
http://labfire.com/