Thread: I have problem with Postgres.

I have problem with Postgres.

From
Попов Андрей
Date:
Hi.
I have problem with Postgres.
I have perl script, that add 2 records in 2 tables. This script I run
from web throught web server Apache (on FreeBSD).
(perl version 5.005)

Tables structure:
create table object_list
( obj_id serial primary key, obj_name varchar(100)
);

create table texts
( obj_id int4, text_title varchar(100),  text_content oid
);

Algorithm:
1. Put one record in table object_list.
2. Get last id record from table object_list.
3. Put one record in table texts, where obj_id field is id from 2 step.

Problem:
Sometimes, I get next result - in table object_list inserting 2 record
and in table texts inserting 1 record.
What is happened?
Thanks for listening!

Best Regards, Andrey.



Script code:
#!/usr/bin/perl

use DBI;
use DBD::Pg;

$dbh =
DBI->connect("dbi:Pg:dbname=triz;host=localhost;port=6565","postgres",""
, {PrintError=>1, RaiseError=>1, AutoCommit=>0});
if (!$dbh)
{ print ("Error: $DBI::errstr\n"); exit
}

$obj_name = 'object_name';
$text_title = 'text_title';
$text_content = 'text_content';

create_text ($obj_name, $text_title, $text_content);

$dbh->commit;
$dbh->disconnect;


sub create_text
{ my ($obj_name, $text_title, $text_content) = @_; my ($obj_id); my $tempdir = '/tmp/';
 eval {   $sth = $dbh->prepare("insert into object_list (obj_name) values
(?)");   $sth->execute($obj_name);
   $sth = $dbh->prepare("select max(obj_id) from object_list");   $sth->execute();   $max = $sth->fetchrow;
if($max>0)  {     $obj_id=$max;   }   else   {     $dbh->rollback;     exit;   } 
   $tempstr=$tempdir."text".$$.(time).".tmp";
   open (TEMPF, ">".$tempstr);   print TEMPF $config{'text_content'};   close (TEMPF);
   $sth=$dbh->prepare("insert into texts (obj_id, text_title,
text_content) values ( ?, ?, lo_import('".$tempstr."'))");   $sth->execute($obj_id, $text_title);
   unlink $tempstr;
   $sth->finish; }; if ($@) {   $dbh->rollback;   exit; }
}



Re: I have problem with Postgres.

From
Rod Taylor
Date:
On Thu, 2002-11-14 at 03:40, =F0=CF=D0=CF=D7 =E1=CE=C4=D2=C5=CA wrote:
> Hi.
> I have problem with Postgres.
> I have perl script, that add 2 records in 2 tables. This script I run
> from web throught web server Apache (on FreeBSD).
> (perl version 5.005)

You would be better off using the sequence rather than trying to fetch
out max(obj_id).


select currval('object_list_obj_id_seq') AS nextvalue;

after the insert into object_list.

>=20
>     $sth =3D $dbh->prepare("select max(obj_id) from object_list");
>     $sth->execute();
>     $max =3D $sth->fetchrow;

--=20
Rod Taylor <rbt@rbt.ca>