SQLAlchemy: Trigger not firing on INSERT
<p>I have a problem with a trigger that doesn't fire through a <code>.merge()</code> call in SQLAlchemy. <br>
I have the following trigger:</p>

<pre><code>CREATE TRIGGER trigger_update_comment_count
BEFORE INSERT ON mediacomment
FOR EACH ROW
EXECUTE PROCEDURE update_comment_count();
</code></pre>

<p>The trigger executes the following procedure:</p>

<pre><code>CREATE OR REPLACE FUNCTION update_comment_count() RETURNS trigger AS $BODY$
    BEGIN
        UPDATE channel_users
        SET comment_count = comment_count + 1,
        last_comment = NEW.created_time
        WHERE
        channel_id = (SELECT ch.channel_id FROM media m
                      INNER JOIN channel ch ON ch.user_id = m.user_id
                      WHERE m.media_id = NEW.media_id)
        AND user_id = NEW.user_id;

        RETURN NEW;
    END;
$BODY$ LANGUAGE plpgsql;
</code></pre>

<p>The trigger works great if I do a SQL-based INSERT like this:</p>

<pre><code>INSERT INTO medialike VALUES (999292999,'2016-01-01 00:00:00.000000',367668179,true,'770160534981089486_12336189'); 
</code></pre>

<p><strong>However</strong>, when I use the SQLAlchemy's <code>.merge()</code> method, a row is inserted but the trigger is not fired:</p>

<pre><code>com = MediaComment(
      is_follower=is_follower,
      comment_text=comment['txt'],
      comment_id_native=str(comment['id']),
      created_time=comment['time'],
      user_id=str(comment['id']),
      media_id=self.media_id
      )
self.session.merge(com)
self.session.commit()
</code></pre>

<p>Any ideas of what could cause this problem? <br>
I'm running PostgreSQL-9.4 and Python3</p>

<p>I figured it out by enabling logging and examining the log files in <code>pg_log</code>. The reason was that the transaction with the <code>INSERT</code>, that should have fired the trigger, was being rolled back.</p>

python postgresql sqlalchemy