Thread: Complex transactions without using plPgSQL Functions. It is possible?
Hi,
I'm writing a web application that uses PostgreSQL and I need to do
some operations where I read/write to 3 tables in the same
transaction. To do this I need to store the values of variables and
I'm not sure if it is possible to do this without using plPgSQL.
[code]
SELECT count(email) INTO vCONTA_HIST FROM am_newsletter_hist_alter
WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA;
IF vCONTA_HIST = 0 THEN
vNUM_ALTER := 1;
ELSE
SELECT MAX(num_alter) INTO vNUM_ALTER_ACT FROM am_newsletter_hist_alter
WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA LIMIT 1;
vNUM_ALTER := vNUM_ALTER_ACT + 1;
END IF;
[/code]
This is the plPgSQL code that I need to write in Python. It is
possible to do this without using PlPgSQL?
Best Regards,
On 03/06/2012 11:30 AM, Andre Lopes wrote: > Hi, > > I'm writing a web application that uses PostgreSQL and I need to do > some operations where I read/write to 3 tables in the same > transaction. To do this I need to store the values of variables and > I'm not sure if it is possible to do this without using plPgSQL. > > [code] > SELECT count(email) INTO vCONTA_HIST FROM am_newsletter_hist_alter > WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA; > > IF vCONTA_HIST = 0 THEN > vNUM_ALTER := 1; > ELSE > SELECT MAX(num_alter) INTO vNUM_ALTER_ACT FROM am_newsletter_hist_alter > WHERE email = pEMAIL AND id_website_recolha = pID_WEBSITE_RECOLHA LIMIT 1; > vNUM_ALTER := vNUM_ALTER_ACT + 1; > END IF; > [/code] > > This is the plPgSQL code that I need to write in Python. It is > possible to do this without using PlPgSQL? Sure: http://www.postgresql.org/docs/9.0/interactive/plpython-database.html > > Best Regards, > -- Adrian Klaver adrian.klaver@gmail.com
On Wed, Mar 7, 2012 at 6:30 AM, Andre Lopes <lopes80andre@gmail.com> wrote: > I'm writing a web application that uses PostgreSQL and I need to do > some operations where I read/write to 3 tables in the same > transaction. Is what you're looking for simply the "begin transaction"[1] and "commit"[2] commands? With those, you can write your code to do whatever it likes, and it'll still be all one transaction. With some caveats, of course, but if all you're doing is INSERT / DELETE / UPDATE, you'll be fully protected by the transaction boundaries (eg if your script dies unexpectedly in the middle, the whole thing will be rolled back, all those usual safeties). As a side point: Is it possible to disable Postgres's default autocommit behavior and have it automatically open a transaction on connection and after commit/rollback? That's what I grew up on with DB2 - you do some work, then you commit, then you do more work, then you commit, but never have to say "and begin a transaction too". Chris Angelico [1] http://www.postgresql.org/docs/current/static/sql-begin.html [2] http://www.postgresql.org/docs/current/static/sql-commit.html
Chris Angelico <rosuav@gmail.com> writes:
> As a side point: Is it possible to disable Postgres's default
> autocommit behavior and have it automatically open a transaction on
> connection and after commit/rollback? That's what I grew up on with
> DB2 - you do some work, then you commit, then you do more work, then
> you commit, but never have to say "and begin a transaction too".
In psql, see "\set AUTOCOMMIT off". In other frontends, it would depend
on the client-side code whether or how you can do that.
(We once made an attempt to provide this sort of behavioral option on
the server side; but it was a complete disaster from the client
compatibility standpoint, and was soon, um, rolled back.)
regards, tom lane
On Wed, Mar 7, 2012 at 9:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > In psql, see "\set AUTOCOMMIT off". In other frontends, it would depend > on the client-side code whether or how you can do that. > > (We once made an attempt to provide this sort of behavioral option on > the server side; but it was a complete disaster from the client > compatibility standpoint, and was soon, um, rolled back.) I'm talking about using the application-level protocols (eg the pg_* functions in PHP), so it's your second option. And yeah, I see your point. Major compat issues. Well, at least the libpqxx library can solve that one! ChrisA
Re: Complex transactions without using plPgSQL Functions. It is possible?
From
Sergey Konoplev
Date:
On Tue, Mar 6, 2012 at 11:30 PM, Andre Lopes <lopes80andre@gmail.com> wrote: > This is the plPgSQL code that I need to write in Python. It is > possible to do this without using PlPgSQL? Do you want it to be done using pure SQL or you do not want to wrap it into a stored function? In the second case look at the DO command http://www.postgresql.org/docs/9.1/static/sql-do.html -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com LinkedIn: http://ru.linkedin.com/in/grayhemp JID/GTalk: gray.ru@gmail.com Skype: gray-hemp
Re: Complex transactions without using plPgSQL Functions. It is possible?
From
Martin Gregorie
Date:
On Wed, 2012-03-07 at 14:19 +0400, Sergey Konoplev wrote: > On Tue, Mar 6, 2012 at 11:30 PM, Andre Lopes <lopes80andre@gmail.com> wrote: > > This is the plPgSQL code that I need to write in Python. It is > > possible to do this without using PlPgSQL? > Have you looked at pyodbc? ODBC will usually accept statements allowing you to turn autocommit off and to use connection.commit() to group a set of statements into a transaction. Note that there's a documentation comment saying that autocommit settings are not passed to the driver, However, as the documentation is still talking about bytea fields it may be out of date so I'd suggest running a test. Try writing a Python test program that turns autocommit off and does a couple of inserts followed by a rollback. File a bug if the inserted data is in the table after a successful run. Martin