Comprehensive Debugging and Optimization of a Multi-Step Procedure

Multi-step procedures are like the "Swiss Army knives" of databases. They usually include input validation, making changes (like updating records, inserting logs), and sometimes even analytics. But here's the catch: the more complex the procedure, the higher the chance of something going wrong. A logic bug, a slow query, a missed detail—and everything can go sideways.

Comprehensive debugging covers these aspects:

1. Input data analysis: are the parameters set up right? Is the data passed in correct?
2. Checking key steps execution: are all the steps of the procedure running as they should?
3. Logging intermediate results: so you know what happened before something "broke".
4. Optimizing performance bottlenecks: let's fix the weak spots that "slow down" your queries.

Setting the Task: Example of a Multi-Step Procedure

For our hands-on example, imagine we're working with an online store's database. We need to create a procedure to process an order. It'll do the following steps:

1. Check if the product is in stock.
2. Reserve the product.
3. Update the order status.
4. Write events (like a successful reservation or an error) to the logs table.

Database structure script:

-- Products table
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Orders table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Logs table
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Step 1: Creating the Multi-Step Procedure

Let's make a basic process_order procedure. It'll take the order ID and run through all the processing steps.

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. Get the product ID and order status
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Order % does not exist or missing product_id', p_order_id;
    END IF;

    -- 2. Check if the product is in stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Product % is out of stock', v_product_id;
    END IF;

    -- 3. Update stock quantity
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Update order status
    UPDATE orders
    SET order_status = 'Processed'
    WHERE order_id = p_order_id;

    -- 5. Log the successful event
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Order processed successfully.');
END;
$$;

Step 2: Logging Errors with RAISE NOTICE and RAISE EXCEPTION

This is where the magic starts. We'll add logging for intermediate steps so we can catch errors and see what's happening at each stage.

Updated code with logging:

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE 'Processing order %...', p_order_id;

    -- 1. Get the product ID
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Order % does not exist or missing product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID for order %: %', p_order_id, v_product_id;

    -- 2. Check if the product is in stock
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Product % is out of stock', v_product_id;
    END IF;
    RAISE NOTICE 'Stock quantity for product %: %', v_product_id, v_stock_quantity;

    -- 3. Update stock quantity
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Update order status
    UPDATE orders
    SET order_status = 'Processed'
    WHERE order_id = p_order_id;

    -- 5. Log successful execution
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Order processed successfully.');
    RAISE NOTICE 'Order % processed successfully.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Log the error
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Error: ' || SQLERRM);
    RAISE;
END;
$$;

Step 3: Optimizing with Indexes

If your database has a ton of products or orders, finding the right rows can become a bottleneck. Let's add indexes to speed up lookups during processing:

-- Index to speed up lookups in the orders table
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Index to speed up lookups in the products table
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Step 4: Performance Analysis with EXPLAIN ANALYZE

Now let's check how fast our function runs. We'll call it with performance analysis:

EXPLAIN ANALYZE
SELECT process_order(1);

The result will show how long each step takes. We'll be able to spot which step is the slowest—this will help us optimize the procedure even more.

Step 5: Improving with Transactions

To make things more reliable, you can wrap the whole procedure in a transaction. That way, if something goes wrong, all changes will roll back.

BEGIN;

-- Call the function
SELECT process_order(1);

-- Commit the transaction
COMMIT;

Inside the function code, you can use SAVEPOINT and ROLLBACK TO SAVEPOINT to handle partial errors.

Practical Task: Processing Mass Orders

Let's wrap up the lecture with an example of processing a bunch of orders at once. We'll make a function that processes all orders with the status Pending:

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = 'Pending'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Failed to process order %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

When you call this function, all orders with the status Pending will be processed, and any errors will just get logged.

So, we've shown how to debug and optimize complex procedures, making them more reliable, faster, and easier to read. This stuff will totally come in handy in real projects, where the quality of your procedures can make or break your app's success.

2

Task

SQL SELF, level 56, lesson 3

Locked

Logging Intermediate Steps

Logging Intermediate Steps

Open