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

[Alban Hertroys]

On 2 April 2015 at 19:15, Taylor Brown  wrote:

> So, I would rather put a check like this at the top of my function:
>
> --
> important_variable = (p_request::json->>'important_variable')::integer;
> IF (important_variable is NULL) THEN RAISE EXCEPTION 'important_variable
> must not be NULL.'; END IF;
> --
>
> But I won't be able to get the context for that exception, and all I'll be
> able to return from the function or write to the logs is
> 'important_variable must not be NULL.'. If that's the only place I throw
> that error, I'll know where to look. Otherwise, I have no context, and
> won't be able to determine where my exception was thrown. So I'm actually
> better off _not_ throwing my own custom exceptions, even though I would
> prefer to be more defensive about this sort of thing in my code.
>
> I thought I might be able to "trick" postgres into throwing another
> unrelated exception that would not only include my custom error message,
> but allow me to extract the context, telling me the function where I should
> begin debugging.
>

Just a quick idea, but... Perhaps you could put a constraint on those JSON
messages indirectly, by mapping it to some kind of template table?
The top-level of a JSON message can usually be mapped to a table, and a
table can have constraints and, for example, a BEFORE INSERT trigger that
always returns NULL so that the data is not actually inserted into the
table.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

Re: [GENERAL]

[Pavel Stehule]

Hi

http://stackoverflow.com/questions/4477301/creating-jobs-and-schedules-programatically-with-pgagent

regards

Pavel Stehule

2015-04-03 11:27 GMT+02:00 Ramesh T :

> Hi ,
>   How to create job in pgAgent.Where I need to place script in
> pgAgent..any help
>
> Advanced thanks...
>
>

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

[John McKown]

On Fri, Apr 3, 2015 at 1:40 AM, Octavi Fors  wrote:
> Hi David, John et al.,

> Oops, sorry yes I think I may "miss-spoke" when explaining my second reason
> why not choosing eSATA.
> My situation is the following:
>
> -Two computers (C1 & C2) and NAS (with no eSATA I/O) on the same LAN.
> -C1 acquires images from a telescope and periodically stores them via NFS in
> the NAS (no database involved here, just in the ext4 filesystem).
> -C2 is a 12 xeon core-class server designed to analyze the stored images in
> the NAS, and compute astrometry & photometry measurements (catalogs & light
> curves) for every star & image. These measurements are inserted in the
> catalogs database inside the NAS.
>
> Therefore there's only *one* computer (C2) which will run postgresql server
> with the tablespace onNAS.
>
> So does this approach sound like feasible if the NFS parameters are set
> properly?

OK, it is very understandable why the images are on the NAS. It is the
easiest way to share them. I guess you want the DB on the NAS simply
because you don't have sufficient disk space on the disks connected to
C2.

>

> Could you confirm that
> nas_ip:/volume1/data /home/ofors/Documents/nas nfs noac,sync
> would be good options for /etc/fstab?
>
> Any additional NFS parameter?

Have you done a web search on "NFS Performance"? I got some good hits
with Google.
http://www.tldp.org/HOWTO/NFS-HOWTO/performance.html  (a bit old, I've
been told)
http://www.cyberciti.biz/faq/linux-unix-tuning-nfs-server-client-performance/

Mentions "noatime" and "nodiratime" to not update the last access
date/time on files & directories, saving bandwidth. A good explanation
of the NFS mount options, IMO, are on this site:
http://www.dbaexpert.com/blog/nfs-options-for-performance/

Some interesting "speed test" code:
https://github.com/sabujp/nfsSpeedTest



>

> Thanks a lot in advance,
> --
> Octavi Fors

-- 
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Adrian Klaver]

On 04/02/2015 09:59 PM, 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 a string as needed for this use, 
but I found another way based on Tom's suggestion:


My mistake for grabbing off the top of my head without testing my code.



execute('insert into ' || tblnm || ' select $1.*') using new;




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Serializable transaction restart/re-execute

[Filipe Pina]

Hello,

I come from a GTM background and once of the transactional features there are 
the “Transaction Restarts”.

Transaction restart is when we have two concurrent processes reading/writing to 
the same region/table of the database, the last process to commit will “see” 
that the database is not the same as it was when the transaction started and 
goes back to the beginning of the transactional code and re-executes it.

The closest I found to this in PGSQL is the Serializable transaction isolation 
mode and it does seem to work well except it simply throws an error 
(serialization_failure) instead of restarting.

I’m trying to make use of this exception to implement restartable functions and 
I have all the examples and conditions mentioned here in a question in SO 
(without any answer so far…):

http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure
 


So basically I have two questions:
- the restartable “wrapper” function never gets its “DB view” refreshed once it 
restarts, I assume it’s because of the outter transaction (at function level) 
so it never re-reads the new values and keeps failing with 
serialization_failure.. Any way to solve this?
- the ideal would be to be able to define this at database level so I wouldn’t 
have to implement wrappers for all functions.. Implementing a 
“serialization_failure” generic handler that would simply re-call the function 
that threw that exception (up to a number of tries). Is this possible without 
going into pgsql source code?

Thanks,
Filipe

Re: [GENERAL] Serializable transaction restart/re-execute

[Bill Moran]

On Fri, 3 Apr 2015 15:35:14 +0100
Filipe Pina  wrote:

> Hello,
> 
> I come from a GTM background and once of the transactional features there are 
> the ?Transaction Restarts?.
> 
> Transaction restart is when we have two concurrent processes reading/writing 
> to the same region/table of the database, the last process to commit will 
> ?see? that the database is not the same as it was when the transaction 
> started and goes back to the beginning of the transactional code and 
> re-executes it.
> 
> The closest I found to this in PGSQL is the Serializable transaction 
> isolation mode and it does seem to work well except it simply throws an error 
> (serialization_failure) instead of restarting.
> 
> I?m trying to make use of this exception to implement restartable functions 
> and I have all the examples and conditions mentioned here in a question in SO 
> (without any answer so far?):
> 
> http://stackoverflow.com/questions/29372202/postgresql-generic-handler-for-serialization-failure
>  
> 
> 
> So basically I have two questions:
> - the restartable ?wrapper? function never gets its ?DB view? refreshed once 
> it restarts, I assume it?s because of the outter transaction (at function 
> level) so it never re-reads the new values and keeps failing with 
> serialization_failure.. Any way to solve this?
> - the ideal would be to be able to define this at database level so I 
> wouldn?t have to implement wrappers for all functions.. Implementing a 
> ?serialization_failure? generic handler that would simply re-call the 
> function that threw that exception (up to a number of tries). Is this 
> possible without going into pgsql source code?

I suspect that savepoints will accomplish what you want:
http://www.postgresql.org/docs/9.4/static/sql-savepoint.html

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Overlap function for hstore?

[Stefan Keller]

Hi,

I'd like to get an overlap function similar to '&&' but for key-value
pairs of hstore!

This underfits:

postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
hstore_to_array('a=>2,d=>4,b=>2'::hstore)

...because array overlaps op takes every element (even 'a' or 2 alone)
and doesn't test for key and value together like in 'b=>2'!

Any clever ideas?

Yours, Stefan


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] "could not split GIN page; no new items fit"

[Chris Curvey]

Hmm, I'm trying to create a gin index, thusly:

create index foo_idx on foo using gin(entry gin_trgm_ops);

and I'm getting the error "could not split GIN page; no new items fit"

Any idea what this means, or how I can get around it?  The table in
question has about 23MM rows, if that makes any difference.  The only
reference that search engines returned was the source code.

select version()

PostgreSQL 9.4.1 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
4.9.1-16ubuntu6) 4.9.1, 64-bit

-Chris

-- 
If money can fix it, it's not a problem.  - Tom Magliozzi

Re: [GENERAL] Overlap function for hstore?

[Paul Jungwirth]

> This underfits:
>
> postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
> hstore_to_array('a=>2,d=>4,b=>2'::hstore)
>
> ...because array overlaps op takes every element (even 'a' or 2 alone)
> and doesn't test for key and value together like in 'b=>2'!

How about hstore_to_matrix? Then you have a tuple for each key/value pair.

Paul




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Overlap function for hstore?

[David G. Johnston]

On Fri, Apr 3, 2015 at 5:37 PM, Stefan Keller  wrote:

> Hi,
>
> I'd like to get an overlap function similar to '&&' but for key-value
> pairs of hstore!
>
> This underfits:
>
> postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
> hstore_to_array('a=>2,d=>4,b=>2'::hstore)
>
> ...because array overlaps op takes every element (even 'a' or 2 alone)
> and doesn't test for key and value together like in 'b=>2'!
>
> Any clever ideas?
>

​Not tested!

SELECT hstore(array_agg(key), array_agg(value)) FROM (
SELECT each(...)
INTERSECT
SELECT each(...)​
) src

David J.

Re: [GENERAL] "could not split GIN page; no new items fit"

[Tom Lane]

Chris Curvey  writes:
> Hmm, I'm trying to create a gin index, thusly:
> create index foo_idx on foo using gin(entry gin_trgm_ops);

> and I'm getting the error "could not split GIN page; no new items fit"

> Any idea what this means, or how I can get around it?

Looks to me like a bug (ie, the code seems to think this is a can't-happen
case).  Don't suppose you could supply sample data that triggers this?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general