Store message text as UTF-8 bytes and migrate existing rows without loss. Cover HTTP round trips, restart persistence, Unicode limits and idempotent retries. Adapt direct database tools to the binary storage format.
72 lines
3.3 KiB
Python
72 lines
3.3 KiB
Python
"""Seed one synthetic chat and explain the two real history page queries."""
|
|
|
|
import argparse
|
|
import secrets
|
|
from datetime import datetime, timezone
|
|
from uuid import uuid4
|
|
|
|
from src.store import Store, connection
|
|
|
|
|
|
def explain(conn, label, query, params):
|
|
print(f"\n{label}: EXPLAIN (ANALYZE, BUFFERS)")
|
|
for row in conn.execute("EXPLAIN (ANALYZE, BUFFERS) " + query, params):
|
|
print(row["QUERY PLAN"])
|
|
|
|
|
|
def main():
|
|
parser = argparse.ArgumentParser(description=__doc__)
|
|
parser.add_argument("--count", type=int, default=10_000)
|
|
parser.add_argument("--deep-offset", type=int, default=8_000)
|
|
args = parser.parse_args()
|
|
if args.count < 10_000 or not 0 < args.deep_offset < args.count - 50:
|
|
parser.error("count must be >=10000 and deep-offset must leave a full 50-row page")
|
|
|
|
store = Store()
|
|
user = store.register("u_" + uuid4().hex[:16], "Synthetic history", secrets.token_urlsafe(18))
|
|
chat = store.create_chat(user["id"], "Synthetic history", [user["id"]])
|
|
base = datetime.now(timezone.utc)
|
|
with connection() as conn:
|
|
conn.execute(
|
|
"INSERT INTO messages (id,chat_id,sender_id,text,created_at) "
|
|
"SELECT gen_random_uuid(), %s, %s, convert_to('synthetic-' || n, 'UTF8'), "
|
|
"%s::timestamptz + n * interval '1 microsecond' "
|
|
"FROM generate_series(1,%s) AS n",
|
|
(chat["id"], user["id"], base, args.count),
|
|
)
|
|
conn.execute("ANALYZE messages")
|
|
count = conn.execute("SELECT count(*) FROM messages WHERE chat_id=%s", (chat["id"],)).fetchone()["count"]
|
|
assert count == args.count
|
|
index = conn.execute(
|
|
"SELECT indexdef FROM pg_indexes WHERE tablename='messages' AND indexname='messages_history_idx'"
|
|
).fetchone()["indexdef"]
|
|
cursor = conn.execute(
|
|
"SELECT created_at,id FROM messages WHERE chat_id=%s "
|
|
"ORDER BY created_at,id OFFSET %s LIMIT 1",
|
|
(chat["id"], args.deep_offset - 1),
|
|
).fetchone()
|
|
projection = "SELECT id,chat_id,sender_id,text,created_at FROM messages WHERE chat_id=%s"
|
|
first = projection + " ORDER BY created_at,id LIMIT 51"
|
|
deep = projection + " AND (created_at,id)>(%s,%s) ORDER BY created_at,id LIMIT 51"
|
|
first_params = (chat["id"],)
|
|
deep_params = (chat["id"], cursor["created_at"], cursor["id"])
|
|
first_rows = conn.execute(first, first_params).fetchall()
|
|
deep_rows = conn.execute(deep, deep_params).fetchall()
|
|
assert len(first_rows) == len(deep_rows) == 51
|
|
assert not {row["id"] for row in first_rows} & {row["id"] for row in deep_rows}
|
|
assert all((a["created_at"], a["id"]) < (b["created_at"], b["id"])
|
|
for rows in (first_rows, deep_rows) for a, b in zip(rows, rows[1:]))
|
|
print("Lab 2 U5 history index evidence")
|
|
print("UTC:", datetime.now(timezone.utc).isoformat())
|
|
print("PostgreSQL:", conn.execute("SHOW server_version").fetchone()["server_version"])
|
|
print("Synthetic chat messages:", count)
|
|
print("Deep cursor ordinal:", args.deep_offset)
|
|
print("History index:", index)
|
|
print("Verified: first/deep pages each have 51 ordered rows with no shared IDs")
|
|
explain(conn, "First page", first, first_params)
|
|
explain(conn, "Deep page", deep, deep_params)
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main()
|