Cách khắc phục lỗi PostgreSQL UPSERT: 'cannot affect row a second time'

Bối cảnh vấn đề

Có thể bạn đang đồng bộ hóa một tập dữ liệu lớn—chẳng hạn như tệp CSV 50MB hoặc JSON payload từ một API bên ngoài—vào một bảng đích. Mọi thứ trong mã nguồn của bạn có vẻ ổn, nhưng ngay khi bạn thực hiện một lệnh UPSERT hàng loạt, PostgreSQL sẽ hủy giao dịch đó. Điều này xảy ra do dữ liệu đầu vào của bạn chứa nhiều bản ghi cho cùng một khóa duy nhất (như Primary Key hoặc địa chỉ email).

Database engine yêu cầu tính dự đoán được. Nó từ chối việc tự đoán xem bộ giá trị nào sẽ "thắng" nếu lệnh của bạn cố gắng cập nhật cùng một hàng vật lý hai lần trong một lần thực thi. Để duy trì tính toàn vẹn dữ liệu, PostgreSQL dừng toàn bộ quá trình thay vì đưa ra một lựa chọn tùy tiện.

Thông báo lỗi

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Tại sao điều này xảy ra

Nguyên nhân gốc rễ là do các khóa bị trùng lặp trong dữ liệu nguồn của bạn. PostgreSQL xử lý một câu lệnh INSERT như một hoạt động nguyên tử (atomic operation) duy nhất. Nếu batch của bạn chứa hai hàng có cùng một ID duy nhất, engine sẽ rơi vào một vòng lặp logic:

• Nó cố gắng chèn hàng đầu tiên (hoặc cập nhật nếu nó đã tồn tại).
• Sau đó, nó lại cố gắng cập nhật chính hàng đó khi gặp bản ghi thứ hai trong cùng một batch.

PostgreSQL nghiêm cấm điều này một cách rõ ràng. Nó không biết bạn muốn lấy giá trị đầu tiên, giá trị cuối cùng hay kết hợp cả hai. Thay vì mạo hiểm gây ra xung đột dữ liệu, nó sẽ báo lỗi.

Một ví dụ thực tế

Hãy tưởng tượng một bảng thương mại điện tử có tên product_inventory với ràng buộc duy nhất (unique constraint) trên cột sku. Bạn nhận được một bản cập nhật hàng loạt như sau:

INSERT INTO product_inventory (sku, stock_count)
VALUES 
  ('LAPTOP-001', 50), 
  ('PHONE-002', 120), 
  ('LAPTOP-001', 45) -- SKU bị trùng trong cùng một batch!
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Vì LAPTOP-001 xuất hiện hai lần, cơ sở dữ liệu không biết lượng tồn kho cuối cùng nên là 50 hay 45. Giao dịch thất bại ngay lập tức.

Các giải pháp từng bước

Để khắc phục điều này, bạn phải làm sạch dữ liệu trước khi đưa vào câu lệnh INSERT. Dưới đây là ba cách đáng tin cậy nhất để xử lý.

Phương pháp 1: Sử dụng DISTINCT ON (Nhanh và Đơn giản)

Đây là lựa chọn tốt nhất nếu bạn chỉ cần chọn một bản ghi từ các bản ghi trùng lặp và tiếp tục. Sử dụng DISTINCT ON trong một subquery để lọc dữ liệu đầu vào.

INSERT INTO product_inventory (sku, stock_count)
SELECT DISTINCT ON (sku) sku, stock_count
FROM (
    VALUES 
      ('LAPTOP-001', 50), 
      ('PHONE-002', 120), 
      ('LAPTOP-001', 45)
) AS input_data(sku, stock_count)
ORDER BY sku, stock_count DESC -- Chọn số lượng tồn kho cao nhất nếu có trùng lặp
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Phương pháp 2: Sử dụng CTE cho logic lấy bản ghi mới nhất

Nếu dữ liệu của bạn có timestamp, bạn có thể muốn giữ lại bản cập nhật mới nhất. Một Common Table Expression (CTE) sử dụng ROW_NUMBER() là cách chuyên nghiệp nhất để xử lý vấn đề này cho các batch trên 1.000 hàng.

WITH cleaned_updates AS (
    SELECT 
        sku, 
        stock_count, 
        updated_at,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY updated_at DESC) as rank
    FROM (
        VALUES 
          ('LAPTOP-001', 50, '2023-10-25 10:00:00'::timestamp), 
          ('PHONE-002', 120, '2023-10-25 10:05:00'::timestamp), 
          ('LAPTOP-001', 45, '2023-10-25 10:10:00'::timestamp)
    ) AS raw_data(sku, stock_count, updated_at)
)
INSERT INTO product_inventory (sku, stock_count)
SELECT sku, stock_count
FROM cleaned_updates
WHERE rank = 1
ON CONFLICT (sku) 
DO UPDATE SET stock_count = EXCLUDED.stock_count;

Phương pháp 3: Tổng hợp dữ liệu trước (Pre-aggregating)

Đối với dữ liệu tài chính hoặc bộ đếm lượt truy cập (hit counters) nơi bạn cần cộng dồn các giá trị thay vì ghi đè chúng, hãy tổng hợp dữ liệu trước.

INSERT INTO daily_sales (product_id, total_sold)
SELECT product_id, SUM(amount)
FROM (VALUES (101, 1), (101, 2), (102, 5)) AS batch(product_id, amount)
GROUP BY product_id
ON CONFLICT (product_id) 
DO UPDATE SET total_sold = daily_sales.total_sold + EXCLUDED.total_sold;

Cách xác minh giải pháp

Đừng chỉ chạy truy vấn và hy vọng mọi thứ sẽ ổn. Hãy làm theo ba bước sau để đảm bảo dữ liệu của bạn sạch:

• Tách riêng phần SELECT: Chỉ chạy phần subquery hoặc CTE trong mã nguồn đã được cấu trúc lại của bạn. Đảm bảo số lượng hàng khớp với số lượng khóa duy nhất mà bạn mong đợi.
• Kiểm tra mất mát dữ liệu: Nếu bạn bắt đầu với 1.000 hàng và kết thúc với 800 hàng, hãy xác minh xem 200 hàng "thiếu" đó có thực sự là các bản ghi trùng lặp hay không.
• Kiểm tra các trường hợp biên (Edge Cases): Thử một batch nhỏ với ba khóa giống hệt nhau. Nếu logic ORDER BY của bạn chính xác, cơ sở dữ liệu sẽ luôn chọn cùng một bản ghi trong mọi lần chạy.

Những điểm rút ra quan trọng

• Một câu lệnh, một hàng: PostgreSQL xem một lệnh duy nhất là một đơn vị công việc (unit of work) duy nhất. Nó không thể sửa đổi cùng một hàng hai lần trong đơn vị đó mà không làm mất tính nhất quán.
• Làm sạch nguồn dữ liệu của bạn: Đừng bao giờ tin tưởng dữ liệu bên ngoài. Hãy luôn giả định rằng tệp CSV hoặc API payload chứa các bản ghi trùng lặp và bao bọc lệnh UPSERT của bạn trong một lớp khử trùng lặp (deduplication layer).
• Hãy rõ ràng: Sử dụng ORDER BY khi khử trùng lặp. Điều này đảm bảo hành vi UPSERT của bạn có thể dự đoán được và không thay đổi ngẫu nhiên giữa các lần chạy.
• Khớp các mục tiêu: Các cột trong mệnh đề ON CONFLICT phải khớp với các cột trong mệnh đề DISTINCT ON hoặc PARTITION BY để giải pháp hoạt động chính xác.