[Peter L. Berghold]

On my development box I am running Postgres 8.1.2 and on my target server my provider is running Postgres 7.4.6. When I run the database explorer against my development instance to generate Hibernate configurations (and Pojos) this works just fine as I would expect.

Just (sorta) by accident I ran the same against my “production” environment and I got an internal error while it was generating the artifacts. The “details” button was greyed out so I went to the .log file in my workspace and I spotted the following:

!ENTRY org.eclipse.core.runtime 4 2 2006-04-30 21:40:17.262
!MESSAGE An internal error occurred during: “Generating Artifacts”.
!STACK 0
org.hibernate.cfg.JDBCBinderException: Foreign key name ($1) mapped to different tables! previous: org.hibernate.mapping.Table(public.account) current:org.hibernate.mapping.Table(public.steward)
at org.hibernate.cfg.reveng.JDBCReader.processForeignKeys(JDBCReader.java:160)
at org.hibernate.cfg.reveng.JDBCReader.readDatabaseSchema(JDBCReader.java:85)
at com.genuitec.eclipse.hibernate.wizards.MEJDBCMetaDataConfiguration$1.readDatabaseSchema(MEJDBCMetaDataConfiguration.java:102)
at org.hibernate.cfg.JDBCBinder.readFromDatabase(JDBCBinder.java:87)
at com.genuitec.eclipse.hibernate.wizards.MEJDBCMetaDataConfiguration.readFromJDBC(MEJDBCMetaDataConfiguration.java:129)
at com.genuitec.eclipse.hibernate.wizards.GenerateArtifactsJob$5.execute(GenerateArtifactsJob.java:405)
at org.hibernate.console.execution.DefaultExecutionContext.execute(DefaultExecutionContext.java:35)
at org.hibernate.console.ConsoleConfiguration.execute(ConsoleConfiguration.java:68)
at com.genuitec.eclipse.hibernate.wizards.GenerateArtifactsJob.buildConfiguration(GenerateArtifactsJob.java:400)
at com.genuitec.eclipse.hibernate.wizards.GenerateArtifactsJob.run(GenerateArtifactsJob.java:266)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:76)

The schemas between development and production are identical since I sync them using the same sql scripts.

Is this a bug? Feature? Elvis?

[Haris Peco]

Hello,

I suppose that Postgresql have some changes between releases related with foreign key names, but I can’r conclude without schema (your sql script or at least all relations with steward and account).

Can you try without table steward or table account ?

Thanks

[Peter L. Berghold]

I get pretty much the same results without them. Here are the scripts that generate those tables::

drop sequence account_id_seq cascade;
create sequence account_id_seq;

drop table account cascade;
create table account (
        account_id              integer not null default nextval('account_id_seq') primary key,
        person_id               integer not null
                                                references person(person_id)
                                                on delete cascade
                                                on update cascade,
        login                   varchar(15) not null,
        passwd                  varchar(50) not null,
        active                  boolean not null default 'true',
        locked                  boolean not null default 'false'
);



drop sequence account_audit_log_entry_id_seq cascade;
create sequence account_audit_log_entry_id_seq;

drop table account_audit_log cascade;
create table account_audit_log (
        account_audit_log_entry_id      integer not null
                                        default
                                nextval('account_audit_log_entry_id_seq')
                                        primary key,
        account_id              integer not null
                                        references account(account_id)
                                        on delete cascade
                                        on update cascade,
        entry                   varchar(128) not null,
        event_time              timestamp not null default now()
);

create or replace function update_account_audit() returns trigger as '
BEGIN
        if tg_op = ''INSERT''
        then
                insert into account_audit_log(account_id,entry)
                values (NEW.account_id,''created account'');
                return null;
        end if;
        if tg_op = ''UPDATE''
        then
                insert into account_audit_log(account_id,entry)
                values (NEW.account_id,''updated acount'');
                return null;
        end if;


END
' language 'plpgsql';

create trigger account_audit_trigger after insert or update or delete
on account for each row execute procedure update_account_audit();




create or replace function find_accountid_by_login(text) returns integer as '
DECLARE
        lid alias for $1;
        aid integer;
BEGIN
        select into aid account_id from account where login = lid;
        if not found
        then
                return -1;
        end if;
        return aid;
END
' language 'plpgsql';

drop sequence steward_id_seq cascade;
drop sequence job_id_seq cascade;
drop sequence job_mapping_id_seq cascade;
create sequence steward_id_seq;
create sequence job_id_seq;
create sequence job_mapping_id_seq;

drop table job cascade;
create table job (
        job_id                  integer not null default nextval('job_id_seq') primary key,
        name                    varchar(120) not null
);

insert into job (name) values ('Gate Steward');
insert into job (name) values ('Scribe');
insert into job (name) values ('Timer');
insert into job (name) values ('Assistant Scribe');
insert into job (name) values ('Score Runner');
insert into job (name) values ('Ring Crew');
insert into job (name) values ('Ring Captain');
insert into job (name) values ('Course Builder');
insert into job (name) values ('Chief Course Builder');
insert into job (name) values ('Volunteer Coordinator');

create or replace function find_jobid_byname(TEXT) returns int as '
DECLARE
        jname alias for $1;
        retval integer;
BEGIN
        select into retval job_id from job where name = jname;
        if not found
        then
                return -1;
        end if;
        return retval;
END
' language 'plpgsql';

drop table steward cascade;
create table steward (
        steward_id              integer not null default nextval('steward_id_seq') primary key,
        person_id               integer not null
                                                references person(person_id)
                                                on delete cascade
                                                on update cascade
);

create or replace function add_steward_by_name (text,text) returns int as '
DECLARE
        gn alias for $1;
        sn alias for $2;
        retval integer;
        pid    integer;
BEGIN

        pid = find_personid_by_name(gn,sn);
        if  pid = -1
        then
                return -1;
        end if;
        select into retval steward_id from steward where person_id = pid;
        if not found
        then

                insert into steward(person_id) values (pid);
                select into retval steward_id from steward where person_id = pid;
        end if;
        return retval;

END
' language 'plpgsql';

drop table job_map cascade;
create table job_map (
        job_mapping_id  integer not null default nextval('job_mapping_id_seq') primary key,
        job_id                  integer not null
                                                references job(job_id)
                                                on delete cascade
                                                on update cascade,
        perdiem_rate    money not null default '$100.00',
        preference_level integer not null default 0,
        barter_entry     boolean not null default false,
        comments         text
);

create or replace function map_job_by_name(text,text,text,money,int,boolean,text) returns int as '
DECLARE
        gn alias for $1;
        sn alias for $2;
        jn alias for $3;
        pdr alias for $4;
        pl  alias for $5;
        barter alias for $6;
        comment alias for $7;
BEGIN

END
' language 'plpgsql';

The “test” and “prodcution” databases are synced by invoking psql -f {filename} trialdb agilitys and passing in the password as asked for. (Actually… I cheat and have a .pgpass file so the shell script that invokes the sql files can run unaided). I’m letting the respective databases name the foriegn keys on their own.

[Haris Peco]

Peter,
If you see same fk names then jdbc driver make troubles; if you use same jdbc driver then server protocol return different data to driver (postgreslq changed protcol from version 7 to 8)

if you use different jdbc driver then you try with same (newer) for both databases – new driver support back to <= 7.2

MyEclipse use jdbc info for mapping only, and this error tell that driver return sampe foreign key name for 2 relations.I remember old jdbc bugs for imported (foreign key) and exported keys, but i don’t sure for what version.

However, you can use mapping files which you created on ver. 8 for version 7.You haven’t to create mapping files for every database separate.

Best regards

[Author]

Posts