How to Change collate & ctype for an existing database?

[Daulat Ram]

Hi,

How we can change the   Collate &  Ctype from "c"  to  "C.UTF-8" on existing 
production database.

SELECT datcollate FROM pg_database WHERE datname='wwkidbt';

datcollate



C
postgres=# select version();
 version
--
PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit



Please advice about the below method:

update pg_database set datcollate='C.UTF-8', datctype='C.UTF-8'  where 
datname='wwkidbt';

Is there any impact on data/corruption if we do via update command ?

Thanks,

Daulat

Re: How to Change collate & ctype for an existing database?

[Pavel Stehule]

Hi

pá 13. 12. 2019 v 9:57 odesílatel Daulat Ram 
napsal:

> Hi,
>
> How we can change the   Collate &  Ctype from “c”  to  “C.UTF-8” on
> existing production database.
>
> SELECT datcollate FROM pg_database WHERE datname='wwkidbt';
>
> datcollate
>
> 
>
> C
>
> postgres=# select version();
>
>  version
>
>
> --
>
>
> PostgreSQL 9.5.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 5.3.1-14ubuntu2) 5.3.1 20160413, 64-bit
>
>
There is not any official setup how to doit other than just pg_dump and
load.


>
>
> Please advice about the below method:
>
> update pg_database set datcollate='C.UTF-8', datctype='C.UTF-8'  where
> datname='wwkidbt';
>
> Is there any impact on data/corruption if we do via update command ?
>
sure - almost all your indexes will be broken. Direct update of system
tables is usually really bad idea.

Regards

Pavel


> Thanks,
>
> Daulat
>
>
>
>
>
>
>
>
>

Re: Tuple concurrency issue in large objects

[Daniel Verite]

Shalini wrote:

> > Is there a workaround to this concurrency issue without creating a
> > new   large object?

The transaction failing with the "Tuple concurrently updated"
error could be resubmitted by the client, as if it was a
serialization failure.
Or the failure could be prevented by using advisory locks:
https://www.postgresql.org/docs/current/explicit-locking.html


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

[İlyas Derse]

In MSSQL I am using sp_getapplock, sp_releaseapplock.

How can I achieve the same functionality in PostgreSql?

Re: Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

[Andrei Zhidenkov]

I’m not sure what these function exactly do in MSSQL but what in Postgres you 
probably can use “Advisory Locks” 
(https://www.postgresql.org/docs/9.4/explicit-locking.html 
) in order to achive 
this.

> On 13. Dec 2019, at 13:31, İlyas Derse  wrote:
> 
> sp_getapplock

Re: Is there an equivalent to sp_getapplock, sp_releaseapplock in PostgreSql?

[Justin]

Hi Ityas

Advisory locks do not act like the locks in MSSQL,  Postgresql will ignore
advisory locks for other transactions,  its up the applications layer to
poll/ obey/ enforce advisory locks

You can do the type of locking as  in MSSQL with Postgresql but its avoided
in practice as that is the point of MVCC,  allow the database to handle
locking/updating of resources.  Odds of Deadlocks go way down...

Postgresql has always run in the optional MSSQL Snap Shot Isolation Mode
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

I think MSSQL is now the only major database still shipped with MVCC turned
off..
https://www.postgresql.org/docs/current/mvcc-intro.html

On Fri, Dec 13, 2019 at 9:06 AM Andrei Zhidenkov 
wrote:

> I’m not sure what these function exactly do in MSSQL but what in Postgres
> you probably can use “Advisory Locks” (
> https://www.postgresql.org/docs/9.4/explicit-locking.html) in order to
> achive this.
>
> On 13. Dec 2019, at 13:31, İlyas Derse  wrote:
>
> sp_getapplock
>
>
>