Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 2013-02-06, Bèrto ëd Sèra wrote: > Hi > >> You've hidden nothing from INSERT-RETURNING. > > ?? Or from a select, if the final value is what you mean. What we hide > is the way values are made, clearly not the final value. That bit is > accessible to anyone who can select the table, obviously. >

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wed, Feb 6, 2013 at 8:01 PM, Jasen Betts wrote: > On 2013-02-06, Bèrto ëd Sèra wrote: >> Hi >> >>> You've hidden nothing from INSERT-RETURNING. >> >> ?? Or from a select, if the final value is what you mean. What we hide >> is the way values are made, clearly not the final value. That bit is >

Re: [GENERAL] ERROR: invalid input syntax for integer: ""

Thanks Tom, On 2013-02-06, at 13:42 , Tom Lane wrote: > The only part of this query that looks like it could possibly produce > that error is the res8.resultvalue-to-int cast: >> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108 >> AND res8.del = false >> LEFT JOIN r

[GENERAL] "explain analyze" a procedure verbosely - to find which statement in it takes longer

Hello, I've read in the docs, that every table should better have primary key and so I've rearranged my 8.4.13 database: added primary keys to each table (some of the primary keys are pairs of columns) and dropped all other indices. And I've probably dropped few indices too many, because a stored

Re: [GENERAL] "explain analyze" a procedure verbosely - to find which statement in it takes longer

Hello 2013/2/6 Alexander Farber : > Hello, > > I've read in the docs, that every table should > better have primary key and so I've rearranged > my 8.4.13 database: added primary keys to > each table (some of the primary keys are > pairs of columns) and dropped all other indices. > > And I've pro

Re: [GENERAL] "explain analyze" a procedure verbosely - to find which statement in it takes longer

On 6 February 2013 11:03, Alexander Farber wrote: > begin > > insert into pref_ban2 select > id, > first_name, > last_name, > city, > last_ip >

[GENERAL] Need help understanding WAL and checkpoints

Hi there I'm trying to get my head around WAL and checkpoints and need to ask a couple of questions before I get a headache. Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all over the place - are these the same thing (i.e. files in the pg_xlog directory)? I'm a bit confu

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Hi > I still don't see how that's any better than a stored procedure that > directly does the INSERT. You can conceal the code every bit as > easily. Guys I DO NOT write the customers' security guidelines. I get asked to produce a design in which "party X will make plain INSERTs and ignore the ve

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wed, Feb 6, 2013 at 10:36 PM, Bèrto ëd Sèra wrote: > Hi > >> I still don't see how that's any better than a stored procedure that >> directly does the INSERT. You can conceal the code every bit as >> easily. > > Guys I DO NOT write the customers' security guidelines. I get asked to > produce a

Re: [GENERAL] Need help understanding WAL and checkpoints

drew_hunt wrote: > I'm trying to get my head around WAL and checkpoints and need to ask a couple > of questions before I > get a headache. > > Firstly, I see the terms "WAL log", "WAL file" and "transaction log" all over > the place - are these > the same thing (i.e. files in the pg_xlog directo

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On 6 February 2013 12:56, Chris Angelico wrote: > If you get into a taxi and ask > to be driven to New Zealand within the hour, no amount of begging will > get you what you want. > ...Unless you get into a taxi in New Zealand. -- If you can't see the forest for the trees, Cut the trees and you

Re: [GENERAL] Passing dynamic parameters to a table-returning function

Perfect, that is exactly what I needed. Thanks David! On Mon, Feb 4, 2013 at 6:49 PM, David Johnston wrote: > Moshe Jacobson wrote > > I'm > > unsure of the syntax for passing in values from columns in the database > as > > the parameters of a set-returning function from which I want to select

Re: [GENERAL] ERROR: invalid input syntax for integer: ""

On 02/06/2013 01:28 AM, Ben Madin wrote: Thanks Tom, On 2013-02-06, at 13:42 , Tom Lane wrote: The only part of this query that looks like it could possibly produce that error is the res8.resultvalue-to-int cast: LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = 108

Re: [GENERAL] ERROR: invalid input syntax for integer: ""

Ben Madin writes: > On 2013-02-06, at 13:42 , Tom Lane wrote: >> The only part of this query that looks like it could possibly produce >> that error is the res8.resultvalue-to-int cast: >>> LEFT JOIN results res8 ON res8.reportid = rep.id AND res8.resulttypeid = >>> 108 AND res8.del = false >>>

Re: [GENERAL] best config

On Feb 6, 2013, at 8:14 AM, Roberto Scattini wrote: > hi list, > > we have two new dell poweredge r720. based on recommendations from this list > we have configued the five disks in raid10 + 1 hot spare. You might mention a bit more about how your drives are configured. 5 drives in a RAID1+

Re: [GENERAL] best config

On Feb 6, 2013, at 9:55 AM, Roberto Scattini wrote: > > hi steven, > > > we have two new dell poweredge r720. based on recommendations from this > > list we have configued the five disks in raid10 + 1 hot spare. > > You might mention a bit more about how your drives are configured. 5 drives

[GENERAL] configuring timezone

Timezone configuration parameter (defaulting to system timezone) worked fine for us before upgrading from 8.4. to 9.2. Now we've got a problem. 9.2 Release Notes says: * Identify the server time zone during initdb, and set postgresql.conf entries timezone

[GENERAL] Keeping historical record changes w/ triggers - best practices?

I have a wide-ish table with 60 columns. I want to make a copy of data whenever a record is updated or deleted. Right now I have a table that's almost identical but with a 'created' column (timestamp) and an 'action' column (which gets TG_OP for UPDATE or DELETE). My idea would be to sort on the

Re: [GENERAL] configuring timezone

See the archived thread here: http://www.postgresql.org/message-id/CAEghcWD8DXjroBYCZsdGrx+cHTCbCbW9es2uQ+o7a8NZ61JT=q...@mail.gmail.com Short version: Sorry, but you're going to need to recompile if you want that behavior. Here's a diff applied against 9.2.1 http://pastebin.com/5AyaX2RF. I've dep

Re: [GENERAL] configuring timezone

Terence, Thanks for quick reply, I read your thread (Dec, 2012) before posting my question. But, recompile is not an option for me. Was hoping, that something regarding this issue changed since... Igor Neyman From: Terence Ferraro [mailto:terencejferr...@gmail.com] Sent: Wednesday, February 0

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver wrote: > I have a wide-ish table with 60 columns. I want to make a copy of data > whenever a record is updated or deleted. > > Right now I have a table that's almost identical but with a 'created' column > (timestamp) and an 'action' column (which gets

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

I don't mean to hog my own thread, but the more I look at the hstore type, the more reasonable it seems. The table is just a serial, a timestamp, and two columns 'old' and 'new'. The trigger function inserts these values using hstore(OLD) and hstore(NEW). Then, you can select old, new, and new - o

Re: [GENERAL] DEFERRABLE NOT NULL constraint

On Wednesday, February 6, 2013 at 23:31, 00jkxma...@sneakemail.com (Alban Hertroys haramrae-at-gmail.com |pg-gts/Basic|) wrote: On 6 February 2013 12:56, Chris Angelico wrote: If you get into a taxi and ask to be driven to New Zealand within the hour, no amount of begging will get you what

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

On Wed, Feb 6, 2013 at 11:19 AM, Wells Oliver wrote: > I don't mean to hog my own thread, but the more I look at the hstore type, > the more reasonable it seems. The table is just a serial, a timestamp, and > two columns 'old' and 'new'. The trigger function inserts these values using > hstore(OLD

Re: [GENERAL] configuring timezone

Sorry, but from what I understand the change is permanent. If recompile is not an option but you're on Windows let me know; I do have binaries available.. On Wed, Feb 6, 2013 at 2:05 PM, Igor Neyman wrote: > Terence, > > ** ** > > Thanks for quick reply, I read your thread (Dec, 2012) befor

Re: [GENERAL] configuring timezone

I am on Windows (both 32 and 64 bit) using 32-bit Postgres. So, your binaries are for 9.2.1, you aren't planning to go to 9.2.2? From: Terence Ferraro [mailto:terencejferr...@gmail.com] Sent: Wednesday, February 06, 2013 3:07 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENE

Re: [GENERAL] configuring timezone

On 02/06/2013 10:32 AM, Igor Neyman wrote: Timezone configuration parameter (defaulting to system timezone) worked fine for us before upgrading from 8.4. to 9.2. Now we’ve got a problem. 9.2 Release Notes says: · Identify the server time zone during initdb, and set postgresql.conf entries ti

Re: [GENERAL] configuring timezone

> -Original Message- > From: Adrian Klaver [mailto:adrian.kla...@gmail.com] > Sent: Wednesday, February 06, 2013 4:40 PM > To: Igor Neyman > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] configuring timezone > > On 02/06/2013 10:32 AM, Igor Neyman wrote: > > Timezone configura

Re: [GENERAL] configuring timezone

On 02/06/2013 01:47 PM, Igor Neyman wrote: > >> >> -- >> Adrian Klaver >> adrian.kla...@gmail.com > > Sometimes, but not always. I guess you could ship a script that sets the timezone when the server is installed. > > Going back to the reason for this change in Release Notes: > > "This avoi

Re: [GENERAL] configuring timezone

Igor Neyman writes: > Going back to the reason for this change in Release Notes: > "This avoids expensive time zone probes during server start." > How expensive? The time zone probe logic involves reading every file under /usr/share/zoneinfo (or wherever you have the Olson tz database installed).

Re: [GENERAL] configuring timezone

9.2.1 was the version standard when I was building and deploying...so no, I probably will not (personally) be updating anytime soon... However, if you're interested, I'll see if I can find a place tonight or tomorrow to put these binaries (they are 32-bit as well), source, etc (sourceforge maybe?)

Re: [GENERAL] configuring timezone

Thank you for explaining. Regards, Igor Neyman From: Tom Lane [t...@sss.pgh.pa.us] Sent: Wednesday, February 06, 2013 5:11 PM To: Igor Neyman Cc: Adrian Klaver; pgsql-general@postgresql.org Subject: Re: [GENERAL] configuring timezone Igor Neyman writes:

[GENERAL] Re: [HACKERS] function for setting/getting same timestamp during whole transaction

Removing -hackers and adding -general On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík wrote: > > > >> Alternately, you might be able to use a custom GUC from a rather smaller >> PL/PgSQL function. At transaction start, issue: >> >> set_config('myapp.trigger_time', '', 't'); >> > > This is

Re: [GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

Hi Hari, Thanks for the tip. We tried applying that patch, however the error recurred exactly as before. Regards // Mike -Original Message- From: Hari Babu [mailto:haribabu.ko...@huawei.com] Sent: Tuesday, 5 February 2013 10:07 PM To: Michael Harris; pgsql-general@postgresql.org Subje

Re: [GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

maybe pg_basebackup can`t handle such big database.try rsync,pg_start_backup,rsync,pg_stop_backup,it always works fine for us.our instance is about 2TB and we use pg9.1.x. jov 在 2013-2-7 下午2:25，"Michael Harris" 写道： > Hi Hari, > > Thanks for the tip. We tried applying that patch, however the error

[GENERAL] REINDEX deadlock - Postgresql -9.1

We are hitting a situation where REINDEX is resulting in postgresql to go to dead lock state* for ever*. On debugging the issue we found that 3 connections are going in to some dead lock state. 1. *idle in transaction * 2. *REINDEX waiting * 3. *SELECT waiting* All these connecti

[GENERAL] Re: [HACKERS] function for setting/getting same timestamp during whole transaction

Nice. This solves problem with clearing of session variables. Thank you Miro 2013/2/7 Gurjeet Singh > Removing -hackers and adding -general > > > On Wed, Feb 6, 2013 at 7:26 AM, Miroslav Šimulčík > wrote: > >> >> >> >>> Alternately, you might be able to use a custom GUC from a rather >>> sma

Re: [GENERAL] DEFERRABLE NOT NULL constraint

Gavan Schneider wrote: > Taking a different tangent ... Good idea. > Is there anything in the SQL standards about NOT NULL > constraints being deferrable? > > To my mind we should not consider implementing non-standard > behaviour, but if something is in the standard I can't see why > it shouldn

Re: [GENERAL] ERROR: invalid input syntax for integer: ""

Thank you to all for your help on this problem. I've summarised the resolution in the hope that it might help someone else. With all the advice I have gone forward and discovered that the issue related to a postcode anomaly. A client had provided a new postbox postcode (the application normally

Re: [GENERAL] REINDEX deadlock - Postgresql -9.1

Anoop K wrote: > We are hitting a situation where REINDEX is resulting in postgresql to go to > dead lock state for ever. > On debugging the issue we found that > 3 connections are going in to some dead lock state. > > 1.idle in transaction > 2.REINDEX waiting > 3.SELECT waiting > >