The Heap™ – Consultancy ©®

Paul White says GoFundMonica

4:38 AM

sqlblog.org/2019/12/10/t-sql-tuesday-121-gifts

Andriy M

7:34 AM

@mustaccio Or upper case identifies you

Morning

ypercubeᵀᴹ

morning

@PaulWhitesaysGoFundMonica is that Aaron? I don't see a name.

John aka hot2use

@PaulWhitesaysGoFundMonica Couldn't find the author.

Good Morning

Tom V - Team Monica

@ypercubeᵀᴹ Yes, he tweeted a link to it

twitter.com/AaronBertrand/status/1204529429294276614

John aka hot2use

Do you people actually work between reading all the tweets, moderating multiple sites, reading hacker news and drinking coffee? Or are you paid for this?

Andriy M

@Johnakahot2use Why, you forgot to mention hanging out in The Heap

For some, that may be what ties all the other activities

McNets

7:53 AM

Morning

Tom V - Team Monica

@Johnakahot2use Being a 10x-er and try to avoid making your co-workers look slow?

At least that's what I think some of the others do here. Me I'm mostly only lurking here, twitter/facebook/... I don't really follow. I just checked Aarons twitter just now to be sure it was him posting that linked blog

McNets

@Johnakahot2use I live immersed into the new ERP by now, 12, 13 hours at day.

ypercubeᵀᴹ

8:09 AM

@AndriyM Heaps. Plural ;)

John aka hot2use

;-)

Off to get some dark brown

Paul White says GoFundMonica

9:10 AM

@ypercubeᵀᴹ He's updated his theme recently. You're right, Aaron's name doesn't appear anywhere. That's going to make the CC BY 3.0 licence harder to follow 🙂

Paul White says GoFundMonica

9:21 AM

I sent him a message about it.

ypercubeᵀᴹ

Maybe he wanted it to be a puzzle. ;)

Peter Vandivier

9:44 AM

@ypercubeᵀᴹ I guess the thing that bothers me is that it’s not raised as an error or a notice. It seems like simply disallowing capital letters in identifiers out of quotes makes more sense than silently replacing them and just expecting the user to notice (despite the fact that the standard return message for that command has no indicator of the name)

Or make it a sql_safe_updates style default setting

Sql server seems intuitive to me because in default config it both respects the spec ( foo is equivalent to FOO ) and doesn’t hijack the user’s input without so much as an “if you please”

You could argue that CS collations in sql server break the spec. - but only in the same way that quoted identifiers do in MySQL or Postgres. And CS is non default in SQL server

John aka hot2use

@McNets My heart-felt sympathy

I hope you're enjoying it?

ypercubeᵀᴹ

@PeterVandivier no it does not respect the standard. SQL Server does not allow you to create "foo" and "FOO" if the db collation is CI. That is not standard.

@PeterVandivier what is the hijacking?

You created a table with double quotes CREATE TABLE "SchemaName" (...); and then you tried to access it without. You got an error.

I understand SQL Server (with a CI collation) allows you that and you are used it.

I mean this: dbfiddle.uk/…

would be counter-intuitive to anyone that has worked with Postgres or Oracle only before.

> There is already an object named 'FOO' in the database.

Peter Vandivier

10:01 AM

@ypercubeᵀᴹ ah, i see your confusion - i didn't actually create the table, i was tracking down a bug in a framework

github.com/DbUp/DbUp/issues/449#event-2870295437

@ypercubeᵀᴹ exactly, foo is equivalent to FOO in CI (which is default) - you can't create both becuase they are equivalent

19 hours ago, by mustaccio

@PeterVandivier 5.4.2. An <SQL language identifier> is equivalent to an <SQL language identifier> in which every letter that is a lower-case letter is replaced by the corresponding upper-case letter or letters.

@ypercubeᵀᴹ in a select statement

or really, a create statement either

if unquoted caps are disallowed, then throw an error

Paul White says GoFundMonica

Not directly related to the current discussion, but still interesting:
The only hard-coded collation in SQL Server is `Latin1_General_CI_AS_KS_WS`. It is used for system (as opposed to user) metadata.

17

A: What's up with the collation of some columns in sys.databases?

The official word from Microsoft: Some of the columns that contain pre-defined strings (like types, system descriptions, and constants) are always fixed to a specific collation – Latin1_General_CI_AS_KS_WS. This is irrespective of instance/database collation. The reason is that this is system...

Peter Vandivier

create table FOO ... should throw something like "illegal unquoted identifier" rather than secretly changing what you thought you sent

ypercubeᵀᴹ

@PeterVandivier sure but that is for unquoted identifiers. If the framework had created the table using quotes, then you'll always have to quote it.

Peter Vandivier

and if select * from pg_namespace where relname = 'FOO' gets me a table, i should be able to select * from FOO

ypercubeᵀᴹ

@PeterVandivier but that's what the SQL Standard says. Postgres does do something differently (lowercase than uppercase) but it really follows the standard here

Why should it throw "illegal unquoted identifier". Do you mean that you should always have to quote identifiers? Which DBMS does that?

Peter Vandivier

10:11 AM

i'm saying if you pass in a string that breaks a rule, then at the very least, the verbose stream should let me log that it broke a rule

casing rules around types aren't the craziest thing in the world

ypercubeᵀᴹ

which string are you referring to?

SchemaName?

Peter Vandivier

and if the spec wanted everything to be either caps or lower, then it should say that explicitly. i'd still think it was dumb, but at least it'd be clear

ypercubeᵀᴹ

It might actually say that in very early versions of the standard ;)

Peter Vandivier

@ypercubeᵀᴹ yea, there exist in this postgres database multiple objects with equivalent unquoted names

ypercubeᵀᴹ

Then they "improved" it ;)

Peter Vandivier

10:13 AM

"improved"

ypercubeᵀᴹ

@PeterVandivier example?

Peter Vandivier

see will smith

and the transcript from 10:00-11:00 UTC yesterday

ypercubeᵀᴹ

Can you please show me an example? I can't search at the moment.

You could have FOO, foo and Foo as 3 tables in a db.

Paul White says GoFundMonica

18 hours ago, by Peter Vandivier

ypercubeᵀᴹ

If you try to access without quoting, SELECT * FROM Foo;or SELECT * FROM FOO; or SELECT * FROM foo;, in Postgres you'll get the data from foo

That's all you need to remember.

If you want to call it hijacking, fine. Track down the standard committee ;)

Peter Vandivier

10:17 AM

yes... i know that now... i'm saying that coming into it without assumed foreknowledge, the confusion i experienced is almost inevitable

hence... "unintuitive"

and given the existing artifact type of "sql_safe_updates", this really seems like the sort of behaviour that the server should send you a nice spammy message about

thus making it more intuitive

ypercubeᵀᴹ

OK, let me reverse the situation. I don't like SQL Server behaviour. How easy would it be for me to contact the SQL Server devs in MS and request they add a flag for me in SQL Server so it behaves in the standard way?

Peter Vandivier

stand up time - i have a sassy clap back though - hold plz :p

Paul White says GoFundMonica

@ypercubeᵀᴹ Which bit don't you like? Not being able to create Foo, foo, and Foo as separate tables in a CI-collated database?

ypercubeᵀᴹ

It's rhetorical anyway ;)
I know I could open a Connect item (or whatever they call that now) and get a reply "we will look at it" in seven months and then 3 years later declined with "Works as designed" ;)

@PaulWhitesaysGoFundMonica yes but it's an hypothetical. I don't really mind that.

Paul White says GoFundMonica

All string behaviour is weird and counter-intuitive if you poke at it enough.
It's like dates and times.

@ypercubeᵀᴹ Right. Hey, just out of curiosity, does Postgres have the same concept of collation at instance and database level? Does it affect the ability to create identifiers that differ only by e.g. case?

Andriy M

10:24 AM

@PaulWhitesaysGoFundMonica Like having a date at the wrong time?

Paul White says GoFundMonica

@AndriyM Something like that 😀

ypercubeᵀᴹ

Hah yeah. I have been preparing some Q-and-As (some in draft, some still in my mind) about differences between DBMS (in UPDATE, date-and-time functions/operators, etc) but MonicaGate hit us and I've froze it for a while. Deciding where to post.

Paul White says GoFundMonica

SQL Server has a lot of backwards-compatibility baggage, including with identifiers. Have a look into partially-contained databases sometime if you really enjoy getting frustrated and annoyed.

@ypercubeᵀᴹ You can post in as many places as you like. It would be nice to see some postgres Q & A on xyz.

Andriy M

And if you are not fond of posting the same stuff twice, you can post once... and import once :)

Paul White says GoFundMonica

True

Though realistically copy-paste isn't much harder

The markdown is very compatible

More precisely, TA is close to being a superset of SE

ypercubeᵀᴹ

10:32 AM

@PaulWhitesaysGoFundMonica yes but thinking about it I'm not really sure what happens with identifiers if the instance or db has CI collation. I don't think I've tried that ever. It may well behave like SQL Server ...

Peter Vandivier

@ypercubeᵀᴹ i'll hold on to my sass for another time then ;)

@ypercubeᵀᴹ since you're about this morning though - i'm trying to figure out where to report a bug in the psqlodbc driver - it appears to be treating bool as string. the pgsql-hackers groups are still a bit new and intimidating so if you have any suggestions that'd be pretty swell

unless... that's intentional? github.com/hiinoue/psqlodbc/search?q=bools_as_char

ypercubeᵀᴹ

The readme at that repo has:

> How to contact the authors:

email: pgsql-odbc@postgresql.org
website: https://odbc.postgresql.org/

Peter Vandivier

🤷‍♂️may do - i've been having good luck with bugs and PRs on projects i have no familiarity with lately

it makes me nervous inviting myself in to massive FOSS projects like that though without at least lurking in a relevant chatroom for a little

ypercubeᵀᴹ

This looks like a small side project (psql - odbc) so you can just send them an email.

Is that bool_as_char a config setting?

Peter Vandivier

10:48 AM

i'm thinking it must be

i tried building from source for an hour or two before i just bailed and did a brew install though

so i'm not fully sure

ypercubeᵀᴹ

odbc.postgresql.org/docs/config.html

> Data Type Options: affects how some data types are mapped:

Text as LongVarChar: PostgreSQL TEXT type is mapped to SQLLongVarchar, otherwise SQLVarchar.
Unknowns as LongVarChar: Unknown types (arrays, etc) are mapped to SQLLongVarChar, otherwise SQLVarchar
Bools as Char: Bools are mapped to SQL_CHAR, otherwise to SQL_BIT.

Peter Vandivier

it's saying stuff like "press this button"... is there a UI for managing the driver?

ypercubeᵀᴹ

No idea.

Peter Vandivier

like... it seems like that's configurable

def not a bug at least

ypercubeᵀᴹ

This page has a section "Installing psqlODBC on 64bit Windows" : odbc.postgresql.org/faq.html

Peter Vandivier

10:54 AM

if only i were on windows :p

ypercubeᵀᴹ

with some registry settings that include "BoolsAsChar"="0"

Ah, so if not Windows then what?

Peter Vandivier

yea, looks like i may need to build from source if i want that 😢

macos

odbc.postgresql.org/docs/unix-compilation.html

McNets

@Johnakahot2use I enjoy the work not the users...

3

Peter Vandivier

weird that that's a default for the common distro though (brew is the mac package manager btw)

Michael Green

@George.Palacios I was watching a lecture where the Prof introduces this to his class. The prof's all "best thing since sliced bread" and genuinely shocked that the students aren't dancing in the aisles at the prospect of function in-lining. For my two cents, I don't write nor do I see many of these functions. So the actual run-time impact will be slight. I guess we've been so conditioned, as T-SQL programmers, to avoid them through the years.

Paul White says GoFundMonica

10:59 AM

It's still best to avoid them when possible imo

Michael Green

@PaulWhitesaysGoFundMonica Tell me about it. I used crew with Ms WOOLLEY and Mr SCARFE. Oh, how we laughed.

@Johnakahot2use I'm unemployed. Apart from school drop-offs and pick-ups this is literally my day.

Peter Vandivier

11:44 AM

reddit.com/r/ProgrammerHumor/comments/e8xqgs/…

Tom V - Team Monica

None of the friendly blue messages expresses my feelings on this question

0

Q: formula for how much decrease the speed of the server if pictures are in the database as blob

i am interessted in to know how much decrese the servers speed if you put pictures as blob files in it. Lets say each kind of picture is under 500kb. did existst any kind of formula to see how much pictures are needed to decrease the database speed from 100% down to 80% or 70% depending on each k...

performance

ypercubeᵀᴹ

12:01 PM

dbfiddle.uk is on Postgres Weekly latest issue. @JackDouglas

> DB Fiddle: An SQL Database Playground — I frequently find this tool useful for testing out little bits of SQL. It lets you run basic queries upon several versions of Postgres (including, now, version 12), MySQL, and SQLite, right from your browser.

STATUS200

mustaccio

@ypercubeᵀᴹ It may not be intuitive, but it is in line with the SQL standard, which stipulates that quoted identifiers should be compared using collation rules in effect.

ypercubeᵀᴹ

@mustaccio I stand corrected then. I'll check what Postgres does with CI collations.

Peter Vandivier

12:19 PM

@TomV-TeamMonica closed as stahhhp

John aka hot2use

> Everyone from that age remembers expert sex change.

@TomV-TeamMonica None of the (whatever) blue messages seem to express my feelings. They seem to have lost their meaning in becoming welcoming.

Peter Vandivier

12:42 PM

@ypercubeᵀᴹ :p looks like it's linking db-fiddle, not db<>fiddle

db-fiddle != db<>fiddle

George.Palacios

1:12 PM

@PeterVandivier Well that's a bit annoying isnt it

Two sites with the exact same name lol

ypercubeᵀᴹ

@PeterVandivier oh boy, you are right! it's db-fiddle.com, not our dbfiddle!

George.Palacios

@PeterVandivier Also, db-fiddle<>db<>fiddle

3

FTFY

The pro account features aren't bad on that

Peter Vandivier

@George.Palacios silly me, deliberately choosing the less ambiguous form

George.Palacios

@PeterVandivier Did you not remember what room you're in?

You're meant to be ambiguous and then JAEGL it

Either that or something something quoted identifier

Andriy M

1:30 PM

@George.Palacios From the viewpoint of someone with OCD-like traits (like me), that looks rather inconsistent. If you say, "JAEGL", you should also say "maent".

George.Palacios

@AndriyM What don't you understand about at jokes explaining great length?

This is simple stuff man

I always put A first for some reason

Andriy M

@George.Palacios Jokes at explained great length, but no, I have no complaints about that. My issue is with the inconsistency of your typos.

Paul White says GoFundMonica

I'm sure he maent well

George.Palacios

I'm mot inconfistent at all! I'm noy even making eny errors!

Peter Vandivier

@AndriyM good piont

ypercubeᵀᴹ

1:33 PM

Spare with him. He's only novice typoer ;)

George.Palacios

@AndriyM Fine then I'll explain great lengths at joke instead

Andriy M

@ypercubeᵀᴹ Yes, he cuold learn a thing or two from yuo

George.Palacios

EGLAJ sounds much better anyway

Tom V - Team Monica

3:03 PM

@AndriyM Points for similarity to actual typocube syntax

McNets

3:14 PM

A question for you tehn:

0

Q: A single column in user table need to refer to these multiple book registers

I work in a library, here there are many registers called main, pre-primary, donated etc. Each has a primary key sort of numbering called accession number. I used to log users issue return based on the register and accession number, example if its pre-primary books with accession 872 then I used ...

database-design sqlite

....then I used to lable it PP-872....

Peter Vandivier

3:47 PM

hey hey hey... looks like the latest version of pgAdmin is actually stable on postgres 12

that's a pleasant change of pace

Max Vernon

7:06 PM

hmmm, my answer just got called a "comment". d'oh!

Thanks for the comment. MAXDOP is already correctly set to 8, with a threshold that makes sense for when queries should go parallel (for this server, about 1400). — Mike P. 1 hour ago

Cade Roux

7:47 PM

Y'all ever see the addition of an index cause a query to take much longer? I have 29 views in this system I am looking at and I was comparing a SELECT * from each of the views before and after a hypothetical addition of two indexes, and a view I haven't looked at yet now runs 253% longer.

It looks like maybe one of the new indexes "looks better" to the optimizer for this query than what it was doing but it's (currently) not fully covering that query.

Glad I decide to run this naive run of selecting from all the views.

Andriy M

With what little knowledge I have, I wouldn't be too surprised. The index may be causing SQL Server to come up with a plan that it considers good enough sooner than the one it ends up with without that index.

Cade Roux

I added a column which will make one of the new indexes fully cover this view.

Cade Roux

8:30 PM

Well, I have progress

James

you could try updating the statistics, freeing the proc cache and seeing if the bad plan still gets generated

(on dev)

Max Vernon

Cade Roux

All the caches are cleared before each run, whether baseline or after indexes. This is all in dev.

This is just naive SELECT * from each view - I need to generate the actual workload from the SSRS to see what the particular reports are doing. I was just starting by getting a baseline and since there are only a few views, I figured I could try all of them.

Might not reflect complete real world use since once the views are combined in the particular reports, they might perform better.

Some of these views are built on top of each other as well.

This is a test database of 200k variety of randomly generated cardiovascular studies that have already been shredded from XML into an analytics DB.

James

8:46 PM

lol Max

maybe it's the view-ception causing the problem?

Max Vernon

👍👍

Michael Green

9:27 PM

@CadeRoux did both plans finish with "good enough plan", or did either time out?

Cade Roux

9:49 PM

@MichaelGreen Nothing times out. The most egregious went from 268s to under 1s. With the extra columns, the weird increases come down in line.

Peter Vandivier

10:11 PM

@MaxVernon i'm unreasonably jealous of the secret communist VR hat you seem to hve acquired

Transcript for

The Heap™ – Consultancy ©®