Re: [GENERAL] Statistics collection question
On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: On 04/09/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select statement times? Because the statement has been executed and is in the cache. That answer is way too flippant. In particular it doesn't explain your repeated 80sec queries --- you should have enough memory in that thing to be caching a fair amount of your data. I'm wondering about some transaction taking exclusive lock on the table and sitting on it for a minute or so, and also about network problems delaying transmission of data to the client. How can I check what is causing the lack? When I restart pgsql it goes away. The log is empty for a day or too (I'm only logging errors or slow queries) and the queries are super fast, but after a day it starts filling up with abysmally slow queries, even on simple queries with the WHERE clauses that have only one constant on the indexed column! As for network problems delaying transmission of data -- not sure what this means. MySQL is super fast on the very same system. Does pgsql require anything different? Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and Exim (email server) on the same dedicated hosting server. I don't mind if Postgres hogs 2GB of memory, but I need to know how to tweak it. I have made about eight posts on this list with my entire postgresql.conf posted in. I have read and re-read the manual and devoured as many google-groups archives of this list as I possibly can. I am looking at plenty of catalogue and stats tables (a utility that makes compiles all of it and presents the system's missteps and guidelines may be useful, ala Tuning Primer script from MySQL camp) but I am not sure where to begin! Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial concurrent selects (about 45 million a day). Works fine when I restart, but a day later all goes cattywumpus. TIA! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Statistics collection question
Sounds like index bloat to me ... lots of updates of indexed columns = lots of extra dead index entries. Since IIRC PostgreSQL indexes (indicii?) don't store information about the liveness of the referenced rows, indexed reads would have to sort through a lot of dead wood to find the few live indexed entries. If you can, try to schedule a few minutes of down time every N hours and reindex the effected tables, followed by a vacuum/analyze to reclaim dead space and update stats maybe ? Admittedly hard but perhaps easier to have 5-10 minutes of down time regularly rather than very slow queries for hours on end. If this works even as a temporary solution it might point the way to a better long term fix. It sounds as if you have too many services on one server -- the contentions of each for memory and disk I/O would worry me a lot. I tend to like having dedicated DB servers except for certain light-weight development environments. And stop trying to make PostgreSQL into MySQL, or vice versa. Different engines, different regimes. Not translatable me thinks. Just sodden thoughts ... sorry for top posting (challenged email tool). Greg Williamson Senior DBA GlobeXplorer LLC, a DigitalGlobe company Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message. (My corporate masters made me say this.) -Original Message- From: [EMAIL PROTECTED] on behalf of Phoenix Kiula Sent: Tue 9/4/2007 1:07 AM To: Tom Lane Cc: Richard Broersma Jr; Alban Hertroys; Postgres General Subject: Re: [GENERAL] Statistics collection question On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: Phoenix Kiula [EMAIL PROTECTED] writes: On 04/09/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: Thats odd, I wonder why the EXPLAIN ANALYZE time id some much less that the logged select statement times? Because the statement has been executed and is in the cache. That answer is way too flippant. In particular it doesn't explain your repeated 80sec queries --- you should have enough memory in that thing to be caching a fair amount of your data. I'm wondering about some transaction taking exclusive lock on the table and sitting on it for a minute or so, and also about network problems delaying transmission of data to the client. How can I check what is causing the lack? When I restart pgsql it goes away. The log is empty for a day or too (I'm only logging errors or slow queries) and the queries are super fast, but after a day it starts filling up with abysmally slow queries, even on simple queries with the WHERE clauses that have only one constant on the indexed column! As for network problems delaying transmission of data -- not sure what this means. MySQL is super fast on the very same system. Does pgsql require anything different? Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and Exim (email server) on the same dedicated hosting server. I don't mind if Postgres hogs 2GB of memory, but I need to know how to tweak it. I have made about eight posts on this list with my entire postgresql.conf posted in. I have read and re-read the manual and devoured as many google-groups archives of this list as I possibly can. I am looking at plenty of catalogue and stats tables (a utility that makes compiles all of it and presents the system's missteps and guidelines may be useful, ala Tuning Primer script from MySQL camp) but I am not sure where to begin! Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial concurrent selects (about 45 million a day). Works fine when I restart, but a day later all goes cattywumpus. TIA! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Statistics collection question
On Tue, Sep 04, 2007 at 03:07:41PM +0800, Phoenix Kiula wrote: How can I check what is causing the lack? When I restart pgsql it goes away. The log is empty for a day or too (I'm only logging errors or slow queries) and the queries are super fast, but after a day it starts filling up with abysmally slow queries, even on simple queries with the WHERE clauses that have only one constant on the indexed column! Check you're not running VACUUM FULL anywhere and post the (complete) output of VACUUM VERBOSE as superuser. That way we can rule out index/table bloat. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Instances where enable_seqscan = false is good
Ow Mun Heng [EMAIL PROTECTED] writes: Have not changed anything in that area. Question is.. Do I need to? or should I try out something just to see how it is? (any) Recommendations would be good. Sorry, I don't have all the original plans. Can you post the explain analyze with and without enable_seqscan now that the stats are giving good predictions? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Statistics collection question
Phoenix Kiula wrote: On 04/09/07, Tom Lane [EMAIL PROTECTED] wrote: I'm wondering about some transaction taking exclusive lock on the table and sitting on it for a minute or so, and also about network problems delaying transmission of data to the client. How can I check what is causing the lack? When I restart pgsql it goes You mean the lock? You can check for active locks querying pg_locks away. The log is empty for a day or too (I'm only logging errors or slow queries) and the queries are super fast, but after a day it starts filling up with abysmally slow queries, even on simple queries with the WHERE clauses that have only one constant on the indexed column! That's new information that we could have used earlier, as it means that postgres does pick the right plan (at least initially) and things like network and dns apparently work. Was the explain analyze you sent from the super fast periods or from a slow period? It'd be interesting to see a query plan of a problematic query. I suppose if you try one of your super fast queries it is slow once other queries slow down too? I ask, because I expect that query to not be in the cache at that moment, so it could be a good candidate for an explain analyze. Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. I run Apache, MySQL, Postgres, and Exim (email server) on the same dedicated hosting server. I don't mind if Postgres hogs 2GB of memory, but I need to know how to tweak it. I have made about eight posts on this list with my entire postgresql.conf posted in. I have read and re-read the manual and Yes, but you gave us conflicting information. Only now it is clear what your problem is. that makes compiles all of it and presents the system's missteps and guidelines may be useful, ala Tuning Primer script from MySQL camp) but I am not sure where to begin! I've seen pgadmin III doing quite a nice job at that. Haven't really used it myself, I usually prefer the command line. Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial It looks like your indexes get bloated. Do you vacuum enough? It'd be a good idea to at least analyze the tables involved in those inserts regularly. If you do those inserts in a batch, be sure to call ANALYZE after commiting that batch. That helps quite a bit. Besides that... How are those disks configured? You didn't put them in a raid-5 array I hope? That wouldn't explain the above problem, but it would slow things down (such has been mentioned on this list a few times) and may thus be exaggerating the problem. Good luck! -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Suggestion for new function on pg_catalog: get_config()
Luiz K. Matsumura wrote:
By the way,
select setting AS default_tablespace from pg_show_all_settings() x(name
text, setting text, unit text, category text, short_desc text, extra_desc
text, context text, vartype text, source text, min_val text, max_val text)
where name = 'default_tablespace'
and
select current_setting('default_tablespace' )
can be considered equivalent ?
Probably. Also try
select * from pg_settings;
--
Alvaro Herrera Developer, http://www.PostgreSQL.org/
Industry suffers from the managerial dogma that for the sake of stability
and continuity, the company should be independent of the competence of
individual employees. (E. Dijkstra)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Statistics collection question
Phoenix Kiula [EMAIL PROTECTED] writes: Basically, what I am missing is some info on actually tweaking the postgresql.conf to suit my system. No, that's *not* what you're missing. I'm not sure what the problem is in your system, but I'm pretty sure that everything you have frantically been tweaking is unrelated if not outright counterproductive. You need to stop tweaking and start some methodical evidence-gathering to figure out what the problem actually is. Here are some things I would suggest trying: 1. Do a VACUUM VERBOSE when the system is fast, and save the output. When the system is slow, do another VACUUM VERBOSE, and compare file sizes to see if anything seems markedly bloated. (It might be less labor-intensive to copy pg_class.relname, reltuples, relpages columns into another table for safekeeping after the first VACUUM, and use SQL queries to look for markedly different sizes after the second VACUUM.) 2. Set up a task to dump the results of select * from pg_locks, pg_stat_activity where pid = procpid into a log file every few seconds. Compare what you see when things are fast with when they are slow. In particular you should fairly easily be able to tell if the slow queries are waiting long for locks. 3. Log the output of vmstat 1 over time, compare fast and slow periods. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Data Warehousing
I am on a Linux platform but I'm going to need some pointers regarding the cron job. Are you suggesting that I parse the dump file? I assume I would need to switch to using inserts and then parse the dump looking for where I need to start from? Something that you may want to consider is dblink from contrib. We have a similar situation for the archiving of collected data and have been able to implement a fairly easy solution that does not require the parsing of dump files, just a simple(ish) query based on the time inserted. -Ken ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Statistics collection question
Phoenix Kiula [EMAIL PROTECTED] writes: Would appreciate any help. Why do indexed queries take so much time? It's a simple DB with 10 relations including tables and indexes. Simple inserts and updates, about 5000 a day, but non-trivial concurrent selects (about 45 million a day). Works fine when I restart, but a day later all goes cattywumpus. BTW, just to be perfectly clear: all you do is stop and restart the postmaster (using what commands exactly?), and everything is fast again? That's sufficiently unheard-of that I want to be entirely sure we understood you correctly. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Obtaining random rows from a result set
To follow up on my own post, I came up with a workable solution based on
scrolling cursors. The SP approach didn't work out for me, I didn't
manage to declare a cursor in PL/pgSQL that could be positioned
absolutely (maybe that's due to us still using PG 8.1.something?).
A solution to that would be appreciated.
Anyway, I solved the problem in our application (PHP). I even got a
workable solution to prevent returning the same record more than once.
Here goes:
function randomSet($query, $limit, $uniqueColumn) {
// queries; depends on your DB connector
DECLARE _cur SCROLL CURSOR WITHOUT HOLD FOR $query;
MOVE FORWARD ALL IN _cur;
//GET DIAGNOSTICS _count := ROW_COUNT;
$count = pg_affected_rows();
$uniques = array();
$resultSet = array();
while ($limit 0 count($uniques) $count) {
$idx = random(1, $count);
//query
$record = FETCH ABSOLUTE $idx FROM _cur;
// Skip records with a column value we want to be unique
if (in_array($record[$uniqueColumn], $uniques)
continue;
$uniques[] = $record[$uniqueColumn];
$resultSet[] = $record;
$limit--;
}
// query
CLOSE _cur;
return $resultSet;
}
I hope this is useful to anyone. It worked for us; it is definitely
faster than order by random(), and more random than precalculated column
values. Plus it translates directly to what we are requesting :)
Alban Hertroys wrote:
I thought of another solution (with only a few calculations of random())
that can be deployed in existing versions of PG, using a set-returning
function with a scrolling cursor that accepts the query string as input
like this (in pseudoish-code):
create function random(text _query, integer _limit)
returns set
volatile
as $$
DECLARE
_cur cursor;
_cnt bigint;
_idx integer;
_rowpos bigint;
_rec record;
BEGIN
open _cur for execute query;
fetch forward all into _rec;
-- select total nr of records into _cnt
for _idx in 1.._limit loop
_rowpos := random() * _cnt;
fetch absolute _rowpos into _rec;
return next _rec;
end loop;
return;
END;
$$
language 'plpgsql';
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Obtaining random rows from a result set
Alban Hertroys wrote: To follow up on my own post, I came up with a workable solution based on scrolling cursors. The SP approach didn't work out for me, I didn't manage to declare a cursor in PL/pgSQL that could be positioned absolutely (maybe that's due to us still using PG 8.1.something?). Doh! I mean I couldn't use MOVE FORWARD ALL IN _cur for some reason, it kept saying Syntax error. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 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] SELECT question (splitting a field)
Hi all,
Hopefully a quick question...
Why does:
nmc= SELECT 'Y' AS local FROM domains WHERE dom_name='test.com';
local
---
Y
(1 row)
Work but:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
I am sure I am missing something simple. :)
Thanks!!
Madi
---(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] Database owner can't analyze/vacuum all of the database tables
Hi all, When I try to analyze (or vacuum) the database with the owner of the database I receive the following warnings: help2.com= ANALYZE ; WARNING: skipping pg_authid --- only table or database owner can analyze it WARNING: skipping pg_tablespace --- only table or database owner can analyze it WARNING: skipping pg_pltemplate --- only table or database owner can analyze it WARNING: skipping pg_shdepend --- only table or database owner can analyze it WARNING: skipping pg_shdescription --- only table or database owner can analyze it WARNING: skipping pg_database --- only table or database owner can analyze it WARNING: skipping pg_auth_members --- only table or database owner can analyze it ANALYZE I checked at the documentation and found that those tables are shared across all of the databases of the cluster. So my question is, is it important to analyze/vacuum them? or maybe because they are scarcely used (I don't even know if this assumption is right) it's not important to analyze/vacuum them? I can always change their owner to the database owner but I guess that if the database owner didn't get ownership over those tables too then there is a reason for that... Thanks a lot in advance, Yonatan Ben-Nes
Re: [GENERAL] Reporting services for PostgreSQL
Try OpenRPT - server side rendering engine, and client-side GUI designer. http://sourceforge.net/projects/openrpt Cheers, Ned On 9/1/2007 7:12 AM Andrus wrote: I'm looking for a report generator which renders reports in server and sends rendering result to client. any idea ? Andrus. ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SELECT question (splitting a field)
On 9/4/07, Madison Kelly [EMAIL PROTECTED] wrote: I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' '@test.com' ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] UTF8 frustrations
Trying to do pg_restore from one UTF8 encoded db to another UTF8 encoded db DB_source: Red Hat Enterprise Linux AS release 4 (Nahant Update 4) psql 8.2.4 DB Destination: Debian GNU/Linux 4.0 psql 8.1.9 I've tried: pg_dump from the source box from destination box from destination server pg_dump -i -h source_server db db.8.1.9.sql No matter which way I issue pg_dump command it always fails on the same record. ERROR: pg_restore: ERROR: invalid byte sequence for encoding UTF8: 0xdf69 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: COPY logs, line 69238382 pg_restore: [archiver (db)] error returned by PQendcopy: ERROR: invalid byte sequence for encoding UTF8: 0xdf69 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: COPY logs, line 69238382 UTF8 to UTF8 and everything in between is UTF8, and it still fails, I'm tempted to delete this record and keep going but I'm determined to find a solution. TIA for any help, Jesse Waters ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT question (splitting a field)
On 9/4/07, Madison Kelly [EMAIL PROTECTED] wrote:
Hi all,
Hopefully a quick question...
Why does:
nmc= SELECT 'Y' AS local FROM domains WHERE dom_name='test.com';
local
---
Y
(1 row)
Work but:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
because @dom_name isn't IN [EMAIL PROTECTED]
I think you're looking for pattern matching.
select 'Y' as local from domains where dom_name ilike '%test.com%'
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] SELECT question (splitting a field)
Madison Kelly wrote:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
I don't think IN does what you think it does. It's not a substring-test,
but a set test:
SELECT 1 WHERE 'x' IN ('a','b','c','x');
SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzledwamble);
You could mess around with substring() and length() or I'd use LIKE.
If it's just a domain you're looking for though, might be most efficient
to strip the leading part off your value with regexp_replace().
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] SELECT question (splitting a field)
Rodrigo De León wrote: On 9/4/07, Madison Kelly [EMAIL PROTECTED] wrote: I am sure I am missing something simple. :) Yeah... '[EMAIL PROTECTED]' '@test.com' Well now, don't I feel silly. *sigh* Thanks! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Database owner can't analyze/vacuum all of the database tables
Yonatan Ben-Nes wrote: Hi all, When I try to analyze (or vacuum) the database with the owner of the database I receive the following warnings: WARNING: skipping pg_authid --- only table or database owner can analyze ... WARNING: skipping pg_auth_members --- only table or database owner can I checked at the documentation and found that those tables are shared across all of the databases of the cluster. So my question is, is it important to analyze/vacuum them? or maybe because they are scarcely used (I don't even know if this assumption is right) it's not important to analyze/vacuum them? You'll need to vacuum often enough to prevent transaction wraparound. You might need to vacuum reasonably often if you create drop a lot of system objects (databases/users etc) and end up with bloated tables. I can always change their owner to the database owner but I guess that if the database owner didn't get ownership over those tables too then there is a reason for that... Don't know if you could have a user other than postgres owning them. Not sure anyone's tried. I think most people schedule their vacuum/autovacuum to run as postgres anyway - particularly if they have more than one database in an installation. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] UTF8 frustrations
On Sep 4, 2007, at 14:48 , [EMAIL PROTECTED] wrote: Trying to do pg_restore from one UTF8 encoded db to another UTF8 encoded db DB_source: Red Hat Enterprise Linux AS release 4 (Nahant Update 4) psql 8.2.4 DB Destination: Debian GNU/Linux 4.0 psql 8.1.9 UTF8 to UTF8 and everything in between is UTF8, and it still fails, I'm tempted to delete this record and keep going but I'm determined to find a solution. (1) PostgreSQL does not support dump and restore from a new version of PostgreSQL to an older version. (2) There were changes to UTF8 handling between 8.1 and 8.2. I'm a bit surprised you get an error like this as 8.1 is more lax than 8.2: it accepts some invalid sequences as valid. If you're really determined to do this, I recommend editing the dump file by hand to remove or edit this record so it does not contain the invalid sequence (as you've suggested). Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] SELECT question (splitting a field)
Richard Huxton wrote:
Madison Kelly wrote:
nmc= SELECT 'Y' AS local FROM domains WHERE '@'||dom_name IN
('[EMAIL PROTECTED]');
local
---
(0 rows)
Not work?
I don't think IN does what you think it does. It's not a substring-test,
but a set test:
SELECT 1 WHERE 'x' IN ('a','b','c','x');
SELECT a FROM foo WHERE b IN (SELECT z FROM bar WHERE frozzledwamble);
You could mess around with substring() and length() or I'd use LIKE.
If it's just a domain you're looking for though, might be most efficient
to strip the leading part off your value with regexp_replace().
Yeah, that was my problem. I thought I was using the section following
the '@'. =/
I've been using Postgres for a while now, but only recently getting into
some of the fancier stuff. Until now, I've usually written the program
using PgSQL so I could manipulate the data as I needed. Now I am using
PgSQL as a backend for a few other applications so I am restricted to
using PgSQL to manipulate the data.
It's all left me feeling quite n00bish again. ;)
I did figure out a query that worked:
SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
AND u.usr_email||'@'||d.dom_name IN ('[EMAIL PROTECTED]');
Though this may not be the most efficient. In my case, the 'usr_email'
is the LHS of the '@' sign and 'dom_name' is the domain name. If I
wanted to use (I)LIKE, how would I have matched just the domain section
of '[EMAIL PROTECTED]' in 'dom_name'?
I'll go read up, now that I've got some key words to search the docs on.
Thanks kindly!
Madi
---(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] SELECT question (splitting a field)
Madison Kelly wrote:
SELECT 'Y' AS local FROM domains d, users u WHERE u.usr_dom_id=d.dom_id
AND u.usr_email||'@'||d.dom_name IN ('[EMAIL PROTECTED]');
Though this may not be the most efficient. In my case, the 'usr_email'
is the LHS of the '@' sign and 'dom_name' is the domain name. If I
wanted to use (I)LIKE, how would I have matched just the domain section
of '[EMAIL PROTECTED]' in 'dom_name'?
Something like:
SELECT ... FROM domains d WHERE ('%@' || d.dom_name) LIKE '[EMAIL PROTECTED]';
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] UTF8 frustrations
On 9/4/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Trying to do pg_restore from one UTF8 encoded db to another UTF8 encoded db DB_source: Red Hat Enterprise Linux AS release 4 (Nahant Update 4) psql 8.2.4 DB Destination: Debian GNU/Linux 4.0 psql 8.1.9 I've tried: pg_dump from the source box from destination box from destination server pg_dump -i -h source_server db db.8.1.9.sql Are you issuing the dump and psql / restore command on the same machine? As previously mentioned, dumping from newer to older is not supported. pg_dump from 8.1 might not understand the data structures it finds in an 8.2 db, and pg_dump from 8.2 might create a dump that 8.1 doesn't support. Note that going the other way you are encouraged to use pg_dump from 8.2 to dump the 8.1 database for importing to 8.2. However, In this case I'm guessing that the problem is that you've got different client encodings on each end. i.e. you're dumping with one encoding setting and restoring with another. Note that pgsql autoconverts from the server's encoding to the client's encoding at the request of the client. so, you'll need to check your client encoding from psql on the source and target machines to see if they match. show client_encoding ; will tell you what your client encoding is. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] psql hanging
What would cause psql to hang indefinitely when the backend disappears? We have a script that uses psql to insert a record (TCP connection to DB on different machine). The command is basically psql connection_stuff -c insert into... A while back I had to restart the server and today discovered that some of the client machines have psql processes dating back several months. Obviously no TCP connection on the server end but client-side shows the connection as ESTABLISHED. -Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Documentation fix regarding atan2
In Table 9.4 of the documentation atan2 is described as follows: atan2(*x*, *y*) inverse tangent of *x*/*y* I am sure it should read as: atan2(*y*, x) inverse tangent of y/x This looks to be the standard C++/c atan2(y,x) function. You can easily test this: If y = 2, x = 1, then degrees(atan(y/x)) =63.4 but if we proceed according to the documentation; degrees(atan2(x,y))=degrees(atan2(1,2))=25.6 which is not the same as degrees(atan(y/x)). So it must be degrees(atan2(y,x))=degrees(atan2(2,1))=63.4. Thanks Andrew -- ___ Andrew J. P. Maclean Centre for Autonomous Systems The Rose Street Building J04 The University of Sydney 2006 NSW AUSTRALIA Ph: +61 2 9351 3283 Fax: +61 2 9351 7474 URL: http://www.acfr.usyd.edu.au/ ___
[GENERAL] Controlling locale and impact on LIKE statements
Hi! I am having a bit of trouble with indexes, locales and LIKE queries. Background -- Using Pg8.1/8.2 on a utf-8 database, my left-anchored LIKE clauses were forcing a full table scan instead of using the index. After a bit of digging, I found that Pg can only use the normal index for left-anchored LIKE queries if locale is 'C'. From http://www.postgresql.org/docs/8.1/static/indexes-types.html : The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries. What I think I need to do - As I have a Pg install where the locale is already en_US.UTF-8, and the database already exists, is there a DB-scoped way of controlling the locale? I think the index usage noted above is affected by lc_ctype but I could be wrong. I really don't want to go down the rebuild your pgcluster path as outlined here http://archives.postgresql.org/pgsql-hackers/2007-02/msg00992.php ;-) Is there a better way? In this specific install I can create the additional index... However, this needs a general fix for Moodle, which has an abstract DB schema handling, as we support MySQL, Pg, MSSQL, Oracle. The whole thing of figuring out what the locale is and whether to add magical additional indexes just for Pg makes me look like a loony. New PostgreSQL installs on modern linuxen like Ubuntu default to non-C locales, which makes this more of an issue going forward. See the discussion with Eloy (maintainer of the schema abstraction layer) at http://moodle.org/mod/forum/discuss.php?d=78738#p350512 login as guest to avoid registration. cheers, martin -- --- Martin @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St NZ: +64(4)916-7224MOB: +64(21)364-017UK: 0845 868 5733 ext 7224 Make things as simple as possible, but no simpler - Einstein --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Controlling locale and impact on LIKE statements
Hi! Background: Using Pg8.1/8.2 on a utf-8 database, I found out that my left-anchored LIKE clauses were forcing a full table scan instead of using the index. After a bit of digging, I found that Pg can only use the normal index for left-anchored LIKE queries if locale is 'C'. The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string — for example, col LIKE 'foo%' or col ~ '^foo', but not col LIKE '%bar'. However, if your server does not use the C locale you will need to create the index with a special operator class to support indexing of pattern-matching queries. http://www.postgresql.org/docs/8.1/static/indexes-types.html What I think I need to do: As I have a Pg install where the locale is already en_US.UTF-8, and the database already exists, is there a DB-scoped way of controlling the locale? I think the index usage noted above is affected by lc_ctype but I could be wrong. I really don't want to go down the rebuild your pgcluster path as outlined here http://archives.postgresql.org/pgsql-hackers/2007-02/msg00992.php ;-) Is there a better way? In this specific install I can create the additional index. However, this needs a general fix for Moodle, which has an abstract DB schema handling (we support MySQL, Pg, MSSQL, Oracle) and the whole thing of figuring out what the locale is and whether to add magical additional indexes just for Pg makes me look like a loony. See the discussion with Eloy (maintainer of the schema abstraction layer) at http://moodle.org/mod/forum/discuss.php?d=78738#p350512 login as guest to avoid registration. cheers, martin ---(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] Connecting to PostgreSQL server with Mono using ident authetication
it is my first shot using Mono and I failed to get the example from http://www.mono-project.de/wiki/keyword/PostgreSQL/ working. The reason is obviousely that whatever I tried NpgsqlConnection tries to use password authentication but I have configured my system that ident authentication is used by default. I'm using Debian GNU/Linux testing running postgresql 8.2 and mono 1.2. Could anybody enlight me how to connect to a database where users have no password set because always ident authentication is used on local host. The best list for this kind of thing is certainly the Npgsql mailing list: http://gborg.postgresql.org/mailman/listinfo/npgsql-general What error messages do you get when you try to connect? Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to 'register' functions, so they can be called (plpythonu)
I am defining some functions using plpythonu, through the standard means.
Here I have one function (test1) which calls another (testfunc). When
I excute this I get the following error:
ERROR: plpython: function test1 failed
DETAIL: type 'exceptions.NameError': global name 'testfunc' is not defined
However, from the console, select testfunc('test') works fine.
I was wondering how do I 'register'/'reference' these functions so
that one function can call another? I am guessing this is important,
i.e. how will I access the complete functionality of the general
python libs?
Cheers, Blay.
PS functions are defined in the usual way - e.g.
create or replace function test1(text) returns integer as $$
return 1
$$ language plpythonu;
---(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] Partition Reindexing
What is the effect of reindexing a partition on the inherited table? For example I have a table 'test' with partitions 'test_01_07', 'test_02_07', 'test_03_07', 'test_04_07', corresponding to data from January 2007, February 2007, and so on. I noticed that when I run a reindex on 'test_02_07' (with a large index ~3Gb) the inserts into 'test_04_07' are extremely slow eventually locking up the entire database. Is there a way I could run the reindexing/vacuuming/analyzing processes without affecting the rest of the partitions? This is on PostgreSQL 8.1.3 on Windows 2003 Advanced Server. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Controlling locale and impact on LIKE statements
Martin Langhoff escribió: As I have a Pg install where the locale is already en_US.UTF-8, and the database already exists, is there a DB-scoped way of controlling the locale? Not really. Is there a better way? In this specific install I can create the additional index. However, this needs a general fix for Moodle, which has an abstract DB schema handling (we support MySQL, Pg, MSSQL, Oracle) and the whole thing of figuring out what the locale is and whether to add magical additional indexes just for Pg makes me look like a loony. You are right and Eloy is wrong on that discussion. There is not anything the DB can do to use the regular index if the locale is not C for LIKE queries. There are good reasons for this. There's not much option beyond creating the pattern_ops index. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 Most hackers will be perfectly comfortable conceptualizing users as entropy sources, so let's move on. (Nathaniel Smith) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Documentation fix regarding atan2
On Wed, Sep 05, 2007 at 10:37:18 +1000, Andrew Maclean [EMAIL PROTECTED] wrote: In Table 9.4 of the documentation atan2 is described as follows: atan2(*x*, *y*) inverse tangent of *x*/*y* I am sure it should read as: atan2(*y*, x) inverse tangent of y/x Aren't those two statements sayiong the same thing? You've just switched the names 'x' and 'y' and not changed their relationships. You can easily test this: If y = 2, x = 1, then degrees(atan(y/x)) =63.4 but if we proceed according to the documentation; degrees(atan2(x,y))=degrees(atan2(1,2))=25.6 which is not the same as degrees(atan(y/x)). In this example you switched things around part way thorugh. atan2(1,2) is the atan of (1/2), not atan(2/1) as used at the beginning of the example. So it must be degrees(atan2(y,x))=degrees(atan2(2,1))=63.4. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Controlling locale and impact on LIKE statements
Alvaro Herrera [EMAIL PROTECTED] writes: Martin Langhoff escribió: the whole thing of figuring out what the locale is and whether to add magical additional indexes just for Pg makes me look like a loony. You are right and Eloy is wrong on that discussion. There is not anything the DB can do to use the regular index if the locale is not C for LIKE queries. There are good reasons for this. There's not much option beyond creating the pattern_ops index. Indeed *all* indexes are magical additional things added just for the one database. There's not any standard definition of what indexes you'll need for all databases out there. Indexes aren't even in the SQL standard because they're part of performance tuning for each individual database engine. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Documentation fix regarding atan2
Bruno Wolff III [EMAIL PROTECTED] writes: Andrew Maclean [EMAIL PROTECTED] wrote: In Table 9.4 of the documentation atan2 is described as follows: atan2(*x*, *y*) inverse tangent of *x*/*y* I am sure it should read as: atan2(*y*, x) inverse tangent of y/x Aren't those two statements sayiong the same thing? They're logically equivalent but I think Andrew is right that y/x corresponds to the usual interpretation of X and Y directions in trigonometry. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to 'register' functions, so they can be called (plpythonu)
blay bloo wrote:
I am defining some functions using plpythonu, through the standard means.
Here I have one function (test1) which calls another (testfunc). When
I excute this I get the following error:
ERROR: plpython: function test1 failed
DETAIL: type 'exceptions.NameError': global name 'testfunc' is not defined
However, from the console, select testfunc('test') works fine.
I was wondering how do I 'register'/'reference' these functions so
that one function can call another?
You can't, because the name you give to the function lives in Postgres'
namespace, not Python's. Therefore you can only call the other function
using the SPI interface that PL/Python offers you.
I am guessing this is important,
i.e. how will I access the complete functionality of the general
python libs?
This is quite different -- I'm guessing the Python library can be
accessed via normal means. What you cannot do is use Postgres' CREATE
FUNCTION to define a regular Python function (i.e. you cannot extend the
library).
--
Alvaro Herrerahttp://www.advogato.org/person/alvherre
Bob [Floyd] used to say that he was planning to get a Ph.D. by the green
stamp method, namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree. (Don Knuth)
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] work hour calculations
Hello All, SELECT notification_time, finished_time, sum(finished_time - notification_time) as actual FROM log GROUP BY notification_time, finished_time; gives me: notification_time| finished_time | actual ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 2 days 15:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 1 day 18:50:00 How can write a query to calculate the duration using custom work hours which is Monday 7am / Friday 5pm? The result I'm expecting for the above to be notification_time| finished_time | actual ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 00:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] work hour calculations
correction: The result I'm expecting for the above to be notification_time| finished_time | actual ++- 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 01:20:00 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Documentation fix regarding atan2
A Cartesian coordinate system is generally assumed i.e there exists an x-y coordinate system so there is an inherent ordering property here. Regarding atan2, this makes interesting reading: http://en.wikipedia.org/wiki/Atan2 All I am asking is the documentation for atan2 conform with the correct definition. You are actually using atan2(y,x) in postgresql. The inverse tangent is defined as arctan(y/x). Hence atan2 should be atan2(y,x) to be consistent with this definition. This conforms with C++, C usage. Andrew -Original Message- From: Bruno Wolff III [mailto:[EMAIL PROTECTED] Sent: Wednesday, 5 September 2007 12:34 To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Documentation fix regarding atan2 On Wed, Sep 05, 2007 at 10:37:18 +1000, Andrew Maclean [EMAIL PROTECTED] wrote: In Table 9.4 of the documentation atan2 is described as follows: atan2(*x*, *y*) inverse tangent of *x*/*y* I am sure it should read as: atan2(*y*, x) inverse tangent of y/x Aren't those two statements sayiong the same thing? You've just switched the names 'x' and 'y' and not changed their relationships. You can easily test this: If y = 2, x = 1, then degrees(atan(y/x)) =63.4 but if we proceed according to the documentation; degrees(atan2(x,y))=degrees(atan2(1,2))=25.6 which is not the same as degrees(atan(y/x)). In this example you switched things around part way thorugh. atan2(1,2) is the atan of (1/2), not atan(2/1) as used at the beginning of the example. So it must be degrees(atan2(y,x))=degrees(atan2(2,1))=63.4.
Re: [GENERAL] Connecting to PostgreSQL server with Mono using ident authetication
On Mon, 4 Sep 2007, Albe Laurenz wrote: The best list for this kind of thing is certainly the Npgsql mailing list: http://gborg.postgresql.org/mailman/listinfo/npgsql-general Just subscribed. What error messages do you get when you try to connect? When I use the connection string string connStr = Server=127.0.0.1;Port=5432;User=tillea;Database=InfluenzaWeb;; I get Npgsql.NpgsqlException: Passwort-Authentifizierung für Benutzer 'tillea' fehlgeschlagen -- Translation: Password authentication for user 'tillea' failed Severity: FATAL Code: 28000 at Npgsql.NpgsqlConnector.CheckErrors () [0x0] at Npgsql.NpgsqlConnector.Open () [0x0] at Npgsql.NpgsqlConnectorPool.GetPooledConnector (Npgsql.NpgsqlConnection Connection) [0x0] So npgsql is obviousely trying password authentication and I have no idea how to ask for ident authetication. I also tried Server=localhost If I leave out the Server parameter at all I get System.ArgumentException: Connection string argument missing! Parameter name: SERVER (which is different to other psql connectors I know from Python or Perl). Kind regards Andreas. PS: I've seen a another private answer to my mail in my incoming box but due to a problem here it was deleted. :( Could the kind poster please repost his mail because I don't see it in the archive? Many thanks, Andreas. -- http://fam-tille.de ---(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
