Antipatterns PostgreSQL: معرفات فريدة

في كثير من الأحيان ، يحتاج المطور إلى إنشاء بعض المعرفات الفريدة للسجلات في جدول PostgreSQL - سواء عند إدراج السجلات أو عند قراءتها.

طاولة العداد

يبدو - ما هو أسهل؟ أنشأنا لوحة منفصلة ، فيها - دخول مع عداد. نحتاج إلى الحصول على معرف جديد - اقرأ من هناك لكتابة قيمة جديدة - افعل ذلك UPDATE...



لا تفعل ذلك ! لأنه غدا سيكون عليك حل المشاكل:

• الأقفال المتداخلة المستمرة عند UPDATE
  
  الاطلاع على PostgreSQL Antipatterns: قتال جحافل "الموتى"
• التدهور التدريجي لسرعة الوصول إلى بيانات جدول العداد
  
  راجع PostgreSQL Antipatterns: تحديث جدول كبير تحت الحمل
• ... والحاجة إلى تنظيفه من خلال المعاملات النشطة التي ستزعجك ،
  
  راجع DBA: عندما يمر VACUUM ، نقوم بتنظيف الجدول يدويًا

كائن SEQUENCE

لمثل هذه المهام ، توفر PostgreSQL كيانًا منفصلاً - SEQUENCE. إنها غير معاملات ، أي أنها لا تسبب أقفالًا ، لكن معاملتين "متوازيتين" ستتلقى بالتأكيد قيمًا مختلفة .



للحصول على المعرف التالي من تسلسل ، ما عليك سوى استخدام الوظيفة nextval:

SELECT nextval('seq_name'::regclass);

تحتاج أحيانًا إلى الحصول على عدة معرفات في وقت واحد - للتسجيل المتدفق عبر COPY ، على سبيل المثال. استخدام لهذا خطأsetval(currval() + N) جوهري ! لسبب بسيط وهو أنه بين الاستدعاءات للوظائف "الداخلية" ( ) و "الخارجية" ( ) ، يمكن أن تغير المعاملة المتزامنة القيمة الحالية للتسلسل. الطريقة الصحيحة هي الاتصال بالعدد المطلوب من المرات:currvalsetvalnextval

SELECT
  nextval('seq_name'::regclass)
FROM
  generate_series(1, N);

المسلسل الزائف

ليس من الملائم العمل مع التسلسلات في الوضع "اليدوي". لكن مهمتنا النموذجية هي ضمان إدخال سجل جديد بمعرف تسلسل جديد! لهذا الغرض على وجه الخصوص ، تم اختراع PostgreSQL serial، والتي عند إنشاء جدول "تتوسع" إلى شيء مثل . ليست هناك حاجة لتذكر اسم التسلسل الذي تم إنشاؤه تلقائيًا والمرتبط بالحقل ، فهناك وظيفة لهذا . يمكن استخدام نفس الوظيفة في الاستبدالات الخاصة بك - على سبيل المثال ، إذا كانت هناك حاجة لعمل تسلسل مشترك لعدة جداول في وقت واحد. ومع ذلك ، نظرًا لأن العمل مع التسلسل غير تعاملي ، إذا تم استلام المعرف من خلال معاملة التراجع ، فسيكون تسلسل المعرفات في سجلات الجدول المحفوظة "تسريبًا"id integer NOT NULL DEFAULT nextval('tbl_id_seq')



pg_get_serial_sequence(table_name, column_name)DEFAULT



...

الأعمدة المولدة

بدءًا من PostgreSQL 10 ، من الممكن التصريح عن عمود هوية ( GENERATED AS IDENTITY) يتوافق مع معيار SQL: 2003. في المتغير ، GENERATED BY DEFAULTالسلوك متكافئ serial، لكن مع GENERATED ALWAYSكل شيء أكثر إثارة للاهتمام:

CREATE TABLE tbl(
  id
    integer
      GENERATED ALWAYS AS IDENTITY
);

INSERT INTO tbl(id) VALUES(DEFAULT);
--   :     10 .
INSERT INTO tbl(id) VALUES(1);
-- ERROR:  cannot insert into column "id"
-- DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT:  Use OVERRIDING SYSTEM VALUE to override.

نعم ، لإدراج قيمة محددة "عبر" مثل هذا العمود ، يجب عليك الانتقال إلى عمل إضافي باستخدام OVERRIDING SYSTEM VALUE:

INSERT INTO tbl(id) OVERRIDING SYSTEM VALUE VALUES(1);
--   :     11 .

لاحظ أنه لدينا الآن قيمتان متطابقتان في الجدول id = 1- أي أن GENERATED لا تفرض شروطًا ومؤشرات فريدة إضافية ، ولكنها عبارة عن إقرار حصري أيضًا serial.



بشكل عام ، في إصدارات PostgreSQL الحديثة ، يتم إهمال استخدام المسلسل ، مع البديل المفضل لـ GENERATED. ربما باستثناء حالة دعم التطبيقات ذات الإصدارات المتقاطعة التي تعمل مع PGs أقل من 10.

UUID الذي تم إنشاؤه

كل شيء على ما يرام طالما أنك تعمل ضمن مثيل قاعدة بيانات واحد. ولكن عندما يكون هناك العديد منها ، لا توجد طريقة مناسبة لمزامنة التسلسلات (ومع ذلك ، فإن هذا لا يمنعك من مزامنتها "بشكل غير مناسب " ، إذا كنت تريد ذلك حقًا). هذا هو حيث نوع UUIDو ظائف لتوليد القيم لأنه يهب لنجدة . عادةً ما أستخدمه uuid_generate_v4()باعتباره الأكثر "عرضيًا".

مجالات النظام المخفية

منضدة / ctid

في بعض الأحيان ، عند جلب السجلات من جدول ، تحتاج إلى معالجة سجل "مادي" محدد بطريقة أو بأخرى ، أو معرفة أي قسم معين تم الحصول على سجل معين من خلاله عند الوصول إلى الجدول "الأصل" باستخدام الوراثة .



في هذه الحالة ، ستساعدنا حقول النظام المخفية الموجودة في كل سجل على :

• tableoidيخزن oid-id من الجدول - أي tableoid::regclass::textيعطي اسم قسم جدول معين
• ctid - العنوان "المادي" للسجل بالتنسيق (<>,<>)

على سبيل المثال ، ctidيمكن استخدامه للعمليات مع جدول بدون مفتاح أساسي ، ولكن tableoidلتنفيذ أنواع معينة من المفاتيح الخارجية.

أويد

كان من الممكن الإعلان عن ما يصل إلى 11 من PostgreSQL عند إنشاء جدول السمات WITH OIDS:

CREATE TABLE tbl(id serial) WITH OIDS;

كل دخول في هذا الجدول يحصل على حقل مخفي إضافية oidمع فريدة من نوعها على مستوى العالم قيمة داخل قاعدة البيانات - كما انها نظمت ل جداول النظام مثل pg_class، pg_namespace...



عند إدراج رقما قياسيا في قيمة الجدول ولدت وعاد على الفور إلى نتيجة الاستعلام:

INSERT INTO tbl(id) VALUES(DEFAULT);

  :   OID 16400   11 .

مثل هذا الحقل غير مرئي لاستعلام جدول "عادي":

SELECT * FROM tbl;

id
--
 1

يجب طلب ذلك ، مثل حقول النظام الأخرى ، بشكل صريح:

SELECT tableoid, ctid, xmin, xmax, cmin, cmax, oid, * FROM tbl;

tableoid | ctid  | xmin | xmax | cmin | cmax | oid   | id
---------------------------------------------------------
   16596 | (0,1) |  572 |    0 |    0 |    0 | 16400 |  1

صحيح أن القيمة oidهي 32 بت فقط ، ولذلك فمن السهل جدا الحصول على تجاوز، وبعد ذلك oidلن يكون من الممكن حتى لإنشاء أي جدول (انها تحتاج الى واحد جديد !). لذلك ، منذ PostgreSQL 12 ، WITH OIDSلم يعد مدعومًا .

الوقت "عادل" clock_timestamp

في بعض الأحيان ، عند تشغيل استعلام أو إجراء لفترة طويلة ، فأنت تريد ربط الوقت "الحالي" بالسجل. ينتظر الفشل أي شخص يحاول استخدام الوظيفة للقيام بذلك now()- سيعيد نفس القيمة طوال المعاملة بأكملها .



للحصول على الوقت "الآن" ، هناك وظيفة clock_timestamp()(ومجموعة أخرى من إخوانها). يمكن رؤية الفرق بين سلوك هذه الوظائف في مثال استعلام بسيط:

SELECT
  now()
, clock_timestamp()
FROM
  generate_series(1, 4);

              now              |        clock_timestamp
-------------------------------+-------------------------------
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626758+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626763+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626764+03
 2020-08-19 16:26:05.626629+03 | 2020-08-19 16:26:05.626765+03