The Heap™ – Consultancy ©®

hot2use

5:51 AM

Morning

Andriy M

Morning

Andy K

6:31 AM

Morning and evening

McNets

6:53 AM

Morning

Colin 't Hart

7:12 AM

Morning

McNets

7:50 AM

Is there a reason for a Postgres trigger function to return int[]?

Colin 't Hart

"A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger (for data change triggers) or event_trigger (for database event triggers)."

Source: postgresql.org/docs/current/static/plpgsql-trigger.html

So, no.

McNets

@Colin'tHart Thanks

Colin 't Hart

But not sure what you mean: do you mean the declared return type? Or the type of the value that is actually returned with a return statement?

"A trigger function must return either NULL or a record/row value having exactly the structure of the table the trigger was fired for."

So, an array of ints is still wrong.

Colin 't Hart

8:15 AM

Have you tried to read the pg_dump documentation? — dezso 3 mins ago

Another new close vote suggestion: RTFM.

dezso

exactly

hot2use

@Colin'tHart ...and goodbye to that.

dezso

@ypercubeᵀᴹ Maybe, I don't remember, and I'm too lazy to look at the documentation... — Akina 1 min ago

Tom V

@dezso LOL, the guy who was arguing explicit conversions weren't necessary because it's in the documentation somewhere is too lazy to look at the documentation today :D

Colin 't Hart

@TomV :-)

Tom V

8:33 AM

@ypercubeᵀᴹ I think you missed a closing paren

Colin 't Hart

@TomV IMHO the difference between good users and experts is that the experts read and digest the documentation.

Andriy M

@TomV He's tired from reading too much documentation yesterday.

2

ypercubeᵀᴹ

@TomV I deleted as my main point is mute

@dezso Wouldn't this work?

cur.execute("SELECT * FROM t WHERE col LIKE %s ;", ('%PE%',))

dezso

8:51 AM

@ypercubeᵀᴹ that's totally fine. Doubling is needed only if the query text itself (not counting placeholders) contains %

Andriy M

What language is that? Does it have better ways of parametrisation than using % placeholders? That one doesn't look safe.

I admit, though, that a one-liner like cur.execute('...', [params]) is very enticing

ypercubeᵀᴹ

@AndriyM Pyhton

Andriy M

@ypercubeᵀᴹ Oh, that one. Never heard of it. The name looks a bit like Python. Is it similar? :P

dezso

@AndriyM Typocube™

Andriy M

Typocal

hot2use

9:05 AM

ypical

3

dezso

@AndriyM how is it not safe? Like can be confusing? You can name them, if you want like %(this_here)s and then use a dict with that key as the second parameter. And then there are some extras for doing more complicated stuff (partially in a more² complicated way)

Andriy M

@dezso Not safe as in prone to SQL injection

dezso

@AndriyM it's not (not safe): initd.org/psycopg/docs/usage.html#sql-injection

Andriy M

If used only internally, then not a problem I guess, provided the source is verified to have safe data

dezso

cur.execute('SELECT %s', ['1; SELECT 2--'])

cur.fetchall()
==> [('1; SELECT 2--',)]

ypercubeᵀᴹ

9:14 AM

@AndriyM it is safe.

dezso

(the above example may not be a good proof, but I have to drop users from databases thanks GDPR right now)

Andriy M

Oh, so it adds quotation marks as necessary? That's nice

ypercubeᵀᴹ

@dezso I thought the same thing - about removing CASE - bu beware if the nulls!

@AndriyM yes, depending on the type of the value. It does different things for string, integer, boolean, list, ...

Andriy M

Very versatile, this %s thing

@ypercubeᵀᴹ Good point. Would've removed my comment but the OP appears to be already trying that.

ypercubeᵀᴹ

9:29 AM

@AndriyM I could be wrong about the arrays (in input). But it (psycopg2) does convert them (arrays to lists) in output.

dezso

9:49 AM

@ypercubeᵀᴹ your answer might be also important once fixed

I think something along the pattern of INSERT ... SELECT FROM (VALUES ...) v WHERE v.code LIKE ...

hot2use

@sp_BlitzErik Just wanted to post the same link.

@sp_BlitzErik Up early?

Colin 't Hart

-1

A: How can I reset a mysql table auto-increment to 1 in phpMyAdmin?

First delete 'id' column, then create 'id' column again. So easy.

Bwahahaha

sp_BlitzErik

@hot2use hopefully only temporarily! Back later, hahaha.

ypercubeᵀᴹ

10:15 AM

@dezso I think the issue is that code needs to be replaced with a parameter, too, and row['code']

dezso

@ypercubeᵀᴹ that's also an option

ypercubeᵀᴹ

@dezso I've edited your answer. Perhaps using named parameters would be a clearer.

dezso

10:32 AM

@ypercubeᵀᴹ much clearer

ConcernedOfTunbridgeWells

11:49 AM

@gbn Swiss supermarket giant Coop will sell products containing insects . . .

Pretty sure I've bought plenty of food with insects in it before. They might not have been the core product though.

Colin 't Hart

12:29 PM

Consumers have gotten pretty used to buying products with bugs :-)

James

1:11 PM

LOL! "Coop's marketing analysts are reportedly targeting windows users. A source close to the director allegedly said 'Those idiots already paid for a thousand bugs mashed together - that's why we decided to call our product hamburgers 10!'"

ohh, that's too long to be funy. I should have just written "The bug-patties are marketed as "hamburgers 10"...

hot2use

Well used up all my Close Votes. I think I'll call it a day.

Tom V

@James That could be explained at greater length

James

lollll

Colin 't Hart

Anyone here have any experience with busy email servers? Our server can send out about 4 emails per second -- trying to get a feel for whether that is good or bad performance

James

pretty sure it's not good. mine does about 4,000

but not pretty ones, just text.

Kinda got me in trouble once, lol

my guess would be that it's hardware dependent

gbn

1:28 PM

@Colin'tHart that seems quite limited, maybe deliberately

Forrest

@James Heh, I remember the horror of my first infinite loop email alert

James

lol! I forgot a where clause and sent out an email for every patient that has ever come back to the ER in 72 hours of their last visit.

hot2use

We receive 600'000 mails per day. We send around 200'000 mails per day (138 mails / minute = 2.3 mails / second)

But if you spread 200'000 mails to working hours only, then that give you approx. 7 mails per second

I would recommend releasing the hand-brake.

@billinkc Something I've always wanted to ask: Was your avatar picture taken with a logitech camera?

@billinkc Hi

Colin 't Hart

It's not just a postfix or sendmail MTA, but a whole Zimbra server which includes spam check etc

James

hmm, if it's spam checking out-going mail, that might do it.

Colin 't Hart

1:38 PM

@James Yeah, it is -- default install seems to do that

Tom V

@hot2use It's cropped from a picture @Lamak made a few years ago

hot2use

@TomV thanks

Tom V

@hot2use I'm trying to find it

Got to run, someone will find the picture of bill being punched in the face by homer

billinkc

@TomV Nope. It's from a MacBook Pro running Photobooth. Unsure of the pinhole camera manufacturer

Tom V

@billinkc I was referring to the photoshopped picture where you were punched in the face, but I ruined the joke being unable to find that picture

billinkc

1:48 PM

LookMark would never be mean to me like that

Philᵀᴹ

Amused by the email spam talk

I once sent myself thousands of SMSes. Wasn't fun hitting the phone SMS limit, deleting a few, then it filling up again. Think i got someone to delete them off the SMSC in the end

Colin 't Hart

2:01 PM

@Philᵀᴹ :-)

James

i probably would have just buried the phone outside.

dezso

May 16 at 9:40, by dezso

@George.Palacios did you notice the small arrow on the right of each message when hovering over with the mouse? It links the answers neatly to posts.

Max Vernon

Good for you, @PaulWhite !

from this

presumably, you're not actually retired; the post is?!

great answer, @sp_BlitzErik, by the way!

sp_BlitzErik

2:34 PM

just a joke~

Max Vernon

ahhh. well played then. Thanks for JEAGLing.

Philᵀᴹ

3:05 PM

The review queue needs some love if anyone is bored

McNets

0

A: How to exclude the Minimum and Maximum value from Between Condition in sql server 2008 R2?

From Adam Bertand's "Bad Habits" series: What do BETWEEN and the devil have in common? BETWEEN does an inclusive search with the bounds; that is, it's greater than or equal to the start and less than or equal to the end. Your query is the equivalent of select * from temp where a >= 2 and a <= 6 ...

Adam!!

Max Vernon

and four answers! I VtC'd it, but probably way too late.

Colin 't Hart

I liked this answer:

1

A: How to exclude the Minimum and Maximum value from Between Condition in sql server 2008 R2?

You could toss in and a not in ('2','6') or and a not in ((select min(a) from temp),(select max(a) from temp)) ..but only if you really hate your SAN administrator.

dezso

@McNets where is Adam when you need him

Max Vernon

@Colin'tHart because "SAN"?

dezso

3:19 PM

it's beer o'clock, BTW. I opted for an alcohol-free IPA because curiosity. It's surprisingly IPA-like.

Max Vernon

@dezso name?

Colin 't Hart

3:38 PM

@MaxVernon Yeah

MDCCL

Haven't used SSMS 2017 yet, so is the content of the following question true?

0

A: SQL Server 2012 is putting [brackets] around Table and Column Names

If you are running SSMS 2017 at least v17.6, there is now an option to turn this off. In the options dialog, go to "SQL Server Object Explorer" and then to "Commands". Under the "Drag/Drop" heading is a new option "Surround object names with brackets when dragged". By default, this is set to T...

Philᵀᴹ

@dezso The Brewdog one is nice, Nanny State

The best alcohol free beer I've tried so far is Erdinger Alkoholfrei. It's lovely. (I've given up drinking completely, so have been testing them)

ypercubeᵀᴹ

@MDCCL the question or the 2 recent nswers?

MDCCL

@ypercubeᵀᴹ The answer is what I meant to write; is it accurate?

ypercubeᵀᴹ

@MDCCL seems like it. Two recent answers claiming the same thing. I haven't used SSMS for a while

MDCCL

3:50 PM

There's this page from an Azure feedback forum that appears to confirm it.

dezso

@MaxVernon Dolden NULL (really!) from the deservedly famous Riedenburger Brauhaus

3

@Philᵀᴹ so no pub job anymore

@Philᵀᴹ I drank it once, after a bicycle race. It was very good, but possibly anything would have been. Here (in Germany) there are several better sorts.

ypercubeᵀᴹ

@dezso awesome name. The beer for DBAs ;)

Max Vernon

@ypercubeᵀᴹ no doubt!

Evan Carroll

4:28 PM

That's like the credit scene with game over for this game. There are no more dragons to slay.

Max Vernon

5:12 PM

batten down the hatches; there's a 25K Carroll on the loose! Congrats, Evan!

Evan Carroll

In other news, that's a "milestone" on the site and I have no idea why

Milestone "established user", "gifted user", "access to site analytics"

Sesame Street: One of These Things

►

Tom V

When I first saw the q I was waiting for Solomon to answer

1

A: Another one with collation issues

The problem is that the old SQL Server Collations (i.e. those with names starting with SQL_) and the first two versions of the Windows Collations (the 80 series which came with SQL Server 2000 and do not have a version number in the name, and the 90 series which came with SQL Server 2005) are mis...

Evan Carroll

6:31 PM

theverge.com/2018/5/25/17393766/… awesome!

jcolebrand

6:51 PM

Blah, anyone have some AD expertise that can help me troubleshoot a thing?

0

Q: Select User or Group default location

When searching for users there is a locations sub-dialog that lists the typical local machine, the domain (in my case corp) with the sublocations, and etc. Example this image from petri.com: What determines the default location in this dialog? Even tho my machine is domain joined, it is default...