3

I want to prevent explicit inserts into serial columns. I have come up to the following trigger:

drop table test_table;

create table test_table(
id bigserial primary key,
foobar text
);

create or replace function serial_id_check() returns trigger as
$$
begin
    if new.id != currval(TG_TABLE_NAME||'_id_seq') then
        raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(TG_TABLE_NAME||'_id_seq'), new.id;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger test_table_serial_id_check
before insert on test_table
for each row
execute procedure serial_id_check();

Maybe there is a better approach? Maybe this approach is broken and this cannot be achieved at all?

P.S. I also think about not granting rights for the insert and update, but only for pgplsql procedure for inserting/updating - but this approach is not possible for me right now.

  • Why do you want this? And what is missing from your trigger? – dezso Sep 24 '15 at 21:26
  • This might break for multi-valued inserts, insert into ... select ... , etc. Not sure. – Craig Ringer Sep 25 '15 at 0:28
  • Can you please explain about multi-valued inserts? – Ivan Sopov Sep 25 '15 at 8:23
  • with d as ( select 'pgadmin' as foo union all select 'pgadmin' union all select 'pgadmin' ) insert into test_table(foobar) select foo from d; This query works – Ivan Sopov Sep 25 '15 at 8:35
10

Updateable View

In Postgres 9.4, views are automatically updatable per column. I.e., as long as basic conditions are met, columns are automatically updatable for plain references to underlying columns. Expressions are not. We can exploit this feature:

CREATE TABLE test_table(
  id serial PRIMARY KEY
, foobar text
);

CREATE VIEW test_view AS
SELECT id * 1 AS id  -- id unchanged but not updatable!
     , foobar
FROM   test_table;

By multiplying id with 1, the value is unchanged but the column is not automatically updatable any more.

  • REVOKE INSERT / UPDATE privileges on test_table from your users.
  • Make another role holding those privileges own the view.
  • GRANT INSERT / UPDATE on test_view to your users.

Now they can do everything, but they cannot manually set or alter the value in id. It's your choice how to handle DELETE.

This simple and quick solution works out of the box in Postgres 9.4. Automatically updateable views were introduced with Postgres 9.3, but all columns need to be updateable in that version for the feature to work.

In Postgres 9.3 or older, provide an INSTEAD OF INSERT trigger or an unconditional ON INSERT DO INSTEAD rule.

While writing rules / triggers manually, you don't need the id * 1 trick. You might want to use this even in Postgres 9.4+ to fine-tune functionality. Example:

CREATE VIEW test_view1 AS TABLE test_table;

CREATE OR REPLACE RULE ins_up AS
 ON INSERT TO test_view1 DO INSTEAD
INSERT INTO test_table (foobar) VALUES (NEW.foobar);

Now, INSERT on the view is possible, but not yet UPDATE or DELETE. Write more rules if you want that.

SQL Fiddle (Postgres 9.6)

Important difference: while the automatically updatable view rejects attempts to INSERT / UPDATE values in id with an exception, the demonstrated RULE simply ignores values for id and proceeds without exception.

Simple trigger

Alternatively, you could simply overwrite the serial column with the next value from its sequence unconditionally:

CREATE OR REPLACE FUNCTION force_serial_id()
  RETURNS trigger AS
$func$
BEGIN
   NEW.id := nextval(pg_get_serial_sequence(quote_ident(TG_TABLE_NAME), 'id'));
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

That's simpler and cheaper and less error-prone than trying to be smart about it. quote_ident() to safely escape otherwise illegal names (also defends against SQL injection).

Like @dezso commented, this burns two numbers per row in normal operation with a serial column because a default is fetched before the trigger function kicks in. Typically, gaps in a sequence should not be a problem (to be expected anyway), but you can avoid the side effect by removing the DEFAULT from the column. Then you rely on the trigger exclusively.

You could fine-tune the UPDATE case with a separate trigger and a condition on the trigger itself WHEN (OLD.id <> NEW.id). Syntax example:

Note the use of pg_get_serial_sequence(), which won't break like your original for non-basic identifiers. Think of "MyTable" or a non-default sequence name. Still assuming the column name id which I personally never use since it's not descriptive.

  • 2
    The funny side effect of this trigger is that it skips a value in the sequence when you rely on nextval() as default. Otherwise very good ideas. – dezso Sep 25 '15 at 8:15
  • @dezso: Good catch. I added a bit to address that. – Erwin Brandstetter Sep 25 '15 at 14:41
  • 1
    For the trigger I would recommend the more robust NEW.id := nextval(pg_get_serial_sequence('"' || TG_TABLE_NAME || '"', 'id')); It took me some debbuging to realize this caused the issue. – SzieberthAdam May 1 at 15:54
  • @SzieberthAdam: Good point. Better yet: quote_ident(). See update. – Erwin Brandstetter May 1 at 16:33
  • thanks, i've learned something new – SzieberthAdam May 1 at 17:22
4

Identity Columns

This isn't exactly an answer yet, but what you want is GENERATED ALWAYS. And, it's on its way, maybe as soon as the next release of PostgreSQL, PostgreSQL 10

CREATE TABLE itest4 (
  a int GENERATED ALWAYS AS IDENTITY,
  b text
);

Syntax stolen verbatum from the emails about the patch.

Track the issue in the commitfest here.

From the SQL Spec,

If <identity column specification> is specified, then:
i) An indication that the column is an identity column.
ii) If ALWAYS is specified, then an indication that values are always generated.
iii) If BY DEFAULT is specified, then an indication that values are generated by default.
iv) The General Rules of Subclause 9.26, “Creation of a sequence generator”, are applied with
SGO as OPTIONS and ICT as DATA TYPE; let the descriptor of the sequence generator SG be
the SEQGENDESC returned from the application of those General Rules.
0

The approved answer is all good, but when trying to troubleshoot a scenario where code from somewhere appears to be inserting explicit ID values and thereby breaking the sequence down the road, you really want to raise an error to the offending code instead of simply fixing the problem on the fly (or rewriting everything to use a view).

The OPs own solution does break in the scenario where you do multiple inserts via either INSERT INTO table (col2) VALUES ('val'), ('val') or INSERT INTO table (col2) SELECT somefield FROM anothertable.

This slight fix to the trigger condition fixes that issue. Also note the use of pg_get_serial_sequence to more reliably determine the name of the sequence. (Note that pg_get_serial_sequence relies on the sequence's OWNED BY property being correctly set.)

create or replace function serial_id_check() returns trigger as
$$
begin
    if new.id > currval(pg_get_serial_sequence(TG_TABLE_NAME, 'id')) then
        raise exception 'Explicit insert into serial id, currval = %, tried to insert = %', currval(pg_get_serial_sequence(TG_TABLE_NAME, 'id')), new.id;
    end if;
    return new;
end;
$$ language plpgsql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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