pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Begin/Commit
7+ messages / 4 participants
[nested] [flat]

* Begin/Commit
@ 2001-12-28 22:27 Linh Luong <linh.luong@computalog.com>
  2001-12-28 23:41 ` Re: Begin/Commit Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-12-29 00:26 ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
  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 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
@ 2001-12-28 23:41 ` Stephan Szabo <sszabo@megazone23.bigpanda.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-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
@ 2001-12-29 00:26 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 01:47   ` Re: Begin/Commit Marc Spitzer <marc@oscar.eng.cv.net>
  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-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
  2001-12-29 00:26 ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-12-29 01:47   ` Marc Spitzer <marc@oscar.eng.cv.net>
  2001-12-29 03:54     ` Re: Begin/Commit 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-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
  2001-12-29 00:26 ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 01:47   ` Re: Begin/Commit Marc Spitzer <marc@oscar.eng.cv.net>
@ 2001-12-29 03:54     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 04:05       ` Re: Begin/Commit 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-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
  2001-12-29 00:26 ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 01:47   ` Re: Begin/Commit Marc Spitzer <marc@oscar.eng.cv.net>
  2001-12-29 03:54     ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-12-29 04:05       ` Marc Spitzer <marc@oscar.eng.cv.net>
  2001-12-29 04:34         ` Re: Begin/Commit 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-28 22:27 Begin/Commit Linh Luong <linh.luong@computalog.com>
  2001-12-29 00:26 ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 01:47   ` Re: Begin/Commit Marc Spitzer <marc@oscar.eng.cv.net>
  2001-12-29 03:54     ` Re: Begin/Commit Tom Lane <tgl@sss.pgh.pa.us>
  2001-12-29 04:05       ` Re: Begin/Commit Marc Spitzer <marc@oscar.eng.cv.net>
@ 2001-12-29 04:34         ` Tom Lane <tgl@sss.pgh.pa.us>
  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