Re: [GENERAL] function to send email with query results

On 4/17/2015 10:30 PM, Suresh Raja wrote: I'm looking to write a function to send email with result of a query. Is it possible to send email with in a function. Any help is appreciated. I would do that in an application, not a pl sql function. make a query, fetch the results, forma

Re: [GENERAL] ORDER BY for jsonb

Thanks for the help. So in this case, the performance of ORDER BY will not be affected at all by whether an index is created on the jsonb "setting" field? Pai-Hung -Original Message- From: "Jim Nasby" Sent: ‎4/‎17/‎2015 8:59 PM To: "Pai-Hung Chen" ; "pgsql-general@postgresql.org" Subj

Re: [GENERAL] [SQL] function to send email with query results

On Friday, April 17, 2015, Suresh Raja wrote: > Hi all: > > I'm looking to write a function to send email with result of a query. > Is it possible to send email with in a function. Any help is appreciated. > > Yes...though neither the neither the sql nor the plpgsql languages have the necessary

Re: [GENERAL] "Cast" SRF returning record to a table type?

On Friday, April 17, 2015, Jim Nasby wrote: > On 4/17/15 7:39 PM, David G. Johnston wrote: > >> On Friday, April 17, 2015, Jim Nasby > > wrote: >> >> I'm working on a function that will return a set of test data, for >> unit testing database stuff. It does

[GENERAL] function to send email with query results

Hi all: I'm looking to write a function to send email with result of a query.Is it possible to send email with in a function. Any help is appreciated. Thanks, -Suresh Raja

Re: [GENERAL] "Cast" SRF returning record to a table type?

On 4/17/15 7:39 PM, David G. Johnston wrote: On Friday, April 17, 2015, Jim Nasby mailto:jim.na...@bluetreble.com>> wrote: I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately returns SETOF record that's

Re: [GENERAL] ORDER BY for jsonb

On 4/17/15 9:53 PM, Pai-Hung Chen wrote: Hi, I am new to PostgreSQL and have a question about the new jsonb type in 9.4. Suppose I have a table called "user" that has two columns: (1) "user_id" of type text, also the primary key, (2) "setting" of type jsonb. With the following query pattern: SE

[GENERAL] ORDER BY for jsonb

Hi, I am new to PostgreSQL and have a question about the new jsonb type in 9.4. Suppose I have a table called "user" that has two columns: (1) "user_id" of type text, also the primary key, (2) "setting" of type jsonb. With the following query pattern: SELECT * FROM user WHERE user_id IN [...] ORD

Re: [GENERAL] Waiting on ExclusiveLock on extension

On 4/17/15 4:22 PM, Andomar wrote: Yes, but did you have the same workload when you upgraded to 9.3 as you do today? The workload is very similar. We upgraded from 9.1 to 9.3 only two months ago, and our usage statistics have not changed much. There were no "remaining connection slots are res

Re: [GENERAL] "Cast" SRF returning record to a table type?

On Friday, April 17, 2015, Jim Nasby wrote: > I'm working on a function that will return a set of test data, for unit > testing database stuff. It does a few things, but ultimately returns SETOF > record that's essentially: > > RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name; > > Because it's

[GENERAL] "Cast" SRF returning record to a table type?

I'm working on a function that will return a set of test data, for unit testing database stuff. It does a few things, but ultimately returns SETOF record that's essentially: RETURN QUERY EXECUTE 'SELECT * FROM ' || table_name; Because it's always going to return a real relation, I'd like to be

Re: [GENERAL] Running pg_upgrade under Debian

On 04/17/2015 03:09 PM, rob stone wrote: Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /h

Re: [GENERAL] Running pg_upgrade under Debian

On Sat, 2015-04-18 at 00:25 +0200, Karsten Hilbert wrote: > On Sat, Apr 18, 2015 at 08:09:43AM +1000, rob stone wrote: > > > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this > > error:- > > > > > > postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade > > -b /us

Re: [GENERAL] Running pg_upgrade under Debian

On Sat, Apr 18, 2015 at 08:09:43AM +1000, rob stone wrote: > I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this > error:- > > > postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade > -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin > -d /home/postgres/d

Re: [GENERAL] Help with slow table update

> > [skipped] > > >> But remember that if you update or delete a row, removing it from an >>> index, the data will stay in that index until vacuum comes along. >>> >>> Also, there's no point in doing a REINDEX after a VACUUM FULL; >>> vacuum full rebuilds all the indexes for you. >

[GENERAL] Running pg_upgrade under Debian

Hello, I'm trying to upgrade from 9.3 to 9.4 on my laptop and encountering this error:- postgres@roblaptop:/usr/lib/postgresql/9.4/bin$ ./pg_upgrade -b /usr/lib/postgresql/9.3/bin -B /usr/lib/postgresql/9.4/bin -d /home/postgres/data93/userqueries -D /home/postgres/data94/userqueries -U pguserqu

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

På fredag 17. april 2015 kl. 21:11:05, skrev Jim Nasby mailto:jim.na...@bluetreble.com>>: On 4/15/15 9:22 AM, Andreas Joseph Krogh wrote: > På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper > mailto:a...@adamhooper.com>>: > >     On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh > 

Re: [GENERAL] Waiting on ExclusiveLock on extension

Yes, but did you have the same workload when you upgraded to 9.3 as you do today? The workload is very similar. We upgraded from 9.1 to 9.3 only two months ago, and our usage statistics have not changed much. There were no "remaining connection slots are reserved for non-replication superus

Re: [GENERAL] PL\pgSQL 'ERROR: invalid input syntax for type oid:' [PostgreSQL 9.3.6 and 9.4]

Thanks Adrian! Changing the declaration row_data to be of type RECORD (rather than pg_catalog.pg_class%ROWTYPE) resolved the error :) - Will *Will J Dunn* *willjdunn.com * On Thu, Apr 16, 2015 at 4:36 PM, Adrian Klaver wrote: > On 04/16/2015 07:52 AM, William Dunn wrote

Re: [GENERAL] On using doubles as primary keys

On Apr 17, 2015 8:35 AM, "Kynn Jones" wrote: > (The only reason for wanting to transfer this data to a Pg table > is the hope that it will be easier to work with it by using SQL 800 million 8-byte numbers doesn't seem totally unreasonable for python/R/Matlab, if you have a lot of memory. Are you

Re: [GENERAL] On using doubles as primary keys

On Fri, Apr 17, 2015 at 10:34 AM, Kynn Jones wrote: > I have some data in the form of a matrix of doubles (~2 million > rows, ~400 columns) that I would like to store in a Pg table, > along with the associated table of metadata (same number of rows, > ~30 columns, almost all text). This is large

Re: [GENERAL] How to keep pg_largeobject from growing endlessly

On 4/15/15 9:22 AM, Andreas Joseph Krogh wrote: På onsdag 15. april 2015 kl. 16:05:22, skrev Adam Hooper mailto:a...@adamhooper.com>>: On Wed, Apr 15, 2015 at 9:57 AM, Andreas Joseph Krogh wrote: > > På onsdag 15. april 2015 kl. 15:50:36, skrev Adam Hooper : > >

Re: [GENERAL] Waiting on ExclusiveLock on extension

On 4/16/15 4:39 PM, Andomar wrote: Thanks for your reply. This issue has been complained several times, and here is the most recent one: http://www.postgresql.org/message-id/0ddfb621-7282-4a2b-8879-a47f7cecb...@simply.name That post is about a server with huge shared_buffers, but ours is just

Re: [GENERAL] On using doubles as primary keys

On 4/17/15 1:10 PM, Ray Cote wrote: (Not an IEEE floating point expert, but...) I've learned the hard way to never rely on comparing two floating point numbers for equality -- and that's what you are doing if you join on them as primary keys. If you must use the underlying numeric data for join

Re: [GENERAL] On using doubles as primary keys

On Fri, Apr 17, 2015 at 11:56 AM, David G. Johnston < david.g.johns...@gmail.com> wrote: > ​ > MD>> > ​ > I'm not sure what you mean by doubles. Do you mean bigint data type, or do > you mean use two columns for a primary key? Either way it's pretty simple. > ​ > MD>> > ​ > If you mean a bigint, t

Re: [GENERAL] Waiting on ExclusiveLock on extension

On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund wrote: > Hm. I'm not aware of related changes in 9.4? 9.5 should be a bit better, > but I don't think 9.4 will make much of a difference. > You are right. I mis-read the check-in log. > I don't really agree that that's the most important bit. See > h

Re: [GENERAL] On using doubles as primary keys

On Fri, Apr 17, 2015 at 8:45 AM, Melvin Davidson wrote: > ​ > On Fri, Apr 17, 2015 at 11:34 AM, Kynn Jones wrote: > >> >> One consideration that is complication the choice of primary key >> is wanting to have the ability to store chunks of the data >> table (not the metadata table), including th

Re: [GENERAL] Waiting on ExclusiveLock on extension

On Fri, Apr 17, 2015 at 1:14 AM, Andres Freund wrote: > On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > > On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > > > > b) How can you find the name of the relation being extended? based on > the > > > relation number. > > select ::regclass; > > Tha

Re: [GENERAL] On using doubles as primary keys

First, please ALWAYS include the version and O/S, even with basic questions. I'm not sure what you mean by doubles. Do you mean bigint data type, or do you mean use two columns for a primary key? Either way it's pretty simple. If you mean a bigint, then probably best to use serial data type, which

[GENERAL] On using doubles as primary keys

I have some data in the form of a matrix of doubles (~2 million rows, ~400 columns) that I would like to store in a Pg table, along with the associated table of metadata (same number of rows, ~30 columns, almost all text). This is large enough to make working with it from flat files unwieldy. (Th

Re: [GENERAL] database migration question between different ubuntus and different postgresql server versions

On 04/16/2015 05:52 PM, Octavi Fors wrote: Hi Adrian, I didn't received any answer from Andrews. Yes, sorry I didn't describe completely my migration plan. Right now the database 'db' is in NAS1 mounted via nfs with computer 1 (running ubuntu 12.04 postgresql 9.2). I want to migrate 'db' to a f

Re: [GENERAL] fillfactor and cluster table vs ZFS copy-on-write

On Fri, Apr 17, 2015 at 5:24 AM, Albe Laurenz wrote: > >> Even with COW, I can see fillfactor < 100% still have its virtues. For > >> example, HOT update can avoid adding an extra index item on the index > >> page if it finds the new item can be inserted in the same heap page. > > > That's true,

Re: [GENERAL] fillfactor and cluster table vs ZFS copy-on-write

Geoff Speicher wrote: > On Thu, Apr 16, 2015 at 4:56 PM, Qingqing Zhou > wrote: >> On Thu, Apr 16, 2015 at 5:09 AM, Geoff Speicher >> wrote: >>> ZFS implements copy-on-write, so when PostgreSQL modifies a block on disk, >>> the filesystem writes a new block rather than updating the existing blo

Re: [GENERAL] Waiting on ExclusiveLock on extension

On 2015-04-16 14:23:25 -0700, Qingqing Zhou wrote: > On Thu, Apr 16, 2015 at 1:24 PM, Andomar wrote: > > After upgrading our database from 9.3.5 to 9.4.1 last night, the server > > suffers from high CPU spikes. During these spikes, there are a lot of these > > messages in the logs: > > > > pro

Re: [GENERAL] Waiting on ExclusiveLock on extension

Are you able to take some 'perf top' during high CPU spike and see what's burning CPU there? Though the issue is related to blocking, but high CPU spikes may hint some spinning to acquire behavior. Will do, although hopefully the spikes were only growing pains after the upgrade. If your prev