Обсуждение: temp sequence
"PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)" I am creating a temporary sequence in a function and it seems like it is not going away after the function finishes. The front end is in MS Access 2000 and I have a single connection. When I call the function once it works, when I call it a second time, it gives me an error that the sequence already exists. When I restart the application, I can call the function again. I solved the problem by using: alter sequence seq_linenum restart with 1; The manual states: If specified, the sequence object is created only for this session, and is automatically dropped on session exit. Existing permanent sequences with the same name are not visible (in this session) while the temporary sequence exists, unless they are referenced with schema-qualified names. I thought that a function would be considered its own session, is that incorrect? Thank you Sim
On Feb 3, 2008 1:39 AM, Sim Zacks <sim@compulab.co.il> wrote:
> "PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC i386-pc-linux-gnu-gcc
> (GCC) 4.1.1 (Gentoo 4.1.1)"
>
> I am creating a temporary sequence in a function and it seems like it is not
> going away after the function finishes.
> The front end is in MS Access 2000 and I have a single connection. When I call
> the function once it works, when I call it a second time, it gives me an error
> that the sequence already exists. When I restart the application, I can call the
> function again.
>
create, and use the sequence through EXECUTE
EXECUTE 'create temp sequence seq1';
EXECUTE 'select nextval(' || quote_literal('seq') || ') ';
the same apply for all kind of temp objects, in 8.3 this no longer
will be an issue
--
regards,
Jaime Casanova
"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook
On Saturday 02 February 2008 10:39 pm, Sim Zacks wrote: > "PostgreSQL 8.2.4 on i386-pc-linux-gnu, compiled by GCC > i386-pc-linux-gnu-gcc (GCC) 4.1.1 (Gentoo 4.1.1)" > > I am creating a temporary sequence in a function and it seems like it is > not going away after the function finishes. > The front end is in MS Access 2000 and I have a single connection. When I > call the function once it works, when I call it a second time, it gives me > an error that the sequence already exists. When I restart the application, > I can call the function again. > > I solved the problem by using: alter sequence seq_linenum restart with 1; > > The manual states: > If specified, the sequence object is created only for this session, and is > automatically dropped on session exit. Existing permanent sequences with > the same name are not visible (in this session) while the temporary > sequence exists, unless they are referenced with schema-qualified names. > > I thought that a function would be considered its own session, is that > incorrect? The connection is the session. The function is the transaction. If you are going to maintain the connection you will need to drop the sequence inside the function after using it. > > Thank you > Sim > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Adrian Klaver aklaver@comcast.net
On 03/02/2008 06:39, Sim Zacks wrote: > I call the function once it works, when I call it a second time, it > gives me an error that the sequence already exists. When I restart the > application, I can call the function again. Are you by any chance connecting via ODBC with connection pooling? If so, then the pooled connections remain open, which - as someone else has explained - causes sessions to be maintained. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------