Postgresql Transaction ID Not Found in the session(for a large table)

Asked May 06 '18 at 20:57

Active May 07 '18 at 20:39

Viewed 2,306 times

This is not a duplicate of Postgresql Transaction ID Not Found

I do not have any issue with small tables, but there is a huge table about (4TB) and am trying to copy it from a db to another both located in RDS AWS (Postgresql), after a day or two I receive this message with no additional warning/message and I have to Connect again. I am using pgAdmin 4 v3.

Transaction ID not found in the session.

    SET tcp_keepalives_idle = 60 ;

  select 
 "k","LP_FK","ASSET_PK","Distance" 
 ,"Order",pk into t22 from 
 dblink('address.address port=1111
 user=user  
 password=password dbname=DB', 'select 
 "k","LP_FK","ASSET_PK","Distance" 
 ,"Order",pk   FROM  one_view_feature  ') tt ( 
"k" integer,
 "LP_FK" integer,
"ASSET_PK" integer,
"Distance" double precision,
"Order" double precision,
pk bigint)

As the worst case scenario, I might transfer data in different bunches, but I am sure it will be very slow.

edited May 07 '18 at 20:39

asked May 06 '18 at 20:57

Mohsen Sichani

1,002
12
33

How long does the process take? – NiVeR May 06 '18 at 21:00
Well, As it is not a quick query I have to leave it running. As I mentioned I notice that error after a day or two (maybe 24 hrs)- I have tried several times with this large table but no success (Same error). – Mohsen Sichani May 06 '18 at 21:13
Is strange, as if transactions remain pending and are killed after some time. Do you happen to open a lot of transactions for doing your work? – NiVeR May 06 '18 at 21:18
why would you not try running it server side with psql?.. – Vao Tsun May 06 '18 at 21:22
Not necessarily by me. The db is shared so my colleagues might run some queries (maybe two or three heavy queries ) , but I am 100 sure they are not running that against this table. – Mohsen Sichani May 06 '18 at 21:23
also - selecting 4TB table over dblink?.. not sure it's optimal – Vao Tsun May 06 '18 at 21:24
@VaoTsun, I can give it a shot if you think this is helpful, but since RDS comes as a docker in AWS, I might have the same problem – Mohsen Sichani May 06 '18 at 21:24
@mohsenhs - ah! RDS! stupid suggestion then :) – Vao Tsun May 06 '18 at 21:25
:), What other options do I have with this 4TB? – Mohsen Sichani May 06 '18 at 21:25

Postgresql Transaction ID Not Found in the session(for a large table)

0 Answers0