PostgreSQL back-end. Ref. integrity violation in transaction.

[Steve Jorgensen]

Hi all,

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It's as if my 2 inserts are not using the same connection or something, so my
second insert doesn't "see" the first one since it's not committed yet. All
statements are executed via the same DAO workspace. I'm using DAO because I'm
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it'll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Thanks for any advice anyone has,

- Steve J.

[MGFoster]

Steve Jorgensen wrote:

Hi all,

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It's as if my 2 inserts are not using the same connection or something, so my
second insert doesn't "see" the first one since it's not committed yet. All
statements are executed via the same DAO workspace. I'm using DAO because I'm
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it'll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Perhaps you should put the 2 queries inside one transaction?

workspace.begin
run query 1
run query 2
workspace.commit
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[Steve Jorgensen]

On Sat, 07 Feb 2004 00:38:36 GMT, MGFoster <[e-mail]> wrote:

Steve Jorgensen wrote:

Hi all,

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It's as if my 2 inserts are not using the same connection or something, so my
second insert doesn't "see" the first one since it's not committed yet. All
statements are executed via the same DAO workspace. I'm using DAO because I'm
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

Note that I am using recordsets to do these inserts because this was the
easiest way to get the new AutoNumber keys when JET was the back-end, but I
can change that if it'll help, and just use .Execute since I now have to use a
pass-through query to get the new SERIES values with the PostgreSQL back-end,
anyway.

Perhaps you should put the 2 queries inside one transaction?

workspace.begin
run query 1
run query 2
workspace.commit
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

I am doing everything inside one transaction already, unless Access is
thwarting this somehow by, say, opening a second connection for the recordset.

[Mike Sherrill]

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned.

Odds are good that you need to either commit the first insert before
executing the second (two transactions), or switch to SQL and deferred
constraint checking. (I'm not sure whether PostgreSQL supports
deferred constraints--you'd have to check the docs.)

--
Mike Sherrill
Information Management Systems

[Steve Jorgensen]

On Sun, 08 Feb 2004 06:39:53 -0500, Mike Sherrill
<MS*************@compuserve.com> wrote:

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen
<no****@nospam.nospam> wrote:

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned.

Odds are good that you need to either commit the first insert before
executing the second (two transactions), or switch to SQL and deferred
constraint checking. (I'm not sure whether PostgreSQL supports
deferred constraints--you'd have to check the docs.)

I'm not sure why that should be necessary. Within the transaction, the
records are inserted in proper dependency order. I thought PostgreSQL was
high-end enoughto do this sort of thing properly.

In any case, I'm going to see if using SQL updates instead of recordsets
solves the problem. I've pretty much written the code to try that, but I
haven't tested it yet.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:sa********************************@4ax.com:

Within the transaction, the
records are inserted in proper dependency order. I thought
PostgreSQL was high-end enoughto do this sort of thing properly.

In any case, I'm going to see if using SQL updates instead of
recordsets solves the problem. I've pretty much written the code
to try that, but I haven't tested it yet.

My (uneducated) guess is that this is one of those places where Jet
(via DAO) is getting the way of processing on the back end. That is,
DAO thinks it's holding the transaction open, but for some reason,
the two updates are not occurring in the same open transaction
because of some disconnect between DAO and what's actually going on
in the back end.

Just a guess, though.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Sun, 08 Feb 2004 19:06:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:sa********************************@4ax.com :

Within the transaction, the
records are inserted in proper dependency order. I thought
PostgreSQL was high-end enoughto do this sort of thing properly.

In any case, I'm going to see if using SQL updates instead of
recordsets solves the problem. I've pretty much written the code
to try that, but I haven't tested it yet.

My (uneducated) guess is that this is one of those places where Jet
(via DAO) is getting the way of processing on the back end. That is,
DAO thinks it's holding the transaction open, but for some reason,
the two updates are not occurring in the same open transaction
because of some disconnect between DAO and what's actually going on
in the back end.

Just a guess, though.

Something like that, perhaps. I know at least the first group of updates is
properly within the transaction, though (unless it's being prematurely rolled
back) because that code completes without error, then after the error handler
rolls the transaction back because of the inexplicable dependency error in the
second group, the updates made in the first group do not appear in the
database.

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:r2********************************@4ax.com:

On Sun, 08 Feb 2004 19:06:56 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:sa********************************@4ax.co m:

Within the transaction, the
records are inserted in proper dependency order. I thought
PostgreSQL was high-end enoughto do this sort of thing properly.

In any case, I'm going to see if using SQL updates instead of
recordsets solves the problem. I've pretty much written the
code to try that, but I haven't tested it yet.

My (uneducated) guess is that this is one of those places where
Jet (via DAO) is getting the way of processing on the back end.
That is, DAO thinks it's holding the transaction open, but for
some reason, the two updates are not occurring in the same open
transaction because of some disconnect between DAO and what's
actually going on in the back end.

Just a guess, though.

Something like that, perhaps. I know at least the first group of
updates is properly within the transaction, though (unless it's
being prematurely rolled back) because that code completes without
error, then after the error handler rolls the transaction back
because of the inexplicable dependency error in the second group,
the updates made in the first group do not appear in the database.

Is there, perhaps, something that needs to be refreshed in between?
Maybe the tabledefs collection? My thinking here is that perhaps
there's some kind of metadata that is used in the RI checking.

It's a longshot, definitely, but, hey, anything could help!

BTW, have you tried it with SQL yet?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Sun, 08 Feb 2004 21:49:38 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

....

My (uneducated) guess is that this is one of those places where
Jet (via DAO) is getting the way of processing on the back end.
That is, DAO thinks it's holding the transaction open, but for
some reason, the two updates are not occurring in the same open
transaction because of some disconnect between DAO and what's
actually going on in the back end.

Just a guess, though.

Something like that, perhaps. I know at least the first group of
updates is properly within the transaction, though (unless it's
being prematurely rolled back) because that code completes without
error, then after the error handler rolls the transaction back
because of the inexplicable dependency error in the second group,
the updates made in the first group do not appear in the database.

Is there, perhaps, something that needs to be refreshed in between?
Maybe the tabledefs collection? My thinking here is that perhaps
there's some kind of metadata that is used in the RI checking.

It's a longshot, definitely, but, hey, anything could help!

BTW, have you tried it with SQL yet?

Hey, good thought. I already have code I wrote to upsize the tables, indexes,
and relationships from Jet to PostgreSQL. The changes to make it generate
T-SQL as well would be trivial. If my next test doesn't work, I'll try that.

[Steve Jorgensen]

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

Hi all,

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It's as if my 2 inserts are not using the same connection or something, so my
second insert doesn't "see" the first one since it's not committed yet. All
statements are executed via the same DAO workspace. I'm using DAO because I'm
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

....

It turns out that my problem went away when I saved the password in my table
links. What would seem to have been happening is that Access doesn't
automatically use the defaults in the DSN when connecting to the back-end, and
I had saved the logon name and password in the DSN, but not in the links.
Access kept trying to connect as Admin (the Access user) first, before trying
again as the correct user. Since the previous connection was as a diffent
user than the first user name it wanted to try for the next command, Access
figured it had to open a second connection.

Now, I just have to see if this fix also fixed the previous quirks I already
added work-arounds for.

[Steve Jorgensen]

On Mon, 09 Feb 2004 23:48:40 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen <no****@nospam.nospam>
wrote:

Hi all,

I've migrated an Access database schema to PostgreSQL, and I'm trying to get
everything working again. I'm having the following problem...

Within an Access transaction, I insert a row into one table, then I insert a
row into a table with a foreign key relationship to the first record. The
second insert fails with a referential integrity violation referring to the
same relationhip I mentioned. This worked fine with a JET back-end and the
same relationships.

It's as if my 2 inserts are not using the same connection or something, so my
second insert doesn't "see" the first one since it's not committed yet. All
statements are executed via the same DAO workspace. I'm using DAO because I'm
dealing with heterogenious data, doing things like inserting into PostgreSQL
tables based on selects from data in client-side JET tables.

...

It turns out that my problem went away when I saved the password in my table
links. What would seem to have been happening is that Access doesn't
automatically use the defaults in the DSN when connecting to the back-end, and
I had saved the logon name and password in the DSN, but not in the links.
Access kept trying to connect as Admin (the Access user) first, before trying
again as the correct user. Since the previous connection was as a diffent
user than the first user name it wanted to try for the next command, Access
figured it had to open a second connection.

Now, I just have to see if this fix also fixed the previous quirks I already
added work-arounds for.

Damn, not quite. That fix made the test pass, but the production code still
fails. I've verified that the code uses only one workspace for all
operations, and I've even made sure the code uses only SQL for updates, and
only snapshot recordsets, just to eliminate variables. The administrator
tells me that the code is still trying to do at least 1 logon as Admin with a
blank password, even though I have user name and password explicitly in the
connect string everywhere, and Access is definitely trying to use 2 separate
connections for the 2 insert statements for no obvious reason.

At leat, I now have some idea what's going on, but now, how do I convince
Access to use just one connection so the transaction will work properly?

[David W. Fenton]

Steve Jorgensen <no****@nospam.nospam> wrote in
news:0q********************************@4ax.com:

The administrator
tells me that the code is still trying to do at least 1 logon as
Admin with a blank password, even though I have user name and
password explicitly in the connect string everywhere, and Access
is definitely trying to use 2 separate connections for the 2
insert statements for no obvious reason.

Two separate connections could not possibly be in the same
transaction in PostgreSQL, right?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve Jorgensen]

On Tue, 10 Feb 2004 04:24:15 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

Steve Jorgensen <no****@nospam.nospam> wrote in
news:0q********************************@4ax.com :

The administrator
tells me that the code is still trying to do at least 1 logon as
Admin with a blank password, even though I have user name and
password explicitly in the connect string everywhere, and Access
is definitely trying to use 2 separate connections for the 2
insert statements for no obvious reason.

Two separate connections could not possibly be in the same
transaction in PostgreSQL, right?

Right, nor in Microsoft SQL Server, nor, to my knowledge in any server when
ODBC is the interface. To solve this problem, I'll have to solve why Access
is creating an extra connection in this case.

The strange thing is, when I trace the code, it's doing nothing with the
connection within the problem section that I'm not also doing in my test
routine that is not displaying the symptom.