testes com PostgREST - ppKrauss/detect-template-region GitHub Wiki

temparariamente em http://pg.jats.science/

Conferindo ambiente

ps -ux | grep postgrest
  # returns the pid, with some description with date and the command (ex. ./postgrest postgrest.conf)
ls /etc/nginx/sites-available
  # shows the pg Nginx cofiguration file.
  # ... edit with nano and run service nginx restart
psql -h localhost -U postgres restest
  # to check tables... ex. 
  # select issn_l, count(*) as n from lib.issn_l group by 1 having count(*)>3 order by 2 desc;

Rodando queries

Configurações NGINX

Ver http://pg.jats.science/pg-nginx.conf

server {

	server_name pg.jats.science;
	root /var/www/pg/html;
	index index.html index.htm;

	location / {
		try_files $uri $uri/ @proxy;
	}

	location @proxy {

		rewrite # isC (Canonic ISSN) checker
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/is[cC]$  
		  /vwint_issn_isc?select=isc&issn=eq.$1$2   
		  break;
		rewrite 
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/is[cC]$
		  /vwint_issn_isc?select=isc&issn=eq.$1$2&dig8=eq.$3
		  break;

		rewrite # isN (ISSN) checker
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/is[nN]$
		  /vwint_issn_isn?select=isn&issn=eq.$1$2
		  break;
		rewrite
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/is[nN]$
		  /vwint_issn_isn?select=isn&issn=eq.$1$2&dig8=eq.$3
		  break;

		rewrite # N-to-C convertion (name to canonic, so ISNN to its ISSN-L)
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/[nN]2[cC]$
		  /vwint_issn_n2c?select=issn-l&issn=eq.$1$2
		  break;
		rewrite
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/[nN]2[cC]$
		  /vwint_issn_n2c?select=issn-l&issn=eq.$1$2&dig8=eq.$3
		  break;

		rewrite # N-to-Ns convertions (name to all names, the ISSN-L group)
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)/[nN]2[nN]s?$
		  /vwint_issn_n2ns?select=issns&issn=eq.$1$2
		  break;
		rewrite
		  ^/urn:issn:(\d\d\d\d)\-?(\d\d\d)([0-9xX])/[nN]2[nN]s?$
		  /vwint_issn_n2ns?select=issns&issn=eq.$1$2&dig8=eq.$3
		  break;


		rewrite # URN LEX-Diário Oficial
		  ^/urn:lex-do:br\;(?:rj|rio.janeiro)\;rio\.janeiro:(?:executivo|exe|legislativo|leg|judiciario|jud):materia:([0-9]+)$ 
		  /vw_content?select=urn_do,materia_id,content&materia_id=eq.$1  
		  break;


		rewrite # any other, is a table name with PostgREST-query 
		  ^/(.*)$ 
		  /$1 
		  break;

		proxy_pass  http://127.0.0.1:3000;
		proxy_set_header Host $host;
		proxy_set_header X-Real-IP $remote_addr;
		proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
		default_type  application/json;
		proxy_hide_header Content-Location;
		proxy_set_header  Connection "";
		proxy_http_version 1.1;
	}

}

Resultados


Example counting with [pgbench](https://www.postgresql.org/docs/current/static/pgbench.html):

```sh
echo "SELECT count(*) FROM lib.issn_l where issn>1234567;" \
  | pgbench  -h localhost -U postgres -d restest -t 500 -P 1 -f -

Results:

SELECT lib.issn_isC(2550839)
	latency average: 0.538 ms
	latency stddev: 0.123 ms

SELECT isc FROM vwint_issn_isc WHERE issn=2550839
	latency average: 0.338 ms
	latency stddev: 0.135 ms
	latency average: 0.213 ms
	latency stddev: 0.174 ms

SELECT lib.issn_N2Ns(2550839)
	latency1 average: 0.700 ms
	latency1 stddev: 0.291 ms
	latency2 average: 0.805 ms
	latency2 stddev: 0.210 ms

SELECT issns FROM vwint_issn_n2ns WHERE issn=2550839
	latency1 average: 1.333 ms
	latency1 stddev: 0.455 ms
	latency2 average: 1.526 ms
	latency2 stddev: 0.724 ms
	latency3 average: 1.415 ms
	latency4 stddev: 0.551 ms

SELECT lib.issn_N2Ns(1525003)
	latency average: 0.903 ms
	latency stddev: 0.227 ms

SELECT issns FROM vwint_issn_n2ns WHERE issn=1525003
	latency average: 6.926 ms
	latency stddev: 1.577 ms
	latency2 average: 5.557 ms
	latency2 stddev: 1.616 ms

The first ones are examples of "after some juggling", to obtain a equivalent result by direct quering...