Re: [ADMIN] How to enforce the use of the sequence for serial columns
It's not a full solution anyway since it prevents any kind of update on the table due to check constraints firing even if target field not updated. See below; Right. It was not a valid idea ... Regards, Ben K. Developer http://benix.tamu.edu ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] How to enforce the use of the sequence for serial columns
> It's not a full solution anyway since it prevents any kind of update on the table due to check constraints firing even if target field not updated. > Well, we can extend the check within the trigger: if (coalesce old.a=new.a -- Update OR new.a = currval(tg_argv[0] -- Insert ) then return new; end if; Marc ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] How to enforce the use of the sequence for serial columns
"Ben K." <[EMAIL PROTECTED]> writes:
> > create table mytable (myid serial primary key constraint
> mytable_myid_chk check (myid = currval('mytable_myid_seq'), mydata
> varchar(255), ...);
>
>
> I'd like to clarify that this will not be a full solution, since it
> will not allow update of the table unless nextval has been used in the
> same sequence already.
It's not a full solution anyway since it prevents any kind of update
on the table due to check constraints firing even if target field not
updated. See below;
[EMAIL PROTECTED]
= create table foo (a serial check (a = currval('foo_a_seq')), b int);
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for serial
column "foo.a"
CREATE TABLE
[EMAIL PROTECTED]
= insert into foo values (default, 1);
INSERT 0 1
[EMAIL PROTECTED]
= insert into foo values (default, 1);
INSERT 0 1
[EMAIL PROTECTED]
= insert into foo values (default, 1);
INSERT 0 1
[EMAIL PROTECTED]
= insert into foo values (default, 1);
INSERT 0 1
[EMAIL PROTECTED]
= select * from foo;
a | b
---+---
1 | 1
2 | 1
3 | 1
4 | 1
(4 rows)
[EMAIL PROTECTED]
= update foo set b=2;
ERROR: new row for relation "foo" violates check constraint "foo_a_check"
[EMAIL PROTECTED]
=
>
> There seems to be a hack in case of oracle that allows using currval
> without nextval, but it's a hack and I don't know if there's an
> equivalent in postgresql
> (http://rootshell.be/~yong321/computer/sequence.txt). (Oracle seems to
> have "disable" option when adding check constraint by alter table, but
> the context seem a bit different from ours so may not be useful in our
> case, at any rate.)
>
>
>
>
> Regards
>
> Ben K.
> Developer
> http://benix.tamu.edu
>
>
>
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
---
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [ADMIN] How to enforce the use of the sequence for serial columns
create table mytable (myid serial primary key constraint
mytable_myid_chk check
(myid = currval('mytable_myid_seq'), mydata varchar(255), ...);
I'd like to clarify that this will not be a full solution, since it will
not allow update of the table unless nextval has been used in the same
sequence already.
There seems to be a hack in case of oracle that allows using currval
without nextval, but it's a hack and I don't know if there's an equivalent
in postgresql (http://rootshell.be/~yong321/computer/sequence.txt).
(Oracle seems to have "disable" option when adding check constraint by
alter table, but the context seem a bit different from ours so may not be
useful in our case, at any rate.)
Regards
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [ADMIN] How to enforce the use of the sequence for serial columns ?
>Trigger based solution where same trig can be used for any number of tables by changing the parameter. >Will throw one of 2 exceptions on failure to use sequence for the insert. Many thanks, This seems to be the simplest solution, Marc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] How to enforce the use of the sequence for serial columns
"Marc Mamin" <[EMAIL PROTECTED]> writes:
I'd like to ensure that nobody provide the ID in an insert statement
when the id is linked to a sequence.
I tried it with a trigger, but the id value is fed before the "BEFORE
INSERT" test is performed (see below)...
If you have the luxury of re-creating the table (dump, edit schema,
reload), you might want to try this:
create table mytable (myid serial primary key constraint mytable_myid_chk check (myid =
currval('mytable_myid_seq'), mydata varchar(255), ...);
This will prevent "serial" id field from being arbitrarily manipulated
outside the sequence..
There may be an equivalent way using alter table, but on a first look, currval
cannot be used to check existing values.
Regards
Ben K.
Developer
http://benix.tamu.edu
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] How to enforce the use of the sequence for serial columns ?
"Marc Mamin" <[EMAIL PROTECTED]> writes:
> I'd like to ensure that nobody provide the ID in an insert statement
> when the id is linked to a sequence.
> I tried it with a trigger, but the id value is fed before the "BEFORE
> INSERT" test is performed (see below)...
>
>
> Any Idea ?
Trigger based solution where same trig can be used for any number of
tables by changing the parameter.
Will throw one of 2 exceptions on failure to use sequence for the insert.
create table foo (a serial);
psql:q:2: NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
serial column "foo.a"
CREATE TABLE
create function footrig()
returns trigger
as $$
begin
-- may throw currval not defined exception
if new.a = currval(tg_argv[0]) then
-- currval defined and equal new col value
return new;
end if;
-- currval is defined but not used for this insert
raise exception 'Not using default sequence';
end
$$ language plpgsql;
CREATE FUNCTION
create trigger footrig
before insert on foo
for each row
execute procedure footrig('foo_a_seq');
CREATE TRIGGER
You are now connected to database "jerry".
insert into foo values (1000);
psql:q:25: ERROR: currval of sequence "foo_a_seq" is not yet defined in this
session
CONTEXT: PL/pgSQL function "footrig" line 3 at if
insert into foo values (default);
INSERT 0 1
insert into foo values (1000);
psql:q:27: ERROR: Not using default sequence
select * from foo;
a
---
1
(1 row)
> Cheers,
>
> Marc
>
>
>
> CREATE FUNCTION serialtest() RETURNS trigger AS $serialtest$
> BEGIN
> -- Check that the id is provided
> IF NEW.id IS NOT NULL THEN
> RAISE EXCEPTION 'id will be set from a sequence; do not
> provide it!';
> END IF;
>
> RETURN NEW;
> END;
> $serialtest$ LANGUAGE plpgsql;
>
>
> CREATE TABLE test_table
> (
> id serial primary key,
> foo int
> );
>
>
> CREATE TRIGGER test BEFORE INSERT OR UPDATE ON test_table
> FOR EACH ROW EXECUTE PROCEDURE serialtest();
>
>
> insert into test_table(foo)values(1);
>
> ERROR: id will be set from a sequence; do not provide it!
> SQL state: P0001
--
---
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [ADMIN] How to enforce the use of the sequence for serial columns ?
How to enforce the use of the sequence for serial columns ?There are many ways, here are a couple to think about: 1) Revoke all access to the table and then create a VIEW to access the table where by you simply don't use column "id" in the VIEWs insert / update statements. 2) Manually retrieve the serial number from a trigger function and modify column "id" in the function. Regards Donald Fraser - Original Message - From: Marc Mamin I'd like to ensure that nobody provide the ID in an insert statement when the id is linked to a sequence. I tried it with a trigger, but the id value is fed before the "BEFORE INSERT" test is performed (see below)... Any Idea ? Cheers, Marc CREATE FUNCTION serialtest() RETURNS trigger AS $serialtest$ BEGIN -- Check that the id is provided IF NEW.id IS NOT NULL THEN RAISE EXCEPTION 'id will be set from a sequence; do not provide it!'; END IF; RETURN NEW; END; $serialtest$ LANGUAGE plpgsql; CREATE TABLE test_table ( id serial primary key, foo int ); CREATE TRIGGER test BEFORE INSERT OR UPDATE ON test_table FOR EACH ROW EXECUTE PROCEDURE serialtest(); insert into test_table(foo)values(1); ERROR: id will be set from a sequence; do not provide it! SQL state: P0001 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
