Thread

1. PQfinish(const PGconn *conn) question

   Boulat Khakimov <boulat@inet-interactif.com> — 2001-03-08T22:11:32Z

   Hi,
   
   I'm wondering how safe it is to pass an uninitialized conn to
   PQfinish....
   
   I have set a SIGALRM handler that terminates my daemon and closes
   connection
   to Postgres if time-out happens. However, Im setting the alarm few times
   before the connection to Postgres was established... which means that 
   conn pointer in not initialized yet and it will be passed to PQfinish in
   my
   SIGALRM handler. Im not sure how Postgres will handle that.... so far
   that
   doesnt seem to be causing any errors but Im not sure what's actually
   happening
   behind the scenes...
   
   Any input would be appreciated.
   
   
   
   Regards,
   Boulat Khakimov
   
   -- 
   Nothing Like the Sun
   
   

2. Query speed anomalies

   Camm Maguire <camm@enhanced.com> — 2001-03-08T23:01:00Z

   Greetings!  Why is the first query below so much longer than the
   second?  The only difference is the replacement of dates.asof =
   r1.asof with dates.asof = 'constant', even though
   r1.asof='same-constant' is a condition earlier in the where clause.  
   
   Here are the details:
   
   rdb1=# explain select r1.asof,r1.id,r1.price,r2.price,r1.dividend,r2.split,(r2.price*(case when r2.split isnull then 1.0 else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret from rprices r1,rprices r2,dates where r1.asof = '20010201' and r2.asof = dates.nasof and dates.asof = r1.asof and r1.id = r2.id and r1.price != 0.0 order by r1.id;
   NOTICE:  QUERY PLAN:
   
   Sort  (cost=29853.31..29853.31 rows=11 width=56)
     ->  Nested Loop  (cost=36.15..29853.12 rows=11 width=56)
           ->  Hash Join  (cost=36.15..1163.55 rows=260 width=28)
                 ->  Index Scan using rprices_asof_key on rprices r1  (cost=0.00..1100.76 rows=298 width=20)
                 ->  Hash  (cost=18.78..18.78 rows=1178 width=8)
                       ->  Seq Scan on dates  (cost=0.00..18.78 rows=1178 width=8)
           ->  Index Scan using rprices_id_key on rprices r2  (cost=0.00..109.76 rows=29 width=28)
   
   EXPLAIN
   rdb1=# explain select r1.asof,r1.id,r1.price,r2.price,r1.dividend,r2.split,(r2.price*(case when r2.split isnull then 1.0 else r2.split end)-r1.price+(case when r1.dividend isnull then 0.0 else r1.dividend end))/r1.price as ret,r2.mret from rprices r1,rprices r2,dates where r1.asof = '20010201' and r2.asof = dates.nasof and dates.asof = '20010201' and r1.id = r2.id and r1.price != 0.0 order by r1.id;
   NOTICE:  QUERY PLAN:
   
   Sort  (cost=2235.40..2235.40 rows=13 width=52)
     ->  Hash Join  (cost=1101.51..2235.17 rows=13 width=52)
           ->  Nested Loop  (cost=0.00..1105.76 rows=605 width=32)
                 ->  Index Scan using dates_pkey on dates  (cost=0.00..2.01 rows=1 width=4)
                 ->  Index Scan using rprices_asof_key on rprices r2  (cost=0.00..1100.02 rows=298 width=28)
           ->  Hash  (cost=1100.76..1100.76 rows=298 width=20)
                 ->  Index Scan using rprices_asof_key on rprices r1  (cost=0.00..1100.76 rows=298 width=20)
   
   EXPLAIN
   rdb1=# \d rprices
           Table "rprices"
    Attribute |  Type   | Modifier 
   -----------+---------+----------
    asof      | date    | not null
    id        | integer | not null
    rinfo     | integer | not null
    price     | float8  | not null
    ret       | float8  | not null
    dividend  | float4  | 
    split     | float4  | 
    volume    | integer | 
    idcsplit  | float8  | 
    idcdiv    | float8  | 
    mret      | float8  | 
   Indices: rprices_asof_key,
            rprices_id_key
   
   rdb1=# \d rprices_asof_key
   Index "rprices_asof_key"
    Attribute |  Type   
   -----------+---------
    asof      | date
    id        | integer
   unique btree
   
   rdb1=# \d rprices_id_key
   Index "rprices_id_key"
    Attribute |  Type   
   -----------+---------
    id        | integer
   btree
   
   rdb1=# \d dates
           Table "dates"
    Attribute | Type | Modifier 
   -----------+------+----------
    asof      | date | not null
    nasof     | date | 
    pasof     | date | 
   Index: dates_pkey
   
   rdb1=# \d dates_pkey
   Index "dates_pkey"
    Attribute | Type 
   -----------+------
    asof      | date
   unique btree (primary key)
   
   
   
   Thanks!
   -- 
   Camm Maguire			     			camm@enhanced.com
   ==========================================================================
   "The earth is but one country, and mankind its citizens."  --  Baha'u'llah