The Heap™ – Consultancy ©®

jcolebrand

12:05 AM

@ypercubeᵀᴹ haha true

Max Vernon

12:42 AM

5 people VtC this, yet there is only a single down-vote? It's a crap question, down-vote that sucka. It's free.

Joe Obbish

Thanks for the compliment, Max

not 100% mine, inspired by someone else

Max Vernon

1:29 AM

It nicely captures the nuances of data distribution in lay terms.

Evan Carroll

1:44 AM

@ChrisTravers working on pg internals now

Joe Obbish

I'm an expert at communicating to the layman

Evan Carroll

@JoeObbish Never jumped into coding?

my goal is to teach PostgreSQL how to handle 512 bit hex encoded numbers.

using openssl bignum

Andy K

8:14 AM

evening and morning

Chris Travers

8:34 AM

@EvanCarroll Nice! what sort of stuff?

:41545018

Nice :-)

McNets

8:52 AM

morning all

hot2use

good morning / evening / midday

Q: compare two columns and remove duplicates in the same same table

I have table like id | t1 | t2 --------------- 1 | a | b 2 | c | a 3 | a | e 4 | f | g 5 | c | c I want to compare two columns with each other to remove matching records and get unique values in each columns, like id | t1 | t2 --------------- 1 | a | b 2 | c | e ...

mysql

Tom V

9:19 AM

@hot2use The comments don't really clarify things :) I'm even more confused now than when I read the original question

McNets

@TomV it seems OP wants to maintain only first occurrence of t1 and t2

hot2use

9:49 AM

@McNets I guess duplicate is the wrong phrase for that. They want a kind of first come first selected solution.

McNets

10:12 AM

@hot2use yep

In some way he needs to enumerate distinct occurrences of t1 and t2 in both columns and then null the corresponding values

Chris Travers

@EvanCarroll If you need pointers, I can probably direct you to some things.

We have 32- and 64-bit base32 encoded numbers. in Pg.

No pun intended on the pointers bit.

Andriy M

10:30 AM

@ChrisTravers Was one intended on the bit bit? :)

hot2use

@McNets Nice solution.

Sometimes I look at solutions and then realise that in some cases I am a real noob

Chris Travers

@AndriyM Heh good one :-). Had not seen that. Interestingly one of the most interesting pointer problems I have seen is "the backend crashes when I run explain with my data type."

hot2use

@JackDouglas We are still working on the ORA-600 issues. Next stop is to restart the physcial server as restarting the instance brought no relief.

Chris Travers

The backend crash during explain but no other time is almost always caused by one specific error.

ypercubeᵀᴹ

@ChrisTravers @ChrisTravers how hard would it be to create a VARINT type?

I mean a type that can hold arbitrary large integers?

And where should I start looking?

Chris Travers

10:46 AM

@ypercubeᵀᴹ You mean like numeric?

ypercubeᵀᴹ

yes

Chris Travers

but without floating points

So these are types represented by the varlena struct, and internally represented as an arbitrary string of base-1000 integers.

ypercubeᵀᴹ

yeah. I don't know what structure, numeric type has

oh, thnx. You answered while I was asking

Why base-1000?

Chris Travers

Because 2*10 is 1024, so you get fairly good alignment and overlap over time.

it means in 32-bits you get 2^30

sorry should be 2^10, not 2 * 10

the major limit is that arbitrarily large has limits due to TOAST limits

I think numeric is limited to something like 10^32000

ypercubeᵀᴹ

So, in 64 bits, it would be 2^60 or rather 1000^6? and in 256 bits, 2^250 or 1000^25 ?

Chris Travers

10:50 AM

sorry

you are right

hmmm that cannot be TOAST limits either but probably an addressing issue.

so maybe we could use bigger addresses and have a bigger ints.

ypercubeᵀᴹ

I was thinking of a structure like VARCHAR

Chris Travers

yeah, that is also a varlena type.

ypercubeᵀᴹ

but base-2, without converting to base-1000

Chris Travers

barchan is limited to 1GB of text.

*varchar

gbn

@ChrisTravers Is that like 4chan for SQL monkeys?

Chris Travers

10:59 AM

hahaha

my typing today sucks

Andriy M

TypoCubeᵀᴹ is spreading the infection, it appears

Tom V

@AndriyM 7 more days for him to pick a bash username

Paul White

11:33 AM

@ChrisTravers The up key edits your last message

ypercubeᵀᴹ

12:33 PM

Voting to close as unclear, until the OP provides us with the DBMS. — ypercubeᵀᴹ 1 hour ago

VTC please. Or add the SQL Server tag and reject the close. Seems the most likely bet.

Tom V

@ypercubeᵀᴹ I would guess that's a screenshot from SSMS but I still VtC

OP should provide that information so we're sure

sp_BlitzErik

12:46 PM

@ypercubeᵀᴹ done

ypercubeᵀᴹ

Hi. We are going to need a lot more information here. As it stands now I would answer the question with a simple 'yes there is a way to index the joins' — Tom V 33 mins ago

Tom V

1:35 PM

@ypercubeᵀᴹ deleted by op after he edited it

ypercubeᵀᴹ

2:32 PM

@AndriyM sir, thank you for the edit, sir ;)

McNets

Some developer teams are really dangerous...

0

Q: Is it dangerous to give production (read only) mysql credentials to the developer team?

All is in the title. Someone in my team want credentials to access a production MySQL database. Even in a "read only" mode I think it can be dangerous. What do you think?

mysql read-only-database

gbn

2:48 PM

@McNets I did start a managing director level bun fight over a dev running SELECT statements on live

I saw it, told him to be careful and use ANSI-92 JOINs and use TOP etc.

sp_BlitzErik

"use nolock"

gbn

he instantly escalated to MD level. I won, he was fired soon after (for other reasons too)

James L

Does anyone else remember a recent blog post from someone at Microsoft mentioning that they were working on some new language features including approximate query results, more adaptive query processing, and enhancements to string functions?

sp_BlitzErik

that was all in one post?

James L

Yes, unless I'm imagining things. It was all for 2018 (or whatever vnext is going to be) I think

sp_BlitzErik

2:52 PM

i haven't seen anything official from ms

brentozar.com/archive/2017/11/…

James L

Thanks. It must have been something I read about about or heard from PASS.

i wouldn't mind finding a job that pays me to go to conferences again

Chris Travers

@McNets I think giving a team a set of credentials is always dangerous. I think it is better to give users credentials.

McNets

@gbn We use a copy of our current db as a development db, obviously it is a quite small db

ypercubeᵀᴹ

@JamesL did you mean thi slink, about SQL standard enhancements?

2 days ago, by Evan Carroll

@sp_BlitzErik https://s3.amazonaws.com/artifacts.opencypher.org/website/materials/SQLStandards‌_2017-10-20.pdf

McNets

But now we are planning to buy SAP or SAGE X3 (at end) and we need to set up 3 db servers (production, test and development) due to FDA regulatory rules.

gbn

3:01 PM

@McNets This works for even bigger ones. I restore a 300GB one daily here, have done smaller and bigger

McNets

@ChrisTravers yep

gbn

The side benefit "restores are tested daily"

McNets

@gbn yes, you're right

sp_BlitzErik

hi tom

Tom V

I bet the latter

sp_BlitzErik

3:06 PM

rough day?

Tom V

The weeks after golive will be rough if they go through with that

The week starting with a SQL 2K issue was a bad sign

gbn

Are they deploying onto a SQL 2k box too...?

sp_BlitzErik

got nt 4 and a t1 line and everything

James L

3:32 PM

@ypercubeᵀᴹ no i think it was something specific to sql server, probably from joe sack at pass. but i'll read that presentation, thanks.

Evan Carroll

5:53 PM

@TomV lol, 9 year old bug closed bugzilla.mozilla.org/show_bug.cgi?id=474824

I love when those trackers tell you bugs open for a decade just got closed.

I got one from OO that was like 15 years old

I was wondering if it was my first FOSS bug

Evan Carroll

6:14 PM

I should map out the personal beef between famous FOSS developers, and whether or not it still exists. That would be hillarriuouusss.

Tom V

6:44 PM

@EvanCarroll can you still repro? :)

Evan Carroll

7:00 PM

I'll be honest, I don't much use Firefox anymore.

=( but I can give it a shot when I pull down the 18.04 in another 4 months.

If you do webdev, FF isn't all that hot.

Tom V

Quantum made me like it again

@EvanCarroll My personal favorite is this one which took 10 years to be assigned even if I included a 2 line patch a decade ago

Tom V

7:20 PM

Looking back I find myself in an authors file but can't for the life of me remember what I contributed to that project :)

good times :)

ypercubeᵀᴹ

7:41 PM

@TomV whatever it was, it was 10+ years ago.

github.com/frePPLe/frepple/commit/…

Joe Obbish

Tom V (MUCH-APPRECIATED CONTRIBUTOR)

3

@JamesL Not sure what you mean by "enhancements to string functions". If you want info on the public upcoming adaptive query processing features I might be able to answer your questions

sp_BlitzErik

8:00 PM

there are a bunch of new string functions in 2017

but i haven't heard anything about 2018 yet

the only stuff i know about 2018 is nda

:'(

Lamak

@sp_BlitzErik and they are quite useful IMO

sp_BlitzErik

yes, we're nearly on par with mysql now

Lamak

well....yeah

but aside of the long overdue string_agg one, the translate and trim ones are nice

Joe Obbish

I don't know why we can't have GREATEST() and LEAST()

sp_BlitzErik

we can

when you learn c++ and get a job on the dev team

slacker

Lamak

8:07 PM

@JoeObbish that would make things too easy

Joe Obbish

@sp_BlitzErik that's a task for an intern

so what did you think of that trace flag that I discovered?

did it change your life?

sp_BlitzErik

i was rocked to my very core

Lamak

@sp_BlitzErik so you won't drink anymore?

sp_BlitzErik

yeah that's a reasonable conclusion

Lamak

I've been working on being more reasonable, glad it's going alright

Joe Obbish

8:15 PM

I love it when a query takes twice as long when processing 1/10th of the data

sp_BlitzErik

sounds like parameter sniffing

ahem

Joe Obbish

there are no parameters in the query

Lamak

outdated statistics?

Joe Obbish

oh

well the query has like a million joins

not looking for help

just complaining

Lamak

@JoeObbish we can help you complain, though

Joe Obbish

8:19 PM

yes

somebody ask me if I can use temp tables

Lamak

can you use temp tables?

Joe Obbish

@Lamak no, the application does not allow that

Lamak

that sucks

damn devs

ypercubeᵀᴹ

@JoeObbish good that you not mysql then.

Joe Obbish

why's that?

Lamak

8:26 PM

@JoeObbish it's always good to not use mysql

no other reason needed

Joe Obbish

I didn't think about it like that

sp_BlitzErik

have you tried recompiling

Joe Obbish

don't be a one trick pony

ypercubeᵀᴹ

@JoeObbish max of 61 tables joined allowed

Joe Obbish

@ypercubeᵀᴹ are you kidding? I wish there was a trace flag for that

that would force developers to rewrite their queries

Tom V

8:52 PM

0

Q: What are the most relevant analogies between T-SQL and MDX?

I am proficient in MS T-SQL (CTEs, windowed functions, recursive CTEs, large data, table-valued functions, APPLY, sprocs, etc.) but have never queried an MS SQL cube. What are the top resources for a T-SQL user to expand into MDX on MS SQL Server? I am hoping there are some common analogies bet...

t-sql mdx learning

Still not sure about the question. He's still asking for resources and editing that out would make it borderline duplicate to the one I linked in my comment

I'll post a stub answer so I can follow up tomorrow but I won't cast a vote myself

dezso

9:22 PM

now this is weird:

0

Q: set postgres instance as readonly with no master

I have a pg 10 instance that I want to behave as a read only/replica/standby instance. There is no master, only that read only database and I want the query select pg_is_in_recovery(); to return true to satisfy a monitoring check. I have tried add a recovery.conf file in the data dir with stand...

postgresql

Tom V

9:35 PM

@dezso or he could ignore the alert

dezso

10:10 PM

@ypercubeᵀᴹ no idea about the fake master port. This is something I don't even want to think about :D

if they figure out how not to log all the streaming errors, it might work

ypercubeᵀᴹ

@dezso I just tried it, in 9.4 and 9.5

Yeah, I was about to edit my answer, that the log will be full of errors

@dezso First I only tried to stop a master and see if an existing alve will complain or not.

It didn't even after restarting the cluster

Then I took another cluster (not a slave, not a master), added the recovery.conf and restarted. Just works

swasheck

10:52 PM

hello all

James L

@JoeObbish I think it was about support for regex (or at least some type of pattern matching) in replace()

If it doesn't ring a bell no big deal

@swasheck hello

swasheck

what do you all use for on-call escalation management?

James L

a wiki page with a list of phone numbers

swasheck

sweet.

James L

i know that's not a very helpful response

bluefeet

10:59 PM

@swasheck pager duty - pagerduty.com

James L

but i'm not sure what you mean i guess?

we use telegram for sending alerts to our phones

it integrates pretty well with scom, nagios, etc

swasheck

@bluefeet cool. how do you get stuff to pagerduty? do you have some sort of APM?

@JamesL right. are you using SCOM? Nagios? Prometheus?

James L

@swasheck scom for windows, nagios for unix and miscellaneous stuff, powershell and a gateway to telegram for most sql server related things

bluefeet

@swasheck number which then notifies the person oncall

swasheck

@JamesL soooooooooooooo ... it's not unified :D

@bluefeet ah. didnt SO build their own monitoring service? it probably pushes to PagerDuty

github.com/opserver/Opserver

James L

11:04 PM

@swasheck no don't really see the benefit tbh. maybe i would if i was working with more people

swasheck

where are you, these days?

James L

an investment fund

london

swasheck

nice.

James L

i use bosun and opserver, just not for anything alert-related. pretty nice free tools

bluefeet

@swasheck well we use various alerts, etc that are seen by people which then could lead to escalating to the oncall person

we do use OpServer and Bosun to see what's happening but they don't alert to pagerduty

swasheck

11:09 PM

@bluefeet interesting. it looks like there's a config to push to pagerduty.

then there's this

> The Sentry One guys: Aaron Bertrand and Kevin Kline for even more SQL use cases, and their help with upcoming integration with SQL Sentry.

bluefeet

@swasheck where is that from?

swasheck

the github project's readme

bluefeet

oh yeah

Transcript for

The Heap™ – Consultancy ©®