The Heap™ – Consultancy ©®

7:08 AM

Morning [northern hemisphere people]

7:31 AM

@Philᵀᴹ should we change [nosql] to [No! SQL!]?

surprise: I thought the Refiner badge was a regular occurrence. But now I am only the 10th the get it... Do the others here always answer already perfect questions? ;)

Hmm, I should probably edit more :P I usually do the tags & tidy up, surprised I haven't got it. 16/50! WTF

7:54 AM

This is not a playground, you know. — dezso 10 secs ago

@dezso Clickety

Q: Hot standby recovery configuration changed - now WAL sync fails

8:33 AM

0

I am using 9.2 PostgreSQL, and I was just playing with configuration files. The following things I have done: stopped slave server renamed recovery.conf to recovery.conf.sample restarted slave server Now rsyncing has completely stopped. After this, I tried restarting slave with recovery.con...

this one is on a good track to become the other heavily edited and commented tooth removal operation

would be nice to close quickly

@dezso Already VtC'ed it

@Philᵀᴹ I thought so

I'm voting to close this question as off-topic because MySQL 4.1 is from the dark ages — Philᵀᴹ 33 mins ago

James Anderson

8:53 AM

Are your our next UnLimited Full Stack Developer? stackoverflow.com/jobs/117633/…

nice grammar

> £0 - 30,000

9:09 AM

£30k? Not worth getting out of bed

@Philᵀᴹ £0 is also not so promising

3

Tom V

@dezso I was just as surprised to see the copy editor only handed out a few times

9:34 AM

NO! DO NOT DELETE THIS FILE IF YOU WANT TO KEEP YOUR DATA! — Philᵀᴹ 39 secs ago

9:52 AM

Can you all downvote the above please?

This is bad advice. Very bad. Voting to delete this answer as dangerous. — ypercubeᵀᴹ 10 secs ago

Terribadᵀᴹ advice

I was to output some snark on it but it's now gone

> After this, you really need that luck. @dezso, 1 min ago

yes, that

they possibly lurked in here and read

2 days ago, by billinkc

It's mysql, they'd be better served with rm -rf /

10:03 AM

TBH, the OP was correct. It /would/ solve the problem

There's a nasty Linux exploit around now. dirtycow.ninja Gives you root in a couple of seconds

10:19 AM

LOL, Oracle have patented Cloud Computing patentbuddy.com/Patent/20140075021

10:36 AM

@Philᵀᴹ thnx. I hadn't updated for a week.

@Philᵀᴹ a Postgres Cloud (no patents)

enterprisedb.com/news-events/press-releases/…

12:51 PM

@AndriyM you can add this version in your answer: rextester.com/KVSHA80876

Corrected: rextester.com/BRUOCB49302

@ypercubeᵀᴹ Or like this: rextester.com/YLW35870

@AndriyM I edited your answer.

Oh, thanks.

But I don't have much time, have to go now. Please check for any errors

I actually thought it might be better to post it as a separate answer, because it differs substantially. But it's all right. I'll take a look, thanks again.

1:04 PM

@AndriyM Yeah, the order of the column in the select list affects the result!

2:34 PM

I've noticed that SQL Server Management Studio adds brackets around the name if I give a column the name Key. Should I avoid using that name?

@WilliamMariager Yes, it's a Reserved Keyword

Ah, I see.

As in FOREIGN KEY, PRIMARY KEY.

SYMMETRIC KEY...

It's a popular word :)

Alright.

Sort of related, when I have a column with the type uniqueidentifier, is it always unique to the table, or should I check for duplicates? I realize the chances are miniscule, but I'm curious what's the right thing to do.

2:40 PM

@WilliamMariager If something ought to be unique, it needs to have a unique constraint of some kind.

That way, you don't have to check for duplicates - SQL Server will enforce it.

But I still have to handle the potential error right?

@WilliamMariager Of course.

It rather depends on context though. Perhaps you could elaborate your usage.

I'm storing a GUID as a session key in my system, so when a user logs in, the key is returned and will be used for subsequent requests.

@WilliamMariager Ok, and that will be unique at all times in a Session table?

Or might there be multiple rows for the same session?

In my design each session will always be unique per row. It's possible that the same user may have multiple sessions, but each session will still use a unique key.

2:46 PM

Ok. Well from a design perspective, keys should always be enforced to be unique by the database.

Primary key constraint, unique index/constraint.

Presumably you would want lookups by session key to be fast as well so the index/constraint performs both functions - data integrity and performance.

Makes sense. Would it make sense to just forego the Id column I usually have and just rely on the uniqueidentifier?

Oh right, primary key means I do that, right?

Sorry, very new to databases, so still trying to figure out the different concepts.

Q: When should a primary key be declared non-clustered?

@WilliamMariager All alternatives are valid choices in different scenarios. If you don't need an Id (identity?) you don't have to use one. There are issues associated with having a UUID as the primary key, because the default is that primary keys are clustered.

Having a clustering key with a random insert point profile can lead to table fragmentation and inefficient use of space (memory and disk).

97

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key? Thanks in advance

sql-server primary-key

60

Q: Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and int and why? From what I've seen, int has no fl...

performance sql-server primary-key uniqueidentifier

Thanks.

The topic is widely discussed, both on-site and elsewhere.

Indeed. There are many compromises to be made in the database world.

I see. I think I'll keep the auto incrementing int Id I have, and use it as the clustering key and then use the guid/uuid as the primary key.

2:56 PM

@WilliamMariager That's one of the valid choices :)

Quick update on the blog thing, @PaulWhite: static pages are a possibility. So the issue of broken links and whatnot you mentioned... well, it wouldn't be an issue any more :)

As far as I understand, there shouldn't be any downside to it besides having an additional column on my table.

@JNat Yeah just read that in TL :)

Ah, cool

@JNat Thank you.

2:58 PM

No problem ;)

A recruiter just mailed me asking if I wanted a MySQL contract. I feel dirty

Well, dirtier :P

@WilliamMariager Well everything is a trade off. If you generally fetch rows by UUID, having the PK nonclustered means every fetch requires a lookup to the base table to fetch the non-key columns.

@Philᵀᴹ Did you say yes? ;)

@PaulWhite nah, too busy looking at a new car :P

@Philᵀᴹ Ooo flash. Brand new?

Another Ferrari?

@Philᵀᴹ looking at or looking for?

3:02 PM

@PaulWhite Perhaps it's just the general perception of finding oneself attractive to that crowd ;)

Natural progression Oracle -> SQL Server -> MySQL -? SQLite ?

-> JavaScript

@PaulWhite To be clear: I assume that's the option you wanna go with, right?

@PaulWhite -> JQuery -> NoSQL

@JNat Me personally? Yes. Can't speak for the others. Well I could, but I'm not authorized to :)

@PaulWhite who else might wanna have a say on this? Anyone in specific, or the community as a whole?

3:06 PM

@JNat Sorry to make life difficult. I'm not doing it deliberately, we're just pretty hopeless at giving straight answers / being decisive.

@JNat Community as a whole. But, for my money, especially @AaronBertrand @JackDouglas @jcolebrand @JNK

@PaulWhite Have you considered starting a Meta discussion, then? Something like this?

Can circular references cause issues in SQL Server? Lets say I have a User, with a column that points to itself? I would like to have a CreatedBy column on my user table.

@PaulWhite haha

Actually I have been reading lately (in the TTM mailing list, which is crowded by hard-core relational-model guys) a discussion about how (D) could be implemented in Javascript

and how it is a very good candidate ...

@WilliamMariager What do you mean by "column that points to itself"? That just doesn't make much sense. Did you mean a column that points to another column in the same table?

(If you ask what D is, imagine an implementation of the relational model - which everyone at the list think that SQL isn't (relational).

3:11 PM

@AndriyM Self-ref FK

@AndriyM I meant a Row that points to itself.

@ypercubeᵀᴹ Tutorial D?

@PaulWhite yeah

Sweeeeet.

@ypercubeᵀᴹ Wasn't it implemented in Rel?

@JNat Nope.

@PaulWhite Yeah - and a few other implementations exist

The guy is swearing that Javascript is the futture

3:14 PM

In a perfect world with infinite time, I would have spent much more with D and the associated parties.

@PaulWhite There is an Andl thing recently - that I haven't played around.

It's in C# / .Net I think

gtg back later

@PaulWhite On other communities that have chat rooms dedicated to their blog, I know precisely where and who to contact about decisions to the blog. If they feel like a wider range of opinions is needed, they can bring it to the wider community.

In communities with pretty dead blog like yours, my best chance of finding someone who cares about their blogs is reaching out to mods. If you feel you can make an informed decision for the community, cool. If you think opinions from the community as a whole are necessary, I'd recommend starting a Meta post.

@JNat Ok I pinged the others above. I have no time right now, but "nope" I hadn't considered writing a meta post, but I might, just not right now. Cheers.

Ok, cool ;)

3:18 PM

@PaulWhite BMW 330e

Will be able to get to work just on battery

@dezso For. :-)

@JNat So how will it work if we decide to keep the bolg?

@WilliamMariager In that case I can't immediately think of any issues, not from the perspective of the database itself anyway

Choose some other blog platform and transfer the posts there?

@ypercubeᵀᴹ Depends on what you mean by "keep"

If you mean move the content elsewhere, then we'll provide with a data dump and try to help with migrating

Keep it somewhere in the Internet where it is visible

I thought that keeping it inside SE was not an option

3:23 PM

If you want static pages, pretty much nothing changes, with the exception that they're essentially archived

@AndriyM Alright, appreciate it. :)

26 mins ago, by JNat

Quick update on the blog thing, @PaulWhite: static pages are a possibility. So the issue of broken links and whatnot you mentioned... well, it wouldn't be an issue any more :)

:)

We were still evaluating the possibility when I came in here last week

3:36 PM

@JNat Cool, good updates.

@jcolebrand :)

See my convo with Paul above, re: getting feedback from more people on Meta if you feel it'd be better

3:52 PM

@JNat I always prefer conversations on meta over conversations in chat

I always have.

4:15 PM

@JNat we'd have to look for someone that knows something about databases if you did that :(

4:33 PM

@jcolebrand Ok cool. Then coordinate with Paul and start a Meta post on it. I'll ping you guys in about two weeks or so to find out what conclusions y'all came to :)

@Philᵀᴹ If you migrated to Medium, I know the Worldbuilding community would be willing to help, as they have their blog there

5:45 PM

@JNat You probably missed Phil's self-targeting snark (or intentionally ignored ;)

Oh, yeah, I didn't... read the room? :P

I missed it, yeah

Firefox wants to correct self-targeting to telemarketing ...

@JNat Yep. Cod help us if we need help with a migration.

Thanks a lot by the way. We do appreciate the effort.

Tom V

6:27 PM

In the spirit of closing early

Hi there, it would help if you shared the script, indicate where it fails or what the error is if you get one. — Tom V 40 secs ago

@JNat there was once a plan for a new SE blogging platform based on the Q&A engine, is that abandoned?

It is abandoned, yes, as far as I know.

On Azure I can configure Geo Replication for my SQL database. That's purely to improve access times right?

Err, response times*

Q: dba.BlogOverflow.com — migrate or freeze?

@JNat I thought we could have a productive blog if content creation was familiar to site users and tied to the site, and if people didn't have to have yet another login to provide content — failing that I don't see it working for dba.se on an external blogging platform any more than it did on blogoverflow.

0

Stack Exchange is going to be discontinuing the BlogOverflow.com website for community blogs. We could migrate the few posts we had to another blogging platform, or (iiuc) it can just be converted to static pages in situ.

Q: dba.BlogOverflow.com — migrate or freeze?

Master Database

6:47 PM

0

Stack Exchange is going to be discontinuing the BlogOverflow.com website for community blogs. We could migrate the few posts we had to another blogging platform, or (iiuc) it can just be converted to static pages in situ.

Q: The time has come, the walrus said, to speak of many things

0

However, our blog is something we hardly ever talk about. http://dba.blogoverflow.com/2012/06/help-us-help-you/ Have you ever seen this post? Isn't it helpful? Well, there's this little issue. The resources for BlogOverflow are going to be decommissioned (to some degree) so we have a few o...

ho hum

Q: The time has come, the walrus said, to speak of many things

Yes, I am a dork

1

However, our blog is something we hardly ever talk about. http://dba.blogoverflow.com/2012/06/help-us-help-you/ Have you ever seen this post? Isn't it helpful? Well, there's this little issue. The resources for BlogOverflow are going to be decommissioned (to some degree) so we have a few o...

@JackDouglas Did you beat me to it?

Damn you!

@jcolebrand you just put more effort in, which is commendable

@JackDouglas mebe

I will close mine as a dupe :)

6:49 PM

heh, ok

I also sometimes write really dumb titles @JackDouglas

;-)

1

Q: To blog or not to blog?

However, our blog is something we hardly ever talk about. http://dba.blogoverflow.com/2012/06/help-us-help-you/ Have you ever seen this post? Isn't it helpful? Well, there's this little issue. The resources for BlogOverflow are going to be decommissioned (to some degree) so we have a few o...

:p

It's shake n bake n I hepped

7:21 PM

I think we can do this while still keeping the other option available for us to switch to at any point. — Andriy M 4 mins ago

7:39 PM

I think if it ever were decided to blog again, it'd probably be more of Heap Consultancy-driven Blog anyway?

8:30 PM

I'm voting for this, BUT the excellent post by @Aaaron dba.blogoverflow.com/2012/06/help-us-help-you needs preserving somewhere on the main DBA.se site. I suggest as an answer to a new mod-crafted community question. — Philᵀᴹ 19 secs ago

9:26 PM

@Philᵀᴹ That's the likely idea

9:54 PM

It makes sense to clean up a database from time to time right? I have sessions which are usually only valid for 12 hours. So I was thinking I'd make a task that runs through and removes invalid sessions once per day.

10:09 PM

@WilliamMariager what problem are you trying to solve there?

If by session you mean "connection to the database" ... why do you need to close them?

What's your database stack?

What's your app stack?

Oh, by sessions I mean I have a Sessions table with rows. The rows each have an expiration datetime column, after which the session is invalid and they're not longer used.

I'm using SQL Server. Not sure what app stack means.

And I'm just trying to remove rows that I know will never be used again from a table.

10:46 PM

@WilliamMariager Will you ever need the data for reporting? Analytics? Usage stats?

Very rarely do we generate business data that we then truly want to get rid of

At the very least you probably just want to move it into an audit table, but if it's properly indexed, is it an issue for you?