Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Mail Systems
Eclipse Documentation

How To Guides
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Problem Solutions
Privacy Policy




Databases - Practical PostgreSQL
Previous Page Home Next Page

Transactions and Cursors

PostgreSQL uses a multi-version approach to transactions within the database. A transaction is a formal term for a SQL statement's effects being synchronized with the "current" data in the database. This doesn't necessarily mean that the data is written to disk, but it becomes part of the "current" set of information stored in the database. When a statement's results have effectively been processed in the current state of the database, the transaction is considered to be committed .

The issue of two users attempting to commit changes to the same database object is obviously a potential concern, as their modifications may be exclusive to one another. Some relational database systems rely on automatic locking to prevent such conflicts.

Locking is a mechanism that disallows selecting from a database object while it is being modified, and vice versa. Locking presents several obvious performance concerns. For example, data which is being updated will not be selectable until the update transaction has completed.

PostgreSQL's Multi-Version Concurrency Control (MVCC), however, allows for SQL statements to be performed within transaction-deferred blocks . This means that each connection to PostgreSQL essentially maintains a temporary snapshot of the database for objects modified within a transaction block, before the modifications are committed.

Without explicitly opening a transaction block, all SQL statements issued to PostgreSQL are auto-committed , meaning that the database is synchronized with the results of the statement immediately upon execution. When a transaction block is used, however, changes made to the database will not be visible to other users until the block is committed. This allows for several changes to various objects within a database to be made tentatively. They can then be either committed all at once, or rolled back.

Rolling back a transaction returns the state of any affected objects to the condition they were in before the transaction block began. This can be useful when recovering from a partially failed operation, in that any modifications made part-way into a process can be undone. Rolled back transactions are never actually committed; while the process appears to undo modifications to the user who performed the rollback, other users connected to the same database never know the difference.

PostgreSQL also supports cursors , which are flexible references to fully executed SQL queries. A cursor is able to traverse up and down a result set, and only retrieve those rows which are explicitly requested. Used properly, a cursor can aid an application in efficiently use a static result set. A cursor may only be executed within a transaction block.

The following sections cover the basic use of transactions and cursors. They show how to begin, commit, and roll back transactions, and also how to declare, move, and fetch data from a cursor.

Databases - Practical PostgreSQL
Previous Page Home Next Page

  Published under the terms of the Open Publication License Design by Interspire