The Heap™ – Consultancy ©® - 2020-03-03

« first day (3346 days earlier) ← previous day next day → last day (1520 days later) »

2:44 AM

@PaulWhite9 unintended consequences can be a real bitch.

2 hours later…

Tom V - try topanswers.xyz

4:59 AM

203

Q: What is so bad about "Nancy"?

This question on MSO got a terrible edit war. The OP told a story with somebody called Nancy. For some reason, I don't know which, there was very some vital need to hide/obfuscate/censor the name or even the gender of that person. I never saw something like that before anywhere in SE. I don't un...

discussion edits meta code-of-conduct

2 hours later…

John aka hot2use

6:57 AM

Morning

sort of

Crisp and bright here - morning all!

Tom V - try topanswers.xyz

@Vérace Yeah here as well, cold but sunny. Start of spring

An example of the X-Y problem? What a d*******!

7:17 AM

Morning

@Vérace I think the kind of constraint that may be suitable for the OP's purpose is called an assertion in the standard. Firebird may be the only DBMS to have implemented them (if I understand your remarks in the comments correctly), even if it doesn't call them assertions.

7:36 AM

@AndriyM I don't think Firebird's implementation counts as a full one. AFAIK (haven't tested - read it I think), you can only refer to the same table within the SQL in the CHECK constraints. A real assertion (AKA declarative multi-row constraints) system would allow more complex multi-table SQL.

A problem with them can be seen here, but then that also goes for normal CHECK constraints. Such a shame that FB hasn't received the attention that it deserves - mind you, it is popular in Eastern Europe AIUI. It should be no. 2 in the F/LOSS systems with MySQL as no. 3 (PostgreSQL first, naturally)! :-)

7:49 AM

morning

@Vérace I totally agree.

The problem is interesting though, even if we don't know the rationale.

At least interesting for a puzzle solver ;)

8:21 AM

Morning

9:12 AM

@ypercubeᵀᴹ You can parse that word problem?

@PaulWhite9 yes. It doesn't mean my parsing interprets the OP's vision correctly ;)

Some examples would certainly help.

> Sorry - I'm not sure that I'm getting you? You can put a UNIQUE constraint on A and there will be no duplicates of A, and likewise for B. But now you're telling me that you want any value that already exists in A not to be allowed in _either_ A or B? And likewise for B (no dupes in B _or_ A)? – Vérace Feb 28 at 20:44

that is correct. Hard to explain, sorry for the confusion. – prolink007 Feb 28 at 20:45

Oh there's a deleted answer lol

@ypercubeᵀᴹ It shows that Vérace did manage to come up with a description that the OP seemed to agree with. The OP could add that description to their question to make the problem potentially clearer.

9:26 AM

If only SE allowed anyone to edit posts ;)

I made an edit.

@ypercubeᵀᴹ Yes - it took quite a bit of effort to finally extract an accurate description from the OP, and then the b****** wouldn't tell me why he wanted to do his! You're correct about it being an interesting problem, hence my "intellectual curiosity" as to the reasons underlying it!

I would post the Firebird solution - with the note that it is only for Firebird, not for MySQL.

(and by the way, you don't need the CHECK (column_A != column_B) constraint there, the two NOT IN are enough ;)

@ypercubeᵀᴹ One small step towards getting MySQL to become the #3 FOSS database system

9:41 AM

@ypercubeᵀᴹ you don't need the CHECK (column_A != column_B) constraint there - not the case - see here!

Ah, right.

I thought the test (4,4) covered that. Missed that there was a (2,4) above.

Somewhat counter-intuitive to me. I'd expect the (56, 56) row to fail even without the != check constraint, though I understand how it may be a little complicated to implement that

Perhaps we'll get anti-FKs one day

2

yeah, I get that, here we've already had a few trail races cancelled due to swine fever in some areas
it would be petty to have street races cancelled too :(

morning guys

evening Paul :)

hello

9:52 AM

Paul, may I ask why the 9 in your username?

Shog9, Paul9, SevenOf9

@Marian Tribute to the departed Shog9

Tom V - try topanswers.xyz

@PaulWhite9 I can't wait for some confused anti-fk to a nullable column questions on this site.

eek

@PaulWhite9 ah, ok

ConcernedOfTunbridgeWells

9:58 AM

Seen on the interwebs today ...
What do you call an instagram celeb who got corona?
An influenzer!
But only if they go viral...

6

These people on the interwebs are so merry and groan up

ConcernedOfTunbridgeWells

Dad joke of the day.

I'm a dad now, so I'm allowed to tell dad jokes.

@ConcernedOfTunbridgeWells nice one.

I should be arrested then (when I do)

etymonline.com/word/influenza?ref=etymonline_crossreference

ConcernedOfTunbridgeWells

10:09 AM

In unrelated news ...

user image

3

@ypercubeᵀᴹ I have posted the Firebird solution as suggested - I also changed the title - I hope it succinctly sums up the OP's intentions (removed the "welcome to the forum" bit in my original reply though! :-) )

@PaulWhite9 I changed the title following your clarifiying edit - should have done that myself, but I was sulking! :-)

Yes I saw, thanks

@ypercubeᵀᴹ Oooohh.... Seven of Nine...

yeah, unrelated to the question but a famous 9

10:38 AM

@ypercubeᵀᴹ One of the ways in which FB is inferior to PostgreSQL is in the question of when constraints are evaluated. According to [here](http://firebird.1100200.n4.nabble.com/Deferrable-Constraints-td1114090.html) "No. All constraints are evaluated when the row changes - not even
at statement end, as the standard specifies." old post, but hasn't changed AFAIK - therein lies the problem - the background triggers as implemented must be implemented at the wrong time - maybe if they were deferrable? But that's not possible in FB at the moment - see [here](https://stackoverflow.com/question…

@AndriyM I tried to address your (also ypercube's) point about (56, 56) not being a valid "INSERT" - the two SQL based CHECK constraints should preclude this - I agree, but there is a mechanism to do in the A != B constraint!

@Vérace My guess is they are implemented as BEFORE triggers. And the reason might be so that the transaction could be rolled back. (I mean, my other guess is that you can't roll back the transaction in an AFTER trigger in Firebird.)

→ 4 messages moved to Trash

Could be. Or the the subquery inside the constraint is using the snapshot of the table, not including the currently inserted row.

@AndriyM BEFORE is what I thought myself - the server checks - no 56 in A, no 56 in B, let's go ahead and do the INSERT! It's a wee flaw - now if this system was implemented in PG with deferrable constraints and everything checked before committing, all would be rosy in the garden! Still, I think that using DDL as a powerful DSL, with SQL in constraints for example - would be a really powerful way of controlling data!

Take a bank manager authorising loans - as they authorise a loan which brings their book over, say, €1M, the INSERT fails because they're not allowed be profligate with the bank's money! Might have been good before the 2007-2009 recession when banks were lending money like ti was going out of fashion :-) ! It's not good enough to have SQL within one table, because the manager could have serveral loan types in different tables?

10:54 AM

@Vérace not everything is rosy in Postgres either. UNIQUE constraint are not deferred by default. And they are checked per row, not even at statement end.

@ypercubeᵀᴹ Can you give me a concrete example of where this causes anomalies?

@Vérace dbfiddle.uk/…?

yeah, correct

another: dbfiddle.uk/…

Wow, I didn't realise that PostgreSQL would have an issue with that kind of scenario

but this works: dbfiddle.uk/…

and so does this: dbfiddle.uk/…

(edited ^^)

11:08 AM

Does Postgres use triggers for deferred constraints then?

Not sure

most likely

I was looking at this output in the "but this works" link:

> Trigger for constraint t_c1_key: time=0.484 calls=2

SQL Server uses split/sort/collapse to avoid update intermediate key violations dbfiddle.uk/…

@PaulWhite9 does SQL Server allow deferred FK and CHECK constraints?

@ypercubeᵀᴹ I don't think so, but I always have to go check what deferred means.

check at end of transaction

11:14 AM

not end of statement?

no

then no 🙂

end of statement is normal, = undeferred

then SQL Server is fully normalized

maybe normal is not the correct word, but the default according to SQL standard, not sure

11:16 AM

I understand that end of transaction semantics can be very useful.
Easy to outsmart oneself though.

Hmm, PostgreSQL's UNIQUE constraint is undeferred by default by I wouldn't call the way it works normal or standard-compliant

@AndriyM no, it isn't.

I don't know which word is used to describe constraints that are checked for every row.

i.e. before the end of the statement

immediate?

@PaulWhite9 No. Non-standard check ;)

IMMEDIATE = check at end of statement
DEFERRED = end of transaction

from postgres docs:

> Non-Deferred Uniqueness Constraints
When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.

Yes I just saw:

> Also, PostgreSQL checks non-deferrable uniqueness constraints immediately, not at end of statement as the standard would suggest.

In another document.

I wonder if the "significantly slower" refers to the use of triggers.

11:27 AM

Be aware that this can be significantly slower than immediate uniqueness checking – but is it slower than working around the default behaviour your own way? Say you do need to update multiple keys. You can either redeclare the constraint as DEFERRED or, say, update the keys row by row. Would the DEFERRED declaration make a single update slower than your own RBAR solution?

@AndriyM note that DEFERRED is not needed for Paul's UPDATE example. IMMEDIATE is enough. The question is good though, I don't know

Somewhat related:

15

A: Deferrable unique index in postgres

A index cannot be deferred - doesn't matter if it is UNIQUE or not, partial or not, only a UNIQUE constraint. Other types of constraints (FOREIGN KEY, PRIMARY KEY, EXCLUDE) are also deferrable - but not CHECK constraints. So the unique partial index (and the implicit constraint it implements) wi...

I guess the answer to my question can be found empirically. But it seems notable that they would put that word of caution in the docs.

The caution is I guess because declaring a UNIQUE constraint as DEFERRABLE INITIALLY IMMEDIATE, you get to slow down all updates of the column, not only the ones that would cause an error and need a bypass

Fair enough

1 hour later…

12:36 PM

@PaulWhite9 the answer is yes.

Thank you

Also, as mentioned in the doc you linked, these are CONSTRAINT triggers and they can also be user defined. I think without any associated declared constraint. An idea for @Vérace to add another answer on that q, with a Postgres solution.

@ypercubeᵀᴹ Oh, no! You're giving me more work!

<voice of Yoda...> subtle databases are! Not having deferrable CHECK constraints is a real issue (bug! IMHO). Super up to date page comparing servers here - from the excellent Markus Winand - what is it about Austrians?) - a new version of this (was excellent until maintainer stopped) and this.

@Vérace bug is something broken. Lack of a feature is not bug ;)

One fly in the ointment, is "Deferred constraints(*) Yes Yes No for Oracle, PostgreSQL and SQL Server. Both Oracle and SQL appear to have constraints as DEFERRABLE by default and PostgreSQL doens't allow one to DEFER check constaints. I've sent an email to the address at the top of the page!

12:51 PM

@Vérace "Both Oracle and SQL"? SQL?

@ypercubeᵀᴹ Take a look here! Oracle DEFERs by default!

Ok but what is SQL in that context? The standard?

Tom V - try topanswers.xyz

1:09 PM

Pff, Time zones are hard

3

1:22 PM

@ypercubeᵀᴹ "Both Oracle and SQL" - a mis-typographical-error of the gravest proportions! Bad Vérace! Obviously, I meant "Both Oracle and SQL Server" - I definitely don't regard "SQL" and "SQL Server" as synonyms whatever the fan-boys sometimes suggest!

SQL Server does NOT have deferred constraints.

@ypercubeᵀᴹ "SQL Server does NOT have deferred constraints." - It appears to do them by default!

These are not deferred. These are IMMEDIATE, eg checked at the end of statement.

This would need to work if it was deferred: dbfiddle.uk/…

@ypercubeᵀᴹ "These are IMMEDIATE" - you are (as usual) correct. But this is MEGA-weird - it appears that a transaction only partially completes! Surely there couldn't be a bug as basic as this in SQL Server - am I missing something?

@Vérace dbfiddle.uk/…

1:35 PM

I'll add a PostgreSQL section to my Firebird answer - but later - got stuff to do - AFK for a while!

Only the failed statement was terminated, not the entire batch. Issuing SET XACT_ABORT ON allows you to terminate the entire batch on exception

@Vérace I may have not used correctly the transactions. There, AndriyM corrected me

Postgres with IMMEDIATE: same result as SQL Server: dbfiddle.uk/…

*terminate the batch and roll back the transaction

@AndriyM You call it a "batch" - is it not a transaction?

Postgres with DEFERRED: dbfiddle.uk/…

1:38 PM

@Vérace The batch is just a set of statements sent together. In that case the batch begins and commits a transaction

This demo shows that it's not just the transaction that's terminated and rolled back but also the entire batch that contained it. If only the transaction was terminated, the SELECT after the COMMIT would have executed.

I mean, a batch can only be terminated, not rolled back, of course

@Verace maybe you like this method better with TRY CATCH: dbfiddle.uk/…

Pardon the inconsistent use of terminnators. Copied from a random internet page

Only two are missing. TRY...CATCH is tricky in that regard, it contains many keywords but terminators are allowed only between some of them

I mean, it shouldn't really be difficult, because it's similar to other compound statements. You can't put a terminator before ELSE, for instance. It's only because it's so wordy that it can look tricky for some.

yeah. not allowed between END TRY / BEGIN CATCH

@ypercubeᵀᴹ btw on the subject of triggers and deferred things, I don't know if this is still the current behaviour, but: postgresql.org/message-id/…

> Support deferrable uniqueness constraints.

The current implementation fires an AFTER ROW trigger for each tuple that
looks like it might be non-unique according to the index contents at the
time of insertion. This works well as long as there aren't many conflicts,
but won't scale to massive unique-key reassignments. Improving that case
is a TODO item.

2:00 PM

I think that was from about when (deferrable) UNIQUE constraints were implemented.

But I suppose it explains the caution note

related discussion in pgsql-hackers from 2009: WIP: Deferrable unique constraints

2:19 PM

interesting

2 hours later…

3:55 PM

Correction: Markus Winand is not the author of the database comparision page (sql-workbench.eu/dbms_comparison.html) - the author is Thomas Kellerer, also author of the SQL Workbench/J tool.

From the site "SQL Workbench/J is a free, DBMS-independent, cross-platform SQL query tool. It is written in Java and should run on any operating system that provides a Java Runtime Environment.". Note: I have no relationship, either business or personal, with Thomas Kellerer - just giving credit where credit is due for an excellent (and up-to-date) page!

2 hours later…

6:20 PM

86

Q: An Update On Creative Commons Licensing

We’d like to provide you with an update to our transition to version 4.0 of the CC BY-SA license. We realize that this is something that you care deeply about, and that our response to your concerns is long overdue. We’d like to thank all of you for your patience while we worked on a plan forward...

discussion featured licensing

Please stand by while history is rewritten. We thank you for your patience.

6:31 PM

When you get in the elevator and you smell pizza

Pizza as in free pizza ;)

7:05 PM

Did you guys get your Real Housewives of New Jersy Emergency Preparedness kits? readyjudy.com

A friend of mine is flying one over.

Via China, Iran, and Italy.

hey, they would know what you need lol

Master Database

7:33 PM

-1

Q: AppArmour question - why was it closed?

This questions was recently closed: MySQL was stopped As you can see, the close notice says it's off-topic and too localized. Does that mean all questions where the problem turns out to be related to AppArmour, SELinux or other security systems should also be closed? I think security system co...

discussion closed-questions

oh hello

Feeling sick now. Not from the pizza. They refer to Mongo as database...

@ypercubeᵀᴹ They?

7:49 PM

I am in an event / presentation. Thus the free pizza comment above.

@ypercubeᵀᴹ Yes I wondered who "they" were.

The providers of free pizza and document stores incorrectly called databases

Some insurance broker company

Well I hope the pizza is good

pizza and beer, so all good

You should have led with the beer.

8:06 PM

But it is a database, according to us

14

Q: What is a "database"?

There was lot of discussion in this question: What database technologies do big search engines use? So much discussion that it made me confused. So... what is a database, anyway? Are only relational databases "databases"? Are object-oriented databases "databases"? Is any system that allows me to...

database-theory terminology

8:25 PM

Last speaker, we heard the wotd Postgres. All is happiness now ;)

9:07 PM

I heard it's built from triggers

@mustaccio let's not bring facts into this

« first day (3346 days earlier) ← previous day next day → last day (1520 days later) »

Transcript for

Mar '203

The Heap™ – Consultancy ©®

General on- and off-site discussion for dba.stackexchange.com....

frogs mysql nosql oracle postgresql sqlite sql-server

join this room
about this room

00:00

06:00

12:00

18:00

all times are UTC

site design / logo © 2024 Stack Exchange Inc; legal

mobile