Re: PostgreSQL/PHP: transactions: how-to abstract out?

So let me get straight the behavior that you want. You want all of the
queries executed in a single request to automatically be included in one
transaction that is commited at the end of the script? You don't ever want
to have to decide when things should and shouldn't be grouped together in a
transaction?

I don't think using persistent connections has anything to do with this
problem (someone correct me if I'm wrong) but what are the "inherent
dangers" you see in using them (I'm just curious).

thanks,

Rick

----- Original Message -----
From: "Jean-Christian Imbeault" <jc(at)mega-bucks(dot)co(dot)jp>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, January 09, 2003 10:05 PM
Subject: [GENERAL] PostgreSQL/PHP: transactions: how-to abstract out?

> I posted this on the PHP-general list since it is more of a programming
> techniques question that a PostgreSQL question but I thought I would ask
> it here also since it has to do with abstracting out the DB layer and I
> get the feeling more people on this list have faced this kind of issue
> before and have thought about it the issues more in depth.
>
> The problem:
>
> I tough I had abstracted out the SQL querying part of my code out, just
> to find out today that it doesn't work when it comes to transactions. I
> had come up with this code:
>
> function sql_query($sql) {
> $conn = pg_connect("dbname=JC user=postgres");
> $res = pg_exec($conn, $sql);
> if (!$res) {
> echo "CONNECTION: could not execute query ($sql)";
> die;
> }
> else return $res;
> }
>
> I had transactions in my code implemented like this:
>
> $sql = "BEGIN";
> sql_query($sql);
> [some sql that should be in a transaction ...]
> $sql = "COMMIT";
> sql_query($sql);
>
> This doesn't work. Now that I look at my code I can clearly see why. All
> sql queries are executed using a new Postgres connection, hence the use
> of BEGIN/COMMIT as I was using them have no effect.
>
> Can someone recommend a way to abstract out my DB layer while still
> being able to use transactions? (persistent connections are not an
> option since there are too many inherent dangers).
>
> I was thinking of using the same function but if the incoming query
> contained the word BEGIN, saving that and all future queries in a
> session var and when the COMMIT comes in executing all the saved queries
> as one (i.e. "BEGIN;[....];COMMIT"). One drawback is that all queries
> will be written out to disk (as session vars) and cause a performance hit.
>
> I was also thinking about maybe the $sql a GLOBAL or first building up
> my query as as long string ("BEGIN;[....];COMMIT") and *then* sending it
> to my sql_query() function.
>
> The last two seem easier to implement, safer, and more efficient but
> they don't seem "elegant" because I haven't really abstracted out the
> fact that I want a transaction. Whenever I write an SQL query I have to
> think "does this need to be in a transaction" and then use a different
> coding technique depending on the answer. And if the future something
> that didn't need to be in a transaction now needs to be in a transaction
> I have to revisit my code and change the code.
>
> I'm sure someone out there must have thought about this and come up with
> an elegant solution and way of abstracting out the DB layer from PHP.
>
> Can anyone share their solution with me or give me some pointers to
> reference material?
>
> Thanks,
>
> Jc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>