On Wed, 30 Aug 2006 04:16:35 +0900, Ashley Moran wrote:

> Hi
> 
> I'm trying to write a tool that generates a really long SQL script  
> and passes it to psql, but I've hit a problem because my script is  
> too long to be sent to the stdin of psql.  This is the first time  
> I've run into the limit so it had me scratching my head for a while.   
> I've tried a load of tricks, even putting the lines in an array, eg:
> 
>    MAX_SUCCESSFUL_TIMES = 3047
> 
>    query = ["BEGIN WORK;"]
>    (MAX_SUCCESSFUL_TIMES + 1).times do
>      query << "INSERT INTO people (name) VALUES ('Fred');"
>    end
>    query << "COMMIT;"
> 
>    IO.popen("psql -U test test","r+") do |pipe|
>      query.each { |statement| pipe.puts statement }
>    end
> 
> but it still fails when the total length of commands exceeds the  
> limit (which by experiment I've found to be 128K on Mac OS X, hence  
> the specific number of times above).
> 
> What's the best solution to this.  I would like to stick to inter- 
> process communication, and avoid temporary files and rewriting it to  
> use DBD, if possible.  Or are they my only options?

Rewriting to DBI is not very difficult, so unless you have a reason other
than not wanting DBI calls to clutter your code or not wanting to
massively restructure your code, the following should work:

class DBI::DatabaseHandle
  #Takes a whole SQL script in a string
  #and executes it on the database.
  def batch(sql)
    sql=sql.split(";").delete_at(-1)
    sql.each{ |statement| self.do(statement) }
  end
end

in the sample code you gave above,
query.each {|statement|dbh.do(statement)}
should also work just fine.

Is it possible with the pipe solution that you need to read back the
output data so that pgsql doesn't back up its pipe buffer as Grennady
Bystritsky has suggested?

-- 
Ken Bloom. PhD candidate. Linguistic Cognition Laboratory.
Department of Computer Science. Illinois Institute of Technology.
http://www.iit.edu/~kbloom1/