Thread: Transaction oddities?
Hi. Relative newbie here. I'm using the Perl DBI:ODBC (ActiveState Perl 5.8.0) to work with my psqlodbc DSN (on Windows 2000 SP 4). This generally works fine. However, I'm having some weird behavior that I haven't seen before and can't easily test in an alternate configuration. My script reads information from a Paradox table, formats it, and pushes it into PostgreSQL tables. I have AutoCommit turned off. My script performs a variety of UPDATE and INSERT statements within the same transaction. Then, once finished altering data, it uses a verification routine to compare the results of the operations (by SELECTing from PostgreSQL) to the source data. All of my UPDATE and INSERT statements go through fine. However, when my verify routine tries to SELECT the data they added/changed, it can't see it. What makes this weird is if I change the order of events so the verification of a particular record occurs IMMEDIATELY after the INSERT, the SELECT does see the new data. Ordinarily, the verification occurs only at the end. And of course, the transaction isn't committed or rolled back until after the verification step. Maybe this has nothing to do with psqlodbc, but I can't tell. I've tried using SET TRANSACTION ISOLATION LEVEL READ COMMITTED to no avail (but that's the default anyway, isn't it?) Any ideas or mockery or whatever would be greatly appreciated. Thanks. Ethan Rowe Information Systems Administrator National Braille Press Boston, MA 617-266-6160 ext. 35 erowe@nbp.org
The problem is with the autocommit off and the transaction isolation level you have set. You should not be able to read your changes until after you commit them with this scenario. If you want to do "dirty reads" you need to use read uncommitted. See the following for more information. http://www.postgresql.org/docs/7.4/interactive/transaction-iso.html Ethan Rowe wrote: >Hi. Relative newbie here. > >I'm using the Perl DBI:ODBC (ActiveState Perl 5.8.0) to work with my >psqlodbc DSN (on Windows 2000 SP 4). This generally works fine. >However, I'm having some weird behavior that I haven't seen before and >can't easily test in an alternate configuration. > >My script reads information from a Paradox table, formats it, and pushes >it into PostgreSQL tables. > >I have AutoCommit turned off. My script performs a variety of UPDATE >and INSERT statements within the same transaction. Then, once finished >altering data, it uses a verification routine to compare the results of >the operations (by SELECTing from PostgreSQL) to the source data. > >All of my UPDATE and INSERT statements go through fine. However, when >my verify routine tries to SELECT the data they added/changed, it can't >see it. What makes this weird is if I change the order of events so the >verification of a particular record occurs IMMEDIATELY after the INSERT, >the SELECT does see the new data. Ordinarily, the verification occurs >only at the end. > >And of course, the transaction isn't committed or rolled back until >after the verification step. > >Maybe this has nothing to do with psqlodbc, but I can't tell. I've >tried using SET TRANSACTION ISOLATION LEVEL READ COMMITTED to no avail >(but that's the default anyway, isn't it?) > >Any ideas or mockery or whatever would be greatly appreciated. Thanks. > >Ethan Rowe >Information Systems Administrator >National Braille Press >Boston, MA >617-266-6160 ext. 35 >erowe@nbp.org > > > >---------------------------(end of broadcast)--------------------------- >TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > > > >