That's what I thought. Thank you.
regards
mk
2013/5/13 Adrian Klaver
> On 05/13/2013 02:22 AM, Marcin Krawczyk wrote:
>
>> Hi list,
>>
>> I have some problems with SERIALIZABLE isolation level, namely my users
>> are plagued with concurrency errors. As of postgres 9.1 (which I'm
>> running) t
On 05/13/2013 02:22 AM, Marcin Krawczyk wrote:
Hi list,
I have some problems with SERIALIZABLE isolation level, namely my users
are plagued with concurrency errors. As of postgres 9.1 (which I'm
running) there has been a change to SERIALIZABLE logic, unfortunately my
application has not been upd
Florian Weimer writes:
> hstore greatly simplifies creating triggers for logging table changes,
> which is great. However, when creating a log record, I would like to
> include information about the party who made this change. We
> generally do not allow direct database access for application co
D'Arcy J.M. Cain wrote:
On Fri, 18 Jan 2008 12:16:04 -0300
Gerardo Herzig <[EMAIL PROTECTED]> wrote:
Right. But today, that trigger do some other work, wich includes
writing some files to disk, so there is my problem. Crap, i guess i will
have to review the main logic.
I built a re
On Fri, 18 Jan 2008 12:16:04 -0300
Gerardo Herzig <[EMAIL PROTECTED]> wrote:
> Right. But today, that trigger do some other work, wich includes
> writing some files to disk, so there is my problem. Crap, i guess i will
> have to review the main logic.
I built a replication system that syncs up
Alvaro Herrera wrote:
Gerardo Herzig escribió:
Right. But today, that trigger do some other work, wich includes writing
some files to disk, so there is my problem. Crap, i guess i will have to
review the main logic.
Probably it's better to move the actual file writing to a listene
Gerardo Herzig escribió:
> Right. But today, that trigger do some other work, wich includes writing
> some files to disk, so there is my problem. Crap, i guess i will have to
> review the main logic.
Probably it's better to move the actual file writing to a listener
external process -- the tr
Filip Rembiałkowski wrote:
2008/1/18, Gerardo Herzig <[EMAIL PROTECTED]>:
Hi all. Im puzzled again. Just thinking:
As im having fun trying to make my own replication system, im stuck in
this situation:
Consider a simple table with a unique index on the `id' field, and a
function who will fa
2008/1/18, Gerardo Herzig <[EMAIL PROTECTED]>:
> Hi all. Im puzzled again. Just thinking:
>
> As im having fun trying to make my own replication system, im stuck in
> this situation:
> Consider a simple table with a unique index on the `id' field, and a
> function who will fail, such as
>
> insert
Any function runs into a single transaction so you don't need to worry
about rolling back on exceptions - this is the standard behavior. If there
is an exception while running any statement inside the function it's
rolls back automatically.
On Tue, 05 Dec 2006 17:25:31 +0200, Marian POPE
Marian POPESCU <[EMAIL PROTECTED]> writes:
> I want to write a function that updates several tables; if there is an
> exception while updating, it should rollback the transaction and raise an
> error.
> How can this be done in pgPLSQL in PostgreSQL 8.1 ?
You just do it. You would have to work at
am Tue, dem 05.12.2006, um 16:25:31 +0100 mailte Marian POPESCU folgendes:
> Hi,
>
> I want to write a function that updates several tables; if there is an
> exception while updating, it should rollback the transaction and raise an
> error.
>
> How can this be done in pgPLSQL in PostgreSQL 8.1 ?
-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
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;
-- COMMI
Rafa Couto wrote:
2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>:
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
FRO
2005/5/20, Andrew Hammond <[EMAIL PROTECTED]>:
> 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 contacto
-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
Wolff III <[EMAIL PROTECTED]>
To: H.J. Sanders <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, April 21, 2004 7:30 AM
Subject: Re: [SQL] transaction
> On Tue, Apr 20, 2004 at 21:14:48 +0200,
> "H.J. Sanders" <[EMAIL PROTECTED]> wrote:
> >
On Wed, Apr 21, 2004 at 12:58:56 +0530,
[EMAIL PROTECTED] wrote:
>
> The code looks like:
>
> update tempxitag set qty = qty + nqty where
> ccod = cccod
> GET DIAGNOSTICS nFound = ROW_COUNT;
> If nFound = 0 then
> insert into tempxitag( ccod,
On Tue, Apr 20, 2004 at 09:14:48PM +0200, H.J. Sanders wrote:
>
> - BEGIN WORK
>
> - INSERT ROW
>
> - IF FAILED THEN UPDATE ROW
>
> - COMMIT WORK
You can do it the other way. Begin, update; if 0 rows are updated
then insert.
A
--
Andrew Sullivan | [EMAIL PROTECTED]
---
On Tue, Apr 20, 2004 at 21:14:48 +0200,
"H.J. Sanders" <[EMAIL PROTECTED]> wrote:
>
> Hello list.
>
> We are migrating from Informix to PSQL.
>
> In Informix we used to do:
>
> - BEGIN WORK
>
> - INSERT ROW
>
> - IF FAILED THEN UPDATE ROW
>
> - COMMIT WORK
>
>
> In PSQL this does not se
Holger Jakobs wrote:
Calling a procedure is a statement as well, and it includes all other
procedures called from this procedure. So the statement level is always
the statements that were carried out directly in the transaction. If
anything within one statement fails, the statement was not carried
On 11 Nov, Jan Wieck wrote:
> As long as we talk in an SQL context, can you please stick to SQL
> terms? I don't know exactly what you mean with "operation". If for
> example the statement
>
> DELETE FROM order_line WHERE ol_ordernum = 4711;
>
> has 12 matching rows in order_line, is an op
>
> Why is that "funny behaviour" for you? By putting the statements into
> a transaction block you told the data management system "I want this
> group of statements to be atomic". Atomic means all or nothing. It
> might not be exactly what you intended to say, and you have a point
> if you concl
Holger Jakobs wrote:
Why is that "funny behaviour" for you? By putting the statements into
a transaction block you told the data management system "I want this
group of statements to be atomic". Atomic means all or nothing. It
might not be exactly what you intended to say, and you have a point
if
> Although i am not aware of the roots of this discussion but would like
> to
> comment at this point .
>
> When we work with sequences an aborted transaction does have
> a permanent effect on the last value of sequence. Is this behaviour
> not a violation of above defination of transaction ?
I
Rod Taylor wrote:
be recovered either. When committing a transaction the effects of all
operations that did not fail will be made permanent. This is how
transaction processing is described in the literature.
I would be interested in reading that (URLs please) as I didn't see
> be recovered either. When committing a transaction the effects of all
> operations that did not fail will be made permanent. This is how
> transaction processing is described in the literature.
I would be interested in reading that (URLs please) as I didn't see
anything in the spec that was inte
Holger Jakobs wrote:
Hi Stephan,
On 9 Nov, Stephan Szabo wrote:
On Fri, 7 Nov 2003 [EMAIL PROTECTED] wrote:
Whenever an error occurs within the transaction, PostgreSQL puts the
whole transaction in an *ABORT* state, so that there is no difference
at all between COMMITing or ROLLBACKing it. Even
Hi Stephan,
On 9 Nov, Stephan Szabo wrote:
> On Fri, 7 Nov 2003 [EMAIL PROTECTED] wrote:
>
>> Whenever an error occurs within the transaction, PostgreSQL puts the
>> whole transaction in an *ABORT* state, so that there is no difference
>> at all between COMMITing or ROLLBACKing it. Even commands
On Mon, 10 Nov 2003, Holger Jakobs wrote:
> Hi Stephan,
>
> On 9 Nov, Stephan Szabo wrote:
> > On Fri, 7 Nov 2003 [EMAIL PROTECTED] wrote:
> >
> >> Whenever an error occurs within the transaction, PostgreSQL puts the
> >> whole transaction in an *ABORT* state, so that there is no difference
> >>
On Fri, 7 Nov 2003 [EMAIL PROTECTED] wrote:
> Whenever an error occurs within the transaction, PostgreSQL puts the
> whole transaction in an *ABORT* state, so that there is no difference at
> all between COMMITing or ROLLBACKing it. Even commands successfully
> carried out before the error ocurred
http://www.postgresql.org/docs/7.2/interactive/plpgsql-structure.html
"It is important not to confuse the use of BEGIN/END for grouping statements in
PL/pgSQL with the database commands for transaction control. PL/pgSQL's BEGIN/END are
only for grouping; they do not start or end a transaction. F
After takin a swig o' Arrakan spice grog, Cris Carampa <[EMAIL PROTECTED]> belched
out...:
> It seems that transaction management statements (begin work...commit
> work) are not allowed into plpgsql functions. Is it true? If true,
> what happens if I put a DML statement into a function? Is it
> au
On Thursday 18 September 2003 11:24, Stephan Szabo pronounced:
> On Thu, 18 Sep 2003, tom baker wrote:
> > ah, yes, i DID not see the NOT. that is fixed. here is the code that is
> > having problems (paired down; all error checking has been removed!)
> >
> > $res=pg_query( "BEGIN TRANSACTION" ) ;
>
On Thu, 18 Sep 2003, tom baker wrote:
> ah, yes, i DID not see the NOT. that is fixed. here is the code that is having
> problems (paired down; all error checking has been removed!)
>
> $res=pg_query( "BEGIN TRANSACTION" ) ;
>
> $sql = "LOCK TABLE parts IN SHARE ROW EXCLUSIVE MODE" ;
> $res = pg_q
On Wednesday 17 September 2003 22:33, Stephan Szabo pronounced:
> On Wed, 17 Sep 2003, tom baker wrote:
> > i am (probably) shooting myself in the foot, but here goes the question.
> >
> > inside of a begin transaction/commit block, i am attempting to insert a
> > record into a parts table, and the
On Wed, 17 Sep 2003, tom baker wrote:
> i am (probably) shooting myself in the foot, but here goes the question.
>
> inside of a begin transaction/commit block, i am attempting to insert a record
> into a parts table, and then insert a record that references the part into an
> application table.
> if the primary key is table_id, with default value
> "nextval('table_seq') - then these two statements:
>
> insert into table (field1,field2,field3) values (value1,value2,value3)
> select currval('table_seq')
>
> work to get me the value I need. Except, of course if someone else
> has inserted a
> how can i implement transactions inside my plpgsql functions
I would guess that this is not possible since postgres don't support
nested transactions(?).
--
/Nils Höglund, Naqua KB
E-mail: [EMAIL PROTECTED]
Web:http://www.naqua.se/
Home Phone: +46 (0)18 30 09 19
C
Joe,
> how can i implement transactions inside my plpgsql functions
You can't.
Each PL/pgSQL function is automatically treated as a single transaction
by Postgres. As such, the entire function either succeeds or fails.
To manage a transaction *inside* the PL/pgSQL function, Postgres would
h
Keith Wong writes:
> Does anybody know the transaction isolation level default when a plpgsql
> stored procedure is called?
> Is it possible to set it? Or is this controlled by SPI?
It's whatever the transaction that called the function was set to.
--
Peter Eisentraut [EMAIL PROTECTED]
Use the command "\h set" and you'll see the parameters.
test=# \h set
Command: SET
Description: Set run-time parameters for session
Syntax:
SET variable { TO | = } { value | 'value' | DEFAULT }
SET CONSTRAINTS {ALL | constraintlist} mode
SET TIME ZONE { 'timezone' | LOCAL | DEFAULT }
SET TRAN
43 matches
Mail list logo
