From: Zac <zaccheob@inwind.it>
Subject: Re: Help on Procedure running external function
Date: Mon, 04 Jul 2005 17:27:20 +0200
Organization: Hub.Org Networking Services
Lines: 68
Message-ID: <dabkio$1alj$1@news.hub.org>
References: <opsteqmfbkawcxfg@adi> <dabb6g$7k9$1@news.hub.org>
	<opstevy1lkawcxfg@adi>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla Thunderbird 1.0 (X11/20041206)
In-Reply-To: <opstevy1lkawcxfg@adi>
To: pgsql-sql@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
>>
> 
> 
>