PostgreSQL quals in rewritten query plans

I've just examined PostgreSQL query debug output from the next query :

SELECT * from low_paid_emps_r;

where low_paid_emps_r is defined as

# SELECT definition from pg_views  where viewname like '%emps%';
                           definition     
-----------------------------------------------------
SELECT employees.employee_id, employees.first_name, employees.last_name, employees.email, employees.phone_number, employees.hire_date, employees.job_id, employees.salary, employees.commission_pct, employees.manager_id, employees.department_id FROM employees WHERE (employees.salary < (5000)::numeric);

In particular, I wished to find where is my where expression. It looks like the following.

   :quals 
               {OPEXPR 
               :opno 1754 
               :opfuncid 0 
               :opresulttype 16 
               :opretset false 
               :opcollid 0 
               :inputcollid 0 
               :args (
                  {VAR 
                  :varno 3 
                  :varattno 8 
                  :vartype 1700 
                  :vartypmod 524294 
                  :varcollid 0 
                  :varlevelsup 0 
                  :varnoold 3 
                  :varoattno 8 
                  :location -1
                  }
                  {FUNCEXPR 
                  :funcid 1740 
                  :funcresulttype 1700 
                  :funcretset false 
                  :funcformat 2 
                  :funccollid 0 
                  :inputcollid 0 
                  :args (
                     {CONST 
                     :consttype 23 
                     :consttypmod -1 
                     :constcollid 0 
                     :constlen 4 
                     :constbyval true 
                     :constisnull false 
                     :location -1 
                     :constvalue 4 [ -120 19 0 0 ]
                     }
                  )
                  :location -1
                  }
               )
               :location -1
               }

So, we have here <:

# SELECT oprname from pg_operator where oid=1754;
 oprname 
---------
<
(1 row)

The first argument is 8th attribute of our relation (salary). The other piece is ::numeric conversion:

# select proname from pg_proc where oid=1740;
 proname 
---------
 numeric
(1 row)

Ok, we reconstructed salary < numeric (Const) part. Let's find our constant.

# select typname from pg_type where oid=23;
 typname 
---------
 int4
(1 row)

It's int4. And constvalue as we can see from source code is a Datum. It seems form postgres.h that int4 is int32 and represented as:

#define DatumGetInt32(X) ((int32) GET_4_BYTES(X))

Now we can reconstruct out integer from ":constvalue 4 [ -120 19 0 0 ]" as 19*256+(256-120)=5000... Not evident; I possibly couldn't do this analysis not knowing original statement...

Extracting domain name from uri with SQL

I don't know if it is effective, but this ugly SQL (in PostgreSQL dialect) extracts domain name part from uri (i.e. translates protocol://something/page into "something"):

SELECT uri, substring (uri from position('//' in uri)+2 for ( char_length(uri) - char_length(substring (uri from position ('/' in substring ( uri from position('//' in uri)+2 )) ) )))  from proxy_data

It seems, it would look better with regexp:

 
select uri, substring ( substring (uri from '//[[:alnum:]\._\-]+') from  3) from proxy_data

However, in later case we should list all possible symbols in domain name in our regexp.

One interesting post about ORM

I've just found one interesting post about ORM - you may be interested in it. This post is rather old, but it is quite adequate even now. The link is here . Russian translation may be found at citforum.

Why do I dislike ORM?

"Any problem in computer science can be solved with another level of indirection"... Sometimes we receive too much indirection levels.
Let's try. First time I use psql:

> \d employees 
                                  Table "public.employees"
  Column  |          Type          |                       Modifiers                        
----------+------------------------+--------------------------------------------------------
 id       | integer                | not null default nextval('employees_id_seq'::regclass)
 name     | character varying(255) | 
 surname  | character varying(255) | 
 dept     | character(3)           | 
 phone    | character varying(15)  | 
 hiredate | date                   | 
 job      | character varying(255) | 
 salary   | numeric(9,0)           | 
Indexes:
    "employees_pkey" PRIMARY KEY, btree (id)

Start with query:

UPDATE employees set salary=salary*(1+2/100) WHERE salary<=5000

Will it work? Of course, no. The reason is that we have implicit conversion to integer.
Let's try once more:

UPDATE employees set salary=salary*(1.0+2.0/100) WHERE salary<=5000

Now it worked. Ok.
Let's try to do something similar in Hibernate:

        Query q;
        int updated;       
        q=em.createQuery("update Employees set salary=salary+:par*salary");
        q.setParameter("par", new Float(1.1));
        updated=q.executeUpdate();

And...

INFO: could not bind value '1.1' to parameter: 1; java.lang.Float cannot be cast to java.lang.Integer
Exception in thread "main" java.lang.ClassCastException: java.lang.Float cannot be cast to java.lang.Integer
        at org.hibernate.type.IntegerType.set(IntegerType.java:41)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
        at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:116)
        at org.hibernate.param.NamedParameterSpecification.bind(NamedParameterSpecification.java:38)
        at org.hibernate.hql.ast.exec.BasicExecutor.execute(BasicExecutor.java:67)
        at org.hibernate.hql.ast.QueryTranslatorImpl.executeUpdate(QueryTranslatorImpl.java:396)
        at org.hibernate.engine.query.HQLQueryPlan.performExecuteUpdate(HQLQueryPlan.java:259)
        at org.hibernate.impl.SessionImpl.executeUpdate(SessionImpl.java:1141)
        at org.hibernate.impl.QueryImpl.executeUpdate(QueryImpl.java:94)
        at org.hibernate.ejb.QueryImpl.executeUpdate(QueryImpl.java:49)
        at testhybernate.Main.updateData(Main.java:57)
        at testhybernate.Main.main(Main.java:33)

Ok. Let's try select * from employees; update it and then select * again;

...
em=emf.createEntityManager();
showData(em);
updateData(em);
showData(em);
...
protected static void showData(EntityManager em) {
        Iterator   it;
        Query q;
        List  result;
        em.flush();
        q=em.createQuery("from Employees");
        result=q.getResultList();
        it=result.iterator();
        while(it.hasNext()){
            Employees emp;
            emp=(Employees)it.next();
            System.out.println(emp);
        }        
    }
    private static void updateData(EntityManager em) {
        Query q;
        int updated;       
        q=em.createQuery("update Employees set salary=salary+1.1*salary");
        updated=q.executeUpdate();
        System.out.println("updated "+updated+ " entities");       
    }

And results are:

3 Steven King DIR 1986-01-01 Director 659
5 John Doe BAS 1985-07-10 Engineer 659
1 Elizabeth Bates SAL 2000-12-05 Manager 659
4 John Doe MNT 1989-06-01 Cleaner 659
6 Ken Green BAS 1994-07-02 Programmer 659
7 Sergey Spiridonov MNT 2002-07-02 System Administrator 659
updated 6 entities
3 Steven King DIR 1986-01-01 Director 659
5 John Doe BAS 1985-07-10 Engineer 659
1 Elizabeth Bates SAL 2000-12-05 Manager 659
4 John Doe MNT 1989-06-01 Cleaner 659
6 Ken Green BAS 1994-07-02 Programmer 659
7 Sergey Spiridonov MNT 2002-07-02 System Administrator 659

Oops... Select didn't notice update... O, shit!!!
I had some more experiments: we had forgotten that criteriaQueries exists (they are not implemented in Hibernate JPA, may be they are included in some GlassFish jar, but I'm tired).

It's really easier to create DAO with JDBC. And embedded SQL in C is much easier to understand and debug then all this ORM things... I like to work with database. I understand how it works.
Don't do my life easier. I can create effective SQL myself! I spend more time learning different crutches!!!

SQL is Turing complete

I've found one interesting thing: with recursive CTE and windowing functions SQL is a Turing complete language. The proof maybe found here.