Thread: (long) What's the problem?

Anybody have any ideas about a problem with this query?

urldb2=> EXPLAIN
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2->     substring(
urldb2->       urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2->     )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=23.50..23.50 rows=3 width=48)
   Sort Key: source
   ->  Seq Scan on indexscan  (cost=0.00..23.47 rows=3 width=48)
         Filter: (lower("substring"(urlhost(source), (rposition('www.'::text, (urlhost(source))::character varying) +
2)))~ '^q.*'::text) 
(4 rows)

OK, cost=23.50..23.50, should be a quickie. I'll EXPLAIN ANALYZE just to be safe:

urldb2=> EXPLAIN ANALYZE
urldb2-> SELECT
urldb2->   id,
urldb2->   source,
urldb2->   insertedby,
urldb2->   insertedon,
urldb2->   priority
urldb2-> FROM
urldb2->   indexscan
urldb2-> WHERE
urldb2->   lower(
urldb2->     substring(
urldb2->       urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
urldb2->     )
urldb2->   ) ~ '^q.*'
urldb2-> ORDER BY source;

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

Since EXPLAIN ANALYZE runs the query and then analyzes the results I haven't included that in this email because the
erroris the same. 

urlhost() is an immutable custom Pl/Perl function.
rposition() is a volatile custom C function.
indexscan has 614 tuples and only takes up 7 pages.
The load on this machine is zero when I attempt this, and no other processes are trying to use massive amounts of
resources.This is a P3 550 with 512MB of RAM. 

I can provide more information if needed.

Anybody have ideas about the problem?

--------------------------
David Olbersen
iGuard Engineer
11415 West Bernardo Court
San Diego, CA 92127
1-858-676-2277 x2152

"David Olbersen" <DOlbersen@stbernard.com> writes:
> urldb2=> EXPLAIN ANALYZE
> urldb2-> SELECT
> urldb2->   id,
> urldb2->   source,
> urldb2->   insertedby,
> urldb2->   insertedon,
> urldb2->   priority
> urldb2-> FROM
> urldb2->   indexscan
> urldb2-> WHERE
> urldb2->   lower(
> urldb2->     substring(
> urldb2->       urlhost( source ), rposition( 'www.', urlhost( source ) ) + 2
> urldb2->     )
> urldb2->   ) ~ '^q.*'
> urldb2-> ORDER BY source;

> server closed the connection unexpectedly

Get out your debugger and look at the core file left by the crashing
backend (should be $PGDATA/base/yourdbnumber/core, or some variant of
that name).  If you see no core file in $PGDATA/base/yourdbnumber,
restart the postmaster with "ulimit -c unlimited" and try again.

> rposition() is a volatile custom C function.

The odds are excellent that this is a bug in your custom C code.

            regards, tom lane