Re: [GENERAL] Ordering of window functions with no order specified?

[Andreas Kretschmer]

Am 15.06.2017 um 06:34 schrieb Ken Tanzer:
Hi.  If you use a window function and don't specify an order, will the 
rows be processed in the same order as the query results?


In this particular case, I'm wondering about row_number(), and whether 
I explicitly need to repeat the ordering that's already specified in 
the query?


SELECT a,b,c,row_number()
OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
FROM foo
ORDER  BY c,b

Also, I'm interested in both what if any behavior is guaranteed, and 
what gets done in practice.  (i.e., a SELECT with no order doesn't 
have guarantees, but in practice seems to return the results in the 
order they were added to the table.  Is it something similar here?)





That's different queries and results:

test=# create table foo as select s%5 a, random() as b, random() as c 
from generate_series(1, 20) s;

SELECT 20

test=*# select a, b, c, row_number() over (partition by a) from foo 
order by c,b;

 a | b  |  c  | row_number
---++-+
 3 |  0.293813084252179 | 0.00748801836743951 |  4
 2 |  0.366433540824801 |  0.0825160844251513 |  3
 2 |  0.646373085677624 |   0.144253523554653 |  2
 1 |  0.436142998747528 |   0.149559560697526 |  4
 4 |  0.766950330231339 |   0.279563031159341 |  2
 4 |  0.780563669744879 |0.36753943329677 |  4
 4 |  0.521357014775276 |0.37830171873793 |  1
 3 |  0.641054477542639 |   0.438840930350125 |  3
 0 |   0.23528463486582 |   0.506252389866859 |  1
 1 |  0.883372921962291 |   0.607358017936349 |  2
 2 | 0.0624627070501447 |   0.610814236104488 |  4
 1 |  0.203920441213995 |   0.680096843745559 |  3
 0 |  0.945639119483531 |   0.686336697079241 |  3
 2 |  0.360363553743809 |   0.70250764338 |  1
 0 |  0.493005351629108 |   0.739280233159661 |  4
 0 |  0.844849191140383 |   0.756641649641097 |  2
 1 |  0.375874035060406 |   0.77152630727 |  1
 4 | 0.0844886344857514 |   0.837361172772944 |  3
 3 |   0.50597962597385 |   0.841444775927812 |  2
 3 | 0.0100470245815814 |   0.899044481106102 |  1
(20 Zeilen)

test=*# select a, b, c, row_number() over (partition by a order by c,b) 
from foo order by c,b;

 a | b  |  c  | row_number
---++-+
 3 |  0.293813084252179 | 0.00748801836743951 |  1
 2 |  0.366433540824801 |  0.0825160844251513 |  1
 2 |  0.646373085677624 |   0.144253523554653 |  2
 1 |  0.436142998747528 |   0.149559560697526 |  1
 4 |  0.766950330231339 |   0.279563031159341 |  1
 4 |  0.780563669744879 |0.36753943329677 |  2
 4 |  0.521357014775276 |0.37830171873793 |  3
 3 |  0.641054477542639 |   0.438840930350125 |  2
 0 |   0.23528463486582 |   0.506252389866859 |  1
 1 |  0.883372921962291 |   0.607358017936349 |  2
 2 | 0.0624627070501447 |   0.610814236104488 |  3
 1 |  0.203920441213995 |   0.680096843745559 |  3
 0 |  0.945639119483531 |   0.686336697079241 |  2
 2 |  0.360363553743809 |   0.70250764338 |  4
 0 |  0.493005351629108 |   0.739280233159661 |  3
 0 |  0.844849191140383 |   0.756641649641097 |  4
 1 |  0.375874035060406 |   0.77152630727 |  4
 4 | 0.0844886344857514 |   0.837361172772944 |  4
 3 |   0.50597962597385 |   0.841444775927812 |  3
 3 | 0.0100470245815814 |   0.899044481106102 |  4
(20 Zeilen)

And also different execution plans:

test=*# explain analyse select a, b, c, row_number() over (partition by 
a) from foo order by c,b;

 QUERY PLAN
-
 Sort  (cost=239.18..243.43 rows=1700 width=20) (actual 
time=0.134..0.138 rows=20 loops=1)

   Sort Key: c, b
   Sort Method: quicksort  Memory: 26kB
   ->  WindowAgg  (cost=118.22..147.97 rows=1700 width=20) (actual 
time=0.056..0.101 rows=20 loops=1)
 ->  Sort  (cost=118.22..122.47 rows=1700 width=20) (actual 
time=0.048..0.054 rows=20 loops=1)

   Sort Key: a
   Sort Method: quicksort  Memory: 26kB
   ->  Seq Scan on foo  (cost=0.00..27.00 rows=1700 
width=20) (actual time=0.021..0.028 rows=20 loops=1)

 Planning time: 0.104 ms
 Execution time: 0.200 ms
(10 Zeilen)

test=*# explain analyse select a, b, c, row_number() over (partition by 
a order by c,b) from foo order by c,b;

 QUERY PLAN
-
 Sort  (cost=247.68..251.93 rows=1700 width=20) (actual 
time=0.115..0.119 rows=20 

Re: [GENERAL] Ordering of window functions with no order specified?

[David G. Johnston]

On Wednesday, June 14, 2017, Ken Tanzer  wrote:
>
> SELECT a,b,c,row_number()
> OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
>

Yes.


> FROM foo
> ORDER  BY c,b
>
Also, I'm interested in both what if any behavior is guaranteed, and what
> gets done in practice.  (i.e., a SELECT with no order doesn't have
> guarantees, but in practice seems to return the results in the order they
> were added to the table.  Is it something similar here?)
>

Row numbers would be assigned in the order they are sent up by the "from
foo" clause.

In practice what gets done depends on the execution plan that is chosen
and nothing is guaranteed unless you specify it in the query so that the
execution plan can enforce it.

David J.

[GENERAL] Ordering of window functions with no order specified?

[Ken Tanzer]

Hi.  If you use a window function and don't specify an order, will the rows
be processed in the same order as the query results?

In this particular case, I'm wondering about row_number(), and whether I
explicitly need to repeat the ordering that's already specified in the
query?

SELECT a,b,c,row_number()
OVER (PARTITION BY a) -- Do I need an ORDER BY c,b here?
FROM foo
ORDER  BY c,b

Also, I'm interested in both what if any behavior is guaranteed, and what
gets done in practice.  (i.e., a SELECT with no order doesn't have
guarantees, but in practice seems to return the results in the order they
were added to the table.  Is it something similar here?)

Thanks!

Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://agency-software.org/demo/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

[Andreas Kretschmer]

Am 15.06.2017 um 01:18 schrieb Martin Goodson:



I'm just wondering how people may have implemented this. Do people 
setup pgbouncer nodes on the database servers themselves, on 
application servers, in the middle tier between the application and 
database, and so forth, or some combination of the three? I can think 
of some advantages or drawbacks for each. Or do people find that 
repmgr works better with other tools to handle the promotion 
notification outside the database cluster?


Basically I'm new to this, and I'm wondering how folks have handled 
this issue. I'm basically looking for the community's wisdom :)




Usually we recommend to install pgbouncer on the app-servers.

If you have full control of the application you can try to integrate the 
logic into the application (provide a list of servers, the new 
pg10-version of libpg is working similar in this way:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=274bb2b3857cc987cfa21d14775cae9b0dababa5
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=721f7bd3cbccaf8c07cad2707826b83f84694832
)

Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.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] Repmgr + pgbouncer - Notification of master promotion to application level ...

[Martin Goodson]

On 14/06/2017 19:54, Rory Campbell-Lange wrote:

On 14/06/17, Martin Goodson (kaema...@googlemail.com) wrote:

The new master's repmgr promote script will execute commands to pause
pgbouncer, reconfigure pgbouncer to point to the new database address, and
then resume.


You could just move the service ip address at the new postgresql master
to which the pgbouncer instances on each app server could reconnect.

I assume, with reference to https://github.com/2ndQuadrant/repmgr, that
you could use repmgrd to trigger a script to do just that at
"switchover", possibly something you would need to monitor the
"repl_events" table to achieve.

Rory



Well, yes. That's pretty much the plan. See the repmgr documents:

https://github.com/2ndQuadrant/repmgr/blob/master/docs/repmgrd-node-fencing.md 
-


In a failover situation, repmgrd promotes a standby to master by 
executing the command defined in promote_command. Normally this would be 
something like:


repmgr standby promote -f /etc/repmgr.conf

By wrapping this in a custom script which adjusts the pgbouncer 
configuration on all nodes, it's possible to fence the failed master and 
redirect write connections to the new master.



I'm just wondering how people may have implemented this. Do people setup 
pgbouncer nodes on the database servers themselves, on application 
servers, in the middle tier between the application and database, and so 
forth, or some combination of the three? I can think of some advantages 
or drawbacks for each. Or do people find that repmgr works better with 
other tools to handle the promotion notification outside the database 
cluster?


Basically I'm new to this, and I'm wondering how folks have handled this 
issue. I'm basically looking for the community's wisdom :)


--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."


--
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] Repmgr + pgbouncer - Notification of master promotion to application level ...

[Rory Campbell-Lange]

On 14/06/17, Martin Goodson (kaema...@googlemail.com) wrote:
> The new master's repmgr promote script will execute commands to pause
> pgbouncer, reconfigure pgbouncer to point to the new database address, and
> then resume.

You could just move the service ip address at the new postgresql master
to which the pgbouncer instances on each app server could reconnect.

I assume, with reference to https://github.com/2ndQuadrant/repmgr, that
you could use repmgrd to trigger a script to do just that at
"switchover", possibly something you would need to monitor the
"repl_events" table to achieve.

Rory


-- 
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 + pgbouncer - Notification of master promotion to application level ...

[Martin Goodson]

Hello.

Currently we're running all our PostgreSQL databases on ESX Stretch 
Clusters, but our company is looking at moving away from those to a 
private cloud solution.


I'm informed that, going forward, new servers provisioned for us to 
install PostgreSQL databases on will be entirely isolated, using no 
network/clustered storage of any kind but only local storage - so 
stretch clusters, VCS etc are out.


To this end we're looking at using standard PostgreSQL 
replication/multiple nodes to provide continued availability in the 
event of a primary server failure, and I've been directed to set-up some 
three node testbeds.


It was originally suggested we use Pacemaker/Corosync for this, but then 
our Linux sysadm team advised that Pacemaker/Corosync hadn't been 
certified for our particular cloud solution, and a VIP couldn't be done 
as the database servers may exist on different networks/availability zones.


(BTW, I apologize if somebody reading this is going 'What nonsense! Of 
course it will work! Here's how ...' - I'm no sysadm, I'm just going on 
what I was told)


As a backup/contingency plan I'm now using repmgr to handle things - I'm 
very happy with it (after getting it compiled :) ) it seems nice and 
simple, and repmgrd is working nicely. A number of test failovers have 
worked perfectly.


BUT ...

repmgr doesn't provide a mechanism to notify upstream connections that a 
failover has occurred and that the master is now on server y instead of 
server x.


I'm currently looking at pgbouncer, as that's proposed as a STONITH 
mechanism in the repmgr git documentation.


The new master's repmgr promote script will execute commands to pause 
pgbouncer, reconfigure pgbouncer to point to the new database address, 
and then resume.


This seems like a straightforward and viable option, except that if we 
only have one pgbouncer we've just introduced a new single point of 
failure, and if we have multiple pgbouncers how best to handle that? A 
pgbouncer on every application server, two or three pgbouncers in the 
middle with some kind of load balancer, a pgbouncer on every db server? 
Each option appears to have advantages and drawbacks.


So I was just wondering if the community had any 
recommendations/suggestions/gotchas for this? Is pgbouncer a decent 
choice, or are there better solutions out there? Has anyone tried 
pgbouncer and found it worked well, or tried it and found it was a 
disaster and moved onto something else?


Basically, I'm wondering if I'm on the right path or whether there's 
something out there that everyone is now screaming 'Why on earth is this 
idiot not using X instead?' :)


Any input would be greatly appreciated.

Regards,

Martin.


--
Martin Goodson

"Have you thought up some clever plan, Doctor?"
"Yes, Jamie, I believe I have."
"What're you going to do?"
"Bung a rock at it."



--
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] ERROR: type " " does not exist

[Alexander Farber]

Good evening everyone,

I apologize for the delay in replying and that you had to "reverse
engineer" my question.

This turned out indeed to be a special char problem.

On MBA with macOS Sierra 10.12.5 I am using Postgres 9.5.4.1 of
postgresapp.com.

At the psql prompt I had copy-pasted:

words=> SELECT 1, 'You have resigned ' || 1 || ':' || 1;
ERROR:  type " " does not exist
LINE 1: SELECT 1, 'You have resigned ' || 1 || ':' || 1;
  ^
After I removed the "white space" char before ':', everything worked.

In vi (after \e) I could see that special char by typing %!xxd

: 5345 4c45 4354 2031 2c20 2759 6f75 2068  SELECT 1, 'You h
0010: 6176 6520 7265 7369 676e 6564 2027 207c  ave resigned ' |
0020: 7c20 3120 7c7c c2a0 273a 2720 7c7c 2031  | 1 ||..':' || 1
0030: 3b0a ;.

Thank you.

Re: [GENERAL] pg_upgrade --link on Windows

[Bruce Momjian]

On Wed, Jun 14, 2017 at 09:59:04AM +0200, Klaus P. Pieper wrote:
> > -Ursprüngliche Nachricht-
> > 
> > I apologize for not being smarter on this thread.  When I helped with the
> > Windows port, I was told Windows didn't have hard links for use by
> tablespace
> > directories, so I got it into my head that Windows didn't have hard links.
> > Therefore, when I was writing the docs, I called them junction points.
> 
> It's actually not "Windows" providing hard links, it is the file system
> NTFS. FAT and its modern cousins don't provide hard links - but this will
> rarely be used for databases these days. 
> However, ReFS (introduced with server 2012 and providing some new features
> like automatic integrity checks, clustering etc.) does no longer provide
> hard links. 
> Are hard links used anywhere else but in pg_upgrade?

Nope, it is used only by transfer_relfile() calling linkFile().

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
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] pg_catalog tables don't have constraints?

[Neil Anderson]

That's a great start, thanks! You should put a pointer to that page in the Pg 
wiki.

I think the 1 thing that would really help it though is to show the actual
connection/relations of the columns. For example, a line between pg_index
and pg_class is not quite as informative as a line from pg_index.indexrelid
to pg_class.oid (something the PNG file in the image that Thomas pointed
out does). I suspect that's a limitation of the tool not your desire.


I think that might be possible, I may have turned off the labels on the 
edges.




Is this process automated, or does it require you to lay it out?



The layouts were all built in the DataGrip luckily!


In my perfect world, :) there would be a way to generate it automatically with
Graphviz or something similar. Hmm, perhaps an idea to pursue when I can find
some time.

Kevin




I think SchemaSpy's new output looks really interesting and uses 
Graphviz. After doing the work to add the relationships I realised that 
SchemaSpy does not like the oid system columns that we use for keys so 
it wouldn't work. I will go ahead and replace them all with something 
else and see how it does but I haven't gotten back to it yet.



--
Neil Anderson
n...@postgrescompare.com
http://www.postgrescompare.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] pg_upgrade --link on Windows

[Klaus P. Pieper]

> -Ursprüngliche Nachricht-
> 
> I apologize for not being smarter on this thread.  When I helped with the
> Windows port, I was told Windows didn't have hard links for use by
tablespace
> directories, so I got it into my head that Windows didn't have hard links.
> Therefore, when I was writing the docs, I called them junction points.

It's actually not "Windows" providing hard links, it is the file system
NTFS. FAT and its modern cousins don't provide hard links - but this will
rarely be used for databases these days. 
However, ReFS (introduced with server 2012 and providing some new features
like automatic integrity checks, clustering etc.) does no longer provide
hard links. 
Are hard links used anywhere else but in pg_upgrade?

Klaus



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