PetaPoco insert fails on tables with multiple triggers in PostgreSQL

Question

PetaPoco/Npgsql fails on insert with table trigger.

I believe this is very similar to PetaPoco insert fails on table with trigger, the difference being that question was for SQL 2008 and mine is for PostgreSQL 9.5.4 using the Npgsql C# driver with a table that has multiple triggers. My situation is that I have tables with multiple triggers defined in PostgreSQL like:

CREATE TABLE procedures
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  cpt_recid integer NOT NULL,
  dx_recid integer,
  send_out boolean,
  modified timestamp without time zone NOT NULL DEFAULT now(),
)
WITH (
  OIDS=FALSE
);
ALTER TABLE procedures
  OWNER TO postgres;


CREATE TRIGGER update_modified
  BEFORE UPDATE
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

CREATE TRIGGER zzz_get_next_order_number_trigger
  BEFORE INSERT
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();


CREATE OR REPLACE FUNCTION getnextorderno()
  RETURNS trigger AS
$BODY$

BEGIN
    NEW.orderno := nextval('order_number_seq');
    Return NEW; 
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getnextorderno()
  OWNER TO postgres;

My primary problem is that the PetaPoco database.Insert() insists on saving 0 and epoch time instead of the next orderno or current time.

Is there a simple change that can be made to PetaPoco to respect either the DEFAULTS on the columns or allow the triggers to function correctly?

(I'm very new to PetaPoco).

TIA

Edit#1 Following the proposed changes, the table now looks like the below. The problem I'm still having is that orderno is still being filled in with 0 and hence violating the unique constraint.

duplicate key value violates unique constraint "procedure_order_unique" Key (orderno)=(0) already exists.

The PetaPoco insert is being called from the WCF service as:

p = new procedure
                    {
                        cpt_recid = cpt.recid,
                        chart_recid = _procedure.chart_recid,
                        dx_recid = _procedure.dx_recid,
                        torder = _procedure.torder,
                        on_return_to_office = _procedure.on_return_to_office,
                        send_out = _procedure.send_out,
                        standing_order = _procedure.standing_order,
                        stat = _procedure.stat,
                        tstop = _procedure.tstop,
                        isprocedure = _is_procedure
                    };
                    db.Insert(p);

CREATE TABLE procedures
(
  recid serial NOT NULL,
  orderno integer NOT NULL DEFAULT nextval('order_number_seq'::regclass),
  torder timestamp without time zone NOT NULL DEFAULT now(),
  cpt_recid integer NOT NULL,
  dx_recid integer,
  send_out boolean,
  modified timestamp without time zone NOT NULL DEFAULT now(),
  stat boolean,
  standing_order boolean,
  tstop timestamp without time zone,
  on_return_to_office boolean,
  chart_recid integer NOT NULL,
  isprocedure boolean NOT NULL DEFAULT false,
  is_deferred boolean,
  CONSTRAINT procedures_pk PRIMARY KEY (recid),
  CONSTRAINT procedures_chart_fk FOREIGN KEY (chart_recid)
      REFERENCES charts (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedures_cpt_fk FOREIGN KEY (cpt_recid)
      REFERENCES cpt (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedures_dx_fk FOREIGN KEY (dx_recid)
      REFERENCES dx (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedure_order_unique UNIQUE (orderno)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE procedures
  OWNER TO postgres;

CREATE TRIGGER insert_modified
  BEFORE INSERT
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_modified
  BEFORE UPDATE
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

Thanks for any help.

Phill · Accepted Answer · 2016-09-07T14:49:52.430

1

You don't need the trigger for the order number, just set the sequence on the column and remove the need for the trigger.

The modified is being saved because your POCO modified property is not null so it's sending a default value. If you want to prevent this make your modified trigger run on both before insert / before update.

BEFORE INSERT OR UPDATE

http://www.toptensoftware.com/petapoco/

The documentation says to decorate the class definition with the primary key:

[PetaPoco.PrimaryKey("orderno")]

To get around decorating the table with attributes and modifying the T4 templates, you will need to name your column id instead.

PetaPoco will ignore this on insert and allow the database to handle it.

Any other column name will be assumed as non-primary key and the default value will be sent.

PostgreSQL and SQL Server will both ignore SERIAL and IDENTITY columns when the value is given on insert like what is happening in your scenario.

edited Sep 07 '16 at 14:49

answered Sep 07 '16 at 06:58

Phill

18,398
7
62
102

Please see edit#1 above. The trigger on Insert for the modified time does work correctly when called from PetaPoco, but orderno is still being inserted as 0, not the correct sequence number. What to do?? Thanks. – Alan Wayne Sep 07 '16 at 14:28
@AlanWayne please supply your model / insert code. Have you decorated your model with primary key attribute? Can you not specify the sequence on a trigger and just do it normally as a column constraint. – Phill Sep 07 '16 at 14:31
The primary key for the table is recid and is being correctly handled. I'm trying to avoid changing/adding any decorations as this is a "database-first" model in which I'm constantly updating the model via the T4 templates. So I'm trying to avoid making permanent changes to the templates. Thanks for your help :) – Alan Wayne Sep 07 '16 at 14:41
To add the decoration, I would have to change the T4 template? – Alan Wayne Sep 07 '16 at 14:42
Yes most likely, this is one of the reasons I don't use PetaPoco, and prefer Dapper. – Phill Sep 07 '16 at 14:43
Even using this as a regular trigger as a column constraint fails with the same issue. – Alan Wayne Sep 07 '16 at 14:44
I did not see where Dapper could be used as a "Database-First" model?? – Alan Wayne Sep 07 '16 at 14:45
Because your class is defined as an `int` for `orderno` so it sends the default value of an `int` which is `0`. By decorating the poco you're telling it that on insert, ignore this property because it's my primary key and will be handled by the database. – Phill Sep 07 '16 at 14:45
I'm also guessing that if I let the Orderno be null, so that the default insert is "null", then PostgreSQL will use the sequence?? (Ofcourse, this could also allow incorrect data to the table). – Alan Wayne Sep 07 '16 at 14:58
I posted a working solution. Your last sentence was the key. Thanks. – Alan Wayne Sep 07 '16 at 16:39

score 0 · Answer 2 · answered Sep 07 '16 at 16:36

Taking Phill's lead (above) and not wanting to change the PostgreSQL procedure table itself, I found the following works correctly:

"Tweak" the PetaPoco Database.tt T4 template with:

tables["procedures"]["orderno"].PropertyType="int?";        // Setting default value of orderno to null forces PostgreSQL trigger.

Change the trigger procedure to:

CREATE OR REPLACE FUNCTION getnextorderno()
    RETURNS trigger AS
 $BODY$

BEGIN
IF NEW.orderno is null THEN
    NEW.orderno := nextval('order_number_seq');
END IF; 

Return NEW; 
END;

$BODY$
 LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION getnextorderno()
OWNER TO postgres;

As per Phill above, the trigger will be ignored if a value of 0 is used.

PetaPoco insert fails on tables with multiple triggers in PostgreSQL

2 Answers2