From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | tlange(at)gwdg(dot)de, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2010-05-21 14:47:45 |
Message-ID: | AANLkTilJ-pYLZ7nuSKkpdJ1bwUgdankfw2I9oBa4W_Gg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Fri, May 21, 2010 at 1:15 AM, Craig Ringer
<craig(at)postnewspapers(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Little, Douglas | 2010-05-21 15:23:12 | Access Update error: solution. |
Previous Message | Craig Ringer | 2010-05-21 08:15:37 | Re: |