Learning Notes #34 - Consistency (Correctness) in ACID | Postgres

{% raw %}

As part of the ACID Series, i am refreshing on consistency. In this blog, i jot down notes on consistency (correctness) in postgres database.

What is Consistency?

Consistency ensures that a transaction brings the database from one valid state to another, adhering to predefined rules such as constraints, triggers, and relational integrity. If a transaction violates these rules, it is aborted, and the database remains unchanged. This guarantees that only valid data exists in the database.

Consistency works together with other ACID properties:

• Atomicity ensures the “all-or-nothing” execution of a transaction.
• Isolation ensures transactions don’t interfere with each other.
• Durability guarantees committed transactions persist despite system failures

Key Aspects of Consistency in PostgreSQL

1. Constraints
   • Primary Key: Ensures uniqueness of rows.
   • Foreign Key: Maintains referential integrity.
   • Check Constraints: Enforces custom business rules.
   • Not Null: Ensures that specific columns cannot have null values.
2. Triggers
   • Custom logic executed before or after specific database events.
3. Rules
   • Enforce application-specific invariants on the database.
4. Transactions
   • Changes are made in a controlled environment, ensuring consistency even in the event of errors or system failures.

Practical Examples of Consistency in PostgreSQL

1. Primary Key Constraint

Ensures that no two rows in a table have the same primary key value.

CREATE TABLE accounts (    account_id SERIAL PRIMARY KEY,    account_holder_name VARCHAR(255) NOT NULL,    balance NUMERIC(15, 2) NOT NULL CHECK (balance >= 0));-- Attempt to insert duplicate primary keys.INSERT INTO accounts (account_id, account_holder_name, balance)VALUES (1, 'Alice', 1000.00);INSERT INTO accounts (account_id, account_holder_name, balance)VALUES (1, 'Bob', 2000.00); -- This will fail.

2. Foreign Key Constraint

Enforces referential integrity between tables.

CREATE TABLE transactions (    transaction_id SERIAL PRIMARY KEY,    account_id INT NOT NULL REFERENCES accounts(account_id),    amount NUMERIC(15, 2) NOT NULL,    transaction_type VARCHAR(10) NOT NULL CHECK (transaction_type IN ('credit', 'debit')),    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP);-- Attempt to insert a transaction for a non-existent account.INSERT INTO transactions (account_id, amount, transaction_type)VALUES (999, 500, 'credit'); -- This will fail.

3. Check Constraint

Validates custom business rules.

-- Ensure account balance cannot go negative.INSERT INTO accounts (account_holder_name, balance)VALUES ('Charlie', -500); -- This will fail due to the CHECK constraint.

4. Trigger for Business Logic

Ensures derived data or additional checks are implemented.

CREATE OR REPLACE FUNCTION enforce_minimum_balance()RETURNS TRIGGER AS $$BEGIN    IF NEW.balance < 0 THEN        RAISE EXCEPTION 'Balance cannot be negative';    END IF;    RETURN NEW;END;$$ LANGUAGE plpgsql;CREATE TRIGGER check_balance_before_insertBEFORE INSERT OR UPDATE ON accountsFOR EACH ROW EXECUTE FUNCTION enforce_minimum_balance();-- Attempt to update an account with a negative balance.UPDATE accounts SET balance = -100 WHERE account_id = 1; -- This will fail.

5. Transactions to Maintain Consistency

A transaction groups multiple operations into a single unit, ensuring all succeed or none.

BEGIN;-- Deduct from sender's account.UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;-- Credit to receiver's account.UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;-- If any operation fails, rollback the transaction.COMMIT;

If the system crashes before the COMMIT, the database remains unchanged, ensuring consistency.

How Consistency Works with Other ACID Properties

1. With Atomicity: If any step in a transaction violates a constraint, the entire transaction is rolled back, ensuring that the database remains consistent.
2. With Isolation: Concurrent transactions operate independently, preventing inconsistent states caused by interference.
3. With Durability: Once a transaction is committed, its consistency guarantees persist even in the event of a crash.

Benefits of Consistency

1. Data Integrity: Prevents invalid data from being stored.
2. Application Reliability: Reduces the need for additional application-level checks.
3. Simplified Maintenance: Developers can rely on the database to enforce business rules and relationships.
4. Error Prevention: Constraints and triggers act as safeguards, catching mistakes early.

{% endraw %}