Thread: Nu-B\psql:Command Recall,Repeat?
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
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
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
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
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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?
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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?
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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~