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
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 updated to work with the new logic. I don't have an
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
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 code, so
the PostgreSQL user does
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
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 into test (id) values (1);
insert into test (id) values (
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 ?
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 ?
Thank you for helping me out on this one !
---(end of broadca
-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
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_
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
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 seem to work because after the first error (the
insert)
everything is omitted.
Has someone found a solution for this (w
Hey I hope someone can help me here.
I'm doing some little test on PostgreSQL 7.3.5 and am experiencing some
weird behaviour.
I create some tables and add values to it using insert into select from.
After I am done with that, I create unique constraints on the tables.
The reason to do that aft
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
t see
anything in the spec that was interesting on this topic.
4.8.5 from Framework (part 01)
An SQL-transaction (transaction) is a sequence of executions of
SQL-statements that is atomic with respect to recovery. That is
to say: either the execution result is compl
spec that was interesting on this topic.
4.8.5 from Framework (part 01)
An SQL-transaction (transaction) is a sequence of executions of
SQL-statements that is atomic with respect to recovery. That is
to say: either the execution result is completely successful, or
it ha
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
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 automatically
commited every time the function executes? Is there no way to rollback
the changes?
Dear PostgreSQL Gurus,
I have been using PostgreSQL for quite a while and always relied on its
handling of transaction and concurrency. But recently I discovered a
behaviour that deviates quite a lot from how I understand transactions
and how things are handled by other databases: HP Allbase, Orac
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.
hello all
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. and the transaction fails with this
> 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
Hi,
I've been using Postgres for a while, almost exclusively through the
perl DBI (although I do plenty of work on the command line).
I have realized, belatedly, that I need transactions for this thing I
want to accomplish, but I've not done transactions before, so I need
a bit of help. And,
> 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
how can i implement transactions inside my plpgsql functions
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Regarding "Committed Isolation" on PostgreSql Ver 7.1 Users Guide, is that
possible a "Dead Lock" happened when two concurrent transactions are waiting
each other? And how to avoid or fix it?
Jack
---(end of broadcast)---
TIP 4: Don't 'kill -9' t
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
Hi all,
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?
Cheers,
Keith.
57 matches
Mail list logo
