[BUGS] BUG #1993: Adding/subtracting negative time intervals changes time zone of result
The following bug has been logged online: Bug reference: 1993 Logged by: Nicholas Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3,8.0.4,8.1 Operating system: Gentoo Linux Description:Adding/subtracting negative time intervals changes time zone of result Details: spatula ~ # psql -U postgres Welcome to psql 8.1beta1, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit postgres=# SELECT VERSION(); version -- PostgreSQL 8.1beta1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.6 (Gentoo 3.3.6, ssp-3.3.6-1.0, pie-8.7.8) (1 row) postgres=# SELECT NOW()-interval '1 week'; ?column? --- 2005-10-17 08:52:37.355219+10 (1 row) postgres=# SELECT NOW()-interval '-1 week'; ?column? --- 2005-10-31 08:52:39.021583+11 (1 row) postgres=# ---(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
[BUGS] BUG #1994: Ignore the last bug report; this is a confusing time zone feature, not a bug
The following bug has been logged online: Bug reference: 1994 Logged by: Nicholas Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.3,8.0.4,8.1 Operating system: Gentoo Linux Description:Ignore the last bug report; this is a confusing time zone feature, not a bug Details: I thought Postgres didn't support automatically dealing with daylight savings; I guess it does, hence the change in time zone when crossing the DST boundary. ---(end of broadcast)--- TIP 1: 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
[BUGS] BUG #2218: Variables selected in VIEWs under different names break queries using those views
The following bug has been logged online: Bug reference: 2218 Logged by: Nicholas Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.2 Operating system: Gentoo Linux x86 Description:Variables selected in VIEWs under different names break queries using those views Details: DROP VIEW meeting_versions; CREATE VIEW meeting_versions AS SELECT mi.id AS iid, m.id, m.default_start, m.default_duration, m.default_location, m.team, m.recurrence, m.discontinued, m.purpose, m.lead, m.queue, m.meetingtype, m.monthly_what, m.monthly_ordinal, m."owner", m.version, m.original_id, m.test_email_mode FROM meeting_instances mi JOIN meetings m ON m.id = (( SELECT latestitz(ROW(m.id, m.version)) AS id FROM meetings m WHERE m.original_id = mi.meetingid AND m.version < mi."start")); SELECT cgm.id, g.name, mi.invitees FROM meeting_instances as mi JOIN meeting_versions as m ON m.iid = '33' JOIN groups as g ON g.id = m.team LEFT JOIN cachedgroupmembers as cgm ON cgm.groupid = m.team and not cgm.disabled and cgm.memberid = '55' WHERE mi.id = '33'; ERROR: variable not found in subplan target list DROP VIEW meeting_versions; CREATE VIEW meeting_versions AS SELECT 33 AS iid, m.id, m.default_start, m.default_duration, m.default_location, m.team, m.recurrence, m.discontinued, m.purpose, m.lead, m.queue, m.meetingtype, m.monthly_what, m.monthly_ordinal, m."owner", m.version, m.original_id, m.test_email_mode FROM meeting_instances mi JOIN meetings m ON m.id = (( SELECT latestitz(ROW(m.id, m.version)) AS id FROM meetings m WHERE m.original_id = mi.meetingid AND m.version < mi."start")); SELECT cgm.id, g.name, mi.invitees FROM meeting_instances as mi JOIN meeting_versions as m ON m.iid = '33' JOIN groups as g ON g.id = m.team LEFT JOIN cachedgroupmembers as cgm ON cgm.groupid = m.team and not cgm.disabled and cgm.memberid = '55' WHERE mi.id = '33'; id | name | invitees +--+-- ... (31 rows) Note that the significant difference between the two views is that the first one selects "mi.id as iid" and that the view's join joins upon mi.id and further that the LEFT JOIN's condition depends upon m.iid. If I stop renaming mi.id to iid (as in the second example), OR if I change the LEFT JOIN in the query to a JOIN, OR if I replace the view with a table, OR if I remove the cgm.groupid = m.team condition from the LEFT JOIN, OR if I replace my custom aggregate latestitz() with MAX(), the query succeeds. I have no reason to suspect my custom aggregate is causing this error - it's relatively simple and I have been using it heavily for months with no hint of trouble. However, something about it being a custom aggregate does seem to trigger this off. Maybe you can think of something I might be doing in this function which can cause the error, Unfortunately, I tried to come up with a test case which exposes this bug but failed. Sorry. I also tried to come up with a minimal set of my data which exposes it and also failed. I'm not sure what, specifically, about my schema/data seems to expose this. However, it seems like everything which adds complexity to this query is necessary for it to occur :( If you can tell me what to do, I can probably build a debug version of postgres and run GDB on it and get you some state information, or I can enable whatever debug features might help shed some light on it. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2872: SELECT from view with OR fast in 8.1, slow in 8.2
The following bug has been logged online:
Bug reference: 2872
Logged by: Nicholas Vinen
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.0
Operating system: Linux (AMD64)
Description:SELECT from view with OR fast in 8.1, slow in 8.2
Details:
Sorry that I can't provide data, there's some private information in there,
but hopefully this will be easy enough to reproduce.
I have these tables and this view:
View "public.users_groups"
Column | Type | Modifiers
+-+---
group_id | integer |
group_name | text|
group_emailaddress | text|
group_disabled | boolean |
user_id| integer |
user_name | text|
user_display_name | text|
user_emailaddress | text|
user_disabled | boolean |
View definition:
SELECT g.id AS group_id, g.name AS group_name, g.emailaddress AS
group_emailaddress, pg.disabled AS group_disabled, u.id AS user_id,
u.realname AS user_name, u.displayname AS user_display_name, u.emailaddress
AS user_emailaddress, pu.disabled AS user_disabled
FROM groups g
JOIN principals pg ON pg.objectid = g.id
JOIN cachedgroupmembers cgm ON cgm.groupid = g.id AND NOT cgm.disabled
JOIN users u ON u.id = cgm.memberid
JOIN principals pu ON pu.objectid = u.id;
Table "helpdesk_mirror.users"
Column | Type |
Modifiers
---+--+-
---
id| integer | not null default
nextval('users_id_seq'::regclass)
name | text | not null default
''::text
password | text |
comments | bytea|
signature | bytea|
emailaddress | text |
freeformcontactinfo | text |
organization | text |
realname | text |
nickname | text |
lang | text |
emailencoding | text |
webencoding | text |
externalcontactinfoid | text |
contactinfosystem | text |
externalauthid| text |
authsystem| text |
gecos | text |
homephone | text |
workphone | text |
mobilephone | text |
pagerphone| text |
address1 | text |
address2 | text |
city | text |
state | text |
zip | text |
country | text |
timezone | text |
pgpkey| text |
creator | integer | not null default 0
created | timestamp with time zone |
lastupdatedby | integer | not null default 0
lastupdated | timestamp with time zone |
focus | text |
focuscount| integer |
focusproblemcount | integer |
focusprojectcount | integer |
focusrequestcount | integer |
focussuggestioncount | integer |
focusfeedbackcount| integer |
portraitgeometry | text |
focusuntypedcount | integer |
lastloginip | text |
lastloginhostname | text |
defaultqueue | integer |
interface | text |
default_ownerassignee | text |
flags | text |
guid | bytea|
displayname | text |
title | text |
department| text |
emailalias| text |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_name_key" UNIQUE, btree (name)
"users_emailaddress_idx" btree (emailaddress)
Triggers:
users_trig_final AFTER INSERT OR DELETE OR UPDATE ON users FOR EACH ROW
EXECUTE PROCEDURE mirror_config.log_changes
[BUGS] Query producing the wrong results?
ebatcher=> select version(); version - PostgreSQL 7.2.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7) (1 row) Just create a table with any int column and put in a bit of data: ebatcher=> create table test (id int); CREATE ebatcher=> insert into test values ( 0 ); INSERT 43522 1 ebatcher=> insert into test values ( 1 ); INSERT 43523 1 ebatcher=> insert into test values ( 2 ); INSERT 43524 1 ebatcher=> insert into test values ( 3 ); INSERT 43525 1 ebatcher=> insert into test values ( 4 ); INSERT 43526 1 ebatcher=> select * from test; id 0 1 2 3 4 (5 rows) Ok so far so good, now when you run this query you get this result: ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id 0 3 4 (3 rows) what I would expect is to get a single row returned not 3 rows. ebatcher=> select * from test where id = ((select min(id) from test)); id 0 (1 row) as expected the min is 0 ebatcher=> select round(random() * 4); round --- 1 (1 row) ebatcher=> select round(random() * 4); round --- 3 (1 row) as expected this always returns a random number between 0 and 4 put these together and I would expect to get a random single row not multiple rows. 5 more runs of the query yield these results: ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id 0 3 (2 rows) ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id 1 3 (2 rows) ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id 2 4 (2 rows) ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id (0 rows) ebatcher=> select * from test where id = ((select min(id) from test) + round(random() * 4)); id 1 (1 row) Just tried something further and: ebatcher=> select * from test where id = (0 + round(random() * 4)); id 0 (1 row) ebatcher=> select * from test where id = (0 + round(random() * 4)); id 3 4 (2 rows) and even: ebatcher=> select * from test where id = round(random() * 4); id 0 2 (2 rows) Again I would expect to get just a single row. Is this a bug? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[BUGS] BUG #1649: Date calculation year off-by-one error
The following bug has been logged online:
Bug reference: 1649
Logged by: Nicholas Vinen
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.1
Operating system: Gentoo Linux
Description:Date calculation year off-by-one error
Details:
This is definitely not right:
rt3=# SELECT date_trunc('week', timestamp with time zone '2005-01-02
00:00:00-7');
date_trunc
2006-01-02 00:00:00-08
(1 row)
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
[BUGS] BUG #1650: Upgraded postgres, problem went away
The following bug has been logged online:
Bug reference: 1650
Logged by: Nicholas Vinen
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.2
Operating system: Gentoo Linux
Description:Upgraded postgres, problem went away
Details:
8.0.2 is not marked as "stable" in gentoo but after I had the previous date
problem I installed it anyway. Now it does this:
rt3=# SELECT date_trunc('week', timestamp with time zone '2005-01-02
00:00:00-7');
date_trunc
2004-12-27 00:00:00-08
(1 row)
That seems more correct. Thank you for fixing. You may close my previous bug
report(s).
Thank you :) Whoever fixed that saved me having to do it myself.
Nicholas
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[BUGS] BUG #1648: ISO Year ignores time zone?
The following bug has been logged online: Bug reference: 1648 Logged by: Nicholas Vinen Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0.1 Operating system: Gentoo Linux Description:ISO Year ignores time zone? Details: rt3=# SELECT to_char(timestamp with time zone '2003-12-31 14:44:44-08', 'Mon IYYY'); to_char -- Dec 2004 (1 row) rt3=# SELECT to_char(timestamp with time zone '2003-12-31 14:44:44-08', 'Mon '); to_char -- Dec 2003 (1 row) Is that correct? It confused me... there doesn't seem to be a description in the documentation which mentions IYYY what exactly an ISO year is... ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] BUG #1993: Adding/subtracting negative time intervals
Thanks for all this discussion, fixing, etc. I'm currently having
"issues" getting postgres' date/time functions to do what I want. You
have obviously spotted some of the reasons for this.
Many of my issues disappear when I use 8.1, but it's still in beta. Is
it safe for me to use 8.1 in production, if I don't use any of the new
features? If not, would it be possible to backport these date/time
changes to 8.0 so that my program can operate correctly before 8.1 is
finished beta? I can do this backporting myself if someone can point me
to the relevant files. (I'm sure I can work it out myself if necessary,
but I'm a little busy at the moment).
BTW, Postgres' date functions are *great* except for these minor
problems. The best I've ever used.
Thanks!
Nicholas
Klint Gore wrote:
On Tue, 25 Oct 2005 13:28:00 -0400, Tom Lane <[EMAIL PROTECTED]> wrote:
regression=# select '2005-10-29 13:22:00-04'::timestamptz + '1 day'::interval;
?column?
2005-10-30 13:22:00-05
(1 row)
regression=# select '2005-10-30 13:22:00-05'::timestamptz - '2005-10-29
13:22:00-04'::timestamptz;
?column?
1 day 01:00:00
(1 row)
ISTM that given the former result, the latter calculation ought to
produce '1 day', not something else.
Would the '1 day' result know it was 24 hours or be the new 23/24/25
hour version of '1 day'?
If it was the new version, could you get the original values back?
i.e. what would be the result of
select
('2005-10-29 13:22:00-04'::timestamptz +
('2005-10-30 13:22:00-05'::timestamptz -
'2005-10-29 13:22:00-04'::timestamptz)) at time zone 'EST';
klint.
+---+-+
: Klint Gore: "Non rhyming:
: EMail : [EMAIL PROTECTED] : slang - the:
: Snail : A.B.R.I.: possibilities :
: Mail University of New England : are useless" :
: Armidale NSW 2351 Australia : L.J.J. :
: Fax : +61 2 6772 5376 : :
+---+-+
---(end of broadcast)---
TIP 1: 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
[BUGS] BUG #2285: Can not access database after successful PITR - file naming problems
The following bug has been logged online: Bug reference: 2285 Logged by: Nicholas Vinen Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.3 Operating system: Gentoo Linux x86 Description:Can not access database after successful PITR - file naming problems Details: For debugging/testing purposes, I have taken to performing a PITR on a test machine from the production database back-ups. I restore a snap-shot of the production server which was taken before the time I am interested in debugging, then use the PITR method to get the database to the point I want to test at. I can then "rewind" the database and test again by restoring again. This used to work (either before 8.0 or before 8.1, I'm not sure). I also have this back-up in case the production database server dies, but can't really test restoring it on the production machine except when data is lost, otherwise I will interrupt service. The machines are almost identical, the only real difference is that one is a Pentium 3 and one is a Pentium 4, so I don't see why that should matter. They are both running virtually identical Linux installations. Now, when I try to use PITR to restore the database on my test server, the PITR succeeds as normal, but I can not access the database with an error like: psql: FATAL: could not open relation 1663/16385/605464: No such file or directory This happens when I attempt to connect to my database. I can connect to some of the others (such as the template databases) fine. Interestingly, the 605464 file was in the back-up snapshot, but the process of performing the PITR seems to rename the file, but it's still looking under the old name. This is fully reproducible, so if I am not providing sufficient information here, just let me know what you need in order to fix this. Here is a log of what I have done up to the error: rt2 ~ # cd /var/lib/postgresql/data rt2 data # rm -rf * rt2 data # gzip -cd /backup/Helpdesk/Database/Snapshots/Weekly/2006-06\ \ Sun\ 12\ Feb.io.gz | cpio -i 3229320 blocks rt2 data # ls -al base/16385/605464 -rw--- 1 postgres postgres 40960 Feb 27 02:57 base/16385/605464 rt2 data # rm pg_xlog/* rm: cannot remove `pg_xlog/archive_status': Is a directory rt2 data # cp /data/postgresql/recovery.conf /data/postgresql/postgresql.conf . rt2 data # chown postgres * rt2 data # /etc/init.d/postgresql start * Starting PostgreSQL ... rt2 data # tail -f /var/log/postgres/current Feb 27 03:06:12 [postgres] [1-1] LOG: could not create IPv6 socket: Address family not supported by protocol Feb 27 03:06:12 [postgres] [2-1] LOG: database system was interrupted at 2006-02-12 01:30:02 PST Feb 27 03:06:12 [postgres] [3-1] LOG: starting archive recovery Feb 27 03:06:12 [postgres] [4-1] LOG: restore_command = "gzip -cd /backup/Helpdesk/Database/TransactionLog/"%f">"%p"" Feb 27 03:06:12 [postgres] [5-1] LOG: recovery_target_time = 2006-02-17 08:20:00-08 Feb 27 03:06:12 [postgres] [6-1] LOG: restored log file "0001000B0002.00A9FA60.backup" from archive Feb 27 03:06:14 [postgres] [7-1] LOG: restored log file "0001000B0002" from archive Feb 27 03:06:14 [postgres] [8-1] LOG: checkpoint record is at B/2A9FA60 Feb 27 03:06:14 [postgres] [9-1] LOG: redo record is at B/2A9FA60; undo record is at 0/0; shutdown FALSE Feb 27 03:06:14 [postgres] [10-1] LOG: next transaction ID: 34066581; next OID: 611376 Feb 27 03:06:14 [postgres] [11-1] LOG: next MultiXactId: 633; next MultiXactOffset: 1265 Feb 27 03:06:14 [postgres] [12-1] LOG: automatic recovery in progress Feb 27 03:06:14 [postgres] [13-1] LOG: redo starts at B/2A9FAA4 Feb 27 03:06:27 [postgres] [14-1] LOG: restored log file "0001000B0003" from archive Feb 27 03:06:38 [postgres] [15-1] LOG: restored log file "0001000B0004" from archive Feb 27 03:06:41 [postgres] [16-1] LOG: restored log file "0001000B0005" from archive Feb 27 03:47:13 [postgres] [682-1] LOG: restored log file "0001000D00A1" from archive Feb 27 03:47:17 [postgres] [683-1] LOG: restored log file "0001000D00A2" from archive Feb 27 03:47:21 [postgres] [684-1] LOG: restored log file "0001000D00A3" from archive Feb 27 03:47:21 [postgres] [685-1] LOG: recovery stopping before commit of transaction 42586328, time 2006-02-17 08:20:01 PST Feb 27 03:47:21 [postgres] [686-1] LOG: redo done at D/A35794EC Feb 27 03:47:21 [postgres] [687-1] LOG: selected new timeline ID: 2 Feb 27 03:47:22 [postgres] [688-1] LOG: archive recovery complete Feb 27 03:47:32 [postgres] [689-1] LOG: database system is ready Feb 27 03:47:32 [postgres] [690-1] LOG: transaction ID wrap limit is 1103292637, limited by database "postgres" Feb 27 05:39:54 [postgres] [2-1] LOG: invalid server process ID -1 (new error in 8.1.3) r
