As part of an application I'm working on, users need to be able to store files in directories, and share certain directories for collaboration.
After doing some initial research, I decided to implement a closure table for storing the directory structure (files are stored separately).
Now, I'm l...
I mean, they've specified a reference point describing what they want ("akin to sharing in Dropbox"), but I'm not familiar with that so not sure if that is specific enough.
I hate trolls. Especially the subtle ones. Ever since someone sat down and explained trolling to me, I've been determined to fight this. How can I help? — Evan CarrollMay 20 '14 at 23:46
...
@MDCCL Probably. Maybe it's a bit of a shame the question ended up in rejected migration limbo though (locked here, never to be auto-deleted) closed on SO. Their fault for migrating I guess. Still, it could have been closed as a duplicate of the Q & A ypercubeᵀᴹ suggested, which would at least not have rejected it. Hard to say how it might have turned out.
@PaulWhite Someone with the right knowledge and sufficient experience using Dropbox, you mean. I'm neither so maybe I just shouldn't worry. Only I thought that if they added some outline of how sharing works in Dropbox (from the user's standpoint), they might get more interest from those who are able to address the problem but not familiar enough with Dropbox.
@TomV They are asking about database design and I'm trying to see the end result that design would serve. In particular, I'm trying to guess what kind of input/desired output combinations they might have. That may be the part that the Dropbox analogy would address.
I want to do asynchronous programming on PostgreSQL using stored procedures.
I will use C# on my project. I'm not looking for async or await which are the keywords of the C# programming language. What I want is to do asynchronous programming with PostgreSQL. When I searched for the 'asynchronou...
@ypercubeᵀᴹ, @MartinSmith, @MarkSinkinson, @Philᵀᴹ, @JackDouglas, @JamesLupolt - Xmas drinkies at the Pigs Ears in Richmond. Thursday, December 8 at 7:30pm. Be there or be quadratic.
3
@MarkStoreySmith won't be in the UK, unfortunately.
Debunking
If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example:
CREATE TABLE explode (id integer, something text);
INSERT INTO explode SELECT i, md5(i::text) FROM generate_series(1,...
@dezso Yes you did. There was a custom flag to say all comments were obsolete. There were so many (14) and multiple edits to the question so comments were purged as a block rather than being individually assessed. Sorry about that.
@PaulWhite Yes, it looks like a duplicate of the very good Q & A linked by ypercube. The situation provided value to me personally because I knew of the existance of, preciselly, such a good Q & A.
@PaulWhite On the other hand, the OP of the following Q followed your suggestion and posted an answer (based, in turn, on the accepted answer by dezso):
I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.
Here are the schema of the two tables (edited for readiability):
Table "public.plannin...
@MDCCL Good on him! I try to review all comments I leave people once per day at least. So nice when people take the suggestions on board. It actually surprised me a little how receptive people are here in general terms.
I have an SSIS package that's scheduled to run daily at 5:30AM. Both the SQL Agent job that's launching it and the SISS "Execution Overview" report says it started at 5:30:00AM. Yet the very first thing the package does, is log the [System::StartTime] value in a database table. This is what it logged today:
@PaulWhite Yes, people are, in general terms, as you said, very receptive regarding site usage and technical matters. In my opinion this is a quite fine community.
WITH a AS (SELECT id, max(something) FROM explode GROUP BY id), b AS (SELECT id, max(something) FROM implode GROUP BY id) SELECT id, max(i.something), max(e.something) FROM explode AS e FULL OUTER JOIN implode AS i USING (id) LIMIT 10; ERROR: column "e.id" must appear in the GROUP BY clause or be used in an aggregate function
WITH a AS (SELECT id, max(something) FROM explode GROUP BY id),
b AS (SELECT id, max(something) FROM implode GROUP BY id)
SELECT id, i.max, e.max
FROM explode AS e FULL OUTER JOIN implode AS i USING (id)
LIMIT 10;
@Mat'sMug @billinkc Is it possible both are correct? The job started but the first log entry was only possible some time later when e.g. a blocking condition was removed?
@ypercubeᵀᴹ I mean how do you count them? I just see a bunch of little avatars top right with nothing to say diamond or not. Always been curious about this.
I did make a change to system settings a week ago, guess maybe that's what broke it, because that change is the only change made to that system in the past quarter
@dezso Everything's on the backup server (failover) that didn't have that change, and all's working stableishly. We'll see if there's death in a week :P
@dezso yep, Ubuntu 16.04. Being a linux sysadmin I assumed OOM killer first
but didn't see anything
if it dies off again after a week (the switchover happened yesterday) i'll be back and bothering for assistance
@ypercubeᵀᴹ no, that's what's odd. I had another system blow up on me and such by killing off processes without reporting anything, that's like one in every three hundred VMs I have, so maybe it's unrelated to the system itself?
everything's working right now, so i'm just going to let it sit until it blows up, I have more important things to finish, like the statistics tracking system for my listserv implementation in python (using a postgres DB as a backend for some of it, with dovecot+postfix for the actual mail stuff, and python for the actual listserv stuff)
@Forrest latest hypothesis is that, because the ETL was killed yesterday in the middle of being apparently stalled loading a crapton of records, something didn't clean up properly and there was a memory clog-up that rebooting the server magically "fixed"
now, why it stalled yesterday is yet another mystery
one common reason for stalling SSIS packages is me stupidly writing to a destination table while another part of the data flow is reading from the same table (e.g. a "Lookup Existing" component branches non-matching lookups to "inserts", and matching lookups to a conditional that checks whether the records were modified), effectively deadlocking itself... but that hasn't happened since I learned to avoid doing that.
nothing else running, reports are scheduled for 9AM (in theory the package completes around 7AM); isolation level of the SSIS package is "Serializable", whatever that means
@Mat'sMug I believe the intention was to check the transaction isolation level in use by the SSIS package when it runs.
It's a long time since I touched SSIS for anything except a handy screenshot, but I seem to recall Serializable being the default for a package, but not resulting in Serializable isolation at runtime for reasons.
Might only be used if the container's Transaction Option property is Required. Something like that.
@Mat'sMug I may have missed some of the discussion but is this something you're actually concerned about (causing issues) or just wanting to understand what happened?
one common reason for stalling SSIS packages is me stupidly writing to a destination table while another part of the data flow is reading from the same table (e.g. a "Lookup Existing" component branches non-matching lookups to "inserts", and matching lookups to a conditional that checks whether the records were modified), effectively deadlocking itself... but that hasn't happened since I learned to avoid doing that.
@Mat'sMug Yes the broad issue was mentioned a few times that I saw. Most likely explanation for sure.
@Mat'sMug That's one tool (technically deprecated now). Many places have third-party tools for monitoring that keep history etc. it is possible to set up Profiler/Extended Events to capture stuff as well and save it to files/database/whatever.
well, I solved that problem by using two sorted OLEDB sources and a merge join (full outer) piping into a conditional that branches new/updated/deleted/unchanged rows accordingly. so I don't do "lookup existing" anymore :)
You've got lots of options. The venders to look at that are SQL specific are:
Quest Software - Spotlight
Red Gate Software - SQL Monitor
Confio - Ignite
Idera - SQLdm
Microsoft - System Center Operations Manager
Microsoft - SQL Server 2005 Performance Dashboard
SQL Sentry - Performance Advisor
...
I'm just a programmer dubbed "BI Specialist" in an IT dept with 4 people and no sysadmin and no DBA, so.. I just go with the flow and cross my fingers.. and come here when things break :)
I've seen a number of questions with comments ranging in the 10s and higher. In a very small subset of these, I occasionally see someone open up a chat regarding the conversation within the question's comments. This was a great approach in my opinion to reign in the chaos. I've only ever seen ...
I suppose one of the arguments is that employees are expensive, so spending 8 hours diagnosing a problem that could have been done in 5 minutes using a tool...etc.
@Mat'sMug which is why I'm a moderate advocate of management data warehouse. It's not great but it comes with SQL server and does a somewhat decent job
@Forrest It's poorly expressed. "acquires an exclusive (X) lock on the table" does not (necessarily) mean it acquires a table-level exclusive lock. Just some sort of lock associated with that table.
As part of an application I'm working on, users need to be able to store files in directories, and share certain directories for collaboration.
After doing some initial research, I decided to implement a closure table for storing the directory structure (files are stored separately).
Now, I'm l...
I want to do asynchronous programming on PostgreSQL using stored procedures.
I will use C# on my project. I'm not looking for async or await which are the keywords of the C# programming language. What I want is to do asynchronous programming with PostgreSQL. When I searched for the 'asynchronou...
Debunking
If you are doing all your updates in the same transaction, each of them will have to work an increasingly bigger set of (physical) tuples. See the following example:
CREATE TABLE explode (id integer, something text);
INSERT INTO explode SELECT i, md5(i::text) FROM generate_series(1,...
I have two tables - one titled "planning constraints" which contains the 'sot_allowed' time intervals, and one titled "planning" which contains the 'sot_contribution' time interval.
Here are the schema of the two tables (edited for readiability):
Table "public.plannin...
You've got lots of options. The venders to look at that are SQL specific are:
Quest Software - Spotlight
Red Gate Software - SQL Monitor
Confio - Ignite
Idera - SQLdm
Microsoft - System Center Operations Manager
Microsoft - SQL Server 2005 Performance Dashboard
SQL Sentry - Performance Advisor
...
I've seen a number of questions with comments ranging in the 10s and higher. In a very small subset of these, I occasionally see someone open up a chat regarding the conversation within the question's comments. This was a great approach in my opinion to reign in the chaos. I've only ever seen ...
