[HN Gopher] Prolog language for PostgreSQL proof of concept
___________________________________________________________________
Prolog language for PostgreSQL proof of concept
Author : triska
Score : 171 points
Date : 2024-03-30 09:36 UTC (13 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| nextaccountic wrote:
| So, I expected this to actually query the DB using prolog (using
| the correspondence between relational algebra and logic
| programming).
|
| I mean, can those prolog stored procedures use the db as a source
| of facts for prolog, or otherwise write queries?
| fulafel wrote:
| It looks like it's 1 hour old, and seems to me that embedding
| the Prolog would be a necessary first step.
| segmondy wrote:
| you can do that using ODBC
| ainar-g wrote:
| On a related note, have there ever been extensions for
| PosgreSQL/SQLite/any other FOSS DBMS that would allow using
| Tutorial D[1] (the Third-Manifesto one, not to be confused with
| Walter Bright's D language) to define and query data? In
| particular, I feel like PostgreSQL's CREATE TYPE[2] feature would
| allow for easier bridging between SQL and Tutorial D.
|
| Unfortunately, searches for "PostgreSQL Tutorial D" don't issue
| useful results for obvious reasons.
|
| [1]: https://www.dcs.warwick.ac.uk/~hugh/TTM/index.html
|
| [2]: https://www.postgresql.org/docs/current/sql-createtype.html
| justinclift wrote:
| Maybe ask on a suitable sounding PostgreSQL mailing list?
|
| * https://www.postgresql.org/list/
|
| The catch-all "general" one is probably good enough if nothing
| else seems like a closer match. :)
| cmrdporcupine wrote:
| You might enjoy
| https://www.cidrdb.org/cidr2024/papers/p48-neumann.pdf and
| https://github.com/viktorleis/saneqlcpp
| p4bl0 wrote:
| I would have liked actual examples of what it can do. With a
| simple table, and a few requests and their outputs.
| tatut wrote:
| that is coming, once I get it a bit more developed... this
| isn't anywhere near usable for real yet
| joeatwork wrote:
| An alternative query language for PostgreSQL would be a wonderful
| thing! But it isn't clear that's what this is.
| mritchie712 wrote:
| If you're interested in an alternative query language,
| https://prql-lang.org/ is a good one.
| snthpy wrote:
| Thanks for the mention. For Postgres, the easiest way to use
| PRQL is the plprql extension:
|
| Link: https://github.com/kaspermarstal/plprql
|
| Previous discussion:
| https://news.ycombinator.com/item?id=39428609
| tatut wrote:
| yes, I have plans to try adding some query helpers eventually.
|
| One could imagine representing queries as compound terms, like:
| q(user(id=Id, name=like("Bob%"), email=Email))
|
| which would query, from user table and bind Id and Email for
| all matches. I plan to experiment with something like that.
| OJFord wrote:
| > it isn't clear that's what this is
|
| Because that's _not_ what this is - this is for when you write
| e.g. `language plpgsql` in defining some function; instead of
| that (with this installed) you could write `language plprolog`
| and use prolog.
| steinuil wrote:
| If you're interested in this I would also recommend you check out
| Logica[0], which is a datalog-like language that is explicitly
| made to compile to SQL queries.
|
| 0: https://logica.dev/
| zilti wrote:
| Oh nice, this is the one "SQL replacement" I'd actually look
| into
| breakfastduck wrote:
| What puts you off prequel? https://prql-lang.org
|
| really readable, nice syntax imo
| hashmash wrote:
| PRQL has some very nice features, but the syntax also has
| some not so nice features that you don't see in the
| examples. There's special parsing rules for parenthesis and
| newlines, and a special word wrap character is needed at
| times to disable the rule that treats newlines as the pipe
| operator.
| otabdeveloper4 wrote:
| "Unreadable syntax" is like number 1000 on the list of SQL
| problems.
| ryanjshaw wrote:
| I was wondering how this was possible and then found this
| monstrosity:
| https://colab.research.google.com/github/EvgSkv/logica/blob/...
|
| Click on the SQL tab.
| cess11 wrote:
| Seen longer, more obtuse queries written by hand in some
| applications I've worked on. Big, useful systems often give
| rise to them eventually.
| greenavocado wrote:
| I wrote SQL queries twice that long for credit originations
| reports in banks which would hit several terabytes of disk
| read per run
| ryanjshaw wrote:
| Apologies, I should've been more clear. The _absolute_
| length of SQL wasn 't what I was alluding to: it was the
| ratio of Datalog to SQL I was curious about based on this
| statement in the website:
|
| > Among database theoreticians Datalog and SQL are known to
| be equivalent. And indeed the conversion from Datalog to
| SQL and back is often straightforward.
|
| I was skeptical, and indeed the sample I linked to shows 8
| lines of Datalog turning into 265 lines of SQL. In defense
| of SQL, I note WITH RECURSIVE wasn't used.
| greenavocado wrote:
| CREATE TABLE edges ( source INT,
| target INT ); INSERT INTO edges
| (source, target) SELECT generate_series as
| source, generate_series + 1 as target FROM
| generate_series(0, 999); WITH RECURSIVE
| path_lengths AS ( -- Base case: Direct paths
| from edges SELECT source, target, 1 AS
| distance FROM edges UNION ALL
| -- Recursive step: Double the path length by joining on
| intermediate nodes SELECT p.source, e.target,
| p.distance + 1 AS distance FROM path_lengths
| AS p JOIN edges AS e ON p.target = e.source
| WHERE p.distance < 256 -- Control the recursion depth
| (2^8=256 for C8 equivalent) ) -- Final
| query to select paths from source 0, similar to the logic
| program's final goal , final_paths AS (
| SELECT source, target, MIN(distance) AS distance
| FROM path_lengths WHERE source = 0
| GROUP BY source, target ) SELECT * FROM
| final_paths ORDER BY source, target;
|
| You can confirm the output matches by pasting it and
| clicking run here: https://extendsclass.com/postgresql-
| online.html
| ryanjshaw wrote:
| That's really not bad. I'm looking at CozoDB for a hobby
| project of mine, but if Logica could produce output like
| this consistently I'd probably prefer it because I'm much
| more familiar with maintaining RDBMSs.
| greenavocado wrote:
| Logica didn't output that. I posted SQL equivalent to the
| original Logica.
| larodi wrote:
| Love this, I've long considered that this should've been made as
| Prolog and SQL are ternary logic and basically SQL derives from
| Datalog and it itself from Prolog. A table record can be taught
| as of as a Prolog fact, so this makes the WHERE clauses the
| predicates in the conjunction on the right-hand side of a rule.
| And then exhausting the goal is actually returning the result-
| set.
|
| Hope to see this develop even further, as Prolog has its place
| with relational databases.
| kevindamm wrote:
| Prolog's evaluation semantics are order-dependent, though. I've
| always thought this was the reason why the two language
| paradigms didn't see more merging than they did. There are some
| datalog+RDBMS hybrids but, as much as I'm not a fan of .NET, I
| think LINQ has seen the most success in this space.
| neonsunset wrote:
| What are you concerned about when using .NET?
| tejtm wrote:
| their soul?
| kevindamm wrote:
| For me, I just haven't enjoyed the developer experience.
| It's been a few years so take my opinions with a dash of
| salt but I find the language itself verbose, I should be
| freed of having to think about its memory layout because of
| garbage collection but I still end up thinking about it
| because of type boxing and enumerator-wrappers, the garbage
| collection routine itself is not as mature as other
| environments that I'm familiar with (perhaps that has
| improved?)... and, I know some of my characterization is
| unfair. Among the .NET/CLR languages I really only have in-
| depth experience with C# and perhaps I should give one of
| the others a chance. I also carry baggage from a few years
| on a Unity project that twists what C# actually is (like
| how async/await is mangled and the way some library
| routines are only available from the main thread). I also
| probably allow my experience on a Java project (a few years
| of rather high stress) to bias me w.r.t. C# but I know
| that's also unfair, but these make me less likely to
| approach the language. It wouldn't be a deal-breaker for me
| to join a team but it would step down my enthusiasm some.
|
| I will say, though, that the .NET authors did seem to learn
| some lessons from Java's worse API decisions and the .NET
| API is more uniform and reasonable overall.
| neonsunset wrote:
| To clarify, Unity case is a massive night and day
| difference to what is expected to be "normal" language
| experience e.g. using ASP.NET Core/EF Core, AvaloniaUI,
| writing a CLI app or a systemd service, or GtkSharp even,
| or using Godot/Stride. This is due to GC being much
| slower and more punishing, very often completely
| different way of doing basic operations and it also
| (ab)using IEnumerators as coroutines which may make it
| seem that average usage of them is just as difficult
| (it's not). Performance is also significantly different,
| sometimes by an order of magnitude, even including Burst-
| compiled code.
|
| Boxing is rarely something you have to think about if
| ever in general purpose code, nor is garbage collection
| outside of not insisting on doing things less efficient
| and often more painful way (doing
| int.Parse(text.Substring(0, 4)) over
| int.Parse(text.AsSpan(0..4)), something the analyzer
| prompts you to fix).
|
| If you care about performance as indicated by message
| content, then any JVM language is a very big downgrade as
| many patterns are simply not expressible with it the way
| they are with C#/C++/Rust.
|
| There are also significant differences in tooling as .NET
| one is much more aligned with what you expect from using
| Rust/Go/even Node interacting via CLI (dotnet
| build/run/publish).
| ilaksh wrote:
| Postgres is not just a relational db. It's a way of life.
|
| JSONB, HSTORE, LTREE, Full Text Search, Logical Replication,
| Range Types, BRIN Indexes, GIN Indexes, GiST Indexes, SP-GiST
| Indexes, Table Inheritance, Foreign Data Wrappers, XML Support,
| UUID-OSSP, pg_trgm, Cube, Earthdistance, pg_prolog, pg_partman,
| pgvector, TimescaleDB, PostGIS, Citus, pg_cron, BDR (Bi-
| Directional Replication), PL/Python, PL/Java, PL/V8,
| pg_stat_statements, pg_prewarm, pg_hint_plan, pg_repack, pgAudit,
| pgRouting, Multicorn (FDW), HypoPG, pg_squeeze, pglogical,
| Postgres-XL, Wal2json
| pjmlp wrote:
| All enterprise level RDMS have similar capabilities, some of
| which are yet to come to Postgres.
| qweqwe14 wrote:
| Postgres is currently the most advanced RDMS, anyone who's
| not locked in by Oracle or whatever and doesn't use Postgres
| for new projects is likely misinformed.
|
| Postgres essentially made every other RDMS obsolete except
| for some niche circumstantial cases (e.g. vendor lock-in)
|
| > enterprise level
|
| Postgres _is_ enterprise level (whatever that means).
| Blazingly fast, Web 3, cloud-native, etc etc, pick your own
| buzzwords
| pjmlp wrote:
| The same applies to those that think Postgres does
| everything, every single feature, that Oracle, SQL Server,
| DB 2, and co, are able to deliver, in projects where their
| license costs are kind of irrelevant in the big context of
| the organization.
|
| Usually, it is a great way for many organizations to have a
| database as free beer.
| qweqwe14 wrote:
| Sure, a few organizations may actually need some obscure
| feature that Oracle provides, but again, it's niche. For
| most companies, Postgres provides way more features than
| they will ever use.
|
| And for the other 1%, it sometimes happens that their
| need for a specific feature in Oracle DB turns out to be
| entirely unnecessary.
|
| Not to mention that the vast majority of products turn
| out to be fancy CRUD apps. Doesn't matter though, Oracle
| will convince you that you NEED their DB regardless.
| rad_gruchalski wrote:
| There's one thing Postgres doesn't provide. It doesn't
| provide a supplier who a company can put their liability
| on. A supplier who can fix the problem in Postgres code
| and maintain it with authority.
|
| But don't get me wrong. Postgres is an awesome database
| system.
| vincnetas wrote:
| i beg to differ: Professional Services
|
| https://www.postgresql.org/support/professional_support/
| qweqwe14 wrote:
| SQLite has this as well, although it's use cases are
| different: https://sqlite.org/com/member.html
|
| They have multiple tiers of support as well, pretty
| interesting read
| rad_gruchalski wrote:
| Most of these are consultants specialising in hosting and
| "consulting". There's even AWS in there. How much does it
| cost to be on that list?
| Jedd wrote:
| What does 'put their liability on' mean to you?
|
| Because I suspect it does not mean what I think you think
| it means.
|
| (Typically the EULA of all those non-Postgres systems are
| _' this is sold as is, no warranty as to suitability to
| your purpose, yada yada'_
|
| Often times people believe that if they're paying many
| monies for a support contract, that means they can
| relocate their liability to that company. Almost every
| time, that company has better lawyers / contract writers.
| rad_gruchalski wrote:
| the product is sold as such but there are support
| contracts to cover everything else
| marcosdumay wrote:
| Oh, good luck getting this from any of Oracle, Microsoft
| or IBM... But actually, you usually can get those for
| Postgres.
|
| Potsgres is the one general purpose DBMS out there that
| you can hire a company to actually solve your problems.
| blowski wrote:
| The main enterprise-necessary feature missing in Postgres
| compared to Oracle is free trips to the Bahamas.
| hosh wrote:
| I'd argue that, the real value of Postgres isn't that it
| has capabilities on par with Oracle, but rather that, it
| has a thriving plugin ecosystem. If someone needs that
| obscure Oracle feature, they may have the option of
| writing it. If there's something a lot of people are
| interested in (such as vector features), someone will
| implement it.
| marcosdumay wrote:
| To a very close approximation, Postgres does do
| everything.
|
| And on the very uncommon cases that you need really
| something that Postgres doesn't do, Oracle, MS SQL, DB2,
| and co do not provide enough of a difference, and what
| you actually need is an specialized DBMS.
| ryanjshaw wrote:
| You haven't mentioned support. As much as I'm a fan of
| Postgres, if my org doesn't have a Postgres support
| capability, but it does have a MSSQL team, then I'm going
| to stick with MSSQL.
|
| I've got software to write, I don't want to find out that
| script I copied from stackoverflow to backup the database
| doesn't work in all the scenarios I thought it would
| because something changed a minor version ago.
| riku_iki wrote:
| > Postgres is enterprise level (whatever that means).
| Blazingly fast, Web 3, cloud-native, etc etc, pick your own
| buzzwords
|
| it has issues in several choke points: HA setup is
| complicated, it doesn't utilize multi-core machines well on
| heavy queries.
| Izkata wrote:
| > enterprise level (whatever that means)
|
| It means "non-technical executives/managers recognize the
| name and will approve its use". Like Oracle.
| fuy wrote:
| I love and use Postgres daily for many years, but:
|
| Performance monitoring is pretty much absent, all you have
| is pg_stat_statements and friends. So for any serious scale
| you need 3d party solution (or you're writing your own)
| straight away.
|
| HA is complicated. Patroni is the best option now, but it's
| quite far from experience SQL Server or Oracle provide.
|
| Optimizer is still quite a bit simpler than in SQL
| server/Oracle. One big thing that is missing for me is
| "adaptive" query processing (there's AQP extension, but
| it's not a part of distribution). Even basic adaptive
| features help a lot when optimizer is doing stupid things
| (I'm not gonna bring up query hints here :))
| hosh wrote:
| Postgres is an open-source platform with a thriving
| ecosystem. People have written a vector plugin to follow the
| AI boom, and will likely have whatever people have an
| interest for.
| ForHackernews wrote:
| https://pigsty.io/blog/pg/pg-eat-db-world/
| mark_l_watson wrote:
| As a proof of concept, this looks very cool. Suggestion: add a
| short example to the README.
|
| I had one experience with Prolog in the 1980s that blew my mind.
| I had an IR&D project to build a complete prototype of an
| air/land battle simulator (yes, I was a defense contractor back
| then) in Common Lisp given 6 weeks of coverage to write it and
| demo it. After a month I was satisfied with the functionality and
| after demoing it I asked permission to rewrite it in ExperProlog
| on the Mac (I had done the Common Lisp version in my Xerox 1108
| Lisp Machine). In ten days time it was done, and also had nice
| graphics and UI extensions that the Common Lisp version did not
| have. Anyway, except for few small open source things, that was
| the only large project I ever did in Prolog.
| CoastalCoder wrote:
| Can you explain a little about why / how you modeled a battle
| simulation in Prolog?
|
| To me, the most natural approach is simply a time-stepped
| battlespace model. With event uncertainty (e.g., did the bullet
| hit its target?) modeled as random draws that get baked into
| the outcome.
| mark_l_watson wrote:
| Honestly, that was almost 40 years ago and the details escape
| me. I do remember that it was not a fine detail model. I
| think some 'experts' in my company wrote up how they solved
| certain problems and I tried to capture that. I might have
| used OPS5 in the Common Lisp prototype. I (in hindsight)
| wasted a lot of time back then on symbolic AI that didn't
| scale, and not enough time with neural networks. I was on a
| DARPA neural network advisory panel in the mid 1980s and I
| had a few good wins using NNs.
| crq-yml wrote:
| My speculative reaction: Prolog offers a way to describe an
| AI "commander" that can make plans that optimize for many
| units simultaneously. This technique is often used in
| strategy games, not usually with Prolog itself but using
| similar hand-written planner algorithms in tandem with FSMs.
|
| One of the downsides of this approach where it appears in
| games is that it results in unit behaviors that look robotic
| and overly micromanaged, often using tactics similar to early
| computer chess AI.
| clord wrote:
| This reminds me of an old idea I've toyed with. In a logic
| class in university we talked about how some logic is time
| dependent, like A is true 2 hours after B becomes true. I was
| inspired to try to think through a language like prolog that
| could model and solve these relations through time. Didn't get
| far with it since it's a hard problem and I had too many
| classes that term. I was thinking it would be useful for
| clockless chips.
| PedroBatista wrote:
| Not sure if it's exactly what you're looking for but see this
| paper: https://ceur-ws.org/Vol-2785/paper9.pdf ( A Language
| for Timeline-based Planning )
| raincom wrote:
| It is called Temporal logic or tense logic[1][2]. Linear time
| temporal logic is used in formal verification.
|
| [1] https://plato.stanford.edu/entries/logic-temporal/
|
| [2] https://en.wikipedia.org/wiki/Temporal_logic
| akvadrako wrote:
| How come you don't mention why it blew your mind?
|
| Are you implying it's because a rewrite took less time than the
| first version?
| madsbuch wrote:
| When I saw the title I hopes this would be schema aware. Looking
| into the current proof of concept they allow to write prolog
| definitions.
|
| How cool would be be, if all relations in a Postgres database
| would be lifted into the scope of a prolog process to work
| directly on the relations.
| kevindamm wrote:
| If your entire database can be lifted into the application's
| heap, it's probably small enough that I wonder why you've got
| it stored in an RDBMS... and because Prolog is lexically
| sensitive (order of sentences and the order of clauses in the
| sentence affect the eval result) then you would need to
| effectively load all related DB entries -- or maybe some cursor
| tricks to load domain entries lazily and the many small queries
| that entails.
|
| What I've usually seen instead is starting from Datalog
| (declarative, order-independent) instead of Prolog, and
| converting that into the relevant SQL queries then loading the
| results into variables within the datalog context. This splits
| the knowledge base into IDB and EDB parts.
| naasking wrote:
| > If your entire database can be lifted into the
| application's heap, it's probably small enough that I wonder
| why you've got it stored in an RDBMS
|
| I think he meant lifting the database schema, not the whole
| database. This would help with auto completion and other
| static checks before trying to run queries.
| Izkata wrote:
| Much much more important would be the foreign keys. If I
| remember my prolog correctly, this:
| parent(alice,bob).
|
| is duplicating information that could already be found in
| the schema's relationships.
| madsbuch wrote:
| Something like this! So that you can naturally query your
| data defining relationships and constraints
|
| (This can already be done using joins, but that is not
| very ergonomical, especially when a lot of relations are
| at play)
| ekoontz wrote:
| I did some something like this a while ago:
|
| https://github.com/ekoontz/psqlog
| west0n wrote:
| Recently, I learned that PostgreSQL can integrate with many
| languages through plugins, such as PL/Python, pgrx (Rust), pgzx
| (Zig), and so on. I wonder if anyone is planning to write one for
| Java or C#, lol.
| neonsunset wrote:
| Not that I'm aware of but any plugin system that expects
| dynamically linked binaries integrating through C API can be
| targeted by .NET NativeAOT (e.g. OBS plugins can be written in
| C# without manually dealing with hosting the runtime).
| fiddlerwoaroof wrote:
| There has been PL/Java for a long time:
| https://tada.github.io/pljava/ and PL/Perl and others. The
| projects you mention are largely building on well-established
| interfaces in Postgres.
| AceJohnny2 wrote:
| Tangentially, Gerrit (a git Code Review web-app) deprecated the
| use of Prolog for the Submission Requirement feature.
|
| https://gerrit-review.googlesource.com/Documentation/prolog-...
|
| Prolog was introduced in 2.2.2 (2012), and deprecated in 3.6
| (2022)
___________________________________________________________________
(page generated 2024-03-30 23:00 UTC)