pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednumber os commands inside transaction block
5+ messages / 3 participants
[nested] [flat]
* number os commands inside transaction block
@ 2005-01-31 21:54 Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Luiz Rafael Culik Guimaraes @ 2005-01-31 21:54 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Dear Friends
how i can increse the number of commands in an transaction block
i use postgres 7.4.5 on linux
Regards
Luiz
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Don Drake" <dondrake@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Sent: Monday, January 31, 2005 7:31 PM
Subject: Re: [SQL] plpgsql functions and NULLs
>
> On Sun, 30 Jan 2005, Don Drake wrote:
>
>> OK, I have a function that finds records that changed in a set of
>> tables and attempts to insert them into a data warehouse.
>>
>> There's a large outer loop of candidate rows and I inspect them to see
>> if the values really changed before inserting.
>>
>> My problem is that when I look to see if the row exists in the
>> warehouse already, based on some IDs, it fails when an ID is NULL.
>> The ID is nullable, so that's not a problem.
>>
>> But I'm forced to write an IF statement looking for the potential NULL
>> and write 2 queries:
>>
>> IF omcr_id is null
>> select * from ....
>> WHERE omcr_id is NULL
>> AND ...
>> ELSE
>> select * from ....
>> WHERE omcr_id=candidate.omcr_id
>> AND ....
>> END IF;
>
> Hmm, perhaps some form like:
>
> WHERE not(candidate.omcr_id is distinct from omcr_id)
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Anti-Virus.
> Version: 7.0.300 / Virus Database: 265.8.2 - Release Date: 28/1/2005
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: number os commands inside transaction block
@ 2005-01-31 22:13 Michael Fuhr <mike@fuhr.org>
parent: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Michael Fuhr @ 2005-01-31 22:13 UTC (permalink / raw)
To: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>; +Cc: pgsql-sql
On Mon, Jan 31, 2005 at 07:54:45PM -0200, Luiz Rafael Culik Guimaraes wrote:
>
> how i can increse the number of commands in an transaction block
What do you mean? What problem are you trying to solve?
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: number os commands inside transaction block
@ 2005-01-31 22:29 Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
parent: Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 5+ messages in thread
From: Luiz Rafael Culik Guimaraes @ 2005-01-31 22:29 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql
Hi Michael Fuhr
>> how i can increse the number of commands in an transaction block
>
> What do you mean? What problem are you trying to solve?
i´m trying to solve the follow message
current transaction is aborted, queries ignored until end of transaction
block
some one tell me this is defined inside postgres sources
i recive this message when i execute an certain number of queries inside an
begin/commit block
Regards
Luiz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: number os commands inside transaction block
@ 2005-01-31 22:49 Scott Marlowe <smarlowe@g2switchworks.com>
parent: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2005-01-31 22:49 UTC (permalink / raw)
To: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>; +Cc: Michael Fuhr <mike@fuhr.org>; pgsql-sql
On Mon, 2005-01-31 at 16:29, Luiz Rafael Culik Guimaraes wrote:
> Hi Michael Fuhr
> >> how i can increse the number of commands in an transaction block
> >
> > What do you mean? What problem are you trying to solve?
>
> i´m trying to solve the follow message
> current transaction is aborted, queries ignored until end of transaction
> block
>
> some one tell me this is defined inside postgres sources
> i recive this message when i execute an certain number of queries inside an
> begin/commit block
This is normal postgresql behaviour, and can't really be changed.
However, with the advent of savepoints in 8.0, it is now possible to
detect such errors and roll back so you can then continue. Without
using savepoints with rollback to the given save point, however, there's
not much way to get around it.
It sounds to me like you're getting an error somewhere in your scripts
you need to look into.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: number os commands inside transaction block
@ 2005-01-31 22:54 Michael Fuhr <mike@fuhr.org>
parent: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Fuhr @ 2005-01-31 22:54 UTC (permalink / raw)
To: Luiz Rafael Culik Guimaraes <culikr@brturbo.com>; +Cc: pgsql-sql
On Mon, Jan 31, 2005 at 08:29:42PM -0200, Luiz Rafael Culik Guimaraes wrote:
>
> i´m trying to solve the follow message
> current transaction is aborted, queries ignored until end of transaction
> block
A previous command in the transaction has failed; no more commands
will be executed until you issue a ROLLBACK (or a COMMIT, but the
transaction will be rolled back due to the error). If you're doing
error checking on every command then you should be able to discover
which command failed.
PostgreSQL 8.0 has savepoints so you can roll back part of a
transaction and continue after an error, but that might not be
what you need.
> some one tell me this is defined inside postgres sources
> i recive this message when i execute an certain number of queries inside an
> begin/commit block
Transactions can have 2^32 - 1 (4294967295) commands, so I'd be
surprised if you were hitting that limit. If you were, you should
see the following error:
cannot have more than 2^32-1 commands in a transaction
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-01-31 22:54 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-01-31 21:54 number os commands inside transaction block Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
2005-01-31 22:13 ` Michael Fuhr <mike@fuhr.org>
2005-01-31 22:29 ` Luiz Rafael Culik Guimaraes <culikr@brturbo.com>
2005-01-31 22:49 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-01-31 22:54 ` Michael Fuhr <mike@fuhr.org>
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