How to Allow For Max 4 Rows in Update (PostgreSQL)

Thu, Apr 1, 2010

A question found on the net:

how to restrict users to update only max 4 rows of a table in a transaction.

This looks like a solution to some artificial problem that doesn’t exist, but nevermind, any task should be solved.

PostgreSQL Strange Timings

Sun, Mar 21, 2010

A very simple query. PostgreSQL 8.4. I have no idea why the simple query is 7 times faster than the EXPLAIN ANALYZE version. Any ideas? Anyone?

SELECT COUNT(*), xtype 
FROM test 
GROUP BY xtype 
ORDER BY xtype;

      count  | xtype
    ---------+-------
      669000 | A
       84000 | B
       63000 | D
       15000 | E
      159000 | G
     7866000 | H
     1000000 | N
      144000 | NI
    (8 rows)

    Time: 3366,822 ms
    
explain analyze 
SELECT COUNT(*), xtype 
FROM test 
GROUP BY xtype 
ORDER BY xtype;
                                                               QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
     Sort  (cost=243136.22..243136.24 rows=8 width=2) (actual time=24544.883..24544.889 rows=8 loops=1)
       Sort Key: xtype
       Sort Method:  quicksort  Memory: 25kB
       ->  HashAggregate  (cost=243136.00..243136.10 rows=8 width=2) (actual time=24544.838..24544.848 rows=8 loops=1)
             ->  Seq Scan on test  (cost=0.00..193136.00 rows=10000000 width=2) (actual time=0.012..11501.738 rows=10000000 loops=1)
     Total runtime: 24544.980 ms
    (6 rows)

Why Use ORMs?

Thu, Feb 25, 2010

ORM aka Object Relational Mapping is quite a crappy way of converting a relational database model into an object model. Usually this means that there is a class for each database table.

Many people say that ORMs are the best human idea since the sliced bread. I mention some of their arguments below.

How to Load SQL File to Database

Sat, Feb 20, 2010 It would be nice to have the possibility of loading an sql file to database using a normal database api. OK, let’s define what the ‘normal api’ is. This should be a full featured api for connecting to database and performing queries, with many drivers available. My favorite ones are: JDBC and DBI (yes, the one from Perl). Unfortunately Python doesn’t have any. Python’s DBAPI 2.0 is a kind of joke, not API.

Are SQL Database Joins So Bad?

Thu, Nov 12, 2009

Last time on many forums there were endless discussions about database joins. All about how they are bad, very inefficient, and terrible and the best is to avoid them. Generally that’s not true and I’ll try to show this here (taking some small 10 million records tables as on one forum was suggested).