[SQL] WAL-files restore and nextval('PK')
Dear all,
after an WAL-restore of our Postgres DB, we observe seemingly wrong values of
our sequences.
We have two postgres server (8.4) with pgpool in replication mode.
Recently we tested our restore procedure and played our WAL-files into the
second server after an old file-system backup was restored.
Accidently, we aborted the starting server and had to restart it and therefore
started WAL-replay again.
Now we observe, that the newly restored server has higher values in his
sequences as the other server.
Could it be that during restart of the WAL-file restore SELECT nextval('PK') ;
commands were executed again, whereas the data rows were already restored and
thus leading to higher sequence numbers on the newly restored server?
Best regards for any comments!
Andreas
___
SCANLAB AG
Dr. Andreas Simon Gaab
Entwicklung * R & D
Siemensstr. 2a * 82178 Puchheim * Germany
Tel. +49 (89) 800 746-513 * Fax +49 (89) 800 746-199
mailto:a.g...@scanlab.de * www.scanlab.de
Amtsgericht München: HRB 124707 * USt-IdNr.: DE 129 456 351
Vorstand: Georg Hofner (Sprecher), Christian Huttenloher, Norbert Petschik
Aufsichtsrat (Vorsitz): Dr. Hans J. Langer
___
Re: [SQL] WAL-files restore and nextval('PK')
Andreas Gaab writes: > after an WAL-restore of our Postgres DB, we observe seemingly wrong values of > our sequences. > We have two postgres server (8.4) with pgpool in replication mode. > Recently we tested our restore procedure and played our WAL-files into the > second server after an old file-system backup was restored. > Accidently, we aborted the starting server and had to restart it and > therefore started WAL-replay again. > Now we observe, that the newly restored server has higher values in his > sequences as the other server. It's normal for sequence counters to be a few counts higher after a crash-and-restart than they would have been if no crash had occurred. This is an intentional design tradeoff to minimize the WAL overhead associated with assigning a sequence value. If you find it intolerable for what you're doing, I believe you can prevent it by adjusting the sequence parameters to prevent any "caching" of values. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Tsearch not searching 'Y'
On Thu, Apr 29, 2010 at 01:13:40PM -, sandeep prakash dhumale wrote:
> Hello All,
>
> I am trying to get tsearch working for my application but I am facing a
> problem when alphabet 'Y' is the in the tsquery.
>
> can anyone please share some light on it.
>
>
> # SELECT 'hollywood'::tsvector @@ to_tsquery('holly:*');
> ?column?
> --
> f
> (1 row)
>
> SELECT 'hollywood'::tsvector @@ to_tsquery('holl:*');
> ?column?
> --
> t
> (1 row)
>
>
> It works when i put <> in y as below but i don't want to do it that way.
>
> SELECT 'hollywood'::tsvector @@ to_tsquery('holl:*');
> ?column?
> --
> t
>
> Thanks in advance
That is because the to_tsquery() normalizes the tokens. Here is
what I get from the default configuration:
db=# select to_tsquery('holly:*');
to_tsquery
'holli':*
(1 row)
db=# select to_tsquery('holl:*');
to_tsquery
'holl':*
(1 row)
It is pretty easy to see why you see the behavior that you do.
Maybe you need to change your tsearch configuration to match what
you expect to happen.
Regards,
Ken
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] [GENERAL] Tsearch not searching 'Y'
You can avoid stemming by using 'simple' instead of 'english' as the
language of the words in to_tsvector (which is a little more awkward
than the cast).
"There are no stop words for the simple dictionary. It will just
convert to lower case, and index every unique word.
SELECT to_tsvector('simple', 'Andy andy The the in out');
to_tsvector
-
'in':5 'out':6 'the':3,4 'andy':1,2
(1 row)
John
On Apr 29, 2010, at 4:01 PM, Tom Lane wrote:
"sandeep prakash dhumale" writes:
I am trying to get tsearch working for my application but I am
facing a
problem when alphabet 'Y' is the in the tsquery.
# SELECT 'hollywood'::tsvector @@ to_tsquery('holly:*');
?column?
--
f
(1 row)
You can't use to_tsquery for this sort of thing, because it tries to
normalize the given words:
regression=# select to_tsquery('holly:*');
to_tsquery
'holli':*
(1 row)
If you do this it works:
regression=# SELECT 'hollywood'::tsvector @@ 'holly:*'::tsquery;
?column?
--
t
(1 row)
So if you want to use prefix matching, don't normalize.
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] [GENERAL] Schema design / joins
Hi, This is more of a general schema design, any advice is much appreciated. I have a Organization table. Nearly every other table in the schema is related to this Org table in some way. So, some tables may be 3 or 4 tables 'away' from the Org table. In order to filter by the org_id, I need to join a bunch(?3-6) of tables Simple example below, TeamFees belong to a Team, which belongs to a Season, which belong to an Org. In order to get all the TeamFees that belong to a given Org, I need to join all the tables which isn't a big deal, but I'm just wonder if putting an extra 'org_id' on Team fees would help anything... ** Is it a bad idea to put an extra FK 'org_id' on the TeamFees table to avoid all the joins? ** What about putting an 'org_id' on every table? (it seems somewhat redundant/unnecessary to me) I've never had any formal education in rdbms, but from what I can gather, foreign keys are meant to ensure data consistency, not reduce the number of joins required. Although, it sure seams like it would simplify the queries if I stuck extra 'org_id' columns in certain places. I don't have any particular reason that I'm trying to avoid joins -- I'm just wondering if there is something simpler or if 'thats just how it is.' I would really, really appreciate any suggestions from folks with rdbms schema design experience! Thanks! __Orgs__ id name __Seasons__ id org_id fk(orgs.id) name __Teams__ id season_id fk(seasons.id) name __TeamFees__ id team_id fk(teams.id) *org_id <--- (?put extra fk here to avoid many joins?) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] WAL-files restore and nextval('PK')
Andreas Gaab wrote: > Dear all, > > after an WAL-restore of our Postgres DB, we observe seemingly wrong values of > our sequences. This is normal. The reason is that sequences are WAL-logged in chunks, not single values (otherwise they'd be too slow). So after recovery, the values jump to the logged values, which are necessarily higher than the values they last delivered before the crash. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
