Scribbles for my memory

I wanted to search a year of audit history for one rare value. The table has an index on the timestamp, so I assumed a date filter would make the query cheap. The optimizer disagreed, and it was right.

Check the plan at a few window sizes instead of guessing:

explain plan for
select count(*) from audit_tbl a
 where sys_extract_utc(a.a_date_) >= sys_extract_utc(systimestamp) - interval '30' day(3)
   and a.some_col in ('...');

select * from table(dbms_xplan.display(null, null, 'BASIC +COST +ROWS'));

On a 6.2M-row, 523k-block table the results were:

• 7 days: index range scan, cost ~96K
• 30 days: full table scan, cost ~234K
• 90 days and 1 year: full table scan, cost ~234K

The two access paths scale differently. A full scan reads every block sequentially, many blocks per I/O, so its cost is the same whatever the window. An index range scan is cheap at the index itself. After that it fetches each matching row's table block one at a time, so its cost grows with the number of rows in the window.

How expensive those fetches are depends on the index's clustering factor:

select index_name, clustering_factor, num_rows from all_indexes where table_name = 'AUDIT_TBL';

A value close to the table's block count means rows with nearby keys sit together, so one block read serves many rows. A value close to the row count means nearly every row needs its own block read. Mine was 3.35M against 523k blocks, which is badly scattered. The break-even point landed somewhere between 7 and 30 days. Past that, forcing the index with a hint makes the query slower, not faster.

One related trap: if the index is function-based, for example on SYS_EXTRACT_UTC(a_date_), the predicate has to use the same expression. A plain a_date_ >= ... can't use that index at any window size.

Bash applies redirections from left to right. If opening an input file fails before stderr is redirected, Bash writes the open error to the original stderr.

read -r value < "$file" 2>/dev/null   # Input open happens first
read -r value 2>/dev/null < "$file"   # Stderr is redirected first

When a file may be missing and you want to hide Bash's open error, place 2>/dev/null before the input redirection. This changes where the diagnostic goes, not the command's exit status. It is not a universal “all output redirects first” rule; redirections run left to right, so their order determines which destination is active when each one is applied.

If you host anything customer-controlled under your own domain (say alice.app.example.com) and run your sign-in or billing site on another subdomain (billing.example.com), those customers can set cookies on your billing site. A cookie with Domain=example.com goes to every host under example.com. Signing your cookies doesn't help, because the attacker doesn't need to forge anything.

The attack is login CSRF by "cookie tossing". The attacker signs in to your site and copies their own valid, signed session cookie. A page on their subdomain sends:

Set-Cookie: session=<attacker's valid value>; Domain=example.com; Path=/

A victim opens that page, then visits your billing site. The browser sends the attacker's cookie, so the victim is signed in as the attacker, and whatever they buy is credited to the attacker's account. Having their own session doesn't reliably protect them either: the browser sends both cookies with the same name, and many parsers keep whichever comes last.

Picking a different hostname doesn't help while both hosts share the same registrable domain. The fix is one prefix on the cookie name:

Set-Cookie: __Host-session=<value>; Path=/; Secure; HttpOnly; SameSite=Lax

Browsers only accept a __Host- cookie that has Secure, has Path=/, and has no Domain attribute. It can only be set by the exact host that receives it, so a sibling subdomain can't create or overwrite it. Read and clear the cookie under the same prefixed name. Since the prefix needs Secure, keep an unprefixed name for plain-http local development.

The stronger, structural fix is to serve customer content from a separate registrable domain, the way GitHub uses githubusercontent.com, or to get that domain listed on the Public Suffix List. Either way, cookies stop crossing the boundary at all. __Host- is the change you can ship today.

我有一个手动发推的 CLI（Playwright 驱动登录状态的 Firefox 网页，所以不需要 X API key），还有一个多用户笔记应用（HappyNotes）把新笔记推入 Redis 队列同步到 Mastodon。想让它俩连起来：笔记一发，自动发推。但不想动共享后端、毕竟这是一个很私人的hack，也没法子支持其他用户。

演进三步

1. CLI → 微服务。 把发推核心抽成库，Node 内置 http 起一个服务，只监听 Tailscale IP（100.x.y.z:8090）——内网即鉴权，零依赖、零鉴权代码。任何 tailnet 机器都能入队：

curl -X POST http://100.x.y.z:8090/add \
  -H 'content-type: application/json' \
  -d '{"text":"hello","post_at":"2026-09-07T09:00:00+12:00"}'

2. 图片走 data URL。 别的机器没有你这台的文件系统，multipart 又要新依赖——所以图片在 JSON body 里以 data:image/png;base64,... 传输，Playwright setInputFiles 喂内存 buffer，最多 4 张。

3. 只读 Redis 旁观者。 这是关键设计：不 LPOP（会偷走 Mastodon 消费者的消息），只每秒 LRANGE queue + ZRANGEBYSCORE processing，用 redis-cli 子进程（还是零新依赖）。然后三道过滤：

userId == OWNER_USER_ID  &&  action == "CREATE"  &&  isPrivate == false

task.id 落盘去重（先写 seen 再发布，失败绝不重试——重试 = 重复推文），冷启动基线首轮只记已见不发布，防止积压刷屏。

代价：一个有意的竞态窗口

队列项最多活 5 秒（消费者 LPOP 后成功后 ZREM，删了就没了，没有 deleted 队列可拣漏）。所以旁观者"几乎不漏但不保证"——如果任务在两次轮询之间被消费完，就错过了。要修复的话要动HappyNotes后端（加一个 synced 队列，成功时 LPUSH + 1 小时 TTL），但只为我一个人需求改全站代码不太值，先接受这个妥协看看。

You'll run into this shape in PL/SQL procedures that need to serialize work per row:

SELECT clientid
  INTO l_locked_client_id
  FROM dataela.clients
 WHERE clientid = p_client_id
 FOR UPDATE;

The selected value is never used — the variable name admits it. The query exists purely to take a row-level exclusive lock, turning that client row into a mutex so a read-modify-write sequence can't be clobbered by a concurrent session. It raises NO_DATA_FOUND for free if the client doesn't exist.

Two things about that lock are easy to get wrong.

The lock outlives the procedure. A PL/SQL block is not a transaction boundary. When the procedure returns, the lock is still held — it belongs to the caller's transaction and is released only at COMMIT or ROLLBACK (or when the session dies, or on the implicit commit from a stray DDL statement mid-transaction, which drops it silently). So hold time is decided by the caller, not by the procedure that took the lock. If the caller runs for five minutes, the row is locked for five minutes. Worth a comment on the procedure saying exactly that.

The WAIT clause is the waiter's patience, not the holder's timeout.

FOR UPDATE              -- wait forever (default)
FOR UPDATE NOWAIT       -- fail immediately, ORA-00054
FOR UPDATE WAIT 5       -- give up after 5s, ORA-30006
FOR UPDATE SKIP LOCKED  -- skip locked rows (queue consumers)

Each clause constrains only the statement it's attached to, so whatever the holder wrote has no effect on how long anyone else waits. Oracle has no holder-side "release after N seconds" and no global lock timeout to protect you — if you don't want connections piling up behind a lock, every call site that might wait needs its own NOWAIT or WAIT n plus a handler that retries or returns "try again later". Breaking a lock from outside means ALTER SYSTEM KILL SESSION.

That default infinite wait is also what makes the pattern deadlock-prone: two procedures locking several client rows in opposite orders will wait on each other until ORA-00060. Lock in a consistent order, or use NOWAIT with a retry.

One thing you don't have to worry about: a plain SELECT never blocks on any of this. Oracle rebuilds the pre-change version of the row from undo and reads that, so reporting queries pass straight through a locked row — none of the WITH (NOLOCK) reflex SQL Server teaches. Only FOR UPDATE, UPDATE, and DELETE against the same row queue up behind you, and only that row.