PostgreSQL 错误:EXECUTE 的查询字符串参数为空
本文关键字:字符串 参数 查询 错误 EXECUTE PostgreSQL | 更新日期: 2023-09-27 18:09:15
我有一个名为 evidence 的表,它带有一个触发器,它调用一个存储过程,该过程基本上按月进行表分区。但是,当我开始在负载下插入大量行时,我遇到了一个模糊的错误:
Npgsql.NpgsqlException: query string argument of EXECUTE is null
Severity: ERROR Code: 22004 at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in c:'C#Apps'github.npgsql.Npgsql.stock'src'Npgsql'NpgsqlState.cs:line890 at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in c:'C#Apps'github.npgsql.Npgsql.stock'src'Npgsql'NpgsqlDataReader.cs:line 1175 at
Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in c:'C#Apps'github.npgsql.Npgsql.stock'src'Npgsql'NpgsqlDataReader.cs:line 1191 at
Npgsql.ForwardsOnlyDataReader.NextResult() in c:'C#Apps'github.npgsql.Npgsql.stock'src'Npgsql'NpgsqlDataReader.cs:line 1377 at
Npgsql.NpgsqlCommand.ExecuteNonQuery() in c:'C#Apps'github.npgsql.Npgsql.stock'src'Npgsql'NpgsqlCommand.cs:line523
我的系统具有自动重试功能,最终每条记录都会插入到数据库中,但是在负载很高时会出现许多异常。
数据库是 CentOS 6 服务器上的 PostgreSQL 9.3,客户端是使用 Npgsql 驱动程序的 C# .NET。
桌子:
CREATE TABLE evidence
(
id uuid NOT NULL,
notification_id uuid NOT NULL,
feedback character varying(200),
result character varying(20),
trigger_action_type character varying(200),
trigger_action_id uuid,
data_type integer NOT NULL,
data bytea,
name character varying(30),
CONSTRAINT pk_evidence PRIMARY KEY (id)
);
触发:
CREATE TRIGGER evidence_move_to_partition_tables
BEFORE INSERT
ON evidence
FOR EACH ROW
EXECUTE PROCEDURE partition_evidence_by_month();
触发功能:
CREATE OR REPLACE FUNCTION partition_evidence_by_month()
RETURNS trigger AS
$BODY$
DECLARE
_notification_id uuid;
_raised_local_time timestamp without time zone;
_table_name character varying(35);
_start_date timestamp without time zone;
_end_date timestamp without time zone;
_table_space character varying(50) := 'ls_tablespace2';
_query text;
BEGIN
_notification_id := NEW.notification_id;
SELECT raised_local_time FROM notifications WHERE id=_notification_id INTO _raised_local_time;
_start_date := date_trunc('month', _raised_local_time);
_end_date := _start_date + '1 month'::interval;
_table_name := 'evidence-' || to_char(_start_date, 'YYYY-MM');
-- check to see if table already exists
PERFORM 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND c.relname = _table_name
AND n.nspname = 'public';
-- if the table doesn't exist, then create it now
IF NOT FOUND THEN
-- create partition table
_query := 'CREATE TABLE public.' || quote_ident(_table_name) || ' ( ) INHERITS (public.evidence)';
EXECUTE _query;
-- alter owner
--EXECUTE 'ALTER TABLE public.' || quote_ident(_table_name) || ' OWNER TO postgres';
-- add index
--EXECUTE 'ALTER TABLE public.' || quote_ident(_table_name) || ' ADD PRIMARY KEY (id)';
END IF;
-- move the data to the partition table
EXECUTE 'INSERT INTO public.' || quote_ident(_table_name) || ' VALUES ($1.*)' USING NEW;
RETURN NULL;
END;
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
区号:
using (var cmd = db.CreateCommand())
{
cmd.CommandText = @"INSERT INTO evidence
(id, notification_id, feedback, result, trigger_action_type,
trigger_action_id, data_type, data, name)
VALUES (@id,@nid,@feedback,@result,@tat,@taid,@dt,@data,@name)";
cmd.Parameters.AddWithValue("@id", evItem.ID);
cmd.Parameters.AddWithValue("@nid", evItem.NotificationID);
cmd.Parameters.AddWithValue("@feedback", evItem.Feedback);
cmd.Parameters.AddWithValue("@result", evItem.Result);
cmd.Parameters.AddWithValue("@tat", evItem.TriggerActionType);
cmd.Parameters.AddWithValue("@taid", evItem.TriggerActionID);
cmd.Parameters.AddWithValue("@dt", (int)evItem.DataType);
cmd.Parameters.AddWithValue("@data", evItem.Data);
cmd.Parameters.AddWithValue("@name", evItem.Name);
cmd.ExecuteNonQuery();
}
为什么只有在系统负载不足时才会出现这种奇怪的错误?我该怎么做才能防止它发生?
谢谢!
错误消息是
EXECUTE 的查询字符串参数为空
您有两个EXECUTE
命令:
_query := 'CREATE TABLE public.'
|| quote_ident(_table_name) || ' ( ) INHERITS (public.evidence)';
EXECUTE _query;
...
EXECUTE 'INSERT INTO public.'
|| quote_ident(_table_name) || ' VALUES ($1.*)' USING NEW;
唯一可以NULL
的部分是 table_name
.table_name
成为NULL
的唯一机会就在这里:
SELECT raised_local_time FROM notifications WHERE id=_notification_id
INTO _raised_local_time;
所以原因一定是以下两个原因之一:
NEW.notification_id
是NULL
.给定
NEW.notification_id
notifications
中没有行。
CREATE OR REPLACE FUNCTION partition_evidence_by_month()
RETURNS trigger AS
$func$
DECLARE
_table_name text;
BEGIN
SELECT 'evidence-' || to_char(raised_local_time, 'YYYY-MM')
FROM public.notifications -- schema-qualify to be sure
WHERE id = NEW.notification_id
INTO _table_name;
IF _table_name IS NULL THEN
RAISE EXCEPTION '_table_name is NULL. Should not occur!';
END IF;
IF NOT EXISTS ( -- create table if it does not exist
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND c.relname = _table_name
AND n.nspname = 'public') THEN
EXECUTE 'CREATE TABLE public.'
|| quote_ident(_table_name) || ' ( ) INHERITS (public.evidence)';
END IF;
EXECUTE 'INSERT INTO public.'
|| quote_ident(_table_name) || ' VALUES $1' -- Use NEW row directly
USING NEW; -- write data to the partition table
RETURN NULL;
END
$func$ LANGUAGE plpgsql;
删除未使用的变量并简化代码。(这显然是一个简化的示例。
除此之外,您根本不需要
date_trunc()
。只需将原始时间戳提供给to_char()
.使用
varchar(n)
没有意义.只需使用text
或varchar
.避免不必要的太多赋值 - 在PL/pgSQL中相对昂贵。
添加一个
RAISE
来检查我的假设。
如果您收到错误消息,则下一步将区分两种可能的原因。应该是微不足道的...