st_estimated_extent returns error on empty table

[martins]

To reproduce:

create table t(gid serial); select addgeometrycolumn('t','g',4326,'GEOMETRY',2); vacuum analyze t; select st_estimated_extent('t','g');

ERROR: LWGEOM_estimated_extent: couldn't locate table within current schema

This is especially annoying for us, because we must execute estimated_extent inside a transaction.

Could the function be changed to return NULL instead of an error in this case?

[strk]

+1

See also #510

I'd also like to see that message reworded…

[mcayland]

I think the correct behaviour should be to mimic what "SELECT ST_Extent(foo) FROM table" returns on an empty table, and reduce the message to a WARNING.

[strk]

That'd be a NULL.

[strk]

Note that with current trunk the error message is this: ERROR: LWGEOM_estimated_extent: no statistics for "(current_schema)"."t"."g" (empty table ? did you run ANALYZE ?)

The condition is the same when the table is NOT empty but you didn't run analyze yet. Do we want to always return NULL, or try to tell the two cases apart ?

[martins]

Could this be fixed any time soon? We really need this functionality.

[strk]

The quickest would be producing a patch and attaching to this ticket, or paying someone to do that for you. Shouldn't take more than a couple of hours.

[strk]

Funded! I'm on it…

[strk]

Fixed by r8319 in trunk and r8317 in 1.7 branch.

Documentation would need to be tweaked to reflect the semantic change. Not sure which expressions to use there. Robe ? Speaking of which, I've seen we advertise "VACUUM ANALYZE" as being the command gathering the statistics while "ANALYZE" is enough (and tested now within regress/ticket.sql)

[robe]

you know you could update these things yourself. I guess I'd much rather have people run too much than too little as I don't want to hear their problems when they deleted or updated records. I think nowadays running vacuum analyze is just as fast because with the new changes in freespace map I forget which version — 8.4 or 9.0 — PostgreSQL is smart enough to know when a VACUUM is not necessary. So better to ere on the side of too much than too little.

I didn't know we had a 1.7 branch of PostGIS. When did this come about and why did no one inform me

[strk]

Oops, it was 1.5 of course. Ok with keeping "vaccum analyze". About noticing the difference introduced (NULL instead of exception) I can surely do that myself. Was just asking about a tip for where to look about properly encoding the version-dependent change (some other function that has an example of which docbook tags to use for that?)

[strk]

r8320 documents it in 1.5 branch (r8321 in trunk)

I found inspiration in ST_Extent, which changed return type in a given version.

Since we're discussing about boxes: this function returns box2d, ST_Extent returned box2d once and now returns box3d_extent, ST_3dExtent returns box3d.

[pramsey]

Something deeply wrong in this fix on OSX, will have to examine… fortunately the ticket regression picked it up.

[pramsey]

Fixed the OSX regression in 1.5 at r8985

[strk]

This change regressed in 2.0.0, see #3396