Re: So, why shouldn't SET CONSTRAINTS set a transaction snapshot?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: So, why shouldn't SET CONSTRAINTS set a transaction snapshot?
Date: 2008-12-12 22:27:30
Message-ID: 11055.1229120850@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I wrote:
> The comment in PortalRunUtility asserts loudly that ConstraintsSetStmt
> MUST NOT have a transaction snapshot set before it is executed, but
> I confess that I don't see why not at the moment. We certainly can't
> have it not set a snap if it has any triggers to fire. Comments?

I looked into the CVS history, and found that the exclusion of
ConstraintsSetStmt appeared in my commit here:
http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/tcop/postgres.c.diff?r1=1.304;r2=1.305
pursuant to an earlier proposal here:
http://archives.postgresql.org/pgsql-hackers/2002-10/msg00457.php
but that proposal doesn't mention ConstraintsSetStmt, so I'm not
totally sure why I did that. I have a feeling that I just wanted
to have the principle that "SET doesn't cause the transaction snapshot
to become established" without distinguishing SET CONSTRAINTS from
GUC-variable SET. But we document SET CONSTRAINTS as a different
command anyway, so it's not clear that there's any point to treating it
the same for this. It clearly is necessary for GUC SET to not set the
snapshot, else SET TRANSACTION ISOLATION MODE wouldn't work properly.
It's less clear that there's much of a use-case for SET CONSTRAINTS.

There seem to be two ways we could fix this:

1. Always set a snapshot for SET CONSTRAINTS. This is a minus-one-liner
--- just remove it from the exclusion list in PortalRunUtility.

2. Have it set a snapshot only if it finds pending trigger events to
fire. This would only require another half dozen lines of code, but
it's certainly more complicated than choice #1.

The argument for #2 is mainly that it's certain not to create any
compatibility issues, should there be apps out there that are expecting
to be able to do SET CONSTRAINTS at the start of a transaction without
locking down the transaction snapshot. (The only way there could be
pending trigger events is if we already did a DML command in the current
xact, so the transaction snap has certainly been set.)

While it's not a lot of code, the case we're protecting seems like
a mighty narrow corner case that might very well not exist in the field
anyplace --- it's definitely not promised to work in the documentation.
Any strong opinions one way or the other?

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2008-12-12 22:51:39 Re: So, why shouldn't SET CONSTRAINTS set a transaction snapshot?
Previous Message Josh Berkus 2008-12-12 22:02:15 Re: psql commands for SQL/MED