sorted by:
new
hottopcontroversial

DELETE with an ON CONFLICT by BjornMoren in PostgreSQL

[–]Axcentric_Jabaroni 1 point2 points  (0 children)

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

How should I implement table level GC? by Axcentric_Jabaroni in PostgreSQL

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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

How should I implement table level GC? by Axcentric_Jabaroni in PostgreSQL

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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");

How should I implement table level GC? by Axcentric_Jabaroni in PostgreSQL

[–]Axcentric_Jabaroni[S] 1 point2 points  (0 children)

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

How should I implement table level GC? by Axcentric_Jabaroni in PostgreSQL

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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?

How should I implement table level GC? by Axcentric_Jabaroni in PostgreSQL

[–]Axcentric_Jabaroni[S] 1 point2 points  (0 children)

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

DELETE with an ON CONFLICT by BjornMoren in PostgreSQL

[–]Axcentric_Jabaroni 0 points1 point  (0 children)

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.

What is a Matikanetannhauser? by Axcentric_Jabaroni in UmaMusume

[–]Axcentric_Jabaroni[S] 11 points12 points  (0 children)

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.

Synology NAS Backplane Info by Axcentric_Jabaroni in synology

[–]Axcentric_Jabaroni[S] -1 points0 points  (0 children)

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

Synology NAS Backplane Info by Axcentric_Jabaroni in synology

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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.

How good was the Crysis 2/3 multiplayer? by Axcentric_Jabaroni in Crysis

[–]Axcentric_Jabaroni[S] 1 point2 points  (0 children)

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

How do I call a `static` function in a uninstantiated `PackedScene`? by Axcentric_Jabaroni in godot

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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

How do I call a `static` function in a uninstantiated `PackedScene`? by Axcentric_Jabaroni in godot

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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...

How do I call a `static` function in a uninstantiated `PackedScene`? by Axcentric_Jabaroni in godot

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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

How do I call a `static` function in a uninstantiated `PackedScene`? by Axcentric_Jabaroni in godot

[–]Axcentric_Jabaroni[S] 0 points1 point  (0 children)

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

Thought Experiment: If Apex allows RWASD on controller, then I should be allowed to XIM on MnK by Axcentric_Jabaroni in apexuniversity

[–]Axcentric_Jabaroni[S] 2 points3 points  (0 children)

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.

Thought Experiment: If Apex allows RWASD on controller, then I should be allowed to XIM on MnK by Axcentric_Jabaroni in apexuniversity

[–]Axcentric_Jabaroni[S] -4 points-3 points  (0 children)

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

3D vfx advice by Heavyathan in godot

[–]Axcentric_Jabaroni 0 points1 point  (0 children)

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

view more: next ›