[GENERAL] postgres 9.2.4 - ERROR: invalid input syntax for type numeric:
Hi The follow query below works in one database but not on another database. The databases are similar but independent of each other. From the previous post I found that: *This is fixed by casting the first argument to **text* *:,* but I can’t see which COALESCE to fix out the several COALESCE in the query. ERROR: invalid input syntax for type numeric: Query : SELECT (SELECT br_cde FROM br_prof) as Store, hp_acc.cus_acno as Account, hp_acc.hp_sub_acno as Sub_account, (select con_recover_sum from contract where con_id = hp_acc.con_id) as Contract_amount, (select con_goods from contract where con_id = hp_acc.con_id) as Stock_RSP, (select con_doc_disc from contract where con_id = hp_acc.con_id) as Discount, (hpacc_bdwo_amt - COALESCE(hpacc_bdwo_rec_amt,0) * -1) as Balance_Outstanding_write_off_a, hpacc_inv_fpp as Invoice_FPP, hp_acc.acc_cde as Account_type, ( select endp_addr_line1 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_1, ( select endp_addr_line2 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_2, ( select endp_addr_line3 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_3, ( select endp_addr_line4 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_4, ( select endp_addr_cde from end_point where endp_id = hp_acc.endp_id) as Delivery_address_cde, COALESCE(to_char(hpacc_deal_dte::date,'/mm/dd'),'/00/00') as Date_created, COALESCE(to_char(hpacc_auth_dte::date,'/mm/dd'),'/00/00') as Date_authorized, COALESCE(to_char(hpacc_inv_dte::date,'/mm/dd'),'/00/00') as Date_invoiced, COALESCE(to_char(hpacc_cancelled::date,'/mm/dd'),'/00/00') as Date_cancelled, COALESCE(to_char(hpacc_1_inst_dte::date,'/mm/dd'),'/00/00') as First_installment_date, (select con_instalment from contract where con_id = hp_acc.con_id) as Monthly_installment, (select con_fin_period from contract where con_id = hp_acc.con_id) as Finance_period, (select con_fin_rate from contract where con_id = hp_acc.con_id) as Interest_rate, (select con_cost from contract where con_id = hp_acc.con_id) as Stock_cost, (select COALESCE(con_handling,0) from contract where con_id = hp_acc.con_id) as Cartage, (select con_maint from contract where con_id = hp_acc.con_id) as Maintenance, (select con_club from contract where con_id = hp_acc.con_id) as Club_Fees, (select con_del from contract where con_id = hp_acc.con_id) as Delivery, (select (con_ins + con_rins) from contract where con_id = hp_acc.con_id) as Insurance, (select con_stamp_duty from contract where con_id = hp_acc.con_id) as Stamp_duty, (select COALESCE(con_cash_dep,0) from contract where con_id = hp_acc.con_id) as Cash_deposit, (select COALESCE(con_fin_chrg,0) from contract where con_id = hp_acc.con_id) as Finance_charges, (select COALESCE(con_goods_tax+con_del_tax+con_maint_tax+con_ins_tax+con_rins_tax+con_other_tax+con_club_tax+con_addon_tax+con_doc_disc_tax+con_ln_disc_tax+con_prm_disc_tax+con_admin_fee_tax+con_rainbow_loan_amt_tax+con_installation_fee_tax+con_1st_mth_cr_life_tax+con_handling_tax,0) from contract where con_id = hp_acc.con_id ) as Tax, COALESCE((select sum(TRUNC(CASE WHEN sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax !=0 THEN sku_line.skul_nett_rsp_tax/(sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax)*100 ELSE 0 END,2)) from sku_line join hpacc_sku using (skul_id) where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Tax_rate, ( CASE WHEN (select con_fin_period from contract where con_id = hp_acc.con_id) 0 THEN (select con_club/con_fin_period from contract where con_id = hp_acc.con_id) ELSE 0 END)::numeric(16,2) as Monthly_club_fee, COALESCE(hp_acc.hpacc_deal_no,'0') as Invoice_number, (select con_addon from contract where con_id = hp_acc.con_id) as Add_on_finance, COALESCE(deposit,0) as Agreed_deposit, (total_period_in_days-months_not_paid) as Live_periods, contract_month_left as Period_to_go, COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Arrears_Outstanding_write_off_a, amount_now_due as Now_due, COALESCE(to_char((select hpfin_lst_pay_dte::date from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),'/mm/dd'),'/00/00') as Last_paid_date, COALESCE((select hpfin_lst_pay_amt from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Last_paid_amount, COALESCE(theoretical_instalment,0) as Theoretical_installment, COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Actual_months_since_last_paid_i, COALESCE((select con_initiation_fee from contract where con_id = hp_acc.con_id),0) as Adn_Initiation_fee, COALESCE((select sum(aoc_value) from add_on_contract where addt_cde = '30' and hp_acc.con_id = con_id group by add_on_contract.con_id),0) as License_fee, COALESCE(( select con_installation_fee from contract
Re: [GENERAL] postgres 9.2.4 - ERROR: invalid input syntax for type numeric:
Sorry I found the problem, it’s data problem in another database, it’s trying to convert numeric null but of which it’s not a null data, it’s a blank data. *From:* Khangelani Gama [mailto:kg...@argility.com] *Sent:* Tuesday, May 20, 2014 12:24 PM *To:* pgsql-general@postgresql.org *Subject:* postgres 9.2.4 - ERROR: invalid input syntax for type numeric: Hi The follow query below works in one database but not on another database. The databases are similar but independent of each other. From the previous post I found that: *This is fixed by casting the first argument to **text* *:,* but I can’t see which COALESCE to fix out the several COALESCE in the query. ERROR: invalid input syntax for type numeric: Query : SELECT (SELECT br_cde FROM br_prof) as Store, hp_acc.cus_acno as Account, hp_acc.hp_sub_acno as Sub_account, (select con_recover_sum from contract where con_id = hp_acc.con_id) as Contract_amount, (select con_goods from contract where con_id = hp_acc.con_id) as Stock_RSP, (select con_doc_disc from contract where con_id = hp_acc.con_id) as Discount, (hpacc_bdwo_amt - COALESCE(hpacc_bdwo_rec_amt,0) * -1) as Balance_Outstanding_write_off_a, hpacc_inv_fpp as Invoice_FPP, hp_acc.acc_cde as Account_type, ( select endp_addr_line1 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_1, ( select endp_addr_line2 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_2, ( select endp_addr_line3 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_3, ( select endp_addr_line4 from end_point where endp_id = hp_acc.endp_id) as Delivery_address_4, ( select endp_addr_cde from end_point where endp_id = hp_acc.endp_id) as Delivery_address_cde, COALESCE(to_char(hpacc_deal_dte::date,'/mm/dd'),'/00/00') as Date_created, COALESCE(to_char(hpacc_auth_dte::date,'/mm/dd'),'/00/00') as Date_authorized, COALESCE(to_char(hpacc_inv_dte::date,'/mm/dd'),'/00/00') as Date_invoiced, COALESCE(to_char(hpacc_cancelled::date,'/mm/dd'),'/00/00') as Date_cancelled, COALESCE(to_char(hpacc_1_inst_dte::date,'/mm/dd'),'/00/00') as First_installment_date, (select con_instalment from contract where con_id = hp_acc.con_id) as Monthly_installment, (select con_fin_period from contract where con_id = hp_acc.con_id) as Finance_period, (select con_fin_rate from contract where con_id = hp_acc.con_id) as Interest_rate, (select con_cost from contract where con_id = hp_acc.con_id) as Stock_cost, (select COALESCE(con_handling,0) from contract where con_id = hp_acc.con_id) as Cartage, (select con_maint from contract where con_id = hp_acc.con_id) as Maintenance, (select con_club from contract where con_id = hp_acc.con_id) as Club_Fees, (select con_del from contract where con_id = hp_acc.con_id) as Delivery, (select (con_ins + con_rins) from contract where con_id = hp_acc.con_id) as Insurance, (select con_stamp_duty from contract where con_id = hp_acc.con_id) as Stamp_duty, (select COALESCE(con_cash_dep,0) from contract where con_id = hp_acc.con_id) as Cash_deposit, (select COALESCE(con_fin_chrg,0) from contract where con_id = hp_acc.con_id) as Finance_charges, (select COALESCE(con_goods_tax+con_del_tax+con_maint_tax+con_ins_tax+con_rins_tax+con_other_tax+con_club_tax+con_addon_tax+con_doc_disc_tax+con_ln_disc_tax+con_prm_disc_tax+con_admin_fee_tax+con_rainbow_loan_amt_tax+con_installation_fee_tax+con_1st_mth_cr_life_tax+con_handling_tax,0) from contract where con_id = hp_acc.con_id ) as Tax, COALESCE((select sum(TRUNC(CASE WHEN sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax !=0 THEN sku_line.skul_nett_rsp_tax/(sku_line.skul_nett_rsp-sku_line.skul_nett_rsp_tax)*100 ELSE 0 END,2)) from sku_line join hpacc_sku using (skul_id) where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Tax_rate, ( CASE WHEN (select con_fin_period from contract where con_id = hp_acc.con_id) 0 THEN (select con_club/con_fin_period from contract where con_id = hp_acc.con_id) ELSE 0 END)::numeric(16,2) as Monthly_club_fee, COALESCE(hp_acc.hpacc_deal_no,'0') as Invoice_number, (select con_addon from contract where con_id = hp_acc.con_id) as Add_on_finance, COALESCE(deposit,0) as Agreed_deposit, (total_period_in_days-months_not_paid) as Live_periods, contract_month_left as Period_to_go, COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Arrears_Outstanding_write_off_a, amount_now_due as Now_due, COALESCE(to_char((select hpfin_lst_pay_dte::date from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),'/mm/dd'),'/00/00') as Last_paid_date, COALESCE((select hpfin_lst_pay_amt from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno = hp_acc.hp_sub_acno),0) as Last_paid_amount, COALESCE(theoretical_instalment,0) as Theoretical_installment, COALESCE((select hpfin_me_mth_not_paid from hp_fin where cus_acno = hp_acc.cus_acno AND hp_sub_acno =
[GENERAL] Constraint exclusion for timestamp expression
Greetings.
I have a partitioned table, that can be reproduced the following way:
CREATE TABLE ara (
ara_id int4,
namevarchar(11),
run_id int4,
set_id int4,
created_at timestamp,
CONSTRAINT p_ara PRIMARY KEY (ara_id)
);
DO $partition$
DECLARE
_tabtext;
_montimestamp;
BEGIN
FOR _mon IN
SELECT * FROM generate_series('2013-10-01'::timestamp,
date_trunc('mon',now()+interval'2mon'),interval'1mon') s(dt)
LOOP
_tab:='ara_'||to_char(_mon, 'MM');
RAISE NOTICE '..oO( Creating % )', _tab;
EXECUTE format($$CREATE TABLE %I (CONSTRAINT c_ara_partition CHECK
(created_at = %L AND created_at %L)) INHERITS (ara)$$,
_tab, _mon, (_mon+interval'1mon'));
EXECUTE format($$CREATE UNIQUE INDEX p_%s ON %I(ara_id)$$, _tab,
_tab);
EXECUTE format($$CREATE INDEX i_%s_name ON %I(name)$$, _tab, _tab);
EXECUTE format($$CREATE INDEX i_%s_run_and_name ON
%I(run_id,name)$$, _tab, _tab);
END LOOP;
END;
$partition$;
Now, if I check plan for this query:
EXPLAIN SELECT * FROM ara
WHERE ara.created_at
BETWEEN (current_timestamp-interval'90 days')::timestamp
AND (current_timestamp)::timestamp;
I can see that all partitions are considered by the planner.
If I replace the interval expression with constant, like this:
EXPLAIN SELECT * FROM ara
WHERE ara.created_at
BETWEEN '2014-02-20'::timestamp
AND (current_timestamp)::timestamp;
then partition pruning kicks in and skips outdated partitions. The same
happens for the future-dated partitions if I use a constant timestamp for
the upper limit.
`constraint_exclusion` is default: partition
This happens on 9.1.13, but I get the same plans also on 9.3.4.
How can I enforce pruning to kick in for the initial expressions?
--
Victor Y. Yegorov
Re: [GENERAL] Constraint exclusion for timestamp expression
Victor Yegorov vyego...@gmail.com writes: How can I enforce pruning to kick in for the initial expressions? You already found out: use constants. The planner can't remove partitions on the basis of clauses involving volatile, or even stable, functions, because their results might be different at runtime. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Constraint exclusion for timestamp expression
2014-05-20 14:26 GMT+03:00 Tom Lane t...@sss.pgh.pa.us: Victor Yegorov vyego...@gmail.com writes: How can I enforce pruning to kick in for the initial expressions? You already found out: use constants. The planner can't remove partitions on the basis of clauses involving volatile, or even stable, functions, because their results might be different at runtime. Do you mean constants are the only way here? I.e. there is absolutely no way to use any of the current date / timestamp functions available? I've been trying to wrap current expressions into immutable functions, like `date_trunc`, without any luck. -- Victor Y. Yegorov
[GENERAL] Revoking permission on view pg_prepared_xacts
Hi, I would like know whether there is some way how to revoke permission for doing recovery (showing prepared transactions and working with them) for a user? I've tried someting like revoke all on pg_catalog.pg_prepared_xacts from public; and revoke all on function pg_prepared_xacts from public; but this does not work (at least for me). My intention is to have user which could work with xa - doing commit as being part of a global transaction but which can't do recovery stuff? Is there some way how to achieve that? Thank you for any idea Ondrej -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Force specific index disuse
Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On 5/20/14, 1:38 PM, Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? hi Steve, What is the query? Or at least a sanitized but complete version? Thanks, Seamus PS. I've had luck hinting with OFFSET 0 but it might not help in your use case. http://seamusabshere.github.io/2013/03/29/hinting-postgres-and-mysql-with-offset-and-limit/ Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. -- Seamus Abshere, SCEA +598 99 54 99 54 https://github.com/seamusabshere -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On 05/20/2014 09:44 AM, Seamus Abshere wrote: On 5/20/14, 1:38 PM, Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? hi Steve, What is the query? Or at least a sanitized but complete version? I've now resolved the issue with the one update query I was investigating this morning. But this involved building a test where I removed the index then ran explain and timed the query. The question is actually general as I anticipate reviewing the benefit of dropping more indexes and it would be much more quick and convenient to do something akin to: begin; disable index foodex; explain update bar set baz ; commit; I'm not sure what would be involved in adding this. It seems that simply hiding an index from the planner would be all that is necessary but I'm sure there are, as always, subtleties. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On 05/20/2014 10:44 AM, Alvaro Herrera wrote: Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On 05/20/2014 11:48 AM, Steve Crawford wrote: ... What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; Never mind. Thought it through. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
Steve Crawford scrawf...@pinpointresearch.com writes: On 05/20/2014 10:44 AM, Alvaro Herrera wrote: If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? The index doesn't need to be rebuilt; the transaction need take only as long as your EXPLAIN does. What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; The INSERT would insert a tuple lacking any entry in bothersome_idx, but it doesn't matter since it'll get rolled back. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
Steve Crawford wrote On 05/20/2014 10:44 AM, Alvaro Herrera wrote: Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? What would happen if you did: BEGIN; DROP INDEX bothersome_idx; INSERT INTO indexed_table...; ROLLBACK; DROP INDEX would take a lock, the insert would happen without updating bothersome_idx, then the rollback would revert indexed_table back to the way it was before the DROP INDEX was issued - both data and active indexes. Since the table contents didn't change there is no need to rebuild any associated indexes. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804591.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
Steve Crawford wrote: On 05/20/2014 10:44 AM, Alvaro Herrera wrote: If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? Dropped relations are not deleted from disk until transaction commit, so the original index is kept intact, and when ROLLBACK is executed only catalog state changed by the DROP INDEX is reverted, but it's an instant operation. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Force specific index disuse
On Tue, May 20, 2014 at 11:48 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: On 05/20/2014 10:44 AM, Alvaro Herrera wrote: Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;' worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue. worser case, you forget to enter 'rollback' at all and accidentally commit the index drop. I use the begin...drop...rollback on test servers a lot, but rarely on productions servers. If I don't want to lock for that long, you can often alter the query to make the index useless, for example: explain select * from pgbench_accounts where aid=87; Becomes: explain select * from pgbench_accounts where aid+0=87; Although for complex queries it can be hard to find the antimagic bullet. Cheers, Jeff
Re: [GENERAL] Force specific index disuse
On Tue, May 20, 2014 at 3:20 PM, Jeff Janes [via PostgreSQL] ml-node+s1045698n580459...@n5.nabble.com wrote: On Tue, May 20, 2014 at 11:48 AM, Steve Crawford [hidden email]http://user/SendEmail.jtp?type=nodenode=5804596i=0 wrote: On 05/20/2014 10:44 AM, Alvaro Herrera wrote: Steve Crawford wrote: Is there a way to force a specific index to be removed from consideration in planning a single query? Specifically, on a 60-million-row table I have an index that is a candidate for removal. I have identified the sets of nightly queries that use the index but before dropping it I would like to run EXPLAIN and do timing tests on the queries to see the impact of not having that index available and rewrite the query to efficiently use other indexes if necessary. If you can afford to lock the table for a while, the easiest is BEGIN; DROP INDEX bothersome_idx; EXPLAIN your_query; ROLLBACK; Interesting. But what do you mean by a while? Does the above keep the index intact (brief lock) or does it have to rebuild it on rollback? Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;' worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue. worser case, you forget to enter 'rollback' at all and accidentally commit the index drop. I use the begin...drop...rollback on test servers a lot, but rarely on productions servers. If I don't want to lock for that long, you can often alter the query to make the index useless, for example: explain select * from pgbench_accounts where aid=87; Becomes: explain select * from pgbench_accounts where aid+0=87; Although for complex queries it can be hard to find the antimagic bullet. Cheers, Jeff This whole line of thought is a use-case for Jaime Casanova's recent proposal: http://postgresql.1045698.n5.nabble.com/WIP-showing-index-maintenance-on-EXPLAIN-td5803106.html Note that disabling the index as shown, via clobbering a WHERE clause, doesn't help when the concern is how much time is being spent updating indexes during INSERT/UPDATE. The risk of forgetting to issue the BEGIN; (not so much ROLLBACK given typical default behavior if BEGIN is indeed issued) is probably the one that would concern me the most if working on a production server. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Force-specific-index-disuse-tp5804564p5804601.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Re: [GENERAL] Force specific index disuse
Jeff Janes wrote: Best case, 'A while' means however long it takes the explain (possibly analyze) to run, and for you to then type 'rollback;' worse case, someone else is already holding an incompatible lock (i.e. any lock) on the table, and is going to hang on to it for a long while, so your drop index hangs forever waiting to acquire the lock and in the process brings all other desired activity (except the one already holding the lock) to a screeching halt because they are not allowed to jump the lock queue. worser case, you forget to enter 'rollback' at all and accidentally commit the index drop. I guess you could write a program to do this for you instead of doing it interactively. That way, 1. you never forget BEGIN 2. you never mistake ROLLBACK and type COMMIT instead (oops). 3. you can LOCK TABLE before the DROP, with NOWAIT, and if it fails, just retry later; or you can specify a statement_timeout so that an upper limit to impact on other queries is. (Reset statement_timeout after LOCK TABLE is successful, so that the EXPLAIN can take longer if necessary). I guess you should use a test server, of course, and that would mostly free you from concern (3) anyway. Also: there is, or used to be, a concept of hypothetical indexes in the planner which could be useful to tools attaching to some hook(s) already in core. EDB had an index advisor tool way back when; I don't know if it's still alive. I have never tried any of this. I probably wouldn't run it on a production server anyway ... -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Convert an XML database
I want to set up a new PostgreSQL database from an XML database file. It seems like there should be an obvious way to do this but I can't seem to find any directions anywhere. How is this sort of thing normally done? The database is available to the public here: http://www.drugbank.ca/downloads#full -- Aram -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert an XML database
On 5/20/2014 9:16 PM, Aram Fingal wrote: I want to set up a new PostgreSQL database from an XML database file. It seems like there should be an obvious way to do this but I can't seem to find any directions anywhere. How is this sort of thing normally done? you'd need some sort of tool that could parse the XSD schema definition and generate SQL create table commands, then read the XML data and insert it into the database. a generic ETL (extract, transform, load) tool that supports XML and postgresql should be able to do it, but its not something built into postgresql as-is.I can't recommend any specific ETL tool to do this as I've never needed to do this exactly. there's some ETL tools listed here, http://en.wikipedia.org/wiki/Extract,_transform,_load -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
