Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Hi Tom, hi Ludwig,
Thanks for your support. Yes, this query has grown very big with time, and I
was always asked to add exceptions in it, so the result is quite frightening!
TOM: If you try setting enable_bitmapscan off,
you'll probably find 8.1 beating 7.4 handily for this query.
Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with
enable_bitmapscan = off.
LUDWIG: What about adding an index to the field
etapes_lignes_commandes(code_etape)
I have this index already.
LUDWIG: What about increasing the settings of work_mem?
default work_mem = 1024 == 511 seconds
work_mem = 2048 == 531 seconds
TOM: Am I right in guessing that your database is small
enough to fit into RAM on the new server?
select pg_database_size('groupefpdb');
returns 360428168
That's 360 MB. It fits in RAM, correct!
TOM: If so, it would be reasonable to reduce random_page_cost,
perhaps all the way to 1.0, and this would probably improve
the quality of the planner's choices for you.
With that change I get results in 193 seconds.
TOM: What might work better is to get rid of the indexes
w_code_type_workflow and lw_ordre --- do you have any
queries where those are actually useful?
Yes, I think I do, but let's try:
Drop both indexes
VACUUM FREEZE ANALYZE
ANALYZE
I get the results in 199 seconds.
TOM: Another thing you should look at is increasing the
cpu-cost parameters. The numbers in your EXPLAIN ANALYZE
results suggest that on your new machine the cost of
processing an index tuple is about 1/50th of the cost of
touching an index page; that is, you ought to have
cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
I'd try setting each of them to 0.01 and increasing
cpu_tuple_cost a little bit, maybe to 0.02.
cpu_index_tuple_cost = 0.01
cpu_operator_cost = 0.01
cpu_tuple_cost = 0.02
With these change, plus random_page_cost = 1, I get results in 195 seconds.
(Yes, I did restart the server!)
The new EXPLAIN ANALYSE at this point is here:
Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt
The old EXPLAIN ANALYZE are still here:
Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4 without tuning:
http://www.attiksystem.ch/explain_analyze_81.txt
Is there maybe something I could tune further on the kernel side? I get only 20
% improvements with the new server with Linux, compared to the workstation with
freebsd... Maybe my query is so CPU-bound that the most important thing is the
CPU clock speed, and in both cases I have a single 3Ghz CPU to run the query?
What do you think?
Philippe
-Message d'origine-
De : Tom Lane [mailto:[EMAIL PROTECTED]
Envoyé : mercredi, 31. mai 2006 18:21
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Philippe Lang [EMAIL PROTECTED] writes:
Here are both EXPLAIN ANALYSE results, plus the query itself:
Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
Query is here: http://www.attiksystem.ch/big_query.txt
My goodness, that is one big ugly query :-(
Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes
from the repeated occurrences of this pattern:
SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
INNER JOIN workflows AS w
ON lw.id_workflow = w.id
WHERE w.code_article = lignes_commandes.code_article
AND w.code_type_workflow = commandes.code_type_workflow
AND SUBSTRING(lw.code_etape FROM 1 FOR 3) =
SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
AND lw.ordre = etapes_lignes_commandes.ordre
7.4 is doing this as
- Nested Loop (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087
rows=1 loops=13653)
- Index Scan using w_code_article on workflows w (cost=0.00..15.76
rows=1 width=4) (actual time=0.016..0.024 rows=1 loops=13653)
Index Cond: (code_article = $1)
Filter: (code_type_workflow = $2)
- Index Scan using lw_id_workflow on lignes_workflows lw
(cost=0.00..21.51 rows=1 width=12) (actual time=0.023..0.036 rows=1 loops=13651)
Index Cond: (lw.id_workflow = outer.id)
Filter: ((substring((code_etape)::text, 1, 3) =
substring(($3)::text, 1, 3)) AND (ordre = $4))
8.1 is doing
- Nested Loop (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434
rows=1 loops=13630)
- Bitmap Heap Scan on workflows w (cost=6.63..10.51 rows=1 width=4)
(actual time=0.107..0.107 rows=1 loops=13630)
Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
- BitmapAnd (cost=6.63..6.63 rows=1 width=0) (actual
time=0.104..0.104 rows=0 loops=13630)
- Bitmap Index Scan on w_code_article (cost=0.00..2.02
rows=5 width=0) (actual time=0.017..0.017 rows=5 loops=13630)
Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
About three years ago I did a test to compare the performance of an
Apache-PHP-PostgreSQL web application on Linux vs. FreeBSD. I used the
same machine with the then current versions of the said software. The
results were better by 30-35% on FreeBSD. Since then I have been having
the sneaking feeling that FreeBSD generally performs better than Linux.
(Actually, my personal experience had always been that FreeBSD was much
more responsive on very slow machines (Intel 486) when used
interactively. But this could have been a result of the rougher
scheduler in earlier Linuxes and despite better responsiveness, overall
performance could still have been better with Linux.)
Peter
Philippe Lang wrote:
Hi Tom, hi Ludwig,
Thanks for your support. Yes, this query has grown very big with time, and I
was always asked to add exceptions in it, so the result is quite frightening!
TOM: If you try setting enable_bitmapscan off,
you'll probably find 8.1 beating 7.4 handily for this query.
Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with
enable_bitmapscan = off.
LUDWIG: What about adding an index to the field
etapes_lignes_commandes(code_etape)
I have this index already.
LUDWIG: What about increasing the settings of work_mem?
default work_mem = 1024 == 511 seconds
work_mem = 2048 == 531 seconds
TOM: Am I right in guessing that your database is small
enough to fit into RAM on the new server?
select pg_database_size('groupefpdb');
returns 360428168
That's 360 MB. It fits in RAM, correct!
TOM: If so, it would be reasonable to reduce random_page_cost,
perhaps all the way to 1.0, and this would probably improve
the quality of the planner's choices for you.
With that change I get results in 193 seconds.
TOM: What might work better is to get rid of the indexes
w_code_type_workflow and lw_ordre --- do you have any
queries where those are actually useful?
Yes, I think I do, but let's try:
Drop both indexes
VACUUM FREEZE ANALYZE
ANALYZE
I get the results in 199 seconds.
TOM: Another thing you should look at is increasing the
cpu-cost parameters. The numbers in your EXPLAIN ANALYZE
results suggest that on your new machine the cost of
processing an index tuple is about 1/50th of the cost of
touching an index page; that is, you ought to have
cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
I'd try setting each of them to 0.01 and increasing
cpu_tuple_cost a little bit, maybe to 0.02.
cpu_index_tuple_cost = 0.01
cpu_operator_cost = 0.01
cpu_tuple_cost = 0.02
With these change, plus random_page_cost = 1, I get results in 195 seconds.
(Yes, I did restart the server!)
The new EXPLAIN ANALYSE at this point is here:
Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt
The old EXPLAIN ANALYZE are still here:
Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4 without tuning:
http://www.attiksystem.ch/explain_analyze_81.txt
Is there maybe something I could tune further on the kernel side? I get only 20
% improvements with the new server with Linux, compared to the workstation with
freebsd... Maybe my query is so CPU-bound that the most important thing is the
CPU clock speed, and in both cases I have a single 3Ghz CPU to run the query?
What do you think?
Philippe
-Message d'origine-
De : Tom Lane [mailto:[EMAIL PROTECTED]
Envoyé : mercredi, 31. mai 2006 18:21
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Philippe Lang [EMAIL PROTECTED] writes:
Here are both EXPLAIN ANALYSE results, plus the query itself:
Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
Query is here: http://www.attiksystem.ch/big_query.txt
My goodness, that is one big ugly query :-(
Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes
from the repeated occurrences of this pattern:
SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
INNER JOIN workflows AS w
ON lw.id_workflow = w.id
WHERE w.code_article = lignes_commandes.code_article
AND w.code_type_workflow = commandes.code_type_workflow
AND SUBSTRING(lw.code_etape FROM 1 FOR 3) =
SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
AND lw.ordre = etapes_lignes_commandes.ordre
7.4 is doing this as
- Nested Loop (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087
rows=1 loops=13653)
- Index Scan using w_code_article on workflows w (cost=0.00..15.76
rows=1 width=4) (actual time=0.016..0.024 rows=1 loops=13653)
Index Cond: (code_article = $1)
Filter: (code_type_workflow = $2)
- Index Scan using lw_id_workflow on lignes_workflows lw
(cost=0.00..21.51
Re: [GENERAL] SCSI disk: still the way to go?
Hi Alex, thanks for the answer (thanks to the other guys too!). http://www.cdw.com/shop/products/default.aspx?EDC=912784 SATA - ~$320 Is there a particular reason why you chose a SATA-150 drive? What about SATA-300?Cheers,Riccardo
[GENERAL] strange stable function behavior
HiI have a stable function test.test_stableCREATE OR REPLACE FUNCTION test.test_stable(int4) RETURNS int4 AS$BODY$DECLARE _param ALIAS FOR $1;BEGIN RAISE NOTICE 'ID: %, TIME: %', _param, timeofday()::timestamp; RETURN _param;END$BODY$ LANGUAGE 'plpgsql' STABLE STRICT SECURITY DEFINER;Everything is all right when I execute a simple querySELECT id, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl NOTICE: ID: 10, TIME: 2006-06-01 14:57:07.89594NOTICE: ID: 11, TIME: 2006-06-01 14:57:07.896203NOTICE: ID: 12, TIME: 2006-06-01 14:57:07.896322NOTICE: ID: 13, TIME: 2006-06-01 14:57:07.896417NOTICE: ID: 14, TIME: 2006-06-01 14:57: 07.896494NOTICE: ID: 15, TIME: 2006-06-01 14:57:07.896623But if I want to display field sid twiceSELECT id, sid, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl I can see that function test.test_stable executes twice with identical parametersNOTICE: ID: 10, TIME: 2006-06-01 14:58:52.950292 NOTICE: ID: 10, TIME: 2006-06-01 14:58:52.950485 NOTICE: ID: 11, TIME: 2006-06-01 14:58:52.950582 NOTICE: ID: 11, TIME: 2006-06-01 14:58:52.950679 NOTICE: ID: 12, TIME: 2006-06-01 14:58:52.950765 NOTICE: ID: 12, TIME: 2006-06-01 14:58:52.950835 NOTICE: ID: 13, TIME: 2006-06-01 14:58:52.9511 NOTICE: ID: 13, TIME: 2006-06-01 14:58:52.975477 NOTICE: ID: 14, TIME: 2006-06-01 14:58:52.992098 NOTICE: ID: 14, TIME: 2006-06-01 14:58:53.008741 NOTICE: ID: 15, TIME: 2006-06-01 14:58:53.025425 NOTICE: ID: 15, TIME: 2006-06-01 14:58:53.058589 Is it bug or special feature?PostgresPostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1 20050727 (Red Hat 4.0.1-5)-- Verba volent, scripta manentDan Black
Re: [GENERAL] strange stable function behavior
On Thu, Jun 01, 2006 at 03:09:47PM +0400, Dan Black wrote: Hi I have a stable function test.test_stable snip But if I want to display field sid twice SELECT id, sid, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl I can see that function test.test_stable executes twice with identical parameters Postgres makes no special effort to avoid multiple calls of the same function. Especially since you declared it STABLE. Is it bug or special feature? Not a bug nor a special feature, just the way it is. If you put OFFSET 0 in the subquery, that will stop the expansion of the subquery, thus the function will only be called once. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Restoring databases from a different installment on Windows
Bruno Wolff III wrote: Yes it should work. There should be a recovery if postgres was running when the boc crashed. Note, you don't want to copy files out from under a running server to do backups. That won't work. Actually, my situation is like this: I had a properly running version of Postgres when my Windows crashed. I Installed a fresh copy on Windows on another disk, and then Postgres on top of it. Then I started the system from the new Windows, stopped the Postgres service and copied the data directory from the old system to the new one, but Postgres service refused to start. So it was not some kind of a backup copy, it was a regular directory of another installation. Any ideas? Thanks, Berislav ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB
Yes, we have the same problem! Refer to the thread with the subject Problem V8.1.4 - providing pwd for commandline tools doesn't work anymore. They seem to have change the old behaviour of the commandline tools, which worked well since 8.0! I don't understand why the change should be more secure, but I see that nobody took care about the possible consequences for installation scripts, third party applications and so on. :-((( At least they should have clearly stated this in the update readme. (The comment Fix problem with password prompting on some Win32 systems just says nothing at all, which could have alerted anybody that might concern the effect of it) Sigh, Alexander. on 31.05.2006 09:12 Pit Müller wrote: Hello ! We have a very sophisticated setup for our software which needs to install databases on a PostgreSQL Server if the user chooses PG as database system. This setup worked fine on 8.1 to 8.1.3 but fails now with 8.1.4. It looks like the problem is caused by CREATEDB, which seems to hang. Does anyone know this problem ? Regards, Pit ---(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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Insert into partition table hangs
I am still having the same problem. I tried to eliminate any possible network issues by placing the application on the server itself, but the same thing happened. However, I had an interesting discovery today that I don't understand. I left the insert query that was stuck running. Then, without changing any parameters, I ran reload configuration, aka. sending SIGHUP, and the query immediately completed and went on to the next insert. I also noticed that I am having the similar issue with SELECT statements. Same SELECT statement will run fine 3-4 times in a row and then get stuck and run forever with no results. But if I reload the configuration like above, the query immediatelly returns with correct result. Why does this happen, and could this give any insight in my problem? Thanks. ---(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] Best open source tool for database design / ERDs?
Thanks Thomas. That's too bad about DB Designer. I didn't realize it had been abandoned. What do y'all think of DIA? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problem V8.1.4 - providing pwd for commandline tools doesn't
I don't understand why the change should be more secure, but I see that nobody took care about the possible consequences for installation scripts, third party applications and so on. :-((( If you change such essential behaviour (the old behaviour of the commandline tools did work well since 8.0!) and cause such an incompatibility, you should have at least clearly stated this in the update readme. The comment Fix problem with password prompting on some Win32 systems is a little bit short regarding such an immens impact on any installation script... Sigh, Alexander. on 31.05.2006 10:00 Magnus Hagander wrote: The latest version (V8.1.4) breaks the setup process of our application's installation wizard. We used to call createdb.exe and piped the password for the postgres user (which has been entered by the user in our setup wizard's dialogs) into it. With version V8.1.4 this is not possible anymore, the commandline process prompts for the password nevertheless! This causes our setup to hang (of course it doesn't hang, it waits for the invisible commandline process to return, which waits for a user input that will never complete as the user cannot and shall not see these commandline tools). Any suggestion? (V8.1.3 and earlier did work fine in this respect.) Actually, no, v8.1.3 and earlier are the ones that are broken in this respect, and it was fixed in 8.1.4. You need to put the password either in a pgpass.conf file (http://www.postgresql.org/docs/8.1/static/libpq-pgpass.html), or put it in the PGPASSWORD environment variable (http://www.postgresql.org/docs/8.1/static/libpq-envars.html - it's secure on Windows per the comment) //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Best open source tool for database design / ERDs?
Anyone know if DIA will generate CREATE TABLE statements from an ER diagram? I'd like to have a program where I can create my db design, then be able to instantiate the design in PostgreSQL as well as MySQL. I'll pay for a good commercial tool if it costs less than USD $100. Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] GPL Licensed Files in 8.1.4
PostgreSQL 8.1.4 appears to have 2 GPL licensed files according to licenses embedded in the source. In both cases, the files have had a history of discussion within the PostgreSQL forums. I'm hoping, through this question, to find more concrete information that the GPL code is really approved to be licensed under the PostgreSQL BSD license or to understand if there is a plan to update the source to remove the GPL reference. The 2 files are: - user_lock code from Massimo Dal Zotto - clean_pending.pl from Steve Singer, Navtech Systems I noticed that Massimo Zotto posted a forum answer back in 2001 (: http://archives.postgresql.org/pgsql-hackers/2001-08/msg01089.php) but his statement that the file would be updated to non-GPL text seems to not have been completed. Is there any other authorization or correspondence stating his approval that the user_lock code is licensed to the PostgreSQL project as BSD? There was also a separate forum entry (http://archives.postgresql.org/pgsql-hackers/2005-06/msg00350.php) on clean_pending.pl where it was speculated that the GPL text was added in error since the other source files in this directory are BSD. Has Steve Singer forwarded an authorization that this file is licensed to the PostgreSQL project as BSD? Thank you, Charles Comiskey Tivoli Software IBM Software Group [EMAIL PROTECTED] 919.224.1223 or TL 687-1223
Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem
Philippe Lang a écrit : Hardware is much more powerful: intel server motherboard, dual-xeon 3GHz, SCSI disks (raid 1), 4GB RAM. Do you need the hyperthreading ? Depending on your case, you might have better results with 2x3GHz thant 4x1.5GHz. -- Arnaud ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] UTF-8 context of BYTEA datatype??
On Thu, 2006-06-01 at 02:00 +, Greg Sabino Mullane wrote: #!perl package testone; use DBI; printf SQL_INTEGER is %d\n, SQL_INTEGER; package testtwo; use DBI qw(:sql_types); printf SQL_INTEGER is %d\n, SQL_INTEGER; But this is not as bad as having to use DBD:Pg (or any other dviver speciffic include). unlike most other data types, it is very important that DBD::Pg (and libpq, and the backend) be told explicitly that a binary string is being used, so that the length can be sent, as a null character may not represent the end of the string. Well, for a humble utility programmer like myself - not really knowing the internals - it's *very* desirable to be able to just CREATE TABLE with 'binary' column, and as a result, have the client library know that, and act on provided data accordingly. The most desirable state is when my script works equally well with any driver - like in case, when the sriver is selected on command line (and I don't really mean here eval 'require $ARGV[0]' :). Martijn van Oosterhout asked: Why isn't PQexecPrepared always used? And why does typing it SQL_BINARY not do the same? SQL_BINARY is not the same as PG_BYTEA - we don't necessarily handle binary strings the same way as other databases. Still, it may be worth revisiting This is something I don't understand. As a programmer, I have *chosen* the PG_BYTEA (or to be precise: I've chosen to: CREATE TABLE test (img BYTEA)), just to have the functionality of a binary opoque value - not interpretted in any way by the RDBMS (like: not converted according to clinet_encoding). In my opinion I meant SQL_BINARY. So if in the postresql RDMBS, there is no other datatype closer to the SQL_BINARY semantics, the PG_BYTEA should be just a synonym. -- Rafal Pietrak [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] strange stable function behavior
Thank you very much. It works!!!Where can I read about such features? 2006/6/1, Martijn van Oosterhout kleptog@svana.org: On Thu, Jun 01, 2006 at 03:09:47PM +0400, Dan Black wrote: Hi I have a stable function test.test_stablesnip But if I want to display field sid twice SELECT id, sid, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl I can see that function test.test_stable executes twice with identical parametersPostgres makes no special effort to avoid multiple calls of the same function. Especially since you declared it STABLE. Is it bug or special feature?Not a bug nor a special feature, just the way it is. If you put OFFSET 0in the subquery, that will stop the expansion of the subquery, thus the function will only be called once.Have a nice day,--Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFEftscIB7bNG8LQkwRAjITAJ9csUN2V8oHtfRk280cJYTqkpopIwCfRVQ0 cToHKTMqSf4HD21f+bo3jn0==/z/0-END PGP SIGNATURE--- Verba volent, scripta manentDan Black
Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alexander Scholz Sent: 31 May 2006 09:56 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB Yes, we have the same problem! Refer to the thread with the subject Problem V8.1.4 - providing pwd for commandline tools doesn't work anymore. They seem to have change the old behaviour of the commandline tools, which worked well since 8.0! I don't understand why the change should be more secure, but I see that nobody took care about the possible consequences for installation scripts, third party applications and so on. :-((( At least they should have clearly stated this in the update readme. (The comment Fix problem with password prompting on some Win32 systems just says nothing at all, which could have alerted anybody that might concern the effect of it) The reason was that the way it was broken, a number of people had been completely unable to use psql/pg_dump etc because in certain situations it would (mistakenly) attempt to read the their password from a file and not their command prompt window. I don't think anyone ever imagined that the bug actually allowed people to use utilities in a way that was useful to them, but unintended by everyone else. Had we done, we would certainly have made the warnings more obvious and considered workarounds. Regards, Dave. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problem V8.1.4 - providing pwd for commandline tools doesn't
I don't understand why the change should be more secure, but I see that nobody took care about the possible consequences for installation scripts, third party applications and so on. :-((( If the installation scripts and third party apps followed the standard way of doing it, they would not be affected. What has been done is to change the win32 version of the tools to behave the exact same way as the tools have behaved on Unix for years. Previously, the win32 implementation was not in sync with the documentation. After 8.1.4, it now is. (There's also the fact that the old, incorrect, handling had a serious bug in it that could make it impossible to connect to the database if you had a c:\dev directory on your machine) If you change such essential behaviour (the old behaviour of the commandline tools did work well since 8.0!) and cause such an incompatibility, you should have at least clearly stated this in the update readme. The comment Fix problem with password prompting on some Win32 systems is a little bit short regarding such an immens impact on any installation script... That, however, I agree with. It should've been clearer in the release notes. We just didn't anticipate that people relied on teh old buggy behaviour. Perhaps that's worth updating in the release notes before we release 8.1.5, what do people think? //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] GPL Licensed Files in 8.1.4
Am Mittwoch, 31. Mai 2006 16:50 schrieb Charles Comiskey: PostgreSQL 8.1.4 appears to have 2 GPL licensed files according to licenses embedded in the source. In both cases, the files have had a history of discussion within the PostgreSQL forums. I'm hoping, through this question, to find more concrete information that the GPL code is really approved to be licensed under the PostgreSQL BSD license The GPL-licensed files are GPL-licensed. or to understand if there is a plan to update the source to remove the GPL reference. Yes, but that will entail removing or rewriting the code or the author(s) giving permission to change the license, not just removing the reference. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] PGSQL 7.4 - 8.1 migration performance problem
Hi, I made some tests, with and without Hyperthreading: with hyperthreading, on a dual-processor, top mentions a 25% load, and without, 50%, but computing time is exactly the same. Philippe -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Arnaud Lesauvage Envoyé : mercredi, 31. mai 2006 16:20 À : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 - 8.1 migration performance problem Philippe Lang a écrit : Hardware is much more powerful: intel server motherboard, dual-xeon 3GHz, SCSI disks (raid 1), 4GB RAM. Do you need the hyperthreading ? Depending on your case, you might have better results with 2x3GHz thant 4x1.5GHz. -- Arnaud ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq smime.p7s Description: S/MIME cryptographic signature
[GENERAL] postgres in windows
Hi all. My name is Antonios and I am doing an MSc in Advanced computing at Imperial College London. I need to install postgres for my individual project on windows. Would it be a problem to ask one- or -two questions? First of all is there an installer for postgres for windows? what do i need to install postgres in windows? King regards, Antonios
Re: [GENERAL] Best open source tool for database design / ERDs?
You might look into Eclipse (the java-based IDE). It has at least one ERD design plugin that allows graphical layout, editing of schema, and generation of DDL directly from the schema. It works with many DB platforms and is FREE!!! Sean On 6/1/06 5:44 AM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Anyone know if DIA will generate CREATE TABLE statements from an ER diagram? I'd like to have a program where I can create my db design, then be able to instantiate the design in PostgreSQL as well as MySQL. I'll pay for a good commercial tool if it costs less than USD $100. Thanks. ---(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] Best open source tool for database design / ERDs?
On Thu, 1 Jun 2006, Sean Davis wrote: You might look into Eclipse (the java-based IDE). It has at least one ERD design plugin that allows graphical layout, editing of schema, and generation of DDL directly from the schema. It works with many DB platforms and is FREE!!! Allow me to second that suggestion. I used one (whose name I don't recall) from Azzurri in Japan. It was an easy installation into eclipse, allowed me to design the schema and relations, then generated the postgres statements. I used it on one project; haven't had the need since then (and that was 2.5 years ago). Rich -- Richard B. Shepard, Ph.D. | The Environmental Permitting Applied Ecosystem Services, Inc.(TM) | Accelerator http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(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] Best open source tool for database design / ERDs?
Dia itself is just a diagramming tool. However, there are a number of apps at http://www.gnome.org/projects/dia/links.html that will take Dia diagram files and generate db schemas for you... [EMAIL PROTECTED] wrote: Anyone know if DIA will generate CREATE TABLE statements from an ER diagram? I'd like to have a program where I can create my db design, then be able to instantiate the design in PostgreSQL as well as MySQL. I'll pay for a good commercial tool if it costs less than USD $100. Thanks. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] postgres in windows
On 6/1/06, Antonios Katsikadamos [EMAIL PROTECTED] wrote: Hi all. My name is Antonios and I am doing an MSc in Advanced computing at Imperial College London. I need to install postgres for my individual project on windows. Would it be a problem to ask one- or -two questions? First of all is there an installer for postgres for windows? http://www.postgresql.org/ftp/binary/v8.1.4/win32/ Get the postgresql-8.1.4-1.zip file. -- Postgresql php tutorials http://www.designmagick.com/ ---(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] Fat 32/NTFS
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Antonios KatsikadamosSent: 01 June 2006 15:18To: pgsql-general@postgresql.orgSubject: [GENERAL] Fat 32/NTFS Hi all again. sorry for the disturbance but I am not aware of the postgres installation procedure. my laptop runs Win xp on format fat32. I get a messagefrom installation that requires NTFS format. Is there a way of installing postgres on fat 32 without using linux? what do u propose? See the FAQ: http://www.postgresql.org/docs/faqs.FAQ_windows.html#2.4 Regards, Dave.
Re: [GENERAL] Fat 32/NTFS
On Thursday 1. June 2006 16:18, Antonios Katsikadamos wrote: Hi all again. sorry for the disturbance but I am not aware of the postgres installation procedure. my laptop runs Win xp on format fat32. I get a message from installation that requires NTFS format. Is there a way of installing postgres on fat 32 without using linux? what do u propose? convert c: /fs:ntfs http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/convertfat.mspx -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Best high availability solution ?
But you have a point that having a contact for postgresql support is a very good idea anyway ! Here ya go: http://www.commandprompt.com/support (DISCLAIMER: I represent the company) Josh, don't you think the better starting point when looking for support would be http://www.postgresql.org/support/professional_support ? :-) There are support companies in France and other parts of Europe which might be a better idea for a company in France. After all I think a situation like this might need some on-site availability. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] Best high availability solution ?
Michael Meskes a écrit : Josh, don't you think the better starting point when looking for support would be http://www.postgresql.org/support/professional_support ? :-) There are support companies in France and other parts of Europe which might be a better idea for a company in France. After all I think a situation like this might need some on-site availability. Indeed ! I see that Pervasive has offices in Belgium, and that is very good to know ! -- Arnaud ---(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] postgres in windows
On 1 Jun 2006 at 16:28, Antonios Katsikadamos wrote: First of all is there an installer for postgres for windows? You'll find the installer for the Windows version of PostgreSQL here: http://www.postgresql.org/ftp/binary/v8.1.4/win32/ Note that it will run only on 32-bit Windows, so Win98, ME etc. are out. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Is there a database file system?
Is there a database file system? I have a project that needs very advanced permission control like that offered by Novell NDS. I am wanting to know if anyone knows of a (PHP | Perl | Samba | SQL ) (object | plug-in | module ) that would allow me to serve files from a connection that is controlled by permissions stored in the database? I really need it for Samba shares and would very much like some WSI that would allow me to control the permission via web browser. I know there are projects on the Internet that try to do things like this but they don't seem to measure up. If you have any recommendations for this please give URL's. Royce Souther www.SiliconTao.com Let Open Source help your business move beyond. For security this message is digitally authenticated by GnuPG. signature.asc Description: This is a digitally signed message part
[GENERAL] PostgreSQL authentication as my application's authentication.
Hello,I'm developing a web application, I normally write the authentication using a database table for usernames and passwords, I would like to implement this app using postgresql's authentication, the user types his user/pass and that's the user/pass used for database connectivity, so each user are guaranteed to only be able to read or write data on tables they are allowed instead using a single database user with read/write access to everything and checking before performing an action to see if the user is allowed. I own the server on which this would run, so I'm ok with creating the users and setting up the privileges using the psql prompt, but I do not know this isn't a good idea in servers where I don't have a superuser, because a user with a create user role becomes a superuser and has read/write access to the entire server's database. Can anybody tell me more about this, what should I do? which is the best aproach? Do I need to provide more information?Thanks in advance.Sergio Duran.
Re: [GENERAL] SCSI disk: still the way to go?
On 5/31/06, Chris Browne [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Merlin Moncure) writes: Xyratex From their web site, they sound like they'll be as challenging to get straight answers from as any of the other disk array vendors :-(. valid concerns. I don't have an answer yet except to say that it is price competitive with attached scsi...much (much) cheaper than the major SAN vendors. Let's put it this way, we were quoted a price about half what a major san vendor charges for their 2gbit fc product with less cache. Also at 16 drives for 3u space its about as dense storage as you can get. They were willing (through their retailer) to set us up with a 30 day trial on the box. results to follow. merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB
Dave Page dpage@vale-housing.co.uk writes: I don't think anyone ever imagined that the bug actually allowed people to use utilities in a way that was useful to them, but unintended by everyone else. Had we done, we would certainly have made the warnings more obvious and considered workarounds. Actually, we thought that this had all been resolved years ago when we made the Unix versions work like that; fixing the Windows code to behave like the Unix versions seemed minor. What I find surprising is that it seems a substantial community of apps is already in existence that have never been run with anything but Windows Postgres. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Problem in Pg 8.1.4 with CREATEDB
I don't think anyone ever imagined that the bug actually allowed people to use utilities in a way that was useful to them, but unintended by everyone else. Had we done, we would certainly have made the warnings more obvious and considered workarounds. Actually, we thought that this had all been resolved years ago when we made the Unix versions work like that; fixing the Windows code to behave like the Unix versions seemed minor. Yeah... What I find surprising is that it seems a substantial community of apps is already in existence that have never been run with anything but Windows Postgres. Doesn't surprise me one bit, really. Also, remember that we're talking win32 *client*, which has been around a long time. psql, for example, has built on win32 since 6.something IIRC. So you could very well have client apps and such that used that behaviour running against a Unix PostgreSQL. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Best open source tool for database design / ERDs?
On 6/1/06 12:29 PM, Tomi NA [EMAIL PROTECTED] wrote: On 6/1/06, Sean Davis [EMAIL PROTECTED] wrote: You might look into Eclipse (the java-based IDE). It has at least one ERD design plugin that allows graphical layout, editing of schema, and generation of DDL directly from the schema. It works with many DB platforms and is FREE!!! What's it called? http://www.azzurri.jp/en/software/clay/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] problemas con psql -l
On May 31, 2006, at 3:32 PM, Eliana Providel wrote: cuando intento listar todas las bases de datos existentes con psql -l me sale el siguiente error: ERROR: no existe la relación pg_catalog.pg_user I think you must be using a PostgreSQL server version 8.1 with an older version of psql (e.g. 8.0 or older). Check the psql version from the command line: psql --version and then connect to the database and execute: select version(); John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(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] Problem adding a record with vb6
I have visual basic 6 with sp6 and I'm trying to add data to PG table, but it gives a error msg like this:Current recordset doesn't support updating. This may be a limitation of the provider or of the selected locktype.and I don't know what can it be, this is the code that I use:strSql = "Provider=PostgreSQL; User ID=postgres; Password=mykey; data source=mypc; location=mybd;"conex.Open strSqlstrSql = "select * from prueba limit 1"rsTabla.Open strSql, conex, adOpenKeysetrsTabla.AddNewrsTabla!ced = 1rsTabla!nombre = "prueba"rsTabla.Updatejust when arrive to add method explodesome advice??? __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] SCSI disk: still the way to go?
Maximum througput of a single drive is around 80MB/second, a 300MB/sec interface won't change that.AlexOn 6/1/06, Riccardo Inverni [EMAIL PROTECTED] wrote:Hi Alex, thanks for the answer (thanks to the other guys too!). http://www.cdw.com/shop/products/default.aspx?EDC=912784 SATA - ~$320 Is there a particular reason why you chose a SATA-150 drive? What about SATA-300?Cheers,Riccardo
Re: [GENERAL] Restoring databases from a different installment on Windows
On Wed, May 31, 2006 at 00:26:02 -0700, Berislav Lopac [EMAIL PROTECTED] wrote: Actually, my situation is like this: I had a properly running version of Postgres when my Windows crashed. I Installed a fresh copy on Windows on another disk, and then Postgres on top of it. Then I started the system from the new Windows, stopped the Postgres service and copied the data directory from the old system to the new one, but Postgres service refused to start. So it was not some kind of a backup copy, it was a regular directory of another installation. That's pretty much the same thing. I think that approach should work. Any ideas? What do your logs say? ---(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
