Re: some questions about Informix
Oh well ...
This problem has deprived quietness beside me.
I suppose that I find decision.
-----------------------------------------------------------------------
create raw table seq ( name char(8), val int default 0 ); -- !!!RAW!!!
create procedure seq_set_val( val int )define global g_seq_val int default 0;
let g_seq_val = val;
end procedure;
create trigger seq_urupdate on seq
referencing new as new
for each row
(
execute procedure seq_set_val(new.val)
)
create function sequence(
seq_name like seq.name,
seq_val like seq.val default 1
) returning int;define global g_seq_val int default 0;
update seq set val = val + seq_val where name = seq_name; return g_seq_val;
end function;
-------------------------------------------------------------------------------------
Good luck.
Sergey.
Sergey E. Volkov <sve@raiden.bancorp.ru> ''''' ' ''''''''':8tjqd8$nk31@www.informix.com...
> Hi Piotr.
>
> I think this is a really problem.
>
> Unless you commit your transaction, other sessions can't to get next
> sequence number.
>
> I think there is only one way to do this:
>
> create raw table seq ( id serial );>
> {
> With raw type you take advantage of light
> appends and avoid the overhead of logging.
> }
>
> create function sequence()
> returning int
> insert into seq (id) values (0);> return dbinfo('sqlca.sqlerrd2');
> end function;
>
> Good luck.
>
> Sergey.
>
> Piotr Wawrzyniak <Piotr.Wawrzyniak@put.poznan.pl> ''''' '
> ''''''''':8tjk20$eef$1@sunsite.icm.edu.pl...
> >
> > > > User1
> > > > begin work;
> > > > execute function sequence('same_name');> > > > User2
> > > > begin work;
> > > > execute function sequence('same_name');> > >
> > > If you use CURSOR STABILITY isolation level, then it is no problems with
> > > multiuser access for 'sequence'.
> >
> > I test and there still is a problem (because function sequence want to
> make
> > an update of locked row).
> >
> > > If you use fo
r example COMMITTED READ isolation it is also no problems.
> >
> > Yes a use COMMITTED READ
> >
> > > create function sequence( name like seq.name, inc like seq.val default
> 1 )
> > > returning int;> > > define l_val int;
> > > set isolation to cursor stability;> > > foreach curs
> > > for select s.val + inc into l_val from seq s where s.name =
name --
> > this
> > > place a shared lock on selected record
> > > update seq set val = l_val where current of curs;> > > end foreach;
> > > set isolation to read committed;> > > return l_val;
> > >
> > > end function
> >
> > I don't understand.
> > From manual: CURSOR STABILITY - Acquires a shared lock on the selected
> row.
> > Another process can also acquire a shared lock on the same row, but no
> > process can acquire an exclusive lock to modify data in the row.
> >
> > So how two different transactions can modify this same row ? (I'm not
> > interesting in DIRTY READ).
> >
> > The "normal" situation in DB is that transaction A view changes made by
> > transaction B when the B make a commit. The sequencer is not a "normal"
DB
> > object, because the changes are view by another transaction immadietly.
> > So my question is:
> > How to create something, what be visible for all transaction without
> making
> > a commit ?
> >
> > Piotrek
> >
> > p.s.
> > My idea is to use a serial value inserted into table.
> >
> > create table seq_name ( val serial ); --for every Oracle sequencer> different
> > table
> >
> > create procedure sequence(name varchar)
> > returning int;> > define val int;
> > if name = 'seq_name' then
> > insert into seq_name(val) values(0);> > select id into val from seq_name;
> > delete from seq_name where id=val;> > return val;
> > end if;
> > end procedure;
> >
> >
> >
> >
>
>
And better solution - without logical restore problems:
---------------------------------------------
create table seq ( id serial not null );
create procedure seq_set_val( val int )define global g_seq_val int default 0;
let g_seq_val = val;
raise exception -746
end procedure;
create trigger seq_urinsert on seq
referencing new as new
for each row
(
execute procedure seq_set_val(new.id)
)
create function sequence() returning int;define global g_seq_val int default 0;
on exception in (-746)
end exception with resume;
insert into s values (0); return g_seq_val;
end function;
---------------------------------------------------------
Good luck.
Sergey.
Sergey E. Volkov <sve@raiden.bancorp.ru> ''''' ' ''''''''':8tk00g$nk21@www.informix.com...
> Oh well ...
>
> This problem has deprived quietness beside me.
> I suppose that I find decision.
> -----------------------------------------------------------------------
> create raw table seq ( name char(8), val int default 0 ); -- !!!RAW!!!>
> create procedure seq_set_val( val int )> define global g_seq_val int default 0;
> let g_seq_val = val;
> end procedure;
>
> create trigger seq_ur> update on seq
> referencing new as new
> for each row
> (
> execute procedure seq_set_val(new.val)
> )>
> create function sequence(
> seq_name like seq.name,
> seq_val like seq.val default 1
> ) returning int;> define global g_seq_val int default 0;
> update seq set val = val + seq_val where name = seq_name;> return g_seq_val;
> end function;
> -------------------------------------------------------------------------------------
>
> Good luck.
>
> Sergey.
>
> Sergey E. Volkov <sve@raiden.bancorp.ru> ''''' ' ''''''''':8tjqd8$nk31@www.informix.com...
> > Hi Piotr.
> >
> > I think this is a really problem.
> >
> > Unless you commit your transaction, other sessions can't to get next
> > sequence number.
> >
> > I think there is only one way to do this:
> >
> >
create raw table seq ( id serial );> >
> > {
> > With raw type you take advantage of light
> > appends and avoid the overhead of logging.
> > }
> >
> > create function sequence()
> > returning int
> > insert into seq (id) values (0);> > return dbinfo('sqlca.sqlerrd2');
> > end function;
> >
> > Good luck.
> >
> > Sergey.
> >
> > Piotr Wawrzyniak <Piotr.Wawrzyniak@put.poznan.pl> ''''' '
> > ''''''''':8tjk20$eef$1@sunsite.icm.edu.pl...
> > >
> > > > > User1
> > > > > begin work;
> > > > > execute function sequence('same_name');> > > > > User2
> > > > > begin work;
> > > > > execute function sequence('same_name');> > > >
> > > > If you use CURSOR STABILITY isolation level, then it is no problems
with
> > > > multiuser access for 'sequence'.
> > >
> > > I test and there still is a problem (because function sequence want to
> > make
> > > an update of locked row).
> > >
> > > > If you use fo
> r example COMMITTED READ isolation it is also no problems.
> > >
> > > Yes a use COMMITTED READ
> > >
> > > > create function sequence( name like seq.name, inc like seq.valdefault
> > 1 )
> > > > returning int;
> > > > define l_val int;
> > > > set isolation to cursor stability;> > > > foreach curs
> > > > for select s.val + inc into l_val from seq s where s.name =
> name --
> > > this
> > > > place a shared lock on selected record
> > > > update seq set val = l_val where current of curs;> > > > end foreach;
> > > > set isolation to read committed;> > > > return l_val;
> > > >
> > > > end function
> > >
> > > I don't understand.
> > > From manual: CURSOR STABILITY - Acquires a shared lock on the selected
> > row.
> > > Another process can also acquire a shared lock on the same row, but no
> > > process can acquire an exclusive lock to modify data in the row.
> > >
> > > So how two different transactions can modify this same row ? (I'm not
> > > interesting in DIRTY READ).
> > >
> > > The "normal" situation in DB is that transaction A view changes made
by
> > > transaction B when the B make a commit. The sequencer is not a
"normal"
> DB
> > > object, because the changes are view by another transaction
immadietly.
> > > So my question is:
> > > How to create something, what be visible for all transaction without
> > making
> > > a commit ?
> > >
> > > Piotrek
> > >
> > > p.s.
> > > My idea is to use a serial value inserted into table.
> > >
> > > create table seq_name ( val serial ); --for every Oracle sequencer> > different
> > > table
> > >
> > > create procedure sequence(name varchar)
> > > returning int;> > > define val int;
> > > if name = 'seq_name' then
> > > insert into seq_name(val) values(0);> > > select id into val from seq_name;
> > > delete from seq_name where id=val;> > > return val;
> > > end if;
> > > end procedure;
> > >
> > >
> > >
> > >
> >
> >
>
>
Hi Sergey.
I must told that the solution:
> ---------------------------------------------
> create table seq ( id serial not null );>
> create procedure seq_set_val( val int )> define global g_seq_val int default 0;
> let g_seq_val = val;
> raise exception -746
> end procedure;
>
> create trigger seq_ur> insert on seq
> referencing new as new
> for each row ( execute procedure seq_set_val(new.id) )
>
> create function sequence() returning int;> define global g_seq_val int default 0;
> on exception in (-746)
> end exception with resume;
> insert into s values (0);> return g_seq_val;
> end function;
> ---------------------------------------------------------
is really good, and all my criteria all satisfacted.
The idea of exception in insert trigger is very good.
> Good luck.
Great Thank's for Your help.
Piotrek
Piotr.Wawrzyniak@put.poznan.pl
Hi !
> And better solution - without logical restore problems:
> ---------------------------------------------
> create table seq ( id serial not null );>
> create procedure seq_set_val( val int )> define global g_seq_val int default 0;
> let g_seq_val = val;
> raise exception -746
> end procedure;
>
> create trigger seq_ur> insert on seq
> referencing new as new
> for each row
>
> execute procedure seq_set_val(new.id)
> )>
> create function sequence() returning int;> define global g_seq_val int default 0;
> on exception in (-746)
> end exception with resume;
> insert into s values (0);> return g_seq_val;
> end function;
> ---------------------------------------------------------
> Good luck.
>
> Sergey.
We use this solution but with some modification (cuts):
create procedure sequence() returning int; on exception in (-391)
return dbinfo('sqlca.sqlerrd1');
end exception with resume;
insert into tab_xxx(xx_id) values (0); -- tab_xxx is existed table withserial column xx_id and another not null column
end procedure;
So we must only modify stored procedure, and no addidional DB objects are
needed;
But if we want the pure sequencer, there is no problem too:
create table seq(s serial check(s<0));
create procedure sequence() returning int; on exception in (-530)
return dbinfo('sqlca.sqlerrd1');
end exception with resume;
insert into seq(s) values (0);end procedure;
Piotr Wawrzyniak
Piotr.Wawrzyniak@put.poznan.pl