surfaces/adapter: support the `money` data type

[philip-stoev]

What version of Materialize are you using

v0.7.4-dev (82ae64f89)

What was the issue?

If the Postgres side contains a MONEY type, CREATE VIEWS FROM SOURCE will fail with unknown catalog item 'money'.

Debezium attempts to work around that by replicating MONEY as DECIMAL(N,M)

Is the issue reproducible? If so, please provide reproduction instructions.

I intend to push the following test:

# Copyright Materialize, Inc. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

#
# Test the MONEY data type
#


# Insert data pre-snapshot
$ postgres-execute connection=postgres://postgres:postgres@postgres
ALTER USER postgres WITH replication;
DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;

CREATE TABLE t1 (f1 MONEY);
ALTER TABLE t1 REPLICA IDENTITY FULL;

INSERT INTO t1 VALUES (-92233720368547758.08), (+92233720368547758.07);

CREATE PUBLICATION mz_source FOR ALL TABLES;

> CREATE MATERIALIZED SOURCE mz_source
  FROM POSTGRES HOST 'host=postgres port=5432 user=postgres password=postgres sslmode=require dbname=postgres'
  PUBLICATION 'mz_source';

> SELECT COUNT(*) > 0 FROM mz_source;
true

> CREATE VIEWS FROM SOURCE mz_source;

# Insert the same data post-snapshot
$ postgres-execute connection=postgres://postgres:postgres@postgres
INSERT INTO t1 SELECT * FROM t1;

> SELECT pg_typeof(f1) FROM t1 LIMIT 1;
"double precision"

> SELECT * FROM t1;
-92233720368547758.08
+92233720368547758.07
-92233720368547758.08
+92233720368547758.07

[benesch]

My comment about backcompat (https://github.com/MaterializeInc/materialize/issues/6818#issuecomment-846412618) applies here too!

[petrosagg]

The internal representation of MONEY in postgres is just an int64 https://github.com/postgres/postgres/blob/REL_13_3/src/include/utils/cash.h#L17

Then, there is a database global parameter that instructs the parser how many digits are after the decimal point stored in the locale https://github.com/postgres/postgres/blob/REL_13_3/src/backend/utils/adt/cash.c#L114-L126

I'm pretty sure that if you replicate MONEY typed data from one pg instance to another with a locale that has a different number of fractional units bad things will happen.

Since I got nerd snipped, here are the non-standard locales. The zero ones are probably wrong data, the EUR one is for sure.

Name	Fractional digits
United Arab Emirates dirham	3
Albanian lek	3
Bahraini dinar	3
Bhutanese ngultrum	3
Algerian dinar	3
Egyptian pound	3
Iraqi dinar	3
Jordanian dinar	3
Kuwaiti dinar	3
Lebanese pound	3
Libyan dinar	3
Moroccan dirham	3
Omani rial	3
Qatari riyal	3
Sudanese pound	3
South Sudanese pound	3
Syrian pound	3
Tunisian dinar	3
Yemeni rial	3
Afghan afghani	0
Eritrean nakfa	0
Euro (for the Basque locale)	0
Iranian rial	0
Icelandic króna	0
Japanese yen	0
South Korean won	0
Serbian dinar	0
Vietnamese đồng	0

[umanwizard]

Lebanese pound

These were pegged at 1500 per USD until recently, and are now less valuable than that.

I struggle to imagine what can be usefully done with 0.001 Lebanese pounds...