Re: [GENERAL] SQL works but same function is confused

Bui, Michelle P wrote > #variable_conflict use_variable > DECLARE > v_status TEXT; > BEGIN > RETURN QUERY SELECT category, v_status as status, count (tool_id) AS > tool_count > FROM > (SELECT distinct category, tool_id, 'active' as v_status Seriously? Just pick a different alias for t

Re: [GENERAL] SQL works but same function is confused

Thanks Tom and Adrian - Here are my SQL and the function: SELECT category, v_status as status, count (tool_id) AS tool_count FROM (SELECT distinct category, tool_id, 'active' as v_status FROM tools WHERE time >= 123456 U

Re: [GENERAL] simple update query stuck

On Tue, Apr 01, 2014 at 07:00:16PM -0400, Tom Lane wrote: > one of the clients, in a way that isn't visible to the deadlock detector. > One way for that to happen without any external interconnections is if the > client is waiting for a NOTIFY that will never arrive because the would-be > sender i

Re: [GENERAL] SQL works but same function is confused

On 04/01/2014 06:44 PM, Bui, Michelle P wrote: v_status is not used inthe SQL statement but is used in the function because we need to return a table (catgory, tool_count, status). Even when I did not use v_status, and assign value 'valid' or 'invalid' to the output parameter directly, the sam

Re: [GENERAL] SQL works but same function is confused

"Bui, Michelle P" writes: > I have this query that when executed as a SQL statement, it works perfect! OK ... > I used <> before Declare section and try using block.v_status but > this is not allowed (has syntax error). That should work (in the sense of not getting a syntax error), but since yo

Re: [GENERAL] SQL works but same function is confused

v_status is not used inthe SQL statement but is used in the function because we need to return a table (catgory, tool_count, status). Even when I did not use v_status, and assign value 'valid' or 'invalid' to the output parameter directly, the same error occurred. Thanks, Michelle Can we assi

Re: [GENERAL] SQL works but same function is confused

On 04/01/2014 05:10 PM, Bui, Michelle P wrote: Hi all, I have this query that when executed as a SQL statement, it works perfect! The table tools contains many records in a time series, with attributes like category but without the field status. I assign the value of status ’active’ or ‘inactive

Re: [GENERAL] SQL works but same function is confused

On 04/01/2014 06:10 PM, Bui, Michelle P wrote: Hi all, I have this query that when executed as a SQL statement, it works perfect! The table tools contains many records in a time series, with attributes like category but without the field status. I assign the value of status 'active' or 'inac

Re: [GENERAL] SQL works but same function is confused

/CREATE OR REPLACE FUNCTION get_status/ /RETURNS TABLE (category SMALLINT, status TEXT, tool_count BIGINT) AS/ /$BODY$/ // #variable_conflict use_variable /DECLARE/ /change this to "s_status TEXT;"/ I think it's conflicting with the column alias of same // /v_status TEXT;/ /BEGIN/ /

[GENERAL] SQL works but same function is confused

Hi all, I have this query that when executed as a SQL statement, it works perfect! The table tools contains many records in a time series, with attributes like category but without the field status. I assign the value of status 'active' or 'inactive' depending on whether the tool record exists

Re: [GENERAL] Modifying Postgresql Optimizer to select optimal memory

Sayan Biswas writes: > Hello everyone, > We are currently working on a DBMS course project that requires us to > modify postgresql optimizer. We need to vary work_mem within a range and > note down the cost at those points(allocated memory) after 'explain' > operation and then select the optimal t

Re: [GENERAL] simple update query stuck

Andrew Sullivan writes: > Probably you could have killed one of the queries. But it sounds like > what's happening is that you have multiple queries that are all trying > to update the same rows in a different order. It may be that none of > these is strictly deadlocked, in that no query is wait

Re: [GENERAL] Postgres as In-Memory Database?

Hi Yeb Thanks for the pointers. Of course disk access is not obsolete: As I said, I suppose changes are streamed to disk. When I mentioned "no disk access" I meant the indices of RDBMS which designed to handle disk access - which seems to me different in in-memory dabases. The paper referred by

Re: [GENERAL] simple update query stuck

Andrew Sullivan-8 wrote > On Tue, Apr 01, 2014 at 01:37:17PM -0700, Si Chen wrote: >> You are right. That was the problem. I tried the query from >> http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT >> transaction that was blocking it. >> >> I restarted postgresql again, and (it

Re: [GENERAL] SQL Question

On 4/1/2014 1:27 PM, Robert DiFalco wrote: Heh, scratch that, the EXISTS query DOES work. Is this the most efficient way to perform this kind of query? Thanks! I would try and express that as a left outer join, and use (c2.owner_id IS NOT NULL) as your boolean field (or something like that)

Re: [GENERAL] simple update query stuck

On Tue, Apr 01, 2014 at 01:37:17PM -0700, Si Chen wrote: > You are right. That was the problem. I tried the query from > http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT > transaction that was blocking it. > > I restarted postgresql again, and (it seems) everything went back to

Re: [GENERAL] SQL Question

> Is this the most efficient way to perform this kind of query? I don't think there is one answer that's always correct, but you could compare it with a LEFT OUTER JOIN. There are lots of articles and blog posts about EXISTS vs OUTER JOIN vs IN, for all the major RDBMSes. Note that not all these

Re: [GENERAL] simple update query stuck

You are right. That was the problem. I tried the query from http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT transaction that was blocking it. I restarted postgresql again, and (it seems) everything went back to normal. Was there another way to unlock the table then? On Tue,

Re: [GENERAL] SQL Question

Heh, scratch that, the EXISTS query DOES work. Is this the most efficient way to perform this kind of query? Thanks! On Tue, Apr 1, 2014 at 1:21 PM, Robert DiFalco wrote: > I have two queries I would like to combine into one. > > I have a table that represents a user's contacts. It has fields li

[GENERAL] Modifying Postgresql Optimizer to select optimal memory

Hello everyone, We are currently working on a DBMS course project that requires us to modify postgresql optimizer. We need to vary work_mem within a range and note down the cost at those points(allocated memory) after 'explain' operation and then select the optimal tradeoff. We were changing "post

[GENERAL] SQL Question

I have two queries I would like to combine into one. I have a table that represents a user's contacts. It has fields like "id, owner_id, user_id". Owner ID cannot be null but user_id can be null. They are numeric field, the ID is just generated. I want a query to retrieve all of a user's contacts

Re: [GENERAL] simple update query stuck

Thanks for writing back, but I don't think so. There's no message of a deadlock in the log, and the first query started at 12:25, the next one 12:31, 12:39, 12:50, 12:54, so there's plenty of time in between. On Tue, Apr 1, 2014 at 1:01 PM, Hoover, Jeffrey wrote: > Could they both be trying to

Re: [GENERAL] simple update query stuck

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Si Chen Sent: Tuesday, April 01, 2014 3:51 PM To: pgsql-general@postgresql.org Subject: [GENERAL] simple update query stuck Hello, I'm using postgresql 9.0.13, and I have a simple query that seems

Re: [GENERAL] simple update query stuck

Do these queries update more than one row? I ran into a similar issue a year ago, where two multi-row updates would deadlock because they processed rows in a different order. I'd love to see UPDATE support ORDER BY to fix this, but it doesn't yet. (If I ever try contributing to Postgres, this is a

Re: [GENERAL] Postgres as In-Memory Database?

On 2014-04-01 04:20, Jeff Janes wrote: On Sunday, March 30, 2014, Stefan Keller > wrote: Hi Jeff 2013/11/20 Jeff Janes > I don't know what you mean about enhancements in the buffer pool. For an in-memory database, there shouldn't be a buffer

[GENERAL] simple update query stuck

Hello, I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck. I did a postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity; procpid | query_start | waiting | current_query 32605 | 2014-04-01 12:39:

Re: [GENERAL] Alternative to Multi-Master Replication with 2 Data centers??

Paul, I will read through Chapter 8, sounds like it'll be tremendously helpful. For the second piece here let me be more specific if I may. We currently have 1 DB and 1 Web server in each DC. The load balancer could detect 500 errors on the behalf of either DC so if a DB server went down t

Re: [GENERAL] RHEL 7 and Postgres 9.3.4

On Tue, Apr 1, 2014 at 7:58 PM, Devrim Gündüz wrote: > Hi, > > It works fine. We already branched RHEL 7 RPMs. I am using them on my > testing instance. > > Regards, > Hi Devrim, Thanks for the quick update ! Regards...

Re: [GENERAL] unexpected unnest behaviour

James Harper writes: > I have a query that blows the arguments in pg_proc out: > SELECT pg_proc.oid, UNNEST(pg_proc.proargnames), UNNEST(pg_proc.proargtypes), > UNNEST(pg_proc.proargmodes) FROM pg_proc > And that works great if all arguments are input arguments, but if two are > output argument

Re: [GENERAL] RHEL 7 and Postgres 9.3.4

Hi, It works fine. We already branched RHEL 7 RPMs. I am using them on my testing instance. Regards, On April 1, 2014 5:21:25 PM EEST, Dev Kumkar wrote: >Hello, > >RHEL 7 will be in market by June 2014. >I just wanted to check here RHEL 7 and Postgres 9.3.4 compatibility? > >Regards... -- Se

[GENERAL] RHEL 7 and Postgres 9.3.4

Hello, RHEL 7 will be in market by June 2014. I just wanted to check here RHEL 7 and Postgres 9.3.4 compatibility? Regards...

Re: [GENERAL] Unattended Installation

> -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of shetty65 > Sent: Tuesday, April 01, 2014 3:13 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Unattended Installation > > Hello > > I am using Postgres 9.

Re: [GENERAL] Postgres as In-Memory Database?

Hi Jeff I agree with most of your statements. 2014-04-01 4:20 GMT+02:00 Jeff Janes : > On Sunday, March 30, 2014, Stefan Keller wrote: > >> Hi Jeff >> >> >> 2013/11/20 Jeff Janes >> >>> >>> I don't know what you mean about enhancements in the buffer pool. For >>> an in-memory database, there

[GENERAL] unexpected unnest behaviour

I have a query that blows the arguments in pg_proc out: SELECT pg_proc.oid, UNNEST(pg_proc.proargnames), UNNEST(pg_proc.proargtypes), UNNEST(pg_proc.proargmodes) FROM pg_proc And that works great if all arguments are input arguments, but if two are output arguments, then something unexpected ha

[GENERAL] Unattended Installation

Hello I am using Postgres 9.3.3.1 on Windows (32-Bit Windows 7 Professional). I use the installer executable postgresql-9.3.3-1-windows.exe with the option "--optionfile " The option file has the following content (the ${..} are replaced with correct values before execution): #mode=unattended d