Re: [GENERAL] funny view/temp table problem with query
On Feb 25, 2009, at 11:02 AM, Grzegorz Jaśkiewicz wrote: So I have a 'accounts' table, with id and name, and than some hypothetical 'packages' table, containing some info per customer. I need to retrive distinct pairs , of random packages assigned per customer. Packages table contains 10 packages, id:=[1:10], there's 1M customers for testing purposes. I could name the tables foo/bar again, but decided for something more creative this time ;) Anyways, I have this query: select count(distinct (v,id)) from ( select heh.id, v[i] from ( SELECT ss.id, ARRAY ( SELECT id FROM packages where ss.id0 and id between 2 and 6 ORDER BY random() limit 5 ) as v FROM ( SELECT id FROM accounts ORDER BY random() limit 10 ) ss ) heh,generate_series(1, 5 ) i order by heh.id,v ) ziew; An alternative solution is to NOT order by random and not to limit, but to use a scrollable cursor. Having to order your entire result set by random is a fairly expensive operation and you only want 5 random rows anyway, not 10, so it is an inefficient approach as well: In a good solution you should be calculating random() 5 times, not 10. Normal cursors just pick the next row from the result set as you request them. Scrollable ones allow you to pick specific rows from that result set. As soon as you know how many rows you have, picking 5 random ones isn't that hard. The idea is to calculate 5 random row numbers from your result set and retrieve only those rows. To do this you'll first need to know how many rows there are. That can be determined by scrolling to the last row and reading the instruction result (not the record itself) of that instruction; it contains a row number (mind the one-off difference with a row count). That row number you can feed to the random() function so it returns numbers from 1..(lastRow+1). Scroll to that row and read the result, repeat as often as you like (5 times in your case). You may have realised that there is a chance to get duplicates here if you happen to calculate the same random row number more than once. That's not very hard to fix of course, you only need to keep track of which row numbers you already used and recalculate the random number if it's already in your set. You can put the code to do this in your application (if your connection interface allows for scrollable cursors), or since pg8.3 you can create a stored procedure to do this. I believe before 8.3 scrollable cursors weren't usable in pl/pgsql. Then again, maybe other pl-languages are more suitable for a general solution... Back when I had this problem I was using PHP and pg8.1, putting the code in a function in my application worked fine, but it felt like it didn't belong there. The general opinion seems to be that picking random rows isn't a relational operation, and for that reason a relational database isn't particularly good at that. I think my approach works as well as it does because it's a procedural approach to a procedural problem. If you'd like to see some code, I have posted about this in the past and that contained some code examples. Just search the archives. 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,49a664fc129742059914308! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Format string for ISO-8601 date and time
Hi, Is there a format string for to_char(timestamptz, text) that would output a timestamp in full ISO-8601 format? That is, something like 1977-04-22T01:00:00-05:00 I can't find a way to extract the offset against GMT from the docs here: http://www.postgresql.org/docs/8.3/static/functions-formatting.html If not, what would be the way to convert a timestamp to such a string regardless of the session's datestyle settings? Thanks, -- Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] funny view/temp table problem with query
looks like you completely misunderstood my question. First of all, I wonder why the same query divided up in half - and using temporary table works as expected, and with everything together doesn't. And about rand(), it was tested on large enough set of runs, that I don't think it is to blame. The queries do everything I wanted it to do, and - no - doing it in software is just baaad, and doesn't do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Can I use a query with UPDATE on its SET?
Hi Ho! --- On Thu, 2/26/09, Craig Ringer cr...@postnewspapers.com.au wrote: Eus wrote: Hi Ho! Since I can do: INSERT INTO table (SELECT a_transaction.*); I am wondering whether I can do: UPDATE table SET (SELECT a_transaction.*) WHERE primary_key = (SELECT a_transaction.primary_key); UPDATE table SET fieldname = (SELECT ..) WHERE primary_key = (SELECT...) Is it possible to eliminate the use of `fieldname' completely? So, I just need to type `UPDATE table SET (SELECT ...) WHERE primary_key'. I think this should be possible because if the subquery in the SET clause returns the same number of columns with the same types of those of the destination table, PostgreSQL should be able to just update the values at once. Thanks. -- Craig Ringer Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Format string for ISO-8601 date and time
Hello 2009/2/26 Daniel Verite dan...@manitou-mail.org: Hi, Is there a format string for to_char(timestamptz, text) that would output a timestamp in full ISO-8601 format? That is, something like 1977-04-22T01:00:00-05:00 I can't find a way to extract the offset against GMT from the docs here: http://www.postgresql.org/docs/8.3/static/functions-formatting.html If not, what would be the way to convert a timestamp to such a string regardless of the session's datestyle settings? try to look on function extract, there you can get timezone from any timestamp with time zone. regards Pavel Stehule Thanks, -- Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql with storage
Hi, I have some basic query in postgresql with storage, Please help me for the following 1. What is the best practice to use postgresql with storage. 2. Which are the files and folders we can keep them in storage. Regards Karthikeyan.N
Re: [GENERAL] postgresql with storage
Hi, Not entirely sure what you mean, but here goes 1.Use a hight performance storage device (as applies with all databases) 2. everything that uis located in the PostgreSQL datadirectory can be located on any device you prefer as long as the availability is guaranteed from the perspective of the PostgreSQL processes. It would be helpful if you were more specific in what you want to know and if it were a bit more clear of what your goals and environment is: What OS are you using What will be the purpose of the databases What environment do you have. What services should it provide What database engines do you already have experience with Why do you want to know this (what is your endgoal) What have you already researched (googled) Are you migrating data or is it a new setup. What do the servers look like To name a few questions that come to mind Hope this helps Regards, Serge Fonville On Thu, Feb 26, 2009 at 1:06 PM, Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, I have some basic query in postgresql with storage, Please help me for the following 1. What is the best practice to use postgresql with storage. 2. Which are the files and folders we can keep them in storage. Regards Karthikeyan.N
Re: [GENERAL] Can I use a query with UPDATE on its SET?
On Thu, Feb 26, 2009 at 02:15:49AM -0800, Eus wrote: Is it possible to eliminate the use of `fieldname' completely? So, I just need to type `UPDATE table SET (SELECT ...) WHERE primary_key'. I think this should be possible because if the subquery in the SET clause returns the same number of columns with the same types of those of the destination table, PostgreSQL should be able to just update the values at once. Not at the moment; there was a discussion about it a while ago[1] on the -hackers list. It all petered out after I couldn't think how to explain my position and have since forgotten about it. I'll try and think what I was trying to say and respond again. -- Sam http://samason.me.uk/ [1] http://archives.postgresql.org/pgsql-hackers/2009-01/msg02336.php -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Restore DB
On Wed, Feb 25, 2009 at 6:54 AM, Shahbaz A. Tyagi shahbaz.ty...@sphere.ae wrote: We took using PgAdminIII right click action. And it generated .backup files. However while restoring whole machine is getting hanged for unlimited time. So just checking do we have some other way also, as we have complete Postgre directory also. Are you sure it's actually failing and not just taking a long time? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Format string for ISO-8601 date and time
On Thu, Feb 26, 2009 at 10:50:18AM +0100, Daniel Verite wrote: Is there a format string for to_char(timestamptz, text) that would output a timestamp in full ISO-8601 format? That is, something like 1977-04-22T01:00:00-05:00 If I'm understanding correctly, that's a bit awkward to do. PG only ever records timestamps as an instant in time and throws away any timezone information as soon as it can (i.e. as soon as it's been parsed). The difference between a timestamp with a timezone and without is as follows: 1) values of type timestamp with time zone are always converted to UTC (either using the timezone specified or using the session's current timezone value) and then when they're sent back to the value is then corrected to the session's timezone (or an explicit AT TIME ZONE can be specified). 2) values of type timestamp without time zone (or alternatively spelled just timestamp) ignore any timezone specified and assume the value is UTC. I find it all quite confusing (and hence the above may be wrong) but have the luxury of ignoring it as all as my users are in the same timezone. That said, it's a useful set of abstractions as lots of people use them regularly. I'd recommend a read through the docs at: http://www.postgresql.org/docs/current/static/datatype-datetime.html -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql with storage
Thanks for your information Serge Fonville, My answers are below 1.Use a hight performance storage device (as applies with all databases) The stroages are in cluster 2. everything that uis located in the PostgreSQL datadirectory can be located on any device you prefer as long as the availability is guaranteed from the perspective of the PostgreSQL processes. Availability no issue, Is there any benchmark tool is there to check the performance of Postgres in Storage environment. It would be helpful if you were more specific in what you want to know and if it were a bit more clear of what your goals and environment is: What OS are you using RHEL5 What will be the purpose of the databases Databases for webapplicaiton, datawarehousing What environment do you have. I would like to keep the database in storage controller, which is accessible through NFS. What services should it provide Web services for continous read and write in the database What database engines do you already have experience with I have experience with MySQL storage engines, specifically MyISAM, Innodb. Why do you want to know this (what is your endgoal) The performance of Postgresql in Storage environment. Also we would like to create the Backup, Restore and DR solution using Storage What have you already researched (googled) Installation of Postgres, doing basic functionality Are you migrating data or is it a new setup. Now New setup, But In future we might need to migrat the data from different database like MySQL, Oracle What do the servers look like The servers are IBM X86[32bit ] To name a few questions that come to mind Hope this helps Regards, Serge Fonville On Thu, Feb 26, 2009 at 1:06 PM, Nagalingam, Karthikeyan karthikeyan.nagalin...@netapp.com wrote: Hi, I have some basic query in postgresql with storage, Please help me for the following 1. What is the best practice to use postgresql with storage. 2. Which are the files and folders we can keep them in storage. Regards Karthikeyan.N
[GENERAL] Off Topic: ICD-10 codes in a database table?
Background: ICD-10 is a clinical coding system maintained by the World Health Organization. The system is used in most advanced countries. Hospitals in the USA must convert from ICD-9 to ICD-10 by 2013. Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. Does anyone have or know of an existing table of ICD-10 codes that is already import friendly? Thanks, Andrew Gould
Re: [GENERAL] Format string for ISO-8601 date and time
Pavel Stehule wrote:
Is there a format string for to_char(timestamptz, text) that would
output a
timestamp in full ISO-8601 format? That is, something like
1977-04-22T01:00:00-05:00
I can't find a way to extract the offset against GMT from the docs
here:
http://www.postgresql.org/docs/8.3/static/functions-formatting.html
If not, what would be the way to convert a timestamp to such a
string
regardless of the session's datestyle settings?
try to look on function extract, there you can get timezone from any
timestamp with time zone.
Thanks, I've come up with this expression, then:
to_char(date, '-MM-DD')
|| 'T'
|| to_char(date, 'HH24:MI:SS')
|| to_char(extract('timezone_hour' from date),'S00')
||':'
|| to_char(extract('timezone_minute' from date),'FM00')
This form is typically used in datetime fields in xml files, and
somehow I was expecting a pre-existing format for it, such as php5's
date(c) rather than the complex expression above :)
Best regards,
--
Daniel
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Format string for ISO-8601 date and time
Sam Mason wrote: 1) values of type timestamp with time zone are always converted to UTC (either using the timezone specified or using the session's current timezone value) and then when they're sent back to the value is then corrected to the session's timezone (or an explicit AT TIME ZONE can be specified). 2) values of type timestamp without time zone (or alternatively spelled just timestamp) ignore any timezone specified and assume the value is UTC. I find it all quite confusing (and hence the above may be wrong) but have the luxury of ignoring it as all as my users are in the same timezone. That said, it's a useful set of abstractions as lots of people use them regularly. Thanks, I was aware already that timestamp with time zone doesn't store any timezone contrary to what its name seems to imply. (Though when I first started with PG some years ago, I sure fell in that trap) Best regards, -- Daniel -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foxpro, odbc, data types and unnecessary convertions
Fernando Moreno wrote: Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. i like foxpro it has its quirks as do all languages. Only concern if this is a new app Foxpro has been killed by MS. Version 9 is the last so no 64 bit support and at the mercy of MS to keep 32 bit support working down the road For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = "some sql" + alltrim( str( some_number ) ) + " more sql"; I can combine alltrim and Why the alltrim ? white spaces don't hurt. take a look at Text EndText with TextMerge it has a few gotchas here and there but on complex sql string it makes life allot easier. str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = "some_column = ?foxpro_variable ". don't use ? its from the DOS days, it does some odd conversions because it is a hold over The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do
[GENERAL] Postgresql selecting strange index for simple query
First some details about server: hh=# select version(); version -- PostgreSQL 8.3.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.2-1) 4.3.2 (1 row) hh=# SHOW effective_cache_size; effective_cache_size -- 32GB (1 row) hh=# SHOW random_page_cost; random_page_cost -- 1 (1 row) (random_page_cost set to 1 because whole DB filling in RAM). Now troublesome query: select count(*) from resume where resume.last_change_time '2008-09-01 00:00:00' Table resume have like 70 columns (structure i can post if required) and have 2 index which can be used for completing this query: resume_last_change_time_idx btree (last_change_time) WITH (fillfactor=90) resume_user_id_disabled_is_finished_last_change_time_idx btree (user_id, disabled, is_finished, last_change_time) WITH (fillfactor=90) And have rows: hh=# SELECT count(*) from resume; count - 5843784 (1 row) And postgres doing very strange thing selecting second index for this query: hh=# EXPLAIN ANALYZE select count(*) from resume where resume.last_change_time '2008-09-01 00:00:00'; QUERY PLAN --- Aggregate (cost=612329.41..612329.42 rows=1 width=0) (actual time=1806.860..1806.861 rows=1 loops=1) - Index Scan using resume_user_id_disabled_is_finished_last_change_time_idx on resume (cost=0.00..608254.00 rows=1630162 width=0) (actual time=0.020..1648.265 rows=1627437 loops=1) Index Cond: (last_change_time '2008-09-01 00:00:00'::timestamp without time zone) Total runtime: 1806.915 ms (4 rows) Ofcourse after i drop index resume_user_id_disabled_is_finished_last_change_time_idx postgres start use right index and query become faster: hh=# drop INDEX resume_user_id_disabled_is_finished_last_change_time_idx; DROP INDEX hh=# EXPLAIN ANALYZE select count(*) from resume where resume.last_change_time '2008-09-01 00:00:00'; QUERY PLAN -- Aggregate (cost=695294.37..695294.38 rows=1 width=0) (actual time=1659.655..1659.655 rows=1 loops=1) - Index Scan using resume_last_change_time_idx on resume (cost=0.00..691218.96 rows=1630162 width=0) (actual time=0.021..1500.817 rows=1627437 loops=1) Index Cond: (last_change_time '2008-09-01 00:00:00'::timestamp without time zone) Total runtime: 1659.715 ms (4 rows) (all quieris done after analyze and count(*) on resume and done 2-3 time until runtime become stable). timing real query show same results (eg query with wrong selected index really slower): hh=# \timing Timing is on. hh=# select count(*) from resume where resume.last_change_time '2008-09-01 00:00:00'; count - 1627437 (1 row) Time: 1677.731 ms hh=# drop INDEX resume_user_id_disabled_is_finished_last_change_time_idx; DROP INDEX Time: 0.452 ms hh=# select count(*) from resume where resume.last_change_time '2008-09-01 00:00:00'; count - 1627437 (1 row) Time: 1530.906 ms Somehow postgres think index scan on singlecolumn index slower comparing to scan on 4th field of 4column index. PS: very strange but clean room test with only 4 column part of table resume show postgres selecting right plan: (and full rectreated table (with all 70 columns) have same wrong plan) hh=# SELECT user_id, disabled, is_finished, last_change_time into test_table from resume; SELECT hh=# ANALYZE test_table ; ANALYZE hh=# CREATE INDEX test_table_last_change_time on test_table(last_change_time) with (fillfactor=90); CREATE INDEX hh=# CREATE INDEX test_table_complex_idx on test_table(user_id, disabled, is_finished, last_change_time) with (fillfactor=90); CREATE INDEX hh=# SELECT count(*) from test_table; count - 5843784 (1 row) hh=# EXPLAIN ANALYZE SELECT count(*) from test_table where last_change_time '2008-09-01 00:00:00'; QUERY PLAN - Aggregate (cost=72239.92..72239.93 rows=1 width=0) (actual time=1386.038..1386.038 rows=1 loops=1) - Index Scan using test_table_last_change_time on test_table (cost=0.00..68193.49 rows=1618568 width=0) (actual time=0.057..1228.817 rows=1627437 loops=1) Index Cond: (last_change_time '2008-09-01 00:00:00'::timestamp
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
Andrew Gould wrote: Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. http://www.who.int/classifications/apps/icd/icd10online/ That looks like somewhere between half and a full day's work with a bit of perl. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql selecting strange index for simple query
Tom Lane wrote:
Maxim Boguk mbo...@masterhost.ru writes:
Somehow postgres think index scan on singlecolumn index slower comparing to
scan on 4th field of 4column index.
It does know better than that. I'm wondering if the single-column index
has become very bloated or something. Have you compared the physical
index sizes?
Table fresh loaded from dump on test server... So no index bloat for sure...
As for comparing physical sizes, right single column index indeed smaller then
wrong one:
Right index:
hh=# SELECT pg_size_pretty(pg_relation_size('resume_last_change_time_idx'));
pg_size_pretty
125 MB
(1 row)
Wrong index:
hh=# SELECT
pg_size_pretty(pg_relation_size('resume_user_id_disabled_is_finished_last_change_time_idx'));
pg_size_pretty
226 MB
(1 row)
Regards, Maxim Boguk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
On Thu, Feb 26, 2009 at 10:06 AM, Richard Huxton d...@archonet.com wrote: Andrew Gould wrote: Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. http://www.who.int/classifications/apps/icd/icd10online/ That looks like somewhere between half and a full day's work with a bit of perl. -- Richard Huxton Archonet Ltd I hadn't considered web scraping; but this is the cleanest option I've seen. Thanks, Andrew
Re: [GENERAL] Postgresql selecting strange index for simple query
Maxim Boguk mbo...@masterhost.ru writes: Tom Lane wrote: It does know better than that. I'm wondering if the single-column index has become very bloated or something. Have you compared the physical index sizes? Table fresh loaded from dump on test server... So no index bloat for sure... As for comparing physical sizes, right single column index indeed smaller then wrong one: Huh. I get sane-looking choices when I try a similar case here. Can you put together a self-contained test case? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql selecting strange index for simple query
Maxim Boguk mbo...@masterhost.ru writes: Somehow postgres think index scan on singlecolumn index slower comparing to scan on 4th field of 4column index. It does know better than that. I'm wondering if the single-column index has become very bloated or something. Have you compared the physical index sizes? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting time-dependent load statistics
Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem setting up PostgreSQL
Hello, I am attempting to install PostgreSQL. I downloaded 8.2.12-1zip from ftp9.us.postgresql.org and then followed the installation instructions outlined in http://pginstaller.projects.postgresql.org. I am installing on a Windows XP machine with an NTFS file system. I opened the zip contents from a temporary folder with all internet security functions disabled. When I reached step 11 in the instructions, I received an error message: Internal account look-up failure. No mapping between account names and security ID was done. I retried several times using a new download from a different mirror, but always received the same error message. I searched for and found a prior reference to this message (Sept 04 2008) but ws unable to retrieve the full message and response. Can you offer any suggestions for dealing with this problem? Thanks, Bill Herbert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
Hello,
I can't find SQL definition for OVERLAPS operator so I don't know
whether following expression's result (false) is appropriate
behaviour:
select ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps
('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
Can anyone confirm that ? In my understanding of overlaps it should
result true, as those two periods overlaps in 2007-12-01.
psql (PostgreSQL) 8.3.5
Best regards,
ML
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Table partitioning and query plans
Hi all! I have set up a couple of log tables in our database to use table partitioning. So far it looks to be working pretty well, but I ran into a query plan issue that doesn't make sense to me at all. We have a table omslog, and a set of partition tables omslog_part_, where the #... stuff is a serial number. There is a rule that redirects inserts into omslog, to the most recent partition table. Pretty simple, and it works as advertised. If I select a min() or max() aggregate against an indexed field in omslog, where as before it would use the index, now it does not. However, the index will be used if I execute the same aggregate against one of the partition tables. The difference is demonstrated in the explains below. Doing a sequential scan on the base table makes absolutely no sense whatsoever by any stretch of the imagination; as you can see, there are about 9 million rows there, and the planner knows it (frequent analyze). What would cause the planner to be so silly in this instance? This is on PG 8.1.4. Would this happen to be something that an upgrade might fix? Thanks for any insight, -Glen database=# explain select min(sub_system) from omslog_part_0002; Result (cost=0.19..0.20 rows=1 width=0) InitPlan - Limit (cost=0.00..0.19 rows=1 width=14) - Index Scan using idx_omslog_part_0002_subsys on omslog_part_0002 (cost=0.00..24212.57 rows=129781 width=14) Filter: ((sub_system)::text IS NOT NULL) database=# explain select min(sub_system) from omslog; Aggregate (cost=539575.39..539575.40 rows=1 width=14) - Append (cost=0.00..516884.11 rows=9076511 width=14) - Seq Scan on omslog (cost=0.00..509342.09 rows=8946709 width=14) - Seq Scan on omslog_part_0002 omslog (cost=0.00..7542.02 rows=129802 width=14) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Extended ability to alter column type when empty
Hi Osvaldo, Neat! Thanks a lot for your help! Regards, David On Thu, Feb 19, 2009 at 2:56 PM, Osvaldo Kussama osvaldo.kuss...@gmail.comwrote: 2009/2/17 David Andersen mrdavidander...@gmail.com: Hi, I am a real newbee and I hope this is the right place to post a feature request. I am receiving data from a csv file where one column has a strange data format. It would be nice if I could use Copy From with to_timestamp to transform the date. As far as I know this is not possible to do in one step (unlike MySQL I believe). I, therefore, have to first read large amounts of CSV data into one table where the data is a char(15) column. Then create another table using: CREATE TABLE T (like tempT); ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP; However, then I run into: ERROR: column thedate cannot be cast to type pg_catalog.timestamp This error comes even though the table is empty. Could it be an idea to allow this for empty tables? Am I missing something obvious in my unreasonably complicated approach? Try: ALTER TABLE T ALTER COLUMN thedate TYPE TIMESTAMP USING CAST (thedate AS timestamp); Osvaldo
[GENERAL] Connection refused (0x0000274D/10061).
Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I launched the program it automaticly gives me an error that the database is not available. The postgreSQL database installation is bundled together with installation disk. I read one of the mail discussion that that version of sql is no longer supported so I upgrade to version 8.3. The moteview program has a script called resetdb.cmd (script is given below) whereby as I understand it is to set the database for the program as server : localhost, database : task, user : tele, and password : tiny. When I run the resetdb.cmd program. The error below shows up: psql: could not connect to server: connection refused (0x274D/10061). Is the server running on host localhost and accepting TCP/IP connection on port 5432. I not a database person. I googled the error msg and from my reading it has something to do with authentication Can I have the solution to this matter. Thanks. set PG_HOST=localhost set PG_PORT=5432 set PG_USER=tele set PG_PASS=tiny set PG_DBASE=task set PG_CONN=-h %PG_HOST% -p %PG_PORT% -U %PG_USER% set PG_DDIR=C:\Program Files\PostgreSQL\8.3\data set PG_BIN=C:\Program Files\PostgreSQL\8.3\bin set PATH=%PG_BIN%; %PATH% if '%1' == ' ' goto START set PG_DDIR=%1\PostgreSQL\8.3\data set PG_BIN=%1\PostgreSQL\8.3\bin set PATH=%PG_BIN%;%PATH% : START echo %PG_BIN% echo %PG_DDIR% echo %PATH% echo Granting permissions to access database copy pg_hba.conf %PG_DDIR% net start pgsql-8.3 sleep 5 pg_ctl reload -D %PG_DDIR% echo Setting up PostgreSQL 8.3 database for Moteview psql -e %PG_CONN% template1 db_user.sql psql -e %PG_CONN% %PG_DBASE% db_moteview.sql psql -e %PG_CONN% %PG_DBASE% db_xsensor.sql psql -e %PG_CONN% %PG_DBASE% db_sample_mts310.sql echo DATABASE CREATION FINISHED Appreciate your help... Regards, Najios
[GENERAL] Product Roadmap question and request for recommendation
What would you do in this situation? We are currently at PG 8.1 and are in the process of upgrading to 8.3.6. I read on your development roadmap page that 8.4 is slated for release in Q1 of this year, possibly on the 31st of March: “The next release of PostgreSQL is planned to be the 8.4 release. A tentative schedule for this version has a release in the first quarter of 2009.” I have also read in the postings that the framework for in-place upgrades is being added to 8.4, so the actual upgrade to the forthcoming 8.5 can be done as in-place (without dump/restore), but there won’t be a way to do an in-place upgrade from any 8.3.x version directly to 8.5. Upgrading some of our larger databases is rather painful and is a several day effort (staging historical data over time so the actual cutover can realistically be done in a weekend). Right now 8.1 is working well for us, is extremely stable, and provides all of the functionality we need to support our applications. Given this, it sounds to me like it makes sense to wait a bit longer (2nd half of this year) for a 8.4.x version do to the dump/restore against for the last time so we can then, in the future, do in-place upgrades from 8.5 onward. Any comments you can make on this suggestion would be very much appreciated. Thank you, Keaton Adams
[GENERAL] Array to IN or UNION
I'm doing a procedure and I'm trying to pass a dynamic array into a CURSOR, I found the sentence ANY but it's really slow. Is there any way to convert all the elements in this array to IN condition or one select with unions? For example, BEGIN example CURSOR (codes integer[] ) SELECT * FROM table_1 WHERE code IN (3,5,7) or example CURSOR (codes integer[] ) SELECT * FROM table_1 WHERE codi = 3 UNION SELECT * FROM table_1 WHERE codi = 5 SELECT * FROM table_1 WHERE codi = 7 DECLARE OPEN example(ARRAY[3,5,7]); END;
Re: [GENERAL] postgresql with storage
Nagalingam, Karthikeyan wrote: Thanks for your information Serge Fonville, My answers are below 1.Use a hight performance storage device (as applies with all databases) The stroages are in cluster that could mean almost anything. 2. everything that uis located in the PostgreSQL datadirectory can be located on any device you prefer as long as the availability is guaranteed from the perspective of the PostgreSQL processes. Availability no issue, Is there any benchmark tool is there to check the performance of Postgres in Storage environment. the only meaningful benchmark is your application, all other benchmarks only measure the performance of the benchmark. What environment do you have. I would like to keep the database in storage controller, which is accessible through NFS. NFS is generally not recommended for relational databases, file locking and random access on NFS can be somewhat sketchy. Most high performance database servers use either SAN (FC, iSCSI) or direct attached storage. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] foxpro, odbc, data types and unnecessary convertions
Completely forgot take a look at Cursor Adapter Class, also any cursor in foxpro can be made be updateable with CURSORSETPROP( ) function removing the problem of writing Update's and Inserts Fernando Moreno wrote: Hi all, I'm using visual foxpro 9 -not my decision- for a client application. Statements are writen as the typical sql string and sent through ODBC. For numbers, I have to convert them first to string and then remove the spaces, the code looks like this: sql_string = some sql + alltrim( str( some_number ) ) + more sql; I can combine alltrim and str in a third function but it's still tricky. A shorter and presumably better way to do the same is: sql_string = some_column = ?foxpro_variable . The problem with the last option is that, watching the pgsql log, values are sent this way: '12345'::float(8), so for every numeric value, no matter its type, I'm sending 12 characters more and the server is doing convertions that I don't need. Having a lot of foreign keys and other numeric data, I think this behaviour is not so good for network (remote and poor connection) and server performance. I'm almost decided to keep doing the trim/str thing, but my question is: am I exaggerating? what would you do? Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
On Feb 26, 2009, at 7:12 AM, Andrew Gould wrote: Background: ICD-10 is a clinical coding system maintained by the World Health Organization. The system is used in most advanced countries. Hospitals in the USA must convert from ICD-9 to ICD-10 by 2013. Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. Does anyone have or know of an existing table of ICD-10 codes that is already import friendly? 10 seconds with google finds something in CSV format in a zip file downloadable from the bottom of http://www.hcup-us.ahrq.gov/toolssoftware/icd_10/ccs_icd_10.jsp - is that any use to you? Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
On Thu, Feb 26, 2009 at 11:27 AM, Steve Atkins st...@blighty.com wrote: On Feb 26, 2009, at 7:12 AM, Andrew Gould wrote: Background: ICD-10 is a clinical coding system maintained by the World Health Organization. The system is used in most advanced countries. Hospitals in the USA must convert from ICD-9 to ICD-10 by 2013. Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. Does anyone have or know of an existing table of ICD-10 codes that is already import friendly? 10 seconds with google finds something in CSV format in a zip file downloadable from the bottom of http://www.hcup-us.ahrq.gov/toolssoftware/icd_10/ccs_icd_10.jsp - is that any use to you? Cheers, Steve Hmm.AHRQ never came up in my googling. The table is almost what I need; but also has additional, useful information. In essence, AHRQ had a table that maps ICD-9 codes to their CCS categories. They have added a table that maps ICD-10 codes to those same categories. The mapping is *very* useful. The only thing missing are the ICD-10 code descriptions. Thanks for the help! Andrew
Re: [GENERAL] Postgresql selecting strange index for simple query
Maxim Boguk mbo...@masterhost.ru writes:
Tom Lane wrote:
It does know better than that. I'm wondering if the single-column index
has become very bloated or something. Have you compared the physical
index sizes?
Table fresh loaded from dump on test server... So no index bloat for sure...
As for comparing physical sizes, right single column index indeed smaller then
wrong one:
Huh. I get sane-looking choices when I try a similar case here. Can
you put together a self-contained test case?
Not full self-contained test case but some minimal setup data which can give
you ideas whats going wrong:
Test confirm my theory wrong index selection linked with long rows in table.
My tests contain such queries:
Ok TEST1:
set random_page_cost=1;
drop TABLE if exists test_table ;
SELECT user_id,last_change_time,rpad('a',1,'b') as f1 into test_table from
resume;
ANALYZE test_table;
SELECT count(*) from test_table;
CREATE INDEX right_idx on test_table(last_change_time);
CREATE INDEX wrong_idx on test_table(user_id, last_change_time);
EXPLAIN ANALYZE SELECT 1 from test_table where last_change_time '2009-01-10
00:00:00';
result:
Index Scan using right_idx on test_table (cost=0.00..42763.35 rows=388718
width=0) (actual time=0.020..342.653 rows=390370 loops=1)
Index Cond: (last_change_time '2009-01-10 00:00:00'::timestamp without
time zone)
Total runtime: 368.699 ms
Ok TEST2 (but see: cost increased 4x times when real work time increased only
by 30%):
same but rpad('a',200,'b') instead of rpad('a',1,'b') when populating
test_table:
result:
Index Scan using right_idx on test_table (cost=0.00..179346.09 rows=392268
width=0) (actual time=0.089..422.439 rows=390370 loops=1)
Index Cond: (last_change_time '2009-01-10 00:00:00'::timestamp without
time zone)
Total runtime: 448.717 ms
!!Not ok TEST3:!!
same but rpad('a',500,'b') instead of rpad('a',1,'b') when populating
test_table:
Oops wrong index used:
Index Scan using wrong_idx on test_table (cost=0.00..254918.19 rows=392231
width=0) (actual time=0.067..730.097 rows=390370 loops=1)
Index Cond: (last_change_time '2009-01-10 00:00:00'::timestamp without
time zone)
Total runtime: 757.930 ms
(3 rows)
(btw if drop wrong_idx query become works almost 2х faster:
drop INDEX wrong_idx;
Index Scan using right_idx on test_table (cost=0.00..259709.09 rows=392231
width=0) (actual time=0.019..416.108 rows=390370 loops=1)
Index Cond: (last_change_time '2009-01-10 00:00:00'::timestamp without
time zone)
Total runtime: 442.790 ms
)
Again Ok TEST4 (here toast engine removed all long values from test_table):
same but rpad('a',2000,'b') instead of rpad('a',1,'b') when populating
test_table:
Index Scan using right_idx on test_table (cost=0.00..64606.50 rows=393002
width=0) (actual time=0.058..371.723 rows=390370 loops=1)
Index Cond: (last_change_time '2009-01-10 00:00:00'::timestamp without
time zone)
Total runtime: 397.929 ms
So i have two theory (just waving hands ofcourse):
1)integer owerflow somewhere in cost calculation
2)floating rounding errors (because cost very close in wrong situations:
254918.19 vs 259709.09)
PS: second issue is cost increasing with increasing lenght of rows in table
complete indepenent with increase real work time.
(probably i need tune other _cost parameters for get more exact cost values)
PPS: sorry for my poor english
Regards, Maxim Boguk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Returning null for joined tables when one column non existant
Hi all, I've got a query that crosses a few tables. For example: SELECT a.foo, b.bar, c.baz FROM aaa a, bbb b, ccc c WHERE a.a_id=b.b_a_id AND a.a_id=c.c_a_id AND a.a_id=1; Obviously, if there is no match in 'bbb' or 'ccc' then nothing will be returned, even if there is a match in one or both of the other tables. Is there a way to say something like 'b.bar OR NULL' to make sure that the matching columns with data still show and ones without a match return NULL (or some string)? Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
On Thu, Feb 26, 2009 at 11:36 AM, Andrew Gould andrewlylego...@gmail.comwrote: On Thu, Feb 26, 2009 at 11:27 AM, Steve Atkins st...@blighty.com wrote: On Feb 26, 2009, at 7:12 AM, Andrew Gould wrote: Background: ICD-10 is a clinical coding system maintained by the World Health Organization. The system is used in most advanced countries. Hospitals in the USA must convert from ICD-9 to ICD-10 by 2013. Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. Does anyone have or know of an existing table of ICD-10 codes that is already import friendly? 10 seconds with google finds something in CSV format in a zip file downloadable from the bottom of http://www.hcup-us.ahrq.gov/toolssoftware/icd_10/ccs_icd_10.jsp - is that any use to you? Cheers, Steve Hmm.AHRQ never came up in my googling. The table is almost what I need; but also has additional, useful information. In essence, AHRQ had a table that maps ICD-9 codes to their CCS categories. They have added a table that maps ICD-10 codes to those same categories. The mapping is *very* useful. The only thing missing are the ICD-10 code descriptions. Thanks for the help! Andrew Correction: The table has ICD-10 diagnosis code descriptions! Now I just need them to create the same table for ICD-10 procedure codes. (I'm sure that will follow soon as they have the analagous table for ICD-9's.) Andrew
Re: [GENERAL] Returning null for joined tables when one column non existant
Madison Kelly li...@alteeve.com wrote: Hi all, I've got a query that crosses a few tables. For example: SELECT a.foo, b.bar, c.baz FROM aaa a, bbb b, ccc c WHERE a.a_id=b.b_a_id AND a.a_id=c.c_a_id AND a.a_id=1; Obviously, if there is no match in 'bbb' or 'ccc' then nothing will be returned, even if there is a match in one or both of the other tables. Is there a way to say something like 'b.bar OR NULL' to make sure that the matching columns with data still show and ones without a match return NULL (or some string)? I think, you are looking for left join: test=# create table a(id int, foo text); CREATE TABLE Zeit: 101,738 ms test=*# create table b(id int, bar text); CREATE TABLE Zeit: 74,751 ms test=*# create table c(id int, batz text); CREATE TABLE Zeit: 7,827 ms test=*# commit; COMMIT Zeit: 4,193 ms test=# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id) left join c on (a.id=c.id); foo | bar | batz -+-+-- (0 Zeilen) Zeit: 1,074 ms test=*# insert into a values (1,'foo'); INSERT 0 1 Zeit: 0,469 ms test=*# insert into b values (1,'bar'); INSERT 0 1 Zeit: 0,490 ms test=*# insert into c values (1,'batz'); INSERT 0 1 Zeit: 0,733 ms test=*# insert into a values (2,'foo'); INSERT 0 1 Zeit: 0,328 ms test=*# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id) left join c on (a.id=c.id); foo | bar | batz -+-+-- foo | bar | batz foo | | (2 Zeilen) Zeit: 0,595 ms test=*# insert into c values (2,'bla'); INSERT 0 1 Zeit: 0,410 ms test=*# select a.foo, b.bar, c.batz from a left join b on (a.id=b.id) left join c on (a.id=c.id); foo | bar | batz -+-+-- foo | bar | batz foo | | bla (2 Zeilen) Zeit: 0,615 ms Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql selecting strange index for simple query
Maxim Boguk mbo...@masterhost.ru writes: So i have two theory (just waving hands ofcourse): 1)integer owerflow somewhere in cost calculation Costs are floats, and in any case you're not showing costs anywhere near the integer overflow limit... 2)floating rounding errors (because cost very close in wrong situations: 254918.19 vs 259709.09) The planner is intentionally set up to consider costs within a percent or so of each other as being effectively equal. If the estimated costs are that close then it doesn't surprise me if it sometimes picks the wrong plan. The real question is why are the estimates so close? They should not be, since AFAICS you are talking about a situation where we'd have to scan all of the multicol index versus only about a fifth of the single-col one. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Off Topic: ICD-10 codes in a database table?
ICD10s discovered! http://www.hcup-us.ahrq.gov/toolssoftware/icd_10/dxlabel%202006.csv Thanks Steve! Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. Date: Thu, 26 Feb 2009 13:04:26 -0600 Subject: Re: [GENERAL] Off Topic: ICD-10 codes in a database table? From: andrewlylego...@gmail.com To: st...@blighty.com CC: pgsql-general@postgresql.org On Thu, Feb 26, 2009 at 11:36 AM, Andrew Gould andrewlylego...@gmail.com wrote: On Thu, Feb 26, 2009 at 11:27 AM, Steve Atkins st...@blighty.com wrote: On Feb 26, 2009, at 7:12 AM, Andrew Gould wrote: Background: ICD-10 is a clinical coding system maintained by the World Health Organization. The system is used in most advanced countries. Hospitals in the USA must convert from ICD-9 to ICD-10 by 2013. Problem: I've been trying to find a table of ICD-10 codes to import into a database; but the only listings that I can find are in publicly available pdf files. I've tried to copy the text to text files, but the resulting layout is horrible -- it would take, literally, weeks or months of manual tweaking to parse. Does anyone have or know of an existing table of ICD-10 codes that is already import friendly? 10 seconds with google finds something in CSV format in a zip file downloadable from the bottom of http://www.hcup-us.ahrq.gov/toolssoftware/icd_10/ccs_icd_10.jsp - is that any use to you? Cheers, Steve Hmm.AHRQ never came up in my googling. The table is almost what I need; but also has additional, useful information. In essence, AHRQ had a table that maps ICD-9 codes to their CCS categories. They have added a table that maps ICD-10 codes to those same categories. The mapping is *very* useful. The only thing missing are the ICD-10 code descriptions. Thanks for the help! Andrew Correction: The table has ICD-10 diagnosis code descriptions! Now I just need them to create the same table for ICD-10 procedure codes. (I'm sure that will follow soon as they have the analagous table for ICD-9's.) Andrew _ Access your email online and on the go with Windows Live Hotmail. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_AE_Access_022009
Re: [GENERAL] Getting time-dependent load statistics
On Fri, 20 Feb 2009, Torsten Bronger wrote: Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Have a look at http://pgfouine.projects.postgresql.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql with storage
The only meaningful benchmark is your application, all other benchmarks only measure the performance of the benchmark. As a benchmark you can also look into http://developer.postgresql.org/pgdocs/postgres/pgbench.html Hope this helps Regards, Serge Fonville
Re: [GENERAL] Problem setting up PostgreSQL
--- On Wed, 2/25/09, Bill Herbert wgh8...@earthlink.net wrote: From: Bill Herbert wgh8...@earthlink.net Subject: [GENERAL] Problem setting up PostgreSQL To: pgsql-general@postgresql.org pgsql-general@postgresql.org Date: Wednesday, February 25, 2009, 4:30 AM Hello, I am attempting to install PostgreSQL. I downloaded 8.2.12-1zip from ftp9.us.postgresql.org and then followed the installation instructions outlined in http://pginstaller.projects.postgresql.org. I am installing on a Windows XP machine with an NTFS file system. I opened the zip contents from a temporary folder with all internet security functions disabled. When I reached step 11 in the instructions, I received an error message: Internal account look-up failure. No mapping between account names and security ID was done. I retried several times using a new download from a different mirror, but always received the same error message. I searched for and found a prior reference to this message (Sept 04 2008) but ws unable to retrieve the full message and response. Can you offer any suggestions for dealing with this problem? Thanks, Bill Herbert -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general check if you have postgres user create in the account domain -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
- najmuddin hassan naji...@gmail.com wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I launched the program it automaticly gives me an error that the database is not available. The postgreSQL database installation is bundled together with installation disk. I read one of the mail discussion that that version of sql is no longer supported so I upgrade to version 8.3. The moteview program has a script called resetdb.cmd (script is given below) whereby as I understand it is to set the database for the program as server : localhost, database : task, user : tele, and password : tiny. When I run the resetdb.cmd program. The error below shows up: psql: could not connect to server: connection refused (0x274D/10061). Is the server running on host localhost and accepting TCP/IP connection on port 5432. I not a database person. I googled the error msg and from my reading it has something to do with authentication Can I have the solution to this matter. Thanks. set PG_HOST=localhost set PG_PORT=5432 set PG_USER=tele set PG_PASS=tiny set PG_DBASE=task set PG_CONN=-h %PG_HOST% -p %PG_PORT% -U %PG_USER% set PG_DDIR=C:\Program Files\PostgreSQL\8.3\data set PG_BIN=C:\Program Files\PostgreSQL\8.3\bin set PATH=%PG_BIN%; %PATH% if '%1' == ' ' goto START set PG_DDIR=%1\PostgreSQL\8.3\data set PG_BIN=%1\PostgreSQL\8.3\bin set PATH=%PG_BIN%;%PATH% : START echo %PG_BIN% echo %PG_DDIR% echo %PATH% echo Granting permissions to access database copy pg_hba.conf %PG_DDIR% net start pgsql-8.3 sleep 5 pg_ctl reload -D %PG_DDIR% echo Setting up PostgreSQL 8.3 database for Moteview psql -e %PG_CONN% template1 db_user.sql psql -e %PG_CONN% %PG_DBASE% db_moteview.sql psql -e %PG_CONN% %PG_DBASE% db_xsensor.sql psql -e %PG_CONN% %PG_DBASE% db_sample_mts310.sql echo DATABASE CREATION FINISHED Appreciate your help... Regards, Najios Yes 8.0 is an old version, but in this instance I would use what is bundled with the program. There have been a lot of changes between 8.0 and 8.3 and chances are the application will have problems with them. So first get rid of 8.3 and reinstall the bundled app/db. Try to connect and if not successful post the error messages here. Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql selecting strange index for simple query
Tom Lane wrote:
Maxim Boguk mbo...@masterhost.ru writes:
So i have two theory (just waving hands ofcourse):
1)integer owerflow somewhere in cost calculation
Costs are floats, and in any case you're not showing costs anywhere near
the integer overflow limit...
2)floating rounding errors (because cost very close in wrong situations:
254918.19 vs 259709.09)
The planner is intentionally set up to consider costs within a percent
or so of each other as being effectively equal. If the estimated costs
are that close then it doesn't surprise me if it sometimes picks the
wrong plan. The real question is why are the estimates so close?
They should not be, since AFAICS you are talking about a situation
where we'd have to scan all of the multicol index versus only about
a fifth of the single-col one.
Ok i exploring more:
just one thing:
hh=# SHOW default_statistics_target ;
default_statistics_target
---
10
(1 row)
(btw increase statistic to 1000 do not fix situation).
I try simplify test case and:
Now use sequential user_id, and truncate last_change_time to date:
SELECT nextval('test_seq') as user_id,last_change_time::date ,rpad('a',500,'b')
as f1 into test_table from resume;
ANALYZE test_table;
SELECT count(*) from test_table;
CREATE INDEX right_idx on test_table(last_change_time);
CREATE INDEX wrong_idx on test_table(user_id, last_change_time);
EXPLAIN ANALYZE SELECT 1 from test_table where last_change_time '2009-01-10
00:00:00';
Index Scan using wrong_idx on test_table (cost=0.00..182623.51 rows=316522
width=0) (actual time=0.056..534.620 rows=382671 loops=1)
Index Cond: (last_change_time '2009-01-10'::date)
DROP INDEX wrong_idx;
EXPLAIN ANALYZE SELECT 1 from test_table where last_change_time '2009-01-10
00:00:00';
Index Scan using right_idx on test_table (cost=0.00..221765.19 rows=316522
width=0) (actual time=0.023..346.213 rows=382671 loops=1)
Index Cond: (last_change_time '2009-01-10'::date)
Full index scan over wrong index cost reasonable lower then 1/17 of single
column index (182623 vs 221765)!
So just last_change_time still cannot be generated... but:
hh=# SELECT count(distinct last_change_time) from test_table;
count
---
2133
(1 row)
And statistic values for last_change_time is:
hh=# SELECT * from pg_stats where tablename='test_table' and
attname='last_change_time';
-[ RECORD 1
]-+---
schemaname| public
tablename | test_table
attname | last_change_time
null_frac | 0
avg_width | 4
n_distinct| 1211
most_common_vals |
{2004-02-27,2009-01-26,2008-03-11,2009-01-27,2004-01-15,2008-10-13,2009-01-19,2009-01-22,2009-01-21,2008-09-29}
most_common_freqs |
{0.0083,0.0077,0.0073,0.007,0.0067,0.0053,0.0053,0.0053,0.005,0.0047}
histogram_bounds |
{2003-03-31,2005-08-26,2006-10-04,2007-04-29,2007-09-27,2008-01-24,2008-05-01,2008-07-29,2008-10-01,2008-11-27,2009-01-25}
correlation | 0.261512
I think it is all what planner can use when choose plan... because user_id is
unique sequential values.
regargs, Maxim Boguk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
On Wed, Feb 25, 2009 at 9:40 PM, Dave Page dp...@pgadmin.org wrote: On Wed, Feb 25, 2009 at 8:16 AM, Scara Maccai m_li...@yahoo.it wrote: What? Hot standby won't make it in 8.4? Hot standby != synch-rep. The former is still being reviewed, though it's starting to look like it's cutting it pretty fine for inclusion in 8.4. 8.4 was scheduled to be released march 1. Do we know what the tentative date of release is?
Re: [GENERAL] Postgresql selecting strange index for simple query
Maxim Boguk mbo...@masterhost.ru writes:
I try simplify test case and:
Now use sequential user_id, and truncate last_change_time to date:
SELECT nextval('test_seq') as user_id,last_change_time::date
,rpad('a',500,'b') as f1 into test_table from resume;
Could you send me a dump of this test_table off-list? It seems like
there must be something strange about the stats of last_change_time,
but I don't feel like guessing about what it is ...
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: 8.4 was scheduled to be released march 1. Do we know what the All schedules are subject to change within the community :) tentative date of release is? When it is done of course. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thu, Feb 26, 2009 at 2:03 PM, Adrian Klaver akla...@comcast.net wrote: - najmuddin hassan naji...@gmail.com wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I Yes 8.0 is an old version, but in this instance I would use what is bundled with the program. There have been a lot of changes between 8.0 and 8.3 and chances are the application will have problems with them. So first get rid of 8.3 and reinstall the bundled app/db. Try to connect and if not successful post the error messages here. No way. I might run 8.0.x where x is the latest version of 8.0, but I would not run any system I depended on on 8.0.0.rc. It looks like the OPs problems have to do with client authentication and what not. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
Hallöchen! Torsten Bronger writes: Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. Sorry, this got posted twice because the mod has a long time lag and I thought that it was lost. Tschö, Torsten. -- Torsten Bronger, aquisgrana, europa vetus Jabber ID: torsten.bron...@jabber.rwth-aachen.de -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Database/Table Owner Question
michael.l.bl...@frb.gov wrote: We have a lot of test databases with multiple db_owners, but very few superusers, and table_owners switch all the time. A quick, untested idea: Create a table_owner role. Create your users with NOINHERIT and GRANT table_owner to them as appropriate. REVOKE CREATE ON SCHEMA public FROM public. GRANT CREATE ON SCHEMA PUBLIC TO table_owner. A user would need to SET ROLE table_owner to create a table. RESET ROLE would put them back to normal. Just a thought -Kevin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thursday 26 February 2009 1:51:10 pm Scott Marlowe wrote: On Thu, Feb 26, 2009 at 2:03 PM, Adrian Klaver akla...@comcast.net wrote: - najmuddin hassan naji...@gmail.com wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I Yes 8.0 is an old version, but in this instance I would use what is bundled with the program. There have been a lot of changes between 8.0 and 8.3 and chances are the application will have problems with them. So first get rid of 8.3 and reinstall the bundled app/db. Try to connect and if not successful post the error messages here. No way. I might run 8.0.x where x is the latest version of 8.0, but I would not run any system I depended on on 8.0.0.rc. It looks like the OPs problems have to do with client authentication and what not. I would agree with your recommendation to run the latest 8.0.x in all cases except this one. The issue is that Moteview is an app bundled with Postgres and seems to have some setup scripts included to get things started. I am trying to get the OP back to the baseline setup and establish that the app works out of the box as designed by the app developers. Once that happens then it is possible to move forward. My guess that what is happening is cross contamination between the set up from the original install and the manual upgrade to 8.3. Starting from the beginning narrows the variables. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Mon, Feb 23, 2009 at 6:11 AM, najmuddin hassan naji...@gmail.com wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I launched the program it automaticly gives me an error that the database is not available. The postgreSQL database installation is bundled together with installation disk. I read one of the mail discussion that that version of sql is no longer supported so I upgrade to version 8.3. 8.0 is definitely supported. 8.0.0-rc1 is NOT supported, as it was a release candidate and is quite likely to have some nasty bugs in it. It's why there were point releases made right up until 8.0.20 recently. And will likely be more to come. So, it wouldn't be so bad if they were including 8.0.20. That's a supported release and you find fewer data eating bugs as a release gets older, so even if it was 8.0.11 or something, there'd be a lot fewer hushed whispers of oh my god, that poor man about it being 8.0.0-rc1. The moteview program has a script called resetdb.cmd (script is given below) whereby as I understand it is to set the database for the program as server : localhost, database : task, user : tele, and password : tiny. When I run the resetdb.cmd program. The error below shows up: psql: could not connect to server: connection refused (0x274D/10061). Is the server running on host localhost and accepting TCP/IP connection on port 5432. By default, installations of pgsql do NOT answer tcp/ip ports. You need to read the admin docs on what exactly to do. Quick version, in postgresql.conf: set listen_addresses=* and if 8.0.0 has it, tcp_listen or whatever that setting is near the top in pg_hba.conf set it to allow trusted logins on 127.0.0.1 (i.e. localhost) then restart pgsql. Note that 8.3 may break things. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thu, Feb 26, 2009 at 3:35 PM, Adrian Klaver akla...@comcast.net wrote: On Thursday 26 February 2009 1:51:10 pm Scott Marlowe wrote: On Thu, Feb 26, 2009 at 2:03 PM, Adrian Klaver akla...@comcast.net wrote: - najmuddin hassan naji...@gmail.com wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I Yes 8.0 is an old version, but in this instance I would use what is bundled with the program. There have been a lot of changes between 8.0 and 8.3 and chances are the application will have problems with them. So first get rid of 8.3 and reinstall the bundled app/db. Try to connect and if not successful post the error messages here. No way. I might run 8.0.x where x is the latest version of 8.0, but I would not run any system I depended on on 8.0.0.rc. It looks like the OPs problems have to do with client authentication and what not. I would agree with your recommendation to run the latest 8.0.x in all cases except this one. The issue is that Moteview is an app bundled with Postgres and seems to have some setup scripts included to get things started. I am trying to get the OP back to the baseline setup and establish that the app works out of the box as designed by the app developers. Once that happens then it is possible to move forward. My guess that what is happening is cross contamination between the set up from the original install and the manual upgrade to 8.3. Starting from the beginning narrows the variables. I seriously doubt it's anything more than postgresql.conf and pg_hba.conf settings that's keep him out. While it's possible they've built a custom database with all kinds of fancy UDFs and all, I kinda doubt anyone with the brains to do that would be bundling 8.0.0-rc1. But anything's possible. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
Scott Marlowe scott.marl...@gmail.com writes: 8.0 is definitely supported. 8.0.0-rc1 is NOT supported, as it was a release candidate and is quite likely to have some nasty bugs in it. It's worse than that: he's running on Windows, which means that this is not just any rc version, but an rc for the first native Windows port. We no longer support 8.0.anything on Windows because of the unfixable bugs in that release. If moteview is still shipping 8.0.rc1 to Windows customers in 2009, I'd say that borders on criminal negligence. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
Joshua D. Drake wrote: On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: [according to some page on the web site...] 8.4 was scheduled to be released march 1. Do we know what the All schedules are subject to change within the community :) tentative date of release is? When it is done of course. Perhaps that should be the official position communicated in the various places on the web site. I do notice that the Press FAQ with it's Q4 2008 guess is even more optimistic than the other page on the website people seem to be finding that implies March. http://www.postgresql.org/about/press/faq Q: When will 8.4 come out? A: Historically, PostgreSQL has released approximately every 12 months and there is no desire in the community to change from that pattern. So expect 8.4 sometime in the fourth quarter of 2008. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about no unchanging update rule + ALTER
I found the following on a blog post (http://www.depesz.com/index.php/2007/09/08/avoiding-empty-updates/) which had a rule to prevent empty updates: CREATE RULE no_unchanging_updates AS ON UPDATE TO test_table WHERE ROW(OLD.*) IS NOT DISTINCT FROM ROW(NEW.*) DO INSTEAD NOTHING; Works great, but problem comes when I alter the table and add a new column, it appears the rule doesn't allow an update after adding a new column via ALTER TABLE ADD COLUMN. I created the rule above, then did: ALTER TABLE test_table ADD COLUMN foo TEXT; = UPDATE test_table SET foo = 'bar'; UPDATE 0 When doing a \d on the table I notice the rule is expanded at the time of creation to include each column in an expression, but it is not updated from the ALTER TABLE command. Do I have to drop and recreate this rule after every ALTER TABLE ADD/DELETE column? Or would the following trigger (also found on blog post) be a better solution as my app is for a plugin builder where adding/deleting/changing fields is common: CREATE OR REPLACE FUNCTION prevent_empty_updates() RETURNS trigger as $BODY$ DECLARE BEGIN IF ROW(OLD.*) IS DISTINCT FROM ROW(NEW.*) THEN RETURN NEW; END IF; RETURN NULL; END; $BODY$ language plpgsql; CREATE TRIGGER prevent_empty_updates BEFORE UPDATE ON test FOR EACH ROW EXECUTE PROCEDURE prevent_empty_updates(); Actually after writing this, this TOO does not seem to work after an ADD COLUMN. :/ Any suggestions? Postgres version is 8.3. Thanks, Josh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thu, 2009-02-26 at 18:16 -0500, Tom Lane wrote: Scott Marlowe scott.marl...@gmail.com writes: 8.0 is definitely supported. 8.0.0-rc1 is NOT supported, as it was a release candidate and is quite likely to have some nasty bugs in it. It's worse than that: he's running on Windows, which means that this is not just any rc version, but an rc for the first native Windows port. We no longer support 8.0.anything on Windows because of the unfixable bugs in that release. If moteview is still shipping 8.0.rc1 to Windows customers in 2009, I'd say that borders on criminal negligence. I am sure that MoteView's license says otherwise. Sincerely, Joshua D. Drake regards, tom lane -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
On Thu, 2009-02-26 at 15:27 -0800, Ron Mayer wrote: Joshua D. Drake wrote: On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: [according to some page on the web site...] 8.4 was scheduled to be released march 1. Do we know what the All schedules are subject to change within the community :) tentative date of release is? When it is done of course. Perhaps that should be the official position communicated in the various places on the web site. I do notice that the Press FAQ with it's Q4 2008 guess is even more optimistic than the other page on the website people seem to be finding that implies March. http://www.postgresql.org/about/press/faq Q: When will 8.4 come out? A: Historically, PostgreSQL has released approximately every 12 months and there is no desire in the community to change from that pattern. So expect 8.4 sometime in the fourth quarter of 2008. Wow that must have been written some time ago I don't think anyone thought we would hit that date in any recent (say last 6-8 months). Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thu, Feb 26, 2009 at 4:16 PM, Tom Lane t...@sss.pgh.pa.us wrote: Scott Marlowe scott.marl...@gmail.com writes: 8.0 is definitely supported. 8.0.0-rc1 is NOT supported, as it was a release candidate and is quite likely to have some nasty bugs in it. It's worse than that: he's running on Windows, which means that this is not just any rc version, but an rc for the first native Windows port. We no longer support 8.0.anything on Windows because of the unfixable bugs in that release. If moteview is still shipping 8.0.rc1 to Windows customers in 2009, I'd say that borders on criminal negligence. Oh my goodness, I think I've got a case of the vapors! I'd run far away from their stuff. God knows what logic bombs are waiting to frag company data in the future. At first I thought it was just mild negligence, like letting your kids play with lawn darts, but this is kind of right up there with sitting a toddler on the windowsill. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
Oh yeah, and I fully expect this product to fail to work with 8.3 due to issues with the removed automatic conversions of types there. I'm sure it's chock full of: substring(datefield,12,6) to grab some part of a date stamp and such. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
Joshua D. Drake wrote: On Thu, 2009-02-26 at 15:27 -0800, Ron Mayer wrote: Joshua D. Drake wrote: On Fri, 2009-02-27 at 10:19 +1300, Tim Uckun wrote: 8.4 was scheduled to be released march 1. ... I do notice that the Press FAQ with it's Q4 2008 guess is even more optimistic ... Wow that must have been written some time ago I don't think anyone thought we would hit that date in any recent (say last 6-8 months). That press FAQ page is often sadly amusing. Back in Jan 2008 that same page stated that 8.3 would come out in July 2007: http://archives.postgresql.org/pgsql-advocacy/2008-01/msg00235.php But that's not nearly as sad as the Chinese FAQs that state that the latest version of Postgres is 8.2.1 or 8.2.3 depending on whether you prefer traditional or simplified writing styles. http://www.postgresql.org/docs/faqs.FAQ_chinese_simp.html http://www.postgresql.org/docs/faqs.FAQ_chinese_trad.html And for german speakers, their newest is 8.2.5 http://www.postgresql.org/docs/faqs.FAQ_german.html Basically for all the languages you get a different lateset release. Surely these FAQ entries are doing more harm than good. Can we please just update all of these to link to some page where the actual latest version is instead; and replace the forward-looking expectations with something that is less misleading? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
On Thursday 26 February 2009 2:44:10 pm Scott Marlowe wrote: I seriously doubt it's anything more than postgresql.conf and pg_hba.conf settings that's keep him out. While it's possible they've built a custom database with all kinds of fancy UDFs and all, I kinda doubt anyone with the brains to do that would be bundling 8.0.0-rc1. But anything's possible. By now the OP knows 8.0.0-rc1 is not the version to be running. The issue is getting the app to run. Since we are talking a bundled app it is probably best to go back to the original bundle. Starting anywhere else brings in a lot of assumptions and we know where that leads. Once the original bundle is up and running then we can take a look at the setup and make the changes necessary to move to a better version, if possible. As your later email states there could be problems with type incompatibilities. The OP seems to be new to Postgres and rather than throw a bunch of changes at him all at once, I thought it best to go step by step. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] speaking of 8.4...
On Thu, 2009-02-26 at 15:41 -0800, Ron Mayer wrote: Back in Jan 2008 that same page stated that 8.3 would come out in July 2007: http://archives.postgresql.org/pgsql-advocacy/2008-01/msg00235.php But that's not nearly as sad as the Chinese FAQs that state that the latest version of Postgres is 8.2.1 or 8.2.3 depending on whether you prefer traditional or simplified writing styles. http://www.postgresql.org/docs/faqs.FAQ_chinese_simp.html http://www.postgresql.org/docs/faqs.FAQ_chinese_trad.html And for german speakers, their newest is 8.2.5 http://www.postgresql.org/docs/faqs.FAQ_german.html Basically for all the languages you get a different lateset release. I can fix the press faq but the others will have to go to the translators project. Joshua D. Drake Surely these FAQ entries are doing more harm than good. Can we please just update all of these to link to some page where the actual latest version is instead; and replace the forward-looking expectations with something that is less misleading? -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] funny view/temp table problem with query
On Feb 26, 2009, at 11:02 AM, Grzegorz Jaśkiewicz wrote: looks like you completely misunderstood my question. I'm not surprised. What do you expect with random capitalisation, random table alias names and random indentation combined with queries getting wrapped by the mailing-list software? With some proper formatting and meaningful alias-names some people might actually understand what you're trying to get at. You're not exactly helping here. You're the one who's asking a question, it's your responsibility that we can understand your problem. With respect to your original naming scheme... indeed, foo, bar and baz aren't the most elaborate names for tables or aliases, but at least we are used to them. More meaningful names are still preferred of course. Those meta-table-names are better reserved for theoretical situations where no meaningful names are available. I'm pretty sure in your case more meaningful names are easy to come up with, so please do. First of all, I wonder why the same query divided up in half - and using temporary table works as expected, and with everything together doesn't. And about rand(), it was tested on large enough set of runs, that I don't think it is to blame. Well, as hard as I try reading that SQL, I lose track somewhere halfway due to the above issues. I don't feel like rewriting your queries to make them readable (I have no obligation to do that, after all), and even then I'm not sure what you're trying to show with them. They do look overly complicated, but without knowing their purpose it is kind of hard to see what you're trying to tell. The queries do everything I wanted it to do, and - no - doing it in software is just baaad, and doesn't do. I figured you were complaining about the performance, hence I gave you a better performing solution. Apparently that wasn't what your question was about, but it's still good advice IMO. Your comment about the solution I gave you borders on insulting. The method I showed you isn't any worse than your solution using temp tables, as both solutions move logic to the application. It's hardly any code in the application in either case, I wonder why you'd be so set against using a cursor that you'd prefer a much more inefficient solution that uses about as much application-side code as what I proposed. Besides, I showed that it's possible to put the logic in the database, but apparently you didn't bother to read that far. (What argument are you trying to make there anyway? X is bad and just doesn't do... How is that an argument? - That's a rhetorical question, it isn't). Goodness, look at all the time I wasted trying to get a proper question out of you... 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,49a7359f129748797120425! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] funny view/temp table problem with query
On Fri, 2009-02-27 at 01:36 +0100, Alban Hertroys wrote: On Feb 26, 2009, at 11:02 AM, Grzegorz Jaśkiewicz wrote: You're the one who's asking a question, it's your responsibility that we can understand your problem. Woah... ease up cowboy. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] overlaps behaviour - ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps ('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
On Monday 23 February 2009 8:10:34 am Marek Lewczuk wrote:
Hello,
I can't find SQL definition for OVERLAPS operator so I don't know
whether following expression's result (false) is appropriate
behaviour:
select ('2006-03-01'::TimeStamp, '2007-12-01'::TimeStamp) overlaps
('2007-12-01'::TimeStamp, 'Infinity'::TimeStamp)
Can anyone confirm that ? In my understanding of overlaps it should
result true, as those two periods overlaps in 2007-12-01.
psql (PostgreSQL) 8.3.5
Best regards,
ML
A link to the SQL standard, good luck with understanding it.
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
Some tests
test=# SELECT '2007-12-01'::TimeStamp;
timestamp
-
2007-12-01 00:00:00
(1 row)
test=# select ('2006-03-01'::timestamp, '2007-12-01'::timestamp) overlaps
('2007-12-01'::timestamp, 'Infinity'::timestamp)
;
overlaps
--
f
(1 row)
test=# select ('2006-03-01'::timestamp, '2007-12-01 00:00:01'::timestamp)
overlaps
('2007-12-01'::timestamp, 'Infinity'::timestamp)
;
overlaps
--
t
From the docs
In addition to these functions, the SQL OVERLAPS operator is supported:
(start1, end1) OVERLAPS (start2, end2)
(start1, length1) OVERLAPS (start2, length2)
Would seem that in this case OVERLAPS means the end1 must be greater than
start2, not equal to it. In other words actually overlap.
--
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] funny view/temp table problem with query
On Thu, Feb 26, 2009 at 3:02 AM, Grzegorz Jaśkiewicz gryz...@gmail.com wrote: First of all, I wonder why the same query divided up in half - and using temporary table works as expected, and with everything together I'm betting it's your use of generate_series(). You can get some weird side effects because it sometimes gets run multiple times not just the once you expect. I'm guessing that's what's biting you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Kerberos V5 required for PostgreSQL installation on Windows
If Kerberos V5 is not installed on a Windows platform, the following error dialog is returned upon attempted installation: Posgres.exe - Unable to Locate Component This application has failed to start because krb5_32.dll was not found. Re-installing the application may fix this problem. [OK] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting time-dependent load statistics
Torsten Bronger wrote: Hallöchen! Yesterday I ported a web app to PG. Every 10 minutes, a cron job scanned the log files of MySQL and generated a plot showing the queries/sec for the last 24h. (Admittedly queries/sec is not the holy grail of DB statistics.) But I still like to have something like this. At the moment I just do the same with PG's log file, with log_statement_stats = on But to generate these plots is costly (e.g. I don't need all the lines starting with !), and to interpret them is equally costly. Do you have a suggestion for a better approach? Tschö, Torsten. If I understood you correctly you might get help from following: http://pgfouine.projects.postgresql.org/ With Regards Ashish Karalkar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connection refused (0x0000274D/10061).
najmuddin hassan wrote: Hi, I just installed a program called moteview by crossbow technologies. It uses postgreSQL 8.0.0-rc1 for its database. There is something wrong as when I launched the program it automaticly gives me an error that the database is not available. The postgreSQL database installation is bundled together with installation disk. I read one of the mail discussion that that version of sql is no longer supported so I upgrade to version 8.3. The moteview program has a script called resetdb.cmd (script is given below) whereby as I understand it is to set the database for the program as server : localhost, database : task, user : tele, and password : tiny. When I run the resetdb.cmd program. The error below shows up: psql: could not connect to server: connection refused (0x274D/10061). Is the server running on host localhost and accepting TCP/IP connection on port 5432. I not a database person. I googled the error msg and from my reading it has something to do with authentication Can I have the solution to this matter. Thanks. set PG_HOST=localhost set PG_PORT=5432 set PG_USER=tele set PG_PASS=tiny set PG_DBASE=task set PG_CONN=-h %PG_HOST% -p %PG_PORT% -U %PG_USER% set PG_DDIR=C:\Program Files\PostgreSQL\8.3\data set PG_BIN=C:\Program Files\PostgreSQL\8.3\bin set PATH=%PG_BIN%; %PATH% if '%1' == ' ' goto START set PG_DDIR=%1\PostgreSQL\8.3\data set PG_BIN=%1\PostgreSQL\8.3\bin set PATH=%PG_BIN%;%PATH% : START echo %PG_BIN% echo %PG_DDIR% echo %PATH% echo Granting permissions to access database copy pg_hba.conf %PG_DDIR% net start pgsql-8.3 sleep 5 pg_ctl reload -D %PG_DDIR% echo Setting up PostgreSQL 8.3 database for Moteview psql -e %PG_CONN% template1 db_user.sql psql -e %PG_CONN% %PG_DBASE% db_moteview.sql psql -e %PG_CONN% %PG_DBASE% db_xsensor.sql psql -e %PG_CONN% %PG_DBASE% db_sample_mts310.sql echo DATABASE CREATION FINISHED Appreciate your help... Regards, Najios Do you have listen_adress in postgresql.conf set properly i.e. to the listen to the adress from where connection is comming or just * to listen to all adresses. this postgresql.conf file is in PG_DDIR With Regards --Ashish -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL to Oracle
Hi All, I want to migrate from PostgreSQL to Oracle and need any tool preferably open source. And I am specially concerned with stored procedures / functions. Regards, Abdul Rehman.
Re: [GENERAL] Array to IN or UNION
Jordi Romagos wrote: I'm doing a procedure and I'm trying to pass a dynamic array into a CURSOR, I found the sentence ANY but it's really slow. Is there any way to convert all the elements in this array to IN condition or one select with unions? For example, I've got a feeling that recent versions of PG actually convert IN to ANY(array) - can you get a sample EXPLAIN ANALYSE output for your problem query? -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
