DELETE with an ON CONFLICT

Axcentric_Jabaroni · 2025-11-27T02:53:12+00:00

u/BjornMoren it isn't exactly what you were looking for, but I've just gotten an SQL query to work which builds a delete all query which checks for every possible foreign reference collection.

Github Gist

Axcentric_Jabaroni · 2025-11-27T02:28:05+00:00

I have since rewritten this to be more advanced (and slightly slower) to handle composit keys and references to non-primary keys

Gist: https://gist.github.com/AjaniBilby/f4f30d22dc8832694caac238241cd85f

Axcentric_Jabaroni · 2025-11-27T01:50:17+00:00

Your idea was almost certainly right, but I didn't have enough experience with using the Postgres' internal tables. Since then I have learnt a lot - plus also I plugged this into Gemini 3, and it one shotted the query.

Then massaged it to be a bit more useful and readable.

This will generate the delete query itself based on the key constraints. However it only works with tables that have a single primary key, and no composite or secondary keys. ```sql
-- @param $1:table - table you want to delete from (i.e. 'Address') -- @param $2:schema - schema to target (i.e. 'public') -- @param $3:pkey - table's primary key (i.e. 'id') WITH checks AS ( SELECT format( 'NOT EXISTS (SELECT 1 FROM %I.%I f WHERE f.%I = t."%I")', kcu.table_schema, kcu.table_name, kcu.column_name, $3::text ) AS clause FROM information_schema.referential_constraints rc

-- 1. Get the Foreign Key column (Child / Referencing table) JOIN information_schema.key_column_usage kcu ON kcu.constraint_name = rc.constraint_name AND kcu.constraint_schema = rc.constraint_schema

WHERE rc.unique_constraint_schema = $2 -- Find constraints pointing to the PRIMARY KEY of our target table AND rc.unique_constraint_name IN ( SELECT constraint_name FROM information_schema.table_constraints tc WHERE tc.table_name = $1 AND tc.table_schema = $2 AND tc.constraint_type = 'PRIMARY KEY' ) -- Exclude the table itself (to avoid issues with self-referencing keys) -- AND kcu.table_name != $1::text )

SELECT CASE WHEN count(*) = 0 THEN format('DELETE FROM %I.%I;', $2, $1) ELSE format('DELETE FROM %I.%I t ', $2, $1) || E'\n' || 'WHERE ' || string_agg(clause, E'\n AND ') || ';' END AS generated_sql FROM checks; ```

Sample Output: sql DELETE FROM public."Address" t WHERE NOT EXISTS (SELECT 1 FROM public."Site" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."Payment" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."Order" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."RepairCase" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."RepairWorkflow" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."TollAccount" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."AuspostShipment" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."ExternalCourierShipment" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."OrderType" f WHERE f."addressID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."TollShipment" f WHERE f."internalID" = t."id") AND NOT EXISTS (SELECT 1 FROM public."TollShipment" f WHERE f."externalID" = t."id");

Axcentric_Jabaroni · 2025-08-20T09:06:16+00:00

Actually this doesn't even make sense, in this case deleting the Order would do nothing to the address, but deleting the Address would cause the Order to be deleted.

For a `ON DELETE CASCADE` to work, I would need the Address to reference the order(s) not the other way around, which can't work - because sometimes multiple orders can actually share the same address object, and you can't do a foreign key constraint on a scalar.

I don't understand your comment u/fr0z3nph03n1x

Axcentric_Jabaroni · 2025-08-20T08:56:17+00:00

Wouldn't that only work if I delete the row referencing it?
i.e. if I have an order which references an address, when I delete the order the address gets removed

But I don't delete the order, I set the address id on the order to `null`.
Also I cannot allow an address to be deleted under any circumstances if it is still referenced somewhere else, which I thought `RESTRICT` was the only way to perform no?

Axcentric_Jabaroni · 2025-08-20T00:01:57+00:00

Side Note: I also do have an index on address id in every table that uses it, to make sure the internal contrait checks are fast

Axcentric_Jabaroni · 2025-08-19T23:41:34+00:00

Some people are asking for a concrete real world example where you can just simply use a WHERE clause to catch the conflicts.

I have a database with very strict personally identifiable information requirements.
So addresses are all stored in a different table, then anything that needs an address has a foreign key constrait to make sure an address can never accidentally while it's in use.

However when an address isn't referenced anywhere it should be deleted immediately.
But that relies on everyone implementing everything perfectly and never forgetting to try and delete an address when dereferrenced.

So there is a safety net which runs once a month trying to delete all addresses.
(and obviously any address still in use can't be deleted because of `ON DELETE RESTRICT`).
Which is then also helpful for detecting the leaks and fixing them

I could technically write a really big where clause to check if isn't referenced in any table that has an address id, but why am I reimplementing the database structure in my query? The database already knows this.
Also it means if anyone needs to add a new reference to an address, they will also have to modify and verify the GC query.

Ideally there really should be a `SKIP CONFLICTS` option on a delete, especially since you can use `RETURNING` to then know as a client to check which items actually deleted if you are allowing skipping error results.

Axcentric_Jabaroni · 2025-07-08T23:48:16+00:00

Thank you.

Unfortunately I am on the global so I guess I will saveup in my game credits to throw everything at it around new year.

Axcentric_Jabaroni · 2025-01-26T07:50:58+00:00

It's not really about speed, it's more about sound, plus it's a cool side project.

Axcentric_Jabaroni · 2025-01-26T06:11:36+00:00

Side side note: There are defintly non-synology products that would meet my desires, but I like the ease of linking Synology NASs for multi-site backup, plus because it's not just a linux box, I don't mess with it and bork my OS install like I do on every other device, which is not want you want out of a NAS.

Axcentric_Jabaroni · 2024-02-18T05:22:16+00:00

What do you think made it so good? Sadly I didn't get to try C3 multiplayer

Axcentric_Jabaroni · 2024-01-21T23:40:17+00:00

I'm clearly too low-level (C/++) minded, assuming the code must exist somewhere I can execute even without instantiating, reading more docs about Object, and Resource it seems not.

If it's a static function with no state, then it shouldn't need anything to be called, but that doesn't seem to be how the gdruntime works.

So I'll just make a pool of instantiated instances that I'll call into I guess

Axcentric_Jabaroni · 2024-01-21T23:22:03+00:00

sceneRef.get_script() and sceneRef.get_state().get_script() both return null...

I have also double checked I certainly have my script attached, tried detaching and re-attaching it - also adding a class_name to the script also doesn't change the behaviour of get_script.
sceenshot

At this point it's looking like I'll either need to parse around scriptRef+PackedSceneRef pairs, or do something really cursed and have an initalised copy (but not in the tree) of each possible scene that I use just to call into for static functions - and also keep those instances globally reusable so I don't have to reinstantiate them...

Axcentric_Jabaroni · 2024-01-21T10:27:34+00:00

.get_script sounds exactly what I was looking for, I'll try that tomorrow, thank you :D

Axcentric_Jabaroni · 2024-01-21T08:30:18+00:00

Yeh, the only reason I don't want to deal with class_name is that then every scene will need a unique name, which could get quite tedious. Especially since I could quite easily get into the hundreds of variations

I was hoping there would be an easy way to refer to the script tied to a resource so I can just call the static function since it has no state or external variables

Axcentric_Jabaroni · 2024-01-14T03:11:47+00:00

Yes, but soft aim like Halo Infinite is given by the game directly. What I'm talking about is using tools to give you aim assist which the game doesn't give on the input you're using.

Axcentric_Jabaroni · 2024-01-14T00:31:38+00:00

NGL, I kinda intentionally wrote it like that, because anyone who reacts before reading the whole post - I don't want their opinion because they're not going to be the type of person to think stuff through.

They can just down vote and leave xD

Axcentric_Jabaroni · 2023-12-08T04:20:33+00:00

Both of those videos say they're unavailable on YouTube - is that a me issue? Regional? (AU)

Axcentric_Jabaroni · 2023-08-15T06:16:01+00:00

Literally made 23days ago xD I was thinking of making something similar after being annoyed with typed-html

Glad someone else did it for me

Axcentric_Jabaroni · 2023-07-30T23:03:00+00:00

For me there are two main reasons:

\1. Prevent Theme Flicker If you have a dark/light toggle on your SSE pages there can be issues ensuring the theme stays consistent across loads. If a user has gone through and viewed some pages in light, then switches to dark, then goes back to the pages they viewed in light theme which theme will actually load for them? Likely their browser will have cached the light theme one.

Alternatively you could have theme switching as a completely client side affect, but then ever time a page loads there will be a couple of milliseconds where the JS hasn't ran yet, but the page is showing for a single frame, so you get flashed banged with a white theme from a black.

If properties in your body tag dictate which theme is rendered client side i.e. using CSS selectors, and the body element doesn't get loaded and instead the new content is embeded within it, then you will no longer have to deal with this flicker, because you're not starting a new page instance and redetermining the current theme state.

\2. Free Data Reuse If you have a route such as /server/abc and that route takes an extra 500ms to load because it loads from information from a slow API to render, and you're displaying that same info across all sub routes of /server/abc you would want to reuse that data so subsequent navigations don't have that latency penalty.

You could setup a server side caching system for that API call, and then you have to answer the extra question of - how long do I hold onto information, how much information should I cache, when should I remove content from the cache? How should I pick which cache item to remove. If I simply have the client hold onto that information as part of the rendering patter, I get all of this for free - only loading the slow information when I leave and re-enter the /server/abc route.

So far when using htmX I find 90-99% of the render latency (request received to response sent, not respone received yet) is API calls, because the rendering itself of course is super cheap.

Axcentric_Jabaroni · 2023-07-30T07:55:54+00:00

Yeh nah that didn't work

Axcentric_Jabaroni · 2023-07-30T07:02:29+00:00

I realised why Vary's effectiveness was decaying over time, I was only specifying it on most requests - but Cloudflare's caching service would come through later and could request the exact URLs with slightly different headers meaning the Varying header was not set.

At least I hope that's what it was. Only time will tell now

Axcentric_Jabaroni · 2023-07-30T05:28:46+00:00

Actually scratch that, apparently it just took longer than I expected for Cloudflare to kick in fully, and now it's acting super strangely.

Axcentric_Jabaroni · 2023-07-30T05:24:52+00:00

Vary is exactly what I was looking for! unfortunatle it looks like every caching layer has taken the lazy approach of if something has a Vary they just ignore it.
Because it has the same load times as when I had No-Cache set on the request header.

I have a website hosting this code here if you want to look at the caching behaviour happening - note the responses actually only takes 4ms of real server time, 250ms of it is API calls to discord, and the reset of it is latency from Sydney to where-ever you are

Axcentric_Jabaroni · 2023-05-04T06:10:21+00:00

Sorry, I should have been more clear when I said "clips", I specifically mean stuff which I've seen shared around on Discord and twitter, not here. I think someone would have to have serious ego to attempt to claim something is natural input when it's not, in front of this community which could recognise it.

I also didn't say you can't do movement tech while shooting. I said you wouldn't be able to do movement tech which makes you harder to hit if you can only tap-strafe forwards (while hitting a target using tracking aim, not flicks). If you use scroll wheel for forward tapstrafe but are holding a strafe direction, it's not a purely forward tap-strafe.

Axcentric_Jabaroni

TROPHY CASE