Postgraphile: postgraphql not recognizing/finding stored procedures

[tylerdmace]

I'm having trouble getting a stored procedure to show up in graphiql and I'm not entirely sure why. What is the best practice here and what limitations come with trying to run stored procs from graphql?

[benjie]

Most important things are:

• make sure it doesn't return void
• make sure it's marked as stable (otherwise we'll think it's a mitation)
• make sure it's defined in one of the Postgres schemas you've told PostGraphQL to introspect
• make sure you return a named type (e.g. a scalar, table name or domain) - we don't currently support anonymous types

Feel free to share the definition if you want further help.

[tylerdmace]

We ended up having to return a setof mytablehere where mytablehere was a table created just for this stored proc. I think it's because introspection cannot happen on anonymous/unknown return types. Is that accurate? Forgive me if I'm using words wrong -- this is new territory.

[benjie]

Ah yes, sorry; I should have included that in the initial list. I'm going to edit it up there now. We don't have support for anonymous types right now (but I think you can create a type (CREATE TYPE...) as opposed to a full table?).

[tylerdmace]

Gonna give that a shot! I'll report back; Thank you for the quick replies. You rock.

[benjie]

Added to troubleshooting doc in https://github.com/postgraphql/postgraphql/pull/530

[tylerdmace]

Creating a custom data type worked just fine. Thanks again!

[benjie]

You're welcome!

[Remi-C]

Hey,
just a quick update.
I find it a bit unfair as some volatile functions do not change the data.
Anyway, there are workarounds.

[benjie]

What volatile function would you like to expose as if it were stable?

[Remi-C]

Some of my tables are actually foreign data wrapper,
so no guarantees on the data in it whatsoever.
Not really proper to set the functions using that to stable.
I can work around that though.

Le lun. 6 janv. 2020 à 14:36, Benjie Gillam [e-mail] a
écrit :

What volatile function would you like to expose as if it were stable?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/graphile/postgraphile/issues/529?email_source=notifications&email_token=ABEBYISEYK77YF63Z7MRDQTQ4OB4JA5CNFSM4DULJOMKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEIGQ5NI#issuecomment-571281077,
or unsubscribe
https://github.com/notifications/unsubscribe-auth/ABEBYISREVL2UZZDLK5TZULQ4OB4JANCNFSM4DULJOMA
.

[benjie]

During a query that references any remote tables on a foreign server, postgres_fdw opens a transaction on the remote server if one is not already open corresponding to the current local transaction. The remote transaction is committed or aborted when the local transaction commits or aborts. Savepoints are similarly managed by creating corresponding remote savepoints.

The remote transaction uses SERIALIZABLE isolation level when the local transaction has SERIALIZABLE isolation level; otherwise it uses REPEATABLE READ isolation level. This choice ensures that if a query performs multiple table scans on the remote server, it will get snapshot-consistent results for all the scans.

-- https://www.postgresql.org/docs/current/postgres-fdw.html#id-1.11.7.42.12 (emphasis mine)

A STABLE function cannot modify the database and is guaranteed to return the same results given the same arguments for all rows within a single statement.

-- https://www.postgresql.org/docs/current/xfunc-volatility.html (emphasis mine)

A "single statement" always happens within a single transaction, so querying data from FDW is entirely valid as a STABLE function; and in fact should be marked as such for performance reasons.

[Remi-C]

Excellent, thanks @benjie !