IRC log for #evergreen, 2025-05-30

All times shown according to the server's local time.

Time	Nick	Message
08:38		redavis joined #evergreen
08:39		mmorgan joined #evergreen
08:57		Dyrcona joined #evergreen
09:13		dguarrac joined #evergreen
10:18		mantis1 joined #evergreen
10:20	mantis1	Can patron notes be deleted within the db? I can't run DELETE FROM and when I set the deleted flag to true, the note still appears within the GUI. But if I remove the note manually in the GUI, it does in fact go off the account.
10:34	Dyrcona	Probably a browser cache issue.
10:38	mantis1	the note does have a 'Deleted' flag within the text box but it remains on the account
10:38		sandbergja joined #evergreen
10:40	Dyrcona	Sounds like desired behavior, then.
10:41	Dyrcona	You can delete them in the database, you just need to disable the delete trigger on the table while you do it.
10:47	mantis1	'sorry to ask but how can I do that?
10:47	mantis1	never came up before
10:49	mmorgan	mantis1: I think the deleted flag in actor.usr_message only hides the message from the patron in the opac.
10:50	Dyrcona	alter table actor.usr_message disable trigger protect_usr_message_delete;
10:50	Dyrcona	When you're done, change disable to enable.
10:50	mantis1	Dyrcona++
10:50	mmorgan	If the message is linked to a penalty (actor.usr_standing_penalty), it will still show in the client until the linked penalty is removed.
10:50	mantis1	mmorgan: ah ok that might be it then
10:53	mantis1	mmorgan: hm stayed on
10:54	mantis1	is protect_usr_message some kind of 'invisible' column within the db? just curious
10:54	mantis1	ah ok it come sup with this error
10:54	Dyrcona	It's a trigger. If you do '\dt actor.usr_message` in psql you'll see it.
10:54	mantis1	'trigger "protect_usr_message_delete" for table "usr_message" does not exist'
10:55	mantis1	Dyrcona++
10:55	mantis1	so this needs to be run via command line
10:55	Dyrcona	How are you running queries in the database?
10:56	mantis1	just through PGAdmin but we sometimes do it via terminal - those are mostly for queries that take a longer time or for the update queries
10:58	Dyrcona	You should be able to type that into PgAdmin as part of your SQL. I wouldn't do it during normal business hours.
10:59	mantis1	I was running everything on a test server but the error I posted earlier still showed up
10:59	Dyrcona	PgAdmin should be able to show you the triggers on a table. It has been a while since I used it.
10:59	Dyrcona	What Pg version?
10:59	mantis1	4
10:59	Dyrcona	What Evergreen schema version?
11:00	Dyrcona	You can't be using Pg 4. That's your PgAdmin version.
11:00	mantis1	like the Eg version? This server is on 3.13.6
11:00	Dyrcona	The PostgreSQL server version.
11:01	mantis1	this is Postgres 14
11:01	mantis1	sorry
11:02	Dyrcona	Oh. My mistake. It's a RULE not a trigger.
11:03	Dyrcona	alter table actor.usr_message disable rule protect_usr_message_delete;
11:04	Dyrcona	It's deferred, so if you enable before the end of a transaction, I think it will take effect again.
11:06	mmorgan	mantis1: If you want to try something without disabling the rules or triggers, you could try deleting the row from usr_standing_penalty and setting the deleted flag in the associated usr_message to TRUE. That removed it from the client for me.
11:07	mantis1	I did try running a DELETE FROM query and nothing happened though it looks like something is happening with the rule disabled
11:08	Dyrcona	Oh, never mind. rules can't be deferred. I'm sorry. It's Friday...
11:08	Dyrcona	mantis1: I'd try what mmorgan says. There's probably a penalty associated with the messages.
11:09	Dyrcona	If the usr_message is already "deleted" then nothing will happen with the rule enabled because all it does is set the deleted flag on the row.
11:09	mantis1	yeah I just tried enabling the rule and it says DELETE 0 as the outcome
11:09	mantis1	but something else comes up with it disabled
11:10	Dyrcona	A constraint on actor.usr_standing_penalty?
11:10	mantis1	yes
11:10	mmorgan	So it looks like there's a constraint on usr_standing_penalty that deletes the usr_message.
11:11	mmorgan	But the usr_message has a rule to set the flag instead of actually deleting the row.
11:13	Dyrcona	I don't think it deletes the usr_message. There's no 'on cascade delete' on the standing penalty constraint.
11:13	Dyrcona	You'll have to delete them both, I think.
11:15	Dyrcona	The client may take care of that if you remove the standing penalty that way. I'm not sure/haven't looked.
11:16	Dyrcona	schema design is fraught....
11:16	mmorgan	Oops! I read it wrong! Dyrcona is correct, no on delete cascade for usr_message.
11:18	* Dyrcona	gives up on converting a comment to POD before lunch.
11:37	csharp_	mantis1: we have a nightly cron: https://pastebin.com/zvSnYJ6s
11:37	csharp_	we haven't altered the rule/trigger/whatever
11:38	csharp_	note: in our case, this creates tons of table bloat - haven't figured out how to delete huge chunks of table data without bloating
11:39	Dyrcona	csharp_: vacuum full the table after.
11:39	Dyrcona	Disabling the rule should actually delete the rows.
11:40	Dyrcona	Also, the ad that I'm getting on pastebin.....
11:41	* Dyrcona	uses Chrome and adblock is not a thing any more...
11:42	* Dyrcona	decides it is time to get some lunch...
11:44	Dyrcona	Bleh. Everything thinks I'm in CT because of Verizon Wireless.
11:51	csharp_	Dyrcona: yeah, I should probably automate that
11:51	csharp_	Everything's Coming Up Connecticut
11:52	Dyrcona	"How could I stop him, sir, when I don't know which way is Connecticut?"
11:53	Dyrcona	That's for the old movie buffs.
11:54	* Dyrcona	ordered Jersey Mike's for pickup.
11:56	mantis1	ok it ended up that I could delete the messages; it's just that we had some massive mess when we were making custom standing penalties and linking custom notes
11:56	mantis1	we == me
11:56	mantis1	so we figured it out but thank you Dyrcona for giving me more info on that
12:00		jihpringle joined #evergreen
12:09	csharp_	@decide Jersey Mike's or Connecticut Tony's
12:09	pinesol	csharp_: go with Jersey Mike's
12:17	Dyrcona	mantis1++ Glad you worked it out.
12:26	Dyrcona	hm.. where did I leave that query to see how much space a table takes up?
12:30	Dyrcona	csharp_: After running your script on my test database, my actor.usr_message table was 363 MB. Doing a vacuum full on it dropped it to 193 MB. You could just add the vacuum full after your delete.
12:30	Dyrcona	Think I'll drop the rule and deleted the deleted rows to see what happens.
12:32	Dyrcona	Now it's only 32 MB. :)
12:33	Dyrcona	Not gonna do that in production, but there it is.
13:49	csharp_	Dyrcona: thanks - I have been doing occasional VAC FULLs but I agree that adding that step to the script would work - it does cause a lot of query backups when running because of the exclusive lock on the table, but we run that in the middle of the night EDT
14:01	eeevil	csharp_: sounds like it's time to adjust autovac and page fill settings...
14:01	Dyrcona	meh. It has been a long week for a four-day work week.
14:04		jihpringle joined #evergreen
14:06	eeevil	csharp_: if things are really out of hand, past the point where vac full can finish before it's too painful for users, I'd recommend looking at pg_repack
14:07	Dyrcona	csharp_: Are you doing vaccum full of the whole database or just the table?
15:06	csharp_	Dyrcona: just the table
15:06	csharp_	eeevil: I'll look at our vac/page settings and pg_repack
15:11	csharp_	huh - now I remember that I investigated pg_repack before because it's installed as an extension - guess it's time to crack my knuckles and get it running :-)
15:13		mantis1 left #evergreen
15:57	JBoyer	Can confirm, pg_repack is chef's kiss My only gripe is that I had hoped that increasing -j would engage postgres parallel workers but no, it's basically just how many indexes it will create simultaneously (still helpful! just unevenly depending on the table)
16:39		jihpringle joined #evergreen
16:45		Christineb joined #evergreen
16:53		abowling joined #evergreen
16:54	abowling	Good afternoon. VS Code question. I'm running into a permission denied issue with my private key on a Windows OS. Someone showed me some really stupid fix to correct this? I don't remember whom, but any help?
16:56		mmorgan left #evergreen