Re: [GENERAL] Forward declaration of table

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

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

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

Re: [GENERAL] Forward declaration of table

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

[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

Re: [GENERAL] Forward declaration of table

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,

Re: [GENERAL] Forward declaration of table

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

[GENERAL] Pentaho Odoo PSQL Slave.

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

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

Re: [GENERAL] Foreign key against a partitioned table

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

Re: [GENERAL] Forward declaration of table

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

Re: [GENERAL] PG vs ElasticSearch for Logs

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

Re: [GENERAL] Foreign key against a partitioned table

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

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

Re: [GENERAL] Foreign key against a partitioned table

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

Re: [GENERAL] Foreign key against a partitioned table

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

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

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

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

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.

[GENERAL] Determining table change in an event trigger

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

Re: [GENERAL] Forward declaration of table

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

[GENERAL] Determining table change in an event trigger

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

[GENERAL] question on error during COPY FROM

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

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

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,

Re: [GENERAL] question on error during COPY FROM

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

Re: [GENERAL] question on error during COPY FROM

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

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

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

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

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

Re: [GENERAL] question on error during COPY FROM

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

Re: [GENERAL] Why insertion throughput can be reduced with an increase of batch size?

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

Re: [GENERAL] question on error during COPY FROM

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:

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

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

Re: [GENERAL] question on error during COPY FROM

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