标签：#data

PostgreSQL Error 2200G: Most Specific Type Mismatch PostgreSQL error code 2200G ( most_specific_type_mismatch ) is a SQL-standard data exception that occurs when a value's type does not match the most specific (most derived) type expected in a context involving type hierarchies, XML schema types, or user-defined structured types. It most commonly appears when working with composite types, domain hierarchies, or XML processing functions where type inheritance or derivation is in play. While relatively rare in everyday CRUD operations, it can be a significant pain point in enterprise applications with complex type systems. Top 3 Causes and Fixes 1. Composite or Domain Type Hierarchy Mismatch When a function expects a specific domain or composite type but receives a parent/base type, PostgreSQL raises 2200G. Always cast explicitly to the most specific required type. -- Define types CREATE TYPE base_info AS ( name TEXT , value INTEGER ); CREATE DOMAIN specific_info AS base_info ; -- Function expecting the specific domain type CREATE OR REPLACE FUNCTION handle_info ( data specific_info ) RETURNS TEXT AS $$ BEGIN RETURN ( data ). name || ': ' || ( data ). value ; END ; $$ LANGUAGE plpgsql ; -- WRONG: passing base type causes mismatch -- SELECT handle_info(ROW('test', 42)::base_info); -- CORRECT: explicit cast to the most specific type SELECT handle_info ( ROW ( 'test' , 42 ):: specific_info ); 2. XML Type Processing Mismatch Using XML functions like XMLTABLE or XMLCAST without explicitly matching the expected schema type can trigger this error. Always declare column types explicitly. -- Correct: explicitly typed columns in XMLTABLE SELECT * FROM XMLTABLE ( '//product' PASSING XMLPARSE ( DOCUMENT ' <products> <product> <id>1</id> <price>29.99</price> </product> </products> ' ) COLUMNS product_id INTEGER PATH 'id' , price NUMERIC PATH 'price' ); -- Explicit XMLCAST to resolve type ambiguity SELECT XMLCAST ( XMLQUERY ( '//price/text()' PASSING XMLPARSE ( DOCUMENT '<data><pri

There is a design assumption baked into almost every vector database and AI memory implementation that sounds reasonable until you watch it grow nodes in production: that remembering more is always better. Through testing and refining our AUDN code, that is not exactly correct. After running VEKTOR Slipstream against real development sessions for 99 days, the database held 1,413 stored memories across four namespaces. Looking at the importance score distribution, 83 percent of those memories sat below 0.25 out of 1.0, what the system considers the noise floor. The remaining 17 percent, just 60 memories out of 1,413, sat above 0.75 and dominated every recall result. This is exactly what a curation layer is supposed to produce. Those 1,154 low-scored memories are accurate. They are not deleted. They are retrievable by direct query. What they are not is important enough to compete with the 60 high-signal entries every time the agent needs context. AUDN penalised them gradually over hundreds of writes because similar, more specific, or more frequently reinforced memories covered the same ground better. The system created a hierarchy. Without curation, all 1,413 memories would compete equally for every recall slot — and the agent would consistently surface redundant, lower-value context alongside the things that actually matter. That is what standard vector memory looks like without a curation layer. A slow, invisible degradation that nobody notices until the agent starts confidently giving you answers that are three months out of date. Every memory node in Vektor carries an importance score between 0 & 1. When a memory is first stored, it receives a score based on the content’s estimated significance. That score is not fixed. Every time a new memory arrives that is semantically related but not directly contradictory, the compatible verdict for that existing memory takes a small redundancy penalty. The penalty is intentionally modest: a factor based on how similar the in

Imagine you lose your work laptop on a commute. It holds 3 years of customer PII, internal product roadmaps, and access keys to your company's cloud infrastructure. Without full disk encryption enabled, anyone who finds the device can access every file in 10 minutes or less with a free bootable USB tool. With encryption enabled? They'll never access your data, even if they brute-force the password for decades. Per IBM's 2025 Cost of a Data Breach Report, organizations that use encryption save significantly on breach costs compared to teams that skip encryption. As cyber threats grow more sophisticated, and quantum computing edges closer to breaking legacy cryptographic standards, encryption is no longer an optional add-on—it's a core requirement for every digital system. This guide breaks down everything you need to know about data encryption, from core concepts to 2026's latest post-quantum developments, with actionable best practices for teams of all sizes. Table of Contents Core Concepts of Data Encryption How Does Data Encryption Work? Key Data Encryption Algorithms (2026 Approved & Deprecated) Encryption for All 3 Data States: At Rest, In Transit, In Use Real-World Data Encryption Use Cases Encryption Standards & Compliance Regulations Data Encryption Best Practices Common Encryption Mistakes to Avoid 2024-2026 Encryption Trends & Future Developments Conclusion & Key Takeaways References Core Concepts of Data Encryption Data encryption is a cryptographic process that converts human-readable plaintext into unreadable scrambled ciphertext using mathematical algorithms and secret keys. Only authorized parties with the correct decryption key can reverse the process to recover the original plaintext. Core Benefits of Encryption Encryption provides three non-negotiable security properties: Confidentiality : Only authorized users can access sensitive data Authentication : Verifies the origin of encrypted data Integrity : Confirms encrypted data has not been tampered w

SQLite ON CONFLICT DO SELECT Proposal, PostgreSQL 19 Features & SQLite Critical Bug Today's Highlights This week in databases, a proposal seeks to expand SQLite's ON CONFLICT clause to match PostgreSQL 19's DO SELECT for advanced conflict resolution. Concurrently, an early look at PostgreSQL 19 highlights key features improving performance and data management, while a critical out-of-bounds read bug in SQLite's fossildelta.c extension reminds us of the importance of low-level code security. Request: Support "ON CONFLICT DO SELECT" to match Postgres 19 (SQLite Forum) Source: https://sqlite.org/forum/info/81840ccfecf0885ba4418152d6c7f164de00d189b2cf7c682690151b0 This forum post proposes extending SQLite's ON CONFLICT clause to include a DO SELECT action, mirroring a feature anticipated in PostgreSQL 19. Currently, SQLite supports DO NOTHING and DO UPDATE for handling unique constraint violations. The proposed DO SELECT would allow an application to retrieve existing rows that caused the conflict, providing more granular control over conflict resolution beyond simply ignoring or updating the data. This feature would be particularly useful in complex data pipelines or replication scenarios where knowing which existing data caused a conflict is necessary for subsequent application logic, such as logging, merging, or initiating alternative processing paths. The discussion highlights the growing convergence of SQL features across different database systems and the desire for enhanced compatibility and expressiveness in SQLite. Implementing DO SELECT would empower developers to build more robust and intelligent conflict resolution strategies directly within their SQL statements, reducing the need for multi-step application-side logic involving separate SELECT queries after a failed INSERT or UPDATE attempt. Such an addition could streamline data ingestion processes and improve transactional integrity for embedded SQLite applications. Comment: This feature would be a game-ch

The ShinyHunters hacking gang claims to have compromised the Oracle PeopleSoft servers of more than 100 organizations, including many universities.

Recently open-sourced by Microsoft, pg_durable is a PostgreSQL extension that enables durable workflows to run natively inside the database, eliminating the need for external orchestration systems. By Sergio De Simone

Stop writing SQL strings that look like a ransom note. Here's how to write queries that are readable, safe, and maintainable. The Problem With "Good Enough" SQL Formatting Most Python developers start here: user_id = 5 query = " SELECT * FROM users WHERE id = " + str ( user_id ) cursor . execute ( query ) It works. Until it doesn't — and when it breaks, it breaks badly : SQL injection, cryptic errors from mismatched types, and queries that take 45 minutes to debug at 2am. Let's fix that, permanently. 1. Never Concatenate User Input — Use Parameterized Queries This is rule #1 and it's non-negotiable. ❌ The Wrong Way (SQL Injection Waiting to Happen) username = request . args . get ( " username " ) # Could be: ' OR '1'='1 query = f " SELECT * FROM users WHERE username = ' { username } '" cursor . execute ( query ) If username is ' OR '1'='1 , your entire users table just got exposed. ✅ The Right Way: Parameterized Queries username = request . args . get ( " username " ) # psycopg2 (PostgreSQL) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) # sqlite3 cursor . execute ( " SELECT * FROM users WHERE username = ? " , ( username ,)) # SQLAlchemy Core from sqlalchemy import text result = conn . execute ( text ( " SELECT * FROM users WHERE username = :name " ), { " name " : username }) The database driver handles escaping. You never touch it. This pattern is immune to SQL injection by design. Gotcha: Note the trailing comma in (username,) . Without it, Python treats the string as an iterable and passes each character as a separate parameter. This is one of the most common beginner bugs. # 💥 Bug: passes ('a', 'l', 'i', 'c', 'e') instead of ('alice',) cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username )) # ✅ Correct: single-element tuple cursor . execute ( " SELECT * FROM users WHERE username = %s " , ( username ,)) 2. Multi-Line Queries: Triple Quotes + Consistent Indentation For anything longer than one clause, use tri

Most of the value in SpaceX's IPO is effectively a call option on the company's ambitious space data center plans.

ServiceNow is used by thousands of enterprises to automate their internal processes, but says several customers had data accessed because of a security bug.

With an initial capacity of 24 megawatts, the innovative data center uses seawater as a natural cooling system.

We train a model on handwritten digit classification. 99% accuracy . Then we train the same model on a new task — say, fashion item recognition. We go back and test it on digits. 34% accuracy . It has completely forgotten. Not gradually, not partially — almost entirely. What Just Happened? We trained a CNN on MNIST digits — 99.2% accuracy . After fine‑tuning on Fashion MNIST, it reached 91.1% accuracy . But when re‑evaluated on MNIST, accuracy collapsed to 33.9% . This collapse is catastrophic forgetting : the model’s weights shifted to optimize for the new task, erasing the old solution. Why did training on more data make the model worse at something it already knew? MNIST is handwritten digits (0–9). Fashion MNIST is clothing items like shirts and shoes. Both are 28×28 grayscale images, but the tasks are distinct. Why Does It Happen? The core issue is that the model relies on the same set of weights for both tasks. There is no separation or dedicated memory; every parameter is shared . When training shifts from Task A ( MNIST digits ) to Task B ( Fashion MNIST ), gradient descent simply minimizes the loss on the data it sees at that moment. It has no awareness that Task A ever existed. In the loss landscape, imagine two parabolic bowls: one for Task A and one for Task B. The optimum for Task A lies at θ A ∗ ​ , while Task B's optimum is at θ B ∗ ​ . As training on Task B progresses, the weights θ move towards θ B ∗ ​ . This movement inevitably raises the loss for Task A because its minimum is left behind. The root cause is the shared weight space. Gradient descent is a stateless optimizer; it only follows the current gradient signal. Since the minima for Task A and Task B are far apart, there is no single configuration of θ that satisfies both tasks simultaneously. This is why catastrophic forgetting occurs. Weight space can be visualized as an N-dimensional space, where each axis corresponds to one parameter. Every point in this space represents a full set of wei

Hello everyone! Following up on my previous post , Day 1 of my Modern Data Stack migration was an absolute rollercoaster of refactoring and deep data auditing. I’m moving our legacy system (spreadsheets and Qlik) into a robust pipeline using Python, ClickHouse, and dbt . Here is what went down over the last 24 hours. 1. From Messy Scripts to a Single, Parameterized Extraction Engine 🛠️ In the legacy setup, each company had its own folder, its own .env file, and its own duplicated Python extraction script. It was a maintenance nightmare. Yesterday, I completely refactored this structure: Centralized Configuration: Merged all separate environments into a single, global .env file at the root level, mapping all 8+ companies and their branches. Eliminated Code Duplication (DRY): Instead of having identical extraction logic copied across folders, I built a single, unified codebase. Now, we have one universal script for Sales, one for Stock, one for Orders, etc. The behavior changes dynamically based on the company argument we pass to the CLI (e.g., python -m extract.run extract --source company1 ). To speed up this refactoring, I used Claude to generate the initial application skeleton. Since the AI already had the context of our legacy extraction logic, translating it into this new clean architecture was incredibly smooth. 2. Highs and Lows: The Data Parity Challenge With the pipeline modernized, I ran the pilot ingestion for Company #1 . To minimize friction for our downstream BI consumers, I kept the ClickHouse Bronze tables structured 1:1 with the legacy CSV schemas. The Good News: The data ingestion into the Bronze layer worked flawlessly. Moving up to the Silver layer (where we do data cleaning and domain-specific transformations), everything validated beautifully. Row counts matched perfectly. The "Fun" Part (The $2 Million Gap): When I materialized the Gold layer (our consolidated group business models), I hit a massive wall. The new pipeline reported $2 million U

Adi Polak discusses the architecture required to transition from stateless prompts to state-aware, context-rich AI agents. Drawing on 15 years in distributed systems, she shares how engineering leaders can leverage Apache Kafka and Flink for real-time stream processing, dynamic memory tiering, and tool orchestration via MCP to solve token limits, cost spikes, and latency bottlenecks. By Adi Polak

Redis is fast. But self-hosting Redis on a serverless stack is a nightmare — cold starts, connection pool exhaustion, and managing a persistent server that your serverless functions keep hammering. Upstash solves this with an HTTP-based Redis API that scales to zero, charges per request, and works natively with Next.js App Router. This guide covers the patterns that actually matter in production: cache-aside with proper TTLs, SWR (stale-while-revalidate), session storage, and pub/sub. Real code, real trade-offs. Read the full article with all code examples at stacknotice.com Why Upstash Over a Traditional Redis Instance Standard Redis uses persistent TCP connections. Serverless functions don't maintain persistent connections — every invocation potentially opens a new one. At scale, you hit ECONNREFUSED or max connection errors that are annoying to debug and expensive to fix. Upstash's @upstash/redis client talks over HTTP/REST. No connection pool, no connection limit headaches. Each request is stateless. This is exactly what Next.js Server Components and Route Handlers need. Other advantages: Pay per request — a cache that never gets hit costs $0 Global replication — low latency from any Vercel edge region Native Edge Runtime support — works in Next.js middleware Free tier — 10,000 commands/day, no credit card needed Setup npm install @upstash/redis // lib/redis.ts import { Redis } from ' @upstash/redis ' export const redis = new Redis ({ url : process . env . UPSTASH_REDIS_REST_URL ! , token : process . env . UPSTASH_REDIS_REST_TOKEN ! , }) Pattern 1: Cache-Aside // lib/cache.ts import { redis } from ' ./redis ' export async function withCache < T > ( key : string , fetcher : () => Promise < T > , options : { ttl ?: number ; prefix ?: string } = {} ): Promise < T > { const { ttl = 300 , prefix = ' cache ' } = options const cacheKey = ` ${ prefix } : ${ key } ` const cached = await redis . get < T > ( cacheKey ) if ( cached !== null ) return cached const data = awai

I recently built GeoPrizm , a free and open-source dashboard for tracking bilateral relations through global news event signals. The idea is simple: instead of reading dozens of headlines every day and trying to guess whether a relationship is improving or worsening, can we turn public news event data into a readable trend signal? GeoPrizm is my attempt at that. Website: https://www.geoprizm.com/en GitHub: https://github.com/Haullk/relationship-temperature The problem International relations are usually discussed through headlines, speeches, official statements, and expert commentary. That is valuable, but it creates a few practical problems: It is hard to compare country pairs on the same scale. A single headline can feel more important than it really is. Readers often see conclusions before they see the underlying signals. Most non-specialists do not have time to follow every event in detail. I wanted a lightweight way to answer one question: Based on public news event signals, is this bilateral relationship trending more cooperative, neutral, or tense? Data source: GDELT GeoPrizm uses the GDELT global news event database. GDELT monitors global news coverage and converts news reports into structured event records. These records include fields such as: actor countries event date CAMEO event category GoldsteinScale value number of mentions number of articles source information For GeoPrizm, the key idea is to focus on events where two countries appear as actors, then aggregate the cooperation or conflict signals over time. From event signals to an index Each bilateral pair is converted into a 0-100 relationship index. The midpoint is 50. Above 50 means the recent signal is more cooperative or favorable. Around 50 means the signal is relatively neutral or mixed. Below 50 means the recent signal is more tense or conflict-heavy. The rough process is: Select recent GDELT events for a country pair. Keep events where both actors are present and the GoldsteinScale value is

PostgreSQL Error 2201X: invalid row count in result offset clause PostgreSQL error code 2201X ( invalid_row_count_in_result_offset_clause ) is thrown when the value provided to an OFFSET clause is not a valid non-negative integer. This commonly surfaces in applications that implement pagination using dynamic queries or user-supplied parameters, where the offset value may be negative, NULL, or a non-integer type. Top 3 Causes 1. Negative OFFSET Value The most frequent cause is a miscalculated page offset. When computing (page - 1) * page_size , a bad page number can produce a negative result. -- Triggers error 2201X SELECT * FROM orders ORDER BY created_at DESC OFFSET - 5 LIMIT 20 ; -- ERROR: invalid row count in result offset clause -- Fix: Use GREATEST to clamp the value to 0 SELECT * FROM orders ORDER BY created_at DESC OFFSET GREATEST ( 0 , - 5 ) LIMIT 20 ; 2. NULL Passed as OFFSET When an application fails to initialize or bind the offset parameter, NULL gets passed to the query. This often happens with ORMs or query builders where optional parameters are not explicitly set. -- Triggers error 2201X SELECT * FROM products ORDER BY id OFFSET NULL LIMIT 10 ; -- ERROR: invalid row count in result offset clause -- Fix: Use COALESCE to provide a default value SELECT * FROM products ORDER BY id OFFSET COALESCE ( NULL , 0 ) LIMIT 10 ; -- Parameterized query with safe fallback SELECT * FROM products ORDER BY id OFFSET COALESCE ( $ 1 :: BIGINT , 0 ) LIMIT COALESCE ( $ 2 :: BIGINT , 10 ); 3. Non-Integer Type (Float or String) Passing a float or a string that cannot be cleanly cast to a whole number causes this error. This typically happens when Python float values or unvalidated user input strings are interpolated directly into a query. -- Triggers error 2201X SELECT * FROM employees ORDER BY last_name OFFSET 10 . 7 LIMIT 5 ; -- ERROR: invalid row count in result offset clause -- Fix: Use FLOOR and explicit cast to BIGINT SELECT * FROM employees ORDER BY last_name OFFSET F

SQL is arguably the most widely used language in software engineering, yet it is often the least carefully written. Most teams enforce strict linting on their application code but leave SQL queries as a free-for-all. This guide covers the formatting rules that separate maintainable, team-friendly SQL from query spaghetti that haunts on-call rotations. Why Poorly Written SQL Is a Real Engineering Problem Unformatted SQL is not just an aesthetic issue - it is a correctness risk. Dense, run-on queries make it nearly impossible to spot accidental Cartesian products, missing GROUP BY clauses, or WHERE conditions that silently bypass indexes. By the time a performance problem surfaces in production, tracing it back to the root cause becomes a painful exercise in reading someone else's stream of consciousness. Rule 1: Keyword Capitalization SQL engines treat select and SELECT identically, but human readers do not. Always uppercase reserved keywords such as SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY. Keep table names, column names, and aliases lowercase. This single habit immediately creates a visual boundary between the logic structure of the query and the underlying data it operates on. Rule 2: Indentation and Clause Alignment Think of SQL clauses as layers in a data pipeline. Each major clause - SELECT, FROM, WHERE, GROUP BY, ORDER BY - should start at the left margin. Columns and filter conditions beneath them should be indented by 4 spaces (or 1 tab, as long as your team is consistent). This structure lets any reviewer skim the query top-to-bottom and understand the data flow at a glance. Rule 3: Trailing vs. Leading Commas This is a genuinely debated topic on data teams. Leading commas (placing the comma at the start of each new line) make version control diffs significantly cleaner when columns are added or removed. Trailing commas look more natural for developers coming from JavaScript or Python. Neither approach is wrong - what is wrong is mixing both styles

Why Your Vector Database Is Overpriced: Lucene's 32x Compression and Serverless Economics In 2026, the boundary between "search engine" and "AI infrastructure" has dissolved. What started as text indexing has become the backbone of retrieval-augmented generation, vector databases, and serverless AI pipelines. This is the story of how the oldest search technology in the Java ecosystem became the most important infrastructure you've never noticed. The Convergence No One Saw Coming Five years ago, if you said Apache Lucene would power the next generation of AI infrastructure, you'd have been laughed out of the room. Lucene was the boring Java library that powered Elasticsearch — reliable, yes, but hardly exciting. The action was in vector databases: Pinecone, Weaviate, Qdrant. The cool kids had moved on. That narrative died in 2025. What happened was a structural inversion. While vector-native databases optimized for one thing (fast similarity search), the real production pain points were everywhere else: hybrid search, metadata filtering, provenance tracking, multi-tenant security, and — most critically — the ability to query both your documents and your vectors in a single, unified system. Lucene didn't just survive this transition. It engineered it. Through a series of aggressive, hardware-native optimizations between versions 10.0 and 10.4, Lucene transformed from a text indexer into a vector search kernel capable of outperforming specialized databases while maintaining the operational maturity that enterprises actually need. And Elasticsearch, riding on Lucene's coattails, didn't just integrate vectors — it re-architected itself into a stateless, serverless platform that happens to do search. This post examines three layers of that transformation: the engine (Lucene), the platform (Elasticsearch), and the architecture (AI-native search infrastructure). Each layer tells a different story, but they share a common thread: the future of AI infrastructure is being buil

Imagine a simple money transfer scenario. John sends money to his friend Sarah. The system successfully deducts money from John's account, but before it can credit Sarah's account, the application crashes. Without proper safeguards, John's money would disappear from the system, creating inconsistent and unreliable financial records. To prevent this type of problem, database transactions are used. Transactions ensure that a group of related database operations either complete successfully together or fail together. If any part of the process encounters an error, all changes are reverted, ensuring that the database remains consistent. Transactions make database operations atomic. Atomicity means that all operations inside a transaction are treated as a single unit of work. Either every operation succeeds and is committed to the database, or all operations fail and are rolled back. Partial updates are never permanently stored. A database transaction is a group of one or more database operations executed as a single unit. Either all operations succeed together or all operations fail together. This guarantees database consistency even if an application crashes, a network failure occurs, or an unexpected error is encountered during execution. It is important to understand that a transaction is not simply a single database query. While individual queries such as save, update, or delete interact with the database, a transaction wraps multiple queries inside a controlled all-or-nothing boundary. This prevents partial updates and ensures data integrity throughout the process. Prerequisites Before starting, make sure you have the following: Basic knowledge of NestJS Basic understanding of TypeORM Basic knowledge of PostgreSQL Understanding of basic database operations (save, update, delete) in TypeORM Project Setup In this article, we will create a simple NestJS application to demonstrate the importance of transactional queries when multiple database write or update operations

If you scrape Weibo's hot-search board you get a snapshot: ~50 trending topics, ranked, right now. That's table stakes — and on its own it's almost useless as a signal. The value isn't what is trending; it's what's moving : which topic just jumped 30 places in 20 minutes, which is decaying, which is brand-new this hour. That's velocity , and velocity is where the signal lives — for brand-crisis teams, consumer-trend desks, and anyone modelling attention in China. The catch: a single scrape can't tell you velocity. You have to diff the board against its own past, reliably, run after run. That's a stateful pipeline, and it has a few non-obvious gotchas. Here's the shape of the problem and how to handle it. Why a snapshot isn't enough Rank-right-now tells you nothing about trajectory. "#7" could be a topic on its way to #1 or one fading out of the top 50 — same row, opposite meaning. To act on a trend you need the derivative : direction, speed, and how long it's been climbing. None of that is in a single pull. The trending-delta problem Three things make "just diff the board" harder than it looks: Key by identity, not position. You can't track a topic by its rank — rank is the thing that changes. Key by the topic itself (its text/keyword) or your deltas are nonsense. State has to survive between runs. A scheduled scrape is stateless by default — each run starts cold. To compute "this rose 12 places since 30 minutes ago," you must persist the previous board and reload it next run, keyed so independent schedules don't overwrite each other. The board churns. Topics appear, peak, and fall off. You want each tagged new / rising / falling / steady / dropped , plus how long it's been on the board and its running peak — none of which exist in the raw snapshot. How to handle it (the pattern) current = pull_board () # [{topic, rank, heat}, ...] previous = load_state ( key ) # durable store that persists across runs for t in current : prev = previous . get ( t . topic ) # match o