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

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

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

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

Re: [GENERAL] isnull() function in pgAdmin3

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

Re: [GENERAL] isnull() function in pgAdmin3

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

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

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

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

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

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

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

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

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

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

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 –

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

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

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

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

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

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

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

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

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

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.

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

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

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

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

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

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

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] isnull() function in pgAdmin3

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

Re: [GENERAL] isnull() function in pgAdmin3

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] isnull() function in pgAdmin3

-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

[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

Re: [GENERAL] bitwise storage and operations

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

Re: [GENERAL] Determining server load

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 >

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

Re: [GENERAL] Determining server load

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

[GENERAL] Determining server load

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

Re: [GENERAL] bitwise storage and operations

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)

Re: [GENERAL] lost synchronization with server: got message type "Z"

"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

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

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

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

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

Re: [GENERAL] need approval to join forums/community

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

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

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