How to crash postgres using savepoints - Mailing list pgsql-bugs

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();
     }
}