Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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!

share|improve this question
    
I have even set a breakpoint and debugged it and watched the _query variable being set to a VALID query before failing on EXECUTE _query but if I manually run the value of _query afterwards it works?! –  tommed Jun 19 at 14:30
1  
Interesting. Concurrency/threading issue, possibly within nPgSQL, possibly the app? –  Craig Ringer Jun 19 at 14:35
1  
That's what I thought, though the errors appears in PostgreSQL's log file too and I put a lock around the insert code and it still threw the same error :( –  tommed Jun 19 at 14:37
1  
Did you tried to debug it with RAISE LOG? It could be easier to match them with the errors in the PostgreSQL log. Also: have you set a foreign key on evidence.notification_id to notifications.id? –  pozs Jun 19 at 15:35
    
There's no foreign key, this is completely de-normalised. Don't need RAISE LOG as I have stepped through it with a debugger - there is nothing wrong with the query, it can be run afterwards. Interestingly, I have also been able to reproduce this by manually entering data using pgAdmin too! –  tommed Jun 19 at 16:13
add comment

1 Answer

up vote 1 down vote accepted

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

share|improve this answer
1  
You were correct, the notification was coming in after the evidence so it was null. When I debugged it, obviously it gave the notification time to get created which is why it worked every time. I added raised_local_time to the arguments, thus removing the dependency on notifications which meant the ordering wasn't important. Thanks for the insight! –  tommed Jun 22 at 15:41
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.