Thread: getImportedKeys returns only one key

getImportedKeys returns only one key

From
Ian Wehrman
Date:
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/

Re: getImportedKeys returns only one key

From
Dave Cramer
Date:
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
>
>




Re: getImportedKeys returns only one key

From
Dave Cramer
Date:
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
>
>




Re: getImportedKeys returns only one key

From
"Joe Shevland"
Date:
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 


Re: getImportedKeys returns only one key

From
Ian Wehrman
Date:
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/

Re: getImportedKeys returns only one key

From
Ian Wehrman
Date:
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/