Skip site navigation (1) Skip section navigation (2)

How to crash postgres using savepoints

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-bugs(at)postgresql(dot)org pgsql-general(at)postgresql(dot)org
Subject: How to crash postgres using savepoints
Date: 2006-11-15 19:57:54
Message-ID: ejfrg0$2nr7$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
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(at)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(at)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(at)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(at)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();
     }
}

Responses

pgsql-bugs by date

Next:From: Joe MalickiDate: 2006-11-15 21:05:05
Subject: Postgresql out-of-memory error
Previous:From: Jonathan EllisDate: 2006-11-15 19:44:01
Subject: BUG #2760: log_min_duration_statement always logs to stderr

pgsql-general by date

Next:From: Martijn van OosterhoutDate: 2006-11-15 20:24:18
Subject: Re: PgSQL not recognized
Previous:From: Ashley MoranDate: 2006-11-15 19:50:39
Subject: Re: ORDER BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group