Hasql errors should include the query that triggered them

Majed6 commented 1 year ago

Environment

PostgreSQL version: PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit
PostgREST version: PostgREST 10.0.0
Operating system: Docker on mac (postgrest/postgrest:v10.0.0)

Description of issue

Expected: {"code":"PGRSTX00","details":"select * from something;","hint":null,"message":"RowError 0 8 (ValueError \"End of input\")"}

Actual: {"code":"PGRSTX00","details":null,"hint":null,"message":"RowError 0 8 (ValueError \"End of input\")"}

Steps to reproduce: 1- start postgrest with config:

db-uri = "postgres://user:pass@ip.local:5002/my_db"
db-schemas = "public"
db-anon-role = "user"
db-channel-enabled=false
db-config=false
db-prepared-statements=false

2- Observer :

13/Oct/2022:07:35:28 +0000: Attempting to connect to the database...
13/Oct/2022:07:35:28 +0000: Listening on port 3000
13/Oct/2022:07:35:29 +0000: Connection successful
13/Oct/2022:07:35:31 +0000: An error ocurred when loading the schema cache
13/Oct/2022:07:35:31 +0000: {"code":"PGRSTX00","details":null,"hint":null,"message":"RowError 0 8 (ValueError \"End of input\")"}
13/Oct/2022:07:35:31 +0000: Attempting to connect to the database...
13/Oct/2022:07:35:32 +0000: Connection successful

Note

It would make my life easier to know which query is causing hasql to fail so that I could write a proper report to the community here about the edge case.

I'm here to ask for the query inclusion enhancement . As for the case I don't mind sharing a pcap with a maintainer directly.

laurenceisla commented 1 year ago

As a summary of the conversation we had in gitter:

That error happens in the Hasql library when attempting to parse more columns than there are in the result
I suggested enabling logging in the database and checking the last query of the loop (due to connection attempts), because that one should be the culprit.
It probably is a problem with the allViewsKeyDependencies query

You mentioned that DB logging wasn't possible from your end and that you can modify and build the source code, so an alternative would be to debug each step of the Schema Cache creation using Debug.Trace. You'll have to modify the steps in this file:

https://github.com/PostgREST/postgrest/blob/efecf007e8f84479794e00e83be7f502671b9cf3/src/PostgREST/SchemaCache.hs#L85-L93

You should have something like:


-- Immediately after all the imports
import Debug.Trace as BUG

-- ...

querySchemaCache :: [Schema] -> [Schema] -> Bool -> SQL.Transaction SchemaCache
querySchemaCache schemas extraSearchPath prepared = do
  SQL.sql "set local schema ''" -- This voids the search path. The following queries need this for getting the fully qualified name(schema.name) of every db object
  pgVer   <- BUG.trace "Querying pgVer:" $ SQL.statement mempty pgVersionStatement
  tabs    <- BUG.trace "Querying tabs:" $ SQL.statement schemas $ allTables pgVer prepared
  -- complete for the rest...

That should show you where it's failing, then you can verify which SQL query is the culprit after running PostgREST against your database.

Majed6 commented 1 year ago

15/Oct/2022:05:19:14 +0000: Listening on port 3000
15/Oct/2022:05:19:14 +0000: Attempting to connect to the database...
15/Oct/2022:05:19:14 +0000: Connection successful
Querying pgVer
Querying tabs
15/Oct/2022:05:19:15 +0000: An error ocurred when loading the schema cache
15/Oct/2022:05:19:15 +0000: {"code":"PGRSTX00","details":null,"hint":null,"message":"RowError 0 8 (ValueError \"End of input\")"}
15/Oct/2022:05:19:15 +0000: Attempting to connect to the database...
15/Oct/2022:05:19:15 +0000: Connection successful
15/Oct/2022:05:19:16 +0000: An error ocurred when loading the schema cache
15/Oct/2022:05:19:16 +0000: {"code":"PGRSTX00","details":null,"hint":null,"message":"RowError 0 8 (ValueError \"End of input\")"}
15/Oct/2022:05:19:16 +0000: Attempting to connect to the database..

As for allViewsKeyDependencies query I have removed the conditions and n.nspname = ANY($1 || $2) and where view_schema = ANY ($1) to get a result since ['public','public'] has no result.

Query

```sql with recursive pks_fks as ( -- pk + fk referencing col select contype::text as contype, conname, conrelid as resorigtbl, unnest(conkey) as resorigcol from pg_constraint where contype IN ('p', 'f') union -- fk referenced col select concat(contype, '_ref') as contype, conname, confrelid, unnest(confkey) from pg_constraint where contype='f' ), views as ( select c.oid as view_id, n.nspname as view_schema, c.relname as view_name, r.ev_action as view_definition from pg_class c join pg_namespace n on n.oid = c.relnamespace join pg_rewrite r on r.ev_class = c.oid where c.relkind in ('v', 'm') -- and n.nspname = ANY($1 || $2) ), transform_json as ( select view_id, view_schema, view_name, -- the following formatting is without indentation on purpose -- to allow simple diffs, with less whitespace noise replace( replace( replace( replace( replace( replace( replace( regexp_replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( replace( view_definition::text, -- This conversion to json is heavily optimized for performance. -- The general idea is to use as few regexp_replace() calls as possible. -- Simple replace() is a lot faster, so we jump through some hoops -- to be able to use regexp_replace() only once. -- This has been tested against a huge schema with 250+ different views. -- The unit tests do NOT reflect all possible inputs. Be careful when changing this! -- ----------------------------------------------- -- pattern | replacement | flags -- ----------------------------------------------- -- `<>` in pg_node_tree is the same as `null` in JSON, but due to very poor performance of json_typeof -- we need to make this an empty array here to prevent json_array_elements from throwing an error -- when the targetList is null. -- We'll need to put it first, to make the node protection below work for node lists that start with -- null: `(<> ...`, too. This is the case for coldefexprs, when the first column does not have a default value. '<>' , '()' -- `,` is not part of the pg_node_tree format, but used in the regex. -- This removes all `,` that might be part of column names. ), ',' , '' -- The same applies for `{` and `}`, although those are used a lot in pg_node_tree. -- We remove the escaped ones, which might be part of column names again. ), E'\\{' , '' ), E'\\}' , '' -- The fields we need are formatted as json manually to protect them from the regex. ), ' :targetList ' , ',"targetList":' ), ' :resno ' , ',"resno":' ), ' :resorigtbl ' , ',"resorigtbl":' ), ' :resorigcol ' , ',"resorigcol":' -- Make the regex also match the node type, e.g. `{QUERY ...`, to remove it in one pass. ), '{' , '{ :' -- Protect node lists, which start with `({` or `((` from the greedy regex. -- The extra `{` is removed again later. ), '((' , '{((' ), '({' , '{({' -- This regex removes all unused fields to avoid the need to format all of them correctly. -- This leads to a smaller json result as well. -- Removal stops at `,` for used fields (see above) and `}` for the end of the current node. -- Nesting can't be parsed correctly with a regex, so we stop at `{` as well and -- add an empty key for the followig node. ), ' :[^}{,]+' , ',"":' , 'g' -- For performance, the regex also added those empty keys when hitting a `,` or `}`. -- Those are removed next. ), ',"":}' , '}' ), ',"":,' , ',' -- This reverses the "node list protection" from above. ), '{(' , '(' -- Every key above has been added with a `,` so far. The first key in an object doesn't need it. ), '{,' , '{' -- pg_node_tree has `()` around lists, but JSON uses `[]` ), '(' , '[' ), ')' , ']' -- pg_node_tree has ` ` between list items, but JSON uses `,` ), ' ' , ',' )::json as view_definition from views ), target_entries as( select view_id, view_schema, view_name, json_array_elements(view_definition->0->'targetList') as entry from transform_json ), results as( select view_id, view_schema, view_name, (entry->>'resno')::int as view_column, (entry->>'resorigtbl')::oid as resorigtbl, (entry->>'resorigcol')::int as resorigcol from target_entries ), recursion as( select r.* from results r -- where view_schema = ANY ($1) union all select view.view_id, view.view_schema, view.view_name, view.view_column, tab.resorigtbl, tab.resorigcol from recursion view join results tab on view.resorigtbl=tab.view_id and view.resorigcol=tab.view_column ) select sch.nspname as table_schema, tbl.relname as table_name, rec.view_schema, rec.view_name, pks_fks.conname as constraint_name, pks_fks.contype as constraint_type, array_agg(row(col.attname, vcol.attname) order by col.attnum) as column_dependencies from recursion rec join pg_class tbl on tbl.oid = rec.resorigtbl join pg_attribute col on col.attrelid = tbl.oid and col.attnum = rec.resorigcol join pg_attribute vcol on vcol.attrelid = rec.view_id and vcol.attnum = rec.view_column join pg_namespace sch on sch.oid = tbl.relnamespace join pks_fks using (resorigtbl, resorigcol) group by sch.nspname, tbl.relname, rec.view_schema, rec.view_name, pks_fks.conname, pks_fks.contype; ```

Results

| # | table_schema | table_name | view_schema | view_name | constraint_name | constraint_type | column_dependencies | |----|--------------|-------------------------|--------------------|-----------------------------|-----------------------------------|-----------------|------------------------------------------| | 1 | pg_catalog | pg_authid | pg_catalog | pg_group | pg_authid_oid_index | p | "{""(oid,grosysid)""}" | | 2 | pg_catalog | pg_authid | pg_catalog | pg_roles | pg_authid_oid_index | p | "{""(oid,oid)""}" | | 3 | pg_catalog | pg_authid | pg_catalog | pg_shadow | pg_authid_oid_index | p | "{""(oid,usesysid)""}" | | 4 | pg_catalog | pg_authid | pg_catalog | pg_user | pg_authid_oid_index | p | "{""(oid,usesysid)""}" | | 5 | pg_catalog | pg_class | pg_catalog | pg_stat_all_indexes | pg_class_oid_index | p | "{""(oid,relid)"",""(oid,indexrelid)""}" | | 6 | pg_catalog | pg_class | pg_catalog | pg_stat_all_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 7 | pg_catalog | pg_class | pg_catalog | pg_stat_sys_indexes | pg_class_oid_index | p | "{""(oid,indexrelid)"",""(oid,relid)""}" | | 8 | pg_catalog | pg_class | pg_catalog | pg_stat_sys_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 9 | pg_catalog | pg_class | pg_catalog | pg_stat_user_indexes | pg_class_oid_index | p | "{""(oid,relid)"",""(oid,indexrelid)""}" | | 10 | pg_catalog | pg_class | pg_catalog | pg_stat_user_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 11 | pg_catalog | pg_class | pg_catalog | pg_stat_xact_all_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 12 | pg_catalog | pg_class | pg_catalog | pg_stat_xact_sys_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 13 | pg_catalog | pg_class | pg_catalog | pg_stat_xact_user_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 14 | pg_catalog | pg_class | pg_catalog | pg_statio_all_indexes | pg_class_oid_index | p | "{""(oid,indexrelid)"",""(oid,relid)""}" | | 15 | pg_catalog | pg_class | pg_catalog | pg_statio_all_sequences | pg_class_oid_index | p | "{""(oid,relid)""}" | | 16 | pg_catalog | pg_class | pg_catalog | pg_statio_all_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 17 | pg_catalog | pg_class | pg_catalog | pg_statio_sys_indexes | pg_class_oid_index | p | "{""(oid,relid)"",""(oid,indexrelid)""}" | | 18 | pg_catalog | pg_class | pg_catalog | pg_statio_sys_sequences | pg_class_oid_index | p | "{""(oid,relid)""}" | | 19 | pg_catalog | pg_class | pg_catalog | pg_statio_sys_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 20 | pg_catalog | pg_class | pg_catalog | pg_statio_user_indexes | pg_class_oid_index | p | "{""(oid,relid)"",""(oid,indexrelid)""}" | | 21 | pg_catalog | pg_class | pg_catalog | pg_statio_user_sequences | pg_class_oid_index | p | "{""(oid,relid)""}" | | 22 | pg_catalog | pg_class | pg_catalog | pg_statio_user_tables | pg_class_oid_index | p | "{""(oid,relid)""}" | | 23 | pg_catalog | pg_database | pg_catalog | pg_stat_database_conflicts | pg_database_oid_index | p | "{""(oid,datid)""}" | | 24 | pg_catalog | pg_foreign_data_wrapper | information_schema | _pg_foreign_data_wrappers | pg_foreign_data_wrapper_oid_index | p | "{""(oid,oid)""}" | | 25 | pg_catalog | pg_foreign_server | information_schema | _pg_foreign_servers | pg_foreign_server_oid_index | p | "{""(oid,oid)""}" | | 26 | pg_catalog | pg_foreign_server | pg_catalog | pg_user_mappings | pg_foreign_server_oid_index | p | "{""(oid,srvid)""}" | | 27 | pg_catalog | pg_proc | pg_catalog | pg_stat_user_functions | pg_proc_oid_index | p | "{""(oid,funcid)""}" | | 28 | pg_catalog | pg_proc | pg_catalog | pg_stat_xact_user_functions | pg_proc_oid_index | p | "{""(oid,funcid)""}" | | 29 | pg_catalog | pg_statistic | pg_catalog | pg_stats | pg_statistic_relid_att_inh_index | p | "{""(stainherit,inherited)""}" | | 30 | pg_catalog | pg_subscription | pg_catalog | pg_stat_subscription | pg_subscription_oid_index | p | "{""(oid,subid)""}" | | 31 | pg_catalog | pg_user_mapping | information_schema | _pg_user_mappings | pg_user_mapping_oid_index | p | "{""(oid,oid)""}" | | 32 | pg_catalog | pg_user_mapping | pg_catalog | pg_user_mappings | pg_user_mapping_oid_index | p | "{""(oid,umid)""}" |

PS: I'm not sure If I did this right but I created

tracer :: a -> [Text]
tracer obj = do
  s <- unsafePerformIO (Gs.whoCreated obj)
  return (show s::Text)

and

  toJSON (SQL.ResultError resultError) = JSON.object [
    "code"    .=  InternalErrorCode00,
    "messages" .= tracer resultError,
    "message" .=  (show resultError :: Text),
    "details" .=  JSON.Null,
    "hint"    .= JSON.Null]

and that resulted in

{"code":"PGRSTX00","details":"[]","hint":null,"message":"RowError 0 8 (ValueError \"End of input\")","messages":["\"Main.main (main/Main.hs:(18,1)-(21,52))\"","\"PostgREST.CLI.main (src/PostgREST/CLI.hs:(36,1)-(51,72))\"","\"PostgREST.CLI.main.\\\\ (src/PostgREST/CLI.hs:(46,19)-(51,71))\"","\"PostgREST.App.run (src/PostgREST/App.hs:(67,1)-(90,50))\"","\"PostgREST.Workers.connectionWorker (src/PostgREST/Workers.hs:(72,1)-(111,60))\"","\"PostgREST.Workers.connectionWorker.runExclusively (src/PostgREST/Workers.hs:(77,5)-(80,54))\"","\"PostgREST.Workers.connectionWorker.work (src/PostgREST/Workers.hs:(81,5)-(111,60))\"","\"PostgREST.Workers.loadSchemaCache (src/PostgREST/Workers.hs:(166,1)-(195,21))\"","\"PostgREST.AppState.usePool (src/PostgREST/AppState.hs:109:1-40)\"","\"Hasql.Pool.use (library/Hasql/Pool.hs:(95,1)-(149,48))\"","\"Hasql.Pool.use.onConn (library/Hasql/Pool.hs:(128,5)-(149,48))\"","\"Hasql.Private.Session.run (library/Hasql/Private/Session.hs:(24,1)-(26,30))\"","\"PostgREST.Workers.loadSchemaCache.transaction (src/PostgREST/Workers.hs:169:9-103)\"","\"PostgREST.SchemaCache.querySchemaCache (src/PostgREST/SchemaCache.hs:(89,1)-(105,5))\"","\"PostgREST.SchemaCache.allTables (src/PostgREST/SchemaCache.hs:(438,1)-(441,35))\"","\"PostgREST.SchemaCache.decodeTables (src/PostgREST/SchemaCache.hs:(143,1)-(163,40))\"","\"Hasql.Private.Decoders.rowList (library/Hasql/Private/Decoders.hs:91:1-33)\"","\"Hasql.Private.Decoders.foldrRows (library/Hasql/Private/Decoders.hs:66:1-84)\"","\"Hasql.Private.Decoders.Result.foldr (library/Hasql/Private/Decoders/Result.hs:(202,3)-(219,33))\""]}

laurenceisla commented 1 year ago

OK, both the Debug.Trace and your tracer confirm that the error is in the decodeTables function, which parses the tablesSqlQuery query. RowError 0 8 means the conflict is in the first row (0+1) and ninth column (8+1), so, maybe there's a problem with cols_agg.columns?

Try querying the tablesSqlQuery function and check if that returns the 9 columns (the last one should be called columns). As a reference, in my case, the generated SQL for that function is the following (you may need to change ANY('{api}') to ANY('{public}')):

Query

```sql WITH columns AS ( SELECT nc.nspname::name AS table_schema, c.relname::name AS table_name, a.attname::name AS column_name, d.description AS description, pg_get_expr(ad.adbin, ad.adrelid)::text AS column_default, not (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS is_nullable, CASE WHEN t.typtype = 'd' THEN CASE WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) ELSE format_type(a.atttypid, a.atttypmod) END ELSE CASE WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) ELSE format_type(a.atttypid, a.atttypmod) END END::text AS data_type, information_schema._pg_char_max_length( information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*) )::integer AS character_maximum_length, COALESCE(bt.typname, t.typname)::name AS udt_name, a.attnum::integer AS position FROM pg_attribute a LEFT JOIN pg_description AS d ON d.objoid = a.attrelid and d.objsubid = a.attnum LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd' AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f', 'm', 'p') AND nc.nspname = ANY('{api}') ), columns_agg AS ( SELECT DISTINCT info.table_schema AS table_schema, info.table_name AS table_name, array_agg(row( info.column_name, info.description, info.is_nullable::boolean, info.data_type, info.character_maximum_length, info.column_default, coalesce(enum_info.vals, '{}')) order by info.position) as columns FROM columns info LEFT OUTER JOIN ( SELECT n.nspname AS s, t.typname AS n, array_agg(e.enumlabel ORDER BY e.enumsortorder) AS vals FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid JOIN pg_namespace n ON n.oid = t.typnamespace GROUP BY s,n ) AS enum_info ON info.udt_name = enum_info.n WHERE info.table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY info.table_schema, info.table_name ), tbl_constraints AS ( SELECT c.conname::name AS constraint_name, nr.nspname::name AS table_schema, r.relname::name AS table_name FROM pg_namespace nc JOIN pg_constraint c ON nc.oid = c.connamespace JOIN pg_class r ON c.conrelid = r.oid JOIN pg_namespace nr ON nr.oid = r.relnamespace WHERE r.relkind IN ('r', 'p') AND NOT pg_is_other_temp_schema(nr.oid) AND c.contype = 'p' ), key_col_usage AS ( SELECT ss.conname::name AS constraint_name, ss.nr_nspname::name AS table_schema, ss.relname::name AS table_name, a.attname::name AS column_name, (ss.x).n::integer AS ordinal_position, CASE WHEN ss.contype = 'f' THEN information_schema._pg_index_position(ss.conindid, ss.confkey[(ss.x).n]) ELSE NULL::integer END::integer AS position_in_unique_constraint FROM pg_attribute a JOIN ( SELECT r.oid AS roid, r.relname, r.relowner, nc.nspname AS nc_nspname, nr.nspname AS nr_nspname, c.oid AS coid, c.conname, c.contype, c.conindid, c.confkey, information_schema._pg_expandarray(c.conkey) AS x FROM pg_namespace nr JOIN pg_class r ON nr.oid = r.relnamespace JOIN pg_constraint c ON r.oid = c.conrelid JOIN pg_namespace nc ON c.connamespace = nc.oid WHERE c.contype in ('p', 'u') AND r.relkind IN ('r', 'p') AND NOT pg_is_other_temp_schema(nr.oid) ) ss ON a.attrelid = ss.roid AND a.attnum = (ss.x).x WHERE NOT a.attisdropped ), tbl_pk_cols AS ( SELECT key_col_usage.table_schema, key_col_usage.table_name, array_agg(key_col_usage.column_name) as pk_cols FROM tbl_constraints JOIN key_col_usage ON key_col_usage.table_name = tbl_constraints.table_name AND key_col_usage.table_schema = tbl_constraints.table_schema AND key_col_usage.constraint_name = tbl_constraints.constraint_name WHERE key_col_usage.table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY key_col_usage.table_schema, key_col_usage.table_name ) SELECT n.nspname AS table_schema, c.relname AS table_name, d.description AS table_description, c.relkind IN ('v','m') as is_view, ( c.relkind IN ('r','p') OR ( c.relkind in ('v','f') -- The function `pg_relation_is_updateable` returns a bitmask where 8 -- corresponds to `1 << CMD_INSERT` in the PostgreSQL source code, i.e. -- it's possible to insert into the relation. AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 8) = 8 ) ) AS insertable, ( c.relkind IN ('r','p') OR ( c.relkind in ('v','f') -- CMD_UPDATE AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 4) = 4 ) ) AS updatable, ( c.relkind IN ('r','p') OR ( c.relkind in ('v','f') -- CMD_DELETE AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 16) = 16 ) ) AS deletable, coalesce(tpks.pk_cols, '{}') as pk_cols, coalesce(cols_agg.columns, '{}') as columns FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_description d on d.objoid = c.oid and d.objsubid = 0 LEFT JOIN tbl_pk_cols tpks ON n.nspname = tpks.table_schema AND c.relname = tpks.table_name LEFT JOIN columns_agg cols_agg ON n.nspname = cols_agg.table_schema AND c.relname = cols_agg.table_name WHERE c.relkind IN ('v','r','m','f','p') AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND not c.relispartition ORDER BY table_schema, table_name ```

Result

| table\_schema | table\_name | table\_description | is\_view | insertable | updatable | deletable | pk\_cols | columns | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | api | actors | null | false | true | true | true | {id} | {"$id,,f,integer,,,{}$","$first\_name,,t,text,,,{}$","$last\_name,,t,text,,,{}$"} | | api | competitions | null | false | true | true | true | {id} | {"$id,,f,integer,,,{}$","$name,,t,text,,,{}$","$year,,t,integer,,,{}$"} | | api | directors | null | false | true | true | true | {id} | {"$id,,f,integer,,,{}$","$first\_name,,t,text,,,{}$","$last\_name,,t,text,,,{}$"} | | api | films | null | false | true | true | true | {id} | {"$id,,f,integer,,,{}$","$director\_id,,t,integer,,,{}$","$title,,t,text,,,{}$","$year,,t,integer,,,{}$","$rating,,t,numeric,,,{}$","$language,,t,text,,,{}$"} | ... etc.

Majed6 commented 1 year ago

Here is the result using Datagrip:

Result

| table\_schema | table\_name | table\_description | is\_view | insertable | updatable | deletable | pk\_cols | columns | | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | :--- | | public | arrival\_duration\_rating | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$rating,,t,numeric,,,{}$","$ratings\_count,,t,bigint,,,{}$"} | | public | conformity\_rating | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$rating,,t,numeric,,,{}$","$ratings\_count,,t,bigint,,,{}$"} | | public | lime\_answers | null | false | true | true | true | {qid,code,language,scale\_id} | {"$qid,,f,integer,,,{}$","$code,,f,\\"character varying\\",5,,{}$","$answer,,f,text,,,{}$","$sortorder,,f,integer,,,{}$","$assessment\_value,,f,integer,,0,{}$","$language,,f,\\"character varying\\",20,\\"'en'::character varying\\",{}$","$scale\_id,,f,integer,,0,{}$"} | | public | lime\_assessments | null | false | true | true | true | {id,language} | {"$id,,f,integer,,\\"nextval\('lime\_assessments\_id\_seq'::regclass$\\",{}\)","$sid,,f,integer,,0,{}$","$scope,,f,\\"character varying\\",5,,{}$","$gid,,f,integer,,0,{}$","$name,,f,text,,,{}$","$minimum,,f,\\"character varying\\",50,,{}$","$maximum,,f,\\"character varying\\",50,,{}$","$message,,f,text,,,{}$","$language,,f,\\"character varying\\",20,\\"'en'::character varying\\",{}$"} | | public | lime\_asset\_version | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_asset\_version\_id\_seq'::regclass$\\",{}\)","$path,,f,text,,,{}$","$version,,f,integer,,,{}$"} | | public | lime\_boxes | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_boxes\_id\_seq'::regclass$\\",{}\)","$position,,t,integer,,,{}$","$url,,f,text,,,{}$","$title,,f,text,,,{}$","$ico,,t,\\"character varying\\",255,,{}$","$desc,,f,text,,,{}$","$page,,f,text,,,{}$","$usergroup,,f,integer,,,{}$"} | | public | lime\_conditions | null | false | true | true | true | {cid} | {"$cid,,f,integer,,\\"nextval\('lime\_conditions\_cid\_seq'::regclass$\\",{}\)","$qid,,f,integer,,0,{}$","$cqid,,f,integer,,0,{}$","$cfieldname,,f,\\"character varying\\",50,\\"''::character varying\\",{}$","$method,,f,\\"character varying\\",5,\\"''::character varying\\",{}$","$value,,f,\\"character varying\\",255,\\"''::character varying\\",{}$","$scenario,,f,integer,,1,{}$"} | | public | lime\_defaultvalues | null | false | true | true | true | {qid,specialtype,language,scale\_id,sqid} | {"$qid,,f,integer,,0,{}$","$scale\_id,,f,integer,,0,{}$","$sqid,,f,integer,,0,{}$","$language,,f,\\"character varying\\",20,,{}$","$specialtype,,f,\\"character varying\\",20,\\"''::character varying\\",{}$","$defaultvalue,,t,text,,,{}$"} | | public | lime\_expression\_errors | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_expression\_errors\_id\_seq'::regclass$\\",{}\)","$errortime,,t,\\"character varying\\",50,,{}$","$sid,,t,integer,,,{}$","$gid,,t,integer,,,{}$","$qid,,t,integer,,,{}$","$gseq,,t,integer,,,{}$","$qseq,,t,integer,,,{}$","$type,,t,\\"character varying\\",50,,{}$","$eqn,,t,text,,,{}$","$prettyprint,,t,text,,,{}$"} | | public | lime\_failed\_login\_attempts | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_failed\_login\_attempts\_id\_seq'::regclass$\\",{}\)","$ip,,f,\\"character varying\\",40,,{}$","$last\_attempt,,f,\\"character varying\\",20,,{}$","$number\_attempts,,f,integer,,,{}$"} | | public | lime\_groups | null | false | true | true | true | {gid,language} | {"$gid,,f,integer,,\\"nextval\('lime\_groups\_gid\_seq'::regclass$\\",{}\)","$sid,,f,integer,,0,{}$","$group\_name,,f,\\"character varying\\",100,\\"''::character varying\\",{}$","$group\_order,,f,integer,,0,{}$","$description,,t,text,,,{}$","$language,,f,\\"character varying\\",20,\\"'en'::character varying\\",{}$","$randomization\_group,,f,\\"character varying\\",20,\\"''::character varying\\",{}$","$grelevance,,t,text,,,{}$"} | | public | lime\_labels | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_labels\_id\_seq'::regclass$\\",{}\)","$lid,,f,integer,,0,{}$","$code,,f,\\"character varying\\",5,\\"''::character varying\\",{}$","$title,,t,text,,,{}$","$sortorder,,f,integer,,,{}$","$language,,f,\\"character varying\\",20,\\"'en'::character varying\\",{}$","$assessment\_value,,f,integer,,0,{}$"} | | public | lime\_labelsets | null | false | true | true | true | {lid} | {"$lid,,f,integer,,\\"nextval\('lime\_labelsets\_lid\_seq'::regclass$\\",{}\)","$label\_name,,f,\\"character varying\\",100,\\"''::character varying\\",{}$","$languages,,t,\\"character varying\\",200,\\"'en'::character varying\\",{}$"} | | public | lime\_map\_tutorial\_users | null | false | true | true | true | {uid,tid} | {"$tid,,f,integer,,,{}$","$uid,,f,integer,,,{}$","$taken,,t,integer,,1,{}$"} | | public | lime\_notifications | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_notifications\_id\_seq'::regclass$\\",{}\)","$entity,,f,\\"character varying\\",15,,{}$","$entity\_id,,f,integer,,,{}$","$title,,f,\\"character varying\\",255,,{}$","$message,,f,text,,,{}$","$status,,f,\\"character varying\\",15,\\"'new'::character varying\\",{}$","$importance,,f,integer,,1,{}$","$display\_class,,t,\\"character varying\\",31,\\"'default'::character varying\\",{}$","$hash,,t,\\"character varying\\",64,,{}$","$created,,t,\\"timestamp without time zone\\",,,{}$","$first\_read,,t,\\"timestamp without time zone\\",,,{}$"} | | public | lime\_old\_survey\_292968\_20210217100653 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_292968\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$292968X18X134,,t,text,,,{}$","$292968X18X131,,t,\\"character varying\\",5,,{}$","$292968X18X132,,t,\\"character varying\\",1,,{}$","$292968X18X133,,t,\\"character varying\\",1,,{}$","$292968X19X135,,t,\\"character varying\\",1,,{}$","$292968X17X128,,t,text,,,{}$","$292968X17X129,,t,text,,,{}$","$292968X17X130,,t,text,,,{}$"} | | public | lime\_old\_survey\_292968\_20210223102649 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_292968\_id\_seq1'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$292968X18X134,,t,text,,,{}$","$292968X18X131,,t,\\"character varying\\",5,,{}$","$292968X18X132,,t,\\"character varying\\",1,,{}$","$292968X18X133,,t,\\"character varying\\",1,,{}$","$292968X19X135,,t,\\"character varying\\",1,,{}$","$292968X17X128,,t,text,,,{}$","$292968X17X129,,t,text,,,{}$","$292968X17X130,,t,text,,,{}$"} | | public | lime\_old\_survey\_295487\_20210223102157 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_295487\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$295487X7X102,,t,\\"character varying\\",5,,{}$","$295487X9X103,,t,text,,,{}$","$295487X9X104,,t,text,,,{}$","$295487X9X105,,t,text,,,{}$"} | | public | lime\_old\_survey\_492454\_20210209111954 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_492454\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$492454X20X136,,t,\\"character varying\\",1,,{}$","$492454X20X137,,t,\\"character varying\\",5,,{}$"} | | public | lime\_old\_survey\_958391\_20191213212708 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_958391\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$958391X4X8,,t,text,,,{}$"} | | public | lime\_old\_survey\_997118\_20210218051819 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_997118\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$997118X12X113,,t,\\"character varying\\",1,,{}$","$997118X12X114,,t,\\"character varying\\",1,,{}$","$997118X13X115,,t,\\"character varying\\",1,,{}$","$997118X11X110,,t,text,,,{}$","$997118X11X111,,t,text,,,{}$","$997118X11X112,,t,text,,,{}$"} | | public | lime\_old\_survey\_997118\_20210223104318 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_997118\_id\_seq1'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$997118X12X113,,t,\\"character varying\\",5,,{}$","$997118X12X114,,t,\\"character varying\\",1,,{}$","$997118X13X115,,t,\\"character varying\\",1,,{}$","$997118X11X110,,t,text,,,{}$","$997118X11X111,,t,text,,,{}$","$997118X11X112,,t,text,,,{}$"} | | public | lime\_old\_survey\_997118\_timings\_20210223104318 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_997118\_timings\_id\_seq'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$997118X12time,,t,\\"double precision\\",,,{}$","$997118X12X113time,,t,\\"double precision\\",,,{}$","$997118X12X114time,,t,\\"double precision\\",,,{}$","$997118X13time,,t,\\"double precision\\",,,{}$","$997118X13X115time,,t,\\"double precision\\",,,{}$","$997118X11time,,t,\\"double precision\\",,,{}$","$997118X11X110time,,t,\\"double precision\\",,,{}$","$997118X11X111time,,t,\\"double precision\\",,,{}$","$997118X11X112time,,t,\\"double precision\\",,,{}$"} | | public | lime\_old\_tokens\_292968\_20210217100653 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_292968\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$"} | | public | lime\_participant\_attribute | null | false | true | true | true | {attribute\_id,participant\_id} | {"$participant\_id,,f,\\"character varying\\",50,,{}$","$attribute\_id,,f,integer,,,{}$","$value,,f,text,,,{}$"} | | public | lime\_participant\_attribute\_names | null | false | true | true | true | {attribute\_id,attribute\_type} | {"$attribute\_id,,f,integer,,\\"nextval\('lime\_participant\_attribute\_names\_attribute\_id\_seq'::regclass$\\",{}\)","$attribute\_type,,f,\\"character varying\\",4,,{}$","$defaultname,,f,\\"character varying\\",255,,{}$","$visible,,f,\\"character varying\\",5,,{}$"} | | public | lime\_participant\_attribute\_names\_lang | null | false | true | true | true | {attribute\_id,lang} | {"$attribute\_id,,f,integer,,,{}$","$attribute\_name,,f,\\"character varying\\",255,,{}$","$lang,,f,\\"character varying\\",20,,{}$"} | | public | lime\_participant\_attribute\_values | null | false | true | true | true | {value\_id} | {"$value\_id,,f,integer,,\\"nextval\('lime\_participant\_attribute\_values\_value\_id\_seq'::regclass$\\",{}\)","$attribute\_id,,f,integer,,,{}$","$value,,f,text,,,{}$"} | | public | lime\_participant\_shares | null | false | true | true | true | {share\_uid,participant\_id} | {"$participant\_id,,f,\\"character varying\\",50,,{}$","$share\_uid,,f,integer,,,{}$","$date\_added,,f,\\"timestamp without time zone\\",,,{}$","$can\_edit,,f,\\"character varying\\",5,,{}$"} | | public | lime\_participants | null | false | true | true | true | {participant\_id} | {"$participant\_id,,f,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$language,,t,\\"character varying\\",40,,{}$","$blacklisted,,f,\\"character varying\\",1,,{}$","$owner\_uid,,f,integer,,,{}$","$created\_by,,f,integer,,,{}$","$created,,t,\\"timestamp without time zone\\",,,{}$","$modified,,t,\\"timestamp without time zone\\",,,{}$"} | | public | lime\_permissions | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_permissions\_id\_seq'::regclass$\\",{}\)","$entity,,f,\\"character varying\\",50,,{}$","$entity\_id,,f,integer,,,{}$","$uid,,f,integer,,,{}$","$permission,,f,\\"character varying\\",100,,{}$","$create\_p,,f,integer,,0,{}$","$read\_p,,f,integer,,0,{}$","$update\_p,,f,integer,,0,{}$","$delete\_p,,f,integer,,0,{}$","$import\_p,,f,integer,,0,{}$","$export\_p,,f,integer,,0,{}$"} | | public | lime\_plugin\_settings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_plugin\_settings\_id\_seq'::regclass$\\",{}\)","$plugin\_id,,f,integer,,,{}$","$model,,t,\\"character varying\\",50,,{}$","$model\_id,,t,integer,,,{}$","$key,,f,\\"character varying\\",50,,{}$","$value,,t,text,,,{}$"} | | public | lime\_plugins | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_plugins\_id\_seq'::regclass$\\",{}\)","$name,,f,\\"character varying\\",50,,{}$","$active,,f,integer,,0,{}$","$version,,t,\\"character varying\\",32,,{}$"} | | public | lime\_question\_attributes | null | false | true | true | true | {qaid} | {"$qaid,,f,integer,,\\"nextval\('lime\_question\_attributes\_qaid\_seq'::regclass$\\",{}\)","$qid,,f,integer,,0,{}$","$attribute,,t,\\"character varying\\",50,,{}$","$value,,t,text,,,{}$","$language,,t,\\"character varying\\",20,,{}$"} | | public | lime\_questions | null | false | true | true | true | {language,qid} | {"$qid,,f,integer,,\\"nextval\('lime\_questions\_qid\_seq'::regclass$\\",{}\)","$parent\_qid,,f,integer,,0,{}$","$sid,,f,integer,,0,{}$","$gid,,f,integer,,0,{}$","$type,,f,\\"character varying\\",1,\\"'T'::character varying\\",{}$","$title,,f,\\"character varying\\",20,\\"''::character varying\\",{}$","$question,,f,text,,,{}$","$preg,,t,text,,,{}$","$help,,t,text,,,{}$","$other,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$mandatory,,t,\\"character varying\\",1,,{}$","$question\_order,,f,integer,,,{}$","$language,,f,\\"character varying\\",20,\\"'en'::character varying\\",{}$","$scale\_id,,f,integer,,0,{}$","$same\_default,,f,integer,,0,{}$","$relevance,,t,text,,,{}$","$modulename,,t,\\"character varying\\",255,,{}$"} | | public | lime\_quota | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_quota\_id\_seq'::regclass$\\",{}\)","$sid,,t,integer,,,{}$","$name,,t,\\"character varying\\",255,,{}$","$qlimit,,t,integer,,,{}$","$action,,t,integer,,,{}$","$active,,f,integer,,1,{}$","$autoload\_url,,f,integer,,0,{}$"} | | public | lime\_quota\_languagesettings | null | false | true | true | true | {quotals\_id} | {"$quotals\_id,,f,integer,,\\"nextval\('lime\_quota\_languagesettings\_quotals\_id\_seq'::regclass$\\",{}\)","$quotals\_quota\_id,,f,integer,,0,{}$","$quotals\_language,,f,\\"character varying\\",45,\\"'en'::character varying\\",{}$","$quotals\_name,,t,\\"character varying\\",255,,{}$","$quotals\_message,,f,text,,,{}$","$quotals\_url,,t,\\"character varying\\",255,,{}$","$quotals\_urldescrip,,t,\\"character varying\\",255,,{}$"} | | public | lime\_quota\_members | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_quota\_members\_id\_seq'::regclass$\\",{}\)","$sid,,t,integer,,,{}$","$qid,,t,integer,,,{}$","$quota\_id,,t,integer,,,{}$","$code,,t,\\"character varying\\",11,,{}$"} | | public | lime\_saved\_control | null | false | true | true | true | {scid} | {"$scid,,f,integer,,\\"nextval\('lime\_saved\_control\_scid\_seq'::regclass$\\",{}\)","$sid,,f,integer,,0,{}$","$srid,,f,integer,,0,{}$","$identifier,,f,text,,,{}$","$access\_code,,f,text,,,{}$","$email,,t,\\"character varying\\",192,,{}$","$ip,,f,text,,,{}$","$saved\_thisstep,,f,text,,,{}$","$status,,f,\\"character varying\\",1,\\"''::character varying\\",{}$","$saved\_date,,f,\\"timestamp without time zone\\",,,{}$","$refurl,,t,text,,,{}$"} | | public | lime\_sessions | null | false | true | true | true | {id} | {"$id,,f,\\"character varying\\",32,,{}$","$expire,,t,integer,,,{}$","$data,,t,bytea,,,{}$"} | | public | lime\_settings\_global | null | false | true | true | true | {stg\_name} | {"$stg\_name,,f,\\"character varying\\",50,\\"''::character varying\\",{}$","$stg\_value,,f,text,,,{}$"} | | public | lime\_settings\_user | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_settings\_user\_id\_seq'::regclass$\\",{}\)","$uid,,f,integer,,,{}$","$entity,,t,\\"character varying\\",15,,{}$","$entity\_id,,t,\\"character varying\\",31,,{}$","$stg\_name,,f,\\"character varying\\",63,,{}$","$stg\_value,,t,text,,,{}$"} | | public | lime\_survey\_292968 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_292968\_id\_seq2'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$ipaddr,,t,text,,,{}$","$refurl,,t,text,,,{}$","$292968X18X134,,t,text,,,{}$","$292968X18X131,,t,\\"character varying\\",5,,{}$","$292968X18X132,,t,\\"character varying\\",1,,{}$","$292968X18X133,,t,\\"character varying\\",1,,{}$","$292968X19X135,,t,\\"character varying\\",1,,{}$","$292968X17X128,,t,text,,,{}$","$292968X17X129,,t,text,,,{}$","$292968X17X130,,t,text,,,{}$"} | | public | lime\_survey\_292968\_timings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_292968\_timings\_id\_seq'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$292968X18time,,t,\\"double precision\\",,,{}$","$292968X18X134time,,t,\\"double precision\\",,,{}$","$292968X18X131time,,t,\\"double precision\\",,,{}$","$292968X18X132time,,t,\\"double precision\\",,,{}$","$292968X18X133time,,t,\\"double precision\\",,,{}$","$292968X19time,,t,\\"double precision\\",,,{}$","$292968X19X135time,,t,\\"double precision\\",,,{}$","$292968X17time,,t,\\"double precision\\",,,{}$","$292968X17X128time,,t,\\"double precision\\",,,{}$","$292968X17X129time,,t,\\"double precision\\",,,{}$","$292968X17X130time,,t,\\"double precision\\",,,{}$"} | | public | lime\_survey\_295487 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_295487\_id\_seq1'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$ipaddr,,t,text,,,{}$","$refurl,,t,text,,,{}$","$295487X7X102,,t,\\"character varying\\",5,,{}$","$295487X9X103,,t,text,,,{}$","$295487X9X104,,t,text,,,{}$","$295487X9X105,,t,text,,,{}$"} | | public | lime\_survey\_295487\_timings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_295487\_timings\_id\_seq'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$295487X7time,,t,\\"double precision\\",,,{}$","$295487X7X102time,,t,\\"double precision\\",,,{}$","$295487X9time,,t,\\"double precision\\",,,{}$","$295487X9X103time,,t,\\"double precision\\",,,{}$","$295487X9X104time,,t,\\"double precision\\",,,{}$","$295487X9X105time,,t,\\"double precision\\",,,{}$"} | | public | lime\_survey\_343279 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_343279\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$ipaddr,,t,text,,,{}$","$refurl,,t,text,,,{}$","$343279X1X1SQ001,,t,\\"character varying\\",5,,{}$","$343279X1X2,,t,text,,,{}$","$343279X2X4,,t,\\"character varying\\",5,,{}$","$343279X3X5,,t,text,,,{}$","$343279X3X6,,t,text,,,{}$","$343279X3X7,,t,text,,,{}$"} | | public | lime\_survey\_343279\_timings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_343279\_timings\_id\_seq'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$343279X1time,,t,\\"double precision\\",,,{}$","$343279X1X1time,,t,\\"double precision\\",,,{}$","$343279X1X2time,,t,\\"double precision\\",,,{}$","$343279X2time,,t,\\"double precision\\",,,{}$","$343279X2X4time,,t,\\"double precision\\",,,{}$","$343279X3time,,t,\\"double precision\\",,,{}$","$343279X3X5time,,t,\\"double precision\\",,,{}$","$343279X3X6time,,t,\\"double precision\\",,,{}$","$343279X3X7time,,t,\\"double precision\\",,,{}$"} | | public | lime\_survey\_626376 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_626376\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$ipaddr,,t,text,,,{}$","$refurl,,t,text,,,{}$","$626376X15X123SQ001,,t,\\"character varying\\",5,,{}$","$626376X15X123SQ002,,t,\\"character varying\\",5,,{}$","$626376X15X123SQ003,,t,\\"character varying\\",5,,{}$","$626376X16X127,,t,\\"character varying\\",1,,{}$","$626376X14X116,,t,text,,,{}$","$626376X14X117,,t,text,,,{}$","$626376X14X118,,t,text,,,{}$"} | | public | lime\_survey\_626376\_timings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_626376\_timings\_id\_seq'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$626376X15time,,t,\\"double precision\\",,,{}$","$626376X15X123time,,t,\\"double precision\\",,,{}$","$626376X16time,,t,\\"double precision\\",,,{}$","$626376X16X127time,,t,\\"double precision\\",,,{}$","$626376X14time,,t,\\"double precision\\",,,{}$","$626376X14X116time,,t,\\"double precision\\",,,{}$","$626376X14X117time,,t,\\"double precision\\",,,{}$","$626376X14X118time,,t,\\"double precision\\",,,{}$"} | | public | lime\_survey\_843921 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_843921\_id\_seq'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$843921X5X9SQ002,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ003,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ004,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ005,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ006,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ007,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ008,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ009,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ010,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ011,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ012,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ013,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ014,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ015,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ016,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ017,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ018,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ019,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ020,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ021,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ022,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ023,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ024,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ025,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ026,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ027,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ028,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ029,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ030,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ031,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ032,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ033,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ034,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ035,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ036,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ037,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ038,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ039,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ040,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ041,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ042,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ043,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ044,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ045,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ046,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ047,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ048,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ049,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ050,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ051,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ052,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ053,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ054,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ055,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ056,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ057,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ058,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ059,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ060,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ061,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ062,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ063,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ064,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ065,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ066,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ067,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ068,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ069,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ070,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ071,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ072,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ073,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ074,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ075,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ076,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ077,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ078,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ079,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ080,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ081,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ082,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ083,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ084,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ085,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ086,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ087,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ088,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ089,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ090,,t,\\"character varying\\",5,,{}$","$843921X5X9SQ091,,t,\\"character varying\\",5,,{}$"} | | public | lime\_survey\_997118 | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_997118\_id\_seq2'::regclass$\\",{}\)","$token,,t,\\"character varying\\",35,,{}$","$submitdate,,t,\\"timestamp without time zone\\",,,{}$","$lastpage,,t,integer,,,{}$","$startlanguage,,f,\\"character varying\\",20,,{}$","$seed,,t,\\"character varying\\",31,,{}$","$startdate,,f,\\"timestamp without time zone\\",,,{}$","$datestamp,,f,\\"timestamp without time zone\\",,,{}$","$ipaddr,,t,text,,,{}$","$refurl,,t,text,,,{}$","$997118X12X113,,t,\\"character varying\\",5,,{}$","$997118X12X114,,t,\\"character varying\\",1,,{}$","$997118X13X115,,t,\\"character varying\\",1,,{}$","$997118X11X110,,t,text,,,{}$","$997118X11X111,,t,text,,,{}$","$997118X11X112,,t,text,,,{}$"} | | public | lime\_survey\_997118\_timings | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_997118\_timings\_id\_seq1'::regclass$\\",{}\)","$interviewtime,,t,\\"double precision\\",,,{}$","$997118X12time,,t,\\"double precision\\",,,{}$","$997118X12X113time,,t,\\"double precision\\",,,{}$","$997118X12X114time,,t,\\"double precision\\",,,{}$","$997118X13time,,t,\\"double precision\\",,,{}$","$997118X13X115time,,t,\\"double precision\\",,,{}$","$997118X11time,,t,\\"double precision\\",,,{}$","$997118X11X110time,,t,\\"double precision\\",,,{}$","$997118X11X111time,,t,\\"double precision\\",,,{}$","$997118X11X112time,,t,\\"double precision\\",,,{}$"} | | public | lime\_survey\_links | null | false | true | true | true | {participant\_id,token\_id,survey\_id} | {"$participant\_id,,f,\\"character varying\\",50,,{}$","$token\_id,,f,integer,,,{}$","$survey\_id,,f,integer,,,{}$","$date\_created,,t,\\"timestamp without time zone\\",,,{}$","$date\_invited,,t,\\"timestamp without time zone\\",,,{}$","$date\_completed,,t,\\"timestamp without time zone\\",,,{}$"} | | public | lime\_survey\_url\_parameters | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_survey\_url\_parameters\_id\_seq'::regclass$\\",{}\)","$sid,,f,integer,,,{}$","$parameter,,f,\\"character varying\\",50,,{}$","$targetqid,,t,integer,,,{}$","$targetsqid,,t,integer,,,{}$"} | | public | lime\_surveymenu | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_surveymenu\_id\_seq'::regclass$\\",{}\)","$parent\_id,,t,integer,,,{}$","$survey\_id,,t,integer,,,{}$","$user\_id,,t,integer,,,{}$","$name,,t,\\"character varying\\",128,,{}$","$ordering,,t,integer,,0,{}$","$level,,t,integer,,0,{}$","$title,,f,\\"character varying\\",168,\\"''::character varying\\",{}$","$position,,f,\\"character varying\\",192,\\"'side'::character varying\\",{}$","$description,,t,text,,,{}$","$showincollapse,,t,integer,,0,{}$","$active,,f,integer,,0,{}$","$changed\_at,,t,\\"timestamp without time zone\\",,,{}$","$changed\_by,,f,integer,,0,{}$","$created\_at,,t,\\"timestamp without time zone\\",,,{}$","$created\_by,,f,integer,,0,{}$"} | | public | lime\_surveymenu\_entries | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_surveymenu\_entries\_id\_seq'::regclass$\\",{}\)","$menu\_id,,t,integer,,,{}$","$user\_id,,t,integer,,,{}$","$ordering,,t,integer,,0,{}$","$name,,t,\\"character varying\\",168,\\"''::character varying\\",{}$","$title,,f,\\"character varying\\",168,\\"''::character varying\\",{}$","$menu\_title,,f,\\"character varying\\",168,\\"''::character varying\\",{}$","$menu\_description,,t,text,,,{}$","$menu\_icon,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$menu\_icon\_type,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$menu\_class,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$menu\_link,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$action,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$template,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$partial,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$classes,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$permission,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$permission\_grade,,t,\\"character varying\\",192,,{}$","$data,,t,text,,,{}$","$getdatamethod,,f,\\"character varying\\",192,\\"''::character varying\\",{}$","$language,,f,\\"character varying\\",32,\\"'en-GB'::character varying\\",{}$","$showincollapse,,t,integer,,0,{}$","$active,,f,integer,,0,{}$","$changed\_at,,t,\\"timestamp without time zone\\",,,{}$","$changed\_by,,f,integer,,0,{}$","$created\_at,,t,\\"timestamp without time zone\\",,,{}$","$created\_by,,f,integer,,0,{}$"} | | public | lime\_surveys | null | false | true | true | true | {sid} | {"$sid,,f,integer,,,{}$","$owner\_id,,f,integer,,,{}$","$gsid,,t,integer,,1,{}$","$admin,,t,\\"character varying\\",50,,{}$","$active,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$expires,,t,\\"timestamp without time zone\\",,,{}$","$startdate,,t,\\"timestamp without time zone\\",,,{}$","$adminemail,,t,\\"character varying\\",254,,{}$","$anonymized,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$faxto,,t,\\"character varying\\",20,,{}$","$format,,t,\\"character varying\\",1,,{}$","$savetimings,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$template,,t,\\"character varying\\",100,\\"'default'::character varying\\",{}$","$language,,t,\\"character varying\\",50,,{}$","$additional\_languages,,t,\\"character varying\\",255,,{}$","$datestamp,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$usecookie,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$allowregister,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$allowsave,,f,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$autonumber\_start,,f,integer,,0,{}$","$autoredirect,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$allowprev,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$printanswers,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$ipaddr,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$refurl,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$datecreated,,t,\\"timestamp without time zone\\",,,{}$","$showsurveypolicynotice,,t,integer,,0,{}$","$publicstatistics,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$publicgraphs,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$listpublic,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$htmlemail,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$sendconfirmation,,f,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$tokenanswerspersistence,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$assessments,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$usecaptcha,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$usetokens,,f,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$bounce\_email,,t,\\"character varying\\",254,,{}$","$attributedescriptions,,t,text,,,{}$","$emailresponseto,,t,text,,,{}$","$emailnotificationto,,t,text,,,{}$","$tokenlength,,f,integer,,15,{}$","$showxquestions,,t,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$showgroupinfo,,t,\\"character varying\\",1,\\"'B'::character varying\\",{}$","$shownoanswer,,t,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$showqnumcode,,t,\\"character varying\\",1,\\"'X'::character varying\\",{}$","$bouncetime,,t,integer,,,{}$","$bounceprocessing,,t,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$bounceaccounttype,,t,\\"character varying\\",4,,{}$","$bounceaccounthost,,t,\\"character varying\\",200,,{}$","$bounceaccountpass,,t,\\"character varying\\",100,,{}$","$bounceaccountencryption,,t,\\"character varying\\",3,,{}$","$bounceaccountuser,,t,\\"character varying\\",200,,{}$","$showwelcome,,t,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$showprogress,,t,\\"character varying\\",1,\\"'Y'::character varying\\",{}$","$questionindex,,f,integer,,0,{}$","$navigationdelay,,f,integer,,0,{}$","$nokeyboard,,t,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$alloweditaftercompletion,,t,\\"character varying\\",1,\\"'N'::character varying\\",{}$","$googleanalyticsstyle,,t,\\"character varying\\",1,,{}$","$googleanalyticsapikey,,t,\\"character varying\\",25,,{}$"} | | public | lime\_surveys\_groups | null | false | true | true | true | {gsid} | {"$gsid,,f,integer,,\\"nextval\('lime\_surveys\_groups\_gsid\_seq'::regclass$\\",{}\)","$name,,f,\\"character varying\\",45,,{}$","$title,,t,\\"character varying\\",100,,{}$","$template,,t,\\"character varying\\",128,\\"'default'::character varying\\",{}$","$description,,t,text,,,{}$","$sortorder,,f,integer,,,{}$","$owner\_id,,t,integer,,,{}$","$parent\_id,,t,integer,,,{}$","$created,,t,\\"timestamp without time zone\\",,,{}$","$modified,,t,\\"timestamp without time zone\\",,,{}$","$created\_by,,f,integer,,,{}$"} | | public | lime\_surveys\_languagesettings | null | false | true | true | true | {surveyls\_survey\_id,surveyls\_language} | {"$surveyls\_survey\_id,,f,integer,,,{}$","$surveyls\_language,,f,\\"character varying\\",45,\\"'en'::character varying\\",{}$","$surveyls\_title,,f,\\"character varying\\",200,,{}$","$surveyls\_description,,t,text,,,{}$","$surveyls\_welcometext,,t,text,,,{}$","$surveyls\_endtext,,t,text,,,{}$","$surveyls\_policy\_notice,,t,text,,,{}$","$surveyls\_policy\_error,,t,text,,,{}$","$surveyls\_policy\_notice\_label,,t,\\"character varying\\",192,,{}$","$surveyls\_url,,t,text,,,{}$","$surveyls\_urldescription,,t,\\"character varying\\",255,,{}$","$surveyls\_email\_invite\_subj,,t,\\"character varying\\",255,,{}$","$surveyls\_email\_invite,,t,text,,,{}$","$surveyls\_email\_remind\_subj,,t,\\"character varying\\",255,,{}$","$surveyls\_email\_remind,,t,text,,,{}$","$surveyls\_email\_register\_subj,,t,\\"character varying\\",255,,{}$","$surveyls\_email\_register,,t,text,,,{}$","$surveyls\_email\_confirm\_subj,,t,\\"character varying\\",255,,{}$","$surveyls\_email\_confirm,,t,text,,,{}$","$surveyls\_dateformat,,f,integer,,1,{}$","$surveyls\_attributecaptions,,t,text,,,{}$","$email\_admin\_notification\_subj,,t,\\"character varying\\",255,,{}$","$email\_admin\_notification,,t,text,,,{}$","$email\_admin\_responses\_subj,,t,\\"character varying\\",255,,{}$","$email\_admin\_responses,,t,text,,,{}$","$surveyls\_numberformat,,f,integer,,0,{}$","$attachments,,t,text,,,{}$"} | | public | lime\_template\_configuration | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_template\_configuration\_id\_seq'::regclass$\\",{}\)","$template\_name,,f,\\"character varying\\",150,,{}$","$sid,,t,integer,,,{}$","$gsid,,t,integer,,,{}$","$uid,,t,integer,,,{}$","$files\_css,,t,text,,,{}$","$files\_js,,t,text,,,{}$","$files\_print\_css,,t,text,,,{}$","$options,,t,text,,,{}$","$cssframework\_name,,t,\\"character varying\\",45,,{}$","$cssframework\_css,,t,text,,,{}$","$cssframework\_js,,t,text,,,{}$","$packages\_to\_load,,t,text,,,{}$","$packages\_ltr,,t,text,,,{}$","$packages\_rtl,,t,text,,,{}$"} | | public | lime\_templates | null | false | true | true | true | {id} | {"$id,,f,integer,,\\"nextval\('lime\_templates\_id\_seq'::regclass$\\",{}\)","$name,,f,\\"character varying\\",150,,{}$","$folder,,t,\\"character varying\\",45,,{}$","$title,,f,\\"character varying\\",100,,{}$","$creation\_date,,t,\\"timestamp without time zone\\",,,{}$","$author,,t,\\"character varying\\",150,,{}$","$author\_email,,t,\\"character varying\\",255,,{}$","$author\_url,,t,\\"character varying\\",255,,{}$","$copyright,,t,text,,,{}$","$license,,t,text,,,{}$","$version,,t,\\"character varying\\",45,,{}$","$api\_version,,f,\\"character varying\\",45,,{}$","$view\_folder,,f,\\"character varying\\",45,,{}$","$files\_folder,,f,\\"character varying\\",45,,{}$","$description,,t,text,,,{}$","$last\_update,,t,\\"timestamp without time zone\\",,,{}$","$owner\_id,,t,integer,,,{}$","$extends,,t,\\"character varying\\",150,,{}$"} | | public | lime\_tokens\_292968 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_292968\_tid\_seq1'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$","$attribute\_4,,t,text,,,{}$"} | | public | lime\_tokens\_295487 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_295487\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$"} | | public | lime\_tokens\_343279 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_343279\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$"} | | public | lime\_tokens\_459112 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_459112\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$"} | | public | lime\_tokens\_626376 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_626376\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$"} | | public | lime\_tokens\_997118 | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tokens\_997118\_tid\_seq'::regclass$\\",{}\)","$participant\_id,,t,\\"character varying\\",50,,{}$","$firstname,,t,\\"character varying\\",150,,{}$","$lastname,,t,\\"character varying\\",150,,{}$","$email,,t,text,,,{}$","$emailstatus,,t,text,,,{}$","$token,,t,\\"character varying\\",35,,{}$","$language,,t,\\"character varying\\",25,,{}$","$blacklisted,,t,\\"character varying\\",17,,{}$","$sent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindersent,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$remindercount,,t,integer,,0,{}$","$completed,,t,\\"character varying\\",17,\\"'N'::character varying\\",{}$","$usesleft,,t,integer,,1,{}$","$validfrom,,t,\\"timestamp without time zone\\",,,{}$","$validuntil,,t,\\"timestamp without time zone\\",,,{}$","$mpid,,t,integer,,,{}$","$attribute\_1,,t,text,,,{}$","$attribute\_2,,t,text,,,{}$","$attribute\_3,,t,text,,,{}$"} | | public | lime\_tutorial\_entries | null | false | true | true | true | {teid} | {"$teid,,f,integer,,\\"nextval\('lime\_tutorial\_entries\_teid\_seq'::regclass$\\",{}\)","$ordering,,t,integer,,,{}$","$title,,t,text,,,{}$","$content,,t,text,,,{}$","$settings,,t,text,,,{}$"} | | public | lime\_tutorial\_entry\_relation | null | false | true | true | true | {teid,tid} | {"$teid,,f,integer,,,{}$","$tid,,f,integer,,,{}$","$uid,,t,integer,,,{}$","$sid,,t,integer,,,{}$"} | | public | lime\_tutorials | null | false | true | true | true | {tid} | {"$tid,,f,integer,,\\"nextval\('lime\_tutorials\_tid\_seq'::regclass$\\",{}\)","$name,,t,\\"character varying\\",128,,{}$","$title,,t,\\"character varying\\",192,,{}$","$icon,,t,\\"character varying\\",64,,{}$","$description,,t,text,,,{}$","$active,,t,integer,,0,{}$","$settings,,t,text,,,{}$","$permission,,f,\\"character varying\\",128,,{}$","$permission\_grade,,f,\\"character varying\\",128,,{}$"} | | public | lime\_user\_groups | null | false | true | true | true | {ugid} | {"$ugid,,f,integer,,\\"nextval\('lime\_user\_groups\_ugid\_seq'::regclass$\\",{}\)","$name,,f,\\"character varying\\",20,,{}$","$description,,f,text,,,{}$","$owner\_id,,f,integer,,,{}$"} | | public | lime\_user\_in\_groups | null | false | true | true | true | {ugid,uid} | {"$ugid,,f,integer,,,{}$","$uid,,f,integer,,,{}$"} | | public | lime\_users | null | false | true | true | true | {uid} | {"$uid,,f,integer,,\\"nextval\('lime\_users\_uid\_seq'::regclass$\\",{}\)","$users\_name,,f,\\"character varying\\",64,\\"''::character varying\\",{}$","$password,,f,text,,,{}$","$full\_name,,f,\\"character varying\\",50,,{}$","$parent\_id,,f,integer,,,{}$","$lang,,t,\\"character varying\\",20,,{}$","$email,,t,\\"character varying\\",192,,{}$","$htmleditormode,,t,\\"character varying\\",7,\\"'default'::character varying\\",{}$","$templateeditormode,,f,\\"character varying\\",7,\\"'default'::character varying\\",{}$","$questionselectormode,,f,\\"character varying\\",7,\\"'default'::character varying\\",{}$","$one\_time\_pw,,t,text,,,{}$","$dateformat,,f,integer,,1,{}$","$created,,t,\\"timestamp without time zone\\",,,{}$","$modified,,t,\\"timestamp without time zone\\",,,{}$"} | | public | pro\_ratings | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$rating,,t,numeric,,,{}$","$ratings\_count,,t,bigint,,,{}$"} | | public | pro\_ratings\_v2 | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$arrival\_duration\_rating,,t,numeric,,,{}$","$arrival\_duration\_ratings\_count,,t,bigint,,,{}$","$recommendability\_rating,,t,numeric,,,{}$","$recommendability\_ratings\_count,,t,bigint,,,{}$","$responsiveness\_rating,,t,numeric,,,{}$","$responsiveness\_ratings\_count,,t,bigint,,,{}$","$conformity\_rating,,t,numeric,,,{}$","$conformity\_ratings\_count,,t,bigint,,,{}$"} | | public | recommendability\_rating | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$rating,,t,numeric,,,{}$","$ratings\_count,,t,bigint,,,{}$"} | | public | responsiveness\_rating | null | true | false | false | false | | {"$pro\_id,,t,text,,,{}$","$rating,,t,numeric,,,{}$","$ratings\_count,,t,bigint,,,{}$"} |

laurenceisla commented 1 year ago

I fail to see the problem in your results... perhaps it's another query or maybe Hasql has a problem with the size of the column's result (a wild guess). Just to confirm, older PostgREST versions do not work either, right? Could you try doing a backup of your database and restoring it in another machine (e. g. your local system) to see if it also fails there?

wolfgangwalther commented 1 year ago

Just noting: The error is actually thrown somewhere in here: https://hackage.haskell.org/package/binary-parser-0.5.7.2/docs/src/BinaryParser.html - we can tell from the "End of Input" string.

Here is the result using Datagrip:

The result looks like the pk_cols column has some NULL values. Is that correct? According to our sourcecode, I would expect empty arrays {} in those empty cells. Can you confirm in your SQL query, that those values are indeed NULL?

That could certainly break the arrayColumn decoder, because that expects non-nullable columns:

https://github.com/PostgREST/postgrest/blob/a41380b96595499e976f160c03d11a8bfec6a303/src/PostgREST/DbStructure.hs#L935

It would be helpful indeed, if you could try to reproduce this on a dump of the database - and then start removing pieces of your schema until the error disappears. Maybe it's related to some special object you have in your schema, that we're not parsing correctly from the catalogs. I would start removing arrival_duration_rating and conformity_rating and then see whether that changes the error message?

Majed6 commented 1 year ago

perhaps it's another query

The tracer showed that the decodeTables is the reason.

or maybe Hasql has a problem with the size of the column's result (a wild guess).

That's not the case because slicing columns https://github.com/PostgREST/postgrest/blob/0fbb116dd27af860a3095a6a2499c76fbae3ab6b/src/PostgREST/SchemaCache.hs#L500-L507 to one by

                                     (array_agg(
                                             row (info.column_name, info.description, info.is_nullable::boolean, info.data_type, info.character_maximum_length, info.column_default, coalesce(enum_info.vals, '{}'))
                                             order by info.position))[1:1] as columns

still produces the same exception.

Just to confirm, older PostgREST versions do not work either, right?

I just tried docker versions:

postgrest/postgrest:v10.0.0
postgrest/postgrest:v9.0.1
postgrest/postgrest:v9.0.0

and none of them work. Older versions don't support postgres 14.5 so I didn't try them.

Could you try doing a backup of your database and restoring it in another machine (e. g. your local system) to see if it also fails there?

With a pg_dumpversion it doesn't fail sadly. A pg_dumpall will take quite a while to acquire since it's a multi tenet cluster and exporting everything needs to be approved.

Just noting: The error is actually thrown somewhere in here: https://hackage.haskell.org/package/binary-parser-0.5.7.2/docs/src/BinaryParser.html - we can tell from the "End of Input" string.

I can confirm this from my testing too.

Here is the result using Datagrip:

The result looks like the pk_cols column has some NULL values. Is that correct? According to our sourcecode, I would expect empty arrays {} in those empty cells. Can you confirm in your SQL query, that those values are indeed NULL?

I'm so sorry they must've been removed during my markdown conversion. They are indeed {} and NOT NULL .

That could certainly break the arrayColumn decoder, because that expects non-nullable columns:

https://github.com/PostgREST/postgrest/blob/a41380b96595499e976f160c03d11a8bfec6a303/src/PostgREST/DbStructure.hs#L935

They are {} . Sorry :p .

It would be helpful indeed, if you could try to reproduce this on a dump of the database - and then start removing pieces of your schema until the error disappears. Maybe it's related to some special object you have in your schema, that we're not parsing correctly from the catalogs. I would start removing arrival_duration_rating and conformity_rating and then see whether that changes the error message?

I have been doing the pieces removal with the script below (apologies for the mess in advance :p) :

Script (Connection.settings are not real values)

```haskell {-# LANGUAGE DeriveAnyClass #-} {-# LANGUAGE DeriveGeneric #-} {-# LANGUAGE FlexibleContexts #-} {-# LANGUAGE MultiParamTypeClasses #-} {-# LANGUAGE NamedFieldPuns #-} {-# LANGUAGE ScopedTypeVariables #-} {-# LANGUAGE TypeSynonymInstances #-} {-# LANGUAGE PackageImports #-} {-# LANGUAGE QuasiQuotes #-} {-# OPTIONS_GHC -Wno-unused-matches #-} {-# OPTIONS_GHC -Wno-unused-local-binds #-} import Prelude import Data.Functor.Contravariant import qualified Hasql.Session as Session import qualified Hasql.Connection as Connection import Text.InterpolatedString.Perl6 (q) import "unordered-containers" Data.HashMap.Strict import qualified Hasql.Decoders as HD import qualified Hasql.Encoders as HE import qualified Hasql.Statement as SQL import qualified Data.Aeson as JSON import qualified Data.ByteString.Char8 as BS import Protolude main :: IO () main = do Right connection <- Connection.acquire connectionSettings result <- Session.run allTables connection Prelude.print result where connectionSettings = Connection.settings (BS.pack "192.168.68.115") 5432 (BS.pack "postgres") (BS.pack "") (BS.pack "postgres") allTables :: Session.Session TablesMap allTables = do Session.statement (1,2) tablesSqlQuery tablesSqlQuery :: SQL.Statement (Int64, Int64) TablesMap tablesSqlQuery = SQL.Statement sql encoder decoder True where sql = [q|WITH columns AS (SELECT nc.nspname::name AS table_schema, c.relname::name AS table_name, a.attname::name AS column_name, d.description AS description, pg_get_expr(ad.adbin, ad.adrelid)::text AS column_default, not (a.attnotnull OR t.typtype = 'd' AND t.typnotnull) AS is_nullable, CASE WHEN t.typtype = 'd' THEN CASE WHEN bt.typelem <> 0::oid AND bt.typlen = (-1) THEN 'ARRAY'::text WHEN nbt.nspname = 'pg_catalog'::name THEN format_type(t.typbasetype, NULL::integer) ELSE format_type(a.atttypid, a.atttypmod) END ELSE CASE WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text WHEN nt.nspname = 'pg_catalog'::name THEN format_type(a.atttypid, NULL::integer) ELSE format_type(a.atttypid, a.atttypmod) END END::text AS data_type, information_schema._pg_char_max_length(information_schema._pg_truetypid(a.*, t.*), information_schema._pg_truetypmod(a.*, t.*))::integer AS character_maximum_length, COALESCE(bt.typname, t.typname)::name AS udt_name, a.attnum::integer AS position FROM pg_attribute a LEFT JOIN pg_description AS d ON d.objoid = a.attrelid and d.objsubid = a.attnum LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum JOIN (pg_class c JOIN pg_namespace nc ON c.relnamespace = nc.oid) ON a.attrelid = c.oid JOIN (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) ON a.atttypid = t.oid LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) ON t.typtype = 'd' AND t.typbasetype = bt.oid LEFT JOIN (pg_collation co JOIN pg_namespace nco ON co.collnamespace = nco.oid) ON a.attcollation = co.oid AND (nco.nspname <> 'pg_catalog'::name OR co.collname <> 'default'::name) WHERE NOT pg_is_other_temp_schema(nc.oid) AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f', 'm', 'p')), columns_agg AS (SELECT DISTINCT info.table_schema AS table_schema, info.table_name AS table_name, (array_agg( row (info.column_name, info.description, info.is_nullable::boolean, info.data_type, info.character_maximum_length, info.column_default, coalesce(enum_info.vals, '{}')) order by info.position))[1:1] as columns FROM columns info LEFT OUTER JOIN (SELECT n.nspname AS s, t.typname AS n, array_agg(e.enumlabel ORDER BY e.enumsortorder) AS vals FROM pg_type t JOIN pg_enum e ON t.oid = e.enumtypid JOIN pg_namespace n ON n.oid = t.typnamespace GROUP BY s, n) AS enum_info ON info.udt_name = enum_info.n WHERE info.table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY info.table_schema, info.table_name), tbl_constraints AS (SELECT c.conname::name AS constraint_name, nr.nspname::name AS table_schema, r.relname::name AS table_name FROM pg_namespace nc JOIN pg_constraint c ON nc.oid = c.connamespace JOIN pg_class r ON c.conrelid = r.oid JOIN pg_namespace nr ON nr.oid = r.relnamespace WHERE r.relkind IN ('r', 'p') AND NOT pg_is_other_temp_schema(nr.oid) AND c.contype = 'p'), key_col_usage AS (SELECT ss.conname::name AS constraint_name, ss.nr_nspname::name AS table_schema, ss.relname::name AS table_name, a.attname::name AS column_name, (ss.x).n::integer AS ordinal_position, CASE WHEN ss.contype = 'f' THEN information_schema._pg_index_position(ss.conindid, ss.confkey[(ss.x).n]) ELSE NULL::integer END::integer AS position_in_unique_constraint FROM pg_attribute a JOIN (SELECT r.oid AS roid, r.relname, r.relowner, nc.nspname AS nc_nspname, nr.nspname AS nr_nspname, c.oid AS coid, c.conname, c.contype, c.conindid, c.confkey, information_schema._pg_expandarray(c.conkey) AS x FROM pg_namespace nr JOIN pg_class r ON nr.oid = r.relnamespace JOIN pg_constraint c ON r.oid = c.conrelid JOIN pg_namespace nc ON c.connamespace = nc.oid WHERE c.contype in ('p', 'u') AND r.relkind IN ('r', 'p') AND NOT pg_is_other_temp_schema(nr.oid)) ss ON a.attrelid = ss.roid AND a.attnum = (ss.x).x WHERE NOT a.attisdropped), tbl_pk_cols AS (SELECT key_col_usage.table_schema, key_col_usage.table_name, array_agg(key_col_usage.column_name) as pk_cols FROM tbl_constraints JOIN key_col_usage ON key_col_usage.table_name = tbl_constraints.table_name AND key_col_usage.table_schema = tbl_constraints.table_schema AND key_col_usage.constraint_name = tbl_constraints.constraint_name WHERE key_col_usage.table_schema NOT IN ('pg_catalog', 'information_schema') GROUP BY key_col_usage.table_schema, key_col_usage.table_name) SELECT n.nspname AS table_schema, c.relname AS table_name, d.description AS table_description, c.relkind IN ('v', 'm') as is_view, (c.relkind IN ('r', 'p') OR (c.relkind in ('v', 'f') AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 8) = 8)) AS insertable, (c.relkind IN ('r', 'p') OR (c.relkind in ('v', 'f') AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 4) = 4)) AS updatable, (c.relkind IN ('r', 'p') OR (c.relkind in ('v', 'f') AND (pg_relation_is_updatable(c.oid::regclass, TRUE) & 16) = 16)) AS deletable, coalesce(tpks.pk_cols, '{}') as pk_cols, coalesce(cols_agg.columns, '{}') as columns FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_description d on d.objoid = c.oid and d.objsubid = 0 LEFT JOIN tbl_pk_cols tpks ON n.nspname = tpks.table_schema AND c.relname = tpks.table_name LEFT JOIN columns_agg cols_agg ON n.nspname = cols_agg.table_schema AND c.relname = cols_agg.table_name WHERE c.relkind IN ('v', 'r', 'm', 'f', 'p') AND n.nspname NOT IN ('pg_catalog', 'information_schema') AND not c.relispartition ORDER BY table_schema, table_name limit 1; |] encoder = (fst >$< HE.param (HE.nonNullable HE.int8)) <> (snd >$< HE.param (HE.nonNullable HE.int8)) decoder = decodeTables decodeTables :: HD.Result TablesMap decodeTables = fromList . Protolude.map (\tbl@Table{tableSchema, tableName} -> (QualifiedIdentifier tableSchema tableName, tbl)) <$> HD.rowList tblRow where tblRow = Table <$> column HD.text <*> column HD.text <*> nullableColumn HD.text <*> column HD.bool <*> column HD.bool <*> column HD.bool <*> column HD.bool <*> arrayColumn HD.text <*> compositeArrayColumn (Column <$> compositeField HD.text <*> nullableCompositeField HD.text <*> compositeField HD.bool <*> compositeField HD.text <*> nullableCompositeField HD.int4 <*> nullableCompositeField HD.text <*> compositeFieldArray HD.text) compositeArrayColumn :: HD.Composite a -> HD.Row [a] compositeArrayColumn = arrayColumn . HD.composite compositeField :: HD.Value a -> HD.Composite a compositeField = HD.field . HD.nonNullable nullableCompositeField :: HD.Value a -> HD.Composite (Maybe a) nullableCompositeField = HD.field . HD.nullable compositeFieldArray :: HD.Value a -> HD.Composite [a] compositeFieldArray = HD.field . HD.nonNullable . HD.listArray . HD.nonNullable column :: HD.Value a -> HD.Row a column = HD.column . HD.nonNullable nullableColumn :: HD.Value a -> HD.Row (Maybe a) nullableColumn = HD.column . HD.nullable arrayColumn :: HD.Value a -> HD.Row [a] arrayColumn = column . HD.listArray . HD.nonNullable data QualifiedIdentifier = QualifiedIdentifier { qiSchema :: Schema , qiName :: TableName } deriving (Eq, Ord, Generic, JSON.ToJSON, JSON.ToJSONKey,Show) instance Hashable QualifiedIdentifier type Schema = Text type TableName = Text type FieldName = Text data Table = Table { tableSchema :: Schema , tableName :: TableName , tableDescription :: Maybe Text , tableIsView :: Bool , tableInsertable :: Bool , tableUpdatable :: Bool , tableDeletable :: Bool , tablePKCols :: [FieldName] , tableColumns :: [Column] } deriving (Show, Ord, Generic, JSON.ToJSON) instance Eq Table where Table{tableSchema=s1,tableName=n1} == Table{tableSchema=s2,tableName=n2} = s1 == s2 && n1 == n2 data Column = Column { colName :: FieldName , colDescription :: Maybe Text , colNullable :: Bool , colType :: Text , colMaxLen :: Maybe Int32 , colDefault :: Maybe Text , colEnum :: [Text] } deriving (Eq, Show, Ord, Generic, JSON.ToJSON) type TablesMap = HashMap QualifiedIdentifier Table ```

Notice the slicing of columns and the limiting to one record at the end. It only works when , tableColumns :: [Column] is made into a , tableColumns :: Text along with the mapping.

wolfgangwalther commented 1 year ago

With a pg_dump version it doesn't fail sadly.

Ok, that's very valuable information. In that case it's not a problem with any of the database objects specifically.

It's more likely to be a problem with some server, database or user settings.

Please check the following:

With which encoding is the database created? psql \l
Are there any special settings on the failing database? Run the query below to see all non-default values. It would be best to log in as the postgrest authenticator user, to get the user-specific settings, too.
```
SELECT name, setting, source FROM pg_settings WHERE source !='default';`
```

Maybe any settings stand out - and changing them in your restored backup produces the same failure?

It only works when , tableColumns :: [Column] is made into a , tableColumns :: Text along with the mapping.

Ah, interesting, too. That suggests the problem is inside the column decoding. Can you try to remove fields of Column 1-by-1 and see which one is causing the error? Of course, you'll need to remove it from the decoder and from the columns_agg CTE, too.

Majed6 commented 1 year ago

Thank you all for your support and prompt responses. I'm putting this side issue on pause until I get back from my leave in a month. I wrote a reply to your questions but I feared a bit of inaccuracy in the results, so I decided not to submit the reply not to waste anyone's time.

I greatly appreciate you @laurenceisla and @wolfgangwalther for your interest in the side issue. I also appreciate all of PostgREST community for this amazing product that has served us for years.

Looking forward to getting back to this side issue with more details and replies to your requests and questions. (Fingers crossed, it fixes itself somehow xD ) .

I'm back. DB admins deleted the DB on my leave because it was "consuming connections". I'll get things back on track and restore the staging DB. Thanks again for your interest.

During my talk with the db admins heimdall, sprung up as a culprit for the data corruption during transit. In order to save your time I'll work with heimdall team to make sure it is not the culprit. If it is, I'll post what I gather in a separate reply.

PostgREST / postgrest