标签：#dba

NBCUniversal and Sky will be spun off into separate companies.

How we decide is at the core of architecture, and the architecture advice process is a way to decentralize architectural decisions. It needs to be supported by Architecture Decision Records because of the speed at which technology and systems move, and can be complemented by a weekly architecture advice forum. By Ben Linders

PostgreSQL Error 22036: non numeric sql json item PostgreSQL error code 22036 ( non numeric sql json item ) occurs when a SQL/JSON path expression attempts to perform a numeric operation on a JSON item that is not a number — such as a string, boolean, array, or object. This error was introduced alongside the SQL/JSON Path feature in PostgreSQL 12 and typically surfaces in queries using jsonb_path_query , jsonb_path_exists , or the @@ and @? operators. Top 3 Causes 1. Numeric Values Stored as Strings The most common cause is JSON data where numbers are stored as quoted strings (e.g., "price": "100" instead of "price": 100 ). This frequently happens with data from external APIs or legacy systems that don't enforce type consistency. -- Triggers 22036: "price" is a string, not a number SELECT jsonb_path_query ( '{"price": "100"}' , '$.price + 50' ); -- ERROR: non numeric SQL/JSON item -- Fix: Use the .double() conversion method in JSON Path SELECT jsonb_path_query ( '{"price": "100"}' , '$.price.double() + 50' ); -- Result: 150 -- Alternative fix: Cast at the SQL level SELECT ( data ->> 'price' ):: numeric + 50 FROM ( SELECT '{"price": "100"}' :: jsonb AS data ) t ; -- Result: 150 2. Arithmetic Applied Directly to Arrays or Objects Developers sometimes write JSON Path expressions that target an entire array or object instead of individual elements, then attempt arithmetic on the result. -- Triggers 22036: $.scores returns an array, not a number SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores + 10' ); -- ERROR: non numeric SQL/JSON item -- Fix: Target a specific array index SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[0] + 10' ); -- Result: 90 -- Fix: Use wildcard to apply operation to each element SELECT jsonb_path_query ( '{"scores": [80, 90, 70]}' , '$.scores[*] + 10' ); -- Results: 90, 100, 80 -- Fix: Use unnest for aggregation use cases SELECT elem :: numeric + 10 FROM jsonb_array_elements ( '{"scores": [80, 90, 70]}' :: jsonb ->

Don’t be fooled by the compact size of this soundbar. It’s a solid option for smaller TVs or spaces without having to sacrifice sound quality.

PostgreSQL Error 22P01: Floating Point Exception PostgreSQL error code 22P01 is raised when a floating-point operation produces an exceptional result that cannot be represented as a valid number. This typically occurs during division by zero on float types, operations involving NaN (Not a Number), or arithmetic that yields Infinity . It is most commonly encountered in analytics, financial calculations, and data pipelines processing external or sensor data. Top 3 Causes 1. Division by Zero on Float Types Unlike integer division (which raises 22012 ), dividing a float by zero triggers a floating-point exception. This is especially common in ratio and rate calculations where the denominator can become zero at runtime. -- Problematic query SELECT total_sales :: float / total_orders :: float AS avg_order_value FROM daily_stats ; -- Safe fix using NULLIF SELECT date , total_sales :: float / NULLIF ( total_orders , 0 ):: float AS avg_order_value FROM daily_stats ; 2. NaN Values in Arithmetic Operations Data ingested from external systems, CSVs, or APIs may silently introduce NaN values into float columns. Once NaN participates in arithmetic, results become unpredictable and can trigger exceptions downstream. -- Detect NaN values (NaN is the only value not equal to itself) SELECT id , value FROM sensor_readings WHERE value != value ; -- Replace NaN with NULL safely UPDATE sensor_readings SET value = NULL WHERE value != value ; -- Filter NaN in aggregations SELECT device_id , AVG ( value ) FILTER ( WHERE value = value ) AS clean_avg FROM sensor_readings GROUP BY device_id ; 3. Infinity Arithmetic Conflicts Storing 'Infinity'::float or '-Infinity'::float is valid in PostgreSQL, but performing certain operations on them produces mathematically undefined results (e.g., Infinity - Infinity = NaN ), which can cascade into a floating-point exception. -- Check for Infinity values SELECT id , measurement FROM raw_data WHERE measurement IN ( 'Infinity' :: float , '-Infinity' :: float

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

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

The waiver "serves the public interest by promoting a second large satellite broadband constellation."

PostgreSQL Error 2200D: invalid escape octet The 2200D: invalid escape octet error occurs in PostgreSQL when a bytea value contains an invalid escape sequence. This typically happens with the legacy escape format for binary data, where octet values must be represented as three-digit octal numbers in the range \000 to \377 . If the escape sequence falls outside this range or uses non-octal digits, PostgreSQL raises this error immediately. Top 3 Causes 1. Out-of-range octal values in bytea escape literals The bytea escape format only accepts octal values from \000 to \377 (decimal 0–255). Using values like \400 or non-octal digits like \9 will trigger this error. -- BAD: \400 exceeds valid octal range (max is \377) SELECT E ' \\ 400' :: bytea ; -- ERROR: invalid escape octet -- BAD: \9 is not a valid octal digit SELECT E ' \\ 9AB' :: bytea ; -- ERROR: invalid escape octet -- GOOD: valid octal escape sequences SELECT E ' \\ 377' :: bytea ; -- decimal 255 SELECT E ' \\ 101' :: bytea ; -- 'A' character SELECT E ' \\ 000' :: bytea ; -- null byte 2. Using escape format strings with hex output format Since PostgreSQL 9.0, the default bytea_output is hex . Applications that mix hex -format output back into escape -format input processing can generate malformed escape sequences. -- Check current bytea output format SHOW bytea_output ; -- GOOD: Use hex format (recommended for all new projects) SELECT ' \x DEADBEEF' :: bytea ; SELECT ' \x 48656C6C6F' :: bytea ; -- 'Hello' -- GOOD: Use encode/decode for safe conversions SELECT encode ( ' \x DEADBEEF' :: bytea , 'hex' ); -- output as hex string SELECT encode ( ' \x DEADBEEF' :: bytea , 'base64' ); -- output as base64 SELECT decode ( 'deadbeef' , 'hex' ); -- hex string → bytea SELECT decode ( 'SGVsbG8=' , 'base64' ); -- base64 → bytea 3. Incorrect escaping during data migration or manual SQL When migrating binary data from other databases (Oracle, MySQL) or writing raw INSERT statements manually, developers often confuse octal and

0A000 feature not supported 는? PostgreSQL 에러 코드 0A000 은 현재 사용하려는 기능이 PostgreSQL에서 지원되지 않거나, 특정 컨텍스트에서는 사용할 수 없음을 의미합니다. 주로 트랜잭션 내부에서 허용되지 않는 명령을 실행하거나, 해당 버전의 PostgreSQL에서 아직 구현되지 않은 SQL 표준 문법을 사용할 때, 또는 복제(Replication) 환경의 제약으로 인해 발생합니다. 실무에서는 특히 CREATE DATABASE , VACUUM , CLUSTER 같은 명령을 트랜잭션 블록 안에서 실행하거나, Logical Replication 슬롯과 관련된 작업을 수행할 때 자주 마주치는 에러입니다. 주요 발생 원인 1. 트랜잭션 블록 내에서 허용되지 않는 DDL 명령 실행 PostgreSQL은 일부 DDL 명령을 트랜잭션 블록( BEGIN ... COMMIT ) 내에서 실행하는 것을 허용하지 않습니다. CREATE DATABASE , DROP DATABASE , CREATE TABLESPACE , DROP TABLESPACE , VACUUM , CLUSTER 등의 명령은 트랜잭션 컨텍스트 밖에서 단독으로 실행되어야 하며, 이를 무시하고 트랜잭션 내부에서 호출하면 0A000 에러가 발생합니다. 2. 특정 PostgreSQL 버전에서 지원하지 않는 문법 또는 기능 사용 SQL 표준에는 정의되어 있지만 PostgreSQL의 해당 버전에서 아직 구현되지 않은 기능을 사용할 때 이 에러가 발생합니다. 예를 들어, 구버전 PostgreSQL에서 LATERAL JOIN , MERGE 문, GENERATED ALWAYS AS (expression) STORED 컬럼 정의 등을 사용하거나, 특정 윈도우 함수 옵션 조합을 사용하는 경우 해당 에러를 만날 수 있습니다. 3. 논리 복제(Logical Replication) 또는 스트리밍 복제 환경에서의 제약 위반 Standby 서버나 Logical Replication 구독자(Subscriber) 측에서 쓰기 작업이나 특정 관리 명령을 실행하려 할 때 0A000 에러가 발생합니다. Hot Standby 상태의 서버에서 DDL을 실행하거나, Logical Replication 슬롯이 활성화된 상태에서 지원되지 않는 방식으로 복제 슬롯을 조작하려는 경우 이 에러를 마주치게 됩니다. 해결 방법 원인 1: 트랜잭션 블록 내 허용되지 않는 DDL 실행 트랜잭션 블록을 제거하고 해당 명령을 단독으로 실행하는 것이 핵심입니다. 아래는 잘못된 예제와 올바른 예제를 비교한 것입니다. ❌ 잘못된 예 (0A000 에러 발생) BEGIN ; CREATE DATABASE myapp_db WITH OWNER = myapp_user ENCODING = 'UTF8' LC_COLLATE = 'ko_KR.UTF-8' LC_CTYPE = 'ko_KR.UTF-8' ; COMMIT ; -- ERROR: 0A000: CREATE DATABASE cannot run inside a transaction block ✅ 올바른 예 (트랜잭션 블록 밖에서 실행) -- 트랜잭션 블록 없이 단독 실행 CREATE DATABASE myapp_db WITH OWNER = myapp_user ENCODING = 'UTF8' LC_COLLATE = 'ko_KR.UTF-8' LC_CTYPE = 'ko_KR.UTF-8' ; VACUUM 명령도 동일한 원칙이 적용됩니다. -- ❌ 잘못된 예 BEGIN ; VACUUM ANALYZE public . orders ; COMMIT ; -- ✅ 올바른 예: 트랜잭션 밖에서 단독 실행 VACUUM ANALYZE public . orders ; -- ✅ 특정 테이블만 선택적으로 VACUUM VACUUM ( VERBOSE , ANALYZE ) public . orders ; 애플리케이션 코드(예: Python psycopg2)에서 자동 커밋을 끈 상태로 VACUUM을 호출하는 경우도 흔한 실수입니다. import psycopg2 conn = psycopg2 . conn

ORA-00031: Session Marked for Kill — What It Means and How to Fix It ORA-00031 occurs when a DBA issues ALTER SYSTEM KILL SESSION but Oracle cannot terminate the target session immediately. Instead, Oracle marks the session as "KILLED" and waits for it to reach a safe termination point — typically after completing a rollback or releasing OS-level resources. This is less of a hard error and more of a transitional state that every Oracle DBA will eventually encounter. Top 3 Causes 1. Large Transaction Rollback in Progress When you kill a session mid-transaction, Oracle must roll back all uncommitted changes to preserve data integrity. The larger the transaction, the longer the session stays in KILLED status. -- Check rollback progress for KILLED sessions SELECT s . sid , s . serial # , s . username , t . used_ublk AS undo_blocks , t . used_urec AS undo_records FROM v $ session s JOIN v $ transaction t ON s . taddr = t . addr WHERE s . status = 'KILLED' ; 2. Unresponsive or Disconnected Client If the client network connection is broken or the client process has hung, Oracle cannot deliver the kill signal. The session lingers in KILLED state until the OS-level connection finally times out. -- Find the OS process ID (SPID) for stuck KILLED sessions SELECT s . sid , s . serial # , s . username , s . status , p . spid AS os_pid , s . machine , s . program FROM v $ session s JOIN v $ process p ON s . paddr = p . addr WHERE s . status = 'KILLED' ; 3. OS-Level I/O or Resource Wait Sessions blocked at the OS level (disk I/O stall, memory pressure, storage issues) cannot respond to Oracle's internal kill signal. In these cases, only an OS-level process termination will resolve the problem. -- Identify what the session was waiting on before being killed SELECT sid , serial # , status , event , wait_class , seconds_in_wait FROM v $ session WHERE status = 'KILLED' ; Quick Fix Solutions Option 1 — Use the IMMEDIATE keyword (recommended first step) -- Standard kill (asynchronous) AL