The Heap™ – Consultancy ©®

1:35 AM

boom

0

A: vertica copy unix epoch into TIMESTAMPTZ

You would need to explicitly define the column list, use a filler and then derive it to transform the data type on load: CREATE TABLE public.test ( datetime TIMESTAMPTZ NOT NULL ); COPY public.test (unix_timestamp FILLER VARCHAR(15), datetime AS TO_TIMESTAMP(unix_timestamp))...

Andriy M

6:21 AM

The question has been updated but is less clear to me now than before.

Regarding your update, I don't really understand the actual problem you are facing. Is this about your table already having ranges that overlap and you wanting to detect and resolve them? — Andriy M 59 secs ago

Someone have any idea what it could be?

ypercube

8:19 AM

@AndriyM They probably have not understood how that answer works.

Andriy M

@ypercube That's what I now suspect too.

ypercube

@PaulWhite Nice. But why did @swasheck delete his answer? Seems to be that it's a good suggestion.

ypercube

8:37 AM

Tempted to answer that it's just not possible to get Mongo to be faster than Postgres. I'm sure I'll get a gazillion upvotes (for a rather troll answer.) — ypercube 1 min ago

Andriy M

8:51 AM

@ypercube Hmm, it appears the OP has created another account for some reason. They might be unable to reply to the comments.

Jack Douglas

9:03 AM

18 hours ago, by Paul White

Computed columns that reference functions that access data should be illegal. Shot at the border.

18 hours ago, by Paul White

@swasheck It's up to you. Part of me would hate to see your creativity destroyed. But another part of me would like to see data-access functions in computed columns destroyed. So I'm conflicted :)

Andriy M

18 hours ago, by swasheck

gone. besides, the actual answer is @PaulWhite 's which is why i was asking about the actual delete statement. in the end, we can simply blame it on entity framework

ypercube

thnx.

ypercube

9:35 AM

No one tested or spotted the error in this code:

2

A: How do I place leading zeros for numbers less than 10 without affecting those 10+?

UPDATE MyTable SET MyColumn = '0' + Mycolumn WHERE MyColumn < '10' AND MyColumn NOT LIKE '0%';

dezso

Tempted to answer that it's just not possible to get Mongo to be faster than Postgres. I'm sure I'll get a gazillion upvotes (for a rather troll answer.) — ypercube 1 hour ago

nice job :D

ypercube

What is this guy talking about? I feel I just don't understand something.

Unfortunately it needs to be almost live. Also aggreated results would not be efficient because those logs are not for one user. They are for different user. However I do generate archived reports for reports a user has requested. But they are only valid for 30 minutes. — yoshi 27 mins ago

dezso

10:03 AM

@ypercube does not make much sense

ConcernedOfTunbridgeWells

10:16 AM

I just got asked to suggest why Azure might not be a great platform for a data warehouse.

- No support for master data services
- No system data dictionary (is this for real - not 100% sure from my google-fu)
- No sharepoint integration, PowerView etc.
- No table partitioning
- No standard backup/restore
- Requires clustered indexes (not necessarily desirable for fact tables)
- Does not support the profiler

That's what a bit of google-fu turns up. Reality check - is that correct?

Simon Righarts

I'm pretty sure all of those were true in the past (I'm assuming you mean SQL Azure here, not running your own SQL instance on an Azure VM?)

I just don't know if they're still true today

Simon Righarts

10:47 AM

Now for my daft SQL server question - does anyone know how much transaction log a secondary node in an AG will hold?

ypercube

11:11 AM

I like this phrase "bug compatibility". So, SQLite tries to be compatible with MySQL's bugs, nice! — ypercube 18 secs ago

4

Jack Douglas

11:23 AM

@ConcernedOfTunbridgeWells They did mean SQL Azure not just Azure, right?

@ypercube really good catch

@SimonRigharts just noticed you asked that already :)

user3732505

1:08 PM

hi

JNK

hi

Jack Douglas

@user3732505 are you Karthick?

JNK

@JackDouglas looks like he is based on linked accounts

Paul White

1:26 PM

dun dun dun

Yasir Arsanukaev

0

A: Whether Logfile Shrinking is good or bad one for Performance in SQL?

In SQL Server, log file is used to support transactions, guarantee transaction isolation, and database consistency. If you remember the structure of the log file, you know that it may grow in the following cases: When there are huge DML (UPDATE, DELETE etc.) activity within database done by one...

VtC

swasheck

@SimonRigharts not sure i understand the question

Paul White

1:43 PM

That would make a great epitaph

@YasirArsanukaev Voted to move. But, how does the log file support transaction isolation?

Yasir Arsanukaev

@PaulWhite Thanks for moving (closing).

Yep, I may be wrong here. Let me think. I might have confused the isolation mechanics of SQL Server with those of Oracle which uses undo to support isolation.

Paul White

@YasirArsanukaev If you're thinking of MVCC, SQL Server uses a different mechanism (the version store). In any case, I think the answer would be better without that statement.

Yasir Arsanukaev

Then I remembered that SQL Server combines undo records and online redo log files (as these are called in Oracle) into one file called log file.

Paul White

I don't have much of a clue about Oracle I'm afraid.

Yasir Arsanukaev

Yeah, probably you are right. I just thinking and trying to remember if I remembered everything else correctly and whether it's better to delete than to edit the answer :-D

Paul White

1:51 PM

@YasirArsanukaev Seems fine otherwise.

Yasir Arsanukaev

@PaulWhite I think I beginning to remember. It stores undo records (well, versions if we talk about SQL Server) in a separate database!

Right?

Paul White

@YasirArsanukaev SQL Server stores row versions in the version store in tempdb.

Yasir Arsanukaev

Yep

Paul White

That doesn't affect logging much though. Undo and redo are still generated as normal in the transaction log even when row versioning is in use. It has to do this to ensure recoverability of the database.

i.e. Row versions are in-memory only, not persisted.

Yasir Arsanukaev

And those are started to being produced and stored there (afair) when we're enabling RCSI.

Paul White

1:54 PM

Or SI, yes.

Though things like AFTER triggers and MARS use row versions regardless of RCSI/SI, and there is a separate version store for online index operations.

Yasir Arsanukaev

@PaulWhite "Row versions are in-memory only, not persisted" When you said "in memory", you assumed they are temporary (though they go to log file), right?

So that transaction can be rolled back and they are forgotten right afterwards.

Paul White

Row versions and redo/undo log records are quite separate concepts. Row versions have nothing to do with rollbacks in SQL Server. Also, row versions are not logged (they're temporary, after all).

ConcernedOfTunbridgeWells

@JackDouglas Yes

Yasir Arsanukaev

Well, I just talking only about log file and ongoing transaction not in RCSI mode (Not a native English speaker :)

I mean undo records of the transaction are not persistent in the sense they can be forgotten if the transaction is rolled back.

Paul White

Depends what you mean by "forgotten". The log record containing the undo information is persistent, and not deleted even if the txn rolls back.

ypercube

2:03 PM

@mods, I don't know how to flag this. OP asks a question at the DBA site. After an hour (I was just about to comment), they delete they question and ask it (identical) in the SO site. They get an answer there, and we have the discussion (see comments:)

1

Q: How to construct a set-based query for a subset?

I have a DB of the following structure (picture shows simplified version) How should I construct a query to get only those recipees, that can be cooked, i.e. for each ingredient in a receipe requiredQuaintity > availableQuantity? I tried this: SELECT r.Name FROM Receipe r JOIN RecipeIn...

Yasir Arsanukaev

@PaulWhite Yeaah, I know -- until you commit a txn and backup (truncate) the log file, or commit the txn and SQL Server switches over to the beginning of the log file and overwrites over VLFs, afair.

ypercube

And then, they proceed to ask again (or undelete probably) at DBA: dba.stackexchange.com/questions/72268/…

Now we have 2 questions, in 2 sites, with similar answers but by different users.

Paul White

Messy.

Zane

engadget.com/2014/07/23/marvel-unlimited-comics-for-99-cents

I wonder if I can read all 640 issues that I don't have of Amazing Spider-Man Volume 2 in a month?

Yasir Arsanukaev

@PaulWhite "Row versions are in-memory only, not persisted" I think I figured what you meant: the versions not go into database backup and are being forgot with time (can't remember the whole mechanics at the moment), so only committed versions are those that persist (in log -- and thus in backups).

@PaulWhite Also, thanks for editing the answer!

Max Vernon

2:16 PM

is there a way to force a specific type of code formatting on SO?

I want to mark a piece of code as VB.Net

Yasir Arsanukaev

@MaxVernon Yep, gimme a moment.

Max Vernon

@YasirArsanukaev thanks!

Paul White

115

Q: What is syntax highlighting and how does it work?

I noticed that sometimes my code gets highlighted in different colors when rendered. What is syntax highlighting? How does it work? What if my code isn't highlighted correctly? How do I report a bug or request a new language? What languages are currently available on Stack Exchange? Return to...

support faq syntax-highlighting

ypercube

Yasir Arsanukaev

Or

#lang racket
(define fortytwo 42)
(define (det a b c)
  (- (* b b) (* 4 a c)))
(provide (fortytwo det))

2

Max Vernon

2:26 PM

@YasirArsanukaev gorgeous!

42 42 42 42 42 42 42 42

thanks everyone!

Max Vernon

2:40 PM

for the benefit of developers present, I figured out how to connect to a remote SQL Server in an untrusted domain using the Windows API.

0

A: How can I impersonate a user from a different, untrusted domain, the way Windows 7 does it?

For the benefit of future visitors, I'm posting some VB.Net code that allows a process to use NETONLY impersonation to authenticate against a remote server that resides in an untrusted domain: Option Explicit On Option Infer Off Imports System Imports System.Runtime.InteropServices ' DllImpor...

Yasir Arsanukaev

:-D

(Continuing the discussion about log) For some reason Oracle's online redo looks more elegant to me than SQL Servers redo log.

The latter tends to grow and occupy all your disk.

Kermit

@ypercube source?

Yasir Arsanukaev

Oracle on the other hand just copies the current log file when it's full of redo (which can be as big as 54 mebibites) to archive log and switches over to another log (there can be two or more log files) always keeping redo log at the same size (fixed).

ypercube

@Kermit It's in the answer I commented.

Kermit

@ypercube yup. nevermind... thanks

JohnP

2:51 PM

booyah, beeyotches. Ready for another day of crushing dreams?

Yasir Arsanukaev

@JohnP Do you like databases? :D

Jack Douglas

@YasirArsanukaev presumably it shrinks after a backup?

ConcernedOfTunbridgeWells

@PaulWhite I felt much the same way about learning SSAS and MDX. At the time (circa 2001) there was almost nothing written about it.

JohnP

@YasirArsanukaev - Yeah, I do. Don't know as much as I want to about them, but I'm learning.

Yasir Arsanukaev

@JackDouglas Never shrinks. You just add the redo log file during the database creation or afterwards specifying its size and it always stays at the same size. You may add, drop online redo log files specifying different sizes though. But they never grow or shrink neither manually nor automatically.

Jack Douglas

2:54 PM

@YasirArsanukaev I mean on SQL Server?

Yasir Arsanukaev

@JohnP Just never seen you here. And (prolly) no answers on DBA.se :-]

Jack Douglas

if it shrinks after a backup it's analogous to the online and archivelog files all concatenated

@JohnP I've learned a lot just hanging around in this room :)

Yasir Arsanukaev

@JackDouglas Well, sort of. But what grows is the disk(s) that the full online log file archived (copied online log is called archive log in Oracle) to, not the disk where online log is located.

@JackDouglas @JackDouglas Do you learn Oracle, MySQL, SQLite also? I just noticed (based on your profile) you're predominantly an SQL Server guy (-:

Jack Douglas

@YasirArsanukaev yup that's true, but it makes some sense to keep them together anyway as they are both needed for recovery

JohnP

@YasirArsanukaev - two answers, no questions yet. I'm a developer with a (long ago) background in basic DBA stuff, now I'm revisiting and learning.

Yasir Arsanukaev

3:00 PM

@JohnP I see. Welcome! ;-)

Jack Douglas

@YasirArsanukaev Ha ha, no, I'm an Oracle/postgres guy with a bit of SQL Server and MySQL

Yasir Arsanukaev

@JackDouglas Oops, sorry, prolly confused you with @PaulWhite :-D

6

Then what I'm talking about online log is not interesting to you (unless you're into discussions!)

Jack Douglas

@YasirArsanukaev Wow, what a compliment, I will pin that forever :)

3

and I love discussing the differences between Oracle and SQL Server, they are fascinating, not least the old "index reorg/defrag" debate.

but pg is where my heart is

swasheck

@JackDouglas what came of your "project?"

Yasir Arsanukaev

@JackDouglas Yeah. Correct. But something makes me think that planning your LOG_ARCHIVE_DEST_n (or Fast Recovery Area) destination sizes or making more room in them afterwards (when the workload grows) is easier than shrinking log file and adapting your backup strategy in SQL Server. Don't you have the same feeling?

@JackDouglas never heard of that debate yet :-)

Jack Douglas

3:14 PM

@swasheck I abandoned it after getting a nice (unrelated) email from this nice chap

ypercube

Hm. I rejected this edit but I should have marked the suggestion as helpful, nonetheless!: dba.stackexchange.com/review/suggested-edits/28764

The question starts with "I am using MySql server", while it's obviously about SQL-Server.

swasheck

@JackDouglas hmmmm. trying hard not to read too much into that

Jack Douglas

7

A: When should I rebuild indexes?

When should I rebuild the indexes in my relational database (e.g. SQL Server)? You should rebuild indexes when they become highly fragmented by special events. For example, you perform a large, bulk load of data into an indexed table. Is there a case for rebuilding indexes on a regular b...

Yasir Arsanukaev

@ypercube I either often confuse them when discussing with someone :-/

ypercube

@YasirArsanukaev But you are an Oracle guy. They use the product.

swasheck

3:19 PM

@JackDouglas what's the oracle perspective again?

Jack Douglas

@swasheck don't rebuild

swasheck

right. but why?

Jack Douglas

@YasirArsanukaev orafaq.com/node/2903

@swasheck Nick sums it up well in the answer I linked to, and there are exceptions

Yasir Arsanukaev

@swasheck oh

Jack Douglas

but basically, rebuilding is robbing Peter to pay Paul

usually better to have a steady state than regular defrag+extra subsequent logging

Paul White

3:22 PM

That's fine. Peter has far too much cash.

@ypercube I don't think it's possible to reject an edit and mark it as helpful.

Yasir Arsanukaev

@JackDouglas Afair this chap (rleishman) had great article on index structure and comprehension.

ypercube

@PaulWhite I should have improved it then. But you have done that now, so all is fine.

Jack Douglas

@swasheck don't read too much into it :) btw I'm stepping down as mod over on BH.SE, I'm just working on my meta post.

Paul White

@ypercube Oh I see what you mean. Yes improving and marking as helpful would have been better (I rejected, then edited). Wasn't thinking.

Kermit

4:15 PM

boom

1

A: Vertica partition by week on datetime:TIMESTAMPTZ

If you are confident that there will never be a time where you have data from the same week over more than one year, you just need to add the AT TIME ZONE part to the TIMESTAMPTZ type: CREATE TABLE public.test ( date TIMESTAMPTZ NOT NULL ) PARTITION BY WEEK(date AT TIME ZONE 'UTC'); Oth...

Zane

Undo is probably the best feature ever.

@Kermit TimeStamptZ? Who knew Vertica was so hood?

Kermit

west side represent

Transcript for

The Heap™ – Consultancy ©®