Hi every body,

If :

create table T(id int, x int, ..., ... );
alter table T alter id set default nextval('my_sequence');
alter table T alter x set default currval('my_sequence');

Can i be sure that when an insert occurs, id is assigned before x ?

Merci d'avance ;-)

--
Vincent

LoZ <pat@chaude> wrote:

Nice question ;-)

I have tested:

test=# create table T(id int, x int);
CREATE TABLE
Zeit: 0,718 ms
test=*# create sequence my_sequence;
CREATE SEQUENCE
Zeit: 0,922 ms
test=*# alter table T alter id set default nextval('my_sequence');
ALTER TABLE
Zeit: 36,416 ms
test=*# alter table T alter x set default currval('my_sequence');
ALTER TABLE
Zeit: 0,695 ms
test=*# insert into T values (default, default);
INSERT 0 1
Zeit: 0,354 ms
test=*# select * from T;
id | x
----+---
1 | 1
(1 Zeile)


But i'm not sure if this works reliable. Okay, lets try:

test=# create table T(id int, x int);
CREATE TABLE
Zeit: 1,150 ms
test=*# create sequence my_sequence;
CREATE SEQUENCE
Zeit: 19,393 ms
test=*# alter table T alter x set default nextval('my_sequence');
ALTER TABLE
Zeit: 0,545 ms
test=*# alter table T alter id set default currval('my_sequence');
ALTER TABLE
Zeit: 0,469 ms
test=*# insert into T values (default, default);
ERROR: currval of sequence "my_sequence" is not yet defined in this session
test=!#

Apparently the same, but i changed (only) the column-order.
I think, it is a nice idea, but also a wrong idea...



Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

On Fri, 08 Jan 2010 18:29:37 +0100, LoZ wrote:

I don't know the answer, but even if the answer is "yes", making your
design depend on such thing would be a grave design error.


--
http://mgogala.freehostia.com

Andreas Kretschmer écrivait :
with a trigger, but it seems that if :

create table T(id int, x int, ..., ... );
alter table T alter id set default nextval('my_sequence');

in a after insert trigger I can't do :

new.x=new.id

because the default clause is not executed yet...

Am I right ?

--
Vincent

On 2010-01-09, LoZ <pat@chaude> wrote:
no you can't do that in an after insert trigger because the record is
already inserted and modifying new will have no effect.

however you can do it an a before insert trigger.

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Sun, 10 Jan 2010 09:11:21 +0000, Jasen Betts wrote:

Wrong. Phrases "before" and "after" refer only to commit processing.
Triggers marked as "before transaction" are executed before commit
processing, during the initial block modification phase. Triggers marked
"after transaction" are executed after the block modification phase,
during the commit processing. In other words, "before" trigger will throw
an error immediately, while the "after" trigger will throw an error only
during the commit processing.



--
http://mgogala.freehostia.com

Mladen Gogala wrote:
Sorry, but Jasen is right and you are wrong.

BEFORE and AFTER in triggers has nothing to do with commit.
"after" is *not* short for "after transaction".

The documentation for the CREATE TRIGGER command describes in detail
when the trigger gets fired.

Here is the proof of concept that it will work with a BEFORE trigger:

test=> CREATE TABLE t(id int, x int);

test=> CREATE SEQUENCE my_sequence;

test=> ALTER TABLE t ALTER id SET DEFAULT nextval('my_sequence');

test=> CREATE FUNCTION t_set_x_trig() RETURNS trigger
test-> LANGUAGE plpgsql AS
test-> $$BEGIN NEW.x := NEW.id; RETURN NEW; END;$$;

test=> CREATE TRIGGER t_set_x BEFORE INSERT ON t FOR EACH ROW
test-> EXECUTE PROCEDURE t_set_x_trig();

test=> INSERT INTO t(id, x) VALUES (DEFAULT, DEFAULT);

test=> SELECT * FROM t;
id | x
----+---
1 | 1
(1 row)

Yours,
Laurenz Albe

On Mon, 25 Jan 2010 13:13:07 +0100, Laurenz Albe wrote:

We may be having terminological differences here.


Are we refering to this:
http://www.postgresql.org/docs/8.4/i...tetrigger.html
Parameters

name

The name to give the new trigger. This must be distinct from the name
of any other trigger for the same table.
BEFORE
AFTER

Determines whether the function is called before or after the event.
event

One of INSERT, UPDATE, DELETE, or TRUNCATE; this specifies the event
that will fire the trigger. Multiple events can be specified using OR.





I apologize, but I am not really sure what should this prove. I don't
really have time now but I will create and explain my examples later
today.



--
http://mgogala.byethost5.com

On Mon, 25 Jan 2010 16:54:15 +0000, Mladen Gogala wrote:
It seems that you're right:

I created a table just like yours and the following trigger:

scott=> CREATE FUNCTION t_set_x_trig() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN IF (NEW.x <= 5) THEN RAISE EXCEPTION 'SAMPLE EXCEPTION'; END IF;
END; $$;
scott=> create trigger t_test_x after update on t for each row
scott-> execute procedure t_set_x_trig();
CREATE TRIGGER
scott=> begin work;
BEGIN
scott=> update t set x=5 where id=5;
ERROR: SAMPLE EXCEPTION
scott=>
scott=> end;
ROLLBACK
scott=>

If I were right, the trigger wouldn't fire until the commit processing.
The trigger fired immediately. Funny thing is that the same is the case
with Oracle:

SQL> update t set x=4 where id=1;
update t set x=4 where id=1
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.T_TEST_X", line 2
ORA-04088: error during execution of trigger 'SCOTT.T_TEST_X'


Elapsed: 00:00:00.09

The trigger was, of course, defined like this:

1 create or replace trigger t_test_x
2 after update on t for each row
3 begin
4 if (:NEW.X <= 5) then raise PROGRAM_ERROR; end if;
5* end;

It seems that I've got something mixed up. Thanks for straightening me up.


--
http://mgogala.byethost5.com

Mladen Gogala wrote:
Yup, PostgreSQL and Oracle are pretty similar in this respect.
The only difference in behaviour I can think of is the
order in which multiple triggers for the same event get fired.

Yours,
Laurenz Albe