Re: printing results of query to file in different times

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Geez ... I just saw how my last message got mangled.
Trying again.


On 9/5/2017 7:28 AM, Mariel Cherkassky wrote:
I didn't understand what do you mean by REPEATABLE_READ.

I was referring to transaction isolation levels.  When multiple transactions are running concurrently, the DBMS can (or not) prevent them from seeing changes made by one another.  Consider 2 transactions A and B running concurrently:

  T1:   A reads table X
  T2:   B writes to table X
  T3:   B commits
  T4:   A reads table X again.

Depending on the isolation levels [and the specific query, obviously], A may or may not be able to see what changes B made to X.

The default isolation level in Postgresql is READ COMMITTED, which does allow transactions to see committed writes made by concurrently running transactions.  REPEATABLE READ is a higher level of isolation which effectively takes a snapshot of the table(s) when they are 1st read, and guarantees that any further reads (e.g., by cursors) of the tables made by the transaction continue to see the same results.


My thought was that your loop may be running slowly because the table is being changed underneath your cursor.  It may be better to pull the results into a temporary table and run your cursor loop over that.

For more information, see:
https://www.postgresql.org/docs/current/static/transaction-iso.html
https://www.postgresql.org/docs/9.6/static/sql-begin.html
https://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures

George


--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux