Analyzing Current Queries and Transactions

Let's start with the basics. PostgreSQL gives you an awesome set of tools for analyzing SQL queries and transactions. For example, the built-in functions current_query() and txid_current() let you:

• Get the current running SQL query.
• Find out which transaction the query is running in.
• Log SQL operations for later analysis.
• Track down transaction issues if your code expects one thing but something totally different happens.

All this can really save your bacon when standard debug output isn't enough, or when you want to analyze query behavior "after the fact."

Overview of Built-in Functions

The current_query() Function

current_query() returns the text of the current SQL query running in this connection. "How does it know?" you might ask. PostgreSQL keeps close tabs on the state of every connection, and this function lets you peek behind the curtain.

Syntax:

SELECT current_query();

Example run:

-- Running a query inside a function
DO $$
BEGIN
    RAISE NOTICE 'Current query: %', current_query();
END;
$$;

-- Result:
-- NOTICE: Current query: DO $$ BEGIN RAISE NOTICE 'Current query: %', current_query(); END; $$;

As you can see, current_query() tells us the text of the running query. This info is super useful for analyzing complex procedures: you know exactly what's being executed right now!

The txid_current() Function

When it comes to transactions, the txid_current() function is a fantastic tool. It returns a unique ID for the current transaction. This is especially handy if you want to track the sequence of operations inside a single transaction.

Syntax:

SELECT txid_current();

Example run:

BEGIN;

-- Get the current transaction ID
SELECT txid_current();

-- Output:
-- 564 (for example, the ID)

-- Finish the transaction
COMMIT;

These transaction IDs can be used to match up logs, analyze the sequence of actions, and even debug multi-user systems.

Real-world Usage Examples

1. Logging the current query while it's running.

Sometimes a procedure or function has a bunch of SQL queries. To figure out where something went wrong, you can turn on logging for the current SQL query. For example:

DO $$
DECLARE
    current_txn_id BIGINT;
BEGIN
    current_txn_id := txid_current();
    RAISE NOTICE 'Current transaction ID: %', current_txn_id;

    RAISE NOTICE 'Current query: %', current_query();

    -- Your extra operations could go here
END;
$$;

This code will print the transaction ID and the current query text to the console. Now you know exactly what's running at this moment.

2. Analyzing transactions to find issues.

Let's imagine a scenario where users complain about data loss during a mass update. You create several procedures, each running inside a single transaction. How do you figure out who's to blame? Here's an example:

BEGIN;

-- Add transaction logging
DO $$
BEGIN
    RAISE NOTICE 'Current transaction ID: %', txid_current();
END;
$$;

-- Run the "problem" SQL query
UPDATE orders
SET status = 'processed'
WHERE id IN (SELECT order_id FROM pending_orders);

COMMIT;

If the updates don't go through, you immediately see the transaction ID for your changes. This not only makes it easier to find the bug, but also helps you see if there were any transaction conflicts.

3. Logging queries for historical analysis.

Sometimes you don't just want to fix the current problem, but also remember which SQL queries were run. For example, you can create a table for logging:

CREATE TABLE query_log (
    log_time TIMESTAMP DEFAULT NOW(),
    query_text TEXT,
    txn_id BIGINT
);

Here's how you can log queries using current_query() and txid_current():

DO $$
BEGIN
    INSERT INTO query_log (query_text, txn_id)
    VALUES (current_query(), txid_current());
END;
$$;

Now the query_log table stores info about every query and the transaction it ran in. This is a priceless tool for analyzing your database's activity.

Practical Use Cases

Example 1: Transaction Auditing

Imagine you're analyzing operations in a multi-user system. Logging the transaction ID (txid_current) lets you group actions by transaction.

DO $$
DECLARE
    txn_id BIGINT;
BEGIN
    txn_id := txid_current();
    RAISE NOTICE 'Transaction started with ID: %', txn_id;

    -- Some operation
    UPDATE users SET last_login = NOW() WHERE id = 123;

    RAISE NOTICE 'Current query: %', current_query();
END;
$$;

Example 2: Easier Procedure Debugging

You called a complex procedure and something went wrong. You can add current_query() logging at different stages of the function to see which query was running:

CREATE OR REPLACE FUNCTION debugged_function() RETURNS VOID AS $$
BEGIN
    RAISE NOTICE 'Current query before update: %', current_query();
    UPDATE data_table SET field = 'debugging';
    RAISE NOTICE 'Current query after update: %', current_query();
END;
$$ LANGUAGE plpgsql;

When the function call finishes, you'll get two notifications with the corresponding SQL queries.

Tips for Using These Functions

1. Use current_query() to log queries inside multi-user systems so you know what actions are being performed.
2. txid_current() is perfect for analyzing where changes came from: at what point in your transaction data was added or changed.
3. Don't forget to remove unnecessary logging when you're done with it. Constant RAISE NOTICE messages can slow down your function.

These built-in functions are your "microscope" for exploring the tiny details of how your database works. They'll help you catch bugs, boost performance, and really understand what's going on in complex systems. Somewhere deep inside PostgreSQL, your database is already ready to share its secrets—you just have to learn how to read them.