Re: Problem compiling function with BEGIN WORK; COMMIT WORK;

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Andre Lopes <lopes80andre(at)gmail(dot)com>
Cc: postgresql Forums <pgsql-general(at)postgresql(dot)org>
Subject: Re: Problem compiling function with BEGIN WORK; COMMIT WORK;
Date: 2010-04-25 02:41:11
Message-ID: 4BD3ABC7.9010206@postnewspapers.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 25/04/2010 9:07 AM, Andre Lopes wrote:
> Hi,
>
> I need to use the BEGIN WORK; and COMMIT WORK; to lock a table when I'am
> doing a SELECT and UPDATE operation.

PostgreSQL's server-side functions do *not* support transaction
management. They're functions that're used inside an existing transaction.

However, if you do not explcitly BEGIN a transaction before calling your
function, the statement your function runs in will start and stop its
own transaction. In other words, these two things are equivalent:

BEGIN;
SELECT my_function();
COMMIT;

and

SELECT my_function();

(outside an existing transaction)

Because your function is *always* inside a transaction, it can always
acquire locks and the like. It doesn't need to explicitly start a
transaction first.

--
Craig Ringer

In response to

Browse pgsql-general by date

  From Date Subject
Next Message John Gage 2010-04-25 05:09:05 Re: ISP provider with postgres and perl dbi
Previous Message Bruce Momjian 2010-04-25 02:11:51 Re: how to set CACHEDEBUG ?