title: Python lambda calling Postgres function: not saving changes
body: <p>I have an AWS lambda, written in Python.</p>
  <p>It calls a PL/PGSQL function in my database that should get a number from a database table, increment it and return the incremented value.</p>
  <pre><code>@staticmethod
  def get_next_msg_id():
      query = &quot;select &lt;schema&gt;.&lt;function&gt;();&quot;
      db_conn = _db_engine.connect()
      cursor = db_conn.execute(text(query))
      message_id = cursor.fetchone()[0]
      cursor.close()
      db_conn.close()
      
      logger.debug(f&quot;Next message id: {message_id}&quot;)
      
      return message_id
  </code></pre>
  <p>The database connectivity from the lambda to Postgres is provided by SQLAlchemy and an AWS RDS proxy.</p>
  <p>The PL/PGSQL function looks like this:</p>
  <pre><code>CREATE OR REPLACE FUNCTION &lt;schema&gt;.&lt;function&gt;()
    RETURNS integer
    LANGUAGE plpgsql
  AS $function$
  DECLARE
      new_sequence_value  INTEGER;
  BEGIN
      -- Lock table.
      LOCK TABLE &lt;schema&gt;.&lt;table&gt; IN ROW EXCLUSIVE MODE;
  
      -- Increment; return the next number.
      UPDATE  &lt;schema&gt;.&lt;table&gt;
      AS      updated
      SET     sequence_value = (sequence_value + 1)
      RETURNING updated.sequence_value
          INTO new_sequence_value;
  
      RETURN new_sequence_value;
  END
  $function$
  ;
  </code></pre>
  <p>My understanding was that PL/PGSQL functions are &quot;self encapsulating&quot; and any changes should be saved as part of the implied transaction when the function is called (I've tried putting a &quot;COMMIT&quot; command in the function but Postgres won't allow it)</p>
  <p>However, when I call the function repeatedly as I loop through a set of records in the Python code - via repeated calls to get_next_msg_id() - I can see that the value returned from the function is not incrementing. I have checked the database and the value in . is not being updated.</p>
  <p>Example log output:</p>
  <pre><code>2020-12-21 13:42:33,599 - DEBUG - Next message id: 4559
  2020-12-21 13:42:48,772 - DEBUG - Next message id: 4559
  </code></pre>
  <p>I can see no error messages when the call to the PL/PGSQL function is made.</p>
  <p>Just wondered if anyone can see anything obviously wrong here. By the fact that I am calling the function - and it seems to be returning a value - I am confused as to why the value is not being updated. Is there some kind of caching going on?</p>
  <p>I'm using Postgres version 11.9 and Python 3.7.9.</p>
  <p>Many thanks for any assistance.</p>