From: Din Adrian <adrian.din@easynet.ro>
To: Zac <zaccheob@inwind.it>
To: pgsql-sql@postgresql.org
Subject: Re: Help on Procedure running external function
Date: Tue, 05 Jul 2005 12:10:41 -0700
Message-ID: <opstgfj3k4awcxfg@adi> (raw)
In-Reply-To: <dabkio$1alj$1@news.hub.org>
References: <opsteqmfbkawcxfg@adi>
<dabb6g$7k9$1@news.hub.org>
<opstevy1lkawcxfg@adi>
<dabkio$1alj$1@news.hub.org>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: adrian.din@easynet.ro, zaccheob@inwind.it
Subject: Re: Help on Procedure running external function
In-Reply-To: <opstgfj3k4awcxfg@adi>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox