Why standby restores some WALs many times from archive?

[Victor]

Hi,

I have postgres 9.4 standby with archive-based replication (via 
restore_command).
PostgreSQL 9.4.12 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 
4.9.2-10) 4.9.2, 64-bit

One day I noticed strange behavior in postgres logs: the same WAL has been 
restored many times:

"restored log file ""000200064AA8005D"" from archive",""
"unexpected pageaddr 64A81/F900 in log segment 000200064AA8005E, 
offset 0",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005D"" from archive",""
"restored log file ""000200064AA8005E"" from archive",""
"restored log file ""000200064AA8005F"" from archive",""
"restored log file ""000200064AA80060"" from archive",""


I enabled logging of successful execution in 'restore_command' script and 
realized that it's really happens: some WALs are recovering again and again. 
And this is always connected with 'unexpected pageaddr' log message.

Some statistics of how many 'same WALs' have been restored in 3 minutes:
[vyagofarov@myserver:~] $  cut -f 2 -d '-' wals_restored.log | uniq -c -d | 
sort -r
  9  000200064AA900A4
  9  000200064AA8005D
  8  000200064AAA0044
  7  000200064AAA00AB
  6  000200064AAA00ED
  3  000200064AA700E8
  2  000200064AA8003D
  2  000200064AA80033
  2  000200064AA80028
  2  000200064AA80018
  2  000200064AA80004
  2  000200064AA700FD

Is this a bug?


-- 
Best regards,
Victor Yagofarov

Re: Drop Default Privileges?

[Victor Yegorov]

вт, 19 июн. 2018 г. в 21:32, Pavan Teja :

> In order to remove the default privileges for any particular user/role, we
> should know the list of default privileges.
>

`psql` allows you to check default privileges via `\ddp` command (per
database). You can start `psql` with `-E` switch that will show you
internal queries used for displaying this information, or you can `\set
ECHO_HIDDEN on` with the same effect.

Also, you can do `pg_dumpall -s | grep -E 'DEFAULT PRIVILEGE|\\connect' and
it'll produce a list of all entries for all databases, along with database
name.


-- 
Victor Yegorov

Re: Drop Default Privileges?

[Victor Yegorov]

вт, 19 июн. 2018 г. в 18:20, Louis Battuello :

> Is it possible to drop default privileges?
>
> I’m attempting to run a pg_restore into an RDS instance, which doesn’t
> have a “postgres” user.
>
> I encounter many messages like so:
>
> ALTER DEFAULT PRIVILEGES...
>
> pg_restore: [archiver (db)] Error from TOC entry 10182; 826 253752252 DEFAULT
> ACL DEFAULT PRIVILEGES FOR TABLES postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  role
> "postgres" does not exist
>
> Command was: ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA
> abc_schema REVOKE ALL ON TABLES  FROM PUBLIC;
>
> I’d like to remove these default privileges on the source database to
> avoid this error message, but I can’t find the syntax in the documentation
> (or if it’s possible). I only see GRANT/REVOKE options.
>

If you see `ALTER DEFAULT PRIVILEGES … REVOKE …` and want to undo it, you
will have to GRANT corresponding privilege.


-- 
Victor Yegorov

JSONB arrays

[Victor Noagbodji]

Hey people,

I ended up with the following to check (or similar to return) the intersection 
of two list of values on a JSONB object:

array_length(
array(
select jsonb_array_elements_text(col1)
intersect
select jsonb_array_elements_text(col2)
), 1) > 0

Is there a better way?

Thanks

Re: POLL: Adding transaction status to default psql prompt

[Victor Yegorov]

чт, 6 февр. 2020 г. в 04:55, Vik Fearing :

> Please answer +1 if you want or don't mind seeing transaction status by
> default in psql or -1 if you would prefer to keep the current default.
>


+1

-- 
Victor Yegorov

Enabling extensions on a compiled instance of postgresql 12.1

[Sterpu Victor]

Hello

I compiled from source postgresql 12.1 and all went fine but when I try 
to restore my DB I can see that I have 3 extensions missing: uuid-ossp, 
btree_gist, tablefunc.
I tried to run: CREATE EXTENSION "uuid-ossp"; and the error is ERROR:  
could not open extension control file 
"/usr/local/pgsql_12.1/share/extension/uuid-ossp.control": No such file 
or directory
How do I enable these extensions? I tried to copy the file from my 
previous postgres version but is not compatible.
These were my compiling options:  ./configure --with-ossp-uuid 
--with-uuid=bsd --prefix=/usr/local/pgsql_12.1


Thank you.

Re[2]: Enabling extensions on a compiled instance of postgresql 12.1

[Sterpu Victor]

It works.
Thank you, thank you, thank you.

-- Original Message --
From: "Julien Rouhaud" 
To: "Sterpu Victor" 
Cc: pgsql-gene...@postgresql.org
Sent: 2020-02-13 3:48:08 PM
Subject: Re: Enabling extensions on a compiled instance of postgresql 
12.1



On Thu, Feb 13, 2020 at 01:34:55PM +, Sterpu Victor wrote:

 Hello

 I compiled from source postgresql 12.1 and all went fine but when I 
try to

 restore my DB I can see that I have 3 extensions missing: uuid-ossp,
 btree_gist, tablefunc.
 I tried to run: CREATE EXTENSION "uuid-ossp"; and the error is ERROR: 
 could

 not open extension control file
 "/usr/local/pgsql_12.1/share/extension/uuid-ossp.control": No such 
file or

 directory
 How do I enable these extensions? I tried to copy the file from my 
previous

 postgres version but is not compatible.
 These were my compiling options:  ./configure --with-ossp-uuid
 --with-uuid=bsd --prefix=/usr/local/pgsql_12.1


Did you also do a "make install" under the contrib/ directory?

Query to retrieve the index columns when a function is used.

[Sterpu Victor]

 Hello

I'm testing on Postgresql 12.1 and I have a index like this:
"check_dates_gist" EXCLUDE USING gist (id_test1 WITH =, id_test2 WITH =,
tsrange(valid_from::timestamp without time zone, valid_to::timestamp
without time zone) WITH &&)

When I run this query:
"select pc.relname, pi.indisunique, pi.indisprimary,
array_agg(a.attname) as attname
FROM pg_class pc
JOIN pg_index pi ON pc.oid = pi.indexrelid AND pc.oid IN
(SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test' 
AND pg_class.oid=pg_index.indrelid)

JOIN pg_attribute a ON a.attrelid = pc.oid
GROUP BY pc.relname,pi.indisunique, pi.indisprimary;"
I retrieve the index but there is no detail about the columns valid_from
and valid_to.

How can I retrieve this detail?
Thank you.

--
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus

Re: Performance penalty during logical postgres replication

[Victor Yegorov]

ср, 9 дек. 2020 г. в 10:21, Lars Vonk :

> We are doing a logical postgres replication from Postgres 11 to 12. Our
> database is around 700GB (8 cpu's, 32 GB).
> During the replication process, at some point, we see a huge performance
> penalty on a particular table. This table acts as a queue with lots of
> inserts and deletes happening throughout the day. For most of the time this
> table is empty, but during this performance penalty the number of rows in
> this table grows to 10.000 rows, and processing is not fast enough to empty
> this table. Main reason for this (as far as we see) is that the performance
> of the query for selecting the next row to process drops from < 10MS to
> 400MS. This eventually causes too much cpu load on the Primary and we have
> to cancel the replication process.
>
We already tried the initial load three times, and it consistently fails
> with the same "error". Last try was a per table approach and excluding this
> "queue" table.
> After cancelling the replication the query is fast again and the load on
> the Primary goes back to normal. We see that this happens when replicating
> large tables (> millions of rows). During this performance penalty the
> explain of the query selecting the next row from this table tells us it is
> doing a sequential scan (there is an index but it is not used).
>
> - What could cause this performance penalty?
> - Is this something other people experienced as well during the initial
> load of a logical replication with large tables?
> - We are now thinking of temporarily increasing the number of CPU's and
> RAM for the migration. Would this help in this case?
>

I've seen similar symptoms in cases with (a) home-made queues in the tables
and (b) long transactions.
Unfortunately, queue requires frequent vacuuming to preserve more or less
constant size of the queue and it's indexes.
And long transactions prevent the vacuum from cleaning up the queue.
Initial synchronization phase of the logical replication is in fact such a
transaction.

I would recommend doing the following:
- avoid adding ALL tables to the publication
- instead, split all tables in a batches in such a way, that initial batch
processing takes limited time (say, 15-30 minutes at most)
- of course, this leaves the biggest tables alone — add those one by one to
the publication, preferably at the time slot with minimal load on the queue.
- make sure to catch up on the queue processing and vacuum it between
batches
- on the receiving side, avoid creating indexes on the tables: create just
a necessary PK or UK, wait for the initial load to complete and then add
all the rest ones

As for the queue, PGQ from skytools is using different approach to maintain
queue tables:
- once in a while (2 hours by default) processing is switched to a new
table, tab_1, tab_2, tab_3 are used in a round
- after the switch, any remaining entries can be moved from previous to the
live table (shouldn't be necessary if switch is done properly, although
might be tricky in a presence of a long transactions)
- previous table is TRUNCATEd

In your case, you can do `VACUUM FULL` between replicating each batch of
tables.

-- 
Victor Yegorov

Re: Code of Conduct: Russian Translation for Review

[Victor Yegorov]

сб, 27 февр. 2021 г. в 01:51, Stacey Haysler :

> If you have any comments or suggestions for the translation, please bring
> them to our attention no later than 5:00 PM PST on  Friday, March 5, 2021.
>

Greetings.

I looked through the text and made some comments.


-- 
Victor Yegorov


PostgreSQL Code of Conduct - Russian Translation Feb 26 2021 - review.docx
Description: MS-Word 2007 document

right way of using case-expressions in plpgsql functions

[Victor Dobrovolsky]

I understood from documentation that case expression can be
1) optimized by planner
2) may compute its subexpressions in advance, in case of presence
aggregation functions in them, for example.

The question is - how it is combined with generic prepared plans in
pl/pgsql.
How can I deduct - when using case-expression is "safe" regarding query
goals with parameters in pl/pgsql and when is not.
There are two cases - expression in select list and expression in where
clause.

in where clause:
suppose I have a sql-function like this:

CREATE OR REPLACE FUNCTION nvl_in_where(pf anycompatible, px anycompatible,
py anycompatible)
 RETURNS boolean
 LANGUAGE sql
 STABLE
AS $function$
select (case when px is not null then pf = px
else pf is not distinct from py
end);
$function$
;


and then I use it in some pl/pgsql function:


CREATE OR REPLACE FUNCTION plsql_query_function(in pn numeric )
 RETURNS boolean
 LANGUAGE plpgsql
 STABLE
AS $function$
Declare
  sr record;
Begin

   For sr in Select tbl.p1,tbl.p2
 From tbl
Where
  nvl_in_where(tbl.p1, pn, tbl.p1)
   Loop
 -- do some logic with sr ...
-- ...
 Null;
   end loop;
end;
$function$
;

If execute this query individually with fixed value of parameter $1, the
query plan
would be like

Select tbl.p1,tbl.p2
>From tbl
Where
  tbl.p1 = pn::numeric
;

or

Select tbl.p1,tbl.p2
>From tbl
Where
  tbl.p1 is not distinct from tbl.p1
;

depending if pn is null or  not.

The documentation states that after some executions of such functions the
plan should become generic.
What is a generic plan for such a case and how would it work?
If it is safe to use function like nvl_in_where in where clause of queries
in plpgsql function or,
 maybe, I should only use that query only with the execute statement in
plpgsql?

 in select list:

 suppose, i need something like this :

 select case $1
when '1'::numeric then tbl.p1
when '2'::numeric then tbl.p2
...
end as cresult
 from tbl ...

Can I be sure, that this expression would not be "optimised" in generic
plan just to
select
 tbl.some_lucky_fied
from tbl
Can I use this type of expression in the select list regarding generic plans
or should I prefer dynamic execution  for such type queries in plpgsql?

Thanks in advance.

PS
The question looks like from a novice, and , indeed, I am.

Re: right way of using case-expressions in plpgsql functions

[Victor Dobrovolsky]

First of all, thanks everyone for the answers.

вс, 15 окт. 2023 г. в 20:08, Tom Lane :

> "David G. Johnston"  writes:
> > On Sunday, October 15, 2023, Victor Dobrovolsky 
> > wrote:
> >> select (case when px is not null then pf = px
> >> else pf is not distinct from py
> >> end);
>
> > Every single time this function is called “px is not null” will be
> > evaluated and then one of the two branches will be evaluated.  Nothing
> the
> > optimizer does will change that.  The planner for the function internals
> > does not know whether px will or will not be null on any given
> invocation.
>
> Not necessarily --- I think the SQL-language function will get inlined
> and then there would be opportunity for const-simplification if a
> known value is available for px in the outer function.
>
> At least in the px-not-null case, having "pf = px" rather than an
> impenetrable CASE condition will probably be enough better for
> optimization that the plancache would never choose to switch to a
> generic plan.  However, that might not be true for the other case,
> since we aren't terribly smart about optimizing NOT DISTINCT conditions.
> So the performance you get might well vary depending on which case
> occurs more often during the first few query runs.
>

Ok. I am a "man coming from Oracle-sql" .
The first case is reminiscence of using oracle nvl "sql-function" in form of
Select *
from tbl
Where tbl.somefield = nvl(:parameter, tbl.somefield)
;
In such a case Oracle will produce generic plan like this:
Filter :parameter is not null
  Select * from tbl
  Where tbl.somefield = :parameter -- index scan if possible
Union All
Filter :parameter is null
  Select * from tbl
  Where 1=1  -- table full scan guaranteed
;
Here aligned left Filter conditions assured, that only one branch of union
all will be really taken in each particular execution of query.

Regarding the "pf is not distinct from py" condition - I saw that it was
translated to "not (pf is distinct from py)"
 which is totally correct from a mathematical point of view.
But maybe it would be useful to have an independent translation of the
statement that "pf is identical to pf",
to eliminate the condition totally at last
(don't pay too much attention, this is a superficial newbie opinion, anyway)

In total - should I manually divide these cases in the plpgsql function if
I like to avoid any prepared statements caveats, or should I use
"execute"-statements, if I am lazy enough for that,
or, there is nothing to complain in terms of "generic plan"...

On the whole though, the entire question seems like solving the wrong
> problem.  If you have to resort to this kind of thing to get your
> answers, it's past time to rethink your data representation.
>
Definitely Yes.
But...
My goal is "to translate" some application "as fast as possible", using "as
few structure transformations as possible".
>From that - "short and dirty translation" - point of view - should I prefer
to divide that
$$
Select
case $1
when '1'::numeric then tbl.p1
when '2'::numeric then tbl.p2
...
end as cresult
$$

expression into:
$$
...
if $1 = '1'::numeric then (query_1)
elsif when '2'::numeric then (query_2)
 ...
end if;
...
$$
If I do not want to use an execute statement for that?

Thank you.

Re: right way of using case-expressions in plpgsql functions

[Victor Dobrovolsky]

>Yeah, that would probably be a preferable approach if you're hoping
>for significantly different query plans for the two cases.

Thank you.
My goal is to find out some basic rules that could help me to navigate
issues like this.
Regarding generalized plans in general, and the use of the case-expressions
in particular.


пн, 16 окт. 2023 г. в 00:15, Tom Lane :

> Victor Dobrovolsky  writes:
> > From that - "short and dirty translation" - point of view - should I
> prefer
> > to divide that
> > $$
> > Select
> > case $1
> > when '1'::numeric then tbl.p1
> > when '2'::numeric then tbl.p2
> > ...
> > end as cresult
> > $$
>
> > expression into:
> > $$
> > ...
> > if $1 = '1'::numeric then (query_1)
> > elsif when '2'::numeric then (query_2)
> >  ...
> > end if;
> > ...
> > $$
>
> Yeah, that would probably be a preferable approach if you're hoping
> for significantly different query plans for the two cases.  PG does
> not have the sort of run-time plan choice mechanism that you're
> describing for Oracle.
>
> regards, tom lane
>

Re: right way of using case-expressions in plpgsql functions

[Victor Dobrovolsky]

Thank you. I'll take it.

пн, 16 окт. 2023 г. в 00:20, Ron :

> On 10/15/23 11:19, Victor Dobrovolsky wrote:
>
> [snip]
>
> The documentation states that after some executions of such functions the
> plan should become generic.
> What is a generic plan for such a case and how would it work?
>
>
> It's highly dependent on the query
>
> When I see this happen (after we notice that a procedure starts taking a
> *long* time), the query planner flips from a custom plan to a generic
> plan after about the fifth execution in a session of a function/procedure.
>
> This will make it calculate the plan every time:
> set plan_cache_mode = force_custom_plan;
>
> --
> Born in Arizona, moved to Babylonia.
>

scalar plpgsql functions and their stability flags

[Victor Dobrovolsky]

Good day experts...

Question on scalar plpgsql functions stability flags (immutable, stable)
regarding how it works in sql queries.

It is clear that for immutable/stable functions with constant parameters,
 query planner could/should calculate value in a parse time and use it
directly in query, or at least once per query.

But it is unclear for me what exactly should/can happens when parameters
are bounded not to constant values but to query fields.
In such a case there could be some caching mechanics involved for
parameters combinations and result values.
Like building a hash table for that or something similar.

Can someone give me guidance on this matter.
What limits the usefulness of such a mechanism, if it exists.

Thank you.

Unexpected results from CALL and AUTOCOMMIT=off

[Victor Yegorov]

Greetings.

I am observing the following results on PostgreSQL 15.7
First, setup:

create table t_test(x bigint);
insert into t_test values(0);

create or replace function f_get_x()
returns bigint
language plpgsql
stable
as $function$
declare
l_result bigint;
begin
select x into l_result from t_test;
--raise notice 'f_get_x() >> x=%', l_result;
--raise notice 'f_get_x() >> xact=%', txid_current_if_assigned();
return l_result;
end;
$function$;

create or replace procedure f_print_x(x bigint)
language plpgsql
as $procedure$
begin
raise notice 'f_print_x() >> x=%', x;
--raise notice 'f_print_x() >> xact=%', txid_current_if_assigned();
end;
$procedure$;


Now, the case:
\set AUTOCOMMIT off
do
$$ begin
--raise notice 'do >> xact=%', txid_current_if_assigned();
update t_test set x = 1;
--raise notice 'do >> xact=%', txid_current_if_assigned();
raise notice 'do >> x=%', f_get_x();
--raise notice 'do >> xact=%', txid_current_if_assigned();
call f_print_x(f_get_x());
end; $$;
NOTICE:  do >> x=1
NOTICE:  f_print_x() >> x=0
DO

I don't understand why CALL statement is not seeing an updated record.
With AUTOCOMMIT=on, all goes as expected.

I tried to examine snapshots and xids (commented lines), but they're always
the same.

Can you explain this behavior, please? Is it expected?

-- 
Victor Yegorov

Re: Unexpected results from CALL and AUTOCOMMIT=off

[Victor Yegorov]

пн, 3 июн. 2024 г. в 20:40, Pierre Forstmann :

> You declared function f_get_x as stable which means:
>
> …
>
> If you remove stable from function declaration, it works as expected:
>

Well, I checked
https://www.postgresql.org/docs/current/xfunc-volatility.html
There's a paragraph describing why STABLE (and IMMUTABLE) use different
snapshots:

> For functions written in SQL or in any of the standard procedural
languages, there is a second important property determined by the
volatility category, namely the visibility of any data changes that have
been made by the SQL command that is calling the function. A > VOLATILE
function will see such changes, a STABLE or IMMUTABLE function will not.
This behavior is implemented using the snapshotting behavior of MVCC (see
Chapter 13): STABLE and IMMUTABLE functions use a snapshot established as
of the start of the
> calling query, whereas VOLATILE functions obtain a fresh snapshot at the
start of each query they execute.

But later, docs state, that

> Because of this snapshotting behavior, a function containing only SELECT
commands can safely be marked STABLE, even if it selects from tables that
might be undergoing modifications by concurrent queries. PostgreSQL will
execute all commands of a STABLE function using the snapshot established
for the calling query, and so it will see a fixed view of the database
throughout that query.

And therefore I assume STABLE should work in this case. Well, it seems not
to.

I assume there's smth to do with implicit BEGIN issued in non-AUTOCOMMIT
mode and non-atomic DO block behaviour.


-- 
Victor Yegorov

Re: CSV From Oracle with timestamp column getting errors

[Victor Yegorov]

пн, 22 мар. 2021 г. в 21:38, Saha, Sushanta K <
sushanta.s...@verizonwireless.com>:

> \COPY table1 FROM '/tmp/Oracle_2020_06.csv' DELIMITER ',' CSV HEADER;
> ERROR:  invalid input syntax for type timestamp: "01-JUN-20
> 06.04.20.634000 AM"
> CONTEXT:  COPY table1, line 2, column last_update_timestamp: "01-JUN-20
> 06.04.20.634000 AM"
>
> Appreciate any help with this psql command.
>

I would recommend issuing one of these on the Oracle side *before* taking
the CSV snapshot.
export NLS_DATE_FORMAT="-MM-DD HH24:MI:SS"
ALTER SESSION SET nls_date_format='-MM-DD HH24:MI:SS';

Otherwise, you have to load this CSV file in a table, that has `text` type
for the column and do a post-processing,
smth like:

INSERT INTO permanent_tab
SELECT *, to_timestamp(col, 'DD-MON-YY HH12.MI.SS.S AM') FROM
temp_table;

Hope this helps.

-- 
Victor Yegorov

Intersection or zero-column queries

[Victor Yegorov]

Greetings.

One can issue an empty `SELECT` statement and 1 row without columns will be
returned:

postgres=# select;
--
(1 row)

However, if I'll do `EXCPET` or `INTERSECT` of such queries, I'll get 2
rows:

postgres=# select except select;
--
(2 rows)
postgres=# select intersect all select;
--
(2 rows)

Why is it so?
Should this be reported as a bug?.. ;)


-- 
Victor Yegorov

Re: Intersection or zero-column queries

[Victor Yegorov]

2017-12-22 2:03 GMT+02:00 David G. Johnston :

> On Thu, Dec 21, 2017 at 4:53 PM, Victor Yegorov 
> wrote:
>
>> postgres=# select except select;
>> --
>> (2 rows)
>> postgres=# select intersect all select;
>> --
>> (2 rows)
>>
>> Why is it so?
>> Should this be reported as a bug?.. ;)
>>
>
> ​The intersection case seems correct - one row from each sub-relation is
> returned since ALL is specified and both results as the same.
>

Actually, result will not change with or without `ALL` for both, EXCEPT and
INTERSECT.

Also, intersection should not return more rows, than there're in the
sub-relations.


-- 
Victor Yegorov

Re: change JSON serialization for BIGINT?

[Victor Yegorov]

вт, 26 нояб. 2024 г. в 14:34, Tim McLaughlin :

> Is there a way to have Postgres serialize BIGINT as a string rather than
> number in JSON?  By default it does this:
>
>
> select row_to_json(row(500::bigint));
>  row_to_json
> -
>  {"f1":500}
>
> But I want it to do this (note that "500" is quoted):
>
> select row_to_json(row(500::bigint));
>  row_to_json
> -
>  {"f1":"500"}
>

Will this work?

select row_to_json(row(500::text));

-- 
Victor Yegorov

Re: alter system appending to a value

[Victor Yegorov]

ср, 30 апр. 2025 г. в 14:15, Luca Ferrari :

> as trivial as it sounds, is there a smart way to use ALTER SYSTEM to
> append to a value?
> Something like: ALTER SYSTEM shared_preloaded_libraries =
> current_setting( 'shared_preloaded_libraries' ) || ',foo';
>

I would do smth like:
SELECT format( 'ALTER SYSTEM SET shared_preload_libraries = %L;', setting )
  FROM pg_settings WHERE name = 'shared_preload_libraries' \gexec

Of course, you should add new value to the existing setting, making sure
there are no duplicates and the format is correct.

-- 
Victor Yegorov

how to install pgcrypto

[Ze Victor Harry]

 hello again I have a small problem here can someone tell me briefly how to
do it? I am getting this error When I give ant fresh_install it gives error
PostgreSQL 'pgcrypto' extension installed /up to date? False (not
installed) Create extension pgcrypto

.
I have tried to look up for solutions and they all say I have to run a
command
# Login to your "dspace" database as a superuser
psql --username=postgres dspace
# Enable the pgcrypto extension on this database
CREATE EXTENSION pgcrypto;
 but where I got confused is from where do I run these commands.is it from
cmd? or in pgadmin query tool? I need clearly articulated  steps

Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

[Victor Nordam Suadicani]

Hi,

Is there any nice way to handle sum types (aka tagged unions) in a
PostgreSQL database? I've searched far and wide and have not reached any
satisfying answer.

As a (somewhat contrived) example, say I have the following enum in Rust:

enum TaggedUnion {
Variant1(String),
Variant2(i32),
Variant3(f64),
}

How might I best save this data in a PostgreSQL database? There is to my
knowledge unfortunately no way to "natively" handle sum types like this.

One method would be to have 3 different tables, one for each variant. This
is not a great solution as you can't (as far as I know) easily query for
all variants at once (for instance, to serialize into a Vec on
the Rust side).

Another method would be to use PostgreSQL table inheritance. This has the
same problem as the above workaround but also has the issue that you could
put something into the supertable without having a corresponding entry in
any of the subtables (basically a value not inhabiting any of the variants,
which is nonsense).

A third method would be to save all fields of all variants into a single
table, with all fields being nullable. So you'd have a nullable text field,
nullable integer and nullable double precision field. You'd then need an
additional tag field to indicate which variant of the union is used and
you'd have to write check constraints for each variant to ensure that all
the fields in that variant are not null and all the fields not in that
variant *are* null. This *almost* works, but has two major problems:

1. It wastes space. In Rust, an enum is only as big as its largest variant.
Using this method, a table row would be as big as the sum of all the
variants.

2. Querying the data is very cumbersome, as there is no way to indicate
(beyond check constraints) that, given a specific tag, certain other fields
must be filled while certain other fields must not be. For instance, the
nullability of fields can be used to serialize into the Option type in
Rust. There is no "nice" way to tell the host language that the nullability
of the variant fields is hinged on the value of the extra tag field.

Both of these problems get bigger and bigger as you add more variants - it
doesn't scale well.

Does anyone know of better methods? I realize the example enum is contrived
but this kind of thing comes up *a lot* in all kinds of places. For
instance different kinds of messages that all have various different
associated data. Or different events that record different additional data.
Sum types are ubiquitous in languages like Rust and Haskell.

If there are no good methods of handling this, is there any way PostgreSQL
could be extended with capabilities for this? I have no idea how this would
be done in practice though. Perhaps SQL itself is just unsuited for data of
this kind? I don't really see why it should be though.

Thanks,
Victor Nordam Suadicani

Re: Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

[Victor Nordam Suadicani]

A composite type is a *product type*
<https://en.wikipedia.org/wiki/Product_type>, not a sum type
<https://en.wikipedia.org/wiki/Tagged_union>. PostgreSQL currently has
great support for product types, but basically no support for sum types.
>From the perspective of algebraic data types, this feels like a "missing
link" in the type system. I'm not sure why SQL or the underlying relational
model has never addressed this deficiency. Would greatly appreciate any
insight anyone may have.

On Thu, 18 May 2023 at 16:35, Adrian Klaver 
wrote:

> On 5/18/23 05:27, Victor Nordam Suadicani wrote:
> > Hi,
> >
> > Is there any nice way to handle sum types (aka tagged unions) in a
> > PostgreSQL database? I've searched far and wide and have not reached any
> > satisfying answer.
> >
> > As a (somewhat contrived) example, say I have the following enum in Rust:
> >
> > enum TaggedUnion {
> >  Variant1(String),
> >  Variant2(i32),
> >  Variant3(f64),
> > }
> >
>
> > If there are no good methods of handling this, is there any way
> > PostgreSQL could be extended with capabilities for this? I have no idea
> > how this would be done in practice though. Perhaps SQL itself is just
> > unsuited for data of this kind? I don't really see why it should be
> though.
>
> Composite type?:
>
> https://www.postgresql.org/docs/current/rowtypes.html
> >
> > Thanks,
> > Victor Nordam Suadicani
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>

Re: Is there a good way to handle sum types (or tagged unions) in PostgreSQL?

[Victor Nordam Suadicani]

On Fri, 19 May 2023 at 12:44, Dominique Devienne 
wrote:

> On Thu, May 18, 2023 at 2:28 PM Victor Nordam Suadicani <
> v.n.suadic...@gmail.com> wrote:
>
>> Is there any nice way to handle sum types (aka tagged unions) in a
>> PostgreSQL database? [...]
>>
> A third method would be to save all fields of all variants into a single
>> table, with all fields being nullable.
>>
> So you'd have a nullable text field, nullable integer and nullable double
>> precision field.
>>
>
> Yes, we do that. That's IMHO the only sane way to do it.
> And if some of those alternatives are FKs (relations), that's the only
> choice.
>
> You'd then need an additional tag field to indicate which variant of the
>> union is used
>>
>
> No, you don't need it. That's implicit from the NULL'ability of the
> alternative columns.
> If you want, you can have it as a generated column, thus read-only.
> Worse, having it as an explicit column would make it denormalized, and
> possibly out of sync.
>
>
>> and you'd have to write check constraints for each variant to ensure that
>> all the fields in that variant are not null and all the fields not in that
>> variant *are* null.
>>
>
> Yes indeed.
>
>
>> This *almost* works, but has two major problems:
>>
>> 1. It wastes space. In Rust, an enum is only as big as its largest
>> variant. Using this method, a table row would be as big as the sum of all
>> the variants.
>>
>
> Not really, or not to a point it matters that much.
> I don't know about the actual physical bytes on disk for PostgreSQL, but
> as an example in SQLite,
> all columns have *at least* 1 "header" byte per value, and NULL values
> (and 0 and 1) have no "payload" bytes.
> In PostgreSQL (which is more "rigidly typed" as DRH would say :)) you may
> waste space for primitive types,
> but not for text and bytea, which is where it really matters IMHO.
>
>
>> 2. Querying the data is very cumbersome, [...].
>>
>
> Sure, it's cumbersome. But I don't get your point here. NULL handling is
> part of SQL.
> And sum-types (variants) implemented via exclusive NULL'ness is just a
> special case.
> You "dispatch" to the proper column on writes. You read all alternatives
> and assign the one (if any) NOT NULL to the variant.
>
>
>> Both of these problems get bigger and bigger as you add more variants -
>> it doesn't scale well.
>>
>
> ORMs cannot magically resolve the impedence mismatch between SQL and
> OO-based or sum-type based type systems a la Rust (and co).
> If you need SQL, you need to design for SQL for the get go. Not shoehorn
> your Rust data model into SQL.
>
> My $0.02.
>

Thanks for the perspective :)

> If you need SQL, you need to design for SQL for the get go. Not shoehorn
your Rust data model into SQL.

Sometimes the data in the domain really does fit a sum type and then a sum
type is the right tool to use (whether you use Rust or Haskell or whatever
language). Trying to shoehorn your domain data model into a data format
that doesn't fit isn't the way to go either. I feel like it's a deficiency
in SQL that there is no support for sum types. I would guess this is
influenced by the fact that SQL was developed in a time when there were no
major programming languages with sum type support either.

But really it's not that I "need" SQL per se, it's just that SQL databases
are the most developed and used at this time. Do you know of any other
production-grade databases that actually support sum types in a better way
than SQL? I'd be very curious cause I haven't really found any.