Horton

Thread

  1. How to crash postgres using savepoints

    Joseph Shraibman <jks@selectacast.net> — 2006-11-15T19:57:54Z

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

  2. Re: How to crash postgres using savepoints

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-11-16T20:35:11Z

    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

  3. Re: How to crash postgres using savepoints

    Joseph Shraibman <jks@selectacast.net> — 2006-11-16T20:59:17Z

    
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?

  4. Re: How to crash postgres using savepoints

    Alvaro Herrera <alvherre@commandprompt.com> — 2006-11-16T20:59:33Z

    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.

  5. Re: How to crash postgres using savepoints

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-11-16T21:08:02Z

    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