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

Continue reading Waiting for 8.5 – deferrable uniqueness

1000 posts

I just checked my WordPress dashboard, and it says:

  • 1,000 Posts
  • 1,847 Comments

First entry was posted on 13th of May 2006 – 1166 days ago.

During this time I had 2 breakdowns when I said that I close the blog, switched language, and moved from “everything that interests me" to “99% PostgreSQL only".

Blog helped me get new contacts, contracts and job.

So, thanks go to all of you who read and comment here – I can only try to keep you interested 🙂

In case you're interested – according to FeedBurner I have around 500 subscribers.

Slow vim startup

I had today a very weird situation.

From my laptop, I ssh to another system, and from there to yet another.

On this final system I noticed that vim starts relatively slow. i.e. this command:

time vim -u /dev/null -c “:q"

returned time in around 3.5 seconds, while on my laptop (which is much less powerful) it is:

=> time vim -u /dev/null -c ":q"
 
real    0m0.073s
user    0m0.056s
sys     0m0.016s

I tried to debug the situation, and it got weirder. If I did “su – another_user" (on the final system) – it became fast. What's more: if I did su – depesz (my account) back – vim was still fast!

After some debugging it occurred to me: I have automatic X11 forwarding turned on. I checked – and yes, in the shell that vim starts slowly, i had “DISPLAY" variable, set to localhost:10.0!

Quick unset DISPLAY, and suddenly vim starts 0.027s!

Lesson for future – do not use automatic X11 forwarding for long-distance ssh connections, or make sure you run vim with “-X" option.

Later I learned why it tries X11 connection – to get access to X copy/paste buffer (available as * register). Nice feature, but with quite problematic side effects.

bf hacking for “fun” and definitely no profit

#!/usr/bin/bf
+++++[>++[>>++++>+++++++>+++>+<<<<<-]>>+>+<<<<-]+++++[>+++++[>>>>>>++++>
++++>++++>++++<<<<<<<<<-]>>>>>>>+>++>+++<<<<<<<<<<-]>>>>-.>>>>>>++.--.+.
<<<<<++.>>---.>>.+.>.<<-.<++++.>>>--.<<<<<.>>---.>>>.<<<-.>+.>-.<<+++++.
>>>+++.<<<---.>++.<<<.>>>---.<--.++.>+++.<++.>>>---.<<<<<<<+.>>>.<<<<[-]

If you can read it, then something is definitely wrong with you ;-P