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

>> "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.

Thanks Tom will do.

> 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.

Agree -- it was meant to be a stopgap and don't really see a big need for
making pg_extension TOAST unless you plan to hold some sort of extension
revision history in those arrays
Which was what I thought might have been the original intention and reason
for not overwritting.

> 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.

That is our long term plan we just had some concerns about how to make the
upgrade manageable and had a couple with one being the one you mentioned.
All logged in this ticket

http://trac.osgeo.org/postgis/ticket/1831

So the extension config solution I have only works for extensions and just a
stop gap until we settle down on a more permanent solution.

Thanks for all your help,
Regina