search

pietermartin / sqlg

TinkerPop graph over sql
MIT License
246 stars 51 forks source link

Load index information from information_schema #172

Closed pietermartin closed 7 years ago

pietermartin commented 7 years ago

The index information is not loaded from information_schema. SqlgStartupManager.loadSqlgSchemaFromInformationSchema needs to support loading the index meta data.

JPMoresmau commented 7 years ago

I started looking into this. This is not a problem for vertex indices, say. But what about the globally unique indices? How can we retrieve what columns they apply to? If I'm not mistaken, if the name is too long we just use a number, so we can't reparse that name to figure out the columns we index.

JPMoresmau commented 7 years ago

See https://github.com/JPMoresmau/sqlg/commit/3e8c7dff75f88b8d529e974e02cbdc58ed822711 for the current state. Doesn't handle GIN indices either.

pietermartin commented 7 years ago

Yeah, there is information loss. The same happens with primitive arrays where Sqlg can not tell if its a primitive array or java primitive object array. I don't have any solution for this problem and its bound to get worse as we capture more and more meta data in sqlg_schema

JPMoresmau commented 7 years ago

What's the use case? Is it to be able to survive as well as possible a deletion of sqlg_schema?

pietermartin commented 7 years ago

The primary usecase has been to ease upgrading Sqlg. With all the changes happening with the meta data management is has been easy to just drop sqlg_schema, restart and all is well as opposed to needing to execute db upgrade scripts.

I suppose that will eventually come to an end and we'll have to be more accurate with the upgrade path. Testing and documentation etc.

pietermartin commented 7 years ago

The other important usecase is that currently has no ability to delete/remove schema elements. The only way to remove them without manual intervention from sqlg_schema is to drop the sqlg_schema and recreate it.

So what happens currently in out production systems is that occasionally system engineers stop supporting parts of the app and to make space on disc they might decide to drop some tables and schemas. They know that when they do this they need to drop sqlg_schema and restart they system.

Ideally Sqlg will have an admin ui someday where they can do this as opposed to in pgadmin.

JPMoresmau commented 7 years ago

OK, maybe for global indices we could store the list of types/properties in the COMMENT for the table. It's PostgreSQL specific, though. I suppose adding the possibility to delete edges and vertices labels and delete the underlying tables would be excellent. Maybe create an enhancement issue for it?

pietermartin commented 7 years ago

Yeah, for now it will be great if indexes are supported but in general I don't think we can rely on recreating sqlg_schema from the information schema as a feature. Once we can completely manage the meta data I think we can drop this feature rather than have a partial solution.

There is already #137 for deletion and rename of the meta data.