From: Markus Schatten <markus.schatten@foi.hr>
Organization: Faculty of Organization and Informatics
To: pgsql-sql@postgresql.org
Subject: plpythonu + os.spawnv
Date: Sat, 8 Mar 2008 02:12:55 +0100
User-Agent: KMail/1.9.6
MIME-Version: 1.0
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200803080212.55150.markus.schatten@foi.hr>

Dear all,

I'm not sure if I'm at the right place to ask my question so please excuse =
me=20
if I'm not.

I'm trying to spawn a process from plpythonu using psql  8.1.11. The code i=
s=20
working fine from Python shell but if I put it into a plpythonu function it=
=20
seems like the process isn't starting at all.

The code I'm using is (with additional comment on the important parts):

CREATE OR REPLACE FUNCTION top_inline_query( VARCHAR( 50 ), TEXT )
	RETURNS TEXT
	AS $$
=09
import os
import re
import sets
import random

def f2vars( string ):
  vars_re =3D re.compile( r'[?][^-:,\]\[]*' )
  all_vars =3D vars_re.findall( string )
  set_vars =3D sets.Set( all_vars )
  for i in all_vars:
    if i.startswith( '?_' ):
      try:
        set_vars.remove( i )
      except:
        pass
 =20
  return list( set_vars )

''' THE SPAWN FUNCTION '''
def run(program, *args):
    return os.spawnv(os.P_WAIT, program, (program,) + args)


def f2payser( query, module, path ):
    ''' THE PROCESS TO BE RUN '''
    florahome =3D '/path/to/flora2/runflora' #where runflora is a shell scr=
ipt=20
that runs a shell-like environment (take a look at http://flora.sf.net)

 =20
  quote_re =3D re.compile( r"['][^']*[']" )
  quotes =3D quote_re.findall( query )
 =20
  for i in quotes:
    cp =3D i.replace( ' ', r'\s' )
    query =3D query.replace( i, cp ) =20
 =20
  query =3D query[ :-1 ].replace( ' ', '' )
 =20
  query_vars =3D f2vars( query )
  #print query_vars
 =20
 =20
  vars_print =3D ''
  for i in query_vars:
    vars_print +=3D ",%write('" + i + "=3D')@_io,%write(" + i + ")@_io,%nl@=
_io"
 =20
 =20
  randfilename =3D path + str( random.random() )
  while os.path.exists( randfilename ):
    randfilename =3D path + str( random.random() )
 =20
  '''THE QUERY STRING TO BE PASSED TO FLORA'''
  query_str =3D "-e \"['" + module + "'].\" -e \"%tell('" + randfilename=20
+ "')@_io," + query + vars_print + ",%nl@_io.\" -e \"_halt.\""
 =20
  plpy.info( florahome + ' ' + str( query_str ) )
  run(florahome, query_str)
 =20
  vars_re =3D re.compile( '([?].*)[=3D](.*)')
 =20
  result =3D {}
  results =3D []
 =20
  warning =3D False
  try:
    file =3D open( randfilename, 'r' )
  except:
    file =3D []

  for i in file:
    val_pair =3D vars_re.findall( i )
    if len( val_pair ) =3D=3D 1:
      if not ( val_pair[ 0 ][ 1 ].startswith( '(' ) and val_pair[ 0 ][=20
1 ].endswith( ')' ) and val_pair[ 0 ][ 1 ].find( '?_' ) !=3D -1 ):
        result[ val_pair[ 0 ][ 0 ] ] =3D val_pair[ 0 ][ 1 ]
      else:
        warning =3D True
    elif val_pair =3D=3D []:
      if result !=3D {}:
        if not warning:
          results.append( result )
          result =3D {}
        warning =3D False
       =20
    else:
      raise ValueError,'Syntax error!'
  try:
    os.remove( randfilename )
  except:
    pass
 =20
  res_copy =3D results[ : ]
  for i in res_copy:
    results.remove( i )
    if not i in results:
      results.append( i )
  return results

project =3D args[ 0 ]
query =3D args[ 1 ]

'''THE ORIGINAL QUERY COMMENTED OUT '''
#onto_query =3D 'SELECT top_export_flora2_ontology( \'%s\' ) AS ontology;' =
%=20
project

#ontology =3D plpy.execute( onto_query )[ 0 ][ 'ontology' ]

ontology =3D 'markus:person[ name->markus, surename->schatten ].\n'


path =3D '/path/to/chmoded/directory/to/store/files/in'
randfilename =3D path + str( int( random.random() * 1000000 ) ) + '.flr'
while os.path.exists( randfilename ):
  randfilename =3D path + str( int( random.random() * 1000000 ) ) + '.flr'

onto_file =3D open( randfilename, 'w' )
onto_file.write( ontology )
onto_file.close()
os.chmod( randfilename, 0755 )

module =3D randfilename[ :-4 ]


results =3D f2payser( query, module, path )

'''CLEAN UP'''
os.remove( randfilename )

return str( results )

  $$
  LANGUAGE plpythonu;

The funny thing is that the same code (as said before) works fine when call=
ed=20
from a Python environment so my question is (if I'm not missing something i=
n=20
the code) is there a difference in handling spawned processes in plpthonu=20
and "normal" python?

The query passed to FLORA reads an ontology file queries it and writes the=
=20
results to another file that is than read by the plpythonu function. I trie=
d=20
a lot of different approaches but none of them seems to be starting the FLO=
RA=20
process at all. It seem like plpythonu just runs over the spawn function=20
doing nothing continuing the execution of the rest of the code.

Any help would be very appreciated because I'm running out of ideas ;)

Best regards,

=2D-=20
Markus Schatten, MSc
=46aculty of Organization and Informatics
Vara=C5=BEdin, Croatia
e-mail: markus.schatten@foi.hr
http://www.foi.hr