Hacker News new | comments | show | ask | jobs | submit login
SQL on Ethereum Blockchain with Presto (github.com)
153 points by xiaoyao on July 25, 2017 | hide | past | web | favorite | 45 comments

There seems to be a lot of confusion in the other posts as to what this is.

Simply, it is an adapter that lets you use SQL syntax to introspect Ethereum blockchain data (blocks, and transactions.) Typically people do this using a JS API built into the node software [1], and it may/may not be ones' cup of tea.

[1] https://github.com/ethereum/wiki/wiki/JavaScript-API

Yes, and with presto you can join tables from different sources. So you can join ethereum data with whatever you want, be it a mysql table of accounts in observations, etc.

Oh, they loaded the Etherium blockchain into an SQL database. Easy. From the title, it appeared that someone had figured out how to represent an SQL database in the blockchain using the virtual machine for contracts. That would be hard.

As I've pointed out before, smart contracts need atomic transactions. Either everything commits, or nothing commits. This is a basic property needed for accounting systems.

Transactions on Ethereum are atomic. If something throws, everything rolls back.

There's one well-known exception, which is that if your contract sends ETH to another contract, invoking its fallback function, then a throw in the callee just means the call returns false. So in that particular case you have to check the return value and rethrow to make it atomic; this sounds crazy but in some circumstances you don't actually want to throw. The compiler gives you a warning if you don't check.

Didn't the DAO hack happen because someone found a way to make an "atomic" transaction fail without full rollback?

No, it was a reentrant attack. The contract was doing a state change after sending ETH, and since the recipient called back, it was able to get repeated ETH sends before the state updated.

That's an atomicity failure. That class of bug, incidentally, is a classic source of trouble in window/widget GUI systems.

Hah, I used to run into a lot of those GUI issues, and hadn't made the connection until now.

It's a logic error. It's actually still atomic.

To clarify, reentrant errors are not atomicity errors. Fully serializable transactions can have reentrant errors and they often do, but that class of error is a case of the code not doing what you expect rather than an atomic violation.

What I would instead wager is that it's too easy to introduce reentrant errors in Solidity.

I've noticed since the DAO exploit, the ecosystem has been better about this though. For example, Solidity's docs has a section of reentrancy, and even the in-browser editor can warn you about reentrancy in some cases. Seems to be improving, though unfortunately after big expense.

Luckily, while it's easy to introduce reentrant errors, it's also easy to avoid them: just make sure that an ETH transfer or another call to an unknown contract is always the last thing you do in a transaction.

It's sometimes easy to miss, but the ecosystem is also a lot better at insisting on public security audits.

It's not loaded into a SQL database. You can think of Presto as simply as a SQL query engine. It sources data from Ethereum and run queries on it.

not only would it be hard, but using a blockchain (a type of database) to represent a functional SQL database using smart contracts is a mind-numbingly bad idea.

(insert "should i use a blockchain" infographic, which is amusingly impossible to google image search for, because this question evidently does not often occur to people...)

That might just depend on what parts of a "SQL database" you'd implement using a blockchain, and on the application type. You could e.g. segregate the data and only use the blockchain to authorize or timestamp transactions, for instance. In the latter case, Blockchain is just another consensus layer, and all distributed databases need one. See also https://github.com/pixelspark/catena, which uses the blockchain like a replication journal, logging only mutating queries and providing authorization based on public key crypto (disclosure: I am the author).

not to put too fine a point of it, but there's a world of difference between creating a ledger that creates a "distributed" SQL database whose integrity is guaranteed among non-trusted nodes through a blockchain, and using smart contracts to "put a functioning SQL database on the blockchain."

"get used to blockchain when all you know is SQL" - cool.

"guarantee all us non-trusted nodes have the same SQL database" - ...sure, but for any project such that i want this, i'm pretty sure i don't need a blockchain. (but of course, your project doubtless has uses i haven't thought of)

"i want a trustless SQL database so i'm going to use EVM and solidity and put it on The Blockchain because i'm awesome" - very bad.

"i'm starting a new project, so naturally i need to use a blockchain" - extremely bad.

Is this a JDBC connector for Ethereum? If so, that's neat. Ethereum APIs are generally awful, so a known, sane language for interacting with the blockchain has to be welcome.

It’s a Presto (prestodb.io) plugin - presto is a tool to do SQL queries over data sources (hadoop, nosql dbs, sql dbs, etc), including joining over them

Presto itself has a JDBC driver, so you could run it + this plugin in a Java app/codebase

Business model for you: Host a node yourself, and charge a micropayment in ETH to make SQL queries against the DB.

Interesting - Presto-As-A-Service is hard to do because of the way it handles permissions / auth. With an inherently shared resource like a blockchain it becomes a lot more feasible.

If im not mistaken presto-as-a-service is the power behind aws athena. They charge by the bytes scanned.

I'm not interested in the charging so much as how they handle privacy and security for multiple users/accounts in (supposedly) a single Presto cluster.

How do you account for the cost of the transaction itself? Those aren't cheap.

Querying the blockchain does not cost anything, just making changes to it. Otherwise, verifying transactions would be a recursive cost. I could be wrong though, but pretty sure since it's distributed reads are free, writes are where it costs.

I think he means for the micropayment. For that system to be viable it would need Raiden or some other payment channel system.

The tech isn't quite here yet but payment channels would be a good solution.

Payment channels alone are only a good solution if you consider it acceptable to establish a payment channel with everyone you want to pay. So, in other words, it only makes sense for recurring payments to the same merchant, and you pay the blockchain fee once for each merchant you want to pay. Consequently, if you only pay once you pay the same fee as an on-chain transaction, and if you pay 1000 times you pay, in average, 1000th the blockchain fee per payment.

+1, I have another project on payment channels, coming soon

Those gif are quite annoying to read and don't add anything of value. Static images, or even plain text, would be a lot better.

Edit: Sorry for the negativity. I created a PR for it. https://github.com/xiaoyao1991/presto-ethereum/pull/8

Thanks for the PR, and it's merged, but the gif is still animated though. I'll update with still images later in the day.

I think that's a caching issue

I agree and your comment didn't come off as negative to me.

Agreed. Since they only show single commands and no animation etc, plain text would be better.

The compute abstraction of Presto query engine is great, we have been working on a similar project based on Presto: https://enginesql.com/ (Shameless plug)

Presto is the in house 3d software used by Pixar. I came here and was very confused. Do we not Google search our software's name before assigning it?

I've been thinking about building API's for interaction with the off chain world, it seems like this is something with interest?

Can you elaborate more? What specifically about offchain world?

My first thought was sending email.


Please stop posting unsubstantively like this.


You want to write SQL-like queries for the ETH blockchain using Postgres? How exactly do you intend to do that?

Ignoring the stupidity of that guys "just use postgres" comment, it is actually possible to do this by writing a foreign data wrapper[1].

[1]: https://www.postgresql.org/docs/10/static/fdwhandler.html

Did you even click the link? Your comment makes no sense.

Oh look, another Ethereum post.

Nope, still not putting money/time here, sorry.

Please enlighten us why.

For myself, it's a matter of integrity. As an early holder, I was enamored with the idea, and took "Code is law" at face value. The response to the DAO incident alienated me. I don't have any ill will, and I'm not saying it's a scam or that you shouldn't get any, I'm just saying it is not for me.

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact