[BUGS] could not truncate directory "pg_subtrans": apparent wraparound
hello to all, i found this error in the logs on the hot_standby server. this error appeared only once a few days back and hasn't come up again. the version i am using is 9.0.5 and the primary was loaded by restoring dump files (previous version was 8.4.4). i have never switched to this server and i am using streaming replication. the activity of the primary is very low, it creates about 5-10 wal archives every day and has always been like that. the error does not appear on the primary. if you need more details about the servers plz tell me and i will post them.any information that you may have is more than welcome. thx in advance -- View this message in context: http://postgresql.1045698.n5.nabble.com/could-not-truncate-directory-pg-subtrans-apparent-wraparound-tp5046156p5046156.html Sent from the PostgreSQL - bugs mailing list archive at Nabble.com. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6325: Useless Index updates
The following bug has been logged on the website: Bug reference: 6325 Logged by: Daniel Migowski Email address: dmigow...@ikoffice.de PostgreSQL version: 8.3.16 Operating system: Linux Description: It seems that an update to a row in a table always removes the element from an index and adds it again. Wouldn't it be faster to check for equality of the index parameters in the OLD and NEW record first? I have this problem with an functional index using a relative expensive index function, and noticed that the index function is always called even if the parameter to the index function has not changed. Wouldn't it be better to validate that the input to the index functions has not changed, instead of calling the index function over and over again? Especially since the index functions seems to be called with the new and the old value anyway. I can understand that this might be a precaution in the case that the index function isn't stable (is it even possible to use such a function for an index?), but in the stable case comparing the input parameters allows for much much faster table updates. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6325: Useless Index updates
On 12/04/2011 08:54 PM, dmigow...@ikoffice.de wrote: The following bug has been logged on the website: Bug reference: 6325 Logged by: Daniel Migowski Email address: dmigow...@ikoffice.de PostgreSQL version: 8.3.16 Operating system: Linux Description: It seems that an update to a row in a table always removes the element from an index and adds it again. Wouldn't it be faster to check for equality of the index parameters in the OLD and NEW record first? - This isn't a bug report, it's a feature/enhancement request. Please use the mailing lists. - You're reporting this issue against an old patch release of an old major release. Why not check with 9.1? - The index isn't always updated. Check out HOT (introduced in 8.4, the release after your current one) which reduces unnecessary index updates in cases where the old and new row can fit on the same heap page. - In most other cases the index update can't be avoided, because the new and old rows are on different database pages. The old index entry has to remain in place so that still-running transactions that can see the old row can still find it in the index, so it can't be overwritten and instead a new entry has to be added. I have this problem with an functional index using a relative expensive index function, and noticed that the index function is always called even if the parameter to the index function has not changed. Wouldn't it be better to validate that the input to the index functions has not changed, instead of calling the index function over and over again? Especially since the index functions seems to be called with the new and the old value anyway. That's a more interesting one. Perhaps you could write it up in more detail, with a test case, and submit it to the pgsql-general mailing list? This isn't just about functions anyway. Pg would have to compare *all* inputs to the old index expression to see if they were the same. Otherwise, in an expression like f(g(x,y),z) Pg would not have any stored value for the result of g(x,y) to compare against. It'd have to instead compare (x1,y1,z1) to (x2,y2,z2) and decide that if they were the same the result of the index expression hadn't changed. That's probably possible, but I'm not sure it'd be a win over just evaluating the expression in most cases. How would Pg know when to do it? Using function COST parameters? Essentially, this isn't as simple as it looks at face value. I can understand that this might be a precaution in the case that the index function isn't stable (is it even possible to use such a function for an index?) No, it isn't possible. Index functions must be immutable, not just stable, so their output must be determined entirely by their parameters. At least on newer versions STABLE or VOLATILE functions should be rejected in index expressions. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6325: Useless Index updates
On Sun, Dec 04, 2011 at 12:54:25PM +, dmigow...@ikoffice.de wrote: > It seems that an update to a row in a table always removes the element from > an index and adds it again. Wouldn't it be faster to check for equality of > the index parameters in the OLD and NEW record first? http://www.depesz.com/index.php/2008/11/05/waiting-for-84-suppress_redundant_updates_trigger/ Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6325: Useless Index updates
On 2011-12-04 15:02, Craig Ringer wrote: - The index isn't always updated. Check out HOT (introduced in 8.4, the release after your current one) which reduces unnecessary index updates in cases where the old and new row can fit on the same heap page. Minor correction: HOT was introduced in 8.3. http://www.postgresql.org/docs/9.1/static/release-8-3.html -- Andreas Karlsson -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6325: Useless Index updates
On 12/05/2011 08:16 AM, Andreas Karlsson wrote: On 2011-12-04 15:02, Craig Ringer wrote: - The index isn't always updated. Check out HOT (introduced in 8.4, the release after your current one) which reduces unnecessary index updates in cases where the old and new row can fit on the same heap page. Minor correction: HOT was introduced in 8.3. http://www.postgresql.org/docs/9.1/static/release-8-3.html Whoops, thanks. In that case the OP is already getting the benefit of reduced index updates as much as is possible with PostgreSQL's MVCC design. -- Craig Ringer -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work
The following bug has been logged on the website:
Bug reference: 6326
Logged by: Maksym Boguk
Email address: maxim.bo...@gmail.com
PostgreSQL version: 9.1.1
Operating system: Linux
Description:
SELECT ARRAY(SELECT ...)
doesn't work when subselect return any array.
Test case:
db=# SELECT ARRAY(SELECT 1 UNION ALL SELECT 2);
?column?
--
{1,2}
All good... now:
db=# SELECT ARRAY(SELECT array[1,2]::float[] UNION ALL SELECT
array[3,4]::float[]);
ERROR: could not find array type for data type double precision[]
db=# SELECT ARRAY(SELECT array[1,2]::integer[] UNION ALL SELECT
array[3,4]::integer[]);
ERROR: could not find array type for data type integer[]
Is that syntax supposed to work with anyarray types?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work
maxim.bo...@gmail.com writes: > SELECT ARRAY(SELECT ...) > doesn't work when subselect return any array. > Is that syntax supposed to work with anyarray types? No. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work
On Mon, Dec 5, 2011 at 2:05 PM, Tom Lane wrote: > maxim.bo...@gmail.com writes: > > SELECT ARRAY(SELECT ...) > > doesn't work when subselect return any array. > > Is that syntax supposed to work with anyarray types? > > No. > >regards, tom lane > Hi. Thank you very much for answer. Ok the second problem (possible related to first becuse error is the same): array_agg doesn't work with anyarray as well: sports=# select array_agg(val) FROM (SELECT array[1,2]::integer[] as val UNION ALL SELECT array[3,4]::integer[]) as t; ERROR: could not find array type for data type integer[] PS: I try create two-dimentional integer array from query results: select val1::integer, val2::integer from somequery to get integer[][] with content like: [[val1-1,val1-2], [val2-1, val2-2], val[3-1, val3-2]... ] Is there any way to perform that task it with reasonable efficiency? -- Maxim Boguk Senior Postgresql DBA.
Re: [BUGS] BUG #6326: SELECT ARRAY(SELECT ...) syntax and array[] results doesn't work
Maxim Boguk writes: > PS: I try create two-dimentional integer array from query results: Well, you could do that with a suitably defined aggregate having the signature "agg(anyarray) returns anyarray". But array_agg has the signature "array_agg(anyelement) returns anyarray" so you can't use it on an array input. 2-D arrays are not distinct from 1-D arrays so far as the type system is concerned. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] possible bug seen with -DCLOBBER_CACHE_ALWAYS and changing GUCs
On Wed, Nov 30, 2011 at 08:10:22PM -0500, Tom Lane wrote: > Jeff Davis writes: > > SQL: > > set datestyle to postgres,us; > > prepare stmt as select '02-01-2011'::date::text; > > execute stmt; > > set datestyle to postgres,euro; > > execute stmt; > > deallocate stmt; > > > The results I get with normal debug compilation are: > > > SET > > PREPARE > > text > > > >02-01-2011 > > (1 row) > > > SET > > text > > > >01-02-2011 > > (1 row) > > > DEALLOCATE > > But with -DCLOBBER_CACHE_ALWAYS and -DRELCACHE_FORCE_RELEASE, I get: > > > SET > > PREPARE > > text > > > >02-01-2011 > > (1 row) > > > SET > > text > > > >02-01-2011 > > (1 row) > > > DEALLOCATE > > > Which one of those results is correct? > > I believe what is happening in the second case is that the query is > getting re-parse-analyzed, from scratch, and since now datestyle is > different (DMY not MDY), the date literal gets interpreted differently. > You could argue it either way as to which result is "more correct", > but I doubt we're going to try to do something about that. Best advice > is to avoid ambiguous input, or if you can't, at least avoid flipping > your datestyle on the fly. One could defend consistent use of either the PREPARE-time DateStyle or the EXECUTE-time DateStyle to interpret literals. However, using the value as of the last RevalidateCachedQuery(), its timing independent of any GUC change, is an implementation artifact with no redeeming value for the user. This hazard also arises around IntervalStyle, TimeZone, sql_inheritance, transform_null_equals, and array_nulls. Implementation challenges aside, I'd contend for always using PREPARE-time values during parse analysis. That's more consistent with the user-visible consequences of changing search_path or standard_conforming_strings. That said, I don't have in mind a cure clearly less ugly than the disease. nm -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
