1

I am working on a system to import pre-generated SQL files with data to build PostGIS raster tables. The step which creates the SQL file seems to be working fine and I can manually import the SQL files, but the automated code halts during processing and never exits cleanly.

The import process in question has worked before on previous runs. The main change between then and now is the switch from using separate threads to process multiple runs at once to using the Python multiprocessing module for processing. However, the import process was working for a short time with multiprocessing implemented, and I am not sure exactly what changed to stop it from working.

The following method contains all of the import processing related code. The function is called by a parent object which handles the process management. The function is called within a wrapper function in the parent object which is spawned by Pythons multiprocessing.Process#start. The parameters meta and parent are a dictionary containing information necessary for the import and the parent object instance respectively.

The SQL file being used for import was generated in a previous step which also used Python subprocessing to execute the raster2pgsql command on a GeoTIFF file. The GeoTIFF is approximately 1.5GB.

def importSQL(meta, parent):
    rasterId = meta['rasterId']
    parent.conn.execute(queries.update_raster_state, ("Importing SQL", rasterId))

    sqlFileName = meta['sqlFileName']
    env = os.environ.copy()
    env["PGPASSWORD"] = config.dbPassword()

    #Import SQL file
    log.info("importRaster#importSQL: Importing SQL to spatial table...")
    psql = subprocess.Popen(("psql", "-f", sqlFileName, "-U", config.dbUser(), config.dbName()), stdout=subprocess.PIPE, stderr=subprocess.PIPE, env=env)

    error = False
    for errorLn in psql.stderr:
        if "ERROR" in errorLn:
            error = True
            log.error(errorLn)
    #psql.wait()

    #Delete SQL file after complete
    log.info("importRaster#importSQL: Cleaning directories.")
    if os.path.exists(sqlFileName):
        os.remove(sqlFileName)

    return meta, not error

When it was functioning properly, the SQL import would run for a while, then the cleaning directories stage would post, and the function would return properly. There would also be a new table created from the import. Now, the process never exits. I watched top during processing and there was indeed a postgres process running at around 75% usage for several minutes, and then a dropoff in processing when the command presumably finished. However, after the processing dropoff on top, the process still does not continue to the cleaning directories stage and examination of the database shows that no new table was created.

Evan McCoy
  • 115
  • 2
  • 8
  • Does `psql` generate a lot of output? If so, [beware](https://docs.python.org/3/library/subprocess.html#subprocess.Popen.wait) that "[the process] will deadlock when using stdout=PIPE or stderr=PIPE and the child process generates enough output to a pipe such that it blocks waiting for the OS pipe buffer to accept more data." – unutbu Jan 14 '19 at 21:40
  • If you suspect this might be the problem, then you might try redirecting stdout to stderr: `psql = subprocess.Popen(..., stdout=subprocess.STDERR, stderr=subprocess.PIPE)`. Since you are iterating over `psql.stderr`, this should prevent the stdout pipe buffer from filling up and causing the process to deadlock. – unutbu Jan 14 '19 at 21:44
  • This would also explain why your code used to work, but suddenly stopped working. The process will only deadlock if too much is written to stdout. So your code might still work on small SQL files, but fail on larger ones. (That might be another way to test if this theory is possibly on the right track). – unutbu Jan 14 '19 at 22:02
  • @unutbu I'm currently working on testing for this case. Unfortunately, subprocess.STDERR and subprocess.DEVNULL are only present after Python 3.3. I have been meaning to convert to Python 3 anyway, and this is a good way to force me to. I'll get back to you once I make it through and can run the test. – Evan McCoy Jan 14 '19 at 22:05
  • Alternatively, if you don't really need the stdout output, you could instead just [redirect it to `os.devnull`](https://stackoverflow.com/q/11269575/190597). – unutbu Jan 14 '19 at 23:52
  • @unutbu I believe you were correct that the stdout pipe buffer was filling up. This wasn't an issue before, but something about running it with multiprocessing or perhaps the use of a slightly larger raster in this test compared to others sent it over the edge. I changed my stdout parameter to the return of "open(os.devnull, 'w')" so it can work with Python 2. I ran a stress test with 4 simultaneous raster imports and it executed fine. It seems to be stable now! If you want to write an answer post I'll accept it as the solution. Thank you for the help! – Evan McCoy Jan 15 '19 at 10:25

0 Answers0