How to force new records to have column values ONLY from a sequence in PostgreSQL?

Question

Please see answer below and Edit#1. However, the trigger still fails under PetaPoco/Npgsql.

I am missing something basic here. I need to ensure that any record added to the table has an orderno given only by the sequence. This should include even when the orderno is being supplied by the insert statement itself. That is,

insert into returntooffice (chart_recid, returndate, torder, **orderno**) values (14982,'2016-11-09','2017-12-4 00:21:42.553508', **0**);

and

insert into returntooffice (chart_recid, returndate, torder) values (14982,'2016-11-09','2017-12-4 00:21:42.553508');

should both result in the next orderno from the sequence and not an orderno of 0. That is, what is actually happening is that the supplied orderno (of 0) is being inserted--not the next sequence value (of 8000). I am using a trigger here since the actual composed insert is by an ORM that does not respect postgreSQL DEFAULT clauses on columns.

Here are the details:

CREATE TABLE returntooffice
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  chart_recid integer NOT NULL,
  returndate date,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  return_as_needed boolean,
  is_deferred boolean,
  CONSTRAINT returntooffice_pk PRIMARY KEY (recid),
  CONSTRAINT returntooffice_chart_fk FOREIGN KEY (chart_recid)
      REFERENCES charts (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT returntooffice_order_unqiue UNIQUE (orderno),
  CONSTRAINT returntooffice_unqiue UNIQUE (chart_recid, torder)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE returntooffice
  OWNER TO postgres;

CREATE TRIGGER get_next_order_number_trigger
  BEFORE INSERT
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();

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

CREATE SEQUENCE order_number_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 6558
  CACHE 1;

ALTER TABLE order_number_seq
  OWNER TO postgres;

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;

Edit#1: Renaming the trigger as suggested below, allows everything to work correctly under pgAdmin, but still fails on PetaPoco Insert. Any ideas why?

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

If you indeed created the insert trigger like that, the stored value will always be taken from the sequence. There must be something else going on. — user330315
– user330315, Commented Sep 3, 2016 at 20:00
@a_horse_with_no_name Agreed. But the trigger seems to be ignored even when doing it directly with PgAdmin. I don't get it. :( If I remember correctly, this did work in PostgreSQL 9.3 — Alan Wayne
– Alan Wayne, Commented Sep 3, 2016 at 21:30

Kamil Gosciminski · Accepted Answer · 2016-09-05 17:33:44Z

You may have a different issue such as another trigger on that table which also has the behaviour of BEFORE INSERT and executes after this trigger. Remember that triggers order of execution for the same behaviour is chosen alphabetically by their names.

I've tested your case. (Yes, on Postgres 9.5.4, since you claim that it doesn't work in this version.) Everything works fine. Input value gets overwritten every time with a next value from order_number_seq.

We're starting with a value of 6558 of the sequence, and so:

postgres=# insert into returntooffice (orderno, chart_recid) values (0, 1);
INSERT 0 1
postgres=# insert into returntooffice (chart_recid) values (2);
INSERT 0 1
postgres=# insert into returntooffice (orderno, chart_recid) values 
           (nextval('order_number_seq'::regclass), 3);
INSERT 0 1

And the output as expected is:

postgres=# select recid, orderno, chart_recid from returntooffice;
 recid | orderno | chart_recid
-------+---------+-------------
     1 |    6558 |           1
     2 |    6559 |           2
     3 |    6561 |           3

To reproduce the "issue" I had to modify create scripts by dropping constraints, replacing order of execution for different statements and removing unnecessary parts. Here it is:

CREATE TABLE returntooffice
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  chart_recid integer NOT NULL,
  returndate date,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  return_as_needed boolean,
  is_deferred boolean
)
WITH (
  OIDS=FALSE
);

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;

-- Trigger: get_next_order_number_trigger on returntooffice

-- DROP TRIGGER get_next_order_number_trigger ON returntooffice;

CREATE TRIGGER get_next_order_number_trigger
  BEFORE INSERT
  ON returntooffice
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();


CREATE SEQUENCE order_number_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 6558
  CACHE 1;

Changing the trigger name does work with PgAdmin, but this still fails under PetaPoco--but that is a different question. Thanks.

Clodoaldo Neto · Accepted Answer · 2016-09-05 20:17:41Z

Two alternatives to messy triggers. The first is to manipulate role privileges.

create table t (i serial, s text);

Revoke all privileges on the table from the pertinent roles:

revoke all on t from test;

Grant only select

grant select on t to test;

Grant all on all columns but the sequence one:

grant all (s) on t to test;

Grant usage only on the sequence:

revoke all on t_i_seq from test;
grant usage on t_i_seq to test;

Now that role can not insert into the sequence column:

insert into t (s) values ('a');
INSERT 0 1
insert into t (i,s) values (10,'a');
ERROR:  permission denied for relation t

The second alternative is simpler. Grant only select to the role:

revoke all on t from test;
grant select on t to test;

As the table owner create an insert function with 'security definer':

create function f(_s text)
returns t as $$

    insert into t (s) values (_s)
    returning *;

$$ language sql security definer;

The role will only be able to insert using the function.

Collectives™ on Stack Overflow

How to force new records to have column values ONLY from a sequence in PostgreSQL?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related