Re: [GENERAL] Extension to rewrite queries before execution

> I am looking for an extension or a technique that will allow me to > intercept a query by the exact query text, and replace that query with a > different one. > > The context is running a third-party app which issues queries I have no > control over. I'd like to intercept a specific query (whic

Re: [GENERAL] Extension to rewrite queries before execution

Hi, Le 13 août 2015 9:51 PM, "Jeff Janes" a écrit : > > I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. > > The context is running a third-party app which issues queries I have no control ov

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On Thu, Aug 13, 2015 at 7:26 PM, Stephen Feyrer < stephen.fey...@btinternet.com> wrote: > When we design databases, invariably, normally we design the queries at > the same time. > ​Well this may be true to an extent well implemented models have the ability to answer questions (queries) the origi

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On Fri, 14 Aug 2015 01:58:29 +0100, Adrian Klaver wrote: On 08/13/2015 05:40 PM, Stephen Feyrer wrote: On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver wrote: On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't been abl

Re: [GENERAL] Migrations

On 08/13/2015 05:37 PM, Martín Marqués wrote: El 13/08/15 a las 21:23, Guyren Howe escribió: I also think migrations ought to be a first-class feature… What do you mean with "migrations ought to be a first-class feature"? There have been, and there still are efforts for making upgrading as s

Migrations (was: [GENERAL] First-class Polymorphic joins?)

El 13/08/15 a las 21:23, Guyren Howe escribió: > > I also think migrations ought to be a first-class feature… What do you mean with "migrations ought to be a first-class feature"? There have been, and there still are efforts for making upgrading as smooth and simple as possible, but I'm not real

Re: [GENERAL] First-class Polymorphic joins?

On 08/13/2015 05:59 PM, Guyren Howe wrote: Ccing list On Aug 13, 2015, at 17:49 , Adrian Klaver wrote: A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. I am pretty sure it already does that: http://www.postgresql.org/docs/9.4/interac

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On 08/13/2015 05:40 PM, Stephen Feyrer wrote: On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver wrote: On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't been able to find an answer for. Basically, the question is why is th

Re: [GENERAL] First-class Polymorphic joins?

On 08/13/2015 05:23 PM, Guyren Howe wrote: It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature. A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. I am pretty sure it already does that: http:

Re: [GENERAL] [BDR] vs pgpool-II v3

For your information, I would like to explain a little bit more about pgpool-II own replication (we call it "native replication mode" to separate from streaming replication or slony replication). In the pgpool-II's native replication mode, for a start pgpool-II sends the query to "master" PostgreS

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On Fri, 14 Aug 2015 01:14:12 +0100, Adrian Klaver wrote: On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't been able to find an answer for. Basically, the question is why is there not an equivalent foreign key concept to

Re: [GENERAL] Extension to rewrite queries before execution

El 13/08/15 a las 17:37, Melvin Davidson escribió: > You have not stated which Version or PostgreSQL, He said it was for 9.4. >> Is there anything out there like this? This would be for 9.4. -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Tr

[GENERAL] First-class Polymorphic joins?

It surprises me that no SQL database to my knowledge has polymorphic joins as a first-class feature. A polymorphic join is where a fk contains not just an id but an indicator of which table it refers to. So you could have a "tags" table, that can attach tags to any of a variety of other tables.

Re: [GENERAL] Sync replication + high latency server

El 13/08/15 a las 11:41, Edson Richter escribió: > > Em 13/08/2015 00:40, Joshua D. Drake escreveu: >> >> On 08/12/2015 05:33 PM, Edson Richter wrote: >>> Hi! >>> >>> I've a situation where I would like to keep sync replication, where >>> servers have 10Mbps network connection but high latency (no

Re: [GENERAL] Foreign Keys as first class citizens at design time?

On 08/13/2015 05:03 PM, Stephen Feyrer wrote: Hi, This is probably not an original question merely one which I haven't been able to find an answer for. Basically, the question is why is there not an equivalent foreign key concept to match the primary key we all already know an love? How this w

[GENERAL] Foreign Keys as first class citizens at design time?

Hi, This is probably not an original question merely one which I haven't been able to find an answer for. Basically, the question is why is there not an equivalent foreign key concept to match the primary key we all already know an love? How this would work, would be that the foreign key

Re: [GENERAL] SELECT blocks UPDATE

twoflower writes: > Tom Lane-2 wrote >> So either the SELECT is a SELECT FOR UPDATE, or it's part of a transaction >> that's done datachanges in the past. > If these are the only two explanations, it must be the latter then. What I > still don't understand - these two statements are part of the s

Re: [GENERAL] SELECT blocks UPDATE

On 08/13/2015 02:14 PM, twoflower wrote: Further observation: Now I managed to get rid of the blocking. I am not sure if you are familiar with the NHibernate ORM, but it has a concept of a stateful and stateless sessions. Session holds a connection to the database and transaction is created on a

Re: [GENERAL] SELECT blocks UPDATE

Further observation: Now I managed to get rid of the blocking. I am not sure if you are familiar with the NHibernate ORM, but it has a concept of a stateful and stateless sessions. Session holds a connection to the database and transaction is created on a particular session. In this case, 'begin tr

Re: [GENERAL] [BDR] vs pgpool-II v3

El 13/08/15 a las 14:37, Wayne E. Seguin escribió: > The question is specifically about the replication feature mentioned here > http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of > failing over minimizing downtime. They aim a completely different problems. The thing Joshua m

Re: [GENERAL] Extension to rewrite queries before execution

On 08/13/2015 12:49 PM, Jeff Janes wrote: I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. What is sending the query? In other words what library is the app using to communicate with the

Re: [GENERAL] Extension to rewrite queries before execution

You have not stated which Version or PostgreSQL, nor the O/S involved. That being said, depending on what the specific query is, you might consider using a Rule or Trigger to handle it. If you use a Trigger ( which is the preferred method) you can also embed "set" commands the associated function.

Re: [GENERAL] SELECT blocks UPDATE

The Postgres version is 9.3.9. The actual output of the lock query is (I added *locktype* and *mode* columns from the *pg_locks* table) *blocked_pid*: 7574 *blocked_statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item = 1009184 WHERE id IN (47049861) *blocked_locktype*: transactionid *b

[GENERAL] Extension to rewrite queries before execution

I am looking for an extension or a technique that will allow me to intercept a query by the exact query text, and replace that query with a different one. The context is running a third-party app which issues queries I have no control over. I'd like to intercept a specific query (which has no bin

Re: [GENERAL] I am unable to install PostgreSql

I hope you realize that support for Windows 2003 ended July 2015 http://www.microsoft.com/en-us/server-cloud/products/windows-server-2003/ Plus you never mentioned WHICH VERSION of PostgreSQL you are trying to install. Perhaps you should try a supported version of PostgreSQL on a supported versio

Re: [GENERAL] [BDR] vs pgpool-II v3

(But thank you for the response!!!) On Thu, Aug 13, 2015 at 1:37 PM, Wayne E. Seguin wrote: > The question is specifically about the replication feature mentioned here > http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of > failing over minimizing downtime. > > On Thu, Aug 13

Re: [GENERAL] [BDR] vs pgpool-II v3

The question is specifically about the replication feature mentioned here http://www.pgpool.net/mediawiki/index.php/Main_Page for the purposes of failing over minimizing downtime. On Thu, Aug 13, 2015 at 12:28 PM, Joshua D. Drake wrote: > > On 08/13/2015 08:52 AM, Wayne E. Seguin wrote: > > "BDR

Re: [GENERAL] [BDR] vs pgpool-II v3

On 08/13/2015 08:52 AM, Wayne E. Seguin wrote: "BDR is only one in Beta compare to stable options: https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling. Not sure why this solution would be chosen. Experience with pgpool is that you only needed to change a port and no

[GENERAL] [BDR] vs pgpool-II v3

Hello everyone! The context of this is using BDR to implement a HA solution where we have one node getting all connections at a time, if the node fails we move all connections to another node. (eg. only one node gets all connections at any given time). I am looking for help / advice on how to an

Re: [GENERAL] Sync replication + high latency server

On 08/13/2015 07:41 AM, Edson Richter wrote: Em 13/08/2015 00:40, Joshua D. Drake escreveu: On 08/12/2015 05:33 PM, Edson Richter wrote: Hi! I've a situation where I would like to keep sync replication, where servers have 10Mbps network connection but high latency (normally, ~20ms but someti

Re: [GENERAL] Sync replication + high latency server

Em 13/08/2015 00:40, Joshua D. Drake escreveu: On 08/12/2015 05:33 PM, Edson Richter wrote: Hi! I've a situation where I would like to keep sync replication, where servers have 10Mbps network connection but high latency (normally, ~20ms but sometimes, 1000ms~2000ms, even 3000ms when network i

Re: [GENERAL] Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)

"=?UTF-8?Q?Mart=c3=adn_Marqu=c3=a9s?=" writes: > El 12/08/15 a las 11:12, Tom Lane escribió: >> It does not seem likely to me that this would work at all. You're taking >> a dump file that is full of LATIN9 data and simply asserting that it's >> UTF8 data. That doesn't make it so. If it seemed

Re: [GENERAL] PostgreSQL - The Best Overall Database

On Thu, 13 Aug 2015 09:46:49 -0400, Melvin Davidson wrote: On Thu, Aug 13, 2015 at 9:21 AM, John McKown wrote: On Thu, Aug 13, 2015 at 8:03 AM, Melvin Davidson wrote: This should put a smile on all PostgreSQL DBA's faces. The Best Overall Database ​Very nice. Of course, I have a "th

Re: [GENERAL] SELECT blocks UPDATE

twoflower writes: > if I am reading the documentation on explicit locking > > > correctly, SELECT should never conflict with UPDATE. Pure SELECT, I would think not. But is it really a SELECT FOR UPDATE?

Re: [GENERAL] SELECT blocks UPDATE

On 08/13/2015 06:39 AM, twoflower wrote: Hello, if I am reading the documentation on explicit locking correctly, SELECT should never conflict with UPDATE. However, what I am observing as a result of this mo

Re: [GENERAL] PostgreSQL - The Best Overall Database

I uinderstand and agree. In like fashion, I have a large dislike for MySQL and Access. But then again, are either of them really db's? :) On Thu, Aug 13, 2015 at 9:21 AM, John McKown wrote: > On Thu, Aug 13, 2015 at 8:03 AM, Melvin Davidson > wrote: > >> >> This should put a smile on all Postgr

[GENERAL] SELECT blocks UPDATE

Hello, if I am reading the documentation on explicit locking correctly, SELECT should never conflict with UPDATE. However, what I am observing as a result of this monitoring query: SELECT bl.pid AS bloc

Re: [GENERAL] PostgreSQL - The Best Overall Database

On Thu, Aug 13, 2015 at 8:03 AM, Melvin Davidson wrote: > > This should put a smile on all PostgreSQL DBA's faces. > > The Best Overall Database > > ​Very nice. Of course, I have a "thing" about both

[GENERAL] PostgreSQL - The Best Overall Database

This should put a smile on all PostgreSQL DBA's faces. The Best Overall Database *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.

Fwd: [GENERAL] repmgr won't update witness after failover

-- Forwarded message -- From: Aviel Buskila Date: 2015-08-13 15:43 GMT+03:00 Subject: Re: [GENERAL] repmgr won't update witness after failover To: Jony Cohen ‫‎‎‬ Hey, I have just tried to start the repmgrd on the new standby after I have fixed it as a standby and still this goes

Re: [GENERAL] repmgr won't update witness after failover

Hi Aviel, you can use the 'show cluster' command to see the repmgr state before you do the 2nd failover - make sure the node1 is indeed marked as replica. After a failover the Master doesn't automatically attach to the new master - you need to point him as a slave (standby follow - if possible...)

Re: [GENERAL] Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)

El 12/08/15 a las 11:12, Tom Lane escribió: > Adrian Klaver writes: >> On 08/12/2015 06:46 AM, Bianchi Quota Leonardo wrote: >> Hi, I'm trying to move a db from postgres 8.1 encoded LATIN9 from a >> debian 4.0 box to postgres 8.4 encoded UTF8 on a rh6.6 (the whole job >> is to dismiss the old serv

[GENERAL] repmgr won't update witness after failover

Hey, I have set up three nodes of postgresql 9.4 with repmgr in this way: 1. master - node1 2. standby - node2 3. witness - node3 Now I have set up the replication and the witness as it says here: https://github.com/2ndQuadrant/repmgr/blob/master/FAILOVER.rst Now when I do 'kill -9 $(pidof postma