Ghosts in the DB

The Problem

Transactions Not Charged

Symptoms

• Django saves Transaction (SQL INSERT)
• Django attempts to select Transaction (SQL SELECT)
• DoesNotExist is raised
• API call returns 400, Charge Fails

Added Logging

Also tried waiting .3 seconds after failure and attempting another SELECT

The Transaction instance has an ID, and the save method didn't raise an error, so why can't the ORM find it?

DB Logs

• Heavy load on Transaction table
• INSERTs Succeed
• SELECTs Fail

What is happening here?

Theory: PGPool

PGPool

Scale Postgres to multiple nodes

Are the nodes updated instantaneously?

Probably Not

In this case, the INSERT appears to replicate to the SELECT node after the SELECT has already failed

Solution

Why do we need to SELECT immediately after the INSERT?

# refresh the transaction from the database since 
# a bunch of casting (in the process of insertion into the db)
# probably occurred
t = Transaction.objects.get(id=t.id)

What if we just stop doing that?

No errors since removing the "refresh"

Lessons Learned

In general, minimize DB queries to reduce load on the DB

Specifically, try not to rapidly INSERT/SELECT, as the PGPool nodes won't replicate fast enough