Re: [GENERAL] how to monitor the progress of really large bulk operations?

[Pavel Stehule]

2016-09-28 6:13 GMT+02:00 Pavel Stehule :

> Hi
>
> 2016-09-27 23:03 GMT+02:00 Mike Sofen :
>
>> Hi gang,
>>
>>
>>
>> On PG 9.5.1, linux, I’m running some large ETL operations, migrate data
>> from a legacy mysql system into PG, upwards of 250m rows in a transaction
>> (it’s on a big box).  It’s always a 2 step operation – extract raw mysql
>> data and pull it to the target big box into staging tables that match the
>> source, the second step being read the landed dataset and transform it into
>> the final formats, linking to newly generated ids, compressing big subsets
>> into jsonb documents, etc.
>>
>>
>>
>> While I could break it into smaller chunks, it hasn’t been necessary, and
>> it doesn’t eliminate my need:  how to view the state of a transaction in
>> flight, seeing how many rows have been read or inserted (possible for a
>> transaction in flight?), memory allocations across the various PG
>> processes, etc.
>>
>>
>>
>> Possible or a hallucination?
>>
>>
>>
>> Mike Sofen (Synthetic Genomics)
>>
>
> some years ago I used a trick http://okbob.blogspot.cz/2014/
> 09/nice-unix-filter-pv.html#links
>

pltoolbox has counter function
https://github.com/okbob/pltoolbox/blob/master/utils.c

pavel=# insert into omega2 select (x.xx).*
   from (select pst.counter(omega,20, true) xx
from omega
) x;
NOTICE:  processed 20 rows, current value is '(5,8)'
NOTICE:  processed 20 rows, current value is '(5,8)'
NOTICE:  processed 40 rows, current value is '(6,8)'
NOTICE:  processed 40 rows, current value is '(6,8)'
NOTICE:  processed 60 rows, current value is '(7,8)'
NOTICE:  processed 60 rows, current value is '(7,8)'
NOTICE:  processed 80 rows, current value is '(1,8)'
NOTICE:  processed 80 rows, current value is '(1,8)'
NOTICE:  processed 100 rows, current value is '(5,8)'
NOTICE:  processed 100 rows, current value is '(5,8)'




>
>
> Regards
>
> Pavel
>
>

Re: [GENERAL] how to monitor the progress of really large bulk operations?

[Pavel Stehule]

Hi

2016-09-27 23:03 GMT+02:00 Mike Sofen :

> Hi gang,
>
>
>
> On PG 9.5.1, linux, I’m running some large ETL operations, migrate data
> from a legacy mysql system into PG, upwards of 250m rows in a transaction
> (it’s on a big box).  It’s always a 2 step operation – extract raw mysql
> data and pull it to the target big box into staging tables that match the
> source, the second step being read the landed dataset and transform it into
> the final formats, linking to newly generated ids, compressing big subsets
> into jsonb documents, etc.
>
>
>
> While I could break it into smaller chunks, it hasn’t been necessary, and
> it doesn’t eliminate my need:  how to view the state of a transaction in
> flight, seeing how many rows have been read or inserted (possible for a
> transaction in flight?), memory allocations across the various PG
> processes, etc.
>
>
>
> Possible or a hallucination?
>
>
>
> Mike Sofen (Synthetic Genomics)
>

some years ago I used a trick
http://okbob.blogspot.cz/2014/09/nice-unix-filter-pv.html#links

Regards

Pavel

Re: [GENERAL] isnull() function in pgAdmin3

[dudedoe01]

The tables I migrated from MySQL into postgreSQL have exactly the same amount
of rows of data so the problem is inside the view being created.



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923164.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] isnull() function in pgAdmin3

[dudedoe01]

Thanks Kevin, Igor and Adrian. In MySQL with the isnull() function I get all
three values RPG INV, Owner Inventory, and Builder Inventory showed up in
the view while when I do the is null function in postgreSQL I only see
Builder Inventory and Owner Inventory show up in the view. I don't know why
the RPG_INV is not showing up.

In MySQL:

Owner Inventory is 4481 rows of data
Builder Inventory is 1312 rows of data
RPG_Inv is 374 rows of data

gives the total of 6167 rows 

In postgreSQL:

Owner Inventory is 4521 rows of data
Builder inventory is 1646 rows of data

gives the total of 6167 rows

create view si_model as select
c."DCAD_Prop_ID" as DCAD_ID,
m."Address" as Address,
m."Addition" as Addition,
m."Block" as Block,
m."Lot" as Lot,
m."Lot_Size" as Lot_Size,
m."Lot_Type" as Lot_Type,
l."Funding_Date" as Lot_Sale_Date,
h."Actual_Close_Date" as Home_Closing_Date,
m."District" as District,
g."nhs_builder" as Builder,
g."nhs_sale_date" as NHSContractDate,
'' as "Banks & Ind. Owned Lots",
'' as "Repurchased",
m."Reserved_Lots" as Reserved,
d."Permit__" as "Permit #",
d."Permit_Date" as "Permit Date",
d."Foundation_Date" as "Foundation Date",
d."Frame_Date" as "Frame Date",
d."HCS" as HCS,
'' as "Notes_Comments",
l."Lot_Status" as "Lot Funding Status",
'' as "Property Description",
'' as "GIS Map",
d."Project_ID" as Project_ID,
(case when 
((l."Funding_Date" = '') and ((h."Actual_Close_Date") is null 
or (h."Actual_Close_Date" = ''))) then 'RPG Inventory' 
when 
(((l."Funding_Date") is null or (l."Funding_Date" <> '')) 
and ((h."Actual_Close_Date" = '') or
(h."Actual_Close_Date") is null)) 
then 
'Builder Inventory' 
else 'Owner Occupied' 
end) AS "Lot_Status", 

((case when c."DCAD_Prop_ID" = m."DCAD_Prop_ID" then 'YES' else '' end)) as
"Home Sale",
((case when m."Address" =  s."Address_of_Inventory_Home" then 'C Spec' else
'' end)) as "Current Specs",
((case when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Build' then 'Build' 
when g."nhs_address" = m."Address" and g."nhs_can" = 'false' and
g."nhs_build_spec" = 'Spec' then 'Spec' else '' end))
as "Build/Spec" 
from "Calculations" c 
left join "MasterLotList" m on ((c."DCAD_Prop_ID" = m."DCAD_Prop_ID"))
left join "HomeClosings" h on ((h."Address" = m."Address"))
left join "GrossNewHomeSales" g on ((g."nhs_address" = m."Address"))
left join "HCSTable" d on ((d."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "LotSales" l on ((l."DCAD_Prop_ID" = c."DCAD_Prop_ID"))
left join "CurrentSpecs" s on ((s."Address_of_Inventory_Home" =
m."Address"))

Any help provided would be greatly appreciated.



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122p5923162.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [HACKERS] Re: [GENERAL] inconsistent behaviour of set-returning functions in sub-query with random()

[Tom Lane]

Tom van Tilburg  writes:
> Good to know and I agree that it is not an urgent case.
> I think this practice might be more common in the POSTGIS community where
> there are plenty of set-returning-functions used in this way. My use was
> taking a random sample of a pointcloud distrubution.

Fix pushed to HEAD only.  Thanks for the report!

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] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Tue, Sep 27, 2016 at 2:31 PM, Patrick B  wrote:

> 2016-09-28 10:25 GMT+13:00 Patrick B :
>
>>
>> Actually I can't use name_last or name_first because some of the rows
> have name_last/name_first = null
>
> I'm inserting more columns that I shown:
>
> CREATE TABLE
>> public.not_monthly
>> (
>> id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
>> NULL,
>> clientid BIGINT,
>> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
>> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
>> company_name CHARACTER VARYING(80)
>> );
>
>
>
> but the only value that is commun between table_1 and table_2 is the
> clientid and c_id.
> Clientid is the same for all the rows
> c_Id is the column I need to update from the inserted on table_1
>
> So.. not many options here
>

​​

​ALTER TABLE public.not_monthly ADD COLUMN c_id bigint NULL;

UPDATE public.not_monthly SET c_id = next_val('c_id_sequence')​;  --might
need a bit of futzing to make this work, but I hope you get the idea...

INSERT INTO table_1 (clientid, c_id, first_name)
SELECT client_id, c_id, first_name FROM not_monthly;

INSERT INTO table_2 (clientid, c_id, last_name)
SELECT client_id, c_id, last_name FROM not_monthly;

David J.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 10:25 GMT+13:00 Patrick B :

>
>
> 2016-09-28 10:11 GMT+13:00 Kevin Grittner :
>
>> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B 
>> wrote:
>>
>> > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into
>> table_2.c_id
>> > - This is the problem.. how can I get the inserted id from STEP2 and
>> put it
>> > into c_id respecting the order?
>>
>> For DML you need to think of the data as being unordered sets, not
>> ordered lists.  The whole concept of a relational database is that
>> related rows can be associated through their common data values.
>> You are splitting them apart and then trying to match them up again
>> to link them back together.  You will be better off if you can
>> leave the relationship intact all the way through -- perhaps by
>> adding name_last to table_1.
>>
>
> Can you give me examples please?
> Patrick
>
>
Actually I can't use name_last or name_first because some of the rows have
name_last/name_first = null

I'm inserting more columns that I shown:

CREATE TABLE
> public.not_monthly
> (
> id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
> NULL,
> clientid BIGINT,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> company_name CHARACTER VARYING(80)
> );



but the only value that is commun between table_1 and table_2 is the
clientid and c_id.
Clientid is the same for all the rows
c_Id is the column I need to update from the inserted on table_1

So.. not many options here

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 10:11 GMT+13:00 Kevin Grittner :

> On Tue, Sep 27, 2016 at 3:33 PM, Patrick B 
> wrote:
>
> > 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into
> table_2.c_id
> > - This is the problem.. how can I get the inserted id from STEP2 and put
> it
> > into c_id respecting the order?
>
> For DML you need to think of the data as being unordered sets, not
> ordered lists.  The whole concept of a relational database is that
> related rows can be associated through their common data values.
> You are splitting them apart and then trying to match them up again
> to link them back together.  You will be better off if you can
> leave the relationship intact all the way through -- perhaps by
> adding name_last to table_1.
>

Can you give me examples please?
Patrick

Re: [GENERAL] how to monitor the progress of really large bulk operations?

[Melvin Davidson]

On Tue, Sep 27, 2016 at 5:03 PM, Mike Sofen  wrote:

> Hi gang,
>
>
>
> On PG 9.5.1, linux, I’m running some large ETL operations, migrate data
> from a legacy mysql system into PG, upwards of 250m rows in a transaction
> (it’s on a big box).  It’s always a 2 step operation – extract raw mysql
> data and pull it to the target big box into staging tables that match the
> source, the second step being read the landed dataset and transform it into
> the final formats, linking to newly generated ids, compressing big subsets
> into jsonb documents, etc.
>
>
>
> While I could break it into smaller chunks, it hasn’t been necessary, and
> it doesn’t eliminate my need:  how to view the state of a transaction in
> flight, seeing how many rows have been read or inserted (possible for a
> transaction in flight?), memory allocations across the various PG
> processes, etc.
>
>
>
> Possible or a hallucination?
>
>
>
> Mike Sofen (Synthetic Genomics)
>

AFAIK, it is not currently possible to monitor the progress/status of a
query. However, I do see that this is planned for "sometime in the  future".
*https://postgresql.uservoice.com/forums/21853-general/suggestions/249745-query-progress-reported-by-percentage-in-pg-stat
*

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Kevin Grittner]

On Tue, Sep 27, 2016 at 3:33 PM, Patrick B  wrote:

> 4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into table_2.c_id
> - This is the problem.. how can I get the inserted id from STEP2 and put it
> into c_id respecting the order?

For DML you need to think of the data as being unordered sets, not
ordered lists.  The whole concept of a relational database is that
related rows can be associated through their common data values.
You are splitting them apart and then trying to match them up again
to link them back together.  You will be better off if you can
leave the relationship intact all the way through -- perhaps by
adding name_last to table_1.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] how to monitor the progress of really large bulk operations?

[Mike Sofen]

Hi gang,

 

On PG 9.5.1, linux, I'm running some large ETL operations, migrate data from
a legacy mysql system into PG, upwards of 250m rows in a transaction (it's
on a big box).  It's always a 2 step operation - extract raw mysql data and
pull it to the target big box into staging tables that match the source, the
second step being read the landed dataset and transform it into the final
formats, linking to newly generated ids, compressing big subsets into jsonb
documents, etc.

 

While I could break it into smaller chunks, it hasn't been necessary, and it
doesn't eliminate my need:  how to view the state of a transaction in
flight, seeing how many rows have been read or inserted (possible for a
transaction in flight?), memory allocations across the various PG processes,
etc.

 

Possible or a hallucination?

 

Mike Sofen (Synthetic Genomics)

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 9:23 GMT+13:00 Kevin Grittner :

> On Tue, Sep 27, 2016 at 2:59 PM, Patrick B 
> wrote:
>
> [sel is a relation which can have multiple rows; the fact that it
> is being generated in a CTE isn't relevant for purposes of the
> error.]
>
>  UPDATE table_2 SET c_id =
>  (
>    SELECT c_id
>    FROM sel
>    ORDER BY c_id
>  )
>  WHERE clientid = 124312;
>
>  ERROR:  more than one row returned by a subquery used as an expression
>
> > isn't clear what I'm trying to achieve?
>
> Nope.
>
> > That's what I need, I just want a way to do that, as the way I'm
> > doing isn't working.
>
> You are specifying that you want to assign all the "c_id" values
> from the "sel" relation to the "c_id" column in "table2" for any
> and all rows which have a "clientid" value of 124312.  Effectively
> the database is complaining that it can only store one value, not a
> set of values.  I can only guess at what you might be intending to
> ask the database to do.  Can you explain what you are trying to do?
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>


Sorry.. didn't mean to be rude... just in my thoughts  I've already
explained it well.
Let see if the more clear now:


Table has data from a CSV file"
>
> CREATE TABLE
> public.not_monthly
> (
> id BIGINT DEFAULT "nextval"('"not_monthly_id_seq"'::"regclass") NOT
> NULL,

clientid BIGINT,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );



Target table number 1 - On this table, I'll get clientid and name_first
from public.not_monthly:

> CREATE TABLE
> table_1
> (
> id BIGINT DEFAULT "nextval"('"table_1_id_seq"'::"regclass") NOT
> NULL,
> clientid BIGINT DEFAULT 0 NOT NULL,
> name_first CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );


Target table number 2 - On this table, I'll get clientid and name_last
from public.not_monthly
and c_id from table_1.id:

> CREATE TABLE
> table_2
> (
> id BIGINT DEFAULT "nextval"('"table_2_id_seq"'::"regclass") NOT
> NULL,
> c_id BIGINT, --This value must be taken from table_1 inserted sql
> clientid BIGINT DEFAULT 0 NOT NULL,
> name_last CHARACTER VARYING(80) DEFAULT ''::CHARACTER VARYING
> );




So, it would be:


*1 - Select the data*

> WITH rows AS (
> SELECT
> t1.clientid,
> t1.name_first,
> t1.name_last
> FROM
> public.not_monthly t1
> ),


*2 - Insert the data into table_1(clientid,name_first)*

> ins_table_1 AS (
> INSERT INTO public.table_1 (clientid,name_first)
> SELECT
> clientid,
> name_first
> FROM rows
> RETURNING id
> ),



*3 - Insert the data into table_2(clientid,name_last)*

> ins_table_2 AS (
> INSERT INTO public.table_2 (name_last,clientid)
> SELECT
> name_last,
> clientid
> FROM rows
> RETURNING id
> )


*4 - Get the table_1.c_id from the STEP NUMBER 2 and put it into
table_2.c_id* - This is the problem.. how can I get the inserted id from
STEP2 and put it into c_id respecting the order?


clientid is the same for all the rows.. so I can't put a WHERE using
clientid because it won't work.


Patrick

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Kevin Grittner]

On Tue, Sep 27, 2016 at 2:59 PM, Patrick B  wrote:

[sel is a relation which can have multiple rows; the fact that it
is being generated in a CTE isn't relevant for purposes of the
error.]

 UPDATE table_2 SET c_id =
 (
   SELECT c_id
   FROM sel
   ORDER BY c_id
 )
 WHERE clientid = 124312;

 ERROR:  more than one row returned by a subquery used as an expression

> isn't clear what I'm trying to achieve?

Nope.

> That's what I need, I just want a way to do that, as the way I'm
> doing isn't working.

You are specifying that you want to assign all the "c_id" values
from the "sel" relation to the "c_id" column in "table2" for any
and all rows which have a "clientid" value of 124312.  Effectively
the database is complaining that it can only store one value, not a
set of values.  I can only guess at what you might be intending to
ask the database to do.  Can you explain what you are trying to do?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Tue, Sep 27, 2016 at 1:10 PM, Igor Neyman  wrote:

>
>
> Patrick,
>
>
>
> You need to explain your problems in more “coherent” way, David suggested
> one.
>
> If you aren’t willing,  people will stop responding to your request, they
> are not obligated to read your mind.
>
>
>

​I'll put it this way - the only conclusion I can draw in reading the
provided code is that the data model is screwed up and may not be capable
of accommodating this goal.  Albeit this is based upon a incompletely
specified model...

​If you want table1 and table2 to have a 1-to-1 relationship then the
assignment of the unique key should not occur via next_val() calls in the
default expression​ of either table.  When you query "not_monthly" you need
to figure out what the new ID should be and explicitly add it to both
INSERT statements.

David J.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Patrick B
Sent: Tuesday, September 27, 2016 4:00 PM
To: David G. Johnston 
Cc: pgsql-general 
Subject: Re: [GENERAL] Update two tables returning id from insert CTE Query



2016-09-28 8:54 GMT+13:00 David G. Johnston 
>:
On Mon, Sep 26, 2016 at 9:06 PM, Patrick B 
> wrote:

I'm doing this now:


sel AS (
SELECT i.id AS c_id
FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
JOIN rows s USING (rn)
)
UPDATE table_2 SET c_id =
(
  SELECT c_id
  FROM sel
  ORDER BY c_id
)
WHERE clientid = 124312;

But I get ERROR:  more than one row returned by a subquery used as an expression


​And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working using 
multiple queries in a transaction, probably with the help of temporary tables, 
then post that self-contained working example and ask for suggestions on how to 
turn it into a single query using CTEs (if its ever worth the effort at that 
point).

David J.​




isn't clear what I'm trying to achieve? That's what I need, I just want a way 
to do that, as the way I'm doing isn't working.


Patrick,

You need to explain your problems in more “coherent” way, David suggested one.
If you aren’t willing,  people will stop responding to your request, they are 
not obligated to read your mind.

Regards,
Igor Neyman

Re: [GENERAL] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 8:54 GMT+13:00 David G. Johnston :

> On Mon, Sep 26, 2016 at 9:06 PM, Patrick B 
> wrote:
>
>>
>> I'm doing this now:
>>
>>
>> sel AS (
>>> SELECT i.id AS c_id
>>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
>>> i
>>> JOIN rows s USING (rn)
>>> )
>>> UPDATE table_2 SET c_id =
>>> (
>>>   SELECT c_id
>>>   FROM sel
>>>   ORDER BY c_id
>>> )
>>> WHERE clientid = 124312;
>>
>>
>> But I get *ERROR:  more than one row returned by a subquery used as an
>> expression*
>>
>>
> ​And this surprises you why?
>
> I'd advise you get whatever it is you are trying to accomplish working
> using multiple queries in a transaction, probably with the help of
> temporary tables, then post that self-contained working example and ask for
> suggestions on how to turn it into a single query using CTEs (if its ever
> worth the effort at that point).
>
> David J.​
>
>
>

isn't clear what I'm trying to achieve? That's what I need, I just want a
way to do that, as the way I'm doing isn't working.

Re: [GENERAL] Update two tables returning id from insert CTE Query

[David G. Johnston]

On Mon, Sep 26, 2016 at 9:06 PM, Patrick B  wrote:

>
> I'm doing this now:
>
>
> sel AS (
>> SELECT i.id AS c_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1) i
>> JOIN rows s USING (rn)
>> )
>> UPDATE table_2 SET c_id =
>> (
>>   SELECT c_id
>>   FROM sel
>>   ORDER BY c_id
>> )
>> WHERE clientid = 124312;
>
>
> But I get *ERROR:  more than one row returned by a subquery used as an
> expression*
>
>
​And this surprises you why?

I'd advise you get whatever it is you are trying to accomplish working
using multiple queries in a transaction, probably with the help of
temporary tables, then post that self-contained working example and ask for
suggestions on how to turn it into a single query using CTEs (if its ever
worth the effort at that point).

David J.​

Re: [GENERAL] Determining server load

[Israel Brewster]

On Sep 27, 2016, at 11:16 AM, John R Pierce  wrote:
> 
> On 9/27/2016 12:06 PM, Israel Brewster wrote:
>> That helps for one-time stat collection, but as I mentioned in my original 
>> message, since connections may not last long, I could be getting close to, 
>> or even hitting, my connection limit while still getting values back from 
>> those that show plenty of connections remaining, depending on how often I 
>> checked.
>> 
>> I guess what would be ideal in my mind is that whenever Postgresql logged an 
>> opened/closed connection, it also looked the *total* number of open 
>> connections at that time. I don't think that's possible, however :-)
> 
> if you stick pgbouncer in front of postgres (with a pool for each 
> user@database), I believe you CAN track the max connections via pgbouncer's 
> pool stats.

Ahh! If so, that alone would be reason enough for using pgbouncer. Thanks!

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---


> 
> 
> -- 
> john r pierce, recycling bits in santa cruz
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] Determining server load

[John R Pierce]

On 9/27/2016 12:06 PM, Israel Brewster wrote:
That helps for one-time stat collection, but as I mentioned in my 
original message, since connections may not last long, I could be 
getting close to, or even hitting, my connection limit while still 
getting values back from those that show plenty of connections 
remaining, depending on how often I checked.


I guess what would be ideal in my mind is that whenever Postgresql 
logged an opened/closed connection, it also looked the *total* number 
of open connections at that time. I don't think that's possible, 
however :-)


if you stick pgbouncer in front of postgres (with a pool for each 
user@database), I believe you CAN track the max connections via 
pgbouncer's pool stats.



--
john r pierce, recycling bits in santa cruz



--
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] Update two tables returning id from insert CTE Query

[Patrick B]

2016-09-28 0:29 GMT+13:00 Vitaly Burovoy :

> On 9/26/16, Patrick B  wrote:
> > 2016-09-27 16:22 GMT+13:00 Patrick B :
> > I'm doing this now:
> >
> > sel AS (
> >> SELECT i.id AS c_id
> >> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
> >> i
> >> JOIN rows s USING (rn)
> >> )
> >> UPDATE table_2 SET c_id =
> >> (
> >>   SELECT c_id
> >>   FROM sel
> >>   ORDER BY c_id
> >> )
> >> WHERE clientid = 124312;
> >
> >
> > But I get *ERROR:  more than one row returned by a subquery used as an
> > expression*
> >
>
> To update rows of one table by rows from another table you should use
> UPDATE ... SET ... FROM ... WHERE ...
> clause described in the docs[1] (see example around the sentence "A
> similar result could be accomplished with a join:" and note below).
>
> [1] https://www.postgresql.org/docs/devel/static/sql-update.html
> --
> Best regards,
> Vitaly Burovoy
>


 clientid is the same for all the rows
Your example doesn't work. And isn't much different than mine.

Patrick

Re: [GENERAL] Determining server load

[Israel Brewster]

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---


BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD
 

> On Sep 27, 2016, at 10:48 AM, Adrian Klaver  wrote:
> 
> On 09/27/2016 11:40 AM, Israel Brewster wrote:
>> On Sep 27, 2016, at 9:55 AM, John R Pierce  wrote:
>>> 
>>> On 9/27/2016 9:54 AM, Israel Brewster wrote:
 
 I did look at pgbadger, which tells me I have gotten as high as 62 
 connections/second, but given that most of those connections are probably 
 very short lived that doesn't really tell me anything about concurrent 
 connections.
>>> 
>>> Each connection requires a process fork of the database server, which is 
>>> very expensive.  you might consider using a connection pool such as 
>>> pgbouncer, to maintain a fixed(dynamic) number of real database 
>>> connections, and have your apps connect/disconnect to this pool.
>>> Obviously, you need a pool for each database, and your apps need to be 
>>> 'stateless' and not make or rely on any session changes to the connection 
>>> so they don't interfere with each other.   Doing this correctly can make an 
>>> huge performance improvement on the sort of apps that do (connect, 
>>> transaction, disconnect) a lot.
>> 
>> Understood. My main *performance critical* apps all use an internal 
>> connection pool for this reason - Python's psycopg2 pool, to be exact. I 
>> still see a lot of connects/disconnects, but I *think* that's psycopg2 
>> recycling connections in the background - I'm not 100% certain how the pools 
>> there work (and maybe they need some tweaking as well, i.e. setting to 
>> re-use connections more times or something). The apps that don't use pools 
>> are typically data-gathering scripts where it doesn't mater how long it 
>> takes to connect/write the data (within reason).
> 
> http://initd.org/psycopg/docs/pool.html
> 
> "Note
> 
> This pool class is mostly designed to interact with Zope and probably not 
> useful in generic applications. "
> 
> Are you using Zope?

You'll notice that note only applies to the PersistentConnectionPool, not the 
ThreadedConnectionPool (Which has a note saying that it can be safely used in 
multi-threaded applications), or the SimpleConnectionPool (which is useful only 
for single-threaded applications). Since I'm not using Zope, and do have 
multi-threaded applications, I'm naturally using the ThreadedConnectionPool :-)

> 
>> 
>> That said, it seems highly probable, if not a given, that there comes a 
>> point where the overhead of handling all those connections starts slowing 
>> things down, and not just for the new connection being made. How to figure 
>> out where that point is for my system, and how close to it I am at the 
>> moment, is a large part of what I am wondering.
>> 
>> Note also that I did realize I was completely wrong about the initial issue 
>> - it turned out it was a network issue, not a postgresql one. Still, I think 
>> my specific questions still apply, if only in an academic sense now :-)
>> 
>> ---
>> Israel Brewster
>> Systems Analyst II
>> Ravn Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7293
>> ---
>> 
>> 
>>> 
>>> 
>>> 
>>> --
>>> john r pierce, recycling bits in santa cruz
>>> 
>>> 
>>> 
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>> 
>> 
>> 
> 
> 
> -- 
> 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


-- 
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] Determining server load

[Adrian Klaver]

On 09/27/2016 12:01 PM, Israel Brewster wrote:


---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---







On Sep 27, 2016, at 10:48 AM, Adrian Klaver  wrote:

On 09/27/2016 11:40 AM, Israel Brewster wrote:

On Sep 27, 2016, at 9:55 AM, John R Pierce  wrote:


On 9/27/2016 9:54 AM, Israel Brewster wrote:


I did look at pgbadger, which tells me I have gotten as high as 62 
connections/second, but given that most of those connections are probably very 
short lived that doesn't really tell me anything about concurrent connections.


Each connection requires a process fork of the database server, which is very 
expensive.  you might consider using a connection pool such as pgbouncer, to 
maintain a fixed(dynamic) number of real database connections, and have your 
apps connect/disconnect to this pool.Obviously, you need a pool for each 
database, and your apps need to be 'stateless' and not make or rely on any 
session changes to the connection so they don't interfere with each other.   
Doing this correctly can make an huge performance improvement on the sort of 
apps that do (connect, transaction, disconnect) a lot.


Understood. My main *performance critical* apps all use an internal connection 
pool for this reason - Python's psycopg2 pool, to be exact. I still see a lot 
of connects/disconnects, but I *think* that's psycopg2 recycling connections in 
the background - I'm not 100% certain how the pools there work (and maybe they 
need some tweaking as well, i.e. setting to re-use connections more times or 
something). The apps that don't use pools are typically data-gathering scripts 
where it doesn't mater how long it takes to connect/write the data (within 
reason).


http://initd.org/psycopg/docs/pool.html

"Note

This pool class is mostly designed to interact with Zope and probably not useful in 
generic applications. "

Are you using Zope?


You'll notice that note only applies to the PersistentConnectionPool, not the 
ThreadedConnectionPool (Which has a note saying that it can be safely used in 
multi-threaded applications), or the SimpleConnectionPool (which is useful only 
for single-threaded applications). Since I'm not using Zope, and do have 
multi-threaded applications, I'm naturally using the ThreadedConnectionPool :-)


Oops, did not catch that.








That said, it seems highly probable, if not a given, that there comes a point 
where the overhead of handling all those connections starts slowing things 
down, and not just for the new connection being made. How to figure out where 
that point is for my system, and how close to it I am at the moment, is a large 
part of what I am wondering.

Note also that I did realize I was completely wrong about the initial issue - 
it turned out it was a network issue, not a postgresql one. Still, I think my 
specific questions still apply, if only in an academic sense now :-)

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---






--
john r pierce, recycling bits in santa cruz



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







--
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





--
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

Re: [GENERAL] Determining server load

[Israel Brewster]

On Sep 27, 2016, at 10:55 AM, Melvin Davidson  wrote:
> 
> 
> 
> On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster  > wrote:
>> >I'm still curious as to how I can track concurrent connections, ...
>> 
>> Have you considered enabling the following  in postgresql.conf?
>> log_connections=on
>> log_disconnections=on
>> 
>> It will put a bit of a bloat in you postgres log, but it will all allow you 
>> extract connects/disconnects over a time range. That should allow you
>> to determine concurrent connections during that that.
> 
> I do have those on, and I could write a parser that scans through the logs 
> counting connections and disconnections to give a number of current 
> connections at any given time. Trying to make it operate "in real time" would 
> be interesting, though, as PG logs into different files by day-of-the-week 
> (at least, with the settings I have), rather than into a single file that 
> gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, 
> unfortunately, only seems to track connections per second and not consecutive 
> connections), already existed, or that there was some way to have the 
> database itself track this metric. If not, well, I guess that's another 
> project :)
> 
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293 
> ---
> 
>> 
>> 
>> Melvin Davidson
>> I reserve the right to fantasize.  Whether or not you 
>> wish to share my fantasy is entirely up to you. 
> 
> 
> Does this help?
> 
> --Total concurrent connections
> SELECT COUNT(*)
>   FROM pg_stat_activity;
> 
> --concurrent connections by user
> SELECT usename,
>count(*)
>  FROM pg_stat_activity
> GROUP BY 1
> ORDER BY 1;
> 
> --concurrent connections by database
> SELECT datname,
>usename,
>count(*)
>  FROM pg_stat_activity
> GROUP BY 1, 2
> ORDER BY 1, 2;
> 
> -- database connections by user
> SELECT usename,
>datname,
>count(*)
>  FROM pg_stat_activity
> GROUP BY 1, 2
> ORDER BY 1, 2;
> 
> -- 
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you 
> wish to share my fantasy is entirely up to you. 

That helps for one-time stat collection, but as I mentioned in my original 
message, since connections may not last long, I could be getting close to, or 
even hitting, my connection limit while still getting values back from those 
that show plenty of connections remaining, depending on how often I checked.

I guess what would be ideal in my mind is that whenever Postgresql logged an 
opened/closed connection, it also looked the *total* number of open connections 
at that time. I don't think that's possible, however :-)

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---

Re: [GENERAL] Determining server load

[Melvin Davidson]

On Tue, Sep 27, 2016 at 2:46 PM, Israel Brewster 
wrote:

> >I'm still curious as to how I can track concurrent connections, ...
>>
>
> Have you considered enabling the following  in postgresql.conf?
> log_connections=on
> log_disconnections=on
>
> It will put a bit of a bloat in you postgres log, but it will all allow
> you extract connects/disconnects over a time range. That should allow you
> to determine concurrent connections during that that.
>
>
> I do have those on, and I could write a parser that scans through the logs
> counting connections and disconnections to give a number of current
> connections at any given time. Trying to make it operate "in real time"
> would be interesting, though, as PG logs into different files by
> day-of-the-week (at least, with the settings I have), rather than into a
> single file that gets rotated out. I was kind of hoping such a tool, such
> as pgbadger (which, unfortunately, only seems to track connections per
> second and not consecutive connections), already existed, or that there was
> some way to have the database itself track this metric. If not, well, I
> guess that's another project :)
>
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293
> ---
>
>
>
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>
>
>


*Does this help?*

*--Total concurrent connections*


















*SELECT COUNT(*)  FROM pg_stat_activity;--concurrent connections by
userSELECT usename,   count(*) FROM pg_stat_activityGROUP BY 1ORDER BY
1;--concurrent connections by databaseSELECT datname,   usename,
count(*) FROM pg_stat_activityGROUP BY 1, 2ORDER BY 1, 2;*

*-- database connections by user*








*SELECT usename,   datname,   count(*) FROM pg_stat_activityGROUP
BY 1, 2ORDER BY 1, 2;-- *
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Determining server load

[Israel Brewster]

On Sep 27, 2016, at 10:55 AM, Jonathan Vanasco  wrote:
> 
> 
> On Sep 27, 2016, at 2:46 PM, Israel Brewster wrote:
> 
>> I do have those on, and I could write a parser that scans through the logs 
>> counting connections and disconnections to give a number of current 
>> connections at any given time. Trying to make it operate "in real time" 
>> would be interesting, though, as PG logs into different files by 
>> day-of-the-week (at least, with the settings I have), rather than into a 
>> single file that gets rotated out. I was kind of hoping such a tool, such as 
>> pgbadger (which, unfortunately, only seems to track connections per second 
>> and not consecutive connections), already existed, or that there was some 
>> way to have the database itself track this metric. If not, well, I guess 
>> that's another project :)
> 
> There are a lot of postgres configs and server specific tools... but on the 
> application side and for general debugging, have you looked at statsd ?  
> https://github.com/etsy/statsd 
> 
> it's a lightweight node.js app that runs on your server and listens for UDP 
> signals, which your apps can emit for counting or timing.  We have a ton of 
> Python apps logging to it, including every postgres connection open/close and 
> error.  The overhead of clients and server is negligible.  When combined with 
> the graphite app for browsing data via charts, it becomes really useful at 
> detecting issues with load or errors stemming from a deployment  -- you just 
> look for spikes and cliffs.  We even use it to log the volume of INSERTS vs 
> SELECTS vs UPDATES being sent to postgres.
> 
> The more services/apps you run, the more useful it gets, as you can figure 
> out which apps/deployments are screwing up postgres and the exact moment 
> things went wrong.
> 

That sounds quite promising. I'll look into it. Thanks!


---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---

Re: [GENERAL] Determining server load

[Jonathan Vanasco]

On Sep 27, 2016, at 2:46 PM, Israel Brewster wrote:

> I do have those on, and I could write a parser that scans through the logs 
> counting connections and disconnections to give a number of current 
> connections at any given time. Trying to make it operate "in real time" would 
> be interesting, though, as PG logs into different files by day-of-the-week 
> (at least, with the settings I have), rather than into a single file that 
> gets rotated out. I was kind of hoping such a tool, such as pgbadger (which, 
> unfortunately, only seems to track connections per second and not consecutive 
> connections), already existed, or that there was some way to have the 
> database itself track this metric. If not, well, I guess that's another 
> project :)

There are a lot of postgres configs and server specific tools... but on the 
application side and for general debugging, have you looked at statsd ?  
https://github.com/etsy/statsd

it's a lightweight node.js app that runs on your server and listens for UDP 
signals, which your apps can emit for counting or timing.  We have a ton of 
Python apps logging to it, including every postgres connection open/close and 
error.  The overhead of clients and server is negligible.  When combined with 
the graphite app for browsing data via charts, it becomes really useful at 
detecting issues with load or errors stemming from a deployment  -- you just 
look for spikes and cliffs.  We even use it to log the volume of INSERTS vs 
SELECTS vs UPDATES being sent to postgres.

The more services/apps you run, the more useful it gets, as you can figure out 
which apps/deployments are screwing up postgres and the exact moment things 
went wrong.

Re: [GENERAL] Determining server load

[Adrian Klaver]

On 09/27/2016 11:40 AM, Israel Brewster wrote:

On Sep 27, 2016, at 9:55 AM, John R Pierce  wrote:


On 9/27/2016 9:54 AM, Israel Brewster wrote:


I did look at pgbadger, which tells me I have gotten as high as 62 
connections/second, but given that most of those connections are probably very 
short lived that doesn't really tell me anything about concurrent connections.


Each connection requires a process fork of the database server, which is very 
expensive.  you might consider using a connection pool such as pgbouncer, to 
maintain a fixed(dynamic) number of real database connections, and have your 
apps connect/disconnect to this pool.Obviously, you need a pool for each 
database, and your apps need to be 'stateless' and not make or rely on any 
session changes to the connection so they don't interfere with each other.   
Doing this correctly can make an huge performance improvement on the sort of 
apps that do (connect, transaction, disconnect) a lot.


Understood. My main *performance critical* apps all use an internal connection 
pool for this reason - Python's psycopg2 pool, to be exact. I still see a lot 
of connects/disconnects, but I *think* that's psycopg2 recycling connections in 
the background - I'm not 100% certain how the pools there work (and maybe they 
need some tweaking as well, i.e. setting to re-use connections more times or 
something). The apps that don't use pools are typically data-gathering scripts 
where it doesn't mater how long it takes to connect/write the data (within 
reason).


http://initd.org/psycopg/docs/pool.html

"Note

This pool class is mostly designed to interact with Zope and probably 
not useful in generic applications. "


Are you using Zope?



That said, it seems highly probable, if not a given, that there comes a point 
where the overhead of handling all those connections starts slowing things 
down, and not just for the new connection being made. How to figure out where 
that point is for my system, and how close to it I am at the moment, is a large 
part of what I am wondering.

Note also that I did realize I was completely wrong about the initial issue - 
it turned out it was a network issue, not a postgresql one. Still, I think my 
specific questions still apply, if only in an academic sense now :-)

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---






--
john r pierce, recycling bits in santa cruz



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







--
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

Re: [GENERAL] Determining server load

[Israel Brewster]

> >I'm still curious as to how I can track concurrent connections, ...
> 
> Have you considered enabling the following  in postgresql.conf?
> log_connections=on
> log_disconnections=on
> 
> It will put a bit of a bloat in you postgres log, but it will all allow you 
> extract connects/disconnects over a time range. That should allow you
> to determine concurrent connections during that that.

I do have those on, and I could write a parser that scans through the logs 
counting connections and disconnections to give a number of current connections 
at any given time. Trying to make it operate "in real time" would be 
interesting, though, as PG logs into different files by day-of-the-week (at 
least, with the settings I have), rather than into a single file that gets 
rotated out. I was kind of hoping such a tool, such as pgbadger (which, 
unfortunately, only seems to track connections per second and not consecutive 
connections), already existed, or that there was some way to have the database 
itself track this metric. If not, well, I guess that's another project :)

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---

> 
> 
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you 
> wish to share my fantasy is entirely up to you. 

Re: [GENERAL] isnull() function in pgAdmin3

[Kevin Grittner]

On Tue, Sep 27, 2016 at 12:04 PM, dudedoe01  wrote:

> I am trying to emulate the isnull() function used in MySQL into postreSQL.

In the future, please describe the results you are trying to achieve
or at least describe the semantics of the function from elsewhere that
you are trying to emulate.  I had no idea what the ISNLL() function of
MySQL does; on a quick web search, it looks like you can replace:
  isnull(expression)

with:

  (expression) IS NULL


> In MySQL:
>
> (case
> when
> ((`s`.`Funding_Date` = '')
> and (isnull(`s`.`Actual_Close_Date`)
> or (`s`.`Actual_Close_Date` = '')))
> then
> 'RPG_INV'
> when
> ((isnull(`s`.`Funding_Date`)
> or (`s`.`Funding_Date` <> ''))
> and ((`s`.`Actual_Close_Date` = '')
> or isnull(`s`.`Actual_Close_Date`)))
> then
> 'Builder_Inventory'
> else 'Owner_Inventory'
> end) AS `Lot_Status`,

In PostgreSQL perhaps:

(case
when
(("s"."Funding_Date" = '')
and (("s"."Actual_Close_Date") is null
or ("s"."Actual_Close_Date" = '')))
then
'RPG_INV'
when
((("s"."Funding_Date") is null
or ("s"."Funding_Date" <> ''))
and (("s"."Actual_Close_Date" = '')
or ("s"."Actual_Close_Date") is null))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS "Lot_Status",

-- 
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] isnull() function in pgAdmin3

[Adrian Klaver]

On 09/27/2016 10:04 AM, dudedoe01 wrote:

Hi,

I am trying to emulate the isnull() function used in MySQL into postreSQL. I
have tried different ways such is null but it's not producing the results
desired. I am doing a data migration from MySQL into postgreSQL and need
help with the isnull() in pgAdmin3.

Any assistance provided would be greatly appreciated.

Thanks,

In MySQL:

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`,




aklaver@test=> select 1 is null;
 ?column?
--
 f
(1 row)

aklaver@test=> select null is null;
 ?column?
--
 t

or

  ^
aklaver@test=> select 1 isnull;
 ?column?
--
 f
(1 row)

aklaver@test=> select null isnull;
 ?column?
--
 t
(1 row)




--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.





--
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

Re: [GENERAL] Determining server load

[Israel Brewster]

On Sep 27, 2016, at 9:55 AM, John R Pierce  wrote:
> 
> On 9/27/2016 9:54 AM, Israel Brewster wrote:
>> 
>> I did look at pgbadger, which tells me I have gotten as high as 62 
>> connections/second, but given that most of those connections are probably 
>> very short lived that doesn't really tell me anything about concurrent 
>> connections.
> 
> Each connection requires a process fork of the database server, which is very 
> expensive.  you might consider using a connection pool such as pgbouncer, to 
> maintain a fixed(dynamic) number of real database connections, and have your 
> apps connect/disconnect to this pool.Obviously, you need a pool for each 
> database, and your apps need to be 'stateless' and not make or rely on any 
> session changes to the connection so they don't interfere with each other.   
> Doing this correctly can make an huge performance improvement on the sort of 
> apps that do (connect, transaction, disconnect) a lot.

Understood. My main *performance critical* apps all use an internal connection 
pool for this reason - Python's psycopg2 pool, to be exact. I still see a lot 
of connects/disconnects, but I *think* that's psycopg2 recycling connections in 
the background - I'm not 100% certain how the pools there work (and maybe they 
need some tweaking as well, i.e. setting to re-use connections more times or 
something). The apps that don't use pools are typically data-gathering scripts 
where it doesn't mater how long it takes to connect/write the data (within 
reason).

That said, it seems highly probable, if not a given, that there comes a point 
where the overhead of handling all those connections starts slowing things 
down, and not just for the new connection being made. How to figure out where 
that point is for my system, and how close to it I am at the moment, is a large 
part of what I am wondering.

Note also that I did realize I was completely wrong about the initial issue - 
it turned out it was a network issue, not a postgresql one. Still, I think my 
specific questions still apply, if only in an academic sense now :-)

---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---


> 
> 
> 
> -- 
> john r pierce, recycling bits in santa cruz
> 
> 
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general



-- 
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] isnull() function in pgAdmin3

[Igor Neyman]

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of dudedoe01
Sent: Tuesday, September 27, 2016 1:05 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] isnull() function in pgAdmin3

Hi,

I am trying to emulate the isnull() function used in MySQL into postreSQL. I 
have tried different ways such is null but it's not producing the results 
desired. I am doing a data migration from MySQL into postgreSQL and need help 
with the isnull() in pgAdmin3.

Any assistance provided would be greatly appreciated.

Thanks,

In MySQL:

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`,

__


isnull(`s`.`Actual_Close_Date`) in MySQL is equivalent to:

s.actual_close_date  IS NULL  in Postgres.

What exactly didn't work for you?

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

[GENERAL] isnull() function in pgAdmin3

[dudedoe01]

Hi,

I am trying to emulate the isnull() function used in MySQL into postreSQL. I
have tried different ways such is null but it's not producing the results
desired. I am doing a data migration from MySQL into postgreSQL and need
help with the isnull() in pgAdmin3.

Any assistance provided would be greatly appreciated.

Thanks,

In MySQL:

(case
when
((`s`.`Funding_Date` = '')
and (isnull(`s`.`Actual_Close_Date`)
or (`s`.`Actual_Close_Date` = '')))
then
'RPG_INV'
when
((isnull(`s`.`Funding_Date`)
or (`s`.`Funding_Date` <> ''))
and ((`s`.`Actual_Close_Date` = '')
or isnull(`s`.`Actual_Close_Date`)))
then
'Builder_Inventory'
else 'Owner_Inventory'
end) AS `Lot_Status`,



--
View this message in context: 
http://postgresql.nabble.com/isnull-function-in-pgAdmin3-tp5923122.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


-- 
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] bitwise storage and operations

[Jonathan Vanasco]

On Sep 27, 2016, at 10:54 AM, Brian Dunavant wrote:

> db=# select 'foo' where (9 & 1) > 0;

A HA

Thank you Brian and David -- I didn't realize that you needed to do the 
comparison to the result.

(or convert the result as these work):

select 'foo' where (9 & 1)::bool;
select 'foo' where bool(9 & 1);

I kept trying to figure out how to run operators on "9"  and "1" independently 
to create a boolean result.  I either needed more coffee or less yesterday.

As a followup question...

Some searches suggested that Postgres can't use indexes of INTs for these 
comparisons, but could on bitwise string columns.

One of these tables has over 30MM rows, so I'm trying to avoid a seq scan as 
much as possible.

I thought of creating a function index that casts my column to a bitstring, and 
then tailors searches onto that. For example:

CREATE TEMPORARY TABLE example_toggle(
id int primary key,
toggle int default null
);
INSERT INTO example_toggle (id, toggle) VALUES (1, 1), (2, 2), (3, 3), 
(4, 5), (5, 8);
CREATE INDEX idx_example_toggle_toggle_bit ON 
example_toggle(cast(toggle as bit(4)));

While these selects work...

select * from example_toggle where (toggle & 1)::bool AND (toggle & 
4)::bool;
select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 
0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <> 0::bit(4));

Only about 200k items have a flag right now (out of 30MM) so I thought of using 
a partial index on the set flags.

The only way I've been able to get an index on the not null/0 used is to do the 
following:

CREATE INDEX idx_example_toggle_toggle_bit ON 
example_toggle(cast(toggle as bit(4))) WHERE toggle <> 0;

then tweak the query with 

select * from example_toggle where (toggle & 1)::bool AND (toggle & 
4)::bool AND (toggle > 0);
select * from example_toggle where (toggle::bit(4) & 1::bit(4) <> 
0::bit(4)) AND (toggle::bit(4) & 4::bit(4) <> 0::bit(4)) AND (toggle > 0);

obviously, the sample above is far too small for an index to be considered... 
but in general... is a partial index of "toggle <> 0" and then hinting with 
"toggle > 0" the best way to only index the values that are not null or 0?




-- 
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] Determining server load

[Melvin Davidson]

On Tue, Sep 27, 2016 at 2:25 PM, Israel Brewster 
wrote:

> On Sep 27, 2016, at 10:07 AM, Adrian Klaver 
> wrote:
> >
> > On 09/27/2016 09:54 AM, Israel Brewster wrote:
> >> I have a Postgresql (9.4.6) cluster that hosts several databases, used
> >> by about half-a-dozen different in-house apps. I have two servers set up
> >> as master-slave with streaming replication. Lately I've been running
> >> into an issue where one of the apps periodically can't connect to the
> >> db. The problem is always extremely short lived (less than a minute),
> >> such that by the time I can look into it, there is no issue. My
> >> *suspicion* is that I am briefly hitting the max connection limit of my
> >> server (currently set at 100). If so, I can certainly *fix* the issue
> >> easily by increasing the connection limit, but I have two questions
> >> about this:
> >
> > What does your Postgres log show around this time?
>
> So in looking further, I realized the actual error I was getting was "no
> route to host", which is obviously a networking issue and not a postgres
> issue - could not connect was only the end result. The logs then, of
> course, show normal operation. That said, now that I am thinking about it,
> I'm still curious as to how I can track concurrent connections, with the
> revised goal of simply seeing how heavily loaded my server really is, and
> when tools such as pgpool or the pgbouncer that another user mentioned
> start making sense for the number of connections I am dealing with. Thanks.
> ---
> Israel Brewster
> Systems Analyst II
> Ravn Alaska
> 5245 Airport Industrial Rd
> Fairbanks, AK 99709
> (907) 450-7293
> ---
>
> >
> >>
> >> 1) Is there some way I can track concurrent connections to see if my
> >> theory is correct? I know I can do a count(*) on pg_stat_activity to get
> >> the current number of connections at any point (currently at 45 BTW),
> >> but aside from repeatedly querying this at short intervals, which I am
> >> afraid would put undue load on the server by the time it is frequent
> >> enough to be of use, I don't know how to track concurrent connections.
> >>
> >> I did look at pgbadger, which tells me I have gotten as high as 62
> >> connections/second, but given that most of those connections are
> >> probably very short lived that doesn't really tell me anything about
> >> concurrent connections.
> >>
> >> 2) Is increasing the connection limit even the "proper" fix for this, or
> >> am I at a load point where I need to start looking at tools like pgpool
> >> or something to distribute some of the load to my hot standby server? I
> >> do realize you may not be able to answer that directly, since I haven't
> >> given enough information about my server/hardware/load, etc, but answers
> >> that tell me how to better look at the load over time and figure out if
> >> I am overloaded are appreciated.
> >>
> >> For reference, the server is running on the following hardware:
> >>
> >> 2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower
> there)
> >> 32 GB Ram total, currently with 533144k showing as "free" and 370464k of
> >> swap used
> >> 371 GB SSD RAID 10 (currently only using 40GB of space)
> >> Dual Gigabit ethernet
> >>
> >> Thanks for any advice that can be provided!
> >> ---
> >> Israel Brewster
> >> Systems Analyst II
> >> Ravn Alaska
> >> 5245 Airport Industrial Rd
> >> Fairbanks, AK 99709
> >> (907) 450-7293
> >> ---
> >>
> >>
> >>
> >>
> >>
> >
> >
> > --
> > 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

>I'm still curious as to how I can track concurrent connections, ...

Have you considered enabling the following  in postgresql.conf?
log_connections=on
log_disconnections=on

It will put a bit of a bloat in you postgres log, but it will all allow you
extract connects/disconnects over a time range. That should allow you
to determine concurrent connections during that that.


*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Determining server load

[Israel Brewster]

On Sep 27, 2016, at 10:07 AM, Adrian Klaver  wrote:
> 
> On 09/27/2016 09:54 AM, Israel Brewster wrote:
>> I have a Postgresql (9.4.6) cluster that hosts several databases, used
>> by about half-a-dozen different in-house apps. I have two servers set up
>> as master-slave with streaming replication. Lately I've been running
>> into an issue where one of the apps periodically can't connect to the
>> db. The problem is always extremely short lived (less than a minute),
>> such that by the time I can look into it, there is no issue. My
>> *suspicion* is that I am briefly hitting the max connection limit of my
>> server (currently set at 100). If so, I can certainly *fix* the issue
>> easily by increasing the connection limit, but I have two questions
>> about this:
> 
> What does your Postgres log show around this time?

So in looking further, I realized the actual error I was getting was "no route 
to host", which is obviously a networking issue and not a postgres issue - 
could not connect was only the end result. The logs then, of course, show 
normal operation. That said, now that I am thinking about it, I'm still curious 
as to how I can track concurrent connections, with the revised goal of simply 
seeing how heavily loaded my server really is, and when tools such as pgpool or 
the pgbouncer that another user mentioned start making sense for the number of 
connections I am dealing with. Thanks.
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---

> 
>> 
>> 1) Is there some way I can track concurrent connections to see if my
>> theory is correct? I know I can do a count(*) on pg_stat_activity to get
>> the current number of connections at any point (currently at 45 BTW),
>> but aside from repeatedly querying this at short intervals, which I am
>> afraid would put undue load on the server by the time it is frequent
>> enough to be of use, I don't know how to track concurrent connections.
>> 
>> I did look at pgbadger, which tells me I have gotten as high as 62
>> connections/second, but given that most of those connections are
>> probably very short lived that doesn't really tell me anything about
>> concurrent connections.
>> 
>> 2) Is increasing the connection limit even the "proper" fix for this, or
>> am I at a load point where I need to start looking at tools like pgpool
>> or something to distribute some of the load to my hot standby server? I
>> do realize you may not be able to answer that directly, since I haven't
>> given enough information about my server/hardware/load, etc, but answers
>> that tell me how to better look at the load over time and figure out if
>> I am overloaded are appreciated.
>> 
>> For reference, the server is running on the following hardware:
>> 
>> 2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower there)
>> 32 GB Ram total, currently with 533144k showing as "free" and 370464k of
>> swap used
>> 371 GB SSD RAID 10 (currently only using 40GB of space)
>> Dual Gigabit ethernet
>> 
>> Thanks for any advice that can be provided!
>> ---
>> Israel Brewster
>> Systems Analyst II
>> Ravn Alaska
>> 5245 Airport Industrial Rd
>> Fairbanks, AK 99709
>> (907) 450-7293
>> ---
>> 
>> 
>> 
>> 
>> 
> 
> 
> -- 
> 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



-- 
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] Determining server load

[Adrian Klaver]

On 09/27/2016 09:54 AM, Israel Brewster wrote:

I have a Postgresql (9.4.6) cluster that hosts several databases, used
by about half-a-dozen different in-house apps. I have two servers set up
as master-slave with streaming replication. Lately I've been running
into an issue where one of the apps periodically can't connect to the
db. The problem is always extremely short lived (less than a minute),
such that by the time I can look into it, there is no issue. My
*suspicion* is that I am briefly hitting the max connection limit of my
server (currently set at 100). If so, I can certainly *fix* the issue
easily by increasing the connection limit, but I have two questions
about this:


What does your Postgres log show around this time?



1) Is there some way I can track concurrent connections to see if my
theory is correct? I know I can do a count(*) on pg_stat_activity to get
the current number of connections at any point (currently at 45 BTW),
but aside from repeatedly querying this at short intervals, which I am
afraid would put undue load on the server by the time it is frequent
enough to be of use, I don't know how to track concurrent connections.

I did look at pgbadger, which tells me I have gotten as high as 62
connections/second, but given that most of those connections are
probably very short lived that doesn't really tell me anything about
concurrent connections.

2) Is increasing the connection limit even the "proper" fix for this, or
am I at a load point where I need to start looking at tools like pgpool
or something to distribute some of the load to my hot standby server? I
do realize you may not be able to answer that directly, since I haven't
given enough information about my server/hardware/load, etc, but answers
that tell me how to better look at the load over time and figure out if
I am overloaded are appreciated.

For reference, the server is running on the following hardware:

2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower there)
32 GB Ram total, currently with 533144k showing as "free" and 370464k of
swap used
371 GB SSD RAID 10 (currently only using 40GB of space)
Dual Gigabit ethernet

Thanks for any advice that can be provided!
---
Israel Brewster
Systems Analyst II
Ravn Alaska
5245 Airport Industrial Rd
Fairbanks, AK 99709
(907) 450-7293
---








--
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

Re: [GENERAL] Determining server load

[John R Pierce]

On 9/27/2016 9:54 AM, Israel Brewster wrote:


I did look at pgbadger, which tells me I have gotten as high as 62 
connections/second, but given that most of those connections are 
probably very short lived that doesn't really tell me anything about 
concurrent connections.


Each connection requires a process fork of the database server, which is 
very expensive.  you might consider using a connection pool such as 
pgbouncer, to maintain a fixed(dynamic) number of real database 
connections, and have your apps connect/disconnect to this pool.
Obviously, you need a pool for each database, and your apps need to be 
'stateless' and not make or rely on any session changes to the 
connection so they don't interfere with each other.   Doing this 
correctly can make an huge performance improvement on the sort of apps 
that do (connect, transaction, disconnect) a lot.




--
john r pierce, recycling bits in santa cruz



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

[GENERAL] Determining server load

[Israel Brewster]

I have a Postgresql (9.4.6) cluster that hosts several databases, used by about half-a-dozen different in-house apps. I have two servers set up as master-slave with streaming replication. Lately I've been running into an issue where one of the apps periodically can't connect to the db. The problem is always extremely short lived (less than a minute), such that by the time I can look into it, there is no issue. My *suspicion* is that I am briefly hitting the max connection limit of my server (currently set at 100). If so, I can certainly *fix* the issue easily by increasing the connection limit, but I have two questions about this:1) Is there some way I can track concurrent connections to see if my theory is correct? I know I can do a count(*) on pg_stat_activity to get the current number of connections at any point (currently at 45 BTW), but aside from repeatedly querying this at short intervals, which I am afraid would put undue load on the server by the time it is frequent enough to be of use, I don't know how to track concurrent connections.I did look at pgbadger, which tells me I have gotten as high as 62 connections/second, but given that most of those connections are probably very short lived that doesn't really tell me anything about concurrent connections.2) Is increasing the connection limit even the "proper" fix for this, or am I at a load point where I need to start looking at tools like pgpool or something to distribute some of the load to my hot standby server? I do realize you may not be able to answer that directly, since I haven't given enough information about my server/hardware/load, etc, but answers that tell me how to better look at the load over time and figure out if I am overloaded are appreciated.For reference, the server is running on the following hardware:2x 8-core Xeon E5-2630 v3 2.4 GHz processor (so plenty of horsepower there)32 GB Ram total, currently with 533144k showing as "free" and 370464k of swap used 371 GB SSD RAID 10 (currently only using 40GB of space)Dual Gigabit ethernetThanks for any advice that can be provided!
---Israel BrewsterSystems Analyst IIRavn Alaska5245 Airport Industrial RdFairbanks, AK 99709(907) 450-7293---BEGIN:VCARD
VERSION:3.0
N:Brewster;Israel;;;
FN:Israel Brewster
ORG:Frontier Flying Service;MIS
TITLE:PC Support Tech II
EMAIL;type=INTERNET;type=WORK;type=pref:isr...@frontierflying.com
TEL;type=WORK;type=pref:907-450-7293
item1.ADR;type=WORK;type=pref:;;5245 Airport Industrial Wy;Fairbanks;AK;99701;
item1.X-ABADR:us
CATEGORIES:General
X-ABUID:36305438-95EA-4410-91AB-45D16CABCDDC\:ABPerson
END:VCARD

Re: [GENERAL] bitwise storage and operations

[Brian Dunavant]

If it's in integer columns, bitwise logic works just like you would
expect it to as well.
https://www.postgresql.org/docs/current/static/functions-math.html

db=# select 'foo' where (9 & 1) > 0;
 ?column?
--
 foo
(1 row)

db=# select 'foo' where (9 & 2) > 0;
 ?column?
--
(0 rows)

Just bit-wise AND them and compare if the result is > 0.  If you use
the bitshift operator (<<) make sure you use parens to force ordering.
This is important.

On Mon, Sep 26, 2016 at 7:34 PM, David G. Johnston
 wrote:
> Please include the list in all replies.
>
> On Mon, Sep 26, 2016 at 4:14 PM, Jonathan Vanasco  wrote:
>>
>>
>> On Sep 26, 2016, at 5:04 PM, David G. Johnston wrote:
>>
>> On Mon, Sep 26, 2016 at 1:44 PM, Jonathan Vanasco 
>> wrote:
>>>
>>> The documentation doesn't have any examples for SELECT for the bitwise
>>> operators,
>>
>>
>> That shows a simple computation.  One can "SELECT" any computation and get
>> a value.
>>
>> It doesn't show a bitwise operator being used against an INT or BIT
>> column, as I further elaborated.
>
>
> I assumed a certain level of familiarity with databases and provided enough
> info to answer your main question: "what are the available bit string
> operators?".  That you can apply these operator to either constants or
> columns was knowledge I took for granted.
>
>>
>> From what I can tell so far, i need to extract and compare a substring for
>> the (reverse) index of the particular bit I want to filter on.
>
>
> B'1001' is typed bit(4)...
>
> The only requirement with a WHERE clause is that the computation must result
> in a boolean.  My example SELECT computation does just that.  It uses
> "varbit" for convenience but INT can be CAST() to BIT and the operators
> themselves should operate on any of the BIT variants.
>
> What you want is the "bit-wise AND" operator and the equality operator, both
> of which you were shown.
>
> I'd suggest you put forth your own example, filling in pseudo-code where
> needed, if you wish for more specific advice.
>
> David J.
>


-- 
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] lost synchronization with server: got message type "Z"

[Tom Lane]

"Marek Petr"  writes:
> From time to time we receive following event from application (Adobe 
> Campaign - former Neolane):
> PostgreSQL error: lost synchronization with server: got message type "Z", 
> length 0\n (iRc=-2006)

Hm.

> Could something else than network cause this event?

We've fixed bugs with related symptoms in the past.

> postgres=# select version();
> PostgreSQL 9.2.13 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 
> 20120313 (Red Hat 4.4.7-11), 64-bit

What libpq version is being used on the client side?  The most recent
related bugfix I can find in the 9.2 commit history was in libpq, and
it came out in 9.2.8.

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] Update two tables returning id from insert CTE Query

[Vitaly Burovoy]

On 9/26/16, Patrick B  wrote:
> 2016-09-27 16:22 GMT+13:00 Patrick B :
> I'm doing this now:
>
> sel AS (
>> SELECT i.id AS c_id
>> FROM (select id, row_number() OVER (ORDER BY id) AS rn FROM ins_table_1)
>> i
>> JOIN rows s USING (rn)
>> )
>> UPDATE table_2 SET c_id =
>> (
>>   SELECT c_id
>>   FROM sel
>>   ORDER BY c_id
>> )
>> WHERE clientid = 124312;
>
>
> But I get *ERROR:  more than one row returned by a subquery used as an
> expression*
>

To update rows of one table by rows from another table you should use
UPDATE ... SET ... FROM ... WHERE ...
clause described in the docs[1] (see example around the sentence "A
similar result could be accomplished with a join:" and note below).

[1] https://www.postgresql.org/docs/devel/static/sql-update.html
-- 
Best regards,
Vitaly Burovoy


-- 
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] Update two tables returning id from insert CTE Query

[Vitaly Burovoy]

On 9/26/16, Patrick B  wrote:
> Hi guys,
>
> I've got 2k rows in a table:

...

> So I'd imagine now I would do the update? How can I update table_2.c_id
> with the ins_table_1.id value?
> I'm using Postgres 9.2
>
> Thanks
> Patrick
>

Hello,

It is not possible to change one row more than once by one query.
You try to do so by inserting in ins_table_2 (it is "change" of a row)
and update the inserted row by a final query.

It is hard to understand what you want to do because your query is
very artificial: get non-unique data from a table, split it (to get
less unique data) but then "match" two non-unique data via inserted
unique identifier.

The only common hint I can give you is to use data from ins_table_1 in
SELECT part of the ins_table_2 (which should be now not CTE, but the
final query):
WITH rows AS (
SELECT ...
),
ins_table_1 AS (
INSERT INTO public.table_1 (clientid,name_first)
...
RETURNING id -- may be also clientid and name_first?
)
INSERT INTO public.table_2 (c_id, name_last,clientid)
SELECT
ins.id,
rows.name_last,
rows.clientid
FROM rows
INNER JOIN (
SELECT ..., ... OVER()... FROM ins_table_1
) ins ON (...)

Note than CTEs not have indexes and a join process is not fast (for
bigger number of rows).

-- 
Best regards,
Vitaly Burovoy


-- 
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] need approval to join forums/community

[PHANIKUMAR G]

hi Adrian,

looks my mail id is added to pgsql-general mailing lists
and able to to get mails.

thanks for your help.


Phani Kumar

On Tue, Sep 27, 2016 at 12:35 AM, Adrian Klaver 
wrote:

> On 09/23/2016 10:26 PM, PHANIKUMAR G wrote:
>
>> hi owners/admins of postgresql,
>>
>>
>> my name is phani kumar and I would like to be member of postgres
>> forums/groups/communities. I have already sent mail regarding my
>> concern, looks my request overlooked and i did not get any response.
>> Second time I am approaching you, please add my mail id
>> phanikumar...@gmail.com  so that i can
>> be part of postgres knowledge sharing groups. As we are using postgres
>> in our products it is very important for me to be part of postgres
>> community.
>>
>
> So what are you signing up for:
>
> Mailing Lists
> https://www.postgresql.org/list/
>
> or
>
> Community account:
>
> https://www.postgresql.org/account/signup/
>
> or both or something else?
>
>
> Also, how did you sign up?
>
>
>>
>> thanks
>> PhaniKumar
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] inconsistent behaviour of set-returning functions in sub-query with random()

[Tom van Tilburg]

Good to know and I agree that it is not an urgent case.
I think this practice might be more common in the POSTGIS community where
there are plenty of set-returning-functions used in this way. My use was
taking a random sample of a pointcloud distrubution.

I took the liberty to post your answer at stackexchange.

thanks,
 Tom

On Mon, 26 Sep 2016 at 21:38 Tom Lane  wrote:

> Tom van Tilburg  writes:
> > I'm often using the WHERE clause random() > 0.5 to pick a random subset
> of
> > my data. Now I noticed that when using a set-returning function in a
> > sub-query, I either get the whole set or none (meaning that the WHERE
> > random() > 0.5 clause is interpreted *before* the set is being
> generated).
> > e.g.:
> >
> > SELECT num FROM (
> > SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num) AS foo WHERE
> random() > 0.5;
>
> Hmm, I think this is an optimizer bug.  There are two legitimate behaviors
> here:
>
> SELECT * FROM unnest(ARRAY[1,2,3,4,5,6,7,8,9,10]) WHERE random() > 0.5;
>
> should (and does) re-evaluate the WHERE for every row output by unnest().
>
> SELECT unnest(ARRAY[1,2,3,4,5,6,7,8,9,10]) WHERE random() > 0.5;
>
> should evaluate WHERE only once, since that happens before expansion of the
> set-returning function in the targetlist.  (If you're an Oracle user and
> you imagine this query as having an implicit "FROM dual", the WHERE should
> be evaluated for the single row coming out of the FROM clause.)
>
> In the case you've got here, given the placement of the WHERE in the outer
> query, you'd certainly expect it to be evaluated for each row coming out
> of the inner query.  But the optimizer is deciding it can push the WHERE
> clause down to become a WHERE of the sub-select.  That is legitimate in a
> lot of cases, but not when there are SRF(s) in the sub-select's
> targetlist, because that pushes the WHERE to occur before the SRF(s),
> analogously to the change between the two queries I wrote.
>
> I'm a bit hesitant to change this in existing releases.  Given the lack
> of previous complaints, it seems more likely to break queries that were
> behaving as-expected than to make people happy.  But we could change it
> in v10 and up, especially since some other corner-case changes in
> SRF-in-tlist behavior are afoot.
>
> In the meantime, you could force it to work as you wish by inserting the
> all-purpose optimization fence "OFFSET 0" in the sub-select:
>
> =# SELECT num FROM (
> SELECT unnest(Array[1,2,3,4,5,6,7,8,9,10]) num OFFSET 0) AS foo WHERE
> random() > 0.5;
>  num
> -
>1
>4
>7
>9
> (4 rows)
>
>
> regards, tom lane
>