PostGIS批量为空间表添加GIST索引的DO块执行报错求助

我来帮你排查这个问题哈，你的DO块执行报错主要是几个语法和逻辑上的小问题导致的，咱们一步步来解决：

先分析报错原因 #

你遇到的ERROR: current transaction is aborted, commands ignored until end of transaction block错误，本质是DO块里的事务逻辑混乱，加上查询语句的语法错误，导致触发异常后整个事务被中止，后续命令无法执行。具体问题点有这几个：

1. 查询空间表的语法完全错误：你写的SELECT f_table_name FROM information_schema.tables WHERE geometry_columns是完全不对的，应该直接从PostGIS自带的geometry_columns视图获取空间表信息，这个视图专门存储所有带空间字段的表的元数据。
2. 多余的事务开启语句：DO块本身就运行在一个事务上下文里，你里面加的BEGIN;会尝试开启嵌套事务，但PostgreSQL默认不支持显式嵌套事务，这会直接导致事务出错中止。而且你的EXCEPTION对应的应该是块级的BEGIN，不是事务的BEGIN;。
3. 未过滤已存在的GIST索引：原逻辑会强制给所有符合条件的表创建索引，不管该表是否已经有GIST索引，重复创建索引的错误会直接触发事务中止。

修正后的DO块代码 #

下面是调整后的完整代码，解决了上述所有问题：

DO $$
DECLARE
    table_name text;
BEGIN
    -- 从geometry_columns获取空间表，排除视图、带st_union的字段，同时过滤已有GIST索引的表
    FOR table_name IN (
        SELECT DISTINCT gc.f_table_name
        FROM geometry_columns gc
        LEFT JOIN pg_indexes pi 
            ON pi.tablename = gc.f_table_name 
            AND pi.indexname LIKE 'gist_%'
        WHERE 
            gc.f_table_name NOT ILIKE '%view%'
            AND gc.f_geometry_column NOT ILIKE '%st_union%'
            AND pi.indexname IS NULL -- 只处理还没有GIST索引的表
    ) LOOP
        BEGIN
            -- 执行创建索引的语句，索引名命名更清晰
            EXECUTE format(
                'CREATE INDEX "gist_%I_geom" ON %I USING GIST (geom);', 
                table_name, 
                table_name
            );
            RAISE NOTICE '成功为表 % 创建GIST索引', table_name;
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE '为表 % 创建索引时出错: %', table_name, SQLERRM;
        END;
    END LOOP;
END $$;

代码修改说明 #

• 修正查询逻辑：直接从geometry_columns获取空间表，关联pg_indexes视图过滤掉已有GIST索引的表（通过判断pi.indexname IS NULL），避免重复创建。
• 移除错误的事务语句：用块级的BEGIN...END包裹每个表的索引创建逻辑，这样单个表的创建失败只会触发当前块的异常，不会导致整个DO块的事务中止，其他表的处理还能继续。
• 优化索引命名：把索引名改成gist_表名_geom，更清晰地标识这是哪个表的geom字段的GIST索引。
• 增加成功提示：添加了RAISE NOTICE提示成功创建的信息，方便你追踪处理进度。

备注：内容来源于stack exchange，提问作者user21251905