Table seems empty but its size is in gigabytes
Hi PostgreSQL,
I'musing PostgreSQL 9.3 running on Ubuntu Server.
Ihave a complex function to populate a big table, in order to improve performance;data is prepared in temporary tables before it will be inserted. I called thisfunction many times from my application, but something goes wrong with the diskassigned to my temporary tablespace and all threads were dropped. Now my bigtable statistics shows tuples inserted and its size is in order of gigabytes,but a simple SELECT has no rows, is there any way to recover the data inthis table?
Import Notes
Reference msg id not found: 1259397935.1706749.1460477508402.JavaMail.yahoo.ref@mail.yahoo.com
On Tue, Apr 12, 2016 at 9:11 AM, Juan Carlos Michaca Lucero <
jc_mich2c3c1@yahoo.com.mx> wrote:
Hi PostgreSQL,
I'm using PostgreSQL 9.3 running on Ubuntu Server.
I have a complex function to populate a big table, in order to improve
performance; data is prepared in temporary tables before it will be
inserted. I called this function many times from my application, but
something goes wrong with the disk assigned to my temporary tablespace and
all threads were dropped. Now my big table statistics shows tuples inserted
and its size is in order of gigabytes, but a simple SELECT has no rows, is
there any way to recover the data in this table?
Do you want to recover the dead data or the space that it consumes?
David J.
On 4/12/2016 9:16 AM, David G. Johnston wrote:
Now my big table statistics shows tuples inserted and its size is
in order of gigabytes, but a simple SELECT has no rows, is there
any way to recover the data in this table?Do you want to recover the dead data or the space that it consumes?
to free the disk space, use...
vacuum full tablename;
I don't know how to reclaim tuples that were written but rolled back.
--
john r pierce, recycling bits in santa cruz
On Tue, Apr 12, 2016 at 9:30 AM, John R Pierce <pierce@hogranch.com> wrote:
On 4/12/2016 9:16 AM, David G. Johnston wrote:
Now my big table statistics shows tuples inserted and its size is in order
of gigabytes, but a simple SELECT has no rows, is there any way to recover
the data in this table?Do you want to recover the dead data or the space that it consumes?
to free the disk space, use...
vacuum full tablename;
In the table is (should be) empty you could also do TRUNCATE tablename; I
suspect there isn't a functional difference between the two options - the
later does work on non-empty tables though it has some performance
implications if done on one containing data.
I don't know how to reclaim tuples that were written but rolled back.
This is the request - got an off-list reply to that effect.
David J.
I want to recover the dead data
El Martes, 12 de abril, 2016 11:38:22, David G. Johnston <david.g.johnston@gmail.com> escribió:
Send all replies to the list please. In this specific case I'm not going to be of much help but others should be. You are likely going to need to supply additional context.
On Tue, Apr 12, 2016 at 9:22 AM, Juan Carlos Michaca Lucero <jc_mich2c3c1@yahoo.com.mx> wrote:
I want to recover the dead data.
El Martes, 12 de abril, 2016 11:16:15, David G. Johnston <david.g.johnston@gmail.com> escribió:
On Tue, Apr 12, 2016 at 9:11 AM, Juan Carlos Michaca Lucero <jc_mich2c3c1@yahoo.com.mx> wrote:
Hi PostgreSQL, I'musing PostgreSQL 9.3 running on Ubuntu Server. Ihave a complex function to populate a big table, in order to improve performance;data is prepared in temporary tables before it will be inserted. I called thisfunction many times from my application, but something goes wrong with the diskassigned to my temporary tablespace and all threads were dropped. Now my bigtable statistics shows tuples inserted and its size is in order of gigabytes,but a simple SELECT has no rows, is there any way to recover the data inthis table?
Do you want to recover the dead data or the space that it consumes?
David J.
Import Notes
Reply to msg id not found: CAKFQuwYVKpE9BQG3oxnE_dX7qg-HhBx4ZO08=XzH79M5aCL73A@mail.gmail.comReference msg id not found: CAKFQuwYVKpE9BQG3oxnE_dX7qg-HhBx4ZO08=XzH79M5aCL73A@mail.gmail.com | Resolved by subject fallback
On 04/12/2016 09:11 AM, Juan Carlos Michaca Lucero wrote:
Hi PostgreSQL,
I'm using PostgreSQL 9.3 running on Ubuntu Server.
I have a complex function to populate a big table, in order to improve
performance; data is prepared in temporary tables before it will be
inserted. I called this function many times from my application, but
something goes wrong with the disk assigned to my temporary tablespace
and all threads were dropped. Now my big table statistics shows tuples
inserted and its size is in order of gigabytes, but a simple SELECT has
no rows, is there any way to recover the data in this table?
Do you really want to? It seems to me 'something goes wrong with the
disk assigned...' is not a good thing and I would not be too confident
in any data that was returned, should you be able to recover it.
Out of curiosity has this happened more then once?
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general