Postgres Pro
Downloads
Home   >   mailing lists

Thread: temp sequence

temp sequence

From
Sim Zacks
Date:
"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

Re: temp sequence

From
"Jaime Casanova"
Date:
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

Re: temp sequence

From
Adrian Klaver
Date:
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

Re: temp sequence

From
Raymond O'Donnell
Date:
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
---------------------------------------------------------------