You might be familiar with hstore datatype in PostgreSQL – if you're not – check it out, as it is really cool.
Basically it's hash in database, which you can search in.
Continue reading Deserialization of hstore data structure in Perl
Tag: postgresql
Waiting for 8.4 – new FSM (Free Space Map)
On 30th of September, Heikki Linnakangas committed his patch that changes FSM:
Rewrite the FSM. Instead of relying on a fixed-size shared memory segment, the free space information is stored in a dedicated FSM relation fork, with each relation (except for hash indexes; they don't use FSM). This eliminates the max_fsm_relations and max_fsm_pages GUC options; remove any trace of them from the backend, initdb, and documentation. Rewrite contrib/pg_freespacemap to match the new FSM implementation. Also introduce a new variant of the get_raw_page(regclass, int4, int4) function in contrib/pageinspect that let's you to return pages from any relation fork, and a new fsm_page_contents() function to inspect the new FSM pages.
Waiting for 8.4 – database-level lc_collation and lc_ctype
On 23rd of September, Heikki Linnakangas committed patch that was written by Radek Strnad (actually committed patch is a stripped-down version of original).
What it does? It adds ability to have (finally!) different collation order and character categories for different databases.
Continue reading Waiting for 8.4 – database-level lc_collation and lc_ctype
Getting record by 2 criteria, ordered by third – how to do it quickly?
Let's assume we have a simple table:
CREATE TABLE objects ( id serial primary key, category INT4 NOT NULL DEFAULT 0, object_type INT4 NOT NULL DEFAULT 0, entered_on TIMESTAMPTZ NOT NULL DEFAULT now() );
(This is simplification, but it contains all necessary columns).
What should I do to be able to quickly get 50 newest objects in given category/object_type (or in many categories/many object_types, or in all categories/object_types), optionally with limiting entered_on “older than …".
Continue reading Getting record by 2 criteria, ordered by third – how to do it quickly?
Waiting for 8.4 – pgbench with timed execution
Takahiro Itagaki wrote, and Tom Lane committed nice patch, which I personally find really helpful:
Add a duration option to pgbench, so that test length can be specified in seconds instead of by number of transactions to run. Takahiro Itagaki
Continue reading Waiting for 8.4 – pgbench with timed execution
Waiting for 8.4 – ordered data loading in pg_dump
Great (and admittedly long overdue) patch by Tom Lane:
Make pg_dump --data-only try to order the table dumps so that foreign keys' referenced tables are dumped before the referencing tables. This avoids failures when the data is loaded with the FK constraints already active. If no such ordering is possible because of circular or self-referential constraints, print a NOTICE to warn the user about it.
What it exactly means?
Continue reading Waiting for 8.4 – ordered data loading in pg_dump
Waiting for 8.4 – \ef in psql
Today, Tom Lane committed patch, written by Abhijit Menon-Sen, which adds interesting feature to psql. Namely – it simplifies changing definition of functions.
Hunting “idle in transactions”
If you ever encountered “idle in transaction" connections, you most likely hate them. I know, I personally hate them. They interfere with most of “cool toys" like replication, vacuum, DDL queries.
So, when I saw them on a database I was looking on, I decided to act.
Easier to say, difficult to do. How to fix the problem?
NULLs vs. NOT IN()
Friend of mine found something that he thought looked like a bug in Pg.
Very simple query:
select * from table where id not in (select field from other_table)
was not returning any rows, despite the fact that there definitely are some “ids" that are not in other_table.field. Why is that?
Waiting for 8.4 – case insensitive text ( citext )
On 29th of July, Tom Lane committed patch written by David E. Wheeler, which added new contrib module: citext.
Continue reading Waiting for 8.4 – case insensitive text ( citext )