[BUGS] BUG #5214: Permission troubles for views
The following bug has been logged online: Bug reference: 5214 Logged by: Nikolay Email address: whee...@gmail.com PostgreSQL version: 8.4.1 Operating system: Gentoo Linux Description:Permission troubles for views Details: There is two users in database: user_a and user_b. user_a is an owner of current schema. Table "tbl_data": ALTER TABLE "tbl_data" OWNER TO user_a; REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE "tbl_data" FROM user_a; GRANT REFERENCES, TRIGGER ON TABLE "tbl_data" TO user_a; GRANT ALL ON TABLE "tbl_data" TO user_b; Execute query as user_a: SELECT * FROM "tbl_data"; - permission denied for relation tbl_data. This is correct. user_a can't select from table tbl_data. Execute query as user_b: SELECT * FROM "tbl_data"; - returns rows from table. This is correct. user_b can select from table tbl_data. View "vw_data": CREATE VIEW "vw_data" as select * from "tbl_data"; ALTER TABLE "vw_data" OWNER TO user_a; REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLE "vw_data" FROM user_a; GRANT ALL ON TABLE "vw_data" TO user_b; Execute query as user_a: SELECT * FROM "vw_data"; - permission denied for relation vw_data. This is correct. Execute query as user_b: SELECT * FROM "vw_data"; - permission denied for relation tbl_data. But permissions say that user_b can select from tbl_data and from vw_data. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #7578: Not able to drop user if S/he has permission on tablespace
Where can I found this path? 03.10.2012 18:55, Alvaro Herrera пишет: Excerpts from wheelly's message of mar oct 02 05:49:27 -0300 2012: Where is a bug in PostgreSQL or in documentation? I think it was a bug in the code. I have committed a patch that should fix this problem. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4479: Incorrect TSearch2 results when inserting after deleting
I want to say many thanks to Teodor for really quick bug fixing. Currently we are waiting for decision when official bugfix will be issued (we had to make some workarounds for production environment and thinking, what to do -- rollback to 8.3.3 or apply the patch). So the question is when 8.3.5 will be released? I suspect many DBAs need it. On Wed, Oct 22, 2008 at 5:20 PM, Teodor Sigaev <[EMAIL PROTECTED]> wrote: > Fixed, the reason is the same as for > http://archives.postgresql.org/pgsql-general/2008-10/msg00845.php > > Patch: > http://archives.postgresql.org/pgsql-committers/2008-10/msg00187.php > -- > Teodor Sigaev E-mail: [EMAIL PROTECTED] > WWW: > http://www.sigaev.ru/ > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- Sincerely yours, Nikolay Samokhvalov Postgresmen LLC, http://postgresmen.ru
[BUGS] BUG #2490: '||' and type casting for user defined types
The following bug has been logged online:
Bug reference: 2490
Logged by: Nikolay Samokhvalov
Email address: [EMAIL PROTECTED]
PostgreSQL version: CVS
Operating system: fedora core 5
Description:'||' and type casting for user defined types
Details:
Assume that we are creating foolish type 'aaa', which works like
varchar(3),
or is simply 'string, which length is not more than 3'.
In/out functions for this type:
Datum
aaa_in(PG_FUNCTION_ARGS)
{
char*s = PG_GETARG_CSTRING(0);
int len = strlen(s);
VarChar *result;
if (len > 3)
ereport(ERROR,
(errcode(ERRCODE_EXTERNAL_ROUTINE_EXCEPTION),
errmsg("too much chars: length=\"%d\"",
len)));
result = (VarChar *) palloc(len + VARHDRSZ);
VARATT_SIZEP(result) = len + VARHDRSZ;
memcpy(VARDATA(result), s, len);
PG_RETURN_VARCHAR_P(result);
}
Datum
aaa_out(PG_FUNCTION_ARGS)
{
VarChar *s = PG_GETARG_VARCHAR_P(0);
char*result;
int32 len;
/* copy and add null term */
len = VARSIZE(s) - VARHDRSZ;
result = palloc(len + 1);
memcpy(result, VARDATA(s), len);
result[len] = '\0';
PG_RETURN_CSTRING(result);
}
SQL code:
CREATE FUNCTION aaa_in(cstring)
RETURNS aaa
AS 'MODULE_PATHNAME'
LANGUAGE C RETURNS NULL ON NULL INPUT;
CREATE FUNCTION aaa_out(aaa)
RETURNS cstring
AS 'MODULE_PATHNAME'
LANGUAGE C RETURNS NULL ON NULL INPUT;
CREATE TYPE aaa (
INTERNALLENGTH = -1,
INPUT = aaa_in,
OUTPUT = aaa_out,
STORAGE = extended
);
CREATE CAST (aaa AS text) WITHOUT FUNCTION
AS IMPLICIT;
CREATE CAST (text AS aaa) WITHOUT FUNCTION
AS IMPLICIT;
Well, let's do some tests. After applying sql code in the database 'trash':
trash=# select 'asd'::aaa;
aaa
-
asd
(1 row)
trash=# select 'asdf'::aaa;
ERROR: too much chars: length="4"
trash=# select ('as' || 'df')::aaa;
aaa
--
asdf
(1 row)
In the last case, function aaa_in() wasn't invoked at all and we obtained
the sting of type aaa with length > 3...
---(end of broadcast)---
TIP 6: explain analyze is your friend
[BUGS] Fwd: [NOVICE] Trigger and Recursive Relation ?
Is this a bug or not? Actually, ordinary person get used to think that if "delete from tbl" ends, then there should no rows exists in tbl, but I understand that DELETE FROM works in a loop and... Let's take a look at the standard paper (ISO/IEC 9075-2:2003 -- 14.7 -- General Rules): "... 11) All rows that are marked for deletion are effectively deleted at the end of the , prior to the checking of any integrity constraints. 12) If is specified, then the is evaluated for each row of T prior invocation of any caused by the imminent or actual deletion of any row of T. ..." So, is it a bug? Seems to be so.. -- Forwarded message -- From: Greg Steele <[EMAIL PROTECTED]> Date: Aug 1, 2006 11:31 PM Subject: [NOVICE] Trigger and Recursive Relation ? To: Postgres Novice <[EMAIL PROTECTED]> Hi, I'm a Postgres newbie trying to figure out a trigger problem. I have a table with a recursive relationship, and I'm trying to create a trigger that will maintain the parent child relationship when a record is deleted. For example, I have records where record 0 references null, record 1 references record 0, record 2 references record1, and so on. I created a trigger that maintains the relationship after a deletion. For example, if I delete record 1 in the above example, record 2 will now point to record 0 (record 1's parent). The scenario works fine when I individually delete records, but when I try to delete a set of records at once, only 1/2 of the records are deleted. Probably something simple, but I can't figure out what's happening. Here's a simplified example of what I am try to do...Please help! Thanks Regards, Greg Steele CREATE TABLE recursive( id int PRIMARY KEY, parent int, FOREIGN KEY (parent) REFERENCES recursive ON DELETE CASCADE ); CREATE OR REPLACE FUNCTION delete_on_recursive_trigger_fx() RETURNS trigger AS $$ BEGIN UPDATE recursive SET parent = OLD.parent WHERE parent = OLD.id; RETURN OLD; END; $$ Language 'plpgsql'; CREATE TRIGGER delete_on_recursive_trigger BEFORE DELETE ON recursive FOR EACH ROW EXECUTE PROCEDURE delete_on_recursive_trigger_fx(); INSERT INTO recursive(id, parent) values(1, null); INSERT INTO recursive(id, parent) values(2, 1); INSERT INTO recursive(id, parent) values(3, 2); INSERT INTO recursive(id, parent) values(4, 3); --only 1/2 of the records are deleted! DELETE FROM recursive; ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Best regards, Nikolay ---(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
Re: [BUGS] Fwd: [NOVICE] Trigger and Recursive Relation ?
OK, then we should at least forbit making such things... Otherwise, it
seems to be smth like gotcha.
But look at this please:
"12) If is specified, then the is
evaluated for each row of T prior
invocation of any caused by the imminent or actual
deletion of any row of T."
Does Postgres work this way? In the case of 'delete from tbl;' we
have search condition>=TRUE for all rows. If we evaluate it *before*
any other operation, we should mark all rows to be deleted. I guess,
Postgres doesn't follow this logic..
Am I wrong?
P.S. BTW, look at the -novice list - he reports, that problem remains
even after dropping FK at all.
On 8/2/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Nikolay Samokhvalov" <[EMAIL PROTECTED]> writes:
> Is this a bug or not?
I don't think so --- or perhaps better, this is a buggy trigger.
he UPDATE in the trigger will supersede the base DELETE query for any
rows that the UPDATE changes before the base DELETE has reached 'em.
Essentially you've written an indeterminate system ...
regards, tom lane
--
Best regards,
Nikolay
---(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] Fwd: [NOVICE] Trigger and Recursive Relation ?
On 8/2/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: Does Postgres work this way? In the case of 'delete from tbl;' we have search condition>=TRUE for all rows. If we evaluate it *before* any other operation, we should mark all rows to be deleted. I guess, Postgres doesn't follow this logic.. My assumption: Postgres takes one row, marks it as deleted, then executes trigger and updates another row. Due to MVCC new version of that row is created and in the following iteration Postgres simply doesn't "see" this row... I don't understand how this can be called "not bug"... Please, help me understand it :-) -- Best regards, Nikolay ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)
I still think that this is quite strange behaviour. When I write
'...SELECT NEW.id...' I don't expect that another calling of column's
default expr will take place. I just want to have access to "id"
column of just-created row.
Any thoughts?
-- Forwarded message --
From: Nikolay Samokhvalov <[EMAIL PROTECTED]>
Date: Jul 13, 2006 6:15 PM
Subject: Strange behaviour of RULE (selecting last inserted ID of
'sequenced' column)
To: PostgreSQL-general
Is this a bug?
test=> create sequence strange_seq;
CREATE SEQUENCE
test=> create table strange(id integer not null default
nextval('strange_seq') primary key, data text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"strange_pkey" for table "strange"
CREATE TABLE
test=> create rule strange_rule as on insert to strange do select new.id as id;
CREATE RULE
test=> insert into strange(data) values('adas');
id
2
(1 row)
test=> select * from strange;
id | data
+--
1 | adas
(1 row)
test=> insert into strange(data) values('adas');
id
4
(1 row)
test=> insert into strange(data) values('adas');
id
6
(1 row)
test=> select * from strange;
id | data
+--
1 | adas
3 | adas
5 | adas
(3 rows)
--
Best regards,
Nikolay
--
Best regards,
Nikolay
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [BUGS] Fwd: Strange behaviour of RULE (selecting last inserted ID of 'sequenced' column)
yes, I've found some words about similar sutuation on Varlena:
"... The SERIAL column NEW.job_id is effectively replaced by
nextval(...) and the nextval() function is called twice--once for the
original row and once for that traced row."
(http://www.varlena.com/GeneralBits/101.php)
What am I doing? I've just wanted to introduce the rule that allows to
retrieve "last inserted ID" on every INSERT executed.
I'm afraid that this is not expected behaviour. OK, let it be so for
some time...
Simple workaround is: to use currval() instead of NEW.id.
On 8/8/06, Jim Nasby <[EMAIL PROTECTED]> wrote:
More of a gotcha than a bug... basically, your select rule is hitting
the sequence again. I think there's a section in the rules chapter
that talks about this. GeneralBits might also have info.
Probably a better question is, what are you trying to do?
On Aug 4, 2006, at 4:50 AM, Nikolay Samokhvalov wrote:
> I still think that this is quite strange behaviour. When I write
> '...SELECT NEW.id...' I don't expect that another calling of column's
> default expr will take place. I just want to have access to "id"
> column of just-created row.
>
> Any thoughts?
>
> -- Forwarded message --
> From: Nikolay Samokhvalov <[EMAIL PROTECTED]>
> Date: Jul 13, 2006 6:15 PM
> Subject: Strange behaviour of RULE (selecting last inserted ID of
> 'sequenced' column)
> To: PostgreSQL-general
>
>
> Is this a bug?
>
> test=> create sequence strange_seq;
> CREATE SEQUENCE
> test=> create table strange(id integer not null default
> nextval('strange_seq') primary key, data text);
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "strange_pkey" for table "strange"
> CREATE TABLE
> test=> create rule strange_rule as on insert to strange do select
> new.id as id;
> CREATE RULE
> test=> insert into strange(data) values('adas');
> id
>
> 2
> (1 row)
>
> test=> select * from strange;
> id | data
> +--
> 1 | adas
> (1 row)
>
> test=> insert into strange(data) values('adas');
> id
>
> 4
> (1 row)
>
> test=> insert into strange(data) values('adas');
> id
>
> 6
> (1 row)
>
> test=> select * from strange;
> id | data
> +--
> 1 | adas
> 3 | adas
> 5 | adas
> (3 rows)
>
>
> --
> Best regards,
> Nikolay
>
>
> --
> Best regards,
> Nikolay
>
> ---(end of
> broadcast)---
> TIP 5: don't forget to increase your free space map settings
>
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
--
Best regards,
Nikolay
---(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] No error when FROM is missing in subquery
Following query is considered as correct, no "missing from" error has been reported (so, entire table will be updated and "on update" triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] No error when FROM is missing in subquery
ok, sorry, I've realized that it's yet another example of "outer reference", Tom will say "read any SQL book" again :-) http://archives.postgresql.org/pgsql-bugs/2006-12/msg00115.php On 12/19/06, Nikolay Samokhvalov <[EMAIL PROTECTED]> wrote: Following query is considered as correct, no "missing from" error has been reported (so, entire table will be updated and "on update" triggers will be fired for every row): update item set obj_id = obj_id where obj_id in (select obj_id where item_point is null order by obj_modified limit 10) Is it a bug? If no, maybe to produce warning in such cases? -- Best regards, Nikolay -- Best regards, Nikolay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] Bug #843: pg_clog files problem - clarification
Hi I am the bug reporter. I have uploaded some misleading information. Actually the log entries produced on startup after power down are the following: DEBUG: database system was interrupted at 2002-12-09 14:59:34 EEST DEBUG: checkpoint record is at 0/4239AF0 DEBUG: redo record is at 0/4239AF0; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 4523; next oid: 62486 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: redo starts at 0/4239B30 DEBUG: ReadRecord: record with zero length at 0/43B2CA0 DEBUG: redo done at 0/43B2C78 DEBUG: database system is ready And here follows the reaction of a query that I don't remember. FATAL 2: open of /usr/share/postgresql/data/pg_clog/0640 failed: No such file or directory DEBUG: server process (pid 115) exited with exit code 2 DEBUG: terminating any other active server processes DEBUG: all server processes terminated; reinitializing shared memory and semaphores Then I copied the data directory to my machine and continued there. The log entries from my original message actually should start here. Please excuse me. Best regards, Nikolay Hristov [EMAIL PROTECTED] wrote: Nikolay Hristov ([EMAIL PROTECTED]) reports a bug with a severity of 1 The lower the number the more severe it is. Short Description pg_clog files problem Long Description I am running PostgreSQL 7.2.1 distributed with cygwin on Windows NT4 SP6. The data directory is on NTFS drive. I performed the following test: - started a transaction with many(about 50) inserts, updates and deletes); - in the middle of this I unplugged the power cable of the machine. After rebooting, I started the postmaster again and the entries in the log file ware as follows: FindExec: found "/usr/bin/postgres" using argv[0] invoking IpcMemoryCreate(size=1441792) FindExec: found "/usr/bin/postmaster" using argv[0] DEBUG: database system was interrupted at 2002-12-09 15:20:37 EEST DEBUG: checkpoint record is at 0/43B2D20 DEBUG: redo record is at 0/43B2D20; undo record is at 0/0; shutdown TRUE DEBUG: next transaction id: 4565; next oid: 70678 DEBUG: database system was not properly shut down; automatic recovery in progress DEBUG: ReadRecord: record with zero length at 0/43B2D60 DEBUG: redo is not required DEBUG: database system is ready I stopped the server, changed some debug display options and started it again: FindExec: found "/usr/bin/postgres" using argv[0] invoking IpcMemoryCreate(size=1441792) FindExec: found "/usr/bin/postmaster" using argv[0] 2002-12-09 15:42:03 [243]DEBUG: database system was shut down at 2002-12-09 15:41:49 EEST 2002-12-09 15:42:03 [243]DEBUG: checkpoint record is at 0/43B2DA0 2002-12-09 15:42:03 [243]DEBUG: redo record is at 0/43B2DA0; undo record is at 0/0; shutdown TRUE 2002-12-09 15:42:03 [243]DEBUG: next transaction id: 4565; next oid: 70678 2002-12-09 15:42:03 [243]DEBUG: database system is ready After that I established a connection and executed a query (select count(*) from pg_class): 2002-12-09 15:42:59 [180]DEBUG: connection: host=127.0.0.1 user=admin database=lager /usr/bin/postmaster child[180]: starting with (postgres -d2 -v131072 -p lager ) 2002-12-09 15:42:59 [180]DEBUG: InitPostgres 2002-12-09 15:42:59 [180]DEBUG: StartTransactionCommand 2002-12-09 15:42:59 [180]DEBUG: query: select getdatabaseencoding() 2002-12-09 15:42:59 [180]DEBUG: ProcessQuery 2002-12-09 15:42:59 [180]DEBUG: CommitTransactionCommand 2002-12-09 15:42:59 [180]DEBUG: StartTransactionCommand 2002-12-09 15:42:59 [180]DEBUG: query: SELECT usesuper FROM pg_user WHERE usename = 'admin' 2002-12-09 15:42:59 [180]DEBUG: ProcessQuery 2002-12-09 15:42:59 [180]DEBUG: CommitTransactionCommand 2002-12-09 15:43:16 [180]DEBUG: StartTransactionCommand 2002-12-09 15:43:16 [180]DEBUG: query: select count(*) from pg_class; 2002-12-09 15:43:16 [180]DEBUG: ProcessQuery 2002-12-09 15:43:16 [180]FATAL 2: open of /cygdrive/d/TEMP/data/pg_clog/0677 failed: No such file or directory 2002-12-09 15:43:16 [180]DEBUG: proc_exit(2) 2002-12-09 15:43:16 [180]DEBUG: shmem_exit(2) 2002-12-09 15:43:17 [180]DEBUG: exit(2) There is only one file in pg_clog: -rw-r--r--1 nike None 8192 Dec 9 15:05 I performed this test a few times during the last two days. This situation was reproduced twice. Sample Code 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]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2245: pg_dump doesn't dump expressions with sequence in DEFAULT setting for some column in table
The following bug has been logged online:
Bug reference: 2245
Logged by: Nikolay Samokhvalov
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.2
Operating system: Linux Fedora Core 4
Description:pg_dump doesn't dump expressions with sequence in
DEFAULT setting for some column in table
Details:
I use some expression as DEFAULT setting for some column of some table. For
example, nextval('myseq') * 10.
Then, I pg_dump my database and restore it. I see 'nextval('myseq')' (w/o
'*10').
(Surely, '*10' is just an example, I know, that I can increase the INCREMENT
parameter for the sequence.)
I suppose it's a bug.
More details:
http://archives.postgresql.org/pgsql-general/2006-02/msg00251.php
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #6330: Incorrect select results when using mutually exclusive conditions for nullable column with index
The following bug has been logged on the website:
Bug reference: 6330
Logged by: Nikolay Gorshkov
Email address: nikolay.gorsh...@gmail.com
PostgreSQL version: 9.0.4
Operating system: Ubuntu 10.04.2 LTS
Description:
How to reproduce:
# create table test (uid varchar(255) primary key, dt date);
# insert into test values ('1', now()), ('2', now()), ('3', null);
# create index test_dt on test(dt);
# analyze test;
# select * from test where dt is null and dt >= '2011-01-01 +01:00:00';
I expect zero results from the selection since the conditions are mutually
exclusive. However, one row with NULL value in "dt" column is returned:
uid | dt
-+
3 |
(1 row)
If the index "test_dt" is dropped:
# drop index test_dt;
The same query return zero rows, as expected:
# select * from test where dt is null and dt >= '2011-01-01 +01:00:00';
uid | dt
-+
(0 rows)
PostgreSQL version information:
# select version();
Result: PostgreSQL 9.0.4 on i486-pc-linux-gnu, compiled by GCC gcc-4.4.real
(Ubuntu 4.4.3-4ubuntu5) 4.4.3, 32-bit
Linux information:
# uname -a
Result: Linux hostname 2.6.32-21-generic-pae #32-Ubuntu SMP Fri Apr 16
09:39:35 UTC 2010 i686 GNU/Linux
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
