Re: help implementing OGR Postgres foreign data wrapper

[Ben Madin]

Hi Brent,

not sure why, but it is working for me using ogrinfo:

~ $ ogrinfo
https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer\?SERVICE\=WFS\&REQUEST\=GetCapabilities
INFO: Open of `
https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer?SERVICE=WFS&REQUEST=GetCapabilities
'
  using driver `WFS' successful.
Metadata:
  ABSTRACT=To develop a marine environment classification for waters in
depths shallower than 3000 m within the outer boundary of the NZ 200 naut.
mile EEZ  (an OGC WFS Layer).
  TITLE=WFS
1: BOMEC_15_Class_WFS:BOMEC_15_Class (title: BOMEC_15_Class) (Multi Surface)

not very helpful, but a 400 error might be some kind of encoding problem
with request?

cheers

Ben



On Tue, 5 Oct 2021 at 14:18, Brent Wood  wrote:

> Hi,
>
> Apologies, this not strictly a Postgres question, but Postgres is so
> fundamental to what I'm trying to do and this list is generally so helpful,
> so here I go...
>
> I'm using FDW's to create virtual tables providing access to data from
> external data sources. This is working well for data in other Postgres
> databases using the Postgres FDW.
>
> I also want to be able to link to data from WFS services via the OGR FDW (
> https://www.pgxn.org/dist/ogr_fdw/)
> The first service I'm trying to connect to is:
>
> https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer
>
> I can retrieve the capabilities doc in a browser (test 1 passed!) and plot
> the data as a layer in QGIS (test 2 passed). My next step is to test OGR
> access via ogrinfo. This I can't seem to get working. I'm assuming I need
> to sort this out before trying to get the OGR_FDW working.
>
> I've tried:
> ogrinfo
> https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer
> ogrinfo
> https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer
> ?SERVICE=WFS&REQUEST=GetCapabilities
>
> I can only get the following response
> *ogrinfo
> https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer?SERVICE=WFS&REQUEST=GetCapabilities
> *
> *[1] 3121502*
> *ERROR 1: HTTP error code : 400*
> *ERROR 1: HTTP error code : 400*
> *FAILURE:*
> *Unable to open datasource
> `https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer?SERVICE=WFS
> '
> with the following drivers.*
>
> *  -> ESRIC *
> *...*
> *  -> OGR_PDS*
> *  -> WFS*
>
> *  -> OAPIF *
>
> *... *
>
> Can anyone suggest what might be the problem?
>
> Thanks.
>
> Brent Wood
>
> Principal Technician, Fisheries
> NIWA
> DDI:  +64 (4) 3860529
> 
> Brent Wood
> Principal Technician - GIS and Spatial Data Management
> Programme Leader - Environmental Information Delivery
> +64-4-386-0529
>
> National Institute of Water & Atmospheric Research Ltd (NIWA)
> 301 Evans Bay Parade Hataitai Wellington New Zealand
> *Connect with NIWA:* niwa.co.nz  Facebook
>  LinkedIn
>  Twitter
>  Instagram
>  To ensure compliance with legal
> requirements and to maintain cyber security standards, NIWA's IT systems
> are subject to ongoing monitoring, activity logging and auditing. This
> monitoring and auditing service may be provided by third parties. Such
> third parties can access information transmitted to, processed by and
> stored on NIWA's IT systems
>


-- 

[image: Ausvet Logo] 

Dr Ben Madin

BVMS MVPHMgmt PhD MANZCVS GAICD
Managing Director
Mobile:
+61 448 887 220 <+61448887220>
E-mail:
b...@ausvet.com.au
Website:
www.ausvet.com.au
Skype: benmadin
Address:
5 Shuffrey Street
Fremantle, WA 6160
Australia

Re: help implementing OGR Postgres foreign data wrapper

[Brent Wood]

Thanks Ben,

How is life treating the AusVet these days? Been quite a few versions under the 
bridge since we discussed R and Postgres stuff. 2009 was it??

That kicked some quiescent brain cells into gear. I just tried it from home, 
where it also works fine, if a bit sluggish, which seems to be how ESRI 
supports open standards.

So it may be some network settings from NIWA causing the failure. At least I 
can kick that problem elsewhere!!!


Much appreciated.


Brent Wood

Principal Technician, Fisheries
NIWA
DDI:  +64 (4) 3860529


From: Ben Madin 
Sent: Tuesday, October 5, 2021 20:57
To: Brent Wood 
Cc: pgsql-general@lists.postgresql.org 
Subject: Re: help implementing OGR Postgres foreign data wrapper

Hi Brent,

not sure why, but it is working for me using ogrinfo:

~ $ ogrinfo 
https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer\?SERVICE\=WFS\&REQUEST\=GetCapabilities
INFO: Open of 
`https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer?SERVICE=WFS&REQUEST=GetCapabilities'
  using driver `WFS' successful.
Metadata:
  ABSTRACT=To develop a marine environment classification for waters in depths 
shallower than 3000 m within the outer boundary of the NZ 200 naut. mile EEZ  
(an OGC WFS Layer).
  TITLE=WFS
1: BOMEC_15_Class_WFS:BOMEC_15_Class (title: BOMEC_15_Class) (Multi Surface)

not very helpful, but a 400 error might be some kind of encoding problem with 
request?

cheers

Ben



On Tue, 5 Oct 2021 at 14:18, Brent Wood 
mailto:brent.w...@niwa.co.nz>> wrote:
Hi,

Apologies, this not strictly a Postgres question, but Postgres is so 
fundamental to what I'm trying to do and this list is generally so helpful, so 
here I go...

I'm using FDW's to create virtual tables providing access to data from external 
data sources. This is working well for data in other Postgres databases using 
the Postgres FDW.

I also want to be able to link to data from WFS services via the OGR FDW 
(https://www.pgxn.org/dist/ogr_fdw/)
The first service I'm trying to connect to is:
https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer

I can retrieve the capabilities doc in a browser (test 1 passed!) and plot the 
data as a layer in QGIS (test 2 passed). My next step is to test OGR access via 
ogrinfo. This I can't seem to get working. I'm assuming I need to sort this out 
before trying to get the OGR_FDW working.

I've tried:
ogrinfo 
https://dservices3.arcgis.com/fp1tibNcN9mbExhG/arcgis/services/BOMEC_15_Class_WFS/WFSServer
ogrinfo 
https://dservices3.arcgis.com/fp1t

Re: Growth planning

[Israel Brewster]

Just a quick update to this topic from my testing: I whipped up a quick python 
script to create the partition tables for me, which went smoothly enough, and 
created a table LIST partitioned on station. Once populated with my current 
data, this table proved marginally faster than the unpartitioned table, 
especially on the initial select (~4 seconds vs ~6 seconds, speeding up to 
around 2.4 seconds on subsequent queries). Of course, it is entirely possible 
that performance will remain higher than with the unpartitioned table as the 
column count grows. 

Then I tried partitioning by station, with the station tables sub-partitioned 
by channel, on the logic that most queries (especially the ones that need to be 
fast) are only interested in a single channel on a single station. This made a 
HUGE improvement (relatively speaking). Initial query time dropped to ~2.5 
seconds, with subsequent queries coming in at closer to 1 second!

I’ll have to think about the maintenance side - what happens if a new 
station/channel comes online that I don’t have a partition for? I’m thinking 
try to catch the error in my python code when I try to insert such a record, 
create the relevant table(s), then try the INSERT again, but I’ll have to 
investigate more to figure out if this is an option (what sort of error do I 
get), or if there is a better one.

I guess time will tell if this is a sustainable/good schema, but at least for 
the 1,171,575,191 rows I currently have, this gives much better performance 
than the non-partitioned table, and presumably will continue to do so into the 
future.

Of course, if anyone else has any other suggestions other than simple 
partitioning, I’m all ears! Like I said, this is far outside my experience in 
terms of sheer data size (I will be talking to the timescaledb people tomorrow)!

Thanks again!
---
Israel Brewster
Software Engineer
Alaska Volcano Observatory 
Geophysical Institute - UAF 
2156 Koyukuk Drive 
Fairbanks AK 99775-7320
Work: 907-474-5172
cell:  907-328-9145

> On Oct 4, 2021, at 12:46 PM, Ron  wrote:
> 
> On 10/4/21 12:36 PM, Israel Brewster wrote:
> [snip]
>> Indeed. Table per station as opposed to partitioning? The *most* I can 
>> reasonably envision needing is to query two stations, i.e. I could see 
>> potentially wanting to compare station a to some “baseline” station b. In 
>> general, though, the stations are independent, and it seems unlikely that we 
>> will need any multi-station queries. Perhaps query one station, then a 
>> second query for a second to display graphs for both side-by-side to look 
>> for correlations or something, but nothing like that has been suggested at 
>> the moment.
>> 
> 
> Postgresql partitions are tables.  What if you partition by station (or range 
> of stations)?
> 
> -- 
> Angular momentum makes the world go 'round.

type bug?

[ml]

Hello,

there is a bug with types in functions. Here is an example:

--drop type  xyz; 
create type xyz as ( x numeric,  y numeric,  z numeric );

--drop table  test_xyz ; 
create table test_xyz (  a int,  b xyz, c xyz );
insert into  test_xyz values
( 1,(11.1, 12.3, 14.5 ), (22.2, 22.3, 24.5 ) ) ;

--drop  function test_xyz() ;
create function test_xyz() returns table ( a xyz, b xyz)  as
$$
 select b, c from test_xyz ;
$$ language sql ; 

--drop   function test_xyz2() ;
create function test_xyz2() returns table ( b xyz)  as
$$
 select  b from test_xyz  ;
$$ language sql ; 

--drop   function test_xyz3() ;
create function test_xyz3() returns table ( b xyz)  as
$$
declare
  bb xyz;
  cc xyz;
begin
  select b, c into bb, cc from test_xyz ;
  return bb ;
end;  
$$ language plpgsql ; 
-- -- 
select * from test_xyz() ; 
select * from test_xyz2() ; -- 3 columns, but 1 should be correct?
select b from test_xyz2() ; -- error
select x from test_xyz2() ; -- never declared column
select * from test_xyz3() ; -- compiling error
-- -- 

(I am not a member of the pg-developer mailinglist. )

Franz

Re: type bug?

[Ron]

What version of Postgresql do you think has the bug?
Where did you get the Postgresql binaries from?
What platform?

On 10/5/21 10:50 PM, m...@ft-c.de wrote:

Hello,

there is a bug with types in functions. Here is an example:

--drop type  xyz;
create type xyz as ( x numeric,  y numeric,  z numeric );

--drop table  test_xyz ;
create table test_xyz (  a int,  b xyz, c xyz );
insert into  test_xyz values
( 1,(11.1, 12.3, 14.5 ), (22.2, 22.3, 24.5 ) ) ;

--drop  function test_xyz() ;
create function test_xyz() returns table ( a xyz, b xyz)  as
$$
  select b, c from test_xyz ;
$$ language sql ;

--drop   function test_xyz2() ;
create function test_xyz2() returns table ( b xyz)  as
$$
  select  b from test_xyz  ;
$$ language sql ;

--drop   function test_xyz3() ;
create function test_xyz3() returns table ( b xyz)  as
$$
declare
   bb xyz;
   cc xyz;
begin
   select b, c into bb, cc from test_xyz ;
   return bb ;
end;
$$ language plpgsql ;
-- --
select * from test_xyz() ;
select * from test_xyz2() ; -- 3 columns, but 1 should be correct?
select b from test_xyz2() ; -- error
select x from test_xyz2() ; -- never declared column
select * from test_xyz3() ; -- compiling error
-- --

(I am not a member of the pg-developer mailinglist. )

Franz





--
Angular momentum makes the world go 'round.

Re: Query time related to limit clause

[Michael Lewis]

What's the definition for abc_serv_nch_q1_2021_expr_idx3? That is a jsonb
field I assume? Statistics aren't great on jsonb data, so you may benefit
greatly from pulling keys out to be stored as a standard column. I would be
curious for more "pure" estimates on each quarterly partition directly for
only the condition below (explain analyze, just looking at estimated vs
actual row counts) since they seem to be rather severe overestimates but
I'm not sure how much the date condition is obscuring that.

(((common_details -> 'commonDetails'::text) -> 'nchBundle'::text) ->>
'subGridName'::text) = '905811-22_MISCN_data'::text)"

The planner is choosing to use ONLY the index on the created timestamp when
limit is 1 and finding they nearly all match (I hope all on the q2
partition) and needs to filter almost all of those out (all from q1
partition I think, and nearly all from the others). I believe that the
planner thinks the other criteria in the query are not nearly as selective
as they are, and so it thinks it will find 1 match very quickly and be
done. That isn't the case.

When you want more rows, the planner decides that using both indexes is
less costly and it is correct.

Re: type bug?

[ml]

On Wed, 2021-10-06 at 00:21 -0500, Ron wrote:
> 
> What version of Postgresql do you think has the bug?
> Where did you get the Postgresql binaries from?
> What platform?
FreeBSD
pkg install postgresql ...
Version 13.3

a friend use Debian unstable with PG version 14 and have the same
problem.

> 
> On 10/5/21 10:50 PM, m...@ft-c.de wrote:
> > Hello,
> > 
> > there is a bug with types in functions. Here is an example:
> > 
> > --drop type  xyz;
> > create type xyz as ( x numeric,  y numeric,  z numeric );
> > 
> > --drop table  test_xyz ;
> > create table test_xyz (  a int,  b xyz, c xyz );
> > insert into  test_xyz values
> > ( 1,(11.1, 12.3, 14.5 ), (22.2, 22.3, 24.5 ) ) ;
> > 
> > --drop  function test_xyz() ;
> > create function test_xyz() returns table ( a xyz, b xyz)  as
> > $$
> >   select b, c from test_xyz ;
> > $$ language sql ;
> > 
> > --drop   function test_xyz2() ;
> > create function test_xyz2() returns table ( b xyz)  as
> > $$
> >   select  b from test_xyz  ;
> > $$ language sql ;
> > 
> > --drop   function test_xyz3() ;
> > create function test_xyz3() returns table ( b xyz)  as
> > $$
> > declare
> >    bb xyz;
> >    cc xyz;
> > begin
> >    select b, c into bb, cc from test_xyz ;
> >    return bb ;
> > end;
> > $$ language plpgsql ;
> > -- --
> > select * from test_xyz() ;
> > select * from test_xyz2() ; -- 3 columns, but 1 should be correct?
> > select b from test_xyz2() ; -- error
> > select x from test_xyz2() ; -- never declared column
> > select * from test_xyz3() ; -- compiling error
> > -- --
> > 
> > (I am not a member of the pg-developer mailinglist. )
> > 
> > Franz
> > 
> > 
> > 
> 

Re: type bug?

[David G. Johnston]

On Tuesday, October 5, 2021,  wrote:

>
> select * from test_xyz2() ; -- 3 columns, but 1 should be correct?
> select b from test_xyz2() ; -- error
> select x from test_xyz2() ; -- never declared column


All three of these stem from:

https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS

If the function is defined to return a composite type, the table function
produces a column for each attribute of the composite type.

Table(composite) == “setof composite”. (Though it seems to not be
explicitly documented in that way, it explains the observed behavior)

David J.

Re: type bug?

[David G. Johnston]

On Tuesday, October 5, 2021,  wrote:

>
> create function test_xyz3() returns table ( b xyz)  as
> $$
> declare
>   bb xyz;
>   cc xyz;
> begin
>   select b, c into bb, cc from test_xyz ;
>   return bb ;
> end;
> $$ language plpgsql ;
> -- --
>
> select * from test_xyz3() ; -- compiling error
>
>
Help others help you by writing out exact error messages (many of us can
answer from just reading code and error messages without needing be able to
execute said code ourselves).  I suspect “table test_xyz not found” due to
the typo.

David J.

Re: type bug?

[Pavel Stehule]

st 6. 10. 2021 v 5:50 odesílatel  napsal:

> Hello,
>
> there is a bug with types in functions. Here is an example:
>
> --drop type  xyz;
> create type xyz as ( x numeric,  y numeric,  z numeric );
>
> --drop table  test_xyz ;
> create table test_xyz (  a int,  b xyz, c xyz );
> insert into  test_xyz values
> ( 1,(11.1, 12.3, 14.5 ), (22.2, 22.3, 24.5 ) ) ;
>
> --drop  function test_xyz() ;
> create function test_xyz() returns table ( a xyz, b xyz)  as
> $$
>  select b, c from test_xyz ;
> $$ language sql ;
>
> --drop   function test_xyz2() ;
> create function test_xyz2() returns table ( b xyz)  as
> $$
>  select  b from test_xyz  ;
> $$ language sql ;
>
> --drop   function test_xyz3() ;
> create function test_xyz3() returns table ( b xyz)  as
> $$
> declare
>   bb xyz;
>   cc xyz;
> begin
>   select b, c into bb, cc from test_xyz ;
>   return bb ;
> end;
> $$ language plpgsql ;
> -- --
> select * from test_xyz() ;
> select * from test_xyz2() ; -- 3 columns, but 1 should be correct?
> select b from test_xyz2() ; -- error
> select x from test_xyz2() ; -- never declared column
> select * from test_xyz3() ; -- compiling error
>

This is correct;

This should be:

create function test_xyz3() returns table ( b xyz)  as
$$
declare
  bb xyz;
  cc xyz;
  r record
begin
  -- this case is not supported, you cannot to assign to list of composite
variables
  -- select b, c into bb, cc from test_xyz ;
  select b, c into r;
  bb := r.b;
 -- you cannot to use return bb, because expected result type is (xyz) not
xyz
  return next;
end;
$$ language plpgsql ;

I am not sure, if you want to use RETURNS table(), because in this case
multi row result is expected. In this case you should to use RETURN NEXT
instead RETURN in PLpgSQL

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

Regards

Pavel




-- --
>
> (I am not a member of the pg-developer mailinglist. )
>
> Franz
>
>
>
>