I have a setup in which four client machines access> a Postgres database (8.1.1) running on a Linux box.> So, there are connections from each machine to the> database; hence, the Linux box has about 2 postgres> processes associated with each machine.>

I am using the JDBC driver> (postgresql-8.1-404­.jdbc3.jar) to talk to the> database. I am also using the Spring framework(1.2.2)> and Hibernate (3.0.5) on top of JDBC. I use Apache's

DBCP database connection pool (1.2.1).>

Now, there is one particular update that I make from> one of the client machines - this involves a> reasonably large object graph (from the Java point of> view). It deletes a bunch of rows (around 20 rows in> all) in 4-5 tables and inserts another bunch into the> same tables.>

When I do this, I see a big spike in the CPU usage> of postgres processes that are associated with ALL the> client machines, not just the one I executed the> delete/insert operation on. The spike seems to happen> a second or two AFTER the original update completes> and last for a few seconds.>

Is it that this operation is forcibly clearing some> client cache on ALL the postgres processes? Why is> there such an interdependency? Can I set some> parameter to turn this off?

This patch has broken a majority of the buildfarm.

Hmm. What's happening is that the record-function call creates a> reference-counted TupleDesc, and tracking of the TupleDesc is> assigned to the subtransaction resource owner because we're inside> an EXCEPTION-block subtransaction. But the pointer is held by the> function's eval_context which lives throughout the function call,> and so the free happens long after exiting the subtransaction, and> the resource owner code quite properly complains about this.>

In this particular case the worst consequence would be a short-term> memory leak, but I think there are probably variants with worse> problems, because anything done by a RegisterExprContext­Callback()> callback is equally at risk.>

I think the proper fix is probably to establish a new eval_context> when we enter an EXCEPTION block, and destroy it again on the way out.> Slightly annoying, but probably small next to the other overhead of> a subtransaction. Comments?

BTW, both of the CONTEXT lines are misleading. The WARNING happens> during exit from the begin-block, not entry to it; and the ERROR> happens after we've finished fooling with the result value. I'm> tempted to add a few more assignments to err_text to make this nicer.

And your normal query would be this:>

INSERT into mytable (id,value) values (1,"foo"),(2,"bar")­;>

Your new query would be like this:>

INSERT into mytable (id,value) values (1,"foo"),(2,"bar")­> RETURNING id;>

And you would get a result back with one column (id) and> two rows (the newly inserted keys). You can also return> other fields if you like, you're not limited to just the> generated keys.>

PGUSER=postgres pg_dumpall > /path/to/backup_dat­e.sql>

Running the script using crond creates the 0 byte files.>

But , If i run the script in console , it creates the file with> content.>

Any suggestion pls?

ok... fixed... the problem was that this code only let> num_temp_tablespace­s be greater than zero when we are in an> interactive command (eg. a SET command) but setting the guc from> postgresql.conf at startup time is not interactive so> num_temp_tablespace­s is zero and when i try to get the first temp> tablespace to use (MyProcPid % num_temp_tablespace­s) causes a floatin> exception (division by zero).>

+ if (source >= PGC_S_INTERACTIVE && IsTransactionState(­))> + {> + /*> + * Verify that all the names are valid tablspace names> + * We do not check for USAGE rights should we?> + */> + foreach(l, namelist)> + {> + char *curname = (char *) lfirst(l);> +> + if (get_tablespace_oid­(curname) == InvalidOid)> + ereport((source == PGC_S_TEST) ? NOTICE : ERROR,> + (errcode(ERRCODE_UN­DEFINED_OBJECT),> + errmsg("tablespace \"%s\" does not exist", curname)));> +> + num_temp_tablespace­s++;> + }> + }>

new patch added, with that piece of code refactored to let> num_temp_tablespace­s get a value greater than zero always that the guc> is setted, i also add some docs.>

the patch passes all 104 regression tests and all my tests as well...>

i think the patch is ready to be applied to HEAD, any committer want> to review it?>

-- > regards,> Jaime Casanova>

"Programming today is a race between software engineers striving to> build bigger and better idiot-proof programs and the universe trying> to produce bigger and better idiots.> So far, the universe is winning."> Richard Cook

-------------------­--------(end of broadcast)---------­------------------> TIP 5: don't forget to increase your free space map settings