Re: [GENERAL] Determining table change in an event trigger

[Alvaro Herrera]

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.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Foreign key against a partitioned table

[Craig James]

On Tue, Aug 23, 2016 at 1:07 PM, Igor Neyman  wrote:

>
>
> *From:* pgsql-general-ow...@postgresql.org [mailto:pgsql-general-owner@
> postgresql.org] *On Behalf Of *Craig James
> *Sent:* Tuesday, August 23, 2016 4:00 PM
> *To:* pgsql-general@postgresql.org
> *Subject:* [GENERAL] Foreign key against a partitioned table
>
>
>
> How do you create a foreign key that references a partitioned table?
>
>
>
> I'm splitting a large table "molecules" into 20 partitions, which also has
> an associated "molecular_properties" table. It looks something like this
> (pseudo-code):
>
>
>
> create table molecules(molecule_idinteger primary key,
>
>molecule_data  text,
>
>p  integer);
>
>
>
> foreach $p (0..19) {
>
> create table molecules_$p (check(p = $p)) inherits (molecules);
>
> }
>
>
>
> create table molecular_properties(molprops_id   integer primary key,
>
>   molecule_id   integer,
>
>   molecular_weight  numeric(8,3));
>
> alter table molecular_properties
>
>   add constraint fk_molecular_properties
>
>   foreign key(molecule_id)
>
>   references molecules(molecule_id);
>
>
>
> (NB: There is no natural way to partition molecules, so the value for p is
> a random number. There is a good reason for partitioning that's not
> relevant to my question...)
>
>
>
> When I try to insert something into the molecular_properties table it
> fails:
>
>
>
> insert or update on table "molecular_properties" violates foreign key
> constraint "fk_molecular_properties"
>
> DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".
>
>
>
> This surprised me. Obviously ID isn't in the "molecules" parent table, but
> I guessed that the foreign key would work anyway since the parent table is
> supposed to behave as though it includes all of the child tables.
>
>
>
> So how do you create a foreign key on a partitioned table?
>
>
>
> I suppose I could partition the molecular_properties table, but that would
> add unnecessary complication to the schema for no reason other than the "on
> delete cascade" feature.
>
>
>
> The only other thing I can think of is a delete trigger on each of the
> partition child tables. That would work, but it's a nuisance.
>
>
>
> Thanks,
>
> Craig
>
>
>
>
>
> You can’t.
>
> Only through triggers as you suggested.
>

OK thanks. Triggers it is.

Craig


>
>
> Regards,
>
> Igor
>



-- 
-
Craig A. James
Chief Technology Officer
eMolecules, Inc.
-

[GENERAL] Determining table change in an event trigger

[Jonathan Rogers]

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?

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

[GENERAL] Determining table change in an event trigger

[Jonathan Rogers]

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?

-- 
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] Foreign key against a partitioned table

[Adrian Klaver]

On 08/23/2016 01:00 PM, Craig James wrote:

How do you create a foreign key that references a partitioned table?

I'm splitting a large table "molecules" into 20 partitions, which also
has an associated "molecular_properties" table. It looks something like
this (pseudo-code):

create table molecules(molecule_idinteger primary key,
   molecule_data  text,
   p  integer);

foreach $p (0..19) {
create table molecules_$p (check(p = $p)) inherits (molecules);
}

create table molecular_properties(molprops_id   integer primary key,
  molecule_id   integer,
  molecular_weight  numeric(8,3));
alter table molecular_properties
  add constraint fk_molecular_properties
  foreign key(molecule_id)
  references molecules(molecule_id);


(NB: There is no natural way to partition molecules, so the value for p
is a random number. There is a good reason for partitioning that's not
relevant to my question...)

When I try to insert something into the molecular_properties table it fails:

insert or update on table "molecular_properties" violates foreign
key constraint "fk_molecular_properties"
DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".


This surprised me. Obviously ID isn't in the "molecules" parent table,
but I guessed that the foreign key would work anyway since the parent
table is supposed to behave as though it includes all of the child tables.


I would say it is because of this:

https://www.postgresql.org/docs/9.5/static/sql-createtable.html

" Notes
...

Unique constraints and primary keys are not inherited in the current 
implementation. This makes the combination of inheritance and unique 
constraints rather dysfunctional.

...
"



So how do you create a foreign key on a partitioned table?

I suppose I could partition the molecular_properties table, but that
would add unnecessary complication to the schema for no reason other
than the "on delete cascade" feature.

The only other thing I can think of is a delete trigger on each of the
partition child tables. That would work, but it's a nuisance.

Thanks,
Craig



--
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] Foreign key against a partitioned table

[Adam Brusselback]

I have wondered if there were any plans to enhance fkey support for
partitioned tables now that more work is being done on partitioning (I know
there has been a large thread on declarative partitioning on hackers,
though I haven't followed it too closely).

Foreign keys are all done through triggers on the backend anyways, it does
seem totally possible to have it work for partitioned tables if the code is
aware that a table is partitioned and it needs to look in all inherited
tables as well as the one specified.

Re: [GENERAL] PG vs ElasticSearch for Logs

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

T

On Mon, Aug 22, 2016 at 7:03 AM, Andy Colson  wrote:

> On 8/22/2016 2:39 AM, Thomas Güttler wrote:
>
>>
>>
>> Am 19.08.2016 um 19:59 schrieb Andy Colson:
>>
>>> On 8/19/2016 2:32 AM, Thomas Güttler wrote:
>>>
 I want to store logs in a simple table.

 Here my columns:

   Primary-key (auto generated)
   timestamp
   host
   service-on-host
   loglevel
   msg
   json (optional)

 I am unsure which DB to choose: Postgres, ElasticSearch or ...?

 We don't have high traffic. About 200k rows per day.

 My heart beats for postgres. We use it since several years.

 On the other hand, the sentence "Don't store logs in a DB" is
 somewhere in my head.

 What do you think?




>>> I played with ElasticSearch a little, mostly because I wanted to use
>>> Kibana which looks really pretty.  I dumped a ton
>>> of logs into it, and made a pretty dashboard ... but in the end it
>>> didn't really help me, and wasn't that useful.  My
>>> problem is, I don't want to have to go look at it.  If something goes
>>> bad, then I want an email alert, at which point
>>> I'm going to go run top, and tail the logs.
>>>
>>> Another problem I had with kibana/ES is the syntax to search stuff is
>>> different than I'm used to.  It made it hard to
>>> find stuff in kibana.
>>>
>>> Right now, I have a perl script that reads apache logs and fires off
>>> updates into PG to keep stats.  But its an hourly
>>> summary, which the website turns around and queries the stats to show
>>> pretty usage graphs.
>>>
>>
>> You use Perl to read apache logs. Does this work?
>>
>> Forwarding logs reliably is not easy. Logs are streams, files in unix
>> are not streams. Sooner or later
>> the files get rotated. RELP exists, but AFAIK it's usage is not wide
>> spread:
>>
>>   https://en.wikipedia.org/wiki/Reliable_Event_Logging_Protocol
>>
>> Let's see how to get the logs into postgres 
>>
>> In the end, PG or ES, all depends on what you want.
>>>
>>
>> Most of my logs start from a http request. I want a unique id per request
>> in every log line which gets created. This way I can trace the request,
>> even if its impact spans to several hosts and systems which do not
>> receive http requests.
>>
>> Regards,
>>   Thomas Güttler
>>
>>
>>
> I don't read the file.  In apache.conf:
>
> # v, countyia, ip, sess, ts, url, query, status
> LogFormat 
> "3,%{countyName}e,%a,%{VCSID}C,%{%Y-%m-%dT%H:%M:%S%z}t,\"%U\",\"%q\",%>s"
> csv3
>
> CustomLog "|/usr/local/bin/statSender.pl -r 127.0.0.1" csv3
>
> I think I read somewhere that if you pipe to a script (like above) and you
> dont read fast enough, it could slow apache down.  That's why the script
> above dumps do redis first.  That way I can move processes around, restart
> the database, etc, etc, and not break apache in any way.
>
> The important part of the script:
>
> while (my $x = <>)
> {
> chomp($x);
> next unless ($x);
> try_again:
> if ($redis)
> {
> eval {
> $redis->lpush($qname, $x);
> };
> if ($@)
> {
> $redis = redis_connect();
> goto try_again;
> }
> # just silence this one
> eval {
> $redis->ltrim($qname, 0, 1000);
> };
> }
> }
>
> Any other machine, or even multiple, then reads from redis and inserts
> into PG.
>
> You can see, in my script, I trim the queue to 1000 items, but that's
> because I'm not as worried about loosing results.  Your setup would
> probably be different.  I also setup redis to not save anything to disk,
> again, because I don't mind if I loose a few hits here or there.  But you
> get the idea.
>
> -Andy
>
>
>
> --
> 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] Foreign key against a partitioned table

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Craig James
Sent: Tuesday, August 23, 2016 4:00 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Foreign key against a partitioned table

How do you create a foreign key that references a partitioned table?

I'm splitting a large table "molecules" into 20 partitions, which also has an 
associated "molecular_properties" table. It looks something like this 
(pseudo-code):

create table molecules(molecule_idinteger primary key,
   molecule_data  text,
   p  integer);

foreach $p (0..19) {
create table molecules_$p (check(p = $p)) inherits (molecules);
}

create table molecular_properties(molprops_id   integer primary key,
  molecule_id   integer,
  molecular_weight  numeric(8,3));
alter table molecular_properties
  add constraint fk_molecular_properties
  foreign key(molecule_id)
  references molecules(molecule_id);

(NB: There is no natural way to partition molecules, so the value for p is a 
random number. There is a good reason for partitioning that's not relevant to 
my question...)

When I try to insert something into the molecular_properties table it fails:

insert or update on table "molecular_properties" violates foreign key 
constraint "fk_molecular_properties"
DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".

This surprised me. Obviously ID isn't in the "molecules" parent table, but I 
guessed that the foreign key would work anyway since the parent table is 
supposed to behave as though it includes all of the child tables.

So how do you create a foreign key on a partitioned table?

I suppose I could partition the molecular_properties table, but that would add 
unnecessary complication to the schema for no reason other than the "on delete 
cascade" feature.

The only other thing I can think of is a delete trigger on each of the 
partition child tables. That would work, but it's a nuisance.

Thanks,
Craig


You can’t.
Only through triggers as you suggested.

Regards,
Igor

[GENERAL] Foreign key against a partitioned table

[Craig James]

How do you create a foreign key that references a partitioned table?

I'm splitting a large table "molecules" into 20 partitions, which also has
an associated "molecular_properties" table. It looks something like this
(pseudo-code):

create table molecules(molecule_idinteger primary key,
   molecule_data  text,
   p  integer);

foreach $p (0..19) {
create table molecules_$p (check(p = $p)) inherits (molecules);
}

create table molecular_properties(molprops_id   integer primary key,
  molecule_id   integer,
  molecular_weight  numeric(8,3));
alter table molecular_properties
  add constraint fk_molecular_properties
  foreign key(molecule_id)
  references molecules(molecule_id);


(NB: There is no natural way to partition molecules, so the value for p is
a random number. There is a good reason for partitioning that's not
relevant to my question...)

When I try to insert something into the molecular_properties table it fails:

insert or update on table "molecular_properties" violates foreign key
constraint "fk_molecular_properties"
DETAIL:  Key (molecule_id)=(83147) is not present in table "molecules".


This surprised me. Obviously ID isn't in the "molecules" parent table, but
I guessed that the foreign key would work anyway since the parent table is
supposed to behave as though it includes all of the child tables.

So how do you create a foreign key on a partitioned table?

I suppose I could partition the molecular_properties table, but that would
add unnecessary complication to the schema for no reason other than the "on
delete cascade" feature.

The only other thing I can think of is a delete trigger on each of the
partition child tables. That would work, but it's a nuisance.

Thanks,
Craig

Re: [GENERAL] Forward declaration of table

[Igor Neyman]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 3:33 PM
Cc: pgsql-general 
Subject: Re: [GENERAL] Forward declaration of table

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman 
> wrote:
mailto:pgsql-general-ow...@postgresql.org]
 On Behalf Of Alexander Farber
https://gist.github.com/afarber/c40b9fc5447335db7d24

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete 
cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE


you are correct, but I need to send most recent move in each game together with 
the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve them 
every time dynamically with
WITH last_moves AS (
  SELECT *
  FROM words_moves wm1
  WHERE
played = (SELECT max(played)
  FROM words_moves wm2
  WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
  LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
  player1 = 1 OR
  player2 = 1;

Regards
Alex

Or, for the last moves you could probably have the third table LAST_MOVES 
maintained through triggers on WORDS_MOVES table.
Then, you just join WORDS_GAMES and LAST_MOVES tables.

Regards,
Igor

Re: [GENERAL] Forward declaration of table

[Alexander Farber]

Hi Igor,

On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman  wrote:

> mailto:pgsql-general-ow...@postgresql.org] *On Behalf Of *Alexander Farber
>
>
https://gist.github.com/afarber/c40b9fc5447335db7d24
>
>
>
> Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on
> delete cascade).
>
> So, you don’t need mid1, mid2 columns in WORD_GAMES table.
>
> What you need is this column in WORD_MOVES table:
>
>
>
> gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
>
>
>

you are correct, but I need to send most recent move in each game together
with the other game data.

If I don't store the recent moves in mid1, mid2 then I'd have to retrieve
them every time dynamically with

WITH last_moves AS (
  SELECT *
  FROM words_moves wm1
  WHERE
played = (SELECT max(played)
  FROM words_moves wm2
  WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
  LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
  player1 = 1 OR
  player2 = 1;


Regards
Alex

Re: [GENERAL] Forward declaration of table

[Igor Neyman]

Regards,
Igor

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 1:11 PM
To: pgsql-general 
Subject: [GENERAL] Forward declaration of table

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player games:
DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);

This has worked well for me (when a user connects to the game server, I send 
her all games she is taking part in), but then I have decided to add another 
table to act as a "logging journal" for player moves:
DROP TABLE IF EXISTS words_moves;
DROP TYPE IF EXISTS words_action;
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the latest 
moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all active 
games and status updates on the recent opponent moves.

However the 2 added columns do not work:
ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist

So my question is if I can somehow "forward declare" the words_moves table?

Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

Thank you
Alex

Alex,
I think, you’ve got this reference “backwards”.

Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete 
cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:

gid integer REFERENCES WORD_GAMES ON DELETE CASCADE

Am right/wrong?

Regards,
Igor

Re: [GENERAL] Forward declaration of table

[Adrian Klaver]

On 08/23/2016 10:29 AM, David G. Johnston wrote:

On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver
>wrote:


use ALTER TABLE ADD table_constraint :

https://www.postgresql.org/docs/9.5/static/sql-altertable.html


to add the FK references to word_games.


​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore
hazards here.  Maybe pg_dump is smart enough to handle this correctly,
though - maybe by adding constraint definitions after all tables and
columns are present.


It does. Though the usual caveats about doing partial dumps apply, eg if 
I had only specified -t fk_child below I would not get fk_parent 
automatically:


postgres@test=# create table fk_child(id int, fk_id int);
CREATE TABLE
postgres@test=# create table fk_parent(id int, some_id int UNIQUE);
CREATE TABLE
postgres@test=# alter table fk_child ADD CONSTRAINT fk_constraint 
FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id);

ALTER TABLE

pg_dump -d test -U postgres  -t fk_parent -t fk_child -f test.sql


--
-- Name: fk_child; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_child (
id integer,
fk_id integer
);


ALTER TABLE fk_child OWNER TO postgres;

--
-- Name: fk_parent; Type: TABLE; Schema: public; Owner: postgres
--

CREATE TABLE fk_parent (
id integer,
some_id integer
);


ALTER TABLE fk_parent OWNER TO postgres;

--
-- Data for Name: fk_child; Type: TABLE DATA; Schema: public; Owner: 
postgres

--

COPY fk_child (id, fk_id) FROM stdin;
\.


--
-- Data for Name: fk_parent; Type: TABLE DATA; Schema: public; Owner: 
postgres

--

COPY fk_parent (id, some_id) FROM stdin;
\.


--
-- Name: fk_parent_some_id_key; Type: CONSTRAINT; Schema: public; Owner: 
postgres

--

ALTER TABLE ONLY fk_parent
ADD CONSTRAINT fk_parent_some_id_key UNIQUE (some_id);


--
-- Name: fk_constraint; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY fk_child
ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES 
fk_parent(some_id);





David J.​




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

[GENERAL] Pentaho Odoo PSQL Slave.

[Periko Support]

Hi.

Do someone here running Pentaho(Odoo) on a PSQL Master-Slave in the
slave server?

Thanks.


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

[David G. Johnston]

On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver 
wrote:

>
> use ALTER TABLE ADD table_constraint :
>
> https://www.postgresql.org/docs/9.5/static/sql-altertable.html
>
> to add the FK references to word_games.
>
>
​Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore hazards
here.  Maybe pg_dump is smart enough to handle this correctly, though -
maybe by adding constraint definitions after all tables and columns are
present.

David J.​

Re: [GENERAL] Forward declaration of table

[David G. Johnston]

On Tue, Aug 23, 2016 at 1:10 PM, Alexander Farber <
alexander.far...@gmail.com> wrote:

>
> So my question is if I can somehow "forward declare" the words_moves table?
>
>
​A better way to phrase this is:

Is it possible to create circular foreign key dependencies between tables?

The answer is no.

You generally need to introduce a third table.  I haven't delved deep
enough into your scenario to be more specific.

David J.
​

Re: [GENERAL] Forward declaration of table

[Adrian Klaver]

On 08/23/2016 10:10 AM, Alexander Farber wrote:

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player
games:

DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE
CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);


This has worked well for me (when a user connects to the game server, I
send her all games she is taking part in), but then I have decided to
add another table to act as a "logging journal" for player moves:

DROP TABLE IF EXISTS words_moves;

DROP TYPE IF EXISTS words_action;

CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the
latest moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all
active games and status updates on the recent opponent moves.

However the 2 added columns do not work:

ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist


So my question is if I can somehow "forward declare" the words_moves table?


Off the top of my head:

Change this:
--mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE, 
--mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,


to

mid1 integer
mid2 integer

and then after

CREATE TABLE words_moves ...

use ALTER TABLE ADD table_constraint :

https://www.postgresql.org/docs/9.5/static/sql-altertable.html

to add the FK references to word_games.



Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

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

[GENERAL] Forward declaration of table

[Alexander Farber]

Good evening,

with PostgreSQL 9.5.3 I am using the following table to store 2-player
games:

DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile  varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);


This has worked well for me (when a user connects to the game server, I
send her all games she is taking part in), but then I have decided to add
another table to act as a "logging journal" for player moves:

DROP TABLE IF EXISTS words_moves;

DROP TYPE IF EXISTS words_action;
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);

Also, in the former table words_games I wanted to add references to the
latest moves performed by players:

-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,

The intention is: whenever a player connects to the server, sent her all
active games and status updates on the recent opponent moves.

However the 2 added columns do not work:

ERROR:  relation "words_moves" does not exist
ERROR:  relation "words_games" does not exist
ERROR:  relation "words_moves" does not exist


So my question is if I can somehow "forward declare" the words_moves table?

Here are all tables of my game for more context:
https://gist.github.com/afarber/c40b9fc5447335db7d24

Thank you
Alex

Re: [GENERAL] Sequential vs. random values - number of pages in B-tree

[Francisco Olarte]

Hi Rob:

On Tue, Aug 23, 2016 at 4:52 PM, Rob Sargent  wrote:
> By 'this' I was referring to the optimizations mentioned, and am wondering
> if this holds true under user load.

For that you'll have to refer to the source, or ask someone more
versed in pg source arcanes.

> Much magic can happen in a custom data
> load, but do these optimization apply to an application loading single (or
> perhaps several) records per transaction.  Does one, in that scenario, not
> suffer any consequence for continuously loading one side of the tree (the
> rightmost node?).

Not that much magic is neccesary. The time I did it I just needed to
detect on every insertion whether I was at the rightmost position (
made easier because I had minimum/maximum keys cached in the tree
object header ), and have a special routine for inserting a new last
node ( put in last page, whose pointer I had, grabbing a new one of
needed, whose pointer will be appended at the tail of the parent,
etc.., it was just a pruned down version of the general insert
routine, but made insertions run easily 20 times faster by avoiding
nearly every check knowing I was on the right edge ). I do not know if
pg inserts several items at a time in bulk loading, but I doubt it.
Normally every btree indexing library has some optimization for this
cases, as they are common, just like every real sort routine has some
optimization for presorted input.

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] Sequential vs. random values - number of pages in B-tree

[Rob Sargent]

On 08/23/2016 08:34 AM, Francisco Olarte wrote:

On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent  wrote:

On 08/23/2016 07:44 AM, Francisco Olarte wrote:

On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference... I did another, simplified
test,
to make sure there is no duplicates and the only difference between both
sets is the order:

...

INSERT INTO t_sequential SELECT * FROM source_sequential;
102258,949 ms
INSERT INTO t_random SELECT * FROM source_random;
1657575,699 ms

If I read correctly, you are getting 100s/10Mkeys=10us/key in
sequential, and 165 in random.

I'm not surprissed at all. I've got greater differences on a memory
tree, sorted insertion can be easily optimized to be very fast. AS an
example, sequential insertion can easily avoid moving data while
filling the pages and, with a little care, it can also avoid some of
them when splitting. I'm not current with the current postgres
details, but it does not surprise me they have big optimizations for
this, especially when index ordered insertion is quite common in
things like bulk loads or timestamped log lines.

And if each insert is in a separate transaction, does this still hold true?

What are you referring to by 'this'? ( BTW, bear in mind one
transaction needs at least a disk flush, and, if done via network, at
least one RTT, so I doubt you can achieve 10us/transaction unless you
have very special conditions ).

Francisco Olarte.
By 'this' I was referring to the optimizations mentioned, and am 
wondering if this holds true under user load.  Much magic can happen in 
a custom data load, but do these optimization apply to an application 
loading single (or perhaps several) records per transaction.  Does one, 
in that scenario, not suffer any consequence for continuously loading 
one side of the tree (the rightmost node?).



--
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] Sequential vs. random values - number of pages in B-tree

[Francisco Olarte]

On Tue, Aug 23, 2016 at 4:28 PM, Rob Sargent  wrote:
> On 08/23/2016 07:44 AM, Francisco Olarte wrote:
>> On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:
>>> I am just surprised by the order of magnitude in the difference though. 2
>>> and 27 minutes that's the huge difference... I did another, simplified
>>> test,
>>> to make sure there is no duplicates and the only difference between both
>>> sets is the order:
>>
>> ...
>>>
>>> INSERT INTO t_sequential SELECT * FROM source_sequential;
>>> 102258,949 ms
>>> INSERT INTO t_random SELECT * FROM source_random;
>>> 1657575,699 ms
>>
>> If I read correctly, you are getting 100s/10Mkeys=10us/key in
>> sequential, and 165 in random.
>>
>> I'm not surprissed at all. I've got greater differences on a memory
>> tree, sorted insertion can be easily optimized to be very fast. AS an
>> example, sequential insertion can easily avoid moving data while
>> filling the pages and, with a little care, it can also avoid some of
>> them when splitting. I'm not current with the current postgres
>> details, but it does not surprise me they have big optimizations for
>> this, especially when index ordered insertion is quite common in
>> things like bulk loads or timestamped log lines.

> And if each insert is in a separate transaction, does this still hold true?

What are you referring to by 'this'? ( BTW, bear in mind one
transaction needs at least a disk flush, and, if done via network, at
least one RTT, so I doubt you can achieve 10us/transaction unless you
have very special conditions ).

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] Sequential vs. random values - number of pages in B-tree

[Rob Sargent]

On 08/23/2016 07:44 AM, Francisco Olarte wrote:

Hi pinker:

On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference... I did another, simplified test,
to make sure there is no duplicates and the only difference between both
sets is the order:

...

INSERT INTO t_sequential SELECT * FROM source_sequential;
102258,949 ms
INSERT INTO t_random SELECT * FROM source_random;
1657575,699 ms

If I read correctly, you are getting 100s/10Mkeys=10us/key in
sequential, and 165 in random.

I'm not surprissed at all. I've got greater differences on a memory
tree, sorted insertion can be easily optimized to be very fast. AS an
example, sequential insertion can easily avoid moving data while
filling the pages and, with a little care, it can also avoid some of
them when splitting. I'm not current with the current postgres
details, but it does not surprise me they have big optimizations for
this, especially when index ordered insertion is quite common in
things like bulk loads or timestamped log lines.

Francisco Olarte.



And if each insert is in a separate transaction, does this still hold true?




--
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] question on error during COPY FROM

[Francisco Olarte]

On Tue, Aug 23, 2016 at 4:06 PM, Rakesh Kumar
 wrote:
> Is it true that one datafile in PG can only belong to one object (table/index)

If this is a question, yes, AFAIK ( in fact they are split in 1G
chunks to prevent problems with quirky filesystems ). Search for "Each
table and index is stored in a separate file" in
https://www.postgresql.org/docs/9.5/static/storage-file-layout.html


A full periodic read, even if superficial, of the postgres manual, is
quite beneficial. I feel poetic today ;-> . Seriously, is quite well
written and full of interesting information, even if skipping large
chunks knowing where the info is can sava you a lot of work and mails.
AAMOF, it's one of the main reasons I've been using postgres all this
years.

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] question on error during COPY FROM

[Adrian Klaver]

On 08/23/2016 07:06 AM, Rakesh Kumar wrote:

Is it true that one datafile in PG can only belong to one object (table/index)


Yes, assuming by datafile you mean an on disk file. Though one object 
may have many in disk files associated with it:


https://www.postgresql.org/docs/9.5/static/storage-file-layout.html

Details start just under Table 63-1. Contents of PGDATA




On Tue, Aug 23, 2016 at 9:55 AM, Francisco Olarte
 wrote:

On Tue, Aug 23, 2016 at 2:32 PM, Ilya Kazakevich
 wrote:

does that mean that I should always execute a VACUUM to recover the
wasted space when an error is triggered or will the auto-vacuum mechanism
do the job by itself ?

If you have autovacuum enabled it will clean up tablespace. However, space will 
not be returned to filesystem but will be reused by database.
You may run VACUUM FULL manually to return it to filesystem.


A normal vacuum may also return some space, specially after a big bulk
load, see second paragraph of 23.1.2 the URL you posted:

https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html


Where it says "However, it will not return the space to the operating
system, except in the special case where one or more pages at the end
of a table become entirely free and an exclusive table lock can be
easily obtained.". A big aborted bulk load may just fit the case, as
it may put a lot of tuples at new pages at the end and be executed in
a low-load period where the lock is easier to acquire.


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






--
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] question on error during COPY FROM

[Rakesh Kumar]

Is it true that one datafile in PG can only belong to one object (table/index)

On Tue, Aug 23, 2016 at 9:55 AM, Francisco Olarte
 wrote:
> On Tue, Aug 23, 2016 at 2:32 PM, Ilya Kazakevich
>  wrote:
>>>does that mean that I should always execute a VACUUM to recover the
>>>wasted space when an error is triggered or will the auto-vacuum mechanism
>>>do the job by itself ?
>> If you have autovacuum enabled it will clean up tablespace. However, space 
>> will not be returned to filesystem but will be reused by database.
>> You may run VACUUM FULL manually to return it to filesystem.
>
> A normal vacuum may also return some space, specially after a big bulk
> load, see second paragraph of 23.1.2 the URL you posted:
>> https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html
>
> Where it says "However, it will not return the space to the operating
> system, except in the special case where one or more pages at the end
> of a table become entirely free and an exclusive table lock can be
> easily obtained.". A big aborted bulk load may just fit the case, as
> it may put a lot of tuples at new pages at the end and be executed in
> a low-load period where the lock is easier to acquire.
>
>
> 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


-- 
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] Why insertion throughput can be reduced with an increase of batch size?

[Adrian Klaver]

On 08/22/2016 11:49 PM, Павел Филонов wrote:

Please reply to list also.
Ccing list




2016-08-23 4:02 GMT+03:00 Adrian Klaver >:

On 08/21/2016 11:53 PM, Павел Филонов wrote:

My greetings to everybody!

I recently faced with the observation which I can not explain. Why
insertion throughput can be reduced with an increase of batch size?

Brief description of the experiment.

  * PostgreSQL 9.5.4 as server
  * https://github.com/sfackler/rust-postgres
 library as client driver
  * one relation with two indices (scheme in attach)

Experiment steps:

  * populate DB with 25920 random records
  * start insertion for 60 seconds with one client thread and
batch size = m
  * record insertions per second (ips) in clients code

Plot median ips from m for m in [2^0, 2^1, ..., 2^15] (in
attachment).


On figure with can see that from m = 128 to m = 256 throughput
have been
reduced from 13 000 ips to 5000.

I hope someone can help me understand what is the reason for
such behavior?


To add to Jeff's questions:

You say you are measuring the IPS in the clients code.

Where is the client, on the same machine, same network or remote
network?


Postgres runs in docker and client runs on the host machine. Client
connects to localhost interface.


I don't use Docker so I have no insights here.




--
Best regards
Filonov Pavel





--
Adrian Klaver
adrian.kla...@aklaver.com 




--
С наилучшими пожеланиями,
Филонов Павел



--
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] question on error during COPY FROM

[Francisco Olarte]

On Tue, Aug 23, 2016 at 2:32 PM, Ilya Kazakevich
 wrote:
>>does that mean that I should always execute a VACUUM to recover the
>>wasted space when an error is triggered or will the auto-vacuum mechanism
>>do the job by itself ?
> If you have autovacuum enabled it will clean up tablespace. However, space 
> will not be returned to filesystem but will be reused by database.
> You may run VACUUM FULL manually to return it to filesystem.

A normal vacuum may also return some space, specially after a big bulk
load, see second paragraph of 23.1.2 the URL you posted:
> https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html

Where it says "However, it will not return the space to the operating
system, except in the special case where one or more pages at the end
of a table become entirely free and an exclusive table lock can be
easily obtained.". A big aborted bulk load may just fit the case, as
it may put a lot of tuples at new pages at the end and be executed in
a low-load period where the lock is easier to acquire.


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] Sequential vs. random values - number of pages in B-tree

[Francisco Olarte]

Hi pinker:

On Tue, Aug 23, 2016 at 2:26 PM, pinker  wrote:
> I am just surprised by the order of magnitude in the difference though. 2
> and 27 minutes that's the huge difference... I did another, simplified test,
> to make sure there is no duplicates and the only difference between both
> sets is the order:
...
> INSERT INTO t_sequential SELECT * FROM source_sequential;
> 102258,949 ms
> INSERT INTO t_random SELECT * FROM source_random;
> 1657575,699 ms

If I read correctly, you are getting 100s/10Mkeys=10us/key in
sequential, and 165 in random.

I'm not surprissed at all. I've got greater differences on a memory
tree, sorted insertion can be easily optimized to be very fast. AS an
example, sequential insertion can easily avoid moving data while
filling the pages and, with a little care, it can also avoid some of
them when splitting. I'm not current with the current postgres
details, but it does not surprise me they have big optimizations for
this, especially when index ordered insertion is quite common in
things like bulk loads or timestamped log lines.

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] question on error during COPY FROM

[Ilya Kazakevich]

Hi.

>does that mean that I should always execute a VACUUM to recover the
>wasted space when an error is triggered or will the auto-vacuum mechanism
>do the job by itself ?


If you have autovacuum enabled it will clean up tablespace. However, space will 
not be returned to filesystem but will be reused by database.
You may run VACUUM FULL manually to return it to filesystem.

https://www.postgresql.org/docs/9.1/static/routine-vacuuming.html


Ilya Kazakevich

JetBrains
http://www.jetbrains.com
The Drive to Develop



-- 
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] Sequential vs. random values - number of pages in B-tree

[pinker]

Francisco Olarte wrote
> It's already been told that btrees work that way, if you find itstrange
> read a bit about them, this is completely normal, but ...

I am just surprised by the order of magnitude in the difference though. 2
and 27 minutes that's the huge difference...I did another, simplified test,
to make sure there is no duplicates and the only difference between both
sets is the order:
CREATE TABLE source_sequential AS SELECT s from generate_series(1,1000)
as s; CREATE TABLE  source_randomAS SELECT * from source_sequential
ORDER BY random();CREATE TABLE t_sequential (id bigint);CREATE INDEX
i_sequential ON t_sequential (id);CREATE TABLE t_random (id bigint);CREATE
INDEX i_random ON t_random (id);INSERT INTO t_sequential SELECT * FROM
source_sequential;*102258,949 ms*INSERT INTO t_random SELECT * FROM
source_random;*1657575,699 ms*




--
View this message in context: 
http://postgresql.nabble.com/Sequential-vs-random-values-number-of-pages-in-B-tree-tp5916956p5917292.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] question on error during COPY FROM

[Jerome Wagner]

Hello,

in the documentation I read
https://www.postgresql.org/docs/current/static/sql-copy.html


COPY stops operation at the first error. This should not lead to problems
in the event of a COPY TO, but the target table will already have received
earlier rows in a COPY FROM. These rows will not be visible or accessible,
but they still occupy disk space. This might amount to a considerable
amount of wasted disk space if the failure happened well into a large copy
operation. You might wish to invoke VACUUM to recover the wasted space.

does that mean that I should always execute a VACUUM to recover the wasted
space when an error is triggered or will the auto-vacuum mechanism do the
job by itself ?

Thanks
Jerome