A chairde - Morning all!

Look at that - last one out, first one in! Yaay for me!

Morning

Morning

@Vérace WAL seems to work.

pga_hba.conf is a bonus (Security)

It's slightly more stable than other "free" RDBMSs

Performance is better. We have moved some database systems from Oracle to PostgreSQL with no down-side performance-wise

@JohnK.N. Hi - you say "slightly more stable" - which implies some level of instability. What sort of issues have you experienced? With respect to Oracle? "Performance is better.". Better than what? Oracle? MySQL? Reads/Writes? I'm interested in the opinions of those who have used both proprietary and Open Source systems in production - many shops are single server only - when one only has a hammer, everything turns into a nail! :-)

Seriously though, I've worked with Oracle in the past and I know that it's a beast of a system in both the good and bad senses of the word - functionality up the wazoo, but many, many buttons & knobs to twiddle. PostgreSQL has ~ 300 parameters in postgresq.conf. Anyway, interested in your opinion!

@JohnK.N. The database on PostgreSQL just run. Indefinitely. Without issues. MySQL database have occasional locking issues, which don't seem to occur in PostgreSQL.

Maybe it has to do with how the developers develop for MySQL (with the various engines you can select) compared to developers for PostgreSQL.

PostgreSQL and MySQL probably attract a different crowd and/or mindset of developers.

Morning

@dezso I believe that "internet scale" is part of the ISO-XYZ-9999-2022 buzzword-compliance standard - usually said in the same breath as "cloud native"!

Both phrases can mean pretty much anything the (marketing-) person uttering them wants them to mean.

Skilling, FFS

Somone in marketing has too much free time and lacks judgement

This may come as a shock, but the best and brightest people do not always choose a marketing career

yeah

Qell, so far they're (s)killing it.

Quelle surprise

Did I accidentally edit that comment? I'm pretty sure I wrote it without a typo

Thanks for someone™ removing that comment, but I still am wondering what scale it actually is. Also, the direction of 'velocity' in that statement.

@Zikato Nope, never edited

damn, my brain is playing tricks on me

@dezso Sounds like you have a cassandra terminology question

Why is changing a column from NULL to NOT NULL such a pain?

@Zikato What do you mean exactly? That the engine has to check every record to make sure there are no existing nulls?

Or that every row has to be physically rewritten?

Well… both. Mainly the online aspect of it. For example, existing indexes prevent me from changing that as well so I have to drop and recreate even though the data is not changed

Do you care that the definition changes, or happy to add a check constraint?

The optimizer will trust a trusted constraint, yes

ALTER TABLE dbo.Comments
    WITH CHECK ADD
        CONSTRAINT [CHK dbo.Comments Score NOT NULL]
            CHECK (Score IS NOT NULL);

For example.

This is a more "online operation"

In 2022, yes

I think that's right? They added online table constraints to 2022?

Or is it just some kinds of constraint? I'll check (ha)

I mean that I don't have to drop and recreate indexes for this to work

Oh yeah, it's resumable PK and UQ constraints for 2022

but by the same logic. If I introduce a trusted check constraint, is there any reason why changing the definition of the column now would force the engine to check every record or rewrite the rows? Other than that it's probably not programmed

It could skip the checks, but I'm not certain it does

ALTER TABLE dbo.Comments
    ALTER COLUMN Score integer NOT NULL
        WITH (ONLINE = ON);

exploring this train of thought even further - if this is all possible, then it could be an internal implementation. I wouldn't have to create a constraint and then change the definition, it would all be done automatically when I issue ALTER COLUMN… NOT NULL

It's not a metadata-only change, though I suppose it could be, if they put enough effort in. You'd just be spreading the cost of rewriting the records over time though, assuming they're ever updated.

@PaulWhite Still requires me to drop the existing dependent index

yes, the biggest pain point now is the dependent index

@Zikato Right, but the end result would not be the same.

would it not?

@Zikato Many of the effects (e.g. on optimization) would be the same, but the metadata still says the column allows nulls, and the physical pages are laid out for a nullable column e.g. null bitmap.

but once you have that guarantee and don't have to check the values. It could be a metadata operation (change the null bitmap)

No, because the physical row layout is different. A metadata-only change only changes metadata.

The null bitmap is part of each physical row, not metadata.

oh, yeah, that's right

I gotta revise my internals info

Hm

Actually, now I think about it, I'm pretty sure changing null to not null doesn't require changing the physical rows.

The null bitmap is always present (there's one edge case exception to that) and will already be set correctly.

So, in principle, the check for nulls could be skipped, and the change made in metadata.

It's very hard to remember all this stuff. As I think I have demonstrated quite well.

and indexes don't contain a null bitmap, right?

since it's a copy of the original data

I still can't let the dependent indexes go.

As Rocky said: "If the data doesn't change and the index doesn't change, then nothing has to change."

@Zikato I just checked. Nonclustered indexes do have a null bitmap if there are any nullable columns in the index row.

They don't have a bitmap (or number of columns field) if there are no nullable columns.

I've tried to czech as well. Record Attributes = NULL_BITMAP<- this means it's there, right?

Yes

One could argue the nonclustered index wouldn't need to change if there would still be at least one nullable column in it after the column of interest was changed to NOT NULL, but this is getting a bit silly. And there might be some edge case idk.

Even though the bitmap is there, the value of the bitmap doesn't change since it's all 0s for that column anyway

Right

But if it's the only nullable column in the index, it'll go away when the change to NOT NULL is made

I'd keep it there, I won't mind

True

But your next DBCC CHECKDB might

The whole thing is a massive pain, but there you go

that's overrated anyway. There was a bug in the DBCC CHECKDB and it was there from CU13 until fixed in CU 17 (SQL 2019)

Nulls were a mistake

NULLs are nothing of a value

@Zikato There have been quite a few since the rewrite. Mostly false negatives, but still.

exactly this case - In Memory table types this time.

@Zikato This bug? dba.stackexchange.com/q/307609/1192

yes. Fixed in CU17 if you're inclined to update your answer

Yep, I'll do that now

The weird thing is, that when I've googled for it, this Q/A didn't come up. Probably a bad SEO

Well, the error message and number is different

DBCC CHECKDB for the sample database given in the question returns without error now, so that's good enough for me.

yes. I've linked the same KB as you did the first time around. But then I noticed they mention TVF instead of Table Types

maybe two different bugs?

Perhaps. I can't be bothered to check how they fixed it.

That's fair

@Zikato Given the recent quality of CU docs, it's just as likely the CU is wrong.

The release was during the summer

intern-al issues then

Who is this Al, and why he makes so many mistakes?

@dezso Apologies for my earlier flippancy - I think that "internet scale" means a) distributed and b) has a large number of compute nodes and/or users. My earlier point about it being vague stands - what is a "large number" of users/nodes?

The example Cassandra quotes a lot is Netflix

> “The automated tooling that Netflix has developed lets us quickly deploy large scale Cassandra clusters, in this case a few clicks on a web page and about an hour to go from nothing to a very large Cassandra cluster consisting of 288 medium sized instances, with 96 instances in each of three EC2 availability zones in the US-East region...

> ...Using an additional 60 instances as clients running the stress program, we ran a workload of 1.1 million client writes per second. Data was automatically replicated across all three zones making a total of 3.3 million writes per second across the cluster.”

@Zikato popcorn.gif?

@SeanGallardy-MostlyRetired you've probably got first row seats

Since I moved out of CSS I try not to deal with this much anymore

I'm sure it'll get renamed in another 5 years when someone needs a promotion

improve.microsoft.com - you've heard it here first

That'd be a great feedback site

I thought feedback is redirected to NUL

They do what they can :D

@Zikato But not /dev/null.

I’m still trying to get someone at Microsoft to change the header on their feedback site to include “where ideas go to die”

To be fair, some of those ideas should stay just ideas.

I didn’t say every death was wrongful

@PaulWhite would a link to my blog post be a useful addition to this meta q&a or is it just shameless self promotion?

@ErikDarling Yes it would. I'll add it later if you don't want to

@PaulWhite I’m fine doing it. If you have a preference on location and wording let me know.

@Vérace I had already done the same comment. And seems to be a valid solution to the OP's question.

@ypercubeᵀᴹ You reckon I should delete?

No need 😀

not sure what's best for that duplicate q (duplicated at SO)

twitter.com/kendra_little/status/1574519701396287488 interesting seasoning and flavours you have there

What Is the Poo Driver?

Sure do. Sure do.

I didn't expect civilizational collapse to happen quite so quickly

Traveling at the speed of light

@ypercubeᵀᴹ Just a timing difference. If we had seen it at the time, we would have closed it, and it would be deleted by now. Fixed.

The user hasn't even visited this site in over a year.