PostgreSQL的错误:EXECUTE的查询字符串参数为空 [英] PostgreSQL error: query string argument of EXECUTE is null
问题描述
我有一个名为与调用存储过程按月基本上不表分区的触发器证据表。不过,我得到一个模糊的错误,当我开始负载下插入大量的行:
Npgsql.NpgsqlException:EXECUTE的查询字符串参数为空
严重错误代码:22004在Npgsql.NpgsqlState< ProcessBackendResponses_Ver_3>在C d__a.MoveNext():\C#Apps\github.npgsql.Npgsql.stock\src\Npgsql\ NpgsqlState.cs:line890在Npgsql.ForwardsOnlyDataReader.GetNextResponseObject(c)中:\C#Apps\github.npgsql.Npgsql.stock\src\Npgsql\NpgsqlDataReader.cs:1175线在
Npgsql .ForwardsOnlyDataReader.GetNextRowDescription(c)中:\C#Apps\github.npgsql.Npgsql.stock\src\Npgsql\NpgsqlDataReader.cs:在
Npgsql.ForwardsOnlyDataReader.NextResult线1191()中C:\C#Apps\github.npgsql.Npgsql.stock\src\Npgsql\NpgsqlDataReader.cs:\C#应用程序:在
Npgsql.NpgsqlCommand.ExecuteNonQuery(c)中线路1377 \github.npgsql.Npgsql.stock\src\Npgsql\NpgsqlCommand.cs:line523
我的系统具有自动重试功能,最终每一条记录被插入到数据库中,但许多许多异常之后,当负载较高。
数据库是PostgreSQL的9.3 CentOS 6的服务器上,客户端使用Npgsql司机C#.NET。
表格:
CREATE TABLE证据
(
ID UUID NOT NULL,
notification_id UUID NOT NULL,
反馈字符改变(200),
结果字符改变(20),
trigger_action_type字符改变(200),
trigger_action_id UUID,
DATA_TYPE整数NOT NULL,
数据BYTEA,
名称字符改变(30),
约束pk_evidence PRIMARY KEY(ID)
);
触发:
CREATE TRIGGER evidence_move_to_partition_tables
BEFORE INSERT
证据
FOR EACH ROW
EXECUTE PROCEDURE partition_evidence_by_month();
触发功能:
CREATE OR REPLACE FUNCTION partition_evidence_by_month()
返回一个触发器AS
$ BODY $
DECLARE
_notification_id UUID;
_raised_local_time时间戳没有时区;
_table_name字符改变(35);
_start_date时间戳没有时区;
_end_date时间戳没有时区;
_table_space字符改变(50):='ls_tablespace2';
_query文本;
BEGIN
_notification_id:= NEW.notification_id;
选择raised_local_time FROM通知WHERE ID = _notification_id INTO _raised_local_time;
_start_date:= date_trunc('月',_raised_local_time);
_end_date:= _start_date +'1个月::区间;
_table_name:='循证'|| TO_CHAR(_start_date,'YYYY-MM');
- 检查,看看表已经存在
PERFORM 1
FROM pg_catalog.pg_classç
JOIN pg_catalog.pg_namespace N于n.oid = c.relnamespace
,其中c.relkind ='R'
和c.relname = _table_name
和n.nspname ='公共';
- 如果表不存在,那么创建它现在
如果找不到,则
- 创建分区表
_query:='CREATE TABLE公开'||。 quote_ident(_table_name)|| '()INHERITS(public.evidence)';
EXECUTE _query;
- 改变所有者
--executeALTER TABLE公开。'|| quote_ident(_table_name)|| 主人Postgres的;
- 添加索引
--executeALTER TABLE公开。'|| quote_ident(_table_name)|| ADD PRIMARY KEY(ID);
END IF;
- 将数据移动到分区表
执行'INSERT INTO公开。'|| quote_ident(_table_name)|| VALUES($ 1 *)'运用新;
返回NULL;
端;
$ BODY $ LANGUAGE PLPGSQL挥发性成本100;
长途区号:
使用(VAR CMD = db.CreateCommand())
{
cmd.CommandText = @INSERT INTO证据
(ID,notification_id ,反馈,因此,trigger_action_type,
trigger_action_id,DATA_TYPE,数据,名称)
值(@ ID,@ NID,@反馈@结果,@达,@ TAID,@ DT,@数据,@名称);
cmd.Parameters.AddWithValue(@ ID,evItem.ID);
cmd.Parameters.AddWithValue(@ NID,evItem.NotificationID);
cmd.Parameters.AddWithValue(@反馈,evItem.Feedback);
cmd.Parameters.AddWithValue(@结果,evItem.Result);
cmd.Parameters.AddWithValue(@达,evItem.TriggerActionType);
cmd.Parameters.AddWithValue(@ TAID,evItem.TriggerActionID);
cmd.Parameters.AddWithValue(@ DT,(int)的evItem.DataType);
cmd.Parameters.AddWithValue(@数据,evItem.Data);
cmd.Parameters.AddWithValue(@名,evItem.Name);
cmd.ExecuteNonQuery();
}
为什么只有当系统负载下出现这一离奇的错误?我能做些什么来防止它的发生?
谢谢!
该错误消息
查询字符串EXECUTE的参数为空
块引用>
您有两个
执行
命令:
<预类= 郎咸平-SQL prettyprint-覆盖>_query:='CREATE TABLE公众
|| quote_ident(_table_name)|| '()INHERITS(public.evidence)';
EXECUTE _query;
...
执行'INSERT INTO公开。
|| quote_ident(_table_name)|| VALUES($ 1 *)'运用新;
这可能是唯一的一部分
NULL
是table_name的
。结果
的唯一机会,table_name的
成为NULL
是在这里:
<预类=郎-SQL prettyprint-覆盖>SELECT raised_local_time FROM通知WHERE ID = _notification_id
INTO _raised_local_time;
所以原因必须两个原因之一:
NEW.notification_id
是NULL
。
有在
的通知没有行
为给定的NEW .notification_id
。
试试这个修正触发功能进行调试
<预类=郎-SQL prettyprint-覆盖>CREATE OR REPLACE FUNCTION partition_evidence_by_month()
,返回一个触发器AS
$ FUNC $
DECLARE
_table_name文本;
BEGIN
选择'循证'|| TO_CHAR(raised_local_time,'YYYY-MM')
从public.notifications - 架构的资格,以确保
WHERE ID = NEW.notification_id $ B B $ INTO _table_name;
如果_table_name为null,则
引发异常'_table_name为NULL。应该不会出现'!;
END IF;
如果NOT EXISTS( - 创建表,如果不存在
它选择1
FROM pg_catalog.pg_classç
JOIN pg_catalog.pg_namespace N于n.oid = c.relnamespace
,其中c.relkind ='R'
和c.relname = _table_name
和n.nspname ='公共')THEN
执行'CREATE表公开。
|| quote_ident(_table_name)|| '()INHERITS(public.evidence)';
END IF;
执行'INSERT INTO公开。
|| quote_ident(_table_name)|| 价值$ 1' - 利用新行直接
使用新的; - 将数据写入分区表
返回NULL;
端
$ FUNC $ LANGUAGE PLPGSQL;
删除未使用的变量和简化代码。 (这显然是一个简单的例子。)
除此之外,你不需要
date_trunc ()
可言。只需将原有的时间戳喂TO_CHAR()
。
在不使用点
VARCHAR(n)的
。只需使用文本
或VARCHAR
。
避免过多的任务,其中不必要的 - 在PL / pgSQL里比较昂贵
添加<$ C $。 C> RAISE 来检查我的假设。结果
如果你得到错误信息,这两个可能的原因区别对待将是下一个步骤。应该是微不足道的...
I have a table called evidence with a trigger which calls a stored procedure which basically does table partitioning by month. However I get an obscure error when I start inserting lots of rows under load:
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
My system has automatic retry functionality and eventually every record gets inserted into the database, but after many many exceptions when the load is high.
Database is PostgreSQL 9.3 on a CentOS 6 server and client is C# .NET using Npgsql driver.
Table:
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) );
Trigger:
CREATE TRIGGER evidence_move_to_partition_tables BEFORE INSERT ON evidence FOR EACH ROW EXECUTE PROCEDURE partition_evidence_by_month();
Trigger Function:
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;
Calling Code:
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(); }
Why would this bizarre error appear only when the system is under load? What can I do to prevent it happening?
Thanks!
解决方案The error message is
query string argument of EXECUTE is null
You have two
EXECUTE
commands:_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;
The only part that can be
NULL
istable_name
.
The only chance fortable_name
to becomeNULL
is here:SELECT raised_local_time FROM notifications WHERE id=_notification_id INTO _raised_local_time;
So the cause must be one of two reasons:
Try this modified trigger function for debugging:
NEW.notification_id
isNULL
.There is no row in
notifications
for the givenNEW.notification_id
.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;
Remove unused variables and simplify code. (This is obviously a simplified example.)
Among other things, you don't need
date_trunc()
at all. Simply feed the original timestamp toto_char()
.No point in using
varchar(n)
. Simply usetext
orvarchar
.Avoid too many assignments where unnecessary - comparatively expensive in PL/pgSQL.
Add a
RAISE
to check my hypothesis.
If you get the error message, discriminating between the two possible causes would be the next step. Should be trivial ...这篇关于PostgreSQL的错误:EXECUTE的查询字符串参数为空的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!