Subject: Re: printing results of query to file in different times
To: Mariel Cherkassky <mariel.cherkassky@gmail.com>
References: <634f2eb6-daf6-6314-e6f5-1b531b50a9ec@comcast.net>
 <CA+t6e1kS59uQ7HYSrx-KH2ieFLhJUsnprJ1rGBNPvO1n0YjvbA@mail.gmail.com>
From: George Neuner <gneuner2@comcast.net>
Cc: pgsql-performance@postgresql.org
Message-ID: <62fce205-db63-ed25-c726-d8c442f6223f@comcast.net>
Date: Tue, 5 Sep 2017 10:23:14 -0400
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:52.0) Gecko/20100101
 Thunderbird/52.3.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+t6e1kS59uQ7HYSrx-KH2ieFLhJUsnprJ1rGBNPvO1n0YjvbA@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------DD03C1300D3AAAF0D28A261A"
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------DD03C1300D3AAAF0D28A261A
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Hi Mariel,

Please don't top post in the Postgresql groups.

On 9/5/2017 7:28 AM, Mariel Cherkassky wrote:
> 2017-08-31 16:24 GMT+03:00 George Neuner <gneuner2@comcast.net  > <mailto:gneuner2@comcast.net>>: > >> One thing you might look at is 
the isolation level of the query. >> If you are using READ_COMMITTED or 
less, and the table is busy, >> other writing queries may be stepping on 
yours and even >> potentially changing your result set during the cursor 
loop. I >> would try using REPEATABLE_READ and see what happens. > > 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

--------------DD03C1300D3AAAF0D28A261A
Content-Type: text/html; charset=windows-1252
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html;
      charset=windows-1252">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    Hi Mariel,<br>
    <br>
    Please don't top post in the Postgresql groups.<br>
    <br>
    On 9/5/2017 7:28 AM, Mariel Cherkassky wrote:<br>
    <span style="white-space: pre-wrap; display: block; width: 98vw;">&gt; 2017-08-31 16:24 GMT+03:00 George Neuner <a class="moz-txt-link-rfc2396E" href="mailto:gneuner2@comcast.net">&lt;gneuner2@comcast.net
&gt;</a> <a class="moz-txt-link-rfc2396E" href="mailto:gneuner2@comcast.net">&lt;mailto:gneuner2@comcast.net&gt;</a>&gt;:
&gt; 
&gt;&gt; One thing you might look at is the isolation level of the query.
&gt;&gt; If you are using READ_COMMITTED or less, and the table is busy,
&gt;&gt; other writing queries may be stepping on yours and even
&gt;&gt; potentially changing your result set during the cursor loop.  I
&gt;&gt; would try using REPEATABLE_READ and see what happens.
&gt; 
&gt; I didn't understand what do you mean by REPEATABLE_READ .
</span><br>
    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:<br>
    <br>
      T1:   A reads table X<br>
      T2:   B writes to table X<br>
      T3:   B commits<br>
      T4:   A reads table X again.<br>
    <br>
    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.<br>
    <br>
    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.<br>
    <br>
    <br>
    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.<br>
    <br>
    For more information, see: <br>
    <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/current/static/transaction-iso.html">https://www.postgresql.org/docs/current/static/transaction-iso.html</a><br>
    <a class="moz-txt-link-freetext" href="https://www.postgresql.org/docs/9.6/static/sql-begin.html">https://www.postgresql.org/docs/9.6/static/sql-begin.html</a><br>
<a class="moz-txt-link-freetext" href="https://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures">https://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures</a><br>
    <br>
    George<br>
  </body>
</html>

--------------DD03C1300D3AAAF0D28A261A--