Thread: out of memory error on a delete command
Hi all,
I wonder what could cause such an exception (see below). I was executing
a "DELETE FROM table_name" command on a table with a lot of records (~
80 million). Now after the fact I realize I should have tried it in psql
too to see if it fails there too, but I truncated the tables in the
meantime via "TRUNCATE". The only reason I tried to do it via delete is
to see how many rows were deleted, but it looks like a bad idea...
Any enlightening thoughts ?
Cheers,
Csaba.
The (partial) stack trace:
org.postgresql.util.PSQLException: ERROR: out of memory
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:314)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:264)
[... our classes follow]
Looks like you received too many error messages Dave On 12-Jul-05, at 6:43 AM, Csaba Nagy wrote: > Hi all, > > I wonder what could cause such an exception (see below). I was > executing > a "DELETE FROM table_name" command on a table with a lot of records (~ > 80 million). Now after the fact I realize I should have tried it in > psql > too to see if it fails there too, but I truncated the tables in the > meantime via "TRUNCATE". The only reason I tried to do it via > delete is > to see how many rows were deleted, but it looks like a bad idea... > > Any enlightening thoughts ? > > Cheers, > Csaba. > > The (partial) stack trace: > > org.postgresql.util.PSQLException: ERROR: out of memory > at > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse > (QueryExecutorImpl.java:1471) > at > org.postgresql.core.v3.QueryExecutorImpl.processResults > (QueryExecutorImpl.java:1256) > at > org.postgresql.core.v3.QueryExecutorImpl.execute > (QueryExecutorImpl.java:175) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.execute > (AbstractJdbc2Statement.java:389) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags > (AbstractJdbc2Statement.java:314) > at > org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate > (AbstractJdbc2Statement.java:264) > [... our classes follow] > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > >
On Tue, 12 Jul 2005, Csaba Nagy wrote: > I wonder what could cause such an exception (see below). I was executing > a "DELETE FROM table_name" command on a table with a lot of records (~ > 80 million). Now after the fact I realize I should have tried it in psql > too to see if it fails there too, but I truncated the tables in the > meantime via "TRUNCATE". The only reason I tried to do it via delete is > to see how many rows were deleted, but it looks like a bad idea... > > org.postgresql.util.PSQLException: ERROR: out of memory This is a backend error message so the fact that you issued the command using JDBC is not relevent and it would have failed from psql as well. My immediate suspicion was the deferred trigger queue being unable to hold an event for each row deleted, but any ON DELETE triggers should have prevented TRUNCATE from running. In any case something on the backend side is probably using up some memory for each row being deleted. Kris Jurka
This is interesting... we actually do have a trigger on that table which inserts a record in a kind of archive table for each deleted record. I actually forgot about this, good that you reminded me :-) But then the TRUNCATE worked fine, and the table was actually truncated, and I'm sure the trigger didn't kick in at all. Is this something unexpected ? The trigger itself is a BEFORE DELETE ... FOR EACH ROW trigger, and it's not deferred. I've checked the table too where the trigger inserts, and it had indeed a few records, but not as many as the original table had. That could though come from previous runs... Thanks, Csaba. On Tue, 2005-07-12 at 21:53, Kris Jurka wrote: > On Tue, 12 Jul 2005, Csaba Nagy wrote: > > > I wonder what could cause such an exception (see below). I was executing > > a "DELETE FROM table_name" command on a table with a lot of records (~ > > 80 million). Now after the fact I realize I should have tried it in psql > > too to see if it fails there too, but I truncated the tables in the > > meantime via "TRUNCATE". The only reason I tried to do it via delete is > > to see how many rows were deleted, but it looks like a bad idea... > > > > org.postgresql.util.PSQLException: ERROR: out of memory > > This is a backend error message so the fact that you issued the command > using JDBC is not relevent and it would have failed from psql as well. > My immediate suspicion was the deferred trigger queue being unable to hold > an event for each row deleted, but any ON DELETE triggers should have > prevented TRUNCATE from running. In any case something on the backend > side is probably using up some memory for each row being deleted. > > Kris Jurka >
OK, that might very well be a reason... is there a way to see what were the error messages ? Are they accumulating at server side or on the JDBC side ? If it's the JDBC side, is it reasonable maybe to just discard error messages if they are too many, or truncate if it's too big, in order to avoid this exception (which is not very helpful in itself...) ? Thanks, Csaba. On Tue, 2005-07-12 at 21:43, Dave Cramer wrote: > Looks like you received too many error messages > > Dave > On 12-Jul-05, at 6:43 AM, Csaba Nagy wrote: > > > Hi all, > > > > I wonder what could cause such an exception (see below). I was > > executing > > a "DELETE FROM table_name" command on a table with a lot of records (~ > > 80 million). Now after the fact I realize I should have tried it in > > psql > > too to see if it fails there too, but I truncated the tables in the > > meantime via "TRUNCATE". The only reason I tried to do it via > > delete is > > to see how many rows were deleted, but it looks like a bad idea... > > > > Any enlightening thoughts ? > > > > Cheers, > > Csaba. > > > > The (partial) stack trace: > > > > org.postgresql.util.PSQLException: ERROR: out of memory > > at > > org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse > > (QueryExecutorImpl.java:1471) > > at > > org.postgresql.core.v3.QueryExecutorImpl.processResults > > (QueryExecutorImpl.java:1256) > > at > > org.postgresql.core.v3.QueryExecutorImpl.execute > > (QueryExecutorImpl.java:175) > > at > > org.postgresql.jdbc2.AbstractJdbc2Statement.execute > > (AbstractJdbc2Statement.java:389) > > at > > org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags > > (AbstractJdbc2Statement.java:314) > > at > > org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate > > (AbstractJdbc2Statement.java:264) > > [... our classes follow] > > > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: don't forget to increase your free space map settings > > > > >
Csaba Nagy wrote: > OK, that might very well be a reason... is there a way to see what were > the error messages ? Are they accumulating at server side or on the JDBC > side ? If it's the JDBC side, is it reasonable maybe to just discard > error messages if they are too many, or truncate if it's too big, in > order to avoid this exception (which is not very helpful in itself...) ? I think Dave actually means "too many warnings" (or NOTICEs?) which can indeed fill Java heap as they're accumulated as SQLWarnings attached to the relevant Statement. In that case, though, you'd see an OutOfMemoryError. This exception: >>>org.postgresql.util.PSQLException: ERROR: out of memory is just the driver reporting a server-side error, so it's the server that's run out of memory in this case. -O
On 13-Jul-05, at 6:30 AM, Oliver Jowett wrote: > Csaba Nagy wrote: > >> OK, that might very well be a reason... is there a way to see what >> were >> the error messages ? Are they accumulating at server side or on >> the JDBC >> side ? If it's the JDBC side, is it reasonable maybe to just discard >> error messages if they are too many, or truncate if it's too big, in >> order to avoid this exception (which is not very helpful in >> itself...) ? >> > > I think Dave actually means "too many warnings" (or NOTICEs?) which > can indeed fill Java heap as they're accumulated as SQLWarnings > attached to the relevant Statement. In that case, though, you'd see > an OutOfMemoryError. This exception: Yes, this is what I meant. > > >>>> org.postgresql.util.PSQLException: ERROR: out of memory >>>> > > is just the driver reporting a server-side error, so it's the > server that's run out of memory in this case. If this is the case, I wonder if we can be more verbose about the message. Seems we can't see the forest for the trees here. > > -O > >
Dave Cramer wrote: >>>>> org.postgresql.util.PSQLException: ERROR: out of memory >>>>> >> >> is just the driver reporting a server-side error, so it's the server >> that's run out of memory in this case. > > > If this is the case, I wonder if we can be more verbose about the > message. Seems we can't see the forest for the trees here. Well, we're just propagating whatever the server tells us so if you want a better message there it needs to be changed in the backend. We do exactly the same for all backend errors, it's just that usually it's a more obvious message along the lines of "ERROR: syntax error near ..." or similar. We could add some sort of "Error reported by backend:" prefix, I suppose, but I'm not sure if that's really useful in most cases since most errors are self-explanatory. Kris has suggested adding more fields from the backend's error to the default exception message, e.g. the DETAIL field. I'm not sure if that would add anything in this particular case though. -O
Well, I'm still at a guess what could cause the problem on the kind of query I was executing (a "delete"). Kris' explanation with the deferred trigger was the closest till now, except the trigger was not deferred, and truncate still succeded with the "ON DELETE" trigger in place... so I wonder now if there is some problem with the backend ? Maybe I should post on the general list. Cheers, Csaba. On Wed, 2005-07-13 at 14:07, Oliver Jowett wrote: > Dave Cramer wrote: > > >>>>> org.postgresql.util.PSQLException: ERROR: out of memory > >>>>> > >> > >> is just the driver reporting a server-side error, so it's the server > >> that's run out of memory in this case. > > > > > > If this is the case, I wonder if we can be more verbose about the > > message. Seems we can't see the forest for the trees here. > > Well, we're just propagating whatever the server tells us so if you want > a better message there it needs to be changed in the backend. We do > exactly the same for all backend errors, it's just that usually it's a > more obvious message along the lines of "ERROR: syntax error near ..." > or similar. > > We could add some sort of "Error reported by backend:" prefix, I > suppose, but I'm not sure if that's really useful in most cases since > most errors are self-explanatory. > > Kris has suggested adding more fields from the backend's error to the > default exception message, e.g. the DETAIL field. I'm not sure if that > would add anything in this particular case though. > > -O
Csaba Nagy wrote: > Well, I'm still at a guess what could cause the problem on the kind of > query I was executing (a "delete"). Kris' explanation with the deferred > trigger was the closest till now, except the trigger was not deferred, > and truncate still succeded with the "ON DELETE" trigger in place... Doesn't TRUNCATE explicitly ignore ON DELETE triggers? -O
Maybe in theory, cause I succeeded to truncate a table which had an ON DELETE trigger on it. I don't have time/test database ready to do more tests now, but as soon as I'll have I'll do the same operation again from psql to see how it behaves. Cheers, Csaba. On Wed, 2005-07-13 at 14:23, Oliver Jowett wrote: > Csaba Nagy wrote: > > Well, I'm still at a guess what could cause the problem on the kind of > > query I was executing (a "delete"). Kris' explanation with the deferred > > trigger was the closest till now, except the trigger was not deferred, > > and truncate still succeded with the "ON DELETE" trigger in place... > > Doesn't TRUNCATE explicitly ignore ON DELETE triggers? > > -O
Ah, OK, I've misread your mail, so you say ignoring on delete triggers is normal for TRUNCATE. Then all is fine with this one. I still need to find out what have eaten the memory on that particular delete operation. Cheers, Csaba. On Wed, 2005-07-13 at 14:23, Oliver Jowett wrote: > Csaba Nagy wrote: > > Well, I'm still at a guess what could cause the problem on the kind of > > query I was executing (a "delete"). Kris' explanation with the deferred > > trigger was the closest till now, except the trigger was not deferred, > > and truncate still succeded with the "ON DELETE" trigger in place... > > Doesn't TRUNCATE explicitly ignore ON DELETE triggers? > > -O
Csaba Nagy <nagy@ecircle-ag.com> writes:
> This is interesting... we actually do have a trigger on that table which
> inserts a record in a kind of archive table for each deleted record. I
> actually forgot about this, good that you reminded me :-)
> But then the TRUNCATE worked fine, and the table was actually truncated,
> and I'm sure the trigger didn't kick in at all. Is this something
> unexpected ?
> The trigger itself is a BEFORE DELETE ... FOR EACH ROW trigger, and it's
> not deferred. I've checked the table too where the trigger inserts, and
> it had indeed a few records, but not as many as the original table had.
> That could though come from previous runs...
If it's a BEFORE trigger then the pending-trigger list isn't at issue.
It sounds to me like the trigger function itself is doing something that
leaks a little memory per firing. You could learn more by looking in
the postmaster log: there should be a verbose report of memory context
sizes put there at the time of the "out of memory" error report.
regards, tom lane