Craig Ringer wrote:
> On Tue, 2009-09-15 at 19:26 +0000, Joseph Shraibman wrote:
>> The following bug has been logged online:
> While the test case is appreciated, it's not really that clear what's
> expected to happen vs what happens, at what point things go wrong and
> under what circumstances, etc. Looking at your code and your post, I
> can't tell:
>
> - If the code you've attached is before or after the change described
> - Where the change is/was made
> - Why
> - What it actually does
Look at the main method. I run 3 queries that should return the same
thing, but 1 time out of 3 it doesn't.
>
> There are also a couple of randomly commented-out bits of code that seem
> odd, some code seems like unnecessary wrapper code in such a simple
> example ( eg getStatement(...) ) and some of the code is downright
> hairy.
I started writing this example program based on my actual codebase in an
attempt to distill down to code that demonstrates the bug. That's why
there was some extra stuff left in there.
> In particular:
>
> private static StringBuilder toString(Object o, StringBuilder sb)
>
> cries out "use parameterized queries!". You're making life unnecessarily
> hard for yourself, increasing SQL injection risk dramatically, and
> giving yourself more to debug than you need by building queries as
> strings including all parameters.
This is supposed to be a simple example that demonstrates the bug, not
production code. The toString() method above is used for printing out
the results, not constructing sql.
>
>
> Perhaps you could pare down the code to that required to demonstrate the
> issue and document the issue a little? Cut out unnecessary or duplicate
> code (why are there two doQuery() implementations with mostly the same
> code),
To demonstrate the bug. One works, one has a problem.
drop any result-printing code etc if it doesn't help demonstrate
> the bug you're reporting, remove commented out code and empty blocks,
> and generally tidy things up so that the reader can read it to follow
> your problem report, not puzzle out why your code does things how it
> does.
>
Here is a simplified version. I've gotten rid of some of the dead code
and a doQuery() method and added some comments.
PgBug.java :
--------------------------------------------------------------------
import java.sql.*;
public class PgBug {
private Connection conn = null;
private static int verbose = 0;
public PgBug() throws SQLException, ClassNotFoundException{
// Load the driver
Class.forName("org.postgresql.Driver");
conn = DriverManager.getConnection("jdbc:postgresql:playpen",
"postgres", "");;
System.out.println("jdbc version:
"+org.postgresql.Driver.getVersion());
}
/** Converts this Object to a String. If o is an array recursively
calls for all elements of the array */
private static StringBuilder toString(Object o, StringBuilder sb){
if (sb == null)
sb = new StringBuilder();
if (o == null)
sb.append("null");
else if (o.getClass().isArray()){
Class c = o.getClass().getComponentType();
if (c.isPrimitive()){
int len = java.lang.reflect.Array.getLength(o);
sb.append("[");
for(int i = 0; i < len ; i++){
sb.append(java.lang.reflect.Array.get(o, i));
if (i+1 < len)
sb.append(',');
}
sb.append("]");
}else{
Object[] arr = (Object[])o;
sb.append("{");
for(int i = 0; i < arr.length ; i++){
toString(arr[i],sb);
if (i+1 < arr.length)
sb.append(',');
}
sb.append("}");
}
}else
sb.append(o);
return sb;
}
static java.sql.Statement getStatement(java.sql.Connection
conn)throws java.sql.SQLException{
return
conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
java.sql.ResultSet.CONCUR_READ_ONLY);
}
static int getRsSize(java.sql.ResultSet rs)throws
java.sql.SQLException{
if (!rs.last()) return 0;
int ans = rs.getRow();
rs.first(); rs.previous(); //reset pointer to before first row
if ( ! rs.isBeforeFirst())
throw new java.sql.SQLException(" getRsSize(): Could not
reset pointer");
return ans;
}
private static void printAnyWarnings(java.sql.Statement
st,CharSequence line){
try{
java.sql.SQLWarning warn = st.getWarnings();
if (warn != null)
System.err.println("SQLWarning: "+warn.getMessage()+"
from sql: "+line);
}catch(java.sql.SQLException e){}
}
/** Convert a ResultSet into Object[][] */
public static Object[][] getResults(ResultSet rs)throws SQLException{
final int num_rows = getRsSize(rs);
if (num_rows == 0) return new Object[0][0];
final int cols = rs.getMetaData().getColumnCount();
Object answer[][] = new Object[num_rows][cols] ;
for (int i = 0 ; rs.next() ; i++ ) {
for (int j = 0 ; j < cols ; j++) {
answer[i][j] = rs.getObject(j+1);
if (verbose >= 3)
System.out.println("getResults()
answer["+i+"]["+j+"] = "+answer[i][j]);
if (answer[i][j] instanceof java.sql.Array)
answer[i][j] =
((java.sql.Array)answer[i][j]).getArray();
else if (answer[i][j] instanceof
org.postgresql.util.PGobject)
answer[i][j] = answer[i][j].toString();
}
}
return answer;
}
public int doUpdate(String line)throws SQLException{
Statement st = conn.createStatement();
int ans = st.executeUpdate(line);
printAnyWarnings(st,line);
st.close();
return ans;
}
/** @param eu If true use executeUpdate() for the pre_line, if
false use st.execute() */
public Object[][] doQuery(String pre_line, String line, String
post_line, boolean eu)throws SQLException{
if (line == null)
throw new SQLException ("doQuery: line is null.");
Statement st = getStatement(conn);
if (pre_line != null || post_line != null){
if (pre_line != null){
//Here is where the bug is. Originally I was calling
st.executeUpdate(pre_line). No Exceptions
//happened and I assumed everything was dandy until I
realized that my results were wrong.
//The problem is that my temp table is not set up
properly when I do this.
if (eu)
st.executeUpdate(pre_line);
else
st.execute(pre_line);
printAnyWarnings(st,pre_line);
}
}
ResultSet rs = st.executeQuery(line);
Object[][] ans = getResults(rs);
if (verbose >= 1)
System.out.println("doQuery(): ans is: "+toString(ans, null));
if (post_line != null){
st.executeUpdate(post_line);
printAnyWarnings(st,post_line);
}
st.close();
return ans;
}
//Used by setup() below
private String getSeq(int num){
StringBuilder sb = new StringBuilder();
for(int i = 0; i < num ; i++){
sb.append(i);
if (i+1 < num)
sb.append(',');
}
return sb.toString();
}
void setup()throws SQLException{
doUpdate("CREATE TABLE joa (jobid int, uids int[])");
for(int i = 1; i < 15 ; i++){
doUpdate("INSERT INTO joa VALUES("+i+",
ARRAY["+getSeq(i)+"])");
}
String q = "CREATE OR REPLACE FUNCTION
insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$\n"+
"BEGIN\n"+
" FOR i IN array_lower(thearr,1)..array_upper(thearr,1)
LOOP\n"+
"execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid
|| ',' || thearr[i] || ');';\n"+
"END LOOP;\n"+
"END;\n"+
"$$ language 'plpgsql' VOLATILE ;";
doUpdate(q);
}
void doTest1(boolean eu)throws SQLException{
//Create a temp table, and use insert_to_japlcu_table() to
populate it with data from the joa table
String pre = " BEGIN; CREATE TEMP TABLE japlc (jobid int, uid
int); SELECT insert_to_japlcu_table(uids, jobid) FROM joa; ANALYZE
japlc;";
//You can replace this sql with SELECT * to see the contents of
the temp table
Object[][] oaa = doQuery(pre, "SELECT count(*) FROM japlc;",
"ABORT;", eu);
System.out.print(toString(oaa, null));
}
void teardown()throws SQLException{
doUpdate("DROP TABLE joa;");
}
void setServerLogLevel(String s)throws SQLException{
doUpdate("set client_min_messages = "+s);
doUpdate("set log_min_messages = "+s);
}
public static final void main(final String[] args)throws
SQLException, ClassNotFoundException {
PgBug bug = new PgBug();
try{
System.out.print("tearing down (in case of previous bad
exit)...");
bug.teardown();
System.out.println("done.");
}catch(SQLException e){
System.out.println("nothing to do.");
}
for(int i = 0; i < args.length ; i++){
if(args[i].equals("--serverloglevel"))
bug.setServerLogLevel(args[++i]);
else if(args[i].equals("--verbose"))
verbose = Integer.parseInt(args[++i]);
}
System.out.print("setting up...");
bug.setup();
System.out.println("done.");
System.out.print("running test with executeUpdate(pre)...");
bug.doTest1(true);
System.out.println("done.");
System.out.print("running test with execute(pre)...");
bug.doTest1(false);
System.out.println("done.");
System.out.print("tearing down...");
bug.teardown();
System.out.println("done.");
bug.conn.close();
}
}
---------------------------------------------------------------------------------------
The server log when run with --serverloglevel debug5. You can see in
the ANALYZE output the different sizes of the temp table. Other than
that I don't see anything interesting.
LOG: execute <unnamed>: DROP TABLE joa
ERROR: table "joa" does not exist
STATEMENT: DROP TABLE joa
LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[])
LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0])
LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1])
LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2])
LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3])
LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4])
LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5])
LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6])
LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7])
LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8])
LOG: execute <unnamed>: INSERT INTO joa VALUES(10,
ARRAY[0,1,2,3,4,5,6,7,8,9])
LOG: execute <unnamed>: INSERT INTO joa VALUES(11,
ARRAY[0,1,2,3,4,5,6,7,8,9,10])
LOG: execute <unnamed>: INSERT INTO joa VALUES(12,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11])
LOG: execute <unnamed>: INSERT INTO joa VALUES(13,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12])
LOG: execute <unnamed>: INSERT INTO joa VALUES(14,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13])
LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION
insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$
BEGIN
FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP
execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' ||
thearr[i] || ');';
END LOOP;
END;
$$ language 'plpgsql' VOLATILE
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
LOG: execute <unnamed>: ANALYZE japlc
LOG: execute <unnamed>: SELECT count(*) FROM japlc
LOG: execute <unnamed>: ABORT
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
LOG: execute <unnamed>: ANALYZE japlc
LOG: execute <unnamed>: SELECT count(*) FROM japlc
LOG: execute <unnamed>: ABORT
LOG: execute <unnamed>: DROP TABLE joa
LOG: execute <unnamed>: select logtime from eventlog order by id desc
limit 1
LOG: execute <unnamed>: DROP TABLE joa
ERROR: table "joa" does not exist
STATEMENT: DROP TABLE joa
LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[])
LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0])
LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1])
LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2])
LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3])
LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4])
LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5])
LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6])
LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7])
LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8])
LOG: execute <unnamed>: INSERT INTO joa VALUES(10,
ARRAY[0,1,2,3,4,5,6,7,8,9])
LOG: execute <unnamed>: INSERT INTO joa VALUES(11,
ARRAY[0,1,2,3,4,5,6,7,8,9,10])
LOG: execute <unnamed>: INSERT INTO joa VALUES(12,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11])
LOG: execute <unnamed>: INSERT INTO joa VALUES(13,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12])
LOG: execute <unnamed>: INSERT INTO joa VALUES(14,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13])
LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION
insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$
BEGIN
FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP
execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' ||
thearr[i] || ');';
END LOOP;
END;
$$ language 'plpgsql' VOLATILE
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
LOG: execute <unnamed>: ANALYZE japlc
LOG: execute <unnamed>: SELECT count(*) FROM japlc
LOG: execute <unnamed>: ABORT
LOG: execute <unnamed>: BEGIN
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
LOG: execute <unnamed>: ANALYZE japlc
LOG: execute <unnamed>: SELECT count(*) FROM japlc
LOG: execute <unnamed>: ABORT
LOG: execute <unnamed>: DROP TABLE joa
LOG: execute <unnamed>: DROP TABLE joa
ERROR: table "joa" does not exist
STATEMENT: DROP TABLE joa
LOG: execute <unnamed>: set client_min_messages = debug5
LOG: execute <unnamed>: set log_min_messages = debug5
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125327/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: CREATE TABLE joa (jobid int, uids int[])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125328/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: CREATE TABLE joa (jobid int, uids int[])
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125328/1/8, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125329/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(1, ARRAY[0])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125329/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125330/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(2, ARRAY[0,1])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125330/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125331/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(3, ARRAY[0,1,2])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125331/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125332/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(4, ARRAY[0,1,2,3])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125332/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125333/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(5, ARRAY[0,1,2,3,4])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125333/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125334/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(6, ARRAY[0,1,2,3,4,5])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125334/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125335/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(7, ARRAY[0,1,2,3,4,5,6])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125335/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125336/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(8, ARRAY[0,1,2,3,4,5,6,7])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125336/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125337/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(9, ARRAY[0,1,2,3,4,5,6,7,8])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125337/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(10,
ARRAY[0,1,2,3,4,5,6,7,8,9])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125338/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(10,
ARRAY[0,1,2,3,4,5,6,7,8,9])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125338/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(11,
ARRAY[0,1,2,3,4,5,6,7,8,9,10])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125339/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(11,
ARRAY[0,1,2,3,4,5,6,7,8,9,10])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125339/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(12,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125340/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(12,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125340/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(13,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125341/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(13,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125341/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: INSERT INTO joa VALUES(14,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13])
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125342/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: INSERT INTO joa VALUES(14,
ARRAY[0,1,2,3,4,5,6,7,8,9,10,11,12,13])
DEBUG: ProcessQuery
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125342/1/2, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: CREATE OR REPLACE FUNCTION
insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$
BEGIN
FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP
execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' ||
thearr[i] || ');';
END LOOP;
END;
$$ language 'plpgsql' VOLATILE
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125343/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: CREATE OR REPLACE FUNCTION
insert_to_japlcu_table(thearr int[], jobid int) returns void AS $$
BEGIN
FOR i IN array_lower(thearr,1)..array_upper(thearr,1) LOOP
execute 'INSERT INTO japlc (jobid, uid) VALUES( ' || jobid || ',' ||
thearr[i] || ');';
END LOOP;
END;
$$ language 'plpgsql' VOLATILE
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125343/1/3, nestlvl: 1, children: <>
DEBUG: parse <unnamed>: BEGIN
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125344/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: BEGIN
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
DEBUG: ProcessUtility
DEBUG: parse <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
DEBUG: parse <unnamed>: ANALYZE japlc
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: ANALYZE japlc
DEBUG: ProcessUtility
DEBUG: analyzing "pg_temp_12.japlc"
DEBUG: "japlc": scanned 1 of 1 pages, containing 1 live rows and 0 dead
rows; 1 rows in sample, 1 estimated total rows
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: SELECT count(*) FROM japlc
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: SELECT count(*) FROM japlc
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: ABORT
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: ABORT
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: BEGIN
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125345/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: BEGIN
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: CREATE TEMP TABLE japlc (jobid int, uid int)
DEBUG: ProcessUtility
DEBUG: parse <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: SELECT insert_to_japlcu_table(uids, jobid)
FROM joa
DEBUG: parse <unnamed>: ANALYZE japlc
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: ANALYZE japlc
DEBUG: ProcessUtility
DEBUG: analyzing "pg_temp_12.japlc"
DEBUG: "japlc": scanned 1 of 1 pages, containing 105 live rows and 0
dead rows; 105 rows in sample, 105 estimated total rows
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: SELECT count(*) FROM japlc
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: SELECT count(*) FROM japlc
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: ABORT
DEBUG: StartTransactionCommand
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: ABORT
DEBUG: ProcessUtility
DEBUG: CommitTransactionCommand
DEBUG: parse <unnamed>: DROP TABLE joa
DEBUG: StartTransactionCommand
DEBUG: StartTransaction
DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR,
xid/subid/cid: 26125346/1/0, nestlvl: 1, children: <>
DEBUG: bind <unnamed> to <unnamed>
LOG: execute <unnamed>: DROP TABLE joa
DEBUG: ProcessUtility
DEBUG: drop auto-cascades to toast table pg_toast.pg_toast_1214500
DEBUG: drop auto-cascades to type pg_toast.pg_toast_1214500
DEBUG: drop auto-cascades to index pg_toast.pg_toast_1214500_index
DEBUG: drop auto-cascades to type joa
DEBUG: CommitTransactionCommand
DEBUG: CommitTransaction
DEBUG: name: unnamed; blockState: STARTED; state: INPROGR,
xid/subid/cid: 26125346/1/12, nestlvl: 1, children: <>
DEBUG: proc_exit(0)
DEBUG: shmem_exit(0)
DEBUG: exit(0)