Re: [GENERAL] fsync on ext4 does not work
I have run fsync_test on this partition, and I got 2500+ for all kind of sync method. dmesg says: blkfront: xvde: barriers enabled blkfront: xvda: barriers enabled One thing I haven't mentioned yet, that this a VM virtualized with Xen. Perhaps this has some effect. Thanks, Otto 2011/12/20 Greg Smith > On 12/19/2011 10:52 AM, Havasvölgyi Ottó wrote: > >> PgSql 9.1.2 >> Debian, 2.6.32 kernel >> WAL filesystem: ext4 with defaults >> > > There's a pg_test_fsync program included with the postgresql-contrib > package that might help you sort out what's going on here. This will > eliminate the possibility that you're doing something wrong with pgbench, > and give an easy to interpret number relative to the drive RPM rate. > > You said default settings, which eliminated "nobarrier" as a cause here. > The only other thing I know of that can screw up fsync here is using one > of the incompatible LVM features to build your filesystem. I don't know > which currently work and don't work, but last I checked there were a few > ways you could set LVM up that would eliminate filesystem barriers from > working properly. You might check: > > dmesg | grep barrier > > To see if you have any kernel messages related to this. > > Here's a pg_test_fsync example from a Debian system on 2.6.32 with ext4 > filesystem and 7200 RPM drive, default mount parameters and no LVM: > > $ ./pg_test_fsync > 2000 operations per test > O_DIRECT supported on this platform for open_datasync and open_sync. > > Compare file sync methods using one 8kB write: > (in wal_sync_method preference order, except fdatasync > is Linux's default) >open_datasync n/a >fdatasync 113.901 ops/sec >fsync 28.794 ops/sec >fsync_writethroughn/a >open_sync 111.726 ops/sec > > Compare file sync methods using two 8kB writes: > (in wal_sync_method preference order, except fdatasync > is Linux's default) >open_datasync n/a >fdatasync 112.637 ops/sec >fsync 28.641 ops/sec >fsync_writethroughn/a >open_sync 55.546 ops/sec > > Compare open_sync with different write sizes: > (This is designed to compare the cost of writing 16kB > in different write open_sync sizes.) >16kB open_sync write 111.909 ops/sec > 8kB open_sync writes 55.278 ops/sec > 4kB open_sync writes 28.026 ops/sec > 2kB open_sync writes 14.002 ops/sec > 1kB open_sync writes 7.011 ops/sec > > Test if fsync on non-write file descriptor is honored: > (If the times are similar, fsync() can sync data written > on a different descriptor.) >write, fsync, close28.836 ops/sec >write, close, fsync28.890 ops/sec > > Non-Sync'ed 8kB writes: >write 112113.908 ops/sec > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
Re: [GENERAL] fsync on ext4 does not work
Thank you guys for the ideas and suggestions, I will check them. Best regards, Otto
Re: [GENERAL] fsync on ext4 does not work
2011/12/19 Florian Weimer > * Havasvölgyi Ottó: > > > Even though the TPS in pgbench about 700 with 1 client. > > I have tried other sync methods (fdatasync, open_sync), but all are > similar. > > Should I disable write cache on HDD to make it work? > > Did you mount your ext4 file system with the nobarrier option? > > By default, ext4 is supposed to cope properly with hard disk caches, > unless the drive is lying about completing writes (but in that case, > disabling write caching is probably not going to help much with > reliability, either). > It is mounted with defaults, no other option yet, so it should flush. These HDDs are 7200 rpm SATA with some low level software RAID1. I cannot understand why disabling HDD write cache does not help either. Could you explain please? There is also an InnoDB transaction log on this partition, but its commit time is quite longer. On the same workload PgSql's commit is about 1 ms, but InnoDB's is about 4-7 ms. I think 4-7 is also too short to flush something to such disk, am I right? Or perhaps does it do something different? It is set to fsync synchronously. Also a difference that as I increase concurrency, InnoDb's avg. commit time is going up quite quickly, however PgSql's one rather slowly. I wonder if this is because InnoDb really flushes to disk, or just because PostgreSQL is better :). Best regards, Otto > -- > Florian Weimer > BFK edv-consulting GmbH http://www.bfk.de/ > Kriegsstraße 100 tel: +49-721-96201-1 > D-76133 Karlsruhe fax: +49-721-96201-99 >
Re: [GENERAL] fsync on ext4 does not work
2011/12/19 Tomas Vondra > On 19 Prosinec 2011, 16:52, Havasvölgyi Ottó wrote: > > config: > > fsync=on > > sync_commit=on > > wal_sync_method=fsync > > I don't think you need to set wal_sync_method, comment it out. > > > Even though the TPS in pgbench about 700 with 1 client. > > I have tried other sync methods (fdatasync, open_sync), but all are > > similar. > > Should I disable write cache on HDD to make it work? > > Yes, disable that. > > > Have you any idea why? > > What scale factor have you used with pgbench? And how long are the pgbench > runs? The smaller the data set, the more it will be affected by the write > cache. > Scale factor was 1, client count 1, and ran it for 100 seconds. I just wanted to check that the commit rate does not go beyond 120 (7200 rpm HDD). > Tomas > >
[GENERAL] fsync on ext4 does not work
Hi all, Somewhy fsync does not work for me. PgSql 9.1.2 Debian, 2.6.32 kernel WAL filesystem: ext4 with defaults config: fsync=on sync_commit=on wal_sync_method=fsync Even though the TPS in pgbench about 700 with 1 client. I have tried other sync methods (fdatasync, open_sync), but all are similar. Should I disable write cache on HDD to make it work? Have you any idea why? Thanks, Otto
Re: [GENERAL] Rounding incompatibility
Yes, they are both your packages from your official site. So this means that in 8.2 and in earlier versions the rounding is not the regular one. Best regards, Otto 2009/6/15 Dave Page > On Mon, Jun 15, 2009 at 3:33 PM, Tom Lane wrote: > > =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= > writes: > >> I have found the following strangeness on Windows versions: > > > > Were your two versions built different ways (perhaps with different > > compilers)? This comes down to what the system-supplied rint() function > > does. > > If they are our packages, then 8.2 was built with mingw/msys, and 8.3 with > VC++. > > > -- > Dave Page > EnterpriseDB UK: http://www.enterprisedb.com >
[GENERAL] Rounding incompatibility
Hi, I have found the following strangeness on Windows versions: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); select round(value) from round_test; psql 8.2.13 returns 2 -2 4 But psql 8.3.3 returns 1 -1 3 Trying more values it seems that 8.2 rounding works according to banker's rounding rules. Can you confirm this? How can I avoid this incompatibility or perhaps bug? Thanks, Otto
Re: [GENERAL] Trigger Function and backup
Hi, I have found the following strangeness on Windows: create table round_test (id int primary key, value double precision); insert into round_test(id, value) values(1, 1.5); insert into round_test(id, value) values(2, -1.5); insert into round_test(id, value) values(3, 3.5); select round(value) from round_test; psql 8.2.13 returns 2 -2 4 But psql 8.3.3 returns 1 -1 3 What does cause this? How can I avoid this incompatibility or perhaps bug? Thanks, Otto
Re: [GENERAL] 8.3: timestamp subtraction
Thanks Tom for your comments. I meant the build in this directory: http://www.postgresql.org/ftp/binary/v8.3.6/win32/, and the builds for win32 of other versions in the binary directory. What is the trend of these builds regarding floating point timestamps? For example what about 8.4? Thanks, Otto 2009/5/24 Tom Lane > =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= writes: > > Thanks, It's off in both 8.2 and 8.3. > > As was already stated, that depends on which build you're using. > (And no, "the Win32 distribution on the PgSql site" is not a unique > description, not even for a single PG version.) > > > What will be the default in 8.4? > > The same comment will apply to 8.4. There is a general trend away from > floating point timestamps, but there will probably be some builds > continuing to use them for a long time to come, because of compatibility > considerations. > >regards, tom lane >
Re: [GENERAL] 8.3: timestamp subtraction
Thanks, It's off in both 8.2 and 8.3. What will be the default in 8.4? Best regards, Otto 2009/5/23 Alvaro Herrera > Havasvölgyi Ottó escribió: > > I mean the Win32 distribution on the PgSql site. I always used that. > > If you want to find out whether a particular build used floating point or > integer datetimes, issue "SHOW integer_datetimes". > > If it says "off", then it's floating point. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ <http://www.commandprompt.com/> > PostgreSQL Replication, Consulting, Custom Development, 24x7 support >
Re: [GENERAL] 8.3: timestamp subtraction
I mean the Win32 distribution on the PgSql site. I always used that. It would be very good if these data types were exact by default, even if that's a bit slower. Otto 2009/5/23 Christophe > > On May 23, 2009, at 10:44 AM, Havasvölgyi Ottó wrote: > > Thanks. >> I tested the standard Win32 distribution of 8.3.6. >> The same happens on 8.2. But on 8.0 it works. >> >> When I don't use milliseconds, then it works. >> >> Will 8.4 work fine on Win32 again? >> > > If the issue is using floating point timestamps, then the particular > version of PostgreSQL isn't the issue; it's whether the distribution you > were using was built with integer or floating point timestamps. > > -- > 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] 8.3: timestamp subtraction
Hi,
On 8.2 this comparision is also not true:
select '240:0:0.3'::interval = '10 0:0:0.3'::interval;
But without milliseconds it's true.
Is this also because interval is represented internally as a floating point
value?
On 8.3 this test does not fail.
Best regards,
Otto
2009/5/23 Havasvölgyi Ottó
> Thanks.
> I tested the standard Win32 distribution of 8.3.6.
> The same happens on 8.2. But on 8.0 it works.
>
> When I don't use milliseconds, then it works.
>
> Will 8.4 work fine on Win32 again?
>
> Thanks,
> Otto
>
>
>
> 2009/5/23 Ludwig Kniprath
>
> Scott Marlowe schrieb:
>>
>>> On Sat, May 23, 2009 at 7:18 AM, Christophe wrote:
>>>
>>>
>>>> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>>>
>>>>
>>>>> I don't know why this query returns false:
>>>>>> SELECT '20040506 070809.01'::timestamp(6) - '20010203
>>>>>> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
>>>>>> millisecond'::interval;
>>>>>> If I just subtract the two timestamps, its result is the interval I
>>>>>> specified.
>>>>>> What may cause this?
>>>>>>
>>>>>>
>>>>> It works for me:
>>>>>
>>>>> test=> SELECT '20040506 070809.01'::timestamp(6) -
>>>>> '20010203 040506.007000'::timestamp(6)=
>>>>> '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
>>>>> ?column? --
>>>>> t
>>>>> (1 row)
>>>>>
>>>>>
>>>> Could this be due to the OP's build of PG using floating point
>>>> timestamps?
>>>>
>>>>
>>>
>>> That's what I'm thinking
>>>
>> Me too, a testquery-result on a Windows-System with version "PostgreSQL
>> 8.3.0, compiled by Visual C++ build 1400":
>>
>> SELECT ('20040506 070809.01'::timestamp(6) - '20010203
>> 040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
>> millisecond'::interval) * 1e10;
>>
>> => -00:01:28.220986
>>
>>
>> --
>> 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] 8.3: timestamp subtraction
Thanks.
I tested the standard Win32 distribution of 8.3.6.
The same happens on 8.2. But on 8.0 it works.
When I don't use milliseconds, then it works.
Will 8.4 work fine on Win32 again?
Thanks,
Otto
2009/5/23 Ludwig Kniprath
> Scott Marlowe schrieb:
>
>> On Sat, May 23, 2009 at 7:18 AM, Christophe wrote:
>>
>>
>>> On May 23, 2009, at 9:13 AM, Daniel Verite wrote:
>>>
>>>
I don't know why this query returns false:
> SELECT '20040506 070809.01'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval;
> If I just subtract the two timestamps, its result is the interval I
> specified.
> What may cause this?
>
>
It works for me:
test=> SELECT '20040506 070809.01'::timestamp(6) -
'20010203 040506.007000'::timestamp(6)=
'1188 day 3 hour 3 minute 3 second 3 millisecond'::interval;
?column? --
t
(1 row)
>>> Could this be due to the OP's build of PG using floating point
>>> timestamps?
>>>
>>>
>>
>> That's what I'm thinking
>>
> Me too, a testquery-result on a Windows-System with version "PostgreSQL
> 8.3.0, compiled by Visual C++ build 1400":
>
> SELECT ('20040506 070809.01'::timestamp(6) - '20010203
> 040506.007000'::timestamp(6) - '1188 day 3 hour 3 minute 3 second 3
> millisecond'::interval) * 1e10;
>
> => -00:01:28.220986
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
[GENERAL] 8.3: timestamp subtraction
Hi, I don't know why this query returns false: SELECT '20040506 070809.01'::timestamp(6) - '20010203 040506.007000'::timestamp(6) = '1188 day 3 hour 3 minute 3 second 3 millisecond'::interval; If I just subtract the two timestamps, its result is the interval I specified. What may cause this? Thanks, Otto
[GENERAL] Some rare questions
Hi, I am writing a driver for PostgreSQL, and I need some rare info: How can I query the collation/locale of the database cluster? What can be the maximal length of the indexed part of the string. So I have a text field, and I create an index on it. How long can be one index key max in this case? How deep can be the subquery nesting? How long can be the index key? What is maximal number of compare operations for a single query? What is maximal length of a query text (characters) ? What is maximal length of a row (bytes) ? What is the maximal length of char/varchar/text ? What operations cannot be rolled back with Rollback ? Thanks in advance, Otto ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Creating then dropping primary key constraint
Hi, I noticed that when I create a primary key with ALTER TABLE ... ADD CONSTRAINT ... PRIMARY KEY (...), and then drop this constraint, then the "not null" modifier stays on the column on which the primary key was defined although there were no constraint on that column before. Is this normal? Pg 8.0.4 create table pritest(id integer); \d pritest alter table pritest add constraint pk_pritest primary key (id); \d pritest alter table pritest drop constraint pk_pritest; \d pritest drop table pritest; Best regards, Otto ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PL/PGSQL parameter count vs perfomace
Hi,
I would sometimes need a lot of parameters, even 100 or so. These would be
the data access functions for tables. I know the default count limit is 32,
and FUNC_MAX_ARGS compile option should be set to, say, 256.
But I have another option, a bit harder, I could pass the parameters in a
record type. Passing parameters such way is a problematic because the
provider I would use does not support it yet.
My question is that is the second method faster?
Simple example:
create table person(id serial primary key, name text not null, address
text);
--First option:
create function person_ins(_name text, _address text) -- <<--
returns person
language plpgsql
as
$$
declare
inserted_row person;
begin
--insert row
insert into person (name, address) values (_name, _address); -- <<-
--retrieve inserted row
select into inserted_row * from person where
id=currval('person_id_seq'::text);
--return with it
return inserted_row;
end;
$$;
--Second option:
create function person_ins(_person person) --<<
returns person
language plpgsql
as
$$
declare
inserted_row person;
begin
--insert row
insert into person (name, address) values (_person.name,
person.address); -- <<--
--retrieve inserted row
select into inserted_row * from person where
id=currval('person_id_seq'::text);
--return with it
return inserted_row;
end;
$$;
I hope there are no syntax errors.
Of course in a real application I would have a lot more parameters.
Best Regards,
Otto
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] explain analyzing a query inside an sql stored procedure
Hi, Is there any easy way to explain analyze a query, which is inside an sql stored procedure? I could of course copy the query out of the procedure, and explain analyze it but this is a slower process. I would do this with a lot of procedures, that's why it should be fast. create function myquery() returns setof record as $$ select * from mytable; --this is a set returning query $$ language sql; explain analyze select * from myquery(); For me this shows function scan, and not the plan of the query itself. Best Regards, Otto ---(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
Re: [GENERAL] dbf to pgsql
Peter, There is a dbf2pg in the contrib library, I have tried to use it, but I had some probems with it: I had a numeric field whose decimal length was 0. Dbf2pg couldn't handle this situation, it said that it has an illegel number format. So I had to generate manually a big convertion script, and I fed to psql. EMS has a Data Import tool, it can handle DBF too. Best Regards, Otto - Original Message - From: "Piotr" <[EMAIL PROTECTED]> To: Sent: Thursday, August 04, 2005 10:42 AM Subject: [GENERAL] dbf to pgsql Hi, Im lookig for tool to regulary transfer data from dbf files into pgsql. Would be excellent if the tool would have data tranformation possibility. regards Peter ---(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 5: don't forget to increase your free space map settings
Re: [GENERAL] feeding big script to psql
Tom, My queries were written in multi-line mode like this: insert into t1 values(1, 2, 3); I don't know, what effect this has to performace.. Regards, Otto - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, August 03, 2005 1:03 AM Subject: Re: [GENERAL] feeding big script to psql =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: Thanks for the suggestion. I have just applied both switch , -f (I have applied this in the previous case too) and -n, but it becomes slow again. At the beginning it reads about 300 KB a second, and when it has read 1.5 MB, it reads only about 10 KB a second, it slows down gradually. Maybe others should also try this scenario. Can I help anything? Well, I don't see it happening here. I made up a script consisting of a whole lot of repetitions of insert into t1 values(1,2,3); with one of these inserted every 1000 lines: \echo 1000 `date` so I could track the performance. I created a table by hand: create table t1(f1 int, f2 int, f3 int); and then started the script with psql -q -f big.sql testdb At the beginning I was seeing about two echoes per second. I let it run for an hour, and I was still seeing about two echoes per second. That's something close to 170MB of script file read (over 5.7 million rows inserted by the time I stopped it). So, either this test case is too simple to expose your problem, or there's something platform-specific going on. I don't have a windows machine to try it on ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
Re: [GENERAL] feeding big script to psql
Scott, There were no foreign keys (even no indices) during data import, and none of the tables had more than 4000 records. And I have checked the log for durations, and all insert statements were 0.000 ms. So it seems that the problem is not at the server. During the process no other application did anything. No other HDD activity either. Best Regadrs, Otto - Original Message - From: "Scott Marlowe" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; Sent: Tuesday, August 02, 2005 5:57 PM Subject: Re: [GENERAL] feeding big script to psql On Tue, 2005-08-02 at 04:24, Havasvölgyi Ottó wrote: Tom, Thanks for the suggestion. I have just applied both switch , -f (I have applied this in the previous case too) and -n, but it becomes slow again. At the beginning it reads about 300 KB a second, and when it has read 1.5 MB, it reads only about 10 KB a second, it slows down gradually. Maybe others should also try this scenario. Can I help anything? I be you've got an issue where a seq scan on an fk field or something works fine for the first few thousand rows. At some point, pgsql should switch to an index scan, but it just doesn't know it. Try wrapping every 10,000 or so inserts with begin; commit; analyze; begin; rinse, wash repeat. You probably won't need an analyze after the first one though. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] feeding big script to psql
Hi, Now I am at 7 MB, and the reading speed is 3-4KB/sec. Best Regards, Otto - Original Message - From: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> To: Sent: Tuesday, August 02, 2005 1:31 PM Subject: Re: [GENERAL] feeding big script to psql Hi, The effect is the same even if I redirect the output to file with the -o switch. At the beginning 200 KB/sec, at 1.5 MB the speed is less than 20 KB/sec. Best Regards, Otto - Original Message - From: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 02, 2005 11:24 AM Subject: Re: [GENERAL] feeding big script to psql Tom, Thanks for the suggestion. I have just applied both switch , -f (I have applied this in the previous case too) and -n, but it becomes slow again. At the beginning it reads about 300 KB a second, and when it has read 1.5 MB, it reads only about 10 KB a second, it slows down gradually. Maybe others should also try this scenario. Can I help anything? Best Regards, Otto - Original Message ----- From: "Tom Lane" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 02, 2005 3:54 AM Subject: Re: [GENERAL] feeding big script to psql =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: I know it would be faster with COPY, but this is extremly slow, and the bottleneck is psql. What is the problem? Hmm, does the Windows port have readline support, and if so does adding the "-n" switch to the psql invocation fix the problem? Or you could try feeding the script with -f switch or \i rather than "psql ---(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 4: Have you searched our list archives? http://archives.postgresql.org ---(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
Re: [GENERAL] feeding big script to psql
Hi, The effect is the same even if I redirect the output to file with the -o switch. At the beginning 200 KB/sec, at 1.5 MB the speed is less than 20 KB/sec. Best Regards, Otto - Original Message - From: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 02, 2005 11:24 AM Subject: Re: [GENERAL] feeding big script to psql Tom, Thanks for the suggestion. I have just applied both switch , -f (I have applied this in the previous case too) and -n, but it becomes slow again. At the beginning it reads about 300 KB a second, and when it has read 1.5 MB, it reads only about 10 KB a second, it slows down gradually. Maybe others should also try this scenario. Can I help anything? Best Regards, Otto - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 02, 2005 3:54 AM Subject: Re: [GENERAL] feeding big script to psql =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: I know it would be faster with COPY, but this is extremly slow, and the bottleneck is psql. What is the problem? Hmm, does the Windows port have readline support, and if so does adding the "-n" switch to the psql invocation fix the problem? Or you could try feeding the script with -f switch or \i rather than "psql ---(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 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] feeding big script to psql
Tom, Thanks for the suggestion. I have just applied both switch , -f (I have applied this in the previous case too) and -n, but it becomes slow again. At the beginning it reads about 300 KB a second, and when it has read 1.5 MB, it reads only about 10 KB a second, it slows down gradually. Maybe others should also try this scenario. Can I help anything? Best Regards, Otto - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, August 02, 2005 3:54 AM Subject: Re: [GENERAL] feeding big script to psql =?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: I know it would be faster with COPY, but this is extremly slow, and the bottleneck is psql. What is the problem? Hmm, does the Windows port have readline support, and if so does adding the "-n" switch to the psql invocation fix the problem? Or you could try feeding the script with -f switch or \i rather than "psql ---(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] feeding big script to psql
Hi, A generated a big SQL script (about 20 Mb), and fed it to psql. I was very surprised that within a minute psql became quite slow. There were areas, where less than 10 row were inserted in a second. This is on a WinXP machine with local server 8.0.3, and only I use it. Looking at the log files of PG the commands are executed fast. I have monitored psql with te TaskManager, and it was at 45-50% CPU (this is Hyperthreaded, so 50% is the max for one thread), and the memory allocation/deallocation was very active, even about +-2Mb/sec. The command were simple create table and insert commands chunked into several lines like this: CREATE TABLE aeloleg( vevo CHAR(6), szallito INTEGER, datum DATE, hatarido DATE, vevo_nev CHAR(30), ir_szam INTEGER, helyseg CHAR(20), cim CHAR(20), befizetes INTEGER, terheles INTEGER, hitel INTEGER, rendeles INTEGER, jel CHAR(1), trans INTEGER, szoveg TEXT, storno BOOLEAN) WITHOUT OIDS; The insert commands for one table were surrounded by BEGIN and COMMIT like this: CREATE BEGIN INSERT INSERT ... INSERT COMMIT I know it would be faster with COPY, but this is extremly slow, and the bottleneck is psql. What is the problem? Regards, Otto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Select for update
Hi, Yes, I misspelled in the mail, I don't any way to copy it from the console, so I rewrote it here. I will post if I can reproduce it again. I hope that I have missed something. Best Regards, Otto - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Friday, July 29, 2005 3:12 AM Subject: Re: [GENERAL] Select for update On Fri, Jul 29, 2005 at 12:05:46AM +0200, Havasvölgyi Ottó wrote: create function pidtest_del(_pid integer) returns void as $$ declare row pidtest; begin perform pid from pidtest where pid>=_pid for update; delete from pidtest where pid=_pid; for row in select * from pidtest where pid>_pid order by pid loop update pidtest set pid=pid-1 where pid=row.pid; end loop; return; end; $$ language plpgslq; I suspect this isn't exactly the code you're running, because creating this function fails with the following error: ERROR: language "plpgslq" does not exist If I correct the spelling to "plpgsql" then I get the following results: SELECT * FROM pidtest; pid | szoveg -+ 3 | three 4 | four 5 | five 6 | six 7 | seven (5 rows) SELECT pidtest_del(5); pidtest_del - (1 row) SELECT * FROM pidtest; pid | szoveg -+ 3 | three 4 | four 5 | six 6 | seven (4 rows) Sorry, now I cannot reproduce it, but yesterday I was suprised that the szoveg field's contents in the locked records went away. What you describe isn't supposed to happen, but we can't do much to investigate the problem unless we can see how to reproduce it. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 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
Re: [GENERAL] Select for update
Bruno, I know this is inefficient. In fact it was someone other's problem to eliminate gaps and I solved it this way. I don't do such things for myself. Best Regards, Otto - Original Message - From: "Bruno Wolff III" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Friday, July 29, 2005 8:58 AM Subject: Re: [GENERAL] Select for update On Fri, Jul 29, 2005 at 00:05:46 +0200, Havasvölgyi Ottó <[EMAIL PROTECTED]> wrote: This function deletes a row, and updates the pid field where pid is geater than the deleted pid value, so that the gap caused by the deletion is not present any more. This isn't directly related to your problem, but why are you doing this? This is a pretty inefficient thing to be doing unless there is a business rule that there can't be gaps in the pid values. If you just want to number rows in reports, you should do that in your reporting application, not by continuously renumbering your records. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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
Re: [GENERAL] Select for update
Hi, Oh, sorry. CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS; INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three'); INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four'); INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five'); INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six'); INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven'); create function pidtest_del(_pid integer) returns void as $$ declare row pidtest; begin perform pid from pidtest where pid>=_pid for update; delete from pidtest where pid=_pid; for row in select * from pidtest where pid>_pid order by pid loop update pidtest set pid=pid-1 where pid=row.pid; end loop; return; end; $$ language plpgslq; This function deletes a row, and updates the pid field where pid is geater than the deleted pid value, so that the gap caused by the deletion is not present any more. Sorry, now I cannot reproduce it, but yesterday I was suprised that the szoveg field's contents in the locked records went away. Best Regards, Otto - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Thursday, July 28, 2005 2:02 PM Subject: Re: [GENERAL] Select for update On Thu, Jul 28, 2005 at 01:22:31PM +0200, Havasvölgyi Ottó wrote: Is it normal that when I select for update a record, but I don't select all the fields, that the contents of fields not selected will be deleted: create table pidtest(pid integer, szoveg text) without oids; select pid from pistest where pid>5 for update; After committing (autocommit), the contents of the szoveg field for the locked rows will be erased. Could you provide a complete test case? Works fine here: CREATE TABLE pidtest (pid integer, szoveg text) WITHOUT OIDS; INSERT INTO pidtest (pid, szoveg) VALUES (3, 'three'); INSERT INTO pidtest (pid, szoveg) VALUES (4, 'four'); INSERT INTO pidtest (pid, szoveg) VALUES (5, 'five'); INSERT INTO pidtest (pid, szoveg) VALUES (6, 'six'); INSERT INTO pidtest (pid, szoveg) VALUES (7, 'seven'); SELECT pid FROM pidtest WHERE pid > 5 FOR UPDATE; pid - 6 7 (2 rows) SELECT * FROM pidtest; pid | szoveg -+ 3 | three 4 | four 5 | five 6 | six 7 | seven (5 rows) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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 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
[GENERAL] Select for update
Hi, Is it normal that when I select for update a record, but I don't select all the fields, that the contents of fields not selected will be deleted: create table pidtest(pid integer, szoveg text) without oids; select pid from pistest where pid>5 for update; After committing (autocommit), the contents of the szoveg field for the locked rows will be erased. PostgreSQL 8.0.3 WinXP Regards, Otto ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] checkpoint segments
Hi, Sorry, this is Pg 8.0.3 Regards, Otto ---(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
[GENERAL] checkpoint segments
Hi, I have a small database on my Windows XP, I rarely use it. Even so, the log file show says this: [2005-07-02 02:02:09] LOG: received fast shutdown request [2005-07-02 02:02:09] LOG: checkpoints are occurring too frequently (0 seconds apart) [2005-07-02 02:02:09] HINT: Consider increasing the configuration parameter "checkpoint_segments". [2005-07-02 02:02:09] LOG: shutting down [2005-07-02 02:02:10] LOG: database system is shut down [2005-07-02 02:02:13] LOG: logger shutting down My checkpoint_segments setting is default, 3, and there are 2 wal log files. Why does it say, that "too frequenty"? In fact what are checkpoints? These files? I couldn't understand it exactly from the docs. Thanks, Otto ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] field alias in where condition
Hi all, I issued the following queries: select substring(proname from 1 to 1) as nevresz, count(*) from pg_proc where nevresz = 'a' order by nevresz group by nevresz; select substring(proname from 1 to 1) as nevresz, count(*) from pg_proc order by nevresz group by nevresz; The first query fails, and says that column 'nevresz' does not exist. The second is OK. What is the problem? I cannot use column alias in where condition? Thanks, Otto ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] interval integer comparison
Thank you Tom. It was a bit confusing because my WHERE clause looked something like this: ... WHERE date_field - current_date < '21 days'::interval; And then I got records, whose with date_field's year was 2010. :-o Now I am using this formula: ... WHERE date_field < current_date + '21 days'::interval; Best Regards, Otto - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Havasvölgyi Ottó" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, May 31, 2005 5:46 PM Subject: Re: [GENERAL] interval integer comparison > =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes: > > Pg 8.0.3 allows me to compare interval with integer, but I cannot see any > > reasonable rule: > > > 1 < '1 days'::interval > > The reason that doesn't fail outright is that both integer and > interval have implicit coercions to text. So the only interpretation > the parser can find is to convert both sides to text and use the text < > operator. As text comparisons your answers all make sense. > > I've been arguing for a long time that we need to cut down on the number > of implicit coercions to text... > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Accessing PostgreSQL from C++
Jeff, Yes, libpq. Look at the 27th chapter in the manual. Otto - Original Message - From: "Jeff Brown" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 31, 2005 8:31 AM Subject: [GENERAL] Accessing PostgreSQL from C++ > Hi guys > > Is there some sort of C API available for PostgreSQL? > > Cheers > Jeff > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] interval integer comparison
Hi all, Pg 8.0.3 allows me to compare interval with integer, but I cannot see any reasonable rule: These are true: 1 < '1 days'::interval 2 > '1 days'::interval 999 > '1 days'::interval 1999 < '2 days'::interval 2000 != '2 days'::interval 2001 > '2 days'::interval ... 20999 < '21 days'::interval 21000 != '21 days'::interval 21001 > '21 days'::interval and so on Is this a bug? Best Regards, Otto ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Manipulating a dataset on the client side
Hi, Situation: The client program queries a dataset from the PG server, and it displays it, say, in a table. The user can navigate and update fields, insert new records, delete records. And any change should be visible on all clients. I think I have basically 2 choices: 1 : using SELECT and download the whole dataset with pg_exec() 2 : using cursors (pg_exec(..,'DECLARE ... ')) In the 1st case after each update,insert delete of the user I should run the query again to see the changes on this client ? This can be slow if the table is large. The behaviour of the 2nd case is not so trivial for me. I know that this will be faster for large tables, but I don't know if the change I perform will be visible for this user after a simple refetch without opening another cursor. DECLARE and CLOSE should be enclosed in a transaction, and with SAVEPOINTs I think I can commit the user's operations before closing the cursor. Please help, Otto
