Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

Adding "raw" content present on Nabble that gets filtered by the mailing list. On Wednesday, April 1, 2015, Taytay wrote: > We make heavy use of `GET STACKED DIAGNOSTICS` to determine where errors > happened. > However, I am trying to use RAISE EXCEPTION to report errors, and have > discovered t

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

2015-04-02 9:13 GMT+02:00 David G. Johnston : > Adding "raw" content present on Nabble that gets filtered by the mailing > list. > > On Wednesday, April 1, 2015, Taytay wrote: > >> We make heavy use of `GET STACKED DIAGNOSTICS` to determine where errors >> happened. >> However, I am trying to use

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

On Thursday, April 2, 2015, Pavel Stehule wrote: > > > 2015-04-02 9:13 GMT+02:00 David G. Johnston >: > >> Adding "raw" content present on Nabble that gets filtered by the mailing >> list. >> >> On Wednesday, April 1, 2015, Taytay > > wrote: >> >>> We make heavy use of `GET STACKED DIAGNOSTICS`

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

The OP on this thread has introduced a potential compromise. Keep the > current printing behavior for RAISE but the construction of the error > itself should contain all of the relevant detail so that the caller can get > to the suppressed information via, in this instance, GET STACKED > DIAGNOSTI

[GENERAL] implicit CAST on CSV COPY FROM

Hi I have a set of CSV data that I'm importing containing dates stored as INT values (eg 20150402). The value 0 represents a null date in this format. I've created a function and cast that (ab)uses the system text::date cast: CREATE FUNCTION to_date(integer) RETURNS date AS $$SELECT

[GENERAL] Relation name stored in Postgres

Hi, I want to know how the relation name is stored in postgres, In which part of the postgres source code could I find the relation name being stored. Thank you -- Regards, K.Ravikiran ᐧ

Re: [GENERAL] implicit CAST on CSV COPY FROM

On 04/02/2015 04:07 AM, Geoff Winkless wrote: Hi I have a set of CSV data that I'm importing containing dates stored as INT values (eg 20150402). The value 0 represents a null date in this format. I've created a function and cast that (ab)uses the system text::date cast: CREAT

Re: [GENERAL] Relation name stored in Postgres

Hi it is in system catalog - table pg_class, column relname Regards Pavel Stehule 2015-04-02 15:52 GMT+02:00 Ravi Kiran : > Hi, > > I want to know how the relation name is stored in postgres, In which part > of the postgres source code could I find the relation name being stored. > > Thank you

Re: [GENERAL] Relation name stored in Postgres

Hi, Thank you Sir. Also, could you tell me during which stage(whether parser,optimizer or executor) does the table name gets stored, and if possible could you tell me which program specifically does that. Thank you. ᐧ On Thu, Apr 2, 2015 at 7:32 PM, Pavel Stehule wrote: > Hi > > it is in system

Re: [GENERAL] Relation name stored in Postgres

Hi, Also, could you tell me during which stage(whether parser,optimizer or executor) does the table name gets stored, and if possible could you tell me which program specifically does that. ᐧ On Thu, Apr 2, 2015 at 7:56 PM, Ravi Kiran wrote: > Hi, > Thank you Sir. > Also, could you tell me du

Re: [GENERAL] Relation name stored in Postgres

2015-04-02 16:26 GMT+02:00 Ravi Kiran : > Hi, > Thank you Sir. > Also, could you tell me during which stage(whether parser,optimizer or > executor) does the table name gets stored, and if possible could you tell > me which program specifically does that. > Usually parser, optimizer and command ex

Re: [GENERAL] How to recover or resent the password for the user 'postgres'

On Monday, March 30, 2015 06:27:19 AM Adrian Klaver wrote: > On 03/30/2015 01:09 AM, Arup Rakshit wrote: > > Hi, > > > > I am trying to follow what has been mentioned below **Setting Up Postgres** > > (https://www.digitalocean.com/community/tutorials/how-to-setup-ruby-on-rails-with-postgres). > >

Re: [GENERAL] Relation name stored in Postgres

The table name is stored in pg_class when you execute the CREATE TABLE statement. The PostgreSQL main program, "postmaster" handles all the work. It appears to me your concept of how PostgreSQL works is very distorted. Perhaps you would best be served by purchasing and reading "Beginning Databases

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

There appears to be a fair amount of nuance here, but I am _very_ impressed with how quickly you have responded. Thank you for your quick attention to this issue! (Yet another thing that makes me happy to be using Postgres). We have fair amount of business logic in Postgres functions, and the abil

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

I believe the availability of trapping the error codes and raising the appropriate message is already in PLPGSQL. Please see the two sections below. http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING http://www.postgresql.org/docs/9.4/interactive

Re: [GENERAL] Why doesn't `RAISE EXCEPTION` provide error context?

Indeed it is possible Melvin. I read through those links, and I am afraid I wasn't clear enough. If Postgres throws an exception, we can handle it and get the context, which will allow us to pinpoint exactly where the problem was, and what functions were called leading up to the error. However, we

Re: [GENERAL] now() vs 'epoch'::timestamp

> "SC" == Steve Crawford writes: SC> Very convoluted calculation as others have noted. As to why it is SC> "off", you are casting one part of the statement to an integer thus SC> truncating the microseconds but are not doing the same on the other SC> side of the calculation. It wasn't the mi

Re: [GENERAL] now() vs 'epoch'::timestamp

On Thu, Apr 2, 2015 at 10:27 AM, James Cloos wrote: > > "SC" == Steve Crawford writes: > > SC> Very convoluted calculation as others have noted. As to why it is > SC> "off", you are casting one part of the statement to an integer thus > SC> truncating the microseconds but are not doing the s

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/02/2015 10:34 AM, David G. Johnston wrote: On Thu, Apr 2, 2015 at 10:27 AM, James Cloos >wrote: > "SC" == Steve Crawford mailto:scrawf...@pinpointresearch.com>> writes: ... What I haven't determined is why converting back is off by 21600 secon

Re: [GENERAL] now() vs 'epoch'::timestamp

> "DGJ" == David G Johnston writes: DGJ> ​What timezone is your server set to - and/or the client requesting the DGJ> calculation? Everything is in UTC. -JimC -- James Cloos OpenPGP: 0x997A9F17ED7DAEA6 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To mak

Re: [GENERAL] now() vs 'epoch'::timestamp

> "SC" == Steve Crawford writes: SC> select (now() - (now() - 'epoch')) ; SC> ?column? SC> SC> 1969-12-31 17:00:00-08 My servers all run in UTC, so that query works here. The first query where I noticed this, I had just run date +%s and used that value in n

[GENERAL] bdr global sequence not initialized

I install patched PostgreSQL 9.4.1 with BDR 0.9.0, and set up a BDR group of 2 linux hosts, each has 4 replicated databases. Global sequence is enabled (whose configuration is added in postgresql.conf). When I insert new records into any of 4 databases in the first host (created via bdr.bdr_group

Re: [GENERAL] Would like to know how analyze works technically

On Wed, April 1, 2015 5:50 pm, Tom Lane-2 [via PostgreSQL] wrote: > > > TonyS writes: > >> The analyze function has crashed again while the overcommit entries >> were as above. The last bit of the PostgreSQL log shows: MdSmgr: 41934848 >> total in 14 blocks; 639936 free (0 chunks); 41294912 used

Re: [GENERAL] now() vs 'epoch'::timestamp

On 04/02/2015 11:01 AM, Steve Crawford wrote: On 04/02/2015 10:34 AM, David G. Johnston wrote: On Thu, Apr 2, 2015 at 10:27 AM, James Cloos mailto:cl...@jhcloos.com>>wrote: > "SC" == Steve Crawford mailto:scrawf...@pinpointresearch.com>> writes: ... What I haven't determined is

[GENERAL] The case of PostgreSQL on NFS Server (II)

Hi, this is somehow overlapping one thread which was already posted in this list here . However, I'm newbie in PostgreSQL and would need some help from experts on two aspects. I apologize if these were already implicitely mentioned

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

On Thu, Apr 2, 2015 at 1:01 PM, Octavi Fors wrote: > I don't see how to migrate the databases from my desktop directory > determined in a) to my NAS. Could someone please provide the steps to > accomplish that? > ALTER DATABASE name SET TABLESPACE new_tablespace ​You are solely responsible for

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

On Thu, Apr 2, 2015 at 3:01 PM, Octavi Fors wrote: > Hi, > > this is somehow overlapping one thread which was already posted in this list >here. > > However, I'm newbie in PostgreSQL and would need some help from experts on > two aspects. I apologize if these were already implicitely mentioned in

[GENERAL] Error handling in C API function calls in a way that doesn't close client connection

Hello all, This question refers to version 9.4 of Postgres. I have have a function Datum do_something(PG_FUNCTION_ARGS) { ... if(error_occured) { ereport(ERROR, (errmsg("some error occured"))); } ... } When I call do_something in a way to deliberately cause the error cond

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

Thanks John for your extensive and helpful response. A few quick answers which may clarify my desktop-NAS system details: If you are running SELinux enabled & enforcing, it is even > more complicated. > -no, I'm not running SELinux. -My NAS is a Synology DS2415+

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

On Thu, Apr 2, 2015 at 7:09 PM, Octavi Fors wrote: > Thanks John for your extensive and helpful response. > > You see that I used the ALTER from David in last message, instead your > suggestion of creating the whole database again. Looks good! > > Two only questions remain for me: > > 1) could

[GENERAL] ERROR: out of memory

Hey folks, I have 4 postgresql servers 9.3.6(on master I use 9.3.5) configured with streaming replication - with 1 maser(30GB RAM, processor - Intel Xeon E5-2680 v2) and 3 slaves(61 Intel Xeon E5-2670 v2), all on Ubuntu 14.04.1 LTS, Master configuration: default_statistics_target = 50 maintena

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

On Apr 2, 2015, at 5:09 PM, Octavi Fors wrote: > > And second, because I need the database to be accessible from two computers > in the same LAN. If you do this, you will destroy your database[1]. Why not have the database running on one machine, all the time, potentially with a real disk s

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

On Thu, Apr 2, 2015 at 5:09 PM, Octavi Fors wrote: > Thanks John for your extensive and helpful response. > > I have a NAS box. But I would worry about responsiveness. What is >> better, IMO, is an external SATA connected DAS box. DAS is "Direct >> Attached Storage". Many PCs have a eSATA port on

Re: [GENERAL] ERROR: out of memory

On Thu, Apr 2, 2015 at 5:24 PM, Dzmitry Nikitsin wrote: > Hey folks, > I have 4 postgresql servers 9.3.6(on master I use 9.3.5) configured with > streaming replication - with 1 maser(30GB RAM, processor - Intel Xeon > E5-2680 v2) and 3 slaves(61 Intel Xeon E5-2670 v2), all on Ubuntu 14.04.1 > L

Re: [GENERAL] ERROR: out of memory

Well right of the bat, if your master shared_buffers = 7GB and 3 slaves shared_buffers = 10GB, that is 37GB total, which means you are guaranteed to exceed the 30GB physical limit on your machine. General recommendation is to only allocate 1/4 total memory for shared_buffers, so start by cutting ba

Re: [GENERAL] ERROR: out of memory

On Thursday, April 2, 2015, Melvin Davidson wrote: > Well right of the bat, if your master shared_buffers = 7GB and 3 slaves > shared_buffers = 10GB, that is 37GB total, which means you are guaranteed > to exceed the 30GB physical limit on your machine. > I don't get why you are adding these tog

Re: [GENERAL] ERROR: out of memory

it¹s 4 different servers. From: "David G. Johnston" Date: Thursday, April 2, 2015 at 9:37 PM To: Melvin Davidson Cc: Bob Jones , "pgsql-general@postgresql.org" Subject: Re: [GENERAL] ERROR: out of memory On Thursday, April 2, 2015, Melvin Davidson wrote: > Well right of the bat, if your

Re: [GENERAL] ERROR: out of memory

Thank you David. I see some queries running for 10+ seconds, but I do not have transactions there, it’s just select queries. More thoughts ? Thanks, Dzmitry From: "David G. Johnston" Date: Thursday, April 2, 2015 at 8:57 PM To: Bob Jones Cc: "pgsql-general@postgresql.org" Subject: Re: [

Re: [GENERAL] ERROR: out of memory

Actually I checked it wrong, state for queries I mentioned is idle, I.e. - they are showing previous transaction, so I do not see any long running transactions right now. Thanks, Dzmitry From: "David G. Johnston" Date: Thursday, April 2, 2015 at 8:57 PM To: Bob Jones Cc: "pgsql-general@p

[GENERAL] quick q re execute & scope of new

Easier to give an example than describe the question, any chance of making something like this work? execute('insert into ' || tblname || ' values(new.*)'); -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ https://www.linkedin.com/in/scottribe/ (303) 722-0567 voice

Re: [GENERAL] quick q re execute & scope of new

Scott Ribe writes: > Easier to give an example than describe the question, any chance of making > something like this work? > execute('insert into ' || tblname || ' values(new.*)'); Not like that, for certain. It might work to use EXECUTE ... USING new.* or some variant of that.

Re: [GENERAL] quick q re execute & scope of new

On 04/02/2015 08:30 PM, Scott Ribe wrote: Easier to give an example than describe the question, any chance of making something like this work? You doing this in plpgsql trigger function I presume? execute('insert into ' || tblname || ' values(new.*)'); So http://www.postgresql.org/docs/

Re: [GENERAL] quick q re execute & scope of new

On Apr 2, 2015, at 10:10 PM, Tom Lane wrote: > > Not like that, for certain. It might work to use EXECUTE ... USING new.* > or some variant of that. Couldn't get a variant of that to work, but this did: execute('insert into ' || tblnm || ' select $1.*') using new; -- Scott Ribe scott_r...@el

Re: [GENERAL] quick q re execute & scope of new

On Apr 2, 2015, at 10:14 PM, Adrian Klaver wrote: > > EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* || ')' Not that easy, strings are not quoted correctly, and null values are blank. Might be a function to translate new.* into a string as needed for this use, but I foun

Re: [GENERAL] Error handling in C API function calls in a way that doesn't close client connection

Igor Stassiy writes: > This question refers to version 9.4 of Postgres. I have have a function > Datum do_something(PG_FUNCTION_ARGS) > { > ... > if(error_occured) { > ereport(ERROR, (errmsg("some error occured"))); > } > ... > } > When I call do_something in a way to del

Re: [GENERAL] quick q re execute & scope of new

On April 2, 2015, Scott Ribe wrote: > On Apr 2, 2015, at 10:14 PM, Adrian Klaver wrote: > > EXECUTE 'insert into ' || quote_ident(tblname) || ' values(' || new.* || > > ')' > > Not that easy, strings are not quoted correctly, and null values are blank. > Might be a function to translate new.* into

Re: [GENERAL] The case of PostgreSQL on NFS Server (II)

Hi David, John et al., as far as I understand eSATA is not an option for me. First because I >> already have bought the DS2415+ NAS, which does not has eSATA I/O :( >> And second, because I need the database to be accessible from two >> computers in the same LAN. >> > > ​This is new - and while th