Fixed chars

[Enrico Pirozzi]

Hi , I've seen this strange thing.

sitedb=# create table test_tb(codice char(7));
CREATE TABLE
sitedb=# insert into test_tb values('pippo');
INSERT 0 1
sitedb=# select codice || 'a'::char(1),length(codice) from test_tb
 ?column? | length
--+
 pippoa   |  5
(1 row)


On the official documentation

|character(/|n|/)|,|char(/|n|/)| fixed-length, blank padded

https://www.postgresql.org/docs/10/static/datatype-character.html


Can anyone help me?

Enrico

--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: Fixed chars

[Enrico Pirozzi]

Resolved...my missing


Thanks

Enrico


https://www.postgresql.org/docs/10/static/datatype-character.html

"Values of type|character|are physically padded with spaces to the 
specified width/|n|/, and are stored and displayed that way. However, 
trailing spaces are treated as semantically insignificant and 
disregarded when comparing two values of type|character|. In collations 
where whitespace is significant, this behavior can produce unexpected 
results; for example|SELECT 'a '::CHAR(2) collate "C" < 
E'a\n'::CHAR(2)|returns true, even though|C|locale would consider a 
space to be greater than a newline. Trailing spaces are removed when 
converting a|character|value to one of the other string types. Note that 
trailing spaces/are/semantically significant in|character 
varying|and|text|values, and when using pattern matching, that 
is|LIKE|and regular expressions."



Il 28/03/2018 11:32, Andreas Kretschmer ha scritto:



Am 28.03.2018 um 11:11 schrieb Enrico Pirozzi:

Hi , I've seen this strange thing.

sitedb=# create table test_tb(codice char(7));
CREATE TABLE
sitedb=# insert into test_tb values('pippo');
INSERT 0 1
sitedb=# select codice || 'a'::char(1),length(codice) from test_tb
 ?column? | length
--+
 pippoa   |  5
(1 row)



where is the problem? length() returns the number of chars in string, 
and the string in codice is 5 chars long.





On the official documentation

|character(/|n|/)|,|char(/|n|/)| fixed-length, blank padded

https://www.postgresql.org/docs/10/static/datatype-character.html


test=*# select length('12345'::char(10)), 
pg_column_size('12345'::char(10));

 length | pg_column_size
+----
  5 | 14
(1 Zeile)



helps that?


Regards, Andreas



--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: New website

[Enrico Pirozzi]

Hi Adrian,
I had the same problem too, but only on Chrome

Regards,

Enrico


Il 19/04/2018 00:03, Adrian Klaver ha scritto:
I would contact the Webmaster but Contact goes to a big image of an 
elephant head. That is also where Downloads, Support and Donate lands. 
Might have been a good idea to roll out a demo site for testing first. 
Will reserve judgment on the site design until it is functioning.




--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: Problem Postgres

[Enrico Pirozzi]

Hi Emanuele,

you can post here in italian :)

http://lists.psql.it/mailman/listinfo

Regards
Enrico

Il 26/06/2018 10:45, Fabio Pardi ha scritto:


Hi Emanuele,


For the next time, is best if you post to an italian mailing list if 
your log messages are in italian. Else change settings in order to 
have english errors.


Back to your problem:

Looks like permissions on your installation are not OK, and have been 
manipulated from the default ones. The fact that happens every 20 
minutes, means maybe something 'scheduled' is occurring, like, eg, a 
query (maybe from your monitoring system), or a checkpoint.



Could you please:

* post the full logfile of the next line, the one on 'ISTRUZIONE' and 
anything else you think is relevant in the logs


* tell us more about your installation (from package, compiled, which 
version of Postres and OS, if you did anything special after installation)


*post the permissions of the folder 'base' and those of 
base/16395/19739338


* tell us under which user is postgres running


regards,

fabio pardi



On 26/06/18 10:05, Emanuele Musella wrote:

Good morning,

we have the following error:

2018-06-26 09:48:44 CEST ERRORE:  non è stato possibile ottenere 
informazioni sul file "base/16395/19739338": Permission denied
2018-06-26 09:48:44 CEST ISTRUZIONE:  select 
p.datname,pg_database_size(p.datname) from pg_database p


This error repetly every 20 minutes.

Can you help me?

Regards

Emanuele Musella



--
Questo messaggio è stato analizzato con Libra ESVA ed è risultato non 
infetto.




--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Re: postgresql api

[Enrico Pirozzi]

Hi,

Have you tried to query the pg_stat_replication?


Enrico


Il lun 17 set 2018, 11:52 vyshu Ysh  ha scritto:

> Hi Team,
>
> Is there any postgresql API which tells when the master/standby server is
> down ?
>
> Thanks,
> Sudha
>

Foreign keys and locks.

[Enrico Pirozzi]

Hi all,
Today I found this behavior on two tables.

I have 2 tables t2 and t3 , described as below:

testdb=# \d t2
 Tabella "public.t2"
 Colonna |  Tipo   | Ordinamento | Può essere null | Default
-+-+-+-+-
 id  | integer | | not null|
 value   | integer | | |
Indici:
"t2_id_p_key" PRIMARY KEY, btree (id)
"t2_rate_idx" btree (value)
Referenziato da:
TABLE "t3" CONSTRAINT "t3_roomrate_fkey" FOREIGN KEY (value) REFERENCES
t2(id) ON UPDATE CASCADE ON DELETE CASCADE

testdb=# \d t3
 Tabella "public.t3"
 Colonna |  Tipo   | Ordinamento | Può essere null | Default
-+-+-+-+-
 id  | integer | | not null|
 value   | integer | | |
Indici:
"t3_id_p_key" PRIMARY KEY, btree (id)
Vincoli di integrità referenziale
"t3_roomrate_fkey" FOREIGN KEY (value) REFERENCES t2(id) ON UPDATE
CASCADE ON DELETE CASCADE

the two tables are populated with records that satisfy the referential
integrity constraints.

1) If I execute

testdb=# delete from t2 where id=1978800 ;

it  works;

2) but if I execute
testdb=# delete from t2 where value=20342;

postgresql applies locks and the query freezes.

testdb=# SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, l.pid, granted,a.query
FROM pg_catalog.pg_locks l
JOIN pg_stat_activity a on a.pid=l.pid
LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'testdb')
AND NOT l.pid = pg_backend_pid()
and a.state = 'active';
 locktype |  relation   |   mode   | tid |  vtid  | pid  | granted
|   query
--+-+--+-++--+-+---
 relation | t3_id_p_key | RowExclusiveLock | | 9/1475 | 8685 | t
| delete from t2 where value=20342;
 relation | t3  | RowExclusiveLock | | 9/1475 | 8685 | t
| delete from t2 where value=20342;
 relation | t2_rate_idx | RowExclusiveLock | | 9/1475 | 8685 | t
| delete from t2 where value=20342;
 relation | t2_id_p_key | RowExclusiveLock | | 9/1475 | 8685 | t
| delete from t2 where value=20342;
 relation | t2  | RowExclusiveLock | | 9/1475 | 8685 | t
| delete from t2 where value=20342;


I tried to check the problem on other tables but on other tables I did not
find the same behavior
and the second type of query works without problems.

Any Ideas?

Thanks in advance for your reply

Enrico

Re: Foreign keys and locks.

[Enrico Pirozzi]

Solved, it was an index that didn't work well :)

Enrico

Il giorno mer 20 nov 2019 alle ore 15:16 Enrico Pirozzi 
ha scritto:

> Hi all,
> Today I found this behavior on two tables.
>
> I have 2 tables t2 and t3 , described as below:
>
> testdb=# \d t2
>  Tabella "public.t2"
>  Colonna |  Tipo   | Ordinamento | Può essere null | Default
> -+-+-+-+-
>  id  | integer | | not null|
>  value   | integer | | |
> Indici:
> "t2_id_p_key" PRIMARY KEY, btree (id)
> "t2_rate_idx" btree (value)
> Referenziato da:
> TABLE "t3" CONSTRAINT "t3_roomrate_fkey" FOREIGN KEY (value)
> REFERENCES t2(id) ON UPDATE CASCADE ON DELETE CASCADE
>
> testdb=# \d t3
>  Tabella "public.t3"
>  Colonna |  Tipo   | Ordinamento | Può essere null | Default
> -+-+-+-+-
>  id  | integer | | not null|
>  value   | integer | | |
> Indici:
> "t3_id_p_key" PRIMARY KEY, btree (id)
> Vincoli di integrità referenziale
> "t3_roomrate_fkey" FOREIGN KEY (value) REFERENCES t2(id) ON UPDATE
> CASCADE ON DELETE CASCADE
>
> the two tables are populated with records that satisfy the referential
> integrity constraints.
>
> 1) If I execute
>
> testdb=# delete from t2 where id=1978800 ;
>
> it  works;
>
> 2) but if I execute
> testdb=# delete from t2 where value=20342;
>
> postgresql applies locks and the query freezes.
>
> testdb=# SELECT locktype, relation::regclass, mode, transactionid AS tid,
> virtualtransaction AS vtid, l.pid, granted,a.query
> FROM pg_catalog.pg_locks l
> JOIN pg_stat_activity a on a.pid=l.pid
> LEFT JOIN pg_catalog.pg_database db
> ON db.oid = l.database WHERE (db.datname = 'testdb')
> AND NOT l.pid = pg_backend_pid()
> and a.state = 'active';
>  locktype |  relation   |   mode   | tid |  vtid  | pid  | granted
> |   query
>
> --+-+--+-++--+-+---
>  relation | t3_id_p_key | RowExclusiveLock | | 9/1475 | 8685 | t
> | delete from t2 where value=20342;
>  relation | t3  | RowExclusiveLock | | 9/1475 | 8685 | t
> | delete from t2 where value=20342;
>  relation | t2_rate_idx | RowExclusiveLock | | 9/1475 | 8685 | t
> | delete from t2 where value=20342;
>  relation | t2_id_p_key | RowExclusiveLock | | 9/1475 | 8685 | t
> | delete from t2 where value=20342;
>  relation | t2  | RowExclusiveLock |     | 9/1475 | 8685 | t
> | delete from t2 where value=20342;
>
>
> I tried to check the problem on other tables but on other tables I did not
> find the same behavior
> and the second type of query works without problems.
>
> Any Ideas?
>
> Thanks in advance for your reply
>
> Enrico
>
>

-- 

Enrico Pirozzi
Tel.  +39 0861 1855771 - Mob.+39 328 4164437 - Fax  +39 0861 1850310
http://www.pgtraining.com <http://www.pgtraining.com-> -
i...@pgtraining.com
www.enricopirozzi.info - i...@enricopirozzi.info
Skype sscotty71 - Gtalk sscott...@gmail.com

Re: PostegreSQL 9.2 to 9.6

[Enrico Pirozzi]

Hi Lucas I've made it,  from 9.2 to 12.2 using slony and it worked very
well :)

Enrico

Il sab 28 mar 2020, 13:29 Lucas Possamai  ha scritto:

> Hi guys.
>
> We need to upgrade (asap) our PostgreSQL 9.2 cluster. We run our cluster
> on AWS EC2 instances, and want to upgrade to RDS 9.6. Once in RDS, it makes
> easier for us to major upgrade to 11.
>
> Since this upgrade must be done with minimal downtime, my original idea
> was to use Bucardo .
>
> Does anybody have any better idea? please share.
> Just wanna make sure I'm going on the right path.
>
> Cluster size is 2TB.
>
> Cheers
> Lucas
>

Re: upgrade postgres 9.5 to 9.6

[Enrico Pirozzi]

Hi Atul,
you could use logical replication and do an hot upgrade between the 2 servers 
with a zero downtime ;)

If you want to use logical replication between a 9.5 , 9.6 version you could 
use for example pglogical.

You could achieve the same result using an external logic replication tool like 
slony, although pglogical is more powerful.

I hope that this can help you 

Regards,
Enrico

On Fri, 15 Jan 2021 18:47:51 +0530
Atul Kumar  wrote:

> Hi,
> 
> I want to upgrade my server from postgres 9.5 to 9.6, but my DB size
> is in TBs and I want to do it in minimum downtime (2-3 hours) so
> please help me how should I perform it.
> 
> 
> Please share the document, if possible, it will be grateful.
> 
> 
> 
> Regards,
> Atul

-- 
Enrico Pirozzi 

Re: migrating from Oracle to PostgreSQL 11

[Enrico Pirozzi]

Hi,

I think you can start from:

- https://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion

- 
https://severalnines.com/blog/migrating-oracle-postgresql-what-you-should-know


- http://ora2pg.darold.net/

- https://pgxn.org/dist/orafce/

Regards,

Enrico


Il 26/06/2019 15:42, Hitesh Chadda ha scritto:

Hi PostgresSQL Support,

I have to migrate from Oracle 11g to PostgresSQL 11.4. Please suggest 
best solution for doing the migration.


Regards

H.Kumar


--
Questo messaggio è stato analizzato con Libra ESVA ed è risultato non 
infetto.



--
Enrico Pirozzi
NBS Group S.r.l.
via Val Tiberina 23/A
63074, San Benedetto del Tronto (AP)
Tel. 0735-7626201

Connection type

[Enrico Pirozzi]

Hi,

is there a way to know what kind of connection a client is doing? 
(ODBC,JDBC..etc)


I saw the pg_stat_activity view,

but in the application name field there no infomation about
what kind of connection a client is doing.

Thanks

Enrico

e.piro...@nbsgroup.it

Re: Connection type

[Enrico Pirozzi]

Il 18/01/2018 17:19, Steve Atkins ha scritto:

A client can voluntarily set the application_name, e.g. as part of it's 
connection string,
to identify itself to the server, if you want to be able to identify which sort 
of client
is connected easily.


Thank you very much :)

Regards
Enrico

PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

Hi, I was doing a test on a 9.6 and I've seen a strange thing

if I run

create sequence test_sequence;
create view v_test_sequence as select * from test_sequence;

PostgreSQL creates a view based on the sequence and the strange thing  
is that, among the various fields that are created in the view,

there is a field named  sequence_name  with datatype  name.

I've seen in the docs 
https://www.postgresql.org/docs/9.6/static/datatype-character.html

and the datatype name should be reserved only for structures internal of PG.

Does anyone help me?

Enrico



--
Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

tmp1=# create sequence x;
CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
+-+---
  1 |   0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.


Yes but this appens on a 10.x version :)

Ciao
--

Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

tmp1=# create sequence x;

CREATE SEQUENCE
Time: 69,857 ms
tmp1=# select * from x;
last_value | log_cnt | is_called
+-+---
  1 |   0 | f
(1 row)

Maybe what you want to do is

create view v_test_sequence as select nextval( 'test_sequence' );

Ciao.


Yes but this happens on a 10.x version on a  9.6 version things are 
differents


Ciao
Enrico

--
Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

it's just to know why postgresql does not return a warning.


Il 24/01/2018 13:06, Vincenzo Romano ha scritto:

I haven't any v9 any more in my deployments. Sorry.

What is the objective of that view?
If it's to hide the nextval() function, then the solution can be
similar to mine.
If it's to access the sequence metainfo, then maybe it'd be better to
explore the pg_catalog schema.


--
Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

Yes the query is legal,but if you use pg_upgrade to upgrade from 9.6 to 
10.x , pg_upgrade will fail .


Enrico


Il 24/01/2018 13:11, Vincenzo Romano ha scritto:

Those queries are all legal. No warning is due.


--
Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

It is all documented here:
https://www.postgresql.org/docs/10/static/release-10.html#idm46428658049888

"...selecting from a sequence relation now returns only the three
fields named above."

And there's no way to keep portability from 9.x to 10.x

Thanks ;)

Enrico




--
Enrico Pirozzi
e.piro...@nbsgroup.it

Re: PostgreSQL 9.6: view based on sequence

[Enrico Pirozzi]

I found this db, it's not mine

Thanks ;)

Enrico


Il 25/01/2018 18:07, Peter Eisentraut ha scritto:

There is nothing from with user code using the data type "name".  It's
probably just not the best choice.  But if you create a view over a
system table, then you'll get whatever types the system table uses.
There is nothing from with that.


--
Enrico Pirozzi
e.piro...@nbsgroup.it