[postgis-devel] Strange error on Postgis 1.5.1-Postgres 8.4.4

Andrea Peri aperi2007 at gmail.com
Fri Sep 3 06:15:24 PDT 2010 

Hi,

I'm having a strange situation.
I don't sure it is an error, but a query that seem simple after about 7
hours don't terminate so I stop it and because the log don't report
anything,
I decide to set max level of detail (debug5) and verbose information.

(I work on windows 7 - 64 bit with postgres 8.4.4. 32 bit and postgis 1.5.1)

The query is this:

INSERT INTO public._test_campionamento_mp (id,idrtt,geom) (
    select
        c.id,
        c.idrtt,
        st_force_2d(st_multi(st_union(c.geom)))
    from (
        select
            a.id as id,
            a.idrtt as idrtt,
            (ST_DumpPoints(a.geom)).* as geom
        from
            public._test_campionamento as a
    ) as c
    group by
        c.id,
        c.idrtt
);

See-ing the log I can notice that it initially don't report nothing about
some errors.
The log was like thi:

...
    SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: <NULL>,ST_MultiLineString
2010-09-03 14:41:43 CEST CONTEXT:  SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: {1},ST_LineString
2010-09-03 14:41:43 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
    SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: <NULL>,ST_MultiLineString
2010-09-03 14:41:43 CEST CONTEXT:  SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: {1},ST_LineString
2010-09-03 14:41:43 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
    SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: <NULL>,ST_MultiLineString
2010-09-03 14:41:43 CEST CONTEXT:  SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: {1},ST_LineString
2010-09-03 14:41:43 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
    SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: <NULL>,ST_MultiLineString
2010-09-03 14:41:43 CEST CONTEXT:  SQL function "st_dumppoints" statement 1
2010-09-03 14:41:43 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:41:43 CEST DEBUG:  00000: {1},ST_LineString
2010-09-03 14:41:43 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
...

but after few time it report an error and the execution of the query seem to
stop.

.....
2010-09-03 14:44:19 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
    SQL function "st_dumppoints" statement 1
2010-09-03 14:44:19 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:44:19 CEST DEBUG:  00000: <NULL>,ST_MultiLineString
2010-09-03 14:44:19 CEST CONTEXT:  SQL function "st_dumppoints" statement 1
2010-09-03 14:44:19 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:44:19 CEST DEBUG:  00000: {1},ST_LineString
2010-09-03 14:44:19 CEST CONTEXT:  funzione PL/pgSQL "_st_dumppoints" linea
22 a ciclo FOR su righe di SELECT
    SQL function "st_dumppoints" statement 1
2010-09-03 14:44:19 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:44:19 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:19 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182
2010-09-03 14:44:19 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:19 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182
2010-09-03 14:44:20 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:20 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182
2010-09-03 14:44:20 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:20 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182
2010-09-03 14:44:23 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:23 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182
2010-09-03 14:44:28 CEST DEBUG:  00000: my backend id is 3
2010-09-03 14:44:28 CEST LOCATION:  SharedInvalBackendInit,
.\src\backend\storage\ipc\sinvaladt.c:316
2010-09-03 14:44:28 CEST DEBUG:  00000: StartTransaction
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionState,
.\src\backend\access\transam\xact.c:4077
2010-09-03 14:44:28 CEST DEBUG:  00000: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionStateRec,
.\src\backend\access\transam\xact.c:4115
2010-09-03 14:44:28 CEST DEBUG:  00000: CommitTransaction
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionState,
.\src\backend\access\transam\xact.c:4077
2010-09-03 14:44:28 CEST DEBUG:  00000: name: unnamed; blockState:
STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionStateRec,
.\src\backend\access\transam\xact.c:4115
2010-09-03 14:44:28 CEST DEBUG:  00000: autovacuum: processing database
"template0"
2010-09-03 14:44:28 CEST LOCATION:  AutoVacWorkerMain,
.\src\backend\postmaster\autovacuum.c:1628
2010-09-03 14:44:28 CEST DEBUG:  00000: StartTransaction
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionState,
.\src\backend\access\transam\xact.c:4077
2010-09-03 14:44:28 CEST DEBUG:  00000: name: unnamed; blockState:
DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children:
2010-09-03 14:44:28 CEST LOCATION:  ShowTransactionStateRec,
.\src\backend\access\transam\xact.c:4115
2010-09-03 14:44:28 CEST DEBUG:  00000: pg_type: vac: 0 (threshold 107),
anl: 0 (threshold 78)
2010-09-03 14:44:28 CEST LOCATION:  relation_needs_vacanalyze,
.\src\backend\postmaster\autovacuum.c:2627
2010-09-03 14:44:28 CEST DEBUG:  00000: pg_statistic: vac: 62 (threshold
119), anl: 62 (threshold 85)
...

after this seem the running of the query will be stopped.
Because the log don't report nothing else about the query.
I don't understand if the error reported:

2010-09-03 14:44:19 CEST LOCATION:  exec_stmt_raise,
.\src\pl\plpgsql\src\pl_exec.c:2545
2010-09-03 14:44:19 CEST DEBUG:  00000: mapped win32 error code 2 to 2
2010-09-03 14:44:19 CEST LOCATION:  _dosmaperr, .\src\port\win32error.c:182

is on postgis or on postgres.

Any hints is welcome,

Thx

-- 
-----------------
Andrea Peri
. . . . . . . . .
qwerty àèìòù
-----------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20100903/5fae52e8/attachment.html>

More information about the postgis-devel mailing list