错误:未为索引document_bt_id_sys_period_app_period_excl定义函数ammarkpos
错误:未为索引document_bt_id_sys_period_app_period_excl定义函数ammarkpos
提问于 2020-11-23 14:18:09
我询问DB。一切都好，但是当我添加FULL JOIN时，我得到了错误调试输出在这里: pastebin
SELECT
*
FROM ( SELECT * FROM "document" WHERE TRUE OR agreement_id = 1109 ) d
-- This line cause problem:
FULL JOIN (select * from ( VALUES ( 'StopAgreement' ) ) AS t( doctype) ) stop ON true
WHERE d.id IS NULL OR d.document_type IN ( 'NewAgreement', 'ChangeAgreement', 'StopAgreement' )
;
ERROR: function ammarkpos is not defined for index document_bt_id_sys_period_app_period_excl上述索引的定义是：
"document_bt_id_sys_period_app_period_excl" EXCLUDE USING gist (id WITH =, sys_period WITH &&, app_period WITH &&)我的FULL JOIN怎么了？如何解决错误？
select * from version();
PostgreSQL 13.0 (Debian 13.0-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian
8.3.0-6) 8.3.0, 64-bit
(1 row)更新
这个问题也在PostgreSQL v11.5上重现。
tucha=> \dS+ document;
Column | Type | Collation | Nullable | Default | Storage | Description
---------------+--------------------------+-----------+----------+--------------+----------+-------------
id | integer | | | | plain |
owner_id | integer | | | | plain |
document_type | character varying(32) | | | | extended |
docn | character varying(32) | | | | extended |
docdate | date | | | | plain |
agreement_id | integer | | | | plain |
info | json | | | '{}'::json | extended |
prow_date | timestamp with time zone | | | | plain |
app_period | tstzrange | | | app_period() | extended |
View definition:
SELECT document_bt.id,
document_bt.owner_id,
document_bt.document_type,
document_bt.docn,
document_bt.docdate,
document_bt.agreement_id,
document_bt.info,
document_bt.prow_date,
document_bt.app_period
FROM document_bt
WHERE document_bt.sys_period @> sys_time() AND (biconf('app_period'::text) IS NULL OR document_bt.app_period && biconf('app_period'::text)::tstzrange);
Triggers:
document_bt_delete INSTEAD OF DELETE ON document FOR EACH ROW EXECUTE PROCEDURE document_bt_delete()
document_bt_insert INSTEAD OF INSERT ON document FOR EACH ROW EXECUTE PROCEDURE document_bt_insert()
document_bt_update INSTEAD OF UPDATE ON document FOR EACH ROW EXECUTE PROCEDURE document_bt_update()
tucha=> \d document_bt
Table "public.document_bt"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+--------------------------+-----------+----------+-------------------------------------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('document_id_seq'::regclass) | plain | |
owner_id | integer | | | | plain | |
document_type | character varying(32) | | not null | | extended | |
docn | character varying(32) | | not null | | extended | |
docdate | date | | not null | now() | plain | |
agreement_id | integer | | | | plain | |
info | json | | not null | '{}'::json | extended | |
sid | integer | | not null | nextval('document_bt_sid_seq'::regclass) | plain | |
app_period | tstzrange | | not null | app_period() | extended | |
sys_period | tstzrange | | not null | tstzrange(sys_time(), 'infinity'::timestamp with time zone, '[)'::text) | extended | |
prow_date | timestamp with time zone | | | | plain | |
Indexes:
"document_bt_id_sys_period_app_period_excl" EXCLUDE USING gist (id WITH =, sys_period WITH &&, app_period WITH &&)
Check constraints:
"document_bt_sys_period_check" CHECK (NOT isempty(sys_period))当我清空桌子的时候。查询有效。
当我在表中添加一行时，查询就会工作。
转储/还原数据库时:查询不工作
更新
CTE计划，其工作如下：
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Merge Full Join (cost=3177.93..3177.98 rows=1 width=284) (actual time=4.994..16.219 rows=53 loops=1) |
CTE x |
-> Index Scan using document_bt_id_sys_period_app_period_excl on document_bt (cost=0.53..3177.93 rows=1 width=75) (actual time=4.969..16.071 rows=53 loops=1) |
Index Cond: (sys_period @> sys_time()) |
Filter: (((id IS NULL) OR ((document_type)::text = ANY ('{NewAgreement,ChangeAgreement,StopAgreement}'::text[]))) AND ((NULLIF(current_setting('my.app_period'::text, true), ''::text) IS NULL) OR (app_period && (NULLIF(current_setting('my.app_per|
Rows Removed by Filter: 9251 |
-> CTE Scan on x (cost=0.00..0.02 rows=1 width=252) (actual time=4.977..16.149 rows=53 loops=1) |
-> Materialize (cost=0.00..0.02 rows=1 width=32) (actual time=0.009..0.016 rows=1 loops=1) |
-> Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1) |
Planning Time: 0.536 ms |
Execution Time: 16.303 ms |查询不起作用时的计划：
EXPLAIN ANALYSE SELECT
*
FROM ( SELECT * FROM "document" ) d
FULL JOIN (select * from ( VALUES ( 'StopAgreement' ) ) AS t( doctype) ) stop ON true
WHERE d.id IS NULL OR d.document_type IN ( 'NewAgreement', 'ChangeAgreement', 'StopAgreement' );
ERROR: function ammarkpos is not defined for index document_bt_id_sys_period_app_period_excl更新
不经分析
EXPLAIN SELECT
*
FROM ( SELECT * FROM "document" ) d
FULL JOIN (select * from ( VALUES ( 'StopAgreement' ) ) AS t( doctype) ) stop ON true
WHERE d.id IS NULL OR d.document_type IN ( 'NewAgreement', 'ChangeAgreement', 'StopAgreement' );
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Merge Full Join (cost=0.53..3144.17 rows=1 width=107)
Filter: ((document_bt.id IS NULL) OR ((document_bt.document_type)::text = ANY ('{NewAgreement,ChangeAgreement,StopAgreement}'::text[])))
-> Result (cost=0.00..0.01 rows=1 width=32)
-> Index Scan using document_bt_id_sys_period_app_period_excl on document_bt (cost=0.53..3142.18 rows=143 width=75)
Index Cond: (sys_period @> sys_time())
Filter: ((NULLIF(current_setting('my.app_period'::text, true), ''::text) IS NULL) OR (app_period && (NULLIF(current_setting('my.app_period'::text, true), ''::text))::tstzrange))
(6 rows)
EXPLAIN WITH x AS (SELECT
*
FROM ( SELECT * FROM "document" WHERE TRUE OR agreement_id = 1109 ) d
WHERE d.id IS NULL OR d.document_type IN ( 'NewAgreement', 'ChangeAgreement', 'StopAgreement' )
)
SELECT * FROM x
FULL JOIN (select * from ( VALUES ( 'StopAgreement' ) ) AS t( doctype) ) stop ON true
;
QUERY PLAN |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
Merge Full Join (cost=3177.93..3177.98 rows=1 width=284) |
CTE x |
-> Index Scan using document_bt_id_sys_period_app_period_excl on document_bt (cost=0.53..3177.93 rows=1 width=75) |
Index Cond: (sys_period @> sys_time()) |
Filter: (((id IS NULL) OR ((document_type)::text = ANY ('{NewAgreement,ChangeAgreement,StopAgreement}'::text[]))) AND ((NULLIF(current_setting('my.app_period'::text, true), ''::text) IS NULL) OR (app_period && (NULLIF(current_setting('my.app_per|
-> CTE Scan on x (cost=0.00..0.02 rows=1 width=252) |
-> Materialize (cost=0.00..0.02 rows=1 width=32) |
-> Result (cost=0.00..0.01 rows=1 width=32) |回答 1
Stack Overflow用户
发布于 2020-11-23 17:26:01
要解决错误，我可以重写一些查询：
WITH x AS (SELECT
*
FROM ( SELECT * FROM "document" WHERE TRUE OR agreement_id = 1109 ) d
WHERE d.id IS NULL OR d.document_type IN ( 'NewAgreement', 'ChangeAgreement', 'StopAgreement' )
)
SELECT * FROM x
-- This FULL JOIN do noting =(
FULL JOIN (select * from ( VALUES ( 'StopAgreement' ) ) AS t( doctype) ) stop ON true更新
这是个边缘案件。见黑客邮件列表。在下一个版本=中会有修正
感谢来自irc频道的RhodiumToad对此进行调查。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/64970209
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号