2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Traceable commit for PostgreSQL 10
craig.ringer

Traceable commit for PostgreSQL 10

April 5, 2017/0 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

PostgreSQL 10 now supports finding out the status of a recent transaction for recovery after network connection loss or crash.

Recovery from indeterminate COMMIT

If there was a COMMIT “in flight” when an application crashed, lost its connection to the DB server, or the DB server crashed, the commit’s status is indeterminate as far as the application can tell. The application does not know if the COMMIT message was received by the database and processed to completion or not, so it doesn’t know if re-issuing that transaction is necessary or correct. Sometimes the application can examine rows in its tables to figure it out; sometimes it can’t.

Applications that need to recover pending work after a crash usually use two-phase commit. That works, but it’s slow even after the 2PC performance improvements in Pg 10 because there’s an extra client/server round-trip for PREPARE TRANSACTION.

How does traceable commit help?

The new txid_status(...) function addresses this by allowing applications to look up a recent transaction-id to find out if it committed or aborted, either due to crash or explicit rollback.

The extra round-trip is now only necessary during recovery after a failure, and there’s no extra server-side workload from two-phase commit.

commit 857ee8e391ff6654ef9dcc5dd8b658d7709d0a3c
Author: Robert Haas <[email protected]>
Date:   Fri Mar 24 12:00:53 2017 -0400

    Add a txid_status function.
    
    If your connection to the database server is lost while a COMMIT is
    in progress, it may be difficult to figure out whether the COMMIT was
    successful or not.  This function will tell you, provided that you
    don't wait too long to ask.  It may be useful in other situations,
    too.
    
    Craig Ringer, reviewed by Simon Riggs and by me
    
    Discussion: http://postgr.es/m/[email protected]om

How to use it

The application must obtain the transaction ID of any transaction it wants to look up using txid_current() or txid_current_if_assigned() before it sends the COMMIT. It can avoid an extra round-trip for this query by:

  • Issuing a “multi-statement query”, e.g. INSERT INTO ...; SELECT txid_current(); if it doesn’t need the result of the prior query;
  • Combining it with another query, e.g. INSERT INTO ... RETURNING txid_current();
  • Using client driver support for batching queries to dispatch the txid_current() query along with other queries without waiting for a reply for each query.

In a future version PostgreSQL may automatically report the transaction ID when it is assigned to make this easier for applications.

The application then stores the transaction ID for the pending transaction locally alongside its pending work before it sends the COMMIT to PostgreSQL. For example purposes lets say it gets the xid 63204.

If network connection to PostgreSQL is lost after the COMMIT is sent but before a reply is received, the application can recover by reprocessing its pending work queue, checking to see if each indeterminate-status item needs to be re-processed or not by testing whether the saved xid committed or not, e.g.:

SELECT txid_status(BIGINT '63204')

which will return committed if the transaction is already safely on disk on the DB, so the application can skip re-issuing it. Or it’ll return aborted if the transaction was lost before commit and needs to be re-issued.

Because of the vagaries of TCP/IP, the database may not have even noticed that the application connection has gone away by the time the application reconnects. In this case the query will return in progress. The application can wait to find out if the transaction lands up committing, or it can look up the associated backend in pg_stat_activity and pg_terminate_backend(...) it to hurry things up.

Finally, if the application takes too long to ask after the status of its lost-in-flight commit, the database may return NULL to indicate that its status is no longer known. This happens once all tuples associated with that transaction and all older transactions have been deleted or updated to newer versions and vacuum has cleaned up the old transaction history information.

Because knowledge of transaction status is eventually discarded and because you cannot actually ROLLBACK a transaction once committed, the traceable commit feature does not substitute for two-phase commit in all applications. It won’t plug in to your JTA or XA transaction manager or your MS-DTC setup, but it’ll help throughput for reliable queue-processing apps among others.

An implementation detail

Experienced users may have noticed that txid_status(bigint) takes a bigint (as returned by txid_current(), not PostgreSQL’s internal transaction ID data type xid. That’s because xid is 32-bit and wraps around and we don’t want to risk giving the wrong answer. The bigint is extended to 64 bits with an epoch counter that increments each time the xid wraps around.

Tags: 2pc, 2QLovesPG, PostgreSQL, PostgreSQL10, traceable commit, two phase commit, txid_status
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
2ndQuadrant – Not the Gartner Magic Quadrant Using the PostgreSQL TAP framework in extensions
Scroll to top
×