Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Repo info
Activity
    eliecerthoms
    @eliecerthoms
    when I do select * from crtrs.get_notifications_alt(); on my ide, it works ok
    Any way I could force postgraphile to select it like that?
    If not I could just make a separate api endpoint no problem
    And I have no rush, if you can look on it when you have the time I'd appreciate it a lot
    Benjie Gillam
    @benjie
    Ah we don’t support combining setof with arrays; sorry this restriction isn’t documented; it’s effectively because arrays in pg are not so strongly typed (they can change dimensionality/etc) and also because we use unnest which goes deep rather than just one level (I think)
    If you need that wrap it in a custom composite type and return setof that.
    “Connection (list) of arrays (lists) of json” seems a weird thing to want; perhaps use json_aggregate instead and have the json itself be the array?
    eliecerthoms
    @eliecerthoms
    Alright! Thanks!
    Benjie Gillam
    @benjie
    Np
    Nathan Aherne
    @nathanaherne
    I am trying to create a computed column that creates the column by pulling it from from a different table. I don't seem to be having an success and therefore I think it may not be possible to do this, can someone confirm if this is possible or not please? See code below.
    create or replace function admin.event_startdatetime(event admin.event)
    returns timestamp as $$
      select
      event_start_datetime.event_start_datetime
      from 
      admin.event
      left join
        (select
        admin.event_date_time.event_id,
        min(to_timestamp(EXTRACT(EPOCH FROM (min(admin.event_date_time.event_date) || ' ' || min(admin.event_date_time.start_time))::timestamp))::timestamp) over (partition by admin.event_date_time.event_id) as event_start_datetime
        from 
        admin.event_date_time
        group by
        admin.event_date_time.event_id
        ) as event_start_datetime
      on event.id = event_start_datetime.event_id;
    $$ language sql stable security definer;
    
    comment on function admin.event_startdatetime(admin.event) is 'Get the start date/time of each event - computed column on admin.events table';
    The function works fine but I am not getting the computed column in GraphQL
    Benjie Gillam
    @benjie
    Odd. Yes it should definitely work. I can’t immediately see an issue with the function; is admin in the list of schemas PostGraphile is using?
    Are you using no-ignore-rbac? If so, check you’ve granted execute on the function.
    Functions are a black box to PostGraphile; we only see what’s in the declaration, not the body.
    james-s-turner
    @james-s-turner

    Just written my first query and was wondering where the 'condition' syntax comes from. e.g.

      allBoatClasses (condition: {id: 1} ) {
        edges  {
          node {
            id
            name
          }
        }
      }
    }

    I can't find any reference to 'condition' in GraphQL docs. Is it specific to Postgraphile?

    Benjie Gillam
    @benjie
    Yes
    There’s a filter plugin you might want to check out also, it’s much more powerful, but you should understand the risks (see the README) before using it.
    Michael Kelly
    @mkellyclare
    Hi @benjie, great project, was trying it out and wondering if there is any way for the generated enum types to be lowercase. Have some existing code and types that are lowercase and wondering if there is any way to match them. Thanks
    More later; but try follow what that plugin does but for enums.
    Michael Kelly
    @mkellyclare
    Thanks! I'll give that a go.
    matrixbot
    @matrixbot
    Nolan Is it possible to define a RLS policy such that queries in with clauses run outside of RLS checks? I have an order table, and an attempt table tracking attempts on those orders. I have RLS policies blocking which attempts people who fulfill the orders can see, but now I want a policy that shows orders without any attempts. Problem is, just because a given role can't see an attempt doesn't mean one doesn't exist.
    Nolan So right now the RLS policy limiting what attempts someone can see kicks in. Then I have another RLS policy on orders that only shows any for which attempts don't currently exist, but if someone can't see the attempt then this check returns true and just shows everything.
    Benjie Gillam
    @benjie
    @mkellyclare Here's the plugin (11 lines of code, mostly boilerplate) that makes the enums into capitals in PostGraphile. Probably the easiest thing to do is to write another plugin of your own that changes them back lower case again. https://github.com/graphile/graphile-build/blob/69d7b1cbe48ea5f50e3121916e7b1e12d1c70008/packages/postgraphile-core/src/index.js#L87-L97
    matrixbot
    @matrixbot
    Nolan OK, think I figured it out. Moved the logic into a security definer function.
    Benjie Gillam
    @benjie
    Nolan / @matrixbot : RLS policies themselves obey RLS, this is generally what you want but sometimes it isn't - when it's not you can use a SECURITY DEFINER function to bypass RLS (and RBAC) and perform whatever checks you need.
    e.g. I commonly do something like CREATE POLICY select_admins ON topics FOR select USING (organization_id = any(current_user_admin_organization_ids()));
    This is very efficient (much more so than a join) because it only needs to look up the organization IDs once, and then it can use an index check on that.
    Ah. Thats what you did. Sorry; still catching up!
    matrixbot
    @matrixbot
    Nolan Got it. And just to confirm what I suspect, RLS policies can't block updates to specific columns, right? I.e. if I have an accepted field on attempt that everyone can see but only the recipient of the attempt can change, I should probably just define a custom mutation and @omit the field from the generated update mutation?
    Nolan Still trying to wrap my head around when it's best to use different tables vs. RLS vs. custom mutations/field definitions.
    Benjie Gillam
    @benjie
    Yeah; that's basically correct - RBAC deals with columns/tables, RLS deals with rows.
    I'm not sure what happens when you try to combine them (e.g. create role user_a; create_role user_b; grant user_a to postgraphile_user; grant user_b to postgraphile_user; grant update (a) on foo to user_a; grant update (b) on foo to user_b; create policy update_a on foo for update to user_a using(check_a()); create policy update_b on foo for update to user_b using(check_b());) - I've not tried this out yet.
    Nolan: do you receive edits? I edit alot; not sure if you're using IRC or whatever?
    matrixbot
    @matrixbot
    Nolan Using Matrix, it doesn't seem to edit the messages but I pop into the web interface and check them.
    Benjie Gillam
    @benjie
    :+1:
    Jesús Ángel Loredo Álvarez
    @loredo_alvarez_twitter
    Based on your experience, how do INDEXES let us build a better and performant API with Postgraphile?
    Benjie Gillam
    @benjie
    Uhhhh... Are you asking me how to optimise your database, or...?
    Jesús Ángel Loredo Álvarez
    @loredo_alvarez_twitter
    Yep!, I've been reading some comments here, and at some point, you mention something about PostgreSQL Indexes, those affect on some way the performance of the API?
    Benjie Gillam
    @benjie
    :@ Looks like Gitter lost my message :(
    Anyway; standard PostgreSQL optimisations apply.
    What works best for your database will likely be different from someone else’s
    It’s highly recommended you have the standard expected PostgreSQL indexes, like primary keys, unique indexes, indexes on foreign keys, indexes on things your RLS policies check (if appropriate). Don’t just sprinkle them everywhere though!
    If you need help optimising your DB specifically then I can help with that via my consultancy
    Jesús Ángel Loredo Álvarez
    @loredo_alvarez_twitter
    Thanks a lot, I'll check the basics you mention, if I need Something more DB specific I'll be definitely in contact with you :smile: :+1:
    Michael Kelly
    @mkellyclare
    Thanks for the help @benjie, added a plugin to lowercase the enum names and it did the trick. Here is the code for it if anyone is interested -
    // lowerCaseEnums.ts
    
    export function lowerCaseEnums(builder: any) {
      builder.hook('inflection', (inflection: any) => ({
        ...inflection,
        enumName(value: string) {
          return value.toLowerCase();
        }
      }));
    }
    
    // index.ts
    
    app.use(
      postgraphile(getDbPool(), 'schema_name', {
        appendPlugins: [lowerCaseEnums]  
      })
    );
    Benjie Gillam
    @benjie
    @jemgillam ^ snippet
    matrixbot
    @matrixbot
    Nolan How do I return a single row from a plpgsql function? return query wants me to use a setof, which I don't think I want. I'm trying to return the newly-mutated row in a custom update mutation.
    Jesús Ángel Loredo Álvarez
    @loredo_alvarez_twitter
    UPDATE schema.table SET fields_to_update RETURNING * INTO variable_to_return;