我有一个名为与调用存储过程按月基本上不表分区的触发器证据表。不过,我得到一个模糊的错误,当我开始负载下插入大量的行:
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的参数为空
您有两个执行命令:
_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 是在这里:
SELECT raised_local_time FROM通知WHERE ID = _notification_id INTO _raised_local_time;
所以原因必须两个原因之一:
NEW.notification_id 是 NULL 。
有在的通知没有行为给定的 NEW .notification_id 。
试试这个修正触发功能进行调试 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里比较昂贵
-
-
添加 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:line523My 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 is table_name. The only chance for table_name to become NULL 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:
NEW.notification_id is NULL.
There is no row in notifications for the given NEW.notification_id.
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 to to_char().
No point in using varchar(n). Simply use text or varchar.
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的查询字符串参数为空
发布评论