[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] 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
