I am about to start using transactions in PSQL 9.5 for the first time.
Are there any limitations on the number of tables or the number of rows
or the number of changes that can be involved in a transaction? Thanks.

--
..Bill.

On Feb 21, 9:08 am, "Bill" <n... (AT) no (DOT) com> wrote:

There are no practical limits. The only limit that I know of is
memory. The MKDE still requires all pages modified inside a user
transaction to reside in cache until the transaction completes. You
may receive a Status 100 if the MKDE can't allocate any more cache
buffers to accommodate these modified pages. With v8 and v9x, cache
allocation can be set up to be managed dynamically, so if you get this
status code with v9.5, your choices are to add more memory to the
server or to re-architect your transactions.

This isn't a precise limitation, as you can tell. It depends on how
large the rows are, how large the table sizes are of all the tables in
the transaction, and so on.

Wayne Freeman
Analytica Business Systems
http://www.analyticabiz.com
wfreeman at theabovedomain dot com

Thanks for the detailed explanation. I gather from what you said about
modified pages being held in memory that locking is done at the page
level since it would be impossible to have two transaction modify
different records on the same page then have one transaction commit and
the other roll back.

--
..Bill.

FYI, I finally had to open a support ticket to get answers to my
questions about transactions and ODBC. I asked about the limitations
imposed by caching changed pages in memory and was told that the
changed pages are not cached in memory and that there is no memory
limitation on the size of a transaction. Changed pages are stored in
the database.

--
..Bill.