title: Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
body: <p>In connection with <a href="https://stackoverflow.com/a/10032028/939860"><strong>this answer</strong></a> I stumbled upon a phenomenon I cannot explain.</p>
  <p>Version:<br />
  PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit</p>
  <p>Testbed:</p>
  <pre><code>CREATE TEMP TABLE t (
    id  integer
  , txt text
  , CONSTRAINT t_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
  );
  
  INSERT INTO t VALUES
    (1, 'one')
  , (2, 'two');
  </code></pre>
  <h3>1) <code>UPDATE</code> statement modifying multiple rows:</h3>
  <pre><code>UPDATE t
  SET    id = t_old.id
  FROM   t t_old
  WHERE (t.id, t_old.id) IN ((1,2), (2,1));
  </code></pre>
  <p>The above <code>UPDATE</code> works, though it expected it <strong>should not</strong>. The constraint is defined <code>INITIALLY IMMEDIATE</code> and I did not use <code>SET CONSTRAINTS</code>.</p>
  <p>Am I missing something or is this a (rather harmless) bug?</p>
  <h3>2) Data modifying CTE</h3>
  <p>Consequently, a data modifying CTE works, too. Though it fails with a <code>NOT DEFERRED</code> pk:</p>
  <pre><code>WITH x AS (UPDATE t SET id = 1 WHERE id = 2)
  UPDATE t SET id = 2 WHERE id = 1;
  </code></pre>
  <p>I quote the <a href="https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-MODIFYING" rel="nofollow noreferrer">manual on CTEs</a>:</p>
  <blockquote>
  <p>The sub-statements in <code>WITH</code> are executed concurrently with each other
  and with the main query. Therefore, when using data-modifying
  statements in <code>WITH</code>, the order in which the specified updates
  actually happen is unpredictable. All the statements are executed with
  the same <em>snapshot</em> (see <a href="https://www.postgresql.org/docs/current/mvcc.html" rel="nofollow noreferrer">Chapter 13</a>), so they cannot &quot;see&quot; each
  others' effects on the target tables.</p>
  </blockquote>
  <h3>3) Multiple UPDATE statements in one transaction</h3>
  <p>Without <code>SET CONSTRAINTS</code>, this fails with a UNIQUE violation - as expected:</p>
  <pre><code>BEGIN;
  -- SET CONSTRAINTS t_pkey DEFERRED;
  UPDATE t SET id = 2 WHERE txt = 'one';
  UPDATE t SET id = 1 WHERE txt = 'two';
  COMMIT;
  </code></pre>