pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Nextval Problems
7+ messages / 4 participants
[nested] [flat]

* Nextval Problems
@ 2002-08-22 00:33 Tom Haddon <tom@betterhealthfoundation.org>
  2002-08-22 05:06 ` Re: Nextval Problems Keary Suska <hierophant@pcisys.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Haddon @ 2002-08-22 00:33 UTC (permalink / raw)
  To: pgsql-php

Hi Folks,

Quick question regarding inserting records into multiple tables that have
key relationships. I have a php page that sets a variable to the nextval
value, adds the record into the first table, and then inserts into the other
tables with the value that was used for the first (foreign keys).

Problem is, I have to add 1 onto the nextval to make this work, and when I
look at the ids that are being added into the tables, they are not
consecutive, one is being missed each time, so for instance I have 1000,
1002, 1004, etc.

Can anyone help? Is this something to do with the way I am connecting to the
database? I build it all the insertion pieces into one query separated by
";", of course. Originally I thought that was the problem, and split them
into separate queries and then ran them one after the other, but no dice. Am
I misunderstanding nextval?

Here's how I get the nextval

// Need to get value of next input id for agency_contact_info field
$strnextval="SELECT nextval('agency_contact_info_id_key');";

// Open Recordset
$q_nextval=pg_exec($dbconn, $strnextval)
	or die("Couldn't open the database --> " . pg_errormessage($q_nextval));

// Need to work out why this has to be '+1'
$nextid=pg_result($q_nextval,0)+1;


And here's me building the query to add in the record in the other tables

// Breast Cancer Resources
$strinsert_breast="INSERT INTO breast_cancer_resources (id) VALUES
($nextid);";


Thanks, much appreciated, Tom

_______________________________
Tom Haddon
IT Director
The Better Health Foundation
414 Thirteenth Street, Suite 450
Oakland, CA 94612
(510) 444-5096
www.betterhealthfoundation.org
_______________________________




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Nextval Problems
  2002-08-22 00:33 Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
@ 2002-08-22 05:06 ` Keary Suska <hierophant@pcisys.net>
  2002-08-22 12:43   ` Re: Nextval Problems Justin Clift <justin@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Keary Suska @ 2002-08-22 05:06 UTC (permalink / raw)
  To: pgsql-php

on 8/21/02 6:33 PM, tom@betterhealthfoundation.org purportedly said:

> Problem is, I have to add 1 onto the nextval to make this work, and when I
> look at the ids that are being added into the tables, they are not
> consecutive, one is being missed each time, so for instance I have 1000,
> 1002, 1004, etc.
> 
> Can anyone help? Is this something to do with the way I am connecting to the
> database? I build it all the insertion pieces into one query separated by
> ";", of course.

That is a really bad idea. Don't do that.

> Originally I thought that was the problem, and split them
> into separate queries and then ran them one after the other, but no dice. Am
> I misunderstanding nextval?

Hard to say. The code below is not all of the code, and insufficient to
determine what is wrong. In any case, nextval will increment the sequence
and return the incremented value. It's good to keep in mind that sequence
manipulations are not effected by transactions, just in case.

Now let's see what we have here.
 
> // Need to get value of next input id for agency_contact_info field
> $strnextval="SELECT nextval('agency_contact_info_id_key');";

The semicolon in the query is at least superfluous if not dangerous. Remove
it. Are you *really* sure you never call this SQL again? *Anywhere*? Are you
*really* sure you are not calling setval, *anywhere*?

> // Open Recordset
> $q_nextval=pg_exec($dbconn, $strnextval)
> or die("Couldn't open the database --> " . pg_errormessage($q_nextval));
> 
> // Need to work out why this has to be '+1'
> $nextid=pg_result($q_nextval,0)+1;

Surprised you're not getting an error here. There is a 3rd missing parameter
to the pg_result command. And this command is deprecated as of 4.2. In any
case, adding 1 to this value should be pointless and dangerous.
> 
> // Breast Cancer Resources
> $strinsert_breast="INSERT INTO breast_cancer_resources (id) VALUES
> ($nextid);";

Looks OK, except you should remove the semicolon.

So--I don't see much wrong. If you make the changes I recommend, and you are
still experiencing the problem, there is still something wrong with your
code. Probably in the parts you don't show us.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Nextval Problems
  2002-08-22 00:33 Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
  2002-08-22 05:06 ` Re: Nextval Problems Keary Suska <hierophant@pcisys.net>
@ 2002-08-22 12:43   ` Justin Clift <justin@postgresql.org>
  2002-08-22 19:58     ` Re: Nextval Problems Simeo Reig <simreig@terra.es>
  0 siblings, 1 reply; 7+ messages in thread

From: Justin Clift @ 2002-08-22 12:43 UTC (permalink / raw)
  To: Keary Suska <hierophant@pcisys.net>; +Cc: pgsql-php

Hi Keary,

Keary Suska wrote:
<snip> 
> So--I don't see much wrong. If you make the changes I recommend, and you are
> still experiencing the problem, there is still something wrong with your
> code. Probably in the parts you don't show us.

Once had a *weird* problem with PG 7.0.? whereby it was incrementing by
2 for every invocation of nextval(), even though I was the only client -
a single connection with psql - and the increment value of the sequence
was definitely 1.

Can't remember how I fixed it for sure, but think it was something along
the lines of dropping the sequence and creating it again (manually). 
Have a feeling that many triggers or constraints turned out to be
involved, but can't remember for sure.

Definitely don't think this is a common occurance though.

Regards and best wishes,

Justin Clift


> Keary Suska
> Esoteritech, Inc.
> "Leveraging Open Source for a better Internet"
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Nextval Problems
  2002-08-22 00:33 Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
  2002-08-22 05:06 ` Re: Nextval Problems Keary Suska <hierophant@pcisys.net>
  2002-08-22 12:43   ` Re: Nextval Problems Justin Clift <justin@postgresql.org>
@ 2002-08-22 19:58     ` Simeo Reig <simreig@terra.es>
  0 siblings, 0 replies; 7+ messages in thread

From: Simeo Reig @ 2002-08-22 19:58 UTC (permalink / raw)
  To: Justin Clift <justin@postgresql.org>; Keary Suska <hierophant@pcisys.net>; +Cc: pgsql-php

curval() or currval() can't remember

Best Regards
Simeo Reig

----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Keary Suska" <hierophant@pcisys.net>
Cc: "Postgres-PHP" <pgsql-php@postgresql.org>
Sent: Thursday, August 22, 2002 2:43 PM
Subject: Re: [PHP] Nextval Problems


> Hi Keary,
>
> Keary Suska wrote:
> <snip>
> > So--I don't see much wrong. If you make the changes I recommend, and you
are
> > still experiencing the problem, there is still something wrong with your
> > code. Probably in the parts you don't show us.
>
> Once had a *weird* problem with PG 7.0.? whereby it was incrementing by
> 2 for every invocation of nextval(), even though I was the only client -
> a single connection with psql - and the increment value of the sequence
> was definitely 1.
>
> Can't remember how I fixed it for sure, but think it was something along
> the lines of dropping the sequence and creating it again (manually).
> Have a feeling that many triggers or constraints turned out to be
> involved, but can't remember for sure.
>
> Definitely don't think this is a common occurance though.
>
> Regards and best wishes,
>
> Justin Clift
>
>
> > Keary Suska
> > Esoteritech, Inc.
> > "Leveraging Open Source for a better Internet"
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> --
> "My grandfather once told me that there are two kinds of people: those
> who work and those who take the credit. He told me to try to be in the
> first group; there was less competition there."
>    - Indira Gandhi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* FW: Nextval Problems
@ 2002-08-22 16:14 Tom Haddon <tom@betterhealthfoundation.org>
  2002-08-22 17:24 ` Re: Nextval Problems Keary Suska <hierophant@pcisys.net>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Haddon @ 2002-08-22 16:14 UTC (permalink / raw)
  To: pgsql-php

Hi Keary,

Thanks for the comments. Could you help me out with a few questions:

Why is "building it all the insertion pieces into one query separated by
";", of course" a bad idea?

Could you explain why "The semicolon in the query is at least superfluous if
not dangerous." - sorry, it's just I thought that was a standard way to
close out a SQL query, and I wonder if you could set me straight there. And
I am definitely not calling setval anywhere else in the code, unless it is
called implicitly by an "INSERT" query.

If it would be helpful and you are interested in spending the time to look
over it, I could send you the file in full to review - I'm relatively new to
php-postgres, and would definitely appreciate another set of eyes - this
applies to anyone else as well.

Thanks, Tom

-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Keary Suska
Sent: Wednesday, August 21, 2002 10:07 PM
To: Postgres-PHP
Subject: Re: [PHP] Nextval Problems


on 8/21/02 6:33 PM, tom@betterhealthfoundation.org purportedly said:

> Problem is, I have to add 1 onto the nextval to make this work, and when I
> look at the ids that are being added into the tables, they are not
> consecutive, one is being missed each time, so for instance I have 1000,
> 1002, 1004, etc.
>
> Can anyone help? Is this something to do with the way I am connecting to
the
> database? I build it all the insertion pieces into one query separated by
> ";", of course.

That is a really bad idea. Don't do that.

> Originally I thought that was the problem, and split them
> into separate queries and then ran them one after the other, but no dice.
Am
> I misunderstanding nextval?

Hard to say. The code below is not all of the code, and insufficient to
determine what is wrong. In any case, nextval will increment the sequence
and return the incremented value. It's good to keep in mind that sequence
manipulations are not effected by transactions, just in case.

Now let's see what we have here.

> // Need to get value of next input id for agency_contact_info field
> $strnextval="SELECT nextval('agency_contact_info_id_key');";

The semicolon in the query is at least superfluous if not dangerous. Remove
it. Are you *really* sure you never call this SQL again? *Anywhere*? Are you
*really* sure you are not calling setval, *anywhere*?

> // Open Recordset
> $q_nextval=pg_exec($dbconn, $strnextval)
> or die("Couldn't open the database --> " . pg_errormessage($q_nextval));
>
> // Need to work out why this has to be '+1'
> $nextid=pg_result($q_nextval,0)+1;

Surprised you're not getting an error here. There is a 3rd missing parameter
to the pg_result command. And this command is deprecated as of 4.2. In any
case, adding 1 to this value should be pointless and dangerous.
>
> // Breast Cancer Resources
> $strinsert_breast="INSERT INTO breast_cancer_resources (id) VALUES
> ($nextid);";

Looks OK, except you should remove the semicolon.

So--I don't see much wrong. If you make the changes I recommend, and you are
still experiencing the problem, there is still something wrong with your
code. Probably in the parts you don't show us.

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Nextval Problems
  2002-08-22 16:14 FW: Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
@ 2002-08-22 17:24 ` Keary Suska <hierophant@pcisys.net>
  2002-08-22 18:09   ` Re: Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
  0 siblings, 1 reply; 7+ messages in thread

From: Keary Suska @ 2002-08-22 17:24 UTC (permalink / raw)
  To: pgsql-php

on 8/22/02 10:14 AM, tom@betterhealthfoundation.org purportedly said:

> Why is "building it all the insertion pieces into one query separated by
> ";", of course" a bad idea?

If for no other reason, it makes code difficult to debug and manage. For
instance, in the case of a failure, there is no way to know which statement
failed unless there is enough context in the error message (which there
wouldn't be if the queries are similar in structure, like a series of INSERT
statements on a single table). Also, unless code is very carefully
maintained you can open security holes. You also have no control over
exception handling, unless every time you do something it is an
all-or-nothing event (i.e., using transactions, rollback is issued upon
*any* exception). Again, it requires more planning and maintenance to
support multiple query calls than to simply issue them singly.

> Could you explain why "The semicolon in the query is at least superfluous if
> not dangerous." - sorry, it's just I thought that was a standard way to
> close out a SQL query, and I wonder if you could set me straight there. And
> I am definitely not calling setval anywhere else in the code, unless it is
> called implicitly by an "INSERT" query.

It is superfluous to the extent that the pg_exec/pg_query commend
automatically issues a "go" instruction (what tells the backend to execute
the query). So what you are doing is the same as doing the following in the
psql shell:
    SELECT * FROM table;;
                       ^^ note double semicolons
So you are actually executing two statements--the select, and then a null
statement. Postgres ignores the null statement, which makes it seem like it
works. But an unexpected bug could cause statements to be executed that you
don't want to, as well as open a security hole.

Again, you have more control.

> If it would be helpful and you are interested in spending the time to look
> over it, I could send you the file in full to review - I'm relatively new to
> php-postgres, and would definitely appreciate another set of eyes - this
> applies to anyone else as well.

You can send it to me personally, if you do not wish the code to be public.
I will take a look, but I can't promise anything ;-) HOWEVER, before you do
that, break out all your queries into individual calls. Otherwise it makes
debugging difficult, and hence harder for me to help, which usually amounts
to not getting the kind of help you need, since, after all, we all have more
important things to do that debug list member's code all day ;-)

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Nextval Problems
  2002-08-22 16:14 FW: Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
  2002-08-22 17:24 ` Re: Nextval Problems Keary Suska <hierophant@pcisys.net>
@ 2002-08-22 18:09   ` Tom Haddon <tom@betterhealthfoundation.org>
  0 siblings, 0 replies; 7+ messages in thread

From: Tom Haddon @ 2002-08-22 18:09 UTC (permalink / raw)
  To: Keary Suska <hierophant@pcisys.net>; pgsql-php

Hi Keary,

Thanks for the insight, and thanks for the offer. I could send the page over
to you, but I think that it may not be necessary. I split the queries up
separately, and used the currval to get the inserted id after it had been
done. Everything seems to be working great, and I'll be removing all the
unnecessary ";"s :)

I'd love to have you look over the code, as I'm definitely in need of some
fine tuning, but as you say, you don't have all day to sit around looking at
list members' code, so unless you'd like to specifically, I'll just say
thanks for the help, and leave it at that.

Tom

-----Original Message-----
From: pgsql-php-owner@postgresql.org
[mailto:pgsql-php-owner@postgresql.org]On Behalf Of Keary Suska
Sent: Thursday, August 22, 2002 10:25 AM
To: Postgres-PHP
Subject: Re: [PHP] Nextval Problems


on 8/22/02 10:14 AM, tom@betterhealthfoundation.org purportedly said:

> Why is "building it all the insertion pieces into one query separated by
> ";", of course" a bad idea?

If for no other reason, it makes code difficult to debug and manage. For
instance, in the case of a failure, there is no way to know which statement
failed unless there is enough context in the error message (which there
wouldn't be if the queries are similar in structure, like a series of INSERT
statements on a single table). Also, unless code is very carefully
maintained you can open security holes. You also have no control over
exception handling, unless every time you do something it is an
all-or-nothing event (i.e., using transactions, rollback is issued upon
*any* exception). Again, it requires more planning and maintenance to
support multiple query calls than to simply issue them singly.

> Could you explain why "The semicolon in the query is at least superfluous
if
> not dangerous." - sorry, it's just I thought that was a standard way to
> close out a SQL query, and I wonder if you could set me straight there.
And
> I am definitely not calling setval anywhere else in the code, unless it is
> called implicitly by an "INSERT" query.

It is superfluous to the extent that the pg_exec/pg_query commend
automatically issues a "go" instruction (what tells the backend to execute
the query). So what you are doing is the same as doing the following in the
psql shell:
    SELECT * FROM table;;
                       ^^ note double semicolons
So you are actually executing two statements--the select, and then a null
statement. Postgres ignores the null statement, which makes it seem like it
works. But an unexpected bug could cause statements to be executed that you
don't want to, as well as open a security hole.

Again, you have more control.

> If it would be helpful and you are interested in spending the time to look
> over it, I could send you the file in full to review - I'm relatively new
to
> php-postgres, and would definitely appreciate another set of eyes - this
> applies to anyone else as well.

You can send it to me personally, if you do not wish the code to be public.
I will take a look, but I can't promise anything ;-) HOWEVER, before you do
that, break out all your queries into individual calls. Otherwise it makes
debugging difficult, and hence harder for me to help, which usually amounts
to not getting the kind of help you need, since, after all, we all have more
important things to do that debug list member's code all day ;-)

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2002-08-22 19:58 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-22 00:33 Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
2002-08-22 05:06 ` Keary Suska <hierophant@pcisys.net>
2002-08-22 12:43   ` Justin Clift <justin@postgresql.org>
2002-08-22 19:58     ` Simeo Reig <simreig@terra.es>
2002-08-22 16:14 FW: Nextval Problems Tom Haddon <tom@betterhealthfoundation.org>
2002-08-22 17:24 ` Keary Suska <hierophant@pcisys.net>
2002-08-22 18:09   ` Tom Haddon <tom@betterhealthfoundation.org>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox