Re: [GENERAL] Performance issues of one vs. two split tables.
On Tue, May 15, 2007 at 09:20:53PM +0200, PFC wrote: From an outside perspective it just seems odd that potentially a large amount of data would be pulled off disk into memory that is never used. Perhaps there's an overriding reason for this. Yeah, where would you put this data if you didn't put it where it is now ? Swish-e isn't a database by any means, but it does have a way to store column like meta data for each row. When it does a search it only explicitly pulls from disk the meta data that it's asked to return. Granted, the OS is reading from disk more than the application is asking for, but the application is only allocating memory for the data it's going to return. And the column (meta data) is not always stored together on disk. Without knowing Pg internals I wasn't aware of how the actual table data was organized and fetched into memory. Premature optimization is the root of all evil Exactly what prompted this thread. ;) -- Bill Moseley [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fixing broken permissions for deleted user
Justin Pasher wrote:
I have a PostgreSQL 7.4.14 database that is being backed up nightly
using pg_dump. Some time back, we deleted a user from the server that
was no longer employed. This in turn caused some problems with ownership
of some of the tables (since the user didn't exist, the database could
only go by the user ID the the catalog, which made the dumped confused).
We are able to fix those tables relatively easily using ALTER TABLE
table_name OWNER TO new_owner.
The problem we face are with the permissions on some tables. There are a
few tables that were originally created by this deleted user which in
turn also granted some additional permissions to others. Here is an
example what what \dp shows now (hopefully word wrap is nice to me):
| Table|Access privileges
++
| menu_items | {101=a*r*w*d*R*x*t*/101,justinp=a*r*w*d*R*x*t*/101}
The deleted user had ID 101 (obviously).
Something like:
CREATE USER temp SYSID 101
Then fix ownership etc, then drop the user.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] OIDs - file objects, are damaged by PostgreSQL.
Purusothaman A wrote: Hi all, I am using Postgresql 8.2. 8.2.which? I am using client side api to upload/download files to/from postgresql using calls lo_export()/lo_import(); If I download a file from postgresql, few weeks later, files object's contents got damaged. I don't know why. Do any of you have encountered same problem? It's not impossible that PostgreSQL is corrupting your large-objects, but it does seem unlikely. In what way were the large-object's contents corrupted? Was the file-length the same? Do you know exactly when it got corrupted? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dns less connection
Hi folks
Magnus thanks for respond, but i can't success full
with this.
Hi think it's string connection mater, but can't
connect, you try any time without pgsqlodbc
installed?.
I'm not using (vb) :D but using COM way.
Every time odcb reclaim for MS dont know default
driver or something like this, even psqlodb
installed.
Any suggestion will be appreciated?
best regards
MDC
--- Magnus Hagander [EMAIL PROTECTED] escribió:
On Mon, May 14, 2007 at 08:46:23PM -0300, marcelo
Cortez wrote:
hi all
there any was successful with connect to postgres
with psqlodbc in dsn less mode? It's wrong list
for
this subject? any pointer be appreciated
Certainly, all the time. For example (unix people
close your eyes, this is
vbscript):
dbconn.Open Driver={PostgreSQL
UNICODE};DATABASE=admin;SERVER=my.server.se;uid=
wshshell.Environment(PROCESS)(USERNAME)
//Magnus
---(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
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Plpgsql function and variable substitute
Hello, I have searched the Internet for an answer but nothing works for me. There is a plpgsql function which is used in a trigger. I need to substitute a value 7 for lifetime (integer) SELECTed in a previous query. DECLARE lifetime integer; BEGIN SELECT foo INTO lifetime FROM tblname; UPDATE SET expiretime = expiretime + interval '7 days' WHERE . RETURN NEW; END; This doesn't work for me: SET expiretime = expiretime + interval 'lifetime days' SET expiretime = expiretime + interval || lifetime || 'days' Thank you for your help or for a some useful link. -- Jiri Nemec http://www.meneashop.cz/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres Printed Manuals
On 5/14/07, Alvaro Herrera [EMAIL PROTECTED] wrote: Rich Shepard wrote: On Mon, 14 May 2007, Bruce Momjian wrote: How much would it be to email the PDF manual to someone like Kinkos and get it printed? Effectively, that might be the cheepest solution because it is print-on-demand. What I would suggest is, rather than hoping somebody has the bankroll for one LARGE document, somebody might take a close look at a PostgreSQL Documentation Project to break down those 1600 pages into three or four manuals that would be more reasonable to print one at a time. Well, I didn't do that, but as an exercise I split the manual in 740 page chunks (maximum size at lulu), which misses the last couple hundred pages (old release notes and index, mostly) and put them on lulu. It comes to $19.33 each volume for 2 volumes. I agree that it would be great to have them split up a bit so they could fit in normal sized books, but there it is, if you want a dead tree version. pdftk is an open source pdf breaker-upper I used for this. http://www.lulu.com/content/863723 http://www.lulu.com/content/864445 - Ian ---(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] Plpgsql function and variable substitute
am Wed, dem 16.05.2007, um 15:11:17 +0200 mailte Ji?í N?mec folgendes: Hello, I have searched the Internet for an answer but nothing works for me. There is a plpgsql function which is used in a trigger. I need to substitute a value 7 for lifetime (integer) SELECTed in a previous query. DECLARE lifetime integer; BEGIN SELECT foo INTO lifetime FROM tblname; UPDATE SET expiretime = expiretime + interval '7 days' WHERE . RETURN NEW; END; This doesn't work for me: SET expiretime = expiretime + interval 'lifetime days' SET expiretime = expiretime + interval || lifetime || 'days' *untested* execute 'UPDATE ...' || lifetime || 'days' http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] HowTo SSL probaply with ODBC ?
Hi, is there a documentation on how to secure a connection withe SSL? That is an option of the ODBC driver, isn't it? The motivation is that I need to rent a remote server for PG. Their admin proposes to open port 5432 on the outside of their firewall but he has no idea how to secure the access besides PG's user/password. I couldn't even restrict the accessing IPs within PG because they will be dynamic. Regards Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dns less connection
No, you need pgsqlodbc installed. You just don't need a DSN configured, but
the driver has to be installed.
//Magnus
On Wed, May 16, 2007 at 10:19:29AM -0300, marcelo Cortez wrote:
Hi folks
Magnus thanks for respond, but i can't success full
with this.
Hi think it's string connection mater, but can't
connect, you try any time without pgsqlodbc
installed?.
I'm not using (vb) :D but using COM way.
Every time odcb reclaim for MS dont know default
driver or something like this, even psqlodb
installed.
Any suggestion will be appreciated?
best regards
MDC
--- Magnus Hagander [EMAIL PROTECTED] escribió:
On Mon, May 14, 2007 at 08:46:23PM -0300, marcelo
Cortez wrote:
hi all
there any was successful with connect to postgres
with psqlodbc in dsn less mode? It's wrong list
for
this subject? any pointer be appreciated
Certainly, all the time. For example (unix people
close your eyes, this is
vbscript):
dbconn.Open Driver={PostgreSQL
UNICODE};DATABASE=admin;SERVER=my.server.se;uid=
wshshell.Environment(PROCESS)(USERNAME)
//Magnus
---(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
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Plpgsql function and variable substitute
Jiří Němec wrote: SET expiretime = expiretime + interval 'lifetime days' SET expiretime = expiretime + interval || lifetime || 'days' SET expiretime = expiretime + lifetime * interval '1 day' -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] dns less connection
There is actually an ODBC list vor PG availlable ;)
The drivers name is postgresql unicode
orpostgresql ansidepending on your needs.
Driver={PostgreSQL UNICODE};DATABASE=X1;SERVER=X2;PORT=X3;UID=X4;PWD=X5;X6;
This is the connection string but you have to replace
X1 with the name of your database
X2 .. server
X3 your port ... ok this would probaply be 5432
X4, X5 your PG-username and password
X6 are ODBC parameters that you like to use e.g.
BoolsAsChar=0;TrueIsMinus1=1;RowVersioning=1;
This works at least for ADODB.
DAO had it like this:
ODBC;Driver=PostgreSQL
UNICODE;DATABASE=X1;SERVER=X2;PORT=X3;UID=X4;PWD=X5;X6;
marcelo Cortez schrieb:
Hi folks
Magnus thanks for respond, but i can't success full
with this.
Hi think it's string connection mater, but can't
connect, you try any time without pgsqlodbc
installed?.
I'm not using (vb) :D but using COM way.
Every time odcb reclaim for MS dont know default
driver or something like this, even psqlodb
installed.
Any suggestion will be appreciated?
best regards
MDC
--- Magnus Hagander [EMAIL PROTECTED] escribió:
On Mon, May 14, 2007 at 08:46:23PM -0300, marcelo
Cortez wrote:
hi all
there any was successful with connect to postgres
with psqlodbc in dsn less mode? It's wrong list
for
this subject? any pointer be appreciated
Certainly, all the time. For example (unix people
close your eyes, this is
vbscript):
dbconn.Open Driver={PostgreSQL
UNICODE};DATABASE=admin;SERVER=my.server.se;uid=
wshshell.Environment(PROCESS)(USERNAME)
//Magnus
---(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
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] basic postgres questions...
hey.. . can someone point me to the cmds that i'd use in order to see what databases are created in my postgres app. i need to see what's here, and then i need to know the cmd to then delete a given database, and the underlying tables. in searching google, i'm seeing different cmds... also, is there an irc channel for postgres! thanks -bruce ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] basic postgres questions...
On Wed, May 16, 2007 at 08:21:59AM -0700, bruce wrote: hey.. . can someone point me to the cmds that i'd use in order to see what databases are created in my postgres app. i need to see what's here, and then i need template1=# select datname, oid from pg_database; datname | oid ---+--- postgres | 10793 template1 | 1 template0 | 10792 wiki | 26158 (4 rows) template1=# \l List of databases Name| Owner | Encoding ---+-+-- postgres | pgadmin | UTF8 template0 | pgadmin | UTF8 template1 | pgadmin | UTF8 wiki | pgadmin | UTF8 (4 rows) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] to_number behavior change between 8.1 and 8.2
I am getting an error from to_number() in PostgreSQL 8.2 that does not occur in 8.1: invalid input syntax for type numeric: I assume that it is rejecting spaces, so I tried trimming with: to_number(trim (both ' ' from streetnum),'9') but still get the same error. Any suggestions? Thanks, Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] dns less connection
Andreas ,Magnus
I do where you say me but...
' ''IM002: [Microsoft][ODBC Driver Manager] Data
source name not found and no default driver
specified''')
i'm follow your instrutions and replace parts of
connectString but don't work .
Later a try with debugging options.
best regards
MDC
--- Andreas [EMAIL PROTECTED] escribió:
There is actually an ODBC list vor PG availlable ;)
The drivers name is postgresql unicode
orpostgresql ansidepending on your needs.
Driver={PostgreSQL
UNICODE};DATABASE=X1;SERVER=X2;PORT=X3;UID=X4;PWD=X5;X6;
This is the connection string but you have to
replace
X1 with the name of your database
X2 .. server
X3 your port ... ok this would probaply be 5432
X4, X5 your PG-username and password
X6 are ODBC parameters that you like to use e.g.
BoolsAsChar=0;TrueIsMinus1=1;RowVersioning=1;
This works at least for ADODB.
DAO had it like this:
ODBC;Driver=PostgreSQL
UNICODE;DATABASE=X1;SERVER=X2;PORT=X3;UID=X4;PWD=X5;X6;
marcelo Cortez schrieb:
Hi folks
Magnus thanks for respond, but i can't success
full
with this.
Hi think it's string connection mater, but can't
connect, you try any time without pgsqlodbc
installed?.
I'm not using (vb) :D but using COM way.
Every time odcb reclaim for MS dont know default
driver or something like this, even psqlodb
installed.
Any suggestion will be appreciated?
best regards
MDC
--- Magnus Hagander [EMAIL PROTECTED]
escribió:
On Mon, May 14, 2007 at 08:46:23PM -0300, marcelo
Cortez wrote:
hi all
there any was successful with connect to
postgres
with psqlodbc in dsn less mode? It's wrong list
for
this subject? any pointer be appreciated
Certainly, all the time. For example (unix people
close your eyes, this is
vbscript):
dbconn.Open Driver={PostgreSQL
UNICODE};DATABASE=admin;SERVER=my.server.se;uid=
wshshell.Environment(PROCESS)(USERNAME)
//Magnus
---(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
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of
broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
__
Preguntá. Respondé. Descubrí.
Todo lo que querías saber, y lo que ni imaginabas,
está en Yahoo! Respuestas (Beta).
¡Probalo ya!
http://www.yahoo.com.ar/respuestas
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] to_number behavior change between 8.1 and 8.2
Richard Greenwood wrote: I am getting an error from to_number() in PostgreSQL 8.2 that does not occur in 8.1: invalid input syntax for type numeric: I assume that it is rejecting spaces, so I tried trimming with: to_number(trim (both ' ' from streetnum),'9') but still get the same error. In 8.2 we no longer accept anything but valid numbers for numbers :). is not a valid number. The way around this is to change to NULL . Sincerely, Joshua D. Drake Any suggestions? Thanks, Rich ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] to_number behavior change between 8.1 and 8.2
Richard Greenwood wrote: I am getting an error from to_number() in PostgreSQL 8.2 that does not occur in 8.1: invalid input syntax for type numeric: I assume that it is rejecting spaces, so I tried trimming with: to_number(trim (both ' ' from streetnum),'9') but still get the same error. Any suggestions? I seem to recall it was tightened up - you'll need to give it a valid number. What number is '' supposed to be? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] stats collector spins my disk up every 500ms (8.2.3)
Alvaro Herrera wrote: Daniel Barlow wrote: 1 battery life from my laptop, I noticed that one source of periodic disk writes was the postgres stats collector process, which appears to write to pgstat.tmp every 500ms) Hmm, I don't think we have an optimization to avoid writing it when the data hasn't changed. This seems easy to do ... see attached patch (untested) Any chance something like this can make it to the patch queues. I had also earlier noticed disks not spinning down when running demos on my laptop. ---(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] stats collector spins my disk up every 500ms (8.2.3)
Alvaro Herrera [EMAIL PROTECTED] writes: Daniel Barlow wrote: Questions: 1) this is on a system with I believe to be quiescent - there is only one client open which is not doing anything. Before I get more involved with this, can someone just confirm that pgstat will continue to update this file even when nothing is happening? If not, then I guess something /is/ happening and I need to investigate what it is (advice on how to track it down is welcome - I already have statement logging on, and there's nothing showing in there) Hmm, I don't think we have an optimization to avoid writing it when the data hasn't changed. This seems easy to do ... see attached patch (untested) Have you made any effort to confirm this is needed? Watching the collector with strace, I don't see it doing anything but poll() unless new messages come in. However, in HEAD it seems new messages come in every 30 seconds or so --- it looks to me like the autovacuum launcher is rather uselessly launching a new worker every few seconds, and the worker generates stats in the process of examining a database. Can we damp that behavior down a bit in an idle system? I'm not sure about Daniel's report of unprovoked writes every 500ms. It might be an artifact of the stats delay bug we fixed in 8.2.4, but this isn't the symptom that was reported. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] a few questions on backup
Marco Colombo [EMAIL PROTECTED] writes: Good to know, thanks. I think I'll experiment a bit with archive_command. My point was that since I know (or better assume) that old segments are going to stay in my pg_xlog for *days* before getting recycled, On what do you base that assumption? Once the system thinks they're not needed anymore, they'll be recycled immediately. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] to_number behavior change between 8.1 and 8.2
On 5/16/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Richard Greenwood wrote: I am getting an error from to_number() in PostgreSQL 8.2 that does not occur in 8.1: invalid input syntax for type numeric: I assume that it is rejecting spaces, so I tried trimming with: to_number(trim (both ' ' from streetnum),'9') but still get the same error. In 8.2 we no longer accept anything but valid numbers for numbers :). is not a valid number. The way around this is to change to NULL . Sincerely, Joshua D. Drake Thanks for the replies. The old behavior was certainly handier for my application. I had been using the old to_number() to get a numeric sort on a char field of street address numbers that contained spaces and numbers like '502B'. So with 8.2, I could: 1. pre-process (clean up) the data so that it really was numeric 2. write my own function duplicating the 8.1 behavior 3. or, any other ideas? Thanks again, Rich -- Richard Greenwood [EMAIL PROTECTED] www.greenwoodmap.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a few questions on backup
Tom Lane wrote: Marco Colombo [EMAIL PROTECTED] writes: Good to know, thanks. I think I'll experiment a bit with archive_command. My point was that since I know (or better assume) that old segments are going to stay in my pg_xlog for *days* before getting recycled, On what do you base that assumption? Once the system thinks they're not needed anymore, they'll be recycled immediately. regards, tom lane Well now that you make me think of it, I do make some assumptions. One is that only one file in pg_xlog is the active segment. Two is that I can trust modification times (so that a file inside pg_xlog that looks old is actually old... and since postgresql does not run as root, it couldn't cheat on that even if it tried to). The best thing I can do is to configure archiving, and see what gets archived exactly. I'm making assumptions there too. I expect for each file in pg_xlog to find a copy in the archive directory (say archiving is done with cp), with one exception, the segment currently beeing written to. There will be a file with the same name but different contents (and older modification time). I'll try that out. Maybe my ideas are so far from the truth that I'm having a hard time in explaing them to people who actually know how things work. I'll be back with results. Meanwhile, thanks for your time. .TM. ---(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] cube operations
Hi, I have a array column which has 12 real values in it. Basically these values represent co-ordinates in 12 dimensions for a substance. My main need is to find substances similar to a particular compound. Now I can do by calculating differences with each array in the whole table. But the table has millions of rows. So I need some kinda higher dimensional index. I have read about the cube operation in postgre, can it be extended to 12 dimensions or something like that. Thanks Abhang ---(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 Printed Manuals
On May 13, 2007, at 10:43 PM, Tom Lane wrote: It's hard to make any money that way :-(. Rich Morin used to run a business called Prime Time Freeware that published hardcopy versions of our manuals along with much other open-source documentation. He gave up on it some years ago, though, and I doubt that the market has improved. Seems like an ideal use of those print-on-demand services like lulu.com among others. The project could post an official PDF of the docs, and take a cut every time someone ordered a printed copy. The question is who would take the time to make a professional looking PDF. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] stats collector spins my disk up every 500ms (8.2.3)
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Daniel Barlow wrote: Questions: 1) this is on a system with I believe to be quiescent - there is only one client open which is not doing anything. Before I get more involved with this, can someone just confirm that pgstat will continue to update this file even when nothing is happening? If not, then I guess something /is/ happening and I need to investigate what it is (advice on how to track it down is welcome - I already have statement logging on, and there's nothing showing in there) Hmm, I don't think we have an optimization to avoid writing it when the data hasn't changed. This seems easy to do ... see attached patch (untested) Have you made any effort to confirm this is needed? Watching the collector with strace, I don't see it doing anything but poll() unless new messages come in. Hmm, right, it doesn't do anything. However, in HEAD it seems new messages come in every 30 seconds or so --- it looks to me like the autovacuum launcher is rather uselessly launching a new worker every few seconds, and the worker generates stats in the process of examining a database. Correct, it's autovacuum causing it (turning autovac off makes it go away). Can we damp that behavior down a bit in an idle system? What we could do, I think, is to keep a counter of insert/update/delete operations per database, and have autovac skip processing one if it sees that it hasn't had any activity. That would remove this particular symptom. (Magnus wanted to add the global counters anyway for some reason.) I'm not sure about Daniel's report of unprovoked writes every 500ms. It might be an artifact of the stats delay bug we fixed in 8.2.4, but this isn't the symptom that was reported. Yup. So going back to his original question, Before I get more involved with this, can someone just confirm that pgstat will continue to update this file even when nothing is happening? If not, then I guess something /is/ happening and I need to investigate what it is I suggest turning log_min_messages to debug2 and seeing if there is any activity showing up. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Postgres Printed Manuals
Vivek Khera wrote: On May 13, 2007, at 10:43 PM, Tom Lane wrote: It's hard to make any money that way :-(. Rich Morin used to run a business called Prime Time Freeware that published hardcopy versions of our manuals along with much other open-source documentation. He gave up on it some years ago, though, and I doubt that the market has improved. Seems like an ideal use of those print-on-demand services like lulu.com among others. The project could post an official PDF of the docs, and take a cut every time someone ordered a printed copy. The question is who would take the time to make a professional looking PDF. http://www.lulu.com/content/863723 http://www.lulu.com/content/864445 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(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] Performance issues of one vs. two split tables.
On May 14, 2007, at 4:37 PM, Bill Moseley wrote: Say that there's also about 10 columns of settings or preferences for each user. Are there any cases or reasons to have a separate user_preferences table vs. just placing all the columns together in one table? when you have, say 65 million users, it makes sense to push the ancillary info to another table to keep from having to copy too much data when you update the main info (like last access time). ---(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] Performance issues of one vs. two split tables.
On May 15, 2007, at 10:35 AM, Bill Moseley wrote: For some value of large, is there a time when one might consider using a single column in the user or user_prefs table to represent their color choices instead of a link table? We use bitfields on our large user table. It is becoming unworkable to scan for matches, since overall most people have very few selections made. We are moving it to a model like your favorite_colors table which just links the option and the user. We find that doing joins on large tables which can be indexed to avoid full table scans are very fast in postgres, since the index can do much of your culling of potential matching rows. With bitfields, you are more or less forced into doing a sequence scan to find everyone who likes the color red. Of course, if you're playing with only a few thousand users, either approach works well. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgres Printed Manuals
On Wed, 16 May 2007, Vivek Khera wrote: Seems like an ideal use of those print-on-demand services like lulu.com among others. The project could post an official PDF of the docs, and take a cut every time someone ordered a printed copy. The question is who would take the time to make a professional looking PDF. I have no idea how the docs are written, but using LaTeX (and the LyX GUI front end) produces typeset output. The pdf is, literally, camera-ready. Rich -- Richard B. Shepard, Ph.D. |The Environmental Permitting Applied Ecosystem Services, Inc.| Accelerator(TM) http://www.appl-ecosys.com Voice: 503-667-4517 Fax: 503-667-8863 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] cube operations
2007/5/16, ABHANG RANE [EMAIL PROTECTED]: Hi, I have a array column which has 12 real values in it. Basically these values represent co-ordinates in 12 dimensions for a substance. My main need is to find substances similar to a particular compound. Now I can do by calculating differences with each array in the whole table. But the table has millions of rows. So I need some kinda higher dimensional index. I have read about the cube operation in postgre, can it be extended to 12 dimensions or something like that. Don't know if this helps, but have a look at intarray: http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/intarray/ If you feel brave you could take this code and try to write some proximity- or similarity-checking functions in C to speedup the calculations. Also consider representing values by integers, since integer operations are much faster. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance issues of one vs. two split tables.
We use bitfields on our large user table. It is becoming unworkable to
scan for matches, since overall most people have very few selections
made.
We are moving it to a model like your favorite_colors table which just
links the option and the user.
We find that doing joins on large tables which can be indexed to avoid
full table scans are very fast in postgres, since the index can do much
of your culling of potential matching rows. With bitfields, you are
more or less forced into doing a sequence scan to find everyone who
likes the color red.
Of course, if you're playing with only a few thousand users, either
approach works well.
Things you could try :
* Use an integer array instead of a bitfield
(for instance, in users table, column favourites would contain { 1,2 } if
the user selected items 1 and 2 )
Then, you can make a Gist index on it and use the indexed intersection
operator
This is likely the optimal solution if the maximum number of items is
small (say, 100 is good, 10 is not)
* keep your bitfields and create conditional indexes :
CREATE INDEX ... WHERE bitfield_column 1;
CREATE INDEX ... WHERE bitfield_column 2;
CREATE INDEX ... WHERE bitfield_column 4;
CREATE INDEX ... WHERE bitfield_column 8;
CREATE INDEX ... WHERE bitfield_column 16;
etc...
Obviously this will only work if you have, say, 10 favouritess. 100
indexes on a table would really suck.
Then, when looking for users who chose bits 1 and 2, do :
SELECT WHERE (bitfield_column 1) AND (bitfield_column 2)
postgres will do a bitmap-and using the two indexes
(note : when we get bitmap indexes, this will be even better)
* use tsearch2 :
favourites = 'red blue'
and fulltext-search it
* use a favourites table :
This makes queries hard to optimize. Consider the table (user_id,
item_id) meaning user selected this item as favourite.
If you want to know which users did select both items 1 and 2, you have
to do a self-join, something like :
SELECT... FROM favourites a, favourites b WHERE a.user_id = b.user_id AND
a.item_id=1 AND b.item_id = 2
This is likely to be not very fast if 1 million users check each option
but only 100 check both.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Constructing a SELECT statement in pl/pgsql
I have a function that returns a set of records based on one of my views. The function takes two arguments of user_id and saved search name, looks up the search values from a table previously saved and performs a SELECT query on my view to return my set of records found. However, I don't want to SELECT all columns of the view in my query, only the fields that have values specified to search for. So, if a value in my search table for a clientname field is blank, omit this field from my query so I can use DISTINCT and pull only those records. I hope this is not confusing. I guess my question is how to replace the following... FOR searchresults IN SELECT * FROM my_view WHERE snip LOOP With something like this... FOR searchresults IN SELECT DISTINCT clientname FROM my_view WHERE snip LOOP But I don't know if the field I want to search is going to be clientname or clienttype or even other types of fields until I test them for values. Is there a way for me to construst a comma separated list of columns to search in my pl/pgsql script and then use it in my FOR LOOP? I know I can use Perl to help build my list of columns, if needed, but then how can I pass that off to my SELECT query? If I have a variable called 'myfields' and try to place that in my SELECT query, it thinks there is a field named myfields, of course. Maybe a way to evaluate the comman sepeated list? Thanks in advance for any help! Or suggestion for a better way to develop user saved searches. -- Robert ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Constructing a SELECT statement in pl/pgsql
Robert Fitzpatrick wrote: I guess my question is how to replace the following... FOR searchresults IN SELECT * FROM my_view WHERE snip LOOP With something like this... FOR searchresults IN SELECT DISTINCT clientname FROM my_view WHERE snip LOOP Build the query as text and use EXECUTE (see manuals for details). You'll also find quote_ident() and quote_literal() useful - again see manuals. You'll need to think through the type implications of picking columns based on supplied parameters - how do you cope with selecting an integer this time and a text column next time? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance issues of one vs. two split tables.
On 16-May-07, at 4:05 PM, PFC wrote: This makes queries hard to optimize. Consider the table (user_id, item_id) meaning user selected this item as favourite. If you want to know which users did select both items 1 and 2, you have to do a self-join, something like : SELECT... FROM favourites a, favourites b WHERE a.user_id = b.user_id AND a.item_id=1 AND b.item_id = 2 You could get users who have selected both items 1 and 2 without doing a self-join with a query like the following: select user_id from favourite where item_id = 1 or item_id = 2 group by user_id having count(*) = 2;
[GENERAL] Vacuum non-clustered tables only
I think I know the answer to this, but... Is there a semi-easy way vacuum all tables in a database *except* those that are clustered? (VACUUM [full]; CLUSTER;) is a huge waste of time and resources when you have clustered tables. If there isn't a way to do this, can we consider it a feature request? Perhaps VACUUM unclustered or something? -Glen ---(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] Vacuum non-clustered tables only
Glen Parker wrote: I think I know the answer to this, but... Is there a semi-easy way vacuum all tables in a database *except* those that are clustered? Not that I know of. J (VACUUM [full]; CLUSTER;) is a huge waste of time and resources when you have clustered tables. If there isn't a way to do this, can we consider it a feature request? Perhaps VACUUM unclustered or something? -Glen ---(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 -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] HowTo SSL probaply with ODBC ?
On May 16, 2007, at 10:23 AM, Andreas wrote: is there a documentation on how to secure a connection withe SSL? That is an option of the ODBC driver, isn't it? http://www.postgresql.org/docs/8.2/interactive/ssl-tcp.html I don't know about ODBC. The motivation is that I need to rent a remote server for PG. Their admin proposes to open port 5432 on the outside of their firewall but he has no idea how to secure the access besides PG's user/password. I couldn't even restrict the accessing IPs within PG because they will be dynamic. If they won't setup PostgreSQL properly with SSL support, your best bet is to setup a SSH tunnel. This will also work with ODBC. If they don't support SSH, find another provider :). Here is some background on setting it up: http://pgedit.com/tip/postgresql/ssh_tunneling John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] cube operations
ABHANG RANE wrote: I have a array column which has 12 real values in it. Basically these values represent co-ordinates in 12 dimensions for a substance. My main need is to find substances similar to a particular compound. Now I can do by calculating differences with each array in the whole table. But the table has millions of rows. So I need some kinda higher dimensional index. Is there any particular reason you're using an array? If every row has all twelve values, I'd just make them columns. Then I could use a multi-column index. I have read about the cube operation in postgre, can it be extended to 12 dimensions or something like that. I have no experience with CUBE, but I think it's just a kind of summarization aggregate. It sounds like you want the Nearest Neighbor(s) of your particular compound. You might to read about that: http://en.wikipedia.org/wiki/Nearest_neighbor_search - John Burger G63 ---(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] Vacuum non-clustered tables only
On Wed, May 16, 2007 at 03:40:27PM -0700, Glen Parker wrote: I think I know the answer to this, but... Is there a semi-easy way vacuum all tables in a database *except* those that are clustered? You could query for tables that aren't clustered and use that to build a list of VACUUM commands, but a better question is... why? If you create dead tuples in a CLUSTERed table you still need to vacuum it eventually... or do you just cluster the database often enough that it doesn't matter? -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fixing broken permissions for deleted user
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 16, 2007 4:56 AM
To: Justin Pasher
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Fixing broken permissions for deleted user
Justin Pasher wrote:
I have a PostgreSQL 7.4.14 database that is being backed up nightly
using pg_dump. Some time back, we deleted a user from the server that
was no longer employed. This in turn caused some problems with ownership
of some of the tables (since the user didn't exist, the database could
only go by the user ID the the catalog, which made the dumped confused).
We are able to fix those tables relatively easily using ALTER TABLE
table_name OWNER TO new_owner.
The problem we face are with the permissions on some tables. There are a
few tables that were originally created by this deleted user which in
turn also granted some additional permissions to others. Here is an
example what what \dp shows now (hopefully word wrap is nice to me):
| Table|Access privileges
++
| menu_items | {101=a*r*w*d*R*x*t*/101,justinp=a*r*w*d*R*x*t*/101}
The deleted user had ID 101 (obviously).
Something like:
CREATE USER temp SYSID 101
Then fix ownership etc, then drop the user.
OK. After playing around with this extensively I FINALLY got the permissions
remove (from anything I can see).
| Table|Access privileges
++
| menu_items | {justinp=a*r*w*d*R*x*t*/justinp,group
dbs_readwrite=arwd/justinp}
The table owner is also a different user from user id 101. However, it still
gives me the same complaint.
pg_dump: WARNING: owner of data type menu_items appears to be invalid
pg_dump: WARNING: owner of data type pg_toast_47831338 appears to be
invalid
I do notice the error says owner of data type, so perhaps it is referring
to something else besides the table? I'm also trying to figure out an easy
way to find the other entity (pg_toast_47831338).
Justin Pasher
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Windows Vista Support
Can you confirm that you don't provide support for Windows Vista for any release of Postgres. I'm dumbfounded an it appears that you don't support Vista. If so, are you planning any releases. I have a major project and was hoping to use Postgres. Michael Alexander Impower
Re: [GENERAL] Windows Vista Support
[EMAIL PROTECTED] wrote: Can you confirm that you don't provide support for Windows Vista for any release of Postgres. I'm dumbfounded an it appears that you don't support Vista. If so, are you planning any releases. I have a major project and was hoping to use Postgres. Michael Alexander Impower I've got PG running on a few Vista machines at this stage without problems. The main thing you need to look out for is disabling User Account Control for the installation process, otherwise the install will fail. This is done through Control Panel - User Accounts. Once the install is completed you can re-enable it without issue. Regards, -- Paul Lambert Database Administrator AutoLedgers ---(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] cube operations
hacking contrib/intarray could help you. You need to add function which return the number of overlapped elements. Oleg On Wed, 16 May 2007, John D. Burger wrote: ABHANG RANE wrote: I have a array column which has 12 real values in it. Basically these values represent co-ordinates in 12 dimensions for a substance. My main need is to find substances similar to a particular compound. Now I can do by calculating differences with each array in the whole table. But the table has millions of rows. So I need some kinda higher dimensional index. Is there any particular reason you're using an array? If every row has all twelve values, I'd just make them columns. Then I could use a multi-column index. I have read about the cube operation in postgre, can it be extended to 12 dimensions or something like that. I have no experience with CUBE, but I think it's just a kind of summarization aggregate. It sounds like you want the Nearest Neighbor(s) of your particular compound. You might to read about that: http://en.wikipedia.org/wiki/Nearest_neighbor_search - John Burger G63 ---(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 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Fixing broken permissions for deleted user
Justin Pasher [EMAIL PROTECTED] writes: OK. After playing around with this extensively I FINALLY got the permissions remove (from anything I can see). ... The table owner is also a different user from user id 101. However, it still gives me the same complaint. pg_dump: WARNING: owner of data type menu_items appears to be invalid pg_dump: WARNING: owner of data type pg_toast_47831338 appears to be invalid I do notice the error says owner of data type, so perhaps it is referring to something else besides the table? Yeah, the pg_type entry for the table's rowtype. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
