ethereumetl stream crashes when streaming to local postgres

[ddkang]

I am trying to use ethereumetl stream with the following command line:

ethereumetl stream --provider-uri file:///mnt/disks/geth/geth/geth.ipc --output=postgresql+pg8000://daniel_d_kang:[password here]@localhost:5432/ethereum

And I am getting the following error:

[...]
pg8000.core.ProgrammingError: {'S': 'ERROR', 'V': 'ERROR', 'C': '42P10', 'M': 'there is no unique or exclusion constraint matching the ON CONFLICT specification', 'F': 'plancat.c', 'L': '842', 'R': 'infer_arbiter_indexes'}

[...]
sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError) {'S': 'ERROR', 'V': 'ERROR', 'C': '42P10', 'M': 'there is no unique or exclusion constraint matching the ON CONFLICT specification', 'F': 'plancat.c', 'L': '842', 'R': 'infer_arbiter_indexes'}
[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: ('1970-01-01 00:00:00', Decimal('0'), '0xd4e56740f876aef8c010b86a40d5f56745a118d0906a34e69aec8c0db1cb8fa3', '0x0000000000000000000000000000000000000000000000000000000000000000', '0x0000000000000042', '0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347', '0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ... (216 characters truncated) ... 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000', '0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421', '0xd7f8974fb5ac78d9ac099b9ad5018bedc2ce0a72dad1827a1709da30580f0544', '0x56e81f171bcc55a6ff8345e692c0f86e5b48e01b996cadc001622fb5e363b421', '0x0000000000000000000000000000000000000000', Decimal('17179869184'), Decimal('17179869184'), Decimal('540'), '0x11bbe8db4e347b4e8c937c1c8370e4b5ed33adb3db69cbdb7a38e1e50b1b82fa', Decimal('5000'), Decimal('0'), Decimal('0'), None)]
(Background on this error at: http://sqlalche.me/e/f405)

Please let me know if there is anything other information you need to help with this bug.

[medvedev1088]

Thanks for reporting this. Have you applied indexes to your tables https://github.com/blockchain-etl/ethereum-etl-postgres/tree/master/indexes ?

[ddkang]

That seems to have worked, thanks! It might be nice to mention that the indexes need to be applied before streaming.

[michael-gendy]

Sorry to hi-jack this issue but I'm seeing something very similar trying to stream to QuestDB (following this tutorial https://medium.com/geekculture/streaming-ethereum-on-chain-data-to-questdb-ea6b51d990ab).

I've created a blocks table as below (changed to java data types) from https://github.com/blockchain-etl/ethereum-etl-postgres/blob/master/schema/blocks.sql:

create table blocks ( timestamp timestamp, number bigint, hash varchar(66), parent_hash varchar(66), nonce varchar(42), sha3_uncles varchar(66), logs_bloom text, transactions_root varchar(66), state_root varchar(66), receipts_root varchar(66), miner varchar(42), difficulty numeric(38), total_difficulty numeric(38), size bigint, extra_data text, gas_limit bigint, gas_used bigint, transaction_count bigint, base_fee_per_gas bigint );

and I'm running the following command line to stream:

ethereumetl stream -e block --output postgresql+pg8000://admin:@localhost:8812/qdb --provider-uri <MyInfuraLink>

Getting the following error similar to the one above but with no identifiable error code: sqlalchemy.exc.ProgrammingError: (pg8000.core.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: ('2015-11-26 14:27:50', Decimal('600000'), '0x068c1569e89ffb3d496641617e41404c7d47c3a0ccab55e6ae0b045ff189b389', '0x5783e02a998984893a4e44139c4cd452c32d5a1f30405cca196812e787859fb9', '0x4d2dd664365d838b', '0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347', '0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ... (216 characters truncated) ... 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000', '0xa32a12a7a8bd77d1060aaf6f9cc6e2d288d9ad31e3fad64ffa18d2b1e08adb92', '0x33cf7a130c40b468ba86a68d8d1b389cee7cafb16aed51dd8c74c1a33aab8669', '0x81a77819ee61e0391dae44a4a76ddce06a0b08bb3b75c1af12cbd7e93cc00de7', '0x52bc44d5378309ee2abf1539bf71de1b7d7be3b5', Decimal('7558412686319'), Decimal('3481936417512068289'), Decimal('656'), '0xd783010203844765746887676f312e342e32856c696e7578', Decimal('3141592'), Decimal('21000'), Decimal('1'), None)] (Background on this error at: http://sqlalche.me/e/f405)

Is anyone able to point me in the right direction on what may be wrong? Have tried various schema edits and indexing but no luck so far.

[swindire]

Sorry to hi-jack this issue but I'm seeing something very similar trying to stream to QuestDB (following this tutorial https://medium.com/geekculture/streaming-ethereum-on-chain-data-to-questdb-ea6b51d990ab).

I've created a blocks table as below (changed to java data types) from https://github.com/blockchain-etl/ethereum-etl-postgres/blob/master/schema/blocks.sql:

create table blocks ( timestamp timestamp, number bigint, hash varchar(66), parent_hash varchar(66), nonce varchar(42), sha3_uncles varchar(66), logs_bloom text, transactions_root varchar(66), state_root varchar(66), receipts_root varchar(66), miner varchar(42), difficulty numeric(38), total_difficulty numeric(38), size bigint, extra_data text, gas_limit bigint, gas_used bigint, transaction_count bigint, base_fee_per_gas bigint );

and I'm running the following command line to stream:

ethereumetl stream -e block --output postgresql+pg8000://admin:@localhost:8812/qdb --provider-uri

Getting the following error similar to the one above but with no identifiable error code: sqlalchemy.exc.ProgrammingError: (pg8000.core.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: ('2015-11-26 14:27:50', Decimal('600000'), '0x068c1569e89ffb3d496641617e41404c7d47c3a0ccab55e6ae0b045ff189b389', '0x5783e02a998984893a4e44139c4cd452c32d5a1f30405cca196812e787859fb9', '0x4d2dd664365d838b', '0x1dcc4de8dec75d7aab85b567b6ccd41ad312451b948a7413f0a142fd40d49347', '0x000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 ... (216 characters truncated) ... 00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000', '0xa32a12a7a8bd77d1060aaf6f9cc6e2d288d9ad31e3fad64ffa18d2b1e08adb92', '0x33cf7a130c40b468ba86a68d8d1b389cee7cafb16aed51dd8c74c1a33aab8669', '0x81a77819ee61e0391dae44a4a76ddce06a0b08bb3b75c1af12cbd7e93cc00de7', '0x52bc44d5378309ee2abf1539bf71de1b7d7be3b5', Decimal('7558412686319'), Decimal('3481936417512068289'), Decimal('656'), '0xd783010203844765746887676f312e342e32856c696e7578', Decimal('3141592'), Decimal('21000'), Decimal('1'), None)] (Background on this error at: http://sqlalche.me/e/f405)

Is anyone able to point me in the right direction on what may be wrong? Have tried various schema edits and indexing but no luck so far.

i also meet this problem, have you solved this? thanks

[swindire]

i also meet this problem, have you solved this? thanks

[ManivannanMurugavel]

Team,

I have the same error "sqlalchemy.exc.ProgrammingError: (pg8000.exceptions.ProgrammingError) {'S': 'ERROR', 'V': 'ERROR', 'C': '42P10', 'M': 'there is no unique or exclusion constraint matching the ON CONFLICT specification', 'F': 'plancat.c', 'L': '842', 'R': 'infer_arbiter_indexes'"

But only i am changing

alter table blocks add constraint blocks_pk primary key (hash, timestamp);

instead of

alter table blocks add constraint blocks_pk primary key (hash);

Please help me to resolve this issue...