Postgres schema error in Database Manager on some specific tables

[cloudrendr]

OS info

• Operating System : Windows 10.0.19043
• Wappler Version : 4.7.2

Problem description

This problem was reported already with bug report 1648450646370, but not solved with the latest 4.7.2. release.

The latest error report: report1648794648445.zip (147.9 KB)

Unfortunately, project development can not continue until the problem is solved so it is a difficult situation to deal with. Apologies for the rant

To explain the situation again:

I have a PostreSql db and table ‘users’ containing all user detail for the system. As an own practice I don’t do any database schema updates in Wappler but rather on the database level itself through pgAdmin4.

The situation is that, seemingly after 4.7.1, this particular table’s schema, ‘users’, cannot be synchronized with the database.

I noticed the problem after starting to have some strange behavior with server actions involving the table since complaining that certain fields are missing. Only then it was discovered that the table schema is out of sync with the database.

From the bug report:

[2022-04-01 08:30:19.270] START refreshSchemaForNode
[2022-04-01 08:30:20.016] got columnInfo error: TypeError: val.lastIndexOf is not a function
at convertPostgresDataType (file:///C:/Users/nantu/AppData/Local/Wappler/resources/app/Shared/DMXzone/dmxAppCreator/UI/Databases/knex_dbTypeMappings.js:98:18)
at file:///C:/Users/nantu/AppData/Local/Wappler/resources/app/Shared/DMXzone/dmxAppCreator/UI/databaseManager.js:8:104152
at $each (file:///C:/Users/nantu/AppData/Local/Wappler/resources/app/Shared/DMXzone/dmxAppCreator/UI/jQuery/js_lib.js:588:10)
at file:///C:/Users/nantu/AppData/Local/Wappler/resources/app/Shared/DMXzone/dmxAppCreator/UI/databaseManager.js:8:103948
at processTicksAndRejections (internal/process/task_queues.js:93:5)

The schema for the users table is shown here:

Steps to reproduce

1.Screencast of problem
2.
3.

[Notum]

I faced same issue once and my work around was:

1. Completely wipe all tables from pgAdmin(I have my SQL script to fully re-create table structure)
2. Refresh schema on DB level
   

If after that you’ll still see all your tables:

1. Wipe all tables from Wappler itself
2. Refresh schema on DB level

[cloudrendr]

Hi Notum

Thanks for the input. Much appreciated.

It certainly looks like a solution, but a temporary one at best since the problem can arise again. With a large number of tables and data, I’m not certain what affect such drastic action will have on existing server connects and dynamic events for the existing pages.

Really hoping the Wappler team can look into it.

If not, I’ll have to implement you solution.

Regards

But I will

[George]

@Notum

Completely wipe all tables from pgAdmin

I don't think you mean to delete all tables ... that is quite drastic measure...

Just refresh the schema should be fine with F5

[George]

@cloudrendr

got columnInfo error: TypeError: val.lastIndexOf is not a function
at convertPostgresDataType

I see that our code is crashing when trying to convert some of the default values entered on your table field columns.

What is the exact definition of your table? Maybe post here the creation SQL - that you can see in pgAdmin for this table.

[cloudrendr]

Hi George

Thanks for the reply.

The tables create script is:

> -- Table: public.users
> 
> -- DROP TABLE IF EXISTS public.users;
> 
> CREATE TABLE IF NOT EXISTS public.users
> (
>     user_id integer NOT NULL DEFAULT nextval('users_user_id_seq'::regclass),
>     role integer NOT NULL,
>     status integer NOT NULL DEFAULT 1,
>     first_name text COLLATE pg_catalog."default" NOT NULL,
>     last_name text COLLATE pg_catalog."default" NOT NULL,
>     email text COLLATE pg_catalog."default" NOT NULL,
>     password text COLLATE pg_catalog."default" NOT NULL,
>     created time with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
>     verified boolean NOT NULL DEFAULT false,
>     notes text COLLATE pg_catalog."default",
>     CONSTRAINT users_pkey PRIMARY KEY (user_id),
>     CONSTRAINT unique_email UNIQUE (email),
>     CONSTRAINT fk_user_role FOREIGN KEY (role)
>         REFERENCES public.user_roles (user_role_id) MATCH SIMPLE
>         ON UPDATE NO ACTION
>         ON DELETE NO ACTION
>         NOT VALID,
>     CONSTRAINT fk_user_status FOREIGN KEY (status)
>         REFERENCES public.user_statuses (user_status_id) MATCH SIMPLE
>         ON UPDATE NO ACTION
>         ON DELETE NO ACTION
>         NOT VALID
> )
> 
> TABLESPACE pg_default;
> 
> ALTER TABLE IF EXISTS public.users
>     OWNER to postgres;
> 
> -- Trigger: create_profile
> 
> -- DROP TRIGGER IF EXISTS create_profile ON public.users;
> 
> CREATE TRIGGER create_profile
>     AFTER INSERT
>     ON public.users
>     FOR EACH ROW
>     EXECUTE FUNCTION public.create_profile();

[George]

Found the problem - it wss the default value of your “status” field.
It was numeric but we were wrongly assuming it is always string.

So it will be fixed in the next update. For now you can either remove the default or use it in the next update.

[cloudrendr]

Hi George

Many thanks! I will for the time being add the default value within the Wappler server connect action and not leave it to the database. But like creation timestamps, the database is the better place.

When will the next update be? just for planning purposes.

Many thanks again!

[George]

We might put an update on Monday to address some urgent issues as well, as the rest of the week we have a team building week

[cloudrendr]

Many thanks! I’m back on track. Table is back!

Looking out for the coming update.

I can only sing great praises to the Wappler Team and product!

[Teodor]

This has been fixed in Wappler 4.7.3

[cloudrendr]

Hi Teodor

Many thanks for the feedback. Yes, indeed, I could see the bug been fixed. Just as I was about to get depressed, my prayers were answered… by the Wappler Team.

Many thanks again.