pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTransaction in plpgslq
6+ messages / 4 participants
[nested] [flat]
* Transaction in plpgslq
@ 2005-05-20 10:47 Rafa Couto <rafacouto@gmail.com>
0 siblings, 2 replies; 6+ messages in thread
From: Rafa Couto @ 2005-05-20 10:47 UTC (permalink / raw)
To: pgsql-sql
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]
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Transaction in plpgslq
@ 2005-05-20 17:54 Andrew Hammond <ahammond@ca.afilias.info>
parent: Rafa Couto <rafacouto@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Andrew Hammond @ 2005-05-20 17:54 UTC (permalink / raw)
To: Rafa Couto <rafacouto@gmail.com>; +Cc: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Transaction in plpgslq
@ 2005-05-24 10:40 Rafa Couto <rafacouto@gmail.com>
parent: Andrew Hammond <ahammond@ca.afilias.info>
0 siblings, 2 replies; 6+ messages in thread
From: Rafa Couto @ 2005-05-24 10:40 UTC (permalink / raw)
To: Andrew Hammond <ahammond@ca.afilias.info>; +Cc: pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Transaction in plpgslq
@ 2005-05-24 11:00 Jan B. <jan@monso.de>
parent: Rafa Couto <rafacouto@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jan B. @ 2005-05-24 11:00 UTC (permalink / raw)
To: Rafa Couto <rafacouto@gmail.com>; +Cc: Andrew Hammond <ahammond@ca.afilias.info>; pgsql-sql
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).
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Transaction in plpgslq
@ 2005-05-24 11:59 Richard Huxton <dev@archonet.com>
parent: Rafa Couto <rafacouto@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Richard Huxton @ 2005-05-24 11:59 UTC (permalink / raw)
To: Rafa Couto <rafacouto@gmail.com>; +Cc: pgsql-sql
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 than using min(id)).
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Transaction in plpgslq
@ 2005-05-24 16:10 Andrew Hammond <ahammond@ca.afilias.info>
parent: Rafa Couto <rafacouto@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Andrew Hammond @ 2005-05-24 16:10 UTC (permalink / raw)
To: Rafa Couto <rafacouto@gmail.com>; +Cc: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-05-24 16:10 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-20 10:47 Transaction in plpgslq Rafa Couto <rafacouto@gmail.com>
2005-05-20 17:54 ` Andrew Hammond <ahammond@ca.afilias.info>
2005-05-24 10:40 ` Rafa Couto <rafacouto@gmail.com>
2005-05-24 11:00 ` Jan B. <jan@monso.de>
2005-05-24 16:10 ` Andrew Hammond <ahammond@ca.afilias.info>
2005-05-24 11:59 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox