Previously I tested performance of pl/PgSQL coded foreign keys to partitioned table.
Now, let's see if I can make creation of them a bit easier.
Tag: plpgsql
Foreign Key to partitioned table – part 2
Previously I wrote about how to create foreign key pointing to partitioned table.
Final solution in there required four separate functions and four triggers for each key between two tables.
Let's see how fast it is, and if it's possible to make it simpler.
Foreign Key to partitioned table
One of the long standing limitations of partitions is that you can't have foreign keys pointing to them.
Let's see if I can make it possible to have some kind of constraint that would do the same thing as fkey.
Variables in SQL, what, how, when?
One of the questions that gets asked quite a lot, is: how can you use variables in your queries? Other databases have them, does PostgreSQL?
Actually the answer is “no". But, it is actually very easy to work around, and what's more important – quite often, what you need are not variables in queries, but rather variables in client. What does it mean? Let's see.
Waiting for 9.2 – NULLS from pg_*_size() functions
On 19t of January, Heikki Linnakangas committed patch:
Make pg_relation_size() and friends return NULL if the object doesn't exist. That avoids errors when the functions are used in queries like "SELECT pg_relation_size(oid) FROM pg_class", and a table is dropped concurrently. Phil Sorber
Continue reading Waiting for 9.2 – NULLS from pg_*_size() functions
Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
On 18th of July, Tom Lane committed patch:
Add GET STACKED DIAGNOSTICS plpgsql command to retrieve exception info. This is more SQL-spec-compliant, more easily extensible, and better performing than the old method of inventing special variables. Pavel Stehule, reviewed by Shigeru Hanada and David Wheeler
Continue reading Waiting for 9.2 – Stacked Diagnostics in PL/pgSQL
Waiting for 9.1 – FOREACH IN ARRAY
On 16th of February, Tom Lane committed patch:
Add FOREACH IN ARRAY looping to plpgsql. (I'm not entirely sure that we've finished bikeshedding the syntax details, but the functionality seems OK.) Pavel Stehule, reviewed by Stephen Frost and Tom Lane
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?
Test driven development for PostgreSQL
I have a mixed love/hate relationship with tests.
I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.
But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.
I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.
For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.
Stupid tricks – hiding value of column in select *
One of the most common questions is “how do I get select * from table, but without one of the column".
Short answer is of course – name your columns, instead of using *. Or use a view.
But I decided to take a look at the problem.
Continue reading Stupid tricks – hiding value of column in select *