Opened 6 years ago

Closed 6 years ago

#1797 closed defect (fixed)

validatetopology error when asking for column names

Reported by: jomarlla Owned by: strk
Priority: low Milestone: PostGIS 2.0.1
Component: topology Version: 2.0.x
Keywords: Cc:


Something is going wrong with the create temp table when one try to use select (validatetopology ('t1')).*;

This is ok.

s9=# select validatetopology ('t1');
(0 rows)

now the error:

s9=# select (validatetopology ('t1')).*;
ERROR:  relation "face_check" already exists
CONTEXT:  SQL statement "CREATE TEMP TABLE face_check ON COMMIT DROP AS SELECT face_id, topology.ST_GetFaceGeometry('t1', face_id) as geom, mbr FROM t1.face WHERE face_id > 0"
PL/pgSQL function "validatetopology" line 204 at EXECUTE statement

Change History (3)

comment:1 Changed 6 years ago by strk

Ouch. I think this is due to what could be considered like a bug (IMHO) of PostgreSQL, that is the fact that a set-returning function called with the syntax: (srf()).* gets expanded as multiple calls to the function (one per returning column) and results in actual calls rather than reusing the result from the function (I think the function is STABLE).

So, since the function creates a temporary table, with transaction lifetime, the second call finds the table already existing and bails out.

Not sure how could this be fixed, except maybe by avoiding the creation of the temporary table (which would slow things down considerably).

comment:2 Changed 6 years ago by strk

A quick talk with andres on #postgresql @freenode suggests there are a lot of performance implications too in the use of (srf()).* syntax. See these:

comment:3 Changed 6 years ago by strk

Resolution: fixed
Status: newclosed

r9895 in 2.0 r9896 in trunk

Note: See TracTickets for help on using tickets.