« first day (3263 days earlier)      last day (45 days later) » 

 
3 hours later…
7:34 AM
@mustaccio Or upper case identifies you
Morning
 
morning
@PaulWhitesaysGoFundMonica is that Aaron? I don't see a name.
 
@PaulWhitesaysGoFundMonica Couldn't find the author.
Good Morning
 
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?
 
@Johnakahot2use Why, you forgot to mention hanging out in The Heap
For some, that may be what ties all the other activities
 
7:53 AM
Morning
 
@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
 
@Johnakahot2use I live immersed into the new ERP by now, 12, 13 hours at day.
 
8:09 AM
@AndriyM Heaps. Plural ;)
 
;-)
Off to get some dark brown
 
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 🙂
 
9:21 AM
I sent him a message about it.
 
Maybe he wanted it to be a puzzle. ;)
 
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
 
@McNets My heart-felt sympathy
I hope you're enjoying it?
 
@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.
 
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
@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
 
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?

Aaron BertrandThe 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...

 
create table FOO ... should throw something like "illegal unquoted identifier" rather than secretly changing what you thought you sent
 
@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.
 
and if select * from pg_namespace where relname = 'FOO' gets me a table, i should be able to select * from FOO
 
@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?
 
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
 
which string are you referring to?
SchemaName?
 
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
 
It might actually say that in very early versions of the standard ;)
 
@ypercubeᵀᴹ yea, there exist in this postgres database multiple objects with equivalent unquoted names
 
Then they "improved" it ;)
 
10:13 AM
"improved"
 
@PeterVandivier example?
 
see will smith
and the transcript from 10:00-11:00 UTC yesterday
 
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.
 
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 ;)
 
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
 
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?
 
stand up time - i have a sassy clap back though - hold plz :p
 
@ypercubeᵀᴹ Which bit don't you like? Not being able to create Foo, foo, and Foo as separate tables in a CI-collated database?
 
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.
 
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?
 
10:24 AM
@PaulWhitesaysGoFundMonica Like having a date at the wrong time?
 
@AndriyM Something like that 😀
 
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.
 
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.
 
And if you are not fond of posting the same stuff twice, you can post once... and import once :)
 
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
 
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 ...
 
@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
 
The readme at that repo has:
> How to contact the authors:

email: pgsql-odbc@postgresql.org
website: https://odbc.postgresql.org/
 
🤷‍♂️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
 
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?
 
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
 
> 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.
 
it's saying stuff like "press this button"... is there a UI for managing the driver?
 
No idea.
 
like... it seems like that's configurable
def not a bug at least
 
This page has a section "Installing psqlODBC on 64bit Windows" : odbc.postgresql.org/faq.html
 
10:54 AM
if only i were on windows :p
 
with some registry settings that include "BoolsAsChar"="0"
Ah, so if not Windows then what?
 
yea, looks like i may need to build from source if i want that 😢
macos
 
@Johnakahot2use I enjoy the work not the users...
3
 
weird that that's a default for the common distro though (brew is the mac package manager btw)
 
@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.
 
10:59 AM
It's still best to avoid them when possible imo
 
@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.
 
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

Eduard Testeri 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...

 
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
 
@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.
 
@mustaccio I stand corrected then. I'll check what Postgres does with CI collations.
 
12:19 PM
@TomV-TeamMonica closed as stahhhp
 
> 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.
 
12:42 PM
@ypercubeᵀᴹ :p looks like it's linking db-fiddle, not db<>fiddle
db-fiddle != db<>fiddle
 
1:12 PM
@PeterVandivier Well that's a bit annoying isnt it
Two sites with the exact same name lol
 
@PeterVandivier oh boy, you are right! it's db-fiddle.com, not our dbfiddle!
 
@PeterVandivier Also, db-fiddle<>db<>fiddle
3
FTFY
The pro account features aren't bad on that
 
@George.Palacios silly me, deliberately choosing the less ambiguous form
 
@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
 
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".
 
@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
 
@George.Palacios Jokes at explained great length, but no, I have no complaints about that. My issue is with the inconsistency of your typos.
 
I'm sure he maent well
 
I'm mot inconfistent at all! I'm noy even making eny errors!
 
@AndriyM good piont
 
1:33 PM
Spare with him. He's only novice typoer ;)
 
@AndriyM Fine then I'll explain great lengths at joke instead
 
@ypercubeᵀᴹ Yes, he cuold learn a thing or two from yuo
 
EGLAJ sounds much better anyway
 
 
1 hour later…
3:03 PM
@AndriyM Points for similarity to actual typocube syntax
 
3:14 PM
A question for you tehn:
0
Q: A single column in user table need to refer to these multiple book registers

a2warikI 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 ...

....then I used to lable it PP-872....
 
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
 
 
3 hours later…
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
 
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.
 
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.
 
I added a column which will make one of the new indexes fully cover this view.
 
8:30 PM
Well, I have progress
 
you could try updating the statistics, freeing the proc cache and seeing if the bad plan still gets generated
(on dev)
 
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.
 
8:46 PM
lol Max
maybe it's the view-ception causing the problem?
 
👍👍
 
9:27 PM
@CadeRoux did both plans finish with "good enough plan", or did either time out?
 
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.
 
10:11 PM
@MaxVernon i'm unreasonably jealous of the secret communist VR hat you seem to hve acquired
 

« first day (3263 days earlier)      last day (45 days later) »