Matthias van de Meent (@[email protected])

Looks like today is going to be the last day: I'm halting my activity here. You can find me in the fediverse at @mmeent_pg@fosstodon.org; this account is shutting down.

@fujii_masao Yeah, I think it'd be a quagmire: Would there be a separate statement start time for invocations of (potentially inlined) SQL functions? What about subselects? Etc.

But with nested statements calling other nested statements, it's unclear what timestamp should be returned or how the function should behave. So maybe it doesn't make sense to support this in core.

statement_timestamp() returns the start time of the top-level statement, so all nested statements get the same value. To get different ones between nested statements, would it make sense to support the function returning the start time of nested statement?

@samokhvalov Mastodon has a default of 500 characters (IIUC, it's configurable per instance). There are also various other implementations of ActivityPub, e.g. Sharkey with 3k chars (Calckey, Misskey) for microblogging, Pixelfed focused on image sharing... And those are just the popular ones.

@mmeent_pg trying bsky.app/profile/samokh…, can definitely cross-post easily, but I don't like character limits at all do Mastodon / Fediverse platforms have limits?

Indeed, ppl with middle school mentality are ruling this country now. I'm thinking to join the party - replacing the T logo on my tesla with something interesting. Plenty options on Amazon, like "F ELON" Though most are just simple stickers -- anyone saw something solid, to be a logo replacement?

@samokhvalov @PostgreSQL Not to forget, if you think you need the second, then the first is easier: - "TABLE mytable;" not "SELECT * FROM mytable;"

Some lazy @PostgreSQL engineer's tips: - "Postgres", not "PostgreSQL" - "select;" (or just ";"), not "SELECT 1;" - "count(*) filter (where ...)", not "COUNT(CASE WHEN ... THEN 1 ELSE 0 END)" - "create table t();" - "timestamptz" - "group by 1 order by 1"

@MarkCallaghanDB Then I think it's mostly down to a PG planner/executor/index AM limitation. For those interested, here are some related threads about this: pgvector - #issuecomment-1840786021" target="_blank" rel="nofollow noopener">github.com/pgvector/pgvec… psql-hackers - postgresql.org/message-id/fla…

@MarkCallaghanDB Personally I suspect it has to do with distance getting projected by the executor even after the index provided this exact data, but you may know whether there is more going on than just that.

@MarkCallaghanDB > * pgvector gets less QPS because it uses more CPU to compute the distance metric Do you know of a good reason why, or is this just empirical data?

@fujii_masao Could you explain what you mean by "the SQL firewall feature"? That's the first time I've heard of it, and AFAIK not part of ISO 9075 (at least, not clearly from its parts' descriptions).

I'm just curious—how many people actually use the SQL firewall feature in production to block unexpected queries? 🤔

@PierreZ @MarkCallaghanDB A good start would be the PostgreSQL Wiki page on MT: wiki.postgresql.org/wiki/Multithre… It contains most of the current state of the effort.

@mmeent_pg @MarkCallaghanDB Any pointers towards the work? 😁

@FranckPachot @MarkCallaghanDB I'm not familiar enough with MySQL's pluggable storage APIs to make comments to that - though it looks like several features in PostgreSQL seem to be implemented above the TableAM abstraction, whereas in MySQL they seem to be pushed down into Pluggable Storage (e.g. FKs)

@FranckPachot @MarkCallaghanDB True, though the HEAP part is optional, as is (technically) the blocks part. The only significant restrictions are - TID correllation implies tuple locality - Each indexed TID indicates a constant set of attribute values.

@MarkCallaghanDB But indeed, compressed storage isn't exactly great inside the core PostgreSQL feature set. Extensions usually cover important gaps; that's considered part of the design of the DBMS, not an oversight/missing feature per se. E.g. PostGIS, pgvector, etc.

@MarkCallaghanDB There also is (was?) OreoleDB, which did bring its own buffer pool and data storage, but it was a fairly involved fork, not an extension. It broke with most of Postgres' storage model, including its indexing.

@MarkCallaghanDB > why not LSM Complexities integrating a new table AM into PG, presumably. Adding a new table AM would require a lot of code, and the current page-based buffers don't work nice with the (presumably) variable but larger than page-sized chunks that are implied by the usage of LSMs.

@MarkCallaghanDB > why not MultiThreaded Historical reasons. There is work actively ongoing to get multithreading ready, but it'll take some time yet. Hopefully by PG19 we have the capability, though I think it'll take more time to turn it on by default.

So in line with previous years, I plan to travel to #pgconfeu 2025 by train, not by plane. Considering I'm already halfway up the Baltics, I might just go back through Estonia and Finland, taking the long way around the Baltic Sea.

🇳🇱>Riga is only 3 travel days by rail from NL (~48h door-to-door for me), with most of that time on the Warsaw-Vilnius-Riga section waiting for daily trains: with the Warsaw-Riga section by bus it's ~23h (but 10h on a bus🙁) @RailBaltica could reduce that to just 18h by 2030

@fujii_masao if the xact is still running it may still have open portals (i.e. queries, through cursors) that were started (opened) before the statement that created/updated/deleted the tuple, and may thus need to see the old tuples, even if it updated/deleted them in later statements.

@fujii_masao Also note that the same is still true when a tuple's xmin equals its xmax. One might think that's a trivial case of "nobody can see this", but that's only true if the xact has committed or rolled back, because (see next)

In Read Committed mode, long transactions without an XID don’t block vacuuming of dead tuples, but those with an XID do. It made me wonder—why should a long transaction with an XID prevent vacuuming of dead tuples it didn’t generate? Maybe I'm just confused...😵‍💫