Re: BUG #11638: Transaction safety fails when constraints are dropped and analyze is done

Hi,

On 2014-10-10 18:14:33 +0000, cg(at)osss(dot)net wrote:
> The following bug has been logged on the website:
>
> Bug reference: 11638
> Logged by: Casey Allen Shobe
> Email address: cg(at)osss(dot)net
> PostgreSQL version: 9.3.5
> Operating system: Linux (RHEL 5)
> Description:
>
> Discovered on 8.4.5, reproducible on 9.3.5.
>
> The paste pretty much explains it all:
> http://pgsql.privatepaste.com/162682d176

Please attach - the paste will expire in a couple days. That makes
researching issues later quite annoying.

> When, within a transaction:
> * I drop a foreign key (or any) constraint.
> * Load some data to the table which won't fit the constraint.
> * Analyze the table.
> * Attempt to re-add the constraint which fails and should roll back the
> whole transaction.
>
> The constraint is still missing after rollback.
>
> If I take out the analyze step, it works as expected.

Yuck. This is ugly. The problem is this nice bit:

void
vac_update_relstats(Relation relation,
BlockNumber num_pages, double num_tuples,
BlockNumber num_all_visible_pages,
bool hasindex, TransactionId frozenxid,
{ MultiXactId minmulti)
...
if (pgcform->relhastriggers && relation->trigdesc == NULL)
{
pgcform->relhastriggers = false;
dirty = true;
}
...
/* If anything changed, write out the tuple. */
if (dirty)
heap_inplace_update(rd, ctup);
}

That's, like, a *seriously* bad idea. The current xact doesn't see a
trigger, so we remove relhastriggers (and similarly other relhas*
stuff). The kicker is that we do so *nontransactionally*.

That's fine enough for VACUUM because that doesn't run in a
transaction. But vac_update_relstats is also run for ANALYZE.

I've no time to think this through right now, but my current thinking is
that we should use a transactional update for ANALYZE.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services