Re: BUG #11638: Transaction safety fails when constraints are dropped and analyze is done - Mailing list pgsql-bugs

Hi,

On 2014-10-10 18:14:33 +0000, cg@osss.net wrote:
> The following bug has been logged on the website:
>
> Bug reference:      11638
> Logged by:          Casey Allen Shobe
> Email address:      cg@osss.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