Re: Errors with schema migration and logical replication — expected?

[Mike Lissner]

On Tue, Dec 11, 2018 at 3:10 PM Adrian Klaver 
wrote:

>
> > Well, I was able to fix this by briefly allowing nulls on the
> > subscriber, letting it catch up with the publisher, setting all
> > nulls to empty strings (a Django convention), and then disallowing
> > nulls again. After letting it catch up, there were 118 nulls on the
> > subscriber in this column:
>
> So recap_sequence_number is not actually a number, it is a code?
>

It has sequential values, but they're not necessarily numbers.


>
> >
> > I appreciate all the responses. I'm scared to say so, but I think
> > this is a bug in logical replication. Somehow a null value appeared
> > at the subscriber that was never in the publisher.
> >
> > I also still have this question/suggestion from my first email:
> >
> >  > Is the process for schema migrations documented somewhere beyond
> > the above?
>
> Not that I know of. It might help, if possible, to detail the steps in
> the migration. Also what program you used to do it. Given that is Django
> I am assuming some combination of migrate, makemigrations and/or
> sqlmigrate.
>

Pretty simple/standard, I think:
 - Changed the code.
 - Generated the migration using manage.py makemigration
 - Generated the SQL using sqlmigrate
 - Ran the migration using manage.py migrate on the master and using psql
on the replica

Mike

Idle connections / sessions

[Oygun Josef]

Hi,

Is it possible to terminate idle connections/sessions automatically through a 
timeout in AWS or do I need to run a periodical cron job for this?

Postgres version: 9.6.6
Instance: db.t2.micro
RAM : 1GB

We are running a microservice architecture using docker with kubernetes and I 
can see that every pod on every node that has connected to the DB still has a 
idle connection as long as the node is still active even.

It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle 
connections.


Josef Oygun

Re: Idle connections / sessions

[Fabio Pardi]

Hi Josef,

please avoid cross posting to multiple lists.

I m not a developer, but I think that if you do not want idle connections, you 
should terminate them on the side they have been created.

If your application leaves the connection open, then you will notice idle 
connections on Postgres when not in use.

regards,

fabio pardi


On 12/12/2018 10:37, Oygun Josef wrote:
>
> Hi,
>
>  
>
> Is it possible to terminate idle connections/sessions automatically through a 
> timeout in AWS or do I need to run a periodical cron job for this?
>
>  
>
> Postgres version: 9.6.6
>
> Instance: db.t2.micro
>
> RAM : 1GB
>
>  
>
> We are running a microservice architecture using docker with kubernetes and I 
> can see that every pod on every node that has connected to the DB still has a 
> idle connection as long as the node is still active even.
>
>  
>
> It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle 
> connections.
>
>  
>
>  
>
> *Josef Oygun*
>
>  
>

Re: Idle connections / sessions

[Thomas Poty]

Hi Josef,

pg_terminator may help you.

thomas

Le mer. 12 déc. 2018 à 11:02, Fabio Pardi  a écrit :

> Hi Josef,
>
> please avoid cross posting to multiple lists.
>
> I m not a developer, but I think that if you do not want idle connections,
> you should terminate them on the side they have been created.
>
> If your application leaves the connection open, then you will notice idle
> connections on Postgres when not in use.
>
> regards,
>
> fabio pardi
>
>
> On 12/12/2018 10:37, Oygun Josef wrote:
>
> Hi,
>
>
>
> Is it possible to terminate idle connections/sessions automatically
> through a timeout in AWS or do I need to run a periodical cron job for this?
>
>
>
> Postgres version: 9.6.6
>
> Instance: db.t2.micro
>
> RAM : 1GB
>
>
>
> We are running a microservice architecture using docker with kubernetes
> and I can see that every pod on every node that has connected to the DB
> still has a idle connection as long as the node is still active even.
>
>
>
> It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle
> connections.
>
>
>
>
>
> *Josef Oygun*
>
>
>
>
>

SV: Idle connections / sessions

[Oygun Josef]

Hi,

Sorry for that!

Thank you for the answers, this is good but do you know if there is a way do to 
it through AWS console with some kind of configuration instead?

Reason for that is the we use terraform scripts to create and keep state of our 
instances so pg_terminator would require me to add every new db props to it.

/Josef


Från: Thomas Poty [mailto:thomas.p...@gmail.com]
Skickat: den 12 december 2018 11:11
Till: pgsql-general@lists.postgresql.org
Ämne: Re: Idle connections / sessions

Hi Josef,

pg_terminator may help you.

thomas

Le mer. 12 déc. 2018 à 11:02, Fabio Pardi 
mailto:f.pa...@portavita.eu>> a écrit :
Hi Josef,

please avoid cross posting to multiple lists.

I m not a developer, but I think that if you do not want idle connections, you 
should terminate them on the side they have been created.

If your application leaves the connection open, then you will notice idle 
connections on Postgres when not in use.

regards,

fabio pardi

On 12/12/2018 10:37, Oygun Josef wrote:
Hi,

Is it possible to terminate idle connections/sessions automatically through a 
timeout in AWS or do I need to run a periodical cron job for this?

Postgres version: 9.6.6
Instance: db.t2.micro
RAM : 1GB

We are running a microservice architecture using docker with kubernetes and I 
can see that every pod on every node that has connected to the DB still has a 
idle connection as long as the node is still active even.

It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle 
connections.


Josef Oygun

Does PostgreSQL use atomic file creation of FS?

[Dmitry Lazurkin]

Hello.

Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
catch situation when system crashes between open call and write call? I
am interesting in this because I would like use PostgreSQL on network
file system.

Thank you.

Re: Does PostgreSQL use atomic file creation of FS?

[Karsten Hilbert]

On Wed, Dec 12, 2018 at 02:48:12PM +0300, Dmitry Lazurkin wrote:

> Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
> catch situation when system crashes between open call and write call? I
> am interesting in this because I would like use PostgreSQL on network
> file system.

I doubt we can get more certainty than this:


https://www.postgresql.org/docs/devel/creating-cluster.html#CREATING-CLUSTER-NFS

Best,
Karsten
-- 
GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B

Re: Does PostgreSQL use atomic file creation of FS?

[Dmitry Lazurkin]

Thank you. But I have read this. I said about network file system only
for example. I would like to known how PostgreSQL handles this specific
case (of course if someone knowns a answer):

fd = open(file, "w");
write(fd, data);
// crash and now I have empty file which isn't correct
fsync(fd);

PS. I think PostgreSQL doesn't have this problem.

On 12/12/18 15:37, Karsten Hilbert wrote:
> On Wed, Dec 12, 2018 at 02:48:12PM +0300, Dmitry Lazurkin wrote:
>
>> Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
>> catch situation when system crashes between open call and write call? I
>> am interesting in this because I would like use PostgreSQL on network
>> file system.
> I doubt we can get more certainty than this:
>
>   
> https://www.postgresql.org/docs/devel/creating-cluster.html#CREATING-CLUSTER-NFS
>
> Best,
> Karsten

Re: Debian : No echo after pg_dump | psql

[Moreno Andreo]

Adrian, Andrew, thanks and apologies for the late reply

Il 30/11/2018 05:08, Andrew Gierth ha scritto:

"Moreno" == Moreno Andreo  writes:

  Moreno> The command I'm using is
  Moreno> root@x:~# pg_dump -v -C -h  -p 6543 -U postgres
  Moreno>  | psql -h localhost -p 6543 -U postgres 
  Moreno> It presents a double password prompt after I run it:
  Moreno> Password: Password for user postgres:

This is going to prompt once for the remote host's password and twice
for the local one (because -C), and the concurrently-running commands
are going to be fighting over access to the terminal to do it. Best
avoided by using pgpass or non-password-based auth methods.

More seriously, you're misunderstanding how -C works. When you use -C,
the database you specify to psql (or pg_restore) is NOT the database
you're restoring into - the restored db will ALWAYS have the same name
as it had when dumped (if that's not what you want then don't use -C).
Instead, the database you specify to psql or pg_restore is the database
to connect to to issue the CREATE DATABASE command, which should usually
be 'postgres'. That explains this bit:

  Moreno> If I create database (just database, not schema) on target
  Moreno> machine, I receive the error "database  already exists" but
  Moreno> the dump goes on If I don't create it, I receive the error
  Moreno> "database  does not exist" and processing aborts.

I removed -C usage since the target database (the database itself, not 
the schema) is created with a CREATE DATABASE before issuing pg_dump (so 
I don't need it), but strange prompt behavior remained the same


I'm having a really hard time these days, so I can't investigate 
further. Maybe on holidays, when I hope the pressure will be released a 
bit.


Will report as soon as I can.

THanks again

Cheers,

Moreno.-

REVOKE to an user that doesn't exist

[Moreno Andreo]

Hi all,

I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I 
don't think it matters).


At a certain point an error is thrown while parsing a trigger:

could not execute query: ERROR:  role "1067431" does not exist

command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Here's the evidence :-)

postgres=# \du
    List of roles
    Role name    |    Attributes    | 
Member of

--+-+---
 user5  | Create role, Create DB | {}
 user2     | Superuser, Create role, Create DB, Replication | {}
 user4 | Create role, Create DB | {}
 user3   | | {}
 user1  | Superuser, Create role, Create DB, Replication | {}
 postgres | Superuser, Create role, Create DB, Replication | {}

postgres=# select * from pg_roles;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb 
| rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | 
rolpassword | rolvaliduntil | rolconfig |   oid

--+--++---+-+--+-++--+-+---+---+-
 postgres | t    | t  | t | t   
| t    | t   | t |   -1 |     
|   |   |  10
 user1  | t    | t  | t | t | t    
| t   | t  |   -1 |     
|   |   |   16393
 user2     | t    | t  | t | t   | 
t    | t   | t  |   -1 |     
|   |   |   16394
 user3   | f    | t  | f | f | f    
| t   | f  |   -1 |     
|   |   |   16395
 user4 | f    | t  | t | t   | 
f    | t   | f  |   -1 |     
|   |   | 1067432
 user5  | f    | t  | t | t   | 
f    | t   | f |   -1 |     
|   |   |   30602

(6 rows)

So, no user with that OID. I checked in the trigger pointed to the error 
and I found


GRANT EXECUTE ON FUNCTION x() TO "1067431" WITH GRANT OPTION;

How to REVOKE that non-existing user so pg_upgrade can proceed?

thanks

Moreno.-

why would postgres be throttling a streaming replication slot's sending?

[Chris Withers]

On 11/12/2018 14:48, Achilleas Mantzios wrote:

On 11/12/18 4:00 μ.μ., Chris Withers wrote:


I'm looking after a multi-tenant PG 9.4 cluster, and we've started 
getting alerts for the number of WALs on the server.
It'd be great to understand what's generating all that WAL and what's 
likely to be causing any problems.\


Regarding you wals in pg_wal,  a good threshold could be anything more 
than a e.g. 10% increase from wal_keep_segments with a trend to go up. 
If this number goes up chances are something bad is happening.


wal_keep_segments is 0 here, so I went hunting in pg_replication_slots 
and found that it's the barman slot that's behind on the WAL.


strace'ing on the barman receiving side shows it waiting on the socket, 
so no problem on that side.


on the sending side, it's a little confusing, since the server is 
basically idle, no cpu or disk activity, and yet strace'ing the sending 
process attached to the other end of the socket shows time being spent 
waiting on a poll which, while it includes the socket being sent to, 
also includes a bunch of pipes. I've attached a chunk of the output 
below in the hope that someone on this list could offer an explanation 
as to what might cause the WAL to be trickling over this port rather 
than catching up as fast as it can?


cheers,

Chris

strace output for the streaming_barman slot servicing the barman 
replication slot:


--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1)   = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16)   = 1
read(6, 0x7fffdcd7, 1)  = -1 EAGAIN (Resource 
temporarily unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource 
temporarily unavailable)
read(5, 
"\3\1\0\0\7\326\355\2\343\0\0\0@\n\0\0\0\17\1\230\313\4\0\0)\347\372l\0\0\0\0"..., 
4272) = 4272
sendto(10, "d\0\0\20\315w\0\0\4\313\230\1\17P\0\0\4\313\230\1 
\0\0\2\37\321\343\345\306\20\3\1"..., 4302, 0, NULL, 0) = 4302
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource 
temporarily unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, 
events=POLLIN}], 3, 26076) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)

--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---
write(4, "\0", 1)   = 1
rt_sigreturn({mask=[]}) = -1 EINTR (Interrupted system call)
read(3, "\0", 16)   = 1
read(6, 0x7fffdcd7, 1)  = -1 EAGAIN (Resource 
temporarily unavailable)
recvfrom(10, 0x7fffdcaf, 1, 0, NULL, NULL) = -1 EAGAIN (Resource 
temporarily unavailable)
read(5, "~\320\5\0\3\0\0\0\0 
\1\230\313\4\0\0\3\0\0\0\0\0\0\0\323\316\314\0\0\0\0\0"..., 8192) = 8192
sendto(10, "d\0\0 \35w\0\0\4\313\230\1 
\0\0\0\4\313\230\1@\0\0\2\37\321\343\361\376\226~\320"..., 8222, 0, 
NULL, 0) = 8222
read(3, 0x7fffdc90, 16) = -1 EAGAIN (Resource 
temporarily unavailable)
poll([{fd=10, events=POLLIN}, {fd=3, events=POLLIN}, {fd=6, 
events=POLLIN}], 3, 25275) = ? ERESTART_RESTARTBLOCK (Interrupted by signal)

--- SIGUSR1 {si_signo=SIGUSR1, si_code=SI_USER, si_pid=3577, si_uid=26} ---

Interesting numbers:
- process 3577 is the wal writer process
- fd 10 is the socket, 3 and 6 are pipes
- time is visibly spent sitting on that poll
- the network is only seeing 2Mbit/sec, which is nothing

cheers,

Chris

Re: REVOKE to an user that doesn't exist

[Adrian Klaver]

On 12/12/18 5:11 AM, Moreno Andreo wrote:

Hi all,

I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I 
don't think it matters).


At a certain point an error is thrown while parsing a trigger:

could not execute query: ERROR:  role "1067431" does not exist

command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Here's the evidence :-)


The below is from the 9.1 cluster, correct?


postgres=# select * from pg_roles;
  rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb 
| rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | 
rolpassword | rolvaliduntil | rolconfig |   oid
--+--++---+-+--+-++--+-+---+---+- 

  postgres | t    | t  | t | t | 
t    | t   | t |   -1 |  |   
|   |  10
  user1  | t    | t  | t | t | t | 
t   | t  |   -1 |  |   
|   |   16393
  user2     | t    | t  | t | t   | 
t    | t   | t  |   -1 |  
|   |   |   16394
  user3   | f    | t  | f | f | f | 
t   | f  |   -1 |  |   
|   |   16395
  user4 | f    | t  | t | t   | 
f    | t   | f  |   -1 |  
|   |   | 1067432
  user5  | f    | t  | t | t   | 
f    | t   | f |   -1 |  |   
|   |   30602

(6 rows)

So, no user with that OID. I checked in the trigger pointed to the error 
and I found


GRANT EXECUTE ON FUNCTION x() TO "1067431" WITH GRANT OPTION;


I am not following as a trigger would not have that in its code.

Are you referring to the function x()?

If so is the GRANT in the function?




How to REVOKE that non-existing user so pg_upgrade can proceed?

thanks

Moreno.-







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

Re: Debian : No echo after pg_dump | psql

[Adrian Klaver]

On 12/12/18 4:51 AM, Moreno Andreo wrote:

Adrian, Andrew, thanks and apologies for the late reply

Il 30/11/2018 05:08, Andrew Gierth ha scritto:

"Moreno" == Moreno Andreo  writes:

  Moreno> The command I'm using is
  Moreno> root@x:~# pg_dump -v -C -h  -p 6543 -U 
postgres

  Moreno>  | psql -h localhost -p 6543 -U postgres 
  Moreno> It presents a double password prompt after I run it:
  Moreno> Password: Password for user postgres:

This is going to prompt once for the remote host's password and twice
for the local one (because -C), and the concurrently-running commands
are going to be fighting over access to the terminal to do it. Best
avoided by using pgpass or non-password-based auth methods.

More seriously, you're misunderstanding how -C works. When you use -C,
the database you specify to psql (or pg_restore) is NOT the database
you're restoring into - the restored db will ALWAYS have the same name
as it had when dumped (if that's not what you want then don't use -C).
Instead, the database you specify to psql or pg_restore is the database
to connect to to issue the CREATE DATABASE command, which should usually
be 'postgres'. That explains this bit:

  Moreno> If I create database (just database, not schema) on target
  Moreno> machine, I receive the error "database  already exists" but
  Moreno> the dump goes on If I don't create it, I receive the error
  Moreno> "database  does not exist" and processing aborts.

I removed -C usage since the target database (the database itself, not 
the schema) is created with a CREATE DATABASE before issuing pg_dump (so 
I don't need it), but strange prompt behavior remained the same


I'm having a really hard time these days, so I can't investigate 
further. Maybe on holidays, when I hope the pressure will be released a 
bit.


Understood.


Will report as soon as I can.

THanks again

Cheers,

Moreno.-







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

Re: REVOKE to an user that doesn't exist

[Tom Lane]

Moreno Andreo  writes:
> I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I 
> don't think it matters).

> At a certain point an error is thrown while parsing a trigger:
> could not execute query: ERROR:  role "1067431" does not exist
> command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
> GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Hm.  We've seen similar bugs before; the mechanism is that at some
point the function owner granted privileges to somebody else, and
at some later point the somebody-else role got dropped, but the
privilege grant stayed behind because the system had lost, or never
made, the pg_shdepend entry indicating that this function had an ACL
entry mentioning that role.  The extra ACL entry is harmless, until
you wonder why pg_dump is printing a nonsensical command due to it.

We fixed a couple of bugs of that ilk just last month [1], but they were
for cases involving types and schemas, not functions.  The last case
involving function privileges that I see in a quick trawl of the commit
log predates 9.0 release [2].  I wonder how old this cluster is ...

> How to REVOKE that non-existing user so pg_upgrade can proceed?

The safest way to clean it up manually would be to set the pg_proc.proacl
field for that function to NULL.  If there are other grants about the
function, you could try removing the bad entry, but it would likely be
safer to just re-grant after the upgrade.

regards, tom lane

[1] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=fa2952d8e
[2] https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=d691cb914

Re: REVOKE to an user that doesn't exist

[Moreno Andreo]

Il 12/12/2018 15:39, Adrian Klaver ha scritto:

On 12/12/18 5:11 AM, Moreno Andreo wrote:

Hi all,

I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I 
don't think it matters).


At a certain point an error is thrown while parsing a trigger:

could not execute query: ERROR:  role "1067431" does not exist

command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Here's the evidence :-)


The below is from the 9.1 cluster, correct?

Correct, 9.1.6, IIRC



postgres=# select * from pg_roles;
  rolname  | rolsuper | rolinherit | rolcreaterole | 
rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | 
rolconnlimit | rolpassword | rolvaliduntil | rolconfig |   oid
--+--++---+-+--+-++--+-+---+---+- 

  postgres | t    | t  | t | t | 
t    | t   | t |   -1 |  
|   |   |  10
  user1  | t    | t  | t | t | t | 
t   | t  |   -1 |  
|   |   |   16393
  user2     | t    | t  | t | t   | 
t    | t   | t |   -1 |  
|   |   |   16394
  user3   | f    | t  | f | f | f | 
t   | f  |   -1 |  
|   |   |   16395
  user4 | f    | t  | t | t   | 
f    | t   | f  |   -1 |  
|   |   | 1067432
  user5  | f    | t  | t | 
t   | f    | t   | f |   -1 | 
 |   |   |   30602

(6 rows)

So, no user with that OID. I checked in the trigger pointed to the 
error and I found


GRANT EXECUTE ON FUNCTION x() TO "1067431" WITH GRANT OPTION;


I am not following as a trigger would not have that in its code.

Are you referring to the function x()?

If so is the GRANT in the function?
Sorry, the grant above is extracted from the CREATE statement that 
PgAdmin3 shows when you click on the trigger

Re: Errors with schema migration and logical replication — expected?

[Adrian Klaver]

On 12/12/18 12:15 AM, Mike Lissner wrote:



On Tue, Dec 11, 2018 at 3:10 PM Adrian Klaver > wrote:



 >     Well, I was able to fix this by briefly allowing nulls on the
 >     subscriber, letting it catch up with the publisher, setting all
 >     nulls to empty strings (a Django convention), and then
disallowing
 >     nulls again. After letting it catch up, there were 118 nulls
on the
 >     subscriber in this column:

So recap_sequence_number is not actually a number, it is a code?


It has sequential values, but they're not necessarily numbers.


 >
 >     I appreciate all the responses. I'm scared to say so, but I think
 >     this is a bug in logical replication. Somehow a null value
appeared
 >     at the subscriber that was never in the publisher.
 >
 >     I also still have this question/suggestion from my first email:
 >
 >      > Is the process for schema migrations documented somewhere
beyond
 >     the above?

Not that I know of. It might help, if possible, to detail the steps in
the migration. Also what program you used to do it. Given that is
Django
I am assuming some combination of migrate, makemigrations and/or
sqlmigrate.


Pretty simple/standard, I think:
  - Changed the code.
  - Generated the migration using manage.py makemigration
  - Generated the SQL using sqlmigrate
  - Ran the migration using manage.py migrate on the master and using 
psql on the replica


The only thing I can think of involves this sequence on the subscriber:

ALTER TABLE "search_docketentry" ADD COLUMN "recap_sequence_number" 
varchar(50) DEFAULT '' NOT NULL;
ALTER TABLE "search_docketentry" ALTER COLUMN "recap_sequence_number" 
DROP DEFAULT;


and then this:

https://www.postgresql.org/docs/11/logical-replication-subscription.html

"Columns of a table are also matched by name. A different order of 
columns in the target table is allowed, but the column types have to 
match. The target table can have additional columns not provided by the 
published table. Those will be filled with their default values."


https://www.postgresql.org/docs/10/sql-createtable.html

"If there is no default for a column, then the default is null."

So the subscriber finished the migration first, as alluded to in an 
earlier post. There is no data for recap_sequence_number coming from the 
provider so Postgres place holds the data with NULL until such time as 
the migration on the provider finishes and actual data for 
recap_sequence_number starts flowing.


Going forward, options I see:

1) Making sure there is a DEFAULT other then NULL for a NOT NULL column.

2) Stop the replication and do the migration scripts on both provider 
and subscriber until they both complete and then start replication again.






Mike



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

Re: REVOKE to an user that doesn't exist

[Moreno Andreo]

Il 12/12/2018 16:01, Tom Lane ha scritto:

Moreno Andreo  writes:

I'm trying to pg_upgrade a cluster from 9.1 to 10 (Windows 10, but I
don't think it matters).
At a certain point an error is thrown while parsing a trigger:
could not execute query: ERROR:  role "1067431" does not exist
command was: REVOKE ALL ON FUNCTION "x"() FROM PUBLIC;
GRANT ALL ON FUNCTION "public"."x"() TO "1067431" WITH GRANT OPTION;

Hm.  We've seen similar bugs before; the mechanism is that at some
point the function owner granted privileges to somebody else, and
at some later point the somebody-else role got dropped, but the
privilege grant stayed behind because the system had lost, or never
made, the pg_shdepend entry indicating that this function had an ACL
entry mentioning that role.  The extra ACL entry is harmless, until
you wonder why pg_dump is printing a nonsensical command due to it.
That's the case. The customer never complained about any problem but 
today, while upgrading his cluster, we bumped into it.


We fixed a couple of bugs of that ilk just last month [1], but they were
for cases involving types and schemas, not functions.  The last case
involving function privileges that I see in a quick trawl of the commit
log predates 9.0 release [2].  I wonder how old this cluster is ...

The cluster is version 9.1.6, IIRC



How to REVOKE that non-existing user so pg_upgrade can proceed?

The safest way to clean it up manually would be to set the pg_proc.proacl
field for that function to NULL.  If there are other grants about the
function, you could try removing the bad entry, but it would likely be
safer to just re-grant after the upgrade.


Is it not the case to edit proacl column to just remove the unwanted 
role? Is it faster and safer to null the column and just reGRANT?


Thanks

Moreno.-

Stack Builder for pgAgent on Production

[Srinivas]

Hello,



I am working towards creating scheduled jobs in PostgreSQL database server.
During the process of creating jobs, i need to install pgAgent and for this
i am using Stack Builder to install the pgAgent component  My
concern/question here is *Can we use stack builder for pgAgent on
Production environment for free*? Please let me know.



Thanks and Regards

A.K.Srinivas

Re: Code for getting particular day of week number from month

[Andrew Gierth]

> "Mike" == Mike Martin  writes:

 Mike> Hi

 Mike> For a particular sequence I needed to do (schedule 2nd monday in
 Mike> month for coming year) I created the following query

That doesn't look like the best way - you're generating and discarding a
lot of rows.

"second monday in month X" can be expressed as follows:

"second monday in X" = "(first monday in X) + 1 week"

"first monday in X"
  = "first day of X + N days, where N is (dow(Monday) - dow(1st))
 reduced to 0..6 mod 7"

i.e. if the month starts on Monday, N=0
  .. on Tuesday, N = 6   (1 - 2 == 6 mod 7)
  .. on Wednesday, N = 5  etc.

So:

select to_char(d, 'Day DD/MM/')
  from (select month
   + interval '1 week'
   + ((1 - extract(dow from month)::integer + 7) % 7)
 * interval '1 day'
   as d
  from generate_series(timestamp '2018-12-01',
   timestamp '2020-12-01',
   interval '1 month') month) s;

-- 
Andrew (irc:RhodiumToad)

Re: Idle connections / sessions

[Thomas Poty]

Sorry but i don't know (i am not familiar with aws) 
Maybe this will  help: If you want pg_terminator can run on postgresql
server.

Regards
Thomas

Le mer. 12 déc. 2018 à 12:20, Oygun Josef  a écrit :

> Hi,
>
>
>
> Sorry for that!
>
>
>
> Thank you for the answers, this is good but do you know if there is a way
> do to it through AWS console with some kind of configuration instead?
>
>
>
> Reason for that is the we use terraform scripts to create and keep state
> of our instances so pg_terminator would require me to add every new db
> props to it.
>
>
>
> /Josef
>
>
>
>
>
> *Från:* Thomas Poty [mailto:thomas.p...@gmail.com]
> *Skickat:* den 12 december 2018 11:11
> *Till:* pgsql-general@lists.postgresql.org
> *Ämne:* Re: Idle connections / sessions
>
>
>
> Hi Josef,
>
>
>
> pg_terminator may help you.
>
>
>
> thomas
>
>
>
> Le mer. 12 déc. 2018 à 11:02, Fabio Pardi  a écrit :
>
> Hi Josef,
>
> please avoid cross posting to multiple lists.
>
> I m not a developer, but I think that if you do not want idle connections,
> you should terminate them on the side they have been created.
>
> If your application leaves the connection open, then you will notice idle
> connections on Postgres when not in use.
>
> regards,
>
> fabio pardi
>
> On 12/12/2018 10:37, Oygun Josef wrote:
>
> Hi,
>
>
>
> Is it possible to terminate idle connections/sessions automatically
> through a timeout in AWS or do I need to run a periodical cron job for this?
>
>
>
> Postgres version: 9.6.6
>
> Instance: db.t2.micro
>
> RAM : 1GB
>
>
>
> We are running a microservice architecture using docker with kubernetes
> and I can see that every pod on every node that has connected to the DB
> still has a idle connection as long as the node is still active even.
>
>
>
> It is both PGAdmin and the PostgreSQL JDBC Driver that leaves open idle
> connections.
>
>
>
>
>
> *Josef Oygun*
>
>
>
>
>
>

psql output in Japanese Code Page

[Abraham, Danny]

Hi,

While under Code Page 932 (=Japanese) the old PSQL 9.5.5 - used to talk plain 
English, like in \d 

This behavior has changed in PSQL 10. We are now forced to run "chcp 1252" 
before any PSQL activity.

Does anyone know about it? Any suggestions?

I tried \encoding WIN1252. Did not work.

Thanks

Danny Abraham

Re: Does PostgreSQL use atomic file creation of FS?

[Laurenz Albe]

Dmitry Lazurkin wrote:
> Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
> catch situation when system crashes between open call and write call? I
> am interesting in this because I would like use PostgreSQL on network
> file system.

If there is a crash, the file would be left behind.
This is slightly annoying, but shouldn't be a major problem.
Persisting such information in a crash-safe way would require
fsyncs that hurt I/O performance.

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

Re: Does PostgreSQL use atomic file creation of FS?

[Thomas Munro]

On Wed, Dec 12, 2018 at 11:52 PM Dmitry Lazurkin  wrote:
> Thank you. But I have read this. I said about network file system only
> for example. I would like to known how PostgreSQL handles this specific
> case (of course if someone knowns a answer):
>
> fd = open(file, "w");
> write(fd, data);
> // crash and now I have empty file which isn't correct
> fsync(fd);
>
> PS. I think PostgreSQL doesn't have this problem.

It depends on the context, but in general PostgreSQL knows about that
sort of thing.  When the cluster shuts down, it records that it shut
down cleanly, meaning that everything that should be on disk is on
disk.  When you start the cluster up, if it sees that it didn't shut
down cleanly, it enters recovery.  During recovery it tolerates files
being too short while it's replaying the WAL to get back to a
consistent state.  See the comment in mdread() for example:

https://github.com/postgres/postgres/blob/master/src/backend/storage/smgr/md.c#L755

It's called "write-ahead log" because we log our intention before we
write to data files (and make sure it's on disk first), so we'll
always replay the same effects again if we're interrupted.  The WAL is
a magic source of reliability (we can do it again if things go wrong)
and also performance (IO becomes serial, optimised for the storage
hardware).

https://www.postgresql.org/docs/current/wal-intro.html

-- 
Thomas Munro
http://www.enterprisedb.com

explain analyze cost

[Ravi Krishna]

I am running explain analyze cost on a SQL which reads from two large
tables (122mil and 37 mil).  The query is an UPDATE SQL where we use
derives table in the from clause and then join it back to the table
being updated.
The explain analyze cost itself is taking forever to run. It is running
for the last 1 hr. Does that actually run the SQL to find out the
impact of I/O (as indicated in COSTS). If not, what can cause it to run
this slow.__

Re: explain analyze cost

[Ron]

On 12/12/2018 04:37 PM, Ravi Krishna wrote:
I am running explain analyze cost on a SQL which reads from two large 
tables (122mil and 37 mil).  The query is an UPDATE SQL where we use 
derives table in the from clause and then join it back to the table being 
updated.


The explain analyze cost itself is taking forever to run. It is running 
for the last 1 hr. Does that actually run the SQL to find out the impact 
of I/O (as indicated in COSTS).


Yes.

https://www.postgresql.org/docs/9.6/sql-explain.html

"The ANALYZE option causes the statement to be actually executed, not only 
planned."



If not, what can cause it to run this slow.



--
Angular momentum makes the world go 'round.

Re: explain analyze cost

[Andres Freund]

On 2018-12-12 17:37:47 -0500, Ravi Krishna wrote:
> I am running explain analyze cost on a SQL which reads from two large
> tables (122mil and 37 mil).  The query is an UPDATE SQL where we use
> derives table in the from clause and then join it back to the table
> being updated.
> The explain analyze cost itself is taking forever to run. It is running
> for the last 1 hr. Does that actually run the SQL to find out the
> impact of I/O (as indicated in COSTS). If not, what can cause it to run
> this slow.__

Please do not hijack other threads by replying to a message and changing
the subject.  Just send a new mail to
pgsql-general@lists.postgresql.org, or whatever list you want to send an
email to.

Thanks,

Andres

Re: Errors with schema migration and logical replication — expected?

[Mike Lissner]

This sounds *very* plausible. So I think there are a few takeaways:

1. Should the docs mention that additive changes with NOT NULL constraints
are bad?

2. Is there a way this could work without completely breaking replication?
For example, should Postgresql realize replication can't work in this
instance and then stop it until schemas are back in sync, like it does with
other incompatible schema changes? That'd be better than failing in this
way and is what I'd expect to happen.

3. Are there other edge cases like this that aren't well documented that we
can expect to creep up on us? If so, should we try to spell out exactly
*which* additive changes *are* OK?

This feels like a major "gotcha" to me, and I'm trying to avoid those. I
feel like the docs are pretty lacking here and that others will find
themselves in similarly bad positions.

Better schema migration docs would surely help, too.

Mike


On Wed, Dec 12, 2018 at 7:11 AM Adrian Klaver 
wrote:

> On 12/12/18 12:15 AM, Mike Lissner wrote:
> >
> >
> > On Tue, Dec 11, 2018 at 3:10 PM Adrian Klaver  > > wrote:
> >
> >
> >  > Well, I was able to fix this by briefly allowing nulls on the
> >  > subscriber, letting it catch up with the publisher, setting
> all
> >  > nulls to empty strings (a Django convention), and then
> > disallowing
> >  > nulls again. After letting it catch up, there were 118 nulls
> > on the
> >  > subscriber in this column:
> >
> > So recap_sequence_number is not actually a number, it is a code?
> >
> >
> > It has sequential values, but they're not necessarily numbers.
> >
> >
> >  >
> >  > I appreciate all the responses. I'm scared to say so, but I
> think
> >  > this is a bug in logical replication. Somehow a null value
> > appeared
> >  > at the subscriber that was never in the publisher.
> >  >
> >  > I also still have this question/suggestion from my first
> email:
> >  >
> >  >  > Is the process for schema migrations documented somewhere
> > beyond
> >  > the above?
> >
> > Not that I know of. It might help, if possible, to detail the steps
> in
> > the migration. Also what program you used to do it. Given that is
> > Django
> > I am assuming some combination of migrate, makemigrations and/or
> > sqlmigrate.
> >
> >
> > Pretty simple/standard, I think:
> >   - Changed the code.
> >   - Generated the migration using manage.py makemigration
> >   - Generated the SQL using sqlmigrate
> >   - Ran the migration using manage.py migrate on the master and using
> > psql on the replica
>
> The only thing I can think of involves this sequence on the subscriber:
>
> ALTER TABLE "search_docketentry" ADD COLUMN "recap_sequence_number"
> varchar(50) DEFAULT '' NOT NULL;
> ALTER TABLE "search_docketentry" ALTER COLUMN "recap_sequence_number"
> DROP DEFAULT;
>
> and then this:
>
> https://www.postgresql.org/docs/11/logical-replication-subscription.html
>
> "Columns of a table are also matched by name. A different order of
> columns in the target table is allowed, but the column types have to
> match. The target table can have additional columns not provided by the
> published table. Those will be filled with their default values."
>
> https://www.postgresql.org/docs/10/sql-createtable.html
>
> "If there is no default for a column, then the default is null."
>
> So the subscriber finished the migration first, as alluded to in an
> earlier post. There is no data for recap_sequence_number coming from the
> provider so Postgres place holds the data with NULL until such time as
> the migration on the provider finishes and actual data for
> recap_sequence_number starts flowing.
>
> Going forward, options I see:
>
> 1) Making sure there is a DEFAULT other then NULL for a NOT NULL column.
>
> 2) Stop the replication and do the migration scripts on both provider
> and subscriber until they both complete and then start replication again.
>
>
>
>
> > Mike
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: psql output in Japanese Code Page

[Tatsuo Ishii]

> Hi,
> 
> While under Code Page 932 (=Japanese) the old PSQL 9.5.5 - used to talk plain 
> English, like in \d 
> 
> This behavior has changed in PSQL 10. We are now forced to run "chcp 1252" 
> before any PSQL activity.
> 
> Does anyone know about it? Any suggestions?
> 
> I tried \encoding WIN1252. Did not work.

What's your database encoding? (\l command on psql prompt will show it).
Also what's the result of chcp (with no argument).

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp

Re: postgresql10-server RPM unpacking of archive failed

[Chris Mair]

on a CentOS 7.5 machine with PostgreSQL 10.3 installed from the PGDG yum repo,
I have a strange error when trying to update to 10.6.

A simple "yum update" updated everything except postgresql10-server.x86_64!

That package gives (repeatedly) the error message:

Error unpacking rpm package postgresql10-server-10.6-1PGDG.rhel7.x86_64
error: unpacking of archive failed: cpio: lstat



I would try cleaning up all the data (packages and metadata) and trying again:

yum clean all
yum update postgresql10-server

Regards,


Thanks Martín,

a yum clean all did not help.

In the end, some time later (and after a reboot) yum update
succeeded and we never found out why.

I think we can archive this as a one-time glitch with yum.

Bye,
Chris.

Re: explain analyze cost

[Ravi Krishna]

> Please do not hijack other threads by replying to a message and
> changing> the subject.  Just send a new mail to
> pgsql-general@lists.postgresql.org, or whatever list you want
> to send an> email to.
> 

I am truly sorry and this will not be repeated. I was just lazy.
I guess this would break threaded views , because on non threaded views
it does notmatter.

Re: Errors with schema migration and logical replication — expected?

[Adrian Klaver]

On 12/12/18 3:19 PM, Mike Lissner wrote:

This sounds *very* plausible. So I think there are a few takeaways:

1. Should the docs mention that additive changes with NOT NULL 
constraints are bad?


It's not the NOT NULL it's the lack of a DEFAULT. In general a column 
with a NOT NULL and no DEFAULT is going to to bite you sooner or later:) 
At this point I have gathered enough of those bite marks to just make it 
my policy to always provide a DEFAULT for a NOT NULL column.




2. Is there a way this could work without completely breaking 
replication? For example, should Postgresql realize replication can't 
work in this instance and then stop it until schemas are back in sync, 
like it does with other incompatible schema changes? That'd be better 
than failing in this way and is what I'd expect to happen.


Not sure as there is no requirement that a column has a specified 
DEFAULT. This is unlike PK and FK constraint violations where the 
relationship is spelled out. Trying to parse all the possible ways a 
user could get into trouble would require something on the order of an 
AI and I don't see that happening anytime soon.




3. Are there other edge cases like this that aren't well documented that 
we can expect to creep up on us? If so, should we try to spell out 
exactly *which* additive changes *are* OK?


Not that I know of. By their nature edge cases are rare and often are 
dealt with in the moment and not pushed out to everybody. The only 
solution I know of is pretesting your schema change/replication setup on 
a dev installation.




This feels like a major "gotcha" to me, and I'm trying to avoid those. I 
feel like the docs are pretty lacking here and that others will find 
themselves in similarly bad positions.


Logical replication in core(not the pglogical extension) appeared for 
the first time in version 10. On the crawl/walk/run spectrum it is 
moving from crawl to walk. The docs will take some time to be more 
complete. Just for the record my previous post was sketching out a 
possible scenario not an ironclad answer. If you think the answer is 
plausible and a 'gotcha' I would file a bug:


https://www.postgresql.org/account/login/?next=/account/submitbug/



Better schema migration docs would surely help, too.

Mike





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

Re: Errors with schema migration and logical replication — expected?

[Mike Lissner]

Thanks Adrian for all the help. I filed this as bug #15549. I hope this all
helps get logical replication into the "Running" stage.

On Wed, Dec 12, 2018 at 5:06 PM Adrian Klaver 
wrote:

> On 12/12/18 3:19 PM, Mike Lissner wrote:
> > This sounds *very* plausible. So I think there are a few takeaways:
> >
> > 1. Should the docs mention that additive changes with NOT NULL
> > constraints are bad?
>
> It's not the NOT NULL it's the lack of a DEFAULT. In general a column
> with a NOT NULL and no DEFAULT is going to to bite you sooner or later:)
> At this point I have gathered enough of those bite marks to just make it
> my policy to always provide a DEFAULT for a NOT NULL column.
>
> >
> > 2. Is there a way this could work without completely breaking
> > replication? For example, should Postgresql realize replication can't
> > work in this instance and then stop it until schemas are back in sync,
> > like it does with other incompatible schema changes? That'd be better
> > than failing in this way and is what I'd expect to happen.
>
> Not sure as there is no requirement that a column has a specified
> DEFAULT. This is unlike PK and FK constraint violations where the
> relationship is spelled out. Trying to parse all the possible ways a
> user could get into trouble would require something on the order of an
> AI and I don't see that happening anytime soon.
>
> >
> > 3. Are there other edge cases like this that aren't well documented that
> > we can expect to creep up on us? If so, should we try to spell out
> > exactly *which* additive changes *are* OK?
>
> Not that I know of. By their nature edge cases are rare and often are
> dealt with in the moment and not pushed out to everybody. The only
> solution I know of is pretesting your schema change/replication setup on
> a dev installation.
>
> >
> > This feels like a major "gotcha" to me, and I'm trying to avoid those. I
> > feel like the docs are pretty lacking here and that others will find
> > themselves in similarly bad positions.
>
> Logical replication in core(not the pglogical extension) appeared for
> the first time in version 10. On the crawl/walk/run spectrum it is
> moving from crawl to walk. The docs will take some time to be more
> complete. Just for the record my previous post was sketching out a
> possible scenario not an ironclad answer. If you think the answer is
> plausible and a 'gotcha' I would file a bug:
>
> https://www.postgresql.org/account/login/?next=/account/submitbug/
>
> >
> > Better schema migration docs would surely help, too.
> >
> > Mike
> >
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>