Re: [HACKERS] Moving sequences to another schema
Alvaro Herrera [EMAIL PROTECTED] writes: I think this is done by AddRelationRawConstraints. You'd have to get the parsetree of the default expression. I think you could get that by applying raw_parser() to pg_attrdef.adsrc. Not adsrc --- that's not trustworthy. In practice I think you could just assume you know what the default expression ought to be, and store a new one without looking at the old. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] For review: dbsize patch
-Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: 28 June 2005 00:58 To: Dave Page Cc: PostgreSQL-development Subject: Re: [HACKERS] For review: dbsize patch Dave Page wrote: The attached patch is an update of the dbsize integration patch discussed last week. This version includes the following functions: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) The only remaining function that last week's brief discussion indicated was required is a replacement for total_relation_size() (or pg_table_size() as it might now be called). I didn't realise until a few minutes ago that this function (which is actually broken because it doesn't handle schemas) was only committed a couple of months ago (v1.5, http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dbsiz e/dbsize.s ql.in) and has therefore never been in a release version. Uh, do any of these include the index size? TOAST size? No, only total_relation_size() does that. So should we include this new feature, and if so, how is it best added - rewrite in C, or one long line in pg_proc? I would follow whatever we do in pg_proc now. There are a couple of SQL functions in there, but they are nowhere near as long as this one. I'll look at implementing it in C. Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] contrib/rtree_gist into core system?
I think we still have a serious problem with multicolumn indexes. As they stand they're basically only indexes on the first column. The later columns are not used to determine page splits. 1. In your meaning, btree has bad split algorithm too. Look at _bt_compare, if first keys on page are unique the the later keys will not be compared ;) 2. About rtree interface: it's possible to write GiST-RTree layer compatibility interface. User's interface will just copied from RTree, and layer will translate it to GiST interface. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] #ifdef NOT_USED
(Sorry for the delayed response. I just got home from Germany after my visit to Linuxtag, and I'm catching up with my -hackers email now.) At 2005-06-25 09:30:17 -0400, pgman@candle.pha.pa.us wrote: Hi, i have found several #ifdef NOT_USED marked code... We keep such blocks of code around in case we might need to use it some day. I think that's a bad idea. Unused code should be removed with a suitable CVS checkin comment (and perhaps a comment where the code was), not left to clutter live code. That's what version control is for. -- ams ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes
Tom Lane [EMAIL PROTECTED] wrote:
Yeah, this is about what I was afraid of: if you're actually fsyncing
then you get at best one commit per disk revolution, and the negotiation
with the OS is down in the noise.
If we disable writeback-cache and use open_sync, the per-page writing
behavior in WAL module will show up as bad result. O_DIRECT is similar
to O_DSYNC (at least on linux), so that the benefit of it will disappear
behind the slow disk revolution.
In the current source, WAL is written as:
for (i = 0; i N; i++) { write(buffers[i], BLCKSZ); }
Is this intentional? Can we rewrite it as follows?
write(buffers[0], N * BLCKSZ);
In order to achieve it, I wrote a 'gather-write' patch (xlog.gw.diff).
Aside from this, I'll also send the fixed direct io patch (xlog.dio.diff).
These two patches are independent, so they can be applied either or both.
I tested them on my machine and the results as follows. It shows that
direct-io and gather-write is the best choice when writeback-cache is off.
Are these two patches worth trying if they are used together?
| writeback | fsync= | fdata | open_ | fsync_ | open_
patch | cache | false | sync | sync | direct | direct
+---++---+---++-
direct io | off | 124.2 | 105.7 | 48.3 | 48.3 | 48.2
direct io | on| 129.1 | 112.3 | 114.1 | 142.9 | 144.5
gather-write| off | 124.3 | 108.7 | 105.4 | (N/A) | (N/A)
both| off | 131.5 | 115.5 | 114.4 | 145.4 | 145.2
- 20runs * pgbench -s 100 -c 50 -t 200
- with tuning (wal_buffers=64, commit_delay=500, checkpoint_segments=8)
- using 2 ATA disks:
- hda(reiserfs) includes system and wal.
- hdc(jfs) includes database files. writeback-cache is always on.
---
ITAGAKI Takahiro
NTT Cyber Space Laboratories
xlog.dio.diff
Description: Binary data
xlog.gw.diff
Description: Binary data
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Wierd panic with 7.4.7
Joshua D. Drake [EMAIL PROTECTED] writes: 2005-06-27 16:37:53 ERROR: could not send data to client: Broken pipe 2005-06-27 16:37:53 PANIC: cannot abort transaction 146017848, it was already committed A reasonable guess as to what happened there is: 1. Client process dies just as server is committing a transaction on its behalf. 2. For some reason, server tries to send a message to client while it's doing post-commit cleanup (before it gets to the point of resetting its state to show that it's not in the transaction anymore). 3. Kernel rejects message, causing elog(ERROR), causing entry to AbortTransaction, causing above panic. There are a couple of big problems with this theory, though. In the first place, there aren't any messages sent to the client during post-commit; unless possibly it's an error message due to a failure during post-commit, and that should have shown up in the server log. In the second place, we don't treat communication failures as ERRORs, so how did step 3 happen? Do you know what the dead client was doing? Can you reproduce this? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] For review: Server instrumentation patch
Thanks . /D -Original Message- From: Andreas Pflug[EMAIL PROTECTED] Sent: 27/06/05 22:41:35 To: Dave Pagedpage@vale-housing.co.uk Cc: PostgreSQL-developmentpgsql-hackers@postgresql.org, Bruce Momjianpgman@candle.pha.pa.us Subject: Re: For review: Server instrumentation patch Dave Page wrote: As per Bruce's request, here's a copy of Andreas' server instrumentation patch for review. I've separated out the dbsize stuff and pg_terminate_backend is also not included. This version was generated against CVS today. As far as I can tell from review of comments made back to pre-8.0, all security and other concerns raised have been addressed. Regards, Dave. (Andreas, can you eyeball this to make sure I didn't miss anything or clobber anything I shouldn't have when I trimmed the dbsize stuff please) Seems fine. Regards, Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
Neil Conway wrote: I agree the current parser is a hack, but it's difficult to see how else it could be implemented. Since the lexical structure of SQL/PSM seems to be about the same as the main SQL, maybe you could get away with having the main parser just accepting any tokens at the point where the function body belongs and make it count BEGIN's and END's or whatever nesting elements there might be. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Occupied port warning
During a recent training session I was reminded about a peculiar misbehavior that recent PostgreSQL releases exhibit when the TCP port they are trying to bind to is occupied: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost The trainees found this behavior somewhat unuseful. Can someone remind me why this is not an error? Does any other server software behave this way? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] For review: Server instrumentation patch
[Resent as the list seems to have rejected yesterdays attempt] As per Bruce's request, here's a copy of Andreas' server instrumentation patch for review. I've separated out the dbsize stuff and pg_terminate_backend is also not included. This version was generated against CVS today. As far as I can tell from review of comments made back to pre-8.0, all security and other concerns raised have been addressed. Regards, Dave. instrumentation.tar.gz Description: instrumentation.tar.gz ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] commit_delay, siblings
Just a warning, because I might bring it up after feature freeze. If we yank them ( and I agree) I think we have to do it before feature freeze. I believe that we have consensus to yank them. Hans says that he did extensive testing back as far as 7.4 and the options had no effect. My opinion is, we'd better test with at least 8.0, or even better with current. I think I can do the testing after Jul 1 if those features are remained. I have a dual Xeon system with a 15000RPM SCSI disk system in my office. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
On 6/28/2005 5:55 AM, Peter Eisentraut wrote: Neil Conway wrote: I agree the current parser is a hack, but it's difficult to see how else it could be implemented. Since the lexical structure of SQL/PSM seems to be about the same as the main SQL, maybe you could get away with having the main parser just accepting any tokens at the point where the function body belongs and make it count BEGIN's and END's or whatever nesting elements there might be. Which then would require that SPI gets another interface added that allows to feed in a token sequence instead of a query string. After thinking more about what I wrote yesterday I noticed that we would lose the potential for query plan recompilation after system cache invalidation if we do not keep the queries inside of a PL function in some sort of source code (lexer tokens still are). Jan ---(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
[HACKERS] CVS pg_config --includedir-server broken
The valure returned from pg_config --includedir-server is broken as of CVS. It points to unexistent directory: /home/extra/pgroot-cvs/include/server Correct value would be: /home/extra/pgroot-cvs/include/postgresql/server --strk; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
One thing bytecode would allow us to do is to write a debugger with break points etc. Using a java jvm however is considerable overkill. Dave On 27-Jun-05, at 8:28 PM, Neil Conway wrote: Jonah H. Harris wrote: I don't recommend discussion for this in this thread, but it could also tie in with the packages support we've discussed and (although some may argue this), compiling the PL to bytecode and using that. How would compilation to bytecode help? -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Occupied port warning
Peter Eisentraut said: During a recent training session I was reminded about a peculiar misbehavior that recent PostgreSQL releases exhibit when the TCP port they are trying to bind to is occupied: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost The trainees found this behavior somewhat unuseful. Can someone remind me why this is not an error? Does any other server software behave this way? IIRC, in previous versions any bind failure was fatal, but in 8.0 we decided to be slightly more forgiving and only bail out if we failed to bind at all. cheers andrew ---(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: [HACKERS] Problem with dblink regression test
Jim, you should have a file buildroot/HEAD/lastrun-logs/make.log that shows the link steps for the libraries. Can you either put that file somewhere we can look at it or extract the relevant lines and post in a reply? thanks andrew Jim C. Nasby wrote: I have no clue why the mailling list is eating my original messages, unless it's because I attached a diff to them... in any case, applying http://stats.distributed.net/~buildfarm/patch provides a listing of what all the binaries and libraries in a buildfarm install are linking against. I couldn't figure out a decent way to send that info to pgbuildfarm.org, but http://stats.distributed.net/~buildfarm/libcheck.log is that info for a run. Based on the logfile, it looks like Tom's guess is correct that psql (and other binaries) are linking to the buildfarm libraries, while dblink.so (and other libraries) are linking against the system postgresql libraries. If someone wants to point me in the right direction I'll try and fix this, since I'm guessing it's just a make issue (or maybe a buildfarm issue). Actually, looking at my config (http://stats.distributed.net/~buildfarm/build-farm.conf), could the --with-libraries=/usr/local/lib be the issue, and if so, what's the proper way to handle system libraries (like libintl) living in /usr/local/lib and not /usr/lib? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Dbsize backend integration
The attached patch integrates dbsize functions into the backend, as per discussion on -hackers. The following functions are included: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_table_size(text) - Get table size (including all indexes and toast tables) by name/schema.name pg_table_size(oid) - Get table size (including all indexes and toast tables) by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) This is based on the dbsize contrib module, and previous patches from Andreas Pflug and Ed L. The dbsize module should be removed once this is applied, and the catalog version incremented as I haven't included that in the patch. Regards, Dave. dbsize.c Description: dbsize.c dbsize.patch Description: dbsize.patch ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Dave Cramer wrote:
One thing bytecode would allow us to do is to write a debugger with
break points etc.
We can write debugger with breakpoints without bytecode. Every stmt rec
can have flag if has breakpoints. No problem. I don't see any advance of
bytecode. Maybe, goto stmt is possible.
What is problem? We need synchronous comunication (message) between
backend frontend.
I have idea (in exec_stmt()
CHECK_FOR_INTERRUPTS();
if (stmt-breakpoints)
estate-debug_mode = true;
if (estate-debug_mode)
{
for (;;)
{
rc = request_command();
switch (rc)
{
case 'c': -- continue
estate-debug_mode = false;
break
case 'q':
elog(EXCEPTION, stop debug);
break;
case 'n':
break;
case 'l':
sendstring(line(estate-src,
stmt-lineno));
Please, can somebody help me with protocol enhancing? It is mayor work on
PL/pgSQL debugger (and plperl and plpython too).
Using a java jvm however is considerable overkill.
Dave
On 27-Jun-05, at 8:28 PM, Neil Conway wrote:
Jonah H. Harris wrote:
I don't recommend discussion for this in this thread, but it could
also tie in with the packages support we've discussed and
(although some may argue this), compiling the PL to bytecode and
using that.
How would compilation to bytecode help?
-Neil
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
Pavel,
What do you think you need for enhanced protocol ?
Dave
On 28-Jun-05, at 8:51 AM, Pavel Stehule wrote:
On Tue, 28 Jun 2005, Dave Cramer wrote:
One thing bytecode would allow us to do is to write a debugger with
break points etc.
We can write debugger with breakpoints without bytecode. Every stmt
rec
can have flag if has breakpoints. No problem. I don't see any
advance of
bytecode. Maybe, goto stmt is possible.
What is problem? We need synchronous comunication (message) between
backend frontend.
I have idea (in exec_stmt()
CHECK_FOR_INTERRUPTS();
if (stmt-breakpoints)
estate-debug_mode = true;
if (estate-debug_mode)
{
for (;;)
{
rc = request_command();
switch (rc)
{
case 'c': -- continue
estate-debug_mode = false;
break
case 'q':
elog(EXCEPTION, stop debug);
break;
case 'n':
break;
case 'l':
sendstring(line(estate-src,
stmt-lineno));
Please, can somebody help me with protocol enhancing? It is mayor
work on
PL/pgSQL debugger (and plperl and plpython too).
Using a java jvm however is considerable overkill.
Dave
On 27-Jun-05, at 8:28 PM, Neil Conway wrote:
Jonah H. Harris wrote:
I don't recommend discussion for this in this thread, but it could
also tie in with the packages support we've discussed and
(although some may argue this), compiling the PL to bytecode and
using that.
How would compilation to bytecode help?
-Neil
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Occupied port warning
Andrew Dunstan wrote: IIRC, in previous versions any bind failure was fatal, but in 8.0 we decided to be slightly more forgiving and only bail out if we failed to bind at all. I realize that, but I would like to know where that bright idea came from in violation of all other principles of this and any other software. I recall that it had something to do with IPv6, but I'm not sure. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. Pavel ---(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: [HACKERS] Occupied port warning
At 2005-06-28 15:14:29 +0200, [EMAIL PROTECTED] wrote: I recall that it had something to do with IPv6, but I'm not sure. Under Linux, if you bind to AF_INET6/::0, a subsequent bind to AF_INET/0 will fail, but the IPv4 address is also bound by the first call, and the program will accept IPv4 connections anyway (BSD behaves differently). Maybe that had something to do with it? I remember I had to add code to my program to allow that second bind to fail without complaint, and now my code also exits only if it can't bind anything at all. (For what it's worth, I don't think this behaviour is such a big deal.) -- ams ---(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
[HACKERS] bug: LC_CTYPE=en_US.UTF-8 confuses query planner
I encountered a bug where the same query behaves differently under different LC_CTYPE settings, C and en_US.UTF-8. The query is of type SELECT ... WHERE a like 'x' and b like 'y', where relevant indexes exist for a and b, and 'x' and 'y' strings do not contain the % character. When database is initdb'ed with LC_CTYPE=C, the query uses index scan; when LC_CTYPE=en_US.UTF-8 it is the sequential scan. The table is large, so it doesn't seem that planner selects seqscan out of performance reasons. Also, I think this is a bug since when the query contains only one 'like' statement, the query planner does use the index, no matter what $LC_CTYPE value is. Details: pgsql 8.0.3 LC_CTYPE=C: # explain select * from queues where username like 'a' and hostname like 'b'; QUERY PLAN -- Index Scan using queues_idx_hostname_time on queues (cost=0.00..11.48 rows=1 width=161) Index Cond: (hostname = 'b'::text) Filter: ((username ~~ 'a'::text) AND (hostname ~~ 'b'::text)) (3 rows) LC_CTYPE=en_US.UTF-8: # explain select * from queues where username like 'a' and hostname like 'b'; QUERY PLAN Seq Scan on queues (cost=1.00..10016.15 rows=1 width=161) Filter: ((username ~~ 'a'::text) AND (hostname ~~ 'b'::text)) (2 rows) # \d queues ... username | text | not null hostname | text | not null -- Sincerely, Dmitry Karasik --- catpipe Systems ApS *BSD solutions, consulting, development www.catpipe.net +45 7021 0050 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Occupied port warning
Peter Eisentraut wrote: Andrew Dunstan wrote: IIRC, in previous versions any bind failure was fatal, but in 8.0 we decided to be slightly more forgiving and only bail out if we failed to bind at all. I realize that, but I would like to know where that bright idea came from in violation of all other principles of this and any other software. I recall that it had something to do with IPv6, but I'm not sure. It came from the fertile brain of Tom Lane :-) see http://archives.postgresql.org/pgsql-hackers/2004-03/msg00679.php I think violation of all other principles of this and any other software is far too strong. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Occupied port warning
On Tue, Jun 28, 2005 at 03:14:29PM +0200, Peter Eisentraut wrote: Andrew Dunstan wrote: IIRC, in previous versions any bind failure was fatal, but in 8.0 we decided to be slightly more forgiving and only bail out if we failed to bind at all. I realize that, but I would like to know where that bright idea came from in violation of all other principles of this and any other software. I recall that it had something to do with IPv6, but I'm not sure. If the TCP socket is used we can still bind to the Unix-domain socket, no? -- Alvaro Herrera (alvherre[a]surnet.cl) Vivir y dejar de vivir son soluciones imaginarias. La existencia está en otra parte (Andre Breton) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Occupied port warning
Alvaro Herrera wrote: If the TCP socket is used we can still bind to the Unix-domain socket, no? If I configured a TCP/IP socket, what good does a Unix-domain socket do me? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Occupied port warning
Andrew Dunstan wrote: see http://archives.postgresql.org/pgsql-hackers/2004-03/msg00679.php Well, with once release of field experience behind me I'd like to revisit this idea. Who would actually be hurt by generating an error here like it used to do? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Occupied port warning
Peter Eisentraut [EMAIL PROTECTED] writes: During a recent training session I was reminded about a peculiar misbehavior that recent PostgreSQL releases exhibit when the TCP port they are trying to bind to is occupied: LOG: could not bind IPv4 socket: Address already in use HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. WARNING: could not create listen socket for localhost The trainees found this behavior somewhat unuseful. What behavior are you proposing, exactly? I don't think it's practical to make the server error out if it can't bind to every socket it tries to bind to --- that will leave you dead in the water in an uncomfortably large number of scenarios. I think the cases that forced us to adopt this behavior originally were ones where userland thinks IPv6 is supported but the kernel does not. Thus, we can *not* treat the list returned by getaddrinfo as gospel. It might be reasonable to treat some error conditions as fatal but not others. But you'd have to engage in pretty close analysis to make sure you weren't buying into any bad behaviors. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] #ifdef NOT_USED
Abhijit Menon-Sen [EMAIL PROTECTED] writes: We keep such blocks of code around in case we might need to use it some day. I think that's a bad idea. Unused code should be removed with a suitable CVS checkin comment (and perhaps a comment where the code was), The code is that comment. Mostly, these blocks are subroutines that happen not to be needed right at the moment, but form an obvious part of a module's API and might be needed again at any time. (An example is BufFileTellBlock in buffile.c.) If someone did need them, they'd be unlikely to think to root through the CVS history to find if the functionality they needed had once existed --- they'd probably waste time rewriting the routine from scratch. I personally think that the policy of ifdef'ing out API functions just because they happen to be unreferenced at the moment is a bad idea; who's to say that someone's extension module won't need the function? But ifdef is a whole lot better than removing the code completely. There are other common patterns for NOT_USED --- one is to document arguments that are passed to, but currently ignored by, functions following some API or other. There are a few cases where a NOT_USED block represents functionality that won't ever be resurrected --- for instance, I just recently removed the last NOT_USED vestiges of UNDO support in xlog.c, because it's clear now that we have no intention of going down that design path. In a quick look, though, I did not see very many blocks that I'd favor removing. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] initdb -W failure with role-capable catalogs
After the recent role-capable catalog commit, initdb -W fails with the following error: initializing pg_authid ... ok Enter new superuser password: Enter it again: setting password ... ok initdb: The password file was not generated. Please report this problem. initdb: removing contents of data directory data I saw Tom's comment about many loose ends remaining -- is this one of them? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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: [HACKERS] initdb -W failure with role-capable catalogs
Michael Fuhr [EMAIL PROTECTED] writes: After the recent role-capable catalog commit, initdb -W fails with the following error: initializing pg_authid ... ok Enter new superuser password: Enter it again: setting password ... ok initdb: The password file was not generated. Please report this problem. initdb: removing contents of data directory data I saw Tom's comment about many loose ends remaining -- is this one of them? Yup. Thanks for the report --- will fix ASAP. 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: [HACKERS] bug: LC_CTYPE=en_US.UTF-8 confuses query planner
Dmitry Karasik [EMAIL PROTECTED] writes: When database is initdb'ed with LC_CTYPE=C, the query uses index scan; when LC_CTYPE=en_US.UTF-8 it is the sequential scan. This is in the FAQ: When using wild-card operators such as LIKE or ~, indexes can only be used in certain circumstances: ... * The default C locale must be used during initdb because it is not possible to know the next-greatest character in a non-C locale. You can create a special text_pattern_ops index for such cases that work only for LIKE indexing. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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: [HACKERS] commit_delay, siblings
Tatsuo Ishii [EMAIL PROTECTED] writes: If we yank them ( and I agree) I think we have to do it before feature freeze. I believe that we have consensus to yank them. Hans says that he did extensive testing back as far as 7.4 and the options had no effect. My opinion is, we'd better test with at least 8.0, or even better with current. I think I can do the testing after Jul 1 if those features are remained. I have a dual Xeon system with a 15000RPM SCSI disk system in my office. Well, the proposal is on the table, and the implementation is pretty obvious. If you want to be sticky about the feature freeze rule, someone could generate a diff to remove the variables and post it to -patches before July 1, and then it would be fully per-rules to evaluate it after July 1. I vote not to require ourselves to go through that pushup. If Tatsuo can do some testing next week, I'm happy to hold off removing the variables until then. 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: [HACKERS] commit_delay, siblings
On Tue, Jun 28, 2005 at 10:35:43AM -0400, Tom Lane wrote: Tatsuo Ishii [EMAIL PROTECTED] writes: If we yank them ( and I agree) I think we have to do it before feature freeze. I believe that we have consensus to yank them. Hans says that he did extensive testing back as far as 7.4 and the options had no effect. My opinion is, we'd better test with at least 8.0, or even better with current. I think I can do the testing after Jul 1 if those features are remained. I have a dual Xeon system with a 15000RPM SCSI disk system in my office. Well, the proposal is on the table, and the implementation is pretty obvious. If you want to be sticky about the feature freeze rule, someone could generate a diff to remove the variables and post it to -patches before July 1, and then it would be fully per-rules to evaluate it after July 1. That'd be needlessly legalistic ... I propose we stick to the spirit of the rules, rather than the letter. I vote not to require ourselves to go through that pushup. I agree. -- Alvaro Herrera (alvherre[a]surnet.cl) Cada quien es cada cual y baja las escaleras como quiere (JMSerrat) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Implementing SQL/PSM for PG 8.2
Christopher Browne [EMAIL PROTECTED] writes: There are essentially four choices: Aside: I suppose there are as many possible choices as there are bytecode compiled systems out there. One could consider Icon, CLISP, Python, PHP, OCAML, CMU/CL, all of which have bytecode compilers. But none of those VMs are particularly intended to be reused/abused for other purposes; they were designed for the convenience of the respective language implementors. Mind you, the Icon VM is probably pretty stable by now :-). -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://www.ntlug.org/~cbbrowne/sap.html Rules of the Evil Overlord #78. I will not tell my Legions of Terror And he must be taken alive! The command will be: ``And try to take him alive if it is reasonably practical.'' http://www.eviloverlord.com/ ---(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: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Attached please find files and patches associated with moving from the User/Group system currently in place to Roles, as discussed previously. I have cleaned this up a bit and committed it. I normally wouldn't commit an incomplete patch, but this change is blocking Alvaro's work on dependencies for shared objects, so I felt it was best to get the catalog changes in now. That will let Alvaro work on dependencies while I sort out the unfinished bits of roles, which I intend to do over the next day or so. Great, glad to hear it. I hope you got a chance to look over the open items in the 'milestones' file. I'd really like to see the grammar be fixed to match SQL spec for GRANT ROLE/REVOKE ROLE. I think an approach to take there might be to try and get GrantRoleStmt and GrantStmt to use the same productions at the end of the line if possible or something along those lines. Also, I've been looking through the diff between my tree and what you committed to CVS and had a couple comments (just my 2c: I think it would have been alot easier using SVN to see exaclty what was different from my patch vs. other changes since my last CVS up): First, sorry about the gratuitous name changes, it helped me find every place I needed to look at the code and think about if it needed to be changed in some way (ie: Int32GetDatum - ObjectIdGetDatum, etc). I had planned on changing some of them back to minimize the patch but kind of ran out of time. Second, looks like I missed fixing an owner check in pg_proc.c Current CVS has, line 269: if (GetUserId() != oldproc-proowner !superuser()) Which is not a sufficient owner check. This should by fixed by doing a proper pg_proc_ownercheck, ie: if (!pg_proc_ownercheck(HeapTupleGetOid(oldtup), GetUserId())) Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). Without this there is no way for a given role to allow other roles to perform owner-level actions on objects which they create. The point of adding roles was to allow owner-level actions on objects to more than a single user or the superuser. Requiring the superuser to get involved with every table creation defeats much of the point. This should really be possible either by explicitly changing the ownership of an object using ALTER ... OWNER, or by a SET ROLE followed by CREATE TABLE, etc. SET ROLE is defined by the SQL specification, though we don't support it specifically yet (shouldn't be too difficult to add now though). Certainly if we accept that SET ROLE should be supported and that objects then created should be owned by the role set in SET ROLE we should be willing to support non-superusers doing ALTER ... OWNER given that they could effectively do the same thing via SET ROLE (though with much more difficulty, which has no appreciable gain). Fourth, not that I use it, but, it looks like my changes to src/interfaces/ecpg/preproc/preproc.y were lost. Not sure if that was intentional or not (I wouldn't think so... I do wish ecpg could just be the differences necessary for ecpg and be based off the main parser somehow, but that'd be a rather large change). Oh, and in that same boat, src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. Many thanks for your work on this! Happy to have helped though frustrated that you seem to have removed the part that I was originally looking for. I don't feel that's justification for having it (I feel I've addressed that above) but it certainly would have been nice to be aware of that earlier and perhaps to have discussed the issues around it a bit more before being so close to the feature freeze (I know, alot my fault, but there it is). Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] CVS tip build failure (win32)
I'm seeing the following failure on win32, post roles patch application: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wold-style-definition -Wendif-labels -fno-strict-aliasing -I../../../src/include -I./src/include/port/win32 -DEXEC_BACKEND -I../../../src/include/port/win32 -DBUILDING_DLL -c -o namespace.o namespace.c In file included from namespace.c:38: ../../../src/include/utils/acl.h:214: error: conflicting types for 'InitializeAcl' c:/mingw/bin/../lib/gcc/mingw32/3.4.2/../../../../include/winbase.h:1571 : error: previous declaration of 'InitializeAcl' was here ../../../src/include/utils/acl.h:214: error: conflicting types for 'InitializeAcl' c:/mingw/bin/../lib/gcc/mingw32/3.4.2/../../../../include/winbase.h:1571 : error: previous declaration of 'InitializeAcl' was here make[3]: *** [namespace.o] Error 1 make[3]: Leaving directory `/cvs/pgsql.dbsize/src/backend/catalog' make[2]: *** [catalog-recursive] Error 2 make[2]: Leaving directory `/cvs/pgsql.dbsize/src/backend' make[1]: *** [all] Error 2 make[1]: Leaving directory `/cvs/pgsql.dbsize/src' make: *** [all] Error 2 That's following a cvs update and a make clean. All was fine before I updated :-( Regards, Dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
* Tom Lane ([EMAIL PROTECTED]) wrote:
The SQL99 spec has for GRANT (REVOKE has the identical issue):
grant privilege statement ::=
GRANT privileges
TO grantee [ { comma grantee }... ]
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
[ GRANTED BY grantor ]
SQL2003 seems to have the same issue. The only possible additional bit
is (in SQL2003 at least, I imagine 99 is the same):
privileges ::= object privileges ON object name
Is there some way we could use that 'ON' is required for the
'privileges' grant?
The only grammar-level solution I can see is to promote all of the
following into some category of reserved word:
INSERT UPDATE USAGE DELETE RULE TRIGGER EXECUTE TEMPORARY TEMP
which is pretty annoying, even though SQL99 gives us license to do so
for most of them. (But reserving RULE or TEMP would be contrary to
spec.)
What about 'ON', from above? I don't suppose making that a reserved
word would maybe help (if it's not already, if it's allowed by the spec,
etc)? Sorry, just guessing really but it did seem like something you
didn't consider so I thought I'd mention it.
Alternatively we might consider not distinguishing GRANT PRIVILEGE
from GRANT ROLE at parse time, but sorting it out later. The most
extreme form of this would be to actually allow both things in the
same GRANT:
GRANT INSERT, role1, UPDATE TO joe;
That would certainly be rather.. odd. It also doesn't really follow the
spec I don't think. Honestly, I'd think we'd want to error out if we
came across a situation here and assume the user misspelled a privilege
or something.
treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable
spellings of the same thing (which they very nearly are anyway).
This I agree with, kind of silly to have them be named differently like
that.
One objection to this is that misspelling a privilege keyword would
give you a complaint about unknown role, which might be a bit
confusing; but I suspect we cannot avoid that anyway --- there is
absolutely no basis on which we can say that
GRANT INSIRT TO joe;
isn't a GRANT ROLE operation, until we fail to find the role name.
Right, looks very much like a GRANT ROLE operation.
(Possibly we could alleviate this by adding a HINT.)
Probably wouldn't hurt..
These considerations also suggest that it'd be a good idea to disallow
the privilege names (select insert etc) as role names.
If using the 'ON' requirement isn't possible then yes, I'd say we should
disallow the use of the privilege names as role names.
On the whole, the SQL99 committee should have followed Stephen's
idea and made the syntax be GRANT ROLE rolenames ...
I can't argue with that. :)
Thanks,
Stephen
signature.asc
Description: Digital signature
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
Pavel, I am in agreement with Tom here, we should use a separate port, and protocol specifically designed for this. My understanding is that this protocol would be synchronous, and be used for transferring state information, variables, etc back and forth whereas the existing protocol would still be used to transfer data back and forth Dave On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote: On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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]) ---(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: [HACKERS] Occupied port warning
Tom Lane wrote: What behavior are you proposing, exactly? The least thing it should do is error out if *no* TCP/IP port could be created while listen_addresses is set. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Role syntax (or, SQL99 versus sanity)
I'm looking at the problem Stephen Frost noted of not being able to
duplicate the SQL99-specified syntax for GRANT/REVOKE with roles.
The SQL99 spec has for GRANT (REVOKE has the identical issue):
grant privilege statement ::=
GRANT privileges
TO grantee [ { comma grantee }... ]
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
[ GRANTED BY grantor ]
grant role statement ::=
GRANT role granted [ { comma role granted }... ]
TO grantee [ { comma grantee }... ]
[ WITH ADMIN OPTION ]
[ GRANTED BY grantor ]
Barring the appearance of one of the OPTION clauses, it is actually
impossible to tell which kind of statement you are dealing with,
other than by noticing whether the words appearing between GRANT and TO
all look like known privilege keywords. The bison conflicts Stephen
was seeing come from the fact that we treat most of the privilege
keywords as unreserved words, and so the ambiguity is fatal as far
as bison is concerned.
The only grammar-level solution I can see is to promote all of the
following into some category of reserved word:
INSERT UPDATE USAGE DELETE RULE TRIGGER EXECUTE TEMPORARY TEMP
which is pretty annoying, even though SQL99 gives us license to do so
for most of them. (But reserving RULE or TEMP would be contrary to
spec.)
Alternatively we might consider not distinguishing GRANT PRIVILEGE
from GRANT ROLE at parse time, but sorting it out later. The most
extreme form of this would be to actually allow both things in the
same GRANT:
GRANT INSERT, role1, UPDATE TO joe;
treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable
spellings of the same thing (which they very nearly are anyway).
One objection to this is that misspelling a privilege keyword would
give you a complaint about unknown role, which might be a bit
confusing; but I suspect we cannot avoid that anyway --- there is
absolutely no basis on which we can say that
GRANT INSIRT TO joe;
isn't a GRANT ROLE operation, until we fail to find the role name.
(Possibly we could alleviate this by adding a HINT.)
These considerations also suggest that it'd be a good idea to disallow
the privilege names (select insert etc) as role names.
On the whole, the SQL99 committee should have followed Stephen's
idea and made the syntax be GRANT ROLE rolenames ...
Thoughts anyone?
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] initdb -W failure with role-capable catalogs
* Michael Fuhr ([EMAIL PROTECTED]) wrote: After the recent role-capable catalog commit, initdb -W fails with the following error: Whoops, sorry about that, didn't know initdb had a -W option. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Problem with dblink regression test
All the logs for the most recent run against HEAD are now at http://stats.distributed.net/~buildfarm/ On Tue, Jun 28, 2005 at 08:30:44AM -0400, Andrew Dunstan wrote: Jim, you should have a file buildroot/HEAD/lastrun-logs/make.log that shows the link steps for the libraries. Can you either put that file somewhere we can look at it or extract the relevant lines and post in a reply? thanks andrew Jim C. Nasby wrote: I have no clue why the mailling list is eating my original messages, unless it's because I attached a diff to them... in any case, applying http://stats.distributed.net/~buildfarm/patch provides a listing of what all the binaries and libraries in a buildfarm install are linking against. I couldn't figure out a decent way to send that info to pgbuildfarm.org, but http://stats.distributed.net/~buildfarm/libcheck.log is that info for a run. Based on the logfile, it looks like Tom's guess is correct that psql (and other binaries) are linking to the buildfarm libraries, while dblink.so (and other libraries) are linking against the system postgresql libraries. If someone wants to point me in the right direction I'll try and fix this, since I'm guessing it's just a make issue (or maybe a buildfarm issue). Actually, looking at my config (http://stats.distributed.net/~buildfarm/build-farm.conf), could the --with-libraries=/usr/local/lib be the issue, and if so, what's the proper way to handle system libraries (like libintl) living in /usr/local/lib and not /usr/lib? -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Hi Fabien, * Fabien COELHO ([EMAIL PROTECTED]) wrote: I've looked very quickly at the patch. ISTM that the proposed patch is a reworking of the user/group stuff, which are both unified for a new role concept where a user is a kind of role and a role can be a member of another role. Well, why not. Right, it's a beginning to proper 'Role' support as defined by the SQL specification. Some added files seems not to be provided in the patch : pg_authid.h and pg_auth_members.h were attached to the email. They're also available at http://kenobi.snowman.net/~sfrost/pg_role/ ; but the patch has already been applied by Tom to CVS HEAD (well, with lots of modifications and whatnot), so you probably should just take a look at that. Anyway, from what I can see in the patch it seems that the roles are per cluster, and not per catalog. So this is not so conceptually different from user/group as already provided in pg. It's conceptually different from users/groups in that it's roles, which aren't the same thing. You're right, it's still per-cluster though. What would have been much more interesting for me would be a per catalog role, so that rights could be administrated locally in each database. I'm not sure how to provide such a feature, AFAICS the current version does not give me new abilities wrt right management. I understand your concerns here and while I agree with the basic idea that per-catalog role sets would be nice it wasn't what I had set out to do with this patch. Perhaps what you're asking for will be added later on. Some things this patch does do though are: Allow role ownership. This role can also have members, and doesn't necessairly have to be allowed to log in. Members of a role which owns an object have owner-level rights on that object (so, fe: roles user1, user2 and group1 where user1 and user2 are members of group1, a table owned by group1 can be vacuumed, have columns added/removed, have indexes create on it, etc, by user1 or user2). Allow granting roles to other roles based on the 'with admin option'. This means you don't have to be a superuser to add a member to a role which you have the 'admin option' on. There's other things (startup may be a bit faster since the pg_auth file is sorted by the backend instead of during each startup, etc) but the above were the types of things that I was looking to do mainly. I'd like to see it possible to distinguish between 'superuser' and 'createrole' permissions, but I didn't get to that point with the roles support (it's really a seperate issue anyway). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] commit_delay, siblings
Tom, Incidentally, I have tests in the queue. It's just that the STP has been very unreliable for the last month so I've not been able to get definitive test results. More important than commit_*, is, of course the WAL/CRC stuff for checkpoint cost, which I'm also getting impatient to test. Will be setting up my own test machines today ... -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Wierd panic with 7.4.7
Joshua D. Drake [EMAIL PROTECTED] writes: Do you know what the dead client was doing? Unfortunately I don't. We didn't have PID logging turned on so I can't tell which process it was. The only thing I was told was, I am running a Full Vacuum, CRAP the server just died ;) Hmm ... VACUUM FULL is a tad weird because it marks itself committed before it does the last stage of the operation (ie truncating the relation). I suppose it is possible that an error during that last stage would have this symptom. There's still the question of why the postmaster log doesn't show the hypothetical initial error, though. (Also, it's likely that there's some defense against this scenario in VACUUM FULL, otherwise we'd hear this kind of report more often, I should think. I don't have time to go looking right now though.) 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: [HACKERS] CVS tip build failure (win32)
Dave Page dpage@vale-housing.co.uk writes: ../../../src/include/utils/acl.h:214: error: conflicting types for 'InitializeAcl' c:/mingw/bin/../lib/gcc/mingw32/3.4.2/../../../../include/winbase.h:1571 : error: previous declaration of 'InitializeAcl' was here Grumble. I'll change the routine name. Thanks... 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: [HACKERS] Occupied port warning
Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: What behavior are you proposing, exactly? The least thing it should do is error out if *no* TCP/IP port could be created while listen_addresses is set. That might be reasonable --- I think right now we only die if we couldn't create the Unix socket either. 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: [HACKERS] Role syntax (or, SQL99 versus sanity)
Stephen Frost [EMAIL PROTECTED] writes: Is there some way we could use that 'ON' is required for the 'privileges' grant? Well, the difficulty is that we can't see the ON until we've scanned the list of privilege or role names. Now that I've calmed down a bit, the solution is fairly obvious: the name list has to be left as strings during the grammar. We'll check the privilege names for validity at execution. 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: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: Also, I've been looking through the diff between my tree and what you committed to CVS and had a couple comments First, sorry about the gratuitous name changes, it helped me find every place I needed to look at the code and think about if it needed to be changed in some way (ie: Int32GetDatum - ObjectIdGetDatum, etc). I had planned on changing some of them back to minimize the patch but kind of ran out of time. No problem, I figured that was why you'd done it, but changing them back helped me to understand the patch also ;-) Second, looks like I missed fixing an owner check in pg_proc.c Got it. I was wondering if there were more --- might be worth checking all the superuser() calls. Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. I took that out because it struck me as a likely security hole; we don't allow non-superuser users to give away objects now, and we shouldn't allow non-superuser roles to do so either. Moreover the tests you had were inconsistent (not same test everyplace). Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). This is what SET SESSION AUTHORIZATION/SET ROLE is for, no? You set the auth to a role you are allowed to be in, then create the object. I do notice that we don't have this yet, but it's surely a required piece of the puzzle. Fourth, not that I use it, but, it looks like my changes to src/interfaces/ecpg/preproc/preproc.y were lost. Not sure if that was intentional or not Yeah, it was. I leave it to Michael Meskes to sync ecpg with the main parser; on the occasions where I've tried to do it for him, things didn't work out well. I do wish ecpg could just be the differences necessary for ecpg and be based off the main parser somehow, Me too, but I haven't seen a way yet. src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. That's an automatically generated list; there's no need to edit it. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Wierd panic with 7.4.7
There are a couple of big problems with this theory, though. In the first place, there aren't any messages sent to the client during post-commit; unless possibly it's an error message due to a failure during post-commit, and that should have shown up in the server log. In the second place, we don't treat communication failures as ERRORs, so how did step 3 happen? Do you know what the dead client was doing? Unfortunately I don't. We didn't have PID logging turned on so I can't tell which process it was. The only thing I was told was, I am running a Full Vacuum, CRAP the server just died ;) Can you reproduce this? Let me see if the client has a dev database we can try to reproduce with. Sincerely, Joshua D. Drake regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] CVS tip build failure (win32)
* Dave Page (dpage@vale-housing.co.uk) wrote: I'm seeing the following failure on win32, post roles patch application: [...] 'InitializeAcl' [...] That's following a cvs update and a make clean. All was fine before I updated :-( Wow. Apparently 'InitializeAcl' is part of the Windows API. My bad, sorry about that. I guess we should rename it? I don't see any particular problem with that (it's only used in 3 places), perhpas 'PGInitializeAcl' or 'InitializeRoleCache' or some such. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: The code I had for this was: if (!pg_class_ownercheck(tuple,GetUserId()) || !is_role_member(newowner,GetUserId())) That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side Um, right, that was another problem I had with it --- at one point the regression tests were failing because the superuser wasn't allowed to reassign object ownership ... I'm still fairly concerned about the security implications of letting ordinary users reassign object ownership. The fact that SET ROLE would let you *create* an object with ownership X is a long way away from saying that you should be allowed to change an *existing* object to have ownership X. This is particularly so if you are a member of a couple of different roles with different memberships: you will be able to cause objects to become effectively owned by certain other people, or make them stop being effectively owned by those people. I don't have a clear trouble case in mind at the moment, but this sure sounds like the stuff of routine security-hole reports. (Altering the ownership of a SECURITY DEFINER function, in particular, sounds like a great path for a cracker to pursue.) One place I recall seeing one and not being sure if it should be a new *_ownercheck() function or not was in the 2PC patch- twophase.c, line 380: This one I think we can leave... 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
[HACKERS] Proposed TODO: --encoding option for pg_dump
Folks, There's no time to do this for 8.1, but I'd like to get it on the books for 8.2: The Problem: Occassionally a DBA needs to dump a database to a new encoding. In instances where the current encoding, (or lack of an encoding, like SQL_ASCII) is poorly supported on the target database server, it can be useful to dump into a particular encoding. But, currently the only way to set the encoding of a pg_dump file is to change client_encoding in postgresql.conf and restart postmaster. This is more than a little awkward for production systems. The TODO: add an --encoding=[encoding name] option to pg_dump. This would set client_encoding for pg_dump's session(s). -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Occupied port warning
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane wrote: What behavior are you proposing, exactly? The least thing it should do is error out if *no* TCP/IP port could be created while listen_addresses is set. That might be reasonable --- I think right now we only die if we couldn't create the Unix socket either. correct (in the cases where we try to create it, e.g. Unix but not Windows). cheers andrew ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Second, looks like I missed fixing an owner check in pg_proc.c Got it. I was wondering if there were more --- might be worth checking all the superuser() calls. Yeah, let's come up with a decision about what exactly we should allow and then perhaps I can go through all of the superuser() calls and see what needs fixing up. Third, I feel it's incorrect to only allow superuser() to change ownership of objects under a role-based system. I took that out because it struck me as a likely security hole; we don't allow non-superuser users to give away objects now, and we shouldn't allow non-superuser roles to do so either. Moreover the tests you had were inconsistent (not same test everyplace). Sorry about them being inconsistent, I didn't intend for them to be. I went through a couple of iterations of them trying to do the check the 'right' way. Thinking back on it, even the checks I ended up with were wrong (in the superuser case), though I think they were closer. Basically my thought was to allow the same thing you could do w/ SET ROLE, etc: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. The code I had for this was: if (!pg_class_ownercheck(tuple,GetUserId()) || !is_role_member(newowner,GetUserId())) That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side (currently anyway, I suppose a superuser could be considered to be in any role, so we could change is_role_member to always return true for superusers, that'd probably make pg_group look ugly though, either way): if (!superuser() !(pg_class_ownercheck(tupe,GetUserId()) is_role_member(newowner,GetUserId( I think that's the correct check and can be done the same way for pretty much all of the objects. Were there other security concerns you had? I'd be happy to look through the superuser() checks in commands/ and develop a patch following what I described above, as well as looking for other cases where we should be using the *_ownercheck() functions. One place I recall seeing one and not being sure if it should be a new *_ownercheck() function or not was in the 2PC patch- twophase.c, line 380: if (user != gxact-owner !superuser_arg(user)) Wasn't sure if that made sense to have *_ownercheck, or, even if we added one for it, if it made sense to check is_member_of_role() for prepared transactions. I don't think SQL has anything to say about it, anyone know what other DBs do here? Users must be able to create objects owned by a role they're in (as opposed to owned only by themselves). This is what SET SESSION AUTHORIZATION/SET ROLE is for, no? You set the auth to a role you are allowed to be in, then create the object. I do notice that we don't have this yet, but it's surely a required piece of the puzzle. (Technically I think SET SESSION AUTHORIZATION is different from SET ROLE, but anyway) Right, that's another way to do it (as I mentioned), and that lets you do ownership changes, but they're much more painful: CONNECT AS joe; CREATE TABLE abc as SELECT name,avg(a),sum(b) FROM reallybigtable; -- Whoops, I meant for abc to be owned by role C so sally can add her -- column to it later, or vacuum/analyze it, whatever GRANT SELECT ON abc TO C; -- Might not be necessary ALTER TABLE abc RENAME TO abc_temp; SET ROLE C; CREATE TABLE abc AS SELECT * FROM abc_temp; -- Could be big :( SET ROLE NONE; -- Might be just 'SET ROLE;'? Gotta check the spec DROP TABLE abc_temp; I don't really see the point in making users go through all of these hoops to do an ownership change. In the end, it's the same result near as I can tell... Yeah, it was. I leave it to Michael Meskes to sync ecpg with the main parser; on the occasions where I've tried to do it for him, things didn't work out well. Ah, ok. src/tools/pgindent/pgindent also appears to not have gotten the changes that I made. That's an automatically generated list; there's no need to edit it. Hah, silly me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] contrib/rtree_gist into core system?
Teodor Sigaev [EMAIL PROTECTED] writes: 1. In your meaning, btree has bad split algorithm too. Look at _bt_compare, if first keys on page are unique the the later keys will not be compared ;) I'm confused now. I was under the impression that gist didn't look at subsequent columns if the first column was identical. Maybe I got it backwards and it's insert that fails to look at subsequent columns but page split handles it ok? So if you insert lots of tuples with identical first columns the page will be split intelligently but then subsequent inserts will be distributed essentially randomly between the two resulting pages. -- greg ---(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: [HACKERS] Problem with dblink regression test
Jim C. Nasby wrote: All the logs for the most recent run against HEAD are now at http://stats.distributed.net/~buildfarm/ A quick look shows that when you use --with-libraries=/foo/bar the generated link line for libraries says -L/foo/bar -lpq and it should probably be the other way around (as it is for the executables). So I suspect we need some makefile tuning. cheers andrew ---(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: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: That needs a check for superuser though because while the test will pass on the 'pg_class_ownercheck' side, it won't on the 'is_role_member' side Um, right, that was another problem I had with it --- at one point the regression tests were failing because the superuser wasn't allowed to reassign object ownership ... Yeah, sorry about that. I'm still fairly concerned about the security implications of letting ordinary users reassign object ownership. The fact that SET ROLE would let you *create* an object with ownership X is a long way away from saying that you should be allowed to change an *existing* object to have ownership X. This is particularly so if you are a member of a couple of different roles with different memberships: you will be able to cause objects to become effectively owned by certain other people, or make them stop being effectively owned by those people. I don't have a clear trouble case in mind at the moment, but this sure sounds like the stuff of routine security-hole reports. (Altering the ownership of a SECURITY DEFINER function, in particular, sounds like a great path for a cracker to pursue.) SET ROLE also lets you *drop* an object owned by that role. Or alter it. Or CREATE OR REPLACE FUNCTION ... I can understand your concern. The specific use case I'm thinking about is where a user creates an object, does some work on it, and then wants to change its ownership to be owned by a role which that user is in. I find myself doing that a fair bit (as superuser atm). One thing I don't like about limiting it to that is that you then can't go back without the whole drop/create business or getting an admin. This also isn't stuff that couldn't be done through other means, even in the SECURITY DEFINER function case, you just need to drop, set role, create. Having a role with members be able to own objects isn't meant to replace the privileges system and I don't expect people to try to use it to. I can perhaps see a special case for SECURITY DEFINER functions but if we're going to special case them I'd think we'd need to make them only be creatable/modifiable at all by superusers or add another flag to the role to allow that. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
Tom Lane wrote:
I'm looking at the problem Stephen Frost noted of not being able to
duplicate the SQL99-specified syntax for GRANT/REVOKE with roles.
The SQL99 spec has for GRANT (REVOKE has the identical issue):
grant privilege statement ::=
GRANT privileges
TO grantee [ { comma grantee }... ]
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
[ GRANTED BY grantor ]
grant role statement ::=
GRANT role granted [ { comma role granted }... ]
TO grantee [ { comma grantee }... ]
[ WITH ADMIN OPTION ]
[ GRANTED BY grantor ]
Barring the appearance of one of the OPTION clauses, it is actually
impossible to tell which kind of statement you are dealing with,
other than by noticing whether the words appearing between GRANT and TO
all look like known privilege keywords. The bison conflicts Stephen
was seeing come from the fact that we treat most of the privilege
keywords as unreserved words, and so the ambiguity is fatal as far
as bison is concerned.
The only grammar-level solution I can see is to promote all of the
following into some category of reserved word:
INSERT UPDATE USAGE DELETE RULE TRIGGER EXECUTE TEMPORARY TEMP
which is pretty annoying, even though SQL99 gives us license to do so
for most of them. (But reserving RULE or TEMP would be contrary to
spec.)
Alternatively we might consider not distinguishing GRANT PRIVILEGE
from GRANT ROLE at parse time, but sorting it out later. The most
extreme form of this would be to actually allow both things in the
same GRANT:
GRANT INSERT, role1, UPDATE TO joe;
treating WITH GRANT OPTION and WITH ADMIN OPTION as interchangeable
spellings of the same thing (which they very nearly are anyway).
One objection to this is that misspelling a privilege keyword would
give you a complaint about unknown role, which might be a bit
confusing; but I suspect we cannot avoid that anyway --- there is
absolutely no basis on which we can say that
GRANT INSIRT TO joe;
isn't a GRANT ROLE operation, until we fail to find the role name.
(Possibly we could alleviate this by adding a HINT.)
These considerations also suggest that it'd be a good idea to disallow
the privilege names (select insert etc) as role names.
On the whole, the SQL99 committee should have followed Stephen's
idea and made the syntax be GRANT ROLE rolenames ...
Thoughts anyone?
going backwards ...
. getting to SQL99 syntax might be a pain but we should do it, however
unwise they were in choice of syntax.
. excluding named privileges from use as role names seems highly desireable
. thought on resolution
- not sure if we can play games with %prec - I suspect we can't
- could we get there if we declare the named privs as reserved just
for this purpose? That would probably imply partitioning the unreserved
keywords list.
cheers
andrew
---(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: [HACKERS] CVS tip build failure (win32)
-Original Message- From: Stephen Frost [mailto:[EMAIL PROTECTED] Sent: 28 June 2005 18:30 To: Dave Page Cc: PostgreSQL-development Subject: Re: [HACKERS] CVS tip build failure (win32) * Dave Page (dpage@vale-housing.co.uk) wrote: I'm seeing the following failure on win32, post roles patch application: [...] 'InitializeAcl' [...] That's following a cvs update and a make clean. All was fine before I updated :-( Wow. Apparently 'InitializeAcl' is part of the Windows API. My bad, sorry about that. I guess we should rename it? I don't see any particular problem with that (it's only used in 3 places), perhpas 'PGInitializeAcl' or 'InitializeRoleCache' or some such. Yes, it is. Sorry was a bit pushed for time and couldn't investigate earlier. PGInitializeAcl sounds OK to me. /D ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Proposed TODO: --encoding option for pg_dump
On Tue, 28 Jun 2005, Josh Berkus wrote: The TODO: add an --encoding=[encoding name] option to pg_dump. This would set client_encoding for pg_dump's session(s). What about just using the PGCLIENTENCODING environment variable? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Proposed TODO: --encoding option for pg_dump
Josh Berkus wrote: currently the only way to set the encoding of a pg_dump file is to change client_encoding in postgresql.conf and restart postmaster. Another way is to set the environment variable PGCLIENTENCODING. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. ---(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: [HACKERS] [PATCHES] Users/Groups - Roles
On Tue, Jun 28, 2005 at 14:52:07 -0500, Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. Thinking about it some more, drops wouldn't be an issue since the owner can always drop objects. Creating objects in particular schemas or databases is not something that all roles may be able to do. ---(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: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost wrote: I can perhaps see a special case for SECURITY DEFINER functions but if we're going to special case them I'd think we'd need to make them only be creatable/modifiable at all by superusers or add another flag to the role to allow that. I agree that owner changes of SECURITY DEFINER functions seem dangerous. I would follow Stephen's idea that SECURITY DEFINER functions should only be creatable/modifiable by superusers. This would be similar to unix, where setting the suid/sgid bits is usually only allowed to root. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruno Wolff III ([EMAIL PROTECTED]) wrote: On Tue, Jun 28, 2005 at 14:45:06 -0400, Stephen Frost [EMAIL PROTECTED] wrote: If you are the owner of the object to be changed (following the normal owner checking rules) AND would still be considered the owner of the object *after* the change, then you can change the ownership. That still isn't a good idea, because the new owner may not have had access to create the object you just gave to them. Or you may not have had access to drop the object you just gave away. That is going to be a security hole. If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposed TODO: --encoding option for pg_dump
There's no time to do this for 8.1, but I'd like to get it on the books for 8.2: The Problem: Occassionally a DBA needs to dump a database to a new encoding. In instances where the current encoding, (or lack of an encoding, like SQL_ASCII) is poorly supported on the target database server, it can be useful to dump into a particular encoding. But, currently the only way to set the encoding of a pg_dump file is to change client_encoding in postgresql.conf and restart postmaster. This is more than a little awkward for production systems. The TODO: add an --encoding=[encoding name] option to pg_dump. This would set client_encoding for pg_dump's session(s). I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) //Magnus pg_dump.diff Description: pg_dump.diff pg_dump.sgml.diff Description: pg_dump.sgml.diff ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost wrote: If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? For me this would be the natural way how SET ROLE would behave. This is unix'ism again, but using setuid to become another user, you loose the privileges of the old user context. Therefore SET ROLE should not inherit privileges from the other role. This seems to be the safes approach. Nevertheless, what does the standard say? If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Right, that's an issue. But since the new role will be the *owner* of the object, it *should* really have create-privileges in that schema. So the above way seems to be correct anyway. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Bruno Wolff III ([EMAIL PROTECTED]) wrote: Thinking about it some more, drops wouldn't be an issue since the owner can always drop objects. Right. Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. If you're not allowed to change ownership of objects and SET ROLE drops your regular ROLE's privileges then the role which owns the object originally (and which you're required to be in) must have had create access to that schema at some point. I can see requiring the role that's changing the ownership to have create access to the schema in which the object that's being changed is in. Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] [HACKERS] Proposed TODO: --encoding option for pg_dump
On Tue, Jun 28, 2005 at 10:24:19PM +0200, Magnus Hagander wrote: I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) You forgot to document the long option, I think. -- Alvaro Herrera (alvherre[a]surnet.cl) No necesitamos banderas No reconocemos fronteras (Jorge González) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] [HACKERS] Proposed TODO: --encoding option for pg_dump
I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) You forgot to document the long option, I think. Oops. Fixed. Thanks. //Magnus pg_dump.sgml.diff Description: pg_dump.sgml.diff ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] Proposed TODO: --encoding option for pg_dump
I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) You forgot to document the long option, I think. Are the man pages generated from the sgml docs? Have never had a look at that. Yes - using docbook2man. //Magnus ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [HACKERS] Proposed TODO: --encoding option for pg_dump
Alvaro Herrera wrote: On Tue, Jun 28, 2005 at 10:24:19PM +0200, Magnus Hagander wrote: I *think* that's easy enough to do in time for 8.1. Trivial patch attached. I hope it's enough :-) It passed my very quick testing... (Yup, I read the mails aobut PGCLIENTENCODING, but an option to pg_dump is certainly easier) You forgot to document the long option, I think. Are the man pages generated from the sgml docs? Have never had a look at that. Best Regards, Michael Paesold ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Michael Paesold ([EMAIL PROTECTED]) wrote: Stephen Frost wrote: If you're considered the owner of an object then you have access to drop it already. You have to be a member of the role to which you're changing the ownership. That role not having permission to create the object in place is an interesting question. That's an issue for SET ROLE too, to some extent I think, do you still have your role's permissions after you've SET ROLE to another role? For me this would be the natural way how SET ROLE would behave. This is unix'ism again, but using setuid to become another user, you loose the privileges of the old user context. Therefore SET ROLE should not inherit privileges from the other role. This seems to be the safes approach. Nevertheless, what does the standard say? Hmm, it says there's a stack and that the thing on top is what's currently used, so it sounds like it would drop the privs too, but imv it's not entirely clear. If not then you'd have to grant CREATE on the schema to the role in order to create objects owned by that role, and I don't think that's necessairly something you'd want to do. Right, that's an issue. But since the new role will be the *owner* of the object, it *should* really have create-privileges in that schema. So the above way seems to be correct anyway. I'm not entirely sure that you'd necessairly want the role to have create privileges on the schema even when it owns things in the schema but the more I think about it that doesn't seem all that unreasonable either. I don't think it'd be very difficult to add such a check to the ALTER OWNER code too though. In general, and perhaps as a unix'ism to some extent, I don't particularly like having to su to people. To get all the other permissions which the role has you don't have to 'su' currently, and personally I like that and think that's correct for a role-based environment (unlike unix where you have users and groups). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Moving sequences to another schema
--On Dienstag, Juni 28, 2005 09:38:56 +0800 Christopher Kings-Lynne [EMAIL PROTECTED] wrote: Does ALTER TABLE/RENAME code help you? You can rename sequences with that... Hmm, that doesn't cover pg_attrdef.adbin. I think the best way is to create the default expressions from scratch, as Tom already mentioned. -- Bernd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Moving sequences to another schema
--On Dienstag, Juni 28, 2005 02:01:33 -0400 Tom Lane [EMAIL PROTECTED] wrote: Not adsrc --- that's not trustworthy. Yes, that's documented in the docs, too. In practice I think you could just assume you know what the default expression ought to be, and store a new one without looking at the old. I think i'll go for that (need to figure out how to do that first, but it shouldn't be so hard). Whats the least that should go to -patches for feature release on 1 July (only to know, if i can hold timeline or not).?? -- Bernd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
On Tue, 28 Jun 2005, Dave Cramer wrote: Pavel, I am in agreement with Tom here, we should use a separate port, and protocol specifically designed for this. My understanding is that this protocol would be synchronous, and be used for transferring state information, variables, etc back and forth whereas the existing protocol would still be used to transfer data back and forth We can it. It can be good start point. I can do it alone. It simpler. But I don't think so this is optimal solution. You need two protocols. Maybe I don't understand, but I think so changes in protocol3 files will be minimal. I wont to do prototype. Pavel Dave On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote: On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
Dave, I lean with you and Tom. While running it over the same libpq protocol would be helpful in some ways, it would have a lot of drawbacks and would really change the function of libpq. I think a separate debugging protocol is in order. Also, as far as bytecode comments go, let's separate them from this thread. I have a pretty sweet hand-written stack-based VM that understands PL/SQL, but it's kinda old and written using PCCTS 1.33 (a recursive descent parser). It has compilation, decompilation, and full debugging capabilities. Unfortunately, PCCTS is no longer maintained as Terrence Parr (the originator) has since moved to ANTLR. ANTLR currently does not generate C code although I have done some starting work on it (ANTLR currently generates Python, Java, or C++). I don't suggest we really reuse one of the current VMs as it would require a lot more support and coordination. Let's take the bytecode discussion off this thread and move it to another. There is certainly a good and bad side to using bytecode and I would be glad to discuss it in another thread. Dave Cramer wrote: Pavel, I am in agreement with Tom here, we should use a separate port, and protocol specifically designed for this. My understanding is that this protocol would be synchronous, and be used for transferring state information, variables, etc back and forth whereas the existing protocol would still be used to transfer data back and forth Dave On 28-Jun-05, at 10:36 AM, Pavel Stehule wrote: On Tue, 28 Jun 2005, Tom Lane wrote: Pavel Stehule [EMAIL PROTECTED] writes: What do you think you need for enhanced protocol ? What I need? Some like synchronous elog(NOTICE,''), which can return some user's interaction, if it's possible. I didn't find how I do it with current set of messages. But my knowleadges of protocol are minimal. It'd probably be smarter to manage the debugging across a separate connection, so that you could carry out debugging without requiring sophisticated support for it inside the client program. If it's single-connection then it will be essentially impractical to debug except from a few specialized clients such as pgadmin; which will make it hard to investigate behaviors that are only seen under load from a client app. I don't think it. Debug process halt query process in bouth variants - remote | protocol. Remote debugging has one advance. I can monitor any living plpgsql process, but I have to connect to some special port, and it can be problem. Protocol debugging can be supported libpq, and all clients libpq can debug. But is problem if PostgreSQL support bouth variants? btw: debuging have to be only for some users, GRANT DEBUG ON LANGUAGE plpgsql TO .. For me, is better variant if I can debug plpgsql code in psql console. Without spec application. I don't speak so spec application don't have to exists (from my view, ofcourse). Maybe: set debug_mode to true; -- if 't' then func stmt has src reset function myfce(integer, integer); -- need recompilation create breakpoint on myfce(integer, integer) line 1; select myfce(10,10); dbg \l .. list current line \c .. continue \n .. next stmt \L .. show src \s .. show stack \b .. switch breakpoint \q .. quit function select myvar+10 .. any sql expression variable .. print variable \c myfce - 10 that's all. Maybe I have big fantasy :). Regards Pavel + small argument: if psql support debug mode, I don't need leave my emacs postgresql mode. I don't know exactly how to cause such a connection to get set up, especially remotely. But we should try to think of a way. 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]) ---(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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
Stephen Frost [EMAIL PROTECTED] writes: * Bruno Wolff III ([EMAIL PROTECTED]) wrote: Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. We have a precedent, which is that RENAME checks for create rights. If you want to lean on the argument that this is just a shortcut for dropping the object and then recreating it somewhere else, then you need (a) the right to drop the object --- which is inherent in being the old owner, and (b) the right to create the new object, which means that (b1) you can become the role you wish to have owning the object, and (b2) *as that role* you would have the rights needed to create the object. Stephen's original analysis covers (a) and (b1) but not (b2). With (b2) I'd agree that it's just a useful shortcut. I don't see a need to treat SECURITY DEFINER functions as superuser-only. We've had that facility since 7.3 or so and no one has complained that it's too dangerous. 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: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
Title: Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger I'm psyched for EDB to particpate and/or in some way sponsor this effort. How can we best help to make this a reality sooner rather than later?? There's going to be a painful period later this year when Mysqueel is able to claim that their production db has more ansi compatability than PG (at least for triggers and stored procs). It'll be very kewl having native PG with a fully ansi-iso compliant stored procedure language with an efficient and clean implementation with great performance charateristics and a debugger to boot... --Luss --Original Message-- From: Jonah H. Harris To: Dave Cramer Cc: Pavel Stehule Cc: Tom Lane Cc: Neil Conway Cc: Jan Wieck Cc: Denis Lussier Cc: pgsql-hackers@postgresql.org Sent: Jun 28, 2005 5:58 PM Subject: Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger Dave, I lean with you and Tom. While running it over the same libpq protocol would be helpful in some ways, it would have a lot of drawbacks and would really change the function of libpq. I think a separate debugging protocol is in order. Also, as far as bytecode comments go, let's separate them from this thread. I have a pretty sweet hand-written stack-based VM that understands PL/SQL, but it's kinda old and written using PCCTS 1.33 (a recursive descent parser). It has compilation, decompilation, and full debugging capabilities. Unfortunately, PCCTS is no longer maintained as Terrence Parr (the originator) has since moved to ANTLR. ANTLR currently does not generate C code although I have done some starting work on it (ANTLR currently generates Python, Java, or C++). I don't suggest we really reuse one of the current VMs as it would require a lot more support and coordination. Let's take the bytecode discussion off this thread and move it to another. There is certainly a good and bad side to using bytecode and I would be glad to discuss it in another thread. Dave Cramer wrote: Pavel, I am in agreement with Tom here, we should use a separate port, and protocol specifically designed for this. My understanding is that this protocol would be synchronous, and be used for transferring state information, variables, etc back and forth whereas the existing protocol would still be used to transfer data back --Original Message Truncated-- --Luss
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
I lean with you and Tom. While running it over the same libpq protocol would be helpful in some ways, it would have a lot of drawbacks and would really change the function of libpq. I think a separate debugging protocol is in order. One message? I can't belive :). work on it (ANTLR currently generates Python, Java, or C++). I don't suggest we really reuse one of the current VMs as it would require a lot more support and coordination. Let's take the bytecode discussion off this thread and move it to another. There is certainly a good and bad side to using bytecode and I would be glad to discuss it in another thread. I see only one advantage of WM - sharing between languages. But SQL/PSM or PL/pgSQL are not clasic languages. Big advantage is big disadvantage too - relation on SQL engine. I can use all SQL types, but I can't to do efective concation of strings. Sorry, I don't see any benefit of bytecode for these languages. PL/pgSQL works fine (for specific task). What can be better? o evaluation of expressions. -- needs integration with sql parser o debugging o persistent compiled code o syntax Please, write me, private, your opinions. And don't scowl at me, so I am in oportunity :). Regards Pavek ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Implementing SQL/PSM for PG 8.2 - debugger
There's going to be a painful period later this year when Mysqueel is able to claim that their production db has more ansi compatability than PG (at least for triggers and stored procs). MySQL5 is really comparable with Pg8, but Firebird2 or SQLlite3 too. But from my perspective procedural language isn't essentials. Possiblity run perl or python prucedures is important. Today is first day of discussion and there is half of year space for developing. It'll be very kewl having native PG with a fully ansi-iso compliant stored procedure language with an efficient and clean implementation with great performance charateristics and a debugger to boot... Who not? ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Role syntax (or, SQL99 versus sanity)
On Tue, Jun 28, 2005 at 12:29:22PM -0400, Tom Lane wrote: One objection to this is that misspelling a privilege keyword would give you a complaint about unknown role, which might be a bit confusing; but I suspect we cannot avoid that anyway --- there is absolutely no basis on which we can say that GRANT INSIRT TO joe; This alone makes me want to ditch the SQL99 syntax... IMHO there should be a definative way to differentiate between a role grant and a privilege grant. But I tend to agree that supporting SQL99 is a good thing, so... How horrid would it be to support both SQL99 and the suggested GRANT ROLE syntax, possible with a means to turn off the SQL99 syntax. This would allow catching typo'd GRANT privilege statements. Another possibility is to support GRANT, GRANT ROLE, and GRANT PRIVILEGE, and strongly suggest that users use the latter 2 and not the first one. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] For review: dbsize patch
Dave Page wrote: Dave Page wrote: The attached patch is an update of the dbsize integration patch discussed last week. This version includes the following functions: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) ... Uh, do any of these include the index size? TOAST size? No, only total_relation_size() does that. And we are dropping total_relation_size() in this patch, right? I do like the new redesign --- it is very clear and consistent, and it is clear you are looking at relation/tablespace/database levels in the API. Can we rename pg_relation_size to be pg_object_size(), because it handles indexes and TOAST, and use pg_relation_size to return the total usage of relations, and error if called with a TOAST or index? I would like to give some way to report a total without having to query the system catalogs. So should we include this new feature, and if so, how is it best added - rewrite in C, or one long line in pg_proc? I would follow whatever we do in pg_proc now. There are a couple of SQL functions in there, but they are nowhere near as long as this one. I'll look at implementing it in C. Regards, Dave. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CVS pg_config --includedir-server broken
strk wrote: The valure returned from pg_config --includedir-server is broken as of CVS. It points to unexistent directory: /home/extra/pgroot-cvs/include/server Correct value would be: /home/extra/pgroot-cvs/include/postgresql/server Well, on my system on CVS is right: $ pg_config --includedir-server /usr/var/local/postgres/include/server What configure flags did you use to set the install locations? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem with dblink regression test - FIXED
On Tue, Jun 28, 2005 at 02:28:11PM -0400, Andrew Dunstan wrote: Jim C. Nasby wrote: All the logs for the most recent run against HEAD are now at http://stats.distributed.net/~buildfarm/ A quick look shows that when you use --with-libraries=/foo/bar the generated link line for libraries says -L/foo/bar -lpq and it should probably be the other way around (as it is for the executables). So I suspect we need some makefile tuning. You were correct. This patch fixes it: Index: Makefile.shlib === RCS file: /projects/cvsroot/pgsql/src/Makefile.shlib,v retrieving revision 1.90 diff -c -r1.90 Makefile.shlib *** Makefile.shlib 20 Nov 2004 21:13:04 - 1.90 --- Makefile.shlib 29 Jun 2005 00:21:10 - *** *** 240,246 SHLIB_LINK += -ltermcap -lstdc++.r4 -lbind -lsocket -L/boot/develop/lib/x86 endif ! SHLIB_LINK := $(filter -L%, $(LDFLAGS)) $(SHLIB_LINK) ifeq ($(enable_rpath), yes) SHLIB_LINK += $(rpath) endif --- 240,246 SHLIB_LINK += -ltermcap -lstdc++.r4 -lbind -lsocket -L/boot/develop/lib/x86 endif ! SHLIB_LINK := $(SHLIB_LINK) $(filter -L%, $(LDFLAGS)) ifeq ($(enable_rpath), yes) SHLIB_LINK += $(rpath) endif -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Users/Groups - Roles
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: * Bruno Wolff III ([EMAIL PROTECTED]) wrote: Creating objects in particular schemas or databases is not something that all roles may be able to do. Yeah, I'm not entirely sure what I think about this issue. We have a precedent, which is that RENAME checks for create rights. Ah, ok. Precedent is good. If you want to lean on the argument that this is just a shortcut for dropping the object and then recreating it somewhere else, then you need (a) the right to drop the object --- which is inherent in being the old owner, and (b) the right to create the new object, which means that (b1) you can become the role you wish to have owning the object, and (b2) *as that role* you would have the rights needed to create the object. Stephen's original analysis covers (a) and (b1) but not (b2). With (b2) I'd agree that it's just a useful shortcut. Right. Ok, I'll develop a patch which covers (a), (b1) and (b2). I'll also go through all of the superuser() calls in src/backend/commands/ and check for other places we may need *_ownercheck calls. I expect to have the patch done either tonight or tommorow. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCHES] Dbsize backend integration
Dave Page wrote: The attached patch integrates dbsize functions into the backend, as per discussion on -hackers. The following functions are included: pg_relation_size(text) - Get relation size by name/schema.name pg_relation_size(oid)- Get relation size by OID pg_tablespace_size(name) - Get tablespace size by name pg_tablespace_size(oid) - Get tablespace size by OID pg_database_size(name) - Get database size by name pg_database_size(oid)- Get database size by OID pg_table_size(text)- Get table size (including all indexes and toast tables) by name/schema.name pg_table_size(oid) - Get table size (including all indexes and toast tables) by OID pg_size_pretty(int8) - Pretty print (and round) the byte size specified (eg, 123456 = 121KB) This is based on the dbsize contrib module, and previous patches from Andreas Pflug and Ed L. The dbsize module should be removed once this is applied, and the catalog version incremented as I haven't included that in the patch. OK, so you went with relation as heap/index/toast only, and table as the total of them. I am not sure that makes sense because we usually equate relation with table, and an index isn't a relation, really. Do we have to use pg_object_size? Is there a better name? Are indexes/toasts even objects? Of course, these issues are all minor, but we might as well get them resolved. -- 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 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: [HACKERS] For review: Server instrumentation patch
[ pick up new version.] Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Dave Page wrote: [Resent as the list seems to have rejected yesterdays attempt] As per Bruce's request, here's a copy of Andreas' server instrumentation patch for review. I've separated out the dbsize stuff and pg_terminate_backend is also not included. This version was generated against CVS today. As far as I can tell from review of comments made back to pre-8.0, all security and other concerns raised have been addressed. Regards, Dave. Content-Description: instrumentation.tar.gz [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- 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 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
[HACKERS] Open items
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 - -- 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
[HACKERS] Avoiding io penalty when updating large objects
I would like to write a postgres extension type which represents a btree of data and allows me to access and modify elements within that logical btree. Assume the type is named btree_extension, and I have the table: CREATE TABLE example ( a TEXT, b TEXT, c BTREE_EXTENSION, UNIQUE(a,b) ); If, for a given row, the value of c is, say, approximately 2^30 bytes large, then I would expect it to be divided up into 8K chunks in an external table, and I should be able to fetch individual chunks of that object (by offset) rather than having to detoast the whole thing. But what if I want to update a single chunk, or only a couple chunks? How can I go about loading chunks, modifying them, and writing them back to disk, without incurring the overhead of writing 2^30 bytes back out to disk? And if I can do this in a hand coded c function, what does the corresponding SQL statement look like to call the function? Is it an update statement? Also, is it possible that only the rows in the *external* table get marked as updated during my transaction, or will the row in the example table be marked as updated? I expect this is not possible, but it would be really great if it were, and I haven't found a definitive No, you can't do this in the documentation yet. The idea is to store the first and second level entries of a tree directly in columns a and b, but then to store arbitrarily deep children in a btree type stored in column c. It doesn't make sense to have a really wide table to represent the tree for multiple reasons, mostly involving data duplication in the leftward columns but also because you can't know ahead of time how wide to make the table. I look forward to any useful responses. Thanks, Mark Dilger ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 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 signature.asc Description: Digital signature
Re: [HACKERS] Avoiding io penalty when updating large objects
On Tue, Jun 28, 2005 at 07:38:43PM -0700, Mark Dilger wrote: I would like to write a postgres extension type which represents a btree of data and allows me to access and modify elements within that logical btree. Assume the type is named btree_extension, and I have the table: CREATE TABLE example ( a TEXT, b TEXT, c BTREE_EXTENSION, UNIQUE(a,b) ); If, for a given row, the value of c is, say, approximately 2^30 bytes large, then I would expect it to be divided up into 8K chunks in an external table, and I should be able to fetch individual chunks of that object (by offset) rather than having to detoast the whole thing. I don't think you can do this with the TOAST mechanism. The problem is that there's no API which allows you to operate on only certain chunks of data. You can do it with large objects though -- those you create with lo_creat(). You can do lo_seek(), lo_read() and lo_write() as you see fit. Of course, this allows you to change the LO by chunks. -- Alvaro Herrera (alvherre[a]surnet.cl) No hay hombre que no aspire a la plenitud, es decir, la suma de experiencias de que un hombre es capaz ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open items
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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Open items
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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Moving sequences to another schema
Bernd Helmle [EMAIL PROTECTED] writes: Whats the least that should go to -patches for feature release on 1 July (only to know, if i can hold timeline or not).?? Something reasonably complete, working, credible. We'll cut you slack on documentation changes and regression tests, and if it has a bug or three that's what beta testing is for; but it has to at least look like it will work. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Feature request from irc...
Is it possible for a pl/pgsql trigger function to look at the sql command that caused it to be triggered? If not, is this an idea? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
