TOAST error in 7.4.2 on frequently truncated tables
People,
Here's another:
Summary: TOAST error on frequently truncated tables
Version: 7.4.2
Severity: Abort
Platform: Red Hat 9.0
When running a script against a reporting database, I'm getting the following:
DBD::Pg::st execute failed: ERROR: expected both swapped tables to have TOAST
tables at sql_data_xfer.pl line 120, <STDIN> line 2.
Process Failed: from step SELECT count(*) FROM elbs_client;Process Failed:
from step SELECT count(*) FROM elbs_client;
1 at sql_data_xfer.pl line 120, <STDIN> line 2.
What's happening in the scipt is that it's checking for the presence of data
in load tables and truncating them if data is found. I'm not absolutely
certain whether it is the COUNT(*) query, or the TRUNCATE step which is
producing the above error; TRUNCATE would seem more reasonable given the
nature of the error.
I've searched the source code, and the only reference to the error I can find
is for CLUSTER, which is confusing because none of the tables involved are
clustered.
Is this related to the index problem fixed in 7.4.x?
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
DBD::Pg::st execute failed: ERROR: expected both swapped tables to have TOAST
tables at sql_data_xfer.pl line 120, <STDIN> line 2.
Have you done more to this table than TRUNCATE it?
We recognized relatively recently that ALTER DROP COLUMN could drop the
last/only variable-width column, in which case there is no longer any
need for a TOAST table. The 8.0 code for CLUSTER/TRUNCATE copes with
this possibility but 7.4 doesn't.
I'm quite sure you couldn't get this error from anything except CLUSTER
or TRUNCATE in 7.4 though. Seems like your client-side code is in error
to be fingering a plain SELECT as the cause.
regards, tom lane
Tom,
DBD::Pg::st execute failed: ERROR: expected both swapped tables to have
TOAST tables at sql_data_xfer.pl line 120, <STDIN> line 2.Have you done more to this table than TRUNCATE it?
We recognized relatively recently that ALTER DROP COLUMN could drop the
last/only variable-width column, in which case there is no longer any
need for a TOAST table. The 8.0 code for CLUSTER/TRUNCATE copes with
this possibility but 7.4 doesn't.
Aha! Yes, the problem is that I dropped the last VARCHAR column, not in that
table but in one that came after it. Any workaround to fix?
I'm quite sure you couldn't get this error from anything except CLUSTER
or TRUNCATE in 7.4 though. Seems like your client-side code is in error
to be fingering a plain SELECT as the cause.
Yeah, well, I can't get the client to install Exception module.
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
Aha! Yes, the problem is that I dropped the last VARCHAR column, not in that
table but in one that came after it. Any workaround to fix?
Easiest is to add back a useless varchar column ...
regards, tom lane
Tom,
Aha! Yes, the problem is that I dropped the last VARCHAR column, not in
that table but in one that came after it. Any workaround to fix?Easiest is to add back a useless varchar column ...
Can't do it, the column needed to be dropped in order to fix a problem with
the data transfer. Maybe re-create the table?
--
Josh Berkus
Aglio Database Solutions
San Francisco
Josh Berkus <josh@agliodbs.com> writes:
Easiest is to add back a useless varchar column ...
Can't do it, the column needed to be dropped in order to fix a problem with
the data transfer. Maybe re-create the table?
Yeah, I think you're stuck with doing that.
regards, tom lane
Tom,
Yeah, I think you're stuck with doing that.
BTW, this is a pretty nasty error, although apparently infrequent give the
lack of list e-mails. Can we fix it for 7.4 series?
--
Josh Berkus
Aglio Database Solutions
San Francisco
BTW, this is a pretty nasty error, although apparently infrequent give the
lack of list e-mails. Can we fix it for 7.4 series?
Possibly, but I'm not very excited about it --- it's certainly a corner
case. I'm not sure it's worth the risk of breaking something.
regards, tom lane