search

oguimbal / pg-mem

An in memory postgres DB instance for your unit tests
MIT License
2k stars 97 forks source link

Support tuple assignation: DO UPDATE SET (a,b) = ROW(x,y) #215

Open rhlsthrm opened 2 years ago

rhlsthrm commented 2 years ago

Describe the bug

Query works fine in real Postgres but throws an error in pg-mem.

Error: 💔 Your query failed to parse.
This is most likely due to a SQL syntax error. However, you might also have hit a bug, or an unimplemented feature of pg-mem.
If this is the case, please file an issue at https://github.com/oguimbal/pg-mem along with a query that reproduces this syntax error.

👉 Failed query:

    INSERT INTO "transfers" ("call_data", "call_to", "destination_domain", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "idx", "nonce", "origin_domain", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "router", "status", "to", "transfer_id", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") VALUES ('0x0', '0x0000000000000000000000000000000000000000', '1111', null, null, null, null, null, null, null, null, null, null, '0', '1234', '2221', null, null, null, null, null, null, null, null, null, null, '0xb000000000000000000000000000000000000000', 'xcalled', '0xfaded00000000000000000000000000000000000', '0x3a88331f65389190d244db54b6a5727dcf085f30eb538e4502827ac08fb856ba', '7654321', '0xfaded00000000000000000000000000000000000', '80000', '5000000000', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000', '1650527296', '0x69095493d880416433d9140fec026a97d9b99fdd9e3b8e91c961ef1a030f87f0', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000') ON CONFLICT ("transfer_id") DO UPDATE SET ("transfer_id", "destination_domain", "origin_domain", "nonce", "to", "call_data", "call_to", "idx", "router", "status", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") = ROW(EXCLUDED."transfer_id", EXCLUDED."destination_domain", EXCLUDED."origin_domain", EXCLUDED."nonce", EXCLUDED."to", EXCLUDED."call_data", EXCLUDED."call_to", EXCLUDED."idx", EXCLUDED."router", EXCLUDED."status", EXCLUDED."execute_block_number", EXCLUDED."execute_caller", EXCLUDED."execute_gas_limit", EXCLUDED."execute_gas_price", EXCLUDED."execute_local_amount", EXCLUDED."execute_local_asset", EXCLUDED."execute_timestamp", EXCLUDED."execute_transaction_hash", EXCLUDED."execute_transferring_amount", EXCLUDED."execute_transferring_asset", EXCLUDED."reconcile_block_number", EXCLUDED."reconcile_caller", EXCLUDED."reconcile_gas_limit", EXCLUDED."reconcile_gas_price", EXCLUDED."reconcile_local_amount", EXCLUDED."reconcile_local_asset", EXCLUDED."reconcile_timestamp", EXCLUDED."reconcile_transaction_hash", EXCLUDED."reconcile_transferring_amount", EXCLUDED."reconcile_transferring_asset", EXCLUDED."xcall_block_number", EXCLUDED."xcall_caller", EXCLUDED."xcall_gas_limit", EXCLUDED."xcall_gas_price", EXCLUDED."xcall_local_amount", EXCLUDED."xcall_local_asset", EXCLUDED."xcall_timestamp", EXCLUDED."xcall_transaction_hash", EXCLUDED."xcall_transferring_amount", EXCLUDED."xcall_transferring_asset") RETURNING to_jsonb("transfers".*) || jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result;

💀 Syntax error at line 1 col 2456:

  INSERT INTO "transfers" ("call_data", "call_to", "destination_domain", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "idx", "nonce", "origin_domain", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "router", "status", "to", "transfer_id", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") VALUES ('0x0', '0x0000000000000000000000000000000000000000', '1111', null, null, null, null, null, null, null, null, null, null, '0', '1234', '2221', null, null, null, null, null, null, null, null, null, null, '0xb000000000000000000000000000000000000000', 'xcalled', '0xfaded00000000000000000000000000000000000', '0x3a88331f65389190d244db54b6a5727dcf085f30eb538e4502827ac08fb856ba', '7654321', '0xfaded00000000000000000000000000000000000', '80000', '5000000000', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000', '1650527296', '0x69095493d880416433d9140fec026a97d9b99fdd9e3b8e91c961ef1a030f87f0', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000') ON CONFLICT ("transfer_id") DO UPDATE SET ("transfer_id", "destination_domain", "origin_domain", "nonce", "to", "call_data", "call_to", "idx", "router", "status", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") = ROW

To Reproduce


-- create tables
create type transfer_status as enum ('pending', 'xcalled', 'executed', 'reconciled', 'failed');
create table transfers (
  -- meta
  transfer_id character(66) primary key,
  origin_domain varchar(255) not null,
  destination_domain varchar(255) not null,
  status transfer_status not null default 'pending',

  -- transfer data
  "to" character(42) not null,
  call_to character(42) not null default '0x0000000000000000000000000000000000000000',
  call_data text,
  idx bigint,
  nonce bigint not null,
  router character(42),

  -- xcall
  xcall_caller character(42),
  xcall_transferring_amount numeric,
  xcall_local_amount numeric,
  xcall_transferring_asset character(42),
  xcall_local_asset character(42),
  xcall_transaction_hash character(66),
  xcall_timestamp integer,
  xcall_gas_price numeric,
  xcall_gas_limit numeric,
  xcall_block_number integer,

  -- execute
  execute_caller character(42),
  execute_transferring_amount numeric,
  execute_local_amount numeric,
  execute_transferring_asset character(42),
  execute_local_asset character(42),
  execute_transaction_hash character(66),
  execute_timestamp integer,
  execute_gas_price numeric,
  execute_gas_limit numeric,
  execute_block_number integer,

  -- reconcile
  reconcile_caller character(42),
  reconcile_transferring_amount numeric,
  reconcile_local_amount numeric,
  reconcile_transferring_asset character(42),
  reconcile_local_asset character(42),
  reconcile_transaction_hash character(66),
  reconcile_timestamp integer,
  reconcile_gas_price numeric,
  reconcile_gas_limit numeric,
  reconcile_block_number integer
);

-- insert data
INSERT INTO "transfers" ("call_data", "call_to", "destination_domain", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "idx", "nonce", "origin_domain", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "router", "status", "to", "transfer_id", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") VALUES ('0x0', '0x0000000000000000000000000000000000000000', '1111', null, null, null, null, null, null, null, null, null, null, '0', '1234', '2221', null, null, null, null, null, null, null, null, null, null, '0xb000000000000000000000000000000000000000', 'xcalled', '0xfaded00000000000000000000000000000000000', '0x3a88331f65389190d244db54b6a5727dcf085f30eb538e4502827ac08fb856ba', '7654321', '0xfaded00000000000000000000000000000000000', '80000', '5000000000', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000', '1650527296', '0x69095493d880416433d9140fec026a97d9b99fdd9e3b8e91c961ef1a030f87f0', '10000000000000000000000000', '0xaaa0000000000000000000000000000000000000') ON CONFLICT ("transfer_id") DO UPDATE SET ("transfer_id", "destination_domain", "origin_domain", "nonce", "to", "call_data", "call_to", "idx", "router", "status", "execute_block_number", "execute_caller", "execute_gas_limit", "execute_gas_price", "execute_local_amount", "execute_local_asset", "execute_timestamp", "execute_transaction_hash", "execute_transferring_amount", "execute_transferring_asset", "reconcile_block_number", "reconcile_caller", "reconcile_gas_limit", "reconcile_gas_price", "reconcile_local_amount", "reconcile_local_asset", "reconcile_timestamp", "reconcile_transaction_hash", "reconcile_transferring_amount", "reconcile_transferring_asset", "xcall_block_number", "xcall_caller", "xcall_gas_limit", "xcall_gas_price", "xcall_local_amount", "xcall_local_asset", "xcall_timestamp", "xcall_transaction_hash", "xcall_transferring_amount", "xcall_transferring_asset") = ROW(EXCLUDED."transfer_id", EXCLUDED."destination_domain", EXCLUDED."origin_domain", EXCLUDED."nonce", EXCLUDED."to", EXCLUDED."call_data", EXCLUDED."call_to", EXCLUDED."idx", EXCLUDED."router", EXCLUDED."status", EXCLUDED."execute_block_number", EXCLUDED."execute_caller", EXCLUDED."execute_gas_limit", EXCLUDED."execute_gas_price", EXCLUDED."execute_local_amount", EXCLUDED."execute_local_asset", EXCLUDED."execute_timestamp", EXCLUDED."execute_transaction_hash", EXCLUDED."execute_transferring_amount", EXCLUDED."execute_transferring_asset", EXCLUDED."reconcile_block_number", EXCLUDED."reconcile_caller", EXCLUDED."reconcile_gas_limit", EXCLUDED."reconcile_gas_price", EXCLUDED."reconcile_local_amount", EXCLUDED."reconcile_local_asset", EXCLUDED."reconcile_timestamp", EXCLUDED."reconcile_transaction_hash", EXCLUDED."reconcile_transferring_amount", EXCLUDED."reconcile_transferring_asset", EXCLUDED."xcall_block_number", EXCLUDED."xcall_caller", EXCLUDED."xcall_gas_limit", EXCLUDED."xcall_gas_price", EXCLUDED."xcall_local_amount", EXCLUDED."xcall_local_asset", EXCLUDED."xcall_timestamp", EXCLUDED."xcall_transaction_hash", EXCLUDED."xcall_transferring_amount", EXCLUDED."xcall_transferring_asset") RETURNING to_jsonb("transfers".*) || jsonb_build_object('$action', CASE xmax WHEN 0 THEN 'INSERT' ELSE 'UPDATE' END) AS result;

pg-mem version

"version": "2.3.5"

rhlsthrm commented 2 years ago

Btw, I was trying to use this library with Zapatos which would be an awesome PG stack to use.