[HACKERS] exec_execute_message crush
While inspecting a complain from a pgpool user, I found that PostgreSQL crushes with following statck trace: #0 0x0826436a in list_length (l=0xaabe4e28) at ../../../src/include/nodes/pg_list.h:94 #1 0x08262168 in IsTransactionStmtList (parseTrees=0xaabe4e28) at postgres.c:2429 #2 0x0826132e in exec_execute_message (portal_name=0x857bab0 , max_rows=0) at postgres.c:1824 #3 0x08263b2a in PostgresMain (argc=4, argv=0x84f6c28, username=0x84f6b08 t-ishii) at postgres.c:3671 #4 0x0823299e in BackendRun (port=0x8511e68) at postmaster.c:3449 #5 0x08231f78 in BackendStartup (port=0x8511e68) at postmaster.c:3063 #6 0x0822f90a in ServerLoop () at postmaster.c:1387 #7 0x0822f131 in PostmasterMain (argc=3, argv=0x84f4bf8) at postmaster.c:1040 #8 0x081c6217 in main (argc=3, argv=0x84f4bf8) at main.c:188 This happens with following extended commands sequence: parse bind describe execute normaly done parse invalid SQL thus abort a transaction bind (error) describe (error) execute (crush) exec_execute_message crushes here: /* Does the portal contain a transaction command? */ is_xact_command = IsTransactionStmtList(portal-stmts); Looking into portal: $5 = {name = 0x85727bc , prepStmtName = 0x0, heap = 0x8596798, resowner = 0x0, cleanup = 0, createSubid = 1, sourceText = 0x859ac78 SELECT NULL AS TABLE_CAT, n.nspname AS TABLE_SCHEM, ct.relname AS TABLE_NAME, a.attname AS COLUMN_NAME, a.attnum AS KEY_SEQ, ci.relname AS PK_NAME FROM pg_catalog.pg_namespace n, pg_catalog.pg_c..., commandTag = 0x84682aa SELECT, stmts = 0xaabe4e28, cplan = 0x0, portalParams = 0x0, strategy = PORTAL_ONE_SELECT, cursorOptions = 4, status = PORTAL_READY, queryDesc = 0x0, tupDesc = 0x85db060, formats = 0x859b0c8, holdStore = 0x0, holdContext = 0x0, atStart = 1 '\001', atEnd = 1 '\001', posOverflow = 0 '\0', portalPos = 0, creation_time = 315313855337710, visible = 1 '\001'} Problem is, stmts points to invalid memory address: (gdb) p *portal-stmts Cannot access memory at address 0xaabe4e28 It seems the source of the problem is, exec_execute_message tries to execute unamed portal which has unnamed statement which has already gone. Please note that without pgpool backend does not crush. This is because JDBC driver does not do execute() if prior parse, bind etc. failed, I think. The crush happens PostgreSQL 8.3.8, 8.3.9 and 8.4.2. Any thought? -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
Tatsuo Ishii is...@postgresql.org writes: It seems the source of the problem is, exec_execute_message tries to execute unamed portal which has unnamed statement which has already gone. Could we see an actual test case? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
Tatsuo Ishii is...@postgresql.org writes: It seems the source of the problem is, exec_execute_message tries to execute unamed portal which has unnamed statement which has already gone. Could we see an actual test case? If you don't mind to use pgpool, it would be possible. If not, I need to write a small program which handles frontend/backend protocol directly. What shall I do? -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
Tatsuo Ishii is...@postgresql.org writes: Could we see an actual test case? If you don't mind to use pgpool, it would be possible. If not, I need to write a small program which handles frontend/backend protocol directly. What shall I do? Hm, can't you get libpq to do it? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
If you don't mind to use pgpool, it would be possible. If not, I need to write a small program which handles frontend/backend protocol directly. What shall I do? Hm, can't you get libpq to do it? That depends on how libpq is intelligent:-) Let me try... Another idea is a packet recorder, which could record packets from pgpool to PostgreSQL and replay them. I don't remember at present, but I vaguely recall something like that exists. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
Hm, can't you get libpq to do it? That depends on how libpq is intelligent:-) Let me try... Another idea is a packet recorder, which could record packets from pgpool to PostgreSQL and replay them. I don't remember at present, but I vaguely recall something like that exists. It seems we can't get libpq to do it. libpq does not provide a function which can execute bind alone. In my understanding PQexecPrepared does bind + execute. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
Tatsuo Ishii is...@postgresql.org writes: Hm, can't you get libpq to do it? It seems we can't get libpq to do it. libpq does not provide a function which can execute bind alone. In my understanding PQexecPrepared does bind + execute. The event sequence you mentioned had bind followed by execute, so I'm not seeing the problem. (In any case, some kind of quick lobotomy in libpq would be easier than writing a standalone test program, no?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
(In any case, some kind of quick lobotomy in libpq would be easier than writing a standalone test program, no?) Sounds nice idea. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
On Tue, 29 Dec 2009, Tatsuo Ishii wrote: parse bind describe execute normaly done parse invalid SQL thus abort a transaction bind (error) describe (error) execute (crush) Please note that without pgpool backend does not crush. This is because JDBC driver does not do execute() if prior parse, bind etc. failed, I think. The JDBC driver will fire away parse, bind, and execute all at once before a sync, to avoid network roundtrips, so your assumption of what's going on here without pgpool doesn't seem accurate. Attached is a test case that tries to duplicate what you've described and it errors out normally. Below is the JDBC driver's protocol level logging. 21:41:39.407 (1) FE= Parse(stmt=S_1,query=BEGIN,oids={}) 21:41:39.407 (1) FE= Bind(stmt=S_1,portal=null) 21:41:39.407 (1) FE= Execute(portal=null,limit=0) 21:41:39.408 (1) FE= Parse(stmt=null,query=SELECT $1 ,oids={23}) 21:41:39.408 (1) FE= Bind(stmt=null,portal=null,$1='1') 21:41:39.408 (1) FE= Describe(portal=null) 21:41:39.408 (1) FE= Execute(portal=null,limit=0) 21:41:39.408 (1) FE= Parse(stmt=null,query= SELECT SELECT $1 ,oids={23}) 21:41:39.408 (1) FE= Bind(stmt=null,portal=null,$1='2') 21:41:39.409 (1) FE= Describe(portal=null) 21:41:39.409 (1) FE= Execute(portal=null,limit=0) 21:41:39.409 (1) FE= Sync 21:41:39.443 (1) =BE ParseComplete [S_1] 21:41:39.443 (1) =BE BindComplete [null] 21:41:39.443 (1) =BE CommandStatus(BEGIN) 21:41:39.443 (1) =BE ParseComplete [null] 21:41:39.443 (1) =BE BindComplete [null] 21:41:39.444 (1) =BE RowDescription(1) 21:41:39.444 (1) =BE DataRow 21:41:39.444 (1) =BE CommandStatus(SELECT) 21:41:39.454 (1) =BE ErrorMessage(ERROR: syntax error at or near SELECT Position: 9) So this shows everything working as expected. Perhaps enabling this logging on your JDBC client would show more clearly what it is trying to do. Kris Jurkaimport java.sql.*; public class Crash { public static void main(String args[]) throws Exception { Class.forName(org.postgresql.Driver); Connection conn = DriverManager.getConnection(jdbc:postgresql://localhost:5844/jurka?loglevel=2,jurka,); conn.setAutoCommit(false); PreparedStatement pstmt = conn.prepareStatement(SELECT ? ; SELECT SELECT ? ); pstmt.setInt(1, 1); pstmt.setInt(2, 2); pstmt.execute(); } } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] exec_execute_message crush
parse bind describe execute normaly done parse invalid SQL thus abort a transaction bind (error) describe (error) execute (crush) Please note that without pgpool backend does not crush. This is because JDBC driver does not do execute() if prior parse, bind etc. failed, I think. The JDBC driver will fire away parse, bind, and execute all at once before a sync, to avoid network roundtrips, so your assumption of what's going on here without pgpool doesn't seem accurate. Attached is a test case that tries to duplicate what you've described and it errors out normally. Below is the JDBC driver's protocol level logging. 21:41:39.407 (1) FE= Parse(stmt=S_1,query=BEGIN,oids={}) 21:41:39.407 (1) FE= Bind(stmt=S_1,portal=null) 21:41:39.407 (1) FE= Execute(portal=null,limit=0) 21:41:39.408 (1) FE= Parse(stmt=null,query=SELECT $1 ,oids={23}) 21:41:39.408 (1) FE= Bind(stmt=null,portal=null,$1='1') 21:41:39.408 (1) FE= Describe(portal=null) 21:41:39.408 (1) FE= Execute(portal=null,limit=0) 21:41:39.408 (1) FE= Parse(stmt=null,query= SELECT SELECT $1 ,oids={23}) 21:41:39.408 (1) FE= Bind(stmt=null,portal=null,$1='2') 21:41:39.409 (1) FE= Describe(portal=null) 21:41:39.409 (1) FE= Execute(portal=null,limit=0) 21:41:39.409 (1) FE= Sync 21:41:39.443 (1) =BE ParseComplete [S_1] 21:41:39.443 (1) =BE BindComplete [null] 21:41:39.443 (1) =BE CommandStatus(BEGIN) 21:41:39.443 (1) =BE ParseComplete [null] 21:41:39.443 (1) =BE BindComplete [null] 21:41:39.444 (1) =BE RowDescription(1) 21:41:39.444 (1) =BE DataRow 21:41:39.444 (1) =BE CommandStatus(SELECT) 21:41:39.454 (1) =BE ErrorMessage(ERROR: syntax error at or near SELECT Position: 9) So this shows everything working as expected. Perhaps enabling this logging on your JDBC client would show more clearly what it is trying to do. Thanks for clarification. I will look into more between pgpool and PostgreSQL packet exchange. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers