[BUGS] BUG #1186: Broken Index?
The following bug has been logged online: Bug reference: 1186 Logged by: Gosen, Hitoshi Email address: [EMAIL PROTECTED] PostgreSQL version: 7.4 Operating system: linux 2.4.18 Description:Broken Index? Details: Hello All, We are using PostgreSQL 7.4.2 for our website that handles over 200,000 transactions a day. About a month ago, the responses from the SELECT queries on the database became terribly slow. We tried to anaylze the cause of the problem, searching throught the system logs and all, but nothing appeared to be out of the ordinary. What we did to resolve this was to dump the database, delete the database, recreate the database, and finally restore it. After that, things were back to normal. >From the above experience, we were able to hypothesize that the fault of the slow responses was not from a broken data or hardware failures, but from a broken index, since we were able to recover 100% of the data on the same machine. Today, the same problem occured, and the same actions are going to be taken to temporary resolve it. Final note: we will also experiment with the 'vacuum full' command to see if it counters this problem. ---(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] BUG #1161: User permissions are kept, even if user is
little example of generating usesysid create user test sysid 2147483647; CREATE USER create user test1; CREATE USER select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig --+-+-+--+---++--+--- postgres | 1 | t | t| t | | | test | 2147483647 | f | f| f | | | test1| -2147483648 | f | f| f | | | (3 rows) create user test2; psql:./tt:5: ERROR: duplicate key violates unique constraint "pg_shadow_usesysid_index" And I have still a question: How to remove privileges of the nonexistent (removed) user? And the offer: Can realize removal of privileges of nonexistent users during time vacuum? PS: Sorry for my ugly english ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #1161: User permissions are kept, even if user is
> And I have still a question: > How to remove privileges of the nonexistent (removed) user? I'm not sure it is even desirable? There are 2 differents concepts: - logins with a user name, password... - sysids which is really an int. a login must have a sysid, but a sysid may or may not correspond to a login. As it is implemented, the privileges belong to the sysids. This give the opportunity (let's call that a feature) to recreate a deleted user that would reclaim its previous status wrt priviliges. > And the offer: > Can realize removal of privileges of nonexistent users during time vacuum? Although that could be done, I'm not sure I would like such a thing to happen. -- Fabien Coelho - [EMAIL PROTECTED] ---(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
Re: [BUGS] Possible bug?
On Jul 1 2004, Stephan Szabo wrote:
Technically speaking, I believe the queries are simply invalid without the
presence of casts from the spec's standpoint.
Theoretically, I'd either expect both to give 0 rows (convert 3 into a
string and compare) or both to give 1 row (convert '003' to an integer and
compare) if it didn't error.
What does Oracle do if you insert a value like 'XXX' into the column?
Below is the behaviour of Oracle 8.0.5 for the value 'XXX'
CREATE TABLE T1 (F1 CHAR(4), F2 CHAR(4));
CREATE INDEX T1_F1 ON T1 (F1);
INSERT INTO T1 (F1, F2) VALUES ('001', '001');
INSERT INTO T1 (F1, F2) VALUES ('002', '002');
INSERT INTO T1 (F1, F2) VALUES ('XXX', '003');
INSERT INTO T1 (F1, F2) VALUES ('004', '004');
SELECT * FROM T1 WHERE F1 = 3;
ORA-01722: invalid number
SELECT * FROM T1 WHERE F2 = 3;
F1 F2
XXX 003
1 row selected.
So, yes Oracle is doing a char to integer implicit conversion and
performing an integer with integer comparison.
Thanks for the quick response.
(BTW I appologise for my last report being posted twice. I was not a member
of PGSQL-bugs list when I posted the first one, and since it was taking a
while for it to be approved, I decied to join the list and then post it
again.)
Best regards,
Ilir
--
Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London EC1V 0HB
email: [EMAIL PROTECTED]
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] timestamp arithmetic (a possible bug?)
Hi,
I saw this behaviour in PostgreSQL 7.2. (Once again, I know this is an old
release but I do not have a newer version installed, and I am only using
the server for research purposes). If you execute the following statement
SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004
10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
The result returned is:
?column?
-
2004-01-01 00:01:00
(1 row)
I was expecting: 2004-01-01 10:01:00.
Tried it on Oracle 8.0.5:
SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM. HH:MI:SS') -
TO_DATE('01.01.2004 10:00:00', 'DD.MM. HH:MI:SS') + TO_DATE('01.01.2004
10:00:00', 'DD.MM. HH:MI:SS') FROM DUAL;
---
2004-01-01 10:01:00
(1 row selected)
And MSSQL 7:
SELECT (CAST('01.01.2004 10:01:00' AS DATETIME) - CAST('01.01.2004
10:00:00' AS DATETIME) + CAST('01.01.2004 10:00:00' AS DATETIME));
---
2004-01-01 10:01:00.000
(1 row(s) affected)
Is this a bug? Same thing happens if I use TimestampTZ rather than
Timestamp.
Best regards,
Ilir
Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London EC1V 0HB
email: [EMAIL PROTECTED]
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[BUGS] Grant Update (Possible bug)?
Hi, This is a another bug reported for the Firebird 1.0 server. I subsequently ran it in PostgreSQL 7.2, Oracle 8.0.5 anf MSSQL 7. Reproducible script: Connect as pgsql: CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50)); INSERT INTO TEST (ID) VALUES (1); GRANT UPDATE ON TEST TO TESTUSER; Connect as TestUser; UPDATE TEST SET NAME='TEST' WHERE ID=1; ERROR: test: Permission denied. UPDATE TEST SET NAME='TEST'; Executes successfully. So the user can update the whole table but not specific columns. Is this a bug or as specified (I read briefly the Reference Guide and I didn't see this highlited anywhere, but I may have missed it.). Firebird and MSSQL have the same behaviour as PostgreSQL (Firebird have acknowledged this as a bug, I haven't checked on MSSQL website yet.) In Oracle 8.0.5 both updates execute sucessfully. Best regards, Ilir Ilir Gashi PhD Student Centre for Software Reliability City University Northampton Square, London EC1V 0HB email: [EMAIL PROTECTED] website: http://www.csr.city.ac.uk/csr_city/staff/gashi/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] timestamp arithmetic (a possible bug?)
Interesting
If I reverse the order it works...
agencysacks=# SELECT CAST('01.01.2004 10:00:00' AS
TIMESTAMP) + (CAST('01.01.2004 10:01:00' AS TIMESTAMP)
- CAST('01.01.2004 10:00:00' AS TIMESTAMP)) as answer;
answer
-
2004-01-01 10:01:00
(1 row)
However your original...
agencysacks=# SELECT (CAST('01.01.2004 10:01:00' AS
TIMESTAMP) - CAST('01.01.2004 10:00:00' AS TIMESTAMP))
+ CAST('01.01.2004 10:00:00' AS TIMESTAMP);
ERROR: operator does not exist: interval + timestamp
without time zone
HINT: No operator matches the given name and argument
type(s). You may need to add explicit type casts.
agencysacks=# select version();
version
-
PostgreSQL 7.4.2 on powerpc-apple-darwin7.2.0,
compiled by GCC gcc (GCC) 3.3 20030304 (Apple
Computer, Inc. build 1495)
(1 row)
Looks like postgresql demands the order to be
timestamp +- interval.
Ted
--- Ilir Gashi <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I saw this behaviour in PostgreSQL 7.2. (Once again,
> I know this is an old
> release but I do not have a newer version installed,
> and I am only using
> the server for research purposes). If you execute
> the following statement
>
> SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) -
> CAST('01.01.2004
> 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004
> 10:00:00' AS TIMESTAMP);
>
> The result returned is:
>
> ?column?
> -
> 2004-01-01 00:01:00
> (1 row)
>
> I was expecting: 2004-01-01 10:01:00.
>
> Tried it on Oracle 8.0.5:
>
> SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM.
> HH:MI:SS') -
> TO_DATE('01.01.2004 10:00:00', 'DD.MM.
> HH:MI:SS') + TO_DATE('01.01.2004
> 10:00:00', 'DD.MM. HH:MI:SS') FROM DUAL;
>
> ---
> 2004-01-01 10:01:00
> (1 row selected)
>
>
> And MSSQL 7:
>
> SELECT (CAST('01.01.2004 10:01:00' AS DATETIME) -
> CAST('01.01.2004
> 10:00:00' AS DATETIME) + CAST('01.01.2004 10:00:00'
> AS DATETIME));
>
>
> ---
>
> 2004-01-01 10:01:00.000
>
> (1 row(s) affected)
>
>
> Is this a bug? Same thing happens if I use
> TimestampTZ rather than
> Timestamp.
>
> Best regards,
>
> Ilir
>
>
>
> Ilir Gashi
> PhD Student
> Centre for Software Reliability
> City University
> Northampton Square, London EC1V 0HB
> email: [EMAIL PROTECTED]
> website:
> http://www.csr.city.ac.uk/csr_city/staff/gashi/
>
>
>
> ---(end of
> broadcast)---
> TIP 1: subscribe and unsubscribe commands go to
> [EMAIL PROTECTED]
>
__
Do you Yahoo!?
Yahoo! Mail - 50x more storage than other providers!
http://promotions.yahoo.com/new_mail
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [BUGS] timestamp arithmetic (a possible bug?)
O kyrios Ilir Gashi egrapse stis Jul 2, 2004 :
SELECT '01.01.2004 10:00:00'::timestamp + ('01.01.2004
10:01:00'::timestamp - '01.01.2004 10:00:00'::timestamp)::interval;
?column?
-
2004-01-01 10:01:00
(1 row)
works fine in my 7.4.2
> Hi,
>
> I saw this behaviour in PostgreSQL 7.2. (Once again, I know this is an old
> release but I do not have a newer version installed, and I am only using
> the server for research purposes). If you execute the following statement
>
> SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004
> 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
>
> The result returned is:
>
> ?column?
> -
> 2004-01-01 00:01:00
> (1 row)
>
> I was expecting: 2004-01-01 10:01:00.
>
> Tried it on Oracle 8.0.5:
>
> SELECT TO_DATE('01.01.2004 10:01:00', 'DD.MM. HH:MI:SS') -
> TO_DATE('01.01.2004 10:00:00', 'DD.MM. HH:MI:SS') + TO_DATE('01.01.2004
> 10:00:00', 'DD.MM. HH:MI:SS') FROM DUAL;
>
> ---
> 2004-01-01 10:01:00
> (1 row selected)
>
>
> And MSSQL 7:
>
> SELECT (CAST('01.01.2004 10:01:00' AS DATETIME) - CAST('01.01.2004
> 10:00:00' AS DATETIME) + CAST('01.01.2004 10:00:00' AS DATETIME));
>
>
> ---
>
> 2004-01-01 10:01:00.000
>
> (1 row(s) affected)
>
>
> Is this a bug? Same thing happens if I use TimestampTZ rather than
> Timestamp.
>
> Best regards,
>
> Ilir
>
>
>
> Ilir Gashi
> PhD Student
> Centre for Software Reliability
> City University
> Northampton Square, London EC1V 0HB
> email: [EMAIL PROTECTED]
> website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
>
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
--
-Achilleus
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #1186: Broken Index?
On Fri, Jul 02, 2004 at 04:50:07 -0300, PostgreSQL Bugs List <[EMAIL PROTECTED]> wrote: > > The following bug has been logged online: This doesn't appear to be a bug at this point. It sounds like you have a self induced performance problem, so I am moving the discussion to pgsql-performance. > > Bug reference: 1186 > Logged by: Gosen, Hitoshi > > Email address: [EMAIL PROTECTED] > > PostgreSQL version: 7.4 > > Operating system: linux 2.4.18 > > Description:Broken Index? > > Details: > > Hello All, > We are using PostgreSQL 7.4.2 for our website that handles over 200,000 > transactions a day. > About a month ago, the responses from the SELECT queries on the database > became terribly slow. > We tried to anaylze the cause of the problem, searching throught the system > logs and all, but nothing appeared to be out of the ordinary. > > What we did to resolve this was to dump the database, delete the database, > recreate the database, and finally restore it. After that, things were back > to normal. > > From the above experience, we were able to hypothesize that the fault of the > slow responses was not from a broken data or hardware failures, but from a > broken index, since we were able to recover 100% of the data on the same > machine. > > Today, the same problem occured, and the same actions are going to be taken > to temporary resolve it. > > Final note: we will also experiment with the 'vacuum full' command to see > if it counters this problem. It sounds like you aren't properly vacuuming your database. It is possible that you need a higher FSM setting or to vacuum more frequently. ---(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] timestamp arithmetic (a possible bug?)
On Jul 2 2004, Achilleus Mantzios wrote:
SELECT '01.01.2004 10:00:00'::timestamp + ('01.01.2004
10:01:00'::timestamp - '01.01.2004 10:00:00'::timestamp)::interval;
?column?
-
2004-01-01 10:01:00
(1 row)
works fine in my 7.4.2
Yes. I've just tried it in PostgreSQL 7.2 and it works fine there as well.
On Jul 2 2004, Theodore Petrosky wrote:
Interesting
If I reverse the order it works...
agencysacks=# SELECT CAST('01.01.2004 10:00:00' AS
TIMESTAMP) + (CAST('01.01.2004 10:01:00' AS TIMESTAMP)
- CAST('01.01.2004 10:00:00' AS TIMESTAMP)) as answer;
answer
-
2004-01-01 10:01:00
(1 row)
Yes. The above works for me as well in 7.2.
However your original...
agencysacks=# SELECT (CAST('01.01.2004 10:01:00' AS
TIMESTAMP) - CAST('01.01.2004 10:00:00' AS TIMESTAMP))
+ CAST('01.01.2004 10:00:00' AS TIMESTAMP);
ERROR: operator does not exist: interval + timestamp
without time zone
HINT: No operator matches the given name and argument
type(s). You may need to add explicit type casts.
agencysacks=# select version();
version
-
PostgreSQL 7.4.2 on powerpc-apple-darwin7.2.0,
compiled by GCC gcc (GCC) 3.3 20030304 (Apple
Computer, Inc. build 1495)
(1 row)
Looks like postgresql demands the order to be
timestamp +- interval.
Ted
I am not getting the error above. I get the incorrect result as reported in
my original report. Does this mean that this was a buggy behaviour in 7.2
and then was changed in the latter releases to give the error message that
7.4.2 is giving?
Thanks for the quick responses.
Best regards,
Ilir
Ilir Gashi
PhD Student
Centre for Software Reliability
City University
Northampton Square, London EC1V 0HB
email: [EMAIL PROTECTED]
website: http://www.csr.city.ac.uk/csr_city/staff/gashi/
---(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] Possible bug?
Am Donnerstag, 1. Juli 2004 23:10 schrieb Tom Lane: > Right. The reason PG doesn't error is that we have an implicit cast > from integer to text. I've opined before that we should mark most > cross-type-category casts as explicit-only ... they are just too prone > to give unexpected answers, as in this case. It seems we're just opining in circles. Why not do it? Is anyone objecting? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] timestamp arithmetic (a possible bug?)
Ilir Gashi <[EMAIL PROTECTED]> writes:
> I saw this behaviour in PostgreSQL 7.2. (Once again, I know this is an old
> release but I do not have a newer version installed, and I am only using
> the server for research purposes). If you execute the following statement
> SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004
> 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
More recent releases fail with
regression=# SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004
10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
ERROR: operator does not exist: interval + timestamp without time zone
HINT: No operator matches the given name and argument type(s). You may need to add
explicit type casts.
(There is a timestamp + interval operator, so you could make it work by
flipping around the outer addition.)
It looks like what is happening in 7.2 is the parser is choosing the
closest available operator, which happens to be time + date, and
then coercing the interval and timestamp to time and date respectively.
The former isn't so bad but the latter loses your 10AM data...
Since then (I think in 7.3) we tightened the rules so that
information-losing coercions, such as timestamp to date, won't be
invoked implicitly.
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] Grant Update (Possible bug)?
Am Freitag, 2. Juli 2004 13:20 schrieb Ilir Gashi: > CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50)); > INSERT INTO TEST (ID) VALUES (1); > GRANT UPDATE ON TEST TO TESTUSER; > Connect as TestUser; > UPDATE TEST SET NAME='TEST' WHERE ID=1; > ERROR: test: Permission denied. > UPDATE TEST SET NAME='TEST'; > Executes successfully. According to the letter of the SQL standard, this behavior is not conforming. But PostgreSQL enforces that you need SELECT privilege for columns that you read for the purpose of performing an UPDATE. The reason is that otherwise you could infer a great deal about the data in the table by just looking at the update count. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [BUGS] Grant Update (Possible bug)?
Ilir Gashi <[EMAIL PROTECTED]> writes: > This is a another bug reported for the Firebird 1.0 server. I subsequently > ran it in PostgreSQL 7.2, Oracle 8.0.5 anf MSSQL 7. > Reproducible script: > Connect as pgsql: > CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50)); > INSERT INTO TEST (ID) VALUES (1); > GRANT UPDATE ON TEST TO TESTUSER; > Connect as TestUser; > UPDATE TEST SET NAME='TEST' WHERE ID=1; > ERROR: test: Permission denied. > UPDATE TEST SET NAME='TEST'; > Executes successfully. This is not a bug. That UPDATE requires SELECT permission because it makes use of the ID field in the where clause. If you grant someone UPDATE but not SELECT, presumably you want them to be able to insert data but not learn anything about what is in the table. If we allowed such commands then something like UPDATE TEST SET NAME = NAME WHERE ID = 1 could be used to determine whether the table contains a row with ID=1 (by inspecting the reported row count). So it would be a security flaw. The SQL specification also requires this behavior. In SQL92 the Access Rules for say 1) The applicable privileges shall include SELECT for T if CR is contained in any of: a) a immediately contained in a or an ; or b) a immediately contained in an . > So the user can update the whole table but not specific columns. Is this a > bug or as specified (I read briefly the Reference Guide and I didn't see > this highlited anywhere, but I may have missed it.). Firebird and MSSQL > have the same behaviour as PostgreSQL (Firebird have acknowledged this as a > bug, I haven't checked on MSSQL website yet.) It's not a bug. Please withdraw the complaint against Firebird. > In Oracle 8.0.5 both updates execute sucessfully. Oracle is a very poor reference for SQL-spec-compliant behavior :-( regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] timestamp arithmetic (a possible bug?)
On Fri, 2 Jul 2004, Tom Lane wrote:
> Ilir Gashi <[EMAIL PROTECTED]> writes:
> > I saw this behaviour in PostgreSQL 7.2. (Once again, I know this is an old
> > release but I do not have a newer version installed, and I am only using
> > the server for research purposes). If you execute the following statement
>
> > SELECT (CAST('01.01.2004 10:01:00' AS TIMESTAMP) - CAST('01.01.2004
> > 10:00:00' AS TIMESTAMP)) + CAST('01.01.2004 10:00:00' AS TIMESTAMP);
>
> (There is a timestamp + interval operator, so you could make it work by
> flipping around the outer addition.)
Should we be providing an interval + timestamp operator as well since it
looks like the spec implies both orderings should work?
---(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
Re: [BUGS] timestamp arithmetic (a possible bug?)
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Fri, 2 Jul 2004, Tom Lane wrote: >> (There is a timestamp + interval operator, so you could make it work by >> flipping around the outer addition.) > Should we be providing an interval + timestamp operator as well since it > looks like the spec implies both orderings should work? If you see spec support for it, then yes ... where do you read that exactly? 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] Possible bug?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Am Donnerstag, 1. Juli 2004 23:10 schrieb Tom Lane: >> Right. The reason PG doesn't error is that we have an implicit cast >> from integer to text. I've opined before that we should mark most >> cross-type-category casts as explicit-only ... they are just too prone >> to give unexpected answers, as in this case. > It seems we're just opining in circles. Why not do it? Is anyone objecting? IIRC there were objections the last time it was seriously proposed, basically of the form "but that will break my application which relies on writing so-and-so without a cast". We did get as far as removing all the implicit cross-category coercions except these casts to text: bigint | text smallint| text integer | text real| text double precision| text numeric | text oid | text date| text time without time zone | text time with time zone | text timestamp without time zone | text timestamp with time zone| text interval| text but these seem to have a constituency :-( regards, tom lane ---(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] Grant Update (Possible bug)?
On Jul 2 2004, Tom Lane wrote: Ilir Gashi <[EMAIL PROTECTED]> writes: > This is a another bug reported for the Firebird 1.0 server. I > subsequently ran it in PostgreSQL 7.2, Oracle 8.0.5 anf MSSQL 7. > Reproducible script: > Connect as pgsql: > CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50)); > INSERT INTO TEST (ID) VALUES (1); > GRANT UPDATE ON TEST TO TESTUSER; > Connect as TestUser; > UPDATE TEST SET NAME='TEST' WHERE ID=1; > ERROR: test: Permission denied. > UPDATE TEST SET NAME='TEST'; > Executes successfully. This is not a bug. That UPDATE requires SELECT permission because it makes use of the ID field in the where clause. If you grant someone UPDATE but not SELECT, presumably you want them to be able to insert data but not learn anything about what is in the table. If we allowed such commands then something like UPDATE TEST SET NAME = NAME WHERE ID = 1 could be used to determine whether the table contains a row with ID=1 (by inspecting the reported row count). So it would be a security flaw. The SQL specification also requires this behavior. In SQL92 the Access Rules for say 1) The applicable privileges shall include SELECT for T if CR is contained in any of: a) a immediately contained in a or an ; or b) a immediately contained in an . > So the user can update the whole table but not specific columns. Is > this a bug or as specified (I read briefly the Reference Guide and I > didn't see this highlited anywhere, but I may have missed it.). > Firebird and MSSQL have the same behaviour as PostgreSQL (Firebird have > acknowledged this as a bug, I haven't checked on MSSQL website yet.) It's not a bug. Please withdraw the complaint against Firebird. > In Oracle 8.0.5 both updates execute sucessfully. Oracle is a very poor reference for SQL-spec-compliant behavior :-( regards, tom lane Thanks for the quick reply. I also suspected that it was not a bug, but was confused by The Firebird bug-tracker at SourceForge who had marked it as an 'Initial bug', and became even more confused from the behaviour of Oracle. Thanks for the clarification. Best regards, Ilir Ilir Gashi PhD Student Centre for Software Reliability City University Northampton Square, London EC1V 0HB email: [EMAIL PROTECTED] website: http://www.csr.city.ac.uk/csr_city/staff/gashi/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] timestamp arithmetic (a possible bug?)
On Fri, 2 Jul 2004, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Fri, 2 Jul 2004, Tom Lane wrote: > >> (There is a timestamp + interval operator, so you could make it work by > >> flipping around the outer addition.) > > > Should we be providing an interval + timestamp operator as well since it > > looks like the spec implies both orderings should work? > > If you see spec support for it, then yes ... where do you read that > exactly? SQL92 (draft) 4.5.3 Operators involving datetimes and intervals (the table appears to be the same in SQL99 4.7.3) The list given there is: datetime - datetime -> interval datetime +,- interval -> datetime interval + datetime -> datetime interval +,- interval -> interval interval *,/ numeric -> interval numeric * interval -> interval ---(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
Re: [BUGS] Possible bug?
On Fri, Jul 02, 2004 at 10:27:35 -0400, Tom Lane <[EMAIL PROTECTED]> wrote: > > IIRC there were objections the last time it was seriously proposed, > basically of the form "but that will break my application which relies > on writing so-and-so without a cast". We did get as far as removing all > the implicit cross-category coercions except these casts to text: > > bigint | text > smallint| text > integer | text > real| text > double precision| text > numeric | text > oid | text > date| text > time without time zone | text > time with time zone | text > timestamp without time zone | text > timestamp with time zone| text > interval| text > > but these seem to have a constituency :-( I think it is reasoable to expect people to use an explicit cast when doing these conversions. These are not conversions that you would normally want to do. And even if you do, you need to be very concerned about how the converted text looks for different ranges of source data values. Allowing these makes it harder to notice mistakes. And I expect that overall the hiding mistakes case occurs a lot more than the I meant to do that but didn't want to type ::text case. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [BUGS] Grant Update (Possible bug)?
Me wrote: > Am Freitag, 2. Juli 2004 13:20 schrieb Ilir Gashi: > > CREATE TABLE TEST(ID INTEGER,NAME VARCHAR(50)); > > INSERT INTO TEST (ID) VALUES (1); > > GRANT UPDATE ON TEST TO TESTUSER; > > Connect as TestUser; > > UPDATE TEST SET NAME='TEST' WHERE ID=1; > > ERROR: test: Permission denied. > > UPDATE TEST SET NAME='TEST'; > > Executes successfully. > > According to the letter of the SQL standard, this behavior is not > conforming. OK, I was wrong, the behavior is correct, but the specification was hidden somewhere in the subclauses. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] Possible bug?
Bruno Wolff III <[EMAIL PROTECTED]> writes: > Tom Lane <[EMAIL PROTECTED]> wrote: >> but these seem to have a constituency :-( > I think it is reasoable to expect people to use an explicit cast when > doing these conversions. That's what I think, but I lost the argument last time round... I think it would be easier to sell making these changes as part of a move that creates non-implicit casts to/from text for *all* datatypes (implemented on top of their I/O routines). So I don't plan on making the proposal again until I or somebody else have time to write some infrastructure for that. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] timestamp arithmetic (a possible bug?)
Stephan Szabo <[EMAIL PROTECTED]> writes: >>> Should we be providing an interval + timestamp operator as well since it >>> looks like the spec implies both orderings should work? >> >> If you see spec support for it, then yes ... where do you read that >> exactly? > SQL92 (draft) 4.5.3 Operators involving datetimes and intervals (the table > appears to be the same in SQL99 4.7.3) Yeah. It looks like we have most of these, but would need to add interval + date interval + timetz interval + timestamp interval + timestamptz and for consistency integer + date Curiously, we do have interval + time without time zone ... I guess Tom Lockhart overlooked these when he was working in the area. I notice also that date - date yields an integer (ie, number of days) where I think that strict spec compliance would mandate yielding an interval instead. I'm uneager to change this though. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Grant Update (Possible bug)?
Peter Eisentraut <[EMAIL PROTECTED]> writes: > According to the letter of the SQL standard, this behavior is not conforming. > But PostgreSQL enforces that you need SELECT privilege for columns that you > read for the purpose of performing an UPDATE. Why do you think it's non-conformant? AFAICS SQL92 section 6.4 Access Rule 1(a) requires exactly this behavior. I notice that Annex E item 5 points this out as an incompatibility with SQL89 ... maybe Oracle is still on SQL89 ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Grant Update (Possible bug)?
* Tom Lane ([EMAIL PROTECTED]) wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > According to the letter of the SQL standard, this behavior is not conforming. > > But PostgreSQL enforces that you need SELECT privilege for columns that you > > read for the purpose of performing an UPDATE. > > Why do you think it's non-conformant? AFAICS SQL92 section 6.4 reference> Access Rule 1(a) requires exactly this behavior. > > I notice that Annex E item 5 points this out as an incompatibility with > SQL89 ... maybe Oracle is still on SQL89 ... It would seem reasonable, then, that a grant of update privilege would imply a grant of select privilege. Currently this isn't the case. Stephen signature.asc Description: Digital signature
