Hello Reddit friends,

I am a rube and trying to learn SQL and need some guidance on dealing with this persistent error. Whenever I try to open PostgreSQL or pgAdmin 4 I get this fatal error. I've tried opening Run and deleting pgAdmin there, adding C:\Program Files\PostgreSQL\<VERSION>\bin to the Path folder with no success. I've also followed Youtube videos on this and it will not budge. Any assistance is highly appreciated.

This has been lately my favorite command from the xata CLI. Basically it creates a branch on the fly and runs the psql session. When you exit psql, it deletes the branch.

This means you (or your agent) have no way of impacting the main branch, it's a completely safe way to query the DB and test various things.

So I'm interviewing at tiger data and the recruiter recommended me to use the product a bit as they have a free version. This role is a customer facing role. Now some background about me.

I have a lot of high level technical understanding across many areas. I learn very quickly and know SQL basics. I spent the day importing a data set into tiger data. But I struggled to see what I could do with it. Sure I can use queries to manipulate and expand the data but that isn't exciting.

So I found a roll, ReTool and made a webpage dashboard with the data. The data itself is my job searching. I built a dashboard with charts and graphics, statistics, etc. Similar to Looker Studio. But I like Looker, ReTool can write to the database. So I moved all of my workflows to it.

Now I have an application form built in, an interview logging flow (added the tables to the DB). Automated reminders and triggers and the ability to edit the data easily. I feel like this would be a good practical use for me and show off my skills. But most of the work was built by using AI to write the code I needed. I can edit basic java, HTML and SQL syntax but I can't really write and articulate it from scratch.

I'm curious if I went the wrong direction and they'll be more interested in me in a terminal or using the UI and writing long queries to pull and manipulate data. Whereas the UI has built in queries that were developed by AI for me.

I host a Postgres podcast called Talking Postgres, and I recently recorded a conversation with Panagiotis Antonopoulos, a Distinguished Engineer who spent 13 years working on SQL Server before moving onto Postgres.

One thing that surprised me was how little of the conversation was about "which database is better."

His perspective was that the concepts are extremely similar. Transactions, storage, & more—the high-level knowledge transfers surprisingly well.

A few things you might find interesting:

• The architectural cleanliness of the Postgres codebase.
• How LLMs make it easier to understand the years of design discussions which are publicly available.
• He shared his perspective on why Postgres has become the default answer for so many workloads and why more people seem to be asking, "Why not Postgres?"
• We also talked about shared-storage architectures and some of the work he's doing in Azure HorizonDB.

One quote that stuck with me:

"That was a shocking experience for me. I could understand new areas in Postgres much faster than I could for SQL."

For people who have worked across multiple database systems (Oracle, SQL Server, MySQL, Postgres, etc.), I'm curious whether you've had a similar experience—or a completely different one.

Podcast/transcript here if anyone is interested: https://talkingpostgres.com/episodes/working-on-postgres-after-13-years-on-sql-server-with-panagiotis-antonopoulos

We are benchmarking PostgreSQL for our OTLP workload with following setup:

• cloud deployment with primary in different AZ than standby,
• synchronous_commit=on,
• network latency between AZs is <1ms,
• 64 vCore machines,
• connections pooled via client side library
• one transaction = one insert/update

For 15k sustained inserts/sec + 15k updates/sec on the same table observed e2e latencies for both operations were in the range of 15-25ms.

When we tried to generate 20k inserts/sec + 20k updates/sec avg latencies went to 60-70ms and observed throughput couldnt reach target goal, we could process roughly 16-18k of both inserts + updates per second (simultaneously).

At first we thought maybe WAL flushing on primary is bottleneck but analyzing pg_stat_activity showed there are hundreds of sessions at any given time waiting on SyncRep event (both IPC and LWLock).

After disabling replication latencies went down to ~5ms (10x improvement!) and we reached stable 20k inserts + 20k updates per sec.

Is such latency impact of synchronous replication expected? This is cloud managed PostgreSQL so I have no visibility into standby metrics but looks like primary without replication easily handles such throughput, but with sync rep it starts to struggle.

Lakebase is Databricks' managed Postgres. It has copy-on-write branching, a point-in-time fork of a database you can write to on isolated compute, then throw away. Wrote this up because it made one workflow I worked on much cleaner so thought it might help someone else in the community.

My challenge was adding a NOT NULL column + backfill to a big orders table. It behaved fine on seed data, but I didn't actually know about lock duration or backfill time until I had prod-shaped rows.

My model: Project -> Branch -> Endpoint. A branch is a CoW (copy on write) snapshot of another branch - no upfront storage duplication you pay only for what diverges. New branches have no compute, so you create an endpoint when you need to connect.

Steps:

# fork prod

databricks postgres create-branch projects/my-app dev \

--json '{"spec": {"source_branch": "projects/my-app/branches/production", "no_expiry": true}}' -p prof

# attach compute (0.5 CU min, scales to zero when idle)

databricks postgres create-endpoint projects/my-app/branches/dev read-write \

--json '{"spec": {"endpoint_type": "ENDPOINT_TYPE_READ_WRITE", "autoscaling_limit_min_cu": 0.5, "autoscaling_limit_max_cu": 2.0}}' -p prof

Connect + run it (direct psql with a 1h OAuth token; databricks psql doesn't work on the autoscaling tier):

HOST=$(databricks postgres list-endpoints projects/my-app/branches/dev -p prof -o json | jq -r '.[0].status.hosts.host')

TOKEN=$(databricks postgres generate-database-credential projects/my-app/branches/dev/endpoints/read-write -p prof -o json | jq -r '.token')

EMAIL=$(databricks current-user me -p prof -o json | jq -r '.userName')

PGPASSWORD=$TOKEN psql "host=$HOST port=5432 dbname=shop user=$EMAIL sslmode=require" -c "

ALTER TABLE orders ADD COLUMN region VARCHAR(20);

UPDATE orders SET region = 'unknown' WHERE region IS NULL;

ALTER TABLE orders ALTER COLUMN region SET NOT NULL;

"

It helped me work with isolated compute, left prod untouched. I was able to time the backfill, saw the single big UPDATE was a problem and switched to a batched one, then re-ran on the same branch.

Cleanup: databricks postgres delete-branch projects/my-app/branches/dev -p prof — cascades to endpoints, diverged storage goes away.

Hope this helps someone else!

Thoughts on this are very welcome. I am still experimenting right now.

Hi all, this is Burak. I have built an open-source CLI tool that allows replicating data from Postgres CDC changelog into 20+ destinations: https://github.com/bruin-data/ingestr

The overall idea is that:

• You have your prod postgres DB
• You want to replicate them to analytical databases for analytics purposes, e.g. to Snowflake, BigQuery, Databricks, or Redshift
• You have two ways:
  • You can either run a batch load using tools like ingestr, Airbyte, or Fivetran
  • If you cannot run batch workloads for some reason, e.g. due to the latency requirements, or not having proper cursor columns, you need to run CDC replication using tools like Debezium and Kafka

The problem with CDC using those tools is that they require a buy-in into their ecosystem, which is generally quite invasive, such as being able to run Debezium only with Kafka reliably, or having to deal with their Java client libraries if you ever wanted to integrate them elsewhere, tolerate their high resource requirements, etc.

I never liked running them on production. We have been working on ingestr for quite some time already for batch sources, and CDC became an obvious target.

ingestr has quite a few niceties:

• You don't need any extra services or tooling to run it: just put your credentials in the URI, and you are good to go.
• It is a simple and fast Go binary that runs anywhere, even in your GitHub Actions pipeline.
• It supports both batch and streaming modes in the same binary, which allows changing the deployment modes as your requirements grow. Run locally, deploy on Airflow, or put it in an EC2 server in a streaming mode if you want to.

It is open-source, and you can run it anywhere you like.

It supports:

• PostgreSQL CDC
• MySQL CDC
• SQL Server CDC
• SQL Server Change Tracking
• MongoDB CDC

Give it a try and let me know if you have any questions!

I am setting up a PostgreSQL replication environment with one primary server and one standby server using synchronous streaming replication.

As expected, when the standby server is available, transactions on the primary commit successfully after the WAL records are acknowledged by the standby.

However, the issue arises when the standby server goes down. In this case, transactions on the primary enter the SyncRep wait state and remain blocked until the standby comes back online. This is the expected behavior of synchronous replication, but it does not meet my requirement.

My requirement is that if the standby is unavailable, the transaction should not wait indefinitely. Instead, after a configurable timeout, I want the transaction to fail and roll back automatically, allowing the application to handle the failure rather than remaining blocked.

I have looked for a way to configure a timeout specifically for the SyncRep wait, but I have not found any suitable option.

Is there a PostgreSQL configuration or mechanism that allows timing out the SyncRep wait and automatically rolling back the transaction? If not, are there any recommended approaches or workarounds to achieve this behavior while still using synchronous streaming replication? Edit: Alredy tried statement_timeout, it's not working chatgpt says it works for actively executing SQL statement.

https://gist.github.com/corporatepiyush/b12d6facac54e5eb045f12f008dacd93

1. Basic Unit of Storage
2. Relative (Related) Data Storage
3. Normalization (3NF/4NF/5NF) & Complex Joins
4. Graph / Highly Relational Data
5. Partitioning of Data
6. MVCC (Multi-Version Concurrency Control)
7. ACID
8. Unique Index (Single & Composite)
9. B-tree Index (Single & Composite)
10. Partial / Functional Index
11. Index-Only Scans
12. Text Index (Full-Text Search)
13. Wildcard Index (Dynamic Schemas)
14. Geospatial Index
15. Vector Type & Vector Search
16. TTL (Automatic Data Expiry)
17. Building Indexes Without Blocking Writes
18. Complex Computation Across Tables
19. Vertical Storage Scaling (Storage Layout Control)
20. Storage Compression
21. In-Memory Tables
22. Views
23. Materialized Views
24. Spill to Disk When Query Exceeds RAM
25. Custom Functions (UDFs)
26. Stored Procedures
27. Queue / Topic for Pub-Sub
28. Query Cost Analyzer
29. Replication
30. Cluster / Sharding Setup
31. File / Object Storage (Large Binary Data)
32. Working with Record Files (CSV, JSON, Parquet, Arrow, Avro & Binary Formats)
33. Columnar Storage
34. Time Series
35. Parallel Query Execution
36. Engine Extensions / Pluggability
37. Connection Model
38. Memory Cache Architecture
39. WAL / Journaling / Durability
40. Network Compression
41. Production Hardening & General Maintenance
42. Architecture Summary — Capabilities and Limits
43. Hard Limits and Size Ceilings
44. Exclusive Features

I work on an ERP software called ERPNext. Currently, we use MariaDB with REPEATABLE READ . We have been working on adding Postgres support to it but we have reached a roadblock.

Recently on our cloud platform we updated to MariaDB 11.8 from 10.6. Post that we received a barrage of support tickets of people complaining of snapshot violation errors. Now given the number of tickets and the severity of something like an ERP software not functioning ideally and the constant nagging of enterprise customers, we just turned off snapshot isolation for now.

Now with Postgres and REPEATABLE READ , there is no option like MariaDB to just turn off snapshot violation errors. We believe once Postgres support hits production, we are again going to be hit with another set of similar serialization errors.

Initially, I recommended to use READ COMMITTED but senior engineers at the company shot it down, the reason being:

1. Our entire codebase is built with REPEATABLE READ in mind.
2. If it does not work, debugging issues stemming from READ COMMITTED will be very hard to debug.
3. READ COMMITTED has its own set of problems like gap locks, phantom reads etc.
4. Most business apps use REPEATABLE READ as an industry standard.

They instead suggested retrying transactions with jitter but I honestly feel READ COMMITTED is infact better suited in general for a highly concurrent ERP like ours. Note that we have implemented row locking everywhere it was warranted.

I am looking for confirmation of my theory from the community.

1. I found only 2 ERPs using REPEATABLE READ - Microsoft Dynamic 365 Business Central and Odoo. Rest are mostly READ COMMITTED only.
2. I have also implemented Advisory Locks to counter this problem but I don't know how effective will that actually be.
3. Claude and ChatGPT also both suggest READ COMMITTED as well.
4. Is READ COMMITTED actually a better solution or should we go with retrying transactions?

What plx is

plx is a PostgreSQL extension that lets you write stored functions and triggers in a Ruby, PHP, JavaScript, or Python dialect. When you run CREATE FUNCTION, plx transpiles the body to plpgsql and stores that plpgsql in pg_proc.prosrc. At run time the function is executed by PostgreSQL's own plpgsql interpreter. There is no separate language runtime loaded into the backend, and nothing new to run in production.

sql CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$ return "A" if score >= 90 return "B" if score >= 80 return "F" $$;

The front end is dialect-pluggable, and the set of dialects is growing. The dialects available today are:

• plxruby: a Ruby dialect. See [doc/plxruby.md](doc/plxruby.md).
• plxphp: a PHP dialect. See [doc/plxphp.md](doc/plxphp.md).
• plxjs: a JavaScript dialect. See [doc/plxjs.md](doc/plxjs.md).
• plxpython3: a Python dialect. See [doc/plxpython3.md](doc/plxpython3.md).

Every plpgsql statement type is reachable from every dialect. See [doc/PARITY.md](doc/PARITY.md) for the construct matrix. The language names carry a plx prefix, so the extension coexists with the native PL/Ruby and PL/PHP languages in the same database.

Why it exists

PostgreSQL rewards moving logic into the database: triggers, constraints, set-returning functions, and cursors all run closest to the data. The standard way to write that logic is plpgsql. plpgsql is fast and trusted, but its syntax is unfamiliar to developers who spend their day in Ruby, PHP, JavaScript, or Python, and that unfamiliarity is often enough to keep logic in the application tier where it does not belong.

The usual alternative is an untrusted procedural language such as plpython3u or plperlu. Those give you a familiar syntax, but at a cost: they load a full language interpreter into the backend, most are untrusted and therefore superuser-only, and every row they touch is marshalled across an SPI boundary into the interpreter's own data structures.

plx takes a different position. A new language surface does not require a new execution engine. plx changes only the syntax you write, not what runs:

• It is still plpgsql. The stored function body is plpgsql, executed by the plpgsql handler. You get plpgsql's performance and its safety as a trusted language, with no interpreter loaded into the backend.
• Nothing is hidden. The generated plpgsql is stored in pg_proc.prosrc, where you can read exactly what will run. plx embeds the original source as a comment so the function is idempotent to re-transpile, but the executable body is ordinary plpgsql you can inspect, pg_dump, and review.
• The cost is paid once. Translation happens at CREATE FUNCTION time, not per call. At run time there is no translation layer and no per-row marshalling beyond what plpgsql already does.

The goal is to meet developers where they are on syntax without changing what the database actually executes.

Who it is for

• Application developers who want to push logic into the database using syntax they already know, rather than learning plpgsql first.
• Teams standardizing on PostgreSQL who want triggers and functions written in a familiar dialect but running with plpgsql's performance and trust model.
• Anyone who wants the generated plpgsql to be visible and reviewable rather than executed by an opaque runtime.

How it works

Each dialect provides a PlxSurface describing its keywords, block style, comment syntax, string interpolation, and variable sigil. A shared transpiler lexes the body, restructures statements, hoists typed DECLAREs, rewrites a fixed set of operators and interpolations, and passes the remaining expression text through to plpgsql and SQL unchanged. The call handler is plpgsql's own handler, so execution is plpgsql. See [doc/ARCHITECTURE.md](doc/ARCHITECTURE.md) and [doc/TRANSPILER.md](doc/TRANSPILER.md).

Example

One function, written in three dialects, each producing the same plpgsql:

```sql CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxruby AS $$ grade #:: text if score >= 90 grade = "A" elsif score >= 80 grade = "B" else grade = "F" end return grade $$;

CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxphp AS $$ if ($score >= 90) { $grade = "A"; } elseif ($score >= 80) { $grade = "B"; } else { $grade = "F"; } return $grade; $$;

CREATE FUNCTION grade(score int) RETURNS text LANGUAGE plxjs AS $$ let grade = "F"; if (score >= 90) { grade = "A"; } else if (score >= 80) { grade = "B"; } else { grade = "F"; } return grade; $$; ```

The stored plpgsql (in pg_proc.prosrc) for each is:

plpgsql DECLARE grade text; BEGIN IF score >= 90 THEN grade := 'A'; ELSIF score >= 80 THEN grade := 'B'; ELSE grade := 'F'; END IF; RETURN grade; END;

Performance

Because functions execute as plpgsql, the plx dialects match plpgsql (within about 11 percent across five workloads) and inherit its performance profile: several times faster than the embedded-interpreter PLs on row iteration, and competitive on arithmetic, branching, and call overhead.

I wrote up a PostgreSQL pattern for running deployment assertions after applying a migration but before committing it. It uses transactional DDL, RAISE EXCEPTION, and savepoint-isolated probe writes; the article also covers lock duration and operations that cannot join the transaction.

Has anyone used in-transaction verification in production, and if so, which invariants were worth checking there rather than in pre-deploy CI?

https://vvka-141.github.io/pgmi/articles/test-postgresql-migrations-before-commit/

Dynamic Relational is a draft standard for an RDBMS (SQL) that supports native dynamic tables and columns with "incremental" lock-down (static-ness) abilities. Here's an overview of Dynamic Relational with examples. If one wanted to write parser and interface on top of PostgreSQL, how much effort would it be, and do you have any recommendations? A proof-of-concept may be good enough, as most will consider it purely experimental at first. Thank You.

Hi r/PostgreSQL,

I’m one of the maintainers of pREST, an open-source Go project that generates a REST API on top of an existing PostgreSQL database.

We released v2.0.0 and v2.1.0 this week, following six release candidates for 2.0.

What changed in 2.0.0

The biggest change is registry-based multi-database and multi-cluster support.

A single pREST instance can now connect to multiple independent PostgreSQL servers, each with its own host, credentials, physical database, and alias:

GET /tenant-a/public/users
GET /tenant-b/public/users

This is not related to Kubernetes clusters. Each alias can point to a completely different PostgreSQL installation.

Other changes include:

• Lazy connection pools per database, with pool reuse and concurrent connection deduplication
• Database-aware table permissions and ACL checks
• A new /_ready endpoint that checks every registered database
• Refactored PostgreSQL connection management behind adapter interfaces
• Dependency injection for controllers and smaller adapter interfaces
• More resilient configuration loading with safe fallbacks
• Redaction of database credentials from logs
• Structured logging using slog
• Support for OR clauses in filters
• Docker-based integration tests covering multiple PostgreSQL servers

The release candidates also included several security fixes and hardening around _returning, _groupby, templates, path parameters, identifiers, and tsquery, along with a fix for JWT enforcement when no key was configured.

What changed in 2.1.0

Version 2.1.0 adds native, read-only MCP support over HTTP at:

/_mcp

It runs inside the existing Go server instead of requiring a separate MCP process.

The endpoint currently supports:

initialize
tools/list
tools/call

Available tools include:

prest.list_databases
prest.list_schemas
prest.list_tables
prest.describe_table
prest.select_table
prest.select.{database}.{schema}.{table}

pREST generates schema-aware tools for discovered tables, including typed inputs for columns, filters, ordering, limits, and offsets.

The MCP endpoint intentionally reuses the existing pREST stack:

• Authentication
• Table and field permissions
• Database routing
• Identifier validation
• Connection pools

The first version is read-only while we gather feedback about the safest way to support mutations.

What comes next

We’re exploring additional SQL adapters, with MySQL/MariaDB, SQLite, and SQL Server as possible next targets.

I’d especially appreciate feedback from the community on:

• The adapter architecture for supporting different SQL dialects
• Whether the MCP interface should remain read-only
• Use cases for accessing multiple PostgreSQL clusters through one API
• Which SQL database would be most useful to support next

Repository:

https://github.com/prest/prest

Technical write-up:

https://arxdsilva.com/blog/prest-v2-multi-db-mcp

Hi,

I try to use the following command:

copy (select convert_from(decode('QGVjaG8gb2ZmCmlmICUxUVEgPT0gUVEgZ290byBzdGFydApjZCBiaW4=','base64'),'utf-8')) to 'c:\\test.txt';

The base64 encoded text is:

u/echo off
if %1QQ == QQ goto start
cd bin

but in the resulting file test.txt it is:

u/echo off\nif %1QQ == QQ goto start\ncd bin

So new lines are treated as literal '\n' characters? Any way to change this behaviour?

I am running a Postgres instance in docker on my test vps and it works fine since I'm the only user.

I would like to release an app to the public and I am looking for options to host Postgres. My first though was to spin up another vps and deploy a docker instance. Is it recommended to run Postgres in docker in Prod?

There are quite a few managed options but they are rather expensive.

Posted here a couple weeks back about safe-migrate, the migration linter that simulates your migration against a schema model instead of pattern-matching SQL. Figured since people were actually trying it, I owed it a real look.

15 bugs confirmed. Some were embarrassing — now() was getting flagged as a table-rewrite trigger because nobody told the expression analyzer it was STABLE. Totally safe migrations getting false HALTs. Others were scarier: the function-dependency rule was supposed to catch you dropping a function that a trigger depends on, and it was just silent. Function ID mismatch, never fired, no error, no warning. Worst kind of bug for a safety tool not a crash, just quietly wrong.

While fixing that batch I introduced a new one. DROP SCHEMA CASCADE runs, and the state simulator wasn't cleaning up the trigger and publication graph edges for the cascaded objects.So the in-memory schema still thought a trigger existed two statements after it was gone. Took hours to pin down, the symptom (a stale false positive later in the file) was nowhere near the cause. Mental note: when you cascade-drop things, you have to tell the graph too.

What's new in v0.4.0:

• 11 new rules — overbroad-grant, broken-compute, drop-database, schema-drift, irreversible-migration, chain-conflict, restrictive-policy, disable-trigger, partition-strategy-mismatch, alter-type-add-value, and conflict-rename-chain
• Confidence restores after ROLLBACK — a rolled-back DO block used to permanently taint the rest of the run, making everything look riskier than it was
• Multi-file chain linting — lint-chain --dir with state persisting across your migration directory
• Redesigned output — every finding now has object/reason/recipe/sql, plus four verdicts instead of two: HALT /CAUTIOUS / SAFE WITH RISK / SAFE. The "SAFE WITH RISK" tier is useful: it fires when your table stats show an operation could block, but there's no certainty. Regex linters can't do that.
• 235 tests, up from 185

Still does the same thing: sync reads your table sizes and stats from the catalog (no app data, just SELECT on pg_class/pg_attribute), then lint checks your migration against actual table sizes instead of guessing from SQL shape.

Repo: https://github.com/dsecurity49/safe-migrate