Lists: | pgsql-odbc |
---|
From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ? |
Date: | 2009-08-13 09:34:28 |
Message-ID: | 4A83DE24.2030104@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
Hi,
I've got an Access-App using PG 8.4 in a LAN.
The systems dude said he updated to odbc 8.400 on all clients.
Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.
Among others, there are 2 tables sitting in a schema.
Their primary keys are sequences.
I link those tables into access and the keys are shown as autovalue as
expected.
When Access tries to add a record, it complains that the relation
"versandinfos_versandinfo_id_seq" wouldn't exist and throws an error #7.
There are 2 solutions.
1) I move those tables to the public schema and relink within access.
2) I remove the sequence as default ... and reset it again at once.
That makes Access happy without relink.
Like:
ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT 0;
ALTER TABLE versand.versandinfos ALTER COLUMN versandinfo_id SET DEFAULT
nextval('versand.versandinfos_versandinfo_id_seq'::regclass);
I can't point a finger to where this issue roots.
It's obviously no permission problem since I can enter records all the
time with psql or pgAdmin.
It appears when I used ALTER TABLE SET SCHEMA.
The recent "outbreak" might be triggered by a pg_restore since I sat up
a new db-machine yesterday and reimported the whole database.
From: | "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)ch> |
---|---|
To: | "Andreas" <maps(dot)on(at)gmx(dot)net>, <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ? |
Date: | 2009-08-13 11:36:54 |
Message-ID: | E6A0649F1FBFA3408A37F505400E7AC21F8625@email.attiksystem.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
pgsql-odbc-owner(at)postgresql(dot)org wrote:
> Hi,
>
> I've got an Access-App using PG 8.4 in a LAN.
> The systems dude said he updated to odbc 8.400 on all clients.
> Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.
>
> Among others, there are 2 tables sitting in a schema.
> Their primary keys are sequences.
> I link those tables into access and the keys are shown as autovalue
> as expected.
>
> When Access tries to add a record, it complains that the relation
> "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error
> #7.
Hi,
Have you checked if your ODBC user has enough rights on your sequence
"versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that.
Regards,
-----------------------------------------------------------------------
Philippe Lang Web : www.attiksystem.ch
Attik System Email : philippe(dot)lang(at)attiksystem(dot)ch
rte de la Fonderie 2 Phone : +41 26 422 13 75
1700 Fribourg Mobile : +41 79 351 49 94
Switzerland Fax : +41 26 422 13 76
From: | Andreas <maps(dot)on(at)gmx(dot)net> |
---|---|
To: | Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> |
Cc: | pgsql-odbc(at)postgresql(dot)org |
Subject: | Re: Is there a mysthical problem with psqlODBC + MSAccess + sequences IN SCHEMAS ? |
Date: | 2009-08-13 15:54:51 |
Message-ID: | 4A84374B.80302@gmx.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-odbc |
Philippe Lang schrieb:
> pgsql-odbc-owner(at)postgresql(dot)org wrote:
>
>> Hi,
>>
>> I've got an Access-App using PG 8.4 in a LAN.
>> The systems dude said he updated to odbc 8.400 on all clients.
>> Though the same issue appeared with PG 8.3.7 and odbc 8.2.5 before.
>>
>> Among others, there are 2 tables sitting in a schema.
>> Their primary keys are sequences.
>> I link those tables into access and the keys are shown as autovalue
>> as expected.
>>
>> When Access tries to add a record, it complains that the relation
>> "versandinfos_versandinfo_id_seq" wouldn't exist and throws an error
>> #7.
>>
>
> Hi,
>
> Have you checked if your ODBC user has enough rights on your sequence
> "versandinfos_versandinfo_id_seq"? You can use pgAdmin to check that
>
By desperation I made GRANT ... TO public.
This can't be the solution since I don't touch the sequence in my
workaround.
I just set the default of the key twice. This affects only the one
collumn in the table but not the seq.
I found this accidently when I wanted to reduce the error sources some
time ago bye cutting the sequence off.
As it worked with manually finding the next key, I reattached the
sequence as before and expected to get the error again but it was gone.
There must be a interference between odbc/access and sequences within
schemas.
As I mentioned. The problem goes away when I move the table (inluding
the seq.) to the public schema and reappeares when I push it in the
schema again.
Obviously some element in the dataaccess chain "forgets" to store or use
the schema to address the seq.