postgresql - 如何在具有唯一约束的列中插入长文本(> 3K 字符)
问题描述
在 postgres 中插入太长的文本存在一些问题。当我有一个带有文本的简单表格时,我可以根据需要插入文本(我测试了多达 40K 个字符)。但是,当我添加unique
约束时,我开始遇到一个奇怪的btree
问题,请参阅下面的最小工作示例 (MWE)
MWE:
#!/bin/bash
N=4096 # Aiming for a URL of length 4,096 characters
DB_NAME='foo'
# Generate random N character alphanumeric string of lenght 4,096
URL="http://www.$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w $(($N-15)) | head -n 1).com"
# Case 1 we have a table ('website') which has a single column with no
# constraints
TABLE_NAME='website'
sudo -u postgres psql -c "drop database if exists $DB_NAME;"
sudo -u postgres psql -c "create database $DB_NAME;"
sudo -u postgres psql -d $DB_NAME -c "drop table if exists $TABLE_NAME;"
sudo -u postgres psql -d $DB_NAME -c "create table $TABLE_NAME (url text);"
sudo -u postgres psql -d $DB_NAME -c "insert into $TABLE_NAME (url) values ('$URL');"
# Case 2 we have a table ('website2') which has a single column which must be
# unique
TABLE_NAME='website2'
sudo -u postgres psql -c "drop database if exists $DB_NAME;"
sudo -u postgres psql -c "create database $DB_NAME;"
sudo -u postgres psql -d $DB_NAME -c "drop table if exists $TABLE_NAME;"
sudo -u postgres psql -d $DB_NAME -c "create table $TABLE_NAME (url text unique);"
sudo -u postgres psql -d $DB_NAME -c "insert into $TABLE_NAME (url) values ('$URL');"
输出:
$ ./test.sh
DROP DATABASE
CREATE DATABASE
NOTICE: table "website" does not exist, skipping
DROP TABLE
CREATE TABLE
INSERT 0 1
DROP DATABASE
CREATE DATABASE
NOTICE: table "website2" does not exist, skipping
DROP TABLE
CREATE TABLE
ERROR: index row size 4112 exceeds btree version 4 maximum 2704 for index "website2_url_key"
DETAIL: Index row references tuple (0,1) in relation "website2".
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
问题:如果我的文本过长(>3K 字符),我该怎么办?
- 有什么方法可以禁用这个 btree 错误吗?
- 我应该删除
unique
约束并仅在应用程序级别进行检查吗? - 我应该压缩所有的 URL 吗?
- 是否根本不可能通过 PSQL 实现这一目标?
解决方案
我在大型 VARCHAR 的 UNIQUE 约束中找到了一个解决方案 - PostgreSQL
解决方案:
N=4096 # Aiming for a URL of length 4,096 characters
DB_NAME='foo'
# Generate random N character alphanumeric string of lenght 4,096
URL="http://www.$(cat /dev/urandom | tr -dc 'a-zA-Z0-9' | fold -w $(($N-15)) | head -n 1).com"
# To get over the length limitation of a text column with a unique
# constraint we carry out the following
# 1) Remove UNIQUE constraint from column
# 2) Add UNIQUE constraint for md5 of column
TABLE_NAME='websitemd5'
sudo -u postgres psql -c "drop database if exists $DB_NAME;"
sudo -u postgres psql -c "create database $DB_NAME;"
sudo -u postgres psql -d $DB_NAME -c "drop table if exists $TABLE_NAME;"
sudo -u postgres psql -d $DB_NAME -c "create table $TABLE_NAME (url text);"
sudo -u postgres psql -d $DB_NAME -c "create unique index unique_url_index on $TABLE_NAME (md5(url));"
sudo -u postgres psql -d $DB_NAME -c "insert into $TABLE_NAME (url) values ('$URL');"
sudo -u postgres psql -d $DB_NAME -c "insert into $TABLE_NAME (url) values ('$URL');"
输出:
DROP DATABASE
CREATE DATABASE
NOTICE: table "websitemd5" does not exist, skipping
DROP TABLE
CREATE TABLE
CREATE INDEX
INSERT 0 1
ERROR: duplicate key value violates unique constraint "unique_url_index"
DETAIL: Key (md5(url))=(71bf6c554ab335360cd657d060f84c2d) already exists.
推荐阅读
- java - 如何按降序打印多行字符串?
- ios - iOS swift 4.0 从 BLE 热敏打印机打印
- javascript - 带有拇指的轮播问题(react-id-swiper)
- javascript - 使用 CLI package.json 脚本时生产部署失败
- azure - 在 Azure 上规划 Kubernetes 集群
- java - 满足一定条件如何遍历节点列表并替换内容?
- android - 具有多个 Activity 的 Android 导航抽屉
- swift - 与视觉暂留相匹配的颜色选择 两种颜色混合
- ruby - Capybara / Ruby - 尝试仅从所有模棱两可的 css 选择器中获取文本并将其转换为字符串
- python - 从 Google Cloud Function 访问 SIFT