[GENERAL] Weird query sort
I have a table, queries, with a column value. There is a trigger on
this table that inserts into query_history for each update to value.
I'm trying to graph the query_history table, so I was using a custom
aggregate to turn it into an array:
CREATE AGGREGATE array_accum (anyelement)
(
sfunc = array_append,
stype = anyarray,
initcond = '{}'
);
This worked out pretty well. I was initially concerned that the order
was correct because the table was stored in the right order on the
disk, so I got a query something like this:
select queries.query_id,
array_accum(value) as current_values,
min(query_time) as min_time,
max(query_time) as max_time
fromqueries,
(select query_id, value, query_time
fromquery_history
order by query_time) hist
where queries.query_id = hist.query_id
anddirty = true
andquery_time = update_time
andquery_time update_time - '1 hour'::interval
group by queries.query_id
This works out, but I decided to switch to the last 16 values instead
of the last hour.
So I ended up with this:
select queries.query_id,
array_accum(value) as current_values,
null as previous_values,
min(query_time) as min_time,
max(query_time) as max_time
fromqueries,
(select query_id, value, query_time from (
select query_id, value, query_time
fromquery_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist
where queries.query_id = hist.query_id
anddirty = true
andquery_time = update_time
group by queries.query_id
The part I'm wondering about is this piece:
(select query_id, value, query_time from (
select query_id, value, query_time
fromquery_history
order by query_time desc
limit 16) desc_hist
order by query_time desc) hist
I was intiially trying to sort the inner loop by the time descending,
and the outer loop by the time ascending, but that resulted in an
array that was the reverse of the desired order. Switching the outer
query to order by desc fixed it and comes out in the proper order.
It seems like I should be able to order by quer_time desc and then
query_time asc. Am I missing something? Is this a bug?
-Jeff
--
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] SQL - finding next date
On 4/11/07, Raymond O'Donnell [EMAIL PROTECTED] wrote: Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- SELECT main.term_name, main.term_starts mts, next.term_name, next.term_starts nts FROM terms main LEFT JOIN terms NEXT ON main.term_starts NEXT.term_starts WHERE NOT EXISTS (SELECT 1 FROM terms t WHERE t.term_starts main.term_starts AND t.term_starts NEXT.term_starts) It's kind of a sneak attack way of getting at the min(term_starts) without using an aggregate. Jeff
Re: [GENERAL] SQL - finding next date
On 4/11/07, Raymond O'Donnell [EMAIL PROTECTED] wrote: Hi all, This is probably a very simple one, but I just can't see the answer and it's driving me nuts. I have a table holding details of academic terms, and I need an SQL query such that for any given term I want to find the next term by starting date (or just NULL if there isn't one). Here's the table - CREATE TABLE terms ( term_id serial NOT NULL, term_name character varying(40) NOT NULL, term_starts date NOT NULL, term_ends date NOT NULL, . ) - so, supposing I have the following data - term_id | term_name | term_starts | ... -+-+-+-- 1 | Spring 2007 | 2007-01-10 | ... 2 | Autumn 2007 | 2007-09-01 | ... 6 | Spring 2008 | 2008-01-06 | ... - then for term '1' I'd like to return '2', for term '2' I'd like to return '6', and so on. The closest I've got is getting ALL terms that start after a given one, but I run into trouble after thatany help will be appreciated! Thanks in advance, Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- SELECT main.term_name, main.term_starts mts, next.term_name, next.term_starts nts FROM terms main LEFT JOIN terms NEXT ON main.term_starts NEXT.term_starts WHERE NOT EXISTS (SELECT 1 FROM terms t WHERE t.term_starts main.term_starts AND t.term_starts NEXT.term_starts) It's kind of a sneak attack way of getting at the min(term_starts) without using an aggregate. Jeff
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.?
Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. On 1/8/07, Ian Harding [EMAIL PROTECTED] wrote: There is no GUI tool that I know of, but there is EXPLAIN which gives the same information. - Ian On 1/8/07, guillermo arias [EMAIL PROTECTED] wrote: is there a tracking trace tool in postgre? like the SQL Analizer in MS sqlserver. I have downloaded the PGAdmin III and i have not found any tool like this. Thanks Get your FREE, LinuxWaves.com Email Now! -- http://www.LinuxWaves.com Join Linux Discussions! -- http://Community.LinuxWaves.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.?
On 1/8/07, Jeffrey Melloy [EMAIL PROTECTED] wrote: Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. On 1/8/07, Ian Harding [EMAIL PROTECTED] wrote: There is no GUI tool that I know of, but there is EXPLAIN which gives the same information. - Ian On 1/8/07, guillermo arias [EMAIL PROTECTED] wrote: is there a tracking trace tool in postgre? like the SQL Analizer in MS sqlserver. I have downloaded the PGAdmin III and i have not found any tool like this. Thanks Get your FREE, LinuxWaves.com Email Now! -- http://www.LinuxWaves.com Join Linux Discussions! -- http://Community.LinuxWaves.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.?
Whoops, you're right. On 1/8/07, Ian Harding [EMAIL PROTECTED] wrote: I thought that was called SQL Profiler. http://msdn2.microsoft.com/en-us/library/ms181091.aspx Query Analyzer is EXPLAIN with a GUI. http://msdn2.microsoft.com/en-gb/library/aa178423(SQL.80).aspx Anyway, I have not heard of such a thing for PostgreSQL, although I am sure the basic information you want could be obtained from logging queries and timing. - Ian On 1/8/07, Jeffrey Melloy [EMAIL PROTECTED] wrote: Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. On 1/8/07, Ian Harding [EMAIL PROTECTED] wrote: There is no GUI tool that I know of, but there is EXPLAIN which gives the same information. - Ian On 1/8/07, guillermo arias [EMAIL PROTECTED] wrote: is there a tracking trace tool in postgre? like the SQL Analizer in MS sqlserver. I have downloaded the PGAdmin III and i have not found any tool like this. Thanks Get your FREE, LinuxWaves.com Email Now! -- http://www.LinuxWaves.com Join Linux Discussions! -- http://Community.LinuxWaves.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer
How long has that been available for OS X? Last time I looked at it it wasn't. On 1/8/07, Dave Page [EMAIL PROTECTED] wrote: --- Original Message --- From: Ian Harding [EMAIL PROTECTED] To: Jeffrey Melloy [EMAIL PROTECTED] Sent: 1/8/07, 7:06:31 PM Subject: Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.? I thought that was called SQL Profiler. http://msdn2.microsoft.com/en-us/library/ms181091.aspx Query Analyzer is EXPLAIN with a GUI. http://msdn2.microsoft.com/en-gb/library/aa178423(SQL.80).aspx Anyway, I have not heard of such a thing for PostgreSQL, although I am sure the basic information you want could be obtained from logging queries and timing. pgAdmin has graphical explain, and basic activity monitoring. It is free, and has great support! Regards, Dave ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Search by distance
Oscar Picasso wrote: HI, I would like to implement a search by distance to my application. Something like (pseudo sql): select * from users where users.location is less than 15 miles from chicago. Any documentation on how to implements that? I guess I also need a database of the cities coordinates. Where could I find one? Thanks Oscar I can't help you with the coordinates, but this is exactly what the contrib package PostGIS is designed for. Jeff ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PSQL Data Type: text vs. varchar(n)
Patrick TJ McPhee wrote: In article [EMAIL PROTECTED], Jim Nasby [EMAIL PROTECTED] wrote: % Not sure if it's still true, but DB2 used to limit varchar to 255. I % don't think anyone limits it lower than that. Sybase: 254. Silently truncates. IIRC, Oracle is 4096. Jeff ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Advantages of PostgreSQL over MySQL 5.0
Jimbo1 wrote: Hello there, I'm a freelance Oracle Developer by trade (can almost hear the boos now ;o)), and am looking into developing my own Snowboarding-related website over the next few years. Anyway, I'm making some decisions now about the site architecture, and the database I'm going to need is obviously included. If my site works out, I'm expecting reasonably heavy traffic, so want a database that I'm confident can cope with it. It is out of the question for me to use Oracle, although I am a (biased) 'fan' of that RDBMS. I definitely need to go for a cheaper route, and to that end I'm looking at either MySQL or PostgreSQL. Regarding MySQL, I've been put off by Oracle's recent purchase of InnoDB and realise this could badly impact the latest version of the MySQL database. I can almost hear Larry Ellison's laughter from here (allegedly)! I've also been put off by the heavy marketing propaganda on the MySQL website. I use Oracle at work and PostgreSQL for personal projects, and I think you'll find that PostgreSQL is the more feature-complete (or Oracle-like) database. There are definitely situations Oracle comes out ahead, but for a website I doubt you'll find them. Also, the syntax between the two is more closer to the standard (PostgreSQL is actually better in this) than MySQL. Postgres has Pl/PgSQL, which is close enough PlSQL to not cause any problems. Jeff ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] find last day of month
Andrus Moor wrote:
I have a table containing month column in format mm.
create table months ( tmkuu c(7));
insert into months values ('01.2005');
insert into months values ('02.2005');
How to create select statement which converts this column to date type
containing last day of month like
'2005-01-31'
'2005-02-28'
Andrus.
select to_date(tmkuu, 'mm.') + '1 month'::interval - '1
day'::interval from months; will convert it to a timestamp. You can
further downcast to date if you need to.
Jeff
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] fts, compond words?
Mike Rylander wrote: Mike Rylander wrote: On 12/6/05, Marcus Engene [EMAIL PROTECTED] wrote: [snip] A (B | (New OperatorTheNextWordMustFollow York)) Actually, I love that idea. Oleg, would it be possible to create a tsquery operator that understands proximity? Or, how allowing a predicate to the current '' op, as in '[dist=1]' meaning next token follows with a max distance of 1. I imagine that it would only be useful on unstripped tsvectors, but if the lexem position is already stored ... This might not be a solution in the longer term, but what I do for that type of thing is idxfti @@ '(ab)' and message ~* 'a b' Postgres is smart enough to use the results of the GIST index and go from there with the message scanning. Jeff ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] is there a function which elminates spaces?
codeWarrior wrote: SELECT trim(trailing ' ' from city_name) AS city_name FROM sys_cities; You might consider reading the manual as there are a multitude of string manipulation functions built into postgreSQL You didn't answer his question. If you're going to rag on someone for not reading the manual, at least you could read what he's asking. Pierre Couderc [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] In a WHERE clause, I want to to compare strings ignoring the spaces inside them. Is therd a function to do that? I do not find it in the documentation. SELECT ... WHERE (ignore_spaces(table.phone_number) ~* igore_spaces(?)); would be fine but ignore_space() does not exist! Maybe there is a solution based on regular epxression, but I do not see it. I don't see a way to do it through regular expressions, either, though in the phone number case, you could split the phone number into different columns based on area code, whatever the middle group is called, and whatever the last group is called. Or you could remove the spaces before inserting and comparing, or write a function with pl/perl or something. With perl's greater regular expression control, it would probably be a one liner. Jeff ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PostgreSQL 8.1 vs. MySQL 5.0?
On Oct 13, 2005, at 12:00 PM, Alex Turner wrote: snip Instance Manager: Uniquely MySQL. It allows things like starting and stopping the database remotely. I cannot think of a reason ever to need this when we have OpenSSH snip I'm just curious, but how does this work for a windows box? There are plenty of Remote Management options for Windows. One of the common ones ships with XP Pro and allows you to start and stop services remotely, etc. Jeff ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-advocacy] [GENERAL] Oracle buys Innobase
Joshua D. Drake wrote: Bruce Momjian wrote: Matthew Terenzio wrote: As much as I respect Marc and Postgresql.org, I can't see Oracle hiring him away as a killer threat to the community. People would set up camp somewhere else, like Command Prompt. It would hurt things for a while but the software is too important to too many to be killed by a domain name or person. Right, all these damages are temporary, which is probably why we haven't been attacked yet. There are also logistical problems with attacking PostgreSQL because nobody owns it. MySQL was an easy target because of the way they negotiated their business contracts for use of Innodb. PostgreSQL doesn't suffer from that. Our only real, substantiated concern that I can see is the potential for the Software Patent crap. Sincerely, Joshua D. Drake But what if they came in sideways and bought Command Prompt? (As an example.) You could do a lot more to destroy PostgreSQL's market in the business world by destroying the various support mechanisms. Your business is much closer to eating their lunch than PostgreSQL itself. So what if they bought Command Prompt (or someone else like it) and then cut it off at the knees?No one ever accused Larry Ellison of being dumb ... different strategies for different opponents. Jeff ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL Gotchas
Neil Conway wrote: COUNT(*) very slow: this is a known issue -- see the -hackers archives for many prior discussions. MVCC makes this hard to solve effectively (whether applications should actually be using COUNT(*) on large tables with no WHERE clause is another matter...) -Neil And it's not like a count(*) on an Oracle database of any decently-sized dataset is blazing fast, or even in blazing's ballpark. The only thing I could see actually being an issue is the random() one and add missing from. The rest are trivial. The random() thing is interesting, esoteric, and probably has never been a problem in a real situation. (Or has exactly once, when he wrote that gotcha) Jeff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] POSS. FEATURE REQ: Dynamic Views
Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. DYNAMIC is something I made up. ALTER VIEW RECOMPILE is Oraclese but I'm not sure what we're talking about here is exactly the same purpose. I'm not sure it even does anything in Oracle any more. It used to be that *any* DDL on underlying tables caused view on them to become invalid and produce errors until they were recompiled. I think that's changed and recompile may be a noop now on Oracle. It's still necessary in Oracle 9i. Any time a table is changed that has a view on it Bad Things Happen. Jeff ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tool for database design documentation?
On Jul 30, 2005, at 2:30 PM, Jon Christian Ottersen wrote:We are trying to find a good way to document our database design – what is the rationale behind each table/field, what kind of information is each field supposed to contain, perhaps also something about the linking between the tables etc. Is there anybody who has some experience on this? Is the built in ‘comments’ fields in pgsql the best tool or are there any special tools that would be recommendable?Druid (http://druid.sourceforge.net) works fairly well. (Though the interface is abysmal, it does its job). It makes javadoc-style documentation for tables and columns.Jeff
[GENERAL] Table Update Systems (was: chosing a database name)
I think a better approach is to handle configuration management with a table in each schema. Update the schema, update the table. This works well with automating database upgrades as well, where upgrades are written as scripts, and applied in a given order to upgrade a database from release A to C, or A to X, depending on when it was archived. A script naming convention (e.g. numerical) can determine order, and each script can register in (write a line to) the configuration management table. This allows for error analysis, among other things. Rick I'm currently looking at implementing a system almost exactly like this, and I was wondering if there is anything around that does this. Jeff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need help writing SQL statement
D A GERM wrote:
I have been trying to write an sql statement that returns the same
hours in a time stamp no matter what the date.
I can to pull same hours on the the same days but have not been able
to figure out how to pull all the same hours no matter what the date.
Here is the one sql statement I have been using:
SELECT COUNT(time_stamp) FROM table WHERE time_stamp BETWEEN
2005062910 and 2005063110;
Any help would be appreciated.
Thanks in advanced for any help
You can do something like
SELECT count(*)
FROM table
where date_part('hour', timestamp) in (10, 11)
This query is going to require a seq scan, so if you're running it
frequently you can make an index on date_part('hour', timestamp)
Jeff
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Days in month query
Or
select date_part('day', date_trunc('month', '01/10/04') + '1
month'::interval - '1 day'::interval) as days;
or
select date_part('day', to_date('mon', 'Jan') + '1 month'::interval - '1
day'::interval) as days;
Arthur Hoogervorst wrote:
Hi,
Something like this?
SELECT date_part('day',
(date_part('year', '01/10/04' :: date) || '-' ||
date_part('month', '01/10/04' :: date) || '-01') ::date
+ '1 month'::interval
- '1 day'::interval) AS days;
Regards,
Arthur
On Wed, 30 Mar 2005 16:45:43 -0700, Mark Fox [EMAIL PROTECTED] wrote:
Greetings,
Thanks Dan, but I searched for, and scoured, that page before asking
my question. It helped with some of the details, but not on the
general approach. I'll try to restate my problem in a better way:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
Mark
On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit [EMAIL PROTECTED] wrote:
The online documentation has a search function. It would lead you to
this:
http://www.postgresql.org/docs/8.0/static/functions-datetime.html
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Fox
Sent: Wednesday, March 30, 2005 3:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Days in month query
Greetings,
This is more of an SQL question, but since each database server seems
to use it's own syntax for handling dates...
Is there a way to query for the days in a month? For example,
querying for the days in January of this year? Listing the days
between two dates would be useful as well.
I'm sure I saw a query like this somewhere, but I can't track it down.
Just to be clear, there were no tables involved. Just a SELECT
statement that returned all the days in a given month.
Basically, I have a table of events and I'd like to generate a
histogram of how many events occur on the days of a particular month.
What I do now is create a temporary table, fill it with the
appropriate days, and then do a cross join and summation to generate
what I need. This works, but seems messy to me.
Mark
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] SCHEMA compatibility with Oracle/DB2/Firebird
Chris wrote: I know this isn't entirely postgresql specific, but it wouldn't be on another list either so here goes... I am writing an open source application where I would like to support at least oracle, and possibly firebird or DB2, in addition to postgresql which will be the default. I'm not going to try to support mysql. The application has many users, and in postgresql what works well is to create a schema for each user instead of a separate database. The main reason for schema's instead of databases is that the app runs under mod perl, and there are too many users to have a pool of open connections to each database. There are also a set of common functions that I usually store in the public schema. That way when working with the data of a particular user I can do a SET search_path TO user,public, and have access to all the functions without having to duplicate them in every schema. My question is how easily would this work with other databases? I know Oracle supports schema's, but I dont' know about the others. I also don't know if other databases have the concept of a search path, but I would think that they do. Although Oracle doesn't have a search path, it is possible to make functions publicly available by doing grant blah to public. After that they can be used without a schema identifier. Jeff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Index on a view??
Ragnar Hafstað wrote: On Wed, 2005-01-05 at 13:03 -0800, Jeff Davis wrote: On Wed, 2005-01-05 at 13:14 -0700, Michael Fuhr wrote: On Wed, Jan 05, 2005 at 08:15:28PM +0100, Joost Kraaijeveld wrote: [snip] PostgreSQL doesn't have materialized views per se but it does have functionality that can implement them. Can you tell me what you mean by that? triggers, maybe ? gnari Specifically, info can be found here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Jeff ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Memory Errors OS X
I attempted to install 8.0 RC 2 alongside 7.4.5 on my OS X box, but initdb failed with an error about not enough shared memory. Remembering that this was a problem for starting two postmasters at the same time on OS X, I increased the shmmax value to 500 megabytes (I had seen something say raising it to half the available ram would be fine), but when I rebooted my machine neither 8.0 or 7.4.5 would start. So I lowered it to 256 megabytes, thinking there might be an upper limit on that kind of stuff. When I rebooted my machine, 7.4.5 starts fine, but 8.0 still will not start alongside it. I don't particularly need both postmasters running at the same time, but I would like to figure out the solution to this problem. (By the way, in the course of this I attempted to manually run /etc/rc ... there were humorous results and my computer didn't really like it: http://www.visualdistortion.org/misc/dont_do_this.png) Jeffrey Melloy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Memory Errors OS X
Tom Lane wrote: Jeffrey Melloy [EMAIL PROTECTED] writes: I attempted to install 8.0 RC 2 alongside 7.4.5 on my OS X box, but initdb failed with an error about not enough shared memory. Don't forget that both shmmax and shmall may need attention ... and, just to confuse matters, they are measured in different units. regards, tom lane I didn't realize that they were different units. Setting shmmax to 268435456 and shmall to 65536 works fine. Thanks, Jeff ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] combining two queries?
If you want to return rows with zeros, you may need to do something like this: select b.name as viewer, count(viewerid) from xenons b left join viewer_movies a on (b.id = a.viewerid) group by b.name Eddy Macnaghten wrote: select b.name as viewer, count(*) from viewer_movies a, xenons b where b.id = a.viewerid group by b.name On Sat, 2004-10-23 at 00:55, Mark Harrison wrote: How can I combine these two queries? # select viewerid,count(*) from viewer_movies group by viewerid order by viewerid; viewerid | count --+ 22964835 | 3055 22964836 | 1291 22964837 | 3105 22964838 |199 planb=# select name from xenons where id = 23500637; name - x.moray I would like to end up with a query result like this: viewer | count --+ x.surf | 3055 x.dream | 1291 x.moray | 3105 x.sleepy |199 Many TIA! Mark ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Out of memory errors on OS X
I have a couple users trying to install Postgres on OS X. To the best of my knowledge, both of them are using 7.4.5/10.3.5, and got identical errors while trying to init the database: Reducing the shared buffers didn't help. Any thoughts would be appreciated. Jeffrey Melloy [EMAIL PROTECTED] William-Rowcliffes-Computer:/Users/wmrowcliffe postgres$ /usr/local/bin/initdb -D /usr/local/pgsql/data The files belonging to this database system will be owned by user postgres. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory /usr/local/pgsql/data... ok creating directory /usr/local/pgsql/data/base... ok creating directory /usr/local/pgsql/data/global... ok creating directory /usr/local/pgsql/data/pg_xlog... ok creating directory /usr/local/pgsql/data/pg_clog... ok selecting default max_connections... 10 selecting default shared_buffers... 50 creating configuration files... ok creating template1 database in /usr/local/pgsql/data/base/1... FATAL: could not create shared memory segment: Cannot allocate memory DETAIL: Failed system call was shmget(key=1, size=1081344, 03600). HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 1081344 bytes), reduce PostgreSQL's shared_buffers parameter (currently 50) and/or its max_connections parameter (currently 10). The PostgreSQL documentation contains more information about shared memory configuration. initdb: failed ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Out of memory errors on OS X
Tom Lane wrote: Jeffrey Melloy [EMAIL PROTECTED] writes: I have a couple users trying to install Postgres on OS X. To the best of my knowledge, both of them are using 7.4.5/10.3.5, and got identical errors while trying to init the database: They need to increase the system's shmmax limit (sysctl kern.sysv.shmmax, which is only 4MB by default). You can run one postmaster that way ... not very well, but it will run ... but you definitely can't start two. I surmise that they already had one postmaster running? In OSX 10.3 I believe that the recommended way to fix this is to edit /etc/rc's setting, and then reboot. AFAICS there is no reason not to raise shmmax to 50% or so of physical RAM. I have asked Apple about using a saner default for shmmax, but a few more complaints in their bug system wouldn't hurt. regards, tom lane I'll pass it on, though I'm wondering why they would have that problem and others (myself included) don't. Jeff ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Installing FullTextSearchTool tsearch2
Oleg Bartunov wrote: Marcel, it's very difficult from you message where do you lost. pgsql version, OS version, cut'n paste of commands you run and output would be fine. To install tsearch2 most people need (as postgresql superuser): 1. install postgresql and headers 2. cd contrib/tsearch2 3. make; make install; make installcheck Note, tsearch2 is just a plain contrib module and installation is the same as for other modules. Oleg On Wed, 18 Aug 2004, Marcel Boscher wrote: Hello everybody, i tried to J.U.S.T install the FullTextSearchTool tsearch2 under the guidiance of : http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ http://www.sai.msu.su/%7Emegera/postgres/gist/tsearch/V2/ and i'm almost losing my mind... runningagainstwallheadsfirst after i installed postgre which works fine i change to the folder /contrib/tsearch2/ there i guessed to use MAKE as it is not mentioned in the manual seemed to work... from then everything i tried to execute as guided on their website i get loads and pagelong error messages varying in many different error messages most are current transaction is aborted til end stuff over to syntax error at or near default at character 1 or could not access file$libdir/tsearch2: no such file or directory does anybody have a short instruction manual with only the syntax on how to install the fulltextcrap without 1 words around telling lies of how wonderful and easy this god made tool is? ... Gods are here to see: http://www.sai.msu.su/~megera/postgres/gist/oleg-teodor-1.jpg http://www.sai.msu.su/%7Emegera/postgres/gist/tsearch/V2/ Just a simple 10 line instruction on what to do after installing postgre the fashioned old way and now wanting to add tsearch2 Any help would be appreciated Thx in advance The instructions I have for installing it are: cd /POSTGRES_SOURCE_LOCATION//contrib/tsearch2 make sudo make install psql tsearch2.sql (http://www.visualdistortion.org/sqllogger/install.html) Not very tough. Jeff ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Renaming a schema
Recently, I decided to rename one of my schemas from adium to im. Then, all inserts started failing. I recreated a couple functions, changed the search path, and all inserts are still failing due to referential integrity checks going against adium still. Is there any way I can fix this, short of manually dropping every constraint and recreating them? Jeff ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Humor me: Postgresql vs. MySql (esp. licensing)
On Thursday, October 9, 2003, at 01:42 AM, Shridhar Daithankar wrote: [EMAIL PROTECTED] wrote: One of my friend lost data with mysql yesterday.. The machine was taken down for disk upgrade and mysql apperantly did not commit the last insert.. OK he was using myisam but still..:-) It sounds like that is more a problem with improper operating protocols than with the underlying database. No. Problem is machine was shutdown with shutdown -h. It sends sigterm to everybody. A good process would flsuh the buffers to disk before finishing. Mysql didn't on that occasion. Transactions or not, this behaviour is unacceptable for any serious app. Would PG know enough to do a commit regardless of how the database was shut down? A second question is whether doing a commit is what the user or application would always want to have happen, as it could result in a half-completed transaction. Do a shutdown -h on a live database machine with pg. It will gracefully shut itself down. Shridhar I'm curious ... do MySQL lists talk about this as much as we do? What do they say? Well, we run Slashdot. Well, we can select count(*) faster We have all the features they do! Nobody uses views or triggers! Jeff ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] SELECT Question
If I'm understanding you correctly, you can do something like:
select cola,
colb,
exists
(select 'x'
from tableb
where colc = colb)
from tablea
Since that has a subselect, you may get better performance with
something like this:
select cola,
colb,
case when colc is null
then 'f' else 't' end as exists
from table1 left join table2 on colb = colc;
jmelloy=# create table table1(cola serial, colb char);
NOTICE: CREATE TABLE will create implicit sequence 'table1_cola_seq'
for SERIAL column 'table1.cola'
CREATE TABLE
jmelloy=# create table table2 (colc char);
CREATE TABLE
jmelloy=# insert into table1 (colb) values ('A');
INSERT 1551538 1
jmelloy=# insert into table1 (colb) values ('B');
INSERT 1551539 1
jmelloy=# insert into table1 (colb) values ('a');
INSERT 1551540 1
jmelloy=# insert into table2 values ('B');
INSERT 1551541 1
jmelloy=# select cola, colb, exists (select 'x' from table2 where colc
= colb) from table1;
cola | colb | ?column?
--+--+--
1 | A| f
2 | B| t
3 | a| f
(3 rows)
jmelloy=# select cola, colb, case when colc is null then 'f' else 't'
end as exists from table1 left join table2 on colb = colc;
cola | colb | exists
--+--+
1 | A| f
2 | B| t
3 | a| f
(3 rows)
On Sunday, August 31, 2003, at 12:03 PM, Alex wrote:
Hi,
I need to form a query where i can add some columns based on the
result.
Table A
ColA, ColB
--
1 A
2 B
3 A
Table B
ColC
A
If A exists if would like the result back as
1 A OK
2 B NG
3 A OK
Is it possible to replace the value in the query ?
Thanks
Alex
---(end of
broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Join question
On Thursday, August 28, 2003, at 09:03 PM, Williams, Travis L, NEO wrote: I have a table1 with 2 col (a b) where b can sometimes be null. I need a query that if B is null I get back the contents of A.. but if B is not null I do a select d from table2 where d like '%b%' There is nothing to join between table1 table2 (unless you can join on likes You can do something like this, but I can't promise any great performance: select case when b is null then a else (select d from table2 where d ~* b) end as value fromtable1; jmelloy=# select * from table1; a | b ---+-- 1 | 2 | 3 | 4 | for 5 | asdf 6 | coo (6 rows) jmelloy=# select * from table2; d -- forsythe manasdf cool (3 rows) jmelloy=# select case when b is null then a::varchar else jmelloy-# (select d from table2 where d ~* b) end as value jmelloy-# from table1; value -- 1 2 3 forsythe manasdf cool (6 rows) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pgplsql - Oracle nvl
Hi, I'll try to switch from Oracle to postgres for some small applications. Is it possible to build functions like Oracle's nvl or decode with pgplsql? How can I make a function like nvl that works for every datatype? Best regards, Christian Try coalesce. The syntax is the same as nvl. Jeff ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] 'now' vs now() performance
I was recently running into performance problems with a query containing now()::date or CURRENT_DATE. When I went to debug, 'now'::date made efficient use of the index (on a timestamp field). The docs say that 'now' is turned into a constant right away. Is this overhead/poor planning simply because 'now' gets converted to a constant so much earlier in the process? I've pasted the query plans below. Jeff jmelloy=# explain analyze select distinct sender_id from messages where message_date now()::date; QUERY PLAN -- Unique (cost=4517.17..4639.74 rows=2451 width=4) (actual time=1697.62..1697.90 rows=4 loops=1) - Sort (cost=4517.17..4578.45 rows=24515 width=4) (actual time=1697.61..1697.74 rows=62 loops=1) Sort Key: sender_id - Seq Scan on messages (cost=0.00..2729.88 rows=24515 width=4) (actual time=1695.42..1697.22 rows=62 loops=1) Filter: (message_date ((now())::date)::timestamp without time zone) Total runtime: 1698.11 msec (6 rows) jmelloy=# explain analyze select distinct sender_id from messages where message_date 'now'::date; QUERY PLAN Unique (cost=201.86..202.14 rows=6 width=4) (actual time=1.24..1.52 rows=4 loops=1) - Sort (cost=201.86..202.00 rows=56 width=4) (actual time=1.23..1.36 rows=62 loops=1) Sort Key: sender_id - Index Scan using adium_msg_date_sender_recipient on messages (cost=0.00..200.22 rows=56 width=4) (actual time=0.23..0.84 rows=62 loops=1) Index Cond: (message_date '2003-08-18 00:00:00'::timestamp without time zone) Total runtime: 1.74 msec (6 rows) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Graphical Mapping a Database
If you don't mind spending a little money and are on Windows, you can use Microsoft Visio Professional. On Saturday, August 16, 2003, at 01:38 AM, Ron Johnson wrote: On Thu, 2003-08-14 at 23:17, David Fetter wrote: Tim Edwards [EMAIL PROTECTED] wrote: I have been request to create a relational database map that can be place on the wall in my office. Generally I done these in the past by just manually typing the information into Access and then printing a map from there. However the database they want mapped has a couple dozen tables in it and I don't relish the idea of entering all that by hand. Nor do I relish the idea of sitting down and writing one a program to automaticly do it if there one aviaiable. Does anyone have a utility to map out database structures and put it in a printable format ? PDF, GIF, JPG, etc. Try DBVisualizer at http://www.minq.se/. There's also AutoDoc http://www.rbt.ca/autodoc/index.html which can output to a dia-compatible format. +---+ | Ron Johnson, Jr.Home: [EMAIL PROTECTED] | | Jefferson, LA USA| | | | Man, I'm pretty. Hoo Hah! | |Johnny Bravo | +---+ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Sorting Problem
It does if you look at the original email. Maksim must've just transposed a couple letters when he was writing his demo. Jeff Kathy zhu wrote: If it skips -, then RMT-* should come before RM-V*, but they don't, why ?? Maksim Likharev wrote: en_US locale skips? punctuation from sorting index, so in your case RM-791 RM-AV2100 RM-PP401 RM-PP402 RM-PP404 RM-V10 RM-V11 RM-V12 RMT-D10 RMT-D108A RMT-D109A RMT-D116A RMT-V402 == RM791 RMAV2100 RMPP401 RMPP402 RMPP404 RMV10 RMV11 RMV12 RMTD10 RMTD108A RMTD109A RMTD116A RMTV402 -Original Message- From: Kathy zhu [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 3:30 PM To: Tom Lane Cc: Tim Edwards; [EMAIL PROTECTED] Subject: Re: [GENERAL] Sorting Problem Do you mean that soring doesn't work for en_US locale ??? And, does encoding affect sorting at all ?? thanks, kathy Tom Lane wrote: Tim Edwards [EMAIL PROTECTED] writes: When I sort ASC on the varchar I get some strange results. Here a section of data cut after running a sort. It starts with RM- then does RMT- Then goes back for more RM-. Sounds like you're in en_US locale, or at least something other than C locale. Unfortunately this can only be fixed by re-initdb'ing :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Am I using the SERIAL type properly?
If you use a serial datatype, it simply says the *default* behavior is
to use the next value of sequence a. So you can insert 1, 2,
10,204,492 into the column just fine. However, by inserting data into
something you want to auto-increment, you can run into non-uniqueness.
(Like you were). So most people tend to leave the id field as
completely null. Setting the value is only for loading data, or data
that already has something as a PK.
The command to set it is select pg_catalog.setval('sequence_name',
value). Check the docs on sequences for more info.
On Saturday, July 12, 2003, at 02:36 AM, Chad N. Tindel wrote:
drop table A;
create table A (
id SERIAL PRIMARY KEY,
foo int default 5,
bar int default 10
);
insert into A (id, foo, bar) values (1, 1, 1);
insert into A (id, foo, bar) values (2, 2, 2);
insert into A (id, foo, bar) values (3, 3, 3);
insert into A (id, foo, bar) values (4, 4, 4);
A serial data type will allow you to input values into it, but the
counter is still at 0. That's why your first update statement's
nextval outputs 1. It's not showing what was already inserted, it's
showing what would have been. So at this point you need to set the
current value of id at 4.
I wasn't quite able to figure out what the solution to my problem
should be...
Are you saying that a serial type isn't smart enough to realize that I
just
inserted the values 1, 2, 3, 4, and to figure out that if I ask it to
self-generate the next one that it should return a 5? I have to tell
it that
the next value it should return is 5?
Maybe I'm just using the wrong feature of postgres... is there some
way to
get the same behavior as a mysql auto_increment primary key column?
Nextval *sets* the sequence at N+1.
OK... didn't know that. How do I set the sequence to an arbitrary
value X?
Thanks for the help! Hopefully I'll get the hang of this soon.
Chad
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Am I using the SERIAL type properly?
drop table A; create table A ( id SERIAL PRIMARY KEY, foo int default 5, bar int default 10 ); insert into A (id, foo, bar) values (1, 1, 1); insert into A (id, foo, bar) values (2, 2, 2); insert into A (id, foo, bar) values (3, 3, 3); insert into A (id, foo, bar) values (4, 4, 4); A serial data type will allow you to input values into it, but the counter is still at 0. That's why your first update statement's nextval outputs 1. It's not showing what was already inserted, it's showing what would have been. So at this point you need to set the current value of id at 4. insert into A (foo, bar) values (5, 5); insert into A (foo, bar) values (6, 6); -- The output that I get is: [EMAIL PROTECTED] Setup]$ p a.sql DROP TABLE NOTICE: CREATE TABLE will create implicit sequence 'a_id_seq' for SERIAL column 'a.id' NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'a_pkey' for table 'a' CREATE TABLE INSERT 44289 1 INSERT 44290 1 INSERT 44291 1 INSERT 44292 1 ERROR: Cannot insert a duplicate key into unique index a_pkey Here it's trying to insert 1 ERROR: Cannot insert a duplicate key into unique index a_pkey Here 2 Nextval *sets* the sequence at N+1. INSERT 44319 1 id | foo | bar +-+- 1 | 1 | 1 (1 row) nextval - 1 === These are unrelated. Nextval is at 4 now, so the insert (foo,bar) works correctly. id | foo | bar +-+- 1 | 1 | 1 2 | 2 | 2 3 | 3 | 3 4 | 4 | 4 5 | 5 | 5 (5 rows) nextval - 6 == This increases it again, showing the skipping behavior you were seeing. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
