Re: ERROR: tuple concurrently updated

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)net> writes:
> > Subject pretty much says it all. I've put up with this error in the
> > past when it has caused me trouble but it's now starting to hit our
> > clients on occation which is just unacceptable.
>
> Have you tracked down the exact scenario making it happen?

I think I might have confused two different issues, sorry. :/

> > If this is correct then the solution seems to be either add versioning
> > to the SysCache data,
>
> You have provided no evidence that that would fix anything at all.
> To my mind a "concurrently updated" failure is more likely to mean
> insufficient locking around update operations.

I havn't built a reliable test case yet but I *think* the tuple
concurrently updated problem is with an analyze being run inside of a
function and also being run by autovacuum. The SysCache stuff I was
thinking about previously was actually for another problem that I hadn't
seen in a long time (because I hadn't been doing a particular set of
operations, not because it's that difficult to have happen) but just ran
into again today:
ERROR: cache lookup failed for relation ...

I first started seeing this happen, iirc, when I created a function
which went against pg_class/pg_attribute/pg_type and used
pg_table_is_visible(). This function (and another which uses pg_class,
pg_constraint and pg_attribute) gets used over and over again from
another pl/pgsql function. Basically we are walking through a list of
tables pulling the column names and primary keys and then running some
checks on the tables. Anyhow, while this is running (and it takes
upwards of half an hour to run) other activity on the database (this
time it was creating a view in a seperate completely unrelated schema)
can cause the lookup failure which kills the long-running function
(which gets to be very frustrating...).

Thanks,

Stephen