Re: SQL:2011 application time - Mailing list pgsql-hackers
From | Paul Jungwirth |
---|---|
Subject | Re: SQL:2011 application time |
Date | |
Msg-id | fc6f2f08-e62b-4070-986f-d8eb32bd5ed3@illuminatedcomputing.com Whole thread Raw |
In response to | Re: SQL:2011 application time (jian he <jian.universality@gmail.com>) |
Responses |
Re: SQL:2011 application time
Re: SQL:2011 application time |
List | pgsql-hackers |
Thank you again for such thorough reviews! On Thu, Nov 16, 2023 at 11:12 PM jian he <jian.universality@gmail.com> wrote: > UPDATE FOR PORTION OF, may need insert privilege. We also need to document this. > Similarly, we also need to apply the above logic to DELETE FOR PORTION OF. I don't think UPDATE/DELETE FOR PORTION OF is supposed to require INSERT permission. Notionally the INSERTs are just to preserve what was there already, not to add new data. The idea is that a temporal table is equivalent to a table with one row for every "instant", i.e. one row per microsecond/second/day/whatever-time-resolution. Of course that would be too slow, so we use PERIODs/ranges instead, but the behavior should be the same. Date's book has a good discussion of this idea. I also checked the SQL:2011 draft standard, and there is a section called Access Rules in Part 2: SQL/Foundation for UPDATE and DELETE statements. Those sections say you need UPDATE/DELETE privileges, but say nothing about needing INSERT privileges. That is on page 949 and 972 of the PDFs from the "SQL:20nn Working Draft Documents" link at [1]. If someone has a copy of SQL:2016 maybe something was changed, but I would be surprised. I also checked MariaDB and IBM DB2, the only two RDBMSes that implement FOR PORTION OF to my knowledge. (It is not in Oracle or MSSQL.) I created a table with one row, then gave another user privileges to SELECT & UPDATE, but not INSERT. In both cases, that user could execute an UPDATE FOR PORTION OF that resulted in new rows, but could not INSERT genuinely new rows. [2,3] So instead of changing this I've updated the documentation to make it explicit that you do not need INSERT privilege to use FOR PORTION OF. I also documented which triggers will fire and in which order. > + <para> > + If the table has a <link > linkend="ddl-periods-application-periods">range column > + or <literal>PERIOD</literal></link>, you may supply a > > should be > > + <para> > + If the table has a range column or <link > linkend="ddl-periods-application-periods"> > + <literal>PERIOD</literal></link>, you may supply a > > similarly the doc/src/sgml/ref/delete.sgml the link reference also broken. Okay, changed. > "given interval", "cut off" these words, imho, feel not so clear. > We also need a document that: > "UPDATE FOR PORTION OF" is UPDATE and INSERT (if overlaps). > If the "UPDATE FOR PORTION OF" range overlaps then > It will invoke triggers in the following order: before row update, > before row insert, after row insert. after row update. Okay, reworked the docs for this. > src/test/regress/sql/for_portion_of.sql > You only need to create two triggers? > since for_portion_of_trigger only raises notice to output the triggers > meta info. Changed. v19 patch series attached, rebased to a11c9c42ea. [1] https://web.archive.org/web/20230923221106/https://www.wiscorp.com/SQLStandards.html [2] MariaDB test: First create a table as the root user: ``` create table t (id int, ds date, de date, name text, period for valid_at (ds, de)); insert into t values (1, '2000-01-01', '2001-01-01', 'foo'); ``` and give another user select & update privlege (but not insert): ``` create database paul; use paul; create user 'update_only'@'localhost' identified by 'test'; grant select, update on paul.t to 'update_only'@'localhost'; flush privileges; ``` Now as that user: ``` mysql -uupdate_only -p use paul; -- We can update the whole record: update t for portion of valid_at from '2000-01-01' to '2001-01-01' set name = 'bar'; -- We can update a part of the record: update t for portion of valid_at from '2000-01-01' to '2000-07-01' set name = 'baz'; select * from t; +------+------------+------------+------+ | id | ds | de | name | +------+------------+------------+------+ | 1 | 2000-01-01 | 2000-07-01 | baz | | 1 | 2000-07-01 | 2001-01-01 | bar | +------+------------+------------+------+ -- We cannot insert: insert into t values (2, '2000-01-01', '2001-01-01' 'another'); ERROR 1142 (42000): INSERT command denied to user 'update_only'@'localhost' for table `paul`.`t` ``` [3] IBM DB2 test: ``` mkdir ~/local/db2 cd ~/local/db2 tar xzvf ~/Downloads/v11.5.9_linuxx64_server_dec.tar.gz cd server_dev ./db2_install # should put something at ~/sqllib source ~/sqllib/db2profile db2start # but I got "The database manager is already active." db2 create database paul -- first time only, note no semicolon connect to paul create table t (id integer, ds date not null, de date not null, name varchar(4000), period business_time (ds, de)); insert into t values (1, '2000-01-01', '2001-01-01', 'foo'); grant connect on database to user james; grant select, update on t to user james; ``` Now as james: ``` source ~paul/sqllib/db2profile db2 connect to paul select * from paul.t; update paul.t for portion of business_time from '2000-01-01' to '2000-06-01' set name = 'bar'; DB20000I The SQL command completed successfully. select * from paul.t; insert into paul.t values (2, '2000-01-01', '2001-01-01', 'bar'); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0551N The statement failed because the authorization ID does not have the required authorization or privilege to perform the operation. Authorization ID: "JAMES". Operation: "INSERT". Object: "PAUL.T". SQLSTATE=42501 ``` Yours, -- Paul ~{:-) pj@illuminatedcomputing.com
Attachment
pgsql-hackers by date: