[GENERAL] FATAL: bogus freespace amount
Hi, We are facing the following problem during the PostgreSQL 8.2.4 startup in a Windows XP/SP2 box: 2008-03-31 13:35:12 1: LOG: database system was interrupted at 2008-03-31 09:45:44 2008-03-31 13:35:12 2: LOG: checkpoint record is at 0/7588FFD8 2008-03-31 13:35:12 3: LOG: redo record is at 0/7588FFD8; undo record is at 0/0; shutdown TRUE 2008-03-31 13:35:12 4: LOG: next transaction ID: 0/100487; next OID: 909454 2008-03-31 13:35:12 5: LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-03-31 13:35:12 6: LOG: database system was not properly shut down; automatic recovery in progress 2008-03-31 13:35:12 7: LOG: record with zero length at 0/75890038 2008-03-31 13:35:12 8: LOG: redo is not required 2008-03-31 13:35:12 9: LOG: database system is ready 2008-03-31 13:35:12 10: FATAL: bogus freespace amount 2008-03-31 13:35:12 1: LOG: startup process (PID 856) exited with exit code 1 2008-03-31 13:35:12 2: LOG: aborting startup due to startup process failure 2008-03-31 13:35:13 1: LOG: logger shutting down What can be done to bring the system up again? Thank you in advance! Reimer
[GENERAL] current_query pg_stat_activity column
Hi, I´ve noticed in my Fedora Core 6 box running PG 8.2.3 that column current_query of pg_stat_activity view is not showing the complete query. The complete query: _ SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS, (SELECT DESMAR FROM TT_MAR MAR WHERE INT.FILMAR = MAR.FILMAR AND INT.CODMAR = MAR.CODMAR AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR, INT.DESCREVE, CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade') THEN INT.PROMOC ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X'),'.' ,','),'X','.')) FROM TT_PRE PRE WHERE PRE.FILPRE = '001' AND PRE.CODPRE = '1' AND PRE.FILMAT = INT.FILMAT AND PRE.CODMAT = INT.CODMAT) ELSE 'Ver Grade' END END AS PRECO, CASE WHEN INT.PROMOC = 'Ver Grade' THEN 'Ver Grade' WHEN INT.PROMOC = '0' THEN 'Não' ELSE 'Sim' END AS PROMOC FROM ( SELECT PRO.DESMAT, PRO.ESPMAT, PRO.MONTAR, PRO.DECIMA, PRO.REFBAS, PRO.FILMAR, PRO.CODMAR, PRO.ITEMAR, PRO.FILMAT,PRO.CODMAT,PRO.CODGRA, COALESCE(RPad(PRO.DESMAT,30),'') || COALESCE(RPad(PRO.ESPMAT,30),'') || COALESCE(RPad(PRO.REFBAS,15),'') AS DESCREVE, COALESCE(CASE WHEN PRO.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(CPM.PRECOP,'9,999,999.99'),',','X'),'.', ','),'X','.')) FROM TT_CPM CPM INNER JOIN TT_PRE PRE ON CPM.FILPRE=PRE.FILPRE AND CPM.SEQPRE=PRE.SEQUEN AND COALESCE(CPM.FILPRO,'003') = '003' WHERE PRE.FILMAT = PRO.FILMAT AND PRE.CODMAT = PRO.CODMAT AND PRE.FILPRE = '001' AND PRE.CODPRE = '1' AND CPM.DATVAL >= TRUNC( AGORA()) AND (( CPM.FILPGT = '001' AND CPM.CODPGT = ' 1') OR (CPM.FILPGT IS NULL)) ) ELSE 'Ver Grade' END,'0') AS PROMOC FROM TT_PRO PRO WHERE PRO.SIGNAT >= 0 AND PRO.FILMAT = PRO.FILMAT AND PRO.CODMAT = PRO.CODMAT AND EXISTS (SELECT 1 AS OK FROM TT_GRA GRA WHERE GRA.FILMAT = PRO.FILMAT AND GRA.CODMAT = PRO.CODMAT AND GRA.GRAATI = 'T') ORDER BY PRO.DESMAT, PRO.ESPMAT, PRO.REFBAS ) INT _ The output from the select current_query from pg_stat_activity: _ SELECT INT.DESMAT, INT.ESPMAT, INT.MONTAR, INT.DECIMA, INT.REFBAS, (SELECT DESMAR FROM TT_MAR MAR WHERE INT.FILMAR = MAR.FILMAR AND INT.CODMAR = MAR.CODMAR AND INT.ITEMAR = MAR.ITEMAR) AS DESMAR, INT.DESCREVE, CASE WHEN (INT.PROMOC <> '0') AND (INT.PROMOC <> 'Ver Grade') THEN INT.PROMOC ELSE CASE WHEN INT.CODGRA = 0 THEN (SELECT MIN(REPLACE(REPLACE(REPLACE(TO_CHAR(PRE.PRECOV,'9,999,9990.99'),',','X '),'.',','),'X','.')) FROM TT_PRE PRE WHERE PRE.FILPRE = '001' AND PRE.CODPRE = '2' AND PRE.FILMAT = INT.FILMAT AND PRE.CODMAT = INT.CODMAT) ELSE 'Ver Grade' END END AS PRECO, CASE WHEN INT.PROMOC = 'Ver Grade' THEN ' _ Am I missing anything here? Thank you in advance! Reimer
RES: RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
> "Scott Marlowe" <[EMAIL PROTECTED]> writes: > > On 10/11/07, Carlos H. Reimer <[EMAIL PROTECTED]> wrote: > >> It=B4s "" but the "query_start" column is refreshed. > > > Then the query runs and finishes and the problem is something to do > > with the delivery of the data. Not sure after that... > > That seems to eliminate the possibility that the problem is on the > server side. I'd suggest trying to get a stack trace from the client > to figure out what it's doing. > > BTW, have you looked into the theory that it's triggered by total > data volume rather than number of columns? That is, try selecting > all the columns but use LIMIT to reduce the number of rows fetched? The where clause limits the number of rows returned to 1 only. Only some primery keys are affected. For example, "Select * from table where pk=1" works and returns only one line but "select * from table where pk=2" locks and there is only one line with pk=2 in the table. I believe it is triggered by something else. Reimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
RES: RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
> On 10/11/07, Carlos H. Reimer <[EMAIL PROTECTED]> wrote: > > > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > > > SELECT * or naming all the columns locks the client > > > application. Yesterday > > > > I´ve wrongly said that when naming all the columns instead of > > > using the * > > > > the applications did not lock. > > > > > > Hm, are you sure it's not one specific column that's causing the > > > problem? > > Yes, I´ve just doublechecked again. The table has 11 columns, I used 11 > > SELECTs, one for each column, and all run successfully. Started > adding more > > columns, no problem. When the full list of columns was specified in the > > SELECT it locked. > > If you turn on stats_command_string do you see in > > select * from pg_stat_activity; > > for the current_query ??? It´s "" but the "query_start" column is refreshed. Reimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
RES: RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > SELECT * or naming all the columns locks the client > application. Yesterday > > I´ve wrongly said that when naming all the columns instead of > using the * > > the applications did not lock. > > Hm, are you sure it's not one specific column that's causing the > problem? Yes, I´ve just doublechecked again. The table has 11 columns, I used 11 SELECTs, one for each column, and all run successfully. Started adding more columns, no problem. When the full list of columns was specified in the SELECT it locked. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
Some new data about this issue: SELECT * or naming all the columns locks the client application. Yesterday I´ve wrongly said that when naming all the columns instead of using the * the applications did not lock. I can not reproduce the problem in others 8.2.4 servers. I´ve others 8.2.4 servers and I´m able to do the "SELECT * from tb_produtos where codigo=5002" and always using the same windows psql client. I´ve transfered the table to the other 8.2.4 servers using the pg_dump/psql sequence. If I create another database in the same server, and transfer the table with a pg_dump/psql in this new database the problem persists. Even creating another cluster in the same server and restoring the table there it does not work. This is the only 64 bits box with the 8.2.4, don´t know if this has anything to do with the problem. Don´t know but apparently the problem is not an issue in the client, as I´m able to connect and do the select * in other 8.2.4 servers. Don´t know what kind of tests I should do to help fixing this problem. Any suggestions? Reimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
Hi Erik, > Are all of these remote connections from the same machine? Did you > upgrade your client postgres libraries on your remote machine(s) as > well? No, the problem happens with many machines where our Visual Basic applications is running. After debuging the application we discovered that the problem was always with "select *" statements. We started then some tests to understand what was happening. This tests were done using the psql utility from a Windows Postgresql 8.2.4 server against the SUSE PG 8.2.4 production server. Using this Windows server we make all the other tests against the SUSE PG 8.2.4 production server. Reimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: [GENERAL] 8.2.4 selects make applications wait indefinitely
Thank you Tom, > That sounds like your unspecified "remote client" has got some issues, > but you've not provided any details that would let anyone else figure > it out. The referred client is a Windows psql 8.2.4 utility. This client is running as part of a Windows PostgreSQL 8.2.4 server where we are making this tests against the SUSE Postgresql 8.2.4 server. But the problem first was reported by users of our Visual Basic applications. After debuging the application we realized that all the problems reported with the VB applications were with the "select *" statements. We used the Windows PostgreSQL 8.2.4 server to make the psql "select *" into the SUSE PostgreSQL 8.2.4 server because we suspected that the problem with the VB applications was the ODBC driver but when the psql from the PostgreSQL 8.2.4 Windows server tried to do the same "select *" into the SUSE and it locked, we discard the possibility of a problem with the ODBC driver. Reimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] 8.2.4 selects make applications wait indefinitely
Hi all, We are facing some problems after the migration of our PostgreSQL 8.0 to the 8.2.4 version. The entire box runs under SUSE 10.3. bd_sgp=# select version(); version PostgreSQL 8.2.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.2.1 (SUSE Linux) The problem occurs when some SELECTs does not return any row and the application waits indefinitely. One of the SELECTs that locks is the "SELECT * FROM tb_produtos where codigo=5002;" although the query "SELECT codigo, descricao, embalagem, grupo, marca, unidade, grupo_cliente, codmarca, ativo, kg, codigo_deposito FROM tb_produtos where codigo=5002" runs fine. In summary, if you name all the table columns instead of using the * the query runs fine, otherwise it locks. I've queried the pg_locks and no locks are there when the application was waiting. pg_stat_activity reports that the SELECT was accepted by the database because the column "query_start" is updated although the pg_log (log_statement(all)) does not report it. If the where clause is changed from "codigo=5002" to "codigo=3334" in the "SELECT *" statement, it runs fine. The problem only occurs if we use remote clients, if the "SELECT * from tb_produtos where codigo=5002" is processed by a local(server) psql utility it runs fine too. When we try to run the query in a remote client using the windows psql it locks. The pg_stat_activity's current_query column reports "". We also tried ODBC clients and they lock too. I've defined another table using the LIKE CREATE option and inserted all the 85 lines of tb_produtos into the new one and tried the "SELECT * FROM tb_produtostest where codigo=5002" against it. The query locks too. Summary: Local SELECT * FROM tb_produtos where codigo=5002 Runs Remote SELECT * FROM tb_produtos where codigo=5002 locks Remote SELECT * from tb_produtos where codigo=3334 runs Remote SELECT list of all columns FROM tb_produtos where codigo=5002 runs I´ve noticed one strange local psql behaviour when we try to see the table definition of the tb_produtos table using the \d command. The column named "codigo_deposito" is returned as "ndices:deposito". Apparently is a psql issue because if we query the pg_attribute the column name appears correctly as "codigo_deposito". I'm thinking to install the 8.2.5 to fix this issue. Am I thinking right? Would appreciate any other suggestions. Thank you very much in advance. Reimer
[GENERAL] PostgreSQL abnormally terminating with signal 5
Hi, I´ve a PostgreSQL 8.0.13 running in a Windows XP Pro 2002 SP2 box that is terminating abnormally with signal code 5 every time I make a simple select in one of our tables. 2007-06-04 21:24:12 LOG: database system was shut down at 2007-06-04 21:23:42 E. South America Standard Time 2007-06-04 21:24:12 LOG: checkpoint record is at 4/7D7FFC68 2007-06-04 21:24:12 LOG: redo record is at 4/7D7FFC68; undo record is at 0/0; shutdown TRUE 2007-06-04 21:24:12 LOG: next transaction ID: 1783448; next OID: 3111310 2007-06-04 21:24:12 LOG: database system is ready 2007-06-04 21:27:39 LOG: server process (PID 3512) was terminated by signal 5 2007-06-04 21:27:39 LOG: terminating any other active server processes 2007-06-04 21:27:39 LOG: all server processes terminated; reinitializing 2007-06-04 21:27:40 LOG: database system was interrupted at 2007-06-04 21:24:12 E. South America Standard Time 2007-06-04 21:27:40 LOG: checkpoint record is at 4/7D7FFC68 2007-06-04 21:27:40 LOG: redo record is at 4/7D7FFC68; undo record is at 0/0; shutdown TRUE 2007-06-04 21:27:40 LOG: next transaction ID: 1783448; next OID: 3111310 2007-06-04 21:27:40 LOG: database system was not properly shut down; automatic recovery in progress 2007-06-04 21:27:40 FATAL: the database system is starting up 2007-06-04 21:27:40 LOG: record with zero length at 4/7D7FFCA8 2007-06-04 21:27:40 LOG: redo is not required 2007-06-04 21:27:40 LOG: database system is ready select version(); PostgreSQL 8.0.13 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) Is there anything I can do to help solve this problem? Thanks! Reimer
RES: [GENERAL] Order by behaviour
Hi, I was trying to find the docs about the collating sequence standards but could not find. Would like to know for example which characters are ignored by the "order by" in some of the collating types. Please, can anyone indicate me where could I find documentation about these standards? Thanks in advance! > -Mensagem original- > De: Stephan Szabo [mailto:[EMAIL PROTECTED] > Enviada em: quarta-feira, 28 de março de 2007 19:23 > Para: Carlos H. Reimer > Cc: pgsql-general@postgresql.org > Assunto: Re: [GENERAL] Order by behaviour > > > On Wed, 28 Mar 2007, Carlos H. Reimer wrote: > > > Hi, > > > > We have a PostgreSQL 8.0.6 cluster configured with > lc_collate=pt_BR.UTF-8 > > and when we run the following SELECT: > > SELECT substr(nomerazao,1,4), > > ascii(substr(nomerazao,1,1)), > > ascii(substr(nomerazao,2,1)) > > from spunico.unico order by nomerazao; > > > > is returning: > > > > substr | ascii | ascii > > +---+--- > > |32 | 0 > > |32 | 0 > > 1000 |49 |48 > > 1.DI |49 |46 > > 1° R |49 | 176 > > 2M C |50 |77 > > 3A.G |51 |65 > > A. A |65 |46 > > AABA |65 |65 > > A.A. |65 |46 > > A.AG |65 |46 > > A.A. |65 |46 > > A.A. |65 |46 > > ABAS |65 |66 > > ABAS |65 |66 > > ABAT |65 |66 > > A.B. |65 |46 > > A.B. |65 |46 > > ABCC |65 |66 > > A.B. |65 |46 > > A.B. |65 |46 > > > > Are not the lines out of order or is it a normal behaviour for > a server with > > lc_collate=pt_BR.UTF-8? > > Many collations ignore spaces and symbols on the first pass, so, for > example you might have "A Z" > "AB" despite the fact that a space has a > lower value than a B. > > ---(end of broadcast)--- TIP 6: explain analyze is your friend
RES: [GENERAL] Order by behaviour
Humm, ok, it is clear now. And is there a way to change something in this behaviour, like not ignore spaces and some type of symbols? A configuration file or a patch? Thanks in advance! > -Mensagem original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] nome de Stephan Szabo > Enviada em: quarta-feira, 28 de março de 2007 19:23 > Para: Carlos H. Reimer > Cc: pgsql-general@postgresql.org > Assunto: Re: [GENERAL] Order by behaviour > > > On Wed, 28 Mar 2007, Carlos H. Reimer wrote: > > > Hi, > > > > We have a PostgreSQL 8.0.6 cluster configured with > lc_collate=pt_BR.UTF-8 > > and when we run the following SELECT: > > SELECT substr(nomerazao,1,4), > > ascii(substr(nomerazao,1,1)), > > ascii(substr(nomerazao,2,1)) > > from spunico.unico order by nomerazao; > > > > is returning: > > > > substr | ascii | ascii > > +---+--- > > |32 | 0 > > |32 | 0 > > 1000 |49 |48 > > 1.DI |49 |46 > > 1° R |49 | 176 > > 2M C |50 |77 > > 3A.G |51 |65 > > A. A |65 |46 > > AABA |65 |65 > > A.A. |65 |46 > > A.AG |65 |46 > > A.A. |65 |46 > > A.A. |65 |46 > > ABAS |65 |66 > > ABAS |65 |66 > > ABAT |65 |66 > > A.B. |65 |46 > > A.B. |65 |46 > > ABCC |65 |66 > > A.B. |65 |46 > > A.B. |65 |46 > > > > Are not the lines out of order or is it a normal behaviour for > a server with > > lc_collate=pt_BR.UTF-8? > > Many collations ignore spaces and symbols on the first pass, so, for > example you might have "A Z" > "AB" despite the fact that a space has a > lower value than a B. > > ---(end of broadcast)--- > TIP 1: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to [EMAIL PROTECTED] so that your >message can get through to the mailing list cleanly > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Order by behaviour
Hi, We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8 and when we run the following SELECT: SELECT substr(nomerazao,1,4), ascii(substr(nomerazao,1,1)), ascii(substr(nomerazao,2,1)) from spunico.unico order by nomerazao; is returning: substr | ascii | ascii +---+--- |32 | 0 |32 | 0 1000 |49 |48 1.DI |49 |46 1° R |49 | 176 2M C |50 |77 3A.G |51 |65 A. A |65 |46 AABA |65 |65 A.A. |65 |46 A.AG |65 |46 A.A. |65 |46 A.A. |65 |46 ABAS |65 |66 ABAS |65 |66 ABAT |65 |66 A.B. |65 |46 A.B. |65 |46 ABCC |65 |66 A.B. |65 |46 A.B. |65 |46 Are not the lines out of order or is it a normal behaviour for a server with lc_collate=pt_BR.UTF-8? Thank you in advance! Reimer
[GENERAL] Improve response time of a SQL command
Hi,
I would like to improve the response time of the following SQL command but I
need some help to indentify where is it taking most of the processing time.
It seams that it is in the Seq Scan on tt_gra gra, but not sure. Am I right?
Can a new index help in this issue?
Thank you in advance!
explain analyze
SELECT IPR.REFPRO, IPR.NOMPRO, IPR.MEDUNI,
IVE.VLRMOV /IVE.QTDMOV AS PRECOV,
VEN.DESCON, IVE.QTDMOV,
COALESCE(IVE.TAX001,0) AS ICMS,
SUBSTR(SIT.DESDOM,1,30) AS SITUACAO,
IVE.VLRMOV AS TOTITE,
IOR.FILRES
FROM TT_IVE IVE LEFT OUTER JOIN TV_IPR IPR ON IPR.FILMAT =
IVE.FILMAT AND IPR.CODMAT = IVE.CODMAT AND IPR.CODCOR = IVE.CODCOR AND
IPR.CODTAM = IVE.CODTAM
LEFT OUTER JOIN TT_DOM SIT ON SIT.CODARQ = 'IVE'
AND SIT.NOMCPO = 'SITMOV' AND SIT.CODCHR = IVE.SITMOV
LEFT OUTER JOIN TT_VEN VEN ON IVE.CODFIL =
VEN.CODFIL AND IVE.SEQUEN = VEN.SEQUEN
LEFT OUTER JOIN TT_IOR IOR ON IVE.CODFIL =
IOR.FILIVE AND IVE.SEQUEN = IOR.SEQIVE AND IVE.NUMITE = IOR.NUMIVE
WHERE IVE.CODFIL= '001'
AND IVE.SEQUEN= '113519'
;
QUERY PLAN
-
---
Nested Loop Left Join (cost=2234.61..2326.73 rows=10 width=148) (actual
time=6641.168..6689.295 rows=1 loops=1)
-> Nested Loop Left Join (cost=2234.61..2291.17 rows=9 width=172)
(actual time=6630.985..6679.105 rows=1 loops=1)
-> Hash Left Join (cost=2234.61..2255.78 rows=9 width=163)
(actual time=6630.873..6678.987 rows=1 loops=1)
Hash Cond: ("outer".sitmov = "inner".codchr)
-> Nested Loop Left Join (cost=2231.16..2252.28 rows=9
width=154) (actual time=6628.171..6676.282 rows=1 loo
ps=1)
Join Filter: (("inner".filmat = "outer".filmat) AND
("inner".codmat = "outer".codmat) AND ("inner".codco
r = "outer".codcor) AND ("inner".codtam = "outer".codtam))
-> Index Scan using pk_ive on tt_ive ive
(cost=0.00..17.88 rows=9 width=98) (actual time=17.450..17.45
5 rows=1 loops=1)
Index Cond: ((codfil = '001'::bpchar) AND (sequen
= '113519'::bpchar))
-> Materialize (cost=2231.16..2231.28 rows=12
width=126) (actual time=3.803..6593.478 rows=32069 loops
=1)
-> Subquery Scan ipr (cost=4.69..2231.15
rows=12 width=126) (actual time=3.791..6541.255 rows=32
069 loops=1)
-> Nested Loop (cost=4.69..2231.03
rows=12 width=1264) (actual time=3.775..6353.475 rows=3
2069 loops=1)
-> Hash Join (cost=4.69..2081.99
rows=11 width=658) (actual time=3.257..1305.769 row
s=32069 loops=1)
Hash Cond:
(("outer".codcor)::text = (("inner".codite)::text || ''::text))
-> Hash Join
(cost=2.06..2077.23 rows=161 width=613) (actual time=1.386..697.0
22 rows=32069 loops=1)
Hash Cond:
(("outer".codtam)::text = (("inner".codite)::text || ''::text))
-> Seq Scan on tt_gra
gra (cost=0.00..1672.14 rows=32114 width=551) (act
ual time=0.047..78.800 rows=32069 loops=1)
-> Hash
(cost=2.06..2.06 rows=1 width=70) (actual time=0.087..0.087 rows
=0 loops=1)
-> Nested Loop
(cost=0.00..2.06 rows=1 width=70) (actual time=0.05
2..0.060 rows=1 loops=1)
Join Filter:
("outer".codsub = "inner".codtab)
-> Seq Scan
on tt_sub sub (cost=0.00..1.01 rows=1 width=48)
(actual time=0.014..0.015 rows=1 loops=1)
-> Seq Scan
on td_sub dsub (cost=0.00..1.02 rows=2 width=31)
(actual time=0.011..0.015 rows=2 loops=1)
-> Hash (cost=2.60..2.60
rows=13 width=54) (actual time=0.321..0.321 rows=0 lo
ops=1)
-> Merge Join
(cost=2.40..2.60 rows=13 width=54) (actual time=0.219..0.2
66 rows=13 loops=1)
Merge Cond:
("outer".codtab = "inner".coddiv)
-> Sort
(cost=1.03..1.03 rows=2 width=22) (actual time=0.120..0.12
2 rows=2 loops=1)
Sort Key:
ddiv.codtab
[GENERAL] Bad performance in bulky updates
Hi, We have very bad performance issues in one of our customer PostgreSQL servers and we would like some suggestions to improve the performance for bulky updates. When one of the biggest tables has all lines updated for example, it takes at about 30 minutes for processing. If we drop all indexes (21) and let only the primary index the same update takes 2 minutes. As far as I could see there is no cpu nor memory bottleneck but sar is indicating 50% of iowait during those update processings. What I'm suggesting in priority order: a) add another 2 SCSI disks and move index data to the new drivesb) add a SCSCI controllers with RAID support and move existing disks to the new controllerc) add another disk and move pg_xlog to there Server configuration data:http://www.opendb.com.br/servidor.htm Is there any other suggestion to improve the performance of those updates? Thank you in advance! Carlos
RES: RES: RES: RES: [GENERAL] Dates rejected
Hi Tom, I think I´ve got it... If you change something in the timezone file that is specified in postgresq.conf, PG will know this changes automatically? Am I right? Carlos > -Mensagem original- > De: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] nome de Tom Lane > Enviada em: terça-feira, 17 de outubro de 2006 00:02 > Para: [EMAIL PROTECTED] > Cc: Pgsql-General@Postgresql.Org > Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected > > > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > To discover if it works this way I´ve changed the > /etc/localtime to relect > > the following timezone: > > Um ... what PG version are you working with? 8.0 and up don't pay > attention to /etc/localtime, because they have their own timezone info. > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: RES: RES: RES: [GENERAL] Dates rejected
Yes, it is the 8.0.8 version. You mean that changes to /etc/localtime should not reflect in the to_timestamp behavior? Strange, why does to_timestamp behavior changed here when the /etc/localtime was overlaped? Well, maybe the PG refresh I´ve done after changing the /etc/localtime did it (pg_ctl restart). Anyway, if it will not look at the /etc/localtime, how PG will discover the DST changes? Thank you in advance! Carlos > -Mensagem original- > De: Tom Lane [mailto:[EMAIL PROTECTED] > Enviada em: terça-feira, 17 de outubro de 2006 00:02 > Para: [EMAIL PROTECTED] > Cc: Pgsql-General@Postgresql.Org > Assunto: Re: RES: RES: RES: [GENERAL] Dates rejected > > > "Carlos H. Reimer" <[EMAIL PROTECTED]> writes: > > To discover if it works this way I´ve changed the > /etc/localtime to relect > > the following timezone: > > Um ... what PG version are you working with? 8.0 and up don't pay > attention to /etc/localtime, because they have their own timezone info. > > regards, tom lane > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: RES: RES: [GENERAL] Dates rejected
Hi Tom,
Thank you very much for your explanation!
Let me know if I´ve understood correctly:
If I move the first day DST from Oct 15th to Nov 05th, then the to_timestamp
should show the offset on day Nov 05th and not anymore on Oct 15th, right?
To discover if it works this way I´ve changed the /etc/localtime to relect
the following timezone:
Rule Brazil 2006 only - Nov 05 00:00 1 S
Rule Brazil 2007 only - Feb 25 00:00 0 -
Zone Brazil/hv2006 -3:00 Brazil BR%sT
And tried:
template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/');
to_timestamp
2006-10-15 00:00:00-03
(1 row)
Ok, the result now is reflecting the DST changing of the timezone.
Another tried:
template1=# select pg_catalog.to_timestamp('05/11/2006','dd/mm/');
to_timestamp
2006-11-05 00:00:00-03
(1 row)
Should it not show 2006-11-05 01:00:00-02 as happened before with date
15/10/2006 (dd/mm/)?
Am I missing something?
Thanks in advance!
Carlos
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 21:38
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > The problem is related with the to_timestamp function that
> returns +1 hour
> > offset only for the date 15/10/2006. The 15th october is the
> first day of
> > our day light change.
>
> The reason is that it's generating '2006-10-15 00:00:00-03' to start
> with, but there *is* no such time in your time zone: that was the
> instant that the clocks stepped forward, and so it's equally legitimate
> to display that time as '2006-10-15 01:00:00-02', which is what in fact
> our code happens to do. Then when you coerce the timestamp with time
> zone down to plain timestamp, the offset info that might have cued you
> what's going on goes away...
>
> I believe the lack of a definite midnight hour is one reason why most
> countries prefer to change their clocks at some other time of night.
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>
>
---(end of broadcast)---
TIP 6: explain analyze is your friend
RES: RES: [GENERAL] Dates rejected
Hi,
The problem is related with the to_timestamp function that returns +1 hour
offset only for the date 15/10/2006. The 15th october is the first day of
our day light change.
template1=# select pg_catalog.to_timestamp('15/10/2006','dd/mm/') as
date;
date
2006-10-15 01:00:00-02
(1 row)
Why is this offset present only for date 15/10/2006 (DD/MM/YYY)?
Carlos
> -Mensagem original-
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] nome de Tom Lane
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > select to_date('16/10/2006','DD/MM/');
> >to_date
> > -
> > 2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()? The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/');
> to_date
>
> 2006-10-15
> (1 row)
>
>
> regards, tom lane
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
RES: RES: [GENERAL] Dates rejected
Hi Tom,
You are right, I´ve discovered that the to_date was changed to return a
timestamp, the original function is returning the right values.
The to_date I´ve found:
CREATE OR REPLACE FUNCTION PUBLIC.TO_DATE(text, text) RETURNS TIMESTAMP
AS '
BEGIN
RETURN pg_Catalog.TO_TIMESTAMP($1,$2);
END;
' language 'plpgsql';
I don´t know why they have changed it but anyway why is this changed
function returning 01:00:00 in the hour field only for the date 15/10/2006
(DD/MM/)?
It started happening in the first day when Linux has changed to the day
light time (15/10/2006).
Thanks in advance!
Carlos
> -Mensagem original-
> De: Tom Lane [mailto:[EMAIL PROTECTED]
> Enviada em: segunda-feira, 16 de outubro de 2006 16:27
> Para: [EMAIL PROTECTED]
> Cc: Andreas Kretschmer; Pgsql-General@Postgresql.Org
> Assunto: Re: RES: [GENERAL] Dates rejected
>
>
> "Carlos H. Reimer" <[EMAIL PROTECTED]> writes:
> > select to_date('16/10/2006','DD/MM/');
> >to_date
> > -
> > 2006-10-16 00:00:00
> > (1 row)
>
> Um... what have you done to to_date()? The standard version returns a
> date, not a timestamp:
>
> regression=# select to_date('15/10/2006','DD/MM/');
> to_date
>
> 2006-10-15
> (1 row)
>
>
> regards, tom lane
>
>
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
RES: [GENERAL] Dates rejected
Hi,
I don´t know why the developers build in this way... but let me change a
little bit my question.
I´ve executed tree to_date functions but they give a strange answer for date
16/10/2006 (DD/MM/).
select to_date('16/10/2006','DD/MM/');
to_date
-
2006-10-16 00:00:00
(1 row)
select to_date('15/10/2006','DD/MM/');
to_date
-
2006-10-15 01:00:00
(1 row)
select to_date('14/10/2006','DD/MM/');
to_date
-
2006-10-14 00:00:00
(1 row)
How can we explain the 01:00:00 hour that the to_date function returns for
date 15/10/2006?
Thank you!
Carlos
> -Mensagem original-
> De: Andreas Kretschmer,,, [mailto:[EMAIL PROTECTED] nome de
> Andreas Kretschmer
> Enviada em: segunda-feira, 16 de outubro de 2006 13:41
> Para: pgsql-general@postgresql.org
> Cc: Carlos H. Reimer
> Assunto: Re: [GENERAL] Dates rejected
>
>
> Martijn van Oosterhout schrieb:
> > > create table tt_teste (datfis timestamp without time zone not null
> > > CHECK (datfis = trunc(datfis::timestamp without time zone)));
> >
> > What are you trying to do here? If you only want a date, why not just
> > use a date type?
>
> This is an other question ;-)
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."(unknow)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>
>
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Dates rejected
Hi,
We´ve a simple
insert that is not working. The strange thing is that all kind of date are
working with the exception of 15/10 (DD/MM) dates.
create table tt_teste (datfis timestamp without
time zone not null CHECK (datfis = trunc(datfis::timestamp without time
zone)));
INSERT INTO tt_teste (datfis) VALUES (
to_date('15/10/2006','DD/MM/') );
ERROR: new row for relation "tt_teste"
violates check constraint "tt_teste_datfis_check"
I´ll
appreciate any help!
Thanks in
advance!
Carlos
Reimer
[GENERAL] Mobile servers replication
Hi, We´re looking for a replication solution that could address the following situation: every morning our sellers connect to the master server and make a copy of all tables they will need during the day to their laptop database. After a day of work, with a lot of work done at their local database they reconnect to the master server and resync their data. In my opinion, to address that, this situation must be taken into consideration when the application and database design are designed. Or is there a magic solution, a product, that could do it? I would like to hear some other opinions about this issue. Reimer ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RES: [GENERAL] Phantom groups
Sorry, I would like to say pg_dumpall and not pg_dump. --no-privileges is an option but I need to restore the privileges. There are a lot of them in the system, many many objects and privileges. > -Mensagem original- > De: Chris [mailto:[EMAIL PROTECTED] > Enviada em: quarta-feira, 5 de julho de 2006 00:42 > Para: [EMAIL PROTECTED] > Cc: pgsql-general@postgresql.org > Assunto: Re: [GENERAL] Phantom groups > > > Carlos H. Reimer wrote: > > Hi, > > > > I´m planning to migrate from 7.4 to 8.0.7 and I discovered some strange > > behavior during migration. > > > > pg_dump inserted GRANT to phantom groups. They do not exist in pg_group; > > > > When the script created by pg_dump is processed by psql during restore, > > these GRANTs produce a lot of errors. > > pg_dump won't include users and groups. pg_dumpall would include that > information but of course includes all databases. > > If you use the --no-privileges option with pg_dump it won't include the > grant/revoke statements. > > http://www.postgresql.org/docs/8.0/static/app-pgdump.html > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Phantom groups
Hi, I´m planning to migrate from 7.4 to 8.0.7 and I discovered some strange behavior during migration. pg_dump inserted GRANT to phantom groups. They do not exist in pg_group; When the script created by pg_dump is processed by psql during restore, these GRANTs produce a lot of errors. How could I fix it? Reimer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Lock contention during inserts
Hello, During mass inserts, we have some locking contention in tables referenced by foreign keys. It´s a 8.0.3 box and I know that 8.1 solved this but I would like to know if there is an easy and safe way to only apply this patch to 8.0.3? Reimer ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
RES: [GENERAL] XID comparations
Thanks, In my first question I would like to use xmin instead of cmin, even so I could understand the logic. Then for each XID you have 2 bilions XIDs that are considered lower than and the other 2 bi higher than. About row visibility: are all the rows with xmin higher than my XID be considered in the future and not visible to my XID? Thanks in advance! Reimer ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] XID comparations
Hi, I would like to understand better the logic to determine when a xid is older than another one. As I could understand, the XID is always incremented, never reset. If it is true, then we can have rows with cmin ranging from 1 to 4.294.967.295 (2^32-1). When xid overflows (32 bits) the next one will be 3 (1 and 2 are reserved). In this case, we could have have lines with cmin 4.294.967.295 and lines with cmin 3. How are they compared to determine that rows with cmin 3 are newer than rows with cmin 4.294.967.295? Thanks in advance, Reimer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
