« first day (1666 days earlier)      last day (2120 days later) » 

4:45 AM
@billinkc thanks for the vote of confidence but somehow I think that MySQL comment will cost me. There's a ton of fans out there... Not least of which is a guy who became my boss at my old job and who was convinced MySQL was every bit as capable as SQL Server Enterprise.
To say we butted heads just might be an understatement.
5:36 AM
@MaxVernon You mean that person is a user here?
6:12 AM
@MaxVernon I'd have to disagree with your old boss. MySQL is even more capable than Microsoft SQL Server Enterprise. It has the SET and ENUM data types which SQL Server lacks! Plus, MySQL doesn't try and confuse people with things like CTEs and Window functions, Check CONSTRAINTs and other trivia. :-)
6:37 AM
It's GROUP BY clauses are also more flexible.
@PaulWhite And what could be better to teach people than letting them learn from their mistakes? "Yes Johnny, put your hand in the fire and see what happens..."
Ow! My hand!
7:24 AM
VtC as dupe:
Q: How can i get list of postgresql database through php or using just sql query?

Abhijit GujarWhat i want to do is to make a drop down list of all the databases of my postgresql 9.3 server using php . if i can get just the sql script that can do the trick then i can handle the php part. Thanks for your time.

(I cannot believe there was no duplicate on SO)
@dezso VTC? 70 meanings... Could you expand?
Vote to Close
@PaulWhite Thanks for that. I haven't been hanging round here long enough to get into the group slang.
@JNK Hm. Interesting idea.
1 hour later…
8:45 AM
Morning all
8:59 AM
9:15 AM
@Vérace I can expand, it's just a question of food
@dezso I cannot Virtual Training Company questions yet, but I did Federal Trade Commission that one.
9:32 AM
We should have a spam-killer badge
> 101 posts marked spam
> 100 deemed helpful
@ypercube That four-sided hammer of yours is better than any badge :)
9:53 AM
@dezso brilliant :)
10:17 AM
Q: How to improve perfomance or Query Execution Time in MySQL?

Ashok GujjarThis is my query string select a.z_companyid_pk "z_companyid_pk",a.company_name "Client",a.display_name "Display_Name",a.z_parentcompanyid_fk "Parent_Company", a.z_resellerid_fk "Reseller", (select company_name from company_mst where z_companyid_pk= a.z_resellerid_fk) "Reseller_name", (select ...

That's ..... special.
Q: Simultaneous calls to the same function: how are deadlocks occurring?

Jack DouglasMy function new_customer is called several times per second (but only once per session) by a web application. The very first thing it does is lock the customer table (to do an 'insert if not exists'—a simple variant of an upsert). My understanding of the docs is that other calls to new_customer ...

@JackDouglas this is really weird
does it do the same if you replace everything after the LOCK with a sleep (or other, unrelated activity)?
10:58 AM
@JackDouglas Session isn't in autocommit mode?
But I think your log message "2015-07-28 08:02:58 BST CONTEXT: SQL function "new_customer" statement 1" says it isn't.
Which point release are you on?
Can you reproduce it in a test environment on a minimal test case?
11:53 AM
RDBMSs optimise SQL to the actual functions they perform. You don't need to add these kludges to try and optimise yourself. Put on the right index and do the right join and it will just work. — JamesRyan 3 mins ago
That's the aim but I wish it was always so simple.
But then, if dbms were so clever, we'd be out of work ;)
1 hour later…
1:17 PM
A: How to improve perfomance or Query Execution Time in MySQL?

Andriy MThere are at least three parts of your query that will or may benefit from rewriting: These five correlated subqueries in the SELECT clause are retrieving data from the same table based on the same condition: (select websitelink from company_profile where z_companyid_fk=a.z_companyid_pk) "Web...

This answer needs some upvoting.
so @ypercube, when are you gonna nominate yourself for the dba mod election?
@Lamak there is a greek word that means when/if
Sometimes when, sometimes if and sometimes is ambiguous.
sounds appropriate
oh, we have 2 candidates already.
IMHO, good candidates
1:26 PM
@ypercube I have no problem upvoting good solid answers to MySQL questions.
@AndriyM deserves a medal for that one!
dear lord, is that question a wall o' code.
@MaxVernon yeah, he deserved a +1 only for that!
Craig Ringer reached 20K
next on line: @JackDouglas
@ypercube Craig is a serious asset here.
@MaxVernon I don't say it is much easier to understand with the formatting, but is at least prettier :)
@dezso agreed 1,000%. I cannot understand how anyone can manage queries written like that, as a single blob of code.
1:54 PM
I'm confused at this one
at first sight it seems legit
but MONyog seems like a strange suggestion of you're only suggesting 1 monitoring tool for mysql
he is an admin on that page
yeah i'm leaning towards a flag but wanted some extra eyes
assuming this is the same guy blog.webyog.com/author/karthik
There are two Karthiks on their About page.
Chances are he may be one of them. (Though he is Kartik here, not Karthik.)
2:09 PM
on the other hand he does address the points in the questions, and "some" monitoring tool could be of help for the second point
that's true, but it's still a must to tell people that/if he is an employee there
His #1 seems a bit pointless to me, though. "What is the recommended TransactionDeadlockDetectionTimeout in a mysql cluster?" – "Well, some people like it low, some like it high." What kind of an answer is that?
might be a valid 'it depends' :)
I see you guys commented by now :)
@dezso No, it doesn't. I think it is a bug - it doesn't happen if I take out the CTE!
2:15 PM
@JackDouglas by now, on a hint from a colleague, I can reproduce your errors
it's about lock upgrade, as Daniel suggests
the colleague, in theory, is writing an answer
@Colin'tHart 9.4.1
@dezso cool!
@JackDouglas If you remove the lock from the function and wrap the lock and call to function in a transaction, does it stil lhappen?
the pattern is:

UPDATE b SET id = 10 WHERE id = 9;
                                            UPDATE b SET id = 9 WHERE id = 10; -- no row found
                                            LOCK b IN EXCLUSIVE MODE; -- will wait
-- deadlock!
how to format a block here
@dezso ctrl-k
2:23 PM
@dezso the lock statement is the first command of the session (except for a set schema)
@ypercube trying to figure out how to do that in PHP :S
@JackDouglas now that is really interesting
@JackDouglas haha. How do you access the DB?
@ypercube the caller doesn't have permission to lock the table - inside the function it is allowed because it's SECURITY DEFINER
@dezso with these PHP functions: php.net/manual/en/function.pg-connect.php
in view of your response to ypercube, it doesn't matter much :)
those 'r's in the code blocks are the ugliest ever
actually I was wrong, I get deadlocks even without the CTE.
2:42 PM
so, enter function -> lock -> BAMM?
can it be that the PHP side does the transaction wrapping?
@dezso yes, though if I take out the insert -> no deadlock
@dezso don't think so, but to be sure I've explicitly wrapped the calls in a transaction block
that sounds like the deadlock occurs on the second call to the same function (first INSERT, then upgrade the lock in the second call)
it does
(not that I don't believe you about having the function wrapped properly)
it would be nice to be able to take PHP out of the equation
I will try and reproduce it without PHP
2:46 PM
@JackDouglas Can you reproduce outside of PHP?
2:57 PM
@Colin'tHart yes
for i in {1..100}; do psql postgres post -c "select new_host2(109,'blah')"; done;
^^^ in two bash sessions simultaneously
And boom
new_host2 is a bit like new_customer (actually a bit simpler)
Can you post a complete trimmed down generic version for us?
I'm assuming there's nothing special about these tables that causes this.
I'd like to try it against 9.5a1
I'll try and make it as minimal as possible
first up I'm trying 9.4.4
@Colin'tHart I don't think so - they are basic and small
3:17 PM
@MaxVernon Craig is awesome. Also, really pleased to see you put yourself forward for the election - I hope a few other regulars are also willing to do the same but we'll see :)
@JackDouglas Does it make any difference if you use "DO $$begin perform new_host2(109, 'blah'); end;$$" instead of the select version?
@JackDouglas thanks!
hmmm, Debian Jessie packages 9.4.3 not 9.4.4. Anyway it's reproducible on the latest version Debian gives me.
It would also be interesting to try some older versions.
But what are we trying to prove now exactly with the different versions?
I perused the release notes and didn't read anything that sounded like it had to do with this.
Perused the releases notes of 9.4.2, 9.4.3, 9.4.4 that is.
I think a small, reproducible test case is the next step now.
@Colin'tHart yes, working on that now
3:28 PM
And probably post that to pg-hackers
Unless you get wiser in making the test case.
This might be totally silly but is there any chance it might be related to php connection settings?
Could you still try the "do" syntax?
Trying to eliminate the "select" causing some unforeseen action on a database object.
that END should be outside the $$, right?
No, inside.
21 mins ago, by Colin 't Hart
3:39 PM
It's DO $$ <block> $$
But you probably need a semicolon after the closing $$
I don't expect it to make a difference.
@ypercube No, Jack can reproduce it outside of PHP with two parallel psql sessions firing the same function call repeatedly.
@Colin'tHart yes, still get it with that syntax
Good :-)
with a 1000 iteration loop in each bash session I'm getting a handful of deadlocks each time
which makes me think there is a race condition in there somewhere
3:45 PM
Right, it's a fairly small window, it seems.
I've updated the question with a minimal(ish) test case.
@Colin'tHart I'd be interested to know the result if you do
I'm sorry but voted to close this question. There is nothing worthy to be optimized here. The first part of the query does not even have proper SQL syntax (try it with ONLY_FULL_GROUP_BY option on) and will give ill-determined results. If you want this question salvaged, I suggest you rewrite it, explaining what exactly you want the query to return (include a few sample rows from each table and the wanted result set.) After you have it made working right, then another, optimization question might be suitable. — ypercube 1 min ago
4:02 PM
@JackDouglas I get a relatively high number of deadlocks from it
especially closing on to 100 (the number of loops I do)
@dezso are you on 9.4 too?
thanks by the way
@JackDouglas 9.4.0
if it's a race condition it's probably CPU dependant
is the server fast?
@JackDouglas @JackDouglas What if you have only one statement, like:
with w as ( insert into customer ...),
     w2 as (insert into collection ...)
select .. from customer ;
@ypercube have you see the update?
@ypercube that wouldn't return the customer_id if if already exists, would it?
I might have misunderstood...
4:07 PM
@JackDouglas it's a VM on my laptop
but compared to that, it's fast
@JackDouglas ah yes. it needs more rewriting for that
@JackDouglas No, thinking again, it would work. The last select having from test where val =v;
create or replace function f_test(v text) returns integer language sql security definer  as $$
  lock test in exclusive mode;
  with w as (insert into test(val) select v where not exists(select * from test where val=v)
             returning id)
  select id from test where val = v;
oh yes
if it's selecting from TEST and not from W
I got you
I'll try that
@ypercube though you don't need the returning then, right?
@ypercube still get deadlocks :)
4:29 PM
@Colin'tHart I posted it to pg-sql bugs instead after reading this
@JackDouglas and with this:
  lock test in exclusive mode;
  with w  as (select ins.val, test.id from (values (v)) as ins(val) left join test using (val)),
       w2 as (insert into test(val) select val from w where w.id is null
              returning val, id)
  select coalesce(w2.id, w.id) as id from w left join w2 using (val);
@ypercube yes, still deadlocking intermittently
@SQL_Kiwi You're a blogging machine of late! Pinal Dave is looking over his shoulder...
@sqlcrossjoin Thanks I think :)
People and their humours
4:44 PM
I picked up some leeches the other day, thought my humours were out of balance
I can spare some if you need
Thanks! Trying to type on my phone. Always a mistake.
Except that time perhaps
Leeches to make your fingers skinnier or you want to use one as a stylus?
Australians are weird, y'all
@PaulWhite aqueous or vitreous?
@JackDouglas yeah, tested in 9.3, too
But I don't get deadlocks if i change the bash script to have "begin; lock test in exclusive mode; select f_test('blah'); end". Do you get?
5:12 PM
@ypercube you mean take the lock out of f_test as well?
@JackDouglas with or without the lock inside the f_test
@ypercube it doesn't look like it
that's useful info, I'll need to add that to the bug
@JackDouglas It doesn't explain why but the function call may be somehow related to the locks.
it think it points more clearly to where the bug is
oh man ... you cant permalink to election comments
@ypercube are you nominating yourself?
@bluefeet I don't think so.
@ypercube dammit - seems I need to work on my sportsmanship :-)
> @MaxVernon is a left-handed Canadian and, therefore, doubly untrustworthy
<--- bows
5:44 PM
own it!
5:59 PM
@ypercube, @Colin, @dezso all is revealed
There was far less nudity in that post than I was expecting
@billinkc mod platform
@JackDouglas aha !
i thought postgres didnt lock
@swasheck I'm not sure if you are serious or you are billinkcing
6:13 PM
everyone who tells me that posgress r0x0rs sqlserver's s0x0rs tells me that MVCC doesnt lock and is, therefore, >>> sql server
@swasheck jack's question/code was about an explicit lock.
@ypercube Best reference evar
@ypercube tldr;
The import/export wizard in SSMS does datatype guessing. You could ask Microsoft for their algorithm. — Tab Alleman 56 mins ago
I award one full Internet to Tab Alleman for this comment. May they spend it well
@swasheck I'm not very sure of this but usually the implicit locks are row level. Thats' probably what those s0xors mean.
6:18 PM
@swasheck the problem is, that like SQL Server (and the rest, except MySQL), it's merge/upsert is pants
soon to be resolved in pg
@ypercube writers never block readers: that's the beauty of MVCC
@JackDouglas it is merge/upsert is pants? also, is pants good or bad?
@swasheck yes
it's pants, it's rubbish, it's shite, it's swasheck. All of those are common UK slang for specifying something is terrible
hey - at least i'm in the spec
6:21 PM
@swasheck you should reply with "snapshot" ;)
RFC 3764 Part 7 Paragraph A
@ypercube yeah, well ... haters gonna hate hate hate hate hate
6:25 PM
@ypercube oh yuck
TIL: the english believe pants to be bad
Which is amusing considering they outlawed kilts
that's why we broke up. too wishy-washy. couldnt make up their mind. got overbearing and domineering.
@swasheck empires do that to you
que sera sera
6:49 PM
Mmmm queso
@swasheck Darn. I have nachos for lunch instead.
@JackDouglas from the same country: theguardian.com/media/2015/jul/28/…
She's 40? Good gravy, her face looks more like late 50s early 60s
@billinkc judge not lest ye be judged
6:58 PM
@billinkc mumble mumble mumble put away mumble
Hmm my buffer pool seems to have emptied.
That is indeed a bummer.
what happened to flush your pool?
7:00 PM
Developers are always happening
Ohhh, let's look at every field in every record in this ginormous table and let the app find the right one
somewhere, @AaronBertrand 's head just asploted
a hundred gigs worth?
100GB of BP Cache Flush isn't out of the ordinary depending on the circumstances. what were the surrounding circumstances?
7:17 PM
weird. I'm seeing in in SQL Sentry but the other DBA doesn't
That's really odd.
Alright I've got to go figure out this effing thing.
See you all tomorrow.
@swasheck I plan on coming out to Denver either way BTW. So no biggy if I don't get a speaking spot.
Q: Should [database-restore] be a synonym of [restore]?

Normal HumanThe tag restore has 461 questions. The tag database-restore has 54 questions but no wiki or excerpt. Furthermore, database-restoring is synonymized into restore. I can't imagine any reason for treating database-restore differently from database-restoring. It should also be a synonym of resto...

Nice write your query in such a way that it does a full table scan on your biggest table 9 times.
Very good.
I called it
@MasterDatabase yes
Well they certainly make it easy to find the problem queries I'll give them that.
That's a hell of an estimate.
7:43 PM
@JackDouglas Great that it's resolved. And good that it's more of a known issue rather than really a bug. I completely overlooked that it was a SQL language function rather than plpgsql. Coming from an Oracle background I always forget about these. Not that that would have helped me to diagnose the issue ;-)
@Zane is it a weird cartesian or something?
lol no nothing that forgivable
@JNK Look before I delete.
@Zane for xxxxxxxx, no less.
7:50 PM
Probably shouldn't post that sort of thing on here but good grief.
I meant me and my code.
It's really really bad.
@ypercube you here?
@Zane I love that you maxed-out the bigint used for that column
I didn't even notice that.
Well that's it for me today. Keeping up SQL Sentry is making it quite easy for me to spot all of this nonsense.
The problem is fixing the sheer volume of them.
8:16 PM
Hypothetically, if your sql server instance managed to fill up the C: drive so much and crashed and when you restarted it, it wouldn't restart due to full disk space (as reported by vSphere) ummm what would you do. Asking for a friend
@billinkc if it's vmware why not increase the disk as a quick fix?
Even with the VM powered down, it doesn't allow them to increase the size of the disk allocated to the C: in the host.
It's greyed out and I saw the word thick client attached to the configuration
(I'm looking over the client's shoulder for all of this)
@billinkc what's filling the disk? can you free anything?
hope it's not the pagefile :)
No idea. If we could get onto the box, then sure, I suspect we could clean out some junk to get breathing room
not sure how urgent this is, but I guess you could mount the .vhd on another system to get to the filesystem
8:20 PM
Oh, that makes sense to me
It's only their production system so I think based on the number of people that have walked into the room and walked out they're looking for getting it back up asap
then I would get the vmware guys to find a way to increase the disk
I haven't heard of any limitations due to any "thick client" stuff and neither seems google
unless there's another reason
I saw Thick Provisioned Lazy Zeroed pubs.vmware.com/vsphere-50/…
But the idea of mounting it elsewhere might at least get them going
that's what i'd go for if increasing size isn't an option
if it's really tight-sized you might not be able to clean up enough, or if it's the pagefile you might not be able to shrink it until you boot the vm
but if you can clean anything up when mounted in another vm you might be good to go
@billinkc also: they probably need monitoring ;)
8:36 PM
@billinkc this will end up with a CFO running in the room "Why are we down this will cost $$" and then the VM guys saying "We warned you but the budget was only approved for next year"
This client is under 30 people. The VM Guy is also the Exchange/VOIP/Network/etc
sounds like 70-80% of my clients then
jack of all trades, master of ....
OTOH, easy to be the star in those environments sometimes
I really want to take the mouse out of his hand but instead I wait patiently. I've provided your thoughts and suggested he go that route but he's ... whatever he's doing
really, just mounting the disk, running something like treesize or whatever you fancy, see if you can clean something up easily and if not increase the disk size is IMO the way to go
but let them sweat I guess ;)
Right. I have done a similar thing but with actual physical disks so the concept seems sound. He's just timid I guess about doing it. Keeps trying to ring up the san provider to talk to them but getting busy signals
8:47 PM
where I live I can imagine the expression on his face "peentjes zweten"
and scared to make decisions himself, so he's calling vendors to avoid responsibility if anything goes wrong
for kicks, ask him how confident he is in his backups ;)
@TomV nice! Nothing like kicking a man when he's down!
depends on where he's kicked
amen, brotha!
@MaxVernon i know it isn't nice, but it's a way to get the "manager" to confess he isn't managing and make sure you didn't fuck up if it breaks
@TomV I agree; just not sure if the timing would be right for that question.
8:52 PM
bc if you try to help and it ends up with a breakage without backups you did it
that's something for the after-party.
@TomV oh, I see where you're going.
@TomV Sweating carrots, is that literal translation correct?
@billinkc the literal translation is correct yes
not sure if the saying has an equivalent though
I need to learn dutch just enough to slip that into a conversation
but just make sure you don't end up being the one who broke it
8:57 PM
Probably "sweating bullets"
@billinkc yea that should be it
those single man it departments have a way of blaming a vendor
"I didn't touch it, he did and it broke, we didn't have backups but he's supposed to be an expert and still broke it"
"we pay him 100$/hr and look what he did"
@billinkc sweating carrots according to google
The Netherlands didn't have a protracted wild-west phase like we did
also "All I did was call people, that didn't break squat"
9:24 PM
@billinkc I'm logging out, keep me posted I'd like to know how this ends
aye aye and thanks for your thoughts
Downtime is t plus at least 75 minutes and counting
9:40 PM
Can't find any other examples of a database query in a movie
that's pretty funny
@JamesLupolt Another movie that has SQL ;)
"SQL people accusing immigrants of harassing young women, and accosting immigrants on the road and berating them in English (the immigrants don't speak the language). SQL also did itself no favors by aligning with some national anti-immigration groups and getting speakers from those groups to address a local "Day of Truth" conference" <-- This is why DBAs have a bad reputation?
is it still legit to refer to a Nurse Practitioner as "Dr. So-And-So"?
I can find some more, not so funny though
9:55 PM
Aha, root cause identified. They took a snapshot yesterday at the VMWare level and apparently it's like a SQL Server database snapshot where it kept accumulating the changes or whatever and that's what ran the machine out of space
> worked it IT (SQL-Server) before moving to LA to pursue her acting carrer
@billinkc yes. vmware snapshots are supercrapaliscious
Test: what do the movie Evil Cult and the book SQL for dummies have in common?
10:13 PM
@ypercube The author of SQL For Dummies is the producer of Evil Cult?
How did you find that?
One of my coworkers gave me that book as a joke when I left Rackspace
@ypercube they're both gigantic steaming turds?
@JamesLupolt The imdb page says he is part of the cast ! (Cedric)
@JamesLupolt Oh boy, you are right, "Taylor Twin productions"
1 hour later…
