Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

KeyError: (u'topology', u'topoelementarray') with PostGIS #91

Open
icholy opened this Issue Mar 19, 2014 · 18 comments

Comments

Projects
None yet
5 participants

icholy commented Mar 19, 2014

I'm trying to use dbtoyaml on a postgis table:

$ dbtoyaml -H localhost -U postgres -W -n public -t points gis_data
Password:

I get the following error:

Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in <module>
    load_entry_point('Pyrseas==0.7.1', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 263, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 173, in from_catalog
    self._link_refs(self.db)
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/database.py", line 138, in _link_refs
    db.types.link_refs(db.columns, db.constraints, db.functions)
  File "/usr/local/lib/python2.7/dist-packages/pyrseas/dbobject/dbtype.py", line 364, in link_refs
    assert self[(sch, typ)]
KeyError: (u'topology', u'topoelementarray')

The table has 2 columns: geometry and real.

@jmafc jmafc added the dbtoyaml label Mar 19, 2014

Owner

jmafc commented Mar 19, 2014

Please advise what version of PostGIS you're using (version of Postgres would help too). Also, do you get the same error when not using the -t option?

icholy commented Mar 19, 2014

postgres=# select version();

version | PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
postgres=# select PostGIS_full_version();

postgis_full_version | POSTGIS="2.1.1 r12113" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.8.0" LIBJSON="UNKNOWN" TOPOLOGY RASTER

I get the same error without -t

Owner

jmafc commented Mar 19, 2014

OK, so the source of the problem is (probably) a table or perhaps a function that uses the topology.topoelementary type. Can you please find out more about that table, function or other object, by examing the output of pg_dump -s?

icholy commented Mar 20, 2014

It's a fresh install and I created the table with

CREATE TABLE points (
    geom geometry,
    value real
);

here's the schema dump

--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: topology; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA topology;


ALTER SCHEMA topology OWNER TO postgres;

--
-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;


--
-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';


--
-- Name: postgis; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS postgis WITH SCHEMA public;


--
-- Name: EXTENSION postgis; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION postgis IS 'PostGIS geometry, geography, and raster spatial types and functions';


--
-- Name: postgis_topology; Type: EXTENSION; Schema: -; Owner: 
--

CREATE EXTENSION IF NOT EXISTS postgis_topology WITH SCHEMA topology;


--
-- Name: EXTENSION postgis_topology; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION postgis_topology IS 'PostGIS topology spatial types and functions';


SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: points; Type: TABLE; Schema: public; Owner: icholy; Tablespace: 
--

CREATE TABLE points (
    geom geometry,
    value real
);


ALTER TABLE public.points OWNER TO icholy;

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- PostgreSQL database dump complete
--
Owner

jmafc commented Mar 20, 2014

OK, I was misled by your use of -t into thinking there were other tables in your db. Then it's almost certain that the problem lies in how Pyrseas is dealing with PostGIS 2.1 (the last time I tried Pyrseas with PostGIS was at version 1.5, so I'll have to install 2.1 to check this out). Presumably the error occurs even if no table is created.

icholy commented Mar 20, 2014

You're right, I dropped the points table and got the same error.

Owner

jmafc commented Mar 27, 2014

OK, I've reproduced the problem after installing PostGIS, creating extension postgis and, more importantly, creating extension postgis_topology (the problem doesn't occur with just the former).

Owner

jmafc commented Mar 27, 2014

The problem seems to be with the TypeDict query. As it stands, no tuples are fetched, so the TypeDict is empty by the time we get to TypeDict.link_refs.

Owner

jmafc commented Mar 27, 2014

So the reason the TypeDict.query doesn't retrieve any tuples is because topology.topoelementarray is a type introduced by CREATE EXTENSION postgis_topology. The last part of the WHERE clause, namely,

              AND t.oid NOT IN (
                 SELECT objid FROM pg_depend WHERE deptype = 'e'
                              AND classid = 'pg_type'::regclass)

was put there to exclude types which are part of an extension. The error in TypeDict.link_refs is due to the constraint topology.dimensions so it seems we need to add a similar exclusion to the ConstraintDict.query.

kworr commented May 6, 2014

Can I chime in?

Traceback (most recent call last):
  File "/usr/local/bin/dbtoyaml", line 9, in <module>
    load_entry_point('Pyrseas==0.7.1', 'console_scripts', 'dbtoyaml')()
  File "/usr/local/lib/python2.7/site-packages/pyrseas/dbtoyaml.py", line 49, in main
    dbmap = db.to_map()
  File "/usr/local/lib/python2.7/site-packages/pyrseas/database.py", line 263, in to_map
    self.from_catalog()
  File "/usr/local/lib/python2.7/site-packages/pyrseas/database.py", line 173, in from_catalog
    self._link_refs(self.db)
  File "/usr/local/lib/python2.7/site-packages/pyrseas/database.py", line 138, in _link_refs
    db.types.link_refs(db.columns, db.constraints, db.functions)
  File "/usr/local/lib/python2.7/site-packages/pyrseas/dbobject/dbtype.py", line 400, in link_refs
    func = dbfuncs[(sch, fnc, arg)]
KeyError: (u'public', 'geography_recv', 'internal')

Looks like there are a number of PostGis quirks.

Owner

jmafc commented May 6, 2014

@kworr Sure, go ahead.

Owner

jmafc commented May 8, 2014

@kworr Was "looks like a number of quirks" the extent of your comments or did you have something else to contribute?

kworr commented Aug 1, 2014

Sorry, I didn't want to offend. I just faced a problem, searched a for a project bug database and found a bug that manifests in the same function and linked to the same third-party product versions (PostGIS 2.1 and PostgreSQL 9.3). I'm not feeling smart enough to understand what's going on by myself but I thought that pointing out another similar case would help someone solving it.

Was this solved? I'm researching schema versioning strategies for a project which relies heavily on PostGIS and find pyrseas very interesting. Do you need help solving this issue?

Owner

jmafc commented Feb 11, 2015

Richard, I haven't checked on this recently, but it seems it's still pending. @dvarrazzo has been doing work on implementing a topological sort of the database objects so that we could deal with all inter-object dependencies. IIRC last time we spoke he was done with the general refactoring but had to go through each of the unit tests and/or create additional ones. We surely welcome any help.

Thanks for letting me know. I've implemented similar topological dependency graph sorting before so I could probably help, but if @dvarrazzo has already gotten as far as it sounds my involvement might prove more counter-productive than productive for this particular feature at this particular time. I look forward to being able to help on some other issue as I start using Pyrseas, though. And do let me know if you need help after all, @dvarrazzo.

Contributor

dvarrazzo commented Feb 11, 2015

Hi @richardolsson @jmafc

sorry if I haven't given any more info. Unfortunately I haven't found any time to work on the topo-sort branch in the last months, and my current situation is not different.

If someone wants to pick up from where I've left I can give him a hand. Unfortunately I don't have time to work on the project now.

The state I've left the project is: I had a big database and I made sure I could dump and reload its schema only using the dependencies info. I cannot provide that schema for testing as it's sensible data but that part was working: feature-wise I think the branch is ok.

My further steps would have been:

  • make the test suite run again: I've refactored mercilessly the code so it's likely the test suite doesn't run either for some changed interface or because genuine bugs introduced by my edits;
  • get rid of the "optional attributes" and give Pyrseas object a more robust internal data model. This is arguably not related to the deptrack branch but we agreed it would be an improvement to the code base.

I've checked the status and my deptrack branch (https://github.com/dvarrazzo/Pyrseas/commits/deptrack) is in line with my laptop; I have some edits in my working copy (http://pastebin.com/YYmYCppD) which appear to be only related to the second point (it's about getting rid of stuff like owner = self.owner if hasattr(self, 'owner') else '' and make sure the attributes are always there instead).

Hope this helps, sorry for not being more helpful but I can definitely exchange some mail in case someone wants to pick up from where I've left.

Owner

jmafc commented Feb 11, 2015

Daniele, I'm going to spend some time each day during the next few weeks going over the test suite on my clone of your deptrack branch. I'll report later, hopefully by end of month.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment