Re: BUG #7756: When upgrading postgis extension get row is too big: size 9272, maximum size 8160

I wrote:
> "Paragon Corporation" <lr(at)pcorp(dot)us> writes:
>> I assumed that the :
>> pg_catalog.pg_extension_config_dump
>> Calls would overwrite each subsequent for a given object for a given
>> extension. So I have that in my upgrade script as well should we add more
>> spatial_ref_sys records we want to avoid dumping.
>> It seems it just adds.

> It probably should overwrite --- this is something we simply didn't
> consider in the original coding.

> The other case I was considering is that ALTER EXTENSION DROP should
> probably remove any extconfig entry for a table that you disassociate
> from the extension.

I've committed patches to do the above in 9.1.8 and later.

However, since you'd probably like to update postgis before those
versions are universally installed, what I suggest as a workaround is to
have the extension update scripts do

UPDATE pg_extension SET extconfig = null, extcondition = null
WHERE extname = 'postgis';

before calling pg_extension_config_dump. Obviously, this wipes all your
config-dump data, so if you've got more than one configuration table
you'll need to be sure to do pg_extension_config_dump for each of them
in the update script.

BTW, I thought a bit about adding a TOAST table to pg_extension to
eliminate the limit on the size of extcondition, but didn't actually
do it. We could not make that happen in 9.1 or 9.2, so you'd have to
deal with the limit in any case. Furthermore, the coding technique
you've got here seems like a bad idea anyway. The way I'd suggest doing
it is to add a flag column to spatial_ref_sys so that the dump filter
condition can be simply "WHERE NOT standard_entry" or some such. That
way the labeling can be directly associated with your source data and
there's a lot less chance of failing to update the filter condition.

regards, tom lane