Fwd: Proposed Italian Translation of Code of Conduct Policy

[Stacey Haysler]

Someone politely informed me that the attachments did not make it through
to the archives, so I'm re-sending from my gmail account in hopes that the
attachments stick this time.

Regards,
Stacey

-- Forwarded message -
*From: *Stacey Haysler 
*Subject: **Proposed Italian Translation of Code of Conduct Policy*
*Date: *March 31, 2021 at 3:25:58 PM PDT
*To: *pgsql-general@lists.postgresql.org

The PostgreSQL Community Code of Conduct Committee has received a draft of
the Italian translation of the Code of Conduct Policy updated August 18,
2020 for review.

The English version of the Policy is at:
*https://www.postgresql.org/about/policies/coc/
*

The translation was created by Federico Campoli.

The translation was reviewed by Ilaria Battiston.

The proposed translation is attached as both a text file and a PDF to this
message.

If you have any comments or suggestions for this translation, please bring
them to our attention by 5:00 PM PDT on Wednesday, April 7, 2021.

Thank you.

Regards,
Stacey

Stacey Haysler
Chair
PostgreSQL Community Code of Conduct Committee
Introduzione

Il progetto PostgreSQL è orgoglioso della qualità del proprio codice,
del proprio lavoro e dei risultati, tecnici e professionali della
propria comunità. È doveroso che ognuno che partecipi alla comunità si
comporti, nell'interesse comune, in modo professionale ed educato
portando rispetto verso utenti e sviluppatori.

A tal fine è stato creato questo Codice di Comportamento (Code of
Conduct, Coc) per gestire i rapporti all'interno della comunità e la
partecipazione al lavoro dell'organizzazione su larga scala. Questo
Codice di Comportamento è pensato per coprire tutte le interazioni tra i
membri della comunità in qualsiasi contesto, a meno che non sia presente
un altro Codice di Comportamento che abbia precedenza su questo (es.
Codice di Comportamento di una conferenza).

Inclusività e comportamento appropriato

La partecipazione al progetto PostgreSQL è aperta a chiunque abbia
interesse a lavorare con PostgreSQL, indipendentemente dal livello di
esperienza con il software o con la tecnologia. Tutti sono incoraggiati
allo sviluppo software, e in generale alla contribuzione, a prescindere
dalla propria formazione.

Sono incoraggiate discussioni riflessive e costruttive, relative al
software, la comunità, lo stato attuale e le possibili evoluzioni. Il
tema delle discussioni dovrebbe comunque riguardare il codice e progetti
correlati, progetti comunitari e l'infrastruttura.

Attacchi personali e commenti negativi riguardanti caratteristiche
personali sono inaccettabili e pertanto non saranno permessi. Alcuni
esempi di caratteristiche personali sono età, razza, etnicità,
religione, genere od orientamento sessuale.

Altri esempi di comportamento che violano questo Codice di Comportamento
sono minacce di violenza verso individui o gruppi, minacce di sabotaggio
sia esso di tipo professionale comunitario o di qualunque progetto,
attenzioni sessuali indesiderate sotto qualsiasi forma, comportamenti
che possano causare danno di reputazione al progetto PostgreSQL e
rifiuto di cessare qualsiasi condotta inappropriata quando richiesto.

Ritorsioni

Sono inoltre espressamente vietate ritorsioni contro una persona che
presenti un reclamo ai sensi del presente Codice di Comportamento o
contro chiunque stia prestando assistenza alle indagini per tale
reclamo. La ritorsione può avvenire sotto forma di queste ed altre
azioni:

-   ulteriori attacchi personali (pubblici o privati);
-   azioni che minano lo status professionale e/o lo status di un
individuo nei confronti del datore di lavoro, dei colleghi, dei
clienti o della comunità;
-   azioni che minacciano la privacy, la persona fisica, il benessere,
l'abitazione dell'individuo e/o la sua famiglia.

Gli atti di ritorsione saranno trattati alla stessa maniera di qualsiasi
altra violazione del presente Codice di Comportamento.

Comitato per il Codice di Comportamento

Il Core Team di PostgreSQL nominerà un Comitato per il Codice di
Comportamento, che si occuperà di ricevere ed indagare su tutti i
reclami, e un Presidente del Comitato. Qualsiasi membro della comunità
può offrirsi volontario per far parte del Comitato, ad eccezione dei
membri del Core Team. Poiché il Core Team sovrintende al Comitato, i
membri del Core Team non faranno parte del Comitato per evitare
conflitti di interesse. L'elenco dei membri del Comitato sarà sempre
disponibile al pubblico e può essere visualizzato qui 
https://www.postgresql.org/about/policies/coc_committee/.

I membri del Comitato cambieranno ogni anno. Il Core Team o il
Presidente del Comitato annunceranno le date di apertura e chiusura del
processo annuale di selezione dei membri attraverso i consueti canali di
comunicazione della comunità.

Qualsiasi membro della comunità che desidera far parte del Comitato
dovrà completare un questionario iniziale che verrà 

Re: How to install PostgreSQL binaries on a different directory than the default one

[Benedict Holland]

Why not use the docker container?

Thanks,
Ben

On Fri, Apr 2, 2021, 8:25 PM rob stone  wrote:

> Hello,
>
> On Fri, 2021-04-02 at 22:01 +, Allie Crawford wrote:
> > Hi,
> > I am new in postgresql and I am trying to install PostgreSQL on Linux
> > server but instead of using the default location for the binaries I
> > want to install them in a different directory.
> > I have research for a while and I cannot find any information on how
> > to do that using the dnf utility which is what the PostgreSQL website
> > gives me as instruction when I go the download page.
> >
> > I have a directory called /pg01 that I want to use to install the
> > binaries. The postgreSQL download site (
> > https://www.postgresql.org/download/linux/redhat/ ) gives me the
> > following steps to install the binaries:
> >
> >
> > That set of instructions shown right above, installs the binaries
> > under the/usr/pgsql-13, and what I want is to install the binaries
> > under /pg01 instead of /usr.
> > My question is, what are the changes I need to do in the instructions
> > detailed right above, so that I can get the binary installation under
> > the /pg01 directory instead of the default /usr directory.
> >
> > Thanks in advance for any help you can five me on this.
> >
> > Regards,
> > Allie
>
> Most Linux distros keep the binaries by major version. So when you
> install version 14 it will go into /usr/pgsql-14.
> Thus, by using different port numbers you can run multiple versions on
> the same box.
> Useful for development, QA, etc.
>
> HTH,
> Robert
>
>
>
>
>

Re: How to install PostgreSQL binaries on a different directory than the default one

[rob stone]

Hello,

On Fri, 2021-04-02 at 22:01 +, Allie Crawford wrote:
> Hi,
> I am new in postgresql and I am trying to install PostgreSQL on Linux
> server but instead of using the default location for the binaries I
> want to install them in a different directory.
> I have research for a while and I cannot find any information on how
> to do that using the dnf utility which is what the PostgreSQL website
> gives me as instruction when I go the download page.
>  
> I have a directory called /pg01 that I want to use to install the
> binaries. The postgreSQL download site (
> https://www.postgresql.org/download/linux/redhat/ ) gives me the
> following steps to install the binaries:
>  
>  
> That set of instructions shown right above, installs the binaries
> under the/usr/pgsql-13, and what I want is to install the binaries
> under /pg01 instead of /usr.
> My question is, what are the changes I need to do in the instructions
> detailed right above, so that I can get the binary installation under
> the /pg01 directory instead of the default /usr directory.
>  
> Thanks in advance for any help you can five me on this.
>  
> Regards,
> Allie

Most Linux distros keep the binaries by major version. So when you
install version 14 it will go into /usr/pgsql-14.
Thus, by using different port numbers you can run multiple versions on
the same box.
Useful for development, QA, etc.

HTH,
Robert

Re: Have I found an interval arithmetic bug?

[Bryn Llewellyn]

br...@momjian.us wrote:

> [Thread moved to hackers.] …The best fix I think is…
> 
>> Bryn wrote: Further… there’s a notable asymmetry. The fractional part of 
>> “1.7 years” is 8.4 months. But the fractional part of the months value 
>> doesn’t spread further down into days. However, the fractional part of “29.4 
>> months” (12 days) _does_ spread further down into days. What’s the rationale 
>> for this asymmetry?
> 
> Yes, looking at the code, it seems we only spill down to one unit, not more. 
> I think we need to have a discussion if we want to change that. I think the 
> idea was that if you specify a non-whole number, you probably want to spill 
> down one level, but don't want it spilling all the way to milliseconds, which 
> is certainly possible.

Thanks for the quick response, Bruce. I was half expecting (re the bug) an 
explanation that showed that I’d (once again) misunderstood a fundamental 
principle.

I should come clean about the larger context. I work for Yugabyte, Inc. We have 
a distributed SQL database that uses the Version 11.2 PostgreSQL C code for SQL 
processing “as is”.

https://blog.yugabyte.com/distributed-postgresql-on-a-google-spanner-architecture-query-layer/

The founders decided to document YugabyteDB’s SQL functionality explicitly 
rather than just to point to the published PostgreSQL doc. (There are some DDL 
differences that reflect the storage layer differences.) I’m presently 
documenting date-time functionality. This is why I’m so focused on 
understanding the semantics exactly and on understanding the requirements that 
the functionality was designed to meet. I’m struggling with interval 
functionality. I read this:

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT

« …field values can have fractional parts; for example '1.5 week' or 
'01:02:03.45'. Such input is converted to the appropriate number of months, 
days, and seconds for storage. When this would result in a fractional number of 
months or days, the fraction is added to the lower-order fields using the 
conversion factors 1 month = 30 days and 1 day = 24 hours. For example, '1.5 
month' becomes 1 month and 15 days. Only seconds will ever be shown as 
fractional on output. »

Notice that the doc says that spill-down goes all the way to seconds and not 
just one unit. This simple test is consistent with the doc (output follows the 
dash-dash comment):

select ('6.54321 months'::interval)::text as i; --  6 mons 16 days 07:06:40.32

You see similar spill-down with this:

select ('876.54321 days'::interval)::text as i; -- 876 days 13:02:13.344

And so on down through the remaining smaller units. It’s only this test that 
doesn’t spill down one unit:

select ('6.54321 years'::interval)::text as i; --  6 years 6 mons

This does suggest a straight bug rather than a case for committee debate about 
what might have been intended. What do you think, Bruce?

Re: Have I found an interval arithmetic bug?

[Bruce Momjian]

On Fri, Apr  2, 2021 at 01:20:26PM -0700, Ken Tanzer wrote:
> On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian  wrote:
> 
> 
> Thread moved to hackers, with a patch.
> 
> ---
> 
> 
> 
> Here is a link to that thread, for others who might be curious about it as I
> was:
> https://www.postgresql.org/message-id/flat/20210402180549.GF9270%40momjian.us#
> b3bdafbfeacab0dd8967ff2a3ebf7844
> 
> I get why it can make sense to move a thread.  But if when doing so you post a
> link to the new thread, that would be appreciated.  Thanks!

I didn't think anyone but the original poster, who was copied in the new
thread, would really care about this thread, but it seems I was wrong.

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

How to install PostgreSQL binaries on a different directory than the default one

[Allie Crawford]

Hi,
I am new in postgresql and I am trying to install PostgreSQL on Linux server 
but instead of using the default location for the binaries I want to install 
them in a different directory.
I have research for a while and I cannot find any information on how to do that 
using the dnf utility which is what the PostgreSQL website gives me as 
instruction when I go the download page.

I have a directory called /pg01 that I want to use to install the binaries. The 
postgreSQL download site (https://www.postgresql.org/download/linux/redhat/ ) 
gives me the following steps to install the binaries:

Steps to install postgresql 13

sudo dnf install -y 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql13-server
sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
sudo systemctl enable postgresql-13
sudo systemctl start postgresql-13


That set of instructions shown right above, installs the binaries under the 
/usr/pgsql-13, and what I want is to install the binaries under /pg01 instead 
of /usr.

My question is, what are the changes I need to do in the instructions detailed 
right above, so that I can get the binary installation under the /pg01 
directory instead of the default /usr directory.


Thanks in advance for any help you can five me on this.

Regards,
Allie

Re: Have I found an interval arithmetic bug?

[Ken Tanzer]

On Fri, Apr 2, 2021 at 11:06 AM Bruce Momjian  wrote:

>
> Thread moved to hackers, with a patch.
> ---
>
>
Here is a link to that thread, for others who might be curious about it as
I was:
https://www.postgresql.org/message-id/flat/20210402180549.GF9270%40momjian.us#b3bdafbfeacab0dd8967ff2a3ebf7844

I get why it can make sense to move a thread.  But if when doing so you
post a link to the new thread, that would be appreciated.  Thanks!

Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: Have I found an interval arithmetic bug?

[Bruce Momjian]

Thread moved to hackers, with a patch.

---

On Thu, Apr  1, 2021 at 09:46:58PM -0700, Bryn Llewellyn wrote:
> Or am I misunderstanding something?
> 
> Try this. The result of each “select” is shown as the trailing comment on the
> same line. I added whitespace by hand to line up the fields.
> 
> select interval '-1.7 years';  -- -1 years -8 mons
> 
> select interval '29.4 months'; --  2 years  5 mons 12
> days
> 
> select interval '-1.7 years 29.4 months';  --   8 mons 12
> days << wrong
> select interval '29.4 months -1.7 years';  --   9 mons 12
> days
> 
> select interval '-1.7 years' + interval '29.4 months'; --   9 mons 12
> days
> select interval '29.4 months' + interval '-1.7 years'; --   9 mons 12
> days
> 
> As I reason it, the last four “select” statements are all semantically the
> same. They’re just different syntaxes to add the two intervals  the the first
> two “select” statements use separately. There’s one odd man out. And I reason
> this one to be wrong. Is there a flaw in my reasoning?
> 
> Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 
> 8.4
> months. But the fractional part of the months value doesn’t spread further 
> down
> into days. However, the fractional part of “29.4 months” (12 days) _does_
> spread further down into days. What’s the rationale for this asymmetry?
> 
> I can’t see that my observations here can be explained by the difference
> between calendar time and clock time. Here I’m just working with non-metric
> units like feet and inches. One year is just defined as 12 months. And one
> month is just defined as 30 days. All that stuff about adding a month to
> 3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) ,
> and that other stuff about adding one day to 23:00 on the day before the
> “spring forward” moment taking you to 23:00 on the next day (i.w. when
> intervals are added to timestamps) is downstream of simply adding two
> intervals.
> 

-- 
  Bruce Momjian  https://momjian.us
  EDB  https://enterprisedb.com

  If only the physical world exists, free will is an illusion.

Re: How to implement expiration in PostgreSQL?

[Vincent Veyron]

On Thu, 1 Apr 2021 09:51:38 +0800
Glen Huang  wrote:

Hi,

If you are in a position to modify your design, I believe your problem comes 
from this part :

>   WHERE paid OR join_time > now() - ‘1h’::interval

which suggests that there is a 'paid' column being updated. I learned that the 
proper way to structure a database to maintain membership and save a lot of 
grief is the following :

create table seated_member(
name text not null,
...,
join_time date not null default now,
validity integer not null default 1
);

where validity is a number of whatever unit is appropriate; typically 'year' 
for a club membership (newspapers use 'issue number' instead of join_time to 
account for strikes, when no paper is issued). In your case, 'hour' I suppose.

All you need to do when the member pays is to update the 'validity' field with 
the proper amount of units. This makes for very simple and efficient queries to 
retrieve the data, and you only need to write :

WHERE now() < join_time + 'validity hours'::interval

to retrieve valid accounts. 

Accounts expire automatically, deleting them can wait; it also makes it easier 
to send reminders before the expiration date

-- 
Bien à vous, Vincent Veyron

https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres 
d'assurance

Re: pass non-formated query to PL function

[Joao Miguel Ferreira]

Hi Pavel and Gianni,

Thank you for the suggestions and documentation. That is exactly the kind
of thing I was looking for.

Cool, thx
Joao


On Fri, Apr 2, 2021 at 10:54 AM Gianni Ceccarelli 
wrote:

> On 2021-04-02 Joao Miguel Ferreira 
> wrote:
> > Is it possible, in PL/pgSQL, to pass an argument to a function which
> > is actually a "query skeleton" that the method will "fill in the
> > blanks" and execute it or return it to the caller after ?
>
> you probably want to use the ``EXECUTE`` command:
>
> https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Something like this, maybe::
>
>   CREATE FUNCTION run_me_this(in the_query text) RETURNS record
>   AS $$
>   DECLARE
> this record;
>   BEGIN
> EXECUTE the_query INTO this USING 1, 'foo';
> RETURN this;
>   END;
>   $$ LANGUAGE plpgsql;
>
>   CREATE TABLE test(id serial primary key,name text,value text);
>   INSERT INTO test(name,value) VALUES ('foo','something');
>
>   SELECT *
>   FROM run_me_this('select value from test where id=$1 and name=$2')
>x(value text);
>
> Notice, though, that ``EXECUTE ... INTO`` will only assign *the first
> row* of the results to the given variable (``this`` in my
> example). I'm not sure how to work around this limitation.
>
> --
> Dakkar - 
> GPG public key fingerprint = A071 E618 DD2C 5901 9574
>  6FE2 40EA 9883 7519 3F88
> key id = 0x75193F88
>
>
>
>

Have I found an interval arithmetic bug?

[Bryn Llewellyn]

Or am I misunderstanding something?

Try this. The result of each “select” is shown as the trailing comment on the 
same line. I added whitespace by hand to line up the fields.

select interval '-1.7 years';  -- -1 years -8 mons

select interval '29.4 months'; --  2 years  5 mons 12 
days

select interval '-1.7 years 29.4 months';  --   8 mons 12 
days << wrong
select interval '29.4 months -1.7 years';  --   9 mons 12 
days

select interval '-1.7 years' + interval '29.4 months'; --   9 mons 12 
days
select interval '29.4 months' + interval '-1.7 years'; --   9 mons 12 
days

As I reason it, the last four “select” statements are all semantically the 
same. They’re just different syntaxes to add the two intervals  the the first 
two “select” statements use separately. There’s one odd man out. And I reason 
this one to be wrong. Is there a flaw in my reasoning?

Further… there’s a notable asymmetry. The fractional part of “1.7 years” is 8.4 
months. But the fractional part of the months value doesn’t spread further down 
into days. However, the fractional part of “29.4 months” (12 days) _does_ 
spread further down into days. What’s the rationale for this asymmetry?

I can’t see that my observations here can be explained by the difference 
between calendar time and clock time. Here I’m just working with non-metric 
units like feet and inches. One year is just defined as 12 months. And one 
month is just defined as 30 days. All that stuff about adding a month to 
3-Feb-2020 taking you to 3-Mar-2020 (same for leap years an non-leap years) , 
and that other stuff about adding one day to 23:00 on the day before the 
“spring forward” moment taking you to 23:00 on the next day (i.w. when 
intervals are added to timestamps) is downstream of simply adding two intervals.

Re: pass non-formated query to PL function

[Gianni Ceccarelli]

On 2021-04-02 Joao Miguel Ferreira 
wrote:
> Is it possible, in PL/pgSQL, to pass an argument to a function which
> is actually a "query skeleton" that the method will "fill in the
> blanks" and execute it or return it to the caller after ?

you probably want to use the ``EXECUTE`` command:
https://www.postgresql.org/docs/13/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Something like this, maybe::

  CREATE FUNCTION run_me_this(in the_query text) RETURNS record
  AS $$
  DECLARE
this record;
  BEGIN
EXECUTE the_query INTO this USING 1, 'foo';
RETURN this;
  END;
  $$ LANGUAGE plpgsql;

  CREATE TABLE test(id serial primary key,name text,value text);
  INSERT INTO test(name,value) VALUES ('foo','something');

  SELECT *
  FROM run_me_this('select value from test where id=$1 and name=$2')
   x(value text);

Notice, though, that ``EXECUTE ... INTO`` will only assign *the first
row* of the results to the given variable (``this`` in my
example). I'm not sure how to work around this limitation.

-- 
Dakkar - 
GPG public key fingerprint = A071 E618 DD2C 5901 9574
 6FE2 40EA 9883 7519 3F88
key id = 0x75193F88

Re: pass non-formated query to PL function

[Pavel Stehule]

Hi

pá 2. 4. 2021 v 11:35 odesílatel Joao Miguel Ferreira <
joao.miguel.c.ferre...@gmail.com> napsal:

> Hello all,
>
> Is it possible, in PL/pgSQL, to pass an argument to a function which is
> actually a "query skeleton" that the method will "fill in the blanks" and
> execute it or return it to the caller after ?
>
> The caller does not have access to the variables/values that will be used
> to fill the blanks, but it does know the generic query.
>
> I have a situation where this kind of separation would be nice to have in
> order to make the code a bit cleaner and avoid some code repetitions.
>

It is not problem

create or replace function exec_query(q text)
returns void as $$
declare r int;
begin
  raise notice 'q=%', q;
  execute q using 10 into r;
  raise notice 'r=%', r;
end;
$$ language plpgsql;

postgres=# select exec_query ('select 10 + $1');
NOTICE:  q=select 10 + $1
NOTICE:  r=20
┌┐
│ exec_query │
╞╡
││
└┘
(1 row)

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

Regards

Pavel

>
> Thank you for you suggestions
> Joao
>
>

pass non-formated query to PL function

[Joao Miguel Ferreira]

Hello all,

Is it possible, in PL/pgSQL, to pass an argument to a function which is
actually a "query skeleton" that the method will "fill in the blanks" and
execute it or return it to the caller after ?

The caller does not have access to the variables/values that will be used
to fill the blanks, but it does know the generic query.

I have a situation where this kind of separation would be nice to have in
order to make the code a bit cleaner and avoid some code repetitions.

Thank you for you suggestions
Joao

Re: Primary keys and composite unique keys(basic question)

[Laurenz Albe]

On Thu, 2021-04-01 at 21:28 -0500, Merlin Moncure wrote:
> I would never use UUIDS for keys though.

That makes me curious for your reasons.

I see the following disadvantages:

- A UUID requires twice as much storage space as a bigint.

- B-tree indexes are space optimized for inserting at the
  rightmost leaf page, but UUIDs are random.

- UUIDs are more expensive to generate.

On the other hand, many processes trying to insert into
the same index page might lead to contention.

Is there anything I have missed?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: questions about wraparound

[Jehan-Guillaume de Rorthais]

On Thu, 18 Mar 2021 09:56:16 +0100
Luca Ferrari  wrote:
[...]
> Therefore my question is: shouldn't autovacuum be able to freeze other
> tables/databases? I mean, the wraparound problem in this scenario will
> cause problems, but I was expecting different numbers for different
> tables/databases.

In fact, when an autovacuum worker is spawned, here is how it chooses what
database to process:

1. look for any database needing a vacuum to prevent a wraparound.
2. same with multi-transaction
3. other autovacuum considerations

So as long as there's a database in desperate need for a vacuum to prevent a
wraparound, a worker will try to process it first, again and again.

Because of your long-running transaction, the xid horizon forbid to update the
rel/datfrozenxid. So next autovacuum round will keep trying to process the same
database, ignoring others.

Look at the comment in function "do_stat_worker()" in autovacuum.c for more
details:
https://git.postgresql.org/cgit/postgresql.git/tree/src/backend/postmaster/autovacuum.c#n1207

When looping over the database list, as soon as "for_xid_wrap" is true, any
other database is ignored. Then a new worker is popped from the freeWorkers,
init'ed with the database to freeze and started. So as far as I understand the
code (I might easily be wrong), all the workers will keep trying to process the
same database again and again without considering other ones. All because of
your really-long living xact.

Regards,

Re: Primary keys and composite unique keys(basic question)

[Bertrand Mamasam]

On Fri, Apr 2, 2021 at 8:57 AM Hemil Ruparel 
wrote:

> I used uuid4 for customer ids because i needed to interface with payment
> providers. Is that wrong? All other places except transaction ids, i have
> used serial ints
>
> On Fri 2 Apr, 2021, 8:56 AM Rob Sargent,  wrote:
>
>> On 4/1/21 8:28 PM, Merlin Moncure wrote:
>> >
>> > This is one of the great debates in computer science and it is not
>> > settled.  There are various tradeoffs around using a composite key
>> > derived from the data (aka natural key) vs generated identifiers. It's
>> > a complex topic with many facets: performance, organization,
>> > validation, and correctness are all relevant considerations.  I would
>> > never use UUIDS for keys though.
>> >
>> > merlin
>> >
>> >
>> And, pray tell, for what exactly would you use universally unique
>> identifiers.
>>
>
ULID perform better than UUID with btree indexes. The first part is based
on a timestamp, so if you don't mind exposing this information, they are a
better choice. There is an implementation for postgres here:
https://github.com/geckoboard/pgulid
Maybe there are others.

Re: Copy Statistics Tables During Upgrade

[Pierre Forstmann]

Hello,

This is a pg_upgrade known limitation that has been summarized by Greg 
Sabino Mullane on 
https://www.endpoint.com/blog/2016/12/07/postgres-statistics-and-pain-of-analyze


this way:

The nominal reason for not copying the data is that the table format may 
change from version to version. The real reason is that nobody has 
bothered to write the conversion logic yet, for pg_upgrade could 
certainly copy the pg_statistics information: the table has not changed 
for many years.


Regards

Pierre Forstmann

Le 31/03/2021 à 20:15, Virendra Kumar a écrit :

Hello Team,

I was doing an upgrade of one of our PG (9.6.11 to 11.9) database and 
came across the question that what is reason PostgreSQL is not doing 
copy of stats and we have to analyze right after upgrade. There are 
two situations where this will help when we use this DB as our 
enterprise database:


1. If DB is in magnitude of hundreds of TB and we have to do analyze 
it might take several hours before we handover this to application 
services.


2. We are loosing critical information like most common values and 
most common freqs because these values are populated over time when 
many-many sessions hit table and queries with different values of a 
column.


Any input on this is higly appreciated.


Regards,
Virendra Kumar

Re: Primary keys and composite unique keys(basic question)

[Hemil Ruparel]

I used uuid4 for customer ids because i needed to interface with payment
providers. Is that wrong? All other places except transaction ids, i have
used serial ints

On Fri 2 Apr, 2021, 8:56 AM Rob Sargent,  wrote:

> On 4/1/21 8:28 PM, Merlin Moncure wrote:
> >
> > This is one of the great debates in computer science and it is not
> > settled.  There are various tradeoffs around using a composite key
> > derived from the data (aka natural key) vs generated identifiers. It's
> > a complex topic with many facets: performance, organization,
> > validation, and correctness are all relevant considerations.  I would
> > never use UUIDS for keys though.
> >
> > merlin
> >
> >
> And, pray tell, for what exactly would you use universally unique
> identifiers.
>
>
>