pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Natalie Wenz <nataliewenz@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Cursors
Date: Wed, 15 Aug 2018 16:21:00 -0500
Message-ID: <0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com> (raw)


I have a couple of general questions regarding cursors. 

First, does something like this hold open one transaction, or is each fetch a separate transaction? As a dba I'm wondering if this would interfere with maintenance operations (xid rolling via autovacuum, attaching/detaching partitions, etc).

declare cursname no scroll cursor without hold for ...
fetch forward 50000 from cursname


Secondly, on this particular thing (it's a three-table join, with one of those tables being very "wide", so there's definitely a pg_toast table that's involved besides), it seems to perform pretty terribly. What are the advantages of gathering records this way compared to doing a series of queries with a range (record number >= x and record number < y)? 

Thanks for your help!
Natalie



view thread (2+ messages)  latest in thread

Message-ID: <0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com>
Permalink:  ../0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com/
Also on:    postgresql.org/message-id/0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: nataliewenz@gmail.com
  Subject: Re: Cursors
  In-Reply-To: <0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com>
  References: <0E01B4A9-D944-4300-8A56-FC14DFFAB999@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox