PostgreSQL中PL/SH语言AFTER UPDATE触发器获取更新后数据异常问题求助

Let's break down the core issue first: even though you're using an AFTER UPDATE trigger, the psql call in your PL/SH function is returning the old version of the row. Here's why:

Root Cause: Transaction Isolation & External Connections #

1. PostgreSQL Transaction Boundaries: An AFTER UPDATE trigger runs as part of the same transaction that executed the UPDATE. The transaction doesn't commit until the trigger finishes running.
2. External psql Session: Your PL/SH function spawns a new psql process to query DB-B's table1. This is a separate database session, which operates under PostgreSQL's default READ COMMITTED isolation level. Under this level, uncommitted changes from other transactions (including the trigger's parent UPDATE transaction) are invisible. So the psql call sees the state of table1 before the UPDATE started.
3. PL/SH Limitation: You mentioned PL/SH doesn't support accessing NEW row data directly, which led you to query the table again. But this approach hits the transaction visibility wall.

Solutions to Fix the Issue #

Solution 1: Use PL/pgSQL to Capture NEW Data First #

Since PL/SH can't access NEW data, add a PL/pgSQL trigger to DB-B's table1 to capture the updated row data before your PL/SH logic runs. This avoids needing to re-query the table entirely.

Step 1: Create an Event Table to Store Updated Data

-- On DB-B
CREATE TABLE table1_update_events (
    id INT PRIMARY KEY REFERENCES table1(id),
    updated_row JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

Step 2: Add a PL/pgSQL Trigger to Capture NEW Data

-- On DB-B
CREATE OR REPLACE FUNCTION capture_table1_updates()
RETURNS TRIGGER AS $$
BEGIN
    -- Insert the full updated row into the event table
    INSERT INTO table1_update_events (id, updated_row)
    VALUES (NEW.id, to_jsonb(NEW))
    ON CONFLICT (id) DO UPDATE SET updated_row = EXCLUDED.updated_row;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER table1_after_update_capture
AFTER UPDATE ON table1
FOR EACH ROW EXECUTE FUNCTION capture_table1_updates();

Step 3: Modify Your PL/SH Trigger to Use the Event Table

Now, instead of querying table1 directly, have your PL/SH trigger (attached to table1_update_events on DB-B, or replicated to DB-C) read from table1_update_events. The data here is guaranteed to be the updated version, as it's inserted directly from the NEW row in the PL/pgSQL trigger.

Solution 2: Use Asynchronous Processing (Avoid Transaction Blocking) #

If cross-db visibility is the issue (DB-C's trigger querying DB-B before the transaction commits), switch to an asynchronous workflow:

1. In DB-B's PL/pgSQL trigger, send a message with the updated row data to a message broker (e.g., Redis, RabbitMQ) instead of directly triggering DB-C's logic.
2. Run an independent worker process that consumes these messages after DB-B's transaction has committed. This worker can then query DB-B's table1 safely (since the transaction is done) and update DB-C's table2.

Solution 3: Adjust Isolation Level (Not Recommended) #

You could temporarily set the psql session to use READ UNCOMMITTED isolation level to see uncommitted changes:

echo $(PGPASSWORD=xxx psql testslave xx -X -At -c "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; select * from table1 where id = $1")

⚠️ Warning: This introduces the risk of reading dirty (uncommitted) data that might later be rolled back. Only use this if you fully understand the tradeoffs and can tolerate potential data inconsistencies.

Key Takeaways #

• PL/SH's lack of NEW/OLD access forces workarounds, but re-querying the table in an external session will hit transaction visibility limits.
• Using PL/pgSQL to capture NEW data upfront is the most reliable approach, as it avoids relying on post-update table queries.
• Asynchronous processing eliminates cross-transaction visibility issues entirely, making it ideal for distributed database setups.

内容的提问来源于stack exchange，提问作者ZT Chuah