115 lines
3.8 KiB
SQL
115 lines
3.8 KiB
SQL
-- 测试IMMUTABLE函数修复
|
|
-- 运行此脚本检查函数是否正确创建为IMMUTABLE
|
|
|
|
-- ===================================================================
|
|
-- 1. 检查函数是否存在并且是IMMUTABLE
|
|
-- ===================================================================
|
|
|
|
SELECT 'Checking IMMUTABLE functions...' as status;
|
|
|
|
-- 检查函数存在性和IMMUTABLE属性
|
|
SELECT
|
|
proname as function_name,
|
|
provolatile as volatility_code,
|
|
CASE provolatile
|
|
WHEN 'i' THEN 'IMMUTABLE ✓'
|
|
WHEN 's' THEN 'STABLE ❌'
|
|
WHEN 'v' THEN 'VOLATILE ❌'
|
|
END as volatility_status,
|
|
prosrc as function_body
|
|
FROM pg_proc
|
|
WHERE proname IN ('content_title_to_tsvector', 'content_body_to_tsvector')
|
|
AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');
|
|
|
|
-- ===================================================================
|
|
-- 2. 测试函数功能
|
|
-- ===================================================================
|
|
|
|
SELECT 'Testing function functionality...' as status;
|
|
|
|
-- 测试函数是否能正常工作
|
|
SELECT
|
|
'Title function test' as test_name,
|
|
content_title_to_tsvector('AI Technology News')::text as result;
|
|
|
|
SELECT
|
|
'Content function test' as test_name,
|
|
content_body_to_tsvector('Artificial Intelligence is transforming the world')::text as result;
|
|
|
|
-- ===================================================================
|
|
-- 3. 检查索引状态
|
|
-- ===================================================================
|
|
|
|
SELECT 'Checking index status...' as status;
|
|
|
|
-- 检查索引是否存在
|
|
SELECT
|
|
schemaname,
|
|
tablename,
|
|
indexname,
|
|
indexdef
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public'
|
|
AND indexname IN ('idx_contents_fts_title', 'idx_contents_fts_content');
|
|
|
|
-- ===================================================================
|
|
-- 4. 手动创建索引 (如果自动创建失败)
|
|
-- ===================================================================
|
|
|
|
SELECT 'Manual index creation (if needed)...' as status;
|
|
|
|
-- 如果索引不存在，手动创建
|
|
DO $$
|
|
BEGIN
|
|
-- 检查并创建标题索引
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_class c
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE c.relname = 'idx_contents_fts_title' AND n.nspname = 'public'
|
|
) THEN
|
|
RAISE NOTICE '创建标题全文搜索索引...';
|
|
EXECUTE 'CREATE INDEX idx_contents_fts_title ON public.ak_contents USING gin(content_title_to_tsvector(title))';
|
|
RAISE NOTICE '标题索引创建成功';
|
|
ELSE
|
|
RAISE NOTICE '标题索引已存在';
|
|
END IF;
|
|
|
|
-- 检查并创建内容索引
|
|
IF NOT EXISTS (
|
|
SELECT 1 FROM pg_class c
|
|
JOIN pg_namespace n ON n.oid = c.relnamespace
|
|
WHERE c.relname = 'idx_contents_fts_content' AND n.nspname = 'public'
|
|
) THEN
|
|
RAISE NOTICE '创建内容全文搜索索引...';
|
|
EXECUTE 'CREATE INDEX idx_contents_fts_content ON public.ak_contents USING gin(content_body_to_tsvector(content))';
|
|
RAISE NOTICE '内容索引创建成功';
|
|
ELSE
|
|
RAISE NOTICE '内容索引已存在';
|
|
END IF;
|
|
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
RAISE NOTICE '索引创建时发生错误: %', SQLERRM;
|
|
RAISE NOTICE '错误代码: %', SQLSTATE;
|
|
END
|
|
$$;
|
|
|
|
-- ===================================================================
|
|
-- 5. 最终验证
|
|
-- ===================================================================
|
|
|
|
SELECT 'Final verification...' as status;
|
|
|
|
-- 再次检查索引
|
|
SELECT
|
|
COUNT(*) as index_count,
|
|
CASE
|
|
WHEN COUNT(*) = 2 THEN '✓ 所有索引创建成功'
|
|
ELSE '❌ 索引创建不完整'
|
|
END as status
|
|
FROM pg_indexes
|
|
WHERE schemaname = 'public'
|
|
AND indexname IN ('idx_contents_fts_title', 'idx_contents_fts_content');
|
|
|
|
SELECT '✅ IMMUTABLE函数测试完成' as final_status;
|