Message-ID: <428E245B.4050204@ca.afilias.info>
Date: Fri, 20 May 2005 13:54:35 -0400
From: Andrew Hammond <ahammond@ca.afilias.info>
Organization: Afilias Canada Corp.
User-Agent: Debian Thunderbird 1.0.2 (X11/20050331)
MIME-Version: 1.0
To: Rafa Couto <rafacouto@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Transaction in plpgslq
References: <22df564b05052003479481da1@mail.gmail.com>
In-Reply-To: <22df564b05052003479481da1@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The manual is correct, you can not do transactions within a procedure
since the procedure must be called within a transaction. If you're
working with postgres 8, you can achieve similar functionality using
checkpoints. But that won't solve the problem you have below.

The solution to your problem is locking (or concurrency control if you
prefer). While we're at it, we might as well optimize your statement a
little too using ORDER BY with LIMIT instead of min().

SELECT id INTO _contacto_id
FROM contactos
WHERE contactos.operadora_id IS NULL
  AND contactos.actividad_id > = _actividad_id
ORDER BY id LIMIT 1
FOR UPDATE;

Take a look at the "FOR UPDATE" section of the SELECT description for an
explanation of how this works.

http://www.postgresql.org/docs/8.0/static/sql-select.html

If you still have questions, then you might want to take a look at the
concurrency control section of the manual.

http://www.postgresql.org/docs/8.0/static/mvcc.html

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A


Rafa Couto wrote:
> I have got a plpgsql function:
> 
> CREATE FUNCTION nueva_llamada(integer, integer) RETURNS integer
> 
> as
> 
> DECLARE
>   _operadora_id ALIAS FOR $1;
>   _actividad_id ALIAS FOR $2;
>   _contacto_id integer;
> 
> BEGIN
> 
>   -- BEGIN;
> 
>     SELECT min(id) INTO _contacto_id FROM contactos 
>       WHERE contactos.operadora_id IS NULL AND contactos.actividad_id
> = _actividad_id;
> 
>     UPDATE contactos SET operadora_id = _operadora_id WHERE id = _contacto_id;
> 
>   -- COMMIT;
> 
>   INSERT INTO llamadas (contacto_id, operadora_id, fecha) 
>     VALUES (_contacto_id, _operadora_id, now());
> 
>   RETURN _contacto_id;
> END
> 
> and it works right, but I need atomic execution from --BEGIN and
> --COMMIT, and manual says it is not possible to have transactions in
> PL/pgSQL procedures :-(
> 
> May be with LOCK TABLE?
> 
> 
> 
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCjiRXgfzn5SevSpoRAlZRAJ4pg7UohNBy+RhgoOfbqy0W9wbIXQCff6F1
VEPjPfo4tSxn+kMg6snBbSI=
=bzri
-----END PGP SIGNATURE-----