Hi Alan,

So, if this file has only been added to your system now, although it was part of a revision that was included in your previous checkout, that suggests to me that SlikSVN was misbehaving.

Unlucky for you that what should have been a simple upgrade has caused you so much hassle. On the other hand, you've learnt a few more things about Indicia along the way. Fingers crossed it works better this time.

Jim Bacon

OK, I've successfully uploaded the new 0.8.2 code and the files that were missing before are now there.

I've also run the scripts indicated here

But running the database upgrade gives me this:

Alan

Hi Alan

Okay. first question is which update is this coming from? It might be listed in your log file but, a quick search for pg_attribute in the 0_8_2 database updates folder quickly shows it is 201303071000_term_field_size.sql. (We have cross-posted, I see.)

The update is very small

-- Hacky but efficient way to increase terms.term field size across all dependencies
update pg_attribute set atttypmod=204 where attname='term' and atttypmod=104 and atttypid=1043

which I can translate, thanks to the docs, as "change the length of any varchar field called 'term' to 200 characters in any table or view where it is currently 100 characters."

This update was presumably just added to your system so this is the first attempt to run it. It is an odd one as it is modifying the postgreSQL catalogue rather than the tables directly. I haven't seen this before. As the comment in the update says, "Hacky".

The fact that you are getting permission denied for trying to mess with the tables that postgres uses to organise itself is not all that surprising. More surprising is that my system has logged running the update successfully. If I look at the pg_attribute table (which is in the PostgrSQL catalog in the Indicia database) the update appears to have been ineffective, however.

I can suggest a workaround (running the query in pgAdmin as superuser and then updating the last_run_script to pretend it executed correctly) or you could just delete the update file and live with the current limit on the size of terms.

I guess the friendly

ALTER TABLE terms ALTER term TYPE character varying(200);

was rejected because, as the PostgreSQL documentation says

so it may just be a way to avoid updating the views that use it.

I'm not sure it is a good hack either because

select pgc.relname, pga.attname, pga.atttypid, pga.atttypmod from pg_attribute pga
join pg_class pgc on pgc.oid = pga.attrelid
where pga.attname = 'term'

suggests it affects things that are not related to 'terms.term'.

Also the query is trying to update from 100 to 200 characters in size and my system currently has a size of 150 so that wouldn't have worked anyway.

Perhaps John can tell us if there is any reason why we shouldn't do it the long way which would be to drop all the views, update the column and recreate all the views.

Sorry, Alan. I think this one is a problem on our side.

Jim Bacon.

Yup, I'm learning a lot too.
I'd no idea what that error message meant so I Googled it - probably you did too.
I found a post that says

Sure enough you see the reduce_precision function is declared

CREATE OR REPLACE FUNCTION reduce_precision(geom geometry, confidential boolean, sensitivity_precision integer, sref_system varchar (20))

and later the geom parameter is assigned a value

geom = st_transform(geom, sref_metadata.srid);

Now this does not throw an error on my system. Why not? Well I found PL/pgSQL had been changed to allow parameters to be assigned to.

The date of that change, compared to the release dates of PostgreSQL indicates that it would have been introduced with version 9.0. And that is the version I am using. So I am betting you are using an earlier version. Can you confirm that to make sure I am not barking up the wrong tree.

Jim Bacon.

Hi Alan

The documentation says that Indicia supports version 8.4 or later and I think I can rewrite that function in a couple of seconds.

Would you like to test the attached for us?

Jim Bacon.

Sorry, forgot to declare the variable geom.
Try again.

Finally!!!
Hopefully you can add and delete samples and see them appear and disappear from cache_occurrences now.

Jim Bacon