Re: problem with transactions in VB.NET using npgsql

At 12:41 PM -0400 9/5/07, Owen Hartnett wrote:
>At 11:32 AM -0400 9/5/07, Tom Lane wrote:
>>Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>>> Owen Hartnett wrote:
>>>> I've been able to turn on statement logging (I've set log_statement to
>>>> 'all'), but it doesn't seem to show the begin transaction - commit -
>>>> rollback statements. Is there another way to have them show up
>>>>in the log?
>>
>>> If they don't show up, they are not being executed at all; which
>>> explains why your transactions "don't roll back", because there are no
>>> transaction blocks defined at all.
>>
>>In PG 8.2 I'd agree, but older versions are not so good about logging
>>execution of prepared statements. What's the server version exactly,
>>and is there any indication of use of prepared statements in the log?
>>
>> regards, tom lane
>
>That's what I thought at first, but then I went to pgAdmin's SQL
>page and typed in "Begin" and ran it. No log shows up, but when I
>run it again from pgAdmin (and from my application), I get a "no
>nested transactions allowed" error (which I know Npgsql doesn't
>support), which indicates there's a transaction there which is not
>getting logged.
>
>If I run transactions with ADO's normal Execute* functions, they
>work fine. It's when I'm trying to use the dataset write back
>routines, with a NpgsqlTransaction and a NpgsqlCommandBuilder using
>Update, Insert and Delete commands on multiple tables one after
>another that it behaves as if the transaction isn't there, though it
>blithely accepts the begintransaction and commit commands without
>complaining.
>
>I'm running the latest, 8.2.4. The server is Mac OS X Server and
>Windows XP (of course) clients running the ADO software and npgsql
>stuff.
>

Looking at the problem some more - I've been looking at the logs for
some updates - what I do is update most of the tables with the ADO
dataset writeback (as used in the Mcmanus & Goldstein's book:
"Database Access with Visual Basic .NET"), but I use a simple
ExecuteNoQuery to update another table, all wrapped up in the same
transaction. The transaction affects the ExecuteNoQuery updates, but
doesn't affect the ADO DataSet writebacks. In fact, in the logfiles,
for each logline, it prints "IDLE in transaction" on the
ExecuteNoQuery log statement, but just "IDLE" on the other updates.
It's almost like the ADO has an independent path, even though I'm
assigning the same NpgsqlTransaction object to it as I am to the
ExecuteNoQuery command.

I suppose I'll have to be stepping through that Npgsql code to see
just what's going on.

-Owen