ProHoster > blog > administrasi > PostgreSQL Antipatterns: Meneruskan Set dan Memilih ke SQL

PostgreSQL Antipatterns: Meneruskan Set dan Memilih ke SQL

Dari waktu ke waktu, pengembang membutuhkan berikan satu set parameter atau bahkan seluruh pilihan ke permintaan "di pintu masuk". Terkadang ada solusi yang sangat aneh untuk masalah ini.
PostgreSQL Antipatterns: Meneruskan Set dan Memilih ke SQL
Mari kita pergi "dari kebalikannya" dan lihat bagaimana tidak melakukannya, mengapa, dan bagaimana Anda bisa melakukannya dengan lebih baik.

Langsung "menyisipkan" nilai-nilai di badan permintaan

Biasanya terlihat seperti ini:

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

... atau seperti ini:

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

Tentang metode ini dikatakan, ditulis dan bahkan ditarik cukup:

PostgreSQL Antipatterns: Meneruskan Set dan Memilih ke SQL

Hampir selalu begitu jalur langsung ke injeksi SQL dan beban ekstra pada logika bisnis, yang dipaksa untuk "merekatkan" string kueri Anda.

Pendekatan ini sebagian dapat dibenarkan hanya jika diperlukan. menggunakan partisi di PostgreSQL versi 10 dan di bawahnya untuk paket yang lebih efisien. Dalam versi ini, daftar bagian yang dipindai ditentukan tanpa memperhitungkan parameter yang dikirimkan, hanya berdasarkan badan permintaan.

$n argumen

Menggunakan placeholder parameter yang baik, memungkinkan Anda untuk menggunakan PERNYATAAN YANG DIPERSIAPKAN, mengurangi beban baik pada logika bisnis (string kueri dibentuk dan dikirimkan hanya sekali) dan pada server basis data (penguraian ulang dan perencanaan tidak diperlukan untuk setiap contoh permintaan).

Jumlah variabel argumen

Masalah akan menunggu kita ketika kita ingin melewati sejumlah argumen yang tidak diketahui sebelumnya:

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

Jika Anda meninggalkan permintaan dalam formulir ini, meskipun itu akan menyelamatkan kami dari potensi suntikan, itu tetap akan menyebabkan kebutuhan untuk merekatkan / mengurai permintaan untuk setiap opsi dari jumlah argumen. Sudah lebih baik daripada melakukannya setiap saat, tetapi Anda bisa melakukannya tanpa itu.

Cukup dengan melewatkan hanya satu parameter yang berisi representasi serial dari array:

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

Satu-satunya perbedaan adalah kebutuhan untuk mengonversi argumen secara eksplisit ke tipe array yang diinginkan. Tapi ini tidak menimbulkan masalah, karena kita sudah tahu sebelumnya kemana kita menuju.

Transfer sampel (matriks)

Biasanya ini semua jenis opsi untuk mentransfer set data untuk dimasukkan ke dalam database "dalam satu permintaan":

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

Selain masalah yang dijelaskan di atas dengan permintaan "pengeleman ulang", ini juga dapat membawa kita ke kehabisan memori dan server crash. Alasannya sederhana - PG mencadangkan memori tambahan untuk argumen, dan jumlah record dalam kumpulan hanya dibatasi oleh Daftar Keinginan aplikasi logika bisnis. Dalam kasus-kasus klinis terutama perlu untuk melihat argumen "bernomor" yang lebih besar dari $9000 - jangan lakukan dengan cara ini.

Mari menulis ulang kueri, sudah menerapkan serialisasi "dua tingkat".:

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;

Ya, dalam kasus nilai "kompleks" di dalam array, nilai tersebut harus dibingkai dengan tanda kutip.
Jelas bahwa dengan cara ini Anda dapat "memperluas" pilihan dengan jumlah bidang yang berubah-ubah.

paling tidak bersarang, paling tidak bersarang,…

Dari waktu ke waktu ada opsi untuk meneruskan alih-alih "array array" beberapa "array kolom" yang saya sebutkan di artikel terakhir:

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

Dengan metode ini, jika Anda membuat kesalahan saat membuat daftar nilai untuk kolom yang berbeda, sangat mudah untuk mendapatkannya sepenuhnya hasil yang tidak terduga, yang juga bergantung pada versi server:

-- $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

Mulai dari versi 9.3, PostgreSQL memiliki fungsi lengkap untuk bekerja dengan tipe json. Oleh karena itu, jika parameter input Anda ditentukan di browser, Anda dapat langsung membentuknya objek json untuk kueri SQL:

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

Untuk versi sebelumnya, metode yang sama dapat digunakan untuk masing-masing (hstore), tetapi "melipat" yang benar dengan keluar dari objek kompleks di hstore dapat menyebabkan masalah.

json_populate_recordset

Jika Anda mengetahui sebelumnya bahwa data dari array json "input" akan mengisi beberapa tabel, Anda dapat menyimpan banyak di bidang "dereferencing" dan mentransmisikan ke jenis yang diinginkan menggunakan fungsi json_populate_recordset:

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

json_to_recordset

Dan fungsi ini hanya akan "memperluas" array objek yang diteruskan ke dalam pilihan, tanpa bergantung pada format tabel:

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

TABEL SEMENTARA

Tetapi jika jumlah data dalam sampel yang ditransmisikan sangat besar, sulit untuk membuangnya ke dalam satu parameter berseri, dan terkadang tidak mungkin, karena memerlukan satu kali alokasi memori yang besar. Misalnya, Anda perlu mengumpulkan sejumlah besar data peristiwa dari sistem eksternal untuk waktu yang sangat lama, lalu Anda ingin memprosesnya satu kali di sisi database.

Dalam hal ini, solusi terbaik adalah menggunakan tabel sementara:

CREATE TEMPORARY TABLE tbl(k text, v integer);
...
INSERT INTO tbl(k, v) VALUES($1, $2); -- ΠΏΠΎΠ²Ρ‚ΠΎΡ€ΠΈΡ‚ΡŒ ΠΌΠ½ΠΎΠ³ΠΎ-ΠΌΠ½ΠΎΠ³ΠΎ Ρ€Π°Π·
...
-- Ρ‚ΡƒΡ‚ Π΄Π΅Π»Π°Π΅ΠΌ Ρ‡Ρ‚ΠΎ-Ρ‚ΠΎ ΠΏΠΎΠ»Π΅Π·Π½ΠΎΠ΅ со всСй этой Ρ‚Π°Π±Π»ΠΈΡ†Π΅ΠΉ Ρ†Π΅Π»ΠΈΠΊΠΎΠΌ

Metodenya bagus untuk transmisi volume besar yang jarang data.
Dari sudut pandang deskripsi struktur datanya, tabel sementara berbeda dari tabel "biasa" hanya dalam satu fitur. dalam tabel sistem pg_class, dan dalam pg_type, pg_depend, pg_attribute, pg_attrdef, ... - dan tidak ada sama sekali.

Oleh karena itu, dalam sistem web dengan sejumlah besar koneksi berumur pendek untuk masing-masingnya, tabel seperti itu akan menghasilkan catatan sistem baru setiap kali, yang dihapus saat koneksi ke database ditutup. Pada akhirnya, penggunaan TEMP TABLE yang tidak terkontrol menyebabkan "pembengkakan" tabel di pg_catalog dan memperlambat banyak operasi yang menggunakannya.
Tentu saja, ini bisa diperangi berkala lulus VAKUM PENUH sesuai dengan tabel katalog sistem.

Variabel Sesi

Misalkan pemrosesan data dari kasus sebelumnya cukup rumit untuk satu kueri SQL, tetapi Anda ingin melakukannya cukup sering. Artinya, kami ingin menggunakan pemrosesan prosedural di LAKUKAN blok, tetapi menggunakan transfer data melalui tabel sementara akan terlalu mahal.

Kami juga tidak dapat menggunakan $n-parameter untuk diteruskan ke blok anonim. Variabel sesi dan fungsinya akan membantu kita keluar dari situasi tersebut. pengaturan_saat ini.

Sebelum versi 9.2, Anda harus melakukan pra-konfigurasi ruang nama khusus kelas_variabel_khusus untuk variabel sesi "mereka". Pada versi saat ini, Anda dapat menulis sesuatu seperti ini:

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

Ada solusi lain yang tersedia dalam bahasa prosedural lain yang didukung.

Tahu lebih banyak cara? Bagikan di komentar!

Sumber: www.habr.com

Tambah komentar