Re: [GENERAL] Slave claims requested WAL segment already removed - but it wasn't

On Thu, Jun 2, 2016 at 11:43 AM, Jeff Beck wrote: > Hi- > We have a master (pg 9.4.4 on Ubuntu 14.10) and a slave (pg 9.4.8 on > Centos 7). During a period of heavy use, the slave began complaining > that the “requested WAL segment xx has already been removed”. But > the

Re: [GENERAL] psql remote shell command

Wow, thanks for all the feedback. The question about whether a superuser could do something like this came up at the office today in the context of security issues and handing out superuser to the application users (not necessarily to the accounts/roles that would be used from the applications

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/02/2016 08:37 PM, Patrick Baker wrote: Hi guys, * The function works... All the data is updated as expected. However, when I call the function for the second time, it touches the rows that had already been touched by the previous call * It triplicate ( |LIMIT 3|

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

2016-06-03 15:50 GMT+12:00 David G. Johnston : > On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker > wrote: > >> >> How can I make the function to gets the next 3 rows and not use the same >> rows that have been used before? >> > ​WHERE migrated

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 11:37 PM, Patrick Baker wrote: > > How can I make the function to gets the next 3 rows and not use the same > rows that have been used before? > ​WHERE migrated = 0 ​ ​David J.

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

Hi guys, - The function works... All the data is updated as expected. However, when I call the function for the second time, it touches the rows that had already been touched by the previous call - It triplicate ( LIMIT 3 ) the records. *Question:* How can I make the

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar wrote: > > > On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: >> >> * Sameer Kumar (sameer.ku...@ashnik.com) wrote: >> > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, >> >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

I did: CREATE or REPLACE FUNCTION function_1_data() RETURNS INTEGER AS $$ declare row record; BEGIN -- copying the data to the backup table (not the blobs) FOR row IN EXECUTE ' SELECT t1.file_id FROM table1_n_b

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > > wrote: > > > Can I list all WAL files in pg_xlog by using some sql query in > Postgres? > > >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

> > >> > > Why are you joining to table3_nb? > You do not use any fields from it. > > How do you know what data in table1_n_b to get? > I see this grabbing the same information over and over again. SELECT * INTO table3_n_b FROM ( SELECT account_id, note_id, file_id FROM (

Re: [GENERAL] psql remote shell command

On 6/2/2016 4:42 PM, David G. Johnston wrote: ​ssh user@hostname ? ^ ++ -- john r pierce, recycling bits in santa cruz

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/02/2016 02:03 PM, Patrick Baker wrote: 2016-06-03 2:10 GMT+12:00 David G. Johnston >: Hi David. The SQLs inside the function works I'm just having problem about limiting the query to the number of rows I want, and

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:54 PM, Stephen Frost wrote: > * Dennis (denn...@visi.com) wrote: > > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on

Re: [GENERAL] Slave claims requested WAL segment already removed - but it wasn't

On 06/01/2016 06:43 PM, Jeff Beck wrote: Hi- We have a master (pg 9.4.4 on Ubuntu 14.10) and a slave (pg 9.4.8 on Centos 7). During a period of heavy use, the slave began complaining that the “requested WAL segment xx has already been removed”. But the WAL segment was still on the master.

Re: [GENERAL] psql remote shell command

* David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Jun 2, 2016 at 7:30 PM, Dennis wrote: > > Is it possible to execute command in on system the is hosting postgresql > > remotely using psql or other mechanism? I know I can use \! in psql but > > that executes the

Re: [GENERAL] psql remote shell command

* Dennis (denn...@visi.com) wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but that > executes the commands on the host where I am running psql from. Also, is it > possible for a postgres

Re: [GENERAL] psql remote shell command

On Fri, Jun 3, 2016 at 8:48 AM, David G. Johnston wrote: > On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier > wrote: > I was focused on admin task due to the pg_ctl (not sure you'd want to run > that via psql...) but if you have shell script

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:39 PM, Michael Paquier wrote: > On Fri, Jun 3, 2016 at 8:30 AM, Dennis wrote: > > Is it possible to execute command in on system the is hosting postgresql > > remotely using psql or other mechanism? I know I can use \! in

Re: [GENERAL] psql remote shell command

On Thu, Jun 2, 2016 at 7:30 PM, Dennis wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am running psql from. Also, > is

Re: [GENERAL] psql remote shell command

On Fri, Jun 3, 2016 at 8:30 AM, Dennis wrote: > Is it possible to execute command in on system the is hosting postgresql > remotely using psql or other mechanism? I know I can use \! in psql but > that executes the commands on the host where I am running psql from. Also, > is

[GENERAL] psql remote shell command

Is it possible to execute command in on system the is hosting postgresql remotely using psql or other mechanism? I know I can use \! in psql but that executes the commands on the host where I am running psql from. Also, is it possible for a postgres login/user to stop or restart a running

Re: [GENERAL] RowDescription via the SQL?

On Thu, Jun 2, 2016 at 1:14 AM, Dmitry Igrishin wrote: > Hi, > > It's possible to query pg_prepared_statements view to obtain the > information about > parameters used in the statement that was prepared. But I don't found > how to get the > information about the rows that will

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 5:44 PM, Kevin Grittner wrote: > On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston > wrote: > > On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce > wrote: > >> Thanks all the below seem to do the trick. > >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 5:03 PM, Patrick Baker wrote: > > > 2016-06-03 2:10 GMT+12:00 David G. Johnston : > >> On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker >> wrote: >> >>> > It's all working, except the

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 3:23 PM, David G. Johnston wrote: > On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce wrote: >> Thanks all the below seem to do the trick. I doubt it -- using NOT IN requires (per the SQL specification) handling NULLs in a way

Re: [GENERAL] Partitioned tables do not return affected row counts to client

On Thu, 2016-06-02 at 11:01 -0700, rverghese wrote: > We are looking to move from one large table to partitioned tables. > Since the > inserts and updates are made to the master table and then inserted > into the > appropriate partitioned table based on the trigger rules, the > affected_rows >

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

2016-06-03 2:10 GMT+12:00 David G. Johnston : > On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker > wrote: > >> It's all working, except the LIMIT... if possible can you please give >> me an example of that LIMIT in some of those queries?

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

David, * David G. Johnston (david.g.johns...@gmail.com) wrote: > On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost wrote: > > > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > > > wrote: > > > > Can

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Thu, Jun 2, 2016 at 4:29 PM, Stephen Frost wrote: > * Sameer Kumar (sameer.ku...@ashnik.com) wrote: > > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > > wrote: > > > Can I list all WAL files in pg_xlog by using some sql query in > Postgres? > >

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

* Sameer Kumar (sameer.ku...@ashnik.com) wrote: > On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, > wrote: > > Can I list all WAL files in pg_xlog by using some sql query in Postgres? > > Try > > Select pg_ls_dir('pg_xlog'); Note that this currently requires superuser

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce wrote: > On 6/2/2016 11:10 AM, Steve Clark wrote: > > Thanks all the below seem to do the trick. > > On 06/02/2016 01:58 PM, David G. Johnston wrote: > > select max(id) from yourtable where sts=0 and id not in (select ref_id >

Re: [GENERAL] dumb question

​ > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Steve Clark > Sent: Thursday, June 2, 2016 9:56 AM > To: pgsql > Subject: [GENERAL] dumb question > > Hi List, > > I am a noob

Re: [GENERAL] dumb question

On 06/02/2016 04:07 PM, Dann Corbit wrote: This is your request, translated directly into SQL select max(id) from sometable where sts=0 and ref_id IS NULL Looking at your sample, it seems that sts is always 1 when ref_id exists, so it may possibly simplify to: select max(id)

Re: [GENERAL] dumb question

If ref_id is an instance of id and you are trying to filter that out, then use a self join -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Dann Corbit Sent: Thursday, June 2, 2016 1:08 PM To: 'Steve Clark'

Re: [GENERAL] dumb question

On 6/2/2016 11:10 AM, Steve Clark wrote: Thanks all the below seem to do the trick. On 06/02/2016 01:58 PM, David G. Johnston wrote: select max(id) from yourtable where sts=0 and id not in (select ref_id from yourtable); select max(id) from yourtable where sts=0 and id not in (select ref_id

Re: [GENERAL] dumb question

This is your request, translated directly into SQL select max(id) from sometable where sts=0 and ref_id IS NULL Looking at your sample, it seems that sts is always 1 when ref_id exists, so it may possibly simplify to: select max(id) from sometable where sts=0 -Original

[GENERAL] dumb question

Hi List, I am a noob trying to do something that seems like it should be easy but I can't figure it out. I have a table like so: id | ref_id | sts -- 1 || 0 2 | 1 | 1 3 || 0 4 || 0 5 | 4 | 1 6 || 0 7 | 6 | 1 I want

Re: [GENERAL] dumb question

Thanks all the below seem to do the trick. On 06/02/2016 01:58 PM, David G. Johnston wrote: select max(id) from yourtable where sts=0 and id not in (select ref_id from yourtable); select max(id) from yourtable where sts=0 and id not in (select ref_id from yourtable); -- Stephen Clark

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos wrote: > > > 2016-06-02 14:23 GMT-03:00 Steve Crawford > : > >> Something like: >> >> select max(id) from yourtable where sts=0 and ref_id is null; >> >> That assumes that ref_id is null. It

[GENERAL] Partitioned tables do not return affected row counts to client

We are looking to move from one large table to partitioned tables. Since the inserts and updates are made to the master table and then inserted into the appropriate partitioned table based on the trigger rules, the affected_rows returned to the client (PHP in this case) is always 0. We have been

Re: [GENERAL] dumb question

On Thu, Jun 2, 2016 at 1:48 PM, Steve Crawford < scrawf...@pinpointresearch.com> wrote: > On Thu, Jun 2, 2016 at 10:40 AM, Felipe Santos > wrote: > >> I think sts=0 means ref_id is null >> >> So, what I think he wants to achieve is: >> >> select max(id) from yourtable where

Re: [GENERAL] dumb question

2016-06-02 14:23 GMT-03:00 Steve Crawford : > Something like: > > select max(id) from yourtable where sts=0 and ref_id is null; > > That assumes that ref_id is null. It would help to see your table > structure and the query you tried that doesn't work. If ref_id is

[GENERAL] Refresh materialized views recursively

Hello, This is a response to a rather old message 'automatically refresh all materialized views?' linked below. I just implemented a recursive refresh for my own purposes, as I couldn't find an existing implementation. I've put it as a gist on github in case anyone else runs into the same

Re: [GENERAL] dumb question

Something like: select max(id) from yourtable where sts=0 and ref_id is null; That assumes that ref_id is null. It would help to see your table structure and the query you tried that doesn't work. If ref_id is actually a character string then you might need ref_id='' or coalesce(ref_id,'')='' if

Re: [GENERAL] dumb question

On Thursday, June 2, 2016, Steve Clark wrote: > Hi List, > > I am a noob trying to do something that seems like it should be easy but I > can't figure it out. > > I have a table like so: > > id | ref_id | sts > -- > 1 || 0 > 2 | 1 | 1 >

[GENERAL] dumb question

Hi List, I am a noob trying to do something that seems like it should be easy but I can't figure it out. I have a table like so: id | ref_id | sts -- 1 || 0 2 | 1 | 1 3 || 0 4 || 0 5 | 4 | 1 6 || 0 7 | 6 | 1 I want

Re: [GENERAL] Replication

On 6/2/2016 6:32 AM, Bertrand Paquet wrote: On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ? The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false. "The IP" assumes there is only one...

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, wrote: > Hello! > > Can I list all WAL files in pg_xlog by using some sql query in Postgres? > Try Select pg_ls_dir('pg_xlog'); > -- > Alex Ignatov > Postgres Professional: http://www.postgrespro.com > The Russian

[GENERAL] WAL's listing in pg_xlog by some sql query

Hello! Can I list all WAL files in pg_xlog by using some sql query in Postgres? -- Alex Ignatov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company

Re: [GENERAL] Replication

On Thu, 2 Jun 2016, 10:34 p.m. Scott Mead, wrote: > On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson > wrote: > >> It's been a few years since I worked with slony, and you did not state >> which version of slony or PostgreSQL you are working with, nor

[GENERAL] Slave claims requested WAL segment already removed - but it wasn't

Hi- We have a master (pg 9.4.4 on Ubuntu 14.10) and a slave (pg 9.4.8 on Centos 7). During a period of heavy use, the slave began complaining that the “requested WAL segment xx has already been removed”. But the WAL segment was still on the master. The issue was resolved by manually copying

Re: [GENERAL] Replication

On Thu, Jun 2, 2016 at 10:16 AM, Melvin Davidson wrote: > It's been a few years since I worked with slony, and you did not state > which version of slony or PostgreSQL you are working with, nor did you > indicate the O/S. > I think OP had pointed to using streaming >

Re: [GENERAL] Replication

It's been a few years since I worked with slony, and you did not state which version of slony or PostgreSQL you are working with, nor did you indicate the O/S. That being said, you should be able to formulate a query with a join between sl_path & sl_node that gives you the information you need.

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On Thu, Jun 2, 2016 at 1:04 AM, Patrick Baker wrote: > >>> It's all working, except the LIMIT... if possible can you please give > me an example of that LIMIT in some of those queries? > > ​​ You also should use ORDER BY when using LIMIT and OFFSET; though depending on

Re: [GENERAL] PL/PGSQL + inserts+updates+limit - Postgres 9.3

On 06/01/2016 10:04 PM, Patrick Baker wrote: I maybe be missing it, but I see no LIMIT in the function. I do see OFFSET and it looks backwards to me?: || $1 ||' offset '|| https://www.postgresql.org/docs/9.5/static/sql-select.html LIMIT Clause The LIMIT clause

[GENERAL] Replication

Hi, On an hot standby streaming server, is there any way to know, in SQL, to know the ip of current master ? The solution I have is to read the recovery.conf file to find primary_conninfo, but, it can be false. Regards, Bertrand

[GENERAL] RowDescription via the SQL?

Hi, It's possible to query pg_prepared_statements view to obtain the information about parameters used in the statement that was prepared. But I don't found how to get the information about the rows that will be returned when the statement is eventually executed. (It's possible to get this