Neden `RETURNING` ifadesi **PostgreSQL**'de **UPSERT** çelişki çözümü sırasında üretilmiş sütunlar için beklenmedik değerler döndürebilir ve hangi mekanizma önerilen ekleme değerlerini düzgün bir şekilde referans alır?

Cevap

Sorunun Tarihçesi

Bu belirsizlik, PostgreSQL 9.5'te ON CONFLICT ifadesi aracılığıyla yerel UPSERT işlevselliğinin tanıtılmasıyla ortaya çıktı. Bu sürümden önce, geliştiriciler karmaşık PL/pgSQL döngüleri veya hata yapmaya müsait uygulama yanlısı mantık kullanarak idempotent eklemeler gerçekleştiriyordu. RETURNING ifadesi, UUID veya seri kimlikleri almak için uzun zamandır önemliydi, ancak UPSERT'in çift yol yürütme modelinin etkileşimi—ifadenin ya bir INSERT ya da UPDATE ile sonuçlanabilmesi—gerçekten dönen satır versiyonunun hangisi olduğuna dair belirsiz bir anlam boşluğu oluşturdu.

Sorun

Bir INSERT ... ON CONFLICT ... DO UPDATE ifadesi benzersiz bir ihlal ile karşılaştığında, mevcut satırı güncelleme yönüne döner. RETURNING ifadesi bu satırın nihai kalıcı durumunu referans alır. Ancak, uygulama mantığınız ekleme denemesi için üretilen değerlerin—created_at zaman damgaları, varsayılan ifadeler veya uygulama hesaplanan değerleri gibi—üzerine inşa edildiğinde, ifade bunun yerine varolan satırın eski verilerini döndürür. Bu sessiz değişim, önbellek eşitsizliği, denetim kaydı bozulması ve sonraki sistemlerin zaman açısından tutarsız meta verilere sahip olabileceği ince yarış koşullarına yol açar.

Çözüm

EXCLUDED takma tablo, çelişkiye neden olan önerilen ekleme değerlerine bir göz atma imkanı sağlar. RETURNING ifadesinde veya UPDATE set listesinde EXCLUDED.column_name'i açıkça referans alarak, hangi yürütme yolunun alındığından bağımsız olarak, istenen yeni verilere erişiminizi garanti edersiniz.

INSERT INTO user_sessions 
    (user_id, login_count, last_seen, session_token)
VALUES 
    (1001, 1, NOW(), gen_random_uuid())
ON CONFLICT (user_id) 
DO UPDATE SET 
    login_count = user_sessions.login_count + 1,
    last_seen = EXCLUDED.last_seen,
    session_token = EXCLUDED.session_token
RETURNING 
    session_id,
    user_id,
    CASE WHEN xmax = 0 THEN 'inserted' ELSE 'updated' END AS operation_type,
    session_token,
    EXCLUDED.last_seen AS intended_timestamp;

Bu yapı içinde, EXCLUDED.last_seen ve EXCLUDED.session_token, uygulamanın, veritabanı bir güncelleme gerçekleştirse bile, girişimdeki taze değerleri almasını sağlar.

Hayattan bir Durum

Eşzamanlı sadakat puanı birikimi

Yüksek frekansta mikro işlemleri işleyen bir fintech platformu, hayalet ödül hesaplamaları ile karşılaştı. İki paralel istek aynı kullanıcı hesabına puan eklemeye çalıştığında, PostgreSQL veritabanı atomikliği doğru bir şekilde korudu, ancak Redis önbellek katmanı RETURNING ifadesinden eski updated_at zaman damgaları aldı. Bu, önbelleğin geçersiz olan puan artışlarını reddetmesine yol açtı ve gelir kaybı ile müşterilerin kaybolan ödüller hakkında şikayette bulunmasına neden oldu.

Çözüm A: Redis ile Dağıtılmış Kilitlenme

Mühendislik ekibi, veritabanı işlemini gerçekleştirmeden önce Redis'te dağıtılmış kilitler almayı önermiştir. Bu yaklaşım, çelişen işlemleri sıraya koyacak ve sıralı tutarlılığı garanti edecekti. Ancak, bu, tek bir hata noktası oluşturmuş, her istek başına 12-18ms kadar ağ gecikmesi eklemiş ve kilitler alındıktan sonra işlemler iptal edildiğinde karmaşık deadlock senaryoları yaratmıştır. Kilit yönetiminin operasyonel yükü ve olağandışı arızalara yol açma potansiyeli, bu mimariyi büyük ölçekte sürdürülemez hale getirmiştir.

Çözüm B: Uygulama Tarafında Okuma-Değiştirme-Yazma

Diğer bir öneri, önce bir SELECT ile kaydın varlığını sorgulamak, ardından uygulama kodunda INSERT veya UPDATE arasında karar vermekti. Kavramsal olarak basit olan bu desen, READ COMMITTED izolasyonu nedeniyle eşzamanlı yük altında felaketle sonuçlanır çünkü kontrol ve yazım arasında tekrarlanamaz okumalara izin verir. Yarış koşullarını önlemek için SERIALIZABLE izolasyonu uygulamak aşırı serileştirme hatalarına ve yeniden deneme fırtınalarına neden olurken, açık tablo kilitleri ile throughput'u kabul edilemez seviyelere kısıtlamak zorunda kalırdınız.

Çözüm C: Doğru EXCLUDED Kullanımı

Seçilen yaklaşım, sorguyu RETURNING ifadesindeki tüm değiştirilebilir değerler için EXCLUDED'i kullanacak şekilde yeniden yapılandırmaktır. EXCLUDED.points ve EXCLUDED.calculated_at'i referans alarak, uygulama, işlemin yeni bir satıra mı yoksa bir güncellemeye mi yol açtığına bakılmaksızın, ekleme denemesiyle istenen meta verileri sürekli olarak almıştır.

Seçilen Çözüm ve Sonuç

Ekip, ödül mikroservisi genelinde Çözüm C'yi uyguladı. Bu, ağ sıçramaları eklemeden veya izolasyon seviyelerini tehlikeye atmadan, önbellek tutarsızlığı sorunlarını ortadan kaldırdı. Puan birikim doğruluğu %99.99'a yükseldi, veritabanı CPU kullanımı sorgu turlarının azalmasıyla %35 oranında düştü ve sistem, manuel müdahale olmaksızın Kara Cuma trafik zirvelerini başarıyla yönetti.

Adayların Sıkça Gözden Kaçırdığı Noktalar

Birden fazla indeks bulunduğunda PostgreSQL, çatışma tespiti için hangi benzersiz indeksi kullanacağını nasıl belirler?

PostgreSQL, ON CONFLICT ifadesinde açık hakem belirtimi gerektirir. ON CONFLICT (column_list) yazdığınızda, planlayıcı, sağlanan listeyle tam olarak eşleşen indexed sütunlara sahip benzersiz indeksi seçer. Eğer aynı sütunlar üzerinde birden fazla indeks varsa, ilk oluşturulanı seçer. Kısmi benzersiz indeksler (nasıl WHERE koşulları olanlar) veya ifade indeksleri için, ON CONFLICT ON CONSTRAINT constraint_name söz dizimini kullanmalısınız; aksi takdirde, motor hakem indeksini çıkaramayacağına dair bir hata fırlatır. Adaylar sıklıkla veritabanının varsayılan olarak "en seçici" indeksi seçeceğini varsayıyor ya da işlevsel indekslerin açıkça kısıtlamaların adlandırılmasını gerektirdiğini gözden kaçırıyor.

Birden fazla işlem aynı anahtar üzerinde çatıştığında neden bir UPSERT ifadesi sessizce güncellemeleri kaybedebilir?

Bu, UPDATE ifadesinin yeniden değerlendirme davranışı nedeniyle olur. İşlem A bir dizi ekleyip taahhüt ettiğinde, İşlem B—satır kilidinde bekleyen—kendi UPDATE şartını yeni görünür satıra karşı yeniden yürütür. Eğer UPDATE mantığı, SET balance = 100 gibi mutlak atama kullanıyorsa ve EXCLUDED'i referans almıyorsa (örneğin; SET balance = account.balance + EXCLUDED.amount), İşlem B, İşlem A'nın değişikliklerini tamamen ezebilir. Birçok aday, UPSERT'in otomatik birleştirme veya birikim anlamına geldiğini varsayıyor, ancak DO UPDATE ifadesinin EXCLUDED değerlerini açıkça kullanma gerekliliğini anlamakta başarısız oluyor ve bu da idempotent birikim anlamlarını gerçekleştirmek için gereklidir.

Bir UPSERT işleminin bir ekleme yaptığını belirlemek için xmax = 0 ile xmax IS NULL kontrolü arasındaki kesin fark nedir ve bu ayrım HOT güncellemeleri için neden önemlidir?

PostgreSQL'de xmax, silme veya güncelleme işlemi için işlem kimliğini depolar. Yeni eklenen satırlar için xmax, 0 olarak başlatılır, asla NULL olmaz. Adaylar sıklıkla eklemeleri tespit etmek için xmax IS NULL kontrolü uygular, bu her zaman yanlış döner. xmax = 0 kontrolü, eklemeleri güncellemelerden güvenilir bir şekilde ayırt eder. Bu ayrım, HOT (Yalnızca Yığın) güncellemeleri bakımından kritik hale gelir, burada PostgreSQL performansı optimize etmek için satırları aynı sayfa içinde yerinde güncelleyerek dizinleri değiştirmeden günceller. xmax doğru bir şekilde satırın dokunulduğunu gösterirken, 0'ın "önceki güncelleyici yok" anlamına geldiğini, sıfırdan farklı olanların ise sürümleme olduğunu anlamak, satır oluşturma numaralarını hesaplama veya doğum ve mutasyonlar arasında ayrım yapmak zorunda olan özel değişiklik veri yakalama mantıkları uygularken mantıksal hataların önlenmesine yardımcı olur.