From: scott.marlowe <scott.marlowe@ihs.com>
To: Ray Hunter <rhunter@venticon.com>
Cc: pgsql-php@postgresql.org
Subject: Re: serial type question
Date: Thu, 14 Nov 2002 12:35:12 -0700 (MST)
Message-ID: <Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com> (raw)
In-Reply-To: <1037217368.6700.113.camel@pclnxrhunter.digitalglobe.com>
On 13 Nov 2002, Ray Hunter wrote:
> I have a php script that will be inserting from a form into two tables.
>
> Table1 has a serial data type.
> Table2 references Table1's serial data type (PK).
>
> Here are my questions:
> 1. Can i run a transaction process from php? If yes, how?
Yes. And no.
Yes, you can run a transaction. Only requirement is that it cannot span
multiple PHP pages. I.e. you can't start a transaction on one page and
finish it on another. pg_connect drops the connection, aborting the
transaction, and pg_pconnect cannot GUARANTEE that you will get the same
connection the next time you run a page, as a different child process may
answer your request than the one you had on the first page. Making
matters worse, on a low load test box, multiple page transactions will
probably work fine, but on a heavily loaded box you may get the problem
that your child process doesn't stay the same from one page to the next.
If you run the transaction on a single PHP page it should work fine. All
you need to do is:
pg_exec($connection,"begin");
LOTSA PHP CODE TO DO THE WORK GOES HERE
pg_exec($connection,"commit");
You might wanna add some error checking to the above...
> 2. How can i get the serial value for inserting into the 2nd table?
You can use currval('seqname') AFTER the insert to the parent table to
find out what the inserted value was. currval is transaction aware, and
will not give you the REAL current value if some other transaction just
bumped it up right after you did.
Use it something like this:
begin;
insert into parent (field1, field2) values (value1,value2);
select currval('sequsedbyparent');
insert into child (fieldx, fieldy, fk2parent) values
(valuex,valuey,'valfromcurrval');
repeat above for each child table
commit;
That should do it. Again, add some error checking to this example for
production use.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-php@postgresql.org
Cc: scott.marlowe@ihs.com, rhunter@venticon.com
Subject: Re: serial type question
In-Reply-To: <Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com>
References: <1037217368.6700.113.camel@pclnxrhunter.digitalglobe.com> <Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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