Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Cachique]

You can try pg_cron.
https://github.com/citusdata/pg_cron
"pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or
higher) that runs inside the database as an extension. It uses the same
syntax as regular cron, but it allows you to schedule PostgreSQL commands
directly from the database"

It looks like what you want.

Walter.

On Tue, Nov 29, 2016 at 10:40 PM, Patrick B 
wrote:

>
>
> 2016-11-30 14:21 GMT+13:00 John R Pierce :
>
>> On 11/29/2016 5:10 PM, Patrick B wrote:
>>
>>
>> Yep.. once a minute or so. And yes, I need to store a history with
>> timestamp.
>>
>> Any idea? :)
>>
>>
>> so create a table with a timestamptz, plus all the fields you want, have
>> a script (perl?  python?  whatever your favorite poison is with database
>> access) that once a minute executes those two queries (you'll need two
>> database connections since only the slave knows how far behind it is), and
>> inserts the data into your table.
>>
>>
>> --
>> john r pierce, recycling bits in santa cruz
>>
>>
>
> Can't I do it on the DB size? Using a trigger maybe? instead of using Cron?
>
> Patrick
>
>

Re: [GENERAL] update records to have occurance number

[Cachique]

HI:

You can use windows functions. https://www.postgresql.org/
docs/9.5/static/tutorial-window.html
specifically row_number(). https://www.postgresql.org/
docs/9.5/static/tutorial-window.html

I'm assuming that your ordering is based on lname and then fname.

The query for getting 'number' is

sandbox=# select row_number() over (partition by lname order by fname,
lname) number, fname, lname from person
 number | fname  | lname
++---
  1 | first  | last
  2 | second | last
  3 | third  | last
  1 | first  | other
  2 | next   | other
(5 rows)

And combined with UPDATE FROM... https://www.postgresql.org/
docs/9.5/static/sql-update.html

sandbox=# update person p set number = d.number from (select row_number()
over (partition by lname order by fname, lname) number, fname, lname from
person) d where p.fname = d.fname and p.lname = d.lname;
UPDATE 5


Regards,
Walte

On Tue, Oct 25, 2016 at 12:06 PM, Mark Lybarger  wrote:

> I want to update a table to have the value of the occurrence number.  For
> instance, I have the below table.  I want to update the number column to
> increment the count of last name occurrences, so that it looks like this:
>
> first last 1
> second last 2
> third last 3
> first other 1
> next other 2
>
> Here's my simple table:
>
> create table person ( fname text, lname text, number integer);
>
> insert into person (fname, lname) values ('first', 'last');
> insert into person (fname, lname) values ('second', 'last');
> insert into person (fname, lname) values ('third', 'last');
>
> insert into person (fname, lname) values ('first', 'other');
> insert into person (fname, lname) values ('next', 'other');
>
> How would I issue an update statement to update the number column?
>
> thanks!
>

Re: [GENERAL] Doubts about replication from many servers

[Cachique]

Hi.
You mean one and only one big cluster with all databases from your 4 PG
servers ?
What about running 4 clusters (different ports) in your backup server and
each taking replication from your master servers.

Regards,
Walter

On Fri, Oct 21, 2016 at 12:09 PM, Edilmar LISTAS 
wrote:

> Hi,
>
> I have 4 PG servers where each one runs many databases.
>
> Now, I would like to create just one PG backup server to replicate all the
> databases from 4 PG servers, is it possible? Or Do I need to create 4 PG
> backup servers?
>
> And if some PG server goes down, how to recovery the system from PG backup
> after I reconfigure the PG original server?
>
>
>
> --
> 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] Filtering by UUID

[Cachique]

Take a look at these links. It should give you a clue to avoid LIMIT /
OFFSET.

https://www.citusdata.com/blog/2016/03/30/five-ways-to-paginate/

http://use-the-index-luke.com/blog/2013-07/pagination-done-the-postgresql-way

Regards,
Walter

On Sep 29, 2016 19:19, "Guyren Howe"  wrote:

>
> On Sep 29, 2016, at 16:14 , Colin Morelli  wrote:
>
> Well then...just like that you made me feel like a total idiot! Hah.
>
> I guess that would work fine. I just need to encode some additional
> information in the pagination links that the API returns (a pagination
> "marker" would be a combination of created_at and uuid).
>
> I know this question is virtually impossible to answer without more
> information, but based on your gut - do you think it would make sense to
> define a compound index across (created_at, uuid), or do you think just an
> index on created_at is enough, if we can assume that *most* records
> (>80%) won't have collisions on their created_at timestamp?
>
>
> Quite likely just the created_at will be sufficient, although I like to
> add other fields to the index since you're going to have it anyway, if you
> have occasion to often filter the list on some other field.
>
> But you don't need the UUID field because even if there is collision, it
> will be a small number of records.
>
> On Thu, Sep 29, 2016 at 7:09 PM Guyren Howe  wrote:
>
>> On Sep 29, 2016, at 16:03 , Colin Morelli 
>> wrote:
>> >
>> > Hey list,
>> >
>> > I'm using UUID primary keys in my application. I need to add
>> pagination, and am trying to avoid OFFSET/LIMIT. I do have a created_at
>> timestamp that I could use, but it's possible for multiple records to be
>> created at the same timestamp (postgres offers millisecond precision here,
>> I believe?)
>> >
>> > Is there an efficient way to filter based on the time component of UUID
>> v1s? WHERE id > 'some-uuid' doesn't seem to work, as it looks like it's
>> just performing a lexicographic sort on the hex representation of the UUID.
>> Or, alternatively, does anyone have other suggestions on paginating large
>> data sets?
>>
>> Why not just sort on (created_at, uuid) (ie us the UUID just to force a
>> complete ordering)?
>>
>>
>

Re: [GENERAL] a column definition list is required for functions returning "record"

[Cachique]

Hi
>From the documentation... ( https://www.postgresql.org/
docs/current/static/sql-select.html )

'Function calls can appear in the FROM clause. (This is especially useful
for functions that return result sets, but any function can be used.) This
acts as though the function's output were created as a temporary table for
the duration of this single SELECT command...
If the function has been defined as returning the record data type, then an
alias or the key word AS must be present, followed by a column definition
list in the form ( column_name data_type [, ... ]). The column definition
list must match the actual number and types of columns returned by the
function.'


You need to use 'returns table' syntax or to add an alias in your query.
Something like
select * from words_select_games(1) as (gid type, created type, player1
type, ...);

Check for the correct column types


Regards,
Walter

On Fri, Aug 26, 2016 at 11:20 AM, Alexander Farber <
alexander.far...@gmail.com> wrote:

> Good afternon,
>
> in 9.5.3 I have defined the following custom function:
>
> CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
> RETURNS SETOF RECORD AS
> $func$
> BEGIN
> RETURN QUERY SELECT
> g.gid AS gid,
> EXTRACT(EPOCH FROM g.created)::int AS created,
> g.player1 AS player1,
> COALESCE(g.player2, 0) AS player2,
> COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,
> COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played2,
> ARRAY_TO_STRING(g.hand1, '') AS hand1,
> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?',
> 'g') AS hand2,
> g.letters AS letters, /* is a varchar[15][15] */
> g.values AS values,/* is an integer[15][15] */
> g.bid AS bid,
> m.tiles AS last_tiles,
> m.score AS last_score
> FROM words_games g LEFT JOIN words_moves m USING(mid)
> WHERE g.player1 = in_uid
> UNION SELECT
> g.gid AS gid,
> EXTRACT(EPOCH FROM g.created)::int AS created,
> g.player2 AS player1,
> COALESCE(g.player2, 0) AS player1,
> COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played1,
> COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played2,
> ARRAY_TO_STRING(g.hand2, '') AS hand1,
> REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?',
> 'g') AS hand2,
> g.letters AS letters,
> g.values AS values,
> g.bid AS bid,
> m.tiles AS last_tiles,
> m.score AS last_score
> FROM words_games g LEFT JOIN words_moves m USING(mid)
> WHERE g.player2 = in_uid;
> END
> $func$ LANGUAGE plpgsql;
>
> but calling it gives me errors:
>
> words=> select * from words_select_games(1);
> ERROR:  a column definition list is required for functions returning
> "record"
> LINE 1: select * from words_select_games(1);
>   ^
> words=> select gid, bid from words_select_games(1);
>
> ERROR:  a column definition list is required
> for functions returning "record"
> LINE 1: select gid, bid from words_select_games(1);
>  ^
>
> I have also unsuccessfully tried
>
> RETURNS SETOF words_games, words_moves AS
>
> and without the comma:
>
> RETURNS SETOF words_games words_moves AS
>
> How would you recommend to fix my declaration problem please?
>
> Regards
> Alex
>
>