Postgres Pro
Downloads
Home   >   mailing lists

Thread: Transaction in plpgslq

Transaction in plpgslq

From
Rafa Couto
Date:
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?



--
Rafa Couto (caligari)
mailto:rafacouto@gmail.com
urgentes (sólo texto): rafa112@amena.com
PGP 0x30EC5C31 [E6BF 11EF FE55 38B1  CF7E 9380 58E5 9FA3]

Re: Transaction in plpgslq

From
Andrew Hammond
Date:
-----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-----

Re: Transaction in plpgslq

From
Rafa Couto
Date:
2005/5/20, Andrew Hammond <ahammond@ca.afilias.info>:

> 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.


I understand "FOR UPDATE" clause is locking while is selecting rows
only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in
next statement. Is not it?


--
Rafa Couto (caligari)

Re: Transaction in plpgslq

From
"Jan B."
Date:
Rafa Couto wrote:
> 2005/5/20, Andrew Hammond <ahammond@ca.afilias.info>:
> 
> 
>>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.
> 
> 
> 
> I understand "FOR UPDATE" clause is locking while is selecting rows
> only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in
> next statement. Is not it?
> 
> 

After the SELECT FOR UPDATE command locked some rows, other concurrent 
changes to the database could be made, but changes, which require to 
lock that rows will be deferred.

The lock will be hold until the end of the transaction (that means at 
least until the function returns).

Re: Transaction in plpgslq

From
Richard Huxton
Date:
Rafa Couto wrote:
> I have got a plpgsql function:

>   -- 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;

> 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 :-(

OK - the WHOLE FUNCTION takes place within one transaction. So, in that 
sense all changes are atomic.

However, if you want to prevent any changes to "contactos" in-between 
those two statements you'll want additional locks. Read the chapter on 
"concurrency control" for details. You might well want SELECT FOR UPDATE  (and also just ORDER BY id LIMIT 1 rather
thanusing min(id)).
 

--   Richard Huxton  Archonet Ltd

Re: Transaction in plpgslq

From
Andrew Hammond
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Rafa Couto wrote:
> I understand "FOR UPDATE" clause is locking while is selecting rows
> only. It does not secure atomic execution from 'SELECT' to 'UPDATE' in
> next statement. Is not it?

Locks adhere until the transaction ends. I included links to relevant
documentation in my original post. If you read up on locking, you'll
find the answer to your problem. Please post any further questions you
have after reading the documentation.

- --
Andrew Hammond    416-673-4138    ahammond@ca.afilias.info
Database Administrator, Afilias Canada Corp.
CB83 2838 4B67 D40F D086 3568 81FC E7E5 27AF 4A9A
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.0 (GNU/Linux)

iD8DBQFCk1INgfzn5SevSpoRAjVlAJ4kGMlTQFaI1BW+9O9GT8He19TyLACcCYtU
Tucg2FuGiDggsAwV7JA2HHs=
=9rus
-----END PGP SIGNATURE-----