(maybe offtopic) mod_perl/DBI/PostgreSQL

[Bruce Tennant <bl...@yahoo.com>]

I just noticed an oddity in my application.  I setup a table in SQL with a serial, not null, primary key column.
 
Then I have a routine that inserts into the table (not setting the key column).
 
If I do the insert from the psql utitilty the sequence stays consistant (inc by 1) w/each new row added.
 
But when I do it from my mod_perl app, it sometimes jumps by two.  I know the sequence will advance even if there's an error, but I'm not getting any error messages back from DBI.
 
Has anyone else run across something like this?  I know this is kind of off topic, but it maybe related to mod_perl's consistent state.
 
Oh, I'm using Apache::DBI, not plain DBI.
 


www.bluewolverine.com

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software

Re: (maybe offtopic) mod_perl/DBI/PostgreSQL

[Bruce Tennant <bl...@yahoo.com>]

Yeah, actually here is the code snippets I use.
 
Table def:
 
CREATE SEQUENCE s_league_lid minvalue 1;
 
CREATE TABLE t_League (
        /*      lid      integer DEFAULT nextval('s_league_lid') PRIMARY KEY,*/
        lid      SERIAL PRIMARY KEY NOT NULL,
        name     VARCHAR(20) UNIQUE NOT NULL,
        numdiv   integer, 
        draft    integer,
        last_wvr integer 
);
 
As you can see above, I've tried the manual sequence method and the new convenience method.
 
in module "new" function:
 
   $self->{sth}->{League_by_lid} =
       $self->{dbh}->prepare("select * from t_League where lid = ?");
   $self->{sth}->{League_by_name} =
       $self->{dbh}->prepare("select * from t_League where name = ?");
    $self->{ith}->{League} =
        $self->{dbh}->prepare("insert into t_League (name,numdiv,draft,last_wvr) VALUES(?,?,?,?);");
 
Then in a member function, called later:
 
call execute on the by_name handle to see if new name already exists
if it doesn't
  call execute on the insert handle with the new row data
then call the select by name handle to get the id that was used to create new row.
 
all of my execute statements have "or die $DBI::errstr;" tacked on the end to catch any errors.
 


greg@mail.netio.org wrote:
Do you have any code that might do a select to determine the value of the
sequence used?

Example:

INSERT blah blah blah;
SELECT currval(sequence_name);

or somethign like that?

If so, the SELECT may be incrementing the counter and that would explain
the jump by 2 your seeing.

There is a function to determine the value of the sequence without
incrementing for postgres, but I don't remember right now what that is.

I actually just did something like this myself. Creating an entry in a
table and then needing to find out what the entry id was. In my case, I
actually ended up just doing a SELECT with a where clause including the
data I just inserted, reverse sorted by record creation date (timestamp
default now() kind of thing, not included in INSERT statement) with a
limit of 1.

I know I could have used the function to get the sequence value, but I
wasn't absolutely sure what would happen. If two processes incremented the
sequence at the same time, what would the function return, the value
for that connection's insert, or the value of the other session?
Especially when I added Apache::DBI to the mix where db connections appear
to be pooled and shared. This way I know I'm getting the value I'm looking
for.

Greg




www.bluewolverine.com

---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software

Re: (maybe offtopic) mod_perl/DBI/PostgreSQL

[gr...@mail.netio.org]

Do you have any code that might do a select to determine the value of the
sequence used?

Example:

INSERT blah blah blah;
SELECT currval(sequence_name);

or somethign like that?

If so, the SELECT may be incrementing the counter and that would explain
the jump by 2 your seeing.

There is a function to determine the value of the sequence without
incrementing for postgres, but I don't remember right now what that is.

I actually just did something like this myself. Creating an entry in a
table and then needing to find out what the entry id was. In my case, I
actually ended up just doing a SELECT with a where clause including the
data I just inserted, reverse sorted by record creation date (timestamp
default now() kind of thing, not included in INSERT statement) with a
limit of 1.

I know I could have used the function to get the sequence value, but I
wasn't absolutely sure what would happen. If two processes incremented the
sequence at the same time, what would the function return, the value
for that connection's insert, or the value of the other session?
Especially when I added Apache::DBI to the mix where db connections appear
to be pooled and shared. This way I know I'm getting the value I'm looking
for.

Greg