Re: serial type question

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.