[GENERAL] ElasticSearch Beats

[Thomas Güttler]

You may decide not to use Elasticsearch but take a look at other components of 
Elastic Stack like logstash and beats.
They can be helpful even when you use Postgres as the end point. Otherwise 
(IMHO), you would spend a lot of time writing
scripts and jobs to capture and stream logs. If I were you, I would not want to 
do that.


I wanted to know what Beats is. I found this:

{{{
 Beats is the platform for building lightweight, open source data shippers for 
many types of data you want to enrich
 with Logstash, search and analyze in Elasticsearch, and visualize in Kibana.
}}}

Oh what fun. How does "enrich data" work? Imagine you have N bytes. There
are several ways to create N+M bytes from N bytes. I call this bloating,
not enriching. You can analyze data, compress it, visualize it But AFAIK
you can't enrich data.

Reading the next pages of the advertisement:
{{{
 enriched and combined with other data sources using Logstash
}}}

OK, this way data can be enriched by using other data sources.

Still I see no use case for beats.

Do you use it? How does it help you?

I guess I take the boring route. I never used it, but it looks solid:
  https://en.wikipedia.org/wiki/Reliable_Event_Logging_Protocol

Regards,
  Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/


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

[GENERAL] Graylog

[Thomas Güttler]

Am 23.08.2016 um 22:42 schrieb Terry Schmitt:

Certainly Postgres is capable of handling this volume just fine. Throw in some 
partition rotation handling and you have
a solution.
If you want to play with something different, check out Graylog, which is 
backed by Elasticsearch. A bit more work to
set up than a single Postgres table, but it has ben a success for us storing, 
syslog, app logs, and Postgres logs from
several hundred network devices, Windows and Linux servers. Rotation is handled 
based on your requirements and drilling
down to the details is trivial. Alerting is baked in as well. It could well be 
overkill for your needs, but I don't know
what your environment looks like.


Thank you for this hint. I will look at it.

Regards,
  Thomas Güttler

--
Thomas Guettler http://www.thomas-guettler.de/


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

[GENERAL] ON CONFLICT does not support deferrable unique constraints

[Andreas Joseph Krogh]

Hope some -hackers read this...
 
Are there any plans to lift this restriction?
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 

[GENERAL] pglogical Replication CONFLICT

[Steven Forrester]

I am using PostgreSQL 9.4 with pglogical replication. I have 1 provider node 
and 1 subscriber in 2 separate locations. The subscriber subscribes to 1 
database on the provider. Every evening a function runs that DELETE's & 
INSERT's to tables that are replicated to the subscriber and causes CONFLICT 
messages to be logged:-

CONFLICT: remote UPDATE on relation db1.schedule. Resolution: (null).
CONFLICT: remote UPDATE on relation db1.schedule (tuple not found). Resolution: 
skip.

When I run the individual DELETE & INSERT statements to the schedule table 
manually I don't get any conflicts.

I have also compared the provider and subscribers data and they appear to be 
consistent with each other.

Any help or suggestions would be much appreciated.

Re: [GENERAL] ON CONFLICT does not support deferrable unique constraints

[Scott Marlowe]

On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
 wrote:
>
> Hope some -hackers read this...
>
> Are there any plans to lift this restriction?

I'm trying to figure out a method for making this work in my head.
These two things seem kind of opposed to each other.


-- 
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] ON CONFLICT does not support deferrable unique constraints

[Francisco Olarte]

On Wed, Aug 24, 2016 at 6:26 PM, Scott Marlowe  wrote:
> On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
>> Are there any plans to lift this restriction?
> I'm trying to figure out a method for making this work in my head.
> These two things seem kind of opposed to each other.

He probably wants to just treat is as non-deferrable just during the
upsert. I do not know if he has thought this opens a can of worms (
like, the constraint may be already broken due to precious DML ).

Francisco Olarte.


-- 
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] ON CONFLICT does not support deferrable unique constraints

[Andreas Joseph Krogh]

På onsdag 24. august 2016 kl. 18:36:16, skrev Francisco Olarte <
fola...@peoplecall.com >:
On Wed, Aug 24, 2016 at 6:26 PM, Scott Marlowe  wrote:
 > On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
 >> Are there any plans to lift this restriction?
 > I'm trying to figure out a method for making this work in my head.
 > These two things seem kind of opposed to each other.

 He probably wants to just treat is as non-deferrable just during the
 upsert. I do not know if he has thought this opens a can of worms (
 like, the constraint may be already broken due to precious DML ).

 Francisco Olarte.
 
As a developer I want it to "just work", if there's an error of any kind then 
abort the transaction, just as it was non-deferrable.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

Re: [GENERAL] Forward declaration of table

[Alexander Farber]

Hello again,

I have went the ALTER TABLE route to add my 2 "cyclic" FKs:

https://gist.github.com/afarber/c40b9fc5447335db7d24

And now I have these 2 tables in my 9.5.3 database:

#TABLE words_moves;

 mid | action | gid | uid |played |

  tiles

   | score

-++-+-+---++---

   2 | play   |   1 |   1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7,
"row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1,
"letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}]

|13

   3 | play   |   2 |   1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7,
"row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1,
"letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7,
"row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2,
"letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] |31

   4 | play   |   1 |   2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8,
"row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1,
"letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8,
"row": 5, "value": 2, "letter": "С"}]
|33

(3 rows)

# SELECT
  gid,

EXTRACT(EPOCH FROM
created)::int AS created,
  player1,

COALESCE(player2, 0) AS player2,
  COALESCE(EXTRACT(EPOCH FROM
played1)::int, 0) AS played1,
COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2,
  ARRAY_TO_STRING(hand1, '') AS hand1,
  ARRAY_TO_STRING(hand2,
'') AS hand2,
bid

FROM words_games
  WHERE player1 = 1

  OR player2 = 1;

 gid |  created   | player1 | player2 |  played1   |  played2   |  hand1  |
 hand2  | bid
-++-+-+++-+-+-
   2 | 1472068074 |   1 |   0 | 1472068094 |  0 | ЫТОВЕРЛ |
ЕНХЯЭАК |   1
   1 | 1472063658 |   1 |   2 | 1472063800 | 1472068255 | ВГЦЕСИУ |
ННДНСВТ |   1
(2 rows)

Then I am trying to perform the LEFT JOIN to return active games and recent
moves for player 1, but for some reason the first two columns are empty:

# SELECT
  m.tiles,

  m.score,

g.gid,

EXTRACT(EPOCH FROM g.created)::int AS created,
g.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,
  ARRAY_TO_STRING(g.hand2, '') AS hand2,
g.bid

FROM words_games g LEFT JOIN words_moves m
ON (g.mid1 = m.mid OR g.mid2 = m.mid)
  WHERE g.player1 = 1

OR g.player2 = 1;

 tiles | score | gid |  created   | player1 | player2 |  played1   |
 played2   |  hand1  |  hand2  | bid
---+---+-++-+-+++-+-+-
   |   |   2 | 1472068074 |   1 |   0 | 1472068094 |
   0 | ЫТОВЕРЛ | ЕНХЯЭАК |   1
   |   |   1 | 1472063658 |   1 |   2 | 1472063800 |
1472068255 | ВГЦЕСИУ | ННДНСВТ |   1
(2 rows)

Why aren't m.tiles and m.score returned please?

Regards
Alex

Re: [GENERAL] Forward declaration of table

[David G. Johnston]

On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> Why aren't m.tiles and m.score returned please?
>
>
​How about you output g.mid1 and g.mid2 in the first query and confirm that
the rows being returned from words_games actually have a value in the set
{2,3,4} in one of those columns.

David J.
​

Re: [GENERAL] Forward declaration of table

[David G. Johnston]

On Wed, Aug 24, 2016 at 4:38 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>>
>> Why aren't m.tiles and m.score returned please?
>>
>>
> ​How about you output g.mid1 and g.mid2 in the first query and confirm
> that the rows being returned from words_games actually have a value in the
> set {2,3,4} in one of those columns.
>
>
​Also, NULL can be annoying here...might want to try IS DISTINCT FROM or
some similar.

Dave
​

[GENERAL] Alter default privileges vs new schemas

[Francisco Reyes]

https://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html

Have a client where their development teams use extensive use of 
schemas. We use default privileges like this


ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT ON 
tablesTO ro_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT ON 
sequences TO ro_group;


ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT, UPDATE, 
DELETE, INSERT ON tablesTO rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT SELECT, 
USAGE  ON sequences TO rw_group;
ALTER DEFAULT PRIVILEGES FOR ROLE jobs_owner_local GRANT 
EXECUTEON functions TO rw_group;



But I can't find a way to grant usage on new schemas. At DB creation I do

grant usage on schema public to rw_group;
grant usage on schema public to ro_group;


Because we revoke everything from public we have to explicitly grant 
connect to DB and usage on schemas.



Currently having to manually grant usage when a new schema is created 
and wondering if there is a better way.



Postgresql 9.3



--
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] Forward declaration of table

[Adrian Klaver]

On 08/24/2016 01:27 PM, Alexander Farber wrote:

Hello again,

I have went the ALTER TABLE route to add my 2 "cyclic" FKs:

https://gist.github.com/afarber/c40b9fc5447335db7d24

And now I have these 2 tables in my 9.5.3 database:





Why aren't m.tiles and m.score returned please?


Reformatted your LEFT JOIN query(courtesy of http://sqlformat.darold.net/) :

SELECT
m.tiles,
m.score,
g.gid,
extract (
EPOCH
FROM
g.created ) ::INT AS created,
g.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,
array_to_string (
g.hand2,
'' ) AS hand2,
g.bid
FROM
words_games g
LEFT JOIN words_moves m ON (
g.mid1 = m.mid
OR g.mid2 = m.mid )
WHERE
g.player1 = 1
OR g.player2 = 1;

Looking at your tables I would start with something like:

SELECT
wm.tiles, wm.score
FROM
word_games AS wg
JOIN
word_moves AS wm
ON
wg.gid = wm.gid
WHERE
(wg.player1 = 1
OR
wg.player2 = 1)



Regards
Alex



--
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 table change in an event trigger

[Jonathan Rogers]

On 08/24/2016 12:58 AM, Alvaro Herrera wrote:
> Jonathan Rogers wrote:
>> I am trying to use an event trigger to do something when a column
>> changes. I can declare an event trigger "ON sql_drop WHEN TAG IN ('ALTER
>> TABLE')" to get dropped columns. However, I can't figure out any good
>> way to determine when a column has been added or altered.
>>
>> I can declare an event trigger "ON ddl_command_end WHEN TAG IN ('ALTER
>> TABLE')" but that gets unwanted events such as disabling triggers on a
>> table. Function pg_event_trigger_ddl_commands() returns rows with column
>> "command" of type "pg_ddl_command" which contains "a complete
>> representation of the command, in internal format." According to the
>> docs, this cannot be output directly, but it can be passed to other
>> functions to obtain different pieces of information about the command.
>> However, I cannot find any other functions which operate on the type
>> pg_ddl_command. Am I missing something? Is the documentation lacking?
> 
> Yeah, that type can only be processed by C functions.  You'd need to
> write a C function to examine the structure and see whether it matches
> what you need.
> 

OK, thanks for the explanation. It seems like the docs should make it
clear that the "other functions" are not included.

-- 
Jonathan Rogers
Socialserve.com by Emphasys Software
jrog...@emphasys-software.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] Forward declaration of table

[Alexander Farber]

Thank you and apologies for the misformated mail - I kept fixing it for
many minutes, but once I hit the "Send" button in Gmail it went south again.