Lists: | pgsql-general |
---|
From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Removing pollution from log files |
Date: | 2007-08-27 11:00:02 |
Message-ID: | faub8i$2ere$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
I'm using PostgreSQL PostgreSQL 8.2.4 from ODBC 08.02.0300 client.
Postgres log files are polluted with messages
2007-08-27 06:10:38 WARNING: nonstandard use of \\ in a string literal at
character 190
2007-08-27 06:10:38 HINT: Use the escape string syntax for backslashes,
e.g., E'\\'.
2007-08-27 06:10:39 WARNING: nonstandard use of \\ in a string literal at
character 197
2007-08-27 06:10:39 HINT: Use the escape string syntax for backslashes,
e.g., E'\\'.
2007-08-27 06:10:47 WARNING: nonstandard use of \\ in a string literal at
character 190
2007-08-27 06:10:47 HINT: Use the escape string syntax for backslashes,
e.g., E'\\'.
2007-08-27 06:10:48 WARNING: nonstandard use of \\ in a string literal at
character 197
2007-08-27 06:10:48 HINT: Use the escape string syntax for backslashes,
e.g., E'\\'.
How to force Postgres not to write those messages to log file ?
Should I configure ODBC driver, Postgres or change my application ?
Andrus.
From: | Andrew Sullivan <ajs(at)crankycanuck(dot)ca> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-27 19:27:46 |
Message-ID: | 20070827192746.GJ30147@phlogiston.dyndns.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, Aug 27, 2007 at 02:00:02PM +0300, Andrus wrote:
> Postgres log files are polluted with messages
>
> 2007-08-27 06:10:38 WARNING: nonstandard use of \\ in a string literal at
> character 190
> 2007-08-27 06:10:38 HINT: Use the escape string syntax for backslashes,
> e.g., E'\\'.
That's not pollution; it's telling you you need to fix your
application to escape the backslashes differently. If you want to
suppress them, though, you can change your logging level to be higher
than "WARNING".
A
--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
"The year's penultimate month" is not in truth a good way of saying
November.
--H.W. Fowler
From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | problem with transactions in VB.NET using npgsql |
Date: | 2007-08-27 19:49:25 |
Message-ID: | p06230901c2f8d9d00cf1@[192.168.0.102] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Maybe someone here can figure it out. Everything updates fine with
this code, except where there's an exception, it's not rolling back
by the transaction. What I'm trying to do:
Begin a transaction
Do the update, insert, delete checks on each of the data tables,
using a different npgsqlcommandbuilder for each of the tables.
Commit
if any failure happens, roll back all the changes to the transaction beginning.
I assign the transaction object to each of the commands, but it seems
that some tables will get updated, even when I call rollback. Is
something I'm calling secretly calling "commit" somewhere?
My code follows. Thanks for checking it over. Sorry about the
length, but I wanted you to see that I'm updating multiple tables
with multiple dataadapters.
-Owen
Option Explicit On
Imports System.Windows.Forms
Imports npgsql
Imports System.Xml.Serialization
Imports System.IO
Imports System.Collections.Generic
Imports System.Configuration
' Note: some controls, in the forms designer, cover other controls,
i.e. CommUsageCB covers styleCB
Public Class ParcelDisplayFrm
Public Sub WriteAllData()
Dim trans As NpgsqlTransaction = Nothing
Dim cmd As NpgsqlCommandBuilder
Dim i As Integer
Dim success As Boolean
Try
If Not statusReadOnly Then
i = vbCancel
success = priceIt(Me, full_DataSet, True, True, pc)
dt = full_DataSet.Tables(currentSchema & ".parcel")
dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".accounts")
dt.Rows(0).EndEdit()
dt = full_DataSet.Tables(currentSchema & ".bldg")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".commcost")
For i = 0 To dt.Rows.Count - 1
dt.Rows(i).EndEdit()
Next i
dt = full_DataSet.Tables(currentSchema & ".outbuildings")
For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("maplot") = Form1.currentMapLot
End If
Debug.Print(dt.Rows.Count)
dt.Rows(i).EndEdit()
Next i
If Not dirtySketch And Not full_DataSet.HasChanges Then
Exit Sub ' Nothing to change
End If
Dim dg As New SaveChangesDlog
If dg.ShowDialog = Windows.Forms.DialogResult.Cancel
Then Exit Sub ' don't save
writeFinalize()
dt = full_DataSet.Tables(currentSchema & ".parcel")
m_SqlConnection.Open()
' create a transaction for the rest of all the changes
trans = m_SqlConnection.BeginTransaction
cmd = New NpgsqlCommandBuilder(parcel_DataAdapter)
Dim parcelchanges As DataTable =
dt.GetChanges(DataRowState.Modified)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
parcel_DataAdapter.UpdateCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Deleted)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
parcel_DataAdapter.DeleteCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
parcelchanges = dt.GetChanges(DataRowState.Added)
If parcelchanges IsNot Nothing Then
parcel_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
parcel_DataAdapter.InsertCommand.Transaction = trans
parcel_DataAdapter.Update(parcelchanges)
End If
' accounts table
cmd = New NpgsqlCommandBuilder(accts_DataAdapter)
dt = full_DataSet.Tables(currentSchema & ".accounts")
Dim acctchanges As DataTable =
dt.GetChanges(DataRowState.Modified)
If acctchanges IsNot Nothing Then
accts_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(0))
accts_DataAdapter.UpdateCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Deleted)
If acctchanges IsNot Nothing Then
accts_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
accts_DataAdapter.DeleteCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
acctchanges = dt.GetChanges(DataRowState.Added)
If acctchanges IsNot Nothing Then
accts_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
accts_DataAdapter.InsertCommand.Transaction = trans
accts_DataAdapter.Update(acctchanges)
End If
' do for every building
dt = full_DataSet.Tables(currentSchema & ".bldg")
If dt.Rows.Count > 0 Then
If dirtySketch Then
For i = currentBuilding To howManyBuildings - 1
returnSketchToDatabase(dt.Rows(0).Item("maplot"), i, trans, Me)
Next i
End If
cmd = New NpgsqlCommandBuilder(bldg_DataAdapter)
' add modified dates
addModDates(dt, "modified")
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim bldgchanges As DataTable =
dt.GetChanges(DataRowState.Deleted)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
bldg_DataAdapter.DeleteCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Modified)
If bldgchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
bldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
bldg_DataAdapter.UpdateCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
bldgchanges = dt.GetChanges(DataRowState.Added)
If bldgchanges IsNot Nothing Then
bldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(0))
bldg_DataAdapter.InsertCommand.Transaction = trans
bldg_DataAdapter.Update(bldgchanges)
End If
End If
dt = full_DataSet.Tables(currentSchema & ".commcost")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(commbldg_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim commBldgChanges As DataTable
commBldgChanges = dt.GetChanges(DataRowState.Deleted)
If commBldgChanges IsNot Nothing Then
commbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
commbldg_DataAdapter.DeleteCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Modified)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
commbldg_DataAdapter.UpdateCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
commBldgChanges = dt.GetChanges(DataRowState.Added)
If commBldgChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
commbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
commbldg_DataAdapter.InsertCommand.Transaction = trans
commbldg_DataAdapter.Update(commBldgChanges)
End If
End If
dt = full_DataSet.Tables(currentSchema & ".outbuildings")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(outbldg_DataAdapter)
For i = 0 To dt.Rows.Count - 1
If dt.Rows(i).RowState = DataRowState.Added Then
dt.Rows(i).Item("MapLot") =
full_DataSet.Tables(currentSchema & ".parcel").Rows(0).Item("MapLot")
End If
Next i
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim outchanges As DataTable
outchanges = dt.GetChanges(DataRowState.Deleted)
If outchanges IsNot Nothing Then
outbldg_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
outbldg_DataAdapter.DeleteCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Modified)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
outbldg_DataAdapter.UpdateCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
outchanges = dt.GetChanges(DataRowState.Added)
If outchanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
outbldg_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
outbldg_DataAdapter.InsertCommand.Transaction = trans
outbldg_DataAdapter.Update(outchanges)
End If
End If
' write changes to sales tables
dt = full_DataSet.Tables(currentSchema & ".sales")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(sales_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesChanges As DataTable
salesChanges = dt.GetChanges(DataRowState.Deleted)
If salesChanges IsNot Nothing Then
sales_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
sales_DataAdapter.DeleteCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Modified)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
sales_DataAdapter.UpdateCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
salesChanges = dt.GetChanges(DataRowState.Added)
If salesChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
sales_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
sales_DataAdapter.InsertCommand.Transaction = trans
sales_DataAdapter.Update(salesChanges)
End If
End If
' write changes to sales overflow table
dt = full_DataSet.Tables(currentSchema & ".salesovflowtype")
If dt.Rows.Count > 0 Then
cmd = New NpgsqlCommandBuilder(salesOF_DataAdapter)
'Debug.Print(ZoningCode.DataBindings.BindableComponent)
Dim salesOFChanges As DataTable
salesOFChanges = dt.GetChanges(DataRowState.Deleted)
If salesOFChanges IsNot Nothing Then
salesOF_DataAdapter.DeleteCommand =
cmd.GetDeleteCommand(dt.Rows(0))
salesOF_DataAdapter.DeleteCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Modified)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.UpdateCommand =
cmd.GetUpdateCommand(dt.Rows(j))
salesOF_DataAdapter.UpdateCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
salesOFChanges = dt.GetChanges(DataRowState.Added)
If salesOFChanges IsNot Nothing Then
Dim j As Integer = 0
While dt.Rows(j).RowState = DataRowState.Deleted
j = j + 1
End While
salesOF_DataAdapter.InsertCommand =
cmd.GetInsertCommand(dt.Rows(j))
salesOF_DataAdapter.InsertCommand.Transaction = trans
salesOF_DataAdapter.Update(salesOFChanges)
End If
End If
trans.Commit()
m_SqlConnection.Close()
dirtySketch = False
BrowserPanel.Refresh()
End If
Catch ex As Exception
MsgBox(" error on writing data " & ex.Message,
MsgBoxStyle.AbortRetryIgnore)
If trans IsNot Nothing Then trans.Rollback()
If m_SqlConnection.State = ConnectionState.Open Then
m_SqlConnection.Close()
End Try
End Sub
End Class
From: | Rainer Bauer <usenet(at)munnin(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-27 20:20:27 |
Message-ID: | b8c6d353mfa2dalfc769g0gm8mff04ksu9@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andrew Sullivan wrote:
>On Mon, Aug 27, 2007 at 02:00:02PM +0300, Andrus wrote:
>> Postgres log files are polluted with messages
>>
>> 2007-08-27 06:10:38 WARNING: nonstandard use of \\ in a string literal at
>> character 190
>> 2007-08-27 06:10:38 HINT: Use the escape string syntax for backslashes,
>> e.g., E'\\'.
>
>That's not pollution; it's telling you you need to fix your
>application to escape the backslashes differently. If you want to
>suppress them, though, you can change your logging level to be higher
>than "WARNING".
Or lookup
<http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html#GUC-STANDARD-CONFORMING-STRINGS>.
Apart from that: there was a bug in the ODBC driver prior 08.02.0402 which
resulted in this error message whenever binary data of type SQL_LONGVARBINARY
was send.
Rainer
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-08-27 23:05:17 |
Message-ID: | 10138.1188255917@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Owen Hartnett <owen(at)clipboardinc(dot)com> writes:
> I assign the transaction object to each of the commands, but it seems
> that some tables will get updated, even when I call rollback. Is
> something I'm calling secretly calling "commit" somewhere?
Dunno anything about vb.net, but this sounds like an autocommit feature
that's not doing what you expect.
If nothing else comes to mind, try setting the DB to log all statements
(see log_statement), and compare the resulting trace to what you think
your code is doing. That should at least narrow it down a lot.
regards, tom lane
From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-08-28 14:14:03 |
Message-ID: | p06230904c2f9de183e5a@[192.168.0.102] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
At 7:05 PM -0400 8/27/07, Tom Lane wrote:
>Owen Hartnett <owen(at)clipboardinc(dot)com> writes:
>> I assign the transaction object to each of the commands, but it seems
>> that some tables will get updated, even when I call rollback. Is
>> something I'm calling secretly calling "commit" somewhere?
>
>Dunno anything about vb.net, but this sounds like an autocommit feature
>that's not doing what you expect.
>
>If nothing else comes to mind, try setting the DB to log all statements
>(see log_statement), and compare the resulting trace to what you think
>your code is doing. That should at least narrow it down a lot.
>
> regards, tom lane
Thanks, I'll give that a try.
-Owen
From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-28 18:03:54 |
Message-ID: | fb1o4n$2kgc$2@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> That's not pollution; it's telling you you need to fix your
> application to escape the backslashes differently.
I havent seen that ODBC specification requires escaping strings.
So this is task of ODBC driver.
Andrus.
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Andrus <kobruleht2(at)hot(dot)ee> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-30 13:31:17 |
Message-ID: | 20070830133117.GH5872@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andrus wrote:
> > That's not pollution; it's telling you you need to fix your
> > application to escape the backslashes differently.
>
> I havent seen that ODBC specification requires escaping strings.
> So this is task of ODBC driver.
So complain to the ODBC guys. OTOH, maybe you are using ODBC wrongly.
--
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"El destino baraja y nosotros jugamos" (A. Schopenhauer)
From: | "Andrus" <kobruleht2(at)hot(dot)ee> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-30 18:48:53 |
Message-ID: | fb73k2$1o0k$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> Apart from that: there was a bug in the ODBC driver prior 08.02.0402 which
> resulted in this error message whenever binary data of type
> SQL_LONGVARBINARY
> was send.
Where is 0402 driver ?
Last downloadable version in 0400 which has this issue.
Andrus.
From: | Rainer Bauer <usenet(at)munnin(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Removing pollution from log files |
Date: | 2007-08-31 07:13:47 |
Message-ID: | 0nffd3926ann2l5crjhba872p4oslq4lq2@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Andrus wrote:
>> Apart from that: there was a bug in the ODBC driver prior 08.02.0402 which
>> resulted in this error message whenever binary data of type
>> SQL_LONGVARBINARY
>> was send.
>
>Where is 0402 driver ?
The snapshot drivers can be found here:
<http://www.geocities.jp/inocchichichi/psqlodbc/index.html>
Rainer
From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 14:35:15 |
Message-ID: | p06230903c3046ed638b2@[192.168.2.176] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
At 10:14 AM -0400 8/28/07, Owen Hartnett wrote:
>At 7:05 PM -0400 8/27/07, Tom Lane wrote:
>>Owen Hartnett <owen(at)clipboardinc(dot)com> writes:
>>> I assign the transaction object to each of the commands, but it seems
>>> that some tables will get updated, even when I call rollback. Is
>>> something I'm calling secretly calling "commit" somewhere?
>>
>>Dunno anything about vb.net, but this sounds like an autocommit feature
>>that's not doing what you expect.
>>
>>If nothing else comes to mind, try setting the DB to log all statements
>>(see log_statement), and compare the resulting trace to what you think
>>your code is doing. That should at least narrow it down a lot.
>>
>> regards, tom lane
>
>Thanks, I'll give that a try.
>
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?
-Owen
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 14:55:45 |
Message-ID: | 20070905145545.GE6102@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Owen Hartnett wrote:
> At 10:14 AM -0400 8/28/07, Owen Hartnett wrote:
>> At 7:05 PM -0400 8/27/07, Tom Lane wrote:
>>> Owen Hartnett <owen(at)clipboardinc(dot)com> writes:
>>>> I assign the transaction object to each of the commands, but it seems
>>>> that some tables will get updated, even when I call rollback. Is
>>>> something I'm calling secretly calling "commit" somewhere?
>>>
>>> Dunno anything about vb.net, but this sounds like an autocommit feature
>>> that's not doing what you expect.
>>>
>>> If nothing else comes to mind, try setting the DB to log all statements
>>> (see log_statement), and compare the resulting trace to what you think
>>> your code is doing. That should at least narrow it down a lot.
>
> 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.
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Owen Hartnett <owen(at)clipboardinc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 15:32:04 |
Message-ID: | 13514.1189006324@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Owen Hartnett <owen(at)clipboardinc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 15:37:44 |
Message-ID: | 20070905153744.GF6102@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
Humm, but can you prepare BEGIN or COMMIT at all?
--
Alvaro Herrera http://www.advogato.org/person/alvherre
"¿Cómo puedes confiar en algo que pagas y que no ves,
y no confiar en algo que te dan y te lo muestran?" (Germán Poo)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Owen Hartnett <owen(at)clipboardinc(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 15:52:01 |
Message-ID: | 13812.1189007521@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> 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?
> Humm, but can you prepare BEGIN or COMMIT at all?
At the protocol level you're allowed to prepare anything at all. It
surely is a bit useless to prepare BEGIN or COMMIT, since there's no
plan and no noticeable parse/analyze costs, but I seem to recall seeing
logs suggesting that some client software does so...
regards, tom lane
From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 16:41:15 |
Message-ID: | p06230900c3048b5269f8@[192.168.2.176] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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.
-Owen
From: | Owen Hartnett <owen(at)clipboardinc(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: problem with transactions in VB.NET using npgsql |
Date: | 2007-09-05 17:14:32 |
Message-ID: | p06230901c304934a4822@[192.168.0.109] |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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