Postgraphile: CRUD Update and Delete functions not created

[pedrinholula]

I'm submitting a ...

• [x] bug report
• [ ] feature request
• [ ] question

PostGraphile version:
4.0.0-beta.3

Minimal SQL file that can be loaded into a clean database:

CREATE TABLE re_list
(
    id integer NOT NULL DEFAULT nextval('enforce_test.re_list_id_seq'::regclass),
    value real,
    registration text COLLATE pg_catalog."default" NOT NULL,
    date date NOT NULL,
    wallet text COLLATE pg_catalog."default",
    active boolean NOT NULL DEFAULT true,
    appraiser text COLLATE pg_catalog."default",
    typology character varying(12) COLLATE pg_catalog."default" NOT NULL DEFAULT 'commercial'::character varying,
    subtype character varying(32) COLLATE pg_catalog."default",
    level smallint NOT NULL DEFAULT 0,
    address text COLLATE pg_catalog."default" NOT NULL,
    neighbourhood text COLLATE pg_catalog."default" NOT NULL,
    city text COLLATE pg_catalog."default" NOT NULL,
    state character(2) COLLATE pg_catalog."default" NOT NULL,
    land_area integer,
    usefull_area integer NOT NULL DEFAULT 1,
    recovery_token character varying(64) COLLATE pg_catalog."default" DEFAULT NULL::character varying,
    created_on timestamp without time zone NOT NULL DEFAULT now(),
    updated_at timestamp without time zone NOT NULL DEFAULT now(),
    CONSTRAINT re_list_pkey PRIMARY KEY (id),
    CONSTRAINT re_list_recovery_token_key UNIQUE (recovery_token),
    CONSTRAINT re_list_registration_key UNIQUE (registration),
    CONSTRAINT valid_levels CHECK (level = ANY (ARRAY[1, 2, 3])),
    CONSTRAINT valid_tipologies CHECK (typology::text = ANY (ARRAY['commercial'::character varying::text, 'house'::character varying::text, 'apartments'::character varying::text, 'land'::character varying::text]))
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE re_offers
(
    id integer NOT NULL DEFAULT nextval('enforce_test.re_offers_id_seq'::regclass),
    url text COLLATE pg_catalog."default" NOT NULL,
    value numeric NOT NULL DEFAULT 0,
    transfer_kind character(16) COLLATE pg_catalog."default" NOT NULL,
    size numeric DEFAULT 0,
    CONSTRAINT re_offers_pkey PRIMARY KEY (id),
    CONSTRAINT re_offers_id_key UNIQUE (id),
    CONSTRAINT re_offers_url_key UNIQUE (url),
    CONSTRAINT re_offers_size_check CHECK (size >= 0::numeric) NOT VALID,
    CONSTRAINT re_offers_value_check CHECK (value >= 0::numeric) NOT VALID,
    CONSTRAINT transfer_kind_types CHECK (transfer_kind = ANY (ARRAY['sell'::bpchar, 'rent'::bpchar])) NOT VALID
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE list_offer
(
    id_re_list integer NOT NULL,
    id_re_offers integer NOT NULL,
    land_base integer NOT NULL DEFAULT 1,
    depreciation_base integer NOT NULL DEFAULT 1,
    local_base integer NOT NULL DEFAULT 1,
    enabled boolean NOT NULL DEFAULT true,
    CONSTRAINT list_offer_pk PRIMARY KEY (id_re_list, id_re_offers),
    CONSTRAINT list_offer_id_re_list_id_re_offers_key UNIQUE (id_re_list, id_re_offers),
    CONSTRAINT list_id FOREIGN KEY (id_re_list)
        REFERENCES enforce_test.re_list (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT offer_id FOREIGN KEY (id_re_offers)
        REFERENCES enforce_test.re_offers (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Steps to reproduce:
I'm running postgraphile as lib and when i try to access the CRUD function updateListOfferById from postgraphile I got an error.

That's my postgraphile snnipet:

app.use(cors({ origin: true }));
app.use(
  postgraphile(process.env.POSTGRES_URI, schema, {
    dynamicJson: true,
    enableCors: true,
    disableQueryLog: !dev,
    graphiql: dev,
  }),
);

That's the mutation i was trying to execute:

mutation update{
  updateLisOffertById(input:{id:41,listOfferPatch:{landBase:3000}}){
    listOffer{
      id
    }
  }
}

Current behavior:
There is no Update and Delete CRUD functions to some of my tables:

Expected behavior:
The crud function for all database tables.

[benjie]

I'm afraid I cannot reproduce this with the schema you gave. Also my GraphiQL looks different to yours, how are you writing your queries? Are you sure the mutations don't exist in the documentation browser?

[pedrinholula]

The major difference is that i have created a function to my schema. It works on graphql very well, but the crud functions no. I'm as afraid as you.

CREATE OR REPLACE FUNCTION add_offer(_url character varying, _value numeric, _transfer_kind character, _size numeric) RETURNS numeric
    LANGUAGE plpgsql
    AS $$
DECLARE _myId numeric;
BEGIN
    with s AS (
        SELECT id
        FROM re_offers
        WHERE url=_url
    ),
    i AS (
        INSERT INTO re_offers (url, value, transfer_kind, size)
        SELECT _url, _value, _transfer_kind, _size
        WHERE NOT EXISTS (SELECT * FROM re_offers WHERE re_offers.url = _url)
        RETURNING id
    )
    SELECT id INTO _myId FROM (
        SELECT id FROM i
        UNION ALL SELECT id FROM s
    ) AS universal_id ;
    RETURN _myId;

END;
$$;

I think is the major difference. It's working well for you @benjie?

[benjie]

Could you use exportGqlSchemaPath and send me both a full pg_dump of the SQL schema and the generated GraphQL schema for a database that's not playing nicely?

Also, can you confirm you're using PostgreSQL 9.6 or newer?

[lordnynex]

@pedrinholula & @benjie I'm in a bit of a hurry so I haven't tried to reproduce with the schema that is here in the issue. I did just run into this issue (that does not appear to be documented) where CREATE mutations were created but no update or delete.

The issue was a missing primary key on the table. It appears postgraphile will not create these mutation without a primary key. It looks like the schema created here in this issue uses some composite primary keys. I'm not sure if those would also be an issue.

@benjie The reason this came up is during early dev when we are using UUID for ID columns instead of SERIAL. In postgres, SERIAL implies a primary key, where as a UUID column does not.

[benjie]

Ah! Thanks for the insight!

Also I just noticed there’s a typo in the GraphQL query: “updateLisOffertById” - that “t” is in the wrong place.

[pedrinholula]

Thanks @lordnynex. I'm gonna change a little my test schema and will try to see if resolve.
@benjie, should I send you the full schema yet?

[benjie]

@pedrinholula If adding a primary key to the table fixes it then that's probably sufficient. I'm booked out this week but hopefully I can get a fix out next week.

[chadfurman]

is it possible you have not added your schema to the list of schemas that postgraphile is supposed to introspect?

[benjie]

Note to self: check that update/delete works without primary key but with a unique index. (We can't update a row that has no unique indexes as we have nothing to identify the row, but we don't technically need a PK.)

[benjie]

It doesn't; due to a silly error in the code - I accidentally did if (!primaryKeyConstraint) return here and so it skipped running the logic for the later unique keys. Fixing it now 👍

[benjie]

Fixed in https://github.com/graphile/graphile-build/pull/278