Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

pg_restore: error: could not execute query #1875

Open
Fell-x27 opened this issue Oct 8, 2024 · 8 comments
Open

pg_restore: error: could not execute query #1875

Fell-x27 opened this issue Oct 8, 2024 · 8 comments
Assignees
@sgillespie
Labels
bug Something isn't working

Comments

@Fell-x27
Copy link

Fell-x27 commented Oct 8, 2024

OS
Your OS: Ubuntu 22

Versions
The db-sync version (eg cardano-db-sync --version): 13.5.0.2
PostgreSQL version: 17

Build/Install Method
The method you use to build or install cardano-db-sync: prebuilt binaries

Run method
The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): none

Additional context
When I'm trying to restore the last snapshot, I see the error:

pg_restore: error: could not execute query: ERROR:  type "addr29type" already exists
Command was: CREATE DOMAIN public.addr29type AS bytea;

Problem Report
It looks like there is missed "IF NOT EXISTS".
As result - public mainnet snapshots-out-of-the-box are useless now...
@Fell-x27 Fell-x27 added the bug Something isn't working label Oct 8, 2024
@Fell-x27 Fell-x27 changed the title It pg_restore: error: could not execute query Oct 8, 2024
@Fell-x27
Copy link
Author

Fell-x27 commented Oct 8, 2024

I've removed --exit-on-error flag and got even more:

DROP OWNED
db/
db/4203.dat.gz
db/4258.dat.gz
db/4238.dat.gz
db/4176.dat.gz
db/4246.dat.gz
db/4162.dat.gz
db/4262.dat.gz
db/4152.dat.gz
db/4179.dat.gz
db/4234.dat.gz
db/4232.dat.gz
db/4268.dat.gz
db/4193.dat.gz
db/4178.dat.gz
db/4230.dat.gz
db/4221.dat.gz
db/4266.dat.gz
db/4189.dat.gz
db/4256.dat.gz
db/4280.dat.gz
db/4264.dat.gz
db/4195.dat.gz
db/4215.dat.gz
db/4183.dat.gz
db/4276.dat.gz
db/4142.dat.gz
db/4170.dat.gz
db/4191.dat.gz
db/4254.dat.gz
db/4278.dat.gz
db/4219.dat.gz
db/4211.dat.gz
db/4197.dat.gz
db/4213.dat.gz
db/4181.dat.gz
db/4228.dat.gz
db/4160.dat.gz
db/4252.dat.gz
db/4150.dat.gz
db/4248.dat.gz
db/4168.dat.gz
db/4270.dat.gz
db/4205.dat.gz
db/4134.dat.gz
db/4144.dat.gz
db/4138.dat.gz
db/4244.dat.gz
db/toc.dat
db/4236.dat.gz
db/4158.dat.gz
db/4272.dat.gz
db/4174.dat.gz
db/4172.dat.gz
db/4156.dat.gz
db/4250.dat.gz
db/4140.dat.gz
db/4227.dat.gz
db/4199.dat.gz
db/4217.dat.gz
db/4225.dat.gz
db/4207.dat.gz
db/4242.dat.gz
db/4136.dat.gz
db/4240.dat.gz
db/4146.dat.gz
db/4154.dat.gz
db/4185.dat.gz
db/4274.dat.gz
db/4187.dat.gz
db/4166.dat.gz
db/4148.dat.gz
db/4209.dat.gz
db/4223.dat.gz
db/4260.dat.gz
db/4201.dat.gz
db/4164.dat.gz
136684793-e2bb0babbc-513.lstate.gz
pg_restore: error: could not execute query: ERROR:  index "tx_out_reference_script_id_idx" does not exist
Command was: DROP INDEX public.tx_out_reference_script_id_idx;
pg_restore: error: could not execute query: ERROR:  index "tx_out_inline_datum_id_idx" does not exist
Command was: DROP INDEX public.tx_out_inline_datum_id_idx;
pg_restore: error: could not execute query: ERROR:  index "reference_tx_in_tx_out_id_idx" does not exist
Command was: DROP INDEX public.reference_tx_in_tx_out_id_idx;
pg_restore: error: could not execute query: ERROR:  index "redeemer_redeemer_data_id_idx" does not exist
Command was: DROP INDEX public.redeemer_redeemer_data_id_idx;
pg_restore: error: could not execute query: ERROR:  index "redeemer_data_tx_id_idx" does not exist
Command was: DROP INDEX public.redeemer_data_tx_id_idx;
pg_restore: error: could not execute query: ERROR:  index "pool_owner_pool_update_id_idx" does not exist
Command was: DROP INDEX public.pool_owner_pool_update_id_idx;
pg_restore: error: could not execute query: ERROR:  index "idx_withdrawal_tx_id" does not exist
Command was: DROP INDEX public.idx_withdrawal_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_withdrawal_redeemer_id" does not exist
Command was: DROP INDEX public.idx_withdrawal_redeemer_id;
pg_restore: error: could not execute query: ERROR:  index "idx_withdrawal_addr_id" does not exist
Command was: DROP INDEX public.idx_withdrawal_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_out_tx_id" does not exist
Command was: DROP INDEX public.idx_tx_out_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_out_stake_address_id" does not exist
Command was: DROP INDEX public.idx_tx_out_stake_address_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_out_payment_cred" does not exist
Command was: DROP INDEX public.idx_tx_out_payment_cred;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_out_address" does not exist
Command was: DROP INDEX public.idx_tx_out_address;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_metadata_tx_id" does not exist
Command was: DROP INDEX public.idx_tx_metadata_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_in_tx_out_id" does not exist
Command was: DROP INDEX public.idx_tx_in_tx_out_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_in_tx_in_id" does not exist
Command was: DROP INDEX public.idx_tx_in_tx_in_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_in_redeemer_id" does not exist
Command was: DROP INDEX public.idx_tx_in_redeemer_id;
pg_restore: error: could not execute query: ERROR:  index "idx_tx_cbor_tx_id" does not exist
Command was: DROP INDEX public.idx_tx_cbor_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_treasury_tx_id" does not exist
Command was: DROP INDEX public.idx_treasury_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_treasury_addr_id" does not exist
Command was: DROP INDEX public.idx_treasury_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_registration_tx_id" does not exist
Command was: DROP INDEX public.idx_stake_registration_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_registration_addr_id" does not exist
Command was: DROP INDEX public.idx_stake_registration_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_deregistration_tx_id" does not exist
Command was: DROP INDEX public.idx_stake_deregistration_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_deregistration_redeemer_id" does not exist
Command was: DROP INDEX public.idx_stake_deregistration_redeemer_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_deregistration_addr_id" does not exist
Command was: DROP INDEX public.idx_stake_deregistration_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_stake_address_view" does not exist
Command was: DROP INDEX public.idx_stake_address_view;
pg_restore: error: could not execute query: ERROR:  index "idx_slot_leader_pool_hash_id" does not exist
Command was: DROP INDEX public.idx_slot_leader_pool_hash_id;
pg_restore: error: could not execute query: ERROR:  index "idx_script_tx_id" does not exist
Command was: DROP INDEX public.idx_script_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_reward_pool_id" does not exist
Command was: DROP INDEX public.idx_reward_pool_id;
pg_restore: error: could not execute query: ERROR:  index "idx_reward_earned_epoch" does not exist
Command was: DROP INDEX public.idx_reward_earned_epoch;
pg_restore: error: could not execute query: ERROR:  index "idx_reward_addr_id" does not exist
Command was: DROP INDEX public.idx_reward_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_reserved_pool_ticker_pool_hash" does not exist
Command was: DROP INDEX public.idx_reserved_pool_ticker_pool_hash;
pg_restore: error: could not execute query: ERROR:  index "idx_reserve_tx_id" does not exist
Command was: DROP INDEX public.idx_reserve_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_reserve_addr_id" does not exist
Command was: DROP INDEX public.idx_reserve_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_update_reward_addr" does not exist
Command was: DROP INDEX public.idx_pool_update_reward_addr;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_update_meta_id" does not exist
Command was: DROP INDEX public.idx_pool_update_meta_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_update_hash_id" does not exist
Command was: DROP INDEX public.idx_pool_update_hash_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_update_active_epoch_no" does not exist
Command was: DROP INDEX public.idx_pool_update_active_epoch_no;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_retire_hash_id" does not exist
Command was: DROP INDEX public.idx_pool_retire_hash_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_retire_announced_tx_id" does not exist
Command was: DROP INDEX public.idx_pool_retire_announced_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_relay_update_id" does not exist
Command was: DROP INDEX public.idx_pool_relay_update_id;
pg_restore: error: could not execute query: ERROR:  index "idx_pool_metadata_ref_registered_tx_id" does not exist
Command was: DROP INDEX public.idx_pool_metadata_ref_registered_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_param_proposal_registered_tx_id" does not exist
Command was: DROP INDEX public.idx_param_proposal_registered_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_param_proposal_cost_model_id" does not exist
Command was: DROP INDEX public.idx_param_proposal_cost_model_id;
pg_restore: error: could not execute query: ERROR:  index "idx_ma_tx_out_tx_out_id" does not exist
Command was: DROP INDEX public.idx_ma_tx_out_tx_out_id;
pg_restore: error: could not execute query: ERROR:  index "idx_ma_tx_mint_tx_id" does not exist
Command was: DROP INDEX public.idx_ma_tx_mint_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_extra_key_witness_tx_id" does not exist
Command was: DROP INDEX public.idx_extra_key_witness_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_epoch_stake_pool_id" does not exist
Command was: DROP INDEX public.idx_epoch_stake_pool_id;
pg_restore: error: could not execute query: ERROR:  index "idx_epoch_stake_addr_id" does not exist
Command was: DROP INDEX public.idx_epoch_stake_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_epoch_param_cost_model_id" does not exist
Command was: DROP INDEX public.idx_epoch_param_cost_model_id;
pg_restore: error: could not execute query: ERROR:  index "idx_epoch_param_block_id" does not exist
Command was: DROP INDEX public.idx_epoch_param_block_id;
pg_restore: error: could not execute query: ERROR:  index "idx_epoch_no" does not exist
Command was: DROP INDEX public.idx_epoch_no;
pg_restore: error: could not execute query: ERROR:  index "idx_delegation_tx_id" does not exist
Command was: DROP INDEX public.idx_delegation_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_delegation_redeemer_id" does not exist
Command was: DROP INDEX public.idx_delegation_redeemer_id;
pg_restore: error: could not execute query: ERROR:  index "idx_delegation_pool_hash_id" does not exist
Command was: DROP INDEX public.idx_delegation_pool_hash_id;
pg_restore: error: could not execute query: ERROR:  index "idx_delegation_addr_id" does not exist
Command was: DROP INDEX public.idx_delegation_addr_id;
pg_restore: error: could not execute query: ERROR:  index "idx_delegation_active_epoch_no" does not exist
Command was: DROP INDEX public.idx_delegation_active_epoch_no;
pg_restore: error: could not execute query: ERROR:  index "idx_datum_tx_id" does not exist
Command was: DROP INDEX public.idx_datum_tx_id;
pg_restore: error: could not execute query: ERROR:  index "idx_collateral_tx_in_tx_out_id" does not exist
Command was: DROP INDEX public.idx_collateral_tx_in_tx_out_id;
pg_restore: error: could not execute query: ERROR:  index "idx_block_time" does not exist
Command was: DROP INDEX public.idx_block_time;
pg_restore: error: could not execute query: ERROR:  index "idx_block_slot_leader_id" does not exist
Command was: DROP INDEX public.idx_block_slot_leader_id;
pg_restore: error: could not execute query: ERROR:  index "collateral_tx_out_stake_address_id_idx" does not exist
Command was: DROP INDEX public.collateral_tx_out_stake_address_id_idx;
pg_restore: error: could not execute query: ERROR:  index "collateral_tx_out_reference_script_id_idx" does not exist
Command was: DROP INDEX public.collateral_tx_out_reference_script_id_idx;
pg_restore: error: could not execute query: ERROR:  index "collateral_tx_out_inline_datum_id_idx" does not exist
Command was: DROP INDEX public.collateral_tx_out_inline_datum_id_idx;
pg_restore: error: could not execute query: ERROR:  constraint "unique_reward" of relation "reward" does not exist
Command was: ALTER TABLE ONLY public.reward DROP CONSTRAINT unique_reward;
pg_restore: error: could not execute query: ERROR:  constraint "unique_epoch_stake" of relation "epoch_stake" does not exist
Command was: ALTER TABLE ONLY public.epoch_stake DROP CONSTRAINT unique_epoch_stake;

@sgillespie
Copy link
Contributor

It looks like there is missed "IF NOT EXISTS".

You'll need to start from a clean database. If you're using the postgresql-setup.sh script, then try running this first:

./scripts/postgresql-setup.sh --recreatedb

@Fell-x27
Copy link
Author

Fell-x27 commented Oct 9, 2024

You'll need to start from a clean database.

But I do... I drop everything before start.

@sgillespie
Copy link
Contributor

You'll need to start from a clean database.

But I do... I drop everything before start.

How are you doing that?

@Fell-x27
Copy link
Author

How are you doing that?

./scripts/postgresql-setup.sh --dropdb

However, there is actually a clean flag, so pg_restore will drop everything anyway.

The issue is that all these errors occur with PostgreSQL 17, but they are absent when I use PostgreSQL 16.

@sgillespie
Copy link
Contributor

The issue is that all these errors occur with PostgreSQL 17, but they are absent when I use PostgreSQL 16.

Interesting find, I will try to reproduce

@Fell-x27
Copy link
Author

The issue is that all these errors occur with PostgreSQL 17, but they are absent when I use PostgreSQL 16.

Interesting find, I will try to reproduce

But it is still possible to migrate from pg16 to pg17 with upgrade_cluster tools with no issues.

@sgillespie
Copy link
Contributor

Since you're no longer blocked, and PostgreSQL 17.0 is not yet available in nixpkgs, so I'll hold onto this until it's ready

@sgillespie sgillespie self-assigned this Oct 23, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@sgillespie sgillespie

Labels
bug Something isn't working
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@sgillespie @Fell-x27