questions about wraparound

[Luca Ferrari]

Hi all,
I'm doing some experiments on a cluster to see what happens at xid
wraparound, and I'm approaching it. There is no activity in any
database but testdb.
Scenario: I've a procedure that is consuming all xids, while another
connection is inserting a tuple every 20 minutes or so in a table,
just to prevent autovacuum to freeze in emergency. autovacuum is
globally turned off.

Therefore, I'm approaching wraparound:

testdb=> select datname, datfrozenxid, age( datfrozenxid ),
txid_current() from pg_database;WARNING:  database "postgres" must be
vacuumed within 7989757 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in
that database.
You might also need to commit or roll back old prepared transactions,
or drop stale replication slots.
  datname  | datfrozenxid |age | txid_current
---+--++--
 postgres  |   1221679879 | 2139493890 |  11951108361
 backupdb  |   1221679879 | 2139493890 |  11951108361
 template1 |   1221679879 | 2139493890 |  11951108361
 template0 |   1221679879 | 2139493890 |  11951108361
 testdb|   1221679879 | 2139493890 |  11951108361
 pgbench   |   1221679879 | 2139493890 |  11951108361
(6 rows)



What puzzles me is that I'm somehow "locking" the testdb.wa table (by
inserting a tuple every 20 minutes), so all other tables and databases
are free to be frozen by an emergency autovacuum. And I was expecting
the problem to happen due to the testdb.wa table, and therefore the
hint message to be related to "testdb", not "postgres" database.
Digging I found that all the database are becoming old, and all the
tables in every database has the same age.
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.

The other question is: the xid is defined as a 32 bit integer:

typedef uint32 TransactionId;

but it is exposed as a 64 bit integer

typedef uint64 txid;

appending EpochFromFullTransactionId (that I'm not able to find in the
sources). The dumb question then is: why use the 32 bit machinery if
the txid is exposed as 64 bit wide value?

Thanks,
Luca

Domains and generated columns

[Fabian Pijcke]

Dear list,

I ran into an error when running the following snippet of code (I
simplified as much as I could):

CREATE DOMAIN domaintest AS VARCHAR(10) NOT NULL;

CREATE TABLE tabletest (
  fieldtest domaintest GENERATED ALWAYS AS ('valuetest') STORED
);

INSERT INTO tabletest DEFAULT VALUES;

In PostgreSQL 12 and 13, I get the following error:
domain domaintest does not allow null values.

If I move the NOT NULL constraint from the domain to the table, the error
disappears.

I suspect that the NULL check is done before the GENERATED column is
actually computed. I don't think this is related to the warning the
documentation gives about NULL values and DOMAINs (
https://www.postgresql.org/docs/13/sql-createdomain.html#id-1.9.3.62.7).

Is this the expected behaviour? Is there any way to circumvent this and use
a domain with a NOT NULL constraint on a generated column? (I know the
documentation recommends to avoid NOT NULL on domains, but I'd still like
to do so).

Thank you,

--
Fabian Pijcke

Re: questions about wraparound

[Luca Ferrari]

On Thu, Mar 18, 2021 at 9:56 AM Luca Ferrari  wrote:
>
> What puzzles me is that I'm somehow "locking" the testdb.wa table (by
> inserting a tuple every 20 minutes), so all other tables and databases
> are free to be frozen by an emergency autovacuum. And I was expecting
> the problem to happen due to the testdb.wa table, and therefore the
> hint message to be related to "testdb", not "postgres" database.
> Digging I found that all the database are becoming old, and all the
> tables in every database has the same age.
> 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.


And I hit the 1 million transaction theshold, so I tunrned off the
cluster, gone into single user mode and vacuumed the database postgres
(oid = 13811).
Then the system asked me to vacuum another database, on which I've not
generated traffic.

% sudo -u postgres postgres --single -D /postgres/12/data postgres
 WARNING:  database with OID 13811 must be
   vacuumed within 100 transactions
 HINT:  To avoid a database shutdown,
execute a database-wide VACUUM in that database.

PostgreSQL stand-alone backend 12.5
backend> VACUUM VERBOSE
WARNING:  database "backupdb" must be vacuumed within 100 transactions

Instead I restarted the multiuser postgres, and I was able to connect
to the problematic database testdb and issue read-write transacctions.
And now the situation is:

testdb=> select datname, datfrozenxid, age(datfrozenxid) from pg_database;
  datname  | datfrozenxid |   age
---+--+--
 postgres  |   3318163526 | 5002
 backupdb  |   3318163526 | 5002
 template1 |   3368163526 |2
 template0 |   3368163526 |2
 testdb|   3318163526 | 5002
 pgbench   |   3318163526 | 5002


I don't understand why template0 and template1 are two transactions
old (I did two read-write transactions) while all other databases
including the vacuumed postgres are 50 millions old. Clearly that
number is the manual vacuum freeze age:

testdb=> show vacuum_freeze_min_age;
 vacuum_freeze_min_age
---
 5000

but (i) why templates have different values and (ii) why vacuuming a
database has changed the situation of all the other databases?
I digged in the logs, and at server restart (after single user mode),
I found a lot of rows related to "aggressive automatic vacuum" that
involved all databases, including templtes:

% sudo grep automatic $PGDATA/log/postgresql.log | grep template1 | head -n 5
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_statistic": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_type": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_foreign_server": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_authid": index scans: 0
LOG:  automatic aggressive vacuum to prevent wraparound of table
"template1.pg_catalog.pg_statistic_ext_data": index scans: 0

and similar rows about other databases exist. Again, I'm not able to
figure out the differences in ages then.

Thanks,
Luca

Frequetly updated partial index leads to bloat on index for Postresql 11

[Tom Dearman]

Hi,
We have a partial index on a column of the form:

CREATE TABLE table_p2021q1 (
  pk_id BIGINT,
  col1 BIGINT NOT NULL,
  status character varying(255) NOT NULL,
  ...other columns
  PRIMARY KEY (pk_id)
);

CREATE INDEX table_p2021q1_ix04 ON table_p2021q1 (col1) WHERE status =
'IN_PROGRESS';

(the table is also a partitioned table on a date field)
Every one of our entries in the table will go through a state where
status = 'IN_PROGRESS' but there are only a few hundred of them at any
one time.  The index grows quite quickly to about 300MB.  I have run
some bloat queries and the size should always be 8KB and bloat is
always high (99.99% bloat).  We have found that the only way to
reduce the bloat is through reindexing, but after a couple of days the
size goes back to 300MB or so.  Running a vacuum does not reduce the
bloat.

Is this a known issue, are they any ways around it, and if it is an
issue is there a plan to fix it if a fix is possible?

Thanks.

postgresql order lowercase before uppercase

[basti]

Hello,
I need to as follow:

ande
Amma
Anit
Anti
Brac
Cali


I have try ORDER by name COLLATE 'C';

but this order as follow:

Amma
Anit
Anti
Brac
Cali

ande

Best Regards

Open source licenses

[DAVID Nicolas]

Dear PostgreSQL Team,



We are a software editor that historically use PostgreSQL for one of our 
product.

We currently use the version 9.6 since many years and now we would like to 
update to the last version 13.2.

However, before that, we would like to check some points regarding the embedded 
components and their licenses.



First, we install PostgreSQL with our installer using the Zip archive of 
binaries (for Windows) provided by EDB (available from your website).

It seems that the EDB Zip archive embed PgAdmin and StackBuilder in addition to 
the PostgreSQL server.

Do you know if some others modules are added by EDB ?



Then, it appears that the PostgreSQL server links some open source components 
that are not under the PostgreSQL license( ex: openssl, libcharset, ...).

Could you please provide a list of the components included in the PostgreSQL 
server, with the OpenSource license type for each component ? Or even, if 
possible, with the license file for each component ?

Best regards,

Nicolas DAVID
WORKNC DENTAL Project Manager
Manufacturing Intelligence division
Hexagon
E: nicolas.da...@hexagon.com

Hexagon
440 Route des Allogneraies
71850 Charnay-les-Mâcon
France
HexagonMI.com | 
LinkedIn 
| Facebook | 
Twitter

CONFIDENTIALITY NOTICE: This email and any attachments may be confidential and 
protected by legal privilege. If you are not the intended recipient, be aware 
that any disclosure, copying, distribution or use of the e-mail or any 
attachment is prohibited. If you have received this email in error, please 
notify us immediately by replying to the sender and deleting this copy and the 
reply from your system. Thank you for your cooperation. Please note all the 
views and opinions published here are solely based on the author's own opinion 
and should not be considered necessarily as reflecting the opinion of Hexagon 
Manufacturing Intelligence.



-Original Message-
From: Simon Riggs 
Sent: 17 March 2021 18:57
To: DAVID Nicolas 
Cc: secur...@postgresql.org
Subject: Re: Contact



This email is not from Hexagon's Office 365 instance. Please be careful while 
clicking links, opening attachments, or replying to this email.





On Wed, 17 Mar 2021 at 17:29, DAVID Nicolas 
mailto:nicolas.da...@hexagon.com>> wrote:

>

> I use secur...@postgresql.org because I 
> cannot find suitable mail address on the web site.

>

> Is there a mail address to request some information regarding the open source 
> licences of the different postgresql components?



The licence for all software available on postgresql.org is shown here

https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fabout%2Flicence%2F&data=04%7C01%7C%7Ce0c997c9e991479cadf708d8e96e15c6%7C1b16ab3eb8f64fe39f3e2db7fe549f6a%7C0%7C1%7C637516006314187203%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=QL85rotciGB22m6ZOFWusHLh63pn0eXh6c%2FISLmIpfk%3D&reserved=0



The wider PostgreSQL ecosystem consists of many optional extensions and tools, 
both open and closed source, each of which has different licences. There is no 
single central place or authority that lists or controls those components and 
their respective licences.  Some are listed here: 
https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdownload%2Fproduct-categories%2F&data=04%7C01%7C%7Ce0c997c9e991479cadf708d8e96e15c6%7C1b16ab3eb8f64fe39f3e2db7fe549f6a%7C0%7C1%7C637516006314197196%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=Uzh1jvV%2BxHsfufp0qynDPEWuvkzJpD%2FcQqoTVp0eLcw%3D&reserved=0



This is the wrong place to request or discuss such information. Please try 
pgsql-gene...@postgresql.org



--

Simon Riggs
https://eur02.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.enterprisedb.com%2F&data=04%7C01%7C%7Ce0c997c9e991479cadf708d8e96e15c6%7C1b16ab3eb8f64fe39f3e2db7fe549f6a%7C0%7C1%7C637516006314197196%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=1sguhDYtJZvVv4om3IjP1FFn4AbmNM0sKs6spwI6FQs%3D&reserved=0

RE: postgresql order lowercase before uppercase

[Basques, Bob (CI-StPaul)]

Maybe check out using  the UPPER/LOWER/INITCAP functions in the order by clause?

Bobb



> -Original Message-
> From: basti 
> Sent: Thursday, March 18, 2021 9:40 AM
> To: pgsql-gene...@postgresql.org
> Subject: postgresql order lowercase before uppercase
> 
> Think Before You Click: This email originated outside our organization.
> 
> 
> Hello,
> I need to as follow:
> 
> ande
> Amma
> Anit
> Anti
> Brac
> Cali
> 
> 
> I have try ORDER by name COLLATE 'C';
> 
> but this order as follow:
> 
> Amma
> Anit
> Anti
> Brac
> Cali
> 
> ande
> 
> Best Regards
> 

Re: postgresql order lowercase before uppercase

[basti]

This does not help in that case.

On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:

Maybe check out using  the UPPER/LOWER/INITCAP functions in the order by clause?

Bobb




-Original Message-
From: basti 
Sent: Thursday, March 18, 2021 9:40 AM
To: pgsql-gene...@postgresql.org
Subject: postgresql order lowercase before uppercase

Think Before You Click: This email originated outside our organization.


Hello,
I need to as follow:

ande
Amma
Anit
Anti
Brac
Cali


I have try ORDER by name COLLATE 'C';

but this order as follow:

Amma
Anit
Anti
Brac
Cali

ande

Best Regards

Re: postgresql order lowercase before uppercase

[Marc Millas]

to me, collate 'C' ask for the raw ascii order which put caps before
because the hexa coding is lower
did you try any thing else (POSIX is same as 'C')

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Mar 18, 2021 at 4:18 PM basti  wrote:

> This does not help in that case.
>
> On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
> > Maybe check out using  the UPPER/LOWER/INITCAP functions in the order by
> clause?
> >
> > Bobb
> >
> >
> >
> >> -Original Message-
> >> From: basti 
> >> Sent: Thursday, March 18, 2021 9:40 AM
> >> To: pgsql-gene...@postgresql.org
> >> Subject: postgresql order lowercase before uppercase
> >>
> >> Think Before You Click: This email originated outside our organization.
> >>
> >>
> >> Hello,
> >> I need to as follow:
> >>
> >> ande
> >> Amma
> >> Anit
> >> Anti
> >> Brac
> >> Cali
> >> 
> >>
> >> I have try ORDER by name COLLATE 'C';
> >>
> >> but this order as follow:
> >>
> >> Amma
> >> Anit
> >> Anti
> >> Brac
> >> Cali
> >> 
> >> ande
> >>
> >> Best Regards
> >>
> >
>
>
>

Re: postgresql order lowercase before uppercase

[Ron]

Exactly.  "C" collation is the opposite of what he wants.

OP needs something like "de_DE".

On 3/18/21 10:36 AM, Marc Millas wrote:
to me, collate 'C' ask for the raw ascii order which put caps before 
because the hexa coding is lower

did you try any thing else (POSIX is same as 'C')

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 



On Thu, Mar 18, 2021 at 4:18 PM basti > wrote:


This does not help in that case.

On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
> Maybe check out using  the UPPER/LOWER/INITCAP functions in the
order by clause?
>
> Bobb
>
>
>
>> -Original Message-
>> From: basti mailto:mailingl...@unix-solution.de>>
>> Sent: Thursday, March 18, 2021 9:40 AM
>> To: pgsql-gene...@postgresql.org 
>> Subject: postgresql order lowercase before uppercase
>>
>> Think Before You Click: This email originated outside our organization.
>>
>>
>> Hello,
>> I need to as follow:
>>
>> ande
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> 
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> 
>> ande
>>
>> Best Regards
>>
>




--
Angular momentum makes the world go 'round.

Re: postgresql order lowercase before uppercase

[Marc Millas]

or, maybe, just nothing ?

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Thu, Mar 18, 2021 at 4:44 PM Ron  wrote:

> Exactly.  "C" collation is the opposite of what he wants.
>
> OP needs something like "de_DE".
>
> On 3/18/21 10:36 AM, Marc Millas wrote:
>
> to me, collate 'C' ask for the raw ascii order which put caps before
> because the hexa coding is lower
> did you try any thing else (POSIX is same as 'C')
>
> Marc MILLAS
> Senior Architect
> +33607850334
> www.mokadb.com
>
>
>
> On Thu, Mar 18, 2021 at 4:18 PM basti 
> wrote:
>
>> This does not help in that case.
>>
>> On 18.03.21 15:45, Basques, Bob (CI-StPaul) wrote:
>> > Maybe check out using  the UPPER/LOWER/INITCAP functions in the order
>> by clause?
>> >
>> > Bobb
>> >
>> >
>> >
>> >> -Original Message-
>> >> From: basti 
>> >> Sent: Thursday, March 18, 2021 9:40 AM
>> >> To: pgsql-gene...@postgresql.org
>> >> Subject: postgresql order lowercase before uppercase
>> >>
>> >> Think Before You Click: This email originated outside our organization.
>> >>
>> >>
>> >> Hello,
>> >> I need to as follow:
>> >>
>> >> ande
>> >> Amma
>> >> Anit
>> >> Anti
>> >> Brac
>> >> Cali
>> >> 
>> >>
>> >> I have try ORDER by name COLLATE 'C';
>> >>
>> >> but this order as follow:
>> >>
>> >> Amma
>> >> Anit
>> >> Anti
>> >> Brac
>> >> Cali
>> >> 
>> >> ande
>> >>
>> >> Best Regards
>> >>
>> >
>>
>>
>>
> --
> Angular momentum makes the world go 'round.
>

Re: postgresql order lowercase before uppercase

[Laurenz Albe]

On Thu, 2021-03-18 at 15:39 +0100, basti wrote:
> I need to as follow:
> 
> ande
> Amma
> Anit
> Anti
> Brac
> Cali
> 
> 
> I have try ORDER by name COLLATE 'C';
> 
> but this order as follow:
> 
> Amma
> Anit
> Anti
> Brac
> Cali
> 
> ande

Create an ICU collation:

  CREATE COLLATION inv (PROVIDER = icu, LOCALE = "en-US@CaseFirst=LowerFirst");

Then use

  ORDER BY name COLLATE inv

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

Re: Frequetly updated partial index leads to bloat on index for Postresql 11

[Peter Geoghegan]

On Thu, Mar 18, 2021 at 6:51 AM Tom Dearman  wrote:
> Is this a known issue, are they any ways around it, and if it is an
> issue is there a plan to fix it if a fix is possible?

It's not exactly a known issue per se, but I think the problem here is
related to the fact that you have lots of duplicates, which did
perform rather badly prior to Postgres 12. I bet that you'd benefit
from upgrading to Postgres 12, or especially to Postgres 13. The
B-Tree space management is a lot better now. (Actually, it'll be
better again in Postgres 14.)

-- 
Peter Geoghegan

Re: Open source licenses

[Adrian Klaver]

On 3/18/21 6:32 AM, DAVID Nicolas wrote:

Dear PostgreSQL Team,

We are a software editor that historically use PostgreSQL for one of our 
product.


We currently use the version 9.6 since many years and now we would like 
to update to the last version 13.2.


However, before that, we would like to check some points regarding the 
embedded components and their licenses.


First, we install PostgreSQL with our installer using the Zip archive of 
binaries (for Windows) provided by EDB (available from your website).


It seems that the EDB Zip archive embed PgAdmin and StackBuilder in 
addition to the PostgreSQL server.


Do you know if some others modules are added by EDB ?


That is it:

~/postgresql-13.2-1-windows-x64-binaries/pgsql> ls
bin  doc  include  lib  pgAdmin 4  share  StackBuilder  symbols



Then, it appears that the PostgreSQL server links some open source 
components that are not under the PostgreSQL license( ex: openssl, 
libcharset, ...).


Could you please provide a list of the components included in the 
PostgreSQL server, with the OpenSource license type for each component ? 
Or even, if possible, with the license file for each component ?


That is going to depend on what the settings where when the source was 
compiled. The question is what is your concern?




Best regards,

**

*Nicolas DAVID**
*WORKNC DENTAL Project Manager

Manufacturing Intelligence division

*Hexagon*

*E:***_nicolas.da...@hexagon.com _

__

Hexagon

440 Route des Allogneraies

71850 Charnay-les-Mâcon

France

_HexagonMI.com _|_LinkedIn 
_|_Facebook 
_ |_Twitter 
_





--
Adrian Klaver
adrian.kla...@aklaver.com

PITR for an only object in postgres

[aslı cokay]

Hi all,

Is there any way to get for example a table version of 5 minutes ago, or we
should PITR to 5 minutes ago, dump the table and restore with this dump ?

Thanks.

WAL-G shipping to the cloud

[aslı cokay]

Hi all,

I'd like to get postgres log files to the cloud but i want this process
going live i want to automate test process and after that i want it go
live.

What i have in my mind is setting archive_mode is on and archiving_command
with a script. After that i want to execute pgbench to get more WAL files
and trigger with pg_switch_wal() to change WAL files.

Is there any idea do you have about it or is there anyone shipped their log
to the cloud to give me an idea?

Thanks.

Re: WAL-G shipping to the cloud

[Adrian Klaver]

On 3/18/21 2:18 PM, aslı cokay wrote:

Hi all,

I'd like to get postgres log files to the cloud but i want this process 
going live i want to automate test process and after that i want it go 
live.


What i have in my mind is setting archive_mode is on and 
archiving_command with a script. After that i want to execute pgbench to 
get more WAL files and trigger with pg_switch_wal() to change WAL files.


Is there any idea do you have about it or is there anyone shipped their 
log to the cloud to give me an idea?


https://github.com/wal-e/wal-e



Thanks.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: WAL-G shipping to the cloud

[Bruce Momjian]

On Thu, Mar 18, 2021 at 05:18:30PM -0400, aslı cokay wrote:
> Hi all,
> 
> I'd like to get postgres log files to the cloud but i want this process going
> live i want to automate test process and after that i want it go live. 
> 
> What i have in my mind is setting archive_mode is on and archiving_command 
> with
> a script. After that i want to execute pgbench to get more WAL files and
> trigger with pg_switch_wal() to change WAL files.
> 
> Is there any idea do you have about it or is there anyone shipped their log to
> the cloud to give me an idea? 

Well, there is Wal-E and Wal-G, which archive to the cloud. 
pg_backreset also supports cloud WAL storage.

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

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

https://wiki.postgresql.org/wiki/Pg_depend_display and custom types as function parameters

[Franz-Josef Färber]

Hi,

I hope I am right here for a question on the wiki site
https://wiki.postgresql.org/wiki/Pg_depend_display .

The views on that web page build dependency information, but do not seem to
take into account function parameters of custom data types - i. e. CREATE
FUNCTION x(myparam mytype) should be dependent on CREATE TYPE mytype ?

Regards,
fjf2002

Drop Database

[Gustavo FERREYRA]

Hello, a request, which may be resolved in another way and I did not see it.
When doing a BACKUP plain and using "Include DROP DATABASE statement",
could you add by default "IF EXISTS" ?
Because when doing a restore, if the database does not exist, it gives an
error and you have to edit the file to comment out the line.
Thanks for your work and congratulations, it is a very useful and beautiful
tool.
Greetings cordially.

Re: postgresql order lowercase before uppercase

[basti]

Am 18.03.21 um 17:19 schrieb Laurenz Albe:
> On Thu, 2021-03-18 at 15:39 +0100, basti wrote:
>> I need to as follow:
>>
>> ande
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> 
>>
>> I have try ORDER by name COLLATE 'C';
>>
>> but this order as follow:
>>
>> Amma
>> Anit
>> Anti
>> Brac
>> Cali
>> 
>> ande
> 
> Create an ICU collation:
> 
>   CREATE COLLATION inv (PROVIDER = icu, LOCALE = 
> "en-US@CaseFirst=LowerFirst");
> 
> Then use
> 
>   ORDER BY name COLLATE inv
> 
> Yours,
> Laurenz Albe
> 

I have try the inv collate, that does not work on PG 9.6 and 10.

Order is the same as without collate.

SELECT a.name
FROM foo as a
LEFT JOIN (SELECT name from foo WHERE name = 'lowercase name') as b
on a.name = b.name
ORDER by b.name,a.name

does it.

perhaps there is a better way?
There is only one name in lowercase and this one must be the first one.

Re: Drop Database

[Adrian Klaver]

On 3/18/21 3:39 PM, Gustavo FERREYRA wrote:

Hello, a request, which may be resolved in another way and I did not see it.
When doing a BACKUP plain and using "Include DROP DATABASE statement",
could you add by default "IF EXISTS" ?


https://www.postgresql.org/docs/12/app-pgdump.html

--if-exists

Use conditional commands (i.e., add an IF EXISTS clause) when 
cleaning database objects. This option is not valid unless --clean is 
also specified.




Because when doing a restore, if the database does not exist, it gives 
an error and you have to edit the file to comment out the line.


Which should not be causing an issue.


Thanks for your work and congratulations, it is a very useful and 
beautiful tool.

Greetings cordially.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: WAL-G shipping to the cloud

[asli cokay]

Thank you, Bruce. Actually I mean I am using WAL-G what I am curious about
is that I want to test shipping process automate, and I am looking for the
ideas. But thanks for your help.

Bruce Momjian , 18 Mar 2021 Per, 17:24 tarihinde şunu
yazdı:

> On Thu, Mar 18, 2021 at 05:18:30PM -0400, aslı cokay wrote:
> > Hi all,
> >
> > I'd like to get postgres log files to the cloud but i want this process
> going
> > live i want to automate test process and after that i want it go live.
> >
> > What i have in my mind is setting archive_mode is on and
> archiving_command with
> > a script. After that i want to execute pgbench to get more WAL files and
> > trigger with pg_switch_wal() to change WAL files.
> >
> > Is there any idea do you have about it or is there anyone shipped their
> log to
> > the cloud to give me an idea?
>
> Well, there is Wal-E and Wal-G, which archive to the cloud.
> pg_backreset also supports cloud WAL storage.
>
> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   If only the physical world exists, free will is an illusion.
>
>