Re: [GENERAL] SAST FATAL: could not access private key file server.key
On Wed, Jul 2, 2008 at 5:13 PM, Albe Laurenz [EMAIL PROTECTED] wrote: If you don't care, you could 1) remove the symbolic link. 2) copy the key file to server.key in the PostgreSQL directory. 3) chown the copy of the file. Okay, I'll do that. Do you need SSL support at all? If not, just disable it and all your troubles are gone. Well, not at the moment, I'll try that as a last resort. If it worked before, then somebody must have changed something in the meantime. Since it must have been root, you should be able to track down who or what it was. Well, I'm the only person who was using it as root, so it must've been something I've done, dammit! Many people posted the link to the documentation. Read and enjoy! Yes, I've had a look at it, and have tried to follow instructions therein, but I'm not sure I understand it. PS: It is better to CC the list. Oh, sorry! I'm a member of several lists, and some default the reply to the list and some to the poster; I keep forgetting which is which! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing text search treatment of puncutation
In general there seem to be a lot of ways that people wish they could tweak the text search parser, and telling them to write their own parser isn't a very helpful response for most folk. I don't have an idea about how to improve the situation, but it seems like something that should be thought about. We (with Oleg) thought hard about it and we don't find a solution yet. Configurable parser should be: - fast - flexible - not error-prone - comfortable to use by non-programmer (at least for non-C programmer) It might be a table-driven state machine (just put TParserStateAction into table(s) with some caching for first step) , but it's complex to operate and it's needed to prove correctness of changes in states before its become in use. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
On Thu, Jul 3, 2008 at 1:49 AM, Klint Gore [EMAIL PROTECTED] wrote: I'm running it under my own account which has adminstrator rights. Peak memory usage was about 540m which brought the total usage for the machine to about half the physical memory allocated (3g total). Is there a binary debug build for win32 somewhere? If you're using 8.3.x, we include an archive of symbol files alongside the installer on the download site. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory Problem
Hi, On Wed, 02 Jul 2008, Tom Lane [EMAIL PROTECTED] writes: Are there any foreign keys referencing this table? If so, you're probably running out of memory for the list of pending trigger events (to verify that the FK constraint isn't violated by the delete). Allowing the triggers to fire individually would take forever anyway, so it might be best to drop the foreign key constraint(s) and then re-establish them after the delete. Thanks for the warning. Temporarily disabling FKs did the job. I knew the problem about triggers, but didn't expect same applies for FKs also. I'm very suprised FKs cause memory exhaustion too. Regards. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with roles and permissions
On Wed, Jul 2, 2008 at 6:09 PM, Chandra Barnett [EMAIL PROTECTED] wrote: Bah. Must be pgAdmin, then. In its role creation dialogue, INHERIT is definitely off by default. Guess that's not you guys, though. Sorry to have impugned your judgement... =) Fixed in SVN. Sorry for the confusion. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] changing text search treatment of puncutation
On Wed, 2 Jul 2008, Tom Lane wrote: John DeSoi [EMAIL PROTECTED] writes: Is there an easy way to change '/' to be treated like '-' ? I've looked over the documentation several times and could not find anything. Even just a way to get the two tokens 'home' and 'work' without the joined form would be helpful. Seems like the simplest solution is just to apply regexp_replace(text, '/', '-', 'g') before letting the text search stuff have the string. If you're using a trigger to update a tsvector column, this would be pretty trivial to do within the trigger. In general there seem to be a lot of ways that people wish they could tweak the text search parser, and telling them to write their own parser isn't a very helpful response for most folk. I don't have an idea about how to improve the situation, but it seems like something that should be thought about. Sure, we thought about this. The most difficult part in user-configurable parser (we thought about table-driven finite automata) is the foolproof design. There are should be algorithms for testing validity of finite automata, but we don't know any effective way. 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
Tom Lane [EMAIL PROTECTED] writes: Well, you tell me --- *you* reported a behavior that isn't obviously explained by the bug we found. In case it wasn't clear, the bug found was a intra-transaction memory leak. When the transaction ended the memory would be reclaimed. That doesn't seem to match the description of long term memory growth. It's possible that what you were seeing was an indirect effect of the now-known bug: if the xpath leak were to occur repeatedly on a large scale in a long-lived session, I think it's possible that memory allocation behavior might suffer due to fragmentation effects. I feel that that's a pretty hand-wavy explanation though. Another explanation is that there wasn't a inter-transaction memory leak, it was just that the high water mark would grow whenever a transaction processed more data than previous transactions. If the data set size varies a lot most common distributions would have the majority of data sets be about the same size with a long tail of larger sets. That might like a slow creep as the increases get rarer and rarer but do continue to happen. That's just a special case of what would be expected to happen with memory allocation anyways though. Few allocators return memory to the OS anyways. It might just be exaggerated in this case since probably a significant part of Postgres's footprint here was the per-transaction memory being used by this leak. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Functional index adding one
Hi all: I'm trying to create a functional index into column position of token table (see below). I want to make something like: CREATE INDEX token_position_func ON token (position+1); but I get: test=# CREATE INDEX token_position_func test-# ON token (position+1); ERROR: syntax error at or near + LINE 2: ON token (position+1); I read that I can do ON function(column) but, is there a built-in function in PostgreSQL to do what I want (add one to the value) or have i to build one to make this simple calculation? CREATE TABLE doc( id INT, editorial VARCHAR, CONSTRAINT doc_pk PRIMARY KEY (id) ); CREATE TABLE token ( id INT, id_doc INT, token VARCHAR, position INT, CONSTRAINT foreign_doc FOREIGN KEY (id_do) REFERENCES doc (identificador) ); Regards, Mario Barcala -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
am Thu, dem 03.07.2008, um 11:50:39 +0200 mailte [EMAIL PROTECTED] folgendes: Hi all: I'm trying to create a functional index into column position of token table (see below). I want to make something like: CREATE INDEX token_position_func ON token (position+1); but I get: test=# CREATE INDEX token_position_func test-# ON token (position+1); ERROR: syntax error at or near + LINE 2: ON token (position+1); I read that I can do ON function(column) but, is there a built-in function in PostgreSQL to do what I want (add one to the value) or have i to build one to make this simple calculation? Right, write your own function for that, for example: test=# CREATE TABLE token (id int, id_doc int, token text, position int); CREATE TABLE test=*# create function get_pos(int) returns int as $$ declare ret int; begin select into ret position+1 from token where id=$1; return ret; end; $$language plpgsql immutable; CREATE FUNCTION test=*# CREATE INDEX token_position_func ON token (get_pos(position)); CREATE INDEX Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Full text index without accents
Hi again: I am trying to create a full text configuration to ignore word accents in my searches. My approach is similar to simple dicionary one, but i want to remove accents after converting to lower. Is it the only way to do it to develop another .c and write my own dict_noaccent.c, and then compile and install it into the system? Regars, Mario Barcala -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete from Join
Scott Marlowe wrote: Take a look here, in the notes section: http://www.postgresql.org/docs/8.3/interactive/sql-delete.html on the using keyword. Thank you for your reply Scott, I guess this is where the confusion started for me. It says here in your reference that the using clause is not standard but that is fine as I am doing a subst. MS actually allows: DELETE from table from table. I change the second from to a using and it works fine most of the time. What I am actually trying to get past is: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; Where the INNER JOIN keyword is used in the delete. In the documentation section you cited, they refer to the from clause in the usinglist. The from clause link there refers to select which states that a join keyword is valid in a from clause. What I have found is it sometimes compiles but doesn't work. I am just seeking verification if it is supposed to work. Gwyneth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
On Thu, Jul 03, 2008 at 11:50:39AM +0200, [EMAIL PROTECTED] wrote: test=# CREATE INDEX token_position_func test-# ON token (position+1); ERROR: syntax error at or near + LINE 2: ON token (position+1); I read that I can do ON function(column) but, is there a built-in function in PostgreSQL to do what I want (add one to the value) or have i to build one to make this simple calculation? You just want an extra set of brackets; i.e.: CREATE INDEX token_position_func ON token ((position+1)); Should do the trick. Not entirely sure why, but it'll probably have something to do with avoiding ambiguity in the grammar. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete from Join
--- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: Re: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 7:12 PM --- On Wed, 7/2/08, Gwyneth Morrison [EMAIL PROTECTED] wrote: From: Gwyneth Morrison [EMAIL PROTECTED] Subject: [GENERAL] Delete from Join To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 3:15 PM Hello, Is it possible to use a join keyword in a delete? For example: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; It is not directly mentioned in the delete syntax but the delete refers to the select clause where JOIN is valid. G i have a example delete from t1 a using t2 b where a.id = b.oid A standard way to do it is delete from t1 a where id in (select a.id from t1 a inner join t2 b on (a.id = b.oid)) Thank you for your reply, You are absolutely correct, it IS the standard way. What I am actually trying to do here is write a program to convert MS SQL to Postgres. I have had quite a bit of success so far, but this is a sticking point. Apparently using the JOIN keyword directly in a delete statement is valid in MS. I am trying to determine if it is valid in postgres which I figure it is not but cannot find it exactly in the documentation. So I guess the real question is, can the JOIN keyword be used directly in a delete as above. G i have the same problem. i try use JOIN keyword in DELETE syntax but dont work. I assume cant use JOIN keywork -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection to second database on server
Bill Moran wrote: In response to Hermann Muster [EMAIL PROTECTED]: Hello everyone, I already asked about that a couple of days ago, but didn't get an satisfying solution for my problem which is following: I need to create a view that does a query on a second database on the same PostgreSQL server. dblink seems to be the only (???) solution for doing so. The problems are: Referring to dblink documentation I'll have to hardcode (uaah!!)username and password. 1.) Hence, everyone who could see the view definition e.g. in pgAdmin will be able to read the username and password (for the second database). 2.) If I have multiple postgres users with different rights they will all be treated as that one hard-coded user for the second database when querying the view. Someone suggested to set up a pgpass file so the query can get these dynamically. However a pgpass file is also not secure as username and password are stored in plain text, and problem #2 won't be solved, too. Does anyone have an idea how to better set up a database view for viewing records from another database? MSSQL for instance allows schema prefixes for using other databases of the same server, the current user information is being used to connect to this database as well. I feel this paragraph encapsulates your problem. To summarize: you're doing it wrong. Don't take this as an attack, it's not. It's a statement that PostgreSQL handles this kind of thing differently than MySQL, and if you try to do it the MySQL way, you're going to hit these kinds of problems. The PostgreSQL way to do it is to create schemas within a single database, you can then use roles to set permissions, use search_path to determine what users see by default, and schema-qualify when needed. If you can't migrate your setup to use schemas, then I expect anything else you do will feel sub-optimal, as PostgreSQL is designed to use schemas for this sort of thing. I just found the time to try that out and it worked! Thank you for your help. I actually had no idea about using schemas in PostgreSQL. It was easy to setup and db_link isn't needed anymore. I hope I won't run into anymore problems. :-) Regards.
Re: [GENERAL] pg_ctl start check sum failed
use... ps auxw | grep postgres --- On Wed, 7/2/08, Fernando Dominguez [EMAIL PROTECTED] wrote: From: Fernando Dominguez [EMAIL PROTECTED] Subject: [GENERAL] pg_ctl start check sum failed To: pgsql-general@postgresql.org Date: Wednesday, July 2, 2008, 11:30 PM Hello, I try to use an old cluster into a new system. The new system comes with a newer version of postgres so I uninstalled it and I installed the same version that I had in the older system ---8.1 I got impressed when I Installed the 8.1 with dpkg -i and it started to run without starting the daemon... Is it possible to know what directory is the server using to store the data? --- main question Once I have installed the server I try to start it using pg_control start -D /oldCluster directory but I get FATAL checksum incorrect. I want to use the old data, any ideas? Many thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
[EMAIL PROTECTED] writes: Hi all: I'm trying to create a functional index into column position of token table (see below). I want to make something like: CREATE INDEX token_position_func ON token (position+1); but I get: test=# CREATE INDEX token_position_func test-# ON token (position+1); ERROR: syntax error at or near + LINE 2: ON token (position+1); I think you just need another set of parentheses: CREATE INDEX token_position_func on (token(position+1)) Unless you're on a very old version of Postgres, I think 7.3 which had functional indexes but not expression indexes. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] simple installation problem in windows system
I install PostgreSQL 8.3.3 in Windows XP, and set Path environment viarable to C:\Program Files\PostgreSQL\8.3\bin, when I enter create database mydb; it successed, however when createdb mydb; told me syntax error. Dose the Path set wrong or I need to set other viarables?
Re: [GENERAL] simple installation problem in windows system
On 03/07/2008 14:40, Long Cui wrote: I install PostgreSQL 8.3.3 in Windows XP, and set Path environment viarable to C:\Program Files\PostgreSQL\8.3\bin, when I enter create database mydb; it successed, however when createdb mydb; told me syntax error. Dose the Path set wrong or I need to set other viarables? It sounds as if you're issuing the commands from within psql - hence the standard SQL create database works, but createdb.. won't. createdb is just a wrapper program that sends a create database... command to the server, so you need to run it from the Windows command-line. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
Gregory Stark [EMAIL PROTECTED] writes: CREATE INDEX token_position_func on (token(position+1)) Ooops, I misread that as if token were a function and not the table. Sam Mason had the right syntax. Sorry. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [HACKERS] Switching between terminals
On Thu, 2008-07-03 at 19:56 +0530, cinu wrote: Could anyone please tell me where I am going wrong and if there is a way I can get the same behaviour that I am getting while I am executing the through psql prompt. You're mistake is that you think a transaction is related to your terminal, but it is in fact tied to the psql session you are running... Your first example is running one psql instance per terminal, hence one transaction per terminal, while in your second example the transaction is terminated each time psql finishes to run. Basically what you're asking for is to keep a transaction opened by one session (the first psql execution) and connect to it with the second session (the second psql call) and continue the transaction which was opened by the first one... which I'm pretty sure is wrong to want. It is likely possible to do (using PREPARE TRANSACTION), but even likelier that it is a wrong thing to do in normal circumstances. If you'll say what you really want to do, I bet you'll get a lot more useful advices... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Date Formatting for dd/mm/yyyy
J Ottery wrote: Hi all. I am using Postgres 8.3 with Windows XP Pro. System date format is dd/MM/ Having problems when I writing records to a database with a single 'date' type field. I may be confused here. You say writing was your problem but it sounds like the problem is when you read the records out. The way dates are displayed is dependent on (and configured in) the client not on the server. So if you have a specific format you want your dates displayed then you need to convert them to a string in your query. Artacus -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching between terminals
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote: If you'll say what you really want to do, I bet you'll get a lot more useful advices... Oh, and you should use the general list only for these kind of questions, hackers is for discussion about hacking on the postgres code itself. And cross-posting will also not help too much, the subscribers on hackers which are likely to answer you are subscribed to the general list too. Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching between terminals
cinu [EMAIL PROTECTED] writes: Could anyone please tell me where I am going wrong and if there is a way I can get the same behaviour that I am getting while I am executing the through psql prompt. a) you might try hitting return occasionally in your email :) b) you maybe need to put a SELECT pg_sleep(10) between the two queries in the first file you run so that it hasn't updated both tables and exited before the second one even starts. But I'm just guessing since you haven't sent the actual files you're running. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
Sam Mason [EMAIL PROTECTED] writes: You just want an extra set of brackets; i.e.: CREATE INDEX token_position_func ON token ((position+1)); Should do the trick. Not entirely sure why, but it'll probably have something to do with avoiding ambiguity in the grammar. Right. The problem is the Berkeley-era decision to put index opclasses into the syntax without any keyword or punctuation, viz create index ... on table (column_name [ opclass_name ]); So something like CREATE INDEX token_position_func ON token (a + b); is ambiguous: is the + an infix operator, or is it a postfix operator and the b is to be taken as an opclass name? We hacked around that by requiring parens around expressions. For backwards compatibility with other Berkeley-era syntax, there's a special exception that you can omit the parens when the expression is just a function call. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] High inserting by syslog
Hello all, I have a perl script thats load a entire day squid log to a postgres table. I run it at midnight by cronjob and turns off the indexes before do it (turning it on after). The script works fine, but I want to change this to a diferent approach. I'd like to insert on the fly the log lines, so long it be generated to have the data on-line. But the table has some indexes and the load of lines is about 300.000/day, so the average inserting is 3,48/sec. I think this could overload the database server (i did not test yet), so if I want to create a no indexed table to receive the on-line inserting and do a job moving all lines to the main indexed table at midnight. My question is, Does exists a better solution, or this tatic is a good way to do this? -- Valter Douglas Lisbôa Jr. Sócio-Diretor Trenix - IT Solutions Nossas Idéias, suas Soluções! www.trenix.com.br [EMAIL PROTECTED] Tel. +55 19 3402.2957 Cel. +55 19 9183.4244 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Switching between terminals
On Thu, 2008-07-03 at 16:59 +0200, Csaba Nagy wrote: [snip] It is likely possible to do (using PREPARE TRANSACTION) [snip] I was wrong, you can't do it with that either, see: http://www.postgresql.org/docs/8.2/static/sql-prepare-transaction.html Maybe there is some feature to attach/deattach to/from a session, but I might be just confused... Cheers, Csaba. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection to second database on server
On Thu, Jul 3, 2008 at 7:29 AM, Hermann Muster [EMAIL PROTECTED] wrote: I just found the time to try that out and it worked! Thank you for your help. I actually had no idea about using schemas in PostgreSQL. It was easy to setup and db_link isn't needed anymore. I hope I won't run into anymore problems. :-) Well, it's a short but sharp learning curve, so you'll run into more, but that's why we, and the archives for the list are here... Most of the problems people run into when converting from another database have to do with unlearning different and / or bad habits they learned there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Delete from Join
Gwyneth Morrison [EMAIL PROTECTED] writes: What I am actually trying to get past is: DELETE FROM data_table1 using data_table2 INNER JOIN data_table1 ON data_table1.fkey = data_table2.pkey; The equivalent to that in Postgres would be DELETE FROM data_table1 USING data_table2 WHERE data_table1.fkey = data_table2.pkey; The fundamental issue here is that MSSQL expects the USING clause to contain a second reference to the delete target table, whereas PG does not --- if you write the table name again, that's effectively a self-join and you probably won't get the behavior you want. You can use JOIN syntax in USING in Postgres, but only for situations where the query really involves three or more tables. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Functional index adding one
On Thu, Jul 03, 2008 at 11:11:26AM -0400, Tom Lane wrote: Sam Mason [EMAIL PROTECTED] writes: Not entirely sure why, but it'll probably have something to do with avoiding ambiguity in the grammar. Right. The problem is the Berkeley-era decision to put index opclasses into the syntax without any keyword or punctuation, viz create index ... on table (column_name [ opclass_name ]); So something like CREATE INDEX token_position_func ON token (a + b); is ambiguous: is the + an infix operator, or is it a postfix operator and the b is to be taken as an opclass name? In this case it seems unambiguous, but in general I can see why. Hysterical raisins cause all sorts of fun! We hacked around that by requiring parens around expressions. For backwards compatibility with other Berkeley-era syntax, there's a special exception that you can omit the parens when the expression is just a function call. I'm tempted to say how about putting another example in the page but it seems to document the issue quite well already. I've never liked to putting comments into the interactive version of the manual, mainly because they disappear with each major version, but how about having the manual link to the wiki? At the moment, the wiki seems somewhat isolated and I never seem to find myself using it. If there were some nice trails in, maybe it would be used more; somewhere like: http://wiki.postgresql.org/wiki/Manual/sql-createindex I wouldn't worry about versioning, just let the authors of the pages deal with versioning issues in ways appropriate to the subject. Or has this idea been dismissed before? Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Full text index without accents
You can preprocess text (replace accent by nothing) before to_tsvector or to_tsquery Oleg On Thu, 3 Jul 2008, [EMAIL PROTECTED] wrote: Hi again: I am trying to create a full text configuration to ignore word accents in my searches. My approach is similar to simple dicionary one, but i want to remove accents after converting to lower. Is it the only way to do it to develop another .c and write my own dict_noaccent.c, and then compile and install it into the system? Regars, Mario Barcala 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High inserting by syslog
On Thu, 2008-07-03 at 12:05 -0300, Valter Douglas Lisbôa Jr. wrote: Hello all, I have a perl script thats load a entire day squid log to a postgres table. I run it at midnight by cronjob and turns off the indexes before do it (turning it on after). The script works fine, but I want to change this to a diferent approach. Exactly how do you turn off the indexes? I'd like to insert on the fly the log lines, so long it be generated to have the data on-line. But the table has some indexes and the load of lines is about 300.000/day, so the average inserting is 3,48/sec. I think this could overload the database server (i did not test yet), so if I want to create a no indexed table to receive the on-line inserting and do a job moving all lines to the main indexed table at midnight. My question is, Does exists a better solution, or this tatic is a good way to do this? 300,000 inserts a day isn't that much for any reasonable server. Sincerely, Joshua D. Drake -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High inserting by syslog
Valter Douglas Lisbôa Jr. wrote: Hello all, I have a perl script thats load a entire day squid log to a postgres table. I run it at midnight by cronjob and turns off the indexes before do it (turning it on after). The script works fine, but I want to change this to a diferent approach. I'd like to insert on the fly the log lines, so long it be generated to have the data on-line. But the table has some indexes and the load of lines is about 300.000/day, so the average inserting is 3,48/sec. I think this could overload the database server (i did not test yet), so if I want to create a no indexed table to receive the on-line inserting and do a job moving all lines to the main indexed table at midnight. There are two things to bear in mind. 1. What you need to worry about is the peak rate of inserts, not the average. Even at 30 rows/sec that's not too bad. 2. What will your system do if the database is taken offline for a period? How will it catch up? The limiting factor will be the speed of your disks. Assuming a single disk (no battery-backed raid cache) you'll be limited to your RPM (e.g. 10,000 commits / minute). That will fall off rapidly if you only have one disk and it's busy doing other reads/writes. But, if you batch many log-lines together you need many less commits. So - to address both points above, I'd use a script with a flexible batch-size. 1. Estimate how many log-lines need to be saved to the database. 2. Batch together a suitable number of lines (1-1000) and commit them to the database. 3. Sleep 1-10 secs 4. Back to #1, disconnect and reconnect every once in a while. If the database is unavailable for any reason, this script will automatically feed rows faster when it returns. My question is, Does exists a better solution, or this tatic is a good way to do this? You might want to partition the table monthly. That will make it easier to manage a few years from now. http://www.postgresql.org/docs/current/static/ddl-partitioning.html Also, consider increasing checkpoint_segments if you find the system gets backed-up. Perhaps consider setting synchronous_commit to off (but only for the connection saving the log-lines to the database) http://www.postgresql.org/docs/8.3/static/runtime-config-wal.html -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High inserting by syslog
On Thursday 03 July 2008 13:03:49 Joshua D. Drake wrote: On Thu, 2008-07-03 at 12:05 -0300, Valter Douglas Lisbôa Jr. wrote: Hello all, I have a perl script thats load a entire day squid log to a postgres table. I run it at midnight by cronjob and turns off the indexes before do it (turning it on after). The script works fine, but I want to change this to a diferent approach. Exactly how do you turn off the indexes? DROP/CREATE I'd like to insert on the fly the log lines, so long it be generated to have the data on-line. But the table has some indexes and the load of lines is about 300.000/day, so the average inserting is 3,48/sec. I think this could overload the database server (i did not test yet), so if I want to create a no indexed table to receive the on-line inserting and do a job moving all lines to the main indexed table at midnight. My question is, Does exists a better solution, or this tatic is a good way to do this? 300,000 inserts a day isn't that much for any reasonable server. Sincerely, Joshua D. Drake Thanks -- Valter Douglas Lisbôa Jr. Sócio-Diretor Trenix - IT Solutions Nossas Idéias, suas Soluções! www.trenix.com.br [EMAIL PROTECTED] Tel. +55 19 3402.2957 Cel. +55 19 9183.4244 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High inserting by syslog
On Thursday 03 July 2008, Richard Huxton [EMAIL PROTECTED] wrote: You might want to partition the table monthly. That will make it easier to manage a few years from now. http://www.postgresql.org/docs/current/static/ddl-partitioning.html Definitely pay attention to this point ... it's pretty easy to stuff 300K rows a day into a table but a lot more painful to manage the enormous table that results from doing it for any length of time. -- Alan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High inserting by syslog
Valter Douglas Lisbôa Jr. wrote: Hello all, I have a perl script thats load a entire day squid log to a postgres table. I run it at midnight by cronjob and turns off the indexes before do it (turning it on after). The script works fine, but I want to change this to a diferent approach. I'd like to insert on the fly the log lines, so long it be generated to have the data on-line. But the table has some indexes and the load of lines is about 300.000/day, so the average inserting is 3,48/sec. I think this could overload the database server (i did not test yet), so if I want to create a no indexed table to receive the on-line inserting and do a job moving all lines to the main indexed table at midnight. My question is, Does exists a better solution, or this tatic is a good way to do this? The average matters less than the peak. Unless your traffic is even 24x7, your rate will be higher. If your log is concentrated in an 8-hour workday, your average daytime rate will be closer to 10/second with peaks that are much higher. You might consider some form of buffering between the Squid log and the database to avoid blocking. Your current method has the advantage of moving the database workload to off-hours. Instead of moving data, you might look into partitioning your data. How long do you keep your logs actively available in PostgreSQL? I know one company that partitions their log data into months (parent table with child table for each month). They keep 12-months of data live so they rotate through the child tables. At the start of a month, that month's table is truncated. Modify as appropriate for your load - perhaps a partition (child-table) for each day. Or a current-day child-table that is migrated into a main-table nightly. Either way you can make it appear that the parent-table is an up-to-date complete table. You will need to do some reading on table partitioning if you go this route. Pay special attention to the requirements needed to optimize queries. You might also want to check your stats tables to make sure the indexes you currently maintain are actually used by your queries and remove any that are unnecessary to reduce index-maintenance overhead. Another possible technique would be to have a nightly process that creates partial-indexes. One set of indexes would cover all data prior to midnight and the other set all data after midnight. Depending on the nature of your real-time vs. historical queries, these might even be different indexes. You will have to tweak your queries to make use of your indexes but your live data won't have to update your historical indexes. Warning: the date-constraint in the partial index must be static - you can't do something like ...where squidlog_timestamp current_date Your nightly process will be creating new indexes with a new date-constraint. You might even be able to get away with having no indexes on the current-day's data and just recreate historical indexes nightly (similar to your no-index with nightly-insert). But don't try the above till you determine you have a problem. On modest 3-year-old non-dedicated (also running file-storage, rsync backup, mail...) hardware with basic SATA RAID1 we are handling a similar load without strain. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Memory use in 8.3 plpgsql with heavy use of xpath()
Gregory Stark [EMAIL PROTECTED] writes: That's just a special case of what would be expected to happen with memory allocation anyways though. Few allocators return memory to the OS anyways. Well, that does happen on Linux for instance. Since Matt knew in his original report that the xpath leak was intra-transaction, I assumed he must be using a platform where malloc/free can release memory back to the OS --- else he couldn't have seen that behavior from outside the backend. Still, it's entirely possible that some sort of high-water-mark is involved somewhere, perhaps in malloc's internal data structures. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xml and postgresql
Hi folks, I need to load xml data in to database can you tell me they way how do I import xml data into postgresql database. lets dat this is the xml file bookstore book category=CHILDREN titleHarry Potter/title authorJ K. Rowling/author year2005/year price29.99/price /book book category=WEB titleLearning XML/title authorErik T. Ray/author year2003/year price39.95/price /book /bookstore so finally the table name should be bookstore and column names are category,title, author,year,price and all the information in the xml file should be ported to the table can you please tell me how to do this its a bit confusing to me as i am just a beginner to this. Thanks in advance, Aravind.
Re: [GENERAL] pg_dump - lost synchronization with server: got message type d, length 6036499
Tom Lane wrote: Klint Gore [EMAIL PROTECTED] writes: Tom Lane wrote: Would you be willing to send me a pg_dump -s (ie, just schema no data) dump of this DB? Off-list of course. attached. created with pg_dump 8.3.3 win32 to the 8.3.1 win32 server. Thanks. When I reload this here and try to pg_dump it, I see no evidence of any problem --- pg_dump completes pretty quickly and seems to eat no more than a megabyte or so. I'm not sure what to make of that. Could there be a Windows-specific memory problem in pg_dump? Any more thoughts on this? The file being produced when dumping the table in question is about 1 gig when it crashes. In the last check-in for fe-protocol3.c, there's notes about 1 gig strings so I'm wondering if my data when received by pg_dump (or output by the 8.3.1 server) goes over? 270m of bytea converted to \xxx octal strings would make something 1 gig. But the 270m is spread over 400 rows with nothing bigger than 3m. If there's no interest in researching this any further, then I'll stop the server and take a file system copy of the data directory and upgrade it to 8.3.3 and pray the problem goes away. klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LDAP Authentication
2008-06-29_09:44:01-0400 Taha Ozket [EMAIL PROTECTED]: I have a ldap group, pgsql-developers. I have an user (user1) member of this group; ... How can I change this line for give login permission to pgsql-developers members? If you have pam available, you could do pam authentication, and configure pam_ldap to enforce group membership. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] simple tool for building web forms
2008-07-02_12:22:51-0400 jcvlz [EMAIL PROTECTED]: On Wed, Jul 2, 2008 at 8:35 AM, Roberts, Jon [EMAIL PROTECTED] wrote: http://www.sqlmaestro.com/products/postgresql/ I've used the PHP Code Generator with great success for simple stuff like you describe. You could then write a function to do email notifications or whatever you want. If you're looking to a more complex tool, I would recommend any one of the popular Content Management Systems (CMSs). I've had success with many but have been using Drupal lately, combined with the OSS / flash-based, Flex framework. Thanks guys. I'm familiar with these tools, but they are solving a different problem. I want something *end users* can use to produce their own *simple* web forms. The best solution I've found so far is Zoho Creator: http://creator.zoho.com/ I'm not finding any similar good F/OSS tool in the same space. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LDAP Authentication
2008-07-03_21:17:50-0400 Ron Peterson [EMAIL PROTECTED]: 2008-06-29_09:44:01-0400 Taha Ozket [EMAIL PROTECTED]: I have a ldap group, pgsql-developers. I have an user (user1) member of this group; ... How can I change this line for give login permission to pgsql-developers members? If you have pam available, you could do pam authentication, and configure pam_ldap to enforce group membership. ps - FWIW, I typically make group membership an attribute of the user object itself, rather than maintain groups objects and user objects separately. Primarily because many apps aren't sophisticated enough to deal with having them separated. -- Ron Peterson Network Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso - I wish my computer would do what I want it to do - not what I tell it to do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
