meta user
network profile
| bio | website | 0xdave.com |
|---|---|---|
| location | Wellington, New Zealand | |
| age | 28 | |
| visits | member for | 1 year, 4 months |
| seen | yesterday | |
| stats | profile views | 11 |
q-p
d-b
|
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 |
|
Jun 7 |
comment |
Query Optimization There isn't really a question here... |
|
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? |
|
Nov 5 |
accepted | Tricky selection of grouped rows, selecting based on values of two distinct but related rows |
|
Nov 5 |
comment |
Tricky selection of grouped rows, selecting based on values of two distinct but related rows Ah! Now I get it... your last edit makes sense. I really need to read a book about this stuff. Do you have any recommendations? From your query and the query from the previous question I asked, I should be able to concoct the query I want. Thanks again for your help! |
|
Nov 5 |
comment |
Tricky selection of grouped rows, selecting based on values of two distinct but related rows Well, it's a bit more complicated but I wanted to at least try and sort out the complications myself. Basically, I want to group all rows with the same reference where the date_out between each is no more than 30 days. I asked a question about this previously here. So, if there is more than 30 days between two rows with the same reference then it should be treated as a separate "group". |
|
Nov 5 |
comment |
Tricky selection of grouped rows, selecting based on values of two distinct but related rows This is great, thanks for your help. This works well when there are only two occurrences with the same reference, but there are also some instances where there are 3 or 4 taskids with the same reference. I understand how your query operates but at the same time I don't know how to expand it to include the extra related rows. The second one is definitely faster. The first averages 800ms per run whereas the second only 500ms. |
|
Nov 5 |
asked | Tricky selection of grouped rows, selecting based on values of two distinct but related rows |
