PostgreSQL error: query string argument of EXECUTE

问题:

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!

回答1:

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:

1. NEW.notification_id is NULL.

2. There is no row in notifications for the given NEW.notification_id.

Try this modified trigger function for debugging:

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 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 ...