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.receiveEr rorResponse(QueryExecutorImpl.java:1527)
at
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1311)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:190)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:452)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:340)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eUpdate(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.receiveEr rorResponse(QueryExecutorImpl.java:1495)
at
org.postgresql.core.v3.QueryExecutorImpl.processRe sults(QueryExecutorImpl.java:1279)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(Q ueryExecutorImpl.java:186)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut e(AbstractJdbc2Statement.java:392)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eWithFlags(AbstractJdbc2Statement.java:314)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execut eUpdate(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 = "jdbcostgresqllaypen";
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();
}
}

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Joseph Shraibman <jks (AT) selectacast (DOT) net> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tom Lane wrote:
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?

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Alvaro Herrera <alvherre (AT) commandprompt (DOT) com> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Tom Lane wrote:

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.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate