Access - ODBC - serial problem...

Hello,

I have made some more tests regarding my precedent post "[ODBC] Access - ODBC - index strange bug", and in fact it is not index-related. The bug is just more visible when an index is used, apparently.

The problem comes from the serial identifier, which is not know from the client at the time the record is being inserted.

With the table described below, imagine I do, from the client:

insert into test (code) VALUES (20);

How does the client know the id that has been given to the record? With ethereal, I could see Access fetches the id by doing a

select id from test where code = 20"

Of course, another record has the same code, and the wrong id is being fetched back. This explains what I have noticed, and that is explained below...

Is there a solution to that, except inserting records with PL-PGSQL, which I already do in some cases, by the way...?

Is that an Access-related problem? Does it work with SQL Server?

Thanks for your feedback...

Philippe

-----Message d'origine-----
De : pgsql-odbc-owner(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de Philippe Lang
Envoyé : mercredi, 7. avril 2004 08:40
À : pgsql-odbc(at)postgresql(dot)org
Objet : Re: [ODBC] Access - ODBC - index strange bug

Hello,

I was able to reproduce the bug, here it is:

---------------------------------
CREATE TABLE public.test
(
id serial PRIMARY KEY,
code int4,
f1 int4,
f2 varchar(50),
f3 text
) WITHOUT OIDS;

CREATE INDEX test_code ON public.test USING btree (code);

INSERT INTO public.test(code, f1, f2, f3) VALUES(10, 1, 'test1', 'ok1');
INSERT INTO public.test(code, f1, f2, f3) VALUES(20, 12, 'test2', 'ok2');
INSERT INTO public.test(code, f1, f2, f3) VALUES(30, 123, 'test3', 'ok3');
---------------------------------

- Link this table to an Access database, with the "Postgresql" normal driver. (Others don't work for me)

- Open the linked table.

- Type 20 in column code of the "insert" line, and insert the record.
All values are mixed up, and the newly inserted line seems to have the same values as the second record. (Because they have the same code?)

- Close the table, and reopen it: the last inserted line has correct values.

- Type 31 in the same column now, things are fine. (Because code 31 was not used before?)

- Take away the index, and the bug disappears. No need to refresh the table links or whatever.

Can anyone reproduce that?

Philippe

My configuration:
-----------------
Postgresql 7.3.4 / 7.4.2
Latest snapshot from psqlodbc.dll
MS Access 2000
MDAC 2.7 / 2.8

-----Message d'origine-----

Hello,

I experience a strange bug when inserting a row into a table in which I have recently added a few indexes.
This table is linked to a MS Access database through ODBC (Latest known snapshot from Hiroshi).

When I insert a row, the serial id and different other columns are completely mixed up. Onscreen, the inserted line inherits some values from past lines (that have similar values), even the primary key! Things get back to normal when I do a complete requery of the table, all values are correct, as they should always have been.

Everything works fine if I delete the indexes.

I made a try with PG 7.3.4 and 7.4.2, same problem.

I have tried playing with the driver options, especially the one related to indexes, without success.

Did anyone have the same problem already?

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly

On Thursday 08 April 2004 13:23, Philippe Lang wrote:
>
> With the table described below, imagine I do, from the client:
>
> insert into test (code) VALUES (20);
>
> How does the client know the id that has been given to the record? With
> ethereal, I could see Access fetches the id by doing a
>
> select id from test where code = 20"
>
> Of course, another record has the same code, and the wrong id is being
> fetched back. This explains what I have noticed, and that is explained
> below...

Do you not have a primary key on your table?

--
Richard Huxton
Archonet Ltd

The problem, as I see it, is that you are creating a table that will
have multiple instances of the same value for code

>CREATE TABLE public.test
>(
> id serial PRIMARY KEY,
> code int4,
> f1 int4,
> f2 varchar(50),
> f3 text
>) WITHOUT OIDS;
>
Your return for the query 'select id where code = 20' will return a
result set, not a singleton response. This will not tell you what the
latest value of id was, only all values. If you normalize your table on
code (i.e., make code a unique value) you will always get a singleton
response to your query. However, you will get an error that you will
have to trap, when you try to insert duplicate values into the table.

My solution, and I work in Delphi, not Access, so I can't tell you how
access will work with this, is to create a function in postgres that
inserts your values and returns currval(''id''). Currval is always the
id you just inserted into the table. You then call the function as a
stored procedure (or whatever its equivalent is in Access), with the
proper parameters, and you get the id you just inserted to work with.

Check the docs for proper syntax on writing the function and using currval.

HTH

Richard Huxton wrote:

>On Thursday 08 April 2004 13:23, Philippe Lang wrote:
>
>
>>With the table described below, imagine I do, from the client:
>>
>>insert into test (code) VALUES (20);
>>
>>How does the client know the id that has been given to the record? With
>>ethereal, I could see Access fetches the id by doing a
>>
>>select id from test where code = 20"
>>
>>Of course, another record has the same code, and the wrong id is being
>>fetched back. This explains what I have noticed, and that is explained
>>below...
>>
>>
>
>Do you not have a primary key on your table?
>
>
>
>

Philippe Lang wrote:

>Hello,
>
>I have made some more tests regarding my precedent post "[ODBC] Access - ODBC - index strange bug", and in fact it is not index-related. The bug is just more visible when an index is used, apparently.
>
>The problem comes from the serial identifier, which is not know from the client at the time the record is being inserted.
>
>With the table described below, imagine I do, from the client:
>
>insert into test (code) VALUES (20);
>
>How does the client know the id that has been given to the record? With ethereal, I could see Access fetches the id by doing a
>
>select id from test where code = 20"
>
>Of course, another record has the same code, and the wrong id is being fetched back. This explains what I have noticed, and that is explained below...
>
>Is there a solution to that, except inserting records with PL-PGSQL, which I already do in some cases, by the way...?
>
>
I would love it if one of the resident Postgres gurus could verify this,
but I think running "select currval('test_id_seq')" ought to do exactly
what you want in this case. It should tell you the most recent id
assigned by the sequence in your session.

Can someone please verify that this command does not suffer races?

Shachar

--
Shachar Shemesh
Lingnu OpenSource Consulting
http://www.lingnu.com/

On Thursday 08 April 2004 17:44, Shachar Shemesh wrote:
> Philippe Lang wrote:
> >
> >Is there a solution to that, except inserting records with PL-PGSQL, which
> > I already do in some cases, by the way...?
>
> I would love it if one of the resident Postgres gurus could verify this,
> but I think running "select currval('test_id_seq')" ought to do exactly
> what you want in this case. It should tell you the most recent id
> assigned by the sequence in your session.
>
> Can someone please verify that this command does not suffer races?

currval() and nextval() are guaranteed safe. What this does mean is that you
can end up with gaps in your sequence though.

--
Richard Huxton
Archonet Ltd

Philippe,

I believe the problem is that when you linked the table in Access, you
selected code as the unique field, instead of the id field. Try unlinking
the table, then relinking, making sure you select the id field (or
combinations of fields) that is unique.

Thanks.

Michael

""Philippe Lang"" <philippe(dot)lang(at)attiksystem(dot)ch> wrote in message
news:6C0CF58A187DA5479245E0830AF84F420802A0(at)poweredge(dot)attiksystem(dot)ch(dot)(dot)(dot)
> Hello,
>
> I have made some more tests regarding my precedent post "[ODBC] Access -
ODBC - index strange bug", and in fact it is not index-related. The bug is
just more visible when an index is used, apparently.
>
> The problem comes from the serial identifier, which is not know from the
client at the time the record is being inserted.
>
> With the table described below, imagine I do, from the client:
>
> insert into test (code) VALUES (20);
>
> How does the client know the id that has been given to the record? With
ethereal, I could see Access fetches the id by doing a
>
> select id from test where code = 20"
>
> Of course, another record has the same code, and the wrong id is being
fetched back. This explains what I have noticed, and that is explained
below...
>
> Is there a solution to that, except inserting records with PL-PGSQL, which
I already do in some cases, by the way...?
>
> Is that an Access-related problem? Does it work with SQL Server?
>
>
> Thanks for your feedback...
>
> Philippe
>
>
> -----Message d'origine-----
> De : pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de Philippe Lang
> Envoy : mercredi, 7. avril 2004 08:40
> : pgsql-odbc(at)postgresql(dot)org
> Objet : Re: [ODBC] Access - ODBC - index strange bug
>
> Hello,
>
> I was able to reproduce the bug, here it is:
>
> ---------------------------------
> CREATE TABLE public.test
> (
> id serial PRIMARY KEY,
> code int4,
> f1 int4,
> f2 varchar(50),
> f3 text
> ) WITHOUT OIDS;
>
> CREATE INDEX test_code ON public.test USING btree (code);
>
> INSERT INTO public.test(code, f1, f2, f3) VALUES(10, 1, 'test1', 'ok1');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(20, 12, 'test2', 'ok2');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(30, 123, 'test3', 'ok3');
> ---------------------------------
>
> - Link this table to an Access database, with the "Postgresql" normal
driver. (Others don't work for me)
>
> - Open the linked table.
>
> - Type 20 in column code of the "insert" line, and insert the record.
> All values are mixed up, and the newly inserted line seems to have the
same values as the second record. (Because they have the same code?)
>
> - Close the table, and reopen it: the last inserted line has correct
values.
>
> - Type 31 in the same column now, things are fine. (Because code 31 was
not used before?)
>
> - Take away the index, and the bug disappears. No need to refresh the
table links or whatever.
>
>
> Can anyone reproduce that?
>
>
> Philippe
>
>
> My configuration:
> -----------------
> Postgresql 7.3.4 / 7.4.2
> Latest snapshot from psqlodbc.dll
> MS Access 2000
> MDAC 2.7 / 2.8
>
>
> -----Message d'origine-----
>
> Hello,
>
> I experience a strange bug when inserting a row into a table in which I
have recently added a few indexes.
> This table is linked to a MS Access database through ODBC (Latest known
snapshot from Hiroshi).
>
> When I insert a row, the serial id and different other columns are
completely mixed up. Onscreen, the inserted line inherits some values from
past lines (that have similar values), even the primary key! Things get back
to normal when I do a complete requery of the table, all values are correct,
as they should always have been.
>
> Everything works fine if I delete the indexes.
>
> I made a try with PG 7.3.4 and 7.4.2, same problem.
>
> I have tried playing with the driver options, especially the one related
to indexes, without success.
>
> Did anyone have the same problem already?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>