Re: [GENERAL] table design and data type choice

On Wed, Jan 8, 2014 at 3:11 PM, Jayadevan M wrote: > We have a table to record the voteup/votedown by users of questions and > answers (like on stackoverflow). So there will be a large number of inserts > (voteup/down), some updates(user changes mind)and may be a few deletes. The > queries will be

Re: [GENERAL] Sudden slow down and spike in system CPU causes max_connections to get exhausted

On Tue, Jan 7, 2014 at 9:06 AM, Anand Kumar, Karthik < karthik.anandku...@classmates.com> wrote: > We do typically have a lot of idle connections (1500 connections total, > over a 1000 idle at any given time). We're in the midst of installing > pgbouncer to try and mitigate the problem, but that s

Re: [GENERAL] How to know server status variable in postgresql?

On Tue, Jan 7, 2014 at 8:33 PM, ambilalmca wrote: > Hai, I am developing a java application for performance counter. For that i > want to collect all server status counter names with current value. i just > did it for MySQl by, *"SHOW GLOBAL STATUS"*. > What details do you want to collect? That

[GENERAL] returning json object with subset of keys

Hi I'm using the json functionalities of postgresql 9.3. I have a query calling json_populate_recordset like this: json_populate_recordset(null::product, event->'products') but it returns an error: ERROR: cannot call json_populate_recordset on a nested object There is indeed one key in event->

Re: [GENERAL] returning json object with subset of keys

On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin wrote: > Hi > > I'm using the json functionalities of postgresql 9.3. > I have a query calling json_populate_recordset like this: > json_populate_recordset(null::product, event->'products') > but it returns an error: > ERROR: cannot call json_popu

Re: [GENERAL] Consistent file-level backup of pg data directory

On 2014-01-07 21:54, Jeff Janes wrote: > On Tue, Jan 7, 2014 at 10:58 AM, > wrote: > - keep a copy "backup_label" under a different name [...[ > Why under a different name? That sounds dangerous to me. ... otherwise it would be useless, because "pg_stop_backup" will

[GENERAL] Last inserted row id with complex PK

I have a projects log table with a three column PK, project_num, person_num, and sequence, where each new entry for a project/person combination increments the sequence, which is not an auto incrementing sequence. Is there any way to retrieve the last entry to the table? For instance, if the last e

Re: [GENERAL] Last inserted row id with complex PK

Hi Nelson: On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green wrote: > I have a projects log table with a three column PK, project_num, person_num, > and sequence, where each new entry for a project/person combination > increments the sequence, which is not an auto incrementing sequence. Is > there any

Re: [GENERAL] Last inserted row id with complex PK

Francisco Olarte writes: > Hi Nelson: > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green wrote: >> I have a projects log table with a three column PK, project_num, person_num, >> and sequence, where each new entry for a project/person combination >> increments the sequence, which is not an auto incre

Re: [GENERAL] How to know server status variable in postgresql?

I want to collect, *Connections. * Current connections. The number of currently open connections. Connections executing requests. The number of currently open connections that are executing requests. Idle connections. The number of currently idle connections. Max connections. The maximum number o

Re: [GENERAL] Planning error in dynamic string creation in plpgsql

Just wanted to say thanks again for the help to those that responded. For anyone curious, this helped me get a more advanced constraint exclusion feature finished for the partition manager I've been working on http://www.keithf4.com/managing-constraint-exclusion-in-table-partitioning/ -- Keith Fi

[GENERAL] argument of CASE/WHEN must not return a set

Good morning, I've have solved my problem in another way, but I am curious as to why I am getting the following error. The following returns a boolean value a expected: development=# SELECT LENGTH(ARRAY_TO_STRING( REGEXP_MATCHES('12-70510','^[0-9,0-9.0-9]+') development(#

Re: [GENERAL] Last inserted row id with complex PK

On Wed, Jan 8, 2014 at 10:09 AM, Francisco Olarte wrote: > Hi Nelson: > > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green > wrote: > > I have a projects log table with a three column PK, project_num, > person_num, > > and sequence, where each new entry for a project/person combination > > increments

Re: [GENERAL] Last inserted row id with complex PK

On Wed, Jan 8, 2014 at 10:22 AM, Tom Lane wrote: > Francisco Olarte writes: > > Hi Nelson: > > On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green > wrote: > >> I have a projects log table with a three column PK, project_num, > person_num, > >> and sequence, where each new entry for a project/person c

Re: [GENERAL] Consistent file-level backup of pg data directory

On Wed, Jan 8, 2014 at 7:09 AM, wrote: > On 2014-01-07 21:54, Jeff Janes wrote: > > On Tue, Jan 7, 2014 at 10:58 AM, > > wrote: > > - keep a copy "backup_label" under a different name > [...[ > > Why under a different name? That sounds dangerous to me. > ... otherw

Re: [GENERAL] Last inserted row id with complex PK

Nelson Green wrote > My apologies, I was not completely clear. I will not know any of the > columns in advance. The most recent insert is the result of user input > from > a web form, so I won't know what project or what user generated the last > insert. That was why I wandered if that information

Re: [GENERAL] Last inserted row id with complex PK

On Wed, Jan 8, 2014 at 1:24 PM, David Johnston wrote: > Nelson Green wrote > > My apologies, I was not completely clear. I will not know any of the > > columns in advance. The most recent insert is the result of user input > > from > > a web form, so I won't know what project or what user generat

[GENERAL] general questions

Hi All; I recently ran into the following, any thoughts? Thanks in advance... 1) \d and schema's - I setup 2 schema's (sch_a and sch_b) - I added both schema's to my search_path - I created 2 tables: sch_a.test_tab and sch_b.test_tab If I do a \d with no parameters I only see the first test_t

Re: [GENERAL] general questions

CS DBA writes: > 1) \d and schema's > - I setup 2 schema's (sch_a and sch_b) > - I added both schema's to my search_path > - I created 2 tables: sch_a.test_tab and sch_b.test_tab > If I do a \d with no parameters I only see the first test_tab table > based on the order of my search_path. > I ge

Re: [GENERAL] Last inserted row id with complex PK

On 08 Jan 2014, at 16:54, Nelson Green wrote: > I have a projects log table with a three column PK, project_num, person_num, > and sequence, where each new entry for a project/person combination > increments the sequence, which is not an auto incrementing sequence. Is there > any way to retrie

Re: [GENERAL] general questions

On Thu, Jan 9, 2014 at 5:04 AM, Tom Lane wrote: > CS DBA writes: > > 1) \d and schema's > > - I setup 2 schema's (sch_a and sch_b) > > - I added both schema's to my search_path > > - I created 2 tables: sch_a.test_tab and sch_b.test_tab > > > If I do a \d with no parameters I only see the first

Re: [GENERAL] Last inserted row id with complex PK

On Wed, Jan 8, 2014 at 11:54 PM, Nelson Green wrote: > I have a projects log table with a three column PK, > project_num, person_num, and sequence, where each new entry for a > project/person combination increments the sequence, which is not an auto > incrementing sequence. Is there any way to ret

Re: [GENERAL] How to know server status variable in postgresql?

On Wed, Jan 8, 2014 at 7:22 PM, ambilalmca wrote: > I want to collect, > > *Connections. * > > Current connections. The number of currently open connections. > Connections executing requests. The number of currently open connections > that are executing requests. > Idle connections. The number of

Re: [GENERAL] Last inserted row id with complex PK

On 1/8/2014 10:14 AM, Nelson Green wrote: On Wed, Jan 8, 2014 at 4:54 PM, Nelson Green mailto:nelsongree...@gmail.com>> wrote: > I have a projects log table with a three column PK, project_num, person_num, > and sequence, where each new entry for a project/person combination

Re: [GENERAL] returning json object with subset of keys

On Wed, Jan 8, 2014 at 4:05 PM, Merlin Moncure wrote: > On Wed, Jan 8, 2014 at 6:37 AM, Raphael Bauduin wrote: > > Hi > > > > I'm using the json functionalities of postgresql 9.3. > > I have a query calling json_populate_recordset like this: > > json_populate_recordset(null::product, event->'p