187 reputation
110
bio website 0xdave.com
location Wellington, New Zealand
age 29
visits member for 2 years, 10 months
seen Dec 7 at 8:42
q-p
d-b

Nov
17
awarded  Popular Question
Sep
29
awarded  Popular Question
Jul
2
awarded  Curious
Oct
28
comment How can I replace constants 5,6 & 8 in sql instead of in condition
Have a subquery look for party_approval_type_id in another table.
Aug
5
revised Should I be concered by large SERIAL values?
Fixed typo in blockquote (231 to 2^31), and also changed formatting of all exponents.
Aug
5
suggested approved edit on Should I be concered by large SERIAL values?
Jun
11
accepted Having a PostgreSQL database exposed to the internet
Jun
11
comment Having a PostgreSQL database exposed to the internet
I'm marking this one as the answer because of your explanation about minimising "attack surfaces", but I'm still failing to understand the benefits of shifting security elsewhere. I'm inclined to follow @CraigRinger's advice.
Jun
10
comment Having a PostgreSQL database exposed to the internet
That still doesn't sound like the web service offers much advantage over having a database user who is only allowed to call a small set of functions (i.e. the same "actions" that the web service offers). Plus, any changes to the database require changes to the web service also, as well as the front-end.
Jun
10
comment Having a PostgreSQL database exposed to the internet
How is the web service inherently more secure though? Why is shifting security to a web server more secure than leaving it with the database, especially if the database only accepts connections from those with valid certs? The problem is, if I have to expose the database via a web service this will require changing a lot of code in the existing front-end (which is not web-based). If it is the only way, then I'll just have to do it, but I need to know what the risks are of having a database exposed if it is configured only to accept SSL-certified connections.
Jun
10
comment Having a PostgreSQL database exposed to the internet
This makes sense. If for example I had a front-end already developed to make direct connections to the database, would it still be better to tunnel these through a VPN or SSH connection?
Jun
9
revised Having a PostgreSQL database exposed to the internet
Title was mispelled
Jun
9
asked Having a PostgreSQL database exposed to the internet
May
20
awarded  Caucus
May
20
awarded  Constituent
Jan
12
comment Subqueries run very fast individually, but when joined are very slow
Actually I'm not even sure that would help...
Jan
12
comment Subqueries run very fast individually, but when joined are very slow
Does MySQL support CTEs?
Nov
9
revised Tricky selection of grouped rows, selecting based on values of two distinct but related rows
added 811 characters in body
Nov
7
comment Tricky selection of grouped rows, selecting based on values of two distinct but related rows
When I do EXPLAIN ANALYZE on your last solution, it says that most of the time is spent looping over CTE d when joining to c, the actual line is " -> Nested Loop (cost=0.00..2666.80 rows=1 width=58) (actual time=1974.879..111526.212 rows=77 loops=1)". The strange thing is that this contradicts the calculated costs. Both of the other CTEs have much higher costs (around 20,000) but much lower actual time (around 1,000 to 2,000 ms). I'm writing my own procedural function now and will get it working first, and then optimise it later. Hopefully I can sort this out myself. Thanks again!
Nov
7
comment Tricky selection of grouped rows, selecting based on values of two distinct but related rows
Hi Erwin, thanks for all of your help. I developed a procedural function to work this out but it took 2,100ms to only scan the table once, quite a bit slower than some of the solutions you've provided here. Your last example timed-out, after two minutes I cancelled the query, but it seems like adding the complication of the 30-day-between-task grouping makes the query substantially slower. What is the best way to loop over records and return groups at a time?