Thread: Nu-B\psql:Command Recall,Repeat?

Nu-B\psql:Command Recall,Repeat?

From
john-paul delaney
Date:
Hello List...

Excuse this *very* beginner to postgresql & sql.  The 'up-arrow' in psql doesn't
recall my previous typed commands (like in bash) - how do I turn on something
similar?

thanks
/j-p.



-----------------------
 JUSTATEST Art Online
  www.justatest.com




Re: Nu-B\psql:Command Recall,Repeat?

From
"Josh Berkus"
Date:
John-Paul

> Excuse this *very* beginner to postgresql & sql.

No excuse necessary!  The NOVICE list is for beginner questions.

>The 'up-arrow' in
>  psql doesn't
> recall my previous typed commands (like in bash) - how do I turn on
>  something
> similar?

Psql relies on the libreadline library  (from the GNU project) for this
 functionality.  Many Linux distributions, and Cygwin, do not include
 this package by default.  Unfortunately, enabling it requires:
1. Install libreadline
2. Re-compile and re-install the psql binary (or all of postgres).

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

Re: Nu-B\psql:Command Recall,Repeat?

From
john-paul delaney
Date:
Er..yuk.  Looks like I'll have to be careful with my input.  As I want to use this time to learn some postgresql (as
opposedto re-compiling), I'll wait until it's upgrade time again for the libreadline.  Thanks for the explanation. 

regards
/j-p.


On Fri, 22 Feb 2002, Josh Berkus wrote:

> John-Paul
>
> > Excuse this *very* beginner to postgresql & sql.
>
> No excuse necessary!  The NOVICE list is for beginner questions.
>
> >The 'up-arrow' in
> >  psql doesn't
> > recall my previous typed commands (like in bash) - how do I turn on
> >  something
> > similar?
>
> Psql relies on the libreadline library  (from the GNU project) for this
>  functionality.  Many Linux distributions, and Cygwin, do not include
>  this package by default.  Unfortunately, enabling it requires:
> 1. Install libreadline
> 2. Re-compile and re-install the psql binary (or all of postgres).
>
> -Josh Berkus
>
> ______AGLIO DATABASE SOLUTIONS___________________________
>                                        Josh Berkus
>   Complete information technology      josh@agliodbs.com
>    and data management solutions       (415) 565-7293
>   for law firms, small businesses        fax 621-2533
>     and non-profit organizations.      San Francisco
>


-----------------------
 JUSTATEST Art Online
  www.justatest.com




Re: Nu-B\psql:Command Recall,Repeat?

From
Oliver Elphick
Date:
On Fri, 2002-02-22 at 18:15, john-paul delaney wrote:
>
> Hello List...
>
> Excuse this *very* beginner to postgresql & sql.  The 'up-arrow' in psql doesn't
> recall my previous typed commands (like in bash) - how do I turn on something

When you build PostgreSQL, make sure the libreadline development files
are available.  If you look at config.cache, you will probably find that
config could not locate them.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "The LORD bless thee, and keep thee; The LORD make his
      face shine upon thee, and be gracious unto thee; The
      LORD lift up his countenance upon thee, and give thee
      peace."              Numbers 6:24-26


select IN problem

From
Doug Silver
Date:
I've read the IN chapter in Bruce M.'s Postgresql book, but I still can't
seem to get my select/IN to work.  I have two tables, transactions and
transactions_detail, with the transaction_id field as the reference in
the transactions_detail table.

# select transaction_id from transactions where enter_date> cast('2002-02-22' as date);
 transaction_id
----------------
           2043
           2044
           2045

purchases=# select transaction_id from transactions_detail where transaction_id>2042;
 transaction_id
----------------
           2043
           2044
           2045

purchases=# \d transactions_detail
            Table "transactions_detail"
   Attribute    |         Type          | Modifier
----------------+-----------------------+----------
 transaction_id | smallint              |
 products       | character varying(20) |
 quantities     | smallint              |

But the following query causes it to hang, after 10 seconds I finally stop
it.

purchases=# select transaction_id from transactions_detail where
purchases=# transaction_id IN (
purchases=# select transaction_id from transactions where enter_date> cast('2002-02-22' as date)
purchases=# );

Any suggestions?

Thanks!

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~




Re: select IN problem

From
Andrew McMillan
Date:
On Sat, 2002-02-23 at 10:36, Doug Silver wrote:
> I've read the IN chapter in Bruce M.'s Postgresql book, but I still can't
> seem to get my select/IN to work.  I have two tables, transactions and
> transactions_detail, with the transaction_id field as the reference in
> the transactions_detail table.
>
> # select transaction_id from transactions where enter_date> cast('2002-02-22' as date);
>  transaction_id
> ----------------
>            2043
>            2044
>            2045
>
> purchases=# select transaction_id from transactions_detail where transaction_id>2042;
>  transaction_id
> ----------------
>            2043
>            2044
>            2045
>
> purchases=# \d transactions_detail
>             Table "transactions_detail"
>    Attribute    |         Type          | Modifier
> ----------------+-----------------------+----------
>  transaction_id | smallint              |
>  products       | character varying(20) |
>  quantities     | smallint              |
>
> But the following query causes it to hang, after 10 seconds I finally stop
> it.
>
> purchases=# select transaction_id from transactions_detail where
> purchases=# transaction_id IN (
> purchases=# select transaction_id from transactions where enter_date> cast('2002-02-22' as date)
> purchases=# );
>
> Any suggestions?

SELECT td.transaction_id FROM transactions_detail td
WHERE EXISTS (SELECT transaction_id FROM transactions t
   WHERE t.transaction_id = td.transaction_id
     AND t.enter_date > CAST('2002-02-02' AS DATE );

Could well work better.  The problem you are likely to be encountering
is that IN (...) will not use an index.

To see the query plans generated by the different SQL, use 'EXPLAIN <sql
command>' - it is _well_ worth coming to grips with what EXPLAIN can
tell you.

You could also be better with a plan that did a simple JOIN and
DISTINCT:

SELECT DISTINCT td.transaction_id
FROM transactions_detail td, transactions t
WHERE t.enter_date > '2002-02-02'
  AND td.transaction_id = t.transaction_id;

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


Re: select IN problem

From
Doug Silver
Date:
On 23 Feb 2002, Andrew McMillan wrote:
>
> SELECT td.transaction_id FROM transactions_detail td
> WHERE EXISTS (SELECT transaction_id FROM transactions t
>    WHERE t.transaction_id = td.transaction_id
>      AND t.enter_date > CAST('2002-02-02' AS DATE );
>
> Could well work better.  The problem you are likely to be encountering
> is that IN (...) will not use an index.
>
> To see the query plans generated by the different SQL, use 'EXPLAIN <sql
> command>' - it is _well_ worth coming to grips with what EXPLAIN can
> tell you.
>
> You could also be better with a plan that did a simple JOIN and
> DISTINCT:
>
> SELECT DISTINCT td.transaction_id
> FROM transactions_detail td, transactions t
> WHERE t.enter_date > '2002-02-02'
>   AND td.transaction_id = t.transaction_id;
>
> Regards,
>                     Andrew.
>

Ok, the transactions table does have an index, so that must be the problem
there, but should it give an error or will it eventually return something?

Thanks, #2 worked as I should have tried something like that earlier.
Still a bit rusty with my sql queries ...

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


Re: select IN problem

From
Andrew McMillan
Date:
On Sat, 2002-02-23 at 11:15, Doug Silver wrote:
> On 23 Feb 2002, Andrew McMillan wrote:
> >
> > SELECT td.transaction_id FROM transactions_detail td
> > WHERE EXISTS (SELECT transaction_id FROM transactions t
> >    WHERE t.transaction_id = td.transaction_id
> >      AND t.enter_date > CAST('2002-02-02' AS DATE );
> >
> > Could well work better.  The problem you are likely to be encountering
> > is that IN (...) will not use an index.
> >
> > To see the query plans generated by the different SQL, use 'EXPLAIN <sql
> > command>' - it is _well_ worth coming to grips with what EXPLAIN can
> > tell you.
> >
> > You could also be better with a plan that did a simple JOIN and
> > DISTINCT:
> >
> > SELECT DISTINCT td.transaction_id
> > FROM transactions_detail td, transactions t
> > WHERE t.enter_date > '2002-02-02'
> >   AND td.transaction_id = t.transaction_id;
> >
> > Regards,
> >                     Andrew.
> >
>
> Ok, the transactions table does have an index, so that must be the problem
> there, but should it give an error or will it eventually return something?

It will eventually return something.  My guess is that you probably have
a lot of records in one or both tables.

Do a "VACUUM ANALYZE" and then take a look at the EXPLAIN ... output for
the three queries to get a real example of the differences in execution
plans.  Analysis for queries with sub-plans is more complex than the
normal case, however.

What your query would have been doing (I think) is running the subselect
for every row in the transactions_detail table.  That would probably be
about the worst possible case you can imagine, hence the bad query time.

Check the archives of -hackers to see more information about problems
with making IN (...) use an index.


> Thanks, #2 worked as I should have tried something like that earlier.
> Still a bit rusty with my sql queries ...

We've all been there... :-)

Did my first suggestion not work at all?  I think that (in this case) #2
is probably the most efficient, but it's worth understanding the EXISTS
syntax as you can generally turn an IN (...) into EXISTS (...) and
sometimes it _is_ the best way.

Cheers,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


Re: select IN problem

From
Doug Silver
Date:
On 23 Feb 2002, Andrew McMillan wrote:

> It will eventually return something.  My guess is that you probably have
> a lot of records in one or both tables.
>
> Do a "VACUUM ANALYZE" and then take a look at the EXPLAIN ... output for
> the three queries to get a real example of the differences in execution
> plans.  Analysis for queries with sub-plans is more complex than the
> normal case, however.
>
> What your query would have been doing (I think) is running the subselect
> for every row in the transactions_detail table.  That would probably be
> about the worst possible case you can imagine, hence the bad query time.
>
> Check the archives of -hackers to see more information about problems
> with making IN (...) use an index.
>
>
> > Thanks, #2 worked as I should have tried something like that earlier.
> > Still a bit rusty with my sql queries ...
>
> We've all been there... :-)
>
> Did my first suggestion not work at all?  I think that (in this case) #2
> is probably the most efficient, but it's worth understanding the EXISTS
> syntax as you can generally turn an IN (...) into EXISTS (...) and
> sometimes it _is_ the best way.
>
> Cheers,
>                     Andrew.
>

yes, the exists statement worked but much slower.  Both tables are quite
small (~2000 entries) so the delay was surprising.

I assume that's what this result corresponds to:
explain select transaction_id  from transactions_detail
where transaction_id IN
(select transaction_id from transactions where enter_date>cast('2002-02-20' as date));

NOTICE:  QUERY PLAN:

Seq Scan on transactions_detail  (cost=0.00..84701.18 rows=2062 width=2)
  SubPlan
      ->  Seq Scan on transactions  (cost=0.00..82.11 rows=625 width=4)

      EXPLAIN

So it's doing a sequential scan on the trans_detail against the result of the
SubPlan.  ouch.

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Doug Silver
Network Manager
Quantified Systems, Inc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~