Thread: Sequences - problem

Hallo!

I created several tables using a id-sequence for each:
create sequence portid;
create table ports(id integer primary key default nextval('portid'), name
varchar);

Now, since it is a web-application I am working on, I have several
Perl-scripts acting on/with the database.

THe informations-program simply has to read out each sequence in order to
give some statistical data about the database (number of ports, people
...)

As I try a
Pg::doQuery("select currval('portid');", \@ports);
the program gets no reply, on the Postmaster-task (I did not get
postmaster start on startup/background, runs on a task in foreground) I
that message:
ERROR:  regionid.currval is not yet defined in this session
ERROR:  jpid.currval is not yet defined in this session
ERROR:  countri.currval is not yet defined in this session
ERROR:  jprid.currval is not yet defined in this session
ERROR:  portid.currval is not yet defined in this session


What does the problem consist in, and how can I solve it?

In my opinion it is better to read out the counter instead of querying all
elements in a table, so to get the number of elements ...
Pg::doQuery("select * from ports;", \@ports);
Where the number I am looking for is $#ports then ...

I think this method will take a lot of time and is not very effective, as
I do not think I wil ever access one of the entrys read (at least not
while putting some information) ...


Saluti!

Ludwig

> Now, since it is a web-application I am working on, I have several
> Perl-scripts acting on/with the database.
>
> THe informations-program simply has to read out each sequence in order to
> give some statistical data about the database (number of ports, people
> ...)
>
> As I try a
> Pg::doQuery("select currval('portid');", \@ports);
> the program gets no reply, on the Postmaster-task (I did not get
> postmaster start on startup/background, runs on a task in foreground) I
> that message:
> ERROR:  regionid.currval is not yet defined in this session
> ERROR:  jpid.currval is not yet defined in this session
> ERROR:  countri.currval is not yet defined in this session
> ERROR:  jprid.currval is not yet defined in this session
> ERROR:  portid.currval is not yet defined in this session

You cannot call currval on a sequence you have not nextval-ed at least
once in your session.  There was a discussion of the a few weeks (I think)
back.  currval is defined to give you the value of the sequence most
recently given to your session, not the current/highest value of the
sequence especially since that value may never actually go into a table.

In addition, the sequence value is not a good representation of number
of rows anyway, since you may have deleted rows or non-committed rows
(errors, rollback).  If you want number of rows in table, you want
select count(*) from table.

Hallo!

> In addition, the sequence value is not a good representation of number
> of rows anyway, since you may have deleted rows or non-committed rows
> (errors, rollback).  If you want number of rows in table, you want
> select count(*) from table.
thx! Seems to work fine!



Saluti!

Ludwig