search

hasura / graphql-engine

Blazing fast, instant realtime GraphQL APIs on your DB with fine grained access control, also trigger webhooks on database events.
https://hasura.io
Apache License 2.0
31.18k stars 2.77k forks source link

Is hasura beta-5 to beta-6 upgrade breaking? #2800

Closed Israel-Laguan closed 4 years ago

Israel-Laguan commented 5 years ago

Problem

I've tried to update my Heroku' hasura repo with beta-5 to beta-6 but a lot of inconsistency problem arise. I wanted to try the enum feature but seems i can't :disappointed:

What I think is the problem

New release brings breaking changes and data is inconsistent with past releases.

What I've tried

I've upload my my new dockerfile using heroku container:push web && heroku container:release web, all the process ended successfully.

But when I opened my app it crashed. Inspecting the logs says a lot of inconsistencies, like {"definition":{"role":"owner","comment":null,"permission":{"allow_aggregations":false,"columns":["stars","stars_id","target_id"],"filter":{}},"table":"stars"},"reason":"table \"stars\" does not exist","type":"select_permission"}

So it seems like hasura can't undestand the actual database in Postgres.

The I rolled back downgrading the repo to beta-5 and all is normal.

Proposed solution

Warn users that upgrading when you have a working app to a new release is :warning: dangerous :stop_sign: and must be done by an expert or something in that line, so people don't find breaking changes in their apps.

Also some testings upgrading a test app would be nice, so hasura team can tell if the release brings breaking changes to working apps with old release.

BTW: Good job with Hasure!

lexi-lambda commented 5 years ago

The beta-6 release was not intended to have any backwards-incompatible changes, but it’s possible there’s a bug somewhere. If there is, we don’t know about it, and we haven’t managed to reproduce it, so it would be very helpful if you could share an example that reproduces the issue! It’s likely that the behavior you describe was not intentional, and we can fix it in the next release.

(In general, I would always recommend having a backup of your database when upgrading, but that is just judicious caution—we don’t intend to make breaking changes without being explicit about it.)

0x777 commented 5 years ago

@Israel-Laguan Can share your schema and metadata? That'll help us identify the issue that you are facing. Metadata can be exported from the console settings and schema as follows:

curl -H 'x-hasura-admin-secret: <admin-secret>' -d'{"opts":["-O","-x", "--schema-only", "-N" ,"hdb_catalog", "-N", "hdb_views"], "clean_output":true}' 'https://your-app-domain/v1alpha1/pg_dump'
Israel-Laguan commented 5 years ago

The beta-6 release was not intended to have any backwards-incompatible changes, but it’s possible there’s a bug somewhere. If there is, we don’t know about it, and we haven’t managed to reproduce it, so it would be very helpful if you could share an example that reproduces the issue! It’s likely that the behavior you describe was not intentional, and we can fix it in the next release.

(In general, I would always recommend having a backup of your database when upgrading, but that is just judicious caution—we don’t intend to make breaking changes without being explicit about it.)

I will reproduce the issue and share the log

Israel-Laguan commented 5 years ago

@Israel-Laguan Can share your schema and metadata? That'll help us identify the issue that you are facing. Metadata can be exported from the console settings and schema as follows:

curl -H 'x-hasura-admin-secret: <admin-secret>' -d'{"opts":["-O","-x", "--schema-only", "-N" ,"hdb_catalog", "-N", "hdb_views"], "clean_output":true}' 'https://your-app-domain/v1alpha1/pg_dump'

Ok ill give my pg_dump

Israel-Laguan commented 5 years ago

pg_dump:

CREATE EXTENSION IF NOT EXISTS pgcrypto WITH SCHEMA public;
COMMENT ON EXTENSION pgcrypto IS 'cryptographic functions';
CREATE FUNCTION public.set_current_timestamp_updated_at() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
DECLARE
  _new record;
BEGIN
  _new := NEW;
  _new."updated_at" = NOW();
  RETURN _new;
END;
$$;
CREATE TABLE public.cook_input (
    cook_input_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    middle_name text,
    mother_maiden_name text,
    zipcode integer,
    dob text,
    ssn text,
    certification_photo text,
    instagram text,
    bio text,
    video text,
    no_middle_name boolean
);
COMMENT ON TABLE public.cook_input IS 'Info needed from a cook';
COMMENT ON COLUMN public.cook_input.middle_name IS 'If not present, check the field no_middle_name';
COMMENT ON COLUMN public.cook_input.zipcode IS '5 integers, e.g. 06831';
COMMENT ON COLUMN public.cook_input.dob IS 'Date of Birth. In this format: YYYY-MM-DD e.g. 1964-03-15';
COMMENT ON COLUMN public.cook_input.ssn IS 'Format ###-##-### e.g.  111-11-2001';
COMMENT ON COLUMN public.cook_input.certification_photo IS 'URL to a image of a valid Certification from ACF. Refer to https://www.acfchefs.org/ACF/Certify/ACF/Certify/';
COMMENT ON COLUMN public.cook_input.instagram IS 'URL to your Instagram profile.';
COMMENT ON COLUMN public.cook_input.bio IS 'Bragging and swag section here';
COMMENT ON COLUMN public.cook_input.video IS 'URL to a video in Youtube or Vimeo';
COMMENT ON COLUMN public.cook_input.no_middle_name IS 'Tell if middlename is present';
CREATE TABLE public.users (
    user_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    created_at timestamp with time zone DEFAULT now() NOT NULL,
    updated_at timestamp with time zone DEFAULT now() NOT NULL,
    roles json,
    first_name text,
    last_name text,
    email text,
    password text,
    phone_number text,
    nickname text,
    avatar text,
    self_description text,
    promo_code text,
    cook_verified boolean DEFAULT false,
    cook_disabled boolean DEFAULT false,
    cook_paused boolean DEFAULT false,
    best_dish text,
    saved_experiences json,
    diner_verified boolean DEFAULT false,
    diner_disabled boolean DEFAULT false,
    favourite_food text,
    diner_additional_info text,
    other text,
    stripe_costumer_id text,
    location uuid,
    notification_id uuid,
    cook_input uuid,
    schedule uuid,
    preferred_payment uuid
);
COMMENT ON TABLE public.users IS 'App Users.';
COMMENT ON COLUMN public.users.roles IS 'Array of Roles, you can choose  cook | diner | admin';
COMMENT ON COLUMN public.users.email IS 'must be a valid email or verification code fails';
COMMENT ON COLUMN public.users.password IS 'Password encrypted.';
COMMENT ON COLUMN public.users.phone_number IS 'Please use proper phone format';
COMMENT ON COLUMN public.users.avatar IS 'URL to a valid image of the user';
COMMENT ON COLUMN public.users.promo_code IS 'Code to share with friends so they can gain discounts!';
COMMENT ON COLUMN public.users.saved_experiences IS 'Array of reservation_id pointing to each saved experiences';
COMMENT ON COLUMN public.users.diner_additional_info IS 'Something that the cook must know?';
COMMENT ON COLUMN public.users.schedule IS 'schedule_id to the work schedule of a Cook';
COMMENT ON COLUMN public.users.preferred_payment IS 'Must be one id of the Payment Methods Array';
CREATE TABLE public.allergies (
    allergy_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    dish_id uuid,
    type text,
    description text,
    severity text
);
COMMENT ON TABLE public.allergies IS 'Allergy that certain dish can have';
COMMENT ON COLUMN public.allergies.type IS 'Name to identify the allergy';
COMMENT ON COLUMN public.allergies.description IS 'Medium to long description of the allergy';
COMMENT ON COLUMN public.allergies.severity IS 'Express shortly how much is the dish dangerous';
CREATE TABLE public.diner_stars (
    stars integer,
    diner_id uuid,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    star_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    disabled boolean,
    other text
);
COMMENT ON TABLE public.diner_stars IS 'To qualify or rate a Diner';
COMMENT ON COLUMN public.diner_stars.stars IS 'Integer in [0, 5] to rate diner';
COMMENT ON COLUMN public.diner_stars.diner_id IS 'Diner that set the stars';
CREATE TABLE public.dishes (
    dish_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    experience_name text,
    description text,
    attachment json,
    minimun_diners integer,
    maximum_diners integer,
    minimum_cancel_time text,
    required_tools json,
    disabled boolean,
    reasons text,
    paused boolean,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    other text,
    cook_id uuid
);
COMMENT ON TABLE public.dishes IS 'Dish to be cooked for a diner';
COMMENT ON COLUMN public.dishes.required_tools IS 'Array of descriptions fo tools to be used';
CREATE TABLE public.food_styles (
    food_style_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    name text,
    description text,
    dish_id uuid
);
COMMENT ON TABLE public.food_styles IS 'A kind of food or some localized way to cook';
CREATE TABLE public.guests (
    guests_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    reservation_id uuid,
    demographical_group text,
    quantity integer,
    description text,
    other text
);
COMMENT ON TABLE public.guests IS 'People to attend the reservation';
CREATE TABLE public.messages (
    message_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    user_id uuid,
    first_name text,
    last_name text,
    nickname text,
    avatar text,
    message text NOT NULL,
    attachment text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    disabled boolean,
    reservation_id uuid
);
CREATE TABLE public.notifications (
    notifications_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    push boolean DEFAULT true,
    messages_email boolean DEFAULT true,
    messages_sms boolean DEFAULT true,
    alerts_email boolean DEFAULT true,
    alerts_sms boolean DEFAULT true,
    promotions_email boolean DEFAULT true,
    promotions_sms boolean DEFAULT true,
    updates_notification boolean DEFAULT true
);
COMMENT ON TABLE public.notifications IS 'Configure notifications and updates info';
CREATE TABLE public.payment_methods (
    payment_method_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    payment_name text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    invites_experiences boolean DEFAULT false,
    details text,
    other text,
    user_id uuid
);
COMMENT ON TABLE public.payment_methods IS 'Diferent ways to pay';
CREATE TABLE public.payments (
    payment_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    receiver text,
    buyer text,
    concept text,
    ammount integer,
    pay_status text,
    fee integer,
    comment text,
    other text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    pay_method uuid
);
COMMENT ON TABLE public.payments IS 'Payment registry';
CREATE TABLE public.price_info (
    price_info_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    demographical_group text,
    price integer,
    min integer,
    max integer,
    dish_id uuid
);
COMMENT ON TABLE public.price_info IS 'Configure a Price based on Demographical groups';
CREATE TABLE public.reservations (
    reservation_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    "when" text,
    diner_comment text,
    cook_comment text,
    priority integer,
    status text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    other text,
    pay_status text,
    diner_id text,
    location uuid,
    dish_id uuid,
    cook_id uuid,
    review_id uuid
);
COMMENT ON TABLE public.reservations IS 'When a cook cook for a user';
COMMENT ON COLUMN public.reservations."when" IS 'Date when the reservation will take place.';
CREATE TABLE public.reviews (
    review_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    food_taste integer,
    food_presentation integer,
    chef_treatment integer,
    after_cleaning integer,
    timing integer,
    overall_experience integer,
    comment text,
    attachment json,
    service_went_fully boolean,
    review_text text,
    disabled boolean DEFAULT false,
    reasons text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    other text,
    reservation_id uuid,
    diner_id uuid
);
COMMENT ON TABLE public.reviews IS 'A Diner Reviews a Reservation';
CREATE TABLE public.schedule (
    schedule_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    monday text,
    tuesday text,
    wednesday text,
    thursday text,
    friday text,
    saturday text,
    sunday text,
    work_holidays boolean DEFAULT false
);
COMMENT ON TABLE public.schedule IS 'Schedule of work';
CREATE TABLE public.stars (
    stars_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    stars integer,
    target_id uuid
);
COMMENT ON TABLE public.stars IS 'Rate with stars from 0 to 5';
CREATE TABLE public.user_location (
    state text,
    user_location_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    city text,
    address text,
    other text
);
COMMENT ON TABLE public.user_location IS 'To identify tour location';
CREATE TABLE public.staffs (
    staff_id uuid DEFAULT public.gen_random_uuid() NOT NULL,
    roles json,
    email text,
    password text,
    first_name text,
    middle_name text,
    mother_maiden_name text,
    zipcode text,
    dob text,
    ssn text,
    bio text,
    last_name text,
    nickname text,
    avatar text,
    preferred_payment text,
    verified boolean DEFAULT false,
    disabled boolean DEFAULT false,
    other text,
    created_at timestamp with time zone DEFAULT now(),
    updated_at timestamp with time zone DEFAULT now(),
    schedule uuid,
    location uuid
);
COMMENT ON TABLE public.staffs IS 'People working as Helpers to the Cook';
COMMENT ON COLUMN public.staffs.roles IS 'Array of Roles, you can choose account | crew | marketing | metric';
ALTER TABLE ONLY public.allergies
    ADD CONSTRAINT "Allergies_pkey" PRIMARY KEY (allergy_id);
ALTER TABLE ONLY public.cook_input
    ADD CONSTRAINT cook_info_pkey PRIMARY KEY (cook_input_id);
ALTER TABLE ONLY public.dishes
    ADD CONSTRAINT dishes_experience_name_key UNIQUE (experience_name);
ALTER TABLE ONLY public.dishes
    ADD CONSTRAINT dishes_pkey PRIMARY KEY (dish_id);
ALTER TABLE ONLY public.food_styles
    ADD CONSTRAINT food_styles_pkey PRIMARY KEY (food_style_id);
ALTER TABLE ONLY public.guests
    ADD CONSTRAINT guests_pkey PRIMARY KEY (guests_id);
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id);
ALTER TABLE ONLY public.notifications
    ADD CONSTRAINT notifications_pkey PRIMARY KEY (notifications_id);
ALTER TABLE ONLY public.payment_methods
    ADD CONSTRAINT payment_methods_pkey PRIMARY KEY (payment_method_id);
ALTER TABLE ONLY public.payments
    ADD CONSTRAINT payments_pkey PRIMARY KEY (payment_id);
ALTER TABLE ONLY public.price_info
    ADD CONSTRAINT price_info_pkey PRIMARY KEY (price_info_id);
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_pkey PRIMARY KEY (reservation_id);
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_reservation_id_key UNIQUE (reservation_id);
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_pkey PRIMARY KEY (review_id);
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_review_id_key UNIQUE (review_id);
ALTER TABLE ONLY public.schedule
    ADD CONSTRAINT schedule_pkey PRIMARY KEY (schedule_id);
ALTER TABLE ONLY public.staffs
    ADD CONSTRAINT staffs_email_key UNIQUE (email);
ALTER TABLE ONLY public.staffs
    ADD CONSTRAINT staffs_pkey PRIMARY KEY (staff_id);
ALTER TABLE ONLY public.diner_stars
    ADD CONSTRAINT stars_pkey PRIMARY KEY (star_id);
ALTER TABLE ONLY public.stars
    ADD CONSTRAINT stars_pkey1 PRIMARY KEY (stars_id);
ALTER TABLE ONLY public.user_location
    ADD CONSTRAINT user_location_pkey PRIMARY KEY (user_location_id);
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_user_id_key UNIQUE (email, user_id);
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);
CREATE TRIGGER set_public_dishes_updated_at BEFORE UPDATE ON public.dishes FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_dishes_updated_at ON public.dishes IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_messages_updated_at BEFORE UPDATE ON public.messages FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_messages_updated_at ON public.messages IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_payment_methods_updated_at BEFORE UPDATE ON public.payment_methods FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_payment_methods_updated_at ON public.payment_methods IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_payments_updated_at BEFORE UPDATE ON public.payments FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_payments_updated_at ON public.payments IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_reservations_updated_at BEFORE UPDATE ON public.reservations FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_reservations_updated_at ON public.reservations IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_reviews_updated_at BEFORE UPDATE ON public.reviews FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_reviews_updated_at ON public.reviews IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_staffs_updated_at BEFORE UPDATE ON public.staffs FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_staffs_updated_at ON public.staffs IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_stars_updated_at BEFORE UPDATE ON public.diner_stars FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_stars_updated_at ON public.diner_stars IS 'trigger to set value of column "updated_at" to current timestamp on row update';
CREATE TRIGGER set_public_users_updated_at BEFORE UPDATE ON public.users FOR EACH ROW EXECUTE PROCEDURE public.set_current_timestamp_updated_at();
COMMENT ON TRIGGER set_public_users_updated_at ON public.users IS 'trigger to set value of column "updated_at" to current timestamp on row update';
ALTER TABLE ONLY public.allergies
    ADD CONSTRAINT "Allergies_dish_id_fkey" FOREIGN KEY (dish_id) REFERENCES public.dishes(dish_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.dishes
    ADD CONSTRAINT dishes_cook_id_fkey FOREIGN KEY (cook_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.food_styles
    ADD CONSTRAINT food_styles_dish_id_fkey FOREIGN KEY (dish_id) REFERENCES public.dishes(dish_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.guests
    ADD CONSTRAINT guests_reservation_id_fkey FOREIGN KEY (reservation_id) REFERENCES public.reservations(reservation_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_reservation_id_fkey FOREIGN KEY (reservation_id) REFERENCES public.reservations(reservation_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.messages
    ADD CONSTRAINT messages_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.payment_methods
    ADD CONSTRAINT payment_methods_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.payment_methods
    ADD CONSTRAINT payment_methods_user_id_fkey2 FOREIGN KEY (user_id) REFERENCES public.staffs(staff_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.payments
    ADD CONSTRAINT payments_pay_method_fkey FOREIGN KEY (pay_method) REFERENCES public.payment_methods(payment_method_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.price_info
    ADD CONSTRAINT price_info_dish_id_fkey FOREIGN KEY (dish_id) REFERENCES public.dishes(dish_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_cook_id_fkey FOREIGN KEY (cook_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_dish_id_fkey FOREIGN KEY (dish_id) REFERENCES public.dishes(dish_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_location_fkey FOREIGN KEY (location) REFERENCES public.user_location(user_location_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reservations
    ADD CONSTRAINT reservations_review_id_fkey FOREIGN KEY (review_id) REFERENCES public.reviews(review_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_diner_id_fkey FOREIGN KEY (diner_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.reviews
    ADD CONSTRAINT reviews_reservation_id_fkey FOREIGN KEY (reservation_id) REFERENCES public.reservations(reservation_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.staffs
    ADD CONSTRAINT staffs_location_fkey FOREIGN KEY (location) REFERENCES public.user_location(user_location_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.staffs
    ADD CONSTRAINT staffs_schedule_fkey FOREIGN KEY (schedule) REFERENCES public.schedule(schedule_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.stars
    ADD CONSTRAINT stars_target_id_fkey FOREIGN KEY (target_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.stars
    ADD CONSTRAINT stars_target_id_fkey1 FOREIGN KEY (target_id) REFERENCES public.staffs(staff_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.stars
    ADD CONSTRAINT stars_target_id_fkey2 FOREIGN KEY (target_id) REFERENCES public.dishes(dish_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.diner_stars
    ADD CONSTRAINT stars_user_id_fkey FOREIGN KEY (diner_id) REFERENCES public.users(user_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_cook_input_fkey FOREIGN KEY (cook_input) REFERENCES public.cook_input(cook_input_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_location_fkey FOREIGN KEY (location) REFERENCES public.user_location(user_location_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_notification_id_fkey FOREIGN KEY (notification_id) REFERENCES public.notifications(notifications_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_preferred_payment_fkey FOREIGN KEY (preferred_payment) REFERENCES public.payment_methods(payment_method_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_schedule_fkey FOREIGN KEY (schedule) REFERENCES public.schedule(schedule_id) ON UPDATE RESTRICT ON DELETE RESTRICT;
bkstorm commented 5 years ago

I just upgrade graphql-engine from beta-5 to beta-6, but can't roll it back. Here is the error message: {"path":"$","error":"unsupported version : 22","code":"not-supported"}. How to fix that? here is the content of version 22 in hdb_version table: 

INSERT INTO "hdb_catalog"."hdb_version"("hasura_uuid", "version", "upgraded_on", "cli_state", "console_state") VALUES ('49e5033a-4915-4a9b-853d-42a174a70dd9', '22', '2019-09-12 07:45:47.110536+00', '{}', '{"telemetryNotificationShown": true}');

Update: I have to delete 2 schema: hdb_catalogs and hdb_views -> recreate graphql-engine container -> import old data to these schemas.

rakeshkky commented 5 years ago

@bkstorm You can downgrade the catalog from 22 to 19 by running

-- From 22 to 21
DROP VIEW IF EXISTS hdb_catalog.hdb_function_info_agg;
DROP VIEW IF EXISTS hdb_catalog.hdb_function_agg;

CREATE VIEW hdb_catalog.hdb_function_agg AS
(
SELECT
  p.proname::text AS function_name,
  pn.nspname::text AS function_schema,

  CASE
    WHEN (p.provariadic = (0) :: oid) THEN false
    ELSE true
  END AS has_variadic,

  CASE
    WHEN (
      (p.provolatile) :: text = ('i' :: character(1)) :: text
    ) THEN 'IMMUTABLE' :: text
    WHEN (
      (p.provolatile) :: text = ('s' :: character(1)) :: text
    ) THEN 'STABLE' :: text
    WHEN (
      (p.provolatile) :: text = ('v' :: character(1)) :: text
    ) THEN 'VOLATILE' :: text
    ELSE NULL :: text
  END AS function_type,

  pg_get_functiondef(p.oid) AS function_definition,

  rtn.nspname::text AS return_type_schema,
  rt.typname::text AS return_type_name,

  CASE
    WHEN ((rt.typtype) :: text = ('b' :: character(1)) :: text) THEN 'BASE' :: text
    WHEN ((rt.typtype) :: text = ('c' :: character(1)) :: text) THEN 'COMPOSITE' :: text
    WHEN ((rt.typtype) :: text = ('d' :: character(1)) :: text) THEN 'DOMAIN' :: text
    WHEN ((rt.typtype) :: text = ('e' :: character(1)) :: text) THEN 'ENUM' :: text
    WHEN ((rt.typtype) :: text = ('r' :: character(1)) :: text) THEN 'RANGE' :: text
    WHEN ((rt.typtype) :: text = ('p' :: character(1)) :: text) THEN 'PSUEDO' :: text
    ELSE NULL :: text
  END AS return_type_type,
  p.proretset AS returns_set,
  ( SELECT
      COALESCE(json_agg(q.type_name), '[]')
    FROM
      (
        SELECT
          pt.typname AS type_name,
          pat.ordinality
        FROM
          unnest(
            COALESCE(p.proallargtypes, (p.proargtypes) :: oid [])
          ) WITH ORDINALITY pat(oid, ordinality)
          LEFT JOIN pg_type pt ON ((pt.oid = pat.oid))
        ORDER BY pat.ordinality ASC
      ) q
   ) AS input_arg_types,
  to_json(COALESCE(p.proargnames, ARRAY [] :: text [])) AS input_arg_names
FROM
  pg_proc p
  JOIN pg_namespace pn ON (pn.oid = p.pronamespace)
  JOIN pg_type rt ON (rt.oid = p.prorettype)
  JOIN pg_namespace rtn ON (rtn.oid = rt.typnamespace)
WHERE
  pn.nspname :: text NOT LIKE 'pg_%'
  AND pn.nspname :: text NOT IN ('information_schema', 'hdb_catalog', 'hdb_views')
  AND (NOT EXISTS (
          SELECT
            1
          FROM
            pg_aggregate
          WHERE
            ((pg_aggregate.aggfnoid) :: oid = p.oid)
        )
    )
);

CREATE VIEW hdb_catalog.hdb_function_info_agg AS (
  SELECT
    function_name,
    function_schema,
    row_to_json (
      (
        SELECT
          e
          FROM
              (
                SELECT
                  has_variadic,
                  function_type,
                  return_type_schema,
                  return_type_name,
                  return_type_type,
                  returns_set,
                  input_arg_types,
                  input_arg_names,
                  exists(
                    SELECT
                      1
                      FROM
                          information_schema.tables
                     WHERE
                table_schema = return_type_schema
            AND table_name = return_type_name
                  ) AS returns_table
              ) AS e
      )
    ) AS "function_info"
    FROM
        hdb_catalog.hdb_function_agg
);

UPDATE hdb_catalog.hdb_version
   SET version = '21'
 WHERE version = '22';

-- From 21 to 20
DROP INDEX "event_log_locked_idx";

UPDATE hdb_catalog.hdb_version
   SET version = '20'
 WHERE version = '21';

-- From 20 to 19
ALTER TABLE hdb_catalog.hdb_table DROP COLUMN is_enum;

CREATE FUNCTION hdb_catalog.hdb_table_oid_check() RETURNS trigger AS
  $function$
BEGIN
  IF (EXISTS (SELECT 1 FROM information_schema.tables st WHERE st.table_schema = NEW.table_schema AND st.table_name = NEW.table_name)) THEN
    return NEW;
  ELSE
    RAISE foreign_key_violation using message = 'table_schema, table_name not in information_schema.tables';
    return NULL;
  END IF;
END;
$function$
  LANGUAGE plpgsql;

CREATE TRIGGER hdb_table_oid_check BEFORE INSERT OR UPDATE ON hdb_catalog.hdb_table
  FOR EACH ROW EXECUTE PROCEDURE hdb_catalog.hdb_table_oid_check();

DROP VIEW hdb_catalog.hdb_table_info_agg;
DROP VIEW hdb_catalog.hdb_column;
DROP VIEW hdb_catalog.hdb_foreign_key_constraint;

CREATE VIEW hdb_catalog.hdb_foreign_key_constraint AS
SELECT
    q.table_schema :: text,
    q.table_name :: text,
    q.constraint_name :: text,
    min(q.constraint_oid) :: integer as constraint_oid,
    min(q.ref_table_table_schema) :: text as ref_table_table_schema,
    min(q.ref_table) :: text as ref_table,
    json_object_agg(ac.attname, afc.attname) as column_mapping,
    min(q.confupdtype) :: text as on_update,
    min(q.confdeltype) :: text as on_delete
FROM
    (SELECT
        ctn.nspname AS table_schema,
        ct.relname AS table_name,
        r.conrelid AS table_id,
        r.conname as constraint_name,
        r.oid as constraint_oid,
        cftn.nspname AS ref_table_table_schema,
        cft.relname as ref_table,
        r.confrelid as ref_table_id,
        r.confupdtype,
        r.confdeltype,
        UNNEST (r.conkey) AS column_id,
        UNNEST (r.confkey) AS ref_column_id
    FROM
        pg_catalog.pg_constraint r
        JOIN pg_catalog.pg_class ct
          ON r.conrelid = ct.oid
        JOIN pg_catalog.pg_namespace ctn
          ON ct.relnamespace = ctn.oid
        JOIN pg_catalog.pg_class cft
          ON r.confrelid = cft.oid
        JOIN pg_catalog.pg_namespace cftn
          ON cft.relnamespace = cftn.oid
    WHERE
        r.contype = 'f'
    ) q
    JOIN pg_catalog.pg_attribute ac
      ON q.column_id = ac.attnum
         AND q.table_id = ac.attrelid
    JOIN pg_catalog.pg_attribute afc
      ON q.ref_column_id = afc.attnum
         AND q.ref_table_id = afc.attrelid
 GROUP BY q.table_schema, q.table_name, q.constraint_name;

CREATE VIEW hdb_catalog.hdb_table_info_agg AS (
select
  tables.table_name as table_name,
  tables.table_schema as table_schema,
  coalesce(columns.columns, '[]') as columns,
  coalesce(pk.columns, '[]') as primary_key_columns,
  coalesce(constraints.constraints, '[]') as constraints,
  coalesce(views.view_info, 'null') as view_info
from
  information_schema.tables as tables
  left outer join (
    select
      c.table_name,
      c.table_schema,
      json_agg(
        json_build_object(
          'name',
          column_name,
          'type',
          udt_name,
          'is_nullable',
          is_nullable :: boolean
        )
      ) as columns
    from
      information_schema.columns c
    group by
      c.table_schema,
      c.table_name
  ) columns on (
    tables.table_schema = columns.table_schema
    AND tables.table_name = columns.table_name
  )
  left outer join (
    select * from hdb_catalog.hdb_primary_key
  ) pk on (
    tables.table_schema = pk.table_schema
    AND tables.table_name = pk.table_name
  )
  left outer join (
    select
      c.table_schema,
      c.table_name,
      json_agg(constraint_name) as constraints
    from
      information_schema.table_constraints c
    where
      c.constraint_type = 'UNIQUE'
      or c.constraint_type = 'PRIMARY KEY'
    group by
      c.table_schema,
      c.table_name
  ) constraints on (
    tables.table_schema = constraints.table_schema
    AND tables.table_name = constraints.table_name
  )
  left outer join (
    select
      table_schema,
      table_name,
      json_build_object(
        'is_updatable',
        (is_updatable::boolean OR is_trigger_updatable::boolean),
        'is_deletable',
        (is_updatable::boolean OR is_trigger_deletable::boolean),
        'is_insertable',
        (is_insertable_into::boolean OR is_trigger_insertable_into::boolean)
      ) as view_info
    from
      information_schema.views v
  ) views on (
    tables.table_schema = views.table_schema
    AND tables.table_name = views.table_name
  )
);

UPDATE hdb_catalog.hdb_version
   SET version = '19'
 WHERE version = '20';

and roll back to beta.5 We'll soon update the docs with this information.

marionschleifer commented 4 years ago

@Israel-Laguan I hope this issue is resolved. If you upgrade to the latest stable version and still encounter problems, please re-open this issue 🙂