ERROR: missing chunk number 0 for toast value
Hi All,
Test case:
drop table if exists t;
create table t(c text);
insert into t values ('x'), (repeat(md5('abcdefghijklmnop'), 10000));
select pg_column_size(c), pg_column_size(c || '') FROM t;
CREATE OR REPLACE FUNCTION copy_toast_out() RETURNS VOID AS $$
declare
v text;
BEGIN
SELECT c INTO v FROM t WHERE c <> 'x';
Select 1/0;
Exception
When Others Then
PERFORM pg_sleep(30); -- go run "TRUNCATE t" in a 2nd session
raise notice 'length :%', length(v || ''); -- force detoast
END;
$$ language plpgsql;
postgres=# select copy_toast_out();
ERROR: missing chunk number 0 for toast value 16390 in pg_toast_16384
CONTEXT: PL/pgSQL function copy_toast_out() line 10 at RAISE
Analysis:
The basic problem here is that if the lock is released on table before
extracting toasted value, and in meantime someone truncates the table,
this error can occur. Here error coming with PL block contains an Exception
block (as incase there is an exception block, it calls
RollbackAndReleaseCurrentSubTransaction).
Do you think we should detoast the local variable before
RollbackAndReleaseCurrentSubTransaction ? Or any other options ?
Regards,
Rushabh Lathia
www.EnterpriseDB.com
On 01/02/2014 02:24 PM, Rushabh Lathia wrote:
Hi All,
Test case:
drop table if exists t;
create table t(c text);
insert into t values ('x'), (repeat(md5('abcdefghijklmnop'), 10000));
select pg_column_size(c), pg_column_size(c || '') FROM t;CREATE OR REPLACE FUNCTION copy_toast_out() RETURNS VOID AS $$
declare
v text;
BEGIN
SELECT c INTO v FROM t WHERE c <> 'x';
Select 1/0;
Exception
When Others Then
PERFORM pg_sleep(30); -- go run "TRUNCATE t" in a 2nd sessionraise notice 'length :%', length(v || ''); -- force detoast
END;
$$ language plpgsql;postgres=# select copy_toast_out();
ERROR: missing chunk number 0 for toast value 16390 in pg_toast_16384
CONTEXT: PL/pgSQL function copy_toast_out() line 10 at RAISEAnalysis:
The basic problem here is that if the lock is released on table before
extracting toasted value, and in meantime someone truncates the table,
this error can occur. Here error coming with PL block contains an Exception
block (as incase there is an exception block, it calls
RollbackAndReleaseCurrentSubTransaction).
This is another variant of the bug discussed here:
/messages/by-id/0C41674C-FA02-4768-9E1B-548E568875BD@quarantainenet.nl.
Do you think we should detoast the local variable before
RollbackAndReleaseCurrentSubTransaction ? Or any other options ?
Hmm, that would fix this particular test case, but not the other case
where you DROP or TRUNCATE the table in the same transaction.
The simplest fix would be to just detoast everything on assignment but
that was rejected on performance grounds in that previous thread. I
don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway. For simple variables like,
in your test case, it's a good bet to detoast the value immediately;
it'll be detoasted as soon as you try to do anything with it anyway. But
it's not a good bet for record or row variables, because you often fetch
the whole row into a variable but only access a field or two. Then
again, if you run into that, at least you can work around it by changing
your plpgsql code to only fetch the fields you need.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
The simplest fix would be to just detoast everything on assignment but
that was rejected on performance grounds in that previous thread. I
don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway.
Or just say "don't do that". TRUNCATE on a table that's in use by open
transactions has all sorts of issues besides this one. The given example
is a pretty narrow corner case anyway --- with a less contorted coding
pattern, we'd still have AccessShareLock on the table, blocking the
TRUNCATE from removing data. I'd still not want to blow up performance
in order to make this example work.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-02 21:21:15 +0200, Heikki Linnakangas wrote:
I don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway.
We could remember the subtransaction a variable was created in and error
out if it the creating subtransaction aborted and it's not a
pass-by-value datum or similar.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-01-02 21:21:15 +0200, Heikki Linnakangas wrote:
I don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway.
We could remember the subtransaction a variable was created in and error
out if it the creating subtransaction aborted and it's not a
pass-by-value datum or similar.
That would still result in throwing an error, though, so it isn't likely
to make the OP happy. I was wondering if we could somehow arrange to not
release the subtransaction's AccessShareLock on the table, as long as it
was protecting toasted references someplace.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-02 15:00:58 -0500, Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-01-02 21:21:15 +0200, Heikki Linnakangas wrote:
I don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway.We could remember the subtransaction a variable was created in and error
out if it the creating subtransaction aborted and it's not a
pass-by-value datum or similar.That would still result in throwing an error, though, so it isn't likely
to make the OP happy.
Yea, it would give a better error message which might help diagnose the
issue, but not more. We could disallow accessing such variables
generally unless they explicitly had been detoasted, that would make
people notice the problem more easily.
I shortly wondered if we couldn't "just" iterate over plpgsql variables
and detoast them on subabort if created in the aborted xact, but that
doesn't really work because we're in an aborted transaction where it
might not be safe to access relations... Theoretically the subabort
could be split into two phases allowing it by only releasing the lock
after safely switching to the upper transaction but that sounds like a
hammer too big for the problem.
I was wondering if we could somehow arrange to not
release the subtransaction's AccessShareLock on the table, as long as it
was protecting toasted references someplace.
Sounds fairly ugly...
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 2, 2014 at 3:19 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I was wondering if we could somehow arrange to not
release the subtransaction's AccessShareLock on the table, as long as it
was protecting toasted references someplace.Sounds fairly ugly...
I think the only principled fixes are to either retain the lock or
forcibly detoast before releasing it. The main problem I see with
retaining the lock is that you'd need a way of finding out the
relation OIDs of all toast pointers you might later decide to expand.
I don't have an amazingly good idea about how to figure that out.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-02 16:05:09 -0500, Robert Haas wrote:
On Thu, Jan 2, 2014 at 3:19 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I was wondering if we could somehow arrange to not
release the subtransaction's AccessShareLock on the table, as long as it
was protecting toasted references someplace.Sounds fairly ugly...
I think the only principled fixes are to either retain the lock or
forcibly detoast before releasing it.
I don't think that's sufficient. Unless I miss something the problem
isn't restricted to TRUNCATE and such at all. I think a plain VACUUM
should be sufficient? I haven't tested it, but INSERT RETURNING
toasted_col a row, storing the result in a record, and then aborting the
subtransaction will allow the inserted row to be VACUUMed by a
concurrent transaction.
So I don't think anything along those lines will be sufficient.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 3, 2014 at 12:51 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:
On 01/02/2014 02:24 PM, Rushabh Lathia wrote:
Hi All,
Test case:
drop table if exists t;
create table t(c text);
insert into t values ('x'), (repeat(md5('abcdefghijklmnop'), 10000));
select pg_column_size(c), pg_column_size(c || '') FROM t;CREATE OR REPLACE FUNCTION copy_toast_out() RETURNS VOID AS $$
declare
v text;
BEGIN
SELECT c INTO v FROM t WHERE c <> 'x';
Select 1/0;
Exception
When Others Then
PERFORM pg_sleep(30); -- go run "TRUNCATE t" in a 2nd sessionraise notice 'length :%', length(v || ''); -- force detoast
END;
$$ language plpgsql;postgres=# select copy_toast_out();
ERROR: missing chunk number 0 for toast value 16390 in pg_toast_16384
CONTEXT: PL/pgSQL function copy_toast_out() line 10 at RAISEAnalysis:
The basic problem here is that if the lock is released on table before
extracting toasted value, and in meantime someone truncates the table,
this error can occur. Here error coming with PL block contains an
Exception
block (as incase there is an exception block, it calls
RollbackAndReleaseCurrentSubTransaction).This is another variant of the bug discussed here:
/messages/by-id/0C41674C-FA02-4768-9E1B-548E568875BD@quarantainenet.nl.Do you think we should detoast the local variable before
RollbackAndReleaseCurrentSubTransaction ? Or any other options ?Hmm, that would fix this particular test case, but not the other case where
you DROP or TRUNCATE the table in the same transaction.The simplest fix would be to just detoast everything on assignment but that
was rejected on performance grounds in that previous thread. I don't see any
other realistic way to fix this, however, so maybe we should just bite the
bullet and do it anyway. For simple variables like, in your test case, it's
a good bet to detoast the value immediately; it'll be detoasted as soon as
you try to do anything with it anyway. But it's not a good bet for record or
row variables, because you often fetch the whole row into a variable but
only access a field or two.
Yeah, this is exactly what came to my mind as well the first time I saw this
problem that for row and record variables it can be penalty which user might
not expect as he might not be using toasted values.
However is it possible that we do detoasting on assignment when the
variable of function is declared with some specific construct.
For example, we do detoasting at commit time for holdable portals
(referred below code)
/*
* Change the destination to output to the tuplestore. Note we tell
* the tuplestore receiver to detoast all data passed through it.
*/
queryDesc->dest = CreateDestReceiver(DestTuplestore);
SetTuplestoreDestReceiverParams(..);
When the Hold option is specified with cursor, then we perform
detoast on commit, so on similar lines if the specific variable or
function is declared with some particular construct, then we detoast
on assignment.
Another option is that we give more meaningful error with Hint
suggesting the possible reason of error.
This option can be used along with above option in case
variable/function is not declared with particular construct.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Fri, Jan 3, 2014 at 9:05 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Fri, Jan 3, 2014 at 12:51 AM, Heikki Linnakangas
<hlinnakangas@vmware.com> wrote:On 01/02/2014 02:24 PM, Rushabh Lathia wrote:
Do you think we should detoast the local variable before
RollbackAndReleaseCurrentSubTransaction ? Or any other options ?Hmm, that would fix this particular test case, but not the other case where
you DROP or TRUNCATE the table in the same transaction.The simplest fix would be to just detoast everything on assignment but that
was rejected on performance grounds in that previous thread. I don't see any
other realistic way to fix this, however, so maybe we should just bite the
bullet and do it anyway. For simple variables like, in your test case, it's
a good bet to detoast the value immediately; it'll be detoasted as soon as
you try to do anything with it anyway. But it's not a good bet for record or
row variables, because you often fetch the whole row into a variable but
only access a field or two.Yeah, this is exactly what came to my mind as well the first time I saw this
problem that for row and record variables it can be penalty which user might
not expect as he might not be using toasted values.However is it possible that we do detoasting on assignment when the
variable of function is declared with some specific construct.
After reading about handling for similar problem in other databases and
thinking more on it, I wonder if we can make a rule such that values
lesser than some threshold (8K or 16K or 32K) can be allowed to
be retrieved in plpgsql variables.
So with this, we can always detoast on assignment if the value is
less than threshold and return error otherwise.
I think this will help in reducing the performance impact and allow
users to retrieve values (which are of less than threshold) in plpgsql
variables without worrying about the behaviour reported in this and
similar thread.
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Thu, Jan 2, 2014 at 4:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-02 16:05:09 -0500, Robert Haas wrote:
On Thu, Jan 2, 2014 at 3:19 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I was wondering if we could somehow arrange to not
release the subtransaction's AccessShareLock on the table, as long as it
was protecting toasted references someplace.Sounds fairly ugly...
I think the only principled fixes are to either retain the lock or
forcibly detoast before releasing it.I don't think that's sufficient. Unless I miss something the problem
isn't restricted to TRUNCATE and such at all. I think a plain VACUUM
should be sufficient? I haven't tested it, but INSERT RETURNING
toasted_col a row, storing the result in a record, and then aborting the
subtransaction will allow the inserted row to be VACUUMed by a
concurrent transaction.
Hmm, that's actually nastier than the case that the case Rushabh
originally reported. A somewhat plausible response to "my holdable
cursor didn't work after I truncated the table it read from" is "well
don't do that then". But this case could actually happen to someone
who wasn't trying to do anything screwy.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-06 09:10:48 -0500, Robert Haas wrote:
On Thu, Jan 2, 2014 at 4:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I think the only principled fixes are to either retain the lock or
forcibly detoast before releasing it.I don't think that's sufficient. Unless I miss something the problem
isn't restricted to TRUNCATE and such at all. I think a plain VACUUM
should be sufficient? I haven't tested it, but INSERT RETURNING
toasted_col a row, storing the result in a record, and then aborting the
subtransaction will allow the inserted row to be VACUUMed by a
concurrent transaction.Hmm, that's actually nastier than the case that the case Rushabh
originally reported.
A bit, yes. Somebody should probably verify that it can actually happen :P
A somewhat plausible response to "my holdable
cursor didn't work after I truncated the table it read from" is "well
don't do that then". But this case could actually happen to someone
who wasn't trying to do anything screwy.
Personally I think everything that involves using data computed in an
aborted subtransaction but the error code is screwy. I think plpgsql has
been far too lenient in allowing that in an unconstrained fashion.
I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 6, 2014 at 9:19 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 09:10:48 -0500, Robert Haas wrote:
On Thu, Jan 2, 2014 at 4:15 PM, Andres Freund <andres@2ndquadrant.com> wrote:
I think the only principled fixes are to either retain the lock or
forcibly detoast before releasing it.I don't think that's sufficient. Unless I miss something the problem
isn't restricted to TRUNCATE and such at all. I think a plain VACUUM
should be sufficient? I haven't tested it, but INSERT RETURNING
toasted_col a row, storing the result in a record, and then aborting the
subtransaction will allow the inserted row to be VACUUMed by a
concurrent transaction.Hmm, that's actually nastier than the case that the case Rushabh
originally reported.A bit, yes. Somebody should probably verify that it can actually happen :P
A somewhat plausible response to "my holdable
cursor didn't work after I truncated the table it read from" is "well
don't do that then". But this case could actually happen to someone
who wasn't trying to do anything screwy.Personally I think everything that involves using data computed in an
aborted subtransaction but the error code is screwy. I think plpgsql has
been far too lenient in allowing that in an unconstrained fashion.I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.
So, you want to let users do things that are unsafe, but only if they
ask nicely? That hardly seems right.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-06 09:43:45 -0500, Robert Haas wrote:
I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.So, you want to let users do things that are unsafe, but only if they
ask nicely? That hardly seems right.
Well, no. If they have to use that function explicitly *before* the
subxact aborted, we can copy & detoast the value out of that context
safely.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 6, 2014 at 9:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 09:43:45 -0500, Robert Haas wrote:
I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.So, you want to let users do things that are unsafe, but only if they
ask nicely? That hardly seems right.Well, no. If they have to use that function explicitly *before* the
subxact aborted, we can copy & detoast the value out of that context
safely.
Oh, I see. I think that's pretty icky. Users won't expect (and will
complain about) such restrictions.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-06 11:08:41 -0500, Robert Haas wrote:
On Mon, Jan 6, 2014 at 9:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 09:43:45 -0500, Robert Haas wrote:
I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.So, you want to let users do things that are unsafe, but only if they
ask nicely? That hardly seems right.Well, no. If they have to use that function explicitly *before* the
subxact aborted, we can copy & detoast the value out of that context
safely.Oh, I see. I think that's pretty icky. Users won't expect (and will
complain about) such restrictions.
Yea. But at least it would fail reliably instead of just under
concurrency and other strange circumstances - and there'd be a safe way
out. Currently there seem to be all sorts of odd behaviour possible.
I simply don't have a better idea :(
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 6, 2014 at 11:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 11:08:41 -0500, Robert Haas wrote:
On Mon, Jan 6, 2014 at 9:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 09:43:45 -0500, Robert Haas wrote:
I actually vote for not allowing doing so at all by erroring out when
accessing a plpgsql variable created in an aborted subxact, unless you
explicitly signal that you want to do do so by calling some function
deleting the information about which subxact a variable was created
in. I have seen several bugs caused by people assuming that EXCEPTION
BLOCK/subtransaction rollback had some kind of effects on variables
created in them. And we just don't have much support for doing anything
in that direction safely.So, you want to let users do things that are unsafe, but only if they
ask nicely? That hardly seems right.Well, no. If they have to use that function explicitly *before* the
subxact aborted, we can copy & detoast the value out of that context
safely.Oh, I see. I think that's pretty icky. Users won't expect (and will
complain about) such restrictions.Yea. But at least it would fail reliably instead of just under
concurrency and other strange circumstances - and there'd be a safe way
out. Currently there seem to be all sorts of odd behaviour possible.I simply don't have a better idea :(
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.
It's certainly possible to think of scenarios under which it'd be painful,
eg, you fetch all columns into a record but you never actually use the
toasted one(s). OTOH, I can think of cases where forced detoasting might
save cycles too, if it prevents multiple detoastings on later accesses.
Probably what we ought to do is put together a trial patch and try to
do some benchmarking. I agree that this is the simplest route to a
fix if we can stand the overhead.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-06 12:40:25 -0500, Robert Haas wrote:
On Mon, Jan 6, 2014 at 11:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 11:08:41 -0500, Robert Haas wrote:
Yea. But at least it would fail reliably instead of just under
concurrency and other strange circumstances - and there'd be a safe way
out. Currently there seem to be all sorts of odd behaviour possible.I simply don't have a better idea :(
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.
The scenario I am primarily worried about is turning a record assignment
which previously took up to BLOCK_SIZE + slop amount of memory into
something taking up to a gigabyte. That's a pretty damn hefty
change.
And there's no good way of preventing it short of using a variable for
each actually desired column which imnsho isn't really a solution.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-01-06 12:40:25 -0500, Robert Haas wrote:
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.
The scenario I am primarily worried about is turning a record assignment
which previously took up to BLOCK_SIZE + slop amount of memory into
something taking up to a gigabyte. That's a pretty damn hefty
change.
And there's no good way of preventing it short of using a variable for
each actually desired column which imnsho isn't really a solution.
Dunno ... if you have a table that contains a gigabyte-width column,
should you be all that surprised if "SELECT * INTO r FROM table"
results in "r" occupying about a gigabyte? And I can't count the
number of times I've heard people deprecate using "SELECT *" at all
in production code, so I don't agree with the claim that listing the
columns you want is an unacceptable solution.
I don't doubt that there are some folks for whom this would be a
noticeable space-consumption hit compared to current behavior, but I have
a hard time working up a lot of sympathy for them. I'm more concerned
about the possible performance hit from detoasting more-reasonably-sized
columns (say in the tens-of-KB range) when they might not get used.
But we really need to benchmark that rather than just guess about whether
it's a problem.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/2/14, 1:32 PM, Tom Lane wrote:
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
The simplest fix would be to just detoast everything on assignment but
that was rejected on performance grounds in that previous thread. I
don't see any other realistic way to fix this, however, so maybe we
should just bite the bullet and do it anyway.Or just say "don't do that". TRUNCATE on a table that's in use by open
transactions has all sorts of issues besides this one. The given example
is a pretty narrow corner case anyway --- with a less contorted coding
pattern, we'd still have AccessShareLock on the table, blocking the
TRUNCATE from removing data. I'd still not want to blow up performance
in order to make this example work.
If concurrent TRUNCATE isn't safe outside of this case then why do we allow it? IE: why doesn't TRUNCATE exclusive lock the relation?
I'd much rather have working concurrent truncation than having to lock the relation, but if it's not safe we shouldn't hand people that footgun...
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 1/6/14, 2:21 PM, Tom Lane wrote:
Andres Freund <andres@2ndquadrant.com> writes:
On 2014-01-06 12:40:25 -0500, Robert Haas wrote:
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.The scenario I am primarily worried about is turning a record assignment
which previously took up to BLOCK_SIZE + slop amount of memory into
something taking up to a gigabyte. That's a pretty damn hefty
change.
And there's no good way of preventing it short of using a variable for
each actually desired column which imnsho isn't really a solution.Dunno ... if you have a table that contains a gigabyte-width column,
should you be all that surprised if "SELECT * INTO r FROM table"
results in "r" occupying about a gigabyte? And I can't count the
number of times I've heard people deprecate using "SELECT *" at all
in production code, so I don't agree with the claim that listing the
columns you want is an unacceptable solution.
I see your logic, but the problem is a good developer would have actually tested that case and said "Oh look, plpgsql isn't blindly copying the entire record." Now we're changing that case underneath them. That's a pretty significant change that could affect a LOT of code on the user's side. And if they've got conditional code down-stream that sometimes hits the TOASTed value and sometimes doesn't then they're in for even more fun...
The deferred access pattern of detoasting is a very powerful performance improvement and I'd hate to see us limiting it in plpgsql.
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Mon, Jan 6, 2014 at 8:02 PM, Jim Nasby <jim@nasby.net> wrote:
If concurrent TRUNCATE isn't safe outside of this case then why do we allow
it? IE: why doesn't TRUNCATE exclusive lock the relation?
It *does*.
The problem is that the *other* transaction that's reading the
relation can still retain a TOAST pointer after it no longer holds the
lock. That's uncool.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 01/06/2014 08:29 PM, Andres Freund wrote:
On 2014-01-06 12:40:25 -0500, Robert Haas wrote:
On Mon, Jan 6, 2014 at 11:47 AM, Andres Freund <andres@2ndquadrant.com> wrote:
On 2014-01-06 11:08:41 -0500, Robert Haas wrote:
Yea. But at least it would fail reliably instead of just under
concurrency and other strange circumstances - and there'd be a safe way
out. Currently there seem to be all sorts of odd behaviour possible.I simply don't have a better idea :(
Is "forcibly detoast everything" a complete no-go? I realize there
are performance concerns with that approach, but I'm not sure how
realistic a worry it actually is.The scenario I am primarily worried about is turning a record assignment
which previously took up to BLOCK_SIZE + slop amount of memory into
something taking up to a gigabyte. That's a pretty damn hefty
change.
And there's no good way of preventing it short of using a variable for
each actually desired column which imnsho isn't really a solution.
We could mitigate that somewhat by doing an optimization pass of the
PL/pgSQL code after compilation, and check which fields of a row
variable are never referenced, and skip the detoasting for those fields.
It would only work for named row variables, not anonymous record
variables, and you would still unnecessarily detoast fields that are
sometimes accessed but usually not. But it would avoid the detoasting in
the most egregious cases, e.g where you fetch a whole row into a
variable just to access one field.
Overall, I'm leaning towards biting the bullet and always detoasting
everything in master. Probably best to just leave the stable branches alone.
- Heikki
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 2014-01-07 10:45:24 +0200, Heikki Linnakangas wrote:
Overall, I'm leaning towards biting the bullet and always detoasting
everything in master. Probably best to just leave the stable branches alone.
If we're doing something coarse grained as this, I agree, it should be
master only.
I personally vote to rather just leave things as is, seems better than
this pessimization, and it's not like loads of people have hit the issue.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Jan7, 2014, at 09:45 , Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
Overall, I'm leaning towards biting the bullet and always detoasting everything in master. Probably best to just leave the stable branches alone.
+1
The fact that de-TOAST-ing can happen lazily is, at least to me, an
implementation detail that shouldn't be observable. If we want to
allow people to use lazy de-TOAST-ing as an optimization tool, we
should provide an explicit way to do so, e.g. by flagging variables
in pl/pgsql as REFERENCE or something like that.
best regards,
Florian Pflug
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers