[gdal-dev] PostgreSQL problems

[Even Rouault]

Le vendredi 14 octobre 2011 22:57:32, David J. Bakeman a écrit :
> Even Rouault wrote:
> > Le vendredi 14 octobre 2011 21:04:12, David J. Bakeman a écrit :
> > 
> > Try  ogrinfo --debug on  -ro PG:'dbname=mydb'
> 
> OK I am totally confused now here is the output:
> ogrinfo --debug -ro PG:'dbname=mydb'
> INFO: Open of `PG:dbname=mydb'
>        using driver `PostgreSQL' successful.

I asked for "--debug on", not just "--debug". The latter will not work as a 
debugging option : it will "eat" the following -ro.

So basically, it is as if you had type ogrinfo PG:'dbname=mydb' without the -
ro option, so in update mode

But that's interesting. Because actually when the PG driver cannot find any 
Postgis layer in a database opened as read-only, it returns as if there was an 
error (that behaviour might be discussed). Whereas when you open in update 
mode, it succeeds (the justification being that in update mode, you can create 
a new layer in an empty database).

All in all, it means that the driver doesn't recognize valid Postgis layers 
(that is to say tables who have a geometry column referenced in the 
geometry_column table) in your database.

You can try defining the environment variable PG_LIST_ALL_TABLES=YES as 
documented in http://gdal.org/ogr/drv_pg_advanced.html .

> 
> That's all though?  I expected it to give a list of the layers as it
> says in the driver docs.
> 
> By the way without the --debug it still says FAILURE: and list all of
> the drivers????
> 
> > and see  if something interesting shows up (and post the output to the
> > list if you don't find the solution by yourself)
> > 
> > It is really weird that it works with QGIS but not with OGR. That
> > eliminates the usual suspects, like insufficient rights on the postgis
> > "system" tables (geometry_columns, spatial_ref_sys), as a cause for the
> > failure.
> > 
> >> Sorry I was trying to limit the bandwidth I used I figured it was
> >> probably something really stupid and someone would just say you forgot
> >> to XX :<;
> >> 
> >> Anyway this is a test database so it really is named mydb and I copied
> >> the ogrinfo command from the PostgreSQL driver page for OGR.  I checked
> >> before I started to confirm that the version of gdal I'm using supports
> >> Postgresql and the ogrinfo --formats prints ->  "PostgreSQL"
> >> (read/write) so I believe that confirms it.
> >> so both ogrinfo -ro PG:'dbname=mydb' and ogrinfo -ro PG:"dbname='mydb'"
> >> and ogrinfo -ro PG:"dbname='mydb' user='dbakeman' port='5432'" return
> >> the same failure which is to print FAILURE: and a list of drivers which
> >> again includes postgresql.  I skipped the password part because I'm on a
> >> system where I'm happy to run the ident authentication locally at least.
> >> 
> >> Here's what happens if I do psql -d mydb
> >> 
> >> \d
> >> 
> >>                  List of relations
> >>    
> >>    Schema |       Name        | Type  |  Owner
> >> 
> >> --------+-------------------+-------+----------
> >> 
> >>    public | geography_columns | view  | dbakeman
> >>    public | geometry_columns  | table | dbakeman
> >>    public | products_swordxml | table | dbakeman
> >>    public | pxml_points       | view  | dbakeman
> >>    public | spatial_ref_sys   | table | dbakeman
> >>    public | sword_trap        | table | dbakeman
> >> 
> >> Which is a list of the tables I've created.
> >> 
> >> Eli Adam wrote:
> >>> David,
> >>> 
> >>>        It probably is a configuration or simple error, however, you've
> >>> 
> >>> not given much detail to let other people try to guess what it might
> >>> be.  Kyle asked for the output of your command.  Also, the exact
> >>> command (copied from terminal) may help too.  I looked at the OGR
> >>> PosrgreSQL/PostGIS format page and copied this:
> >>> 
> >>> PG:"dbname='databasename' host='addr' port='5432' user='x'
> >>> password='y'"
> >>> 
> >>> Could you try
> >>> 
> >>> ogrinfo PG:"dbname='mydb' host='localhost' port='5432' user='x'
> >>> password='y'"
> >>> 
> >>> from the host machine?
> >>> 
> >>> What do you get?
> >>> 
> >>> PostgreSQL requires very complex quoting if you have any CAPs, do you
> >>> have any caps in the db name, table, name, fieldname, etc?  With lack
> >>> of information, I'm making random guesses that may not be relevant.
> >>> I'm sure that if you provide detailed information, someone
> >>> knowledgeable will quickly help you (or at least that is what I often
> >>> observe on this list).
> >>> 
> >>> Bests, Eli
> >>> 
> >>> On Thu, Oct 13, 2011 at 5:10 PM, David J. Bakeman<dbakeman at comcast.net>
> > 
> > wrote:
> >>>> Mateusz Łoskot wrote:
> >>>>> On 13 October 2011 23:47, David J. Bakeman<dbakeman at comcast.net>
> > 
> > wrote:
> >>>>>> I'm on Fedora core 14 with gdal 1.7.3.
> >>>>>> 
> >>>>>> I setup postgre and created a spatial enabled database with a table
> >>>>>> that includes geometry.  I can connect using psql mydb.  However
> >>>>>> when I try ogrinfo -ro PG:dbname=mydb it fails saying no driver
> >>>>>> found.
> >>>>> 
> >>>>> ogrinfo --formats | grep -i post
> >>>> 
> >>>> ->   "PostgreSQL" (read/write)
> >>>> 
> >>>> I really think it's some configuration thing but I don't know what it
> >>>> is?
> >>>> 
> >>>>> and see if your GDAL/OGR installation has built-in PostgreSQL
> >>>>> support.
> >>>>> 
> >>>>> Best regards,
> >>>> 
> >>>> _______________________________________________
> >>>> gdal-dev mailing list
> >>>> gdal-dev at lists.osgeo.org
> >>>> http://lists.osgeo.org/mailman/listinfo/gdal-dev
> >> 
> >> _______________________________________________
> >> gdal-dev mailing list
> >> gdal-dev at lists.osgeo.org
> >> http://lists.osgeo.org/mailman/listinfo/gdal-dev
> 
> _______________________________________________
> gdal-dev mailing list
> gdal-dev at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/gdal-dev