Re: [GENERAL] can't start tsearch2 in 8.2.4
On Mon, Apr 23, 2007 at 12:31:32AM -0400, Tom Lane wrote: [EMAIL PROTECTED] writes: When I try to initiate tsearch2 in 8.2.4, I got the following error. ERROR: incompatible library /usr/local/pgsql/lib/tsearch2.so: missing magic block Apparently you have a pre-8.2 version installed in /usr/local. Update. Perhaps we should add a HINT message with something to the effect of Perhaps it was compiled for an older version. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Missing magic block
On Sun, Apr 22, 2007 at 06:52:33PM -0400, Brad Buran wrote: I'm trying to learn how to write custom extensions to postgres so wrote a basic C function to see how it works. However, I keep getting the following error Missing magic block when I try to add the function to the database. According to the documentation in the manual, all I need to do is add the following: snip And the sql statement I am using is: CREATE FUNCTION add_one(IN int) RETURNS int AS 'add_one' LANGUAGE C; Shouldn't the name of the library appear in there somewhere? Also, you may need to exit and restart psql to get a new session to make sure the new version of the library is loaded. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] ERROR: Failed to build any 5-way joins
This sounds like a known bug in 8.2.x --- it's fixed for 8.2.4. If you can still reproduce it with 8.2.4, please provide the test case. Hi Tom, thanks for the response. I must have missed it, but when was 8.2.4 released? I don't recall any announcement, and the website still says that 8.2.3 is the latest version. it's not officially released yet, but it will be very soon. /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
Re: [GENERAL] Auditing a database
Hello all, On Thu, 19 Apr 2007 09:27:33 -0400 Kenneth Downs [EMAIL PROTECTED] wrote: Ask the question: can I make sure I always have a complete trail? If you insert the old row, you will always have the old values and the table itself holds the new values. In tablelog (http://pgfoundry.org/projects/tablelog/) i decided to have both rows, the old and the new one. So i don't need to lookup the current state in the original table and be able to fetch any data from one single audit table. Germán Hüttemann Arza wrote: Your quoting ... well, sucks ;-) Kind regards -- Andreas 'ads' Scherbaum Deutsche PostgreSQL Usergroup: http://www.pgug.de DPWN: http://ads.wars-nicht.de/blog/categories/18-PWN ---(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] selective export for subsequent import (COPY)
Tom Lane wrote: chrisj [EMAIL PROTECTED] writes: This helped a lot, but ideally I want a tab field delimiter and -F '\t' does not seem to work, any ideas?? I don't think there's any provision for backslash-notation in that switch; you'd need to type an actual tab character there. Depending on what shell you use, that might be a bit difficult on an interactive shell command line, but it should be simple enough to insert one in a script file. I'm not sure what shell is being used, but the following works with bash, csh, tcsh, and ksh under Linux: In order to emit an actual tab character on the shell command line (and ignore any shell auto-completion features that are normally tied to the tab key), preface the literal tab character with Ctrl-V. Thus, the delimiter specification from above would be typed -F 'Ctrl-VTab'. Hope this helps. Andrew ---(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] selective export for subsequent import (COPY)
Wow, how did you discover that? Andrew Kroeger wrote: Tom Lane wrote: chrisj [EMAIL PROTECTED] writes: This helped a lot, but ideally I want a tab field delimiter and -F '\t' does not seem to work, any ideas?? I don't think there's any provision for backslash-notation in that switch; you'd need to type an actual tab character there. Depending on what shell you use, that might be a bit difficult on an interactive shell command line, but it should be simple enough to insert one in a script file. I'm not sure what shell is being used, but the following works with bash, csh, tcsh, and ksh under Linux: In order to emit an actual tab character on the shell command line (and ignore any shell auto-completion features that are normally tied to the tab key), preface the literal tab character with Ctrl-V. Thus, the delimiter specification from above would be typed -F 'Ctrl-VTab'. Hope this helps. Andrew ---(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 -- View this message in context: http://www.nabble.com/selective-export-for-subsequent-import-%28COPY%29-tf3604927.html#a10139682 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] dollar-quoting trouble
On Fri, Apr 20, 2007 at 06:08:36PM +0930, Shane Ambler wrote: If you do want 2 versions installed (both in different prefix dirs) at the same time then you probably want to make sure that your shell $PATH setting includes the path to the newer version of psql and then specify the full path to the older version of psql when you want to use the older version. Or just use the full path every time you run psql. Thanks, I knew that much from following the list. However, I was under the impression that - on Debian - by using update-alternatives I could set the default PG to the 8.1 install. Which apparently wasn't the case. Thanks for your answer, though, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PQerrorMessage: suppress trailing new line?
Hi, the messages returned by PQerrorMessage() contain a trailing new line. So, they doesn't nicely integrate into strings formatted by printf. Is there a way to suppress the trailing new line (except for alloc/copy to a new string)? Thank You Felix ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] selective export for subsequent import (COPY)
On Monday 23 April 2007 14:56, chrisj wrote: | Wow, how did you discover that? man readline? search for quoted-insert Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Indirect access to data, given table name as a string
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 04/21/07 20:31, Arthaey Angosii wrote: I want to allow notes on any row in any table in my database. A table row may have multiple notes. Say my tables are foo, bar, and qux. I want to avoid having a lookup table for each of them (foo_notes, bar_notes, and qux_notes). Is there a standard way of solving this problem? Not knowing SQL all that well, I thought that maybe I could have a notes table: CREATE TABLE notes ( id integer primary key, table_name regclass not null, row_id integer not null, note text not null ); But I have no idea how I could use notes.table_name and notes.row_id to relate (table_name.id = row_id) to notes.note. I've looked a little bit at information_schema and the system catalog, but I haven't found any examples of what I'm trying to do, so I don't know if I'm on the right track here. Any suggestions would be appreciated! For this kind of scheme, add a note_id integer to each relevant main table, and then have your notes table look like this: CREATE TABLE notes ( id integer, row_id smallint not null, note text not null, primary key (id, row_id) ); You'll also need an id_master table to keep track of the next id. (A serial datatype would not work because the PK is compound.) This makes sense to me. I hope I was able to elucidate it clearly. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFGLMmAS9HxQb37XmcRAgMLAKCgc/bEq8GlqZPeGooeRopxLcilQACfalUb mUV+4cZ3lv6Bas5KGuoKLUE= =MEQY -END PGP SIGNATURE- ---(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] Help debugging a hung postgresql client connection
Hi, We have a Python application that spawns multiple threads the write the the database. We are seeing an intermittent problem where one of the threads will block seemingly for ever. We saw a similar problem twice with the same signature in a C++ based program as well (but that seems harder to reproduce). This is PostgreSQL 8.1.8-1 on a Fedora Core 5 system (x86_64 arch). The PostgreSQL library used is the Python pgdb library. The The backtrace for the client program is: #0 0x00321e6c3086 in poll () from /lib64/libc.so.6 #1 0x003379c0f775 in pqSocketCheck (conn=0x61f070, forRead=1, forWrite=0, end_time=-1) at fe-misc.c:1039 #2 0x003379c0f870 in pqWaitTimed (forRead=Variable forRead is not available. ) at fe-misc.c:913 #3 0x003379c0e792 in PQgetResult (conn=0x61f070) at fe-exec.c: 1186 #4 0x003379c0e86e in PQexecFinish (conn=0x61f070) at fe-exec.c: 1415 #5 0x2e91b341 in pgsource_execute (self=0x2ea4da50, args=Variable args is not available. ) at pgmodule.c:534 #6 0x0032283954a0 in PyEval_EvalFrame () from /usr/lib64/ libpython2.4.so.1.0 The process is connected to the database on the local machine. The connection information in ps says idle in transaction. The backtrace for the postgresql backend process at that time: (gdb) bt #0 0x00321e6cc5e5 in recv () from /lib64/libc.so.6 #1 0x005004f6 in secure_read (port=0xa0e0f0, ptr=0x7dc8c0, len=8192) at /usr/include/bits/socket2.h:35 #2 0x00506114 in pq_recvbuf () at pqcomm.c:697 #3 0x00506537 in pq_getbyte () at pqcomm.c:738 #4 0x005688b7 in PostgresMain (argc=4, argv=0x9ef7d8, username=0x9ef7a0 user) at postgres.c:289 #5 0x005422fb in ServerLoop () at postmaster.c:2865 #6 0x00543234 in PostmasterMain (argc=5, argv=0x9ec590) at postmaster.c:941 #7 0x00507ebe in main (argc=5, argv=0x1a) at main.c:265 There does not appear to be any deadlock: select * from pg_locks; locktype| database | relation | page | tuple | transactionid | classid | objid | objsubid | transaction | pid | mode | granted ---+--+--+--+--- +---+-+---+--+-+--- +-+-nsactionid | | | | | 74260015 | | | |74260015 | 16855 | ExclusiveLock | t relation | 217529 | 1247 | | | | | | |74260015 | 16855 | AccessShareLock | t transactionid | | | | | 74531807 | | | |74531807 | 10377 | ExclusiveLock | t relation | 217529 |10342 | | | | | | |74695161 | 22791 | AccessShareLock | t transactionid | | | | | 74695161 | | | |74695161 | 22791 | ExclusiveLock | t relation | 217529 | 218724 | | | | | | |74260015 | 16855 | AccessShareLock | t (6 rows) \q libpq is built with thread-safety as far as I can tell - #define ENABLE_THREAD_SAFETY 1 in /usr/include/pg_config_x86_64.h Any suggestions for what else I can try to narrow down the problem? I found a couple of similar problems on the archives, but no solution that I could apply. Please let me know if there is any other information that can be collected to help debug this and I will collect it the next time the problem is see. Any suggestions appreciated. Thanks in advance, Venkat ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Mass Update
On Apr 23, 4:25 am, Iain Adams [EMAIL PROTECTED] wrote: Hi, This is probably a really easy question but I have been toiling with this SQL for ages. Okay I have a table with geom_id and id in, this table is called temp. I also have another table called junctions with a column, old_id. Now old_id corresponds to geom_id. I want to update the junctions table column id with the id held in temp where the geom_id column matches the old_id column. I would really appreciate some help on this. I just cant seem to get it right. Thanks Iain update junctions set id = (select id from temp where temp.geom_id = junctions.old_id AND temp.id junctions.id); This assumes that their is no more then 1 cooresponding geom_id for each old_id. Roger ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] The directory of the postgresql source
where can I get the help of the directory explantation to the postgresql src? I have to do some work about the definition of the internal functions. who can help me ? thankyou! -- View this message in context: http://www.nabble.com/The-directory-of-the-postgresql-source-tf3629629.html#a10135088 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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] Mass Update
Hi, This is probably a really easy question but I have been toiling with this SQL for ages. Okay I have a table with geom_id and id in, this table is called temp. I also have another table called junctions with a column, old_id. Now old_id corresponds to geom_id. I want to update the junctions table column id with the id held in temp where the geom_id column matches the old_id column. I would really appreciate some help on this. I just cant seem to get it right. Thanks Iain ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help debugging a hung postgresql client connection
Venkatraju T.V. [EMAIL PROTECTED] writes: The backtrace for the postgresql backend process at that time: (gdb) bt #0 0x00321e6cc5e5 in recv () from /lib64/libc.so.6 #1 0x005004f6 in secure_read (port=0xa0e0f0, ptr=0x7dc8c0, len=8192) at /usr/include/bits/socket2.h:35 #2 0x00506114 in pq_recvbuf () at pqcomm.c:697 #3 0x00506537 in pq_getbyte () at pqcomm.c:738 #4 0x005688b7 in PostgresMain (argc=4, argv=0x9ef7d8, username=0x9ef7a0 user) at postgres.c:289 This backend is waiting for input from the client; it doesn't think it's in the middle of an operation. It looks to me like this is a client-side problem, unless you want to believe that the network lost the last response sent to the client. libpq is built with thread-safety as far as I can tell - #define ENABLE_THREAD_SAFETY 1 in /usr/include/pg_config_x86_64.h Thread-safety or not, libpq does not by itself provide any interlocking to make it safe for multiple threads to use the same PQconn. A multi-thread client needs to either use a separate DB connection per thread, or introduce its own mutex for a shared PQconn. If you're not doing either of those, this is probably a problem with multiple threads messing up the state of the PQconn. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] tsearch2 dictionary that indexes substrings?
* Oleg Bartunov [EMAIL PROTECTED] [20070420 11:32]:
If I understand it correctly such a dictionary would require to write
a custom C component - is that correct? Or could I get away with
writing a plpgsql function that does the above and hooking that
somehow into the tsearch2 config?
You need to write C-function, see example in
http://www.sai.msu.su/~megera/postgres/fts/doc/fts-intdict-xmp.html
Thanks.
My colleague who speaks more C than me came up with the code below
which works fine for us. Will the memory allocated for lexeme be freed
by the caller?
Til
/*
* Dictionary for partials of a word, ie. foo = {f,fo,foo}
*
* Based on the tsearch2/gendict/config.sh generator
*
* Author: Sean Treadway
*
* This code is released under the terms of the PostgreSQL License.
*/
#include postgres.h
#include dict.h
#include common.h
#include subinclude.h
#include ts_locale.h
#define is_utf8_continuation(c) ((unsigned char)(c) = 0x80 (unsigned
char)(c) = 0xBF)
PG_FUNCTION_INFO_V1(dlexize_partial);
Datum dlexize_partial(PG_FUNCTION_ARGS);
Datum
dlexize_partial(PG_FUNCTION_ARGS) {
char* in = (char*)PG_GETARG_POINTER(1);
char* utxt = pnstrdup(in, PG_GETARG_INT32(2)); /* palloc */
char* txt = lowerstr(utxt);/* palloc */
inttxt_len = strlen(txt);
intresults = 0;
inti = 0;
/* may overallocate, that's ok */
TSLexeme *res = palloc(sizeof(TSLexeme)*(txt_len+1));
for (i = 1; i = txt_len; i++) {
/* skip UTF8 control codes until EOS */
if (!is_utf8_continuation(txt[i])) {
res[results++].lexeme = pnstrdup(txt, i);
}
}
res[results].lexeme=NULL;
pfree(utxt);
pfree(txt);
/* Receiver must free res memory and res[].lexeme */
PG_RETURN_POINTER(res);
}
---(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] Setting table ids in slony
I want to start using slony for replication, and have a question about setting table IDs when creating replication sets. The docs say that you have to be careful in what IDs you assign to the tables - if there's a relationship between two tables, the parent needs to have a lower ID. So let's take two tables, users and articles. Users: id serial primary key name varchar(80) Articles: id serial primary key title varchar(80) body text user_id (foreign key on users.id) If I'm using slony to replicate this db, then I need to give the users table an id of 1, and articles should be 2, right? What happens when I add a table that's higher up on the heirarchy? For example now I want to add a companies table. Companis: id serial primary key name varchar(80) Users: company_id (fkey on companies.id) Now would I have to reassign IDs for the tables? companies-1, users-2, articles-3? Thanks for any help. Pat ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: Failed to build any 5-way joins
On 21/04/2007 23:41, Tom Lane wrote: This sounds like a known bug in 8.2.x --- it's fixed for 8.2.4. If you can still reproduce it with 8.2.4, please provide the test case. Hi Tom, I've tried it on 8.2.4 and all seems to be well now. Thanks! Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] using Postgres with Sequoia
I am investigating data replication and failover ideas for a Java web application. I would like the ability to have 2 or more Postgres databases that are kept in synch, with the ability to loose all but 1 database and have the application still function. It seems that Sequoia would work nicely for this. Was wondering if anyone here has had difficulties using Sequoia and Postgres together. Would also love to hear success stories about this strategy. Thanks, -M@ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Problem with encoding (lower() doesn't work)
Steps : export LANG=ru_RU.KOI8-r (locale was set properly) initdb createdb -E WIN tst psql tst tsearch2.sql psql tst : SET client_encoding to KOI8; create table tst (name text, v tsvector); insert .. update tst set v = to_tsvector(lower(nm)); and i've got capitalized russian text in v (vectors) :( the select * from lower(); isnt working too I've tried Postgres 8.1 and 8.2 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] change format of logging statements?
Postgres 8.2.0 is logging statements with variables like $1, $2, etc. and then on the next line saying: DETAIL: parameters: $1 = '100', $2 = '100', $3 = '1003' Is it possible to get statements logged with the parameters placed into the actual query statement so that its more convenient to copy and paste them into psql while debugging? The reason for this is that Hibernate is creating the queries and I'd like to see exactly what those queries are returning. Thanks, -M@ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] change format of logging statements?
Matthew Hixson wrote: Postgres 8.2.0 is logging statements with variables like $1, $2, etc. and then on the next line saying: DETAIL: parameters: $1 = '100', $2 = '100', $3 = '1003' Is it possible to get statements logged with the parameters placed into the actual query statement so that its more convenient to copy and paste them into psql while debugging? The reason for this is that Hibernate is creating the queries and I'd like to see exactly what those queries are returning. Not really. You could probably use PREPARE the query as first logged and EXECUTE with the given paramters instead of interpolating the parameters in the query itself. It might be easier. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Missing magic block
Hi Martijn, Thank you very much for the suggestion: CREATE FUNCTION add_one(IN int) RETURNS int AS 'add_one' LANGUAGE C; I corrected this to say: AS 'Project1', 'add_one' And restarted psql (rebooted for that matter as well) and am still getting the same error. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] ERROR: Failed to build any 5-way joins
On Sun, 2007-04-22 at 17:50, Raymond O'Donnell wrote: On 21/04/2007 23:41, Tom Lane wrote: This sounds like a known bug in 8.2.x --- it's fixed for 8.2.4. If you can still reproduce it with 8.2.4, please provide the test case. Hi Tom, thanks for the response. I must have missed it, but when was 8.2.4 released? I don't recall any announcement, and the website still says that 8.2.3 is the latest version. I'll download 8.2.4 tomorrow and report back on how I get on If I remember correctly, after a few where's this new release snafus, the order of the day now is to silently release the new version, announcing it on -hackers only, until all the ftp mirrors have updated, then to announce it publicly when all the copies have been made. I think. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ERROR: Failed to build any 5-way joins]
On 23/04/2007 23:17, Scott Marlowe wrote: If I remember correctly, after a few where's this new release snafus, the order of the day now is to silently release the new version, announcing it on -hackers only, until all the ftp mirrors have updated, then to announce it publicly when all the copies have been made. g Makes sense! :) R. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] Slow query using simple equality operators
Can anybody explain to me why this query is executing so slow? =# explain select s_content,textdir from text_search where path_id='1' AND tb_id='P2_TB1'; QUERY PLAN - Bitmap Heap Scan on text_search (cost=39861.94..59743.55 rows=5083 width=36) Recheck Cond: ((path_id = 1) AND (tb_id = 'P2_TB1'::text)) - BitmapAnd (cost=39861.94..39861.94 rows=5083 width=0) - Bitmap Index Scan on idx_search_path_id (cost=0.00..16546.09 rows=1016571 width=0) Index Cond: (path_id = 1) - Bitmap Index Scan on idx_search_tb_id (cost=0.00..23315.60 rows=1016571 width=0) Index Cond: (tb_id = 'P2_TB1'::text) (7 rows) Thanks in advance! Benjamin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PQerrorMessage: suppress trailing new line?
On 4/24/07, Felix Kater [EMAIL PROTECTED] wrote: Hi, the messages returned by PQerrorMessage() contain a trailing new line. So, they doesn't nicely integrate into strings formatted by printf. Is there a way to suppress the trailing new line (except for alloc/copy to a new string)? The alloc kind of suggests that you're using C ... is that correct? Either way - please be a bit more specific as to what you're trying to achieve by which means. Thank You Felix Cheers, Andrej ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] where clause help
i have a record set like below: num_prods|num_open_issues|num_provisioned|num_canceled 1|0|1|0 2|0|0|2 3|0|1|1 2|0|1|1 1|0|01 2|0|0|0 3|3|0|0 3|0|0|3 3|1|0|2 3|2|0|1 2|0|2|0 Of the list above only row 3 and row 6 should be returned. Plain english definition: With a result set like above eliminate all rows that should not show up on the provision List. Provision List Definition: All rows that have products that need provisioning. Provisioning means its NOT canceled and it does NOT have an open issue. Some facts: num_open_issues + num_provisioned + num_canceled will never be more than num_prods. no individual column will ever be more than num_prods. thanks! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow query using simple equality operators
To follow up on my own email, by disabling BitmapScan in my postgresql.conf the performance seems to be better. Is something wrong with the query analyzer? v=# explain select s_content,textdir from text_search where path_id='1' AND tb_id='P2_TB1'; QUERY PLAN Index Scan using idx_search_path_id on text_search (cost=0.00..4081857.23 rows=5083 width=36) Index Cond: (path_id = 1) Filter: (tb_id = 'P2_TB1'::text) (3 rows) Benjamin On Apr 23, 2007, at 3:38 PM, Benjamin Arai wrote: Can anybody explain to me why this query is executing so slow? =# explain select s_content,textdir from text_search where path_id='1' AND tb_id='P2_TB1'; QUERY PLAN -- --- Bitmap Heap Scan on text_search (cost=39861.94..59743.55 rows=5083 width=36) Recheck Cond: ((path_id = 1) AND (tb_id = 'P2_TB1'::text)) - BitmapAnd (cost=39861.94..39861.94 rows=5083 width=0) - Bitmap Index Scan on idx_search_path_id (cost=0.00..16546.09 rows=1016571 width=0) Index Cond: (path_id = 1) - Bitmap Index Scan on idx_search_tb_id (cost=0.00..23315.60 rows=1016571 width=0) Index Cond: (tb_id = 'P2_TB1'::text) (7 rows) Thanks in advance! Benjamin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Slow query using simple equality operators
Benjamin Arai [EMAIL PROTECTED] writes: To follow up on my own email, by disabling BitmapScan in my postgresql.conf the performance seems to be better. Is something wrong with the query analyzer? I just rewrote choose_bitmap_and() to fix some issues that might be related to this; please try your query with 8.2.4 or 8.1.9 as the case may be (and next time, mention what version you're using right off the bat). regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] PG service restart failure (start getting ahead of stop?)
We have a nightly restart of one PG database. Today it failed and I can't seem to understand why and how to prevent this in the future (nor can I reproduce the problem). We have a line in a shell script that calls /etc/init.d/postgresql restart. In the shell script's log from this invocation I have: Stopping postgresql service: [FAILED] Starting postgresql service: [ OK ] The outcome of this was that the service was not running (despite the [ OK ] on the second line). The query log from that run ends with: 2007-04-23 03:03:59 PDT [23265] LOG: received fast shutdown request 2007-04-23 03:03:59 PDT [23265] LOG: aborting any active transactions 2007-04-23 03:03:59 PDT [26749] FATAL: terminating connection due to administrator command ... snipped more lines like the one above ... 2007-04-23 03:03:59 PDT [24090] LOG: could not send data to client: Broken pipe 2007-04-23 03:03:59 PDT [26700] FATAL: terminating connection due to administrator command ... snipped more lines like the one above ... 2007-04-23 03:04:13 PDT [26820] FATAL: the database system is shutting down ... snipped more lines like the one above ... 2007-04-23 03:06:10 PDT [23269] LOG: shutting down 2007-04-23 03:06:10 PDT [23269] LOG: database system is shut down 2007-04-23 03:06:13 PDT [23267] LOG: logger shutting down end of log So it looks like the STOPPING of the service actually succeeded, albeit it took a while (more than the usual sessions open?). The STARTING is the one that actually failed (is that because the STOP was still in process?). The question is why -- in a RESTART situation wouldn't/shouldn't the START part wait for the STOP part to complete (regardless of how long it takes)? Also what can we do to avoid this in the future? We can issue a separate STOP and then a START X minutes later, but how long an X? It would seem that a RESTART is really what I want... TIA, George ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Slow query using simple equality operators
Hi, I upgraded to 8.2.4 but there was no significant change in performance. I did notice that hte query appears to be executed incorrectly. Specifically, it appears to perform each equality operation then perform a bitwise AND. I think it should instead be performing one of the equalities then use the results to perform the other. This would create a vastly smaller dataset for the second to work with. I have pasted the EXPLAIN ANALYZE below to illustrate: =# explain analyze select s_content,textdir from (SELECT * from text_search WHERE tb_id='P2_TB1') AS a where path_id='4'; QUERY PLAN - Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083 width=36) (actual time=7418.651..7418.863 rows=52 loops=1) Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB1'::text)) - BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual time=6706.928..6706.928 rows=0 loops=1) - Bitmap Index Scan on idx_search_path_id (cost=0.00..16546.34 rows=1016571 width=0) (actual time=6609.458..6609.458 rows=52777 loops=1) Index Cond: (path_id = 4) - Bitmap Index Scan on idx_search_tb_id (cost=0.00..23315.85 rows=1016571 width=0) (actual time=96.903..96.903 rows=411341 loops=1) Index Cond: (tb_id = 'P2_TB1'::text) Total runtime: 7419.128 ms (8 rows) Is there are way to force the Bitmap Index Scan on idx_search_tb_id to perform first then let Bitmap Index Scan on idx_search_path_id use the results? Benjamin On Apr 23, 2007, at 5:12 PM, Tom Lane wrote: Benjamin Arai [EMAIL PROTECTED] writes: To follow up on my own email, by disabling BitmapScan in my postgresql.conf the performance seems to be better. Is something wrong with the query analyzer? I just rewrote choose_bitmap_and() to fix some issues that might be related to this; please try your query with 8.2.4 or 8.1.9 as the case may be (and next time, mention what version you're using right off the bat). regards, tom lane
Re: [GENERAL] where clause help
where num_prods num_open_issues + num_provisioned + num_canceled if those columns are nullable (which they don't seem to be) you'd have to convert the NULLs (i.e. coalesce(num_canceled,0) ) -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ketema Sent: Monday, April 23, 2007 4:21 PM To: pgsql-general@postgresql.org Subject: [GENERAL] where clause help i have a record set like below: num_prods|num_open_issues|num_provisioned|num_canceled 1|0|1|0 2|0|0|2 3|0|1|1 2|0|1|1 1|0|01 2|0|0|0 3|3|0|0 3|0|0|3 3|1|0|2 3|2|0|1 2|0|2|0 Of the list above only row 3 and row 6 should be returned. Plain english definition: With a result set like above eliminate all rows that should not show up on the provision List. Provision List Definition: All rows that have products that need provisioning. Provisioning means its NOT canceled and it does NOT have an open issue. Some facts: num_open_issues + num_provisioned + num_canceled will never be more than num_prods. no individual column will ever be more than num_prods. thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] where clause help
Man so simple! is your solution the same as: num_provisioned num_products AND (num_open_issues + num_provisioned + num_canceled) num_prods which is what i finally came up with ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG service restart failure (start getting ahead of stop?)
George Pavlov wrote: We have a nightly restart of one PG database. Today it failed and I can't seem to understand why and how to prevent this in the future (nor can I reproduce the problem). If you get the PID of the psql server process then invoke a STOP, then have a loop waiting for that PID to disappear, then run your START, it may do what you want. Or someone may have a more elegant solution :-) Brent Wood We have a line in a shell script that calls /etc/init.d/postgresql restart. In the shell script's log from this invocation I have: Stopping postgresql service: [FAILED] Starting postgresql service: [ OK ] The outcome of this was that the service was not running (despite the [ OK ] on the second line). The query log from that run ends with: 2007-04-23 03:03:59 PDT [23265] LOG: received fast shutdown request 2007-04-23 03:03:59 PDT [23265] LOG: aborting any active transactions 2007-04-23 03:03:59 PDT [26749] FATAL: terminating connection due to administrator command ... snipped more lines like the one above ... 2007-04-23 03:03:59 PDT [24090] LOG: could not send data to client: Broken pipe 2007-04-23 03:03:59 PDT [26700] FATAL: terminating connection due to administrator command ... snipped more lines like the one above ... 2007-04-23 03:04:13 PDT [26820] FATAL: the database system is shutting down ... snipped more lines like the one above ... 2007-04-23 03:06:10 PDT [23269] LOG: shutting down 2007-04-23 03:06:10 PDT [23269] LOG: database system is shut down 2007-04-23 03:06:13 PDT [23267] LOG: logger shutting down end of log So it looks like the STOPPING of the service actually succeeded, albeit it took a while (more than the usual sessions open?). The STARTING is the one that actually failed (is that because the STOP was still in process?). The question is why -- in a RESTART situation wouldn't/shouldn't the START part wait for the STOP part to complete (regardless of how long it takes)? Also what can we do to avoid this in the future? We can issue a separate STOP and then a START X minutes later, but how long an X? It would seem that a RESTART is really what I want... TIA, George ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] where clause help
Ketema wrote: i have a record set like below: num_prods|num_open_issues|num_provisioned|num_canceled 1|0|1|0 2|0|0|2 3|0|1|1 * 2|0|1|1 1|0|0|1 2|0|0|0 * 3|3|0|0 3|0|0|3 3|1|0|2 3|2|0|1 2|0|2|0 Of the list above only row 3 and row 6 should be returned. Plain english definition: With a result set like above eliminate all rows that should not show up on the provision List. Provision List Definition: All rows that have products that need provisioning. Provisioning means its NOT canceled and it does NOT have an open issue. If I understand this correctly, we start with: where num_cancelled num_prods and num_open_issues num_prods Some facts: num_open_issues + num_provisioned + num_canceled will never be more than num_prods. no individual column will ever be more than num_prods. Then in addition to this, we also only retrieve records where: num_open_issues + num_provisioned + num_canceled num_prods and num_open_issues num_prods (already there above, no need to have it twice) and num_provisioned num_prods and num_canceled num_prods (already there above, no need to have it twice) giving the query: select * from table where num_open_issues num_prods and num_provisioned num_prods and num_canceled num_prods and (num_open_issues + num_provisioned + num_canceled) num_prods; With (I think) the result of: records 1,11 fail as num_provisioned is not num_prods records 2,8 fail as num_cancelled is not num_prods record 3 passes all constraints records 4,5,9, 10 fail as num_open_issues + num_provisioned + num_canceled is not num_prods record 6 passes all constraints record 7 fails as num_open_issues is not num_prods Is this what you were after? Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] where clause help
Ketema [EMAIL PROTECTED] writes: Man so simple! is your solution the same as: num_provisioned num_products AND (num_open_issues + num_provisioned + num_canceled) num_prods which is what i finally came up with This can be simplified to num_open_issues + num_provisioned + num_canceled num_prods, without the AND and the other statement. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] [pgsql] inherits and foreign key problem
Dear all, I have some tables like below, and now i have one record for each in simple_texts, simple_directories and side_types. Now I want to insert one record into sides (from simple_directory to simple_text with side_type), but it report error: can not insert or update sides, against foreign key sides_from_fkey. detail: key (from)=(2) is not present in table base_nodes. How does this happen? How to resolve? Thanks. --- CREATE TABLE base_nodes ( id bigserial NOT NULL UNIQUE ); CREATE TABLE name_descriptions ( name varchar (256) NOT NULL PRIMARY KEY, description text NOT NULL, UNIQUE (id) ) INHERITS (base_nodes); CREATE TABLE side_types ( UNIQUE (id), PRIMARY KEY (name) ) INHERITS (name_descriptions); CREATE TABLE sides ( from int8 NOT NULL REFERENCES base_nodes (id) ON DELETE CASCADE, to int8 NOT NULL REFERENCES base_nodes (id) ON DELETE CASCADE, type int8 NOT NULL REFERENCES side_types (id) ON DELETE CASCADE, PRIMARY KEY (from, to, type) ) INHERITS (base_nodes); CREATE TABLE users ( password char (128) NOT NULL, UNIQUE (id), PRIMARY KEY (name) ) INHERITS (name_descriptions); CREATE TABLE simple_texts ( context text NOT NULL, UNIQUE (id), PRIMARY KEY (name) ) INHERITS (name_descriptions); CREATE TABLE simple_directories ( UNIQUE (id), PRIMARY KEY (name) ) INHERITS (name_descriptions); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PG service restart failure (start getting ahead of stop?)
George Pavlov [EMAIL PROTECTED] writes: We have a nightly restart of one PG database. Just out of curiosity, what for? I can't imagine any really good reason for just shutting down the postmaster and immediately restarting it. So it looks like the STOPPING of the service actually succeeded, albeit it took a while (more than the usual sessions open?). The STARTING is the one that actually failed (is that because the STOP was still in process?). The question is why -- in a RESTART situation wouldn't/shouldn't the START part wait for the STOP part to complete (regardless of how long it takes)? Well, this'd depend on the details of the postgres init script you're using, which you gave no hint about (and yes, there are a *ton* of different versions out there). The one I'm currently shipping for Red Hat would give up waiting after a minute, but it should report failure not success in that case. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Major differences between 7.1.1 and 7.4.x
Hi , can any one tell me the major difference between 7.1.1 and 7.4.x version of postgresql, also please tell me what are things to considered when upgrading postgresql from 7.1.1 to 7.4.x. Please reply me.. Thanks Regards J Mageshwaran ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! . ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Regarding WAL
Hi , I want to do replication using WAL , please tell the methods by which log shipping is done ie moving the wal files to slaves and executing it. ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Complete Coverage of the ICC World Cup '07! Log on to www.sify.com/khel for latest updates, expert columns, schedule, desktop scorecard, photo galleries and more! Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com For the Expert view of the ICC World Cup log on to www.sify.com/khel. Read exclusive interviews with Sachin, Ganguly, Yuvraj, Sreesanth, Expert Columns by Gavaskar, Web chat with Dhoni and more! . ---(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] Major differences between 7.1.1 and 7.4.x
Mageshwaran wrote: Hi , can any one tell me the major difference between 7.1.1 and 7.4.x version of postgresql, also please tell me what are things to considered when upgrading postgresql from 7.1.1 to 7.4.x. http://www.postgresql.org/docs/8.2/static/release.html Why are you only upgrading to 7.4 ? If you can go further and up to 8.2.x. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Major differences between 7.1.1 and 7.4.x
On 4/24/07, Tom Lane [EMAIL PROTECTED] wrote: Why in the world are you considering an upgrade from a six-year-old release to a three-year-old release? Do yourself a favor and move onto something more or less modern. If 8.2.4 is too bleeding-edge for you, then 8.1.9. He thought about it ... http://archives.postgresql.org/pgsql-general/2007-03/msg01041.php Cheers, Andrej ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] [pgsql] inherits and foreign key problem
am Tue, dem 24.04.2007, um 10:30:15 +0800 mailte Magicloud Magiclouds folgendes: Dear all, I have some tables like below, and now i have one record for each in simple_texts, simple_directories and side_types. Now I want to insert one record into sides (from simple_directory to simple_text with side_type), but it report error: can not insert or update sides, against foreign key sides_from_fkey. detail: key (from)=(2) is not present in table base_nodes. How does this happen? How to resolve? Quotation from http://www.postgresql.org/docs/current/interactive/ddl-inherit.html : A serious limitation of the inheritance feature is that indexes (including unique constraints) and foreign key constraints only apply to single tables, not to their inheritance children. This is true on both the referencing and referenced sides of a foreign key constraint. 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 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] ERROR: Failed to build any 5-way joins
I must have missed it, but when was 8.2.4 released? I don't recall any announcement, and the website still says that 8.2.3 is the latest version. I'll download 8.2.4 tomorrow and report back on how I get on If I remember correctly, after a few where's this new release snafus, the order of the day now is to silently release the new version, announcing it on -hackers only, until all the ftp mirrors have updated, then to announce it publicly when all the copies have been made. I think. correct. the wait is also for the official binary distributions to be packaged. /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
Re: [GENERAL] Major differences between 7.1.1 and 7.4.x
Mageshwaran schrieb: Hi , can any one tell me the major difference between 7.1.1 and 7.4.x version of postgresql, also please tell me what are things to considered when upgrading postgresql from 7.1.1 to 7.4.x. Please reply me.. All your questions are precicely answered on the postgresql.org web site. The differences between versions and revisions are in the release notes of 7.4 (direct link on the front page) http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-17 The other question regarding WAL replication (which isnt probably not fully what you want, but a start) http://www.postgresql.org/docs/8.2/static/continuous-archiving.html Regards Tino ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
