cyga / www_fdw Goto Github PK

This is a feature request.

Now that PostgreSQL provides JSON[B] in recent versions, there's an opportunity to cut a dependency and use a single point of truth. If you want to do this and want help, please let me know.

Hi,

The latest relased version does not compile for PostgreSQL 9.5+, however git master does. Can you please wrap a new release, so that I can also update the RPMs?

Thanks!

Regards, Devrim

Hi, more of a question than an issue.

www_fdw sounded like the solution to what I needed, but unfortunately the Web API requires authorization. Is there a way to www_fdw and add authorization credentials to it?

The company I work for uses Microsoft Dynamics CRM 365 which is web-based, so we don't have access to the database directly. It has a Web API that is pretty well documented and works well. However, getting data out of it efficiently has been an issue. Something like www_fdw would be the most desirable/efficient solution.

Hello,

I am trying to see how I could use a fdw wrapper to manage a cluster of servers and have all the servers accessible via sql.

I am hesitating on the type of fdw that I should use (I don't even know if this is a good idea). I could use www_fdw and access web services on each servers or I could have each server make believe they are redis servers and use redis_fdw for example.

I was wondering what you think about www_fdw in this use case, since you started this project 2 years ago and must have a good idea of the pros&cons of this approach.

compared to the web services in the example of www_fdw, my web services could also be writable ("start a service", "change this configuration", ..) and I would probably need to be able to POST or PUT variables to the services.

Anyway, I would be very interested in knowing more about your experience with www_fdw. From the outside it looks very powerful to be able to wrap web services like you did. How does it look like from the inside after 2 years of usage ;-) ?

When I insert a row to a www foreign table, it said ERROR: cannot insert into foreign table "www_fdw_test_search". and the webservice cann't receive any data.

WHEN select, the web service server's console log window:

root@iZ250hk0d9wZ:/opt/www# node app
listening on port 1337
  <-- GET /fdw/?q=test&link=test1
## Hello World
  --> GET /fdw/?q=test&link=test1 404 10ms -

WHEN insert:
there are nothing in server's console log window.
How to deal with this problem.
thx.

DROP SERVER www_fdw_test_server CASCADE;
CREATE SERVER www_fdw_test_server FOREIGN DATA WRAPPER www_fdw 
    OPTIONS (uri 'http://101.201.77.171:1337/view/fdw/',
    uri_select 'http://101.201.77.171:1337/fdw/',
    uri_insert 'http://101.201.77.171:1337/fdw/',
    uri_delete 'http://101.201.77.171:1337/fdw/',
    uri_update 'http://101.201.77.171:1337/fdw/'
    );

CREATE USER MAPPING FOR postgres SERVER www_fdw_test_server;

CREATE FOREIGN TABLE www_fdw_test_search (
  title text,
  link text,
  snippet text,
  q text 
) SERVER www_fdw_test_server;

SELECT * FROM www_fdw_test_search WHERE q='test' AND link='test1';

INSERT INTO www_fdw_test_search VALUES('title', 'link', 'snippet', 'qqq');

This patch adds support for PostgreSQL 9.5 by adding a required parameter:
https://gist.github.com/mc-soi/9f31c6d62e85292f8ff5

A "#if (PG_VERSION_NUM >= 90500)" might be needed to make this code also compile with earlier versions.

want to use www_fdw extension of Postgre SQL in order to read data from web services. I used this command to create extension:

CREATE EXTENSION IF NOT EXISTS www_fdw CASCADE

but it gives me following error:

could not open extension control file "C:/Program Files (x86)/PostgreSQL/9.6/share/extension/www_fdw.control": No such file or directory

I am working in Windows.

Any idea whats the reason for error?

Great utility BTW. Trying to authenticate against an https web server to dish out some xml - can you advise if/how it can be done? I have tried the conventional curl stuff, but with no success.

Hi, I'm new to the postgresql community.
Your extension is the first one I wanted to try by installing from pgxn.

After hitting pgxn install www_fdw here's the error output I get.
In short - it fails, and I can't even tell is it because of something I did (or didn't do) or something else..

Other info: OS X 10.9.4 (laptop, not a server), PG version 9.4beta2.
Please let me know is there some other info I can provide. I'll be responsive to any request.

Any help is appreciated! Thanks

I could be mistaken, but doesn't look like the code has been updated to support 9.6 changes.
I'll take a crack at fixing and submit a pull request if I figure it out.

This is what I get for errors

src/www_fdw.c: In function 'www_get_foreign_paths':
src/www_fdw.c:644:38: error: incompatible type for argument 3 of 'create_foreignscan_path'
)); /* no fdw_private data /
^
In file included from src/www_fdw.c:20:0:
C:/MING641/projects/POSTGR1/rel/PG9~2.6W6/include/server/optimizer/pathnode.h:90:21: note: expected 'struct PathTarget *' but argument is of type 'double'
extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
^
src/www_fdw.c:644:38: error: incompatible type for argument 6 of 'create_foreignscan_path'
)); / no fdw_private data /
^
In file included from src/www_fdw.c:20:0:
C:/MING641/projects/POSTGR1/rel/PG9~2.6W6/include/server/optimizer/pathnode.h:90:21: note: expected 'Cost' but argument is of type 'struct List *'
extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
^
src/www_fdw.c:644:38: warning: passing argument 9 of 'create_foreignscan_path' from incompatible pointer type [enabled by default]
)); / no fdw_private data /
^
In file included from src/www_fdw.c:20:0:
C:/MING641/projects/POSTGR1/rel/PG9~2.6W6/include/server/optimizer/pathnode.h:90:21: note: expected 'struct Path *' but argument is of type 'struct List *'
extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
^
src/www_fdw.c:644:38: error: too few arguments to function 'create_foreignscan_path'
)); / no fdw_private data */
^
In file included from src/www_fdw.c:20:0:
C:/MING641/projects/POSTGR1/rel/PG9~2.6W6/include/server/optimizer/pathnode.h:90:21: note: declared here
extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
^
src/www_fdw.c: In function 'serialize_request_with_callback':
src/www_fdw.c:852:9: warning: format '%i' expects argument of type 'int', but argument 3 has type 'uint64' [-Wformat=]
ereport(ERROR,
^
src/www_fdw.c:862:9: warning: format '%i' expects argument of type 'int', but argument 3 has type 'uint64' [-Wformat=]
ereport(ERROR,
^
src/www_fdw.c: In function 'get_www_fdw_options':
src/www_fdw.c:1459:9: warning: format '%d' expects argument of type 'int', but argument 2 has type 'uint64' [-Wformat=]
ereport(ERROR,
^
src/www_fdw.c: In function 'get_www_fdw_post_parameters':
src/www_fdw.c:1526:9: warning: format '%d' expects argument of type 'int', but argument 2 has type 'uint64' [-Wformat=]
ereport(ERROR,
^
src/www_fdw.c: In function 'call_response_iterate_callback':
src/www_fdw.c:2016:9: warning: format '%i' expects argument of type 'int', but argument 3 has type 'uint64' [-Wformat=]
ereport(ERROR,
^
src/www_fdw.c: At top level:
src/www_fdw.c:167:13: warning: 'www_explain_foreign_scan' declared 'static' but never defined [-Wunused-function]
static void www_explain_foreign_scan(ForeignScanState *node, ExplainState *es);^

I don't understand why the clause "WHERE" don't filter my query. In fact the two queries give the same result. In my case I can divert the problem with a with but it does not seem to be the best solution.
SELECT * FROM pheno_table ;
SELECT * FROM pheno_table WHERE espece_id='765' ;
WITH REQ AS (SELECT * FROM pheno_table) SELECt * FROM REQ WHERE espece_id='765' ;
Do you have a solution to advise me to solve this problem with the SQL language ?

-- example
DROP SERVER IF EXISTS pheno_server CASCADE;

CREATE SERVER pheno_server
FOREIGN DATA WRAPPER www_fdw
OPTIONS (uri 'http://www.gdr2968.cnrs.fr/pheno_ws/ServEspece',response_type 'json');

CREATE FOREIGN TABLE pheno_table (espece_nom_scientifique text, espece_nom_vernaculaire text,espece_id text) SERVER pheno_server;

create user mapping for current_user server pheno_server;

I ran into an issue that cost me some small amount of sanity. :)

I wrapped my fdw server in another function that supported local temporal caching in order to minimize the number of repeat service invocations. It would work for some invocations and then it would start throwing a "One operand supposed to be column another constant" error. At first I thought it was data related, but then could reproduce it both working and not working with the exact same service invocation parameters. Recreating the proc would immediately restore the functionality for a time. In my proc I was invoking as:

select result into var1 from www_fdw_myservice where api_key = var2 and user_id = var3;

I'm new to pgsql, but I'm assuming that the above is legitimate for pgsql. My hypothesis is that the procedure is being optimized in such a way that it begins to violate the constraint checking within the fdw service. Explicitly defining the wrapping proc as volatile had no effect (as expected). However, switching to a dynamic execution seems to have eliminated the problem e.g.:

execute 'select result from www_fdw_myservice where api_key = $1 and user_id = $2' into var1 using var2, var3;

If it's not feasible to correct the issue, then there should probably be a note in the documentation so that others can avoid it.

I don't recall getting these warnings last time I compiled www_fdw, but I might have. The dlls compile and load and seem to work, so not sure if I should worry about these or not. This is compiling against PostgreSQL 9.4, though I doubt that matters.

static void www_explain_foreign_scan(ForeignScanState *node, ExplainState *es);
             ^
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g  -I/p
clude/libxml2 -I/usr/include/libxml2 -I. -I./ -Ic:/MING32~2/projects/POSTGR~1/rel/PG9~1.4W3/include/server -Ic:/MING32~2/projects/POSTGR~1/rel/PG9~1.4W3/include/internal -I.  -I/projects/curl/rel-curl-7.39.
2/projects/POSTGR~1/rel/PG9~1.4W3/lib/pgxs/src/MAKEFI~1/../../src/include/port/win32" -Ic:/MING32~2/projects/POSTGR~1/rel/PG9~1.4W3/include/server/port/win32  -c -o libjson-0.8/json.o libjson-0.8/json.c
libjson-0.8/json.c:459:5: warning: no previous prototype for 'act_uc' [-Wmissing-prototypes]
 int act_uc(json_parser *parser)
     ^
libjson-0.8/json.c:467:5: warning: no previous prototype for 'act_yb' [-Wmissing-prototypes]
 int act_yb(json_parser *parser)
     ^
libjson-0.8/json.c:475:5: warning: no previous prototype for 'act_cb' [-Wmissing-prototypes]
 int act_cb(json_parser *parser)
     ^
libjson-0.8/json.c:483:5: warning: no previous prototype for 'act_ce' [-Wmissing-prototypes]
 int act_ce(json_parser *parser)
     ^
libjson-0.8/json.c:489:5: warning: no previous prototype for 'act_ob' [-Wmissing-prototypes]
 int act_ob(json_parser *parser)
     ^
libjson-0.8/json.c:498:5: warning: no previous prototype for 'act_oe' [-Wmissing-prototypes]
 int act_oe(json_parser *parser)
     ^
libjson-0.8/json.c:507:5: warning: no previous prototype for 'act_ab' [-Wmissing-prototypes]
 int act_ab(json_parser *parser)
     ^
libjson-0.8/json.c:514:5: warning: no previous prototype for 'act_ae' [-Wmissing-prototypes]
 int act_ae(json_parser *parser)
     ^
libjson-0.8/json.c:522:5: warning: no previous prototype for 'act_se' [-Wmissing-prototypes]
 int act_se(json_parser *parser)
     ^
libjson-0.8/json.c:532:5: warning: no previous prototype for 'act_sp' [-Wmissing-prototypes]
 int act_sp(json_parser *parser)
     ^
libjson-0.8/json.c:764:5: warning: no previous prototype for 'json_print_mode' [-Wmissing-prototypes]
 int json_print_mode(json_printer *printer, int type, const char *data, uint32_t length, int pretty)
     ^
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g   -sh
son-0.8/json.o -Lc:/MING32~2/projects/POSTGR~1/rel/PG9~1.4W3/lib -Wl,--allow-multiple-definition  -Wl,--as-needed   -L/projects/curl/rel-curl-7.39.0w32gcc481/lib -L/projects/libxml/rel-libxml2-2.7.8w32gcc48

Is there a way to send custom headers to web services?

System: Debian Jessie 8.5.1
PostgreSQL 9.2 (as per https://www.postgresql.org/download/linux/debian/)

sudo -u postgres psql

Then, what should be a really simple example:

postgres=#
DROP EXTENSION IF EXISTS www_fdw CASCADE;
CREATE EXTENSION www_fdw;
CREATE SERVER www_service FOREIGN DATA WRAPPER www_fdw OPTIONS (uri 'http://api.ipify.org');
CREATE USER MAPPING FOR current_user SERVER www_service;
CREATE FOREIGN TABLE ips (ip text) SERVER www_service;
SELECT ip FROM ips;
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.
!> \q

Logfile:

LOG:  server process (PID 16079) was terminated by signal 11: Segmentation fault
DETAIL:  Failed process was running: SELECT ip FROM ips;
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
HINT:  In a moment you should be able to reconnect to the database and repeat your command.
LOG:  all server processes terminated; reinitializing
LOG:  database system was interrupted; last known up at 2017-02-27 12:56:40 GMT
LOG:  database system was not properly shut down; automatic recovery in progress
LOG:  redo starts at 0/17BAA38
LOG:  record with zero length at 0/17D17E0
LOG:  redo done at 0/17D17B0
LOG:  last completed transaction was at log time 2017-02-27 12:57:29.730642+00
LOG:  database system is ready to accept connections
LOG:  autovacuum launcher started

Seems to be linked with #28?

Hello!
I'm trying this with postgres 10.1 and thing is crushing. Any advise on how to debug/get to the bottom of this?
Many thanks, Alex

apvp_db=# CREATE FOREIGN TABLE www_fdw_wiki (search text, "limit" text, "Text" text, "Description" text, "Url" text) SERVER www_fdw_server_wiki;
CREATE FOREIGN TABLE
apvp_db=# select "Text",substring("Description",1,25)||'...',"Url" from www_fdw_wiki where search='postgres' and "limit"='2';
SSL SYSCALL error: EOF detected
The connection to the server was lost. Attempting reset: Failed.

I have a large csv file that I'm trying to load and it only processes the first line of the file. The file has no headers, and I haven't seen anything in the documentation about headers. Are headers required to load a csv file?

After installing Postgresql 9.3 on an Ubuntu 12.04 server (using the packages from postgresql.org's repository), I receive the following errors upon running make (after running make clean, export USE_PGXS=1):

src/serialize_quals.c: In function ‘serialize_node_with_children_callback_json’:
src/serialize_quals.c:92:2: error: expected expression before ‘do’

This error is thrown in several locations in serialize_quals.c where d(...) is called. I confirmed that I can downgrade to Postgresql 9.2 and successfully recompile www_fdw for it. Any thoughts?

Hi,

how can I pass a timeout value to www_fdw ?

Thanks

I can build the extension with 9.1 but with the new 9.2 beta I get the following error when trying to compile:

cp sql/www_fdw.sql sql/www_fdw--0.1.0.sql
gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.5  -c -o src/json_parser.o src/json_parser.c
gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.5  -c -o src/serialize_quals.o src/serialize_quals.c
gcc -g -O2 -fstack-protector --param=ssp-buffer-size=4 -Wformat -Wformat-security -Werror=format-security -fPIC -DLINUX_OOM_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I/usr/include/libxml2 -I. -I. -I/usr/include/postgresql/9.2/server -I/usr/include/postgresql/internal -D_FORTIFY_SOURCE=2 -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include/tcl8.5  -c -o src/www_fdw.o src/www_fdw.c
src/www_fdw.c:126:1: error: unknown type name ‘FdwPlan’
src/www_fdw.c: In function ‘www_fdw_handler’:
src/www_fdw.c:307:12: error: ‘FdwRoutine’ has no member named ‘PlanForeignScan’
src/www_fdw.c: At top level:
src/www_fdw.c:432:1: error: unknown type name ‘FdwPlan’
src/www_fdw.c: In function ‘www_plan’:
src/www_fdw.c:435:2: error: unknown type name ‘FdwPlan’
src/www_fdw.c:439:12: error: ‘FdwPlan’ undeclared (first use in this function)
src/www_fdw.c:439:12: note: each undeclared identifier is reported only once for each function it appears in
src/www_fdw.c:439:12: error: expected expression before ‘)’ token
src/www_fdw.c:440:9: error: request for member ‘fdw_private’ in something not a structure or union
make: *** [src/www_fdw.o] Error 1

I am getting compilation errors that result in segfaulting postgres when trying the google example. The compilation errors and segfaults occur when using Ubuntu 14.04.1 LTS and Postgresql 9.3. I don't get the errors and segfaults when using the same OS and Postgres 9.2. The errors are below:

src/www_fdw.c: In function âget_www_fdw_optionsâ:
src/www_fdw.c:1397:3: warning: implicit declaration of function âheap_deform_tupleâ [-Wimplicit-function-declaration]
heap_deform_tuple(*(SPI_tuptable->vals), SPI_tuptable->tupdesc, data, isnull);
^
src/www_fdw.c: In function âwww_iterateâ:
src/www_fdw.c:1995:3: warning: implicit declaration of function âheap_copytupleâ [-Wimplicit-function-declaration]
tuple = heap_copytuple(reply->tuples[reply->tuple_index++]);
^
src/www_fdw.c:1995:9: warning: assignment makes pointer from integer without a cast [enabled by default]
tuple = heap_copytuple(reply->tuples[reply->tuple_index++]);
^

I do get other errors on both Postgres versions, mostly related to json; but they don't seem to affect the google example, or my use case.

If it helps, my test system is as follows:
A fresh install of Ubuntu 14.04.1 LST with automatic security updates.
Add Postgres repo. Use the following line to install software:
sudo apt-get install postgresql-9.3 postgresql-server-dev-9.3 libcurl4-openssl-dev libxml2-dev unzip make
I then download the master.zip to a folder in my home directory, unzip and make.
For testing against Postgres 9.2, I install a fresh OS and change the version numbers in the install line to 9.2.

If I can help track this down further, let me know.

When I CREATE EXTENSION www_fdw;
The postgresql said:

ERROR:  could not load library "/usr/lib/postgresql/9.3/lib/www_fdw.so": /usr/lib/postgresql/9.3/lib/www_fdw.so: undefined symbol: HeapTupleHeaderGetDatum
********** 错误 **********

ERROR: could not load library "/usr/lib/postgresql/9.3/lib/www_fdw.so": /usr/lib/postgresql/9.3/lib/www_fdw.so: undefined symbol: HeapTupleHeaderGetDatum
SQL 状态: 58P01

** 安装日志 **

root@937118f50889:~/www_fdw# make install
/bin/mkdir -p '/usr/lib/postgresql/9.3/lib'
/bin/mkdir -p '/usr/share/postgresql/9.3/extension'
/bin/mkdir -p '/usr/share/postgresql/9.3/extension'
/usr/bin/install -c -m 755  www_fdw.so '/usr/lib/postgresql/9.3/lib/www_fdw.so'
/usr/bin/install -c -m 644 www_fdw.control '/usr/share/postgresql/9.3/extension/'
/usr/bin/install -c -m 644 sql/www_fdw--0.1.8.sql '/usr/share/postgresql/9.3/extension/'
root@937118f50889:~/www_fdw# netstat -an | grep 5432 -c
127
root@937118f50889:~/www_fdw# ls /usr/lib/postgresql/9.3/lib/
ascii_and_mic.so     euc_kr_and_mic.so      pg_upgrade_support.so  utf8_and_cyrillic.so  utf8_and_gb18030.so    utf8_and_sjis2004.so
cyrillic_and_mic.so  euc_tw_and_big5.so     pgxs                   utf8_and_euc2004.so   utf8_and_gbk.so        utf8_and_uhc.so
dict_snowball.so     latin2_and_win1250.so  plpgsql.so             utf8_and_euc_cn.so    utf8_and_iso8859.so    utf8_and_win.so
euc2004_sjis2004.so  latin_and_mic.so       tsearch2.so            utf8_and_euc_jp.so    utf8_and_iso8859_1.so  www_fdw.so
euc_cn_and_mic.so    libpqwalreceiver.so    utf8_and_ascii.so      utf8_and_euc_kr.so    utf8_and_johab.so
euc_jp_and_sjis.so   mongo_fdw.so           utf8_and_big5.so       utf8_and_euc_tw.so    utf8_and_sjis.so
root@937118f50889:~/www_fdw#

api changed between 9.5beta2 and 9.5rc1. I think a lot of fdws are broken including this one.

src/www_fdw.c: In function 'www_fdw_handler':
src/www_fdw.c:350:32: warning: assignment from incompatible pointer type [enabled by default]
     fdwroutine->GetForeignPlan = www_get_foreign_plan;
                                ^
src/www_fdw.c: In function 'www_get_foreign_paths':
src/www_fdw.c:624:11: warning: passing argument 8 of 'create_foreignscan_path' from incompatible pointer type [enabled by default]
           NIL)); /* no fdw_private data */
           ^
In file included from src/www_fdw.c:20:0:
c:/MING64~1/projects/POSTGR~1/rel/PG9~2.5W6/include/server/optimizer/pathnode.h:82:21: note: expected 'struct Path *' but argument is of type 'struct List *'
 extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
                     ^
src/www_fdw.c:624:11: error: too few arguments to function 'create_foreignscan_path'
           NIL)); /* no fdw_private data */
           ^
In file included from src/www_fdw.c:20:0:
c:/MING64~1/projects/POSTGR~1/rel/PG9~2.5W6/include/server/optimizer/pathnode.h:82:21: note: declared here
 extern ForeignPath *create_foreignscan_path(PlannerInfo *root, RelOptInfo *rel,
                     ^
src/www_fdw.c: In function 'www_get_foreign_plan':
src/www_fdw.c:659:5: error: too few arguments to function 'make_foreignscan'
     );
     ^
In file included from src/www_fdw.c:21:0:
c:/MING64~1/projects/POSTGR~1/rel/PG9~2.5W6/include/server/optimizer/planmain.h:46:21: note: declared here
 extern ForeignScan *make_foreignscan(List *qptlist, List *qpqual,
                     ^
src/www_fdw.c: At top level:
src/www_fdw.c:157:13: warning: 'www_explain_foreign_scan' declared 'static' but never defined [-Wunused-function]
 static void www_explain_foreign_scan(ForeignScanState *node, ExplainState *es);
             ^
src/www_fdw.c: In function 'www_get_foreign_plan':
src/www_fdw.c:660:1: warning: control reaches end of non-void function [-Wreturn-type]
 }

Hi

how can CSV parse / read with "www_fdw"?

Thx.

I want to use this with the Github API, but the Github API is rate limited to 5000 requests per hour and sadly, the queries that I need to do require more than 5000 requests. (It involves querying all the historical pull requests across several very active repositories.) Does this project have any sort of cache support, so that I could pull the results of 5000 requests into a Postgres table, wait an hour, and then update the table with 5000 more? It would also allow me to do more than one query per hour, which would be very helpful. :)

If the project doesn't support this, do you have any suggestions for how I could go about implementing this? Maybe point me at documentation? I'm familiar with Postgresql, but I've never used foreign data wrappers before.

I am interested in making calls to Stripe and I am curious if INSERT, UPDATE, DELETE are supported which would translate into a HTTP POST or DELETE request? If so, are there any examples of that?

using the api:
https://api.duckduckgo.com/api

When u visit:
http://api.duckduckgo.com/?q=test&format=json
u get data

When u use curl:
curl --get 'http://api.duckduckgo.com/?q=test&format=json'
u get data

When u use the fdw not?
For example:
CREATE SERVER www_fdw_server_duckduckgo FOREIGN DATA WRAPPER www_fdw
OPTIONS (uri 'http://api.duckduckgo.com/', response_type 'json');
CREATE USER MAPPING FOR current_user SERVER www_fdw_server_duckduckgo;
CREATE FOREIGN TABLE www_fdw_duckduckgo3 (
/* parameters used in request /
/ format is (json or xml)/
q text,
format text,
/ fields in response */
"Heading" text
) SERVER www_fdw_server_duckduckgo;

but:
select * from www_fdw_duckduckgo3 where q='test' and format='json';
will return no lines (0 lines, just the headers of the table). (and no errors)

debugging:
set client_min_messages to debug5;
did'nt improve

comparing with the wikimedia example, it doesnt seem to matter if all io columns are included,
the same result with more columns (ie no output)

So the given query should return data (as a browser or curl do when accessing the api),
but unfortunately no rows are returned.

Hi,

We recently switched to postgres 10 and www_fdw causes a postgres crash with:

2018-08-22 10:51:07.714 EDT,,,16723,,5b7c43a7.4153,119,,2018-08-21 12:53:59 EDT,,0,LOG,00000,"server process (PID 26661) was terminated by signal 11: Segmentation fault","Failed process was
running: select * from www_fdw.test_balance where retailerid = 1025",,,,,,,,""
2018-08-22 10:51:07.714 EDT,,,16723,,5b7c43a7.4153,120,,2018-08-21 12:53:59 EDT,,0,LOG,00000,"terminating any other active server processes",,,,,,,,,""
2018-08-22 10:51:07.721 EDT,,,16723,,5b7c43a7.4153,121,,2018-08-21 12:53:59 EDT,,0,LOG,00000,"all server processes terminated; reinitializing",,,,,,,,,""
2018-08-22 10:51:07.751 EDT,,,26730,,5b7d785b.686a,1,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"database system was interrupted; last known up at 2018-08-22 00:03:46 EDT",,,,,,,,,""
2018-08-22 10:51:07.911 EDT,,,26731,"10.20.2.39:61696",5b7d785b.686b,1,"",2018-08-22 10:51:07 EDT,,0,LOG,00000,"connection received: host=10.20.2.39 port=61696",,,,,,,,,""
2018-08-22 10:51:07.916 EDT,"postgres","postgres",26731,"10.20.2.39:61696",5b7d785b.686b,2,"",2018-08-22 10:51:07 EDT,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
2018-08-22 10:51:09.411 EDT,,,26733,"10.20.2.39:61699",5b7d785d.686d,1,"",2018-08-22 10:51:09 EDT,,0,LOG,00000,"connection received: host=10.20.2.39 port=61699",,,,,,,,,""
2018-08-22 10:51:09.422 EDT,"postgres","ioana_test",26733,"10.20.2.39:61699",5b7d785d.686d,2,"",2018-08-22 10:51:09 EDT,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
2018-08-22 10:51:10.103 EDT,,,26734,"10.20.2.39:61701",5b7d785e.686e,1,"",2018-08-22 10:51:10 EDT,,0,LOG,00000,"connection received: host=10.20.2.39 port=61701",,,,,,,,,""
2018-08-22 10:51:10.104 EDT,"postgres","ioana_test",26734,"10.20.2.39:61701",5b7d785e.686e,2,"",2018-08-22 10:51:10 EDT,,0,FATAL,57P03,"the database system is in recovery mode",,,,,,,,,""
2018-08-22 10:51:10.220 EDT,,,26730,,5b7d785b.686a,2,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"database system was not properly shut down; automatic recovery in progress",,,,,,,,,""
2018-08-22 10:51:10.228 EDT,,,26730,,5b7d785b.686a,3,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"redo starts at B/8BC9D448",,,,,,,,,""
2018-08-22 10:51:10.232 EDT,,,26730,,5b7d785b.686a,4,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"invalid record length at B/8BD07000: wanted 24, got 0",,,,,,,,,""
2018-08-22 10:51:10.232 EDT,,,26730,,5b7d785b.686a,5,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"redo done at B/8BD06FA8",,,,,,,,,""
2018-08-22 10:51:10.232 EDT,,,26730,,5b7d785b.686a,6,,2018-08-22 10:51:07 EDT,,0,LOG,00000,"last completed transaction was at log time 2018-08-22 10:50:54.762065-04",,,,,,,,,""

Environment:
CentOS Linux release 7.4.1708 (Core)
Postgres 10.4
www_fdw 0.1.9

I use response_deserialize_callback:

CREATE SERVER test_balance_server FOREIGN DATA WRAPPER www_fdw
OPTIONS (uri '', response_type 'other',
response_deserialize_callback 'www_fdw.test_balance_response_deserialize_callback',
ssl_cert '/data01/pki/server-cert.pem',
ssl_key '/data01/pki/server-key.pem',
cainfo '/data01/pki/ca-cert.pem',
proxy '');

and the crash happens only when I use a where condition. I noticed it crashes in the function serialize_request_parameters at:
List *quals = list_copy(node->ss.ps.qual);

and in the list_copy function at the line:
newlist = new_list(oldlist->type);

I use version 0.1.8 with postgres 9.4 and I have no issued with it.

Hany help is greatly appreciated.

Thanks,
Ioana

I am using www_fdw to query from twitter search API, here is the process that I followed:

CREATE extension www_fdw;
CREATE server www_fdw_server_twitter FOREIGN DATA Wrapper www_fdw OPTIONS
     (uri 'https://api.twitter.com/1.1/search/tweets.json',cainfo 'C:/SSLCerts/ca
    bundle.crt'); 
       CREATE USER MAPPING FOR current_user SERVER www_fdw_server_twitter;
        CREATE FOREIGN TABLE www_fdw_twitter (
        /* parameters used in request */
        q text,
        page text,
        rpp text,
        result_type text,
        /* fields in response */
        created_at text,
        from_user text,
        from_user_id text,
        from_user_id_str text,
        geo text,
        id text,
        id_str text,
        is_language_code text,
        profile_image_url text,
        source text,
        text text,
        to_user text,
        to_user_id text
        ) SERVER www_fdw_server_twitter;

When I want to query from my foreign table with following query:

select id,substr(text,1,30)||'...' from www_fdw_twitter where q='postgres';
I get this error:

ERROR: Can't find result in parsed server's json response

I am using postgreSQL 9.5 in window64.

I've been busy with it for several days, and before continuing, I must know if somebody else already passed this journey, to save my time:

In the beginning it didn't compile.
After fixing the compilation problems (which was easy), it crashed the server.
I had to fix 2 different bugs that each of them crashed the server (less easy).
Then I had to find a working example (all of the examples from the EXAMPLES page are obsolete, so I fixed 2 of them - for example the wiki one, where all I had to do is to replace the HTTP by HTTPS).
Now it compiles OK, runs, and I track the entire process, with zillion of logs and prints, and 90% of the way looks OK, but the final result is still bogus (e.g. SELECT returns 0 rows, even when the logs from the fdwroutine functions show more rows).

There is no documentation for porting FDW from old PG versions to the recent ones, so it's very difficult for me to continue, but I believe that www_fdw could make a real revolution IF if worked;
If many of the APIs in the world become automatically PG tables, it will be a real revolution, so I don't want to give up.

So anybody who passed this journey, but succeeded to run it under 13 or 14, please share your experience, tips, and maybe even code.

Thanks!

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?