Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: > On 7/27/2016 9:39 PM, Jeff Janes wrote: >> >> That depends on how how many objects there are consuming that 1 TB. >> With millions of small objects, you will have problems. Not as many >> in 9.5 as there were in 9.1, but still it does not sc

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28/07/16 17:52, Jason Dusek wrote: With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions. https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS Would rei

[GENERAL] WAL directory size calculation

Hi folks! :-) I'm about to bring up my brand new production server and I was wondering if it's possible to calculate (approx.) the WAL directory size. I have to choose what's better in terms of cost vs. performance (we are on Google Cloud Platform) between a ramdisk or a separate persistent dis

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 9:25 AM, Moreno Andreo wrote: > I've read somewhere that the formula should be 16 MB * 3 * > checkpoint_segment in size. > ​[...]​ > Using the above formula I have: > 16 MB * 3 * 1 GB > that leads to to ... uh .. 48000 TB? > ​You seem to be mis-remembering the formu

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless wrote: > On 27 July 2016 at 15:22, Scott Mead wrote: >> >> "The bug we ran into only affected certain releases of Postgres 9.2 and >> has been fixed for a long time now. However, we still find it worrisome that >> this class of bug can happen at al

Re: [GENERAL] Uber migrated from Postgres to MySQL

If we're talking about favourite bug https://bugs.mysql.com/bug.php?id=21153 is mine Join with many tables hangs mysql (and taking 100% cpu) > Description: > the following query hangs the mysql server taking 100% cpu. also an > "explain" of the query hangs the server! It's "not a bug" because yo

Re: [GENERAL] WAL directory size calculation

Am 28.07.2016 um 15:25 schrieb Moreno Andreo: Hi folks! :-) I'm about to bring up my brand new production server and I was wondering if it's possible to calculate (approx.) the WAL directory size. I have to choose what's better in terms of cost vs. performance (we are on Google Cloud Platform)

Re: [GENERAL] GIN Indexes: Extensibility

Vick, you are right. That’s why I’d wish to add some custom code to MY PostgreSQL instance and set such a sort order, which is optimal for my application. On Jul 27, 2016, at 17:44, Vick Khera wrote: > On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich > wrote: >> In my situation this order is i

Re: [GENERAL] GIN Indexes: Extensibility

Paul, This is a really valuable idea. It will work in some situations for me. But in other situations I do not know if table will have a key of type int[] or string[] or even mixed. That’s why I’d wish to use JSON arrays and customize sort ordering. Anyway I appreciate you shared this approach

[GENERAL] Multiple NOTIFY is ignored

Hi, I've a question to the pgsql NOTIFY feature... I use something like SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT dblink_disconnect('{4}'); where {3} is NOTIFY upd_pgm, '0|UPDATE|A|' SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT dbli

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 27.07.2016 19:51, Rakesh Kumar wrote: On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian wrote: Yes. I was saying I don't know how to improve pg_upgrade to address it. This problem is there even in oracle/db2/sqlserver. None of them allow rollback to the lower version unless it is a minor v

Re: [GENERAL] WAL directory size calculation

Il 28/07/2016 15:54, Andreas Kretschmer ha scritto: Am 28.07.2016 um 15:25 schrieb Moreno Andreo: Hi folks! :-) I'm about to bring up my brand new production server and I was wondering if it's possible to calculate (approx.) the WAL directory size. I have to choose what's better in terms of co

Re: [GENERAL] Multiple NOTIFY is ignored

On 07/28/2016 07:29 AM, Markus Kolb wrote: Hi, I've a question to the pgsql NOTIFY feature... I use something like SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT dblink_disconnect('{4}'); where {3} is NOTIFY upd_pgm, '0|UPDATE|A|' SELECT dblink_connect('{0}', '{1}');

Re: [GENERAL] Multiple NOTIFY is ignored

Am 28.07.2016 16:42, schrieb Adrian Klaver: On 07/28/2016 07:29 AM, Markus Kolb wrote: [...] So why the 2nd notify is not delivered/recognized? At a guess the second NOTIFY is not completed by ; It is no problem of syntax. If this would be the case I would get an error/exception. There is

Re: [GENERAL] Server side backend permanent session memory usage ?

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Wednesday, July 27, 2016 4:50 PM To: Day, David Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Server side backend permanent session memory usage ? "Day, David" writes: > We have a server side function written

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov wrote: > Sorry, what? You can rollback to previous version of software what you had. > > https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007 Not so fast. This requires the db to be set in compatibility mode of the lower ver

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28.07.2016 17:50, Rakesh Kumar wrote: On Thu, Jul 28, 2016 at 10:38 AM, Alex Ignatov wrote: Sorry, what? You can rollback to previous version of software what you had. https://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm#UPGRD007 Not so fast. This requires the db to be se

Re: [GENERAL] Multiple NOTIFY is ignored

On Thu, Jul 28, 2016 at 10:29 AM, Markus Kolb < markus.kolb+postg...@tower-net.de> wrote: > If I only do one > SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT > dblink_disconnect('{4}'); > where {3} is > NOTIFY upd_pgm, '0|UPDATE|A|'; NOTIFY upd_pgm, '0|DELETE|A|' > > only

Re: [GENERAL] Multiple NOTIFY is ignored

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of David G. Johnston Sent: Thursday, July 28, 2016 11:05 AM To: Markus Kolb Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Multiple NOTIFY is ignored On Thu, Jul 28, 2016 at 10:29 AM, Markus

Re: [GENERAL] Uber migrated from Postgres to MySQL

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alex Ignatov Sent: Thursday, July 28, 2016 10:59 AM To: Rakesh Kumar Cc: PostgreSQL General Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL On 28.07.2016 17:

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28.07.2016 18:09, Igor Neyman wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Alex Ignatov Sent: Thursday, July 28, 2016 10:59 AM To: Rakesh Kumar Cc: PostgreSQL General Subject: Re: [GENERAL] Uber migrated

Re: [GENERAL] Uber migrated from Postgres to MySQL

-Original Message- From: Alex Ignatov [mailto:a.igna...@postgrespro.ru] Sent: Thursday, July 28, 2016 11:26 AM To: Igor Neyman ; Rakesh Kumar Cc: PostgreSQL General Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL On 28.07.2016 18:09, Igor Neyman wrote: > -Original Mes

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28 July 2016 at 16:34, Igor Neyman wrote: > Which means that you can make use of some new feature, but definitely not > all. > That makes "downgrade" feature very, very limited, if useful at all. > ​ Sufficient to allow you to run the upgrade, find that there's a catastrophic bug in the new v

Re: [GENERAL] Uber migrated from Postgres to MySQL

-Original Message- From: Alex Ignatov [mailto:a.igna...@postgrespro.ru] Sent: Thursday, July 28, 2016 11:26 AM To: Igor Neyman ; Rakesh Kumar Cc: PostgreSQL General Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL On 28.07.2016 18:09, Igor Neyman wrote: > -Original Mess

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe wrote: > On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless > wrote: > > On 27 July 2016 at 15:22, Scott Mead wrote: > >> > >> "The bug we ran into only affected certain releases of Postgres 9.2 and > >> has been fixed for a long time now. However, w

Re: [GENERAL] low perfomances migrating from 9.3 to 9.5

hi Melvin, Adrian, > > Where did you get the respective versions of Postgres? > both were compiled from source, from the official website tar files. gcc is 4.1.2 20080704 (Red Hat 4.1.2-48) > > Where they installed the same way? > yes, exactly the same. My configure command line is: ./configur

Re: [GENERAL] Multiple NOTIFY is ignored

Markus Kolb writes: > If I only do one > SELECT dblink_connect('{0}', '{1}'); SELECT dblink('{2}', '{3}'); SELECT > dblink_disconnect('{4}'); > where {3} is > NOTIFY upd_pgm, '0|UPDATE|A|'; NOTIFY upd_pgm, '0|DELETE|A|' > only the notify with UPDATE is recognized. I can't duplicate this. contri

Re: [GENERAL] WAL directory size calculation

Il 28/07/2016 15:33, David G. Johnston ha scritto: On Thu, Jul 28, 2016 at 9:25 AM, Moreno Andreo wrote: I've read somewhere that the formula should be 16 MB * 3 *

Re: [GENERAL] Multiple NOTIFY is ignored

On 07/28/2016 07:50 AM, Markus Kolb wrote: Am 28.07.2016 16:42, schrieb Adrian Klaver: On 07/28/2016 07:29 AM, Markus Kolb wrote: [...] So why the 2nd notify is not delivered/recognized? At a guess the second NOTIFY is not completed by ; It is no problem of syntax. If this would be the ca

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28.07.2016 18:41, Igor Neyman wrote: -Original Message- From: Alex Ignatov [mailto:a.igna...@postgrespro.ru] Sent: Thursday, July 28, 2016 11:26 AM To: Igor Neyman ; Rakesh Kumar Cc: PostgreSQL General Subject: Re: [GENERAL] Uber migrated from Postgres to MySQL On 28.07.2016 18:0

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 6:32 PM, Alex Ignatov wrote: > > On 28.07.2016 18:41, Igor Neyman wrote: > >> -Original Message- >> From: Alex Ignatov [mailto:a.igna...@postgrespro.ru] >> Sent: Thursday, July 28, 2016 11:26 AM >> To: Igor Neyman ; Rakesh Kumar < >> rakeshkumar46...@gmail.com> >>

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov wrote: > > Oh, so in contrast to "Oracle world" "Postgres world" DBA in their right > to do major upgrade without complete and tested backup? > Ok, I understand you. In Postgres world there always sky is blue and sun is > shining. Of course we hav

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28.07.2016 19:43, Scott Marlowe wrote: On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov wrote: Oh, so in contrast to "Oracle world" "Postgres world" DBA in their right to do major upgrade without complete and tested backup? Ok, I understand you. In Postgres world there always sky is blue

Re: [GENERAL] Uber migrated from Postgres to MySQL

> And? Oracle and MySql doesnt have it but can downgrade right out the box. > Quick and easy. No it is not for mysql. http://dev.mysql.com/doc/refman/5.7/en/downgrading.html "In-place Downgrade: Involves shutting down the new MySQL version, replacing the new MySQL binaries or packages with the o

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov wrote: > > On 28.07.2016 19:43, Scott Marlowe wrote: >> >> On Thu, Jul 28, 2016 at 10:32 AM, Alex Ignatov >> wrote: >>> >>> Oh, so in contrast to "Oracle world" "Postgres world" DBA in their >>> right >>> to do major upgrade without complete and t

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 07/28/2016 10:43 AM, Scott Marlowe wrote: On Thu, Jul 28, 2016 at 11:23 AM, Alex Ignatov wrote: And? Oracle and MySql doesnt have it but can downgrade right out the box. Quick and easy. So you can swap between oracle 11 and 12 back and forth in a live environment with no downtime? Please

Re: [GENERAL] Uber migrated from Postgres to MySQL

Em 28/07/2016 13:07, Chris Travers escreveu: On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe mailto:scott.marl...@gmail.com>> wrote: On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless mailto:pgsqlad...@geoff.dj>> wrote: > On 27 July 2016 at 15:22, Scott Mead mailto:sco...@openscg.com

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 3:25 PM, Moreno Andreo wrote: > Obviously ramdisk will be times faster disk, but having a, say, 512 GB > ramdisk will be a little too expensive :-) Besides defeating the purpose of WAL, if you are going to use non persistent storage for WAL you could as well use minimal le

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, 28 Jul 2016 19:32:39 +0300 Alex Ignatov wrote: > On 28.07.2016 18:41, Igor Neyman wrote: > > "Compactable" or not, in "Oracle world" no DBA in their right mind > > will attempt major upgrade without having complete (and tested) > > backup that could be used in case of upgrade going wrong.

[GENERAL] Using timestamp(tz) in C functions

Working on trying to get a C version of the maintenance function for my pg_partman extension working so I can hopefully make it more flexible and efficient. https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql Up until this point I've just been using SPI and the Dat

Re: [GENERAL] Using timestamp(tz) in C functions

On 07/28/2016 02:15 PM, Keith Fiske wrote: Working on trying to get a C version of the maintenance function for my pg_partman extension working so I can hopefully make it more flexible and efficient. https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql Up until th

Re: [GENERAL] Using timestamp(tz) in C functions

On Thu, Jul 28, 2016 at 5:28 PM, Adrian Klaver wrote: > On 07/28/2016 02:15 PM, Keith Fiske wrote: > >> Working on trying to get a C version of the maintenance function for my >> pg_partman extension working so I can hopefully make it more flexible >> and efficient. >> >> >> https://github.com/ke

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote: > On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: > > On 7/27/2016 9:39 PM, Jeff Janes wrote: > >> > >> That depends on how how many objects there are consuming that 1 TB. > >> With millions of small objects, you will have problems.

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 7/28/2016 3:16 PM, Bruce Momjian wrote: On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote: >On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: > >On 7/27/2016 9:39 PM, Jeff Janes wrote: > >> > >>That depends on how how many objects there are consuming that 1 TB. > >>With millio

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 03:26:17PM -0700, John R Pierce wrote: > Uh, that is only true if the slowness was in _dumping_ many objects. > Most of the fixes have been for _restoring_ many objects, and that is > done in the new cluster, so they should be OK. > > > I thought we were talkin

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 07/28/2016 03:16 PM, Bruce Momjian wrote: > On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote: >> On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce wrote: >>> On 7/27/2016 9:39 PM, Jeff Janes wrote: That depends on how how many objects there are consuming that 1 TB. With mi

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 07/28/2016 04:58 PM, Joe Conway wrote: On 07/28/2016 03:16 PM, Bruce Momjian wrote: Not really true. I ran into two separate cases where on older (pre 9.3 I believe) Postgres if you had hundreds of thousands of tables (in the case I remember well, it was about 500k tables) the schema dump fro

Re: [GENERAL] Uber migrated from Postgres to MySQL

On Thu, Jul 28, 2016 at 8:09 PM, Edson Richter wrote: > Em 28/07/2016 13:07, Chris Travers escreveu: > > > > On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe > wrote: > >> On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless >> wrote: >> > On 27 July 2016 at 15:22, Scott Mead wrote: >> >> >> >> "The

Re: [GENERAL] Using timestamp(tz) in C functions

On 7/28/16, Keith Fiske wrote: > Working on trying to get a C version of the maintenance function for my > pg_partman extension working so I can hopefully make it more flexible and > efficient. > > https://github.com/keithf4/pg_partman/blob/master/sql/functions/run_maintenance.sql > > Up until thi

Re: [GENERAL] WAL directory size calculation

On Thu, Jul 28, 2016 at 9:54 AM, Andreas Kretschmer wrote: > Without Replication 1 GB would be fine, even with replication. But it must > be realible! > > The required size of WAL depends on what your intended checkpoint_timeout vs. the amount of WAL generated from data turnover is. A rather smal

Re: [GENERAL] Uber migrated from Postgres to MySQL

On 28/07/2016 21:09, Edson Richter wrote: Em 28/07/2016 13:07, Chris Travers escreveu: On Thu, Jul 28, 2016 at 3:38 PM, Scott Marlowe mailto:scott.marl...@gmail.com>> wrote: On Wed, Jul 27, 2016 at 9:51 AM, Geoff Winkless mailto:pgsqlad...@geoff.dj>> wrote: > On 27 July 2016 at 15:22,