[GENERAL] pg_stat_activity versus ps
I ran a home-grown self continuous stress test tool against my 7.4.2 database. I banged 'ps' (running freebsd) while it was active and witnessed several of the 'back end' postgres processes exeucting queries, commits, inserts, etc(the actual work the processes were doing was listed in the 'ps' output) however, if I checked pg_stat_activity during the same time period, I saw nothing populated in the current_query column...ever. Am I misunderstanding what the pg_stat_activity table is for? I confirmed that the procpid listed in pg_stat_activity was the same as listed in my 'ps' process list Jeff ---(end of broadcast)--- TIP 3: 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] Bad planner results
Greg Stark <[EMAIL PROTECTED]> writes: > I think this is just a bad case of "nested loops are costed wrong". Looks to me like a statistical failure. Why does it think there will be 4000 rows out of that join when there are only 93? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Connection to a PG 8.0 Beta 1 win32 server
Secrétariat wrote: Hello ! I've installed the Beta 1 on Win XP Pro, it seem working correctly. I load a database from Linux 7.4.3 with pgdumpall, it works too. But I can't connect from other PC over the LAN (I modified pg_hba.conf for the hosts). If I write in postgresql.conf : tcpip_socket = true port = 5432 I can't connect from the LAN, NEITHER from the local machine ?! Where I've made a mistake ? Show us your pg_hba.conf, what is the exact error ? Beta 1 Win32 server at 192.168.0.10, W2k client at 192.168.0.11. My pg_hba.conf : # TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD local all all md5 # IPv4-style local connections host all all 127.0.0.1 255.255.255.255 md5 host all all 192.168.0.0 255.255.255.255 md5 # IPv6-style local connections: #host all all ::1/128 md5 If you want allow all the network 192.168.0.0 then your netmask have to be: 255.255.0.0 Regards Gaetano Mendola ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Python and 8.0 beta
Clodoaldo Pinto Neto wrote: >>Are there any python drivers that work with the version 8 beta? >> >>The version seven ones didn't. > > > This script is working with version 7.4.2, FC2, python 2.3.3 > > [SNIP] May you test the following script and let me know which error you are encountering: #!/usr/bin/python import pgdb if ( __name__ == "__main__") : connection = pgdb.connect( user = 'user', password = 'password', host = '127.0.0.1', database = 'database') if ( connection == None): raise "Could Not Connect" cursor = connection.cursor() cursor.execute ( 'select version()' ) result = cursor.fetchall() cursor.close() while ( type(result) is type([]) ): result = result[ 0 ] print result this is working correctly on my 8.0beta1 installation Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pg_dump in stand alone backend
Ulrich Wisser <[EMAIL PROTECTED]> writes: > I need to to disconnect any other users and do "vacuum full verbose > analyze" "reindex database" and reindex all tables. And for these I will > stop the postmaster and run a stand alone backend. I think the real problem here is stone-age maintenance procedures ;-) You shouldn't need to do vacuum full on a regular basis, and you shouldn't need to do reindexing on a regular basis either. Update to 7.4, if you aren't using it already, and replace these procedures by plain vacuums run often enough to keep the DB from bloating (a look at your FSM parameters would be advisable too). > I figured that doing a nightly backup would be a good idea and running > it in stand alone mode will speed up the process drastically. No it won't. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming
--- Tom Lane <[EMAIL PROTECTED]> wrote: > Ah-hah. The win32 hackers should confirm this, but > my recollection is > that sync/fsync are no-ops under Cygwin (one of the > several reasons > we would never recommend that port for production > use). So this would > fit the assumption that the 7.4 code was simply not > syncing. > Sounds reasonable. However, I don't see the same performance hit while doing bulk database operations (ie: inserts, deletes, updates). Is that expected behavior? Do vacuum operations fsync()/_commit() more often? Regards, Shelby Cain __ Do you Yahoo!? New and Improved Yahoo! Mail - 100MB free storage! http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Not able to build libpq for Windows using 8.0.0 beta1
Hi, I'm not able to compile libpq for Windows environment using the 8.0.0 beta 1 source tree. I got the following errors (The entire compilation output is listed below): C:\temp\postgresql-8.0.0beta1\src>nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cd include if not exist pg_config.h copy pg_config.h.win32 pg_config.h cd .. cd interfaces\libpq nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. Building the Win32 static library... cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nma03256. getaddrinfo.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmb03256. pgstrcasecmp.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmc03256. thread.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmd03256. inet_aton.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nme03256. crypt.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmf03256. noblock.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmg03256. dllist.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmh03256. md5.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmi03256. ip.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmj03256. wchar.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmk03256. encnames.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nml03256. win32.c fe-auth.c fe-protocol2.c fe-protocol3.c fe-connect.c fe-exec.c fe-lobj.c fe-misc.c fe-print.c fe-secure.c pqexpbuffer.c pthread-win32.c link.exe -lib @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmm03256. cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmn03256. libpqdll.c rc.exe /l 0x409 /fo".\Release\libpq.res" libpq.rc link.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmo03256. Creating library .\Release\libpqdll.lib and object .\Release\libpqdll.exp cd ..\..\bin\psql nmake /f win32.mak Microsoft (R) Program Maintenance Utility Version 6.00.8168.0 Copyright (C) Microsoft Corp 1988-1998. All rights reserved. cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nma04004. sprompt.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmb04004. getopt.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmc04004. getopt_long.c cl.exe @C:\DOCUME~1\vguntaka\LOCALS~1\Temp\nmd04004. path.c ..\..\port\path.c(148) : error C2065: 'PGBINDIR' : undeclared identifier ..\..\port\path.c(148) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(148) : warning C4024: 'relative_path' : different types for formal and actual parameter 1 ..\..\port\path.c(148) : error C2065: 'PGSHAREDIR' : undeclared identifier ..\..\port\path.c(148) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(148) : warning C4024: 'relative_path' : different types for formal and actual parameter 2 ..\..\port\path.c(151) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(151) : warning C4024: 'strncpy' : different types for formal and actual parameter 2 ..\..\port\path.c(165) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(165) : warning C4024: 'relative_path' : different types for formal and actual parameter 1 ..\..\port\path.c(182) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(182) : warning C4024: 'relative_path' : different types for formal and actual parameter 1 ..\..\port\path.c(182) : error C2065: 'INCLUDEDIR' : undeclared identifier ..\..\port\path.c(182) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(182) : warning C4024: 'relative_path' : different types for formal and actual parameter 2 ..\..\port\path.c(185) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(185) : warning C4024: 'strncpy' : different types for formal and actual parameter 2 ..\..\port\path.c(199) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(199) : warning C4024: 'relative_path' : different types for formal and actual parameter 1 ..\..\port\path.c(199) : error C2065: 'PKGINCLUDEDIR' : undeclared identifier ..\..\port\path.c(199) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(199) : warning C4024: 'relative_path' : different types for formal and actual parameter 2 ..\..\port\path.c(202) : warning C4047: 'function' : 'const char *' differs in levels of indirection from 'int ' ..\..\port\path.c(202) : warning C4024: 'strncpy' : different types for formal and actual parameter 2 ..\..\port\path.c(216) : w
Re: [GENERAL] Dump and Restore
On 24/08/2004 14:40 Eduardo S. Fontanetti wrote: How can I do a test if my dumping is really working, I can't apply the dump in my database, because it will overwrite a lot of data. I was thinking about restore in another database name, but I can't, it always restore on the original database. Somebody have a cooking recipe about to test if my backup is working fine, and will help if I need sometime? Are you using pg_dump or pg_dumpall? I just use pg_dump on individual databases (pg_dumpall works on the whole cluster) and can restore that to any database. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] select ... where ='' does a seqscan [auf Viren geprueft]
Silvio Matthes <[EMAIL PROTECTED]> writes:
> I try to select the rows of a table where the content of a varchar-column
> is empty ('') and PostgresQL is doing a seqscan.
If there are a very large number of rows with param_value='', it's
entirely possible that using an index to find them is counterproductive.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] [GENERAL] Dump and Restore
Assuming you're using Unix Box 1. Dump your database DB_X pg_dump -v -U postgresUsername DB_X | gzip -f - > DB_X.gz 2.Create database DB_Y psql -U postgresUsername template1 CREATE DATABASE DB_Y \q 3. zcat DB_X.gz | psql -U postgresUsername -f - DB_Y Thilina -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Paul Thomas Sent: 24 August 2004 16:30 To: Eduardo S . Fontanetti Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] [GENERAL] Dump and Restore On 24/08/2004 14:40 Eduardo S. Fontanetti wrote: > How can I do a test if my dumping is really working, I > can't apply the dump in my database, because it will > overwrite a lot of data. I was thinking about restore > in another database name, but I can't, it always > restore on the original database. > > Somebody have a cooking recipe about to test if my > backup is working fine, and will help if I need > sometime? Are you using pg_dump or pg_dumpall? I just use pg_dump on individual databases (pg_dumpall works on the whole cluster) and can restore that to any database. HTH -- Paul Thomas +--+ -+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+ -+ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Invalid input for integer on VIEW
On Tue, 2004-08-24 at 15:42 +0100, mike wrote: > I have the following view definition > > Column | Type | Modifiers > +---+--- > bcode | character varying(15) | > subhead| text | > sc_description | character varying(60) | > Budget | numeric | > expenditure| numeric | > balance| numeric | > head | integer | > period | integer | > View definition: > SELECT > CASE > WHEN vw_rec_sum.code IS NULL AND vw_pay_sum.sum IS NOT NULL > THEN vw_pay_sum.code > ELSE vw_rec_sum.code > END AS bcode, > CASE > WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 'Salary > Costs'::text > WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 'Startup > Costs'::text > WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 'Running > Costs'::text > WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 'Training > Costs'::text > ELSE NULL::text > END AS subhead, sc_description, vw_rec_sum.sum AS "Budget", > vw_pay_sum.sum AS expenditure, > CASE > WHEN vw_pay_sum.sum IS NULL THEN vw_rec_sum.sum > WHEN vw_pay_sum.sum < 0.01 THEN 0.00 - vw_pay_sum.sum + > vw_rec_sum.sum > ELSE vw_rec_sum.sum - vw_pay_sum.sum > END AS balance, > CASE > WHEN "left"(ac_code::text, 2) = 'SA'::text THEN 1 > WHEN "left"(ac_code::text, 2) = 'SC'::text THEN 2 > WHEN "left"(ac_code::text, 2) = 'RC'::text THEN 3 > WHEN "left"(ac_code::text, 2) = 'TC'::text THEN 4 > ELSE NULL::integer > END AS head, > CASE > WHEN to_number(vw_rec_sum.code::text, '999'::text) > > 194::numeric THEN 3 > WHEN to_number(vw_rec_sum.code::text, '999'::text) < > 195::numeric AND to_number(vw_rec_sum.code::text, '999'::text) > > 50::numeric THEN 2 > ELSE 1 > END AS period >FROM vw_rec_sum >FULL JOIN vw_pay_sum ON vw_rec_sum.code::text = vw_pay_sum.code::text >JOIN vw_ac ON vw_rec_sum.code::text = vw_ac.id::text > ORDER BY to_number(vw_rec_sum.code::text, '999'::text); > > > However whenever I try to query it with criteria on the period column I > get SELECT * FROM vw_budget HAVING "period"='3'; > ERROR: invalid input syntax for type numeric: " " > > If I try on the head column the query runs > > Getting stumped - anyone any idea what is going on here. > > This is with 7.4.3 > If I do the same query, except to create a new table, everything works, so is this a view bug? > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Is it possible...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I wasn't able to get a usable answer by googling: Is there a way to create a view on a table that converts rows to columns ? Example: I have a table create table blah ( id int4 serial, parentid int4, pname varchar(64), pvalue varchar(128) ); which is basically a classic key/value pair. Parentid is a field defining that certain rows belong to the same entity, i.e.: 1 1 'firstkey' 'firstvalue' 2 1 'secondkey' 'secondvalue' ... ... 10 2 'firstkey' 'firstvalue' ... All the "parents" have the same set of keys - guaranteed. I'd need to convert this into a table which has the keynames as columns and one row per "parentid", i.e.: parentid firstkey secondkey 1firstvalue secondvalue 2firstvalue secondvalue I hate the idea of writing code that really creates and fills a table, particularly since the original table has a lot of rows. Is the above doable with a view? Any pointers will be appreciated. Thanks UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBK2MfjqGXBvRToM4RAt/dAKCcBaDVMvfP9fIJDy6qFh8euRfnJwCfY8HH hqjmfHNjzTjZ71jdhjv4cIU= =TfAU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] No connection to a PG 8.0 Beta 1 win32 server
Secrétariat wrote: I don't have a telnet server on the Win XP Pro PC acting as PG server for Beta1 ! So ? If you do: telnet 5432 the command only open a TCP connection to the port 5432, this will test if you are able to reach your server. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Is it possible...
Uwe C. Schroeder wrote: parentid firstkey secondkey 1firstvalue secondvalue 2firstvalue secondvalue I hate the idea of writing code that really creates and fills a table, particularly since the original table has a lot of rows. Is the above doable with a view? Any pointers will be appreciated. You can do what you want with set returning functions in contrib/tablefunc. See examples towards the end of my recent OSCON presentation: http://www.joeconway.com/pres_oscon_2004-r1.pdf and the source sql: http://www.joeconway.com/flex.sql HTH, Joe ---(end of broadcast)--- TIP 3: 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] Is it possible...
Uwe C. Schroeder wrote: I wasn't able to get a usable answer by googling: Is there a way to create a view on a table that converts rows to columns ? [snip] Is the above doable with a view? Any pointers will be appreciated. Search the postgresql-sql list archives for "Arbitrary Cross-tab" for recent discussion on this. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgresql 8.0 beta 1 - strange cpu usage statistics and slow vacuuming
Shelby Cain <[EMAIL PROTECTED]> writes: > Sounds reasonable. However, I don't see the same > performance hit while doing bulk database operations > (ie: inserts, deletes, updates). Is that expected > behavior? Do vacuum operations fsync()/_commit() more > often? IIRC, vacuum fsyncs once per table (possibly more depending on whether you add FULL, ANALYZE, etc). Not clear what case you are considering exactly. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] UTF-8 and LIKE vs =
David Wheeler wrote: > But given what you've said, Tatsuo, it makes me wonder if it's worth > it to use the system locale default when running initdb? Yes, because that is the locale that the user prefers. If a locale is broken then you shouldn't set it as system locale in the first place. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] No connection to a PG 8.0 Beta 1 win32 server
Richard Huxton <[EMAIL PROTECTED]> writes: > Secrétariat wrote: >> I already receive 8 times this message, not responding to my question ! >> The question is : >> Why when I enable "tcpip_socket = true" in the postgresql.conf it becomes >> impossible to restart the service ?! (so hosts conections are impossible). > If PostgreSQL won't start with tcpip_socket=true, but can otherwise then > that suggests it can't open the socket. No, it's easier than that: there is no "tcpip_socket" parameter anymore, so the postmaster is rejecting an invalid configuration file entry. (Looking at the postmaster's log would have told you this btw) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Dump and Restore
Eduardo S. Fontanetti wrote: How can I do a test if my dumping is really working, I can't apply the dump in my database, because it will overwrite a lot of data. I was thinking about restore in another database name, but I can't, it always restore on the original database. Somebody have a cooking recipe about to test if my backup is working fine, and will help if I need sometime? OK here's a recipe: If you're using pg_dump as in: pg_dump dbName > fileName create your new database: createdb myNewDB (don't forget your encoding) and then restore from the dump: psql -d myNewDB -f fileName Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Invalid input for integer on VIEW
mike <[EMAIL PROTECTED]> writes: > If I do the same query, except to create a new table, everything works, > so is this a view bug? Possibly, but you haven't given enough info to let someone else reproduce the problem. A SQL script that creates all the necessary tables and the view and then triggers the failure would make it much easier for us to investigate. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Postgresql 8.0 beta 1 - strange cpu usage statistics and slow
Shelby Cain wrote: --- Tom Lane <[EMAIL PROTECTED]> wrote: Ah-hah. The win32 hackers should confirm this, but my recollection is that sync/fsync are no-ops under Cygwin (one of the several reasons we would never recommend that port for production use). So this would fit the assumption that the 7.4 code was simply not syncing. Sounds reasonable. However, I don't see the same performance hit while doing bulk database operations (ie: inserts, deletes, updates). Is that expected behavior? Do vacuum operations fsync()/_commit() more often? I think you have other problems around, see the post where I did some tests. Regards Gaetano Mendola ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] select ... where ='' does a seqscan [auf Viren
Hello Tom,
>> I try to select the rows of a table where
the content of a varchar-column
>> is empty ('') and PostgresQL is doing a seqscan.
>If there are a very large number of rows with param_value='', it's
>entirely possible that using an index to find them is counterproductive.
That's right. I did some research on my database,
that's what I found.
select count(*) from document_params;
count
-
7302418
select param_name,count(param_name) from document_params
where param_value='' group by param_name;
param_name | count
+-
READ_DATE | 1064944
ENTAX_NR | 85853
KONTO_NR | 6672
KUNDEN_NR | 7
So it's ok not using an index for
select count(*) from document_params where param_value='';
But
explain select count(*) from document_params where
param_name='KUNDEN_NR' and param_value='';
also did a seqscan.
And
explain select count(*) from document_params where
param_name='KONTO_NR' and param_value='test';
did an indexscan!
So at the moment it seems to me, that the multicolumn
index is not working in the expected way.
The ''-value is not the problem. The problem is that
there are a million rows with ''-value.
But in my opinion with the multicolumn index in mind
the server should do a index scan, because there are only 7 rows with param_name='KUNDEN_NR'
and param_value=''?!?
Disabling seqscan gives:
explain select count(*) from document_params where
param_name='KUNDEN_NR' and param_value='';
QUERY PLAN
-
Aggregate (cost=639336.57..639336.57 rows=1
width=0)
-> Index Scan using dp_idx_6
on document_params (cost=0.00..638934.84 rows=160694 width=0)
Index Cond: (((param_value)::text
= ''::text) AND ((param_name)::text = 'KUNDEN_NR'::text))
Postmaster is using the multicolumn index (param_value,param_name),
but not in the expected time...
as always, any help would be highly appreciated.
Ciao,
Silvio Matthes
Tom Lane <[EMAIL PROTECTED]>
Gesendet von: [EMAIL PROTECTED]
24.08.2004 17:35
An
Silvio Matthes <[EMAIL PROTECTED]>
Kopie
[EMAIL PROTECTED]
Thema
Re: [GENERAL] select ...
where ='' does a seqscan [auf Viren geprueft]
Silvio Matthes <[EMAIL PROTECTED]> writes:
> I try to select the rows of a table where the content of a varchar-column
> is empty ('') and PostgresQL is doing a seqscan.
If there are a very large number of rows with param_value='', it's
entirely possible that using an index to find them is counterproductive.
regards, tom lane
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
WG: Re: Re: [GENERAL] select ... where ='' does a seqscan [auf Viren geprueft]
>Am Dienstag, 24. August 2004 11:59 schrieb Silvio Matthes: >> So using the index does need more time than a sequential scan? >It's possible. If you want to prove the opposite, please post the output of >EXPLAIN ANALYZE in both cases. On my system, with PostgresQL 8.0Beta1, I could prove the opposite, but that's not the point for me. Both cases are too slow: set enable_seqscan=true explain analyze select count(*) from document_params where param_name='KONTO_NR' and param_value=''; QUERY PLAN Aggregate (cost=241980.45..241980.45 rows=1 width=0) (actual time=47766.000..47766.000 rows=1 loops=1) -> Seq Scan on document_params (cost=0.00..241600.27 rows=152073 width=0) (actual time=0.000..47719.000 rows=6672 loops=1) Filter: (((param_name)::text = 'KONTO_NR'::text) AND ((param_value)::text = ''::text)) Total runtime: 47938.000 ms (4 rows) set enable_seqscan=false explain analyze select count(*) from document_params where param_name='KONTO_NR' and param_value=''; QUERY PLAN Aggregate (cost=592164.03..592164.03 rows=1 width=0) (actual time=31828.000..31828.000 rows=1 loops=1) -> Index Scan using idx_empty on document_params (cost=0.00..591783.84 rows=152073 width=0) (actual time=140.000..31782.000 rows=6672 loops=1) Index Cond: ((param_name)::text = 'KONTO_NR'::text) Filter: ((param_value)::text = ''::text) Total runtime: 31828.000 ms (5 rows) In other tests the server cached the responses so I cannot compare the results, in other cases the server did need some 20s for an index-scan, probably due to too low configured memory. This brings me to the following questions: 1.) What can I do to avoid seqscans if I want rows with empty param_value? 2.) Would it be wiser to change all the empty values to some placeholder (e.g. 'empty')? From the tests made up to now, this would be the fastest solution, but wouldn't be very nice in concern of readability... a bit offtopic, but related for me: 3.) how can I prevent the server from caching or empty the cache under win32 (stopping and re-starting the service doesn't help) for comparing similar statements? 4.) how can I change the memory settings for the postmaster (v8.0beta) in win32? As always, any help would be highly appreciated, Ciao, Silvio Matthes
Re: [GENERAL] Dump and Restore
I am using pg_dump. It means that I can't restore to a different name database?? Eduardo S. Fontanetti --- Paul Thomas <[EMAIL PROTECTED]> escreveu: > > On 24/08/2004 14:40 Eduardo S. Fontanetti wrote: > > How can I do a test if my dumping is really > working, I > > can't apply the dump in my database, because it > will > > overwrite a lot of data. I was thinking about > restore > > in another database name, but I can't, it always > > restore on the original database. > > > > Somebody have a cooking recipe about to test if my > > backup is working fine, and will help if I need > > sometime? > > Are you using pg_dump or pg_dumpall? I just use > pg_dump on individual > databases (pg_dumpall works on the whole cluster) > and can restore that to > any database. > > > HTH > > -- > Paul Thomas > +--+-+ > | Thomas Micro Systems Limited | Software Solutions > for > Business | > | Computer Consultants | > http://www.thomas-micro-systems-ltd.co.uk | > +--+-+ > ___ Yahoo! Acesso Grátis - navegue de graça com conexão de qualidade! http://br.acesso.yahoo.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_stat_activity versus ps
Jeff Amiel <[EMAIL PROTECTED]> writes: > however, if I checked pg_stat_activity during the same time period, I > saw nothing populated in the current_query column...ever. Did you have it turned on? (stats_command_string config parameter) Were you checking as superuser? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] select ... where ='' does a seqscan [auf Viren
Silvio Matthes <[EMAIL PROTECTED]> writes: > But in my opinion with the multicolumn index in mind the server should do > a index scan, because there are only 7 rows with param_name='KUNDEN_NR' > and param_value=''?!? We do not have any cross-column statistics at the moment, so the planner is unable to recognize the potential win here. Note the poor estimate of the number of matching rows in your EXPLAIN result. I think though that it might help to put param_name first in the multicolumn index. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: WG: Re: Re: [GENERAL] select ... where ='' does a seqscan [auf Viren geprueft]
Silvio Matthes <[EMAIL PROTECTED]> writes: > 2.) Would it be wiser to change all the empty values to some placeholder > (e.g. 'empty')? You're missing the point entirely. '' is being treated specially because the planner can see from the column statistics that it occurs a lot. Substituting a different value would simply move the problem over to that value (as soon as you'd done ANALYZE, anyway). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pg_stat_activity versus ps
That was it. (not having it turned on. duh). Guess I should have read section 23.2 of the docs..."The Statistics Collector" Thanks for the heads up. Jeff Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: however, if I checked pg_stat_activity during the same time period, I saw nothing populated in the current_query column...ever. Did you have it turned on? (stats_command_string config parameter) Were you checking as superuser? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] SELECT to an external file
Hi, How can i draw the results of a SELECT in to a file in the filesystem ? Warm Regards, Mário Gamito ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Dump and Restore
On Tue, 2004-08-24 at 17:36, Eduardo S. Fontanetti wrote: > I am using pg_dump. > > It means that I can't restore to a different name > database?? If you use pg_dump[all] without other options it will dump to a text file. Just edit the database name. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "I saw in the night visions, and, behold, one like the Son of man came with the clouds of heaven, and came to the Ancient of days, and they brought him near before him. And there was given him dominion, and glory, and a kingdom, that all people, nations, and languages, should serve him; his dominion is an everlasting dominion, which shall not pass away, and his kingdom that which shall not be destroyed." Daniel 7:13,14 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SELECT to an external file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 24 Aug 2004, [ISO-8859-1] Mário Gamito wrote: > How can i draw the results of a SELECT in to a file in the filesystem ? Use \o: test=# \o testfile test=# SELECT * from mytable; test=# All the output is redirected to testfile. Regards, - -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBK3cJtl86P3SPfQ4RAqXlAJ4rpEmebsqRM5f6eWLX6DKMdlFZ8QCgvzHk bSMZ2kr9s9brM6U6Bn27jJs= =CREY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] UTF-8 and LIKE vs =
On Aug 23, 2004, at 10:25 PM, Joel wrote: If the locale machinery iw functioning correctly (and if I understand correctly), there ought to be a setting that would allow those to collate to the same point. Bleh. There must be some distinction between them. It sounds like querying for synonyms. I'm not sure what the settings would be, or if it's fully funtional yet. Maybe Tatsuo would know? (Hope he doesn't mind me CC-ing him.) So, what was the original language and text of the queries you started this thread with? Korean, but the database I was using has data in 10 different languages in it, making any locale-specific collation useless. Regards, David smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] SELECT to an external file
On Tue, 2004-08-24 at 18:08, Mário Gamito wrote: > Hi, > > How can i draw the results of a SELECT in to a file in the filesystem ? Using psql: 1. \o /path/to/file SELECT ... ; \o 2. psql -d my_database -c "SELECT ... " >/path/to/file -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "I saw in the night visions, and, behold, one like the Son of man came with the clouds of heaven, and came to the Ancient of days, and they brought him near before him. And there was given him dominion, and glory, and a kingdom, that all people, nations, and languages, should serve him; his dominion is an everlasting dominion, which shall not pass away, and his kingdom that which shall not be destroyed." Daniel 7:13,14 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] UTF-8 and LIKE vs =
On Aug 24, 2004, at 12:20 PM, Peter Eisentraut wrote: broken, and that they're useless for multilingual use. I don't agree with that, but perhaps we differ in our interpretation of "multilingual use". If you have special requirements, you can always turn the locales off. Well, we're getting beyond my realm of knowledge here, so I'll leave it to Tatsuo to articulate better than I can. In the meantime, turning locales off is exactly what I will do from here on in. Regards, David smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Thomas Hallgren <[EMAIL PROTECTED]> writes: > ... My suggestion is not that you take on more work but > rather that the comittee is allowed to grow and take on responsabilities > and people beyond the developers of the core database. Enlarging the core committee by the amount you seem to be thinking of would transform it into something quite different than it is now (in particular it would be too large to make decisions effectively, IMHO). And I don't see that it actually accomplishes anything. The real issue here is finding someone to do the work --- it's notable that this thread has been going on for some time now and no one has actually stepped up and volunteered to *do* anything. I think if there were someone out there willing to do it, they could and would do it, with or without core's blessing. regards, tom lane ---(end of broadcast)--- TIP 3: 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 beta 8 on windows starts and stops automatically.
That's right..
I have deleted postmaster.pid and all was ok now..
Thanks..
On Mon, 23 Aug 2004 10:35:00 +0200, [EMAIL PROTECTED] ("Magnus
Hagander") wrote:
>> Hello all,
>>
>> I am testing PostgreSQL 8.0 beta on a windows xp
>> professional. In the time when I did the install I have been
>> working with pgadmin and so on...
>>
>> But after a few days, when I tried to continue with my tests.
>> I cannot start the postgresql service. I always get this
>> message windown when I try to start the service:
>>
>> "The postgresql service on the local computer started and
>> stopped. some services stop automatically if they have no
>> work to do, for example the performance logs and alerts services"
>>
>> The service starts and stops automatically and I cannot to
>> work with pgsql.
>>
>> PS: I have translated (get from internet) the text message
>> window because I have my system in spanish language.
>
>This looks a lot like the postmaster.pid issue. Look in your DATA
>directory, and remove that file if it exists.
>
>If not, then it's a different issue. In this case, edit the
>postgresql.conf file and set log_destination to include eventlog, then
>look at eventlog output. (This is an issue with the installer that will
>be fixed in the next beta release)
>
>//Magnus
>
>---(end of broadcast)---
>TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
On Mon, 23 Aug 2004, Thomas Hallgren wrote: Tom Lane wrote: Enlarging the core committee by the amount you seem to be thinking of would transform it into something quite different than it is now (in particular it would be too large to make decisions effectively, IMHO). I can relate to that. Lean and mean is good. So pehaps the core committee should not be enlarged. Instead, another committee should be established for this purpose that work closely together with the core committee (perhaps cross staffed to some extent). You are almost there ... now, go that one extra step and you have "a side project whose goal is to take the core server and bundle it with 3rd party apps to provide a complete RDBMS solution" ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] UTF-8 and LIKE vs =
David Wheeler <[EMAIL PROTECTED]> writes: >>> Hmm. I tried putting your string into a UNICODE database and I got >>> ERROR: invalid byte sequence for encoding "UNICODE": 0xc7 >> >> Really? Curious. > Oh, are you sure that you got my UTF-8 data? Because it came back in > your reply all mangled. I deliberately left it half-MIME-ified so that the hex byte values would be visible. One of the variables we have not controlled for is whether what you posted is the same as what other people received --- Tim Allen's point about the claimed encoding of the message is very relevant here. Lots of mail software will try to translate encodings. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
On Mon, 23 Aug 2004, Thomas Hallgren wrote: In times when people download gigabytes of film and music using BitTorrent, I think that's the least of our problems. But of course, the distribution should be kept at a reasonable size. That's why I'd like a better solution to replace the inferior one and to limit the number of overlaps. At which time we are once more 'playing favorites' ... Sorry, but that one got right pass me. 'playing favorites'? your comment about 'limiting the number of overlaps' ... to do that, the one maintaining this package would have to decide that 'PL/J' is better then 'PL/Java' (or vice versa) ... if maintainer doesn't know/use Java, then, what, each group of developer then 'lobbies' the maintainer for inclusion? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Tom Lane wrote: Enlarging the core committee by the amount you seem to be thinking of would transform it into something quite different than it is now (in particular it would be too large to make decisions effectively, IMHO). I can relate to that. Lean and mean is good. So pehaps the core committee should not be enlarged. Instead, another committee should be established for this purpose that work closely together with the core committee (perhaps cross staffed to some extent). The real issue here is finding someone to do the work --- it's notable that this thread has been going on for some time now and no one has actually stepped up and volunteered to *do* anything. I think if there were someone out there willing to do it, they could and would do it, with or without core's blessing. Given a financial scenario that would make it possible, I'd love to help with this. My current employment however has no connection to PostgreSQL. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] problem with postgresql-dump while upgrading to 7.4
On Fri, 2004-08-20 at 16:46, Oleg wrote: Dear All, I have upgraded Postgresql from 7.3 to 7.4. Starting pg brings error: The database is in an older format that cannot be read by version 7.4 of PostgreSQL dpkg-upgrade postgresql fails I tried postgresql-dump. While dumping it brings the following error: FATAL: unsupported frontend protocol However the dump file is generated. Does this error means that dump file can not be used or it can be ignored? You seem to be using Debian. The whole process of upgrading between major versions is fraught with problems, and 7.3 -> 7.4 is not well tested, since 7.3 has never been part of Debian stable. In /var/lib/postgres (or wherever else you may have directed that the database be kept) there should be directories data, dumpall* and preserve and (I think) a file called db.out. .../preserve should contain the 7.3 database; .../data should contain the new database, but apparnetly doesn't; db.out ought to be the dump as a text file. Take a look at the dump file, if it is there. Does it look complete? Thank you very much for your ahswer. I have created db.out using postgresql-dump -t db.out -dcivlp $PGDATA/../data.save However postgresql-dump quit with message initdb: failed I run initdb manualy and it worked ok ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] postgres replication only some datas
Hello, a question about a tool or a possibility how could something work. following situation: we have a database which is full of very sensitive information and needed that db to use our online website. but now we move the website to a server outside our office and needed to replicate only some datas to the online db. with the tool slony i found out , that some tables could be replicated, but in some tables are some information, which we do not wont to replicate. so we need a tool or an idea how to do that. I though about a dump and deleting the sensitive datas, but the database is about a half gig and we need the changed entries directly on the online db within seconds. Anybody how could help? Greetings Matthias ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] No connection to a PG 8.0 Beta 1 win32 server
I see in the log file that parameter "tcpip_socket" was invalid, but how can I connect my W2k client with pgAdmin III to my XP Pro server using Beta1 ? Is the Beta1 à standalone version ? regards, luc. > Tom Lane write : > No, it's easier than that: there is no "tcpip_socket" parameter anymore, > so the postmaster is rejecting an invalid configuration file entry. > (Looking at the postmaster's log would have told you this btw) > > regards, tom lane > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Checking whether postgresql is running
[Possible duplicate: original sent to novice never got through! -;(] Hi all! Testing a script where I need to make sure that postgresql is running before passing a instruction I faced this curious behaviour: This is the relevant content of the script: -- #!/bin/bash /usr/lib/postgresql/bin/pg_ctl status -D /var/lib/postgres/data >/dev/null 2>&1 rtn=$? if [ $rtn -ne 0 ]; then echo "not running" else echo "ok ok" fi Now, if I run the script as root, I get: ok ok (or, commenting the script if condition: pg_ctl: postmaster is running (pid: 18658) Command line was: /usr/lib/postgresql/bin/postmasteir) whereas, if I run the same script as ordinary user, the answer is: not running (or, commenting the if lines: pg_ctl: postmaster or postgres is not running) -- Everything is being tested on the same PC [running PG 7.2.1-2Woody5 under GNU/Linux, k. 2.2.22], root being on /dev/tty1, user on /dev/tty2, and postgresql not being stopped while switching from root to user :-). Could anybody throw some light on this issue? Thanks for your attention, Ennio. -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why to use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (used to say Henry Miller) ] -- [Perche' usare Win$ozz (dico io) se ..."anche uno sciocco sa farlo. \\?// Fa' qualche cosa di cui non sei capace!" (diceva Henry Miller) ] (°|°) [Why to use Win$ozz (I say) if ... "even a fool can do that. )=( Do something you aren't good at!" (used to say Henry Miller) ] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem to connect to the Windows Port
Hi Magnus Thanks. > If you meant to match the entire subnet (192.168.*.*), instead put > host minitest postgres 192.168.0.0 255.255.0.0 password Ok. I changed the line in pg_hba.conf to host all all 192.168.0.0 255.255.0.0 password then rebooted the computer and tried again: C:\>psql -h 192.168.1.8 -U postgres -d minitest psql: could not connect to server: Connection refused Is the server running on host "192.168.1.8" and accepting TCP/IP connections on port 5432? The server on 192.168.1.8 is running, (I can connect on this machine to localhost/minitest.). I can ping 192.168.1.8 Regards Conni ---(end of broadcast)--- TIP 3: 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] No connection to a PG 8.0 Beta 1 win32 server
Secrétariat wrote: I see in the log file that parameter "tcpip_socket" was invalid, but how can I connect my W2k client with pgAdmin III to my XP Pro server using Beta1 ? Is the Beta1 à standalone version ? Nope - AFAIK it's a full version. The documentation for 8.0beta is available online at: http://developer.postgresql.org/docs/postgres/index.html If you look at the following page http://developer.postgresql.org/docs/postgres/runtime-config.html you should see that listen_addresses is the new value and should be set to "*" The documentation should be part of your installed package. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Checking whether postgresql is running
Ennio-Sr wrote: [Possible duplicate: original sent to novice never got through! -;(] Hi all! Testing a script where I need to make sure that postgresql is running before passing a instruction I faced this curious behaviour: This is the relevant content of the script: -- #!/bin/bash /usr/lib/postgresql/bin/pg_ctl status -D /var/lib/postgres/data >/dev/null 2>&1 rtn=$? if [ $rtn -ne 0 ]; then echo "not running" else echo "ok ok" fi Now, if I run the script as root, I get: ok ok (or, commenting the script if condition: pg_ctl: postmaster is running (pid: 18658) Command line was: /usr/lib/postgresql/bin/postmasteir) whereas, if I run the same script as ordinary user, the answer is: not running (or, commenting the if lines: pg_ctl: postmaster or postgres is not running) -- Everything is being tested on the same PC [running PG 7.2.1-2Woody5 under GNU/Linux, k. 2.2.22], root being on /dev/tty1, user on /dev/tty2, and postgresql not being stopped while switching from root to user :-). Could anybody throw some light on this issue? Though I don't read Perl at all, so I haven't the slightest idea about what's in the if, I think I can shed some light on the issue... When you execute pg_ctl -D /var/lib/postgres/data, the command fails -- you can not access the directory /var/lib/postgres/data to go and pickup the file postgres.pid (I think that's the filename) to check if that PID is running. So, without understanding that gibberish inside the if, I bet that it simply is returning some error code that is causing the "not running" part of the if to be executed. You have to be user postgres or superuser to be able to use pg_ctl to verify if postmaster is running. A "loose check" would be executing the command "ps -C postgres" and see if there's any output. Or, simply use the PG client library -- you will get an error message saying that postmaster is not running on the specified port. Carlos -- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
On Tue, 24 Aug 2004, Jan Wieck wrote: I want to get rid of the recommendations-vacuum. I don't care if we don't pick the ultimately best of everything that way. If there is a consensus of people who use these things, repeating their recommendation will seldom be bad advice. Those people have proven already that they can make good decisions, they do use PostgreSQL after all :-) Shouldn't some sort of automated voting system be created first? With the ability to 'add software' and then 'vote on it'? Maybe have some sort of login procedure such that someone could later login and change their vote? ie something better came along ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Unsupported 3rd-party solutions (Was: Few questions
Karsten Hilbert wrote: a) More software can make use of your good name and reputation. That's rather dangerous, don't you think ? If PostgreSQL proper (eg the core server) wants to keep its good name it better make sure it is bundled with "good" "add-ons". And that would require precisely the additional workload that several core people have explained they must avoid in order to be able to focus on the core engine to keep its good reputation. So, unless people outside the core team take up the task it won't happen. And if they do - what's the difference to the current state of affairs ? I see you point and I don't disagree. But let me give you a different view. When I look at the current state of affairs, I see a PostgreSQL name that, in spite of very skilled people, great technology, a very vibrant community, and IMO a golden opportunity, is held back by a fear (well grounded, no doubt) that growth will increase the workload and thus make it harder to maintain the core engine. I'm not an advocate of commercial packaging, nor packaging performed by "outsiders" and I don't think packages bundled with operating systems is a great idea either. I'm a fan of free open source and I think the way to archive growth is to appeal to more sponsors. More sponsors equals more resources. More resources, if handled correctly, could both make PostgreSQL grow *and* lessen the workload for the core developers. I really like Jan Wiecks suggestion. To me that sounds like the way to get started. Recommended add-ons will feel a lot "safer" to the end users and it will give some perception of growth. I'd like to help out and I'm perhaps able to put in a couple of hours a week. Maybe there's more people out there that would like to help? If not for any other cause, then maybe to promote their own product. In so, we need to be coordinated. Preferably by someone from "within". Regards, Thomas Hallgren ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] How is this possible? (more on deadlocks)
Ok, now I'm really intrigued by what looks to me (possibly from a naive point of view) like a bug, or rather, a limitation on the implementation. I can't find a reasonable justification why the following would cause a deadlock: I run two instances of psql using the same DB on the same machine. On one of them, I run: create table A (id int primary key); create table B (id int primary key); create table AB ( A_id int references A(id), B_id int references B(id) ); Then I add a few records (all this from the same instance of psql): insert into A values (1); insert into A values (2); insert into B values (10); insert into B values (11); Ok, now, I try two concurrent transactions, by executing commands alternating from one psql instance to the other one: I'll prefix each line with 1: or 2: indicating which console I execute it on -- the commands were executing in the time sequence corresponding to the lines below: 1: begin; 2: begin; 1: insert into AB values (1,10); 2: insert into AB values (2,10); 1: insert into AB values (2,11); At this point, console 1 blocks for a second or two, and then I get an error, reporting that a deadlock was detected; then, console 2 unblocks. I can't see how it is justified that the above causes a deadlock. I do understand how the deadlock is happening: trans. 1 puts a lock on rows 1 of A and row 10 of B -- meaning, "nobody touches these rows until I'm finished"; then trans 2. locks row 2 of A, but is put on hold waiting to lock row 10 of B, since there is already a lock on it. When trans. A now tries to put a lock on row 2 of A, the deadlock happens. The thing is, why? Why is this a deadlock? When we look at the low-level details, sure; but when you look at the nature of what's happening at a conceptual level, a deadlock is not justified, IMHO: Trans. 1 doesn't really need to put a mutex type of lock around row 1 of A -- it simply needs to atomically flag the order: "nobody delete or modify this row of table A"... Another trans. that attempts to place the same order should not block -- it should succeed and return immediately and continue with the transaction; there is no conflict in the above example -- the first transaction does not want to allow anyone to mess with row 1 of A; the other transaction wants exactly the same, so it seems to me that the lock is more restrictive than it needs to be. I don't know about the internals of how transactions and locks and FK constraints are handled, but I'm analyzing it and describing what seems to be happening internally, based on the behaviour I observe. Any comments? Carlos -- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Is this legal SQL? Is it a good practice?
I just noticed that from a C or C++ program using
libpq or libpq++, I can send *one* command that
contains several SQL statements separated by
semicolon. Something like:
PgDatabase db (" ");
const char * const sql =
"insert into blah (...); insert into blah (...)";
if (db.Exec (sql) == PGRES_COMMAND_OK)
{
cout << "Fine!" << endl;
}
And I verify the table, and all the inserts took place
(and of course, the program outputs "Fine!").
But I'm wondering -- is this a PostgreSQL extension,
or is it "legal SQL"? In particular, I'm wondering
if it is a feature that in the future you might
decide to eliminate for not being ANSI-SQL compliant.
What happens if the first command is ok but the second
one fails? I guess PgDatabase::Exec would return an
error code, and PgDatabase::ErrorMessage would return
the error message corresponding to the second statement
(the one that failed). Am I correct in thinking this?
Any reason why this should be avoided? (on the plus
side, I think this might increase efficiency for
transactions where one executes several insert or
update statements).
Thanks for any comments,
Carlos
--
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem to connect to the Windows Port
"Cornelia Boenigk" <[EMAIL PROTECTED]> writes: > C:\>psql -h 192.168.1.8 -U postgres -d minitest > psql: could not connect to server: Connection refused > Is the server running on host "192.168.1.8" and accepting > TCP/IP connections on port 5432? "Connection refused" suggests that you've got a firewall-type problem, ie the operating system is rejecting the connection rather than letting the postmaster receive it. Check packet filtering rules... regards, tom lane ---(end of broadcast)--- TIP 3: 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] Is this legal SQL? Is it a good practice?
Carlos Moreno wrote: > I just noticed that from a C or C++ program using > libpq or libpq++, I can send *one* command that > contains several SQL statements separated by > semicolon. > But I'm wondering -- is this a PostgreSQL extension, > or is it "legal SQL"? The whole libpq API is made up out of thin air, so it's not conforming to any public standard. > In particular, I'm wondering > if it is a feature that in the future you might > decide to eliminate for not being ANSI-SQL compliant. Because of the above, that cannot be a reason for eliminating any interfaces. > What happens if the first command is ok but the second > one fails? All the commands are run in one transaction, so if one fails, the whole sequence is rolled back. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Problem to connect to the Windows Port
Tom Lane <[EMAIL PROTECTED]> writes: > "Cornelia Boenigk" <[EMAIL PROTECTED]> writes: >> C:\>psql -h 192.168.1.8 -U postgres -d minitest >> psql: could not connect to server: Connection refused >> Is the server running on host "192.168.1.8" and accepting >> TCP/IP connections on port 5432? > > "Connection refused" suggests that you've got a firewall-type problem, > ie the operating system is rejecting the connection rather than letting > the postmaster receive it. Check packet filtering rules... It's also possible that the postmaster is listening on a port other than 5342... -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(end of broadcast)--- TIP 3: 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] NAB : insert into select distinct => when used on null, distinct causes loss of type knowledge
Hi, Not exactly a showstopper, but I noticed this behaviour: db=# create table f1 (id int, value int); CREATE TABLE db=# insert into f1 select 1 as id, null; INSERT 25456306 1 db=# insert into f1 select distinct 2 as id, null; ERROR: column "value" is of type integer but expression is of type text HINT: You will need to rewrite or cast the expression. db=# insert into f1 select distinct on (id) 2 as id, null; INSERT 25456307 1 So it seems distinct applied to the second column causes it to lose knowledge on its type. Does anybody happen to know why ? -- Best, Frank. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Upgrading PostgreSQL 7.1.3
Hi: I'm planning to migrate a postgres database version 7.1.3 to a newer version. There are some tables with up to 5 million records and I'm begining to suffer from data corruption in indexes and tables. I did some hardware checkings and everything seems ok. The last move, as someone suggested in this list would be to upgrade postgres version. What is the most advisable version I should upgrade to? My top goal is reliability. Do I have to install/reinstall or upgrade? Do I need to be concerned of any special feature when dumping/restoring data? Is there any good doc about this? Thanks in advance for your vaulable help. Ruben. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How is this possible? (more on deadlocks)
On Tue, 24 Aug 2004, Carlos Moreno wrote: > Ok, now I'm really intrigued by what looks to me > (possibly from a naive point of view) like a bug, > or rather, a limitation on the implementation. Yep. See recent (and historical) discussions on needing a weaker lock than FOR UPDATE for handling foreign keys. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] NAB : insert into select distinct => when used on null, distinct causes loss of type knowledge
Frank van Vugt <[EMAIL PROTECTED]> writes: > So it seems distinct applied to the second column causes it to lose knowledge > on its type. No, because it never had any: NULL is typeless (type UNKNOWN, to the parser). In the straight INSERT this doesn't matter because we don't have to resolve the type until we get up to the INSERT, and then we know we want to insert into the value column. But to do a DISTINCT, the parser has to assign datatypes to all the columns (to determine the comparison rules). The default assumption for an UNKNOWN constant is type TEXT. This is chosen based on the assumption that when someone writes select distinct 'foo'; they are probably expecting the system to treat 'foo' as a TEXT literal. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Gentoo for production DB server?
Hello: At the risk of starting a flame-war, I'd like some more details on the use of Gentoo Linux for a production PostgreSQL server. There have been a couple of comments lately that it is not such a great idea; does anyone have specific experience they'd be willing to share? Some background: we've been running PostgreSQL on assorted versions of Red Hat for a couple of years. RedHat Enterprise is not an option, so we currently have a mix of Fedora, RH 9, White Box Enterprise (a RedHat clone), and Gentoo, and want to settle on a single distro, with White Box and Gentoo the leading contenders. Hardware is mostly Dell PowerEdge or white-box. I prefer to build PostgreSQL from source anyway, so package management is not as important as stability and ease of maintenance/updates. Most of what I've read says that Gentoo is either the greatest thing since sliced bread or not ready for primetime; in either case, details are sketchy. I would like to hear especially from people who are either running Gentoo in production or have tried and rejected it. Thank you. Christine Desmuke Kansas State Historical Society cdesmuke (at) kshs (dot) org ---(end of broadcast)--- TIP 3: 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] NAB : insert into select distinct => when used on null, distinct causes loss of type knowledge
Hi Tom, > No, because it never had any: NULL is typeless (type UNKNOWN, to the > parser). But to do a DISTINCT, the parser has to assign datatypes to all the > columns (to determine the comparison rules). The default assumption for an > UNKNOWN constant is type TEXT. I grok, thanks for the quick reply. -- Best, Frank. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Gentoo for production DB server?
On Tue, 2004-08-24 at 16:33, Christine Desmuke wrote: > At the risk of starting a flame-war, I'd like some more details on the > use of Gentoo Linux for a production PostgreSQL server. There have been > a couple of comments lately that it is not such a great idea; does > anyone have specific experience they'd be willing to share? I used Gentoo for a long time on my home systems but I recently quit. It's a "fun" distro as far as the options and all, and it has a great user community for support.. but I got tired of the Gentoo developers (whether intentional or not) pushing out new stuff marked as "stable" when it obviously was not. The price was right and I knew going in I wasn't getting a perfectly stable distro, but nevertheless they left me with a broken machine on several occasions. Having a slightly faster machine isn't worth the headaches to me personally. For stability, db/web server usage and such, I'd go with Debian. For features, desktop systems, etc., I'd go with Suse. 9.1 is impressive. For security, firewall, or router usage, I'd go with *BSD. -- Greg Donald ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Upgrading PostgreSQL 7.1.3
Date: Sun, 24 Aug 2003 23:25:24 +0200 The date's wrong on your computer. "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> wrote: > Hi: > > I'm planning to migrate a postgres database version 7.1.3 to a newer > version. There are some tables with up to 5 million records and I'm > begining to suffer from data corruption in indexes and tables. I did > some hardware checkings and everything seems ok. The last move, as > someone suggested in this list would be to upgrade postgres version. > > What is the most advisable version I should upgrade to? My top goal is > reliability. Do I have to install/reinstall or upgrade? Do I need to be > concerned of any special feature when dumping/restoring data? Is there > any good doc about this? Make absolutely sure you have a good dump of your data. You would do well to upgrade to the latest 7.4.5. Be prepared to spend some time on it, as the jump from 7.1 to 7.4 is a long way. I don't know what specific problems you might encounter, but be prepared. -- Bill Moran Potential Technologies http://www.potentialtech.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Is this legal SQL? Is it a good practice?
Peter Eisentraut wrote: I just noticed that from a C or C++ program using libpq or libpq++, I can send *one* command that contains several SQL statements separated by semicolon. But I'm wondering -- is this a PostgreSQL extension, or is it "legal SQL"? The whole libpq API is made up out of thin air, so it's not conforming to any public standard. Oh, wait. Though you didn't say it explicitly, I guess you're implying that it is libpq the one that splits the thing into the individual SQL statements and then send each of those to the backend? (wrapped around a transaction?) In particular, I'm wondering if it is a feature that in the future you might decide to eliminate for not being ANSI-SQL compliant. Because of the above, that cannot be a reason for eliminating any interfaces. Well, I was under the impression that the backend would receive *one* command with *one* string that contains all the SQL's separated by semicolons. This is what I thought might be removed in future releases, if it is considered that it's an unnecessary extension, etc. Thanks, Carlos -- ---(end of broadcast)--- TIP 3: 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] Gentoo for production DB server?
I've been extremely happy with my gentoo boxes. I switched from Slackware over the past year or so after many years of Slackware zealotry. I have nothing bad to say about using Gentoo other than I personally wouldnt use portage/ebuilds for PostgreSQL. Personally I always have better experiences when I download the source tarball and install things like PostgreSQL the way the developers distribute them. Gavin Greg Donald wrote: On Tue, 2004-08-24 at 16:33, Christine Desmuke wrote: At the risk of starting a flame-war, I'd like some more details on the use of Gentoo Linux for a production PostgreSQL server. There have been a couple of comments lately that it is not such a great idea; does anyone have specific experience they'd be willing to share? I used Gentoo for a long time on my home systems but I recently quit. It's a "fun" distro as far as the options and all, and it has a great user community for support.. but I got tired of the Gentoo developers (whether intentional or not) pushing out new stuff marked as "stable" when it obviously was not. The price was right and I knew going in I wasn't getting a perfectly stable distro, but nevertheless they left me with a broken machine on several occasions. Having a slightly faster machine isn't worth the headaches to me personally. For stability, db/web server usage and such, I'd go with Debian. For features, desktop systems, etc., I'd go with Suse. 9.1 is impressive. For security, firewall, or router usage, I'd go with *BSD. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
