Supabase•2y ago

Newbie question: database function not running and I have no idea why

I'm new to Supabase and SQL/Postgres so please forgive this. Hopefully it's something basic but I've been pulling my hair out for a few hours and it's one of those things that's tough to Google if you don't know what keywords to search for. The situation is: I've created a trigger that I want to execute every time a row on my covers table is inserted or updated. The trigger calls a function generate_tags() which grabs values from another table, songs, and runs some simple math to output an array of tags[], which is an enumerated type I set up in Supabase. The tags column isn't updating when I add or edit rows. I've tried inserting raise notice into my function but nothing shows up in the logs. How do I debug what's going on and where things are failing?

13 Replies

EvaOP•2y ago

Here's the SQL definition of my covers table:

create table
  public.covers (
    id bigint generated by default as identity,
    slug text not null,
    created_at timestamp with time zone not null default now(),
    original_id text not null,
    cover_id text not null,
    description text null,
    contributor text null,
    tags tags[] null,
    constraint covers_pkey primary key (id),
    constraint covers_slug_key unique (slug),
    constraint covers_original_id_fkey foreign key (original_id) references songs (id) on update cascade on delete set null,
    constraint covers_cover_id_fkey foreign key (cover_id) references songs (id) on update cascade on delete set null,
    constraint covers_contributor_check check ((length(contributor) < 50)),
    constraint covers_description_check check ((length(description) < 160)),
    constraint ids_cannot_equal check ((original_id <> cover_id))
  ) tablespace pg_default;

create trigger on_cover_creation
after insert
or
update on covers for each row
execute function generate_tags ();

create table
  public.covers (
    id bigint generated by default as identity,
    slug text not null,
    created_at timestamp with time zone not null default now(),
    original_id text not null,
    cover_id text not null,
    description text null,
    contributor text null,
    tags tags[] null,
    constraint covers_pkey primary key (id),
    constraint covers_slug_key unique (slug),
    constraint covers_original_id_fkey foreign key (original_id) references songs (id) on update cascade on delete set null,
    constraint covers_cover_id_fkey foreign key (cover_id) references songs (id) on update cascade on delete set null,
    constraint covers_contributor_check check ((length(contributor) < 50)),
    constraint covers_description_check check ((length(description) < 160)),
    constraint ids_cannot_equal check ((original_id <> cover_id))
  ) tablespace pg_default;

create trigger on_cover_creation
after insert
or
update on covers for each row
execute function generate_tags ();

garyaustin•2y ago

use raise log 'my value = %', somevar; to get info into the Postgres logs in the dashboard. Remember you have to meet all RLS on all tables unless the function(s) are "security definer" type. I did not read thru your code, just some general info.

EvaOP•2y ago

I'll try using raise log I currently have security definer set search_path = public

garyaustin•2y ago

That should eliminate RLS and point to logic error. Always better to do public.tablename that worry about search path.

EvaOP•2y ago

So change to security definer set public.covers? Currently both of my tables have RLS set to anon read/write access

garyaustin•2y ago

Not sure what you are referring to. security definer means you run as the role that created the table (in most cases Postgres which bypasses RLS). search_path is the public part. anon means you must be anon and not authenticated or signed in user. "security definer" type function would not care though.

EvaOP•2y ago

If I use the Supabase table editor UI and edit a cell in a row, that should trigger the update and the function, right?

garyaustin•2y ago

yes.

EvaOP•2y ago

oh! raise log did turn up something in the logs

garyaustin•2y ago

You don't show your function so hard to know. Also I'm on my way out for the night, so not really going to process it.

EvaOP•2y ago

okay. well I think this at least lets me know that the function is running, but the object is returning NULL should be enough to go on for now thanks for your help, goodnight! was able to get this all working :)

EvaOP•2y ago

https://genderswap.fm/tagged running a database function to automatically tag covers with attributes and then made pages to filter the tags with 😌

Genderswap.fm

Explore by tag on Genderswap.fm

Find covers that are slower (or faster), happier (or sadder), more danceable (or less), or other things.

ninja11•2y ago

@garyaustin hey gary,i have similar problem around db functions(i cant execute my function through triggers automatically ). if u will have time it will be a great favour for me to look at these ,- https://discord.com/channels/839993398554656828/1196396452797218826 thanks in advance !

Gaming

Programming

Newbie question: database function not running and I have no idea why

Did you find this page helpful?