Thread: Begin/Commit

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

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)

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

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

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

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

Marc Spitzer <marc@oscar.eng.cv.net> writes:
> Is this fixed in 7.2 ?

I believe so.
        regards, tom lane