среда, 21 ноября 2012 г.

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...

понедельник, 12 ноября 2012 г.

We've made this!

Hurray! We've catched up with China! Finally we have our own Great Russian Firewall!!!