Tag: postgresql

Waiting for 9.1 – triggers on views

On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:

Support triggers ON views.
 
This patch adds the SQL-standard concept OF an INSTEAD OF TRIGGER, which
IS fired instead OF performing a physical INSERT/UPDATE/DELETE.  The
TRIGGER FUNCTION IS passed the entire OLD AND/OR NEW ROWS OF the VIEW,
AND must figure OUT what TO do TO the underlying TABLES TO implement
the UPDATE.  So this feature can be used TO implement updatable views
USING TRIGGER programming STYLE rather than rule hacking.
 
IN passing, this patch corrects the names OF SOME COLUMNS IN the
information_schema.triggers VIEW.  It seems the SQL committee renamed
them somewhere BETWEEN SQL:99 AND SQL:2003.
 
Dean Rasheed, reviewed BY Bernd Helmle; SOME additional hacking BY me.

Continue reading Waiting for 9.1 – triggers on views

Performance gains from using foreign keys

Foreign keys are known for couple of things, but speeding up your system is not one of them. But sometimes, having them in place lets you make queries significantly faster.

How? Let me show you example I have seen lately (well, it's simplified example based on something much more convoluted, and definitely longer):

Continue reading Performance gains from using foreign keys

How to group messages into chats?

My jabber server had the feature, that it logs all messages that got sent through it.

This is pretty cool, and useful. And now, i got asked to use it to create list of conversations.

What exactly is this? Whenever I send (or receive) something there is record in database with information about which local user, communication type (send/recv), correspondent, when it happened, and what is the body of message.

And based on this, we want to list messages into chats. How?

Continue reading How to group messages into chats?

Waiting for 9.1 – concat, concat_ws, right, left, reverse

On 24th of August, Takahiro Itagaki committed patch:

Log Message:
-----------
Add string functions: concat(), concat_ws(), left(), right(), and reverse().
 
Pavel Stehule, reviewed by me.

Continue reading Waiting for 9.1 – concat, concat_ws, right, left, reverse

How to manage changes to your database?

Every now and then somebody asks how to make diff of database schemata.

Usual background is like: we have production database, and development database, and we want to see what is different on development to be able to change production in the same way.

Personally I think that such approach is inherently flawed. Why?

Continue reading How to manage changes to your database?

Waiting for 9.1 – Recognize functional dependency on primary keys.

Yesterday (August, 7th), Tom Lane committed:

Log Message:
-----------
Recognize functional dependency on primary keys.  This allows a table's
other columns to be referenced without listing them in GROUP BY, so long as
the primary key column(s) are listed in GROUP BY.
 
Eventually we should also allow functional dependency on a UNIQUE constraint
when the columns are marked NOT NULL, but that has to wait until NOT NULL
constraints are represented in pg_constraint, because we need to have
pg_constraint OIDs for all the conditions needed to ensure functional
dependency.
 
Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane

Continue reading Waiting for 9.1 – Recognize functional dependency on primary keys.

Waiting for 9.1 – Reduced lock levels for ALTER TABLE

On 28th of July, Simon Riggs committed patch which:

Log Message:
-----------
Reduce LOCK levels OF CREATE TRIGGER AND SOME ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used FOR many altering DDL commands, allowing easier
future changes OF LOCK levels. Implementation OF initial analysis ON DDL
sub-commands, so that many LOCK levels are now at ShareUpdateExclusiveLock OR
ShareRowExclusiveLock, allowing certain DDL NOT TO block reads/writes.
FIRST OF NUMBER OF planned changes IN this area; additional docs required
WHEN FULL project complete.

Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE