Open items

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:

Here are our open items. How hard are we going to be about the cutoff
date? Do we give people the weekend to complete some items?

Changes
-------

[...]

I'm not sure what else Tom's already working on wrt roles, but I plan to
send in the reasonably small alter-owner permission requirement changes
tommorow. We really should also support SET ROLE. Perhaps if I have
time I'll go through the SQL spec looking at the specific requirements
of 'Basic Role Support' and 'Extended Role Support' and come up with
what we've got, what we're missing, and then we can decide which are
features, which are bugfixes, and what we can claim in the docs.

Thanks,

Stephen

How about enable/disable triggers?

From TODO:
Allow triggers to be disabled.

http://momjian.postgresql.org/cgi-bin/pgtodo?trigger

I think this is good for COPY performance improvement.

Now I have user functions to enable/disable triggers, not DDL.
It modifies system tables.
But I can rewrite this as a DDL. (ALTER TABLE?)

Any comments?

Bruce Momjian wrote:

Here are our open items. How hard are we going to be about the cutoff
date? Do we give people the weekend to complete some items?

---------------------------------------------------------------------------

PostgreSQL 8.1 Open Items
=========================

Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems.

Changes
-------
integrated auto-vacuum (Alvaro)
ICU locale patch?
Win32 signal handling patch (Magnus)
column-level triggers (Greg)
interval improvements (Michael Glaesemann)
move rtree_gist into core?
config file I/O? (Adreas)
terminate backend fix?
dbsize functions from /contrib? (Andreas)
fix pg_autovacuum O(n^2) behavior
remove wal siblings guc vars?
COPY performance improvements (greenplum)
shared dependency (Alvaro)
concurrent vacuum (Hannu)
make pg_dump E''escape safe
table partitionaing (Simon)
WAL improvements (Simon)

Documentation
-------------

Fixed Since Last Beta
---------------------

--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

On Tue, 28 Jun 2005, Bruce Momjian wrote:

Here are our open items. How hard are we going to be about the cutoff
date? Do we give people the weekend to complete some items?

Sounds reasonable to me ... Always hate doing stuff like this on a Friday
myself ...

---------------------------------------------------------------------------

PostgreSQL 8.1 Open Items
=========================

Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems.

Changes
-------
integrated auto-vacuum (Alvaro)
ICU locale patch?
Win32 signal handling patch (Magnus)
column-level triggers (Greg)
interval improvements (Michael Glaesemann)
move rtree_gist into core?
config file I/O? (Adreas)
terminate backend fix?
dbsize functions from /contrib? (Andreas)
fix pg_autovacuum O(n^2) behavior
remove wal siblings guc vars?
COPY performance improvements (greenplum)
shared dependency (Alvaro)
concurrent vacuum (Hannu)
make pg_dump E''escape safe
table partitionaing (Simon)
WAL improvements (Simon)

Documentation
-------------

Fixed Since Last Beta
---------------------

-- 
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664

Stephen Frost <sfrost@snowman.net> writes:

* Bruce Momjian (pgman@candle.pha.pa.us) wrote:

Here are our open items. How hard are we going to be about the cutoff
date? Do we give people the weekend to complete some items?

I'm not sure what else Tom's already working on wrt roles,

Right at the moment I'm focused on cleaning up serious issues in the
patch-as-committed (ie, the kind of stuff that might make Marc claim
this should get reverted ;-)). I still need to re-read user.c and
acl.c in some detail --- I'm concerned about the locking rules and
ensuring that circular role references can't be created; and I think
the permissions checking during CreateRole is probably wrong; and
I really want to separate superuser from createrole properly. And
information_schema is probably a few bricks shy of a load yet. After
that, there's pg_dump support, documentation, and regression tests.
Nothing terribly critical, but we'd require most of this stuff from
anyone else submitting a patch now, so I feel on the hook to fix it
having committed the patch prematurely.

... We really should also support SET ROLE. Perhaps if I have
time I'll go through the SQL spec looking at the specific requirements
of 'Basic Role Support' and 'Extended Role Support' and come up with
what we've got, what we're missing, and then we can decide which are
features, which are bugfixes, and what we can claim in the docs.

Yes, that'd be a fine thing to do.

regards, tom lane

Changes
-------
integrated auto-vacuum (Alvaro)
ICU locale patch?

That would be Palle, and he's said he thinks he can have it in place in
time. I'll have to update it for win32 build specifics after that, but
that should be ok after the freeze, right?

Please consider removing the question mark ;-)

The latest version of the patch is at
http://people.freebsd.org/~girgen/postgresql-icu/readme.html. It needs
to be updated for 8.1.

//Magnus

Satoshi Nagayasu wrote:

How about enable/disable triggers?

From TODO:
Allow triggers to be disabled.

http://momjian.postgresql.org/cgi-bin/pgtodo?trigger

I think this is good for COPY performance improvement.

Now I have user functions to enable/disable triggers, not DDL.
It modifies system tables.
But I can rewrite this as a DDL. (ALTER TABLE?)

Yea, it is a TODO item, and should be pretty straight-forward to code,
so sure, go ahead.

It has to be something that is super-user-only.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Marc G. Fournier wrote:

On Tue, 28 Jun 2005, Bruce Momjian wrote:

Here are our open items. How hard are we going to be about the cutoff
date? Do we give people the weekend to complete some items?

Sounds reasonable to me ... Always hate doing stuff like this on a Friday
myself ...

Yep. This gives us a few wind-down days, so folks, keep working and
send in stuff by this Monday. We would like to see an intermediate
patch before Monday so we know you are working on stuff though. And
once the patches are submitted, we will work to get them integrated into
CVS, but it might take a few weeks to happen because some patches might
need major work (we hope not).

Also, remember that the weeks after feature freeze get very busy as we
push to get everything into CVS, and people start getting worried their
feature will not make it.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Magnus Hagander wrote:

Changes
-------
integrated auto-vacuum (Alvaro)
ICU locale patch?

That would be Palle, and he's said he thinks he can have it in place in
time. I'll have to update it for win32 build specifics after that, but
that should be ok after the freeze, right?

Yes, unless the Win32 adjustments are major.

Please consider removing the question mark ;-)

Done.

The latest version of the patch is at
http://people.freebsd.org/~girgen/postgresql-icu/readme.html. It needs
to be updated for 8.1.

OK.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

... We really should also support SET ROLE. Perhaps if I have
time I'll go through the SQL spec looking at the specific requirements
of 'Basic Role Support' and 'Extended Role Support' and come up with
what we've got, what we're missing, and then we can decide which are
features, which are bugfixes, and what we can claim in the docs.

Yes, that'd be a fine thing to do.

Here's the results of this. I think we're pretty close to having both
"Basic roles" and "Extended roles" personally. For 'Basic roles' we
need SET ROLE and some information schema tables. For 'Extended roles'
I think we need '<default option> CURRENT_ROLE' (if this isn't already
taken care of because CURRENT_ROLE is a function?), REVOKE ROLE w/
CASCADE drop behavior. There were a few other things in 'Extended
roles' that I didn't entirely follow but think we probably meet or would
meet with the above mentioned items...

Here's the complete list. * = Already supported, ? = Might be
supported, others are to-do items.

Basic roles, Feature T331
* <role name>
* CREATE ROLE
* GRANT ROLE
* DROP ROLE
* REVOKE ROLE
SET ROLE
INFORMATION_SCHEMA.ADMINISTRABLE_ROLE_AUTHORIZATIONS
INFORMATION_SCHEMA.APPLICABLE_ROLES
INFORMATION_SCHEMA.ENABLED_ROLES
INFORMATION_SCHEMA.ROLE_COLUMN_GRANTS
INFORMATION_SCHEMA.ROLE_ROUTINE_GRANTS
INFORMATION_SCHEMA.ROLE_TABLE_GRANTS
INFORMATION_SCHEMA.ROLE_TABLE_METHOD_GRANTS
INFORMATION_SCHEMA.ROLE_USAGE_GRANTS
INFORMATION_SCHEMA.ROLE_UDT_GRANTS

INFORMATION_SCHEMA.ADMIN_ROLE_AUTHS
INFORMATION_SCHEMA.ROLE_ROUT_GRANTS

Extended roles, Feature T332
(Implies Basic roles)
? <default option> CURRENT_ROLE
* CURRENT_ROLE
* CREATE ROLE w/ ADMIN OPTION
* REVOKE ROLE w/ <revoke option extension> GRANT OPTION FOR
(GRANT ADMIN FOR?)
REVOKE ROLE w/ <drop behavior> CASCADE

<revoke statement> containing <privileges> which contain an
<object name> where the owner of the SQL-schema that is
specified explicitly or implicitly in the <object name>
is not the current authorization identifier
(superuser()?)

<revoke statement> with privilege descriptor PD which satisfies:
(a) PD identifies the object identified by <object name> simply
contained in <privileges> contained in the <revoke statement>
(CURRENT_ROLE?)
(b) PD identifies the <grantee> identified by any <grantee> simply
contained in <revoke statement> and that <grantee> does not
identify the owner of the SQL-schema that is specified
explicitly or implicitly in the <object name> simply contained
in <privileges> contained in the <revoke statement>
(CURRENT_USER?)
(c) PD identifies the action identified by the <action> simply
contained in <privileges> contained in the <revoke statement>
(<drop bahavior> ?)
(d) PD indicates that the privilege is grantable
(GRANT ADMIN FOR?)

Thanks,

Stephen

Stephen Frost <sfrost@snowman.net> writes:

Here's the results of this. I think we're pretty close to having both
"Basic roles" and "Extended roles" personally. For 'Basic roles' we
need SET ROLE and some information schema tables.

The information schema views already exist, although I suspect the view
definitions may need more work.

For 'Extended roles'
I think we need '<default option> CURRENT_ROLE' (if this isn't already
taken care of because CURRENT_ROLE is a function?),

Yes, it is.

REVOKE ROLE w/CASCADE drop behavior.

I was just about to quiz you about the lack of any use of the grantor
column in pg_auth_members. I suppose that revoking a membership that
was held WITH ADMIN OPTION ought to lead to searching for and destroying
all memberships granted by that ID (possibly indirectly?). DROP ROLE
has got the same problem.

Also, I've been working on converting the CREATEROLE privilege into
something usable, and am about ready to commit that. The way it works
is that CREATEROLE lets you do anything that user.c formerly required
superuser for, *except* that you have to be superuser to mess with
superuser roles in any way. This all seems fine as far as it goes,
but should revoking CREATEROLE lead to dropping grants that were made
by means of that power? Not sure. We ended up with some fairly
carefully crafted compromises for ACL representation of grants made
by superusers, and I think we'll likely need to think hard about it
for role memberships too.

regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Stephen Frost <sfrost@snowman.net> writes:

Here's the results of this. I think we're pretty close to having both
"Basic roles" and "Extended roles" personally. For 'Basic roles' we
need SET ROLE and some information schema tables.

The information schema views already exist, although I suspect the view
definitions may need more work.

Ok.

REVOKE ROLE w/CASCADE drop behavior.

I was just about to quiz you about the lack of any use of the grantor
column in pg_auth_members. I suppose that revoking a membership that
was held WITH ADMIN OPTION ought to lead to searching for and destroying
all memberships granted by that ID (possibly indirectly?). DROP ROLE
has got the same problem.

Not sure about indirectly, but I think a 'drop role' should check for
existing entries where that role is the 'grantor' and fail if any exist
unless 'cascade' is given. I think 'drop role' at one point (when it
was still seq-scan based) dropped based on the 'grantor' field
(regardless of 'cascade' or not). When I converted it to using an index
apparently I missed that issue, sorry about that. Seems like that'd
mean it'd have to go back to seq-scan based again. :/

Also, I've been working on converting the CREATEROLE privilege into
something usable, and am about ready to commit that. The way it works
is that CREATEROLE lets you do anything that user.c formerly required
superuser for, *except* that you have to be superuser to mess with
superuser roles in any way. This all seems fine as far as it goes,
but should revoking CREATEROLE lead to dropping grants that were made
by means of that power? Not sure. We ended up with some fairly
carefully crafted compromises for ACL representation of grants made
by superusers, and I think we'll likely need to think hard about it
for role memberships too.

I'd tend to think that revoking CREATEROLE wouldn't drop grants which
were made using it. I do agree that it needs to be thought out more
carefully than I believe it has been so far though.

Thanks,

Stephen

Bruce,

I have another patch for the TODO item.

From TODO item:

Add ability to monitor the use of temporary sort files

As I mentioned before, I created a sort statistics patch.

http://archives.postgresql.org/pgsql-hackers/2004-09/msg00380.php

Now my patch can work with 7.4.6 and it creates new system view,
called pg_stat_sorts.

sort=# select * from pg_stat_sorts ;
datname | heap_all | index_all | heap_tape | index_tape | max_size
-----------+----------+-----------+-----------+------------+----------
sort | 11 | 0 | 3 | 0 | 11141120
template1 | 2 | 0 | 0 | 0 | 792
template0 | 0 | 0 | 0 | 0 | 0
(3 rows)

Is this enough for this TODO?
Any comments?
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

Satoshi,

sort=# select * from pg_stat_sorts ;
  datname  | heap_all | index_all | heap_tape | index_tape | max_size
-----------+----------+-----------+-----------+------------+----------
 sort      |       11 |         0 |         3 |          0 | 11141120
 template1 |        2 |         0 |         0 |          0 |      792
 template0 |        0 |         0 |         0 |          0 |        0

Good for me, if you explain the column names?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:

Satoshi,

sort=# select * from pg_stat_sorts ;
� datname �| heap_all | index_all | heap_tape | index_tape | max_size

Good for me, if you explain the column names?

I was wondering about that too ... temporary sort files haven't got
indexes ...

regards, tom lane

Tom Lane wrote:

Josh Berkus <josh@agliodbs.com> writes:

Satoshi,

sort=# select * from pg_stat_sorts ;
� datname �| heap_all | index_all | heap_tape | index_tape | max_size

Good for me, if you explain the column names?

I was wondering about that too ... temporary sort files haven't got
indexes ...

Sorry. It's my misunderstanding. index_tape will be zero forever...

My patch counts inittapes(), tuplesort_begin_heap() and tuplesort_begin_index(),
and collect them, and sum them through the stat collector.

I'm ready to rewrite if it is required.

Thanks.
--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

Satoshi Nagayasu <nagayasus@nttdata.co.jp> writes:

My patch counts inittapes(), tuplesort_begin_heap() and
tuplesort_begin_index(), and collect them, and sum them through the
stat collector.

Hm, that doesn't seem like quite the right level to be counting at.
Shouldn't you be hacking fd.c to count operations on FD_XACT_TEMPORARY
files?

regards, tom lane

Tom Lane wrote:

My patch counts inittapes(), tuplesort_begin_heap() and
tuplesort_begin_index(), and collect them, and sum them through the
stat collector.

Hm, that doesn't seem like quite the right level to be counting at.
Shouldn't you be hacking fd.c to count operations on FD_XACT_TEMPORARY
files?

Why do you think so?
I don't see tuplesort.c is good or not.

But all code of sort operations are in tuplesort.c.
So I thought it is a good place to count them up.

Of course, I can move my code to fd.c if it will be reasonable.

--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>

Satoshi Nagayasu <nagayasus@nttdata.co.jp> writes:

Tom Lane wrote:

Hm, that doesn't seem like quite the right level to be counting at.
Shouldn't you be hacking fd.c to count operations on FD_XACT_TEMPORARY
files?

Why do you think so?
I don't see tuplesort.c is good or not.

But all code of sort operations are in tuplesort.c.
So I thought it is a good place to count them up.

The TODO item is about counting all temporary files, not sorts in
particular. Or at least that's what I thought it meant.

regards, tom lane

The TODO item is about counting all temporary files, not sorts in
particular. Or at least that's what I thought it meant.

If the DBA have to improve the performance,
DBA will need to know about:

- Which SQL generate a disk sort?
- Size of sorts.
- Changing 'work_mem' value can reduce disk sorts?

So, sometime DBA want to know about 'sorts', not temp files.

However, I understand DBA must also care about temp files usage.

I think both are required.
DBA need to know about 'sorts' and 'temp files'.

--
NAGAYASU Satoshi <nagayasus@nttdata.co.jp>