title: AWS Glue preactions: Invalid operation: relation "stage_table" does not exist
body: <p>I'm trying to utilize AWS Glue dynamic frame writer preactions and postactions to perform a redshift upsert merge as described here: <a href="https://aws.amazon.com/premiumsupport/knowledge-center/sql-commands-redshift-glue-job/" rel="nofollow noreferrer">https://aws.amazon.com/premiumsupport/knowledge-center/sql-commands-redshift-glue-job/</a></p>
  <p>I've pretty much just copy/pasted the code from that page into my own Glue job, except I'm using <code>glueContext.write_dynamic_frame.from_options()</code> instead of <code>glueContext.write_dynamic_frame.from_jdbc_conf()</code>.</p>
  <p>It almost works, but everytime I run the job, it errors out during the writing of the dynamic frame with this error:</p>
  <blockquote>
  <p>Amazon Invalid operation: relation &quot;stage_table&quot; does not exist</p>
  </blockquote>
  <p>Here is the code that causes this error (the db table/column names have been changed for simplicity):</p>
  <pre><code>pre_query = 'begin; \
      drop table if exists stage_table; \
      create table stage_table as select * from target_table where 1=2; \
      end;'
  post_query = 'begin; \
      delete from target_table using stage_table \
          where target_table.identifier = stage_table.identifier; \
      insert into target_table select * from stage_table; \
      drop table stage_table; \
      end;'
  glueContext.write_dynamic_frame.from_options(
      frame=dy_out,
      connection_type='redshift',
      connection_options={
          'url': dburl,
          'preactions': pre_query,
          'dbtable': 'stage_table',
          'database': 'public',
          'user': dbuser,
          'password': dbpassword,
          'redshiftTmpDir': args['TempDir'],
          'postactions': post_query
      }
  )
  </code></pre>
  <p>It appears that the error message comes from the call to write_dynamic_frame.from_options(). In fact, if I manually add a stage_table to my database and then run the job, when it's finished, the stage_table no longer exists. So it seems like the preactions drop table is working, but the create table is not, perhaps due to some sort of glue timing issue.</p>
  <p>Note that I have, for the moment, hacked my way around this problem by:</p>
  <pre><code>1. creating a permanent version of the stage_table in my redshift db,
  2. changing my preaction to just truncate the stage_table instead of dropping and creating it, and
  3. changing my postaction to not drop the stage_table.
  </code></pre>
  <p>In other words, the following code <strong>DOES WORK</strong>, proving the functionality of the pre and post actions. But I would rather not have the stage tables residing permanently in my db.</p>
  <pre><code>pre_query = 'truncate table stage_table;'
  post_query = 'begin; \
      delete from target_table using stage_table \
          where stage_table.identifier = target_table.identifier; \
      insert into target_table select * from stage_table; \
      end;'
  glueContext.write_dynamic_frame.from_options(
      frame=dy_out,
      connection_type='redshift',
      connection_options={
          'url': dburl,
          'preactions': pre_query,
          'dbtable': 'stage_table',
          'database': 'public',
          'user': dbuser,
          'password': dbpassword,
          'redshiftTmpDir': args['TempDir'],
          'postactions': post_query
      }
  )
  </code></pre>