Taytay | 2 Apr 07:42 2015

Why doesn't `RAISE EXCEPTION` provide error context?

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 that RAISE is specifically prevented from adding to the error
context:

So that means that this doesn't work:

I've posted more details here:
http://dba.stackexchange.com/questions/96743/postgres-how-to-get-stack-trace-for-a-manually-raised-exception

That context would be awfully useful for us, even for manually generated
exceptions.
Can anyone shed some light on A) why this is, and B) if it's still desired,
and C) if so, are there any workarounds? Is there an exception I can trick
Postgres into throwing that will include my user-generated string?

Many thanks for any help you can offer.

--
View this message in context: http://postgresql.nabble.com/Why-doesn-t-RAISE-EXCEPTION-provide-error-context-tp5844382.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--

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

James Cloos | 1 Apr 20:50 2015
Face

now() vs 'epoch'::timestamp

I've for some time used:

   (now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer

to get the current seconds since the epoch.  The results are consistant
with date +%s.

(Incidently, is there a better way in 9.4?)

But I found the 'epoch'::timestamp + $THAT_VALUE::reltime was off.

I consitantly get 1970-01-01 06:00 plus a fraction of a second from:

select now() -  ((now()::timestamp without time zone - 'epoch'::timestamp without time zone)::reltime::integer)::reltime;

The machines on which I've tried it all have localtime == UTC.

Am I missing something obvious?

Also, is there any way to get the equiv of date +%s%N as a numeric or a
double precision?

-JimC
-- 
James Cloos <cloos <at> jhcloos.com>         OpenPGP: 0x997A9F17ED7DAEA6

--

-- 
Sent via pgsql-general mailing list (pgsql-general <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
(Continue reading)

Rémi Cura | 1 Apr 17:43 2015
Picon

Partitionning using geometry

Hey dear list,

I'd like to partition geographical (geometry) data with postgres mechanism.
(my usage is in fact related to pointcloud, but I use geometry as a work around)
From example I read on constraint, nothing should prevent it from working
Here is a self contained example, the planner doesn"t seems to use the constraint_exclusion mechanism, whatever the constraint

Thanks,
Cheers,
Rémi-C

------

CREATE SCHEMA IF NOT EXISTS test_partitionning;
SET search_path TO test_partitionning, public ;

DROP TABLE IF  EXISTS test_father CASCADE;
CREATE TABLE test_father  (
    gid SERIAL PRIMARY KEY
    , geom geometry
);

create table test_child_1 (
    check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(10,10),10  ) ) )
    ,check ( geom&&ST_Expand(ST_MakePoint(10,10),10  ) ) 
    , CHECK (ST_X(geom) BETWEEN 0 AND 20)
    , CHECK (ST_Y(geom) BETWEEN 0 AND 20)
    , CHECK (  ST_Intersects(geom, ST_Expand(ST_MakePoint(10,10),10  ))  )
) inherits (test_father);
--CREATE INDEX ON test_child_1 USING GIST(geom);

create table test_child_2 (
    check (geometry_overlaps(geom,ST_Expand(ST_MakePoint(30,10),10  ) ) )
    ,check ( geom&&ST_Expand(ST_MakePoint(30,10),10  ) )
    , CHECK (ST_X(geom) BETWEEN 20 AND 40)
    , CHECK (ST_Y(geom) BETWEEN 0 AND 20)
    , CHECK (  ST_Intersects(geom, ST_Expand(ST_MakePoint(30,10),10  ))  )
    ) inherits (test_father);
--CREATE INDEX ON test_child_2 USING GIST(geom);


INSERT INTO test_child_1 (geom)
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())
FROM generate_series(1,90) AS s1, generate_series(1,90) AS s2;

INSERT INTO test_child_2 (geom)
SELECT ST_MakePoint(s1/10.0+random(),s2/10.0+random())
FROM generate_series(200,300) AS s1, generate_series(1,90) AS s2;


SHOW constraint_exclusion;
SET constraint_exclusion TO partition;


WITH area_of_interest AS (
    SELECT ST_Buffer(ST_MakePoint(5,5),1) as buf
)
SELECT *
FROM area_of_interest, test_father
WHERE  -- geom && buf
    ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)
    AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf) ;

 
SELECT *
FROM  test_father , ST_Buffer(ST_MakePoint(5,5),1) as buf
WHERE 
    ST_X(geom) BETWEEN ST_XMin(buf) AND ST_Xmax(buf)
    AND ST_Y(geom) BETWEEN ST_YMin(buf) AND ST_Ymax(buf);
------
Adrian Klaver | 1 Apr 17:23 2015

Re: stack builder

On 04/01/2015 08:21 AM, Ramesh T wrote:
> I had installed on Linux.re-install postgres for stack builder ..?
> cron is  their ,but I am new to this cron and  is this method for job
> schedulers in postgres.

Again, how did you install Postgres?

>
> On Wed, Apr 1, 2015 at 8:39 PM, Adrian Klaver <adrian.klaver <at> aklaver.com
> <mailto:adrian.klaver <at> aklaver.com>> wrote:
>
>     On 04/01/2015 07:59 AM, Ramesh T wrote:
>
>             Hi,
>                        I had installed pgadmin3 but not selected
>         stackbuilder
>         ,let me
>            know how  to  add stackbuilder to pgadmin3 for additional addons.
>
>
>     You cannot, StackBuilder is not part of pgAdmin3, it is another
>     application entirely.
>
>     How did you install Postgres and what OS?
>
>
>         On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver
>         <adrian.klaver <at> aklaver.com <mailto:adrian.klaver <at> aklaver.com>
>         <mailto:adrian.klaver <at> aklaver.__com
>         <mailto:adrian.klaver <at> aklaver.com>>> wrote:
>
>              On 04/01/2015 06:22 AM, Ramesh T wrote:
>
>                  Hi all,
>                                I didn't selected stack builder at the
>         time of
>                  installation
>                  of postgres.Any help how to add*application stack
>         builder* to
>                  existed
>                  postgres.
>
>
>
>              Assuming you are talking about an install done using the
>         EDB installer:
>
>         http://www.enterprisedb.com/____docs/en/9.3/pginstguide/Table%____20of%20Contents.htm
>         <http://www.enterprisedb.com/__docs/en/9.3/pginstguide/Table%__20of%20Contents.htm>
>
>         <http://www.enterprisedb.com/__docs/en/9.3/pginstguide/Table%__20of%20Contents.htm
>         <http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table%20of%20Contents.htm>>
>
>              4 Using Stack Builder
>
>
>              --
>              Adrian Klaver
>         adrian.klaver <at> aklaver.com <mailto:adrian.klaver <at> aklaver.com>
>         <mailto:adrian.klaver <at> aklaver.__com
>         <mailto:adrian.klaver <at> aklaver.com>>
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver <at> aklaver.com <mailto:adrian.klaver <at> aklaver.com>
>
>

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Adrian Klaver | 1 Apr 17:09 2015

Re: stack builder

On 04/01/2015 07:59 AM, Ramesh T wrote:
>    Hi,
>               I had installed pgadmin3 but not selected stackbuilder
> ,let me
>   know how  to  add stackbuilder to pgadmin3 for additional addons.

You cannot, StackBuilder is not part of pgAdmin3, it is another 
application entirely.

How did you install Postgres and what OS?

>
> On Wed, Apr 1, 2015 at 7:15 PM, Adrian Klaver <adrian.klaver <at> aklaver.com
> <mailto:adrian.klaver <at> aklaver.com>> wrote:
>
>     On 04/01/2015 06:22 AM, Ramesh T wrote:
>
>         Hi all,
>                       I didn't selected stack builder at the time of
>         installation
>         of postgres.Any help how to add*application stack builder* to
>         existed
>         postgres.
>
>
>
>     Assuming you are talking about an install done using the EDB installer:
>
>     http://www.enterprisedb.com/__docs/en/9.3/pginstguide/Table%__20of%20Contents.htm
>     <http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table%20of%20Contents.htm>
>
>     4 Using Stack Builder
>
>
>     --
>     Adrian Klaver
>     adrian.klaver <at> aklaver.com <mailto:adrian.klaver <at> aklaver.com>
>
>

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Adrian Klaver | 1 Apr 15:45 2015

Re: stack builder

On 04/01/2015 06:22 AM, Ramesh T wrote:
> Hi all,
>              I didn't selected stack builder at the time of installation
> of postgres.Any help how to add*application stack builder* to existed
> postgres.
>
>

Assuming you are talking about an install done using the EDB installer:

http://www.enterprisedb.com/docs/en/9.3/pginstguide/Table%20of%20Contents.htm

4 Using Stack Builder

-- 
Adrian Klaver
adrian.klaver <at> aklaver.com

--

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

Rémi Cura | 1 Apr 15:40 2015
Picon

partitoning expert : Partitonning with specialization of one column type

Hey, postgres 9.3 here.
for partitionning expert.

I have several table child with columns (gid serial, patch pcpatch(n))
where n may change depending on the tables, and pcpatch is a type from pgpointcloud extension
(here is the definition)
-----
CREATE TYPE pcpatch
   (INPUT=pcpatch_in,
       OUTPUT=pcpatch_out,
       RECEIVE=-,
       SEND=-,
       TYPMOD_IN=pc_typmod_in,
       TYPMOD_OUT=pc_typmod_out,
       ANALYZE=-,
       CATEGORY='U', DEFAULT='',
       INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=MAIN);
-----

The question is, how do I create an inheritance scheme?

If the father table is

CREATE TABLE father(
gid serial,
patch pcpatch
) ;

trying to put child_1, child_2 ... to inherit father raise an error

----
child table "test_child_1" has different type for column "patch"
----

So my question is, how would it be possible (if at all) to inherit of father table, while specializing the type of father table in child table?

Thanks,
Cheers,
Rémi-C
Alexey Bashtanov | 1 Apr 08:56 2015

How to diagnose max_locks_per_transaction is about to be exhausted?

Hello!

max_locks_per_transaction enforces some restriction: the_locks_count <= 
the_threshold

the_threshold is max_locks_per_transaction * (max_connections + 
max_prepared_transactions), the documentation is quite clear.

What is the_locks_count?
In docs we see "no more than this many distinct objects can be locked at 
any one time".
What kinds of locks should we include? does the limit really restricts 
*distinct* objects count and what is the distinction key for every lock 
kind?

What should one change in the following query to make it show the value 
limited by the_threshold?
select count(distinct relation) + count(distinct (classid, objid)) from 
pg_locks

Best regads
   Alexey Bashtanov

--

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

Maulik Shah | 30 Mar 05:52 2015
Picon

how to convert float to timestamp in single select query

Dear Sir/Madam

how to convert float to timestamp in single select query

for exp. i have float as 1.251152515236 ,

i want to convert this to datetime and from datetime to timestamp...
i.e. 02:22:044456

I need sql query

It is actually .crd file made in xml , and i have query inside xml like

<![CDATA[
SELECT USER.LastName, USER.fltime from Tbalname

]>

fltime has float value which i need to convert to time

Regards
Maulik Shah

--

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

Rebecca Zahra | 28 Mar 07:52 2015
Picon

Re: GiST indeices on range types

Good morning, 

I am Rebecca Zahra and I am currently in my final year of Masters studies at the University of Malta. My thesis is about the usage of indexes for multi-dimensional data. 

I was going through the posts regarding GIST indexes and I came across the following http://dba.stackexchange.com/questions/39589/optimizing-queries-on-a-range-of-timestamps-two-columns

I was wondering if maybe you can help me with a question.  I know that an R-Tree index implementation is used on top of GIST to index spatial data. Can you please tell me what type of index is used on top of GIST to index range types? 

Thanks a lot for your time. Greatly appreciate
Rebecca
Anil Menon | 28 Mar 00:18 2015
Picon

Strange behavior of insert CTE with trigger

Hi,

I am trying to wrap my head around a strange problem I am having. I have double checked the documentation but I could not find anything on this.

I am attaching a simplified version of my problem. I my TEST 4 I expect 1 row but I get nothing. The test is

with I(id) as (
  insert into abc(colData) values (2) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id

Here the Insert causes an trigger to be executed. The trigger inserts a record into the table abc_Excp_log. I combine the returned serial key of the insert with the table abc_Excp_log.

However I get no rows  returned from the select statement- looks the insert to abc_Excp_log is executed *after* the select statement or some sort of race condition is executed.

Is this documented anywhere and is the expected behavior? Documented anywhere? The CTE part of the PG doc does not say anything on this.

Currently using ver PG 9.3 on Windows 8.1

Thanks in advance,
AK

create table abc (
  colPK serial not null primary key,
  colData int not null
);

create table abc_ins_log(
  colPK int not null primary key,
  starttime timestamp with time zone,
  endtime timestamp with time zone
);

create table abc_excp_log(
  colPK int not null primary key,
  msgtxt text,
  msg_context text,
  msg_hint text,
  msg_sqlstate text,
  starttime timestamp with time zone,
  endtime timestamp with time zone
);

create or replace function InsertABC( vColData int) returns boolean as
$$
begin
   if vcoldata <=5 then
    raise exception 'Column data value is less than or equal to 5';
    return false; --I know I know
   --else
    --do big business process
   end if;
   return true;
end;
$$ language plpgsql;
   
create or replace function ABCInsertLog() returns trigger as
$$
declare
vstarttime timestamp with time zone;
  verrmsg1 text;
  verrmsg2 text;
  verrmsg3 text;
  verrmsg4 text;
begin
   select now() into vstarttime;
   perform InsertABC(NEW.colData);
   insert into abc_ins_log(colPK, starttime, endtime)
   values(NEW.colPK, vstarttime, now());
   return NEW;
   exception when others then
   GET STACKED DIAGNOSTICS verrmsg1 = MESSAGE_TEXT, verrmsg2 = PG_EXCEPTION_CONTEXT,verrmsg3=PG_EXCEPTION_HINT, verrmsg4=RETURNED_SQLSTATE;
    insert into abc_excp_log(colPK, msgtxt, msg_context, msg_hint, msg_sqlstate, starttime, endtime)
    values(new.colPK, verrmsg1, verrmsg2, verrmsg3, verrmsg4,vstarttime, now());
   return NEW;
end;
$$ language plpgsql;


CREATE TRIGGER abc_tx_tr
  AFTER INSERT
  ON abc
  FOR EACH ROW
  EXECUTE PROCEDURE ABCInsertLog ();

--SANITY Check
select * from abc; --nothing
select * From abc_ins_log; --nothing
select * from abc_excp_log; --nothing

--TEST 1 : check if working: test case no errors
insert into abc(colData) values (10); --Query returned successfully: one row affected, 63 ms execution time.

select * from abc; --1 row
select * From abc_ins_log; --1 row
select * from abc_excp_log; --nothing


--TEST 2 : cause exception
insert into abc(colData) values (3); --Query returned successfully: one row affected, 42 ms execution time.
select * from abc; --2 rows
select * From abc_ins_log; --1 row
select * from abc_excp_log; --1 row
--2;"Column data value is less than or equal to 5";"SQL statement "SELECT InsertABC(NEW.colData)"
--PL/pgSQL function abcinsertlog() line 10 at PERFORM";"";"P0001";"2015-03-28 06:42:56.187+08";"2015-03-28 06:42:56.187+08"

--TEST 3: test with CTE : test success case
with I(id) as (
  insert into abc(colData) values (10) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id
--expected values : nothing
-- got : nothing
select * from abc; --3 rows
select * From abc_ins_log; --2 rows
select * from abc_excp_log; --1 row

--TEST 4 : test with CTE : test failure case
with I(id) as (
  insert into abc(colData) values (2) returning colPK
)
select *
from abc, abc_excp_log, I
where abc.colPK=abc_excp_log.colPK
and abc.colPK=I.id
--expected values : 1 row
-- got : nothing
select * from abc; --4 rows
select * From abc_ins_log; --2 rows
select * from abc_excp_log; --2 rows

--TEST 5 : to test "normal" inserts with CTE
with I(id) as (
  insert into abc(colData) values (2) returning colPK
)
select id
from  I
--expected value : 1 row
--got : 1 row (value 5)
---

--TEST 6 : test with CTE : sanity check
with I(id) as (
  insert into abc(colData) values (10) returning colPK
)
select *
from abc, abc_ins_log , I
where abc.colPK=abc_ins_log.colPK
and abc.colPK=I.id
--expected values : 1 row
-- got : nothing
select * from abc; --6 rows
select * From abc_ins_log; --3 rows
select * from abc_excp_log; --3 row



Gmane