Prohoster > Blog > quản lý > Các loại đáng ngờ

Các loại đáng ngờ

Không có gì đáng nghi ngờ về sự xuất hiện của họ. Hơn nữa, chúng thậm chí còn có vẻ quen thuộc với bạn trong một thời gian dài. Nhưng đó chỉ là cho đến khi bạn kiểm tra chúng. Đây là nơi chúng thể hiện bản chất quỷ quyệt, hoạt động hoàn toàn khác so với những gì bạn mong đợi. Và đôi khi họ làm điều gì đó khiến bạn dựng tóc gáy - chẳng hạn như họ làm mất dữ liệu bí mật được giao phó cho họ. Khi bạn đối đầu với họ, họ tuyên bố rằng họ không biết nhau, mặc dù trong bóng tối họ làm việc chăm chỉ dưới cùng một nhóm. Cuối cùng đã đến lúc đưa chúng đến nơi có nước sạch. Chúng ta cũng hãy đối phó với những loại đáng ngờ này.

Việc gõ dữ liệu trong PostgreSQL, với tất cả logic của nó, đôi khi gây ra những bất ngờ rất kỳ lạ. Trong bài viết này, chúng tôi sẽ cố gắng làm rõ một số điều kỳ quặc của chúng, hiểu lý do dẫn đến hành vi kỳ lạ của chúng và hiểu cách không gặp vấn đề trong thực hành hàng ngày. Thực sự mà nói, tôi biên soạn bài viết này cũng như một loại sách tham khảo cho riêng mình, một cuốn sách tham khảo có thể dễ dàng tham khảo trong những vụ án gây tranh cãi. Vì vậy, nó sẽ được bổ sung khi những bất ngờ mới từ những loại đáng ngờ được phát hiện. Vì vậy, hãy bắt đầu nào, hỡi những người theo dõi cơ sở dữ liệu không biết mệt mỏi!

Hồ sơ số một. độ chính xác thực/kép/số/tiền

Có vẻ như các loại số ít gặp vấn đề nhất về mặt gây bất ngờ trong hành vi. Nhưng dù thế nào đi chăng nữa. Vì vậy, hãy bắt đầu với họ. Vì thế…

Quên cách đếm

SELECT 0.1::real = 0.1

?column?
boolean
---------
f

Có chuyện gì vậy? Vấn đề là PostgreSQL chuyển đổi hằng số chưa được gõ 0.1 thành độ chính xác gấp đôi và cố gắng so sánh nó với 0.1 của loại thực. Và đây là những ý nghĩa hoàn toàn khác nhau! Ý tưởng là biểu diễn số thực trong bộ nhớ máy. Vì 0.1 không thể được biểu diễn dưới dạng phân số nhị phân hữu hạn (nó sẽ là 0.0(0011) ở dạng nhị phân), các số có độ sâu bit khác nhau sẽ khác nhau, do đó kết quả là chúng không bằng nhau. Nói chung đây là chủ đề dành cho một bài viết riêng, tôi sẽ không viết chi tiết hơn ở đây.

Lỗi đến từ đâu?

SELECT double precision(1)

ERROR:  syntax error at or near "("
LINE 1: SELECT double precision(1)
                               ^
********** Ошибка **********
ERROR: syntax error at or near "("
SQL-состояние: 42601
Символ: 24

Nhiều người biết rằng PostgreSQL cho phép ký hiệu chức năng để truyền kiểu. Nghĩa là, bạn không chỉ có thể viết 1::int mà còn có thể viết int(1), tương đương. Nhưng không dành cho những loại có tên bao gồm nhiều từ! Do đó, nếu bạn muốn chuyển một giá trị số thành loại có độ chính xác gấp đôi ở dạng hàm, hãy sử dụng bí danh của loại float8 này, nghĩa là SELECT float8(1).

Cái gì lớn hơn vô cực?

SELECT 'Infinity'::double precision < 'NaN'::double precision

?column?
boolean
---------
t

Hãy nhìn xem nó như thế nào! Hóa ra có một thứ còn lớn hơn vô cùng, đó chính là NaN! Đồng thời, tài liệu PostgreSQL nhìn chúng tôi bằng con mắt trung thực và tuyên bố rằng NaN rõ ràng là lớn hơn bất kỳ số nào khác và do đó là vô cùng. Điều ngược lại cũng đúng với -NaN. Xin chào các bạn yêu toán! Nhưng chúng ta phải nhớ rằng tất cả điều này hoạt động trong bối cảnh số thực.

Làm tròn mắt

SELECT round('2.5'::double precision)
     , round('2.5'::numeric)

      round      |  round
double precision | numeric
-----------------+---------
2                | 3

Một lời chào bất ngờ khác từ căn cứ. Một lần nữa, hãy nhớ rằng độ chính xác kép và kiểu số có hiệu ứng làm tròn khác nhau. Đối với số - số thông thường, khi 0,5 được làm tròn và đối với độ chính xác kép - 0,5 được làm tròn về số nguyên chẵn gần nhất.

Tiền là thứ đặc biệt

SELECT '10'::money::float8

ERROR:  cannot cast type money to double precision
LINE 1: SELECT '10'::money::float8
                          ^
********** Ошибка **********
ERROR: cannot cast type money to double precision
SQL-состояние: 42846
Символ: 19

Theo PostgreSQL, tiền không phải là con số thực. Theo một số cá nhân cũng vậy. Chúng ta cần nhớ rằng chỉ có thể chuyển kiểu tiền sang loại số, cũng như chỉ có thể chuyển kiểu số sang loại tiền. Nhưng bây giờ bạn có thể chơi với nó như trái tim bạn mong muốn. Nhưng nó sẽ không giống số tiền đó.

Smallint và tạo chuỗi

SELECT *
  FROM generate_series(1::smallint, 5::smallint, 1::smallint)

ERROR:  function generate_series(smallint, smallint, smallint) is not unique
LINE 2:   FROM generate_series(1::smallint, 5::smallint, 1::smallint...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Ошибка **********
ERROR: function generate_series(smallint, smallint, smallint) is not unique
SQL-состояние: 42725
Подсказка: Could not choose a best candidate function. You might need to add explicit type casts.
Символ: 18

PostgreSQL không muốn lãng phí thời gian vào những chuyện vặt vãnh. Những chuỗi này dựa trên Smallint là gì? int, không kém! Do đó, khi cố gắng thực hiện truy vấn trên, cơ sở dữ liệu sẽ cố gắng chuyển Smallint sang một số kiểu số nguyên khác và thấy rằng có thể có một số lần chuyển đổi như vậy. Chọn diễn viên nào? Cô ấy không thể quyết định điều này và do đó gặp lỗi.

Tập tin số hai. "char"/char/varchar/văn bản

Một số điều kỳ lạ cũng hiện diện trong các kiểu nhân vật. Chúng ta hãy làm quen với họ quá.

Đây là những loại thủ đoạn gì?

SELECT 'ПЕТЯ'::"char"
     , 'ПЕТЯ'::"char"::bytea
     , 'ПЕТЯ'::char
     , 'ПЕТЯ'::char::bytea

 char  | bytea |    bpchar    | bytea
"char" | bytea | character(1) | bytea
-------+-------+--------------+--------
 ╨     | xd0  | П            | xd09f

Đây là loại "char" gì, đây là loại hề gì? Chúng ta không cần những thứ đó... Bởi vì nó giả vờ là một char bình thường, mặc dù nó ở trong dấu ngoặc kép. Và nó khác với char thông thường, không có dấu ngoặc kép, ở chỗ nó chỉ xuất ra byte đầu tiên của biểu diễn chuỗi, trong khi char bình thường xuất ra ký tự đầu tiên. Trong trường hợp của chúng tôi, ký tự đầu tiên là chữ P, trong biểu diễn unicode chiếm 2 byte, bằng chứng là việc chuyển đổi kết quả sang loại bytetea. Và loại “char” chỉ lấy byte đầu tiên của biểu diễn unicode này. Vậy tại sao lại cần loại này? Tài liệu PostgreSQL nói rằng đây là loại đặc biệt được sử dụng cho các nhu cầu đặc biệt. Vì vậy, chúng tôi khó có thể cần nó. Nhưng hãy nhìn vào mắt anh ấy và bạn sẽ không nhầm lẫn khi bắt gặp anh ấy với cách cư xử đặc biệt.

Không gian bổ sung. Xa mặt cách lòng

SELECT 'abc   '::char(6)::bytea
     , 'abc   '::char(6)::varchar(6)::bytea
     , 'abc   '::varchar(6)::bytea

     bytea     |   bytea  |     bytea
     bytea     |   bytea  |     bytea
---------------+----------+----------------
x616263202020 | x616263 | x616263202020

Hãy xem ví dụ đã cho. Tôi đặc biệt chuyển đổi tất cả các kết quả sang loại bytea để có thể nhìn thấy rõ ràng những gì ở đó. Đâu là khoảng trắng ở cuối sau khi truyền tới varchar(6)? Tài liệu nêu ngắn gọn: "Khi truyền giá trị của ký tự sang loại ký tự khác, khoảng trắng ở cuối sẽ bị loại bỏ." Sự không thích này phải được ghi nhớ. Và lưu ý rằng nếu một hằng chuỗi trích dẫn được truyền trực tiếp sang kiểu varchar(6), thì các khoảng trắng ở cuối sẽ được giữ nguyên. Đó là những điều kỳ diệu.

Tập tin số ba. json/jsonb

JSON là một cấu trúc riêng biệt có cuộc sống riêng. Do đó, các thực thể của nó và của PostgreSQL hơi khác nhau. Dưới đây là những ví dụ.

Johnson và Johnson. cảm nhận sự khác biệt

SELECT 'null'::jsonb IS NULL

?column?
boolean
---------
f

Vấn đề là JSON có thực thể null riêng, không giống với NULL trong PostgreSQL. Đồng thời, bản thân đối tượng JSON cũng có thể có giá trị NULL, do đó, biểu thức SELECT null::jsonb IS NULL (lưu ý rằng không có dấu ngoặc đơn) sẽ trả về true lần này.

Một chữ cái thay đổi mọi thứ

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::json

                     json
                     json
------------------------------------------------
{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}

---

SELECT '{"1": [1, 2, 3], "2": [4, 5, 6], "1": [7, 8, 9]}'::jsonb

             jsonb
             jsonb
--------------------------------
{"1": [7, 8, 9], "2": [4, 5, 6]}

Vấn đề là json và jsonb là những cấu trúc hoàn toàn khác nhau. Trong json, đối tượng được lưu trữ nguyên trạng và trong jsonb, nó đã được lưu trữ ở dạng cấu trúc được lập chỉ mục, phân tích cú pháp. Đó là lý do tại sao trong trường hợp thứ hai, giá trị của đối tượng bằng khóa 1 được thay thế từ [1, 2, 3] thành [7, 8, 9], nằm trong cấu trúc ở cuối cùng với cùng một khóa.

Đừng uống nước từ mặt bạn

SELECT '{"reading": 1.230e-5}'::jsonb
     , '{"reading": 1.230e-5}'::json

          jsonb         |         json
          jsonb         |         json
------------------------+----------------------
{"reading": 0.00001230} | {"reading": 1.230e-5}

PostgreSQL trong quá trình triển khai JSONB của nó sẽ thay đổi định dạng của số thực, đưa chúng về dạng cổ điển. Điều này không xảy ra đối với loại JSON. Hơi lạ một chút, nhưng anh ấy nói đúng.

Tập tin số bốn. ngày/giờ/dấu thời gian

Ngoài ra còn có một số điều kỳ lạ với loại ngày/giờ. Hãy nhìn vào chúng. Hãy để tôi đặt trước ngay rằng một số đặc điểm hành vi sẽ trở nên rõ ràng nếu bạn hiểu rõ bản chất của việc làm việc với các múi giờ. Nhưng đây cũng là một chủ đề cho một bài viết riêng biệt.

Tôi không hiểu bạn

SELECT '08-Jan-99'::date

ERROR:  date/time field value out of range: "08-Jan-99"
LINE 1: SELECT '08-Jan-99'::date
               ^
HINT:  Perhaps you need a different "datestyle" setting.
********** Ошибка **********
ERROR: date/time field value out of range: "08-Jan-99"
SQL-состояние: 22008
Подсказка: Perhaps you need a different "datestyle" setting.
Символ: 8

Có vẻ như có điều gì khó hiểu ở đây? Nhưng cơ sở dữ liệu vẫn không hiểu chúng ta đặt gì ở vị trí đầu tiên ở đây—năm hay ngày? Và cô ấy quyết định rằng đó là ngày 99 tháng 2008 năm XNUMX, điều này khiến cô ấy choáng váng. Nói chung, khi truyền ngày tháng ở định dạng văn bản, bạn cần kiểm tra thật cẩn thận xem cơ sở dữ liệu đã nhận dạng chúng chính xác đến mức nào (đặc biệt, phân tích tham số datestyle bằng lệnh SHOW datestyle), vì sự mơ hồ trong vấn đề này có thể rất tốn kém.

Bạn từ đâu đến?

SELECT '04:05 Europe/Moscow'::time

ERROR:  invalid input syntax for type time: "04:05 Europe/Moscow"
LINE 1: SELECT '04:05 Europe/Moscow'::time
               ^
********** Ошибка **********
ERROR: invalid input syntax for type time: "04:05 Europe/Moscow"
SQL-состояние: 22007
Символ: 8

Tại sao cơ sở dữ liệu không thể hiểu được thời gian được chỉ định rõ ràng? Bởi vì múi giờ không có chữ viết tắt mà là tên đầy đủ, điều này chỉ có ý nghĩa trong bối cảnh ngày tháng, vì nó tính đến lịch sử thay đổi múi giờ và nó không hoạt động nếu không có ngày. Và chính cách diễn đạt của dòng thời gian đã đặt ra câu hỏi - ý của người lập trình viên thực sự là gì? Vì vậy, mọi thứ ở đây đều hợp lý nếu bạn nhìn vào nó.

Có chuyện gì với anh ấy vậy?

Hãy tưởng tượng tình huống này. Bạn có một trường trong bảng có loại timestamptz. Bạn muốn lập chỉ mục nó. Nhưng bạn hiểu rằng việc xây dựng chỉ mục trên trường này không phải lúc nào cũng hợp lý do tính chọn lọc cao của nó (hầu như tất cả các giá trị thuộc loại này sẽ là duy nhất). Vì vậy, bạn quyết định giảm tính chọn lọc của chỉ mục bằng cách chuyển loại thành một ngày. Và bạn nhận được một điều ngạc nhiên:

CREATE INDEX "iIdent-DateLastUpdate"
  ON public."Ident" USING btree
  (("DTLastUpdate"::date));

ERROR:  functions in index expression must be marked IMMUTABLE
********** Ошибка **********
ERROR: functions in index expression must be marked IMMUTABLE
SQL-состояние: 42P17

Có chuyện gì vậy? Thực tế là để chuyển loại dấu thời gian thành loại ngày, giá trị của tham số hệ thống TimeZone được sử dụng, điều này làm cho hàm chuyển đổi loại phụ thuộc vào tham số tùy chỉnh, tức là. bay hơi. Những chức năng như vậy không được phép trong chỉ mục. Trong trường hợp này, bạn phải chỉ ra rõ ràng việc truyền kiểu được thực hiện ở múi giờ nào.

Khi bây giờ thậm chí không phải là bây giờ

Chúng ta đã quen với việc now() trả về ngày/giờ hiện tại, có tính đến múi giờ. Nhưng hãy nhìn vào các truy vấn sau:

START TRANSACTION;
SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

...

SELECT now();

            now
  timestamp with time zone
-----------------------------
2019-11-26 13:13:04.271419+03

COMMIT;

Ngày/giờ được trả về giống nhau cho dù thời gian đã trôi qua bao lâu kể từ yêu cầu trước đó! Có chuyện gì vậy? Thực tế là now() không phải là thời điểm hiện tại mà là thời gian bắt đầu của giao dịch hiện tại. Do đó, nó không thay đổi trong giao dịch. Bất kỳ truy vấn nào được đưa ra ngoài phạm vi của một giao dịch đều được bao bọc hoàn toàn trong một giao dịch, đó là lý do tại sao chúng tôi không nhận thấy rằng thời gian được trả về bởi một lệnh SELECT now() đơn giản; trên thực tế, không phải thời gian hiện tại... Nếu bạn muốn có được thời gian hiện tại trung thực, bạn cần sử dụng hàm clock_timestamp().

Tập tin số năm. chút

Lạ lùng một chút

SELECT '111'::bit(4)

 bit
bit(4)
------
1110

Các bit nên được thêm vào bên nào trong trường hợp mở rộng loại? Có vẻ như nó ở bên trái. Nhưng chỉ có cơ sở mới có ý kiến ​​​​khác về vấn đề này. Hãy cẩn thận: nếu số chữ số không khớp khi truyền một loại, bạn sẽ không nhận được thứ mình muốn. Điều này áp dụng cho cả việc thêm bit vào bên phải và cắt bớt bit. Ngoài ra ở bên phải...

Tập tin số sáu. Mảng

Ngay cả NULL cũng không kích hoạt

SELECT ARRAY[1, 2] || NULL

?column?
integer[]
---------
{1,2}

Như những người bình thường đã học về SQL, chúng tôi mong đợi kết quả của biểu thức này là NULL. Nhưng nó không có ở đó. Một mảng được trả về. Tại sao? Bởi vì trong trường hợp này, cơ sở chuyển NULL thành một mảng số nguyên và gọi ngầm hàm array_cat. Nhưng vẫn chưa rõ vì sao “mèo mảng” này không reset mảng. Hành vi này cũng chỉ cần được ghi nhớ.

Tóm tắt. Có rất nhiều điều kỳ lạ. Tất nhiên, hầu hết họ đều không phê phán đến mức nói về hành vi không phù hợp một cách trắng trợn. Và những thứ khác được giải thích bằng tính dễ sử dụng hoặc tần suất áp dụng của chúng trong những tình huống nhất định. Nhưng đồng thời, có rất nhiều điều bất ngờ. Vì vậy, bạn cần phải biết về họ. Nếu bạn tìm thấy bất kỳ điều gì kỳ lạ hoặc bất thường trong hành vi của bất kỳ loại nào, hãy viết vào phần bình luận, tôi sẽ vui lòng bổ sung vào hồ sơ có sẵn về chúng.

Nguồn: www.habr.com

Thêm một lời nhận xét