pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.




view thread (3+ messages)

Message-ID: <Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com>
Permalink:  ../Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com/
Also on:    postgresql.org/message-id/Pine.LNX.4.33.0211141229270.1891-100000@css120.ihs.com

 · 

reply

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