Title
#users-public
m

Matthew Smalley

08/23/2022, 5:34 PM
Hi everyone! Was trying to follow some of the steps in a blog post called "Streaming on-chain Ethereum data to QuestDB" but myself and others are running into issues with the Postgres integration. Anyone think they could lend me a hand?
Alex Pelagenko

Alex Pelagenko

08/23/2022, 5:37 PM
what is the problem? perhaps the article is out of date with new releases
m

Matthew Smalley

08/23/2022, 5:41 PM
I'm trying to insert data into a table called blocks, the definition of which is below:
create table blocks (
    timestamp string,
    number int,
    hash string,
    parent_hash string,
    nonce string,
    sha3_uncles string,
    logs_bloom string,
    transactions_root string,
    state_root string,
    receipts_root string,
    miner symbol,
    difficulty long,
    total_difficulty long,
    size long,
    extra_data string,
    gas_limit long,
    gas_used long,
    transaction_count long,
    base_fee_per_gas long
);
I tried going into the web console and manually inserting the data which seemed to work. However, when running the linked CLI tool, I get the following error
sqlalchemy.exc.ProgrammingError: (pg8000.exceptions.ProgrammingError) {'C': '00000', 'M': "',' expected", 'S': 'ERROR', 'P': '344'}
[SQL: INSERT INTO blocks (timestamp, number, hash, parent_hash, nonce, sha3_uncles, logs_bloom, transactions_root, state_root, receipts_root, miner, difficulty, total_difficulty, size, extra_data, gas_limit, gas_used, transaction_count, base_fee_per_gas) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ON CONFLICT (hash) DO UPDATE SET timestamp = excluded.timestamp, number = excluded.number, parent_hash = excluded.parent_hash, nonce = excluded.nonce, sha3_uncles = excluded.sha3_uncles, logs_bloom = excluded.logs_bloom, transactions_root = excluded.transactions_root, state_root = excluded.state_root, receipts_root = excluded.receipts_root, miner = excluded.miner, difficulty = excluded.difficulty, total_difficulty = excluded.total_difficulty, size = excluded.size, extra_data = excluded.extra_data, gas_limit = excluded.gas_limit, gas_used = excluded.gas_used, transaction_count = excluded.transaction_count, base_fee_per_gas = excluded.base_fee_per_gas]
[parameters: ('2016-01-25 01:30:05', Decimal('900000'), '0x46b8c1571d9b8511c85f185c6e3fef04431c4b891a294c1a0dfc5056dc5604eb', '0x9b8df528faae645e0b5b2cf6d6aa65f2e45a74fc61f4768a52c051a3d3436f85', '0xe387a98b08a28430', '0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347', '0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ... (216 characters truncated) ... 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000', '0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421', '0xe95270494d6ff7bd0b2868523e66101649a1abc5174e57071994cb362a43623d', '0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421', '0x52bc44d5378309ee2abf1539bf71de1b7d7be3b5', Decimal('10746660430419'), Decimal('6028218451267046594'), Decimal('541'), '0xd783010303844765746887676f312e342e32856c696e7578', Decimal('3141592'), Decimal('0'), Decimal('0'), None)]
The command I executed to get this error is:
ethereumetl stream --start-block 600000 -e block,token_transfer \
--output <postgresql+pg8000://admin:quest@localhost:8812/qdb> \
--provider-uri <REDACTED API KEY>
5:42 PM
Thanks for your help btw!
Alex Pelagenko

Alex Pelagenko

08/23/2022, 5:56 PM
Perhaps table Columns have changed I'll have a look
m

Matthew Smalley

08/23/2022, 6:05 PM
Thanks, I added one of the columns at the bottom but that didn't seem to change much
Alex Pelagenko

Alex Pelagenko

08/24/2022, 10:18 AM
Hey. It seems that it’s not going to work anymore. Looks like
ethereumetl
generates upserts which QuestDB never supported.
10:19 AM
I suspect the reason it used to work was that QuestDB ignored upsert syntax but now it parses it and fails to execute
10:20 AM
so realistically it never fully worked but a version from April 2021 of QuestDB (which I wouldn’t advise to ues) will ignore upsert part and do insert
m

Matthew Smalley

08/25/2022, 2:34 PM
I see, okay, thank you!