[HN Gopher] PostgreSQL adds support for OUT parameters in Proced...
___________________________________________________________________
PostgreSQL adds support for OUT parameters in Procedures
Author : avi_vallarapu
Score : 80 points
Date : 2021-10-15 15:36 UTC (7 hours ago)
(HTM) web link (www.migops.com)
(TXT) w3m dump (www.migops.com)
| emaildanwilson wrote:
| but stored procedures are evil
| AprilArcus wrote:
| an articulate and well-argued position
| inopinatus wrote:
| they run in daemons
| avi_vallarapu wrote:
| No doubt in some aspects. Business logic may be great most of
| the times when it is out of the database. However, the theory
| cannot be applied to numerous legacy applications and also
| while migrating them all to PostgreSQL.
| masklinn wrote:
| You could already use RECORD to return multiple values from a
| proc, so this really is only useful for oracle compatibility.
| lopatin wrote:
| Also for the MySQL community. May companies have hundreds of
| stored procedures. Rewriting them to not use OUT params just to
| even try out Postgres can be a big deal. So I welcome this
| change.
| zz865 wrote:
| Looks like a lot of potential to build an API right in the
| database. Just wish the syntax was a bit more modern. Would be
| nice to have an easy way to just make it a rest server.
| mrweasel wrote:
| There is the PostgREST project that allows you to just have a
| REST API to your database.
| atom_arranger wrote:
| I've used Postgraphile to do this. GraphQL not REST, same idea
| though, autogenerated API from stored procedures, it's pretty
| neat.
|
| Pros: - Can query DB multiple times,
| conditionally, without making multiple trips to DB, since your
| code for a certain procedure is all in the DB. -
| Procedures are accessible using any DB client.
|
| Cons: - Version control of these procedures is
| not as nice as normal code. Graphile Starter has some tools for
| snapshotting the DB schema that help, but the DX is still not
| great. - Scaling your DB is more costly than scaling
| compute, so from a cost/scaling perspective this might not be
| the best idea.
| mikepurvis wrote:
| I'd be nervous about the testability/verifiability of it. I
| like treating the DB as infrastructure, and I get nervous
| when the infrastructure gets too smart.
|
| Maybe I'm just stuck in the olden days and haven't yet
| embraced the brave new world where no one can run a full
| local instance because it depends on queues and storage
| backends and whatever else supplied by a cloud vendor. But
| even in my little world, I feel like I experience this with
| overly-smart Jenkins pipelines that can't really be executed
| except in production or an expensive-to-maintain clone of
| production.
| zz865 wrote:
| I think most people have seen the app server crash and burn
| while the data is nice and safe in the DB. Seems sketchy to
| merge them. But some some usage it would be simpler.
| yobbo wrote:
| If multiple applications with limited functionality are
| interfacing with the db, then adding certain logic with
| procedures can be a good solution for consistency.
|
| Procedures can be triggered by inserts/updates/deletes. This
| means logic can be replayed. There might be a table
| "IncomingActions" that triggers a set of procedures. Procedures
| might sanitize values on updates, update various specific
| materialized views.
|
| Thinking about it generally, consider the db as storing system
| state, and a set of actions that updates the state. So the db
| is a sort of state machine, and the triggers/procedures define
| the reachable state space. For example, there might be FK
| constraints that forbid clients to insert into the table
| "Orders". Instead, clients could insert into "OrderRequests"
| which triggers a set of procedures that ensures the entry in
| "Orders" is legal. In this way, an ORM app could be allowed to
| create Orders without needing to call procedures, or knowing
| anything about what makes "Orders" legal. We also get a log in
| "OrderRequests".
|
| Error handling is ugly though.
| whofw wrote:
| After being thrown into a codebase written with Oracle PL/SQL
| procedures outputted directly into Coldfusion tags, this idea
| gives me PTSD.
| nicoburns wrote:
| Worth noting that Postgres supports many more languages other
| than PL/SQL, the plv8 extensions allows JavaScript to be used
| for example.
| RedShift1 wrote:
| Postgraphile turns your database into a GraphQL API:
| https://www.graphile.org/postgraphile/
| sigg3 wrote:
| It's so awesome even the testimonials have radical names:
| Chad F Sam L Max D
| geekpowa wrote:
| I originally tried this with a project I am working on, moving
| as much of the logic as possible into the DB. But abandoned the
| effort quickly in part because the logic I need to implement
| was simply too complex and was better expressed & validated in
| a client side language.
|
| plpgsql performance is a serious issue for anything non
| trivial.
|
| debugging is slow and challenging.
|
| General rules I apply with procs
|
| * try to keep procs to SQL, not plpgsql. More performant
| usually and sometimes pg will inline these. But sometimes I've
| found forcing it as plpgsql is good too, as it effectively
| 'fences' the query planner just like CTEs used to. As
| supportive as I am with pg's philosophy on querying hinting,
| sometimes you need to press your thumb on the scales a bit.
|
| * procs for very complex and subtle queries where performance
| is not such a concern but readabiltiy of client code or
| dependent queries is useful. Like a client side function,
| something whose inputs and outputs are easy to reason about and
| you can easily build client side regression tests to validate.
|
| * consider using plpgsql proc instead of recursive CTE for
| things where CTE recursion is needed. I've found for some types
| of jobs, plpgsql outperforms a recursive CTE. I think bc query
| planning for recursive CTES is very difficult.
| benji-york wrote:
| You might be interested in PostgREST (
| https://postgrest.org/en/v8.0/)
|
| > PostgREST is a standalone web server that turns your
| PostgreSQL database directly into a RESTful API. The structural
| constraints and permissions in the database determine the API
| endpoints and operations.
| ijidak wrote:
| Anyone aware of a quality tool like this for SQL Server?
|
| I've been looking for something like this for a long time?
| tthun wrote:
| I haven't used it but Hasura[0] supports MS SQL [0]
| https://hasura.io/docs/latest/graphql/core/databases/ms-
| sql-...
| xemoka wrote:
| PostgREST as others have mentioned is excellent. Check out
| https://supabase.io for their hosted/managed option with a few
| more features (and open source tools working in concert).
___________________________________________________________________
(page generated 2021-10-15 23:01 UTC)