pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedBegin/Commit
7+ messages / 4 participants
[nested] [flat]
* Begin/Commit
@ 2001-12-28 22:27 Linh Luong <linh.luong@computalog.com>
0 siblings, 2 replies; 7+ messages in thread
From: Linh Luong @ 2001-12-28 22:27 UTC (permalink / raw)
To: pgsql-sql
Hi,
I was wondering is there a limitation in the number of sql stmt that can go
between a begin and commit.
I have a very large file that contains many updates and inserts (I mean
many). And it is taking too long to restore the data. Pg_restore is out of
the questions at the current moment because of the data version.
Example,
Begin Work;
....
...
.. (1000 update/inserts sql smts)
Commit Work;
Is this allowed????
Thanks
LL
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-28 23:41 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Linh Luong <linh.luong@computalog.com>
1 sibling, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2001-12-28 23:41 UTC (permalink / raw)
To: Linh Luong <linh.luong@computalog.com>; +Cc: pgsql-sql
On Fri, 28 Dec 2001, Linh Luong wrote:
> Hi,
>
> I was wondering is there a limitation in the number of sql stmt that can go
> between a begin and commit.
>
> I have a very large file that contains many updates and inserts (I mean
> many). And it is taking too long to restore the data. Pg_restore is out of
> the questions at the current moment because of the data version.
>
> Example,
> Begin Work;
> ....
> ...
> .. (1000 update/inserts sql smts)
> Commit Work;
>
> Is this allowed????
Yes, in general that should be fine (excepting that
any failed statement will force a rollback which
affects all of the statements in the transaction)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-29 00:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: Linh Luong <linh.luong@computalog.com>
1 sibling, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-12-29 00:26 UTC (permalink / raw)
To: linh.luong@computalog.com; +Cc: pgsql-sql
Linh Luong <linh.luong@computalog.com> writes:
> I was wondering is there a limitation in the number of sql stmt that can go
> between a begin and commit.
No, not as such.
If you are doing operations that involve foreign-key checks, you may see
performance problems in pre-7.2 releases due to some operations that
walk the list of pending foreign-key triggers. You didn't give enough
info to know if that is a problem, though.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-29 01:47 Marc Spitzer <marc@oscar.eng.cv.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marc Spitzer @ 2001-12-29 01:47 UTC (permalink / raw)
To: pgsql-sql
On Fri, Dec 28, 2001 at 07:26:13PM -0500, Tom Lane wrote:
> Linh Luong <linh.luong@computalog.com> writes:
> > I was wondering is there a limitation in the number of sql stmt that can go
> > between a begin and commit.
>
> No, not as such.
>
> If you are doing operations that involve foreign-key checks, you may see
> performance problems in pre-7.2 releases due to some operations that
> walk the list of pending foreign-key triggers. You didn't give enough
> info to know if that is a problem, though.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
Would one of these conditions include deleting a row where there is
a field that is used as a foreign-key in another table?
Thanks
marc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-29 03:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Spitzer <marc@oscar.eng.cv.net>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-12-29 03:54 UTC (permalink / raw)
To: Marc Spitzer <marc@oscar.eng.cv.net>; +Cc: pgsql-sql
Marc Spitzer <marc@oscar.eng.cv.net> writes:
> Would one of these conditions include deleting a row where there is
> a field that is used as a foreign-key in another table?
[ thinks ... ] Yes, I think so. The cases I'm familiar with involve
inserts and updates, but probably a delete on the referenced table would
fire triggers as well. With enough pending triggers, 7.0 and 7.1 will
get slow. See discussions of 'triggered data change violation' error
in the archives.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-29 04:05 Marc Spitzer <marc@oscar.eng.cv.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Marc Spitzer @ 2001-12-29 04:05 UTC (permalink / raw)
To: pgsql-sql
On Fri, Dec 28, 2001 at 10:54:28PM -0500, Tom Lane wrote:
> Marc Spitzer <marc@oscar.eng.cv.net> writes:
> > Would one of these conditions include deleting a row where there is
> > a field that is used as a foreign-key in another table?
>
> [ thinks ... ] Yes, I think so. The cases I'm familiar with involve
> inserts and updates, but probably a delete on the referenced table would
> fire triggers as well. With enough pending triggers, 7.0 and 7.1 will
> get slow. See discussions of 'triggered data change violation' error
> in the archives.
>
> regards, tom lane
Thanks,
That explains a problem I had deleteing around 33,000 rows. I let
it run for a couple of hours and it did not work. I finaly had to
select all the primary keys of the rows I wanted to delete and
delete the rows one at a time, it was doing about 120 rows/min.
Is this fixed in 7.2 ?
Thanks again
marc
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Begin/Commit
@ 2001-12-29 04:34 Tom Lane <tgl@sss.pgh.pa.us>
parent: Marc Spitzer <marc@oscar.eng.cv.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2001-12-29 04:34 UTC (permalink / raw)
To: Marc Spitzer <marc@oscar.eng.cv.net>; +Cc: pgsql-sql
Marc Spitzer <marc@oscar.eng.cv.net> writes:
> Is this fixed in 7.2 ?
I believe so.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-12-29 04:34 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
2001-12-28 23:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-29 00:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-29 01:47 ` Marc Spitzer <marc@oscar.eng.cv.net>
2001-12-29 03:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-29 04:05 ` Marc Spitzer <marc@oscar.eng.cv.net>
2001-12-29 04:34 ` Tom Lane <tgl@sss.pgh.pa.us>
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