Re: [GENERAL] Extension to rewrite queries before execution

[Tatsuo Ishii]

> 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 bind
> parameters) and either replace the query text with a different text which,
> for example, swaps out an "in list" clause to instead be an "exists
> (subquery)".
> 
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.
> 
> Is there anything out there like this?  This would be for 9.4.
> 
> I'm willing to put the query text, and its replacement, directly into the
> extension source code and compile it, but of course something more flexible
> would be ideal.

I don't know if you are interested in using other than extensions, but
you could do it by hacking pgpool-II. It has built-in raw parser with
some functions to walk through the tree such as
raw_expression_tree_walker().  Also you could do the rewriting only
for particular applications (of course only if the application set
application name).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


-- 
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] Extension to rewrite queries before execution

[Guillaume Lelarge]

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 over.  I'd like to intercept a specific query (which has no bind
parameters) and either replace the query text with a different text which,
for example, swaps out an "in list" clause to instead be an "exists
(subquery)".
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into the
extension source code and compile it, but of course something more flexible
would be ideal.
>

I know of no extension that does this, even if the idea is rather
interesting. I don't remember if there is any hook that would allow such a
thing. But that is definitely interesting.

-- 
Guillaume.

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

[David G. Johnston]

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 original designer never thought
of or that were not important at the time.​

​As for the rest - ​invest in a good graphical query builder (or write one
if the existing choices are insufficient).

David J.

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

[Stephen Feyrer]

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 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 field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.

Therefore as an example:

{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket

List","user-attribute":"bucket.l...@example.com"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supern...@example.com"},

{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactio...@transaction.org"},

{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmys...@lovesspam.com"},

{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relatio...@transaction.org"}}


SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED

This would yield

directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam

Alternatively linking two user tables - profiles and contacts

profiles
PK-profiles
user-name
real-name
age
gender
region


contacts
PK-contacts
FK-profiles
phone
email
icq
home-page

Getting the user-name and email would look something like:

SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN


When building our databases we already put a lot of work in  
normalising

as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with  
all

that referential integrity in mind.

The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.

To me this looks like a good idea.


What happens if you have more then one child table with the same field?

So:

contacts
FK-profiles

email


vendors
FK-profiles

email





In that case the result table would look something like:

SELECT email FROM profiles WITH-IMPLICIT-JOIN

'contacts-email','vendors-email'


So what if you want to use a different alias?


That is a good question, the point of making foreign keys links into  
another table is an attempt to reduce verbosity.  Admittedly with that  
reduction you can lose expressiveness.  Given that you would know the  
semantics of the naming scheme you could use:


SELECT contacts-email AS Econtacts, vendor-email AS 'Evendor' FROM  
profiles WITH-IMPLICIT-JOIN


This syntax is not far removed from the regular syntax anyway.  In other  
words, if I'd thought to use the tablename.field nomenclature in the first  
place you probably wouldn't have asked that question (I think).  The  
regular method should not magically disappear just because you've got a  
new tool in your box of tricks.



SELECT contacts.email AS Econtacts  AS Econtacts FROM  
profies JOIN contacts...





What if you only wanted the contacts email and not the vendors?


As I see it, there are two possible ways this might work example would  
give the contacts email only.


SELECT contacts-email FROM profiles WITH-IMPLICIT-JOIN

or

SELECT contacts-email AS Econtacts, vendor-email AS '' FROM profiles  
WITH-IMPLICIT-JOIN



I prefer the former example as it is less verbose but retains the  
specificity.  Some might argue the latter is more readable others that it  
is more confusing, I'd say both.



I see the example below, but now you are changing direction for what I  
consider no good reason.


No, the reason for the change of direction is that there is a join  
happening, in the case of the implicit join the table holding the foreign  
key is to the right so it would look like a right join.





Or to follow a reverse semantic:

SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)


The problem that I see is the current method is self-documenting whereas  
implicit joins means you have to 'know' what is implied. This means some  
other mechanism to discover what is implied. Seems more complicated then  
the present situation.


Once you understand the semantics (should they ever exist beyond this  
discussion) of this system it will be self documenting also.  You'll be  
able to see that a foreign key one table is the primary key in another.   
Admittedly this wasn't well represented in my example 

Re: [GENERAL] Migrations

[Adrian Klaver]

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
smooth and simple as possible, but I'm not really sure where you want to
get with this.


I think Guyren is talking about something like Django or Rails 
migrations, Alembic, Sqitch, etc. A way to do changes to database 
objects either whole or as incremental changes. Basically a schema 
versioning method.




Cheers,




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

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

[Martín Marqués]

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 really sure where you want to
get with this.

Cheers,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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] First-class Polymorphic joins?

[Adrian Klaver]

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/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]


I apologize for not being clearer.

The point is that the fk in different rows can reference different tables. I 
might want to be able to attach a tag to a person or a blog post, say. And then 
I want to find all the persons and blog posts with a particular tag, in a 
single query.


Could you just not turn that around?:

tag
  tag_id
  tag_desc

person
  person_id
  tag_fk references tag

blog
  blog_id
  tag_fk references tag



The simplest implementation is to have a table reference as a first-class value 
I can store in a field.




--
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 Keys as first class citizens at design time?

[Adrian Klaver]

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 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 field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.

Therefore as an example:

{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket

List","user-attribute":"bucket.l...@example.com"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supern...@example.com"},

{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactio...@transaction.org"},

{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmys...@lovesspam.com"},

{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relatio...@transaction.org"}}


SELECT directory-name FROM system WITH-IMPLICIT-JOIN
WHERE-PK-IS-NOT-LINKED

This would yield

directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam

Alternatively linking two user tables - profiles and contacts

profiles
PK-profiles
user-name
real-name
age
gender
region


contacts
PK-contacts
FK-profiles
phone
email
icq
home-page

Getting the user-name and email would look something like:

SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN


When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.

The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily
coded.

To me this looks like a good idea.


What happens if you have more then one child table with the same field?

So:

contacts
FK-profiles

email


vendors
FK-profiles

email





In that case the result table would look something like:

SELECT email FROM profiles WITH-IMPLICIT-JOIN

'contacts-email','vendors-email'


So what if you want to use a different alias?

What if you only wanted the contacts email and not the vendors?

I see the example below, but now you are changing direction for what I 
consider no good reason.





Or to follow a reverse semantic:

SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right
join)


The problem that I see is the current method is self-documenting whereas 
implicit joins means you have to 'know' what is implied. This means some 
other mechanism to discover what is implied. Seems more complicated then 
the present situation.




This would give you
'age','region','email'

Whereas:

For a simple vendors table which might look like:

brand
market
email
rating

SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a
right join)

'brand','region','email'



One point I would like to make clear, is that the foreign key linking
should be a design choice.






--
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] First-class Polymorphic joins?

[Adrian Klaver]

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://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

REFERENCES reftable [ ( refcolumn ) ]

 So you could have a "tags" table, that can attach tags to any of a 
variety of other tables. Rails handles this by including the table name 
as a string.


This seems a reasonable thing to want to do, and it seems that the database 
could handle it by combining the fields from the target tables in the result.

I also think migrations ought to be a first-class feature…


So is this the push the ORM into the database day?






--
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] [BDR] vs pgpool-II v3

[Tatsuo Ishii]

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" PostgreSQL node, which is the first one in
the configuration file. After the PostgreSQL returns response,
pgpool-II sends the query to the second PostgreSQL node. This is to
avoid deadlock between DB nodes. If you have third or fourth DB node,
pgpool-II sends the query concurrently with the second node. So in
theory pgpool-II's performance in DML/DDL is no less than 50%
comparing with single PostgreSQL regardless number of DB nodes.

There's one optimization however. If there's only one concurrent
sessions (i.e. the number pgpool-II preforked child process is 1),
then pgpool-II sends DML/DDL to all the DB nodes at once because
there's no possibilty of deadlock. In this case the DML/DDL
performance is equivalent to single PostgreSQL in theory.

I remember a user deployed inter continental pgpool-II
configuration. If my memory serves, the continentals were Australia
and North America.

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

> 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 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 SQL changes required on an
>>> application side."
>>>
>>> I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
>>>
>>
>> BDR is async multi master. PGPool is largely just a connection pooler with
>> load balancing (although it has other features). They play different sports.
>>
>> JD
>>
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
>> PostgreSQL Centered full stack support, consulting and development.
>> Announcing "I'm offended" is basically telling the world you can't
>> control your own emotions, so everyone else should do it for you.
>>
> 
> 
> 
> -- 
>   ~Wayne
> 
> Wayne E. Seguin
> wayneeseg...@gmail.com
> wayneeseguin on irc.freenode.net
> http://twitter.com/wayneeseguin/
> https://github.com/wayneeseguin/


-- 
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 Keys as first class citizens at design time?

[Stephen Feyrer]

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 match the primary key we all already know an love?

How this would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.

Therefore as an example:

{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket
List","user-attribute":"bucket.l...@example.com"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supern...@example.com"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactio...@transaction.org"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmys...@lovesspam.com"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relatio...@transaction.org"}}

SELECT directory-name FROM system WITH-IMPLICIT-JOIN  
WHERE-PK-IS-NOT-LINKED


This would yield

directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam

Alternatively linking two user tables - profiles and contacts

profiles
PK-profiles
user-name
real-name
age
gender
region


contacts
PK-contacts
FK-profiles
phone
email
icq
home-page

Getting the user-name and email would look something like:

SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN


When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.

The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily  
coded.


To me this looks like a good idea.


What happens if you have more then one child table with the same field?

So:

contacts
FK-profiles

email


vendors
FK-profiles

email





In that case the result table would look something like:

SELECT email FROM profiles WITH-IMPLICIT-JOIN

'contacts-email','vendors-email'

Or to follow a reverse semantic:

SELECT age, region, email FROM contacts WITH-IMPLICIT-JOIN (like a right  
join)


This would give you
'age','region','email'

Whereas:

For a simple vendors table which might look like:

brand
market
email
rating

SELECT brand, region, email FROM vendors WITH-IMPLICIT-JOIN (like a right  
join)


'brand','region','email'



One point I would like to make clear, is that the foreign key linking  
should be a design choice.




--
Kind regards


Stephen Feyrer


--
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] Extension to rewrite queries before execution

[Martín Marqués]

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

[GENERAL] First-class Polymorphic joins?

[Guyren Howe]

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. Rails handles this by including the 
table name as a string.

This seems a reasonable thing to want to do, and it seems that the database 
could handle it by combining the fields from the target tables in the result.

I also think migrations ought to be a first-class feature…

-- 
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] Sync replication + high latency server

[Martín Marqués]

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 (normally, ~20ms
>>> but sometimes, 1000ms~2000ms, even 3000ms when network is under load).
>>>
>>> Considering that I will keep enough WAL files (let's say, 200 WAL
>>> segments on a server with low write rate):
>>>
>>> What happens if I configure timeout in master server for, let's say, 10
>>> seconds?
>>> Will sync replication survive if timeout happens, and then, network
>>> "recover" it self?
>>
>> I think you are misunderstanding how sync rep works.
> 
> I don't think so: sync replication will force commit on both databases
> before returning OK to my app server.

So, you are ok with your app waiting more than 3s (actually 3s + the
actual commit time from both servers) to commit?

This doesn't seem acceptable.

>>
>>>
>>> Or will I be obligated to use async replication in such scenario?
>>>
>>
>> async is your friend here.
> 
> And what about the timeout when using sync replication? What will happen?

There isn't any replication timeout GUC.

If your sync replica fails, your transactions will get stalled waiting
for the transaction to get committed on one of the sync replicas.

Do you get the idea (or actually the problem behind your design)?


-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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 Keys as first class citizens at design time?

[Adrian Klaver]

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 would work, would be that the foreign key field in the host
table would in fact simply be a reference to a key field in the guest
table. Then in the respective SQL syntax a semantic reference may then
be made whether or not to follow such links.

Therefore as an example:

{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket
List","user-attribute":"bucket.l...@example.com"},
{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supern...@example.com"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactio...@transaction.org"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmys...@lovesspam.com"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relatio...@transaction.org"}}

SELECT directory-name FROM system WITH-IMPLICIT-JOIN WHERE-PK-IS-NOT-LINKED

This would yield

directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam

Alternatively linking two user tables - profiles and contacts

profiles
PK-profiles
user-name
real-name
age
gender
region


contacts
PK-contacts
FK-profiles
phone
email
icq
home-page

Getting the user-name and email would look something like:

SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN


When building our databases we already put a lot of work in normalising
as much as we can. Then after all that work we have to virtually start
again building up select, insert and update statements etc. all with all
that referential integrity in mind.

The advantages of a first class foreign key field as I see it are at
least two fold. One it make building and maintaining your database
easier. Two it is a means to provide some iterative structures easily coded.

To me this looks like a good idea.


What happens if you have more then one child table with the same field?

So:

contacts
   FK-profiles
   
   email
   

vendors
   FK-profiles
   
   email
   





--
Kind regards


Stephen Feyrer



--
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] Foreign Keys as first class citizens at design time?

[Stephen Feyrer]

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 field in the host table  
would in fact simply be a reference to a key field in the guest table.  
Then in the respective SQL syntax a semantic reference may then be made  
whether or not to follow such links.


Therefore as an example:

{system:{"primary-key":"1","child-key":"","date":"20150421","directory-name":"Bucket  
List","user-attribute":"bucket.l...@example.com"},

{"primary-key":"2","child-key":"","date":"20150421","directory-name":"Supernova","user-attribute":"supern...@example.com"},
{"primary-key":"3","child-key":"5","date":"20150422","directory-name":"Transactional","user-attribute":"transactio...@transaction.org"},
{"primary-key":"4","child-key":"","date":"20150503","directory-name":"Spam","user-attribute":"allmys...@lovesspam.com"},
{"primary-key":"5","child-key":"","date":"20150506","directory-name":"Relational","user-attribute":"relatio...@transaction.org"}}

SELECT directory-name FROM system WITH-IMPLICIT-JOIN WHERE-PK-IS-NOT-LINKED

This would yield

directory-namedirectory-name
Bucket List
Supernova
Transactional Relational
Spam

Alternatively linking two user tables - profiles and contacts

profiles
PK-profiles
user-name
real-name
age
gender
region


contacts
PK-contacts
FK-profiles
phone
email
icq
home-page

Getting the user-name and email would look something like:

SELECT user-name, email FROM profiles WITH-IMPLICIT-JOIN


When building our databases we already put a lot of work in normalising as  
much as we can. Then after all that work we have to virtually start again  
building up select, insert and update statements etc. all with all that  
referential integrity in mind.


The advantages of a first class foreign key field as I see it are at least  
two fold. One it make building and maintaining your database easier. Two  
it is a means to provide some iterative structures easily coded.


To me this looks like a good idea.


--
Kind regards


Stephen Feyrer

Re: [GENERAL] SELECT blocks UPDATE

[Tom Lane]

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 same
> transaction (because the lock query joins on the lock's transaction id), so
> it looks like a transaction blocking itself. As I think about it now, it
> does not even make sense to me /why/ the lock query joins on the
> lock.transactionid - I would expect two locks will mostly conflict with each
> other when they are executed within /different/ transactions.

No, you're misunderstanding the meaning of the column.  virtualtransaction
and pid are what identify the session that is holding/awaiting a lock.
A transactionid is something that a lock can be taken on, and if you have
a session that is awaiting such a lock then it's waiting for the session
doing that transaction to end its transaction.

The use of such locks in Postgres is pretty narrow, though.  AFAIR this
would only happen in the context of a session waiting to acquire a row
lock on a row that it's trying to update/delete/lock for update.

regards, tom lane


-- 
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] SELECT blocks UPDATE

[Adrian Klaver]

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 particular session. In this case, 'begin
transaction' and the SELECT statement was executed in the context of the
stateful session, while the UPDATE was executed on a stateless session. I am
not sure how this situation manifests on Postgres but since the 'blocked'
and 'blocking' lock apparently belong to the same transaction, it does not
look like it should matter, except it does.


I would crank up the Postgres logging:

http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT

At least set log_statement to all, to see what is actually being sent to 
the server from the ORM during the above.






--
View this message in context: 
http://postgresql.nabble.com/SELECT-blocks-UPDATE-tp5862040p5862097.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] SELECT blocks UPDATE

[twoflower]

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
transaction' and the SELECT statement was executed in the context of the
stateful session, while the UPDATE was executed on a stateless session. I am
not sure how this situation manifests on Postgres but since the 'blocked'
and 'blocking' lock apparently belong to the same transaction, it does not
look like it should matter, except it does.



--
View this message in context: 
http://postgresql.nabble.com/SELECT-blocks-UPDATE-tp5862040p5862097.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] [BDR] vs pgpool-II v3

[Martín Marqués]

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 mentioned about BDR being *async* means that data will
be *eventually* consistent. This tackles the problem with nodes which
are distant (one node in London, another in NY, one in Tokio, ...)

PgPools replication_mode is for nodes on a local network. It would never
scale like BDR with geographically distant nodes.

Conclusion, they don't solve the same problem, so it's hard to compare.

Cheers,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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] Extension to rewrite queries before execution

[Adrian Klaver]

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 
Postgres server?




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 bind
parameters) and either replace the query text with a different text
which, for example, swaps out an "in list" clause to instead be an
"exists (subquery)".

Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
before and a reset of it after.

Is there anything out there like this?  This would be for 9.4.

I'm willing to put the query text, and its replacement, directly into
the extension source code and compile it, but of course something more
flexible would be ideal.

Thanks,

Jeff



--
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] Extension to rewrite queries before execution

[Melvin Davidson]

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.

On Thu, Aug 13, 2015 at 3: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.
>
> 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 bind
> parameters) and either replace the query text with a different text which,
> for example, swaps out an "in list" clause to instead be an "exists
> (subquery)".
>
> Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
> before and a reset of it after.
>
> Is there anything out there like this?  This would be for 9.4.
>
> I'm willing to put the query text, and its replacement, directly into the
> extension source code and compile it, but of course something more flexible
> would be ideal.
>
> Thanks,
>
> Jeff
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] SELECT blocks UPDATE

[twoflower]

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
*blocked_mode*: ShareLock
*blocked_duration*: 00:35:01.81106
*blocking_pid*: 7569
*blocking_statement*: select tmtranslat0_.id as id164_0_, tmtranslat1_.id as
id101_1_, tmlanguage2_.id as id73_2_, tmtranslat0_.status as status164_0_,
...
*blocking_locktype*: transactionid
*blocking_mode*: ExclusiveLock
*blocking_duration*: 00:35:03.017109


User names are irelevant, so I omitted that. Also the *blocking_statement*
is actually cut off even before the FROM clause, but there is only one
SELECT query issued at that moment which matches the start:

select from   "TRANSLATION" tmtranslat0_left outer join "TRANSLATION_UNIT"
tmtranslat1_  on tmtranslat0_.fk_id_translation_unit = tmtranslat1_.idleft
outer join "LANGUAGE" tmlanguage2_  on tmtranslat0_.fk_id_language =
tmlanguage2_.idwhere  tmtranslat0_.id in (47049860, 47049861, 47049862)order 
by tmtranslat0_.id asc


I also suspected a SELECT FOR UPDATE query, but it's not the case. Also, I
don't use these at all in the application.


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 same
transaction (because the lock query joins on the lock's transaction id), so
it looks like a transaction blocking itself. As I think about it now, it
does not even make sense to me /why/ the lock query joins on the
lock.transactionid - I would expect two locks will mostly conflict with each
other when they are executed within /different/ transactions.

As for other context, I fail to see how this situation is special or
different from any other...Is there any pattern I should be looking for? 



--
View this message in context: 
http://postgresql.nabble.com/SELECT-blocks-UPDATE-tp5862040p5862091.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

[GENERAL] Extension to rewrite queries before execution

[Jeff Janes]

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 bind
parameters) and either replace the query text with a different text which,
for example, swaps out an "in list" clause to instead be an "exists
(subquery)".

Or just wrap the query in a "set enable_* =off;" or "set work_mem=*;"
before and a reset of it after.

Is there anything out there like this?  This would be for 9.4.

I'm willing to put the query text, and its replacement, directly into the
extension source code and compile it, but of course something more flexible
would be ideal.

Thanks,

Jeff

Re: [GENERAL] I am unable to install PostgreSql

[Melvin Davidson]

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
version of Windows.
That might work a little better.

On Tue, Aug 11, 2015 at 11:43 PM, Shashank  wrote:

> Hi
>
> I am unable to install Postgresql on window 2003. I have already installed
> previously. As, I forgot the password, I have uninstalled and trying to
> install again.
> I got error as " The database cluster initialization failed ".
>
> The solution to this problem I got as "During the installation, you are
> asked to choose a location to store the postgres data. The default location
> is C:\Program Files\Postgres\data. Choosing a location outside of the
> Program Files directory (e.g. C:\Postgres Data) can often overcome the
> problem described above. "  I got the solution from
> http://www.maplesoft.com/support/faqs/detail.aspx?sid=33315
>
> After doing the above steps, the setup is stuck at the final stage. It is
> just showing as "Installing the database cluster (this make take few
> minutes)".
>
>
> Kindly help me on this. Thanks in advance.
>
>
>
> Regards
> Shashank
> Mumbai, India
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

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

[Wayne E. Seguin]

(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, 2015 at 12:28 PM, Joshua D. Drake 
> wrote:
>
>>
>> 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 SQL changes required on an
>>> application side."
>>>
>>> I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
>>>
>>
>> BDR is async multi master. PGPool is largely just a connection pooler
>> with load balancing (although it has other features). They play different
>> sports.
>>
>> JD
>>
>>
>> --
>> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
>> PostgreSQL Centered full stack support, consulting and development.
>> Announcing "I'm offended" is basically telling the world you can't
>> control your own emotions, so everyone else should do it for you.
>>
>
>
>
> --
>   ~Wayne
>
> Wayne E. Seguin
> wayneeseg...@gmail.com
> wayneeseguin on irc.freenode.net
> http://twitter.com/wayneeseguin/
> https://github.com/wayneeseguin/
>



-- 
  ~Wayne

Wayne E. Seguin
wayneeseg...@gmail.com
wayneeseguin on irc.freenode.net
http://twitter.com/wayneeseguin/
https://github.com/wayneeseguin/

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

[Wayne E. Seguin]

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 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 SQL changes required on an
>> application side."
>>
>> I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
>>
>
> BDR is async multi master. PGPool is largely just a connection pooler with
> load balancing (although it has other features). They play different sports.
>
> JD
>
>
> --
> Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
> PostgreSQL Centered full stack support, consulting and development.
> Announcing "I'm offended" is basically telling the world you can't
> control your own emotions, so everyone else should do it for you.
>



-- 
  ~Wayne

Wayne E. Seguin
wayneeseg...@gmail.com
wayneeseguin on irc.freenode.net
http://twitter.com/wayneeseguin/
https://github.com/wayneeseguin/

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

[Joshua D. Drake]

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 SQL changes required on an
application side."

I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this


BDR is async multi master. PGPool is largely just a connection pooler 
with load balancing (although it has other features). They play 
different sports.


JD


--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.


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

[GENERAL] [BDR] vs pgpool-II v3

[Wayne E. Seguin]

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 answer the following question I
was presented with:

"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 SQL changes required on an
application side."

I appreciate any input as to pros/cons of BDR vs PGPool-II v3 in this
context.

  ~Wayne

Wayne E. Seguin
wayneeseg...@gmail.com
wayneeseguin on irc.freenode.net
http://twitter.com/wayneeseguin/
https://github.com/wayneeseguin/

Re: [GENERAL] Sync replication + high latency server

[Adrian Klaver]

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 sometimes, 1000ms~2000ms, even 3000ms when network is under load).

Considering that I will keep enough WAL files (let's say, 200 WAL
segments on a server with low write rate):

What happens if I configure timeout in master server for, let's say, 10
seconds?
Will sync replication survive if timeout happens, and then, network
"recover" it self?


I think you are misunderstanding how sync rep works.


I don't think so: sync replication will force commit on both databases
before returning OK to my app server.





Or will I be obligated to use async replication in such scenario?



async is your friend here.


And what about the timeout when using sync replication? What will happen?


If you are talking about archive_timeout, all that is going to do is 
generate more WAL files. More to the point the WAL files will still the 
same length(by default 16MB). So I am not sure how that is going to help 
with your network issues.


If you are not talking about the above, then what timeout are referring to?



Thanks,

Edson




JD









--
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] Sync replication + high latency server

[Edson Richter]

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 is under load).

Considering that I will keep enough WAL files (let's say, 200 WAL
segments on a server with low write rate):

What happens if I configure timeout in master server for, let's say, 10
seconds?
Will sync replication survive if timeout happens, and then, network
"recover" it self?


I think you are misunderstanding how sync rep works.


I don't think so: sync replication will force commit on both databases 
before returning OK to my app server.






Or will I be obligated to use async replication in such scenario?



async is your friend here.


And what about the timeout when using sync replication? What will happen?

Thanks,

Edson




JD






--
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] Chars problem restoring to ps 8.4 (utf8) a dumped db from ps 8.1 (latin9)

[Tom Lane]

"=?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 to work, maybe that's
>> because your editor changed the encoding?  Not to be relied on, for sure.

> Well, IIRC a LATIN9 encoding char which is interpreted as UTF8 will get
> inserted with no error on a UTF8 server (although the final data will be
> bogus).

I'd believe the other way around: if you tell the database that you're
using LATIN9, but what you send is really UTF8, it will not reject it
because the individual bytes are perfectly valid LATIN9 characters and
there are no cross-byte checks to make in LATIN9.  But it seems highly
unlikely that LATIN9-encoded data would get past the UTF8 validity
checker with any consistency.

It's possible that the problem is one of mislabeling, ie the database
was claimed to use LATIN9 but what was actually sent was always UTF8.
If that was *always* the case then the OP's fix of changing the label
in the dump file was actually the right thing to do.  But we haven't
been given enough information to be sure of that --- and if that's
what was happening, then some client software fixes would be in order
anyway, because the client code was using the wrong client_encoding.

regards, tom lane


-- 
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] PostgreSQL - The Best Overall Database

[John Turner]

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 "thing" about both MS and Oracle. And  
not a good "thing", either.


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? :)


I must say that if it weren't for Access and their support community, I  
wouldn't have been introduced to PostgreSQL (via my interest in the  
temporal extensions some years back) -- and coming from the business side  
of things, making the leap from Excel to Access many many moons ago served  
as my paradigm 'relational database' moment.  Suffice to say MS really  
captured a niche with an all-in-one app/db.  But yes, for a 'real'  
enterprise database then it drops off the table of contenders ; )


- John

Re: [GENERAL] SELECT blocks UPDATE

[Tom Lane]

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?
That locks individual rows (not the whole table) so it can conflict
against an UPDATE on the same row(s).

> However, what I am
> observing as a result of this monitoring query: 

> SELECT bl.pid AS blocked_pid,
> a.usename  AS blocked_user,
> ka.query   AS blocking_statement,
> now() - ka.query_start AS blocking_duration,
> kl.pid AS blocking_pid,
> ka.usename AS blocking_user,
> a.queryAS blocked_statement,
> now() - a.query_start  AS blocked_duration
> FROM pg_catalog.pg_locks bl
> JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
> JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND
> kl.pid != bl.pid
> JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
> WHERE NOT bl.granted;

Hmm.  In any remotely modern version of PG, a pure SELECT transaction
wouldn't even *have* a transactionid.  So either the SELECT is a
SELECT FOR UPDATE, or it's part of a transaction that's done data
changes in the past.  In that case the blockage could have something to do
with previously-acquired locks.

It's also possible that you're misreading the output of pg_locks.

> 1) How is it possible that these two statements block?
> 2) What can I do about it?

EINSUFFICIENTDATA.  You need to tell us more about the context,
and show us the actual pg_locks query output.  It might also be
relevant just which PG version this is.

regards, tom lane


-- 
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] SELECT blocks UPDATE

[Adrian Klaver]

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 monitoring query:

SELECT bl.pid AS blocked_pid,

 a.usename  AS blocked_user,

 ka.query   AS blocking_statement,

 now() - ka.query_start AS blocking_duration,

 kl.pid AS blocking_pid,

 ka.usename AS blocking_user,

 a.queryAS blocked_statement,

 now() - a.query_start  AS blocked_duration

FROM pg_catalog.pg_locks bl

JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid

JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid 
!= bl.pid

JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid

WHERE NOT bl.granted;


What is the output of the above?





is this

*Blocking statement*: SELECT tmtranslat0_.id as id164_0_, tmtranslat1_.id as id101_1_, 
tmlanguage2_.id as id73_2_, ... FROM "TRANSLATION" ...


What is the entire statement for above?

Is it part of transaction?



*Blocked statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item = 
1000211 WHERE id IN (47032216)







I don't remember ever having problems with things like this. I am not
even issuing SQL queries in parallel from my application (the execution
is single-threaded). Now my application is stuck on the UPDATE statement.

1) How is it possible that these two statements block?
2) What can I do about it?

Thank you.



--
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] PostgreSQL - The Best Overall Database

[Melvin Davidson]

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 PostgreSQL DBA's faces.
>>
>> The Best Overall Database
>> 
>>
>
> ​Very nice. Of course, I have a "thing" about both MS and Oracle. And not
> a good "thing", either.
>
>
>>
>>
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

[GENERAL] SELECT blocks UPDATE

[twoflower]

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 blocked_pid,
a.usename  AS blocked_user,
ka.query   AS blocking_statement,
now() - ka.query_start AS blocking_duration,
kl.pid AS blocking_pid,
ka.usename AS blocking_user,
a.queryAS blocked_statement,
now() - a.query_start  AS blocked_duration
FROM pg_catalog.pg_locks bl
JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND
kl.pid != bl.pid
JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
WHERE NOT bl.granted;


is this
*Blocking statement*: SELECT tmtranslat0_.id as id164_0_, tmtranslat1_.id as
id101_1_, tmlanguage2_.id as id73_2_, ... FROM "TRANSLATION" ...
*Blocked statement*: UPDATE "TRANSLATION" SET fk_assignment_queue_item =
1000211 WHERE id IN (47032216)


I don't remember ever having problems with things like this. I am not even
issuing SQL queries in parallel from my application (the execution is
single-threaded). Now my application is stuck on the UPDATE statement. 

1) How is it possible that these two statements block?
2) What can I do about it?

Thank you.



--
View this message in context: 
http://postgresql.nabble.com/SELECT-blocks-UPDATE-tp5862040.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: [GENERAL] PostgreSQL - The Best Overall Database

[John McKown]

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 MS and Oracle. And not a
good "thing", either.


>
>
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>


-- 

Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.

Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

[GENERAL] PostgreSQL - The Best Overall Database

[Melvin Davidson]

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

[Aviel Buskila]

-- 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 the same way.

from the message given in the repmgrd log in the witness server it seems
that he is not able to elect a new master because he can't see anyone .

I have check in the repl_nodes table in the witness and it shows:
witnessnode3
master node2
master node1

is there a way update the witness after the first failover?

2015-08-13 15:06 GMT+03:00 Jony Cohen :

> 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...)
> did you start the repmgrd on node1 after making it a replica of the new
> master? (it needs 2 daemons to decide what to promote)
>
> Regards,
>  - Jony
>

On Thu, Aug 13, 2015 at 1:29 PM, Aviel Buskila  wrote:

> 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 postmaster)' The witness detects that
> something went wrong and fails over from node1 to node2
> But when I setup the replication now to work from node2 to node1 and I
> kill the postgresql process it doesn't failover and the repmgrd log shows
> the following  message:
> unable to determine a valid master server; waiting 10 seconds to retry...
>
> it seems that the witness doesn't know about the new standby server..
>
> Has anyone got any idea about what am I doing wrong here?
>
>
> Best regards,
> Aviel Buskila
>

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

[Jony Cohen]

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...)
did you start the repmgrd on node1 after making it a replica of the new
master? (it needs 2 daemons to decide what to promote)

Regards,
 - Jony

On Thu, Aug 13, 2015 at 1:29 PM, Aviel Buskila  wrote:

> 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 postmaster)' The witness detects that
> something went wrong and fails over from node1 to node2
> But when I setup the replication now to work from node2 to node1 and I
> kill the postgresql process it doesn't failover and the repmgrd log shows
> the following  message:
> unable to determine a valid master server; waiting 10 seconds to retry...
>
> it seems that the witness doesn't know about the new standby server..
>
> Has anyone got any idea about what am I doing wrong here?
>
>
> Best regards,
> Aviel Buskila
>

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

[Martín Marqués]

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 server, migrate and upgrade bugzilla application)
>
> FYI, 8.4 is no longer community supported. The oldest supported version
> is 9.0 and its support will in September. See here for more details:
>
> http://www.postgresql.org/support/versioning/

I think you should try moving even further ahead from 9.0 or 9.1, as to
avoid the trouble of having to plan a new upgrade any time soon.

>>> I "SOLVED" it doing this way but don't know what I did and I don't know 
>>> which consequences would have in future, then I need to know if it's ok...
>>>
>>> Starting on BOX1
>>> $pg_dump --no-privileges --no-owner -h localhost -U bugs -f DB.sql   (dump 
>>> in latin9)
>>>
>>> $vi DB.sql and changed the first string with the last.
 SET client_encoding = 'LATIN9';
>>>  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 to work, maybe that's
> because your editor changed the encoding?  Not to be relied on, for sure.

Well, IIRC a LATIN9 encoding char which is interpreted as UTF8 will get
inserted with no error on a UTF8 server (although the final data will be
bogus).

IMO Leonardo is confused with the meaning of client_encoding, and should
maybe take a look here before continuing:

http://www.postgresql.org/docs/9.4/static/multibyte.html

And while reading that, they can switch to 9.4. ;)

Regadrs,

-- 
Martín Marquéshttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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

[GENERAL] repmgr won't update witness after failover

[Aviel Buskila]

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 postmaster)' The witness detects that
something went wrong and fails over from node1 to node2
But when I setup the replication now to work from node2 to node1 and I kill
the postgresql process it doesn't failover and the repmgrd log shows the
following  message:
unable to determine a valid master server; waiting 10 seconds to retry...

it seems that the witness doesn't know about the new standby server..

Has anyone got any idea about what am I doing wrong here?


Best regards,
Aviel Buskila