[GENERAL] index not used in joins
Hello all,
i have a problem with index usage and joins.
Attached is some SQL demonstrating my problem;
Why is the index only used in the 2nd query?
Can anybody explain me how to avoid/fix this.
Thanks in advance
Sebastian
CREATE TABLE users (
login NAME NOT NULL PRIMARY KEY,
datum TIMESTAMP,
version INTEGER
);
CREATE TABLE test (
datum TIMESTAMP NOT NULL,
version INTEGER NOT NULL,
approved TIMESTAMP
);
CREATE OR REPLACE VIEW v AS
SELECT t.*
FROM test AS t
INNER JOIN users AS u ON
t.datum = u.datum AND
(t.version = u.version OR
t.approved IS NOT NULL);
CREATE OR REPLACE FUNCTION fill () RETURNS BOOLEAN AS '
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE ''INSERT INTO test (datum,version)
VALUES (now(),''|| i || '')'';
END LOOP;
RETURN TRUE;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION approved () RETURNS BOOLEAN AS '
DECLARE
i INTEGER;
BEGIN
FOR i IN 1..1000 LOOP
EXECUTE ''INSERT INTO test (datum,version,approved)
VALUES (now(),''|| i || '',now())'';
END LOOP;
RETURN TRUE;
END;
' LANGUAGE plpgsql;
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT fill();
SELECT approved();
INSERT INTO users (login,datum,version) VALUES ('sb',now(),'999');
CREATE INDEX test_ ON test (datum);
CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT
NULL;
ANALYZE;
EXPLAIN ANALYZE SELECT * FROM v;
EXPLAIN ANALYZE SELECT t.* FROM test AS t INNER JOIN users AS u ON t.datum =
u.datum AND (t.version = '999' OR t.approved IS NOT NULL);
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] table with sort_key without gaps
Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
On Thu, Dec 09, 2004 at 18:32:19 +0100,
Janning Vygen [EMAIL PROTECTED] wrote:
id should be positive
id should not have gaps within the same account
id should start counting by 1 for each account
i cant use sequences because they are producing gaps and doesn't start
counting by 1 for each account and i dont want to use postgresql array
type for various reasons.
for this model to function you need a lot of sophisticated plpgsql
function to insert, move or delete entries to keep
I doubt you want to use this model if you are going to be deleting records.
Sometimes i am going to delete records. Then i would call a trigger ON DELETE
which moves all other entries to the right place.
- did anyone implemented a table like this and wrote some custom
functions/triggers for inserting, deleting, moving and so on? If yes it
would be nice if he/she is willing to sahre the code with me.
If you aren't deleting records and you don't have a lot of concurrent
requests, you can lock the table and select the current max id for an
account and add 1 to get the next id for for that account.
Updates and deletes are very seldom, but i still dont want to lock the table.
- did anyone implemented a table like this and came to the conclusion
that this shouldn't be done for any reasons out of my sight? (i don't
bother about updating a primary key)
Why are you doing this? Normally uniqness of an ID is good enough. If you
don't need to worry about gaps, you could use one sequence for the entire
table to generate IDs.
maybe your are right. But with Sequences i thought to have problems when i do
inserts in the middle of the sorting array. I need to move all current rows
out of the way to insert a new one. Insert a row at id 3 i need to do
UPDATE mytable SET id = -(id + 1) WHERE id = 3;
UPDATE mytable SET id = -(id) WHERE id 0;
INSERT INTO mytable VALUES (3);
-- UPDATE mytable SET id = id + 1 WHERE id = 3;
-- doesnt work in pgsql if id is a primary key
but with sequences i just have to push my sequence counter up, too. Right?
SELECT nextval('mytable_id_seq');
ok, it should work with sequences, too. I will try it. but isn't there a ready
to use model which explains and avoids problems like the one with the update
statement above?
kind regards
janning
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] table with sort_key without gaps
Hi, On Mon, 2004-12-13 at 10:58 +0100, Janning Vygen wrote: Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III: On Thu, Dec 09, 2004 at 18:32:19 +0100, Janning Vygen [EMAIL PROTECTED] wrote: id should be positive id should not have gaps within the same account id should start counting by 1 for each account i cant use sequences because they are producing gaps and doesn't start counting by 1 for each account and i dont want to use postgresql array type for various reasons. ... ok, it should work with sequences, too. I will try it. but isn't there a ready to use model which explains and avoids problems like the one with the update statement above? Well, to get an idea on what you want to do here, maybe you use a sheet of paper, a red and a blue pen (to simulate two concurrent requests) and do step by step your inserts but do each step for every color: 1 blue 2 red 3 blue 4 red and so on. See to where it leads if you have to maintain the gaplessness. Sometimes you can design the application differently to get what you want at the end. Maybe you can expand a bit on your requirements and what the reason behind these is? Regards Tino ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] index not used in joins
Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). EXPLAIN ANALYZE SELECT * FROM v; QUERY PLAN --- Nested Loop (cost=0.00..263.12 rows=116 width=20) (actual time=5.171..109.910 rows=1020 loops=1) Join Filter: ((inner.version = outer.version) OR (inner.approved IS NOT NULL)) - Seq Scan on users u (cost=0.00..1.01 rows=1 width=12) (actual time=0.005..0.009 rows=1 loops=1) - Index Scan using test_ on test t (cost=0.00..155.74 rows=7092 width=20) (actual time=0.012..64.873 rows=21000 loops=1) Index Cond: (t.datum = outer.datum) Total runtime: 111.879 ms EXPLAIN ANALYZE SELECT t.* FROM test AS t INNER JOIN users AS u ON t.datum = u.datum AND (t.version = '999' OR t.approved IS NOT NULL); QUERY PLAN - Nested Loop (cost=0.00..7.78 rows=133 width=20) (actual time=0.035..7.733 rows=1020 loops=1) - Seq Scan on users u (cost=0.00..1.01 rows=1 width=8) (actual time=0.006..0.010 rows=1 loops=1) - Index Scan using test_999 on test t (cost=0.00..5.11 rows=132 width=20) (actual time=0.017..3.358 rows=1020 loops=1) Index Cond: (t.datum = outer.datum) Filter: ((version = 999) OR (approved IS NOT NULL)) Total runtime: 9.528 ms OK - so what you want to know is why index test_999 is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] index not used in joins
Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what you want to know is why index test_999 is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. But what if the table users contains only 1 row and the column version has a value of 999? Are there any other options to speed up this kind of query? Thanks so far Sebastian ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] index not used in joins
Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what you want to know is why index test_999 is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. But what if the table users contains only 1 row and the column version has a value of 999? It still doesn't know that the only value in version is 999(*). Let's say there were 2000 rows and 1900 had the value 999 - the index is still useless because we'd have to do a sequential scan to check the remaining 200 rows. Are there any other options to speed up this kind of query? Well, your problem is the (version=X OR approved IS NOT NULL) clause. I must admit I can't quite see what this is supposed to do. The test table connects to the users table via version (and datum, though not a simple check) unless the test has been approved, in which case it applies to all users? Can you explain what the various tables/columns are really for? (*) Don't forget the statistics for column values are usually out-of-date compared to the actual data, so you can't rely on it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] index not used in joins
Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Sebastian Böck wrote: Richard Huxton wrote: Can you post the output from your explain analyse calls too? The statistics aren't going to be the same on different machines. Sure, here it is. Thanks. (PS - remember to cc the list too). [output of EXPLAIN ANALYZE] OK - so what you want to know is why index test_999 is used in the second but not the first, even though both return the same rows. The fact is that the conditional index: CREATE INDEX test_999 ON test (datum) WHERE version = '999' OR approved IS NOT NULL; AFAIK looks at the WHERE clause of your query to determine where it can run. Don't forget that the planner needs to pick which index is best *before* it starts fetching data. So - in the first example there might be rows where e.g. t.version=998 which means test_999 would be a poor choice of index. But what if the table users contains only 1 row and the column version has a value of 999? It still doesn't know that the only value in version is 999(*). Let's say there were 2000 rows and 1900 had the value 999 - the index is still useless because we'd have to do a sequential scan to check the remaining 200 rows. Are there any other options to speed up this kind of query? Well, your problem is the (version=X OR approved IS NOT NULL) clause. I must admit I can't quite see what this is supposed to do. The test table connects to the users table via version (and datum, though not a simple check) unless the test has been approved, in which case it applies to all users? Can you explain what the various tables/columns are really for? The whole thing is a multiuser facility managment application. Every user can plan things like he wants (different versions). All these changes apply to a common (approved) version. Things get complicated as everybody should be able to travel through the history via the datum field. That's why i need this silly OR in my where-clause. At the moment i get very exciting results using immutable functions, but i have another question. In the docs it is stated that: IMMUTABLE indicates that the function always returns the same result when given the same argument values; What if i define my functions like: CREATE OR REPLACE FUNCTION datum () RETURNS TIMESTAMP AS ' SELECT datum FROM public.benutzer; ' LANGUAGE sql IMMUTABLE; They normally (untill now) give the correct results, also if the values in the underlaying view changes. Can i relay on this or is it only luck. (*) Don't forget the statistics for column values are usually out-of-date compared to the actual data, so you can't rely on it. I'm aware of that. Thanks Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Temporary tables and disk activity
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: One of the things on the TODO list is making the size of temp-table buffers user-configurable. (Temp table buffers are per-backend, they are not part of the shared buffer arena.) With a large temp-table arena we'd never need to write to the kernel in the first place. Right now you could manually increase the #define that sets it, but it would not pay to make it very large because the management algorithms are very stupid (linear scans). That has to be fixed first :-( I assume you mean your TODO list because the official one has no mention of this. Doesn't it? We've surely discussed the problem enough times, eg http://archives.postgresql.org/pgsql-hackers/2002-08/msg00380.php http://archives.postgresql.org/pgsql-hackers/2002-09/msg01368.php or for that matter here's Vadim complaining about it seven years ago: http://archives.postgresql.org/pgsql-hackers/1997-12/msg00215.php OK, added: * Allow the size of the buffer cache used by temporary objects to be specified as a GUC variable Larger local buffer cache sizes requires more efficient handling of local cache lookups. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Select after insert to the unique column
If you attempted the inserts within a single transaction and any of them fail, they will all fail. The server will automatically undo any and all changes made by the transaction, and any further steps in the transaction will simply result in the error message you are getting. You will not be able to (successfully) issue any further database commands until you end the transaction and start a new one. On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote: On Wed, Dec 08, 2004 at 14:50:04 +0100, Julian Legeny [EMAIL PROTECTED] wrote: Hello, Then I want to process command select count(*) from UNIQUE_COLUMN_TEST that I want to know how many records was already inserted before id faied. But when I try to process that SELECT COUNT(*), there is error occured again: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block How can I solve this? Depending on what you really want to do, you could do each insert in its own transaction. If you don't want any of the inserts to succeed if there are problems, then you should do the counting in the application doing the inserts. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster --- Frank D. Engel, Jr. [EMAIL PROTECTED] $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep John 3:16 John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] subscribe missing?
When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. Apache/1.3.33 Server at webmail.anachronic.net Port 80 Thank you, Jim Apsey
Re: [GENERAL] disabling OIDs?
Title: Re: disabling OIDs? For what it's worth, OIDs are required if you ever want to use updateable cursors with the ODBC driver. We discovered this the hard way. Mark Dexter
Re: [GENERAL] subscribe missing?
On Mon, 13 Dec 2004, Jimmie H. Apsey wrote: When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. How/where did you subscribe from? We made some changes this past weekend to deal with some issues that were reported, so the URL should be http://mail.postgresql.org/mj/mj_wwwusr now, where it used to be http://webmail... I've just fixed the links from the archives, which will become live within the next 30-40 minutes or so ... so if that is where you were seeing it, then that's done ... Anywhere else, please let us know ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Complex data types like BYTEA in embedded SQL with ecpg
I cannot find any information on how to handle the more unusual or complex data types in embedded SQL with C. The only data type treated in the docs is VARCHAR which is handled by the proprocessor. Especially I am interested in handling BYTEA in embedded SQL. How is it declared? -- With kind regards | Mit freundlichen Gruessen Hans-Michael Stahl Condat AG Alt-Moabit 91d | 10559 Berlin | Germany | Old Europe tel: +49.30.3949-1155 | fax: +49.30.3949-2221155 http://www.condat.de/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Substring question
I am trying to select a part of a text field based on a regular expression, the data looks like this Rv0001c_f Rv0002_r Rv1003c_r Etc I would like to be able to select like this (this is a regular expression I would do in perl) SELECT substring(primer_name, '(\w+)\d\d\d\d[c]*_[fr]$') from primer; Is it possible to do this in SQL? Thanks for any help adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] subscribe missing?
When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. How/where did you subscribe from? We made some changes this past weekend to deal with some issues that were reported, so the URL should be http://mail.postgresql.org/mj/mj_wwwusr now, where it used to be http://webmail... I've just fixed the links from the archives, which will become live within the next 30-40 minutes or so ... so if that is where you were seeing it, then that's done ... Anywhere else, please let us know ... Not sure where he got that one from, but that URL is in all the thousands of downloads og pginstaller, per your own instructions ;-) can we get a redirect fromt he old server? Also, is this change permanent so we should update the installer? Perhaps we should put a generic redirect page that will always be there and redirect to the proper location, if it's expected to change? //Magnus ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] table with sort_key without gaps
Am Montag, 13. Dezember 2004 17:37 schrieb Bruno Wolff III:
On Mon, Dec 13, 2004 at 10:58:25 +0100,
Janning Vygen [EMAIL PROTECTED] wrote:
Am Samstag, 11. Dezember 2004 20:06 schrieb Bruno Wolff III:
maybe your are right. But with Sequences i thought to have problems when
i do inserts in the middle of the sorting array. I need to move all
current rows out of the way to insert a new one. Insert a row at id 3 i
need to do
UPDATE mytable SET id = -(id + 1) WHERE id = 3;
UPDATE mytable SET id = -(id) WHERE id 0;
INSERT INTO mytable VALUES (3);
-- UPDATE mytable SET id = id + 1 WHERE id = 3;
-- doesnt work in pgsql if id is a primary key
but with sequences i just have to push my sequence counter up, too.
Right?
Sequences should really only be used to obtain unique values. It is
dangerous to assume any other semantics other than that within a session
the values returned by nextval TO THAT SESSION will monotonically increase.
SELECT nextval('mytable_id_seq');
ok, it should work with sequences, too. I will try it. but isn't there a
ready to use model which explains and avoids problems like the one with
the update statement above?
You still haven't told us why you want to remove the gaps in the id.
Unless you have some business reason for doing that, you shouldn't be
doing that. If you told us what the business reason for doing that is,
then we may be able to give you some better suggestions.
ok, i have users which wants to manage their sporting competitions which
(simplified) has games and fixtures (in german Spieltage, i hope the word
fixtures is understandable). Like German Bundesliga has 9 games on
Spieltag 1, 7 on saturday and two on sunday.
So i have a table:
CREATE TABLE spieltage (
account text NOT NULL,
sort int4 NOT NULL,
name text NOT NULL
PRIMARY KEY (account, sort),
UNIQUE (account, name)
)
and another table (which is not interesting here) with games having a foreign
key referencing spieltage(account, sort). Of course every spieltag has a
unique name but needs more important a sort column.
I need to have sort as a primary key or at least a unique key (which is nearly
the same) because many other tables should reference the (primary or
candidate) key (account, sort) for the main reason that i can easily sort
other tables according to the sort column without the need to make a join.
updating/inserting/deleting to the table spieltage takes happen very seldom,
but it should be possible.
When i have three rows and i want to insert one row between sort 1 and sort
2 i have to move all columns by one.
sample data when using one sequence for sort column
account | sort
--
acc1| 1
acc1| 2
acc2| 3
acc2| 4
acc1| 5
now i insert VALUES ('acc1', 2) i need to move all existing rows out of the
way.
ah, as i am writing i understand my problem:
i CAN say:
SELECT nextval('spieltage_sort_seq'); -- i might move a column to currval
UPDATE spieltage SET sort = -(sort + 1) WHERE account = 'acc1' and sort = 2;
UPDATE spieltage SET sort = -(sort) WHERE account = 'acc1' and sort 0;
INSERT INTO spieltage VALUES ('acc1', 3);
right?
because the duplicate sort column value '3' after moving isnt a problem
because of the two-column primary key which only enforces uniquness of
(account, sort)
the other reason why i wanted gapless sequences was that i would love to use
the id in an URL. But this is easy to manage to translate a positional id in
an URL to the database id.
ok. I think i am going to use sequences. But after all i am wondering to find
so little stuff for this common problem. Lots of people have tables which
have a sort column (example: top ten lists) but i guess normally the sort
column is NOT the primary key.
kind regards
janning
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] High volume inserts - more disks or more CPUs?
At 12:16 AM 12/13/2004 -0600, Guy Rouillier wrote: (3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace? Fast inserts = fast writes. RAID5 = slower writes. You may wish to consider mirroring and striping aka RAID10... With a 4 disk array, reads could be 4X faster and writes could be 2X faster compared to single disk (assuming decent RAID controllers or RAID software). For the same number of disks, RAID5 would be slower than RAID10 but RAID5 will have more storage capacity. RAID10 would have slightly better redundancy - if 2 out of 4 of the right disks fail, you could still have all your data :). If the insert performance is more important then go for more disks over more CPU. If the read queries are more important than the insert performance AND the queries are likely to fit within RAM, then more CPU could be better. If you're not doing lots of fancy queries and the queries don't fit in 16GB, then go for the 50% more disks (6 vs 4). But I'm not a DB consultant ;). Regards, Link. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] table with sort_key without gaps
On Mon, Dec 13, 2004 at 19:37:41 +0100, Janning Vygen [EMAIL PROTECTED] wrote: ok, i have users which wants to manage their sporting competitions which (simplified) has games and fixtures (in german Spieltage, i hope the word fixtures is understandable). Like German Bundesliga has 9 games on Spieltag 1, 7 on saturday and two on sunday. So i have a table: CREATE TABLE spieltage ( account text NOT NULL, sort int4 NOT NULL, name text NOT NULL PRIMARY KEY (account, sort), UNIQUE (account, name) ) and another table (which is not interesting here) with games having a foreign key referencing spieltage(account, sort). Of course every spieltag has a unique name but needs more important a sort column. I need to have sort as a primary key or at least a unique key (which is nearly the same) because many other tables should reference the (primary or candidate) key (account, sort) for the main reason that i can easily sort other tables according to the sort column without the need to make a join. updating/inserting/deleting to the table spieltage takes happen very seldom, but it should be possible. For this emaxmple, I suggest considering using a numeric column for doing the sorting. You can initial load it with integer values in a number of ways. When you need to insert a new row with a value between two existing rows you can use the fractional part of the sort value to give you an apropiate value without having to modify existing rows. It doesn't sound like you need to worry about renumbering after deletions, since gaps shouldn't cause a problem in the sort order. For the actual reports, the application can number the records consecutively as they are returned rather than displaying the sort column values. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Temporary tables and disk activity
Phil Endecott [EMAIL PROTECTED] writes: Does this make sense? I imagine that the temporary table is being added to these tables and then removed again. Yes, a temp table has the same catalog infrastructure as a regular table, so creation and deletion of a temp table will cause some activity in those catalogs. I thought you were concerned about the data within the temp table, though. I do have quite a large number of tables in the database; I have one schema per user and of the order of 20 tables per user and 200 users. I can imagine that in a system with fewer tables this would be insignificant, yet in my case it seems to be writing of the order of a megabyte in each 5-second update. That seems like a lot. How often do you create/delete temp tables? I should mention that I ANALYSE the temporary table after creating it and before using it for anything; I'm not sure if this does any good but I put it in as it couldn't do any harm. This is a good idea (if you analyze after filling the table) ... but it will cause catalog traffic too, because again the pg_statistic rows go into the regular pg_statistic catalog. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Substring question
Michael Fuhr [EMAIL PROTECTED] writes: On Mon, Dec 13, 2004 at 06:17:27PM +, Adam Witney wrote: I would like to be able to select like this (this is a regular expression I would do in perl) Remember that the backslash (\) already has a special meaning in PostgreSQL string literals. To write a pattern constant that contains a backslash, you must write two backslashes in the statement. Is that what you're after? Also, our regular expression engine is based on Tcl's, which has some subtle differences from Perl's. I believe this particular regexp would act the same in both, but if you are a regexp guru you might run into things that act differently. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SELECTing on age
I'm attempting to select records from my postgresql database using php
based on whether someone is at least 17 years old on the date of a
particular visit.
My sql is:
$db_sql = SELECT * from list WHERE ((visit_date - birth_date) = 17)'
$db_result = db_exec($db_sql)
$num = pg_num_rows($db_result);
for($i = 0; $i $num; $i++)
{
$data = pg_num_rows($db_result,$i)
$visit_date = $data[visit_date];
$birth_date = $data[birth_date];
echo Visit date[$visit_date] Birth date[$birth_date];
}
The problem I'm having is that the the query is returning results for
some people with ages 17 (most of them are correct, just a couple of
incorrect ones interspersed with the correct ones that are over 17)?
For example, my output contains:
Visit date[2004-07-14] Birth date[2004-02-19]
and
Visit date[2004-08-11] Birth date[2003-04-21]
which are clearly people who are 17.
Any suggestions on how to track down this problem or rework the query so
it always works correctly? If I reverse the query and look for people
17, I don't get any that are older than 17.
Thanks,
Bruce
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SELECTing on age
On Mon, 2004-12-13 at 15:15, Kall, Bruce A. wrote:
I'm attempting to select records from my postgresql database using php
based on whether someone is at least 17 years old on the date of a
particular visit.
My sql is:
$db_sql = SELECT * from list WHERE ((visit_date - birth_date) = 17)'
$db_result = db_exec($db_sql)
$num = pg_num_rows($db_result);
for($i = 0; $i $num; $i++)
{
$data = pg_num_rows($db_result,$i)
$visit_date = $data[visit_date];
$birth_date = $data[birth_date];
echo Visit date[$visit_date] Birth date[$birth_date];
}
The problem I'm having is that the the query is returning results for
some people with ages 17 (most of them are correct, just a couple of
incorrect ones interspersed with the correct ones that are over 17)?
For example, my output contains:
Visit date[2004-07-14] Birth date[2004-02-19]
and
Visit date[2004-08-11] Birth date[2003-04-21]
which are clearly people who are 17.
Check out what this query tells you:
postgres=# select ('2004-07-31'::date-'2004-07-01'::date);
?column?
--
30
Notice how the output of subtracting one date from another is an int for
the number of days? A better way would be:
select * from table1 where dt now()-'17 years'::interval;
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [ADMIN] plperl loading
Marek Lewczuk wrote: I've made some tests with plperl and I see that when plperl function is executed for the first time, then it takes much more time. I know that this is a shared library problem - is there a way to preload plperl every connection or maybe I can build plperl into postgresql source ? See: http://www.postgresql.org/docs/current/static/runtime-config.html and search for preload_libraries HTH, Joe ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] subscribe missing?
When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. How/where did you subscribe from? We made some changes this past weekend to deal with some issues that were reported, so the URL should be http://mail.postgresql.org/mj/mj_wwwusr now, where it used to be http://webmail... I've just fixed the links from the archives, which will become live within the next 30-40 minutes or so ... so if that is where you were seeing it, then that's done ... Anywhere else, please let us know ... Not sure where he got that one from, but that URL is in all the thousands of downloads og pginstaller, per your own instructions ;-) can we get a redirect fromt he old server? difficult, as the config files are regenerated from a script ... *but* ... I've set it up right now and will try to remember whenever I do run the scirpt to fix it ... Also, is this change permanent so we should update the installer? Perhaps we should put a generic redirect page that will always be there and redirect to the proper location, if it's expected to change? Yes, this is permanent ... I should have set it to mail originally, since mail will always be where majordomo itself resides, where, like I just changed, webmail doesn't even have to be on the same machin :( Ok. I've updated pginstaller to use mail.postgresql.org. Will be in the next rc.. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Possible dump/restore bug
It seems that upon dump restore, UPPER indexes either aren't recreated correctly or not listed somewhere the query analyzer can know it exist. I've encountered first encountered this problem doing an upgrade to 7.3.7 to 7.4.6. I again encountered this program replicating a server (same 7.4.6 on both source dest). Dropping the index and creating it again seems to fix the issue. This server isn't slated to go live for another few weeks so I can leave it in this semi-crippled state for some debugging/testing if needed. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] disabling OIDs?
I don't know why they use OID's for cursors. But I do know that if you run a trace the SQL that creates the cursor uses OID's, so it doesn't work if the table is created without OID's. Also, if you want to have updateable cursors against views (i.e., a view with rules for INSERT, UPDATE, and DELETE), you must name the OID and CTID as columns in the view. Again, we learned this the hard way. Mark Dexter -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, December 13, 2004 12:06 PM To: Mark Dexter Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: disabling OIDs? Mark Dexter [EMAIL PROTECTED] writes: For what it's worth, OIDs are required if you ever want to use updateable cursors with the ODBC driver. We discovered this the hard way. Mark Dexter That's unfortunate. Is it because it's difficult to track down the primary key of the table? Is it any easier to track down the primary key of the table in 8.0? It would be much better if it checked the primary key and used that instead of OIDs. Though I'm unclear implementing updateable cursors in the client-end is really a good idea. I suppose it's nice if you understand the limitations inherent. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] disabling OIDs?
Mark Dexter [EMAIL PROTECTED] writes: For what it's worth, OIDs are required if you ever want to use updateable cursors with the ODBC driver. We discovered this the hard way. Mark Dexter That's unfortunate. Is it because it's difficult to track down the primary key of the table? Is it any easier to track down the primary key of the table in 8.0? It would be much better if it checked the primary key and used that instead of OIDs. Though I'm unclear implementing updateable cursors in the client-end is really a good idea. I suppose it's nice if you understand the limitations inherent. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Temporary tables and disk activity
Phil Endecott [EMAIL PROTECTED] writes: What would happen if I were to rollback at the end of the transaction, rather than committing (having made no changes)? Would that eliminate some or all of the catalog writes? It would avoid fsync'ing the changes at commit time, but not really reduce the write volume per se. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Possible dump/restore bug
Certainly did analyze. Here's the query plans. Note the non-UPPER query uses an indexscan just fine. INFO: analyzing public.fin_vendors INFO: fin_vendors: 4207 pages, 3000 rows sampled, 63063 estimated total rows ANALYZE talisman=# explain analyze select * from fin_vendors where name like 'NBC%'\g Index Scan using idx_finvendors_name on fin_vendors (cost=0.00..4.01 rows=1 width=600) (actual time=0.029..0.036 rows=2 loops=1) Index Cond: ((name = 'NBC'::bpchar) AND (name 'NBD'::bpchar)) Filter: (name ~~ 'NBC%'::text) Total runtime: 0.087 ms (4 rows) talisman=# explain analyze select * from fin_vendors where UPPER(name) like 'NBC%'\g Seq Scan on fin_vendors (cost=0.00..5310.60 rows=316 width=600) (actual time=18.080..104.956 rows=2 loops=1) Filter: (upper((name)::text) ~~ 'NBC%'::text) Total runtime: 105.061 ms (3 rows) I can confirm Postgres thinks there's an index somewhere in the system already. Note that none of these indexes were created by hand so it is not a fat-finger error. talisman=# create index idx_finvendors_upper_name on fin_vendors (upper(name))\g ERROR: relation idx_finvendors_upper_name already exists Since I don't want to drop these seemingly broken indexes just yet, I'll recreate the index by using a new name: talisman=# create index test_upper on fin_vendors (upper(name))\g CREATE INDEX talisman=# analyze fin_vendors\g ANALYZE talisman=# explain analyze select * from fin_vendors where upper(name) like 'NBC%'\g Index Scan using test_upper on fin_vendors (cost=0.00..616.68 rows=316 width=604) (actual time=0.032..0.039 rows=2 loops=1) Index Cond: ((upper((name)::text) = 'NBC'::text) AND (upper((name)::text) 'NBD'::text)) Filter: (upper((name)::text) ~~ 'NBC%'::text) Total runtime: 0.096 ms (4 rows) Tom Lane wrote: William Yu [EMAIL PROTECTED] writes: It seems that upon dump restore, UPPER indexes either aren't recreated correctly or not listed somewhere the query analyzer can know it exist. Seems unlikely. Perhaps you forgot to ANALYZE after reloading? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Performance differences 7.1 to 7.3
Hello all, I have just loaded Postgresql 7.3.6-7 onto a new server on the recommendation of Tom Lane. It is part of Red Hat AS 3. I have Postgresql 7.1.3-5 running on Red Hat AS 2.1. I have a simple view from which I select on both systems. The 7.3.6-7 version requires 18+ seconds to do a select from a particular view. The 7.1.3-5 version requires 3+ seconds to select from the same view. On the 7.1.3-5 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -cselect count(*) from tpv; count --- 33377 (1 row) 0.000u 0.010s 0:03.55 0.2%0+0k 0+0io 332pf+0w [EMAIL PROTECTED] ~]$ And on 7.3.6-7 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -cselect count(*) from tpv; count --- 33377 (1 row) 0.010u 0.000s 0:18.38 0.0%0+0k 0+0io 362pf+0w [EMAIL PROTECTED] ~]$ Does anyone have any clues as to where I should be looking for tuning/whatever? Jim Apsey -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] table with sort_key without gaps
Yeah, that suggestion sounds good as long as you ensure that the sort column has sufficient precision to handle the in-between values. I would suggest checking for value-above and value-below when inserting, then using their midpoint. In the event that there is no value-above, add some integer number to the last used value, preferably 1 (maybe 4, for example), to help avoid the possibility of running out of precision. You might have a maintenance query which could go through and renumber the sort order. In other words, SELECT * FROM spieltage ORDER BY sort; then for each row in the result, re-insert it with a new value for the sort order, increasing by integer values of 4, or whatever. This could be run once-in-a-while to help avoid precision problems, assuming that you will actually have enough updates to consider this an issue. Note: You should probably copy the table into a temp table, delete from the original, then read the data from the temp while inserting into the original, then drop the temp table -- all of this within a single transaction, of course... On Dec 13, 2004, at 2:08 PM, Bruno Wolff III wrote: On Mon, Dec 13, 2004 at 19:37:41 +0100, Janning Vygen [EMAIL PROTECTED] wrote: ok, i have users which wants to manage their sporting competitions which (simplified) has games and fixtures (in german Spieltage, i hope the word fixtures is understandable). Like German Bundesliga has 9 games on Spieltag 1, 7 on saturday and two on sunday. So i have a table: CREATE TABLE spieltage ( account text NOT NULL, sort int4 NOT NULL, name text NOT NULL PRIMARY KEY (account, sort), UNIQUE (account, name) ) and another table (which is not interesting here) with games having a foreign key referencing spieltage(account, sort). Of course every spieltag has a unique name but needs more important a sort column. I need to have sort as a primary key or at least a unique key (which is nearly the same) because many other tables should reference the (primary or candidate) key (account, sort) for the main reason that i can easily sort other tables according to the sort column without the need to make a join. updating/inserting/deleting to the table spieltage takes happen very seldom, but it should be possible. For this emaxmple, I suggest considering using a numeric column for doing the sorting. You can initial load it with integer values in a number of ways. When you need to insert a new row with a value between two existing rows you can use the fractional part of the sort value to give you an apropiate value without having to modify existing rows. It doesn't sound like you need to worry about renumbering after deletions, since gaps shouldn't cause a problem in the sort order. For the actual reports, the application can number the records consecutively as they are returned rather than displaying the sort column values. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly --- Frank D. Engel, Jr. [EMAIL PROTECTED] $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep John 3:16 John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] subscribe missing?
On Mon, 13 Dec 2004, Magnus Hagander wrote: When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. How/where did you subscribe from? We made some changes this past weekend to deal with some issues that were reported, so the URL should be http://mail.postgresql.org/mj/mj_wwwusr now, where it used to be http://webmail... I've just fixed the links from the archives, which will become live within the next 30-40 minutes or so ... so if that is where you were seeing it, then that's done ... Anywhere else, please let us know ... Not sure where he got that one from, but that URL is in all the thousands of downloads og pginstaller, per your own instructions ;-) can we get a redirect fromt he old server? difficult, as the config files are regenerated from a script ... *but* ... I've set it up right now and will try to remember whenever I do run the scirpt to fix it ... Also, is this change permanent so we should update the installer? Perhaps we should put a generic redirect page that will always be there and redirect to the proper location, if it's expected to change? Yes, this is permanent ... I should have set it to mail originally, since mail will always be where majordomo itself resides, where, like I just changed, webmail doesn't even have to be on the same machin :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Possible dump/restore bug
William Yu [EMAIL PROTECTED] writes: It seems that upon dump restore, UPPER indexes either aren't recreated correctly or not listed somewhere the query analyzer can know it exist. Seems unlikely. Perhaps you forgot to ANALYZE after reloading? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Performance differences 7.1 to 7.3
I assume that the schema is identical on both systems. After running vacuum on both systems [for each of the underlying tables in tpv], what does explain say about the queries? Are the shared memory buffers identical on both systems? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jimmie H. Apsey Sent: Monday, December 13, 2004 2:43 PM To: [EMAIL PROTECTED] Subject: [GENERAL] Performance differences 7.1 to 7.3 Hello all, I have just loaded Postgresql 7.3.6-7 onto a new server on the recommendation of Tom Lane. It is part of Red Hat AS 3. I have Postgresql 7.1.3-5 running on Red Hat AS 2.1. I have a simple view from which I select on both systems. The 7.3.6-7 version requires 18+ seconds to do a select from a particular view. The 7.1.3-5 version requires 3+ seconds to select from the same view. On the 7.1.3-5 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -cselect count(*) from tpv; count --- 33377 (1 row) 0.000u 0.010s 0:03.55 0.2%0+0k 0+0io 332pf+0w [EMAIL PROTECTED] ~]$ And on 7.3.6-7 version I do: [EMAIL PROTECTED] ~]$ time /usr/bin/psql mpt -cselect count(*) from tpv; count --- 33377 (1 row) 0.010u 0.000s 0:18.38 0.0%0+0k 0+0io 362pf+0w [EMAIL PROTECTED] ~]$ Does anyone have any clues as to where I should be looking for tuning/whatever? Jim Apsey -- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Multiple foreign keys on same field
I'm in a situation where it would be useful to bind a field in a table via foreign keys to N other tables simultaneously. The table holds a common type of info which all those other tables use. The many tables refer to the common table by keeping references to its serial field. By doing this, I could ensure that when a row in any of the many tables is deleted or updated, the effect travels to the common table. So far, I've been successful in defining more than one foreign key on the same field in the lone table, tied to fields in different tables. (I half expected it not to work though). However, it seems that inserting values in the commons table is a showstopper: it expects that field value to exists not in only one, but in ALL bound tables simultaneously. Is it possible to work around this issue? Perhaps by telling it not to check for such things when rows are inserted in the common table? I expect that would break the whole referential integrity thingy, but that would be useful right about now. Really, I don't want the entire referential integrity thing, I just want the automatic delete/update half. -- Ciprian Popovici ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Corrupt RTREE index
Tom Lane [EMAIL PROTECTED] writes: I wonder if it's actually corrupt, or if it's just that the index semantics don't truly match the operator. If the latter, REINDEXing won't fix it. I think the index always worked properly in the past. But of course it would be hard to tell if that was really true. As for the first theory, have you had any database crashes lately? If so I'd write this off as a failure caused by the lack of WAL-logging support in rtree. Ugh. I have had a couple system crashes recently. I kind of doubt the index was in the process of being written to, I don't tend to watch Farscape at the same time as doing development work... But I can't guarantee it. So you don't think this case is worth doing forensics on? I didn't think @ was broken ... but I might have missed something. I didn't think @ was broken either. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Corrupt RTREE index
Greg Stark [EMAIL PROTECTED] writes: So you don't think this case is worth doing forensics on? If the problem goes away after REINDEX then I'll write it off as missing WAL support. rtree is not high enough on my list of priorities to justify more effort :-( regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] increasing max_connections on freebsd
Hello... I want to increase my max_connections up to 128 connections, but I got that I need to recompile my kernel. I'm newbie in postgresql and freebsd. How to increase max_connections and recompile freebsd kernel. Help Me please. Thank's __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Ridiculous load
On Sat, 11 Dec 2004 10:44:59 -0600, Wes wrote: On 12/9/04 9:23 AM, Peter Haworth [EMAIL PROTECTED] wrote: It runs RHEL ES v3, kernel 2.4.21-20.ELsmp It's generally a very stable box which runs a number of postgresql instances. But last night we hit very high low averages - 10+, vs the normal 0-2. The culprit appeared to be kswapd, which was using huge amounts of cpu. I'd like to know why! There were some major changes in swap management somewhere in the linux 2.4 kernel (also RH 3.0 ES). I don't off hand remember exactly which level. Under a heavy I/O load, we also saw kswapd going nuts with the 2.4.21 kernel, destroying system performance. The only solution we found was to upgrade to a 2.6 kernel. We've now upgraded to RedHat's 2.4.21-25.ELsmp, which seems to have fixed things. -- Peter Haworth [EMAIL PROTECTED] Back then, I remember wondering how come he accepted my offer instead of doing something sensible like, say, running away screaming. -- Yanick Champoux, on being a Perl Golf referee ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Cannot drop template1
Greetings all, I'm trying to drop template1 so that I can re-create it from template0 - I made the mistake of loading a whole pile of crud into template1 (luckily it's a test installation on my WinXP Pro laptop). However, when using - dropdb -U postgres -W template1 at the command line, all I get back is dropdb: database removal failed: ERROR: cannot drop the currently open database. This is even after a cold start; the DBMS service has only just been started and is doing no other work whatsoever - there are no other connections to template1 or indeed any other database. I've tried connecting to a different database and dropping template1 from there, only to be told that I cannot drop a template database. I've also tried connecting to template0, but was told that template0 is not accepting connections. Any suggestions will be appreciated! --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Sheduler in Postgres
Traditionally, PostgreSQL has consciously omitted such things where they would merely be replicating existing operating system functionality. On Unix, cron is the traditional service that provides this functionality. I think there's a port to Windows NT, so you could presumably use that if you haven't got any more native job scheduler. -- output = reverse(moc.liamg @ enworbbc) http://www.ntlug.org/~cbbrowne/lsf.html How should I know if it works? That's what beta testers are for. I only coded it. (Attributed to Linus Torvalds, somewhere in a posting) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] increasing max_connections on freebsd
You might want to check some of these sites: http://www.silverwraith.com/papers/freebsd-kernel.php http://www.freebsd.org/doc/en_US.ISO8859-1/books/handbook/ kernelconfig.html Anyone have a clue why he would need to recompile his kernel for this, though? On Dec 14, 2004, at 5:46 AM, Hengki Suhartoyo wrote: Hello... I want to increase my max_connections up to 128 connections, but I got that I need to recompile my kernel. I'm newbie in postgresql and freebsd. How to increase max_connections and recompile freebsd kernel. Help Me please. Thank's __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly --- Frank D. Engel, Jr. [EMAIL PROTECTED] $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep John 3:16 John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ ___ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Cannot drop template1
Raymond O'Donnell [EMAIL PROTECTED] writes: dropdb -U postgres -W template1 at the command line, all I get back is dropdb: database removal failed: ERROR: cannot drop the currently open database. That's because the dropdb command itself connects to template1. See the article on techdocs.postgresql.org about rebuilding template1 to find out about all the gotchas and workarounds needed to do this. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Best practice in postgres
On Fri, 2004-12-10 at 17:49, Nilesh Doshi wrote:
Hi All,
I'm new to postgres, so I need your help.
We are in the process of migrating from oracle to postgres. DB size is about
400gb.
My question is about schemas in oracle and postgres. Does every schema in
oracle becomes a separate database in postgres ? OR it is still like oracle,
where all schemas are part of big database ?
I'm pretty sure you want schema - schema and not schema - database,
mainly because the semantics for querying across databases are much more
cumbersome than querying across schemas. If you are not doing any cross
schema work, then it probably doesn't matter as much.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Sheduler in Postgres
On Tue, 2004-12-14 at 07:49, Christopher Browne wrote: Traditionally, PostgreSQL has consciously omitted such things where they would merely be replicating existing operating system functionality. On Unix, cron is the traditional service that provides this functionality. I think there's a port to Windows NT, so you could presumably use that if you haven't got any more native job scheduler. I haven't administered a Winbox in a few years, but I remember wincron being quite good back in the day. It's free, but registration gets you support. http://www.wincron.com/index.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Insufficient memory for this operation.
Hi,
it wasn't that! :)
That brrr.,*.:$;,^%^%roaaggh BDE :{ on the client side... that was the
problem.
There were stuck in connections in the BDE stack. After closing all BDE
client, the problem went away.
Sorry for disturbing ...
I discover again and again that Postgres is really GOOD. If you have a
problem, you can be sure, that the problem is somewhere else ... Should
avoid using BDE?... Maybe.
Bye,
-- Csaba
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Egyd Csaba
Sent: Tuesday, December 14, 2004 9:50 AM
To: [EMAIL PROTECTED]
Subject: [GENERAL] Insufficient memory for this operation.
(WinXP, PG8b5, 1GB, 3,2GHz)
Hi,
I regulary get the above error message when I run my applications in
parallel. There are minimum of 5 applications which have to access the
server in parallel, but in the production environment this number will about
30-50 (with the additional clients). Each of them connects to the server
(here comes the error message), runs a query and disconnects.
If one of them is stopped, then everything works well, but 5 applications
seem to be too large.
I monitor the PGAdmin Server Status window. It shows that maximum 2
connections are concurrent in the same time, not more.
My postgresql.conf file:
max_connections = 100
shared_buffers = 2 # min 16, at least max_connections*2, 8KB
each
work_mem = 16384# min 64, size in KB
maintenance_work_mem = 16384# min 1024, size in KB
max_stack_depth = 2048 # min 100, size in KB
(The NT Task Manager reports a 769MB memory usage.)
What should I increase/decrease to acheave the required performance? Or what
do I do wrong?
Many thanks,
-- Csaba Egyd
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] High volume inserts - more disks or more CPUs?
Hi! -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Guy Rouillier Gesendet: Montag, 13. Dezember 2004 07:17 An: PostgreSQL General Betreff: [GENERAL] High volume inserts - more disks or more CPUs? (1) Would we be better off with more CPUs and fewer disks or fewer CPUs and more disks? From my experience, it's generally a good idea to have as many disks as possible - CPU is secondary. Having enough RAM so that at least the frequently accessed parts of your db data including the indexes fit completely into memory is also a good idea. (3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace? RAID5 is not an optimum choice for a database; switch to RAID0+1 if you can afford the disk space lost - this yields much better insert performance than RAID5, as there's no parity calculation involved. There's another performance gain to be achieved by moving the WAL-files to another RAID-set than the database files; splitting tablespaces across RAID-sets usually won't do much for you in terms of performance, but might be convenient when you think about scaling in size. Kind regards Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] High volume inserts - more disks or more CPUs?
Guy Rouillier wrote: Seeking advice on system configuration (and I have read the techdocs.) Probably worth reading the archives for the performance list. We are converting a data collection system from Oracle to PostgreSQL 8.0. We are currently getting about 64 million rows per month; data is put into a new table each month. The number of simultaneous connections is very small: one that does all these inserts, and 5 others that read. We trying to identify a server for this. Options are a 4-way Opteron with 4 SCSI disks, or a 2-way Opteron with 6 SCSI disks. The 4-CPU box currently has 16 GB of memory and the 2-CPU 4 GB, but we can move that memory around as necessary. (1) Would we be better off with more CPUs and fewer disks or fewer CPUs and more disks? Usually, more disks. Obviously, you'll want to test your particular setup, but lots of RAM and lots of disk are generally more important than CPU. (2) The techdocs suggest starting with 10% of available memory for shared buffers, which would be 1.6 GB on the 4-way. But I've seen posts here saying that anything more than 10,000 shared buffers (80 MB) provides little or no improvement. Where should we start? Start at the performance tuning document below: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php (3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace? Check the performance list archive for lots of discussion about this. You might want to put the WAL on separate disk(s) which will reduce the number available for storage. It depends on what your peak write-rate is. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Select after insert to the unique column
Thank you for your answer. I think it's very interesting behaviour. Is it a feature or bug ? I have try this my jUnit test for another DB systems (e.g. Oracle 9i, MS SQL Server 2000, MySQL, DB2, Sybase, SAP DB) and it works for each of these databases (it was possible tu run next command successfully after an exception occured before). With baset regards, Julian Legeny Monday, December 13, 2004, 4:26:24 PM, you wrote: FDEJ If you attempted the inserts within a single transaction and any of FDEJ them fail, they will all fail. The server will automatically undo any FDEJ and all changes made by the transaction, and any further steps in the FDEJ transaction will simply result in the error message you are getting. FDEJ You will not be able to (successfully) issue any further database FDEJ commands until you end the transaction and start a new one. FDEJ On Dec 11, 2004, at 2:29 PM, Bruno Wolff III wrote: On Wed, Dec 08, 2004 at 14:50:04 +0100, Julian Legeny [EMAIL PROTECTED] wrote: Hello, Then I want to process command select count(*) from UNIQUE_COLUMN_TEST that I want to know how many records was already inserted before id faied. But when I try to process that SELECT COUNT(*), there is error occured again: org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block How can I solve this? Depending on what you really want to do, you could do each insert in its own transaction. If you don't want any of the inserts to succeed if there are problems, then you should do the counting in the application doing the inserts. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Select after insert to the unique column
On Mon, Dec 13, 2004 at 17:04:17 +0100, [EMAIL PROTECTED] wrote: Thank you for your answer. I think it's very interesting behaviour. Is it a feature or bug ? Until version 8 (which is in release candidate status now), there was no way to recover from an error within a transaction other than aborting the transaction. With version 8 you will be able to use savepoints to allow for recovery from errors within a transaction. The best answer to your question above, is that it is a limitation and not really a feature and definitely not a bug. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] subscribe missing?
On Mon, 2004-12-13 at 10:46, Jimmie H. Apsey wrote: When I tried to subscribe I got: Not Found The requested URL /mj/mj_wwwusr was not found on this server. __ Apache/1.3.33 Server at webmail.anachronic.net Port 80 Thank you, Jim Apsey Me too, but I got subscribed anyway. I'm guessing we had some kind of crash last week with the mailing list servers... ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] partial index on boolean, problem with v8.0.0rc1
Hi all, PostgreSQL v8.0.0rc1, two variants of a user_msg table: create table user_msg ( message_id integer not null references message(id) on update cascade on delete cascade, user_id integer not null, status smallint not null default 0, is_read boolean not null default false, unique (message_id,user_id) ); create index user_msg_is_read_idx on user_msg(is_read) where is_read=true; create table user_msg ( message_id integer not null references message(id) on update cascade on delete cascade, user_id integer not null, status smallint, is_read boolean, unique (message_id,user_id) ); create index user_msg_is_read_idx on user_msg(is_read) where is_read=true; In both cases, tables are filled with ~10m of rows, is_read is false in the 1st case, and NULL in the 2nd. I did VACUUM FULL ANALYSE after both imports. Here's the problem: in the 2nd case, planner wouldn't choose an index scan using partial index on is_read for the following queries: explain select * from user_msg where is_read=true; explain select * from user_msg where is_read is true; explain select * from user_msg where is_read; In the 1st case, partial index was used for the first query. -- Best Regards, Igor Shevchenko ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Substring question
On Mon, Dec 13, 2004 at 06:17:27PM +, Adam Witney wrote: I am trying to select a part of a text field based on a regular expression, the data looks like this Rv0001c_f Rv0002_r Rv1003c_r Etc I would like to be able to select like this (this is a regular expression I would do in perl) SELECT substring(primer_name, '(\w+)\d\d\d\d[c]*_[fr]$') from primer; The POSIX Regular Expressions section in the manual contains the following note: Remember that the backslash (\) already has a special meaning in PostgreSQL string literals. To write a pattern constant that contains a backslash, you must write two backslashes in the statement. SELECT substring(primer_name, '(\\w+)\\d\\d\\d\\d[c]*_[fr]$') FROM primer; substring --- Rv Rv Rv (3 rows) Is that what you're after? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] table with sort_key without gaps
On Mon, Dec 13, 2004 at 19:37:41 +0100, Janning Vygen [EMAIL PROTECTED] wrote: the other reason why i wanted gapless sequences was that i would love to use the id in an URL. But this is easy to manage to translate a positional id in an URL to the database id. For this you probably shouldn't be using the sort value (even if you use numeric to avoid having to renumber). This should use a real primary key. This you definitely can use a sequence for if there isn't a natural primary key. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Temporary tables and disk activity
Hi Tom,
I thought of a quicker way to investiage this than strace and did an ls
-lt in the data directory and looked up the tables that seem to change
on every transaction in pg_class. They are the catalog tables:
# ls -lt /var/lib/postgres/data/base/17142/
total 530108
-rw---1 postgres postgres 6488064 Dec 13 18:44 1259
-rw---1 postgres postgres 3670016 Dec 13 18:44 1247
-rw---1 postgres postgres 38715392 Dec 13 18:44 1249
-rw---1 postgres postgres 3317760 Dec 13 18:44 16390
-rw---1 postgres postgres 13467648 Dec 13 18:44 16599
-rw---1 postgres postgres 16957440 Dec 13 18:44 16610
-rw---1 postgres postgres 4808704 Dec 13 18:44 16613
-rw---1 postgres postgres 17072128 Dec 13 18:44 16624
-rw---1 postgres postgres 14352384 Dec 13 18:44 16625
-rw---1 postgres postgres 483328 Dec 13 18:44 16630
-rw---1 postgres postgres 2228224 Dec 13 18:44 16652
-rw---1 postgres postgres 5742592 Dec 13 18:44 16653
-rw---1 postgres postgres 63578112 Dec 13 18:44 16609
-rw---1 postgres postgres 13787136 Dec 13 18:44 16614
-rw---1 postgres postgres 483328 Dec 13 18:44 16629
= select pc.relfilenode, pc.relname, pn.nspname from pg_class pc join
pg_namespace pn on (pc.relnamespace=pn.oid) where pc.relfilenode in
('1259','1247','1249','16390','16599','16610','16613','16624','16625','16630','16652','16653','16609','16614','16629');
relfilenode | relname | nspname
-+-+
16599 | pg_depend | pg_catalog
16390 | pg_index| pg_catalog
1259 | pg_class| pg_catalog
1249 | pg_attribute| pg_catalog
1247 | pg_type | pg_catalog
16653 | pg_type_typname_nsp_index | pg_catalog
16652 | pg_type_oid_index | pg_catalog
16630 | pg_index_indexrelid_index | pg_catalog
16629 | pg_index_indrelid_index | pg_catalog
16625 | pg_depend_reference_index | pg_catalog
16624 | pg_depend_depender_index| pg_catalog
16614 | pg_class_relname_nsp_index | pg_catalog
16613 | pg_class_oid_index | pg_catalog
16610 | pg_attribute_relid_attnum_index | pg_catalog
16609 | pg_attribute_relid_attnam_index | pg_catalog
(15 rows)
Does this make sense? I imagine that the temporary table is being added
to these tables and then removed again.
I do have quite a large number of tables in the database; I have one
schema per user and of the order of 20 tables per user and 200 users. I
can imagine that in a system with fewer tables this would be
insignificant, yet in my case it seems to be writing of the order of a
megabyte in each 5-second update.
I should mention that I ANALYSE the temporary table after creating it
and before using it for anything; I'm not sure if this does any good
but I put it in as it couldn't do any harm.
Any thoughts?
Regards,
Phil.
Tom Lane wrote:
Phil Endecott [EMAIL PROTECTED] writes:
Tom Lane wrote:
In principle, therefore, the kernel could hold temp table data in its
own disk buffers and never write it out to disk until the file is
deleted. In practice, of course, the kernel doesn't know the data is
transient and will probably push it out whenever it has nothing else to
do.
That makes sense. I suspect that I am seeing writes every 5 seconds,
which looks like bdflush / update.
But my connections normally only last for a second at most. In this
case, surely the table would normally have been deleted before the
kernel decided to write anything.
That does seem a bit odd, then. Can you strace a typical backend
session and see if it's doing anything to force a disk write?
(I'm too lazy to go check right now whether 7.4 handled temp tables
exactly the same as CVS tip does. I think it's the same but I might
be wrong.)
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] partial index on boolean, problem with v8.0.0rc1
Igor Shevchenko [EMAIL PROTECTED] writes: In both cases, tables are filled with ~10m of rows, is_read is false in the 1st case, and NULL in the 2nd. I did VACUUM FULL ANALYSE after both imports. Here's the problem: in the 2nd case, planner wouldn't choose an index scan using partial index on is_read for the following queries: This is the same problem noted by Mike Mascari a couple weeks ago: ANALYZE does not store any statistics about an all-null column. So there are no stats and the default decision is not to risk an indexscan explain select * from user_msg where is_read=true; explain select * from user_msg where is_read is true; explain select * from user_msg where is_read; Only the first of these could possibly match the partial index anyway. In theory the planner could recognize that the first and third are equivalent spellings of the same condition, but it does not presently do so. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] partial index on boolean, problem with v8.0.0rc1
On Mon, Dec 13, 2004 at 20:18:57 +0200, Igor Shevchenko [EMAIL PROTECTED] wrote: Here's the problem: in the 2nd case, planner wouldn't choose an index scan using partial index on is_read for the following queries: explain select * from user_msg where is_read=true; explain select * from user_msg where is_read is true; explain select * from user_msg where is_read; In the 1st case, partial index was used for the first query. Note that whatever = TRUE is not equivalent to whatever IS TRUE. The latter will return FALSE if whatever is NULL, while the former will return NULL in that case. There are also some limitations on the optimizer recognizing equivalent varients of an expression. This might cause a problem with the third select example. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Temporary tables and disk activity
Tom Lane wrote: Phil Endecott [EMAIL PROTECTED] writes: Does this make sense? I imagine that the temporary table is being added to these tables and then removed again. Yes, a temp table has the same catalog infrastructure as a regular table, so creation and deletion of a temp table will cause some activity in those catalogs. I thought you were concerned about the data within the temp table, though. I'm concerned about unnecessary disk activity, whatever its cause. I guessed that it was the temp table contents. I do have quite a large number of tables in the database; I have one schema per user and of the order of 20 tables per user and 200 users. I can imagine that in a system with fewer tables this would be insignificant, yet in my case it seems to be writing of the order of a megabyte in each 5-second update. That seems like a lot. How often do you create/delete temp tables? Only once or twice per 5-sec update period. I agree that it sounds like a lot which makes me think this could all be a red herring; I suspect that there is something else going on as well as this temp table stuff (possibly nothing to do with postgresql). But FYI this is treefic.com, a family tree website. Have a look at, for example, http://treefic.com/treefic/royal92?a=tree_pageroot_id=10286direction=up The first step in building that diagram is to find the ancestors of the root individual. I have a pl_pgsql function that itteratively finds all of the ancestors, progressively adding them to a temporary table. So it will create, populate, read and then drop one table for each page that it generates. This is reasonably fast; overall speed is not limited by postgres. What would happen if I were to rollback at the end of the transaction, rather than committing (having made no changes)? Would that eliminate some or all of the catalog writes? Many thanks for helping me understand this. Regards, Phil. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Corrupt RTREE index
I have what appears to be a corrupt RTREE index. The first query shows that of the fifteen records I'm looking at, every one of them has the @ based condition showing as true. The second shows one record that really ought to be there not being listed. I just tried the second query with enable_indexscan = off and the missing record reappears. So I guess this is a corrupt index. This is 7.4.6 but the database was initdb'd with an earlier 7.4. Should I REINDEX or should I keep this around for forensic study? slo= SELECT distinct store_id, geom2 @ box '(-72.795693324,46.041219387024),(-74.3364602689304,44.9613057801126)' FROM store_location WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) = 60 ; slo= slo- slo- slo- slo- store_id | ?column? --+-- 504 | t 597 | t 909 | t 2841 | t 2940 | t 2997 | t 3423 | t 3438 | t 3641 | t 3656 | t 4057 | t 4487 | t 4489 | t 4490 | t 4493 | t (15 rows) slo= SELECT distinct store_id, geom2 @ box '(-72.795693324,46.041219387024),(-74.3364602689304,44.9613057801126)' FROM store_location WHERE earth_dist(geom, -73.5660767977984, 45.5012625835683) = 60 AND geom2 @ box '(-72.795693324,46.041219387024),(-74.3364602689304,44.9613057801126)' ; slo= slo- slo- slo- slo- store_id | ?column? --+-- 504 | t 597 | t 909 | t 2841 | t 2940 | t 2997 | t 3423 | t 3438 | t 3641 | t 3656 | t 4057 | t 4487 | t 4489 | t 4490 | t (14 rows) -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Possible dump/restore bug
William Yu [EMAIL PROTECTED] writes: Index Scan using idx_finvendors_name on fin_vendors (cost=0.00..4.01 rows=1 width=600) (actual time=0.029..0.036 rows=2 loops=1) Index Cond: ((name = 'NBC'::bpchar) AND (name 'NBD'::bpchar)) Filter: (name ~~ 'NBC%'::text) Hmm. Apparently column name is of type char(N) rather than text? talisman=# create index test_upper on fin_vendors (upper(name))\g CREATE INDEX talisman=# explain analyze select * from fin_vendors where upper(name) like 'NBC%'\g Index Scan using test_upper on fin_vendors (cost=0.00..616.68 rows=316 width=604) (actual time=0.032..0.039 rows=2 loops=1) Index Cond: ((upper((name)::text) = 'NBC'::text) AND (upper((name)::text) 'NBD'::text)) Filter: (upper((name)::text) ~~ 'NBC%'::text) Total runtime: 0.096 ms Note the inserted casts: the index is really on UPPER(name::text). It was probably shown that way in your dump file. I believe what is happening here is that pre-8.0 PG versions fail to recognize that implicit and explicit casting to text are equivalent operations, and so an index declared as create index foo_upper on foo (upper(name::text)) isn't going to match a query that mentions upper(name) with no cast. This is a slightly tricky issue because there are in fact cases where implicit and explicit casts have different semantics :-(. I think we've got it worked out properly in 8.0 though. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Corrupt RTREE index
Greg Stark [EMAIL PROTECTED] writes: I have what appears to be a corrupt RTREE index. I wonder if it's actually corrupt, or if it's just that the index semantics don't truly match the operator. If the latter, REINDEXing won't fix it. As for the first theory, have you had any database crashes lately? If so I'd write this off as a failure caused by the lack of WAL-logging support in rtree. As for the second theory, in this thread http://archives.postgresql.org/pgsql-general/2004-03/msg01135.php we concluded that the existing mapping of geometric operators onto rtree indexes is wrong; see in particular http://archives.postgresql.org/pgsql-general/2004-03/msg01143.php However that discussion dealt with and related operators, not @. I didn't think @ was broken ... but I might have missed something. (I was expecting bwhite to come back with a patch to fix the rtree problems he'd identified, but he never did, so it's still an open issue.) regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Multiple foreign keys on same field
On Tue, Dec 14, 2004 at 02:06:24 +0200, Ciprian Popovici [EMAIL PROTECTED] wrote: I'm in a situation where it would be useful to bind a field in a table via foreign keys to N other tables simultaneously. The table holds a common type of info which all those other tables use. The many tables refer to the common table by keeping references to its serial field. By doing this, I could ensure that when a row in any of the many tables is deleted or updated, the effect travels to the common table. So far, I've been successful in defining more than one foreign key on the same field in the lone table, tied to fields in different tables. (I half expected it not to work though). However, it seems that inserting values in the commons table is a showstopper: it expects that field value to exists not in only one, but in ALL bound tables simultaneously. Are you sure you don't really want the foreign key relation to go in the other direction? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Corrupt RTREE index
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: So you don't think this case is worth doing forensics on? If the problem goes away after REINDEX then I'll write it off as missing WAL support. rtree is not high enough on my list of priorities to justify more effort :-( Fwiw, the problem went away after REINDEX. -- greg ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Spanning tables
Im using 7.3.4 is there another way? On Monday 13 December 2004 14:52, Shridhar Daithankar wrote: On Thursday 09 Dec 2004 10:37 am, JM wrote: Hi ALL, Im wondering sooner or later my disk will be filled-up by postgres's data.. Can anyone give some suggestion on how to deal with this. In oracle you can just assign tables on a diff partition. You could use tablespaces in postgresql 8.0.. Check this http://developer.postgresql.org/docs/postgres/sql-createtablespace.html http://developer.postgresql.org/docs/postgres/sql-createtable.html HTH Shridhar -- Jerome Macaranas Systems/Network Administrator GMA New Media, Inc. Phone: (632) 9254627 loc 202 Fax: (632) 9284553 Mobile: (632) 918-9336819 [EMAIL PROTECTED] DISCLAIMER: This Message may contain confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you received this message in error please notify your Mail Administrator and delete this message immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of GMA New Media, Inc. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Spanning tables
On Tue, Dec 14, 2004 at 02:04:08PM +0800, Jerome Macaranas wrote: On Monday 13 December 2004 14:52, Shridhar Daithankar wrote: You could use tablespaces in postgresql 8.0.. Im using 7.3.4 is there another way? See my earlier followup in this thread: http://search.postgresql.org/pgsql-general/2004-12/msg00534.php Here are links to the referenced documentation for 7.3: http://www.postgresql.org/docs/7.3/static/manage-ag-alternate-locs.html http://www.postgresql.org/docs/7.3/static/app-initlocation.html http://www.postgresql.org/docs/7.3/static/sql-createdatabase.html I'd recommend testing this before doing it on a production server. I've never done it myself so I don't know if there are any potential problems. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] plperl loading
Hi, I've made some tests with plperl and I see that when plperl function is executed for the first time, then it takes much more time. I know that this is a shared library problem - is there a way to preload plperl every connection or maybe I can build plperl into postgresql source ? Thanks in advance. ML ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] [ADMIN] plperl loading
Joe Conway napisa(a): Marek Lewczuk wrote: I've made some tests with plperl and I see that when plperl function is executed for the first time, then it takes much more time. I know that this is a shared library problem - is there a way to preload plperl every connection or maybe I can build plperl into postgresql source ? See: http://www.postgresql.org/docs/current/static/runtime-config.html and search for preload_libraries Thanks Joe. This is what I was looking for. ML ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
