Thread: How to crash postgres using savepoints
See example below. At the very least the documentation needs to tell
users that savepoints use shared memory, and the cofusing HINT string
needs to be changed to something more useful.
When run on a machine running 8.2b3
version: PostgreSQL 8.2beta3 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5)
[root@pii /tmp]# nice time java SavepointBug
org.postgresql.util.PSQLException: ERROR: out of shared memory
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1527)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1311)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:340)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:286)
at SavepointBug.main(SavepointBug.java:46)
error after iteration: 9912
3.82user 0.79system 4:16.96elapsed 1%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (2873major+1462minor)pagefaults 0swaps
postgres.log ends with:
LOG: execute <unnamed>: INSERT INTO spt (id) VALUES(9910)
LOG: execute <unnamed>: SAVEPOINT sp1
LOG: execute <unnamed>: INSERT INTO spt (id) VALUES(9911)
LOG: execute <unnamed>: SAVEPOINT sp1
LOG: execute <unnamed>: INSERT INTO spt (id) VALUES(9912)
WARNING: out of shared memory
ERROR: out of shared memory
HINT: You may need to increase max_locks_per_transaction.
LOG: execute <unnamed>: ABORT
[root@pii /tmp]# nice time java SavepointBug --release
Completed without error
5.44user 1.31system 3:02.65elapsed 3%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (2870major+1467minor)pagefaults 0swaps
---------------------------------------------
When run on a machine running 8.0.9:
version: PostgreSQL 8.0.9 on i686-pc-linux-gnu, compiled by GCC gcc
(GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)
[root@dii /tmp]# nice time java SavepointBug
java.sql.SQLException: ERROR: out of shared memory
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1495)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1279)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:186)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:314)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:264)
at SavepointBug.main(SavepointBug.java:46)
error after iteration: 7647
2.03user 0.52system 0:27.22elapsed 9%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (1major+4491minor)pagefaults 0swaps
[root@dii /tmp]# nice time java SavepointBug --release
Completed without error
2.84user 0.98system 0:13.67elapsed 27%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (1major+4613minor)pagefaults 0swaps
======================== SavepointBug.java ==========================
import java.sql.*;
public final class SavepointBug {
public static void main(final String[] args) throws Exception{
boolean release_sp = false;
String url = "jdbc:postgresql:playpen";
String user = "user1";
for(int i = 0 ; i < args.length ; i++){
String argi = args[i];
if (argi.equals("--release"))
release_sp = true;
else if (argi.equals("--url"))
url = args[++i];
else if (argi.equals("--user"))
user = args[++i];
else{
System.err.println("unknown argument "+argi);
System.exit(1);
}
}
// Load the driver
Class.forName("org.postgresql.Driver");
Connection db = DriverManager.getConnection(url, user,"");
Statement st =
db.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
st.executeUpdate("BEGIN;");
st.executeUpdate("create temp table spt (id int);");
int i = 1;
try{
for(; i < 12000 ; i++){
st.executeUpdate("SAVEPOINT sp1;");
st.executeUpdate("INSERT INTO spt (id) VALUES("+i+");");
if (release_sp)
st.executeUpdate("RELEASE SAVEPOINT sp1;");
}
System.out.println("Completed without error");
}catch(SQLException e){
st.executeUpdate("ABORT;");
e.printStackTrace();
System.err.println("error after iteration: "+i);
}
db.close();
}
}
Joseph Shraibman <jks@selectacast.net> writes:
> See example below. At the very least the documentation needs to tell
> users that savepoints use shared memory, and the cofusing HINT string
> needs to be changed to something more useful.
Which part of "You may need to increase max_locks_per_transaction" do
you find confusing? If you actually need tens of thousands of nested
savepoints then this is accurate advice.
Actually, I'd say the dubious behavior here is that
SAVEPOINT foo;
SAVEPOINT foo;
SAVEPOINT foo;
creates three nested savepoints ... it might be better if it
automatically released any existing savepoint of the same name.
I notice that the SAVEPOINT reference page says the latter is
the behavior required by the SQL spec. Did we explicitly decide
to do this differently from spec, and if so why?
regards, tom lane
Tom Lane wrote: > Joseph Shraibman <jks@selectacast.net> writes: >> See example below. At the very least the documentation needs to tell >> users that savepoints use shared memory, and the cofusing HINT string >> needs to be changed to something more useful. > > Which part of "You may need to increase max_locks_per_transaction" do > you find confusing? If you actually need tens of thousands of nested > savepoints then this is accurate advice. Because there is nothing anywhere that indicates that a savepoint uses a lock. When I got the message the first time I was very confused and had nowhere to look to figure out what the real problem was. A more helpful message would be "You may need to increase max_locks_per_transaction, or release your savepoints more often" Why does a savepoint need shared memory anyway, if it is only useful inside the transaction it was created in?
Tom Lane wrote: > Actually, I'd say the dubious behavior here is that > > SAVEPOINT foo; > SAVEPOINT foo; > SAVEPOINT foo; > > creates three nested savepoints ... it might be better if it > automatically released any existing savepoint of the same name. > I notice that the SAVEPOINT reference page says the latter is > the behavior required by the SQL spec. Did we explicitly decide > to do this differently from spec, and if so why? Yeah, we did. I think the rationale was what happens when you have another savepoint in the middle, say SAVEPOINT foo; SAVEPOINT bar; SAVEPOINT foo; The fact that the third sentence would implicitely release the "bar" savepoint bothered us. IIRC Oracle gets away with this because their savepoints are not actually nested, but sequential (i.e. you can release "foo" without forgetting about "bar"). I'm not really sure about this. Maybe we could change the behavior so that establishing a savepoint releases a savepoint of the same name if it's the direct parent, but that doesn't follow the KISS principle. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> ... Did we explicitly decide
>> to do this differently from spec, and if so why?
> Yeah, we did. I think the rationale was what happens when you have
> another savepoint in the middle, say
> SAVEPOINT foo;
> SAVEPOINT bar;
> SAVEPOINT foo;
Ah, right. I'm not in a huge hurry to change this, anyway ... it's not
like there aren't any number of other ways to run the system out of
locktable slots.
(I spent a bit of time thinking about whether we needed locktable
entries for subxacts at all, but I don't see how to preserve the
stop-waiting-on-subxact-abort behavior of XactLockTableWait without
them. We can't just wait on the subxact's topmost parent.)
regards, tom lane