115 lines
4.0 KiB
SQL
115 lines
4.0 KiB
SQL
-- 验证IMMUTABLE错误修复
|
||
-- 此脚本检查主数据库结构是否能成功创建，不包含任何可能导致IMMUTABLE错误的索引
|
||
|
||
SELECT 'Testing database structure without IMMUTABLE function issues...' as test_status;
|
||
|
||
-- ===================================================================
|
||
-- 1. 检查表是否存在
|
||
-- ===================================================================
|
||
|
||
SELECT 'Checking tables...' as step;
|
||
|
||
SELECT
|
||
COUNT(*) as table_count,
|
||
CASE
|
||
WHEN COUNT(*) >= 20 THEN '✓ Tables created successfully'
|
||
ELSE '⚠ Some tables may be missing'
|
||
END as status
|
||
FROM information_schema.tables
|
||
WHERE table_schema = 'public' AND table_name LIKE 'ak_%';
|
||
|
||
-- ===================================================================
|
||
-- 2. 检查索引是否创建成功
|
||
-- ===================================================================
|
||
|
||
SELECT 'Checking indexes...' as step;
|
||
|
||
SELECT
|
||
COUNT(*) as index_count,
|
||
CASE
|
||
WHEN COUNT(*) >= 15 THEN '✓ Indexes created successfully'
|
||
ELSE '⚠ Some indexes may be missing'
|
||
END as status
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public' AND indexname LIKE 'idx_%';
|
||
|
||
-- ===================================================================
|
||
-- 3. 检查是否有全文搜索索引（应该没有）
|
||
-- ===================================================================
|
||
|
||
SELECT 'Checking full-text search indexes (should be empty)...' as step;
|
||
|
||
SELECT
|
||
COUNT(*) as fts_index_count,
|
||
CASE
|
||
WHEN COUNT(*) = 0 THEN '✓ No problematic FTS indexes found'
|
||
ELSE '⚠ Found FTS indexes that might cause issues'
|
||
END as status
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public'
|
||
AND (indexdef LIKE '%to_tsvector%' OR indexdef LIKE '%gin%to_%');
|
||
|
||
-- ===================================================================
|
||
-- 4. 检查基本索引是否存在
|
||
-- ===================================================================
|
||
|
||
SELECT 'Checking basic indexes...' as step;
|
||
|
||
SELECT
|
||
indexname,
|
||
'✓ Available' as status
|
||
FROM pg_indexes
|
||
WHERE schemaname = 'public'
|
||
AND indexname IN (
|
||
'idx_contents_title_text',
|
||
'idx_contents_category',
|
||
'idx_contents_published',
|
||
'idx_contents_status'
|
||
);
|
||
|
||
-- ===================================================================
|
||
-- 5. 测试基本查询功能
|
||
-- ===================================================================
|
||
|
||
SELECT 'Testing basic query functionality...' as step;
|
||
|
||
-- 测试简单文本搜索（不使用全文搜索）
|
||
DO $$
|
||
BEGIN
|
||
-- 检查ak_contents表是否可以查询
|
||
IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'ak_contents') THEN
|
||
RAISE NOTICE '✓ ak_contents table is queryable';
|
||
|
||
-- 测试基本的文本搜索功能
|
||
PERFORM count(*) FROM ak_contents WHERE title ILIKE '%test%';
|
||
RAISE NOTICE '✓ Basic text search with ILIKE works';
|
||
|
||
ELSE
|
||
RAISE NOTICE '⚠ ak_contents table not found';
|
||
END IF;
|
||
|
||
EXCEPTION
|
||
WHEN OTHERS THEN
|
||
RAISE NOTICE '⚠ Error testing queries: %', SQLERRM;
|
||
END
|
||
$$;
|
||
|
||
-- ===================================================================
|
||
-- 6. 显示可用的搜索方法
|
||
-- ===================================================================
|
||
|
||
SELECT 'Available search methods:' as info;
|
||
|
||
SELECT '1. Case-insensitive text search:' as method,
|
||
'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'';' as example
|
||
UNION ALL
|
||
SELECT '2. Multiple field search:' as method,
|
||
'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'' OR content ILIKE ''%keyword%'';' as example
|
||
UNION ALL
|
||
SELECT '3. Combined with other filters:' as method,
|
||
'SELECT * FROM ak_contents WHERE title ILIKE ''%keyword%'' AND status = ''published'' ORDER BY published_at DESC;' as example;
|
||
|
||
SELECT '✅ Database structure verification completed!' as final_status;
|
||
SELECT 'The database should now be free of IMMUTABLE function errors.' as note;
|
||
SELECT 'For advanced full-text search, run fulltext_search_optional.sql separately.' as recommendation;
|