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

**MGFoster** · Nov 12 '05, 08:07 PM

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

Steve Jorgensen wrote:
[color=blue]
> 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.[/color]

Perhaps you should put the 2 queries inside one transaction?

workspace.begin
run query 1
run query 2
workspace.commi t

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

**Steve Jorgensen** · Nov 12 '05, 08:07 PM

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

On Sat, 07 Feb 2004 00:38:36 GMT, MGFoster <me@privacy.com > wrote:
[color=blue]
>Steve Jorgensen wrote:
>[color=green]
>> 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.[/color]
>
>Perhaps you should put the 2 queries inside one transaction?
>
>workspace.begi n
> run query 1
> run query 2
>workspace.comm it
>
>
>MGFoster:::mgf 00 <at> earthlink <decimal-point> net
>Oakland, CA (USA)[/color]

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** · Nov 12 '05, 08:09 PM

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

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen
<nospam@nospam. nospam> wrote:
[color=blue]
>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.[/color]

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** · Nov 12 '05, 08:10 PM

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

On Sun, 08 Feb 2004 06:39:53 -0500, Mike Sherrill
<MSherrillnonon o@compuserve.co m> wrote:
[color=blue]
>On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen
><nospam@nospam .nospam> wrote:
>[color=green]
>>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.[/color]
>
>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.)[/color]

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** · Nov 12 '05, 08:10 PM

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

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:saac20h8in kfhp6ukg2svp78f 95mril4bc@4ax.c om:
[color=blue]
> 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.[/color]

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** · Nov 12 '05, 08:10 PM

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

On Sun, 08 Feb 2004 19:06:56 GMT, "David W. Fenton"
<dXXXfenton@bwa y.net.invalid> wrote:
[color=blue]
>Steve Jorgensen <nospam@nospam. nospam> wrote in
>news:saac20h8i nkfhp6ukg2svp78 f95mril4bc@4ax. com:
>[color=green]
>> 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.[/color]
>
>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.[/color]

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** · Nov 12 '05, 08:11 PM

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

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:r26d20107p vinkp9m4u05gsbp lgq33fflk@4ax.c om:
[color=blue]
> On Sun, 08 Feb 2004 19:06:56 GMT, "David W. Fenton"
><dXXXfenton@bw ay.net.invalid> wrote:
>[color=green]
>>Steve Jorgensen <nospam@nospam. nospam> wrote in
>>news:saac20h8 inkfhp6ukg2svp7 8f95mril4bc@4ax .com:
>>[color=darkred]
>>> 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.[/color]
>>
>>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.[/color]
>
> 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.[/color]

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** · Nov 12 '05, 08:11 PM

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

On Sun, 08 Feb 2004 21:49:38 GMT, "David W. Fenton"
<dXXXfenton@bwa y.net.invalid> wrote:

....[color=blue][color=green][color=darkred]
>>>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
>>>transactio n because of some disconnect between DAO and what's
>>>actually going on in the back end.
>>>
>>>Just a guess, though.[/color]
>>
>> 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.[/color]
>
>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?[/color]

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** · Nov 12 '05, 08:13 PM

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

On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen <nospam@nospam. nospam>
wrote:
[color=blue]
>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.[/color]

....

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** · Nov 12 '05, 08:13 PM

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

On Mon, 09 Feb 2004 23:48:40 GMT, Steve Jorgensen <nospam@nospam. nospam>
wrote:
[color=blue]
>On Fri, 06 Feb 2004 01:30:19 GMT, Steve Jorgensen <nospam@nospam. nospam>
>wrote:
>[color=green]
>>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.[/color]
>
>...
>
>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
>automaticall y 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.[/color]

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** · Nov 12 '05, 08:14 PM

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

Steve Jorgensen <nospam@nospam. nospam> wrote in
news:0qdg20tgig 9oa1olemf87icqu fug5lfdij@4ax.c om:
[color=blue]
> 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.[/color]

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** · Nov 12 '05, 08:14 PM

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

On Tue, 10 Feb 2004 04:24:15 GMT, "David W. Fenton"
<dXXXfenton@bwa y.net.invalid> wrote:
[color=blue]
>Steve Jorgensen <nospam@nospam. nospam> wrote in
>news:0qdg20tgi g9oa1olemf87icq ufug5lfdij@4ax. com:
>[color=green]
>> 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.[/color]
>
>Two separate connections could not possibly be in the same
>transaction in PostgreSQL, right?[/color]

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.

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment