[BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
The following bug has been logged online: Bug reference: 4689 Logged by: Email address: xuan--2009.03--submitbug--support--postgresql@baldauf.org PostgreSQL version: 8.3.5 Operating system: Linux 2.6.18-6-amd64 Description:Expanding the length of a VARCHAR column should not induce a table rewrite Details: Suppose there is a table "sometable" with a column "somecolumn" of type "VARCHAR(5)". This table as many rows. When executing "ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the whole table is re-written, and this rewrite takes many hours. During these hours, all writers on this table stall, making the database effectively unavailable. However, in almost all cases, there is no need for such relaxing of limits to require a table rewrite. So the expected run time needed for this statement is about one second, the actual run time needed for this statement is many hours. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
xuan--2009.03--submitbug--support--postgresql@baldauf.org wrote: When executing "ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the whole table is re-written, and this rewrite takes many hours. During these hours, all writers on this table stall, making the database effectively unavailable. However, in almost all cases, there is no need for such relaxing of limits to require a table rewrite. While this isn't a bug, it's a reasonable feature request. I've added this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER Patches are welcome. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4690: an select query is not using the index
The following bug has been logged online: Bug reference: 4690 Logged by: vikas Email address: vikas.du...@newgen.co.in PostgreSQL version: PostgreSQL 7.3 Operating system: i686-pc-linux-gnu Description:an select query is not using the index Details: hi there is a table PDBFolder whose one column is Parentfolderindex on which an index 'idx_folder_parentfolderindex' is created. when i execute the following query Select ParentFolderIndex From PDBFolder Where ParentFolderIndex In ( Select FolderId From FolderTree Where Leaf = 1::int4 ) it takes approximately 10 minutes and when i see the query execution plan of this query it shows sequence scan on pdbfolder(which contain 50,000 rows). can you suggest there is any way to use index idx_folder_parentfolderindex in above query regards Vikas Dubey -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
Heikki Linnakangas wrote: xuan--2009.03--submitbug--support--postgresql@baldauf.org wrote: When executing "ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the whole table is re-written, and this rewrite takes many hours. During these hours, all writers on this table stall, making the database effectively unavailable. However, in almost all cases, there is no need for such relaxing of limits to require a table rewrite. While this isn't a bug, it's a reasonable feature request. I've added this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER Patches are welcome. The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4690: an select query is not using the index
vikas wrote: The following bug has been logged online: Bug reference: 4690 Logged by: vikas Email address: vikas.du...@newgen.co.in PostgreSQL version: PostgreSQL 7.3 Time to upgrade. Operating system: i686-pc-linux-gnu Description:an select query is not using the index Details: hi there is a table PDBFolder whose one column is Parentfolderindex on which an index 'idx_folder_parentfolderindex' is created. More details please. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut wrote: > The question is how you want to implement this in a data type independent > fashion. You can't assume that increasing the typmod is a noop for all data > types. Sure. See my previous answer on -hackers (I don't think this discussion belong to -bugs) and especially the discussion in the archives about Jonas' patch. -- Guillaume -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4688: Bug in cache.
Tom Lane wrote: Heikki Linnakangas writes: If we go down that path, how far do we go? We also know that two enums are never binary-compatible, right? Composite type and a user-defined base type? Hardly, unless you're doing something very hacky... Disallowing binary casts when any composite types or enums are involved seems sane, but that's as far as we can go with a few lines of code. Arrays have embedded type OIDs too ... I've committed a simple check, disallowing composite types, enums and arrays in binary casts. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4690: an select query is not using the index
Peter Eisentraut writes: > vikas wrote: >> PostgreSQL version: PostgreSQL 7.3 > Time to upgrade. Indeed. 7.4 was the first release that had even an inkling of how to optimize IN (sub-SELECT) clauses. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4692: VACUUM: write to WAL gets very slow and seems redundant
The following bug has been logged online:
Bug reference: 4692
Logged by: Peter Much
Email address: p...@citylink.dinoex.sub.org
PostgreSQL version: 8.2.7
Operating system: FreeBSD 6.3
Description:VACUUM: write to WAL gets very slow and seems redundant
Details:
While most of the time the system does run very fine, sometimes (i perceived
it only during VACUUM) the throughput breaks down dramatically: while
normally the VACUUM fills multiple WAL logs per minute, it then takes 10
minutes to fill each WAL log, while the system is rather idle:
Normal:
Mar 4 03:18:29 edge postgres[1781]: [11-1] :[] LOG: archived
transaction log file "000200420097"
Mar 4 03:19:09 edge postgres[1781]: [12-1] :[] LOG: archived
transaction log file "000200420098"
Mar 4 03:20:26 edge postgres[1781]: [13-1] :[] LOG: archived
transaction log file "000200420099"
Mar 4 03:21:11 edge postgres[1781]: [14-1] :[] LOG: archived
transaction log file "00020042009A"
Defect:
Mar 4 10:16:16 edge postgres[1781]: [84-1] :[] LOG: archived
transaction log file "0002004200E0"
Mar 4 10:25:19 edge postgres[1781]: [85-1] :[] LOG: archived
transaction log file "0002004200E1"
Mar 4 10:37:20 edge postgres[1781]: [86-1] :[] LOG: archived
transaction log file "0002004200E2"
Mar 4 10:44:04 edge postgres[1781]: [87-1] :[] LOG: archived
transaction log file "0002004200E3"
Looking closer at this.
1.) The only process being active on the system is the VACUUM (FULL) task:
6322 ?? Ds17:16.52 postgres: pgsql bacula 192.168.98.6(50283) VACUUM
(po
2.) the system load is near idle.
3.) "top" shows the vacuum process waiting in "bo_waa" (or "biowr") nearly
all the time:
PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND
6322 pgsql 1 -50 76596K 44324K bo_wwa 17:22 1.03% postgres
4.) The disk with the WAL logs is 98% busy, and the only file that is read
or written there is the current WAL log:
[r...@edge /var/pgsql/data]# find . -type f -exec ls -lu {} \; | grep
"Mar 4 12:4"
[r...@edge /var/pgsql/data]# find . -type f -exec ls -l {} \; | grep "Mar
4 12:4"
-rw--- 1 pgsql pgsql 16777216 Mar 4 12:41
./pg_xlog/0002004200F0
5.) lsof shows the file-pointer in the WAL log proceeding:
[r...@edge /var/pgsql/data]# lsof -o pg_xlog/0002004200F0 ;
sleep 10; lsof -o pg_xlog/0002004200F0
COMMAND PID USER FD TYPE DEVICE OFFSET NODE NAME
postgres 1780 pgsql9u VREG 0,167 0t8585216 14344
pg_xlog/0002004200F0
postgres 6322 pgsql 11u VREG 0,167 0t14090240 14344
pg_xlog/0002004200F0
COMMAND PID USER FD TYPE DEVICE OFFSET NODE NAME
postgres 1780 pgsql9u VREG 0,167 0t8585216 14344
pg_xlog/0002004200F0
postgres 6322 pgsql 11u VREG 0,167 0t14524416 14344
pg_xlog/0002004200F0
So, during 10 seconds the 6322 process has proceeded 434176 bytes, giving 43
kBps.
6.) iostat shows the WAL disk processing ~60 accesses per second, 16kB each
(while the database disk does nearly nothing):
WAL
KB/t tps MB/s
16.70 69 1.12
16.76 63 1.03
16.00 45 0.70
16.00 58 0.90
16.71 68 1.10
This shows about 1000 kBps.
iostat does not show me which process does this activity, but since the
VACUUM process 6322 is the only active process, and since this process is in
"biowr" state all the time, the activity could hardly come from an other
process.
Evaluating, we have ~1000 kBps thruput but only 43 kBps write advance on the
WAL log, this gives about factor 1:20. And from the logs we see: about
30secs per WAL log in normal operation, and about 10mins in the defect
state, this is also 1:20.
So it seems I am looking at the right thing, and there is no problem on the
system. The only question is: what the is this VACUUM process doing?
When killing the 6322 process, the next task behaves normal:
WAL DB
KB/t tps MB/s KB/t tps MB/s
82.67 3 0.24 22.79 197 4.39
31.25 8 0.24 21.16 154 3.19
26.00 8 0.20 21.43 164 3.44
44.80 5 0.22 24.44 143 3.42
22.40 5 0.11 23.85 162 3.78
24.00 4 0.09 22.45 195 4.28
The behaviour occurs only occasionally; as we can see from the postgres log
above, at 3AM the VACUUM was running fine, only later it started with this
behaviour - for no apparent reason.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4693: When I use the order by it gets me an error, but if i use it without order by it's a correct query.
The following bug has been logged online: Bug reference: 4693 Logged by: Oscar Bejarano Email address: obejara...@msn.com PostgreSQL version: 8.3.0 Operating system: Suse 10 Description:When I use the order by it gets me an error, but if i use it without order by it's a correct query. Details: ===QUERY= select r1.nombreestado,r1.nombreplantel,r1.clavepsp,r1.primerapellido,r1.segundoape llido,r1.nombre,r1.sesion,r1.integracion,r1.cantidad_estudiantil,r1.estudian til,((r1.sesion+r1.integracion+r1.estudiantil)/3) as promedio from ( select t1.clavepsp,t1.nombreestado,t1.nombreplantel,t1.nombre,t1.primerapellido,t1. segundoapellido, (select t2.calificacion from tbevaluaciones_terminadas t2 where t2.evaluacion_tipo='1' and t2.clavepsp=t1.clavepsp) as sesion, (select t3.calificacion from tbevaluaciones_terminadas t3 where t3.evaluacion_tipo='3' and t3.clavepsp=t1.clavepsp) as integracion, (select count(t4.clavepsp) from tbevaluaciones_terminadas t4 where t4.clavepsp=t1.clavepsp and t4.evaluacion_tipo='2') as cantidad_estudiantil, (select avg(t5.calificacion) from tbevaluaciones_terminadas t5 where t5.clavepsp=t1.clavepsp and t5.evaluacion_tipo='2') as estudiantil from( select distinct(a.clavepsp),c.nombre as nombreestado,d.nombre as nombreplantel,a.primerapellido,a.segundoapellido,a.nombre from ctpsps a left join ctestados c on a.claveestado_plantel=c.claveestado left join ctplanteles d on a.claveplantel=d.claveplantel where 1=1 and a.clavepsp in (select b.clavepsp from tbevaluaciones_terminadas b where 1=1 and b.fecha <='31/12/2008') order by 1,2,3,4,5,6 ) t1 ) r1 order by 1,2,3,4,5,6 Thanks, Oscar -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #4691: Installation error
The following bug has been logged online: Bug reference: 4691 Logged by: Gregory Clark Email address: gregwillcl...@hotmail.com PostgreSQL version: 8.2 Operating system: Windows Embedded Standard Description:Installation error Details: I have a Windows Embedded Standard machine just for Postgres 8.2 and no other application runs in the background. I get an error when trying to install Postgre Database Server 8.1.The Error is "Failed to run initdb: 1! Please see the logfile in 'C:\Program Files\PostgreSQL\8.1\tmp\initdb.log'. Note! You must read/copy this logfile before you click OK, or it will be automatically removed." In the Log file "initdb.log" there are: Begnning of log file: - The files belonging to this database system will be owned by user "postgresql". This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory C:/Program Files/PostgreSQL/8.1/data ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/global ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_xlog ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_xlog/archive_status ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_clog ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_subtrans ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_twophase ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_multixact/members ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_multixact/offsets ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/base ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/base/1 ... ok creating directory C:/Program Files/PostgreSQL/8.1/data/pg_tblspc ... ok selecting default max_connections ... The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. 10 selecting default shared_buffers ... The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. The system cannot find the file specified. 50 creating configuration files ... ok creating template1 database in C:/Program Files/PostgreSQL/8.1/data/base/1 ... ok initializing pg_authid ... The system cannot find the file specified. child process was terminated by signal 1 initdb: removing contents of data directory "C:/Program Files/PostgreSQL/8.1/data" End Of Log File >From this log i can't tell what file is missing and i can't fix the problem. Please email response back on how to solve the problem or how to find out who the missing file is.. mail: gregwillcl...@hotmail.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4693: When I use the order by it gets me an error, but if i use it without order by it's a correct query.
On Wed, Mar 4, 2009 at 2:05 PM, Oscar Bejarano wrote: > > The following bug has been logged online: > > Bug reference: 4693 > Logged by: Oscar Bejarano > Email address: obejara...@msn.com > PostgreSQL version: 8.3.0 > Operating system: Suse 10 > Description: When I use the order by it gets me an error, but if i > use it without order by it's a correct query. > Details: > > ===QUERY= which ORDER BY is causing troubles to you? the one in ther outer query or the one in the inner query? can you provide at least the error message? a little test case would be useful -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4691: Installation error
"Gregory Clark" writes: > I get an error when trying to install Postgre Database Server 8.1.The Error > is "Failed to run initdb: 1! Why are you trying to install 8.1? We don't support that on Windows anymore, for good and sufficient reasons. > creating directory C:/Program Files/PostgreSQL/8.1/data/pg_tblspc ... ok > selecting default max_connections ... The system cannot find the file > specified. > The system cannot find the file specified. > The system cannot find the file specified. > The system cannot find the file specified. > The system cannot find the file specified. > The system cannot find the file specified. > 10 > selecting default shared_buffers ... The system cannot find the file > specified. It sorta looks like you are missing postgres.exe. But it's hard to imagine why you'd have initdb and not that --- maybe a permissions problem? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4691: Installation error
On Wed, Mar 4, 2009 at 9:32 PM, Tom Lane wrote: > "Gregory Clark" writes: >> I get an error when trying to install Postgre Database Server 8.1.The Error >> is "Failed to run initdb: 1! > > Why are you trying to install 8.1? We don't support that on Windows > anymore, for good and sufficient reasons. > >> creating directory C:/Program Files/PostgreSQL/8.1/data/pg_tblspc ... ok >> selecting default max_connections ... The system cannot find the file >> specified. >> The system cannot find the file specified. >> The system cannot find the file specified. >> The system cannot find the file specified. >> The system cannot find the file specified. >> The system cannot find the file specified. >> 10 >> selecting default shared_buffers ... The system cannot find the file >> specified. > > It sorta looks like you are missing postgres.exe. But it's hard to > imagine why you'd have initdb and not that --- maybe a permissions > problem? initdb will bail out before it gets to that point if it can't find postgres.exe. I'm wondering about the use of Windows Embedded. I'm not sure if anyone has ever tested that - and personally I have no idea whether it may be cut down in ways that may affect Postgres. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4691: Installation error
Dave Page writes: > On Wed, Mar 4, 2009 at 9:32 PM, Tom Lane wrote: >> "Gregory Clark" writes: >>> The system cannot find the file specified. >>> The system cannot find the file specified. >>> The system cannot find the file specified. >>> The system cannot find the file specified. >>> The system cannot find the file specified. >> It sorta looks like you are missing postgres.exe. But it's hard to >> imagine why you'd have initdb and not that --- maybe a permissions >> problem? > initdb will bail out before it gets to that point if it can't find > postgres.exe. Hmm. Maybe the complaints actually come from failing to load some DLL that postgres.exe needs. > I'm wondering about the use of Windows Embedded. I'm not sure if > anyone has ever tested that - and personally I have no idea whether it > may be cut down in ways that may affect Postgres. ... which would fit with the missing-DLL theory. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4691: Installation error
On Wed, Mar 4, 2009 at 9:47 PM, Tom Lane wrote: > Dave Page writes: >> initdb will bail out before it gets to that point if it can't find >> postgres.exe. > > Hmm. Maybe the complaints actually come from failing to load some DLL > that postgres.exe needs. We don't delay-load anything, so we'd see the failure when initdb calls find_other_exec to validate the version of postgres.exe. That would fail, and initdb would abort. Besides - Windows always spits out DLL not found messages in a message box for some annoying reason. >> I'm wondering about the use of Windows Embedded. I'm not sure if >> anyone has ever tested that - and personally I have no idea whether it >> may be cut down in ways that may affect Postgres. > > ... which would fit with the missing-DLL theory. I was wondering if there are limitations or incompatibilities in the ipc apis for example. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
