Re: [GENERAL] Regex query not using index
On Feb 20, 2008, at 5:51 PM, Postgres User wrote:
Now to end my fixation, one last item. What about the case of a null
or empty param value- is there a way to assign a condition value that
Postgres will ignore when processing the query?
This syntax results in a seq scan: WHERE fielda = Coalesce(param,
fielda)
because it applies only to non-nulls
Is there another way to write this- perhaps using your array syntax on
an empty array? Basically I'd PG to ignore the condition just as it
ignores WHERE 1 = 1
Not sure whether you want no rows returned if param is NULL or all
rows (all rows looking at your example), but you could simply check
param for NULL before comparing it, so either:
WHERE param IS NOT NULL AND fielda = param
or
WHERE param IS NULL OR fielda = param
In the second case, if param IS NULL you will get a sequential scan
of course, as that's the most efficient way to return all rows.
On Wed, Feb 20, 2008 at 8:31 AM, Tom Lane [EMAIL PROTECTED] wrote:
Postgres User [EMAIL PROTECTED] writes:
My users are developers and the goal was to accept a simple
comma-delimited list of string values as a function's input
parameter.
The function would then parse this input param into a valid regex
expression.
Why are you fixated on this being a regex? If you aren't actually
trying to expose regex capabilities to the users, you'll just be
having
to suppress a bunch of strange behaviors for special characters.
ISTM that the best solution is to use an array-of-text parameter,
along the lines of
where name = any (array['Smith', 'Jones', ...])
For what you're doing, you'd not actually want the array[] syntax,
it would look more like
where name = any ('{Smith,Jones}'::text[])
This should optimize into an indexscan in 8.2 or later.
regards, tom lane
---(end of
broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47c1522f233091890169212!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Trigram performance penalty on varchar?
On Feb 21, 2008, at 10:56 AM, Mario Lopez wrote: Hi, I am indexing a 100 million record table composed of varchar(255) as the field to be indexed. I have always seen that examples of pg_trgm are based on text type fields. Is this by any special reason?. A varchar is internally represented as text, with a size constraint of 255 characters in your case (I'm assuming your data requires that constraint?). My computer is creating the index since 5 hours ago so I guess there must be something wrong... I guess your server is running low on memory and the index being created doesn't fit in memory. You may want to have a look at http:// www.postgresql.org/docs/8.3/interactive/populate.html#POPULATE-WORK-MEM Another reason may be an exclusive lock on a row that you're trying to index, but that would mean that some transaction on some client somewhere is keeping that lock for a very long time (should not happen). You can check the pg_locks and pg_stat_activity tables for that. Regards, Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47c159af233092392031086! ---(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] How to make update rapidly?
On Feb 20, 2008, at 5:03 AM, hewei wrote: table: CREATE TABLE price ( TIMESTAMP Timestamp NULL, idnumeric(5,0) NOT NULL, price numeric(10,3) NULL, primary key (id) ); sql: update price set price=* where id=*; So you have about 714us on average per query. That's not impossible, but your hardware and database configuration need to be up to the task. Updates are generally slower than selects, as they have to find a spot for the new record, check constraints, write it, etc. Your problem could be that you're using a prepared statement. For prepared statements the query plan gets calculated when the prepared statement is created, without any knowledge of the actual values to look up. That can result in a non-optimal plan. EXPLAIN ANALYZE of that query should show more. Re-preparing it after analysing the table may improve the performance, not sure about that. Another possible problem, as you're doing updates, is that your data files get bloated with old rows that don't exist anymore (in your current transaction). An update is effectively an insert and a delete (has to be, due to visibility to other transactions - MVCC), so every update changes one row into two. If you don't vacuum often enough there will be many more than 100,000 rows to search through. Added to that; if you don't analyze, the query planner is working with outdated information and may decide on a bad plan (not a sequential scan probably, but non-optimal still). Additionally, if you're trying to update the same row concurrently from multiple sessions, you're waiting on locks. Not much you can do about that, not something you're likely to encounter in a real situation though. On Feb 20, 2008 11:56 AM, Webb Sprague [EMAIL PROTECTED] wrote: Post the table, the query, and the explain output, and then we can help you. On Feb 19, 2008 7:38 PM, hewei [EMAIL PROTECTED] wrote: Hi,Every body; I have a table contains 100,000 rows, and has a primary key(int). Now ,I need to execute sql command like update .. where id=*(id is primary key). I expect execute 1200-1600 sqlcommands per second(1200-1600/s). In test,when the id increase by degrees in sqlcommands, then I can reach the speed(1600/s); But in fact , the id in sqlcommands is out of rule, then the speed is very slow, just 100/s. what can i do? can you help me ? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47c15fde233095552171742! ---(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] configure build flags
On Feb 22, 2008, at 3:29 PM, [EMAIL PROTECTED] wrote: Hi All. Anyone knows if rebuilding the postgresql sources with the flag -- without-tcl --without-perl in ./configure file can prevent the correct use of the triggers wrote in plpgsql language? Wich behaviour I've to expect in postgres using these flags? Thanks in advance. Luca That's like asking whether we'd still be able to speak English if you configure Europe without Latin and Hebrew... Of course you can! ;) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,47c166b7233091851639626! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] unnesesary sorting after Merge Full Join
On Sat, 2008-02-23 at 14:49 -0600, Decibel! wrote: On Feb 21, 2008, at 4:08 AM, Alexey Nalbat wrote: I found comment in src/backend/optimizer/path/pathkeys.c: * EXCEPTION: in a FULL or RIGHT join, we cannot treat the result as * having the outer path's path keys, because null lefthand rows may be * inserted at random points. It must be treated as unsorted. How can I get rid of this sorting? Or could this behavior of Merge Full Join be improved? Theoretically, this can be improved I don't see how. The ORDER BY ... LIMIT ... code is already optimised. If there are NULLs in the left hand side then it needs to be treated as unsorted, which forces a sort. If you know there are no NULLs then don't do a FULL join. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] plpgsql function
Hello. I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...) from mySQL. //- CREATE OR REPLACE FUNCTION BiSCAT_combined.extractyearmonth(date timestamp without time zone) RETURNS character varying AS $BODY$ DECLARE i INTEGER; BEGIN i := EXTRACT(MONTH FROM $1 ::timestamp); if i 9 THEN RETURN EXTRACT(YEAR FROM $1 :: timestamp) || EXTRACT(MONTH FROM $1 :: timestamp); else RETURN EXTRACT(YEAR FROM $1 ::timestamp) || 0 || EXTRACT(MONTH FROM $1 :: timestamp); end if; END; $BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; // One Method call requires 53ms. I'm sure that this function is absolutely unoptimezed but I think 53ms is too long. Are there any suggestions to improve the execution time of the function. Best regards Andi Kendlinger ---(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] reindexing
Hi, I am reindexing my 7.1.4 postgres database. The postmaster seems to create processes for each reindex request. Is there any way to find out more about the processes. ps -aef | grep postgres yields the following, but does not tell me which table is being reindexed or anything meaningful about the process. postgres 605 604 0 Feb 18 ? 0:00 /usr/local/pgsql/ bin/postmaster postgres 5599 579 0 Feb 21 ? 39:12 /usr/local/pgsql/ bin/postmaster postgres 20101 579 0 10:56:52 ? 0:58 /usr/local/pgsql/ bin/postmaster postgres 579 1 0 Feb 18 ? 0:02 /usr/local/pgsql/ bin/postmaster postgres 604 579 0 Feb 18 ? 0:00 /usr/local/pgsql/ bin/postmaster dspace 21563 21391 0 13:27:00 pts/3 0:00 grep postgres postgres 5645 579 0 Feb 21 ? 35:29 /usr/local/pgsql/ bin/postmaster postgres 19695 579 0 10:13:22 ? 2:51 /usr/local/pgsql/ bin/postmaster postgres 19713 579 0 10:15:02 ? 2:43 /usr/local/pgsql/ bin/postmaster postgres 7441 579 0 Feb 21 ? 33:49 /usr/local/pgsql/ bin/postmaster postgres 19963 579 0 10:42:25 ? 1:43 /usr/local/pgsql/ bin/postmaster postgres 19658 579 0 10:09:56 ? 2:52 /usr/local/pgsql/ bin/postmaster postgres 19981 579 0 10:44:43 ? 2:20 /usr/local/pgsql/ bin/postmaster postgres 6276 579 0 Feb 21 ? 39:12 /usr/local/pgsql/ bin/postmaster postgres 19667 579 0 10:10:56 ? 2:25 /usr/local/pgsql/ bin/postmaster postgres 5654 579 0 Feb 21 ? 36:36 /usr/local/pgsql/ bin/postmaster postgres 5657 579 20 Feb 21 ? 33:06 /usr/local/pgsql/ bin/postmaster postgres 5656 579 0 Feb 21 ? 39:17 /usr/local/pgsql/ bin/postmaster postgres 6216 579 0 Feb 21 ? 31:02 /usr/local/pgsql/ bin/postmaster postgres 7508 579 0 Feb 21 ? 29:03 /usr/local/pgsql/ bin/postmaster postgres 20159 579 0 11:03:25 ? 2:22 /usr/local/pgsql/ bin/postmaster postgres 6275 579 0 Feb 21 ? 35:12 /usr/local/pgsql/ bin/postmaster postgres 7474 579 0 Feb 21 ? 32:07 /usr/local/pgsql/ bin/postmaster postgres 19884 579 0 10:33:52 ? 1:38 /usr/local/pgsql/ bin/postmaster postgres 5655 579 0 Feb 21 ? 35:42 /usr/local/pgsql/ bin/postmaster postgres 20100 579 0 10:56:43 ? 2:04 /usr/local/pgsql/ bin/postmaster postgres 5598 579 0 Feb 21 ? 40:22 /usr/local/pgsql/ bin/postmaster postgres 20259 579 0 11:15:33 ? 2:04 /usr/local/pgsql/ bin/postmaster postgres 19696 579 19 10:13:57 ? 2:07 /usr/local/pgsql/ bin/postmaster postgres 7509 579 0 Feb 21 ? 34:43 /usr/local/pgsql/ bin/postmaster postgres 19946 579 0 10:40:11 ? 1:12 /usr/local/pgsql/ bin/postmaster postgres 20006 579 0 10:47:06 ? 2:17 /usr/local/pgsql/ bin/postmaster postgres 6258 579 0 Feb 21 ? 40:08 /usr/local/pgsql/ bin/postmaster ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] text and bytea
It seems to me that postgres is trying to do as you suggest: text is characters and bytea is bytes, like in Java. But the big difference is that, for text type, postgresql knows this is a text but doesnt know the encoding, as my example showed. This goes against the concept of text vs bytes distintion, which per se is very useful and powerful (specially in this Unicode world) and leads to a dubious/clumsy string api (IMHO, as always). You don't indicate what version you are using, this area was rejigged recently. Sorry, I forget to say that my examples are for last version (8.3) Cheers -- Hernán J. González ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Cascading Trigger - changing row on delete does not delete row
All, I have 2 tables which both have triggers on them. When I delete a row on table A, a cascading trigger ends up modifying rows in table B. The modified rows in table B trigger an update on rows in table A which happens to be the same row that I am trying to delete. I don't get any errors from the delete, yet PostgreSQL tells me 0 rows affected by the delete and sure enough the row I just tried to delete is still there. Running the delete a 2nd time works because the trigger does not cascade and effect the deleted row. Is there a way to know that a row I am deleting is being deleted so I don't update it? I thought about adding a boolean column 'is_being_deleted' but I can't set that to true without updating the row (which I'm trying to avoid). I've thought about using PL/Perl to access transaction-level global variables where I could store the ID of the row I'm deleting and fetch that value in order to avoid it in my updates ... but I don't want invoke the PL/Perl interpreter and slow down what I'm already doing in PL/PGSQL. Are there transaction-level variables in PL/PGSQL (globals)? Suggestions? -- Dante ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8
I'm looking at the v7.4 manuals and I don't see how to encode for importing into a v8 DB using UTF8. Maybe I'm making this hard on myself? The old DB is using SQL_ASCII. We'd like the new one to use UTF8. As development proceeds, I'm going to have to do this regularly, both the entire DB and by tables. If not for the encoding, I've got all that down, even automated. Thanks for any help! Ralph --- p.s. Isn't there a 16 bit Unicode for postgreSQL? smithrn at u dot washington dot edu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Cascading Trigger - changing row on delete does not delete row
All, I have 2 tables which both have triggers on them. When I delete a row on table A, a cascading trigger ends up modifying rows in table B. The modified rows in table B trigger an update on rows in table A which happens to be the same row that I am trying to delete. I don't get any errors from the delete, yet PostgreSQL tells me 0 rows affected by the delete and sure enough the row I just tried to delete is still there. Running the delete a 2nd time works because the trigger does not cascade and effect the deleted row. Is there a way to know that a row I am deleting is being deleted so I don't update it? I thought about adding a boolean column 'is_being_deleted' but I can't set that to true without updating the row (which I'm trying to avoid). I've thought about using PL/Perl to access transaction-level global variables where I could store the ID of the row I'm deleting and fetch that value in order to avoid it in my updates ... but I don't want invoke the PL/Perl interpreter and slow down what I'm already doing in PL/PGSQL. Are there transaction-level variables in PL/PGSQL (globals)? Suggestions? -- Dante ---(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] v7.4 pg_dump(all) need to encode from SQL_ASCII to UTF8
On 2008-02-22 17:57, Ralph Smith wrote: I'm looking at the v7.4 manuals and I don't see how to encode for importing into a v8 DB using UTF8. Maybe I'm making this hard on myself? The old DB is using SQL_ASCII. We'd like the new one to use UTF8. As development proceeds, I'm going to have to do this regularly, both the entire DB and by tables. If not for the encoding, I've got all that down, even automated. Thanks for any help! Ralph --- p.s. Isn't there a 16 bit Unicode for postgreSQL? smithrn at u dot washington dot edu There's nothing to do. Dump the database in ASCII; create the new database with a server_encoding of UTF-8; and import the data (which is marked as having a client_encoding of SQL_ASCII). ps: No. -- Mail to my list address MUST be sent via the mailing list. All other mail to my list address will bounce. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] More formal definition of functions in documentation
Dear, I'm currently reading through the Postgresql documentation about how several functions work and which ones I would need. So far the documentation is great and well-structured! Unfortunally I'm not sure what functions will actually do when some non-obvious input is provided (which is sometimes required, as we cannot assume structure of the input at all times). A simple example is substring(text from 'blaat#%#' for '#') where text is not in the format of the regular expression, e.g. when text = 'text'. I don't know if the SQL standard includes such requirements, but documenting such behaviour might be a good addition to the help. If I know how and how to structure it, I'm willing to do some effect. In any case, something as already done for CREATE TABLE and such constructs are very good and well-defined. - Joris ---(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] RETURNS SETOF function question
Suppose that stored procedure foo has the signature: foo( text, text ) RETURNS SETOF text Also, I have some table bar, and that column bar.baz is of type text. Now, I'd like to run something like SELECT foo( frobozz, baz ) FROM bar; If I try this psql complains that I'm trying to execute a set-valued function in the wrong context. But the intention of this invalid statement is to apply foo( frobozz, ? ) once for each row of bar, replacing ? each time with the row's value of baz, and concatenate all the returned tables to produce the final result. (In general, the number of rows resulting from this application has no relation to the number of rows in bar; i.e. it can be less than, equal to, or greater than this number.) What must I do to get the desired behavior? TIA! kynn
Re: [GENERAL] plpgsql function
Andreas Kendlinger [EMAIL PROTECTED] writes:
I wrote a little stored function to simulate the EXTRACT(YEAR_MONTH ...)
from mySQL.
...
One Method call requires 53ms.
Really? Near as I can tell, it takes about 130 microsec on my ancient
HPPA machine, which is surely as slow as anything anyone's still using.
What PG version are you using? Are you sure you're only measuring the
function call and not some other overhead? I tested like this:
regression=# \timing
Timing is on.
regression=# select count(extractyearmonth('2008-02-04')) from
generate_series(1,10);
count
10
(1 row)
Time: 14431.591 ms
regression=# select count(1) from generate_series(1,10);
count
10
(1 row)
Time: 1130.305 ms
regression=# select (14431.591-1130.305)/10;
?column?
0.13301286
(1 row)
Time: 7.262 ms
(This is with the IMMUTABLE marker removed from the function, else it'd
be called only once and we couldn't measure anything.)
However, I certainly think it can be done more easily --- use to_char.
It looks to me like to_char(some_timestamp, 'MM') does what you want,
and that runs in about 18 microsec.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] no-arg cluster and locks ...
How does 8.2 [ or 8.3 ] deal with table locking in the face of no- argument 'cluster' command? Does it lock all tables it is going to visit 'up front', or does it collect locks slowly as it visits tables? If it only locks a new table before it visits it, does it unlock it once it is done? Finally, is it a candidate for deadlock detection and unrolling just as other locking ops are [ I can imagine one wouldn't want to deadlock-kill the clustering backend, but the backend it contends with might be fodder assuming its a lesser command ]. We just some observed an undetected deadlock-ish issue, and the juciest aspect was that a db-wide cluster was running. Thanks! James Robinson Socialserve.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Planner: rows=1 after similar to where condition.
I seem to have some planner oddity, where it seems to completely mispredict the output after a regex compare. I've seem it on other occasions, where it completely screws up the join. You can note the rows=1 after the filter. A similar sitution has occurred when doing a regex filter in a subquery, which was subsequently predited as 1 row and triggered (oddly enough) a sequencial scan. Doing the same using equality on the result to substring(text from regex) seemed to work and produced a useful plan, since it did a hash-join (as it should have). Is this a known problem? Otherwise I think I should build a smaller test case... Using Postgresql 8.2.6 from Debian Etch-backports. Bitmap Heap Scan on log_syslog syslog (cost=13124.26..51855.25 rows=1 width=270) Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text)) Filter: ***SOME VERY LONG SIMILAR TO REGEX - BitmapAnd (cost=13124.26..13124.26 rows=18957 width=0) - Bitmap Index Scan on IX_log_syslog_program (cost=0.00..2223.95 rows=92323 width=0) Index Cond: ((program)::text = 'amavis'::text) - Bitmap Index Scan on IX_log_syslog_facility (cost=0.00..10899.81 rows=463621 width=0) Index Cond: ((facility)::text = 'mail'::text) - Joris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing
On Fri, Feb 22, 2008 at 12:24 PM, LARC/J.L.Shipman/jshipman [EMAIL PROTECTED] wrote: Hi, I am reindexing my 7.1.4 postgres database. The postmaster seems to create processes for each reindex request. Is there any way to find out more about the processes. ps -aef | grep postgres yields the following, but does not tell me which table is being reindexed or anything meaningful about the process. My pgsql-fu regarding obsolete versions is obsolete. You do realize that 7.1.x hasn't been supported for a very long time, and for very good reasons, right? It's not just obsolete in terms of being outdated by more modern versions, but is known to have a few data eating bugs, not to mention the txid wraparound issue. You should be planning on how to upgrade it first, then things like this might be less necessary and / or less of a problem to work with. In later versions of pgsql you've got a stats collector that can do things like tell you what queries are running. As well as autovacuuming and non-full vacuums that make things like reindexing mostly uneeded. ---(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] RETURNS SETOF function question
On Feb 24, 2008, at 4:19 PM, Kynn Jones wrote: Suppose that stored procedure foo has the signature: foo( text, text ) RETURNS SETOF text Also, I have some table bar, and that column bar.baz is of type text. Now, I'd like to run something like SELECT foo( frobozz, baz ) FROM bar; If I try this psql complains that I'm trying to execute a set- valued function in the wrong context. But the intention of this invalid statement is to apply foo ( frobozz, ? ) once for each row of bar, replacing ? each time with the row's value of baz, and concatenate all the returned tables to produce the final result. (In general, the number of rows resulting from this application has no relation to the number of rows in bar; i.e. it can be less than, equal to, or greater than this number.) What must I do to get the desired behavior? There was an article that covered this in the Postgres Online Journal (http://www.postgresonline.com/journal/index.php?/categories/6-pl- programming). Basically, do this: CREATE OR REPLACE FUNCTION foo(text, text) RETURNS SETOF text $$ SELECT * FROM foo($1, $2); $$ LANGUAGE sql; Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] no-arg cluster and locks ...
James Robinson wrote: How does 8.2 [ or 8.3 ] deal with table locking in the face of no- argument 'cluster' command? Does it lock all tables it is going to visit 'up front', or does it collect locks slowly as it visits tables? If it only locks a new table before it visits it, does it unlock it once it is done? It runs on one transaction per table: the lock on each table is grabbed just before working on it, and released as soon as it is done. Of course, for each table there are locks on the indexes and toast table and index involved, too. Finally, is it a candidate for deadlock detection and unrolling just as other locking ops are [ I can imagine one wouldn't want to deadlock-kill the clustering backend, but the backend it contends with might be fodder assuming its a lesser command ]. Hmm, I think it could deadlock if someone is holding a lock on, say, an index, and then attempts to lock the table. I don't recall the exact details. We just some observed an undetected deadlock-ish issue, and the juciest aspect was that a db-wide cluster was running. Can you provide more details? pg_locks, pg_stat_activity, the deadlock message? (Hmm, it would be helpful if the deadlock checker were to save the pg_locks contents and perhaps pg_stat_activity in a file, whenever a deadlock is detected.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] no-arg cluster and locks ...
Can you provide more details? pg_locks, pg_stat_activity, the deadlock message? (Hmm, it would be helpful if the deadlock checker were to save the pg_locks contents and perhaps pg_stat_activity in a file, whenever a deadlock is detected.) Great idea! As somebody who's spent hours tracking down deadlocks recently, I'd love to have a configurable deadlocks.log file capability. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] request help forming query
Hi !'ve been wondering how to formulate a query to get a set of objects out of a database, and am a bit stuck. I hope that someone here might be able to help. This is what the db looks like: Table TYPES id int primary key, description text Table GROUPS id int primary key description text Tables WIDGETS type_id int references TYPES(id), group_id int references GROUPS(id), primary key(type_id, group_id) Now, given two type_id's, say A and B, I would like to find all groups (or group_id's of course) that have a widget of both of these two types. Can anyone help with this please? Thanks! ---(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] request help forming query
danmcb wrote: Hi !'ve been wondering how to formulate a query to get a set of objects out of a database, and am a bit stuck. I hope that someone here might be able to help. This is what the db looks like: Table TYPES id int primary key, description text Table GROUPS id int primary key description text Tables WIDGETS type_id int references TYPES(id), group_id int references GROUPS(id), primary key(type_id, group_id) Now, given two type_id's, say A and B, I would like to find all groups (or group_id's of course) that have a widget of both of these two types. There must be a more a elegant method but here's the first thing that came to me: SELECT group_id FROM widgets WHERE type_id = $1 AND group_id IN ( SELECT DISTINCT group_id FROM widgets WHERE type_id = $2 ); I trust you aren't planning to run this on billions of rows ... b ---(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] Planner: rows=1 after similar to where condition.
On Sun, Feb 24, 2008 at 4:35 PM, Joris Dobbelsteen [EMAIL PROTECTED] wrote: I seem to have some planner oddity, where it seems to completely mispredict the output after a regex compare. I've seem it on other occasions, where it completely screws up the join. You can note the rows=1 after the filter. A similar sitution has occurred when doing a regex filter in a subquery, which was subsequently predited as 1 row and triggered (oddly enough) a sequencial scan. Doing the same using equality on the result to substring(text from regex) seemed to work and produced a useful plan, since it did a hash-join (as it should have). Is this a known problem? Otherwise I think I should build a smaller test case... Using Postgresql 8.2.6 from Debian Etch-backports. Bitmap Heap Scan on log_syslog syslog (cost=13124.26..51855.25 rows=1 width=270) Recheck Cond: (((program)::text = 'amavis'::text) AND ((facility)::text = 'mail'::text)) Filter: ***SOME VERY LONG SIMILAR TO REGEX - BitmapAnd (cost=13124.26..13124.26 rows=18957 width=0) - Bitmap Index Scan on IX_log_syslog_program (cost=0.00..2223.95 rows=92323 width=0) Index Cond: ((program)::text = 'amavis'::text) - Bitmap Index Scan on IX_log_syslog_facility (cost=0.00..10899.81 rows=463621 width=0) Index Cond: ((facility)::text = 'mail'::text) It's not saying it will only get one row back for sure, it's saying it thinks it will return one row. and depending on your query, it might. What's the query, and what's the explain analyze of that query? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reindexing
Scott Marlowe [EMAIL PROTECTED] writes: On Fri, Feb 22, 2008 at 12:24 PM, LARC/J.L.Shipman/jshipman [EMAIL PROTECTED] wrote: I am reindexing my 7.1.4 postgres database. My pgsql-fu regarding obsolete versions is obsolete. You do realize that 7.1.x hasn't been supported for a very long time, and for very good reasons, right? There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 ... not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] request help forming query
Given that (type_id, group_id) is the PK of widgets it is possible to avoid self-join: select group_id from widgets where type_id = A or type_id = B group by group_id having count(1) = 2; ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] More formal definition of functions in documentation
Joris Dobbelsteen wrote: Dear, I'm currently reading through the Postgresql documentation about how several functions work and which ones I would need. So far the documentation is great and well-structured! Unfortunally I'm not sure what functions will actually do when some non-obvious input is provided (which is sometimes required, as we cannot assume structure of the input at all times). A simple example is substring(text from 'blaat#%#' for '#') where text is not in the format of the regular expression, e.g. when text = 'text'. I don't know if the SQL standard includes such requirements, but documenting such behaviour might be a good addition to the help. If I know how and how to structure it, I'm willing to do some effect. In any case, something as already done for CREATE TABLE and such constructs are very good and well-defined. Join the -docs list: http://archives.postgresql.org/pgsql-docs/ and they'll be able to get you started :) -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] autovacuum not freeing up unused space on 8.3.0
It appears (and I am open to correction) that autovacuum is not operating correctly in 8.3.0. I have a vanilla installation where autovacuum is enabled, and is running with all the default settings. I have a table which is continually having rows added to it (~50/sec). For the sake of this example I am limiting it to 2 rows, which means that I am continually having to remove rows (100 at a time) as I get to 2. When I get to 2 rows for the first time the table disk size (using pg_total_relation_size) is around 5MB. Since the autovacuum only kicks in after a while I would expect it to get a little bigger (maybe 6-7MB) and then level out as I am cycling through recovered rows. However the table disk size continues increasing basically linearly and when I stopped it it was approaching 40MB and heading up. During that time I was running ANALYZE VERBOSE periodically and I could see the dead rows increase and then drop down as the autovacuum kicked in - the autovacuum worker process was running. It didn't seem to free any space though. In fact a VACUUM FULL at this point didn't help a whole lot either. I ran the same test but using manual VACUUMs every 60 seconds and the table size leveled out at 6.6MB so it appears like a normal vacuum is working. I changed the normal VACUUM to have the same delay parameters (20ms) as the autovacuum and it still worked. So it appears to me like the autovacuum is not freeing up dead rows correctly. I turned on logging for autovacuum and ran the same test and saw the following messages: LOG: automatic vacuum of table metadb.test.transactions: index scans: 1 pages: 0 removed, 254 remain tuples: 4082 removed, 19957 remain system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec LOG: automatic vacuum of table metadb.test.transactions: index scans: 1 pages: 0 removed, 271 remain tuples: 5045 removed, 19954 remain system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec ERROR: canceling autovacuum task CONTEXT: automatic vacuum of table metadb.test.transactions At this point I had deleted 32800 rows as can be seen from the query below, although the logs only indicated that around 1 rows had been freed up. select min(transaction_key),max(transaction_key) from test.transactions; min | max ---+--- 32801 | 52750 Is there anything I have missed as far as setting this up is concerned, anything I could try? I would really rather use autovacuum than manage the vacuums of a whole lot of tables by hand... Thanks Stuart PS. Running on NetBSD 3 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] text and bytea
On Fri, Feb 22, 2008 at 01:54:46PM -0200, hernan gonzalez wrote: It seems to me that postgres is trying to do as you suggest: text is characters and bytea is bytes, like in Java. But the big difference is that, for text type, postgresql knows this is a text but doesnt know the encoding, as my example showed. This goes against the concept of text vs bytes distintion, which per se is very useful and powerful (specially in this Unicode world) and leads to a dubious/clumsy string api (IMHO, as always). Umm, I think all you showed was that the to_ascii() function was broken. Postgres knows exactly what encoding the string is in, the backend encoding: in your case UTF-8. Now, it would be nice if postgres could handle other encodings in the backend, but there's no agreement on how to implement that feature so it isn't implemented. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
