trying to query greenplum DB using the postgres connector not working about querytree HOT 6 CLOSED

Hi Mark - from what you’ve said I suspect there might be something about the GreenPlum DB table definitions that’s confusing QueryTree. I’ll download the GPDB docker image and do some investigation.

from querytree.

Ok, so I ran the Greenplumb docker image, created a table named test (in the gpadmin schema)

create table test
(
  id          serial (10) default nextval('test_id_seq':: regclass ) not null
  constraint test_pkey
  primary key,
  name        varchar(50),
  description varchar(250)
);

and connected QueryTree to it, and all seems well:

Maybe there's something specific in your schema that's causing a problem. Perhaps you could create a new schema, point QueryTree at it, and then add some of your tables, checking QueryTree each time, until you find the one that causes QueryTree to fall over. Then you could send me the SQL and we can figure out why QueryTree doesn't like it.

from querytree.

Hello

Thanks so much for your prompt support, I think the difference is that I'm using greenplum 4.3.25 (there are two streams of greenplum still) where you possibly used the 5 stream, my version is based on PostgreSQL 8.3, your version I think is based on 8.4 and the catalogs may be different, in particular I think pg_enum is different or something like that on greenplum 4.3x stream

Anyway I believe the difference should be minimal as some tools, example redash, can see the tables of both greenplum streams

I also have several schemas, if you think that could be the issue, could you kindly point me to where I can setup the schema in querytree?

Thanks heaps!
Best regards
Mark

from querytree.

Quick update, I tried QueryTree on postgres 8, 9 and 10, it seems that it works well with 8.4 to 9, it does not work properly with 10, same issue of not showing the tables
Thanks!

from querytree.

Thanks for your help. I've tried QueryTree with Postgres 10 today and it seemed OK. So I think this must be something specific about your database schema that QueryTree can't handle properly.

When I say:

Perhaps you could create a new schema, point QueryTree at it, and then add some of your tables, checking QueryTree each time, until you find the one that causes QueryTree to fall over. Then you could send me the SQL and we can figure out why QueryTree doesn't like it.

What I mean is, the first time QueryTree connects to any database, it queries the INFORMATION_SCHEMA views in the database to see what tables, views and columns there are. (see DbManager.cs) So if you're not seeing any tables in the dropdown that suggests that either QueryTrees's connection details haven't given it access to any tables, or that the code has fallen over somehow while querying it. Maybe one of the columns has a type that's confused it. Or maybe a table has a strange character in its name.

If you could get to a point where you can post a schema definition script on this issue that reproduces the problem, then I'm sure we can fix it. E.g.

CREATE SCHEMA "QTIssue14";
SET search_path TO "QTIssue14";

CREATE TABLE ...;
CREATE TABLE ...;
CREATE TABLE ...;

One thing to keep in mind is that QueryTree caches the schema information once it's loaded it. So when you make schema changes, either restart QueryTree, or delete and recreate the database connection using the QueryTree UI.

from querytree.

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

from querytree.