problem with serial

i do have a serial named 'rowid' it is of "SERIAL PRIMARY KEY" in a table
'items'.

if i delete some of the rows i can't anymore insert afterwards.

even if i rearrange de sequence by :

"SELECT MAX(rowid) FROM items;";
"SELECT last_value FROM items_rowid_seq;";
then if last_value is lower than MAX(rowid) i set it to MAX(rowid) :
"SELECT setval('items_rowid_seq', max(rowid)) FROM items;";

and even after that i get an error when inserting by :
"INSERT ... RETURNING rowid;"

here I'm not only unable to read back the rowid but also inserion failed.

why ?
--
Yvon

On 04/19/2012 07:19 AM, Yvon Thoraval wrote:
> i do have a serial named 'rowid' it is of "SERIAL PRIMARY KEY" in a
> table 'items'.
>
> if i delete some of the rows i can't anymore insert afterwards.
>
> even if i rearrange de sequence by :
>
> "SELECT MAX(rowid) FROM items;";
> "SELECT last_value FROM items_rowid_seq;";
> then if last_value is lower than MAX(rowid) i set it to MAX(rowid) :
> "SELECT setval('items_rowid_seq', max(rowid)) FROM items;";
>
> and even after that i get an error when inserting by :
> "INSERT ... RETURNING rowid;"
>
> here I'm not only unable to read back the rowid but also inserion failed.

What does the error say?

>
> why ?
> --
> Yvon
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On 19 April 2012 16:19, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
> why ?

Because you're doing it wrong, apparently. However, since you left out
all the relevant information that could have helped determining what
you're doing and what errors you got, we can't help you.

Please provide the entire insert statement and the errors you got.
--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Fri, Apr 20, 2012 at 12:19 AM, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
> even if i rearrange de sequence by :
>
> "SELECT MAX(rowid) FROM items;";
> "SELECT last_value FROM items_rowid_seq;";
> then if last_value is lower than MAX(rowid) i set it to MAX(rowid) :
> "SELECT setval('items_rowid_seq', max(rowid)) FROM items;";

As a side point, I would recommend against doing this. Once you've
"used" a rowid, it's not worth reusing it. You'll save yourself some
headaches down the track if you simply ignore those odd gaps (ditto
the gaps that result from rolled-back transactions) and just DELETE
the rows you're throwing out without bothering to setval the sequence
back.

ChrisA

2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>

>
> As a side point, I would recommend against doing this. Once you've
> "used" a rowid, it's not worth reusing it. You'll save yourself some
> headaches down the track if you simply ignore those odd gaps (ditto
> the gaps that result from rolled-back transactions) and just DELETE
> the rows you're throwing out without bothering to setval the sequence
> back.
>

Yes, for sure, however, i did this kind of workaround, because one time
I've seen a rowid of 8, after an insertion even if the max(rowid) was of 42.

--
Yvon

On Fri, Apr 20, 2012 at 1:05 AM, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
> 2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>
>> As a side point, I would recommend against doing this. Once you've
>> "used" a rowid, it's not worth reusing it. You'll save yourself some
>> headaches down the track if you simply ignore those odd gaps (ditto
>> the gaps that result from rolled-back transactions) and just DELETE
>> the rows you're throwing out without bothering to setval the sequence
>> back.
>
> Yes, for sure, however, i did this kind of workaround, because one time I've
> seen a rowid of 8, after an insertion even if the max(rowid) was of 42.

If all your inserts make use of the sequence, and you never alter the
sequence, then this should never happen (unless, that is, 34 other
inserts happened between when you inserted and when you checked the
max). Be extremely careful of selecting max(rowid) when you have
concurrent transactions; it's entirely possible that some other
transaction has consumed a value from the sequence but hasn't yet
written it to the database (at least, not in any way that your
transaction can see), which means you risk resetting the sequence too
low.

ChrisA

Am CCing list so more eyes can see this and because I don't use PHP.

On 04/19/2012 07:59 AM, Yvon Thoraval wrote:
>

>
>
> What does the error say?
>
> Fatal error: Call to a member function fetch() on a non-object in
> /home/yt/Sites/landp_public/landp.php on line 500
>
> This is PHP/PDO error message, here is my code :
> $max_rowid=-1;
> $sql="SELECT MAX(rowid) FROM items;";
> $ret=$db->query($sql);
> while($row=$ret->fetch()){
> $max_rowid=$row['max'];
> }
> $last_value=-1;
> $sql="SELECT last_value FROM items_rowid_seq;";
> $ret=$db->query($sql);
> while($row=$ret->fetch()){
> $last_value=$row['last_value'];
> }
> if($last_value<$max_rowid){
> $sql="SELECT setval('items_rowid_seq', max(rowid)) FROM items;";
> $ret=$db->query($sql);
> while($row=$ret->fetch()){
> $last_value=$row['setval'];
> }
> if($last_value<$max_rowid){
> // générer une erreur
> }
> }
> $rowid=-1;
> $sql="INSERT INTO items (ctime, [...], infos) VALUES (
> '".$dat."', [...], '".str2sql(quoteAsAre($_GET["infos"]))."') RETURNING
> rowid;";
> $ret=$db->query($sql);
> while($row=$ret->fetch()){ // LINE 500
> $rowid=$row['rowid'];
> }
>
> if i test it, $ret isn't an object.
>
> may be this has nothing to do with psql but rather with PHP/PDO.
>
> It might even be a typo ?
>
> But this works "sometime" ???

> --
> Yvon
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>

>
> If all your inserts make use of the sequence, and you never alter the
> sequence, then this should never happen (unless, that is, 34 other
> inserts happened between when you inserted and when you checked the
> max). Be extremely careful of selecting max(rowid) when you have
> concurrent transactions; it's entirely possible that some other
> transaction has consumed a value from the sequence but hasn't yet
> written it to the database (at least, not in any way that your
> transaction can see), which means you risk resetting the sequence too
> low.
>
> ChrisA
>

May be it was an artefact because i was using the same database from
command line and thru php ?
because, right now, i do only :
INSERT ... RETURNING rowid;
and it works well from php...
i did quit the command line by "\q" in between...

--
Yvon

On Fri, Apr 20, 2012 at 1:20 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> Am CCing list so more eyes can see this and because I don't use PHP.
>
> On 04/19/2012 07:59 AM, Yvon Thoraval wrote:
>> Fatal error: Call to a member function fetch() on a non-object in
>> /home/yt/Sites/landp_public/landp.php on line 500
>>
>> This is PHP/PDO error message, here is my code :
>> $max_rowid=-1;
>> $sql="SELECT MAX(rowid) FROM items;";
>> $ret=$db->query($sql);
>> while($row=$ret->fetch()){
>> $max_rowid=$row['max'];
>> }

Side point: This is a rather clunky way of handling single-row
results. There's really no need to have a loop like this when you know
the query can't return more than one row - and can't return less than
one row, either (it might return NULL, but it will always have one
row).

The error you're seeing is a cascaded one stemming from $db->query()
returning something that you can't fetch from - off-hand, I'm thinking
it returns either False or Null, but I haven't checked the docs. More
than likely, it's an SQL error in the statement immediately above line
500:

$sql="INSERT INTO items (ctime, [...], infos) VALUES (
'".$dat."', [...], '".str2sql(quoteAsAre($_GET["infos"]))."') RETURNING
rowid;";

Try echoing this statement, then copying and pasting it to
command-line Postgres. Also, get a display of the actual error
returned (I don't have my PHP docs handy to see how that is, but it'll
be $db->last_error or somesuch).

Are you able to switch to using a parameterized query instead of all
this fiddly quoting and escaping? It's a LOT safer and easier.
Possibly you have an issue with the escaping and that's why you
occasionally get SQL errors. What are str2sql() and quoteAsAre()
doing?

ChrisA

2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>

> On Fri, Apr 20, 2012 at 1:20 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>
> wrote:
> > Am CCing list so more eyes can see this and because I don't use PHP.
> >
> > On 04/19/2012 07:59 AM, Yvon Thoraval wrote:
> >> Fatal error: Call to a member function fetch() on a non-object in
> >> /home/yt/Sites/landp_public/landp.php on line 500
> >>
> >> This is PHP/PDO error message, here is my code :
> >> $max_rowid=-1;
> >> $sql="SELECT MAX(rowid) FROM items;";
> >> $ret=$db->query($sql);
> >> while($row=$ret->fetch()){
> >> $max_rowid=$row['max'];
> >> }
>
> Side point: This is a rather clunky way of handling single-row
> results. There's really no need to have a loop like this when you know
> the query can't return more than one row - and can't return less than
> one row, either (it might return NULL, but it will always have one
> row).
>
> The error you're seeing is a cascaded one stemming from $db->query()
> returning something that you can't fetch from - off-hand, I'm thinking
> it returns either False or Null, but I haven't checked the docs. More
> than likely, it's an SQL error in the statement immediately above line
> 500:
>
> $sql="INSERT INTO items (ctime, [...], infos) VALUES (
> '".$dat."', [...], '".str2sql(quoteAsAre($_GET["infos"]))."') RETURNING
> rowid;";
>
> Try echoing this statement, then copying and pasting it to
> command-line Postgres. Also, get a display of the actual error
> returned (I don't have my PHP docs handy to see how that is, but it'll
> be $db->last_error or somesuch).
>
> Are you able to switch to using a parameterized query instead of all
> this fiddly quoting and escaping? It's a LOT safer and easier.
> Possibly you have an issue with the escaping and that's why you
> occasionally get SQL errors. What are str2sql() and quoteAsAre()
> doing?
>
> ChrisA
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

function quoteAsAre($string){
return preg_replace('/\\\"/', '"', preg_replace("/\\\'/", "'",
$string));
}
function sql2str($string){
return preg_replace('/\'\'/', "'", $string);
}
function str2sql($string){
return preg_replace('/\'/', "''", $string);
}
function nospace($string){
if($string==''){
$string="&nbsp;";
}
return $string;
}

OK, i'll carrefully inspect all of those.

--
Yvon

On Fri, Apr 20, 2012 at 2:03 AM, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
>   function quoteAsAre($string){
>     return preg_replace('/\\\"/', '"', preg_replace("/\\\'/", "'",
> $string));
>   }
>   function sql2str($string){
>     return preg_replace('/\'\'/', "'", $string);
>   }
>   function str2sql($string){
>     return preg_replace('/\'/', "''", $string);
>   }
>
> OK, i'll carrefully inspect all of those.

No; ditch them. I mean no offense to you personally, but these
functions are not worth keeping. Every SQL API includes a function for
quoting something as a literal string. With PDO, it's this one:

http://www.php.net/manual/en/pdo.quote.php

I don't know where you would be using sql2str, but it's just as
dangerous as the others (not to mention inefficient, there's no need
to use regular expressions for simple string replacement). Replace all
your calls to any of these functions with standard quoting functions
and see if your problem disappears. If not, well, it's still not been
a fruitless exercise, because now you are relying for safety and
security on something that the database engine promises is correct :)

Chris Angelico

2012/4/19 Chris Angelico <rosuav(at)gmail(dot)com>

>
> No; ditch them. I mean no offense to you personally, but these
> functions are not worth keeping. Every SQL API includes a function for
> quoting something as a literal string. With PDO, it's this one:
>
> http://www.php.net/manual/en/pdo.quote.php
>
> I don't know where you would be using sql2str, but it's just as
> dangerous as the others (not to mention inefficient, there's no need
> to use regular expressions for simple string replacement). Replace all
> your calls to any of these functions with standard quoting functions
> and see if your problem disappears. If not, well, it's still not been
> a fruitless exercise, because now you are relying for safety and
> security on something that the database engine promises is correct :)
>

OK, OK, this comes from an old workaround with SQLite3...

I put the query into a try / catch like that :
$sql="INSERT INTO categories (idx, ctime, mtime, name) VALUES (
$idx, '$dat', '$dat', '".str2sql(quoteAsAre($name))."' ) RETURNING rowid;";
$xml.="<sql>$sql</sql>";
try {
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$ret=$db->query($sql);
$row=$ret->fetch();
$rowid=$row['rowid'];
$xml.="<rowid>$rowid</rowid>";
} catch (PDOException $e) {
$xml.="<PDOException>".$e->getMessage()."</PDOException>";
}

with that i can read the sql and the error.

I'l follow your advice about quoting, ASAP )))

--
Yvon

On 19/04/2012 17:12, Chris Angelico wrote:
> On Fri, Apr 20, 2012 at 2:03 AM, Yvon Thoraval <yvon(dot)thoraval(at)gmail(dot)com> wrote:
>> function quoteAsAre($string){
>> return preg_replace('/\\\"/', '"', preg_replace("/\\\'/", "'",
>> $string));
>> }
>> function sql2str($string){
>> return preg_replace('/\'\'/', "'", $string);
>> }
>> function str2sql($string){
>> return preg_replace('/\'/', "''", $string);
>> }
>>
>> OK, i'll carrefully inspect all of those.
>
> No; ditch them. I mean no offense to you personally, but these
> functions are not worth keeping. Every SQL API includes a function for
> quoting something as a literal string. With PDO, it's this one:
>
> http://www.php.net/manual/en/pdo.quote.php
>
> I don't know where you would be using sql2str, but it's just as
> dangerous as the others (not to mention inefficient, there's no need
> to use regular expressions for simple string replacement). Replace all
> your calls to any of these functions with standard quoting functions
> and see if your problem disappears. If not, well, it's still not been

Or better still - and easier - use parametrised queries.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

2012/4/19 Raymond O'Donnell <rod(at)iol(dot)ie>

> Or better still - and easier - use parametrised queries.
>

Right it is easier something like :
$sql = "categories (idx, ctime, mtime, name) VALUES ( :idx, :ctime, :mtime,
:name);";
$prep = $db->prepare($sql);
$prep->execute( array(':idx' => $_GET['idx'], ':ctime' => $ctime, ':mtime'
=> $mtime, ':name' => $name));

no more need to $db->quote() in that case, as :
$name = $db->quote($name);
???
I mean, even if $name = "L'envers" ? (ie. with a ' in it ?)
--
Yvon

On 19/04/2012 20:30, Yvon Thoraval wrote:
>
>
> 2012/4/19 Raymond O'Donnell <rod(at)iol(dot)ie <mailto:rod(at)iol(dot)ie>>
>
> Or better still - and easier - use parametrised queries.
>
>
>
> Right it is easier something like :
> $sql = "categories (idx, ctime, mtime, name) VALUES ( :idx, :ctime,
> :mtime, :name);";
> $prep = $db->prepare($sql);
> $prep->execute( array(':idx' => $_GET['idx'], ':ctime' => $ctime,
> ':mtime' => $mtime, ':name' => $name));
>
> no more need to $db->quote() in that case, as :
> $name = $db->quote($name);
> ???
> I mean, even if $name = "L'envers" ? (ie. with a ' in it ?)

Yep - no need to worry about quoting if you use parameters - it's all
done for you. It's also MUCH safer, as it makes SQL injection attacks
much harder (if not impossible).

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie

2012/4/19 Raymond O'Donnell <rod(at)iol(dot)ie>

>
>
> Yep - no need to worry about quoting if you use parameters - it's all
> done for you. It's also MUCH safer, as it makes SQL injection attacks
> much harder (if not impossible).
>
> Ray.
>
>
fine, thanks !

--
Yvon

On Fri, Apr 20, 2012 at 5:33 AM, Raymond O'Donnell <rod(at)iol(dot)ie> wrote:
> Yep - no need to worry about quoting if you use parameters - it's all
> done for you. It's also MUCH safer, as it makes SQL injection attacks
> much harder (if not impossible).

And in some cases, it can even be more bandwidth-efficient. I don't
know if PDO can take advantage of this, but with the
PostgreSQL-specific functions (pg_query_params etc), an alternative
protocol method is used that sends the query and its parameters
separately, to great efficiency.

ChrisA