[GENERAL] Question about SELECT statements with subselects

[Miloslav Semler]

Hello,

I found strange behavior with subselects and I am not able to explain 
it. I have several tables in schema:


tramecky, mt_hodnoty, plata_kusy

in these tables, id is always primary key (serial), table_id is always 
foreign key to table. When I run this query:


select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
expedicni_plato IS NULL

I get 55 rows.

When I run this query:

select tramecky.id FROM a.tramecky WHERE
id NOT IN(SELECT tramecky_id FROM a.plata_kusy) AND
id NOT IN(SELECT tramecky_id FROM a.mt_hodnoty) AND
expedicni_plato IS NULL

I get no rows.. so I expect that rows with foreign keys tramecky_id of 
55 rows are present in table mt_hodnoty. However result of query:


select mt_hodnoty.id FROM a.mt_hodnoty WHERE tramecky_id NOT IN(SELECT 
tramecky_id FROM a.plata_kusy)


is empty set. Can anybody explain such strange behavior?

Thanks in advance,

Miloslav Semler



--
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] a JOIN to a VIEW seems slow

[Frank Millman]

On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:

> Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> query?

Here it is -

https://explain.depesz.com/s/cwm

Frank

Re: [GENERAL] Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

[Pavel Stehule]

DECLARE
>>
>>
>> v_msg TEXT := '''SOMETHING IS WRONG''';
>>
>> v_sqlstate TEXT := '''E0001''';
>>
>> v1 TEXT ;
>>
>>
>> BEGIN
>>
>>   v1 := v_msg || ' USING errcode = ' || v_sqlstate;
>>
>>   RAISE NOTICE '%', v1;
>>
>>   RAISE EXCEPTION '%', v1;
>>
>>
>> EXCEPTION
>>
>>   WHEN SQLSTATE 'E0001' THEN
>>
>>  RAISE NOTICE '%','Error E0001 raised - going to do something about
>> it';
>>
>>   WHEN OTHERS THEN
>>
>>  RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;
>>
>>
>>
>> END
>>
>> $$
>>
>>
>> which returns:
>>
>> NOTICE:  'SOMETHING IS WRONG' USING errcode = 'E0001'
>>
>> NOTICE:  OTHER ERRORS: P0001,'SOMETHING IS WRONG' USING errcode = 'E0001'
>>
>>
>> So clearly the whole of v1 (whilst syntatically correct) is treated as
>> the message and the default sqlstate of P0001 is still raised and caught by
>> WHEN OTHERS.
>>
>>
>> Have tried a few other things but cannot find way to get a custom
>> errcode/sqlstate to be raised except by hardcoding it ! I’m sure there must
>> be a way to do this!
>>
>
> It is not allowed in PLpgSQL - it is based on origin PL/SQL and the master
> origin ADA language - these languages are static to be possible do deep
> static analyse.
>
> If you need this, then you can use PLPythonu or some own C extension.
>
>
Tom has true - you can do it with errcode.

I am sorry

Regards

Pavel


Regards
>
> Pavel
>
>
>> Any help or advice on how to achieve this very much appreciated !
>>
>
>

Re: [GENERAL] Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

[Tom Lane]

mike davis  writes:
> I'm trying to get dynamic version of the RAISE command working so
> that I can use a table of custom application error messages and codes
> for use by all developed plpgsql functions.

This works for me:

DO $$
DECLARE
  v_msg TEXT := 'SOMETHING IS WRONG';
  v_sqlstate TEXT := 'E0001';
BEGIN
  RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate;
EXCEPTION
  WHEN SQLSTATE 'E0001' THEN
 RAISE NOTICE '%','Error E0001 raised - going to do something about it';
  WHEN OTHERS THEN
 RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;
END$$;

NOTICE:  Error E0001 raised - going to do something about it

Or you could do

  RAISE EXCEPTION SQLSTATE v_sqlstate USING message = v_msg;

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

Re: [GENERAL] Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

[Pavel Stehule]

2017-09-22 1:40 GMT+02:00 mike davis :

> I’m trying to get dynamic version of the RAISE command working so that I
> can use a table of custom application error messages and codes for use by
> all developed plpgsql functions. In this way the customer error codes and
> message are not hard coded into code and are defined consistently in one
> place in the db.
>
>
> However, I cannot get a dynamic/parameterised version of the RAISE command
> working with the USING syntax - I want to do this so that i can catch the
> raised error in an EXCEPTION block.
>
>
> The following example shows a example of (working) hardcoded version:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> BEGIN
>
>   RAISE EXCEPTION 'Something is wrong' USING errcode = 'E0001';
>
>
> EXCEPTION
>
>   WHEN SQLSTATE 'E0001' THEN
>
>  RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
>   WHEN OTHERS THEN
>
>  RAISE NOTICE 'OTHER ERRORS: %', sqlstate;
>
>
>
> END
>
> $$
>
>
> Which raises and catches the custom error E0001 and returns (as expected)
>
> NOTICE:  Error E0001 raised - going to do something about it.
>
>
> Now what I am trying to achieve is as above but for the msg text and
> errcode to be retrieved from a table before issuing the RAISE EXCEPTION
> statement.
>
>
> ie. Assume v_msg and v_sqlstate have been retrieved and contain:
>
> v_msg = 'Something is wrong’
>
> v_sqlstate =  ‘E0001’
>
>
> The what I want to raise dynamically is:
>
>
> RAISE EXCEPTION v_msg USING errcode = v_sqlstate;
>
>
> and be able to use the same exception block as above in the hard coded
> example.
>
>
> I searched and found a couple of similar examples where
>
> RAISE EXCEPTION ’%’, i_msg
>
> is used and works but this does not allow a custom SQLSTATE to be raised
> and trapped.
>
>
> ie. The following runs ok:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> v1 TEXT ;
>
>
> BEGIN
>
>
>
>   v1 := 'SOMETHING IS WRONG';
>
>   RAISE NOTICE '%', v1;
>
>   RAISE EXCEPTION '%', v1;
>
>
> EXCEPTION
>
>   WHEN SQLSTATE 'E0001' THEN
>
>  RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
>   WHEN OTHERS THEN
>
>  RAISE NOTICE 'OTHER ERRORS: %', sqlstate;
>
>
>
> END
>
> $$
>
>
> and returns:
>
> NOTICE:  SOMETHING IS WRONG
>
> NOTICE:  OTHER ERRORS: P0001
>
>
> but obviously the WHEN OTHERS has caught the exception and the SQLSTATE is
> the default P0001.
>
>
> So, then what i really want is similar to the above but with the USING
> keyword of RAISE being dynamic/parameterised.
>
>
> So i tried the following:
>
>
> DO
>
> $$
>
> DECLARE
>
>
> v_msg TEXT := '''SOMETHING IS WRONG''';
>
> v_sqlstate TEXT := '''E0001''';
>
> v1 TEXT ;
>
>
> BEGIN
>
>   v1 := v_msg || ' USING errcode = ' || v_sqlstate;
>
>   RAISE NOTICE '%', v1;
>
>   RAISE EXCEPTION '%', v1;
>
>
> EXCEPTION
>
>   WHEN SQLSTATE 'E0001' THEN
>
>  RAISE NOTICE '%','Error E0001 raised - going to do something about
> it';
>
>   WHEN OTHERS THEN
>
>  RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;
>
>
>
> END
>
> $$
>
>
> which returns:
>
> NOTICE:  'SOMETHING IS WRONG' USING errcode = 'E0001'
>
> NOTICE:  OTHER ERRORS: P0001,'SOMETHING IS WRONG' USING errcode = 'E0001'
>
>
> So clearly the whole of v1 (whilst syntatically correct) is treated as the
> message and the default sqlstate of P0001 is still raised and caught by
> WHEN OTHERS.
>
>
> Have tried a few other things but cannot find way to get a custom
> errcode/sqlstate to be raised except by hardcoding it ! I’m sure there must
> be a way to do this!
>

It is not allowed in PLpgSQL - it is based on origin PL/SQL and the master
origin ADA language - these languages are static to be possible do deep
static analyse.

If you need this, then you can use PLPythonu or some own C extension.

Regards

Pavel


> Any help or advice on how to achieve this very much appreciated !
>

Re: [GENERAL] Logical decoding client has the power to crash the server

[Michael Paquier]

On Fri, Sep 22, 2017 at 5:44 AM, Igor Neyman  wrote:
> I think the difference between pg_current_wal_lsn() and confirmed_flush_lsn 
> form pg_catalog.pg_replication_slots for specific replication slot:
>
> SELECT (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance
>FROM pg_catalog.pg_replication_slots
>WHERE slot_name = '';
>
>  provides a measure in Logical Replication environment of how far did (or did 
> not) Subscriber fell behind Publisher, and hence some kind of measure of how 
> much "extra" WALs is stored on the Publisher.

More or less. I also make use of the value of max_wal_size in
pg_settings to do the decision-making, value set depending on a
fraction of the size of the partition dedicated to pg_xlog. By
applying pg_size_bytes() which is new to 9.6 on top of the value
fetched from pg_settings you can make a direct comparison and decide
if a slot can be dropped or not. Make sure that things are casted to
bigint though.
-- 
Michael


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

[GENERAL] Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

[mike davis]

I’m trying to get dynamic version of the RAISE command working so that I can 
use a table of custom application error messages and codes for use by all 
developed plpgsql functions. In this way the customer error codes and message 
are not hard coded into code and are defined consistently in one place in the 
db.


However, I cannot get a dynamic/parameterised version of the RAISE command 
working with the USING syntax - I want to do this so that i can catch the 
raised error in an EXCEPTION block.


The following example shows a example of (working) hardcoded version:


DO

$$

DECLARE


BEGIN

  RAISE EXCEPTION 'Something is wrong' USING errcode = 'E0001';


EXCEPTION

  WHEN SQLSTATE 'E0001' THEN

 RAISE NOTICE '%','Error E0001 raised - going to do something about it';

  WHEN OTHERS THEN

 RAISE NOTICE 'OTHER ERRORS: %', sqlstate;



END

$$


Which raises and catches the custom error E0001 and returns (as expected)

NOTICE:  Error E0001 raised - going to do something about it.


Now what I am trying to achieve is as above but for the msg text and errcode to 
be retrieved from a table before issuing the RAISE EXCEPTION statement.


ie. Assume v_msg and v_sqlstate have been retrieved and contain:

v_msg = 'Something is wrong’

v_sqlstate =  ‘E0001’


The what I want to raise dynamically is:


RAISE EXCEPTION v_msg USING errcode = v_sqlstate;


and be able to use the same exception block as above in the hard coded example.


I searched and found a couple of similar examples where

RAISE EXCEPTION ’%’, i_msg

is used and works but this does not allow a custom SQLSTATE to be raised and 
trapped.


ie. The following runs ok:


DO

$$

DECLARE


v1 TEXT ;


BEGIN



  v1 := 'SOMETHING IS WRONG';

  RAISE NOTICE '%', v1;

  RAISE EXCEPTION '%', v1;


EXCEPTION

  WHEN SQLSTATE 'E0001' THEN

 RAISE NOTICE '%','Error E0001 raised - going to do something about it';

  WHEN OTHERS THEN

 RAISE NOTICE 'OTHER ERRORS: %', sqlstate;



END

$$


and returns:

NOTICE:  SOMETHING IS WRONG

NOTICE:  OTHER ERRORS: P0001


but obviously the WHEN OTHERS has caught the exception and the SQLSTATE is the 
default P0001.


So, then what i really want is similar to the above but with the USING keyword 
of RAISE being dynamic/parameterised.


So i tried the following:


DO

$$

DECLARE


v_msg TEXT := '''SOMETHING IS WRONG''';

v_sqlstate TEXT := '''E0001''';

v1 TEXT ;


BEGIN

  v1 := v_msg || ' USING errcode = ' || v_sqlstate;

  RAISE NOTICE '%', v1;

  RAISE EXCEPTION '%', v1;


EXCEPTION

  WHEN SQLSTATE 'E0001' THEN

 RAISE NOTICE '%','Error E0001 raised - going to do something about it';

  WHEN OTHERS THEN

 RAISE NOTICE 'OTHER ERRORS: %,%', sqlstate,sqlerrm;



END

$$


which returns:

NOTICE:  'SOMETHING IS WRONG' USING errcode = 'E0001'

NOTICE:  OTHER ERRORS: P0001,'SOMETHING IS WRONG' USING errcode = 'E0001'


So clearly the whole of v1 (whilst syntatically correct) is treated as the 
message and the default sqlstate of P0001 is still raised and caught by WHEN 
OTHERS.


Have tried a few other things but cannot find way to get a custom 
errcode/sqlstate to be raised except by hardcoding it ! I’m sure there must be 
a way to do this!


Any help or advice on how to achieve this very much appreciated !

Re: [GENERAL] Performance appending to an array column

[Thomas Kellerer]

Paul A Jungwirth schrieb am 21.09.2017 um 23:05:

but maybe I could write my own extension to
load regular files into Postgres arrays, sort of getting the best of
both worlds.


There is a foreign data wrapper for that:

   https://github.com/adunstan/file_text_array_fdw

but it's pretty old and seems un-maintained.









--
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] Performance appending to an array column

[Paul A Jungwirth]

> It's going to suck big-time :-(.

Ha ha that's what I thought, but thank you for confirming. :-)

> We ended up keeping
> the time series data outside the DB; I doubt the conclusion would be
> different today.

Interesting. That seems a little radical to me, but I'll consider it
more seriously now. I also tried cstore_fdw for this, but my queries
(building a 2-D histogram) were taking 4+ seconds, compared to 500ms
using arrays. Putting everything into regular files gives up filtering
and other SQL built-ins, but maybe I could write my own extension to
load regular files into Postgres arrays, sort of getting the best of
both worlds.

Anyway, thanks for sharing your experience!

Yours,
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] Logical decoding client has the power to crash the server

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Michael Paquier
Sent: Thursday, September 21, 2017 12:33 AM
To: Meel Velliste 
Cc: PostgreSQL mailing lists 
Subject: Re: [GENERAL] Logical decoding client has the power to crash the server

On Thu, Sep 21, 2017 at 1:09 PM, Meel Velliste  wrote:
> In this situation, neither us, nor our customer has the power to 
> install the required monitoring of pg_xlog. The database hosting 
> provider would have to do it. In most cases (e.g. Amazon RDS) the 
> hosting provider does provide a way of monitoring overall disk usage, 
> which may be good enough. But I am thinking it would make sense for 
> postgres to have default, built-in monitoring that drops all the slots 
> when pg_xlog gets too full (based on some configurable limit). 
> Otherwise everybody has to build their own monitoring and I imagine 
> 99% of them would want the same behavior. Nobody wants their database 
> to fail just because some client was not reading the slot.

(Please avoid top-posting, this breaks the logic of the thread and this is 
contrary to the practices of the Postgres mailing lists)

Note that on-disk lookup is not strictly necessary. If you know max_wal_size, 
pg_current_wal_lsn (or pg_last_wal_receive_lsn if working on a standby) and the 
restart_lsn of the slots that's enough.
If you don't have privileges sufficient to see that, well I guess that you will 
need to review the access permissions to your instance.
Postgres 9.6 offers better access control to system functions, so you could be 
granted access to just those resources to be fine using a SQL session.
--
Michael

__

I think the difference between pg_current_wal_lsn() and confirmed_flush_lsn 
form pg_catalog.pg_replication_slots for specific replication slot:

SELECT (pg_current_wal_lsn() - confirmed_flush_lsn) AS lsn_distance   
   FROM pg_catalog.pg_replication_slots
   WHERE slot_name = '';

 provides a measure in Logical Replication environment of how far did (or did 
not) Subscriber fell behind Publisher, and hence some kind of measure of how 
much "extra" WALs is stored on the Publisher.

Regards,
Igor Neyman


-- 
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] Performance appending to an array column

[Tom Lane]

Paul A Jungwirth  writes:
> I'm considering a table structure where I'd be continuously appending
> to long arrays of floats (10 million elements or more). Keeping the
> data in arrays gives me much faster SELECT performance vs keeping it
> in millions of rows.

> But since these arrays keep growing, I'm wondering about the UPDATE
> performance.

It's going to suck big-time :-(.  You'd be constantly replacing all
of a multi-megabyte toasted field.  Even if the UPDATE speed per se
seemed tolerable, this would be pretty nasty in terms of the
vacuuming overhead and/or bloat it would impose.

My very first use of Postgres, twenty years ago, involved time series
data which perhaps is much like what you're doing.  We ended up keeping
the time series data outside the DB; I doubt the conclusion would be
different today.  I seem to recall having heard about a commercial fork
of PG that is less bad for this type of data, but the community code
is not the weapon you want.

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

[GENERAL] Performance appending to an array column

[Paul A Jungwirth]

I'm considering a table structure where I'd be continuously appending
to long arrays of floats (10 million elements or more). Keeping the
data in arrays gives me much faster SELECT performance vs keeping it
in millions of rows.

But since these arrays keep growing, I'm wondering about the UPDATE
performance. I was reading this commit message about improving
performance of *overwriting* individual array elements, and I was
wondering if there is anything similar for growing an array column?:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1dc5ebc9077ab742

Is there a faster way to append to an array than just this?:

UPDATE measurements
SET vals = vals || ARRAY[5.0, 4.2, 9.9]::float[]
;

Thanks!
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] a JOIN to a VIEW seems slow

[Merlin Moncure]

On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman  wrote:
> On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
>>
>> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman 
>> > wrote:
>> >
>>
>> I did not get any response to this, but I am still persevering, and feel
>>
>> that I am getting closer. Instead of waiting 26 minutes for a result, I
>>
>> realise that I can learn a lot by using EXPLAIN. This is what I have found
>>
>> out.
>>
>
>
>>
> Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute
> query?
>>
>
>
> I’m working on it, but my machine is playing up and it is getting late, so I
> will try again tomorrow.
>
> A passing comment – the 26 minute query is more complex, so will need some
> explaining (no pun intended). I was hoping that my simplified example would
> illustrate what I think is the problem.
>
> Anyway, here is the query -
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_30 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30'
> THEN q.balance ELSE 0 END
> ) AS "balance_60 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31'
> THEN q.balance ELSE 0 END
> ) AS "balance_90 AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
> ) AS "balance_120 AS [DECTEXT]"
> FROM
> (SELECT
> due_trans.cust_row_id,
> due_trans.tran_date,
> trans_due.amount_cust +
> COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
> FROM prop.ar_trans_alloc trans_alloc
> LEFT JOIN prop.ar_trans alloc_trans ON
> alloc_trans.tran_type = trans_alloc.tran_type
> AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
> WHERE trans_alloc.due_row_id = trans_due.row_id
> AND alloc_trans.tran_date <= '2015-09-30'
> ), 0)
> AS balance
> FROM prop.ar_trans_due trans_due
> LEFT JOIN prop.ar_trans due_trans ON
> due_trans.tran_type = trans_due.tran_type
> AND due_trans.tran_row_id = trans_due.tran_row_id
> WHERE due_trans.tran_date <= '2015-09-30'
> ) AS q
> GROUP BY q.cust_row_id
> ORDER BY q.cust_row_id;
>
> I will report back with the EXPLAIN ANALYSE tomorrow.

Thank you.  It's a minor point, but take a look at the FILTER syntax here:
https://www.postgresql.org/docs/9.4/static/sql-expressions.html#SYNTAX-AGGREGATES

For example,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",

Could be rewritten as:
SUM(q.balance) FILTER (WHERE  q.tran_date > '2015-08-31')
  AS "balance_curr AS [DECTEXT]",

Besides being more terse and clear, FILTER expressions in my
experience tend to be somewhat faster than aggregations over CASE
statements.

merlin


-- 
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] a JOIN to a VIEW seems slow

[Frank Millman]

On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> 
> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman  wrote:
> >
> > I did not get any response to this, but I am still persevering, and feel
> > that I am getting closer. Instead of waiting 26 minutes for a result, I
> > realise that I can learn a lot by using EXPLAIN. This is what I have found
> > out.
> 
 > Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute 
 > query?
> 
 
I’m working on it, but my machine is playing up and it is getting late, so I 
will try again tomorrow.
A passing comment – the 26 minute query is more complex, so will need some 
explaining (no pun intended). I was hoping that my simplified example would 
illustrate what I think is the problem.
Anyway, here is the query -
SELECT q.cust_row_id,
SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
) AS "balance_curr AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-08-31' AND q.tran_date > '2015-07-31' THEN 
q.balance ELSE 0 END
) AS "balance_30 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-07-31' AND q.tran_date > '2015-06-30' THEN 
q.balance ELSE 0 END
) AS "balance_60 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-06-30' AND q.tran_date > '2015-05-31' THEN 
q.balance ELSE 0 END
) AS "balance_90 AS [DECTEXT]",
SUM(CASE WHEN q.tran_date <= '2015-05-31' THEN q.balance ELSE 0 END
) AS "balance_120 AS [DECTEXT]"
FROM
(SELECT 
due_trans.cust_row_id,
due_trans.tran_date,
trans_due.amount_cust +
COALESCE((SELECT SUM(trans_alloc.alloc_cust+trans_alloc.disc_cust)
FROM prop.ar_trans_alloc trans_alloc
LEFT JOIN prop.ar_trans alloc_trans ON
alloc_trans.tran_type = trans_alloc.tran_type
AND alloc_trans.tran_row_id = trans_alloc.tran_row_id
WHERE trans_alloc.due_row_id = trans_due.row_id
AND alloc_trans.tran_date <= '2015-09-30'
), 0)
AS balance
FROM prop.ar_trans_due trans_due
LEFT JOIN prop.ar_trans due_trans ON
due_trans.tran_type = trans_due.tran_type
AND due_trans.tran_row_id = trans_due.tran_row_id
WHERE due_trans.tran_date <= '2015-09-30'
) AS q
GROUP BY q.cust_row_id
ORDER BY q.cust_row_id;

I will report back with the EXPLAIN ANALYSE tomorrow.

Frank

Re: [GENERAL] VM-Ware Backup of VM safe?

[George Neuner]

On Wed, 20 Sep 2017 20:24:05 +0200, "Klaus P. Pieper"
 wrote:

>> Von: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
>> ow...@postgresql.org] Im Auftrag von George Neuner
>>
>> But VSS is needed only to copy VM files *while* they are in
>> use.  If you snapshot the VM, the snapshot files then are read-only
>> and can be freely copied.  As long as the backup avoids the 
>> currently active files, there is no danger.
>
>But if you take a snapshot without VSS writer functions, I tend 
>to believe that there is a serious potential that relevant 
>information is still only in RAM and not written to disk. This
> would get lost in the snapshot.

I can't speak for all VM managers, but Vmware's standard static
snapshots *do* capture both the memory and power states of the
machine.  If a snapshot is taken while a machine is running,
restarting from that snapshot is the same as if the machine woke up
from suspension.


>I may be wrong, but my understanding of a VSS writer is that all
>transaction and log files are flushed to disk prior tot he snapshot.

You understanding is correct - but I think you are maybe misapplying
it to this case.  VSS operates in the *host*, not in the virtual
machine.  And VSS is purely a Windows mechanism - it does not apply in
Unix or Linux.

VSS is a protocol to enable backing up open application files without
disruptions like share locks.  A backup program can request an aware
application to produce demand snapshots of its open files.  The backup
then copies the snapshots while the application continues to use the
original files.  VSS snapshots themselves are ephemeral: they are
unnamed temporary files that are deleted when closed.


In principle, a VM manager could respond to a VSS request by signaling
aware applications in running VMs to dump state (if relevant).  But
that would only make a VSS snapshot of the machine's "disk" a little
more up to date than one taken statically via the standard mechanism.
And it requires that applications running in the VM, under whatever
guest OS, know how to catch and respond to the VM manager's signal.

In reality, the VSS aware VM managers I know of [including Vmware]
don't do that.  Instead, when requested by backup, they simply take ar
standard snapshot of each running machine.  But instead of saving the
snapshots statically, they create VSS ephemeral snapshots that
disappear when the backup is finished with them.


The long-winded point is that you don't need VSS unless your host is
running Windows, you want to backup machines while they are running,
and you want the backups to be as "fresh" as possible.


Bringing this discussion back to Postgresql, there is no difference
between restoring your database from a saved backup vs rolling back
the virtual machine running Postgresql to a saved snapshot.  The end
result is you have lost whatever was done after the save point.


If you are happy with your VM manager's standard snapshots, there is
no need for additional complexity.  My point initially was simply that
the VM manager's snapshot mechanism saves a disk image, which will
just as happily preserve a damaged disk as a good one.

To be safe(st) I think it's smart to back up your databases using
Postgresql's own mechanisms in addition to backing up your VMs.


George



-- 
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] hard parse?

[Tom Lane]

"David G. Johnston"  writes:
> On Thu, Sep 21, 2017 at 5:48 AM, Peter Koukoulis 
> wrote:
>> I have a query where a filter would always be negative, how many steps,
>> out these:
>> 
>> - parsing and syntax check
>> - semantic analysis
>> - transformation process (query rewrite based on system or
>> user-defined rules)
>> - query optimization
>> - execution
>> 
>> would be performed or not? Also, where in the documentation can I found
>> out which of the above phases would be performed?

> All of them.

Yeah.  The question is more usefully formulated as "how much will the
query optimizer collapse a query with a constant-false condition"?
You can answer that with EXPLAIN, eg.

regression=# create table test1 (x int, y int);
CREATE TABLE
regression=# explain select x,y from test1 where 1=0;
QUERY PLAN
--
 Result  (cost=0.00..0.00 rows=0 width=8)
   One-Time Filter: false
(2 rows)

In this case the answer is "pretty far" --- you get a valid but
dummy plan, which will just exit without returning any rows.

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

Re: [GENERAL] hard parse?

[David G. Johnston]

On Thu, Sep 21, 2017 at 5:48 AM, Peter Koukoulis 
wrote:

> Hi
>
> I have a query where a filter would always be negative, how many steps,
> out these:
>
>- parsing and syntax check
>- semantic analysis
>- transformation process (query rewrite based on system or
>user-defined rules)
>- query optimization
>- execution
>
> would be performed or not? Also, where in the documentation can I found
> out which of the above phases would be performed?
>
> For example, for a query such as the following:
>
> select x,y from test1 where 1=0;
>
>
I'm inferring behavior here but...​

​All of them.  You are still going to get a result set with zero records
and the correct column structure.  i.e., "Execution".  None of the other
stuff can be skipped in getting to engine to that point.  With a "always
false" predicate and that simple of a query structure most of the other
stuff, including execution, is probably performed is seemingly zero time
but it still has to work through that step of the process - if nothing else
than to move through an if-branch to decide that nothing material needs to
be done.

David J.
​

Re: [GENERAL] a JOIN to a VIEW seems slow

[Merlin Moncure]

On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman  wrote:
> On 2017-09-18 Frank Millman wrote:
>>
>> Here are the timings for running the query on identical data sets using
>> Postgresql, Sql Server, and Sqlite3 -
>>
>> PostgreSQL -
>> Method 1 - 0.28 sec
>> Method 2 – 1607 sec, or 26 minutes
>>
>> Sql Server -
>> Method 1 – 0.33 sec
>> Method 2 – 1.8 sec
>>
>> Sqlite3 -
>> Method 1 – 0.15 sec
>> Method 2 – 1.0 sec
>>
>> It seems that Sql Server and Sqlite3 are able to analyse the ‘join’, and
>> execute an indexed read against the underlying physical tables.
>>
>
> I did not get any response to this, but I am still persevering, and feel
> that I am getting closer. Instead of waiting 26 minutes for a result, I
> realise that I can learn a lot by using EXPLAIN. This is what I have found
> out.

Something is not adding up here.  Can you EXPLAIN ANALYZE the 26 minute query?

merlin


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

[GENERAL] hard parse?

[Peter Koukoulis]

Hi

I have a query where a filter would always be negative, how many steps, out
these:

   - parsing and syntax check
   - semantic analysis
   - transformation process (query rewrite based on system or user-defined
   rules)
   - query optimization
   - execution

would be performed or not? Also, where in the documentation can I found out
which of the above phases would be performed?

For example, for a query such as the following:

select x,y from test1 where 1=0;

Thanks
P

[GENERAL] EDB to PST Converter

[johncarter]

EDB to PST Converter    repairs corrupt or damaged
Exchange EDB files and restores the mailboxes into Outlook PST file. You can
easily view Mailbox data from the PST files using MS Outlook application. It
supports MS Exchange Server Recovery for 2003, 2007, 2010 and 2013 corrupt
and unmounted database.

Recovers Calendar, Attachments, Drafts etc., from the damaged EDB files

EDB to PST  Exchange server Recovery    tool helps you
to recover mails, images, attachments, drafts, calendar, journals,
appointments, tasks, notes, etc. from the damaged EDB files. Its ease of use
GUI enables you to preview recovered mail items.

- Recover / Restore only required Mailboxes
- EdbMails capability of granular / brick-level mailbox EDB to PST
conversion support, will help you to recovery any desired Mailboxes data.
- Automatic handling of PST file size limit
- If the Outlook PST file reaches size limit then, EDB to PST Converter will
automatically split the PST file into multiple files based on the size
limitation of the Outlook PST.
- Recover Deleted Mailboxes
- Edb to PST converter can easily recover mailboxes, which have been deleted
knowingly or unknowingly.
- Recover and Save as EML, MSG format
- In addition to saving the recovered data as PST file, it also allows you
to save the recovered data as MSG, EML formats.

http://edbmails.com   



-
Regards,
EdbMails 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


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

[GENERAL] Convert OST to PST

[johncarter]

For the large bunch of Outlook PST files, You have to go for some OST to PST
Recovery Tool. If you have no idea or still confuse which one to go for,
then you may try  EdbMails OST TO PST Converter
  .
The OST to PST Converter Tool exhibit some of the unmatchable features.

Some of its Features are

- Repairs Corrupted OST files
- Ultra-fast export of mails from OST to PST
- Allow Saving of OST files in different Format

For demo version visit: 
https://www.edbmails.com/pages/ost-to-pst-converter-for-ost-to-pst.html
  
For more information visit:  https://www.edbmails.com
  



-
Regards,
EdbMails 
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html


-- 
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] 10 beta 4 foreign table partition check constraint broken?

[Paul Jones]

On Thu, Sep 21, 2017 at 02:59:21PM +0900, Michael Paquier wrote:

/tmp/mutt-mayon-1000-26043-945be079d938129298
> On Fri, Sep 15, 2017 at 10:43 PM, Paul Jones  wrote:
> > Is this a bug in Postgres 10b4?  Looks like neither partition ranges
> > nor check constraints are honored in 10b4 when inserting into
> > partitions that are foreign tables.
> 
> Here is what you are looking for in the documentation:
> https://www.postgresql.org/docs/10/static/sql-createforeigntable.html
> Constraints on foreign tables (such as CHECK or NOT NULL clauses) are
> not enforced by the core PostgreSQL system, and most foreign data
> wrappers do not attempt to enforce them either; that is, the
> constraint is simply assumed to hold true. There would be little point
> in such enforcement since it would only apply to rows inserted or
> updated via the foreign table, and not to rows modified by other
> means, such as directly on the remote server. Instead, a constraint
> attached to a foreign table should represent a constraint that is
> being enforced by the remote server.

Thank you for the pointer... it is clear that I just didn't read far enough.

> 
> > Here is a nearly shovel-ready example.  Just replace with your
> > servers/passwords.
> >
> > -- --
> > -- Server 2
> > -- --
> >
> > CREATE DATABASE cluster;
> > \c cluster
> >
> > CREATE TABLE foo_1 (
> > id  INT NOT NULL,
> > nameTEXT
> > );
> 
> So here I think that you should add a CHECK constraint to this table,
> and that the behavior of your example works as expected.

I will try this, thanks!

> -- 
> Michael

.


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