Re: [GENERAL] function on trigger

On 09/01/2011 01:39 AM, Marcos Hercules Santos wrote: hi guys I'm newbie in Psql and I'm trying to build one function in order to count the products for each supplier. So i'm gonna put it quite simply though this example Please, consider a table called books with the foll

[GENERAL] function param and declared variable of same name

Tested in 8.2 and 9.0.1 In plpgsql, if you have a function parameter and a variable of the same name, it ignores the value passed in and initializes the variable to null. The correct action, IMO, would be to raise an error. create or replace function test1(x int

Re: [GENERAL] ERD Tool

On Wed, 2011-08-31 at 17:24 +0530, Adarsh Sharma wrote: > Dear all, > > Is there any open source ERD Tool for Postgresql Database. > I find some paid tools but looking for free tools. > If you can compile pgAdmin, it offers a database designer tool. It's in its early stages, and still has rough

Re: [GENERAL] function on trigger

On 08/31/11 3:39 PM, Marcos Hercules Santos wrote: I'm newbie in Psql and I'm trying to build one function in order to count the products for each supplier. So i'm gonna put it quite simply though this example Please, consider a table called books with the following fields bookid, title, pric

Re: [GENERAL] function on trigger

On Aug 31, 2011, at 18:39, Marcos Hercules Santos wrote: > hi guys > > I'm newbie in Psql and I'm trying to build one function in order to > count the products for each supplier. So i'm gonna put it quite simply > though this example > > > Please, consider a table called books with the follow

[GENERAL] function on trigger

hi guys I'm newbie in Psql and I'm trying to build one function in order to count the products for each supplier. So i'm gonna put it quite simply though this example Please, consider a table called books with the following fields bookid, title, price, idPublisher and one another table call

Re: [GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, August 31, 2011 7:10 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error "David Johnston" writes: > "in an aggregate with DISTINCT, ORDER BY ex

Re: [GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error

On 31 August 2011 23:54, David Johnston wrote: > "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument > list" > > Why? > > If I add the fields of the ORDER BY expression to the DISTINCT clause I can > no longer use DISTINCT since the ORDER BY values are not unique. Nor do I

Re: [GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error

"David Johnston" writes: > "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument > list" > Why? Because the results are ill-defined otherwise. In your example, > ... ARRAY_AGG(DISTINCT accountnumber ORDER BY amount DESC) ... there may be many rows with the same "a

[GENERAL] ARRAY_AGG(DISTINCT a ORDER BY b) Error

"in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list" Why? If I add the fields of the ORDER BY expression to the DISTINCT clause I can no longer use DISTINCT since the ORDER BY values are not unique. Nor do I want the contents of the final ARRAY to contain the ORDER B

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:53 AM, Ben Chobot wrote: > On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > >> Ben Chobot writes: >>> Tom, if there's anything else we can provide that might you out, let me >>> know. >> >> If you could extract a self-contained test case for the bad estimation, >> that wo

Re: [GENERAL] how do I disable automatic start on mac os x?

if you do: open OS X will open the file in the plist editor. I tried doing that but didn't get a way with saving though :) -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-do-I-disable-automatic-start-on-mac-os-x-tp1926565p4754428.html Sent from the PostgreSQL - ge

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 11:10 AM, Tom Lane wrote: > Ben Chobot writes: >> Tom, if there's anything else we can provide that might you out, let me know. > > If you could extract a self-contained test case for the bad estimation, > that would be useful. OK, we'll pull something together. In the meant

Re: [GENERAL] Using a function in different schemas

Thanks Adrian! That is exactly what I was looking for! =o) On Wed, Aug 31, 2011 at 3:35 PM, Adrian Klaver wrote: > On 08/31/2011 08:38 AM, Juan Manuel Alvarez wrote: >> >> Hello everyone! This is the first time I post on this list. Until now, >> I was able to get a solution to all the problems I

Re: [GENERAL] Using a function in different schemas

On 08/31/2011 08:38 AM, Juan Manuel Alvarez wrote: Hello everyone! This is the first time I post on this list. Until now, I was able to get a solution to all the problems I had, but now I am facing an issue I can't resolve, so I hope you can help me. The problem goes like this: I am using the sa

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

Ben Chobot writes: > Tom, if there's anything else we can provide that might you out, let me know. If you could extract a self-contained test case for the bad estimation, that would be useful. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On Aug 31, 2011, at 10:47 AM, Tom Lane wrote: > Peter Eisentraut writes: >> I don't have an answer for you, but this report looks suspiciously >> similar to the one I posted the other day at >> , >> which, now that I think about i

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

Peter Eisentraut writes: > I don't have an answer for you, but this report looks suspiciously > similar to the one I posted the other day at > , > which, now that I think about it, also manifested itself after the > upgrade to 8.4.

Re: [GENERAL] "invalid input syntax for type bytea"

On 08/31/11 9:02 AM, Alan Millington wrote: I am running Postgres 8.4.1 on Windows XP Pro... 8.4 is currently up to 8.4.8 and you really should upgrade.8.4.1 is about 2 years old, and there's a long list of bugs fixed in the later 8.4 updates, see the release notes for 8.4.2 through 8.4.8

Re: [GENERAL] "invalid input syntax for type bytea"

Alan Millington writes: > I recently hit an error when trying to insert into a bytea column. The > following is the first part of what appears in the Postgres log: > 2011-08-25 14:42:40 BST HINT:  Use the escape string syntax for backslashes, > e.g., E'\\'.2011-08-25 14:42:40 BST ERROR:  invalid

Re: [GENERAL] out of memory - no sort

On Aug 31, 2011, at 10:52 AM, Don wrote: > I had always thought that a 32bit machine could access up to 4GB. > So what is the limiting factor ? - Half of your memory space may be given over to memory-mapped I/O. Now you're down to 2GB. - Your process's executable, plus any libraries it uses, pl

Re: [GENERAL] out of memory - no sort

The server is 64 bit and client is 32 bit...  I tried the select * from table on the server and the query worked... but I am puzzled why it does not work on the 32bit machine.  I had always thought that a 32bit machine could access up to 4GB. So what is the limiting fac

Re: [GENERAL] out of memory - no sort

Hello 2011/8/31 Don : > Pavel... > > Thanks for the reply... > > This still did not solve the issue.  It seems odd that a simple select > command in psql accessing 32MB of records should cause a problem.  I have > tables much larger than this and may want to access them the same way. > so there a

Re: [GENERAL] how do I disable automatic start on mac os x?

On Aug 31, 2011, at 8:46 AM, edwardIshaq wrote: > if you do: > open > OS X will open the file in the plist editor. > I tried doing that but didn't get a way with saving though :) Probably a privileges violation, right? In a prior message on this thread I suggested: sudo launchctl unload -w b

[GENERAL] "invalid input syntax for type bytea"

I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My database is UTF8. My program code is written in Python, and to interface to Postgres I use mxODBC 3.0 and the PostgreSQL Unicode driver PSQLODBCW.DLL version 8.01.02.00 dated 31/01/2006. I recently hit an error when trying

Re: [GENERAL] out of memory - no sort

On Aug 31, 2011, at 9:51 AM, Don wrote: > Both machines are 64bit. Are all your server & client builds 64-bit? 32M rows, unless the rows are <50 bytes each, you'll never be able to manipulate that selection in memory with a 32-bit app. -- Scott Ribe scott_r...@elevated-dev.com http://www.ele

[GENERAL] Using a function in different schemas

Hello everyone! This is the first time I post on this list. Until now, I was able to get a solution to all the problems I had, but now I am facing an issue I can't resolve, so I hope you can help me. The problem goes like this: I am using the same distribution of tables/functions into different sc

Re: [GENERAL] out of memory - no sort

Pavel... Thanks for the reply... This still did not solve the issue. It seems odd that a simple select command in psql accessing 32MB of records should cause a problem. I have tables much larger than this and may want to access them the same way. I have 24 GB RAM on the sever and 32GB RAM

Re: [GENERAL] heavy swapping, not sure why

On Tue, Aug 30, 2011 at 9:05 PM, Scott Marlowe wrote: > Well, we had zone reclaim mode autoset to 1, and we had to turn it off > to get decent performance with postgresql.  Machine was a quad > dodecacore Magny Cours, so 48 cores with 128G RAM.  RAID controller is > an Areca 1680 with BBU, 34 15kR

Re: [GENERAL] Index usage on OR queries

On 31 Srpen 2011, 16:49, Andy Colson wrote: > On 8/31/2011 9:35 AM, Tore Halvorsen wrote: >> Hi, >> >> I'm trying to optimize a query where I have two tables that both have a >> timestamp column. I want the result where either of the timestamps is >> after a specified time. In a reduced form, like

Re: [GENERAL] Index usage on OR queries

On 8/31/2011 9:53 AM, Tore Halvorsen wrote: On Wed, Aug 31, 2011 at 4:49 PM, Andy Colson mailto:a...@squeakycode.net>> wrote: On PG 9, after I ANALYZED the tables, it used indexes: QUERY PLAN --__--__-

Re: [GENERAL] Index usage on OR queries

On Wed, Aug 31, 2011 at 4:49 PM, Andy Colson wrote: > On PG 9, after I ANALYZED the tables, it used indexes: > > > QUERY PLAN > --**--** > --**- > Merge Join (cost=1.59..82778.35 rows=13171 w

Re: [GENERAL] Index usage on OR queries

On 8/31/2011 9:35 AM, Tore Halvorsen wrote: Hi, I'm trying to optimize a query where I have two tables that both have a timestamp column. I want the result where either of the timestamps is after a specified time. In a reduced form, like this: CREATE TABLE a ( id serial NOT NULL PRIMARY KEY

Re: [GENERAL] FATAL: terminating connection due to conflict with recovery

On 08/30/11 18:03, Fujii Masao wrote: On Wed, Aug 31, 2011 at 5:51 AM, Jeff Ross wrote: Is there a setting in this or something else that I should tweak so this query can complete against the replica? Google turned up some threads on the error code associated with the error but I didn't find m

Re: [GENERAL] Index usage on OR queries

> > -- This can be optimized by using CTEs > with am as ( > select * from a where time >= '2011-08-15' > ) > , bm as ( > select * from b where time >= '2011-08-15' > ) > select * from am join bm using(id) > Disregard this, it doesn't to the same at all. Now I'm more confused as to how I can o

[GENERAL] Index usage on OR queries

Hi, I'm trying to optimize a query where I have two tables that both have a timestamp column. I want the result where either of the timestamps is after a specified time. In a reduced form, like this: CREATE TABLE a ( id serial NOT NULL PRIMARY KEY, time timestamp without time zone NOT NULL D

Re: [GENERAL] row is too big

Sim Zacks writes: > The manual doesn't say exactly how much storage a numeric type uses, but > it seems to me that it is taking up about 12 bytes per field. Does this > make any sense? Yeah, that would be the minimum size of a nonzero numeric value in 8.2. (More recent versions can pack them a

Re: [GENERAL] heavy swapping, not sure why

On Tue, Aug 30, 2011 at 10:05 PM, Scott Marlowe wrote: > On Tue, Aug 30, 2011 at 8:36 PM, mark wrote: >> To the broader list, regarding troubles with kswap. I am curious to what >> others seeing from /proc/zoneinfo for DMA pages (not dma32 or normal) - >> basically if it sits at 1 or not.  Settin

Re: [GENERAL] SELECT Query on DB table preventing inserts

On 31 Srpen 2011, 1:07, Dan Scott wrote: > On Tue, Aug 30, 2011 at 13:52, Daniel Verite > wrote: >>        Dan Scott wrote: >> >>> the insert process is unable to insert new rows into the database >> >> You should probably provide the error message on insert or otherwise >> describe >> how it's no

Re: [GENERAL] SELECT Query on DB table preventing inserts

On Tue, Aug 30, 2011 at 13:21, Scott Ribe wrote: > On Aug 30, 2011, at 8:22 AM, Dan Scott wrote: > >> Perhaps because I'm locking the table with my query? > > Do you mean you're explicitly locking the table? If so, why??? No, not explicitly. I just thought of it as a possible explanation. If read

Re: [GENERAL] SELECT Query on DB table preventing inserts

On Tue, Aug 30, 2011 at 13:52, Daniel Verite wrote: >        Dan Scott wrote: > >> the insert process is unable to insert new rows into the database > > You should probably provide the error message on insert or otherwise describe > how it's not working. Normally reading does not unintentionally p

Re: [GENERAL] ERD Tool

Adarsh Sharma, 31.08.2011 13:54: Dear all, Is there any open source ERD Tool for Postgresql Database. I find some paid tools but looking for free tools. Have a look at Power*Architect: http://www.sqlpower.ca/page/architect It's not perfect but it's quite OK. As it is a multi-DBMS tool it doe

[GENERAL] ERD Tool

Dear all, Is there any open source ERD Tool for Postgresql Database. I find some paid tools but looking for free tools. Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Parameterized prepared statements

Hi folks While replying to another query, something struck me as interesting that I thought I'd raise. People here often raise issues where preparing a parameterised query and executing the prepared query results in a plan that's sub-optimal for the particular values substituted into it. The

Re: [GENERAL] IDLE queries taking up space

On 31/08/2011 12:03 AM, JD Wong wrote: Hi, When I run select datname, procpid, current_query from pg_stat_activity; I get 26 rows of queries. How can I set postgres to qutomatically close connections that have finished their queries and now sit idle? If they're not idle in transaction, th

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

On ons, 2011-08-31 at 10:42 +0300, Peter Eisentraut wrote: > I don't have an answer for you, but this report looks suspiciously > similar to the one I posted the other day at > , > which, now that I think about it, also manifested i

Re: [GENERAL] regression between 8.4.8 and 8.4.2?

I don't have an answer for you, but this report looks suspiciously similar to the one I posted the other day at , which, now that I think about it, also manifested itself after the upgrade to 8.4.8. On tis, 2011-08-30 at 15:24 -070

[GENERAL] row is too big

select version() "PostgreSQL 8.2.19 on i686-pc-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2" Before you jump down my throat about bad design, this is a reporting table that is generated based on what the users want to see in their rows and columns. (I'm basically generating a sprea