On Fri, May 21, 2010 at 1:15 AM, Craig Ringer
<cr...@postnewspapers.com.au> wrote:
> Really?
>
> I had problems with Access complaining that the object it just inserted had
> vanished, because the primary key Access had in memory (null) didn't match
> what was stored (the generated PK). I had to fetch the next value in the PK
> sequence manually and store it in Access's record before inserting it to
> work around this.
Trust me, I've felt your pain... In fact, I began to exclusively use
natural primary keys just to avoid this problem.
However, after I've noticed that after 8.3 this problem went away.
Here is a sample of what my postgres log shows:
2010-05-21 07:28:38 PDTLOG: BEGIN; INSERT INTO
"public"."actionitems" ("action","startdate","completiondate")
VALUES (E'Test
Action','2010-05-21'::date,'9999-12-31'::date)
2010-05-21 07:28:38 PDTLOG: statement: COMMIT
/* Now MS-Access requeries to find the newly inserted record. But
since we didn't specify the serial field 'itemnbr' MS-Access still
thinks its NULL. */
2010-05-21 07:28:38 PDTLOG: statement: SELECT
"itemnbr","action","startdate","completiondate"
FROM "public"."actionitems"
WHERE "itemnbr" IS NULL
/* Here is where MS-Access usually chokes since itemnbr is a serial
and IS NOT NULL. It thinks our serial primary key is null since it
doesn't know know that it can auto-increment. But notice what happens
next that fixes this problem, either this is a new feature of Access
2003 or the >= 8.3 ODBC driver (I'm using pg 8.4 here ). */
2010-05-21 07:28:38 PDTLOG: statement: SELECT "public"."actionitems"."itemnbr"
FROM "public"."actionitems"
WHERE "startdate" = '2010-05-21'::date
AND "completio ndate" =
'9999-12-31'::date
/* The table was automatically re-queried to find out what the new
itemnbr actually is according to its default value. And lastly the
former query that failed is re-tried with the newly discovered
itemnbr. */
2010-05-21 07:28:38 PDTLOG: statement: SELECT
"itemnbr","action","startdate","completiondate"
FROM "public"."actionitems"
WHERE "itemnbr" = 49
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
