turbot / steampipe-postgres-fdw

The Steampipe foreign data wrapper (FDW) is a zero-ETL product that provides Postgres foreign tables which translate queries into API calls to cloud services and APIs. It's bundled with Steampipe and also available as a set of standalone extensions for use in your own Postgres database.
https://steampipe.io/
Apache License 2.0
71 stars 16 forks source link

Problem with github_team_member #323

Open mariadb-ChristianBauer opened 2 years ago

mariadb-ChristianBauer commented 2 years ago

Describe the bug It is not possible to provide a set instead of a single value for the organization field when querying the github_team_member table. This is supported for the slug field though.

Steampipe version (steampipe -v) version 0.16.3

Plugin version (steampipe plugin list) | hub.steampipe.io/plugins/turbot/github@latest | 0.22.0 | github |

To reproduce Query:

> select 
    login
  from 
    github_team_member 
  where 
    organization IN (select login from github_my_organization)
    AND slug IN ('team-A', 'team-B')
Error: rpc error: code = Internal desc = 'List' call for table 'github_team_member' is missing 1 required qual: column:'organization' operator: =
 (SQLSTATE HV000)

Expected behavior Support both = and in for the organization field.

Additional context There is 1 organization in my github_my_organization.

misraved commented 2 years ago

Hello @mariadb-ChristianBauer, sorry for the delayed response. I might need to look up the design of quals but it is a known limitation.

For now, you could use the following query to stay unblocked:

select
  t.organization as organization,
  t.name as team_name,
  t.slug as team_slug,
  t.privacy as team_privacy,
  t.description as team_description,
  tm.login as member_login,
  tm.role as member_role,
  tm.state as member_state
from
  github_team as t,
  github_team_member as tm
where
  t.organization = tm.organization
  and t.slug = tm.slug
  and tm.role = 'maintainer';

Please let me know if it unblocks you. Thanks once again for raising this issue and apologies for the delayed response 👍 .

chr-b commented 2 years ago

Hi @misraved , A workaround has already been found, but it is not optimal.

misraved commented 2 years ago

Thanks for the update @chr-b 👍 .

@chr-b @mariadb-ChristianBauer could you please try with steampie v0.17.1 and github v0.24.0 and check if the issue still persists?

Thanks for you patience 👍 .

chr-b commented 2 years ago

Hi @misraved ,

Results are mixed.

When using one element in the slug list:

> select 
    login
  from 
    github_team_member 
  where 
    organization IN (select login from github_my_organization)
    AND slug IN ('team-A')
+------------------------+
| login                  |
+------------------------+
| my-user-id         |
+------------------------+

When using two elements in the slug list:

select 
    login
  from 
    github_team_member 
  where 
    organization IN (select login from github_my_organization)
    AND slug IN ('team-B', 'team-A')

Error: rpc error: code = Internal desc = 'List' call for table 'github_team_member' is missing 1 required qual: column:'organization' operator: =
 (SQLSTATE HV000)

Version numbers:

$ steampipe -v
steampipe version 0.17.1
$ steampipe plugin list | grep github
| hub.steampipe.io/plugins/turbot/github@latest          | 0.24.0  | github    |
misraved commented 2 years ago

Thanks for the reply @chr-b 👍. I am still investigating it, I have similar results as you do. I will try to check if something funky turns up in the Postgres planner 👍.

Thanks for your patience, I will keep you posted once I have some more data.

github-actions[bot] commented 1 year ago

'This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

chr-b commented 1 year ago

This issue still exists in plugin version 0.25

github-actions[bot] commented 1 year ago

'This issue is stale because it has been open 60 days with no activity. Remove stale label or comment or this will be closed in 30 days.'

graza-io commented 1 year ago

Was able to reproduce when using a query similar to that posted above:

select
  organization,
  slug,
  login
from
  github_team_member
where
  organization IN (select login from github_my_organization)
  AND slug IN ('steampipe', 'steampipe-read', 'steampipe-admin');

Result: [HV000] ERROR: rpc error: code = Internal desc = 'List' call for table 'github_team_member' is missing 1 required qual: column:'organization' operator: =

Weirdly, can adjust to lock the first IN down to one exact value and the query works:

select
  organization,
  slug,
  login
from
  github_team_member
where
  organization IN (select login from github_my_organization where login = 'turbot')
  AND slug IN ('steampipe', 'steampipe-read', 'steampipe-admin');

issue_210_work_vs_not_censored

Query plan from not working approach: issue_210_plan_not_working

Query plan & analysis from working approach: issue_210_plan_and_analysis_working

@kaidaguerre - is it possible that this is an issue in the FDW?

kaidaguerre commented 1 year ago

It's possible - I suggest looking in the database logs with INFO enabled and see if it reports any failures to convert quals

graza-io commented 1 year ago

I've sent you some logs

bigdatasourav commented 1 year ago

@kaidaguerre, I've transferred this issue to the FDW repo for better tracking, as this looks like an FDW issue. Please let us know if anything is required from the plugin team.

kaidaguerre commented 1 year ago

@misraved @bigdatasourav this does not look like an FDW issue - it is a query planning issue - the quals are not being provided to the FDW by Postgres

You need to try to restructure the query to get the desired plan