One of my clients hit a strange limitation – apparently you cannot inherit CREATE ROLE privilege.
First, let's test if it's really true:
Continue reading CREATE ROLE privilege cannot be inherited?!
Tag: postgresql
Waiting for 8.5 – Machine readable EXPLAIN
After very long discussions, on 10th of August, Tom Lane committed patch by Robert Haas:
Log Message: ----------- Extend EXPLAIN to support output in XML or JSON format. There are probably still some adjustments to be made in the details of the output, but this gets the basic structure in place. Robert Haas
Waiting for 8.5 – Multi-threaded pgbench
On 3rd of August, Tatsuo Ishii committed patch by ITAGAKI Takahiro:
Log Message: ----------- Multi-threaded version of pgbench contributed by ITAGAKI Takahiro, reviewed by Greg Smith and Josh Williams. Following is the proposal from ITAGAKI Takahiro: Pgbench is a famous tool to measure postgres performance, but nowadays it does not work well because it cannot use multiple CPUs. On the other hand, postgres server can use CPUs very well, so the bottle-neck of workload is *in pgbench*. Multi-threading would be a solution. The attached patch adds -j (number of jobs) option to pgbench. If the value N is greater than 1, pgbench runs with N threads. Connections are equally-divided into them (ex. -c64 -j4 => 4 threads with 16 connections each). It can run on POSIX platforms with pthread and on Windows with win32 threads. Here are results of multi-threaded pgbench runs on Fedora 11 with intel core i7 (8 logical cores = 4 physical cores * HT). -j8 (8 threads) was the best and the tps is 4.5 times of -j1, that is a traditional result. $ pgbench -i -s10 $ pgbench -n -S -c64 -j1 => tps = 11600.158593 $ pgbench -n -S -c64 -j2 => tps = 17947.100954 $ pgbench -n -S -c64 -j4 => tps = 26571.124001 $ pgbench -n -S -c64 -j8 => tps = 52725.470403 $ pgbench -n -S -c64 -j16 => tps = 38976.675319 $ pgbench -n -S -c64 -j32 => tps = 28998.499601 $ pgbench -n -S -c64 -j64 => tps = 26701.877815 Is it acceptable to use pthread in contrib module? If ok, I will add the patch to the next commitfest.
Getting session variables without touching postgresql.conf
This post has been updated with new code that uses temporary table – the code is at the end of post!
There was this question on Stack Overflow.
For future reference: guy asked how to do session variables – i.e. something he could define once in session, and later reuse in standard sql queries – without modifying postgresql.conf – so usage of custom_variable_classes is forbidden 🙂
While I don't actually see why somebody would want to avoid modifying its postgresql.conf (short of “it's shared hosting and I don't have superuser privileges"), I thought that it will be rather simple, and at the same time, rather interesting.
So, let's do it:
Continue reading Getting session variables without touching postgresql.conf
Waiting for 8.5 – hinting for number of distinct values
Robert Haas wrote, and Tom Lane committed (on 2nd of August) patch which adds interesting capability:
Log Message: ----------- Add ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT Robert Haas
Log message is rather terse, so what does it exactly do?
Continue reading Waiting for 8.5 – hinting for number of distinct values
Waiting for 8.5 – better messages for unique violation
On 1st of August Tom Lane committed patch from Itagaki Takahiro:
Improve unique-constraint-violation error messages to include the exact values being complained of. In passing, also remove the arbitrary length limitation in the similar error detail message for foreign key violations. Itagaki Takahiro
and later added an extension to it:
Department of second thoughts: let's show the exact key during unique index build failures, too. Refactor a bit more since that error message isn't spelled the same.
Continue reading Waiting for 8.5 – better messages for unique violation
Waiting for 8.5 – deferrable uniqueness
On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:
Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at the time of insertion. This works well as long as there aren't many conflicts, but won't scale to massive unique-key reassignments. Improving that case is a TODO item. Dean Rasheed
Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.
On 205h of July Andrew Dunstan committed patch by Andres Freund :
Log Message: ----------- DROP IF EXISTS for columns and constraints. Andres Freund.
Continue reading Waiting for 8.5 – pgsql: DROP IF EXISTS for columns and constraints.
Calculating median
Today, on irc (#postgresql on freenode.net) Dim mentioned about writing median calculation code.
It got me thinking, and consequently writing my version of median calculation code.
Getting list of unique elements
Every so often you need to get list of unique elements in some column. The standard way to do it is:
select distinct column from table;
or
select column from table group by column;
The only problem is that it's slow – as it has to seq scan whole table. Can it be done faster?