Thread: Help on Procedure running external function
Hello, I have a 'big' problem: I am trying to run from a procedure a function witch generate a new document number (max from table +1 ) and after to insert a document with this number, but the function returns me the same number each time because the tranzaction is not finished and the inserts are not commited and of course the next document number is the same. ... for ... loop nr=get_me_next_number(params); insert into table values (nr,...) end loop ... ... error inserting in table .. primary_key nr ..... Is any way in making the external function to 'know' that I inserted another row but this insert is in a tranzaction that is not finish yet ? ar onother solution ? for now I 'solved' by asking for a nr once and generate myself next number (+1) but this is not a correct solution (in this time somebody else could insert a document with the same nr as the procedure ) thank you, Adi -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Din Adrian wrote: > Hello, > I have a 'big' problem: > I am trying to run from a procedure a function witch generate a new > document number (max from table +1 ) and after to insert a document > with this number, but the function returns me the same number each time > because the tranzaction is not finished and the inserts are not > commited and of course the next document number is the same. > ... > > for ... > loop > nr=get_me_next_number(params); > insert into table values (nr,...) > end loop > ... > > ... > error inserting in table .. primary_key nr ..... > > Is any way in making the external function to 'know' that I inserted > another row but this insert is in a tranzaction that is not finish yet > ? ar onother solution ? > > for now I 'solved' by asking for a nr once and generate myself next > number (+1) but this is not a correct solution (in this time somebody > else could insert a document with the same nr as the procedure ) > > thank you, > Adi > > I think the best solution is to use a sequence, not "select max(id) +1 from table". Look here: http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html Bye
Yes , I know what sequence is, but our procedure for generating doc numbers is v. big and has manny (4) parameters and we did'nt use sequence in it for this reason .... any other advice ? thak you, Adi On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote: > Din Adrian wrote: >> Hello, >> I have a 'big' problem: >> I am trying to run from a procedure a function witch generate a new >> document number (max from table +1 ) and after to insert a document >> with this number, but the function returns me the same number each >> time because the tranzaction is not finished and the inserts are not >> commited and of course the next document number is the same. >> ... >> for ... >> loop >> nr=get_me_next_number(params); >> insert into table values (nr,...) >> end loop >> ... >> ... >> error inserting in table .. primary_key nr ..... >> Is any way in making the external function to 'know' that I inserted >> another row but this insert is in a tranzaction that is not finish yet >> ? ar onother solution ? >> for now I 'solved' by asking for a nr once and generate myself next >> number (+1) but this is not a correct solution (in this time somebody >> else could insert a document with the same nr as the procedure ) >> thank you, >> Adi >> > I think the best solution is to use a sequence, not "select max(id) +1 > from table". Look here: > http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html > http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html > Bye > > ---------------------------(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 > -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
Maybe you're looking for the VOLATILE attribute of a function. For the query optimizer it depends on what type of function you have how often it is called. |-----Original Message----- |From: Din Adrian [mailto:adrian.din@easynet.ro] |Sent: Dienstag, 05. Juli 2005 01:10 |To: Zac; pgsql-sql@postgresql.org |Subject: Re: [SQL] Help on Procedure running external function | | | |Yes , I know what sequence is, but our procedure for generating doc |numbers is v. big and has manny (4) parameters and we did'nt |use sequence |in it for this reason .... | |any other advice ? | |thak you, |Adi | |On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote: | |> Din Adrian wrote: |>> Hello, |>> I have a 'big' problem: |>> I am trying to run from a procedure a function witch |generate a new |>> document number (max from table +1 ) and after to insert a |document |>> with this number, but the function returns me the same |number each |>> time because the tranzaction is not finished and the |inserts are not |>> commited and of course the next document number is the same. |>> ... |>> for ... |>> loop |>> nr=get_me_next_number(params); |>> insert into table values (nr,...) |>> end loop |>> ... |>> ... |>> error inserting in table .. primary_key nr ..... |>> Is any way in making the external function to 'know' that |I inserted |>> another row but this insert is in a tranzaction that is not |finish yet |>> ? ar onother solution ? |>> for now I 'solved' by asking for a nr once and generate |myself next |>> number (+1) but this is not a correct solution (in this |time somebody |>> else could insert a document with the same nr as the procedure ) |>> thank you, |>> Adi |>> |> I think the best solution is to use a sequence, not "select |max(id) +1 |> from table". Look here: |> |http://www.postgresql.org/docs/8.0/intera|ctive/sql-createsequence.html |> |http://www.postgresql.org/docs/8.0/intera|ctive/functions-sequence.html |> Bye |> |> ---------------------------(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 |> | | | |-- |Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ | | |---------------------------(end of |broadcast)--------------------------- |TIP 1: subscribe and unsubscribe commands go to |majordomo@postgresql.org |
Din Adrian wrote: > > Yes , I know what sequence is, but our procedure for generating doc > numbers is v. big and has manny (4) parameters and we did'nt use > sequence in it for this reason .... > > any other advice ? I think there is no way to have any information about non committed transactions. I don't know if I understand well your problem but from what I see "get_me_next_number" function runs in a different transaction (Why? Is it an externale procedure that make its own connection to the DB?)), otherwise it would see the new inserted number. The better solution is to run "get_me_next_number" in the same transaction. Another solution (not so good but should work) is to generate by yourself the number (as you do) and lock the table until you end to prevent others inserting documents. I hope this helps you. Bye > > thak you, > Adi > > On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote: > >> Din Adrian wrote: >> >>> Hello, >>> I have a 'big' problem: >>> I am trying to run from a procedure a function witch generate a new >>> document number (max from table +1 ) and after to insert a document >>> with this number, but the function returns me the same number each >>> time because the tranzaction is not finished and the inserts are >>> not commited and of course the next document number is the same. >>> ... >>> for ... >>> loop >>> nr=get_me_next_number(params); >>> insert into table values (nr,...) >>> end loop >>> ... >>> ... >>> error inserting in table .. primary_key nr ..... >>> Is any way in making the external function to 'know' that I >>> inserted another row but this insert is in a tranzaction that is >>> not finish yet ? ar onother solution ? >>> for now I 'solved' by asking for a nr once and generate myself >>> next number (+1) but this is not a correct solution (in this time >>> somebody else could insert a document with the same nr as the >>> procedure ) >>> thank you, >>> Adi >>> >> I think the best solution is to use a sequence, not "select max(id) >> +1 from table". Look here: >> http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html >> http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html >> Bye >> >> ---------------------------(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 >> > > >
On Mon, Jul 04, 2005 at 16:10:03 -0700, Din Adrian <adrian.din@easynet.ro> wrote: > > Yes , I know what sequence is, but our procedure for generating doc > numbers is v. big and has manny (4) parameters and we did'nt use sequence > in it for this reason .... Then use a sequence for part of the number.
1. the function get_me_next_number is runing from this procedure (same
trans) but it's an external one ...
2. the second solution I'am using now (using temp a table to store each
maxnrdoc value - but the temp table give me sometimes a relation with OID
##### does not exist - problem that I can only solve by using only
execute - but I don't think I can ...?! :))
here is the example:
create temp table MagMaxNrBon
(
magazie varchar(5),
MaxNrBon int8,
CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
);
...
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare; if vnrbon is null then select into vNrBon o_gen_calc_nr_doc from
o_gen_calc_nr_doc('BC'
,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon); insert into MagMaxNrBon values
(dsgroup.magazie_implicita_lansare,vNrBon); else update MagMaxNrBon set maxnrbon=vNrBon where magazie =
dsgroup.magazie_implicita_lansare; end if;
...
and in this way vNrBon is correct one ... I will try to use oly execute on
insert,update and select on temp table MagMaxNrBon .... (o_gen_calc_nr_doc
is the "get_me_next_number" function)
thank you,
Adria Din
On Mon, 04 Jul 2005 17:27:20 +0200, Zac <zaccheob@inwind.it> wrote:
> Din Adrian wrote:
>> Yes , I know what sequence is, but our procedure for generating doc
>> numbers is v. big and has manny (4) parameters and we did'nt use
>> sequence in it for this reason ....
>> any other advice ?
> I think there is no way to have any information about non committed
> transactions.
> I don't know if I understand well your problem but from what I see
> "get_me_next_number" function runs in a different transaction (Why? Is
> it an externale procedure that make its own connection to the DB?)),
> otherwise it would see the new inserted number. The better solution is
> to run "get_me_next_number" in the same transaction.
> Another solution (not so good but should work) is to generate by
> yourself the number (as you do) and lock the table until you end to
> prevent others inserting documents.
> I hope this helps you.
> Bye
>
>> thak you,
>> Adi
>> On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote:
>>
>>> Din Adrian wrote:
>>>
>>>> Hello,
>>>> I have a 'big' problem:
>>>> I am trying to run from a procedure a function witch generate a new
>>>> document number (max from table +1 ) and after to insert a document
>>>> with this number, but the function returns me the same number each
>>>> time because the tranzaction is not finished and the inserts are
>>>> not commited and of course the next document number is the same.
>>>> ...
>>>> for ...
>>>> loop
>>>> nr=get_me_next_number(params);
>>>> insert into table values (nr,...)
>>>> end loop
>>>> ...
>>>> ...
>>>> error inserting in table .. primary_key nr .....
>>>> Is any way in making the external function to 'know' that I
>>>> inserted another row but this insert is in a tranzaction that is
>>>> not finish yet ? ar onother solution ?
>>>> for now I 'solved' by asking for a nr once and generate myself
>>>> next number (+1) but this is not a correct solution (in this time
>>>> somebody else could insert a document with the same nr as the
>>>> procedure )
>>>> thank you,
>>>> Adi
>>>>
>>> I think the best solution is to use a sequence, not "select max(id)
>>> +1 from table". Look here:
>>> http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
>>> http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
>>> Bye
>>>
>>> ---------------------------(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
>>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
I think you should use 'FOR UPDATE' clause in your first "select":
select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
dsgroup.magazie_implicita_lansare FOR UPDATE;
In this way you lock the rows eventually returned and no one can update
them (or select them "for update") until your transaction finished.
Is this good for you?
However IMHO you should think your procedures to use sequences...
> 1. the function get_me_next_number is runing from this procedure (same
> trans) but it's an external one ...
> 2. the second solution I'am using now (using temp a table to store each
> maxnrdoc value - but the temp table give me sometimes a relation with
> OID ##### does not exist - problem that I can only solve by using
> only execute - but I don't think I can ...?! :))
> here is the example:
>
>
> create temp table MagMaxNrBon
> (
> magazie varchar(5),
> MaxNrBon int8,
> CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
> );
> ...
>
> select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
> dsgroup.magazie_implicita_lansare;
> if vnrbon is null
> then
> select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
> ,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
> insert into MagMaxNrBon values
> (dsgroup.magazie_implicita_lansare,vNrBon);
> else
> update MagMaxNrBon set maxnrbon=vNrBon where magazie =
> dsgroup.magazie_implicita_lansare;
> end if;
> ...
>
> and in this way vNrBon is correct one ... I will try to use oly execute
> on insert,update and select on temp table MagMaxNrBon ....
> (o_gen_calc_nr_doc is the "get_me_next_number" function)
>
> thank you,
> Adria Din
>
>
> On Mon, 04 Jul 2005 17:27:20 +0200, Zac <zaccheob@inwind.it> wrote:
>
>> Din Adrian wrote:
>>
>>> Yes , I know what sequence is, but our procedure for generating
>>> doc numbers is v. big and has manny (4) parameters and we did'nt
>>> use sequence in it for this reason ....
>>> any other advice ?
>>
>> I think there is no way to have any information about non committed
>> transactions.
>> I don't know if I understand well your problem but from what I see
>> "get_me_next_number" function runs in a different transaction (Why?
>> Is it an externale procedure that make its own connection to the
>> DB?)), otherwise it would see the new inserted number. The better
>> solution is to run "get_me_next_number" in the same transaction.
>> Another solution (not so good but should work) is to generate by
>> yourself the number (as you do) and lock the table until you end to
>> prevent others inserting documents.
>> I hope this helps you.
>> Bye
>>
>>> thak you,
>>> Adi
>>> On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote:
>>>
>>>> Din Adrian wrote:
>>>>
>>>>> Hello,
>>>>> I have a 'big' problem:
>>>>> I am trying to run from a procedure a function witch generate a
>>>>> new document number (max from table +1 ) and after to insert a
>>>>> document with this number, but the function returns me the same
>>>>> number each time because the tranzaction is not finished and the
>>>>> inserts are not commited and of course the next document number
>>>>> is the same.
>>>>> ...
>>>>> for ...
>>>>> loop
>>>>> nr=get_me_next_number(params);
>>>>> insert into table values (nr,...)
>>>>> end loop
>>>>> ...
>>>>> ...
>>>>> error inserting in table .. primary_key nr .....
>>>>> Is any way in making the external function to 'know' that I
>>>>> inserted another row but this insert is in a tranzaction that is
>>>>> not finish yet ? ar onother solution ?
>>>>> for now I 'solved' by asking for a nr once and generate myself
>>>>> next number (+1) but this is not a correct solution (in this
>>>>> time somebody else could insert a document with the same nr as
>>>>> the procedure )
>>>>> thank you,
>>>>> Adi
>>>>>
>>>> I think the best solution is to use a sequence, not "select max(id)
>>>> +1 from table". Look here:
>>>> http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
>>>> http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
>>>> Bye
>>>>
>>>> ---------------------------(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
>>>>
>>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faq
>>
>
>
>
I 'solved' my 'relation with OID ##### does not exist' problem using only
execute on temp table ...
Is no need for me to 'lock'(for update) the temp table - is temporary
(lock at 'create temp table MagMaxNrBon')- everybody has his own copy ....
Your right I should change the get_number procedure to create 'on the fly
sequences' for each new user settings (in our app the user have the power
to set the way of generating number for every type of document) (for that
I need time wich I don't have now :) )...
anyway .. is working (not how I wanted but it's ok)
thank you,
Adrian Din
On Tue, 05 Jul 2005 13:47:24 +0200, Zac <zaccheob@inwind.it> wrote:
> I think you should use 'FOR UPDATE' clause in your first "select":
>
> select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
> dsgroup.magazie_implicita_lansare FOR UPDATE;
>
> In this way you lock the rows eventually returned and no one can update
> them (or select them "for update") until your transaction finished.
>
> Is this good for you?
>
> However IMHO you should think your procedures to use sequences...
>
>
>> 1. the function get_me_next_number is runing from this procedure (same
>> trans) but it's an external one ...
>> 2. the second solution I'am using now (using temp a table to store
>> each maxnrdoc value - but the temp table give me sometimes a relation
>> with OID ##### does not exist - problem that I can only solve by
>> using only execute - but I don't think I can ...?! :))
>> here is the example:
>> create temp table MagMaxNrBon
>> (
>> magazie varchar(5),
>> MaxNrBon int8,
>> CONSTRAINT temp_magbon_pkey PRIMARY KEY (magazie)
>> );
>> ...
>> select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie =
>> dsgroup.magazie_implicita_lansare;
>> if vnrbon is null
>> then
>> select into vNrBon o_gen_calc_nr_doc from o_gen_calc_nr_doc('BC'
>> ,vEmitent,dsgroup.magazie_implicita_lansare,dsMasterBon.dataBon);
>> insert into MagMaxNrBon values
>> (dsgroup.magazie_implicita_lansare,vNrBon);
>> else
>> update MagMaxNrBon set maxnrbon=vNrBon where magazie =
>> dsgroup.magazie_implicita_lansare;
>> end if;
>> ...
>> and in this way vNrBon is correct one ... I will try to use oly
>> execute on insert,update and select on temp table MagMaxNrBon ....
>> (o_gen_calc_nr_doc is the "get_me_next_number" function)
>> thank you,
>> Adria Din
>> On Mon, 04 Jul 2005 17:27:20 +0200, Zac <zaccheob@inwind.it> wrote:
>>
>>> Din Adrian wrote:
>>>
>>>> Yes , I know what sequence is, but our procedure for generating
>>>> doc numbers is v. big and has manny (4) parameters and we did'nt
>>>> use sequence in it for this reason ....
>>>> any other advice ?
>>>
>>> I think there is no way to have any information about non committed
>>> transactions.
>>> I don't know if I understand well your problem but from what I see
>>> "get_me_next_number" function runs in a different transaction (Why?
>>> Is it an externale procedure that make its own connection to the
>>> DB?)), otherwise it would see the new inserted number. The better
>>> solution is to run "get_me_next_number" in the same transaction.
>>> Another solution (not so good but should work) is to generate by
>>> yourself the number (as you do) and lock the table until you end to
>>> prevent others inserting documents.
>>> I hope this helps you.
>>> Bye
>>>
>>>> thak you,
>>>> Adi
>>>> On Mon, 04 Jul 2005 14:47:16 +0200, Zac <zaccheob@inwind.it> wrote:
>>>>
>>>>> Din Adrian wrote:
>>>>>
>>>>>> Hello,
>>>>>> I have a 'big' problem:
>>>>>> I am trying to run from a procedure a function witch generate a
>>>>>> new document number (max from table +1 ) and after to insert a
>>>>>> document with this number, but the function returns me the same
>>>>>> number each time because the tranzaction is not finished and the
>>>>>> inserts are not commited and of course the next document number
>>>>>> is the same.
>>>>>> ...
>>>>>> for ...
>>>>>> loop
>>>>>> nr=get_me_next_number(params);
>>>>>> insert into table values (nr,...)
>>>>>> end loop
>>>>>> ...
>>>>>> ...
>>>>>> error inserting in table .. primary_key nr .....
>>>>>> Is any way in making the external function to 'know' that I
>>>>>> inserted another row but this insert is in a tranzaction that is
>>>>>> not finish yet ? ar onother solution ?
>>>>>> for now I 'solved' by asking for a nr once and generate myself
>>>>>> next number (+1) but this is not a correct solution (in this
>>>>>> time somebody else could insert a document with the same nr as
>>>>>> the procedure )
>>>>>> thank you,
>>>>>> Adi
>>>>>>
>>>>> I think the best solution is to use a sequence, not "select max(id)
>>>>> +1 from table". Look here:
>>>>> http://www.postgresql.org/docs/8.0/interactive/sql-createsequence.html
>>>>> http://www.postgresql.org/docs/8.0/interactive/functions-sequence.html
>>>>> Bye
>>>>>
>>>>> ---------------------------(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
>>>>>
>>>>
>>>
>>> ---------------------------(end of
>>> broadcast)---------------------------
>>> TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/docs/faq
>>>
>>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
On Tue, Jul 05, 2005 at 13:47:24 +0200, Zac <zaccheob@inwind.it> wrote: > I think you should use 'FOR UPDATE' clause in your first "select": > > select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie = > dsgroup.magazie_implicita_lansare FOR UPDATE; > > In this way you lock the rows eventually returned and no one can update > them (or select them "for update") until your transaction finished. That doesn't work because you don't lock rows which aren't in the table yet. You need to use a lock table instead.
I am sorry but: the table is TEMPORARY ... I don't need to do any lock on it .... (eventualy I should do a lock on the table where I want to insert the documents - the MagMaxNrBon is only a temp table used for storing the max document nr for each user's setings in this transaction - I did that temp table instead of running always the return_next_number function (the problem I postit first) - I run once the function for every user's settings - get the max doc free number, store in the temp table, do the insert in onother table and next time I get this number and raise by 1 instead of running again the get_next_number function (because as I said - it give the same max number (the insert into table is not 'visible') )) ... as I said it's 'solved' ... thank you, Adrian Din On Tue, 5 Jul 2005 07:39:48 -0500, Bruno Wolff III <bruno@wolff.to> wrote: > On Tue, Jul 05, 2005 at 13:47:24 +0200, > Zac <zaccheob@inwind.it> wrote: >> I think you should use 'FOR UPDATE' clause in your first "select": >> >> select into vnrBon maxnrbon+1 from MagMaxNrBon where magazie = >> dsgroup.magazie_implicita_lansare FOR UPDATE; >> >> In this way you lock the rows eventually returned and no one can update >> them (or select them "for update") until your transaction finished. > > That doesn't work because you don't lock rows which aren't in the table > yet. You need to use a lock table instead. > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/