10 ms·
Kind of unrelated and very high level thoughts, but I wish postgres had a scripting model a bit like redis. Where functions are cached under their sha1 hash and
by janpot 7y ago
Kind of unrelated and very high level thoughts, but I wish postgres had a scripting model a bit like redis. Where functions are cached under their sha1 hash and can be executed by specifying that hash. cached functions would then be evicted after not being run for a certain time.
From the application side I'd just always try executing the hash of the function and create it on the fly when that errors (because doesn't exist on the db side yet).
a model similar to this one would completely convert me to defining most of my app logic in plsql functions.
- matdehaast 7y agoWhat’s wrong with stored procedures?
- mjw1007 7y agoThey have to be managed like the database schema, which tends to involve a lot of ceremony. Coping with different function versions for different client versions can be inconvenient. In cases where you want to run functions on the server for performance (rather than for hiding some underlying structure), it's often more convenient to maintain them together with other client code. You can do things like creating functions in pg_temp once per connection, but more upstream support for this sort of setup would indeed be pleasant.
- manigandham 7y agoYou can use prepared statements: https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
- mjw1007 7y agoSometimes you can, but sometimes you want something that can be called as a function. And there's no PREPARE IF NOT EXISTS, so you either have to track yourself whether you've already done it or grub around in pg_prepared_statements.
- janpot 7y agonot powerful enough, I want programming capabilities. composing statements and branching
- megous 7y agoDO? https://www.postgresql.org/docs/10/sql-do.html https://www.postgresql.org/docs/10/sql-do.html
- cogman10 7y agoI DON'T want that. Maybe if there is a case where things are absolutely faster to do in the DB, but often times, you get better performance and scaling when you ship that stuff off to the application (it makes things easier to maintain as well). I'm currently dealing with a legacy system where some wing-nuts decided to push all the business logic into the DB. It is really REALLY hard to improve things like performance and scaling when that happens.
- janpot 7y agoThanks a lot, didn't know about temp functions. You're hitting the nail on the head with where I want to go with this. I wish postgres put more attention into this kind of interfacing layer.
- ako 7y agoYou can probably execute an anonymous plsql block, and postgres will detect for you if has already parsed that block. No need to do that hash thing yourself.
- zxexz 7y agoI wonder, could you describe your use-case a little more? I'm curious why you couldn't write a stored procedure, then call that from your application? If you need to see if your function exists, you can do something like this [0]. Also using pg_proc, you can get the DDL of your function e.g. [1] SELECT prosrc FROM pg_proc WHERE proname = 'function_name'; And you could then check for equality of the source (or its hash), if you are tied to your current application logic [0] https://stackoverflow.com/questions/24773603/how-to-find-if-a-function-exists-in-postgresql https://stackoverflow.com/questions/24773603/how-to-find-if-... [1] https://stackoverflow.com/questions/3524859/how-to-display-full-stored-procedure-code https://stackoverflow.com/questions/3524859/how-to-display-f...
- mjw1007 7y agoIf you do it that way, you also end up having to rename your function every time it changes, if it's possible that older clients are still running. And if you're tidy-minded you're going to spend effort building a system for removing old versions when they're not going to be needed any more. It's all doable, but it's fiddly. Explicit support for this sort of setup would be a good thing.
- gfody 7y agoof all the reasons a sproc may need to change I can't imagine one where you want to keep the old version around while also giving the new version the same name as the old
- setr 7y agoThe output is slightly different, but the semantic behavior remains the same? Databases and applications update independently, so versioning is required. If the database offers nothing for this, then an adhoc solution will generally be found (eg suffixing with _v1, _v2, etc), but that's not at all ideal
- gfody 7y agosame situation as with any interface, you either break backwards compatibility or you don't - this is not a problem for databases to solve
- manigandham 7y agoSTORED PROCEDURES: Available since PG11. Name them whatever you want, and call them with parameters. They can be written in multiple languages like PL/pgSQL which supports more constructs than standard SQL, and support defining multiple transactions internally. They are part of your schema and always available with support for security access. PREPARED STATEMENTS: Queries that are executed often. Setup on every new session but will be automatically compiled, cached for the session lifetime, and executed with just a minimal identifier. Depends on your client driver to handle seamlessly. More info: https://www.postgresql.org/docs/current/sql-prepare.html https://www.postgresql.org/docs/current/sql-prepare.html
- SigmundA 7y agoLooks like the plan caching is only per session which is a big bummer if you call the same statements over and over from multiple sessions. Plus you have manually deal with this which is weird coming from MSSQL where it just does it with a global cache based on statement text hash all the time. Brings me back to early 2000's when it was recommended to always use stored procedures because either MSSQL 7 or 2000 can't remember didn't cache ad-hoc statements only sprocs.
- deleted 7y ago[deleted]
- manigandham 7y agoPlan caching is different from prepared statements, but yes PG is not as advanced as other commercial databases in execution. Usually the database client drivers will handle this for you. If you're using .NET, the npgsql driver maintains a connection pool and creates prepared statements for each new session. If you have something like PGBouncer in the middle then it gets more complicated.
- SigmundA 7y agoThere is almost no point is using prepared statements in MSSQL anymore, not for a long time. The only benefit comes from not sending the prepared statement over the wire each time and not having to hash it to do a plan lookup. That benefit is so very minor compared to the time taken to parse and plan the statement that almost none uses it, for more details: https://dba.stackexchange.com/questions/129659/what-is-the-sense-and-benefit-of-using-sqlcommand-prepare/129721#129721 https://dba.stackexchange.com/questions/129659/what-is-the-s... In PG its seems to be the only choice and on top of that isn't even shared among connections, this honestly blows my mind and will have to remember if I move anything serious over to PG. I like PG a lot and it does a lot of thing MSSQL doesn't but this is a big missing performance piece.
- SigmundA 7y agoMS SQL Server hashes all incoming statements and looks them up in global shared cache, essentially doing what Redis does without having to deal with the hash directly. The cache will get evicted based on time with no use and memory pressure etc. I am actually kind of surprised to find that Postgresql doesn't do this also, wow, I thought pretty much every modern RDBMS did this.
- manigandham 7y agoMSSQL (and others like Oracle) have 100s of millions in research and development invested by now. PG is great for 80% of uses but it won't match the advanced performance scenarios.
- SigmundA 7y agoHashing incoming text statements and storing the plan for reuse seems like an obvious optimization that does not require billions in R&D, you just copy the well known behavior of the existing databases. Is there some complexity I am missing that would require huge R&D , like its too hard to share the plan and procedures across sessions or the hashing algo would have too much overhead?
- mjw1007 7y agoAIUI, Postgres has had transactional DDL from early on, while other DBMSes didn't. That means that different Postgres sessions can see different schemas. So I can easily believe that sharing plans across sessions would have been more difficult than for other systems, yes. There are presumably also concerns that the same text can have different meanings in different sessions because of different per-session configuration, like the search path. Nowadays, I think it's more that the gain isn't terribly large. Parsing SQL is pretty cheap. Normally with PostgreSQL you re-plan for each execution anyway. If you're going to ask it to reuse plans, the overhead of planning once per session is probably small enough that reducing it further doesn't gain much. And with modern core counts and scalability concerns, there's a lot to be said for per-process rather than global caches anyway.