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 WAL segment was still
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
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|
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 = 0
>
> David J.
>
>
>
lol... that's right David J
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.
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 fun
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,
>> > wrote:
>> > > Can I list all WAL files in pg_xlog by using some sql query i
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 t1
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?
> >
> > Try
> >
> > Select pg_ls_dir('pg_xlog');
>
>
>
>
>>
>
> 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
(
On 6/2/2016 4:42 PM, David G. Johnston wrote:
ssh user@hostname ?
^ ++
--
john r pierce, recycling bits in santa cruz
On 06/02/2016 02:03 PM, Patrick Baker wrote:
2016-06-03 2:10 GMT+12:00 David G. Johnston mailto:david.g.johns...@gmail.com>>:
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 also, to teach the
update SQL t
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 the host where I am run
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. The
* 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 commands on the ho
* 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 l
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 applications you want to run
> you could consider:
>
> h
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 psql but
> > that executes the commands on the h
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 it possible for a
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 it possible for a p
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 post
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 be returned when the
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.
>
> I doubt it -- using NOT IN requires (per the SQL specification)
> han
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 LIMIT... if possible can you please give
>>> me an example of that LIMIT in some of tho
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 that probably does not give you the answer
you wan
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
> retu
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?
>>
>>
> You also should use ORDER BY when using LIMIT
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 I list all WAL files in pg_xlog by using some
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?
> >
> > Try
> >
> > Select pg_ls_dir('pg_xlog');
>
* 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 privileges.
Given the usefu
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
> from yourtable);
>
>
> s
> -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 trying to do something that seems li
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) f
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' ; pgsql
Sub
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
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 Mess
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 t
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
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 would help to see your table
>> structure and the query yo
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 us
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 sts=0 and id not in (s
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 actually
> a character string th
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 proble
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
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
> 3 || 0
> 4 |
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 t
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... hos
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 Postgres Company
>
>
> --
--
Be
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
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 did you
>> indicate the O/S.
>>
>
> I think
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 the
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
> That being said, you s
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.
On
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 the setup it could be omit
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 consi
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
56 matches
Mail list logo
