Обсуждение: Bug in Migration Wizard 2.4.12 with 1-1 relationships

I am trying to use the the Migration Wizard (version 2.4.12) to export a
simple Access database to Pgsql. I notice that the migration wizard fails,
if I have defined 1-1 relationships inside MS Access.

Here is the error log from the wizard:

>Creating table: address_table
>  Copying data...
>  Records Copied: 1
>Creating index: id
>Creating table: name_table
>  Copying data...
>  Records Copied: 1
>Creating index: id
>Creating Foreign Key: address_tablename_table
>
>An error occured at: 21/02/2003 10:25:58 PM:
>-2147467259: ERROR:  parser: parse error at or near "("
>
>Rolling back... Done.

Here is the last bit of the postgres log from the server:

>NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>'address_table_pkey
>' for table 'address_table'
>NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
>'name_table_pkey' f
>or table 'name_table'
>NOTICE:  ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
>ERROR:  parser: parse error at or near "("
>NOTICE:  ROLLBACK: no transaction in progress

I have attached a very simple test case .mdb file which triggers the bug.

I use MS Access 2002 SP2 and PostgreSQL 7.2.

- Rohan Carly.

It's rumoured that Rohan Carly once said:
> I am trying to use the the Migration Wizard (version 2.4.12) to export
> a simple Access database to Pgsql. I notice that the migration wizard
> fails, if I have defined 1-1 relationships inside MS Access.

Hi,

Please try the latest snapshot from the binaries folders at
http://cvs.pgadmin.org/. Instructions are in the readme - you will need to
upgrade all files, not just the migration wizard.
PostgreSQL (and most DBMS') do not refer to foreign keys as relationship
types quite as Access does, so there is no real concept of a 1:1 key as
such. To enforce that, you will need to manually add a unique index on the
fkey column.
Please let me know if you have any trouble.

Regards, Dave.

>     * From: "Dave Page" <dpage@vale-housing.co.uk>
> It's rumoured that Rohan Carly once said:
>> I am trying to use the the Migration Wizard (version 2.4.12) to export
>> a simple Access database to Pgsql. I notice that the migration wizard
>> fails, if I have defined 1-1 relationships inside MS Access.
>
> Please try the latest snapshot from the binaries folders at
> http://cvs.pgadmin.org/. Instructions are in the readme - you will need to
> upgrade all files, not just the migration wizard.
> PostgreSQL (and most DBMS') do not refer to foreign keys as relationship
> types quite as Access does, so there is no real concept of a 1:1 key as
> such. To enforce that, you will need to manually add a unique index on the
> fkey column.
> Please let me know if you have any trouble.

Hi Dave! Hi Group!

I used to experience the same troubles with 1:1 relationships, and I am
very happy to say that your fix did it. I was able to export a complex
data structure with 36 tables including several 1:1 relationships from
Access 97 without a problem. Previously the migration used to stop and
roll back when hitting the first foreign key from a 1:1 relationship.

BTW, I agree with the other guy that pleaded for a "per table commit".
Did I see a new option in the migration wizard doing exactly that allready?

Thanx a lot for the tool and especially for the latest fixes. I
appreciate it. Good job!

Regards
Erwin Brandstetter

> -----Original Message-----
> From: Erwin Brandstetter [mailto:a9006241@unet.univie.ac.at]
> Sent: 24 February 2003 04:40
> To: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] Bug in Migration Wizard 2.4.12
> with 1-1 relationships
>
> Hi Dave! Hi Group!
>
> I used to experience the same troubles with 1:1
> relationships, and I am
> very happy to say that your fix did it. I was able to export
> a complex
> data structure with 36 tables including several 1:1
> relationships from
> Access 97 without a problem. Previously the migration used to
> stop and
> roll back when hitting the first foreign key from a 1:1 relationship.

Good news!

> BTW, I agree with the other guy that pleaded for a "per table
> commit".
> Did I see a new option in the migration wizard doing exactly
> that allready?

Yes, there is an option for per table commits now. It's on the table
selection page.

> Thanx a lot for the tool and especially for the latest fixes. I
> appreciate it. Good job!

You're welcome.

Regards, Dave.