Everyone fill out the StackOverflow developer survey, stackoverflow.com/survey/site-satisfaction/redirect/… Tell them that their site is unwelcoming and suggest term limits for moderators.

A chairde - Morning all!

@EvanCarroll Done! Are you itching to practice your new found PostgreSQL/C skills? :-) Also, do you have any ideas on this one - possibly some sort of recursive regex? Or in PL/pgSQL? C code for sure. The question intrigues me.

Morning

@EvanCarroll That is the system.

Morning

@JohnK.N. Evan was one of the founding members of Trash

Morning

@Vérace-getVACCINATEDNOW something like this: dbfiddle.uk/…

Not near a postgres box at at the moment to check. Doesn't work in dbfiddle as we can't create an op class

@PaulWhite Did I do anything different?

@JohnK.N. No I was kidding. Evan talks a lot of trash.

I moved a message to Trash and then again to another trash room.

Yep all fine.

And the invite is because I moved his message to another room?

@JohnK.N. That happens whenever a message is moved to a room the author isn't currently in

There's no special handling for 'trash' rooms at all

→ 1 message moved to Trash

@JohnK.N. You probably got an invite for that ^^^

If you didn't it could be because you're a room owner there

@PaulWhite I did :-)

Thanks for the clarification.

@PaulWhite watching the game?

If someone is interested on .net Conf

good morning

has anyone seen my motivation? I can't find it.

@bbaird maybe the dog took it away

(Next performance review) "My dog ate my motivation, I swear!"

Sadly our galgo passed last summer

@bbaird oh

@bbaird My motivation resisted the urge to join me at the office this morning.

@PaulWhite Evan talking is trash to none, and treasure to all.

you can't double bounty a question anymore?

what kind of dogshit is that

why would they remove the ability for people to incentivize a question.

I can't re-bounty this for 250 exp dba.stackexchange.com/questions/160354/…

@EvanCarroll - are you planning on giving me another 250 points? That's mighty generous of you! :-) Question - have your actually used Vlad Arkhipov's temporal extension? I upvoted your answer on it here. I upvoted it - well, if you're going to be showering me with points, I might as well return the favour! :-) Seriously though, I was doing a bit of work where they may come in handy! Was looking for your input!

Does it do both System time and app time based periods? Did you ever look at this? Seems interesting?

There "here" upvoted above is here!

I'll try and benchmark your C extension over the weekend - maybe Tue. next by the latest...

So much work to force an interval approach to a point in time problem

don't know that I ever used the temporal table thing myself.

I probably wouldn't trust anything that specialized

@bbaird AS OF queries are not based on intervals? I mean, if I want to know how many million Ryanair shares my portfolio contained, say, at the beginning of November, I'd have to SELECT * FROM portfolio WHERE company = 'Ryanair' AS OF '2021/11/01 00:00:00'::TIMESTAMP - otherwise I have to do LIMIT 1 and ORDER BYs and whatnot...

@EvanCarroll It's useful in some/many cases - auditing, scheduling... lots of use cases.

@Vérace-getVACCINATEDNOW No need to store intervals to do that, and if you need to do that query there's a million ways without limit 1/order by. I'm not going to freak out over 10 extra lines of code here and there.

(million = two)

And doing that work in Postgres is just asking for trouble, especially if you need to do any prior value/next value stuff.

propose a solution rather than "asking for trouble"

I mean, I don't mind universal visibility and predicates. I get some people would rather just set the DB to a specific state at a time. But why would either of these approaches be worse in PostgreSQL than any other RDBMS?

@Vérace-getVACCINATEDNOW What distro are you using?

I compiled pg 13.4 from source!

Grats though you don't have to compile pg from source to use an extension or to compile an extension. I don't typically compile Pg from source unless I am hacking on it

If you're compiling Pg from source (which I wouldn't do here) you need to make sure pg_config is in your path.

I have many different versions running - I like to mess around with zhead and TimescaleDB and Citusdata...

if you don't know where pg_config is at then install locate/mlocate and do updatedb and run updatedb followed by locate pg_config | grep bin

but export PG_EXPORT=/path/to/binaries should do the trick? That's what the git page says!

That means you need to make sure PG_CONFIG points to the actually binary not the directory with the binaries.

but it's PG_CONFIG not PG_EXPORT.

But export adds it to your env variables!

I meant PG_CONFIG!

:-)

export PG_CONFIG=... not export PG_EXPORT

ah, ok well if you export PG_CONFIG and set to a real path to a pg_config that should work.

export PATH=$PG_CONFIG:$PATH worked!

that's because you're setting you're PG_CONFIG to a directory then and not to an executable file.

Nope - PG_CONFIG is always a dir, not a file - at least that's been the way with the stuff I've been installing!

IIRC!

Nope - just checked in new shell - if PG_CONFIG is set to the exe (pg_config) in the bin dir, it doesn't work!

Is there any other mainstream RDBMS that has a range type like PostgreSQL?

@Vérace-getVACCINATEDNOW No. ;)

No to my question about range types?

Or am I wrong about PG_CONFIG...

You're wrong about PG_CONFIG.

@Vérace-getVACCINATEDNOW Yes, but you don't need a range type.

OK - I'll do a reinstall over the weekend... don't have time at the moment - I'm being nagged to come to the table! :-( - you're obviously way more of an expert in this area than I am - I"m not above schoolboy errors!

❯ make PG_CONFIG=/usr/bin
make: /usr/bin: Permission denied
make: *** No targets.  Stop.
❯ make PG_CONFIG=/usr/bin/pg_config
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -g -O2 -fstack-protector-strong -Wformat -Werror=format-security -fno-omit-frame-pointer -Wno-declaration-after-statement -fPIC -I. -I./ -I/usr/include/postgresql/13/server -I/usr/include/po…

I think range types are the biz!

See the paste above. ;)

I saw this - /usr/bin/clang-11...

@Vérace-getVACCINATEDNOW clang is required for JIT

@Vérace-getVACCINATEDNOW Certainly they're useful for things like this because the very act of temporal tables requires a point in time when a row comes into existance, and a point in time when the row is no longer visible. And storing that as two timestamps is rather stupid because it gets you nothing and is less efficient to index and query.

So I'm also kind of confused at @bbaird's reluctance here.

@EvanCarroll So, you store the "birth" time of the record (appearance of 1M Ryanair shares in my portfolio) and set its unknown death to ∞ (or 2038 for MariaDB) when that number goes up to 2M, I store the "death" time of that 1st record where ∞ was and store the birth date of the 2M share record and set its death to ∞ - and then an AS OF query just has to see which record pertained at TIMESTAMP X... I don't see why this is an issue? Is there a better way of doing this using "traditional" SQL?

LAG & LEAD? Not so traditional, but one way of doing it?

Open and closed intervals also help [] vs. ()!

@EvanCarroll Ooops - not on my game today! Just realised that you are in favour of range types!

Sure the real boon there is that get the GIST operator class postgresql.org/docs/current/…

which means you can do all that stuff in a single index scan.

Got any interesting articles/references/URLs about the use of GiST with range types?

I'll search tomorrow...

@Vérace-getVACCINATEDNOW Look at this page postgresql.org/docs/current/… under multirange_ops to get the list of ops supported on the index to find out what they do use postgresql.org/docs/current/functions-range.html

@AJ No it's too tense to enjoy and started at 3am my time anyway

my favorite trash talker!

sup Paul.