Falling PostgreSQL 9.5.3 when calling Google maps API

[Vadim0908]

The server periodically falls . The query itself is executed and returns the result .

LOG: server process (PID 13062) was terminated by signal 11: Segmentation fault
016-07-05 11:05:57 UTC [13070-1] replica@[unknown] WARNING: terminating connection because of crash of another server process
016-07-05 11:05:57 UTC [13070-2] replica@[unknown] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnor
ally and possibly corrupted shared memory.

The example is taken from the documentation:

DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSION www_fdw;
CREATE SERVER www_fdw_server_geocoder_google FOREIGN DATA WRAPPER www_fdw
OPTIONS (uri 'maps.googleapis.com/maps/api/geocode/xml?sensor=false',
response_type 'xml', response_deserialize_callback 'test_response_deserialize_callback');

CREATE USER MAPPING FOR current_user SERVER www_fdw_server_geocoder_google;
CREATE FOREIGN TABLE www_fdw_geocoder_google (
/* parameters used in request /
address text,
/ fields in response */
"type" text,
"formatted_address" text,
"lat" text,
"lng" text,
"location_type" text
) SERVER www_fdw_server_geocoder_google;
CREATE OR REPLACE FUNCTION test_response_deserialize_callback(options WWWFdwOptions, response xml)
RETURNS SETOF www_fdw_geocoder_google AS $$
DECLARE
rows RECORD;
address text;
type text;
formatted_address text;
lat text;
lng text;
location_type text;
r RECORD;
BEGIN
RAISE INFO 'options parameter: %', options;
RAISE INFO 'response parameter: %', response;
FOR rows IN SELECT unnest(xpath('/GeocodeResponse/result', response)) LOOP
type := (xpath('/result/type/text()', rows.unnest))[1];
formatted_address := (xpath('/result/formatted_address/text()', rows.unnest))[1];
lat := (xpath('/result/geometry/location/lat/text()', rows.unnest))[1];
lng := (xpath('/result/geometry/location/lng/text()', rows.unnest))[1];
location_type := (xpath('/result/geometry/location_type/text()', rows.unnest))[1];
r := ROW(address, type, formatted_address, lat, lng, location_type);
RETURN NEXT r;
END LOOP;
END; $$ LANGUAGE PLPGSQL;

postgres=# select * from www_fdw_geocoder_google where address='1600 Amphitheatre Parkway,Mountain View, CA';

Extension does not work on 9.5.3? Or www_fdw does not work with streaming replication?

[robe2]

I'm wondering if this is the same issue I am running into. I was testing 9.6 and it would crash often. So I tried 9.5.3 and also had crashing. The server I use www_fdw heavily on is 9.4, and that doesn't have any crashing.

I traced the crash and it's failing on line 1451
``
*opts_value = HeapTupleGetDatum( BuildTupleFromCStrings(aim, options) );

Here is the backtrace from my mingw64 install
``
PostgreSQL 9.5.2 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev1, Built by MinGW-W64 project) 4.8.3, 64-bit

``

``
[Switching to Thread 19844.0x2900]
0x000007feff9d59e0 in strlen () from C:\Windows\system32\msvcrt.dll
(gdb) bt
#0 0x000007feff9d59e0 in strlen () from C:\Windows\system32\msvcrt.dll
#1 0x000000000076a25d in cstring_to_text (
s=0x7f7f7f7f7f7f7f7f <error: Cannot access memory at address 0x7f7f7f7f7f7f7f7f>) at varlena.c:142
#2 0x0000000000795f9f in InputFunctionCall (flinfo=0x4c2a738,
str=, typioparam=25, typmod=-1) at fmgr.c:1913
#3 0x000000000058c64c in BuildTupleFromCStrings (attinmeta=0x4c2a4f0,
values=values@entry=0x27aeb30) at execTuples.c:1183
#4 0x00000000699455a9 in get_www_fdw_options (
opts_value=, opts_type=,
opts=0x4c6dbe8) at src/www_fdw.c:1451
#5 www_begin (node=0x4c1f5a0, eflags=) at src/www_fdw.c:1765
#6 0x00000000005a56c8 in ExecInitForeignScan (node=0x4c06f60,
estate=estate@entry=0x4c1f488, eflags=16) at nodeForeignscan.c:229
#7 0x00000000005837b4 in ExecInitNode (node=node@entry=0x4c06f60,
estate=estate@entry=0x4c1f488, eflags=eflags@entry=16)
at execProcnode.c:250
#8 0x0000000000581c3b in InitPlan (eflags=16, queryDesc=)
at execMain.c:957
#9 standard_ExecutorStart (queryDesc=, eflags=16)
at execMain.c:237
#10 0x0000000000698eaa in PortalStart (portal=0x27aeeb0,
portal@entry=0x4c093d8, params=0x4c06598, params@entry=0x0,
eflags=eflags@entry=0, snapshot=0x27af250, snapshot@entry=0x0)
at pquery.c:533
#11 0x0000000000696d77 in exec_simple_query (query_string=0x0)
at postgres.c:1065
#12 PostgresMain (argc=, argv=argv@entry=0x219458,
dbname=0x18001700160015 <error: Cannot access memory at address 0x18001700160015>, username=) at postgres.c:4030
#13 0x000000000063495d in BackendRun (port=0x27af400) at postmaster.c:4239
#14 SubPostmasterMain (argc=argc@entry=3, argv=argv@entry=0x127e70)
at postmaster.c:4729
#15 0x00000000007d9078 in main (argc=3, argv=0x127e70) at main.c:205

``

With the mountain view example above sometimes it finishes but often it crashes - but yields the same location of failure as what I have listed above.

[cyga]

@robe2 unfortunately I have no time to debug it now. But knowing the line, you can log parameters and dig the problem deeper.