Prisma1: PSQLException function raise_exception(character varying) does not exist

[carsoni]

Bug Report

Vanilla unmodified install of react-fullstack-advanced was repointed from remote prisma cluster to local Docker environment.
I have done "docker-compose pull" to ensure the latest version

server - yarn start - OK
app - yarn start - OK
SignUp - OK
Login - OK

Navigation - OK
Boilerplate Feed Posts - displaying correctly
Create Draft Button - displays correct page
Create Button on Create Draft Page - does not create a draft

I get Whoops!An internal server error has occurred in the server window and the following error from "docker logs "

{
key":"error/unhandled",
"requestId":"local:api:cjgsuly6v0sjl0831faoadsuh",
"clientId":"analyst$dev",
"payload":{
"exception":"org.postgresql.util.PSQLException:
ERROR: function raise_exception(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Position: 123",
"query":"mutation ($_data: PostCreateInput!) {
createPost(data: $_data) {n idn titlen textn __typenamen }n}n","variables":"{"_data":{"

isPublished":false,"title":"Test","text":"Testing","author":{"connect":{"id":"cjgrb9il0003t0831lie09qs4"}}}}

Apologies for the layout - this is "as-is" from the logs - I didn't want to mess with it in case any of the markup is relevant to the error

Expected behavior?
Pushing the CreateDraft Button should create a draft

Any thoughts as to what might be causing this?
Regards
Ian Carson

[carsoni]

Note: I get exactly the same error when running the createPost mutation from the playground

[marktani]

Hey @carsoni, what version of Postgres are you using?

[bartimaeus]

I ran into this issue as well going through the tutorials with postgres. I believe it is the associated relationships that is the issue. If you remove author from posts schema then the mutation works again.

Here is the mutation I ran:

mutation {
  createPost(data: {
    title: "Sample Post"
    text: "Attempting to add a sample post with an author."
    author: {
      connect:{
        id: "cjgsy42my000z0974ix7akqwt"
      }
    }
  }) {
    id
  }
}

Here is the log from the postgres instance:

2018-05-05 10:04:05.708 UTC [665] ERROR:  function raise_exception(character varying) does not exist at character 127
2018-05-05 10:04:05.708 UTC [665] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2018-05-05 10:04:05.708 UTC [665] STATEMENT:  select case when  exists (  (SELECT "id" FROM "default$default"."User" WHEREFAILURETRIGGER WHERE "id" = $1)  ) then ''  else (raise_exception($2))end
2018-05-05 10:04:58.391 UTC [665] ERROR:  function raise_exception(character varying) does not exist at character 127
2018-05-05 10:04:58.391 UTC [665] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
2018-05-05 10:04:58.391 UTC [665] STATEMENT:  select case when  exists (  (SELECT "id" FROM "default$default"."User" WHEREFAILURETRIGGER WHERE "id" = $1)  ) then ''  else (raise_exception($2))end

[bartimaeus]

I am using the following images:

• prismagraphql/prisma:1.7 (updated 4 days ago)
• postgresql:9.6-alpine (updated 2 months ago)

[bartimaeus]

Just removed the containers and volumes and tried again with postgres:10.3-alpine. I got the same error when attempting to add a post with an associated author.

[bartimaeus]

It looks like the prisma errors are reporting to Bugsnag, but just in case you don't get this one here is the main error from the prisma container:

[Bugsnag - local / testing] Error report: com.bugsnag.Report@405f6bf5
org.postgresql.util.PSQLException: ERROR: function raise_exception(character varying) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

[bartimaeus]

Last comment and then I'll stop. It looks like the primary key on the association table is a different type bpchar(25) vs varchar(25)

[carsoni]

@marktani
pgadmin4 shows version as
PostgreSQL 10.3 (Debian 10.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

[marktani]

Thanks! It seems to be related to the raise_exception function.

Can you try those two approaches:

1. Run this statement

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS void as $$
                                                     BEGIN
                                                        RAISE EXCEPTION '%', $1;
                                                     END;
                                                     $$ Language plpgsql;

this is the statement that is used to create raise_exception by the Postgres connector.

1. Run this statement

CREATE OR REPLACE FUNCTION raise_exception(character varying) RETURNS void as $$
                                                     BEGIN
                                                        RAISE EXCEPTION '%', $1;
                                                     END;
                                                     $$ Language plpgsql;

This statement is _not_ created by the Postgres connector. It would be interesting to hear if adding that function overload fixes the problem for you.

Please try first 1., check if it works and if it doesn't, try 2. and check again. Thanks!

[carsoni]

@marktani
Thanks for these. I'll try them out in the morning (I'm running on Sydney time)

[bartimaeus]

@marktani success! I first attempted each function through a graphical db client (DBeaver). No go. Then I tried adding the first function through the command line and everything works.

1. Connect to postgres docker container

docker exec -it [container_name] bash

1. Connect to postgres with prisma user

psql -U prisma

1. Connect to prisma database

\c prisma

1. Created the function

CREATE OR REPLACE FUNCTION raise_exception(text) RETURNS void as $$
                                                     BEGIN
                                                        RAISE EXCEPTION '%', $1;
                                                     END;
                                                     $$ Language plpgsql;

Thanks for all your help!

[marktani]

Awesome, thanks for confirming the workaround, @bartimaeus!

I see two possible fixes:

• we can add functions with both signatures
• we can look closer into the situation and understand why sometimes the input is a varchar. Then ensure that it is always a text.

The first should be good, but I feel that the second is the cleaner approach 🙂

[bartimaeus]

Sounds great!

[carsoni]

@marktani
Your first solution worked for me but perhaps not for the reason we think.

I examined the schemas of the prisma db through pgadmin4 (looking for any functions) before I opened that app's query tool and ran the CREATE.

I only found 1 function in the 3 schemas (analyst$dev, management, public) and that was "raise_exception(text)" under Functions on the management schema.

I ran your first CREATE query using the query tool while selecting the management schema in the database tree thinking that I would be replacing the function under "management" (pgadmin4 newbie :-) ) however what actually happened was that a new function was created under the public schema and it is when that function is available that the mutation works.

Could it be that the database create script needs to point raise_exception(text) at the public schema and all will be well?

Regards
Ian

[do4gr]

Thanks for reporting this and digging into the problem @carsoni and @bartimaeus . It seems that it is really related to a scoping issue for the function as @carsoni described. In our tests it was created in the public schema but for some reason locally it is created under the management schema and then not in scope when called. I changed it to be created directly in the projects schema.

[lalithanjali0111]

Caused by: org.postgresql.util.PSQLException: ERROR: function format_name(character varying, character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.