Teorema CAP e consistência

import { Pool } from "pg";

const principal = new Pool({ connectionString: process.env.DATABASE_URL });
const replica = new Pool({ connectionString: process.env.DATABASE_REPLICA_URL });

// Grava no principal e devolve o token, lido DEPOIS do commit
export async function alterarStatus(id: number, status: string) {
  await principal.query("UPDATE pedidos SET status = $1 WHERE id = $2", [status, id]);
  const { rows } = await principal.query("SELECT pg_current_wal_lsn() AS lsn");
  return rows[0].lsn as string;
}

export async function lerPedido(id: number, token?: string) {
  let banco = replica;
  if (token) {
    const { rows } = await replica.query(
      "SELECT coalesce(pg_last_wal_replay_lsn() >= $1::pg_lsn, false) AS em_dia",
      [token],
    );
    if (!rows[0].em_dia) banco = principal;
  }
  const { rows } = await banco.query("SELECT id, status FROM pedidos WHERE id = $1", [id]);
  return rows[0];
}

import psycopg

principal = psycopg.connect(DATABASE_URL, autocommit=True)
replica = psycopg.connect(DATABASE_REPLICA_URL, autocommit=True)


def alterar_status(pedido_id: int, status: str) -> str:
    """Grava no principal e devolve o token, lido DEPOIS do commit."""
    principal.execute(
        "UPDATE pedidos SET status = %s WHERE id = %s", (status, pedido_id)
    )
    return principal.execute("SELECT pg_current_wal_lsn()::text").fetchone()[0]


def ler_pedido(pedido_id: int, token: str | None = None):
    banco = replica
    if token:
        em_dia = replica.execute(
            "SELECT coalesce(pg_last_wal_replay_lsn() >= %s::pg_lsn, false)",
            (token,),
        ).fetchone()[0]
        if not em_dia:
            banco = principal
    return banco.execute(
        "SELECT id, status FROM pedidos WHERE id = %s", (pedido_id,)
    ).fetchone()

package pedidos

import (
	"context"
	"database/sql"
)

type Repositorio struct {
	Principal *sql.DB
	Replica   *sql.DB
}

// AlterarStatus grava no principal e devolve o token, lido DEPOIS do commit.
func (r *Repositorio) AlterarStatus(ctx context.Context, id int64, status string) (string, error) {
	_, err := r.Principal.ExecContext(ctx,
		"UPDATE pedidos SET status = $1 WHERE id = $2", status, id)
	if err != nil {
		return "", err
	}
	var token string
	err = r.Principal.QueryRowContext(ctx,
		"SELECT pg_current_wal_lsn()::text").Scan(&token)
	return token, err
}

func (r *Repositorio) LerPedido(ctx context.Context, id int64, token string) (string, error) {
	banco := r.Replica
	if token != "" {
		var emDia bool
		err := r.Replica.QueryRowContext(ctx,
			"SELECT coalesce(pg_last_wal_replay_lsn() >= $1::pg_lsn, false)",
			token).Scan(&emDia)
		if err != nil || !emDia {
			banco = r.Principal
		}
	}
	var status string
	err := banco.QueryRowContext(ctx,
		"SELECT status FROM pedidos WHERE id = $1", id).Scan(&status)
	return status, err
}

import java.sql.SQLException;
import javax.sql.DataSource;

public class Pedidos {
    private final DataSource principal;
    private final DataSource replica;

    public Pedidos(DataSource principal, DataSource replica) {
        this.principal = principal;
        this.replica = replica;
    }

    // Grava no principal e devolve o token, lido DEPOIS do commit
    public String alterarStatus(long id, String status) throws SQLException {
        try (var conexao = principal.getConnection()) {
            try (var update = conexao.prepareStatement(
                    "UPDATE pedidos SET status = ? WHERE id = ?")) {
                update.setString(1, status);
                update.setLong(2, id);
                update.executeUpdate(); // autocommit: confirmado aqui
            }
            try (var lsn = conexao.createStatement();
                 var resultado = lsn.executeQuery("SELECT pg_current_wal_lsn()::text")) {
                resultado.next();
                return resultado.getString(1);
            }
        }
    }

    public String lerStatus(long id, String token) throws SQLException {
        DataSource banco = replica;
        if (token != null) {
            try (var conexao = replica.getConnection();
                 var consulta = conexao.prepareStatement(
                     "SELECT coalesce(pg_last_wal_replay_lsn() >= ?::pg_lsn, false)")) {
                consulta.setString(1, token);
                try (var resultado = consulta.executeQuery()) {
                    resultado.next();
                    if (!resultado.getBoolean(1)) banco = principal;
                }
            }
        }
        try (var conexao = banco.getConnection();
             var consulta = conexao.prepareStatement(
                 "SELECT status FROM pedidos WHERE id = ?")) {
            consulta.setLong(1, id);
            try (var resultado = consulta.executeQuery()) {
                return resultado.next() ? resultado.getString(1) : null;
            }
        }
    }
}

using Npgsql;

public class Pedidos(NpgsqlDataSource principal, NpgsqlDataSource replica)
{
    // Grava no principal e devolve o token, lido DEPOIS do commit
    public async Task<string> AlterarStatus(long id, string status)
    {
        await using (var update = principal.CreateCommand(
            "UPDATE pedidos SET status = $1 WHERE id = $2"))
        {
            update.Parameters.Add(new() { Value = status });
            update.Parameters.Add(new() { Value = id });
            await update.ExecuteNonQueryAsync();
        }

        await using var lsn = principal.CreateCommand("SELECT pg_current_wal_lsn()::text");
        return (string)(await lsn.ExecuteScalarAsync())!;
    }

    public async Task<string?> LerStatus(long id, string? token)
    {
        var banco = replica;
        if (token is not null)
        {
            await using var emDia = replica.CreateCommand(
                "SELECT coalesce(pg_last_wal_replay_lsn() >= $1::pg_lsn, false)");
            emDia.Parameters.Add(new() { Value = token });
            if (!(bool)(await emDia.ExecuteScalarAsync())!) banco = principal;
        }

        await using var consulta = banco.CreateCommand("SELECT status FROM pedidos WHERE id = $1");
        consulta.Parameters.Add(new() { Value = id });
        return (string?)await consulta.ExecuteScalarAsync();
    }
}