Hello hackers,
Following test-sequence causing an error "cache lookup failed for collation
0";
postgres:5432 [42106]=# create table foobar(a bytea primary key, b int);
CREATE TABLE
postgres:5432 [42106]=# insert into foobar
values('\x4c835521685c46ee827ab83d376cf028', 1);
INSERT 0 1
postgres:5432 [42106]=# \d+ foobar
Table "public.foobar"
Column | Type | Collation | Nullable | Default | Storage | Stats
target | Description
--------+---------+-----------+----------+---------+----------+--------------+-------------
a | bytea | | not null | | extended
| |
b | integer | | | | plain
| |
Indexes:
"foobar_pkey" PRIMARY KEY, btree (a)
Access method: heap
postgres:5432 [42106]=# select * from foobar where a like '%1%';
ERROR: cache lookup failed for collation 0
---
After debugging it, I have observed that the code in question was added by
commit 5e1963fb764e9cc092e0f7b58b28985c311431d9 which added support for the
collations with nondeterministic comparison.
The error is coming from get_collation_isdeterministic() when colloid
passed is 0. I think like we do in get_collation_name(), we should return
false here when such collation oid does not exist.
Attached patch doing that change and re-arranged the code to look similar
to get_collation_name(). Also, added small testcase.
---
However, I have not fully understood the code changes done by the said
commit and thus the current behavior i.e. cache lookup error, might be the
expected one. But if that's the case, I kindly request to please explain
why that is expected.
Thanks
--
Jeevan Chalke
Technical Architect, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index b4f2d0f..69e5d88 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -942,16 +942,19 @@ bool
get_collation_isdeterministic(Oid colloid)
{
HeapTuple tp;
- Form_pg_collation colltup;
- bool result;
tp = SearchSysCache1(COLLOID, ObjectIdGetDatum(colloid));
- if (!HeapTupleIsValid(tp))
- elog(ERROR, "cache lookup failed for collation %u", colloid);
- colltup = (Form_pg_collation) GETSTRUCT(tp);
- result = colltup->collisdeterministic;
- ReleaseSysCache(tp);
- return result;
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_collation colltup = (Form_pg_collation) GETSTRUCT(tp);
+ bool result;
+
+ result = colltup->collisdeterministic;
+ ReleaseSysCache(tp);
+ return result;
+ }
+ else
+ return false;
}
/* ---------- CONSTRAINT CACHE ---------- */
diff --git a/src/test/regress/expected/collate.out b/src/test/regress/expected/collate.out
index 0dee7d7..a8e1f6e 100644
--- a/src/test/regress/expected/collate.out
+++ b/src/test/regress/expected/collate.out
@@ -676,13 +676,19 @@ SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1));
"C"
(1 row)
+CREATE TABLE byteatable(a bytea primary key, b int);
+SELECT * FROM byteatable WHERE a LIKE '%1%';
+ a | b
+---+---
+(0 rows)
+
--
-- Clean up. Many of these table names will be re-used if the user is
-- trying to run any platform-specific collation tests later, so we
-- must get rid of them.
--
DROP SCHEMA collate_tests CASCADE;
-NOTICE: drop cascades to 17 other objects
+NOTICE: drop cascades to 18 other objects
DETAIL: drop cascades to table collate_test1
drop cascades to table collate_test_like
drop cascades to table collate_test2
@@ -700,3 +706,4 @@ drop cascades to table collate_test21
drop cascades to table collate_test22
drop cascades to collation mycoll2
drop cascades to table collate_test23
+drop cascades to table byteatable
diff --git a/src/test/regress/sql/collate.sql b/src/test/regress/sql/collate.sql
index 89de26a..124daf6 100644
--- a/src/test/regress/sql/collate.sql
+++ b/src/test/regress/sql/collate.sql
@@ -259,6 +259,9 @@ SELECT collation for ((SELECT a FROM collate_test1 LIMIT 1)); -- non-collatable
SELECT collation for ((SELECT b FROM collate_test1 LIMIT 1));
+CREATE TABLE byteatable(a bytea primary key, b int);
+SELECT * FROM byteatable WHERE a LIKE '%1%';
+
--
-- Clean up. Many of these table names will be re-used if the user is
-- trying to run any platform-specific collation tests later, so we
