Re: [BUGS] BUG #6661: out-of-order XID insertion in KnownAssignedXids
Hallo again, I have the situation again, one of 3 slaves was slow to play all the WAL files and being about 10GB late it crashed with the same error again. I collected DEBUG4 output in this time: https://docs.google.com/open?id=0B2NMMrfiBQcLZjNDbU0xQ3lvWms I hope it will be helpful, Regards, -- Valentine Gogichashvili On Wed, May 23, 2012 at 10:51 PM, Valentine Gogichashvili wrote: > But no subtransactions with writes (plpgsql + EXCEPTION also counts if does >> DML)? >> >> > On the master, yes. Practically all the stored procedures there are > catching exceptions in their own bodies and returning result codes back. > > Regards, > > -- Valentine >
Re: [BUGS] BUG #6375: tsearch does not recognize all valid emails
>
> This email thread from 2010 has a similar problem:
>http://archives.postgresql.org/pgsql-hackers/2010-10/msg00772.php
> What is limiting a fix for this is the breaking of existing behavior,
> and the breaking of indexes used during pg_upgrade.
> I have added your email to the existing TODO item:
>http://wiki.postgresql.org/wiki/Todo#Text_Search
>Improve handling of dash and plus signs in email address user
> names, and
>perhaps improve URL parsing
>
> http://archives.postgresql.org/pgsql-hackers/2010-10/msg00772.php
>tsearch does not recognize all valid emails
Thank you Bruce,
as an intermediate solution, so that people, who have the same problem and
search the mailing archives, can get at least some way to overcome this
issue, I am rewriting such emails when building tverctor, and use rewrite
the tsearch queries as well:
code from a function, that builds the tsvector:
select (select string_agg(
case when CASE WHEN n in ( 1, s ) -- all special outer
chars should be escaped
THEN c = ANY
('{.,!,#,$,%,&,'',*,+,/,=,?,_,`,"{",|,"}",~,^,-}'::text[])
ELSE c = ANY
('{!,#,$,%,&,'',*,+,/,=,?,`,"{",|,"}",~,^}'::text[])
END
then 'BCHR' || ascii(c)::text || 'END'
else c
end, '')
from ( select row_number() over() as n, count(1) over() as s, c
from regexp_split_to_table(split_part(p, '@', 1 ), '') as e(c) ) as ee
) || '@' || split_part(p, '@', 2 )
into email
from ( select p_customer_user_row.email::text as p ) as e;
return to_tsvector('simple', coalesce( p_customer_user_row.first_name,
'') ) ||
to_tsvector('simple', coalesce( p_customer_user_row.last_name, '')
) ||
to_tsvector('simple', coalesce( p_customer_user_row.customer_id,
'') ) ||
to_tsvector('simple', coalesce( email, '') );
code from a function, that builds a tsquery:
RETURN (select to_tsquery('simple',
string_agg(
case when p ~ '^[^@]+@[^@]+$' -- has only one @ inside
then (select string_agg(
case when CASE WHEN n in ( 1, s ) --
all special outer chars should be escaped
THEN c = ANY
('{.,!,#,$,%,&,'',*,+,/,=,?,_,`,"{",|,"}",~,^,-}'::text[])
ELSE c = ANY
('{!,#,$,%,&,'',*,+,/,=,?,`,"{",|,"}",~,^}'::text[])
END
then 'BCHR' || ascii(c)::text ||
'END'
else c
end, '')
from ( select row_number() over() as n,
count(1) over() as s, c from regexp_split_to_table(split_part(p, '@', 1 ),
'') as e(c) ) as ee
) || '@' || split_part(p, '@', 2 )
else (select string_agg(token, ' & ')
from ( select unnest(lexemes) || ':*' as
token
from ts_debug('simple', p)
) as g
)
end, ' & ' )
)
from regexp_split_to_table(btrim(search_text), E'\\s+') as s(p)
);
Cheers,
-- Valentine
Re: [BUGS] BUG #6661: out-of-order XID insertion in KnownAssignedXids
Hi,
On Thursday, June 07, 2012 12:44:08 PM Valentine Gogichashvili wrote:
> I have the situation again, one of 3 slaves was slow to play all the WAL
> files and being about 10GB late it crashed with the same error again.
>
> I collected DEBUG4 output in this time:
> https://docs.google.com/open?id=0B2NMMrfiBQcLZjNDbU0xQ3lvWms
Ok, I stared at this some time and I think I see what the problem is. Some log
excerpts that lead my reasoning:
2012-06-06 15:35:51.954 "recovery snapshot waiting for non-overflowed snapshot
or until oldest active xid on standby is at least 3730302193 (now
3730301766)","xlog redo running xacts: nextXid 3730302194
latestCompletedXid 3730302179 oldestRunningXid 3730301766; 68 xacts: ...
subxid ovf"""
So we found a overflowed snapshot after we already are in
STANDBY_SNAPSHOT_PENDING. This implies we have seen an overflowed snapshot
before.
In STANDBY_SNAPSHOT_PENDING we start recording known assigned xids to build
our snapshot incrementally.
2012-06-06 15:35:51.954 record known xact 3730301766 latestObservedXid
3730302197
2012-06-06 15:37:49.580 record known xact 3730316333 latestObservedXid
3730316332 3730316517
Ok, we started filling the KnownAssignedXid machinery. That means procArray-
>numKnownAssignedXids != 0.
2012-06-06 15:37:54.401 FATAL,XX000,"out-of-order XID insertion in
KnownAssignedXids","xlog redo running xacts: nextXid 3730316518
latestCompletedXid 3730316517 oldestRunningXid 3730316333; 4 xacts: 3730316429
3730316508 3730316333 3730316430"""
the "xlog redo running xacts" bits tells us we have got a snapshot that is
*not* overflowed.
That means in ProcArrayAppylRecoveryInfo in the following part:
if (standbyState == STANDBY_SNAPSHOT_PENDING)
{
/*
* If the snapshot isn't overflowed or if its empty we can
* reset our pending state and use this snapshot instead.
*/
if (!running->subxid_overflow || running->xcnt == 0)
{
standbyState = STANDBY_INITIALIZED;
}
else
{
...
}
}
we reset our state back to STANDBY_INITIALIZED. And fall back to computing our
state at once in one piece instead of incrementally as we started doing for
SNAPSHOT_PENDING.
The problem is is know that that code assumes we run without any previous
recorded xids. This is even formalized in an assert which we don't hit because
were running without Assert:
...
Assert(standbyState == STANDBY_INITIALIZED);
...
Assert(procArray->numKnownAssignedXids == 0);
...
after that we start adding all currently running xids from the snapshot to the
KnownAssigned machinery. They are already recorded though, so we fail in
KnownAssignedXidsAdd with the OPs error.
The simplest fix for that seems to be to simply reset the KnownAssignedXids
state in the above branch. Any arguments against that?
This seems to have been broken in commit
10b7c686e52a6d1bb10194ebf9331ef06f044d46
Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Issue when displaying TIMESTAMPTZ values
Hi,
I seem to have found a bug in Postgres 9.1.3. Apparently timestamp
values are stored correctly in the database, but querying it returns
invalid results. My environment is Windows 7 64bit. The unexpected
result is that timestamps before 1st of May 1921 are displayed
incorrectly when time zone is 'Europe/Helsinki'.
- [snip] --
SELECT version();
DROP TABLE IF EXISTS test2;
CREATE TABLE test2 (ts TIMESTAMPTZ NOT NULL DEFAULT NOW());
INSERT INTO test2 VALUES ('1921-01-01 00:00:00+00');
INSERT INTO test2 VALUES ('1921-04-29 00:00:00+00');
INSERT INTO test2 VALUES ('1921-04-30 00:00:00+00');
INSERT INTO test2 VALUES ('1921-05-01 00:00:00+00');
INSERT INTO test2 VALUES ('1922-01-01 00:00:00+00');
INSERT INTO test2 VALUES ('1999-01-08 04:05:06 -8:00');
INSERT INTO test2 VALUES ('today allballs');
SET TIME ZONE EET;
SELECT * FROM test2;
--ts
--
-- 1921-01-01 02:00:00+02
-- 1921-04-29 02:00:00+02
-- 1921-04-30 02:00:00+02
-- 1921-05-01 02:00:00+02
-- 1922-01-01 02:00:00+02
-- 1999-01-08 14:05:06+02
-- 2012-06-07 03:00:00+03
-- (7 rows)
SET TIME ZONE 'Europe/Helsinki';
SELECT * FROM test2;
-- ts
-- --
-- 1921-01-01 01:39:52+01:39:52
-- 1921-04-29 01:39:52+01:39:52
-- 1921-04-30 01:39:52+01:39:52
-- 1921-05-01 02:00:00+02
-- 1922-01-01 02:00:00+02
-- 1999-01-08 14:05:06+02
-- 2012-06-07 03:00:00+03
-- (7 rows)
- [snip] --
Best regards
Kasper Rönning
--
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] Issue when displaying TIMESTAMPTZ values
=?ISO-8859-1?Q?Kasper_R=F6nning?= writes: > I seem to have found a bug in Postgres 9.1.3. Apparently timestamp > values are stored correctly in the database, but querying it returns > invalid results. My environment is Windows 7 64bit. The unexpected > result is that timestamps before 1st of May 1921 are displayed > incorrectly when time zone is 'Europe/Helsinki'. This is not incorrect. The Olson timezone database shows: # Zone NAMEGMTOFF RULES FORMAT [UNTIL] ZoneEurope/Helsinki 1:39:52 - LMT 1878 May 31 1:39:52 - HMT 1921 May# Helsinki Mean Time 2:00Finland EE%sT 1983 2:00EU EE%sT that is, it was only in 1921 that Helsinki adopted "standard" time referenced to the Greenwich meridian. Before that it would have been typical to set clocks by local mean solar time, which is 1:39:52 east of Greenwich. 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 #6661: out-of-order XID insertion in KnownAssignedXids
On Thursday, June 07, 2012 03:58:24 PM Andres Freund wrote:
> Hi,
>
> On Thursday, June 07, 2012 12:44:08 PM Valentine Gogichashvili wrote:
> > I have the situation again, one of 3 slaves was slow to play all the WAL
> > files and being about 10GB late it crashed with the same error again.
> >
> > I collected DEBUG4 output in this time:
> > https://docs.google.com/open?id=0B2NMMrfiBQcLZjNDbU0xQ3lvWms
>
> Ok, I stared at this some time and I think I see what the problem is. Some
> log excerpts that lead my reasoning:
> ...
> after that we start adding all currently running xids from the snapshot to
> the KnownAssigned machinery. They are already recorded though, so we fail
> in KnownAssignedXidsAdd with the OPs error.
>
> The simplest fix for that seems to be to simply reset the KnownAssignedXids
> state in the above branch. Any arguments against that?
A patch implementing that is attached. Unfortunately not really tested yet
because its kinda hard to hit that code-path.
Valentine, can you test that patch?
Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From 91c6b4195233c5dfeb794b983c97cef61d966e1b Mon Sep 17 00:00:00 2001
From: Andres Freund
Date: Thu, 7 Jun 2012 18:38:32 +0200
Subject: [PATCH] Fix a bug in the assembly of recovery snapshots in Hot
Standby
We previously failed if we read a non-overflowed snapshot after starting to
incrementally assemble a snapshot after reading an overflowed one. Code added
in 10b7c686e52a6d1bb10194ebf9331ef06f044d46 added a fallback in that case to
simply build a completely new snapshot if we have the necessary information but
forgot to cleanup the partial incremental one.
Add and use a KnownAssignedXidsReset function for that.
---
src/backend/storage/ipc/procarray.c | 28 +++-
1 file changed, 27 insertions(+), 1 deletion(-)
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 26469c4..25bfefc 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -160,6 +160,7 @@ static int KnownAssignedXidsGetAndSetXmin(TransactionId *xarray,
TransactionId xmax);
static TransactionId KnownAssignedXidsGetOldestXmin(void);
static void KnownAssignedXidsDisplay(int trace_level);
+static void KnownAssignedXidsReset(void);
/*
* Report shared-memory space needed by CreateSharedProcArray.
@@ -526,6 +527,11 @@ ProcArrayApplyRecoveryInfo(RunningTransactions running)
*/
if (!running->subxid_overflow || running->xcnt == 0)
{
+ /*
+ * we already may have collected assigned xids, we need to throw
+ * that knowledge away to apply the recovery snapshot.
+ */
+ KnownAssignedXidsReset();
standbyState = STANDBY_INITIALIZED;
}
else
@@ -569,7 +575,8 @@ ProcArrayApplyRecoveryInfo(RunningTransactions running)
* xids to subtrans. If RunningXacts is overflowed then we don't have
* enough information to correctly update subtrans anyway.
*/
- Assert(procArray->numKnownAssignedXids == 0);
+ if(procArray->numKnownAssignedXids != 0)
+ elog(ERROR, "the KnownAssignedXids machinery cannot be initialized when applying a full recovery snapshot");
/*
* Allocate a temporary array to avoid modifying the array passed as
@@ -3340,3 +3347,22 @@ KnownAssignedXidsDisplay(int trace_level)
pfree(buf.data);
}
+
+/*
+ * KnownAssignedXidsReset
+ * Resets KnownAssignedXids to be empty
+ */
+static void
+KnownAssignedXidsReset(void)
+{
+ /* use volatile pointer to prevent code rearrangement */
+ volatile ProcArrayStruct *pArray = procArray;
+
+ LWLockAcquire(ProcArrayLock, LW_EXCLUSIVE);
+
+ pArray->numKnownAssignedXids = 0;
+ pArray->tailKnownAssignedXids = 0;
+ pArray->headKnownAssignedXids = 0;
+
+ LWLockRelease(ProcArrayLock);
+}
--
1.7.10.rc3.3.g19a6c.dirty
--
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] Issue when displaying TIMESTAMPTZ values
=?ISO-8859-1?Q?Kasper_R=F6nning?= writes:
> Thank you for the quick reply! I was completely unaware of the old time
> zones of Helsinki! However I'm afraid that the behaviour of Postgresql
> seems plain wrong to me. An example:
> SET TIME ZONE 'Europe/Helsinki';
> DROP TABLE IF EXISTS test1;
> CREATE TABLE test1 (ts TIMESTAMPTZ);
> INSERT INTO test1 VALUES ('0001-01-01 00:00:00');
> SELECT * FROM test1;
> -- ts
> -- --
> -- 0001-01-01 00:00:00+01:39:52
> Here I enter a timestamp in Helsinki time zone, and the query result is
> different, even though the time zone is the same.
No, the query result is the same, it's just more fully specified.
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] Issue when displaying TIMESTAMPTZ values
Hi,
Thank you for the quick reply! I was completely unaware of the old time
zones of Helsinki! However I'm afraid that the behaviour of Postgresql
seems plain wrong to me. An example:
SET TIME ZONE 'Europe/Helsinki';
DROP TABLE IF EXISTS test1;
CREATE TABLE test1 (ts TIMESTAMPTZ);
INSERT INTO test1 VALUES ('0001-01-01 00:00:00');
SELECT * FROM test1;
-- ts
-- --
-- 0001-01-01 00:00:00+01:39:52
Here I enter a timestamp in Helsinki time zone, and the query result is
different, even though the time zone is the same.
I will resolve this matter by using TIMESTAMP WITHOUT TIME ZONE instead,
and storing the offset manually. That way I get two advantages: 1)
queried time stamp will be equal to the inserted one 2) time offset
information is stored, allowing the time to be displayed in the same
time zone as it was entered and at the point in time that it was entered.
Best regards,
Kasper Rönning
On 7.6.2012 17:37, Tom Lane wrote:
=?ISO-8859-1?Q?Kasper_R=F6nning?= writes:
I seem to have found a bug in Postgres 9.1.3. Apparently timestamp
values are stored correctly in the database, but querying it returns
invalid results. My environment is Windows 7 64bit. The unexpected
result is that timestamps before 1st of May 1921 are displayed
incorrectly when time zone is 'Europe/Helsinki'.
This is not incorrect. The Olson timezone database shows:
# Zone NAMEGMTOFF RULES FORMAT [UNTIL]
ZoneEurope/Helsinki 1:39:52 - LMT 1878 May 31
1:39:52 - HMT 1921 May# Helsinki Mean Time
2:00Finland EE%sT 1983
2:00EU EE%sT
that is, it was only in 1921 that Helsinki adopted "standard" time
referenced to the Greenwich meridian. Before that it would have been
typical to set clocks by local mean solar time, which is 1:39:52 east
of Greenwich.
regards, tom lane
--
Kasper Rönning
Reliabit Ay
www.reliabit.fi
kasper.ronn...@reliabit.fi
Tel: +358-445 010 634
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
