Re: [BUGS] Grantor name gets lost when grantor role dropped
Alvaro Herrera wrote: Jeff Davis wrote: CREATE ROLE test_role NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; CREATE ROLE invalid_grantor SUPERUSER INHERIT NOCREATEDB NOCREATEROLE; SET ROLE invalid_grantor; GRANT "postgres" TO "test_role"; SET ROLE postgres; select * from pg_roles; select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members LEFT JOIN pg_roles ur ON roleid = oid LEFT JOIN pg_roles gr ON gr.oid = grantor; DROP ROLE invalid_grantor; select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members LEFT JOIN pg_roles ur ON roleid = oid LEFT JOIN pg_roles gr ON gr.oid = grantor; DROP ROLE test_role; The problem here is that we allowed the drop of invalid_grantor. We are missing a shared dependency on it. So does this make a todo item? But this still leaves the concerns about you can currently get the database into an invalid state that can't be dumped and restored. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] Grantor name gets lost when grantor role dropped
Russell Smith wrote: > Alvaro Herrera wrote: > >Jeff Davis wrote: > > > > > >>CREATE ROLE test_role > >> NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE; > >> > >>CREATE ROLE invalid_grantor > >> SUPERUSER INHERIT NOCREATEDB NOCREATEROLE; > >> > >>SET ROLE invalid_grantor; > >>GRANT "postgres" TO "test_role"; > >>SET ROLE postgres; > >> > >>select * from pg_roles; > >> > >>select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members > >>LEFT JOIN pg_roles ur ON roleid = oid > >>LEFT JOIN pg_roles gr ON gr.oid = grantor; > >> > >>DROP ROLE invalid_grantor; > >> > >>select pg_auth_members.*, ur.rolname, gr.rolname from pg_auth_members > >>LEFT JOIN pg_roles ur ON roleid = oid > >>LEFT JOIN pg_roles gr ON gr.oid = grantor; > >> > >>DROP ROLE test_role; > >> > > > >The problem here is that we allowed the drop of invalid_grantor. We are > >missing a shared dependency on it. > > > So does this make a todo item? > > But this still leaves the concerns about you can currently get the > database into an invalid state that can't be dumped and restored. Correct, which makes it a bug (==> needs fixed) rather than a todo item. We now have a problem because there may already be databases that are undumpable. We might need to provide a workaround for people with such a database. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #3228: Linux/M32R project
On Mon, Apr 16, 2007 at 04:18:02AM +, Kazuhiro Inaoka wrote: > > The following bug has been logged online: > > Bug reference: 3228 > Logged by: Kazuhiro Inaoka > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1 > Operating system: linux > Description:Linux/M32R project > Details: > > Developing Linux/M32R. Inoaka-san, Please consider submitting this as a pgfoundry project or as a porting issue on the pgsql-hackers mailing list. Regards, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(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 #3236: Partitioning has problem with timestamp and timestamptz data type
The following bug has been logged online:
Bug reference: 3236
Logged by: Christian Gonzalez
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.1
Operating system: Red Hat 4.1.1-30
Description:Partitioning has problem with timestamp and timestamptz
data type
Details:
When you use timestamp and timestamptz data type for partitioning
implementation, your postgresql partitioning implementation doesen't work
fine when you make a SELECT using this columns type.
Using Example in PostgreSQL Partitioning page
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
--Create Master Table
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktempint,
unitsales int
);
--Create Child Tables
CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);
-- Add two new column (timestamp and timestamptz) ALTER TABLE measurement
ADD COLUMN logdatet timestamp; ALTER TABLE measurement ADD COLUMN logdatett
timestamptz;
-- Test SELECT in column type DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdate = '2006-01-01'
"Result (cost=0.00..50.75 rows=12 width=32)"
" -> Append (cost=0.00..50.75 rows=12 width=32)"
"-> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"
-- Test SELECT in column type timestamp
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet = '2006-01-01'
"Result (cost=0.00..152.25 rows=36 width=32)"
" -> Append (cost=0.00..152.25 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2004m02 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2004m03 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2005m11 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2005m12 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
-- Test SELECT in column type timestamp whit cast to DATE SET
constraint_exclusion = on; EXPLAIN SELECT * FROM measurement WHERE
logdatet::date = '2006-01-01'::date "Result (cost=0.00..170.70 rows=36
width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2004m02 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2004m03 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2005m11 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2005m12 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2006m01 measurement
(cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE CAST(logdatet AS DATE) =
CAST('2006-01-01' AS DATE) "Result (cost=0.00..170.70 rows=36 width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filter:
[BUGS] BUG #3233: Trigger checks failed on concurrent transactions
The following bug has been logged online: Bug reference: 3233 Logged by: Dmitry Belyavsky Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.6 Operating system: FreeBSD 6 Description:Trigger checks failed on concurrent transactions Details: Greetings! I have a table containing IP ranges and a BEFORE INSERT trigger checking the ranges I insert don't intersect already existing ranges. So the concurrent transactions are able to insert 2 intersecting ranges. Whether TODO record "Allow statement-level triggers to access modified rows" concerns the case I report? Is there any workaround except LOCK TABLE causing performance degradation? Thank you! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #3234: pg_dump uses the first version of a primary key's name when generating reports
The following bug has been logged online: Bug reference: 3234 Logged by: Jonathan Vanasco Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: freebsd 6.1 , osx 10.4.9 Description:pg_dump uses the first version of a primary key's name when generating reports Details: reposted from pg-users, sorry for posting in the wrong place: i believe i found a bug psql> create table test_failure ( id integer primary key ); psql> alter table test_failure alter id rename to id_new ; # not relevant, just illustrating the reason how i got to step 2 psql> alter table test_failure_pkey rename to test_failure_id_new_pkey; $ pg_dump --schema-only -ttest_failure -Utest > test.sql the pkey in the schema is added as 'test_failure_pkey' , not 'test_failure_id_new_pkey'; it happens whether or not you use 'alter table' or 'alter index' on the pkey name ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #3235: Partitioning has problem with timestamp and timestamptz data types
The following bug has been logged online:
Bug reference: 3235
Logged by: Christian Gonzalez
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.2.1
Operating system: Red Hat 4.1.1-30
Description:Partitioning has problem with timestamp and timestamptz
data types
Details:
When you use timestamp and timestamptz data type for partitioning
implementation,
your postgresql partitioning implementation doesen't work fine when you
make a
SELECT using this columns type.
Using Example in PostgreSQL Partitioning page
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
--Create Master Table
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktempint,
unitsales int
);
--Create Child Tables
CREATE TABLE measurement_y2004m02 (
CHECK ( logdate >= DATE '2004-02-01' AND logdate < DATE '2004-03-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2004m03 (
CHECK ( logdate >= DATE '2004-03-01' AND logdate < DATE '2004-04-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m11 (
CHECK ( logdate >= DATE '2005-11-01' AND logdate < DATE '2005-12-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m12 (
CHECK ( logdate >= DATE '2005-12-01' AND logdate < DATE '2006-01-01' )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m01 (
CHECK ( logdate >= DATE '2006-01-01' AND logdate < DATE '2006-02-01' )
) INHERITS (measurement);
-- Add two new column (timestamp and timestamptz)
ALTER TABLE measurement ADD COLUMN logdatet timestamp;
ALTER TABLE measurement ADD COLUMN logdatett timestamptz;
-- Test SELECT in column type DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdate = '2006-01-01'
"Result (cost=0.00..50.75 rows=12 width=32)"
" -> Append (cost=0.00..50.75 rows=12 width=32)"
"-> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdate = '2006-01-01'::date)"
-- Test SELECT in column type timestamp
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet = '2006-01-01'
"Result (cost=0.00..152.25 rows=36 width=32)"
" -> Append (cost=0.00..152.25 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..25.38 rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2004m02 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2004m03 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2005m11 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2005m12 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
"-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..25.38
rows=6 width=32)"
" Filter: (logdatet = '2006-01-01 00:00:00-04'::timestamp with
time zone)"
-- Test SELECT in column type timestamp whit cast to DATE
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE logdatet::date = '2006-01-01'::date
"Result (cost=0.00..170.70 rows=36 width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2004m02 measurement (cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2004m03 measurement (cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2005m11 measurement (cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2005m12 measurement (cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
"-> Seq Scan on measurement_y2006m01 measurement (cost=0.00..28.45
rows=6 width=32)"
" Filter: ((logdatet)::date = '2006-01-01'::date)"
SET constraint_exclusion = on;
EXPLAIN SELECT * FROM measurement WHERE CAST(logdatet AS DATE) =
CAST('2006-01-01' AS DATE)
"Result (cost=0.00..170.70 rows=36 width=32)"
" -> Append (cost=0.00..170.70 rows=36 width=32)"
"-> Seq Scan on measurement (cost=0.00..28.45 rows=6 width=32)"
" Filte
[BUGS] BUG #3232: Regression: pgsql server startup problem with encrypted partitions
The following bug has been logged online: Bug reference: 3232 Logged by: Carsten Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.3 Operating system: Windows XP SP 2 Description:Regression: pgsql server startup problem with encrypted partitions Details: I am running the following scenario: * Windows XP SP 2 * Postgres 8.2.3 installed on a normal Windows partition * One highly sensitive database on a seperate table space which is located on a TureCrypt partition * The TrueCrypt partition only can be mounted after I log into the machine (as I have to enter the password) In the version of PostgreSQL which I was using previously (don't remember version number) this scenario worked fine. As long as I didn't try to access a database on the encrypted partition before mounting it the pgsql server was happy. In current version this no longer works. I have to manually restart the pgsql server after mounting the encyprted partition to access the database on it. Were there any changes which made the pgsql server stricter in requiring that all table-spaces exist on start-up? If yes, any chance of reversing this to support databases on encrypted partitions or removable devices? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] Submit bug report via bug web form - couldn't find the thread
I am reporting a bug on duplicate rows on primary key. Below is the
information:
Thank you for your bug report
The report (reference: 3222) will be forwarded to the development team for
further investigation.
http://wwwmaster.postgresql.org/system/handler.php?page=submitthanks&lang=en
&action=support/submitbug&id=3222
Thank you for your bug report
The report (reference: 3231) will be forwarded to the development team for
further investigation.
Duplicate primary key record
Report Date: April 13, 2007
Subject:Duplicate primary key record
Your name : Amorn Buchheit
Your email address : [EMAIL PROTECTED]
System Configuration
-
Server: IBM
Operating System: Linux
version
-
PostgreSQL 7.3.4 on i386-redhat-linux-gnu, compiled by GCC 2.96
There are several tables have two same rows with the same primary key. Some
tables have a unique index with two same
value. This shouldn't happen.
Has this been reported as a bug in this version?
Table "public.student_year_rlt"
Column | Type | Modifiers
+--+
user_id| integer | not null
school_year_id | integer | not null default 4
grade_level_id | integer | not null
year_detail| character varying(250) |
school_id | integer | not null default 1
status_id | integer | not null default 1
created_date | timestamp with time zone | not null default 'now'
last_modified | timestamp with time zone | default
('now'::text)::timestamp(6) with time zone
Indexes: student_year_rlt_pkey primary key btree (user_id, school_year_id,
school_id)
Foreign Key constraints: school_id_fk FOREIGN KEY (school_id) REFERENCES
school_info_l(school_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
grade_level_id_fk FOREIGN KEY (grade_level_id)
REFERENCES grade_level_l(grade_level_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
status_id_fk FOREIGN KEY (status_id) REFERENCES
status_l(status_id) ON UPDATE NO ACTION ON DELETE NO ACTION,
school_year_id_fk FOREIGN KEY (school_year_id)
REFERENCES school_year_l(school_year_id) ON UPDATE NO ACTION ON DELETE NO
ACTION,
user_id_fk FOREIGN KEY (user_id) REFERENCES
user_common(user_id) ON UPDATE NO ACTION ON DELETE NO ACTION
Triggers: RI_ConstraintTrigger_5663425,
RI_ConstraintTrigger_5663426,
tr_add_school_id,
tr_insert_school_id,
tr_last_modified
project=# select * from student_year_rlt where user_id = 792 and
school_year_id = 6; user_id |
school_year_id | grade_level_id | year_detail | school_id | status_id |
created_date | last_modified
user_id | school_year_id | grade_level_id | year_detail | school_id |
status_id | created_date | last_modified
-+++-+---+--
-+---+---
792 | 6 | 4 | | 10437 |
1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03 01:09:53.577369-05
792 | 6 | 4 | | 10437 |
1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03 01:09:53.577369-05
(2 rows)
SELECT oid,xmin,xmax,* FROM student_year_rlt WHERE user_id = 792 and
school_year_id = 6;
project=# SELECT oid,xmin,xmax,* FROM student_year_rlt WHERE user_id = 792
and school_year_id = 6;
oid | xmin | xmax | user_id | school_year_id | grade_level_id |
year_detail | school_id | status_id | created_date |
last_modified
-+-+--+-+++-
+---+---+---+---
5664062 | 4311665 | 87 | 792 | 6 | 4 |
| 10437 | 1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03
01:09:53.577369-05
5664062 | 4311665 | 87 | 792 | 6 | 4 |
| 10437 | 1 | 2007-03-08 01:09:17.040851-06 | 2007-04-03
01:09:53.577369-05
(2 rows)
project=#
Table "public.bm_student_answers"
Column | Type | Modifiers
---+--+
bm_publication_id | integer | not null
user_id | integer | not null
school_id | integer | not null
bm_question_id| integer | not null
bm_answer_id | integer |
correct_answer| boolean | not null d
Re: [BUGS] BUG #3235: Partitioning has problem with timestamp and timestamptz data types
"Christian Gonzalez" <[EMAIL PROTECTED]> writes: > Description:Partitioning has problem with timestamp and timestamptz > data types In your example as given, you partition the table on "logdate", and then add an unrelated column "logdatet" and seem to expect that the system will think that's a partitioning condition. I think you probably misstated your example, and meant to complain that a table properly partitioned on a timestamptz column isn't behaving as you'd wish for comparisons against "date" values. The problem here is that the conversion is dependent on TimeZone, so is not immutable, so the planner daren't depend on it to prove that the unwanted partition tables need not be searched. You need to think harder about how timezones should enter into your search rules... regards, tom lane ---(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
Re: [BUGS] BUG #3232: Regression: pgsql server startup problem with encrypted partitions
"Carsten" <[EMAIL PROTECTED]> writes: > Were there any changes which made the pgsql server stricter in requiring > that all table-spaces exist on start-up? Not that I can think of --- except that startup for crash recovery has always expected everything to be accessible. If that's not what you're running into, please provide a full test case. regards, tom lane ---(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
