Re: [SQL] trying to repair a bad header block
On Wed, Oct 29, 2008 at 6:36 PM, Tom Lane [EMAIL PROTECTED] wrote: Scott Marlowe [EMAIL PROTECTED] writes: On Wed, Oct 29, 2008 at 4:23 PM, Tom Lane [EMAIL PROTECTED] wrote: If you can tolerate losing the data on that page, just zero out the entire 8K page. dd from /dev/zero is the usual tool. Would zero_damaged_pages work here? I know it's a shotgun to kill a flea, but it's also easier and safer for a lot of folks than dding a page in their table. It would work, but if you have any *other* damaged pages you might lose more than you were expecting ... Agreed. OTOH, on slip of the fingers for a newbie with dd and the whole table is gone. I guess it's always a trade off. Thanks Tom and Scott! I just use dd for simply creating big files (oh, and once to screw up a entire disk :) Im going to man it in order to zero out that page(s). Wish me lucks, dudes. Thanks! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] trying to repair a bad header block
On Wed, Oct 29, 2008 at 7:24 PM, Scott Marlowe [EMAIL PROTECTED] wrote: Oh, and to reply to myself and the original poster, you need to figure out what's causing the pages to get damaged. IT's usually bad hardware, then a buggy driver, then a buggy kernel / OS that can cause it. Run lots of tests. Oh yes, im facing hw problems. All im triyng to do now is 'rebuild' the header blocks in order to do some pg_dump (whichs is failing, off course), set a new machine and get the actual one to the pits :) Thanks again! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Subqueries
alias v not visible in sub-query? - Original Message - From: Pascal Tufenkji To: pgsql-sql@postgresql.org Sent: Thursday, October 30, 2008 12:17 PM Subject: [SQL] Subqueries Hello, I don't understand the following error. Can anyone help me plz Thx Pascal select * from sip_vacations_v v left join ( select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures from mat_grp_v mg inner join planification_v p on p.mat_grp_id = mg.id inner join planification_ens_v pe on pe.planification_id = p.id where mg.annee_univ = v.annee and mg.semestre = v.sem_civ group by pe.emp_id,mg.mat_id,mg.groupe ) p on p.emp_id = v.emp_id and p.mat_id = v.mat_id and p.groupe = v.groupe ERROR: invalid reference to FROM-clause entry for table v LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ ^ HINT: There is an entry for table v, but it cannot be referenced from this part of the query.
Re: [SQL] Subqueries
The v reference need to exist in the inner query. You can't use an outer query reference in the inner query. This happens because the inner query is executed before the outer query and the inner query doesn't even know about the outer query. May be this helps you get what you want. SELECT *, count(pl.id http://p.id/) * 1.5 AS nb_heures FROM sip_vacations_v v LEFT JOIN ( mat_grp_v mg INNER JOIN planification_v pl ON pl.mat_grp_id = mg.id INNER JOIN planification_ens_v pe ON pe.planification_id = pl.idhttp://p.id/ *WHERE** mg.annee_univ = v.annee and mg.semestre = v.sem_civ* GROUP BY pe.emp_id, mg.mat_id, mg.groupe ) p ON p.emp_id = v.emp_id AND p.mat_id = v.mat_id AND p.groupe = v.groupe; I not sure about the count(pl.id) * 1.5 is the same that you are looking for because it will depend of what you ate looking for. Regards On Thu, Oct 30, 2008 at 10:17 AM, Pascal Tufenkji [EMAIL PROTECTED]wrote: Hello, I don't understand the following error. Can anyone help me plz Thx Pascal select * from sip_vacations_v v left join ( select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures from mat_grp_v mg inner join planification_v p on p.mat_grp_id = mg.id inner join planification_ens_v pe on pe.planification_id = p.id *where mg.annee_univ = v.annee and mg.semestre = v.sem_civ* group by pe.emp_id,mg.mat_id,mg.groupe ) p on p.emp_id = v.emp_id and p.mat_id = v.mat_id and p.groupe = v.groupe *ERROR: invalid reference to FROM-clause entry for table v* *LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ* * ^* *HINT: There is an entry for table v, but it cannot be referenced from this part of the query.* -- Helio Campos Mello de Andrade
[SQL] Subqueries
Hello, I don't understand the following error. Can anyone help me plz Thx Pascal select * from sip_vacations_v v left join ( select pe.emp_id,mg.mat_id,mg.groupe,count(p.id) * 1.5 as nb_heures from mat_grp_v mg inner join planification_v p on p.mat_grp_id = mg.id inner join planification_ens_v pe on pe.planification_id = p.id where mg.annee_univ = v.annee and mg.semestre = v.sem_civ group by pe.emp_id,mg.mat_id,mg.groupe ) p on p.emp_id = v.emp_id and p.mat_id = v.mat_id and p.groupe = v.groupe ERROR: invalid reference to FROM-clause entry for table v LINE 9: where mg.annee_univ = v.annee and mg.semestre = v.sem_civ ^ HINT: There is an entry for table v, but it cannot be referenced from this part of the query.
[SQL] Date Index
Hey all, I'm apparently too lazy to figure this out on my own so maybe one of you can just make it easy on me. J I want to index a timestamp field but I only want the index to include the -mm-dd portion of the date, not the time. I figure this would be where the expression portion of the CREATE INDEX syntax would come in, but I'm not sure I understand what the syntax would be for this. Any suggestions? Thanks!
Re: [SQL] Date Index
On Thu, Oct 30, 2008 at 2:49 PM, Ryan Hansen [EMAIL PROTECTED] wrote: Hey all, I'm apparently too lazy to figure this out on my own so maybe one of you can just make it easy on me. J I want to index a timestamp field but I only want the index to include the -mm-dd portion of the date, not the time. I figure this would be where the expression portion of the CREATE INDEX syntax would come in, but I'm not sure I understand what the syntax would be for this. Really depends on what you want to do with it. Easiest way is to cast it: smarlowe=# create table dtest (id int, ts timestamp); CREATE TABLE smarlowe=# insert into dtest values (1,'2008-09-01 12:30:00'); INSERT 0 1 smarlowe=# insert into dtest values (1,'2008-09-02 10:30:00'); INSERT 0 1 create index dtest_tstodate on dtest ((ts::date)); CREATE INDEX set enable_seqscan=off; SET explain select * from dtest where ts::date='2009-09-02'; QUERY PLAN - Index Scan using dtest_tstodate on dtest (cost=0.00..8.27 rows=1 width=12) Index Cond: ((ts)::date = '2009-09-02'::date) (2 rows) Note that since the table is so small the db would have seq scanned it if I hadn't turned off seqscans to test. But since it used the index, that proves it's there and working. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
