public inbox for [email protected]
help / color / mirror / Atom feedFrom: George Neuner <[email protected]>
To: Mariel Cherkassky <[email protected]>
Cc: [email protected]
Subject: Re: printing results of query to file in different times
Date: Tue, 5 Sep 2017 13:12:24 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+t6e1kS59uQ7HYSrx-KH2ieFLhJUsnprJ1rGBNPvO1n0YjvbA@mail.gmail.com>
References: <[email protected]>
<CA+t6e1kS59uQ7HYSrx-KH2ieFLhJUsnprJ1rGBNPvO1n0YjvbA@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: printing results of query to file in different times
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox