From d3919051536729f3b17792bedfc92dd1cac1505b Mon Sep 17 00:00:00 2001 From: Viktor Holmberg Date: Sat, 15 Mar 2025 21:07:01 +0000 Subject: [PATCH] isn extension: Make weak mode a GUC setting isn_weak mode used to be settable only using the isn_weak(boolean) function. This wasn't optimal, as this means it doesn't respect transactions. This change makes the isn.weak a GUC parameter instead. The isn_weak functions are retained for backwards compatibility. The also had a bug where they were marked IMMUTABLE, when in fact they are VOLATILE. This is also fixed. --- contrib/isn/Makefile | 2 +- contrib/isn/expected/isn.out | 11 +++ contrib/isn/isn--1.2--1.3.sql | 7 ++ contrib/isn/isn.c | 18 +++-- contrib/isn/isn.control | 2 +- contrib/isn/isn.h | 1 - contrib/isn/sql/isn.sql | 5 ++ doc/src/sgml/isn.sgml | 139 +++++++++++++++++++--------------- 8 files changed, 118 insertions(+), 67 deletions(-) create mode 100644 contrib/isn/isn--1.2--1.3.sql diff --git a/contrib/isn/Makefile b/contrib/isn/Makefile index 1037506c705..35d9249f3a7 100644 --- a/contrib/isn/Makefile +++ b/contrib/isn/Makefile @@ -4,7 +4,7 @@ MODULES = isn EXTENSION = isn DATA = isn--1.1.sql isn--1.1--1.2.sql \ - isn--1.0--1.1.sql + isn--1.0--1.1.sql isn--1.2--1.3.sql PGFILEDESC = "isn - data types for international product numbering standards" # the other .h files are data tables, we don't install those diff --git a/contrib/isn/expected/isn.out b/contrib/isn/expected/isn.out index 2f05b7eb861..b7addb508dd 100644 --- a/contrib/isn/expected/isn.out +++ b/contrib/isn/expected/isn.out @@ -279,6 +279,17 @@ FROM (VALUES ('9780123456786', 'UPC'), 9771234567003 | ISSN | t | | | | (3 rows) +SELECT '2222222222221'::ean13; +ERROR: invalid check digit for EAN13 number: "2222222222221", should be 2 +LINE 1: SELECT '2222222222221'::ean13; + ^ +SET isn.weak TO TRUE; +SELECT '2222222222221'::ean13; + ean13 +------------------ + 222-222222222-2! +(1 row) + -- -- cleanup -- diff --git a/contrib/isn/isn--1.2--1.3.sql b/contrib/isn/isn--1.2--1.3.sql new file mode 100644 index 00000000000..26d1662c2b8 --- /dev/null +++ b/contrib/isn/isn--1.2--1.3.sql @@ -0,0 +1,7 @@ +ALTER FUNCTION isn_weak() VOLATILE; +ALTER FUNCTION isn_weak(boolean) VOLATILE; + +COMMENT ON FUNCTION isn_weak(boolean) IS +'Sets the weak input mode, and returns the new setting. This function is retained for backward compatibility. The recommended way to set weak mode is via the isn.weak GUC parameter.'; +COMMENT ON FUNCTION isn_weak() IS +'Returns the current status of the weak mode. This function is retained for backward compatibility. Users should check the value of isn.weak instead.'; diff --git a/contrib/isn/isn.c b/contrib/isn/isn.c index db765ee490d..82c239f0ec1 100644 --- a/contrib/isn/isn.c +++ b/contrib/isn/isn.c @@ -21,6 +21,7 @@ #include "UPC.h" #include "fmgr.h" #include "isn.h" +#include "utils/guc.h" PG_MODULE_MAGIC; @@ -929,6 +930,18 @@ _PG_init(void) if (!check_table(UPC_range, UPC_index)) elog(ERROR, "UPC failed check"); } + + /* Define a GUC variable for weak mode. */ + DefineCustomBoolVariable("isn.weak", + "Accept invalid isns", + "When enabled, invalid numbers are accepted but flagged with an exclamation mark (!), indicating an incorrect check digit.", + &g_weak, + false, + PGC_USERSET, + 0, + NULL, + NULL, + NULL); } /* isn_out @@ -1109,17 +1122,12 @@ make_valid(PG_FUNCTION_ARGS) /* this function temporarily sets weak input flag * (to lose the strictness of check digit acceptance) - * It's a helper function, not intended to be used!! */ PG_FUNCTION_INFO_V1(accept_weak_input); Datum accept_weak_input(PG_FUNCTION_ARGS) { -#ifdef ISN_WEAK_MODE g_weak = PG_GETARG_BOOL(0); -#else - /* function has no effect */ -#endif /* ISN_WEAK_MODE */ PG_RETURN_BOOL(g_weak); } diff --git a/contrib/isn/isn.control b/contrib/isn/isn.control index 1cb5e2b2340..e7daea52b84 100644 --- a/contrib/isn/isn.control +++ b/contrib/isn/isn.control @@ -1,6 +1,6 @@ # isn extension comment = 'data types for international product numbering standards' -default_version = '1.2' +default_version = '1.3' module_pathname = '$libdir/isn' relocatable = true trusted = true diff --git a/contrib/isn/isn.h b/contrib/isn/isn.h index 038eb362c39..399896ad417 100644 --- a/contrib/isn/isn.h +++ b/contrib/isn/isn.h @@ -18,7 +18,6 @@ #include "fmgr.h" #undef ISN_DEBUG -#define ISN_WEAK_MODE /* * uint64 is the internal storage format for ISNs. diff --git a/contrib/isn/sql/isn.sql b/contrib/isn/sql/isn.sql index 2c2ea077d1e..3154dc7a8e7 100644 --- a/contrib/isn/sql/isn.sql +++ b/contrib/isn/sql/isn.sql @@ -120,6 +120,11 @@ FROM (VALUES ('9780123456786', 'UPC'), AS a(str,typ), LATERAL pg_input_error_info(a.str, a.typ) as errinfo; +SELECT '2222222222221'::ean13; +SET isn.weak TO TRUE; +SELECT '2222222222221'::ean13; + + -- -- cleanup -- diff --git a/doc/src/sgml/isn.sgml b/doc/src/sgml/isn.sgml index 45a867d98c2..75d5afd26b2 100644 --- a/doc/src/sgml/isn.sgml +++ b/doc/src/sgml/isn.sgml @@ -224,15 +224,72 @@ + + GUC Parameters + + + + isn.weak (boolean) + + isn.weak configuration parameter + + + + + Sets the weak input mode. Default: false (strong mode) + + + + + + Weak mode is used to be able to insert invalid data + into a table. Invalid means the check digit is wrong, not that there are + missing numbers. + + + + Why would you want to use the weak mode? Well, it could be that + you have a huge collection of ISBN numbers, and that there are so many of + them that for weird reasons some have the wrong check digit (perhaps the + numbers were scanned from a printed list and the OCR got the numbers wrong, + perhaps the numbers were manually captured... who knows). Anyway, the point + is you might want to clean the mess up, but you still want to be able to + have all the numbers in your database and maybe use an external tool to + locate the invalid numbers in the database so you can verify the + information and validate it more easily; so for example you'd want to + select all the invalid numbers in the table. + + + + When you insert invalid numbers in a table using the weak mode, the number + will be inserted with the corrected check digit, but it will be displayed + with an exclamation mark (!) at the end, for example + 0-11-000322-5!. This invalid marker can be checked with + the is_valid function and cleared with the + make_valid function. + + + + You can also force the insertion of invalid numbers even when not in the + weak mode, by appending the ! character at the end of the + number. + + + + Another special feature is that during input, you can write + ? in place of the check digit, and the correct check digit + will be inserted automatically. + + + Functions and Operators The isn module provides the standard comparison operators, - plus B-tree and hash indexing support for all these data types. In - addition there are several specialized functions; shown in . - In this table, - isn means any one of the module's data types. + plus B-tree and hash indexing support for all these data types. In + addition, there are several specialized functions, shown in . + In this table, isn means any one of the module's data types. @@ -252,89 +309,53 @@ - isn_weak - isn_weak ( boolean ) - boolean + make_valid + make_valid ( isn ) + isn - Sets the weak input mode, and returns new setting. + Validates an invalid number (clears the invalid flag). - isn_weak () + is_valid + is_valid ( isn ) boolean - Returns the current status of the weak mode. + Checks for the presence of the invalid flag. - make_valid - make_valid ( isn ) - isn + isn_weak + isn_weak ( boolean ) + boolean - Validates an invalid number (clears the invalid flag). + Sets the weak input mode, and returns the new setting. + This function is retained for backward compatibility. + The recommended way to set weak mode is via the isn.weak GUC parameter. - is_valid - is_valid ( isn ) + isn_weak () boolean - Checks for the presence of the invalid flag. + Returns the current status of the weak mode. + This function is retained for backward compatibility. + Users should check the value of isn.weak instead.
- - - Weak mode is used to be able to insert invalid data - into a table. Invalid means the check digit is wrong, not that there are - missing numbers. - - - - Why would you want to use the weak mode? Well, it could be that - you have a huge collection of ISBN numbers, and that there are so many of - them that for weird reasons some have the wrong check digit (perhaps the - numbers were scanned from a printed list and the OCR got the numbers wrong, - perhaps the numbers were manually captured... who knows). Anyway, the point - is you might want to clean the mess up, but you still want to be able to - have all the numbers in your database and maybe use an external tool to - locate the invalid numbers in the database so you can verify the - information and validate it more easily; so for example you'd want to - select all the invalid numbers in the table. - - - - When you insert invalid numbers in a table using the weak mode, the number - will be inserted with the corrected check digit, but it will be displayed - with an exclamation mark (!) at the end, for example - 0-11-000322-5!. This invalid marker can be checked with - the is_valid function and cleared with the - make_valid function. - - - - You can also force the insertion of invalid numbers even when not in the - weak mode, by appending the ! character at the end of the - number. - - - - Another special feature is that during input, you can write - ? in place of the check digit, and the correct check digit - will be inserted automatically. - @@ -366,11 +387,11 @@ SELECT issn('3251231?'); SELECT ismn('979047213542?'); --Using the weak mode: -SELECT isn_weak(true); +SET isn.weak TO true; INSERT INTO test VALUES('978-0-11-000533-4'); INSERT INTO test VALUES('9780141219307'); INSERT INTO test VALUES('2-205-00876-X'); -SELECT isn_weak(false); +SET isn.weak TO false; SELECT id FROM test WHERE NOT is_valid(id); UPDATE test SET id = make_valid(id) WHERE id = '2-205-00876-X!'; -- 2.48.1