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
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]
})
);
And return that variable from PLPGSQL
That's what I do
[ { error: ROLLBACK TO SAVEPOINT can only be used in transaction blocks
at Connection.parseE (/XXXXXXXXX/react-postgraphile/react-postgraphile-front/node_modules/pg/lib/connection.js:553:11)
I have submitted the same mutation using both GraphiQL and the supplied CLI server and it works OK. I found this issue graphile/graphile-build#98 saying there might be a problems with library versions. My package.json is as follows:
"pg": "^7.4.3",
"pg-pool": "^2.0.3",
"postgraphile": "^4.0.0-rc.2",
Actually thatās not right. What it needs is to pass pgForceTransaction:true. Iām surprised I didnāt default that to true.
Iām in France right now; could you file an issue telling me to default that option to true please?
@loxy I donāt understand the question
@benjie will file issue when I have verified
if (needTransaction) {
console.log('BEGIN TRANSACTION');
await pgClient.query('begin');
}
I can see 'BEGIN TRANSACTION' being logged to console. Possibly a race condition?
I'm generating error messages for the front end like this:
create or replace function app_public.register_person(
handle text,
display_name text,
email text,
password text
) returns app_public.person as $$
declare
person app_public.person;
constraint_name text;
begin
insert into app_public.person (handle, display_name) values
(handle, display_name)
returning * into person;
insert into app_private.person_account (person_id, email, password_hash) values
(person.id, email, crypt(password, gen_salt('bf')));
return person;
exception
when check_violation then
get stacked diagnostics constraint_name = CONSTRAINT_NAME;
if constraint_name = 'handle_length' then
raise exception 'Your handle is too long';
end if;
when unique_violation then
get stacked diagnostics constraint_name = CONSTRAINT_NAME;
if constraint_name = 'person_account_email_key' then
raise exception 'That email is already in use';
elsif constraint_name = 'person_handle_key' then
raise exception 'That handle is already in use';
else
raise exception '%', constraint_name;
end if;
end;
$$ language plpgsql strict security definer;It works, but I'm curious as to whether this is the "right" approach or not.
Hi @benjie, just wanted to pop in and say that this project, your handling of it, the docs, support, and community is awesome all around. Postgraphile is a powerful, idomatic, and elegant tool. I may start using it for customer projects and if so, would eventually like to dive into the internals and maybe contribute a bit. Thanks for your efforts on this. Just learned a lot about my own use cases through reading a bunch of your answers in github issues
Contribution to the core would be welcome also; the API is pretty much fixed now so I think the thing that needs the most work is
graphile-utils to make plugin factories to make doing things a lot easier for people. For example, there really should be an inflector plugin factory that enables you to override the inflector. Anyway I'm getting ahead of myself.
