ProHoster > Blog > yönetim > PostgreSQL Antipatterns: Kümeleri ve Seçimleri SQL'e Aktarma

PostgreSQL Antipatterns: Kümeleri ve Seçimleri SQL'e Aktarma

Geliştiricinin zaman zaman ihtiyaç duyduğu isteğe bir dizi parametre veya hatta tüm bir seçimi iletin "girişte". Bazen bu soruna çok garip çözümler vardır.
PostgreSQL Antipatterns: Kümeleri ve Seçimleri SQL'e Aktarma
"Tersten" gidelim ve bunu nasıl, neden ve nasıl daha iyi yapabileceğinizi görelim.

İstek gövdesine değerlerin doğrudan "eklenmesi"

Genellikle şöyle bir şeye benziyor:

query = "SELECT * FROM tbl WHERE id = " + value

... veya bunun gibi:

query = "SELECT * FROM tbl WHERE id = :param".format(param=value)

Bu yöntem hakkında söylenmiş, yazılmış ve hatta çizilmiş yeterli:

PostgreSQL Antipatterns: Kümeleri ve Seçimleri SQL'e Aktarma

Neredeyse her zaman SQL enjeksiyonuna doğrudan yol ve sorgu dizginizi "yapıştırmak" zorunda kalan iş mantığı üzerinde fazladan bir yük.

Bu yaklaşım ancak gerekliyse kısmen haklı çıkarılabilir. bölümleme kullan Daha verimli bir plan için PostgreSQL sürüm 10 ve altı. Bu sürümlerde, taranan bölümlerin listesi, iletilen parametreler dikkate alınmadan, yalnızca istek gövdesi bazında belirlenir.

$n bağımsız değişken

kullanımı yer tutucular parametreler iyidir, kullanmanıza izin verir HAZIRLANAN TABLOLAR, hem iş mantığındaki (sorgu dizesi yalnızca bir kez oluşturulur ve iletilir) hem de veritabanı sunucusundaki (isteğin her örneği için yeniden ayrıştırma ve planlama gerekmez) yükü azaltır.

Değişken bağımsız değişken sayısı

Önceden bilinmeyen sayıda argüman iletmek istediğimizde sorunlar bizi bekliyor olacak:

... id IN ($1, $2, $3, ...) -- $1 : 2, $2 : 3, $3 : 5, ...

Talebi bu formda bırakırsanız, bizi olası enjeksiyonlardan kurtaracak olsa da, yine de talebi yapıştırma / ayrıştırma ihtiyacına yol açacaktır. argüman sayısından her seçenek için. Zaten her seferinde yapmaktan daha iyidir, ama onsuz da yapabilirsiniz.

içeren tek bir parametreyi geçmek yeterlidir. bir dizinin serileştirilmiş gösterimi:

... id = ANY($1::integer[]) -- $1 : '{2,3,5,8,13}'

Tek fark, bağımsız değişkeni açıkça istenen dizi türüne dönüştürme ihtiyacıdır. Ancak bu, nereye hitap ettiğimizi önceden bildiğimiz için sorun yaratmaz.

Numune transferi (matris)

Genellikle bunlar, veri setlerini "tek bir istekte" veritabanına eklemek üzere aktarmak için her türlü seçenektir:

INSERT INTO tbl(k, v) VALUES($1,$2),($3,$4),...

Talebin "yeniden yapıştırılması" ile ilgili yukarıda açıklanan sorunlara ek olarak, bu da bizi şu sonuçlara götürebilir: bellek yetersiz ve sunucu çökmesi. Nedeni basit - PG bağımsız değişkenler için ek bellek ayırır ve kümedeki kayıt sayısı yalnızca iş mantığı uygulaması Wishlist ile sınırlıdır. Özellikle klinik vakalarda görmek gerekliydi. 9000$'dan büyük "numaralı" bağımsız değişkenler - bu şekilde yapma.

Şimdiden uygulayarak sorguyu yeniden yazalım "iki seviyeli" serileştirme:

INSERT INTO tbl
SELECT
  unnest[1]::text k
, unnest[2]::integer v
FROM (
  SELECT
    unnest($1::text[])::text[] -- $1 : '{"{a,1}","{b,2}","{c,3}","{d,4}"}'
) T;

Evet, bir dizi içindeki "karmaşık" değerler söz konusu olduğunda, bunların tırnak işaretleri ile çerçevelenmesi gerekir.
Bu şekilde seçimi istediğiniz sayıda alanla "genişletebileceğiniz" açıktır.

huzursuzluk, huzursuzluk,…

Zaman zaman bir "dizi dizisi" yerine, bahsettiğim birkaç "sütun dizisi" yerine geçmek için seçenekler vardır. son makalede:

SELECT
  unnest($1::text[]) k
, unnest($2::integer[]) v;

Bu metot ile farklı kolonlar için değer listeleri oluştururken bir hata yaparsanız tamamen elde etmek çok kolaydır. beklenmedik sonuçlar, ayrıca sunucu sürümüne de bağlıdır:

-- $1 : '{a,b,c}', $2 : '{1,2}'
-- PostgreSQL 9.4
k | v
-----
a | 1
b | 2
c | 1
a | 2
b | 1
c | 2
-- PostgreSQL 11
k | v
-----
a | 1
b | 2
c |

JSON

9.3 sürümünden başlayarak, PostgreSQL, json türüyle çalışmak için tam teşekküllü işlevlere sahiptir. Bu nedenle, giriş parametreleriniz tarayıcıda tanımlıysa, hemen orada ve form oluşturabilirsiniz. SQL sorgusu için json nesnesi:

SELECT
  key k
, value v
FROM
  json_each($1::json); -- '{"a":1,"b":2,"c":3,"d":4}'

Önceki sürümler için, aynı yöntem aşağıdakiler için kullanılabilir: her(mağaza), ancak hstore'da kaçan karmaşık nesnelerle doğru "katlama" sorunlara neden olabilir.

json_populate_recordset

"Giriş" json dizisinden gelen verilerin bir tabloyu doldurmaya gideceğini önceden biliyorsanız, json_populate_recordset işlevini kullanarak "referanslama" alanlarında ve istenen türlere atamada çok şey kaydedebilirsiniz:

SELECT
  *
FROM
  json_populate_recordset(
    NULL::pg_class
  , $1::json -- $1 : '[{"relname":"pg_class","oid":1262},{"relname":"pg_namespace","oid":2615}]'
  );

json_to_recordset

Ve bu işlev, tablo biçimine güvenmeden, iletilen nesne dizisini basitçe bir seçime "genişletecektir":

SELECT
  *
FROM
  json_to_recordset($1::json) T(k text, v integer);
-- $1 : '[{"k":"a","v":1},{"k":"b","v":2}]'
k | v
-----
a | 1
b | 2

GEÇİCİ TABLO

Ancak iletilen örnekteki veri miktarı çok büyükse, bunu tek bir seri hale getirilmiş parametreye atmak zordur ve bazen imkansızdır, çünkü bir kerelik büyük bellek ayırma. Örneğin, harici bir sistemden çok uzun bir süre büyük miktarda olay verisi toplamanız gerekiyor ve ardından bunu veritabanı tarafında tek seferlik işlemek istiyorsunuz.

Bu durumda en iyi çözüm kullanmak olacaktır. geçici tablolar:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- повторить много-много раз
...
-- тут делаем что-то полезное со всей этой таблицей целиком

yöntem iyi büyük hacimlerin nadiren iletilmesi için verileri.
Verilerinin yapısını açıklama açısından, geçici bir tablo "normal" bir tablodan yalnızca bir özellikte farklılık gösterir. pg_class sistem tablosundaVe içinde pg_type, pg_depend, pg_attribute, pg_attrdef, ... - ve hiçbir şey.

Bu nedenle, her biri için çok sayıda kısa ömürlü bağlantıya sahip web sistemlerinde, böyle bir tablo her seferinde yeni sistem kayıtları üretecek ve veritabanına bağlantı kapatıldığında silinecektir. Sonunda, TEMP TABLE'ın kontrolsüz kullanımı, pg_catalog'daki tabloların "şişmesine" yol açar ve bunları kullanan birçok işlemi yavaşlatmak.
Elbette bununla mücadele edilebilir. periyodik geçiş VAKUM TAM sistem katalog tablolarına göre.

Oturum Değişkenleri

Önceki durumdaki verilerin işlenmesinin tek bir SQL sorgusu için oldukça karmaşık olduğunu, ancak bunu oldukça sık yapmak istediğinizi varsayalım. Yani, prosedürel işlemeyi kullanmak istiyoruz. engelle, ancak geçici tablolar aracılığıyla veri aktarımını kullanmak çok pahalı olacaktır.

Ayrıca anonim bir bloğa geçmek için $n-parameters kullanamayız. Oturum değişkenleri ve işlev, durumdan çıkmamıza yardımcı olacaktır. şimdiki ayar.

9.2 sürümünden önce, önceden yapılandırmanız gerekiyordu özel ad alanı özel_değişken_sınıflar "onların" oturum değişkenleri için. Mevcut sürümlerde şöyle bir şey yazabilirsiniz:

SET my.val = '{1,2,3}';
DO $$
DECLARE
  id integer;
BEGIN
  FOR id IN (SELECT unnest(current_setting('my.val')::integer[])) LOOP
    RAISE NOTICE 'id : %', id;
  END LOOP;
END;
$$ LANGUAGE plpgsql;
-- NOTICE:  id : 1
-- NOTICE:  id : 2
-- NOTICE:  id : 3

Desteklenen diğer yordamsal dillerde kullanılabilen başka çözümler de vardır.

Daha fazla yol biliyor musun? Yorumlarda paylaşın!

Kaynak: habr.com

Yorum ekle