postgresql

At the Seattle PostgreSQL User Group meetup this past Tuesday, we got onto the topic of invalid pages in PostgreSQL. It was a fun discussion and it made me realize that it’d be worth writing down a bunch of the stuff we talked about – it might be interesting to a few more people too!

Invalid Page In Block

You see an error message that looks like this:

At the Seattle PostgreSQL User Group meetup this past Tuesday, we got onto the topic of invalid pages in PostgreSQL. It was a fun discussion and it made me realize that it’d be worth writing down a bunch of the stuff we talked about – it might be interesting to a few more people too!

Invalid Page In Block

You see an error message that looks like this:

At the Seattle PostgreSQL User Group meetup this past Tuesday, we got onto the topic of invalid pages in PostgreSQL. It was a fun discussion and it made me realize that it’d be worth writing down a bunch of the stuff we talked about – it might be interesting to a few more people too!

Invalid Page In Block

You see an error message that looks like this:

improving performance with stored procedures — a pgbench example.

In a previous post I mentioned that I do not use pgbench to benchmark the platform. But when it comes to measuring client/server application, pgbench fully makes makes sense.

I initialize the pgbench schema with small data:

pgbench - initialize - init-steps=dtgvpf -h localhost -p 5432 -U postgres franck

And I run the pgbench builtin workload with does something like a TPC-B

tpcb-like builtin

pgbench --builtin tpcb-like --transactions 30000 --protocol=prepared --jobs=10 --client=10 -h localhost -p 5432 -U postgres franck

I run 30000 transactions there, from 10 threads. It runs for more than 4 minutes:

TL;DR: similar syntax but very different transaction semantic between Oracle and PostgreSQL procedural blocks

I posted a tricky Quiz on Twitter (unfortunately forgot to mention explicitely that I have a unique constraint on DEMO1.N):

Quiz DEMO1 has 1 row with n=42 DEMO2 is empty I execute this anonymous block: begin insert into DEMO1 (n) values (1); insert into DEMO1 (n) values (42); exception when others then insert into DEMO2 select * from DEMO1; end; How many rows in DEMO2?

 — @FranckPachot

pgbench retry for repeatable read transactions — first (re)tries

Trying a no-patch solution for pgbench running on repeatable read transactions, using a custom script with PL/pgSQL

In a previous post I was running pgBench on YugaByteDB in serializable isolation level. But Serializable is optimistic and requires that the transactions are re-tried when failed. But pgBench has no retry mode. There was a patch proposed in several commit fests for that, but patch acceptance is a long journey in PostgreSQL:

WIP: Pgbench Errors and serialization/deadlock retries

MVCC in Oracle vs. PostgreSQL, and a little no-bloat beauty

Databases that are ACID compliant must provide consistency, even when there are concurrent updates.

Let’s take an example:

• at 12:00 I have 1200$ in my account
• at 12:00 My banker runs long report to display the accounts balance. This report will scan the ACCOUNT tables for the next 2 minutes
• at 12:01 an amount of 500$ is transferred to my account
• at 12:02 the banker’s report has fetched all rows

What balance is displayed in my banker’s report?

TL;DR: Everything is different about transaction behavior. This may also change your ideas about “database independent” applications.

I like to explain some PostgreSQL concepts from an oracle DBA point of view. There are many things that are different in the two RDBMS and it is important to understand them.

Auto commit

Here is a short example where I create a table, insert one row and rollback:

TL;DR: AUTOCOMMIT is required when calling a procedure which has some COMMIT inside.

In version 11 PostgreSQL has introduced the possibility to start, commit or rollback transactions in PL/pgSQL procedures (stored or anonymous). Most of the demos have been run from the psql default AUTOCOMMIT on, like 2ndQuadrant and dbi-services blogs. But Bryn Llewellyn (YugaByte) raised an issue when running without AUTOCOMMIT OFF (which, coming from Oracle, looks like the right choice). Here is my investigation on this.

custom vs. generic plans

With my Oracle database background, I know how important it is to balance the pros and cons of using bind variables (parameterized queries) vs. literals.