Hallo Liste,

ich habe folgendes System am Start und wüßte gerne, wie ich es am
besten konfiguriere, damit alle beteiligten Clients (drei Gruppen) die
jeweils korrespondierenden Zeitstempel erhalten und damit alle
beteiligten möglichst effizient arbeiten können:

(a) mehrere Clients populieren eine Tabelle der Datenbank mit Tupeln
der Form (timestamptz, id1, id2, id3, data ...). Dabei bilden
timestamptz, id1, id2, id3 den Primärschlüssel. (id1, id2, id3) ist
ein referenzierter Fremdschlüssel. Jeder dieser Clients hatte bislang
(bis zum "rumspielen" an der Konfiguration heute) die
Umgebungsvariable PGTZ=3DCET und erhält Daten aus der Standardeingabe,
die allesamt Winterzeit referenzieren. Jeder dieser Clients erhält
seine Zeit als Argument z.B. -t '03.04.2006 12:00:00'. Den Wert dieses
Arguments (string, libpqxx) verwendet der Client beim Einfügen in die
Datenbank. Die Tabelle enthält mittlerweile Daten mit mehr als
100GB. Im Endstadium schätze ich das Volumen auf 200GB.

(b) andere Clients starten zu einem beliebigen Zeitpunkt und setzen
ihre Uhr auf localtime abgerundet auf die volle Minute weniger ein
paar gewollten Verzögerungsminuten. Aufgabe dieser Clients ist es, die
jeweils ihrer Zeit entsprechenden neuesten Daten zu erfragen und
korrespondierende Rechenergebnisse zurückzuschreiben. In der
Sommerzeit (CEST) wollen sie die Daten mit ihrem Zeitstempel - 1h; in
der Winterzeit - 0h. Auch diese Clients haben PGTZ=3DCET bzw. jetzt
PGTZ=3D+01.

(c) die letzte Gruppe von Clients will nur die stets aktuellsten
Rechenergebnisse der vorherigen Gruppe (b). Auch diese Clients
verwenden die leicht modifizierte localtime(0) wie oben bei der
Datenbankanfrage. Sie besitzen die Umgebungsvariable PGTZ=3D-0000. Die
Tabelle, deren Daten sie abfragen, hat die Form (timestamptz,
(Fremdschlüssel), data ...). Dieser Fremdschlüssel ist nicht identisc=
h
mit (a). Die Tabelle enthält zwar nur jeweils die letzten 24- oder
eventuell auch weniger Stunden, jedoch sind das auch in etwa
1240-Datensätze pro Minute.

Die Systemzeit war bis gestern Europe/Berlin, seit heute
UTC. Postgres, Version 8.0.4, besitzt in Bezug auf die Zeit die
Voreinstellung, das locale ist auf C. Alle Clients verwenden
PGDATESTYLE=3DGerman.

Phänomen:

Es sei 03.04.2006 09:00:00 UTC. Ein (a)-Client hat Daten mit
'03.04.2006 10:00:00 +01' und schreibt diese in die Datenbank. Eine
spätere Anfrage mittels PGTZ=3DUTC psql liefert keine Daten mit
'03.04.2006 09:00:00'. Die Daten erhalte ich nur mit '03.04.2006
08:00:00'. Mittlerweile habe ich die (a)-Clients umgestellt auf
PGTZ=3DUTC und erhalte nun die entsprechenden Daten mit '03.04.2006
10:00:00'.

Fragen:

1. Wie können die Daten aus (a) unter CET bzw. UTC +01 gespeichert
werden? Sollten diese Daten eventuell mit einem anderen Datum
eingetragen werden? Muss ich Daten seit der Umstellung auf CEST
mittels UPDATE aktualisieren?

2. Welche PGTZ-Einstellung benötigen die Clients aus (b), damit sie
die entsprechenden Daten erhalten? Benötigen diese Clients eventuell
oder unbedingt eine besondere Zeitstempelkonvertierung?

3. Welche PGTZ-Einstellung ist für die (c)-Clients am sinnvollsten?

4. Was bedeutet integer_datetimes =3D off aus den
show-all-Einstellungen?

5. Für (c) benötige ich nun eine performantere Anfrage, die zu jedem
Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunden,
ich bin mir aber auch nicht sicher, ob das der richtige Weg
ist, denn da muss die Datenbank doch das jeweilige Maximum ermitteln
(alle Datensätze durchsuchen), oder? Welcher Weg ist am
effizientesten?

6. Wo könnte ich sonst noch Optimierungen vornehmen? Was sollte ich
anders machen?

Danke für Eure Geduld und Mühe.

Freundliche Grüße
Johannes Brügmann
--





---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

am 03.04.2006, um 12:07:21 +0200 mailte Johannes BrXgmann folgendes:
> Hallo Liste,
>
> ein referenzierter Fremdschlüssel. Jeder dieser Clients hatte bislang
> (bis zum "rumspielen" an der Konfiguration heute) die
> Umgebungsvariable PGTZ=3DCET und erhält Daten aus der Standardeingabe=
,

=C4hm. Warum verwendest Du nicht einfach immer und generell die aktuelle
und korrekte Zeit?

Beim einfügen/updaten von Datensätzen kannst Du ja auch now() verwend=
en.
(bzw. current_timestamp). Das speichert dann die DB.


>
> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu jede=
m
> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunden,

max(timestamptz)


> ich bin mir aber auch nicht sicher, ob das der richtige Weg
> ist, denn da muss die Datenbank doch das jeweilige Maximum ermitteln
> (alle Datensätze durchsuchen), oder? Welcher Weg ist am

Nein, nicht, wenn ein Index drauf liegt.


> 6. Wo könnte ich sonst noch Optimierungen vornehmen? Was sollte ich
> anders machen?
>
> Danke für Eure Geduld und Mühe.

Sorry, so recht Geduld (Zeit) hab ich grad nicht für a-c.

--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=3D=3D=3D Schollglas Unternehmensgruppe =3D=3D=3D

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

am 03.04.2006, um 14:48:14 +0200 mailte Johannes BrXgmann folgendes:
> >> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu j=
edem
> >> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
> >> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunden=
,
> >
> > max(timestamptz)
>
> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräuchte
> irgendetwas < 10s, eher weniger. Noch andere Ideen?

Zeig uns die Tabellenstruktur und ein explain analyse der Abfrage.
Ich habe auf Hinterhof-hardware in einer mehrere Millionen Zeilen
umfassender Tabelle und Suche nach vielleicht 6 Bedingungen Suchzeiten
im 2-stelligen Millisekundenbereich.

Welche Version hast Du?

> >> ich bin mir aber auch nicht sicher, ob das der richtige Weg
> >> ist, denn da muss die Datenbank doch das jeweilige Maximum ermitteln
> >> (alle Datensätze durchsuchen), oder? Welcher Weg ist am
> >
> > Nein, nicht, wenn ein Index drauf liegt.
>
> Hm. Ein Primärschlüssel legt doch einen impliziten Index an. Reicht
> das nicht?

Jein, hast Du weitere Spalten in der WHERE, wo kein Idex ist? Regelmäß=
ig
VACUUM? Ein 'EXPLAIN' bzw. 'EXPLAIN ANALYSE' kann Dir die Schwachstellen
zeigen.


Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=3D=3D=3D Schollglas Unternehmensgruppe =3D=3D=3D

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Hallo Andreas,
hallo Liste,

danke erstmal für Deine prompte Antwort, Andreas!

"A. Kretschmer" <andreas.kretschmer [at] schollglas.com> writes:

> am 03.04.2006, um 12:07:21 +0200 mailte Johannes BrXgmann folgendes:
>
>> ein referenzierter Fremdschlüssel. Jeder dieser Clients hatte bislan=
g
>> (bis zum "rumspielen" an der Konfiguration heute) die
>> Umgebungsvariable PGTZ=3DCET und erhält Daten aus der Standardeingab=
e,
>
> =C4hm. Warum verwendest Du nicht einfach immer und generell die aktuell=
e
> und korrekte Zeit?

Die von mir verwendete Zeit ist eine Referenzzeit und ist mit externen
Ereignissen verknüpft. Sie muss bis zum Schluss der Kette, also von
(a) - (c) durchgeschleift werden, damit sie weitergereicht werden
kann. Alle Daten und Rechenergebnisse beziehen sich immer auf eine
bestimmte Referenzzeit. Es ist außerdem nicht jederzeit gewährleistet=
,
dass die frisch eingetroffenen Daten auch die neuesten sind.

>> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu jed=
em
>> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
>> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunden,
>
> max(timestamptz)

habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräuchte
irgendetwas < 10s, eher weniger. Noch andere Ideen?

>> ich bin mir aber auch nicht sicher, ob das der richtige Weg
>> ist, denn da muss die Datenbank doch das jeweilige Maximum ermitteln
>> (alle Datensätze durchsuchen), oder? Welcher Weg ist am
>
> Nein, nicht, wenn ein Index drauf liegt.

Hm. Ein Primärschlüssel legt doch einen impliziten Index an. Reicht
das nicht?

>> 6. Wo könnte ich sonst noch Optimierungen vornehmen? Was sollte ich
>> anders machen?
>>
>> Danke für Eure Geduld und Mühe.
>
> Sorry, so recht Geduld (Zeit) hab ich grad nicht für a-c.

Danke Dir trotzdem vielmals! Vielleicht probiere ich es auch mal auf
Englisch.

Gruß,
Johannes
--


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hallo Andreas,
hallo Liste,

danke nochmals für Deine Mühe, Andreas!

"A. Kretschmer" <andreas.kretschmer [at] schollglas.com> writes:

> am 03.04.2006, um 14:48:14 +0200 mailte Johannes BrXgmann folgendes:
>> >> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu =
jedem
>> >> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Eine
>> >> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefunde=
n,
>> >
>> > max(timestamptz)
>>
>> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräucht=
e
>> irgendetwas < 10s, eher weniger. Noch andere Ideen?
>
> Zeig uns die Tabellenstruktur und ein explain analyse der Abfrage.
> Ich habe auf Hinterhof-hardware in einer mehrere Millionen Zeilen
> umfassender Tabelle und Suche nach vielleicht 6 Bedingungen Suchzeiten
> im 2-stelligen Millisekundenbereich.

1. Tabelle:
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

CREATE TABLE verkehrsdaten_current (
zeit TIMESTAMP WITH TIME ZONE,
otdf_id char(14),
FOREIGN KEY (otdf_id) REFERENCES compat_otdf (otdf_id),
fluss smallint,
dichte real,
geschwindigkeit smallint,
reisezeit smallint,
verkehrslage smallint,
CHECK (((fluss > 0) AND (fluss < 360))
AND ((dichte > 0.009) AND (dichte < 200.0))
AND ((geschwindigkeit > 0) AND (geschwindigkeit < 150))
AND ((reisezeit > 1) AND (reisezeit < 3600))
AND ((verkehrslage > 0) AND (verkehrslage < 10))),
PRIMARY KEY (zeit, otdf_id)
);

CREATE UNIQUE INDEX verkehrsdaten_current_zeit_index ON verkehrsdaten_cur=
rent(zeit, otdf_id);

Index habe ich vorhin erstellt.

2. Anfragen und Dauer
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D
- grösser als 1 Minute, Hardware: Dual Xeon 3,6 GHz 1GB RAM:
SELECT DISTINCT otdf_id,
max(zeit)
FROM verkehrsdaten_current
GROUP BY otdf_id;

=
QUERY PLAN
------------------------------------------------------------ -------------=
------------------------------------------------------------ -------------=
----------
Unique (cost=3D397630.85..397640.12 rows=3D1236 width=3D26) (actual tim=
e=3D128562.326..128564.593 rows=3D1240 loops=3D1)
-> Sort (cost=3D397630.85..397633.94 rows=3D1236 width=3D26) (actual=
time=3D128562.323..128562.924 rows=3D1240 loops=3D1)
Sort Key: otdf_id, max(zeit)
-> HashAggregate (cost=3D397564.28..397567.37 rows=3D1236 widt=
h=3D26) (actual time=3D128558.353..128559.832 rows=3D1240 loops=3D1)
-> Seq Scan on verkehrsdaten_current (cost=3D0.00..35569=
2.52 rows=3D8374352 width=3D26) (actual time=3D70999.398..118637.183 rows=
=3D8415880 loops=3D1)
Total runtime: 128565.455 ms
(6 Zeilen)

Lese ich das richtig, dass Postgres hier einen Sequential-Scan über
8415880 Zeilen durchführt? Ich verstehe nicht warum.

- weniger als 1 Sekunde:
SELECT DISTINCT otdf_id,
max(zeit)
FROM verkehrsdaten_current
WHERE zeit > localtimestamp(0) - interval '3 hour'
GROUP BY otdf_id;
=
QUERY PLAN
------------------------------------------------------------ -------------=
------------------------------------------------------------ -------------=
------------------------------------
Unique (cost=3D1472.74..1472.75 rows=3D1 width=3D26) (actual time=3D389=
..795..393.038 rows=3D1240 loops=3D1)
-> Sort (cost=3D1472.74..1472.75 rows=3D1 width=3D26) (actual time=3D=
389.793..390.600 rows=3D1240 loops=3D1)
Sort Key: otdf_id, max(zeit)
-> HashAggregate (cost=3D1472.73..1472.73 rows=3D1 width=3D26)=
(actual time=3D383.276..385.640 rows=3D1240 loops=3D1)
-> Index Scan using verkehrsdaten_current_zeit_index on v=
erkehrsdaten_current (cost=3D0.01..1468.62 rows=3D822 width=3D26) (actua=
l time=3D0.106..247.613 rows=3D71920 loops=3D1)
Index Cond: (zeit > (('now'::text)::timestamp(0) wit=
hout time zone - ' [at] 3 hours'::interval))
Total runtime: 394.069 ms
(7 Zeilen)

Die 3 Stunden bei interval bedeuten letztlich nur Daten, die höchstens
eine Stunde zurückliegen, siehe OP.

> Welche Version hast Du?

8.0.4

>> >> ich bin mir aber auch nicht sicher, ob das der richtige Weg
>> >> ist, denn da muss die Datenbank doch das jeweilige Maximum ermittel=
n
>> >> (alle Datensätze durchsuchen), oder? Welcher Weg ist am
>> >
>> > Nein, nicht, wenn ein Index drauf liegt.
>>
>> Hm. Ein Primärschlüssel legt doch einen impliziten Index an. Reich=
t
>> das nicht?
>
> Jein, hast Du weitere Spalten in der WHERE, wo kein Idex ist? Regelmä=
ßig
> VACUUM? Ein 'EXPLAIN' bzw. 'EXPLAIN ANALYSE' kann Dir die Schwachstelle=
n
> zeigen.

Ist noch im Aufbaustadium, daher nicht regelmäßig. VACUUM ANALYZE hab=
e
ich aber Freitag oder so laufen lassen.

Danke jedenfalls,
Johannes
--


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

am 03.04.2006, um 16:42:06 +0200 mailte Johannes BrXgmann folgendes:
> > Zeig uns die Tabellenstruktur und ein explain analyse der Abfrage.
> > Ich habe auf Hinterhof-hardware in einer mehrere Millionen Zeilen
> > umfassender Tabelle und Suche nach vielleicht 6 Bedingungen Suchzeite=
n
> > im 2-stelligen Millisekundenbereich.
>
> 1. Tabelle:
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>
> CREATE TABLE verkehrsdaten_current (
> zeit TIMESTAMP WITH TIME ZONE,
> otdf_id char(14),
> FOREIGN KEY (otdf_id) REFERENCES compat_otdf (otdf_id),
> fluss smallint,
> dichte real,
> geschwindigkeit smallint,
> reisezeit smallint,
> verkehrslage smallint,
> CHECK (((fluss > 0) AND (fluss < 360))
> AND ((dichte > 0.009) AND (dichte < 200.0))
> AND ((geschwindigkeit > 0) AND (geschwindigkeit < 150))
> AND ((reisezeit > 1) AND (reisezeit < 3600))
> AND ((verkehrslage > 0) AND (verkehrslage < 10))),
> PRIMARY KEY (zeit, otdf_id)
> );
>
> CREATE UNIQUE INDEX verkehrsdaten_current_zeit_index ON verkehrsdaten_c=
urrent(zeit, otdf_id);
>
> Index habe ich vorhin erstellt.
>
> 2. Anfragen und Dauer
> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D =3D
> - grösser als 1 Minute, Hardware: Dual Xeon 3,6 GHz 1GB RAM:
> SELECT DISTINCT otdf_id,
> max(zeit)
> FROM verkehrsdaten_current
> GROUP BY otdf_id;
>
> =
QUERY PLAN
> ------------------------------------------------------------ -----------=
------------------------------------------------------------ -------------=
------------
> Unique (cost=3D397630.85..397640.12 rows=3D1236 width=3D26) (actual t=
ime=3D128562.326..128564.593 rows=3D1240 loops=3D1)
> -> Sort (cost=3D397630.85..397633.94 rows=3D1236 width=3D26) (actu=
al time=3D128562.323..128562.924 rows=3D1240 loops=3D1)
> Sort Key: otdf_id, max(zeit)
> -> HashAggregate (cost=3D397564.28..397567.37 rows=3D1236 wi=
dth=3D26) (actual time=3D128558.353..128559.832 rows=3D1240 loops=3D1)
> -> Seq Scan on verkehrsdaten_current (cost=3D0.00..355=
692.52 rows=3D8374352 width=3D26) (actual time=3D70999.398..118637.183 ro=
ws=3D8415880 loops=3D1)
> Total runtime: 128565.455 ms
> (6 Zeilen)
>
> Lese ich das richtig, dass Postgres hier einen Sequential-Scan über
> 8415880 Zeilen durchführt? Ich verstehe nicht warum.

Korrekt.

Vermutung:
Da der Index über (zeit, otdf_id) geht, nützt er hier nix.
2 separate Indexe über otdf_id und zeit würden vermutlich helfen.


>
> - weniger als 1 Sekunde:
> SELECT DISTINCT otdf_id,
> max(zeit)
> FROM verkehrsdaten_current
> WHERE zeit > localtimestamp(0) - interval '3 hour'
> GROUP BY otdf_id;
> =
QUERY PLAN
> ------------------------------------------------------------ -----------=
------------------------------------------------------------ -------------=
--------------------------------------
> Unique (cost=3D1472.74..1472.75 rows=3D1 width=3D26) (actual time=3D3=
89.795..393.038 rows=3D1240 loops=3D1)
> -> Sort (cost=3D1472.74..1472.75 rows=3D1 width=3D26) (actual time=
=3D389.793..390.600 rows=3D1240 loops=3D1)
> Sort Key: otdf_id, max(zeit)
> -> HashAggregate (cost=3D1472.73..1472.73 rows=3D1 width=3D2=
6) (actual time=3D383.276..385.640 rows=3D1240 loops=3D1)
> -> Index Scan using verkehrsdaten_current_zeit_index on=
verkehrsdaten_current (cost=3D0.01..1468.62 rows=3D822 width=3D26) (act=
ual time=3D0.106..247.613 rows=3D71920 loops=3D1)
> Index Cond: (zeit > (('now'::text)::timestamp(0) w=
ithout time zone - ' [at] 3 hours'::interval))
> Total runtime: 394.069 ms
> (7 Zeilen)
>
> Die 3 Stunden bei interval bedeuten letztlich nur Daten, die höchsten=
s
> eine Stunde zurückliegen, siehe OP.

Ja, hier merkt er, daß er den Index doch nutzen kann, da er sehr
selektiv ist.


>
> > Welche Version hast Du?
>
> 8.0.4

Ab 8.1 geht Bitmap Index Scan, der würde möglicherweise auch einiges
bringen.


> Ist noch im Aufbaustadium, daher nicht regelmäßig. VACUUM ANALYZE h=
abe

Ich würde echt empfehlen, auf 8.1 umzusteigen, wenn es noch im Aufbau
ist, ist das auch einfacher.


Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47215, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=3D=3D=3D Schollglas Unternehmensgruppe =3D=3D=3D

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo [at] postgresql.org so that your
message can get through to the mailing list cleanly

Johannes Brügmann schrob:

> "A. Kretschmer" <andreas.kretschmer [at] schollglas.com> writes:
>
>> am 03.04.2006, um 14:48:14 +0200 mailte Johannes BrXgmann folgendes:
>>> >> 5. Für (c) benötige ich nun eine performantere Anfrage, die zu=
jedem
>>> >> Fremdschlüssel-Tupel seine aktuellsten Datensätze liefert. Ein=
e
>>> >> Aggregatfunktion newest(timestamptz) oder so habe ich nicht gefund=
en,
>>> >
>>> > max(timestamptz)
>>>
>>> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräuch=
te
>>> irgendetwas < 10s, eher weniger. Noch andere Ideen?
[...]
>> Welche Version hast Du?
>
> 8.0.4

Die Optimierung für min()/max()-Aggregatfunktionen gibt es erst ab
8.1. Man kann sie mit 8.0 jedoch leicht nachbauen:

select stamp from foo order by stamp limit 1
bzw.
select stamp from foo order by stamp desc limit 1

....können einen Index verwenden.

Gruß
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

am 03.04.2006, um 18:54:19 +0200 mailte Andreas Seltenreich folgendes:
> >>> > max(timestamptz)
> >>>
> >>> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich bräu=
chte
> >>> irgendetwas < 10s, eher weniger. Noch andere Ideen?
> [...]
> >> Welche Version hast Du?
> >
> > 8.0.4
>
> Die Optimierung für min()/max()-Aggregatfunktionen gibt es erst ab
> 8.1. Man kann sie mit 8.0 jedoch leicht nachbauen:

Yepp ;-)


Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
eMail schreiben kann jeder -- lernen: http://webserv/email/email.html

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Ich versuche mal, auf die noch offenen Fragen einzugehen...

Johannes Brügmann schrob:

> ich habe folgendes System am Start und wüßte gerne, wie ich es am
> besten konfiguriere, damit alle beteiligten Clients (drei Gruppen) die
> jeweils korrespondierenden Zeitstempel erhalten und damit alle
> beteiligten möglichst effizient arbeiten können:
>
> (a) mehrere Clients populieren eine Tabelle der Datenbank mit Tupeln
> der Form (timestamptz, id1, id2, id3, data ...). Dabei bilden
> timestamptz, id1, id2, id3 den Primärschlüssel. (id1, id2, id3) ist
> ein referenzierter Fremdschlüssel. Jeder dieser Clients hatte bislang
> (bis zum "rumspielen" an der Konfiguration heute) die
> Umgebungsvariable PGTZ=3DCET und erhält Daten aus der Standardeingabe=
,
> die allesamt Winterzeit referenzieren. Jeder dieser Clients erhält
> seine Zeit als Argument z.B. -t '03.04.2006 12:00:00'. Den Wert
> dieses

D.h., die Stempel sind immer UTC+01, auch im Sommer? In diesem Fall
wäre "CET" die falsche Wahl, denn bei der Verwendung als
Session-Zeitzone bezeichnet "CET" die Zeitzone als Ort, und nicht als
Offset. PGTZ=3DCET verhält sich wahrscheinlich fast identisch zu
PGTZ=3DEurope/Berlin.

Damit würde '01.01.2006 12:00:00' zu '01.01.2006 12:00:00 UTC+01'
und '01.04.2006 12:00:00' zu '01.04.2006 12:00:00 UTC+02'
....und dahin ist die Bedeutungstreue.

> Arguments (string, libpqxx) verwendet der Client beim Einfügen in die
> Datenbank. Die Tabelle enthält mittlerweile Daten mit mehr als
> 100GB. Im Endstadium schätze ich das Volumen auf 200GB.
>
> (b) andere Clients starten zu einem beliebigen Zeitpunkt und setzen
> ihre Uhr auf localtime abgerundet auf die volle Minute weniger ein
> paar gewollten Verzögerungsminuten. Aufgabe dieser Clients ist es, di=
e
> jeweils ihrer Zeit entsprechenden neuesten Daten zu erfragen und
> korrespondierende Rechenergebnisse zurückzuschreiben. In der
> Sommerzeit (CEST) wollen sie die Daten mit ihrem Zeitstempel - 1h; in
> der Winterzeit - 0h. Auch diese Clients haben PGTZ=3DCET bzw. jetzt
> PGTZ=3D+01.

Wozu hier die Unterscheidung der DST? Ein Vergleich mit
"date_trunc('minute', now()) - verzögerung" sollte unabhängig von DST
und Session-Zeitzone die selben Tupel liefern. Vorausgesetzt, die
Stempel wurden in (a) auch konsistent eingepflegt.

> (c) die letzte Gruppe von Clients will nur die stets aktuellsten
> Rechenergebnisse der vorherigen Gruppe (b). Auch diese Clients
> verwenden die leicht modifizierte localtime(0) wie oben bei der
> Datenbankanfrage. Sie besitzen die Umgebungsvariable PGTZ=3D-0000. Die
> Tabelle, deren Daten sie abfragen, hat die Form (timestamptz,
> (Fremdschlüssel), data ...). Dieser Fremdschlüssel ist nicht identi=
sch
> mit (a). Die Tabelle enthält zwar nur jeweils die letzten 24- oder
> eventuell auch weniger Stunden, jedoch sind das auch in etwa
> 1240-Datensätze pro Minute.
>
> Die Systemzeit war bis gestern Europe/Berlin, seit heute
> UTC.

Was war die Motivation? Was ist mit Systemzeit gemeint? Die Zeitzone
des Betriebssystems? Letztere hat AFAIK lediglich Auswirkungen auf den
Defaultwert der Session-Zeitzone, welchen du ja überall zu
überschreiben scheinst.

> Phänomen:
>
> Es sei 03.04.2006 09:00:00 UTC. Ein (a)-Client hat Daten mit
> '03.04.2006 10:00:00 +01' und schreibt diese in die Datenbank. Eine
> spätere Anfrage mittels PGTZ=3DUTC psql liefert keine Daten mit
> '03.04.2006 09:00:00'. Die Daten erhalte ich nur mit '03.04.2006
> 08:00:00'.

Wenn meine Vermutung bei (a) stimmt, dann könnte man das so erklären:

Tatsächlicher Eingabestempel: '03.04.2006 09:00:00 UTC'

Client (a) bekommt '03.04.2006 10:00:00'

Mit PGTZ=3DCET landet dadurch das falsche '03.04.2006 8:00:00 UTC' auf
der Platte, was man dann mit PGTZ=3DUTC auch so zu sehen bekommt.

> Mittlerweile habe ich die (a)-Clients umgestellt auf
> PGTZ=3DUTC und erhalte nun die entsprechenden Daten mit '03.04.2006
> 10:00:00'.

Wenn die Stempel immer in UTC+01 vorliegen, schröbe ein Client (a) mit
PGTZ=3D'+01' die Korrekte Zeit in die Datanbank.

> Fragen:
>
> 1. Wie können die Daten aus (a) unter CET bzw. UTC +01 gespeichert
> werden?

Genaugenommen gar nicht. Die Timestamp-Typen speichern keine
Zeitzoneninformation. Im Falle von timestamptz wird nur
sichergestellt, daß auf der Platte immer UTC liegt. Die Konvertierung
erfolgt unter Beachtung der Session-Zeitzone, und eventuell
vorhandener Offsetangaben im String selbst.

> Muss ich Daten seit der Umstellung auf CEST mittels UPDATE
> aktualisieren?

Auf der Platte liegt nur UTC, ein Update einer timestamptz-Spalte
macht also nur Sinn, wenn man PostgreSQL einmal über die verwendete
Zeitzone getäuscht hat, und UTC deswegen nicht UTC ist.

> 2. Welche PGTZ-Einstellung benötigen die Clients aus (b), damit sie
> die entsprechenden Daten erhalten? Benötigen diese Clients eventuell
> oder unbedingt eine besondere Zeitstempelkonvertierung?

S.o., das sollte bei korrektem Import in (a) einklich tun.

> 3. Welche PGTZ-Einstellung ist für die (c)-Clients am sinnvollsten?

Ich lese in der Beschreibung zu (c) nichts, was eine spezielle
Zeitzone erforden würde.

> 4. Was bedeutet integer_datetimes =3D off aus den
> show-all-Einstellungen?

<http://www.postgresql.org/docs/8.0/static/datatype-datetime.html>

--8<---------------cut here---------------start------------->8---
*Note*

When `timestamp' values are stored as double precision
floating-point numbers (currently the default), the effective
limit of precision may be less than 6. `timestamp' values are
stored as seconds before or after midnight 2000-01-01. Microsecond
precision is achieved for dates within a few years of 2000-01-01,
but the precision degrades for dates further away. When
`timestamp' values are stored as eight-byte integers (a
compile-time option), microsecond precision is available over the
full range of values. However eight-byte integer timestamps have a
more limited range of dates than shown above: from 4713 BC up to
294276 AD. The same compile-time option also determines whether
`time' and `interval' values are stored as floating-point or
eight-byte integers. In the floating-point case, large `interval'
values degrade in precision as the size of the interval increases.
--8<---------------cut here---------------end--------------->8---

HTH
Andreas

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hallo Andreas,
hallo Liste,

Andreas Seltenreich <andreas+pg [at] gate450.dyndns.org> writes:

> Ich versuche mal, auf die noch offenen Fragen einzugehen...

erstmal vielen, vielen, herzlichen Dank für Deine Mühe und Deine gute=
n
Antworten! Dieses Problem hat mich sehr geplagt. Dank Dir ist es nun
behoben.

> Johannes Brügmann schrob:
>
>> (a) mehrere Clients populieren eine Tabelle der Datenbank mit Tupeln
>> der Form (timestamptz, id1, id2, id3, data ...). Dabei bilden
>> timestamptz, id1, id2, id3 den Primärschlüssel. (id1, id2, id3) is=
t
>> ein referenzierter Fremdschlüssel. Jeder dieser Clients hatte bislan=
g
>> (bis zum "rumspielen" an der Konfiguration heute) die
>> Umgebungsvariable PGTZ=3DCET und erhält Daten aus der Standardeingab=
e,
>> die allesamt Winterzeit referenzieren. Jeder dieser Clients erhält
>> seine Zeit als Argument z.B. -t '03.04.2006 12:00:00'. Den Wert
>> dieses
>
> D.h., die Stempel sind immer UTC+01, auch im Sommer?

Ja.

Ich habe aber die Möglichkeit, das Argument als -t '03.04.2006
12:00:00' oder -t '03.04.2006 12:00:00 +01' usw. anzugeben, sowie die
Umgebungsvariable PGTZ zu setzen.

> In diesem Fall wäre "CET" die falsche Wahl, denn bei der Verwendung
> als Session-Zeitzone bezeichnet "CET" die Zeitzone als Ort, und
> nicht als Offset. PGTZ=3DCET verhält sich wahrscheinlich fast
> identisch zu PGTZ=3DEurope/Berlin.
>
> Damit würde '01.01.2006 12:00:00' zu '01.01.2006 12:00:00 UTC+01'
> und '01.04.2006 12:00:00' zu '01.04.2006 12:00:00 UTC+02'
> ...und dahin ist die Bedeutungstreue.

Jetzt beginne ich zu verstehen. Sehr gut! Danke.

Eine Frage hätte ich dann aber doch noch:
Was passiert bei

- PGTZ=3DCET und '01.01.2006 12:00:00 +01' (Postgres speichert
'01.01.2006 10:00:00 UTC', oder?)

- PGTZ=3DCET und '01.04.2006 12:00:00 +01' (Postgres speichert
'01.04.2006 09:00:00 UTC', oder?)

mit anderen Worten:

Die Umgebungsvariable und die Ergänzung '... +01' sollten also immer
"mutually exclusive" verwendet werden, richtig?

Langer Rede kurzer Sinn: (a) PGTZ=3D+01 und keine Ergänzung, der Rest
UTC.

Eigentlich wäre eine solche Ergänzung mit einem oder mehreren guten
Beispielen in der Dokumentation eine feine Sache (pro bono, contra
malum ;-).

>> Arguments (string, libpqxx) verwendet der Client beim Einfügen in di=
e
>> Datenbank. Die Tabelle enthält mittlerweile Daten mit mehr als
>> 100GB. Im Endstadium schätze ich das Volumen auf 200GB.
>>
>> (b) andere Clients starten zu einem beliebigen Zeitpunkt und setzen
>> ihre Uhr auf localtime abgerundet auf die volle Minute weniger ein
>> paar gewollten Verzögerungsminuten. Aufgabe dieser Clients ist es, d=
ie
>> jeweils ihrer Zeit entsprechenden neuesten Daten zu erfragen und
>> korrespondierende Rechenergebnisse zurückzuschreiben. In der
>> Sommerzeit (CEST) wollen sie die Daten mit ihrem Zeitstempel - 1h; in
>> der Winterzeit - 0h. Auch diese Clients haben PGTZ=3DCET bzw. jetzt
>> PGTZ=3D+01.
>
> Wozu hier die Unterscheidung der DST?

Stimmt. Ist Quatsch. Kann ich aber auch jetzt erst feststellen,
nachdem ich das Problem verstanden habe (typisches
Verständnis-Bootstrapping-Problem).

>> Die Systemzeit war bis gestern Europe/Berlin, seit heute
>> UTC.
>
> Was war die Motivation?

Motivation war "Rumspielen an der Konfiguration mangels hinreichendem
Verständnis". ;-)

> Was ist mit Systemzeit gemeint? Die Zeitzone des Betriebssystems?

Ja. Zeitzone und aktuelle Zeit des Betriebssystems.

> Letztere hat AFAIK lediglich Auswirkungen auf den Defaultwert der
> Session-Zeitzone, welchen du ja überall zu überschreiben scheinst.

Ok.

> Wenn die Stempel immer in UTC+01 vorliegen, schröbe ein Client (a) mi=
t
> PGTZ=3D'+01' die Korrekte Zeit in die Datanbank.

Danke, genau das brauche ich!

>> Muss ich Daten seit der Umstellung auf CEST mittels UPDATE
>> aktualisieren?
>
> Auf der Platte liegt nur UTC, ein Update einer timestamptz-Spalte
> macht also nur Sinn, wenn man PostgreSQL einmal über die verwendete
> Zeitzone getäuscht hat, und UTC deswegen nicht UTC ist.

Ich habe PostgreSQL seit Beginn der Sommerzeit über die wahre Zeit
getäuscht. Einen ersten UPDATE-Versuch wies PostgreSQL aber zurück mi=
t
der Begründung, dass Fremdschlüssel kollidieren würden. Ich denke, =
das
liegt wohl an der falschen Reihenfolge. Führt PostgreSQL eigentlich
eine UPDATE-Anfrage auf mehreren Tupeln parallel aus?

>> 2. Welche PGTZ-Einstellung benötigen die Clients aus (b), damit sie
>> die entsprechenden Daten erhalten? Benötigen diese Clients eventuell
>> oder unbedingt eine besondere Zeitstempelkonvertierung?
>
> S.o., das sollte bei korrektem Import in (a) einklich tun.

Ein erster Test bestätigt Deine Theorie :).

Vielen herzlichen Dank nochmals, Andreas.

Freundliche Grüße,
Johannes Brügmann
--


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Hallo Andreas S. und Andreas K.,
hallo Liste,

vorab erstmal nochmals Danke für den hervorragenden Tipp, die
Aggregatfunktion nachzubauen. Ich habe das ausprobiert und werde das
gleich mal umsetzen.

"A. Kretschmer" <andreas.kretschmer [at] schollglas.com> writes:

> am 03.04.2006, um 18:54:19 +0200 mailte Andreas Seltenreich folgendes:
>> >>> > max(timestamptz)
>> >>>
>> >>> habe ich probiert; Antwortzeit > 1 min; viel zu langsam. Ich brä=
uchte
>> >>> irgendetwas < 10s, eher weniger. Noch andere Ideen?
>> [...]
>> >> Welche Version hast Du?
>> >
>> > 8.0.4
>>
>> Die Optimierung für min()/max()-Aggregatfunktionen gibt es erst ab
>> 8.1. Man kann sie mit 8.0 jedoch leicht nachbauen:

Das Update auf 8.1 werde ich mir auf jeden Fall antun. In der
Dokumentation stand nichts davon, dass ich irgendwelche
Schwierigkeiten zu befürchten haben, falls ich von 8.0 auf 8.1
aktualisiere.

Freundliche Grüße,
Johannes Brügmann
--


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Johannes Brügmann schrob:

> Eine Frage hätte ich dann aber doch noch:
> Was passiert bei
>
> - PGTZ=3DCET und '01.01.2006 12:00:00 +01' (Postgres speichert
> '01.01.2006 10:00:00 UTC', oder?)
>
> - PGTZ=3DCET und '01.04.2006 12:00:00 +01' (Postgres speichert
> '01.04.2006 09:00:00 UTC', oder?)

Nein.

<http://www.postgresql.org/docs/8.0/static/datatype-datetime.html#AEN4516=
>

--8<---------------cut here---------------start------------->8---
An input value that has an explicit time zone specified is converted
to UTC using the appropriate offset for that time zone. If no time
zone is stated in the input string, then it is assumed to be in the
time zone indicated by the system's timezone parameter, and is
converted to UTC using the offset for the `timezone' zone.
--8<---------------cut here---------------end--------------->8---

> Eigentlich wäre eine solche Ergänzung mit einem oder mehreren guten
> Beispielen in der Dokumentation eine feine Sache (pro bono, contra
> malum ;-).

Hmm, auch zum ursprünglichen Problem - Zeitzone als Ort vs. Offset -
findet sich ein Hinweis im Handbuch:

<http://www.postgresql.org/docs/8.0/static/datetime-keywords.html#DATETIM=
E-TIMEZONE-SET-TABLE>

--8<---------------cut here---------------start------------->8---
Note that these names are conceptually as well as practically
different from the names shown in Table B-4: most of these names imply
a local daylight-savings time rule, whereas the former names each
represent just a fixed offset from UTC.
--8<---------------cut here---------------end--------------->8---

Aber ich muß zugeben, daß letzteres im Anhang etwas versteckt ist.

>> Auf der Platte liegt nur UTC, ein Update einer timestamptz-Spalte
>> macht also nur Sinn, wenn man PostgreSQL einmal über die verwendete
>> Zeitzone getäuscht hat, und UTC deswegen nicht UTC ist.
>
> Ich habe PostgreSQL seit Beginn der Sommerzeit über die wahre Zeit
> getäuscht. Einen ersten UPDATE-Versuch wies PostgreSQL aber zurück =
mit
> der Begründung, dass Fremdschlüssel kollidieren würden. Ich denke=
, das
> liegt wohl an der falschen Reihenfolge. Führt PostgreSQL eigentlich
> eine UPDATE-Anfrage auf mehreren Tupeln parallel aus?

Offensichtlich nicht :-/. Man scheint die Reihenfolge des UPDATEs aber
mit CLUSTER hinbiegen zu können. Aber irnkwie sieht mir das mehr nach
einem ekligen Hack als nach einem Feature aus...

--8<---------------cut here---------------start------------->8---
scratch=3D# UPDATE bar set a =3D a + 1 ;
ERROR: duplicate key violates unique constraint "bar_pkey"
scratch=3D# create index temp_idx on bar((-a));
CREATE INDEX
scratch=3D# cluster temp_idx on bar;
CLUSTER
scratch=3D# UPDATE bar set a =3D a + 1 ;
UPDATE 3
scratch=3D#
--8<---------------cut here---------------end--------------->8---

....damit wäre dann die Kollison ausgeschlossen, aber ich fürchte,
damit das Update klappt, müssen die Fremdschlüssel entweder ON UPDATE
CASCADE sein, oder man muss sie temporär abschalten (ALTER TABLE
.... DISABLE TRIGGER ALL sollte das leisten).

Auf jeden Fall beim Experimentieren das ROLLBACK griffbereit halten
:-).

Gruß + viel Erfolg beim Update
Andreas

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster