Re: [BUGS] Table constraint ordering disrupted by pg_dump
At 23:55 2/04/01 -0400, Tom Lane wrote: >Philip Warner <[EMAIL PROTECTED]> writes: >> While it't not a bug, it would be nice if pg_dump reproduced definitions as >> faithfully as possible. To that end, would it be worth selecting the >> constraints in OID order (using oid from pg_relcheck)? > >If it's just another clause in a query, you might as well. I wouldn't >take any risks for it though... Just an ORDER BY. This has been done in CVS, but since I could not reproduce the original problem, I can't check it, so let me know how it works. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] PostgreSQL 7.0.2 Date Miscalculation
On Tue, Apr 03, 2001 at 12:59:31AM -0400, Tom Lane wrote: > However, the horology diffs are not, and I can't reproduce them here. > Did anyone else see that? me too (the problem started in these days) ciao, andrea ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
RE: [BUGS] Loosing files after backend crash
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, April 03, 2001 3:35 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [BUGS] Loosing files after backend crash > > > [EMAIL PROTECTED] writes: > > During execution of some sql code backend crashed (the > reason for it's > > crashing is not the actual topic of this report). > > And tried to delete the sequence, which i created in the > previous session. > > That's what pgsql told me: > > ---8<--- > > Netflow_Test=# drop SEQUENCE seq_i___data_buffer ; > > ERROR: cannot open seq_i___data_buffer: No such file or directory > > Was that a system-wide crash, or just a backend crash? It was just a backend crash. > It seems to me that your kernel must have lost that file for you; Postgres > wouldn't have gone out and deleted it. How could kernel _loose_ the file, which was already created? I would agree with the possibility of loosing some data in the file during the backend crash. But loosing the whole file ... The sequence was previously created in an already committed transaction. Correct me if I am wrong, but after I said "commit" the file would be on my disk. If so, how can backend's crash "erase" the file from disk? BTW, I have some more facts to report. Just about half an hour ago the backend crashed one more time in the same situation. Now it has not only lost the file for my sequence (the same very sequence), but it also has TWO tuples in the pg_class table with the name of my sequence. All other attribites of the tuples also hold identical values. And PGSQL doesn't consider it to be a sequence. When I say 'DROP SEQUENCE ...' it says that it is not a sequence and advises me to use 'DROP TABLE'. When I say 'DROP TABLE ...' it says something like 'cannot drop system table pg_temp.X.X' (I changed the actial digits into 'X'-es. Not sure about exact words.) The "relkind" attribute of the corresponding two tuples in the pg_class table hold letter 'S' (The uppercase 's'). Regards, Konstantin Solodovnikov. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] contrib/pg_resetxlog fails to compile under Digital Unix
Alessio Bragadini ([EMAIL PROTECTED]) reports a bug with a severity of 3 The lower the number the more severe it is. Short Description contrib/pg_resetxlog fails to compile under Digital Unix Long Description Under Digital Unix (aka Compaq Tru64) 4.0f with cc, contrib/pg_resetxlog fails to compile because of the lack of snprintf (it's in libdb). Adding a -ldb to the Makefile fixes the problem. Sample Code cc -std -O4 -Olimit 2000 pg_resetxlog.o pg_crc.o -L/usr/local/lib -rpath /usr/local/pgsql/lib -lz -lresolv -lPW -lm -lbsd -lreadline -ltermcap -o pg_resetxlogld: Unresolved: snprintf No file was uploaded with this report ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] PostgreSQL 7.0.2 Date Miscalculation
Philip Warner <[EMAIL PROTECTED]> writes: > At 00:59 3/04/01 -0400, Tom Lane wrote: >> However, the horology diffs are not, and I can't reproduce them here. >> Did anyone else see that? > I've just started seeing both... What is the date of the nearest daylight-savings transition in your timezone? Wait a minute ... considering that the regress tests run in PST8PDT, your local timezone shouldn't make a difference. Maybe a platform- specific issue? What platform (esp. which C library) do you use? FWIW, as of this morning I'm back to no failure on timestamp test (as expected), and still no horology failure either. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Loosing files after backend crash
=?KOI8-R?Q?=F3=CF=CC=CF=C4=CF=D7=CE=C9=CB=CF=D7_=EB=CF=CE=D3=D4=C1?= =?KOI8-R?Q?=CE=D4=C9=CE?= <[EMAIL PROTECTED]> writes: >> It seems to me that your kernel must have lost that file for you; Postgres >> wouldn't have gone out and deleted it. > How could kernel _loose_ the file, which was already created? > I would agree with the possibility of loosing some data in the file during > the backend crash. > But loosing the whole file ... > The sequence was previously created in an already committed transaction. > Correct me if I am wrong, but after I said "commit" the file would be on my > disk. Sooner than that: a create call is issued to the kernel as soon as you say CREATE SEQUENCE. > If so, how can backend's crash "erase" the file from disk? I'd like to know that too. > BTW, I have some more facts to report. > Just about half an hour ago the backend crashed one more time in the same > situation. Backtrace from core file, please? > Now it has not only lost the file for my sequence (the same very sequence), > but it also has TWO tuples in the pg_class table with the name of my > sequence. > All other attribites of the tuples also hold identical values. > And PGSQL doesn't consider it to be a sequence. Could we see the exact contents of pg_class for the sequence? (both tuples including OIDs) > When I say 'DROP SEQUENCE ...' it says that it is not a sequence and advises > me to use 'DROP TABLE'. > When I say 'DROP TABLE ...' it says something like 'cannot drop system table > pg_temp.X.X' (I changed the actial digits into 'X'-es. Not sure about > exact words.) Hm, that sounds like some sort of conflict with a temp table. Is it possible that you have been using a temp table name that matches the sequence name? Are there any pg_class entries whose names begin with pg_temp, and if so could we see details on those too? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[BUGS] to_char miscalculation on April Fool's Day, the start of daylight savings
straka ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description to_char miscalculation on April Fool's Day, the start of daylight savings Long Description Nip this one in the bud because knowledge of it will hinder adoption of PostgreSQL in commercial applications. Probably related to bug 249 - Daylight savings happened in the US on April 1. It's April 3 and I'm developing an update routine to maintain expired records, some of which expired on April 1. When these records didn't get updated, I investigated and identified the alleged bug (which is potentially devastating financially based on date intensive calculations in financial applications). The quickest demonstration is as follows: select to_char(now(), '-MM-DD'); Based on the date you see, subtract an integer value from now() so the query result shows 2-Apr. Assuming it's April 3, enter: select to_char(now() -1, '-MM-DD'); It comes back fine with 2001-04-02. Now decrement by x + 1 to see the bug. Assuming it's April 3, enter: select to_char(now() - 2, '-MM-DD'); It comes back incorrectly with 2001-03-31; You get a correct result of 2000-03-01 if you enter: select to_char(now() -33, '-MM-DD'); My system is Eastern Daylight Time. I'm running on Red Hat Linux 6.2 - select version() returns the following: PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled Sample Code thedb=# select to_char(now(), '-MM-DD'); to_char 2001-04-03 (1 row) thedb=# select to_char(now() -1, '-MM-DD'); to_char 2001-04-02 (1 row) thedb=# select to_char(now() -2, '-MM-DD'); to_char 2001-03-31 (1 row) thedb=# select to_char(now() -3, '-MM-DD'); to_char 2001-03-31 (1 row) thedb=# select to_char(now() -33, '-MM-DD'); to_char 2001-03-01 (1 row) No file was uploaded with this report ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] PostgreSQL 7.0.2 Date Miscalculation
Here is a bit more information on this date type problem.
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 7.0.2 on i386-unknown-freebsdelf3.4, compiled by gcc 2.7.2.3]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: darcy
darcy=> select date_part('dow','april 1, 2001'::date);
date_part
-
6
(1 row)
darcy=> select date_part('dow','april 2, 2001'::date);
date_part
-
1
(1 row)
darcy=>\q
At 10:17 AM 4/3/01 -0400, Tom Lane wrote:
>Philip Warner <[EMAIL PROTECTED]> writes:
>> At 00:59 3/04/01 -0400, Tom Lane wrote:
>>> However, the horology diffs are not, and I can't reproduce them here.
>>> Did anyone else see that?
>
>> I've just started seeing both...
>
>What is the date of the nearest daylight-savings transition in your
>timezone?
>
>Wait a minute ... considering that the regress tests run in PST8PDT,
>your local timezone shouldn't make a difference. Maybe a platform-
>specific issue? What platform (esp. which C library) do you use?
>
>FWIW, as of this morning I'm back to no failure on timestamp test
>(as expected), and still no horology failure either.
>
> regards, tom lane
>
>---(end of broadcast)---
>TIP 2: you can get off all lists at once with the unregister command
>(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
>
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Table constraint ordering disrupted by pg_dump
Patricia Holben <[EMAIL PROTECTED]> writes:
> I have loaded this new version and re-tested but don't see it fixed.
It looks fixed to me. Original SQL in constraints.sql:
CREATE TABLE INSERT_TBL (x INT DEFAULT nextval('insert_seq'),
y TEXT DEFAULT '-NULL-',
z INT DEFAULT -1 * currval('insert_seq'),
CONSTRAINT INSERT_CON CHECK (x >= 3 AND y <> 'check failed' AND x < 8),
CHECK (x + z = 0));
As dumped by current-CVS pg_dump:
CREATE TABLE "insert_tbl" (
"x" integer DEFAULT nextval('insert_seq'::text),
"y" text DEFAULT '-NULL-',
"z" integer DEFAULT (-1 * currval('insert_seq'::text)),
CONSTRAINT "insert_con" CHECK x >= 3) AND (y <> 'check failed'::text)) AND
(x < 8))),
CHECK (((x + z) = 0))
);
> I do have a separate routine which I am running - it isn't complete which
> is why I haven't shared it yet - which proves that when the dump is
> reloaded the constraints are not applied in the original order.
I notice that the backend tends to apply the constraints in the reverse
order of declaration, but this should be consistent between the original
database and the dumped/reloaded one.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[BUGS] Re: PostgreSQL 7.0.2 Date Miscalculation
> > The RELTIME function is miscalculating dates.
> > (all my graphs were wrong today!)
Just an aside: INTERVAL is the preferred type for, uh, intervals.
RELTIME is used internally for historical reasons. In particular,
INTERVAL maintains the distinction between qualitative units such as
months and years, while RELTIME assumes a 30 day month and 365 day year
*always*.
But for your example that does not make a difference...
> > stamp
> >
> > 2001-03-25 02:53:52-05
> > When the date is:
> > Mon Apr 2 19:45:40 EDT 2001
> > And the result SHOULD be:
> >
> > 2001-03-26 02:53:52-05
Should be fixed in current sources (and the upcoming 7.1 release).
> > I also have NO idea what this means:
> > SELECT DATE(CURRENT_DATE - ('1 WEEK'::RELTIME));
> >
> > 0345-05-14
Whoops. Still a problem even in current sources, probably related to
changes to help with time zone manipulation. There is an internal units
mismatch between DATE and RELTIME. Use INTERVAL instead.
> If I changed the system clock back before the DST change... it works fine.
> --- ./results/horology.out Mon Apr 2 17:06:59 2001
> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08";
>03:31:00-08
> -
> ! 03:31:00-07
Hmm. This is just a badly designed regression test (I can say that,
since it is probably mine ;)
I was trying to exercise TIME WITH TIME ZONE with the *implicit* time
zone for today. That really won't work in a testable way, since the
result varies during the year :(
This illustrates a fundamental problem with the SQL9x TIME WITH TIME
ZONE type, which carries no date info for context. And they have no
"date with time zone", which except for a few hours a year might be more
helpful. imho TIMESTAMP is to be preferred in most cases.
- Thomas
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Re: PostgreSQL 7.0.2 Date Miscalculation
Thomas Lockhart <[EMAIL PROTECTED]> writes: >> --- ./results/horology.out Mon Apr 2 17:06:59 2001 >> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08"; >> 03:31:00-08 >> - >> ! 03:31:00-07 > Hmm. This is just a badly designed regression test (I can say that, > since it is probably mine ;) > I was trying to exercise TIME WITH TIME ZONE with the *implicit* time > zone for today. That really won't work in a testable way, since the > result varies during the year :( What I'm curious about is why I'm not seeing a failure on HPUX. If your explanation is right then this test should fail everywhere during daylight savings season. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Re: PostgreSQL 7.0.2 Date Miscalculation
Tom Lane wrote: > > Thomas Lockhart <[EMAIL PROTECTED]> writes: > >> --- ./results/horology.out Mon Apr 2 17:06:59 2001 > >> SELECT time with time zone '01:30' + interval '02:01' AS "03:31:00-08"; > >> 03:31:00-08 > >> - > >> ! 03:31:00-07 > > > Hmm. This is just a badly designed regression test (I can say that, > > since it is probably mine ;) > > > I was trying to exercise TIME WITH TIME ZONE with the *implicit* time > > zone for today. That really won't work in a testable way, since the > > result varies during the year :( > What I'm curious about is why I'm not seeing a failure on HPUX. If your > explanation is right then this test should fail everywhere during > daylight savings season. Well, we won't hold up HPUX as a model for "standard behavior", eh? But I'm not sure why you don't see the behavior. afaik the calculations involved should be something like (haven't looked it up, but...): 1) interpret TIME WITH TIME ZONE '01:30' as the time with the time zone appropriate for that hour today. Convert to internal representation as a time field with an explicit numeric time zone value. 2) interpret INTERVAL '02:01' as an interval. No month/year fields, and no time zone involved. 3) Add the interval to the time. Both are in units of seconds internally. 4) Store the time field modulo 86400, pushing it back into a 24 hour range. Store the time zone field from step (1) into the result. 5) Print result, using only the internal time zone offset. - Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] contrib/pg_resetxlog fails to compile under Digital Unix
[EMAIL PROTECTED] writes: > contrib/pg_resetxlog fails to compile under Digital Unix Fixed. Thanks for the report. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Table constraint ordering disrupted by pg_dump
Patricia Holben <[EMAIL PROTECTED]> writes: > improvement but as usual there is a related error. In the constraints=20 > test, there is a table created "INSERT_CHILD" which has a constraint and= > then inherits from "INSERT_TBL" the constraint "insert_con" and the=20 > unnamed check. If you look at the expected output from this test, when = > the inherited unnamed check fails, the error is $1. After doing the=20 > dump/reload, this error is called $3. Hm. There are a couple of things going on here. The one that may be worth fixing is that pg_dump isn't reliably recognizing nameless inherited constraints as duplicates. It looks for matches on both rcname and rcsrc, but the rcname may get reassigned (particularly if there is multiple inheritance). This will lead to multiple instances of the same constraint, which is inefficient, and becomes more and more so with repeated dump/reload cycles. Rather than using "c.rcname = pg_relcheck.rcname" as part of the match condition, consider (c.rcname = pg_relcheck.rcname OR (c.rcname[0] = '$' AND pg_relcheck.rcname[0] = '$')) so that any two nameless constraints will be considered duplicate if their rcsrcs match. The other thing is that the backend won't necessarily assign a nameless constraint the same $-index in different tables, so even if pg_dump is changed there's not a guarantee that you won't get different error messages in the above example. I don't consider that a bug, however. If you're depending on the name of a constraint then you should name it. regards, tom lane PS: Philip, it seems to me that lines 2071-2121 in pg_dump.c are largely a waste of time, since the subsequent query to fetch the constraints will do all the same work over again. Just have to relax the check at line 2157 to allow ntups2 <= ncheck, and update ncheck after that. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] Table constraint ordering disrupted by pg_dump
At 17:51 3/04/01 -0400, Tom Lane wrote: > >Rather than using "c.rcname = pg_relcheck.rcname" as part of the match >condition, consider > (c.rcname = pg_relcheck.rcname OR >(c.rcname[0] = '$' AND pg_relcheck.rcname[0] = '$')) >so that any two nameless constraints will be considered duplicate if >their rcsrcs match. Looks reasonable, but users can define constraints with names starting with '$', so there is a small chance we will discard a redundant constraint, which is probably OK. BTW, it looks like 'ALTER TABLE ADD CONSTRAINT' on a parent does not affect the already created children. Is this intended behaviour? It seems like a but and it certainly will cause an issue for pg_dump. >PS: Philip, it seems to me that lines 2071-2121 in pg_dump.c are largely >a waste of time, since the subsequent query to fetch the constraints >will do all the same work over again. Just have to relax the check at >line 2157 to allow ntups2 <= ncheck, and update ncheck after that. I was planning to clean up that area when 7.1 was out the door since I assumed that there was an obscure reason for it's existence. Looking at the code more closely, I think it is part of the sanity check pg_dump tries to do. The first piece of SQL finds inherited constraints, and the second one finds non-inherited constraints, but the way the SQL is structured, I can't really see how they would fail to come to the correct total, so it's value as a sanity check seems minimal. Do you agree, or should I leave it in? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [BUGS] Table constraint ordering disrupted by pg_dump
Philip Warner <[EMAIL PROTECTED]> writes: > At 17:51 3/04/01 -0400, Tom Lane wrote: >> Rather than using "c.rcname = pg_relcheck.rcname" as part of the match >> condition, consider >> (c.rcname = pg_relcheck.rcname OR >> (c.rcname[0] = '$' AND pg_relcheck.rcname[0] = '$')) >> so that any two nameless constraints will be considered duplicate if >> their rcsrcs match. > Looks reasonable, but users can define constraints with names starting with > '$', so there is a small chance we will discard a redundant constraint, > which is probably OK. Actually, I was seriously considering the fascist alternative: if the rcsrc fields match then discard the child's constraint, never mind what its name is. Functionally the result is the same... > BTW, it looks like 'ALTER TABLE ADD CONSTRAINT' on a parent does not affect > the already created children. Is this intended behaviour? It seems like a > but and it certainly will cause an issue for pg_dump. This seems a bug, but I do not think we should address it for 7.1. TODO list material. >> PS: Philip, it seems to me that lines 2071-2121 in pg_dump.c are largely >> a waste of time, since the subsequent query to fetch the constraints >> will do all the same work over again. Just have to relax the check at >> line 2157 to allow ntups2 <= ncheck, and update ncheck after that. > I was planning to clean up that area when 7.1 was out the door since I > assumed that there was an obscure reason for it's existence. > Looking at the code more closely, I think it is part of the sanity check > pg_dump tries to do. The first piece of SQL finds inherited constraints, > and the second one finds non-inherited constraints, but the way the SQL is > structured, I can't really see how they would fail to come to the correct > total, so it's value as a sanity check seems minimal. Do you agree, or > should I leave it in? My thought is that it's useless as a sanity check (since there's no orthogonal-condition test there), and it does pose a maintenance problem. For example, to adjust the name-matching condition being discussed above, you'd have to remember to change both queries. Change only one and you're in trouble. Given that, the downside is bigger than the upside. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
