Re: - Mailing list pgsql-sql

On Fri, May 21, 2010 at 1:15 AM, Craig Ringer
<craig@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