Re: [HACKERS] Maintenance announcement for alderaan/barzan/minshara.postgresql.org
Stefan Kaltenbrunner wrote: there will be planned maintenance tomorrow(4 March 2009) from 09:00 to 10:00 CET for the following servers and corresponding services: barzan.postgresql.org - affecting: * xindi.postgresql.org (wiki.postgresql.org) * rakhar.postgresql.org (planet.postgresql.org) * pentarus.postgresql.org (web development sandbox) alderaan.postgreql.org - affecting: * qonos.postgresql.org (git.postgresql.org) * yridian.postgresql.org (PostgreSQL EU) * endicor.postgresql.org minshara.postgresql.org - affecting: * fornax.postgresql.org (ftp.postgresql.org) * gothos.postgresql.org * wysanti.postgresql.org (static www mirror) maintenance completed and all services back up. Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [pgsql-www] [HACKERS] Maintenance announcement for alderaan/barzan/minshara.postgresql.org
Stefan Kaltenbrunner wrote: Stefan Kaltenbrunner wrote: there will be planned maintenance tomorrow(4 March 2009) from 09:00 to 10:00 CET for the following servers and corresponding services: barzan.postgresql.org - affecting: * xindi.postgresql.org (wiki.postgresql.org) * rakhar.postgresql.org (planet.postgresql.org) * pentarus.postgresql.org (web development sandbox) alderaan.postgreql.org - affecting: * qonos.postgresql.org (git.postgresql.org) * yridian.postgresql.org (PostgreSQL EU) * endicor.postgresql.org minshara.postgresql.org - affecting: * fornax.postgresql.org (ftp.postgresql.org) * gothos.postgresql.org * wysanti.postgresql.org (static www mirror) maintenance completed and all services back up. Thanks, Stefan, good job. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
Selena Deckelmann wrote: Tom Lane wrote: Selena Deckelmann sel...@endpoint.com writes: From the comment in hba.c, it appears that the desired behavior is to have the system ignore the failure, I'm not sure how you could possibly read that comment that way. Right. Sorry, poor choice of words. I meant don't die on reload, essentially. The comment is wrong, the patch is correct. It might be sane to distinguish initial load from reload, but I think the behavior is correct as-is for initial load. Agreed. The patch solves this perfectly fine. The caller takes care of sending a FATAL error if it's in startup mode. Also, if we are going to do something like this, we should make sure it's consistent across all the config files. Ok. I can do that. I'll check with some other people before I send another patch, and I'll go through the rest of the config file loads tomorrow. From what I can tell, it is already consistent (once this fix is applied). Permissions wrong on postgresql.conf already sends a warning and not a FATAL. Same for pg_ident.conf. So. I've updated the comment, and applied your patch. Thanks! (You also added a trailing space on the if line - might want to check if you can get your editor to warn about that. Or git diff will..) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Immediate shutdown and system(3)
Per discussion, here's a patch for pg_standby in REL8_3_STABLE. The
signal handling is changed so that SIGQUIT no longer triggers failover,
but immediately kills pg_standby, triggering FATAL death of the startup
process too. That's what you want with immediate shutdown.
SIGUSR1 is now accepted as a signal to trigger failover. SIGINT is still
accepted too, but that should be considered deprecated since we're
likely to use SIGINT for immediate shutdown (for startup process) in 8.4.
We should document the use of signals to trigger failover in the
manual... Any volunteers?
This should be noted in the release notes:
If you are using pg_standby, and if you are using signals (e.g killall
-SIGINT pg_standby) to trigger failover, change your scripts to use
SIGUSR1 instead of SIGQUIT or SIGINT. SIGQUIT no longer triggers
failover, but aborts the recovery and shuts down the standby database.
SIGINT is still accepted as failover trigger, but should be considered
as deprecated and will also be changed to trigger immediate shutdown in
a future release.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Index: pg_standby.c
===
RCS file: /cvsroot/pgsql/contrib/pg_standby/pg_standby.c,v
retrieving revision 1.10.2.3
diff -c -r1.10.2.3 pg_standby.c
*** pg_standby.c 6 Jan 2009 17:27:19 - 1.10.2.3
--- pg_standby.c 4 Mar 2009 09:13:34 -
***
*** 451,464
signaled = true;
}
/* MAIN */
int
main(int argc, char **argv)
{
int c;
! (void) signal(SIGINT, sighandler);
! (void) signal(SIGQUIT, sighandler);
while ((c = getopt(argc, argv, cdk:lr:s:t:w:)) != -1)
{
--- 451,487
signaled = true;
}
+ /* We don't want SIGQUIT to core dump */
+ static void
+ sigquit_handler(int sig)
+ {
+ signal(SIGINT, SIG_DFL);
+ kill(getpid(), SIGINT);
+ }
+
+
/* MAIN */
int
main(int argc, char **argv)
{
int c;
! /*
! * You can send SIGUSR1 to trigger failover.
! *
! * Postmaster uses SIGQUIT to request immediate shutdown. The default
! * action is to core dump, but we don't want that, so trap it and
! * commit suicide without core dump.
! *
! * We used to use SIGINT and SIGQUIT to trigger failover, but that
! * turned out to be a bad idea because postmaster uses SIGQUIT to
! * request immediate shutdown. We still trap SIGINT, but that is
! * deprecated. We will likely switch to using SIGINT for immediate
! * shutdown in future releases.
! */
! (void) signal(SIGUSR1, sighandler);
! (void) signal(SIGINT, sighandler); /* deprecated, use SIGUSR1 */
! (void) signal(SIGQUIT, sigquit_handler);
while ((c = getopt(argc, argv, cdk:lr:s:t:w:)) != -1)
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
Joshua Tolley wrote: On Wed, Mar 04, 2009 at 09:43:55AM +0100, Magnus Hagander wrote: So. I've updated the comment, and applied your patch. Thanks! What would it take to get it applied to a few earlier versions as well? I guess you maintaining your own fork? ;-) Simply put, earlier versions threw away the contents of pg_hba and reloaded it completely. The support for keeping the old one around in case of syntax errors is new for 8.4. You'd basically require backpatching of large parts of that patch, and that's not going to happen. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Wed, Mar 4, 2009 at 11:06 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: While this isn't a bug, it's a reasonable feature request. I've added this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER Patches are welcome. I remember someone proposed a patch for that a long time ago: http://archives.postgresql.org/pgsql-patches/2006-10/msg00154.php AFAICS there were 2 possible follow-ups to this patch: - only manage the case of char/varchar (which is the most common case anyway); - develop a complete infrastructure to handle all the cases as suggested by Gregory. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite
On Wed, Mar 4, 2009 at 11:50 AM, Peter Eisentraut pete...@gmx.net wrote: The question is how you want to implement this in a data type independent fashion. You can't assume that increasing the typmod is a noop for all data types. Sure. See my previous answer on -hackers (I don't think this discussion belong to -bugs) and especially the discussion in the archives about Jonas' patch. -- Guillaume -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Immediate shutdown and system(3)
Fujii Masao wrote: Hi, On Mon, Mar 2, 2009 at 4:59 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Fujii Masao wrote: On Fri, Feb 27, 2009 at 6:52 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I'm leaning towards option 3, but I wonder if anyone sees a better solution. 4. Use the shared memory to tell the startup process about the shutdown state. When a shutdown signal arrives, postmaster sets the corresponding shutdown state to the shared memory before signaling to the child processes. The startup process check the shutdown state whenever executing system(), and determine how to exit according to that state. This solution doesn't change any existing behavior of pg_standby. What is your opinion? That would only solve the problem for pg_standby. Other programs you might use as a restore_command or archive_command like cp or rsync would still core dump on the SIGQUIT. Right. I've just understood your intention. I also agree with option 3 if nobody complains about lack of backward compatibility of pg_standby. If no, how about using SIGUSR2 instead of SIGINT for immediate shutdown of only the archiver and the startup process. SIGUSR2 by default terminates the process. The archiver already uses SIGUSR2 for pgarch_waken_stop, so we need to reassign that function to another signal (SIGINT is suitable, I think). This solution doesn't need signal multiplexing. Thought? Hmm, the startup/archiver process would then in turn need to kill the external command with SIGINT. I guess that would work. There's a problem with my idea of just using SIGINT instead of SIGQUIT. Some (arguably bad-behaving) programs trap SIGINT and exit() with a return code. The startup process won't recognize that as killed by signal, and we're back to same problem we have with pg_standby that the startup process doesn't die but continues with the startup. Notably rsync seems to behave like that. BTW, searching the archive, I found this long thread about this same issue: http://archives.postgresql.org/pgsql-hackers/2006-11/msg00406.php The idea of SIGUSR2 was mentioned there as well, as well as the idea of reimplementing system(3). The conclusion of that thread was the usage of setsid() and process groups, to ensure that the SIGQUIT is delivered to the archive/recovery_command. I'm starting to feel that this is getting too complicated. Maybe we should just fix pg_standby to not trap SIGQUIT, and live with the core dumps... -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
Tom Lane wrote: Emmanuel Cecchet m...@asterdata.com writes: It looks like the behavior of regclass is not consistent when table names are quoted. The name is returned without the quotes if the name is lower case with eventual trailing numbers, otherwise it is returned with quotes. It's intentional that it quotes only when needed. This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? Thanks, Emmanuel -- Emmanuel Cecchet Aster Data Systems Web: http://www.asterdata.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet m...@asterdata.com wrote: This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? regclass's output format is intended for human readers, not for joining against text columns. Why would you need to be joining between regclass and text anyways? Normally you would be joining regclass to either regclass columns or oid columns where it does an integer comparison. Actually normally you would just be joining oid columns since most catalog columns are declared to be oids rather than regfoo columns :( -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
Martin Pihlak wrote: Proposal attached. This adds two C functions: List *GetForeignConnectionOptions(Oid serverid, Oid userid); char *GetForeignConnectionString(Oid serverid, Oid userid); One for obtaining all of the connection related options as a list, and another for transforming these options into a libpq conninfo string. The latter should be useful for dblink (although the userid and serverid need to be obtained first). On top of those there are two SQL accessible functions: pg_foreign_connection_options(server name, user name = current_user, OUT option_class text, OUT option_name text, OUT option_value text); pg_foreign_connection_string(server name, user name = current_user); These should initially be restricted from ordinary users -- grant explicitly if the user should see the connect strings. Back to this one ... I have been thinking about this for a great while now. I am not yet comfortable with how we manage the access rights here. We have restricted access to the user mappings catalog to hide passwords, but it is not entirely clear why a password must be stored in a user mapping. It could also be stored with a server, if we only want to use one global connection for everybody. I think the proper way to handle it might be to introduce a new privilege type -- call it SELECT if you like -- that determines specifically whether you can *see* the options of a foreign-data wrapper, foreign server, or user mapping, respectively. As opposed to USAGE, which means you can use the object for connecting (in the future). This might have other uses: The owner of a server might want to hide the host name, but still let you connect. Comments? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SIGHUP during recovery
Fujii Masao wrote: Currently, the startup process ignores SIGHUP. The attached patch allows the startup process to re-read config file: when SIGHUP arrives, the startup process also receives the signal from postmaster and reload the settings in main redo apply loop. Obviously, this is useful to change the parameters which the startup process may use (e.g. log_line_prefix, log_checkpoints). Thanks, committed. The fact that bgwriter can run simultaneously with the startup process makes this more important than before. Otherwise if you change something like log_line_prefix, bgwriter will use the new setting but startup process will not. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Make SIGHUP less painful if pg_hba.conf is not readable
On Wed, Mar 04, 2009 at 10:28:42AM +0100, Magnus Hagander wrote:
Joshua Tolley wrote:
On Wed, Mar 04, 2009 at 09:43:55AM +0100, Magnus Hagander wrote:
So. I've updated the comment, and applied your patch. Thanks!
What would it take to get it applied to a few earlier versions as well?
I guess you maintaining your own fork? ;-)
Simply put, earlier versions threw away the contents of pg_hba and
reloaded it completely. The support for keeping the old one around in
case of syntax errors is new for 8.4. You'd basically require
backpatching of large parts of that patch, and that's not going to happen.
//Magnus
Given that we ran into the problem in 8.3.6, how about something like
the attached to apply to it?
- Josh / eggyknap
Index: postmaster.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.551.2.1
diff -r1.551.2.1 postmaster.c
1917c1917,1925
load_hba();
---
if (access(HbaFileName, R_OK) == 0)
{
load_hba();
}
else
{
ereport(WARNING,
(errmsg(HBA file %s is unreadable, not reloading, HbaFileName)));
}
signature.asc
Description: Digital signature
[HACKERS] SYNONYMs revisited
Way back in this thread[1] one of the arguments against allowing some version of CREATE SYNONYM was that we couldn't create a synonym for an object in a remote database. Will the SQL/MED work make this sort of thing a possibility? I realize since it's not standard anyway, there's still a discussion or two to be had about how precisely it should work, but thought I'd raise the possibility. - Josh / eggyknap [1] http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php signature.asc Description: Digital signature
Re: [HACKERS] Regclass and quoted table names
Greg Stark st...@enterprisedb.com writes: On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet m...@asterdata.com wrote: This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name. Is there a way to override the default behavior to have a consistent quoted or non-quoted result? regclass's output format is intended for human readers, not for joining against text columns. Why would you need to be joining between regclass and text anyways? The quoting behavior is the *least* of your problems if you're trying to do that. Are you aware of how it works vis-a-vis search_path? Have you thought about what will happen when you rename a table? Use plain oids or regclass values, not a text column, if you are trying to store table identities. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera wrote:
Hmm, I had thought that pg_dump only wanted the header file, not the
keywords.o object file. I now see that I was wrong. I agree that your
proposed solution is a lot better. I'll see about it.
Here it is. The #ifdef parts seem a bit ugly, but I'm not sure how can
this be improved, given that ECPG is already using this file.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/parser/keywords.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.210
diff -c -p -r1.210 keywords.c
*** src/backend/parser/keywords.c 24 Feb 2009 10:06:33 - 1.210
--- src/backend/parser/keywords.c 4 Mar 2009 14:34:53 -
***
*** 29,41
#define YYSTYPE int
#include parser/keywords.h
#ifndef ECPG_COMPILE
#include parser/gram.h
! #else
#include preproc.h
#endif
/*
* List of keyword (name, token-value, category) entries.
*
* !!WARNING!!: This list must be sorted by ASCII name, because binary
--- 29,54
#define YYSTYPE int
#include parser/keywords.h
+
#ifndef ECPG_COMPILE
+ #ifndef FRONTEND
#include parser/gram.h
! #endif
! #else /* ECPG_COMPILE */
#include preproc.h
#endif
/*
+ * We don't want to include the gram.h file on frontend builds, except ECPG, so
+ * leave out the second struct member in that case.
+ */
+ #if !defined FRONTEND || defined ECPG_COMPILE
+ #define PG_KEYWORD(a,b,c) {a,b,c}
+ #else
+ #define PG_KEYWORD(a,b,c) {a,c}
+ #endif
+
+ /*
* List of keyword (name, token-value, category) entries.
*
* !!WARNING!!: This list must be sorted by ASCII name, because binary
***
*** 43,439
*/
const ScanKeyword ScanKeywords[] = {
/* name, value, category */
! {abort, ABORT_P, UNRESERVED_KEYWORD},
! {absolute, ABSOLUTE_P, UNRESERVED_KEYWORD},
! {access, ACCESS, UNRESERVED_KEYWORD},
! {action, ACTION, UNRESERVED_KEYWORD},
! {add, ADD_P, UNRESERVED_KEYWORD},
! {admin, ADMIN, UNRESERVED_KEYWORD},
! {after, AFTER, UNRESERVED_KEYWORD},
! {aggregate, AGGREGATE, UNRESERVED_KEYWORD},
! {all, ALL, RESERVED_KEYWORD},
! {also, ALSO, UNRESERVED_KEYWORD},
! {alter, ALTER, UNRESERVED_KEYWORD},
! {always, ALWAYS, UNRESERVED_KEYWORD},
! {analyse, ANALYSE, RESERVED_KEYWORD}, /* British spelling */
! {analyze, ANALYZE, RESERVED_KEYWORD},
! {and, AND, RESERVED_KEYWORD},
! {any, ANY, RESERVED_KEYWORD},
! {array, ARRAY, RESERVED_KEYWORD},
! {as, AS, RESERVED_KEYWORD},
! {asc, ASC, RESERVED_KEYWORD},
! {assertion, ASSERTION, UNRESERVED_KEYWORD},
! {assignment, ASSIGNMENT, UNRESERVED_KEYWORD},
! {asymmetric, ASYMMETRIC, RESERVED_KEYWORD},
! {at, AT, UNRESERVED_KEYWORD},
! {authorization, AUTHORIZATION, TYPE_FUNC_NAME_KEYWORD},
! {backward, BACKWARD, UNRESERVED_KEYWORD},
! {before, BEFORE, UNRESERVED_KEYWORD},
! {begin, BEGIN_P, UNRESERVED_KEYWORD},
! {between, BETWEEN, TYPE_FUNC_NAME_KEYWORD},
! {bigint, BIGINT, COL_NAME_KEYWORD},
! {binary, BINARY, TYPE_FUNC_NAME_KEYWORD},
! {bit, BIT, COL_NAME_KEYWORD},
! {boolean, BOOLEAN_P, COL_NAME_KEYWORD},
! {both, BOTH, RESERVED_KEYWORD},
! {by, BY, UNRESERVED_KEYWORD},
! {cache, CACHE, UNRESERVED_KEYWORD},
! {called, CALLED, UNRESERVED_KEYWORD},
! {cascade, CASCADE, UNRESERVED_KEYWORD},
! {cascaded, CASCADED, UNRESERVED_KEYWORD},
! {case, CASE, RESERVED_KEYWORD},
! {cast, CAST, RESERVED_KEYWORD},
! {catalog, CATALOG_P, UNRESERVED_KEYWORD},
! {chain, CHAIN, UNRESERVED_KEYWORD},
! {char, CHAR_P, COL_NAME_KEYWORD},
! {character, CHARACTER, COL_NAME_KEYWORD},
! {characteristics, CHARACTERISTICS, UNRESERVED_KEYWORD},
! {check, CHECK, RESERVED_KEYWORD},
! {checkpoint, CHECKPOINT, UNRESERVED_KEYWORD},
! {class, CLASS, UNRESERVED_KEYWORD},
! {close, CLOSE, UNRESERVED_KEYWORD},
! {cluster, CLUSTER, UNRESERVED_KEYWORD},
! {coalesce, COALESCE, COL_NAME_KEYWORD},
! {collate, COLLATE, RESERVED_KEYWORD},
! {column, COLUMN, RESERVED_KEYWORD},
! {comment, COMMENT, UNRESERVED_KEYWORD},
! {commit, COMMIT, UNRESERVED_KEYWORD},
! {committed, COMMITTED, UNRESERVED_KEYWORD},
! {concurrently, CONCURRENTLY, UNRESERVED_KEYWORD},
! {configuration, CONFIGURATION, UNRESERVED_KEYWORD},
! {connection, CONNECTION, UNRESERVED_KEYWORD},
! {constraint, CONSTRAINT, RESERVED_KEYWORD},
! {constraints, CONSTRAINTS, UNRESERVED_KEYWORD},
! {content, CONTENT_P, UNRESERVED_KEYWORD},
! {continue, CONTINUE_P, UNRESERVED_KEYWORD},
! {conversion, CONVERSION_P, UNRESERVED_KEYWORD},
! {copy, COPY, UNRESERVED_KEYWORD},
! {cost, COST, UNRESERVED_KEYWORD},
! {create, CREATE, RESERVED_KEYWORD},
! {createdb, CREATEDB, UNRESERVED_KEYWORD},
! {createrole, CREATEROLE, UNRESERVED_KEYWORD},
! {createuser, CREATEUSER, UNRESERVED_KEYWORD},
! {cross, CROSS, TYPE_FUNC_NAME_KEYWORD},
! {csv, CSV, UNRESERVED_KEYWORD},
! {ctype, CTYPE,
Re: [HACKERS] building pg_dump doesn't work
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Perhaps this could be made less ugly by only having the ScanKeywords array in the .c file, and #including that into other .c files in src/backend/parser, ecpg and pg_dump. What I'd suggest is something similar to the design of the errcodes.h header: create a header file containing just the list of PG_KEYWORD macro calls, and have the different users #include it after defining that macro appropriately. Having .c files include other .c files is usually best avoided on least-surprise grounds. Not sure what to do about ScanKeywordLookup function. Yeah, duplicating that function is a bit annoying. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera wrote:
Alvaro Herrera wrote:
Hmm, I had thought that pg_dump only wanted the header file, not the
keywords.o object file. I now see that I was wrong. I agree that your
proposed solution is a lot better. I'll see about it.
Here it is. The #ifdef parts seem a bit ugly, but I'm not sure how can
this be improved, given that ECPG is already using this file.
Perhaps this could be made less ugly by only having the ScanKeywords
array in the .c file, and #including that into other .c files in
src/backend/parser, ecpg and pg_dump.
So, keywords.c would look like:
#include parser/keywords.h
const ScanKeyword ScanKeywords[] = {
/* name, value, category */
PG_KEYWORD(abort, ABORT_P, UNRESERVED_KEYWORD),
PG_KEYWORD(absolute, ABSOLUTE_P, UNRESERVED_KEYWORD),
PG_KEYWORD(access, ACCESS, UNRESERVED_KEYWORD),
...
And there would be a new file in src/bin/pg_dump, say dumpkeywords.c,
that looks like this:
#include c.h
#define PG_KEYWORD(a,b,c) {a,b,c}
#include src/backend/parser/keywords.c
Not sure what to do about ScanKeywordLookup function.
/*
+ * We don't want to include the gram.h file on frontend builds, except ECPG,
so
+ * leave out the second struct member in that case.
+ */
+ #if !defined FRONTEND || defined ECPG_COMPILE
+ #define PG_KEYWORD(a,b,c) {a,b,c}
+ #else
+ #define PG_KEYWORD(a,b,c) {a,c}
+ #endif
Doesn't that put 'c' into the wrong field in ScanKeyword struct? It only
compiles because both 'value' and 'category' are int16.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Perhaps this could be made less ugly by only having the ScanKeywords array in the .c file, and #including that into other .c files in src/backend/parser, ecpg and pg_dump. What I'd suggest is something similar to the design of the errcodes.h header: create a header file containing just the list of PG_KEYWORD macro calls, and have the different users #include it after defining that macro appropriately. Having .c files include other .c files is usually best avoided on least-surprise grounds. Seems doable. Not sure what to do about ScanKeywordLookup function. Yeah, duplicating that function is a bit annoying. Another new file, backend/parser/kwlookup.c perhaps? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SYNONYMs revisited
Joshua Tolley wrote: Way back in this thread[1] one of the arguments against allowing some version of CREATE SYNONYM was that we couldn't create a synonym for an object in a remote database. Will the SQL/MED work make this sort of thing a possibility? I realize since it's not standard anyway, there's still a discussion or two to be had about how precisely it should work, but thought I'd raise the possibility. - Josh / eggyknap [1] http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php I still think that this is a useful feature. If i remember correctly there were two killer arguments against this: - it encourages people to do lousy development - extra overhead personally i think that this won't be revisted - i cannot see a real connection to SQL/MED here. best regards, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Support, Consulting, Training www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Heikki Linnakangas wrote:
Alvaro Herrera wrote:
/*
+ * We don't want to include the gram.h file on frontend builds, except
ECPG, so
+ * leave out the second struct member in that case.
+ */
+ #if !defined FRONTEND || defined ECPG_COMPILE
+ #define PG_KEYWORD(a,b,c) {a,b,c}
+ #else
+ #define PG_KEYWORD(a,b,c) {a,c}
+ #endif
Doesn't that put 'c' into the wrong field in ScanKeyword struct? It only
compiles because both 'value' and 'category' are int16.
No, because I had the header with the second field omitted too.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera wrote:
Tom Lane wrote:
What I'd suggest is something similar to the design of the errcodes.h
header: create a header file containing just the list of PG_KEYWORD
macro calls, and have the different users #include it after defining
that macro appropriately. Having .c files include other .c files is
usually best avoided on least-surprise grounds.
Seems doable.
Attached.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/parser/Makefile
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/Makefile,v
retrieving revision 1.48
diff -c -p -r1.48 Makefile
*** src/backend/parser/Makefile 4 Oct 2008 21:56:54 - 1.48
--- src/backend/parser/Makefile 4 Mar 2009 15:32:52 -
*** override CPPFLAGS := -I$(srcdir) $(CPPFL
*** 14,20
OBJS= analyze.o gram.o keywords.o parser.o parse_agg.o parse_cte.o parse_clause.o \
parse_expr.o parse_func.o parse_node.o parse_oper.o parse_relation.o \
! parse_type.o parse_coerce.o parse_target.o parse_utilcmd.o scansup.o
FLEXFLAGS = -CF
--- 14,20
OBJS= analyze.o gram.o keywords.o parser.o parse_agg.o parse_cte.o parse_clause.o \
parse_expr.o parse_func.o parse_node.o parse_oper.o parse_relation.o \
! parse_type.o parse_coerce.o parse_target.o parse_utilcmd.o scansup.o kwlookup.o
FLEXFLAGS = -CF
Index: src/backend/parser/keywords.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/keywords.c,v
retrieving revision 1.210
diff -c -p -r1.210 keywords.c
*** src/backend/parser/keywords.c 24 Feb 2009 10:06:33 - 1.210
--- src/backend/parser/keywords.c 4 Mar 2009 15:56:35 -
***
*** 3,10
* keywords.c
* lexical token lookup for key words in PostgreSQL
*
- * NB: This file is also used by pg_dump.
- *
*
* Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
* Portions Copyright (c) 1994, Regents of the University of California
--- 3,8
***
*** 15,503
*
*-
*/
! /* Use c.h so that this file can be built in either frontend or backend */
! #include c.h
!
! #include ctype.h
!
! /*
! * This macro definition overrides the YYSTYPE union definition in gram.h.
! * We don't need that struct in this file, and including the real definition
! * would require sucking in some backend-only include files.
! */
! #define YYSTYPE int
!
#include parser/keywords.h
- #ifndef ECPG_COMPILE
#include parser/gram.h
- #else
- #include preproc.h
- #endif
-
- /*
- * List of keyword (name, token-value, category) entries.
- *
- * !!WARNING!!: This list must be sorted by ASCII name, because binary
- * search is used to locate entries.
- */
- const ScanKeyword ScanKeywords[] = {
- /* name, value, category */
- {abort, ABORT_P, UNRESERVED_KEYWORD},
- {absolute, ABSOLUTE_P, UNRESERVED_KEYWORD},
- {access, ACCESS, UNRESERVED_KEYWORD},
- {action, ACTION, UNRESERVED_KEYWORD},
- {add, ADD_P, UNRESERVED_KEYWORD},
- {admin, ADMIN, UNRESERVED_KEYWORD},
- {after, AFTER, UNRESERVED_KEYWORD},
- {aggregate, AGGREGATE, UNRESERVED_KEYWORD},
- {all, ALL, RESERVED_KEYWORD},
- {also, ALSO, UNRESERVED_KEYWORD},
- {alter, ALTER, UNRESERVED_KEYWORD},
- {always, ALWAYS, UNRESERVED_KEYWORD},
- {analyse, ANALYSE, RESERVED_KEYWORD}, /* British spelling */
- {analyze, ANALYZE, RESERVED_KEYWORD},
- {and, AND, RESERVED_KEYWORD},
- {any, ANY, RESERVED_KEYWORD},
- {array, ARRAY, RESERVED_KEYWORD},
- {as, AS, RESERVED_KEYWORD},
- {asc, ASC, RESERVED_KEYWORD},
- {assertion, ASSERTION, UNRESERVED_KEYWORD},
- {assignment, ASSIGNMENT, UNRESERVED_KEYWORD},
- {asymmetric, ASYMMETRIC, RESERVED_KEYWORD},
- {at, AT, UNRESERVED_KEYWORD},
- {authorization, AUTHORIZATION, TYPE_FUNC_NAME_KEYWORD},
- {backward, BACKWARD, UNRESERVED_KEYWORD},
- {before, BEFORE, UNRESERVED_KEYWORD},
- {begin, BEGIN_P, UNRESERVED_KEYWORD},
- {between, BETWEEN, TYPE_FUNC_NAME_KEYWORD},
- {bigint, BIGINT, COL_NAME_KEYWORD},
- {binary, BINARY, TYPE_FUNC_NAME_KEYWORD},
- {bit, BIT, COL_NAME_KEYWORD},
- {boolean, BOOLEAN_P, COL_NAME_KEYWORD},
- {both, BOTH, RESERVED_KEYWORD},
- {by, BY, UNRESERVED_KEYWORD},
- {cache, CACHE, UNRESERVED_KEYWORD},
- {called, CALLED, UNRESERVED_KEYWORD},
- {cascade, CASCADE, UNRESERVED_KEYWORD},
- {cascaded, CASCADED, UNRESERVED_KEYWORD},
- {case, CASE, RESERVED_KEYWORD},
- {cast, CAST, RESERVED_KEYWORD},
- {catalog, CATALOG_P, UNRESERVED_KEYWORD},
- {chain, CHAIN, UNRESERVED_KEYWORD},
- {char, CHAR_P, COL_NAME_KEYWORD},
- {character, CHARACTER, COL_NAME_KEYWORD},
- {characteristics, CHARACTERISTICS, UNRESERVED_KEYWORD},
- {check, CHECK, RESERVED_KEYWORD},
- {checkpoint, CHECKPOINT,
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera wrote:
Alvaro Herrera wrote:
Tom Lane wrote:
What I'd suggest is something similar to the design of the errcodes.h
header: create a header file containing just the list of PG_KEYWORD
macro calls, and have the different users #include it after defining
that macro appropriately. Having .c files include other .c files is
usually best avoided on least-surprise grounds.
Seems doable.
Attached.
Minor fixes over the previous patch.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
diff -u src/bin/pg_dump/Makefile src/bin/pg_dump/Makefile
--- src/bin/pg_dump/Makefile4 Mar 2009 15:25:11 -
+++ src/bin/pg_dump/Makefile4 Mar 2009 17:02:19 -
@@ -27,13 +27,13 @@
all: submake-libpq submake-libpgport pg_dump pg_restore pg_dumpall
-pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) $(libpq_builddir)/libpq.a
+pg_dump: pg_dump.o common.o pg_dump_sort.o $(OBJS) $(KEYWRDOBJS)
$(libpq_builddir)/libpq.a
$(CC) $(CFLAGS) pg_dump.o common.o pg_dump_sort.o $(KEYWRDOBJS) $(OBJS)
$(libpq_pgport) $(LDFLAGS) $(LIBS) -o $...@$(X)
-pg_restore: pg_restore.o $(OBJS) $(libpq_builddir)/libpq.a
+pg_restore: pg_restore.o $(OBJS) $(KEYWRDOBJS) $(libpq_builddir)/libpq.a
$(CC) $(CFLAGS) pg_restore.o $(KEYWRDOBJS) $(OBJS) $(libpq_pgport)
$(LDFLAGS) $(LIBS) -o $...@$(X)
-pg_dumpall: pg_dumpall.o dumputils.o $(libpq_builddir)/libpq.a
+pg_dumpall: pg_dumpall.o dumputils.o $(KEYWRDOBJS) $(libpq_builddir)/libpq.a
$(CC) $(CFLAGS) pg_dumpall.o dumputils.o $(KEYWRDOBJS) $(WIN32RES)
$(libpq_pgport) $(LDFLAGS) $(LIBS) -o $...@$(X)
install: all installdirs
diff -u src/bin/pg_dump/keywords.c src/bin/pg_dump/keywords.c
--- src/bin/pg_dump/keywords.c 4 Mar 2009 16:08:22 -
+++ src/bin/pg_dump/keywords.c 4 Mar 2009 17:03:08 -
@@ -15,18 +15,11 @@
*/
#include postgres_fe.h
-/*
- * This macro definition overrides the YYSTYPE union definition in gram.h.
- * We don't need that struct in this file, and including the real definition
- * would require sucking in some backend-only include files.
- */
-#define YYSTYPE int
-
#include parser/keywords.h
/*
- * We don't need the token number, so leave it out to avoid requiring extra
- * object files from the backend.
+ * We don't need the token number, so leave it out to avoid requiring other
+ * backend headers.
*/
#define PG_KEYWORD(a,b,c) {a,c}
#define TWO_MEMBER_SCANKEYWORD
only in patch2:
unchanged:
--- src/backend/parser/gram.y 24 Feb 2009 10:06:33 - 2.659
+++ src/backend/parser/gram.y 4 Mar 2009 17:04:39 -
@@ -423,7 +423,7 @@ static TypeName *TableFuncTypeName(List
/*
* If you make any token changes, update the keyword table in
- * parser/keywords.c and add new keywords to the appropriate one of
+ * src/include/parser/kwlist.h and add new keywords to the appropriate one of
* the reserved-or-not-so-reserved keyword lists, below; search
* this file for Name classification hierarchy.
*/
@@ -516,7 +516,7 @@ static TypeName *TableFuncTypeName(List
ZONE
-/* The grammar thinks these are keywords, but they are not in the keywords.c
+/* The grammar thinks these are keywords, but they are not in the kwlist.h
* list and so can never be entered directly. The filter in parser.c
* creates these tokens when required.
*/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
Peter Eisentraut pete...@gmx.net writes: I think the proper way to handle it might be to introduce a new privilege type -- call it SELECT if you like -- that determines specifically whether you can *see* the options of a foreign-data wrapper, foreign server, or user mapping, respectively. As opposed to USAGE, which means you can use the object for connecting (in the future). This might have other uses: The owner of a server might want to hide the host name, but still let you connect. How would you implement/enforce that, in the absence of row-level security on the catalogs involved? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pg_dump doesn't work
Alvaro Herrera alvhe...@commandprompt.com writes: Seems doable. Attached. The TWO_MEMBER_SCANKEYWORD business seems a bit dangerous --- if the header file is read without having #defined that correctly, bad things will happen. It might be better to leave that out, always define the struct the same, and just have pg_dump define PG_KEYWORD to fill the value field with zero. Given alignment considerations, you're not saving any space by omitting the field anyhow. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SQL/MED compatible connection manager
On Wed, Mar 04, 2009 at 03:26:36PM +0200, Peter Eisentraut wrote: Martin Pihlak wrote: Proposal attached. This adds two C functions: List *GetForeignConnectionOptions(Oid serverid, Oid userid); char *GetForeignConnectionString(Oid serverid, Oid userid); One for obtaining all of the connection related options as a list, and another for transforming these options into a libpq conninfo string. The latter should be useful for dblink (although the userid and serverid need to be obtained first). On top of those there are two SQL accessible functions: pg_foreign_connection_options(server name, user name = current_user, OUT option_class text, OUT option_name text, OUT option_value text); pg_foreign_connection_string(server name, user name = current_user); These should initially be restricted from ordinary users -- grant explicitly if the user should see the connect strings. Back to this one ... I have been thinking about this for a great while now. I am not yet comfortable with how we manage the access rights here. We have restricted access to the user mappings catalog to hide passwords, but it is not entirely clear why a password must be stored in a user mapping. It could also be stored with a server, if we only want to use one global connection for everybody. I think the proper way to handle it might be to introduce a new privilege type -- call it SELECT if you like -- that determines specifically whether you can *see* the options of a foreign-data wrapper, foreign server, or user mapping, respectively. As opposed to USAGE, which means you can use the object for connecting (in the future). This might have other uses: The owner of a server might want to hide the host name, but still let you connect. Comments? This could have a more general usage, too. Does SQL:2008 have anything to say about such a capability, or is it already in the column-level privileges, or...? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SYNONYMs revisited
On Wed, Mar 04, 2009 at 10:14:41AM -0500, Jonah H. Harris wrote: SQL/MED does support foreign tables, which are basically synonyms for remote tables. Other than that, it has no real similarity to synonym behavior for other database objects such as views, functions, or local tables. I didn't mean to suggest that SQL/MED on its own could be used to make SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider some sort of CREATE SYNONYM functionality to go along with it. A major argument against CREATE SYNONYM in the past was that we wouldn't be able to create synonyms representing remote objects because we couldn't access remote objects. With SQL/MED that's no longer the case, so perhaps that argument no longer applies. - Josh / eggyknap signature.asc Description: Digital signature
Re: [HACKERS] SYNONYMs revisited
Joshua Tolley eggyk...@gmail.com writes: I didn't mean to suggest that SQL/MED on its own could be used to make SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider some sort of CREATE SYNONYM functionality to go along with it. A major argument against CREATE SYNONYM in the past was that we wouldn't be able to create synonyms representing remote objects because we couldn't access remote objects. With SQL/MED that's no longer the case, so perhaps that argument no longer applies. Well, we're still a long way from having SQL/MED ;-). In particular, one of the elements of that spec is CREATE FOREIGN TABLE, which I think basically *is* a synonym for a table on a remote server. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Is there an official log reader for PostgreSQL?
I am interested in doing a near real time transaction processor. If there is a place to find the specification for the log reader, I would be keen to know it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SYNONYMs revisited
On Wednesday 04 March 2009 16:34:54 Joshua Tolley wrote: Way back in this thread[1] one of the arguments against allowing some version of CREATE SYNONYM was that we couldn't create a synonym for an object in a remote database. Will the SQL/MED work make this sort of thing a possibility? The idea is that SQL/MED will create objects known as foreign tables that represent remote resources but work locally like a normal table. So in some sense, SQL/MED already creates a local synonym-type thing for remote resources anyway, so there is no need to create a separate synonym in the Oracle sense on top of that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: B-Tree emulation for GIN
The GIN_EXTRACT_VALUE macro returns a pointer to a static 'entries' variable. That doesn't seem safe. Is it really never possible to have to two GIN searches in a plan, both calling and using the value returned by extractValue simultaneously? In any case that seems like a pretty weak assumption. You might want to declare extra_data as just void *, instead of an array of pointers. The data type implementation might want to store something there that's not per-key, but applies to the whole query. I see that you're passing it to comparePartial, but that seems to be just future-proofing. What kind of a data type are you envisioning that would make use of it? It seems that you could pass the same information in the partial key Datum itself that extractQuery returns. You're currently using it as a way to avoid some palloc's in gin_tsquery_consistent(). That seems like a pretty dirty hack. I doubt there's any meaningful performance advantage from that, but if there is, I think you could use a statically allocated array instead. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)
Ok, I've taken a quick look at this too. My first impression is that this is actually not a very big patch. Much much smaller than I was afraid of. It seems that dropping the row-level security and the other change you've already done have helped a great deal. My first question is, why does the patch need the walker implementation to gather all the accessed tables and columns? Can't you hook into the usual pg_xxx_aclcheck() functions? In fact, Peter asked that same question here: http://archives.postgresql.org/pgsql-hackers/2009-01/msg02295.php (among other things). Many things have changed since, but I don't think that question has been adequately answered. Different handling of permissions on views was mentioned, but I think that could be handled with just a few extra checks in the rewriter or executor. The hooks in simple_heap_insert also seem a bit weird. Perhaps an artifact of the row-level security stuff that's no longer there. ISTM that setting the defaults should be done in the same places where the defaults for acl columns are filled, e.g in ProcedureCreate. PS. s/proselabal/proselabel -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there an official log reader for PostgreSQL?
Hi, Le 4 mars 09 à 21:17, Dann Corbit a écrit : I am interested in doing a near real time transaction processor. If there is a place to find the specification for the log reader, I would be keen to know it. Have you read about PGQ yet? Some links include: http://www.pgcon.org/2008/schedule/events/79.en.html http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting-framework-pgq/ http://skytools.projects.postgresql.org/doc/pgq-admin.html http://skytools.projects.postgresql.org/doc/pgq-sql.html If your processing is to be coded in python, the facilities are provided in the current skytools releases. If your processing is to be coded in PHP, the facilities exists in the CVS and will get distributed soon'ish. If your processing is to be coded in any other language, you need SQL access and will be able to use the SQL level API directly, which provides all the raw features. HTH, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
marcin mank wrote: Use plain oids or regclass values, not a text column, if you are trying to store table identities. wouldn`t oids change on dump/reload? I don't know. I'd also be interested to know if there is a difference if we use pg_restore with a binary format or sql dump, or if that does not influence at all the way oids are created. manu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is there an official log reader for PostgreSQL?
-Original Message- From: Dimitri Fontaine [mailto:dfonta...@hi-media.com] Sent: Wednesday, March 04, 2009 12:28 PM To: Dann Corbit Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Is there an official log reader for PostgreSQL? Hi, Le 4 mars 09 à 21:17, Dann Corbit a écrit : I am interested in doing a near real time transaction processor. If there is a place to find the specification for the log reader, I would be keen to know it. Have you read about PGQ yet? Some links include: http://www.pgcon.org/2008/schedule/events/79.en.html http://kaiv.wordpress.com/2007/10/19/skytools-database-scripting- framework-pgq/ http://skytools.projects.postgresql.org/doc/pgq-admin.html http://skytools.projects.postgresql.org/doc/pgq-sql.html Not yet. I will read it. Thanks. If your processing is to be coded in python, the facilities are provided in the current skytools releases. If your processing is to be coded in PHP, the facilities exists in the CVS and will get distributed soon'ish. If your processing is to be coded in any other language, you need SQL access and will be able to use the SQL level API directly, which provides all the raw features. The project will be coded in C++. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Prepping to break every past release...
Hello, Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). Thoughts? Examples: postgres=# \d pg_class Table pg_catalog.pg_class Column | Type| Modifiers +---+--- relname| name | not null relnamespace | oid | not null [...] postgres=# \d pg_tables View pg_catalog.pg_tables Column| Type | Modifiers -+-+--- schemaname | name| tablename | name| postgres=# \d pg_stat_user_tables View pg_catalog.pg_stat_user_tables Column | Type | Modifiers --+--+--- relid| oid | schemaname | name | relname | name | postgres=# \d information_schema.tables View information_schema.tables Column| Type| Modifiers --+---+--- table_catalog| information_schema.sql_identifier | table_schema | information_schema.sql_identifier | table_name | information_schema.sql_identifier | -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Regclass and quoted table names
Use plain oids or regclass values, not a text column, if you are trying to store table identities. wouldn`t oids change on dump/reload? Greetings Marcin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SYNONYMs revisited
On Wed, Mar 04, 2009 at 03:15:23PM -0500, Tom Lane wrote: Joshua Tolley eggyk...@gmail.com writes: I didn't mean to suggest that SQL/MED on its own could be used to make SYNONYMs, but rather that given SQL/MED, perhaps we could reconsider some sort of CREATE SYNONYM functionality to go along with it. A major argument against CREATE SYNONYM in the past was that we wouldn't be able to create synonyms representing remote objects because we couldn't access remote objects. With SQL/MED that's no longer the case, so perhaps that argument no longer applies. Well, we're still a long way from having SQL/MED ;-). In particular, one of the elements of that spec is CREATE FOREIGN TABLE, which I think basically *is* a synonym for a table on a remote server. I hadn't followed SQL/MED to really see where we were; I just know that being able to create a synonym for a function, a table, a view, etc. seems like it would be neat (though I can't admit to having a list of use cases, or a good argument for any particular interpretation of its correct behavior). Since one concern expressed was that people might expect to be able to create synonyms of foreign objects, and dismayed to find they couldn't, perhaps having SQL/MED (one day) would remove concerns about building some form of CREATE SYNONYM. - Josh signature.asc Description: Digital signature
[HACKERS] cbrt() broken in AIX
I´m trying to install postgres 8.3.6 at AIX 5.3, and I got a declaration error that I presume its related to this [1] thread. Here´s some info: - Bladecenter IBM JS22 - PowerPC_POWER6 quad 4GHZ 64 bits - AIX 5.3.0 - Postgresql 8.3.6 - gcc version 4.2.0 - GNU Make 3.80 First I tryed to configure with no options, and then make throw this: float.c:74: error: static declaration of 'cbrt' follows non-static declaration After some googling, I find Bruce´s path back on 7.4, that removed my_cbrt trick. So, I changed float.c to the old code (the whole HAVE_CBRT stuff), and the declaration error was gone, but now I got this : /usr/bin/ld -r -o SUBSYS.o access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o tsearch/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a ../../src/backend/port/aix/mkldexport.sh SUBSYS.o . postgres.imp gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv -L../../src/port -Wl,-blibpath:/usr/local/pgsql/lib:/usr/lib:/lib access/SUBSYS.o bootstrap/SUBSYS.o catalog/SUBSYS.o parser/SUBSYS.o commands/SUBSYS.o executor/SUBSYS.o lib/SUBSYS.o libpq/SUBSYS.o main/SUBSYS.o nodes/SUBSYS.o optimizer/SUBSYS.o port/SUBSYS.o postmaster/SUBSYS.o regex/SUBSYS.o rewrite/SUBSYS.o storage/SUBSYS.o tcop/SUBSYS.o tsearch/SUBSYS.o utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a -Wl,-bE:../../src/backend/postgres.imp -lld -o postgres ld: 0711-317 ERROR: Undefined symbol: .lrint ld: 0711-317 ERROR: Undefined symbol: ._isnan ld: 0711-317 ERROR: Undefined symbol: ._isinff ld: 0711-317 ERROR: Undefined symbol: .sqrt ld: 0711-317 ERROR: Undefined symbol: .tan ld: 0711-317 ERROR: Undefined symbol: .sin ld: 0711-317 ERROR: Undefined symbol: .cos ld: 0711-317 ERROR: Undefined symbol: .atan2 ld: 0711-317 ERROR: Undefined symbol: .atan ld: 0711-317 ERROR: Undefined symbol: .asin ld: 0711-317 ERROR: Undefined symbol: .acos ld: 0711-317 ERROR: Undefined symbol: .log10 ld: 0711-317 ERROR: Undefined symbol: .log ld: 0711-317 ERROR: Undefined symbol: .exp ld: 0711-317 ERROR: Undefined symbol: .floor ld: 0711-317 ERROR: Undefined symbol: .pow ld: 0711-317 ERROR: Undefined symbol: .ceil ld: 0711-317 ERROR: Undefined symbol: ._isnanf ld: 0711-317 ERROR: Undefined symbol: .rintf ld: 0711-317 ERROR: Undefined symbol: .lrintf ld: 0711-317 ERROR: Undefined symbol: .llrint ld: 0711-317 ERROR: Undefined symbol: .llrintf ld: 0711-317 ERROR: Undefined symbol: .sqrtf ld: 0711-345 Use the -bloadmap or -bnoquiet option to obtain more information. collect2: ld returned 8 exit status gmake[2]: *** [postgres] Error 1 gmake[2]: Leaving directory `/usr/src/postgresql-8.3.6/src/backend' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/src/postgresql-8.3.6/src' gmake: *** [all] Error 2 What can I do next ? [1] http://archives.postgresql.org/pgsql-patches/2003-05/msg00127.php -- []´s, ACV -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
Hi, André Volpato schrieb: I´m trying to install postgres 8.3.6 at AIX 5.3, and I got a declaration error that I presume its related to this [1] thread. [...] After some googling, I find Bruce´s path back on 7.4, that removed my_cbrt trick. So, I changed float.c to the old code (the whole HAVE_CBRT stuff), and the declaration error was gone, but now I got this : [...] gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline [...] utils/SUBSYS.o ../../src/timezone/SUBSYS.o ../../src/port/libpgport_srv.a -Wl,-bE:../../src/backend/postgres.imp -lld -o postgres ld: 0711-317 ERROR: Undefined symbol: .lrint ld: 0711-317 ERROR: Undefined symbol: ._isnan ld: 0711-317 ERROR: Undefined symbol: ._isinff ld: 0711-317 ERROR: Undefined symbol: .sqrt ld: 0711-317 ERROR: Undefined symbol: .tan ld: 0711-317 ERROR: Undefined symbol: .sin ld: 0711-317 ERROR: Undefined symbol: .cos [...] What can I do next ? I don't have a clue about AIX, but that certainly looks like it's missing a -lm now. Ciao, Thomas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Joshua D. Drake wrote: Hello, Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). Thoughts? What would be the benefit? Apart from satisfying a passion for consistency? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 4, 2009 at 8:32 PM, Joshua D. Drake j...@commandprompt.com wrote: Hello, Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. I assume you'll be putting in the weeks/months of work required to fix pgAdmin phpPgAdmin which would be far better spent on new features than uglifying the code in far nastier ways than the current state of the catalogs? -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 2009-03-04 at 15:50 -0500, Andrew Dunstan wrote: It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). Thoughts? What would be the benefit? Apart from satisfying a passion for consistency? It would lower the cost (intellectually as well as dollars) of development and administration for every single user of the database. I can't count how many times I accidentally type tablename versus table_name or worse relname, etc... Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 2009-03-04 at 21:01 +, Dave Page wrote: On Wed, Mar 4, 2009 at 8:32 PM, Joshua D. Drake j...@commandprompt.com wrote: Hello, Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. I assume you'll be putting in the weeks/months of work required to fix pgAdmin phpPgAdmin which would be far better spent on new features than uglifying the code in far nastier ways than the current state of the catalogs? *Shrug* long term consistency is the better choice. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake j...@commandprompt.com wrote: I assume you'll be putting in the weeks/months of work required to fix pgAdmin phpPgAdmin which would be far better spent on new features than uglifying the code in far nastier ways than the current state of the catalogs? *Shrug* long term consistency is the better choice. Easy to say if you're not one of the people for whom such a change would mean weeks of recoding, the need to start QA'ing everything from scratch again and a hideous mess of code to cope with after adding support for a new version with a different catalog schema. Besides - what percentage of users ever go anywhere near the catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5% of developers. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 2009-03-04 at 21:14 +, Dave Page wrote: On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake j...@commandprompt.com wrote: *Shrug* long term consistency is the better choice. Easy to say if you're not one of the people for whom such a change would mean weeks of recoding, the need to start QA'ing everything from scratch again and a hideous mess of code to cope with after adding support for a new version with a different catalog schema. It is not easy to say. It is correct to say. I am under no illusion that this will not be painful. As far as man weeks of recoding. Sorry, I know that will be tough. Besides - what percentage of users ever go anywhere near the catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5% of developers. Any DBA worth his salt uses system catalogs. Lowering the barrier on uses these catalogs will lead to better and more useful tools as well. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
In response to Dave Page dp...@pgadmin.org: On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake j...@commandprompt.com wrote: I assume you'll be putting in the weeks/months of work required to fix pgAdmin phpPgAdmin which would be far better spent on new features than uglifying the code in far nastier ways than the current state of the catalogs? *Shrug* long term consistency is the better choice. Easy to say if you're not one of the people for whom such a change would mean weeks of recoding Don't those folks have to tweak their code with each new release anyway? Because those tables are constantly changing? I know we hit problems with the way triggers are stored in 8.3 compared to earlier versions. Granted, a sweeping change will necessitate a much larger tweak than a few changed columns, but the long-term benefit should be cleaner client code. Besides - what percentage of users ever go anywhere near the catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5% of developers. You could always take a survey ... bosses love surveys ... I, for one, know of lots of code that I've written that accesses those catalogs. I can't speak for other people. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake j...@commandprompt.com wrote: It is not easy to say. It is correct to say. I am under no illusion that this will not be painful. As far as man weeks of recoding. Sorry, I know that will be tough. It'll be a complete and utter waste of time, and make a horrible mess of any client code that has to support multiple versions. Besides - what percentage of users ever go anywhere near the catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5% of developers. Any DBA worth his salt uses system catalogs. Lowering the barrier on uses these catalogs will lead to better and more useful tools as well. Then psql and pgAdmin aren't doing their jobs properly. Tell us what you need. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Joshua D. Drake j...@commandprompt.com writes: Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). This isn't happening. Don't waste our time suggesting it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 4, 2009 at 9:24 PM, Bill Moran wmo...@potentialtech.com wrote: In response to Dave Page dp...@pgadmin.org: Don't those folks have to tweak their code with each new release anyway? Because those tables are constantly changing? I know we hit problems with the way triggers are stored in 8.3 compared to earlier versions. pg_trigger.tgenabled changed type in 8.3. That's an extremely rare kind of change however - for example, triggers havent really changed since at least 7.3 aside from that. The sort of change JD seems to be suggesting /could/ involve rewriting virtually every query in pgAdmin, as well as surrounding code. Granted, a sweeping change will necessitate a much larger tweak than a few changed columns, but the long-term benefit should be cleaner client code. Only if your app only targets one version of the server. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 2009-03-04 at 21:27 +, Dave Page wrote: On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake j...@commandprompt.com wrote: It is not easy to say. It is correct to say. I am under no illusion that this will not be painful. As far as man weeks of recoding. Sorry, I know that will be tough. It'll be a complete and utter waste of time, and make a horrible mess of any client code that has to support multiple versions. No actually it won't. It will make it easier for every product that uses 8.5 and above. Sometimes you have to cut the cord. Any DBA worth his salt uses system catalogs. Lowering the barrier on uses these catalogs will lead to better and more useful tools as well. Then psql and pgAdmin aren't doing their jobs properly. Tell us what you need. That is an interesting argument but honestly off topic for this thread because that would be a series of new views and functions etc... Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 2009-03-04 at 16:28 -0500, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). This isn't happening. Don't waste our time suggesting it. I wasn't try to waste anyone's time. I thought doing things correctly was a goal of this project. Sorry for the noise. Sincerely, Joshua D. Drake regards, tom lane -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
=?ISO-8859-1?Q?Andr=E9_Volpato?= andre.volp...@ecomtecnologia.com.br writes: First I tryed to configure with no options, and then make throw this: float.c:74: error: static declaration of 'cbrt' follows non-static declaration After some googling, I find Bruce´s path back on 7.4, that removed my_cbrt trick. So, I changed float.c to the old code (the whole HAVE_CBRT stuff), Instead, try putting back the #define cbrt my_cbrt line. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Joshua D. Drake wrote: On Wed, 2009-03-04 at 21:27 +, Dave Page wrote: On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake j...@commandprompt.com wrote: It is not easy to say. It is correct to say. I am under no illusion that this will not be painful. As far as man weeks of recoding. Sorry, I know that will be tough. It'll be a complete and utter waste of time, and make a horrible mess of any client code that has to support multiple versions. No actually it won't. It will make it easier for every product that uses 8.5 and above. Sometimes you have to cut the cord. I think this sounds a lot like another request for a set of system views with nicer names. That way, there'd be no break with backwards compatibility, and you could use the easier names if you were on the newer versions. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Joshua D. Drake j...@commandprompt.com wrote: It would lower the cost (intellectually as well as dollars) of development and administration for every single user of the database. -1 Any savings couldn't possibly pay for the cost and pain of this, at least in our shop. I can't count how many times I accidentally type tablename versus table_name or worse relname, etc... I can count the times I have done so on one finger. I don't find it that hard to keep track of what column names are used in what table. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Magnus Hagander mag...@hagander.net writes: I think this sounds a lot like another request for a set of system views with nicer names. What's the state of the newsysviews project, anyway? I don't recall hearing much about it lately. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Joshua D. Drake wrote: Hello, Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). Well, honestly, this was one of the reasons why AndrewSN, David and I pushed NewSysViews. Non-hackers really shouldn't be accessing the system catalogs directly, and if information_schema doesn't give them enough data, we should start adding new views to I_S. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: I think this sounds a lot like another request for a set of system views with nicer names. What's the state of the newsysviews project, anyway? I don't recall hearing much about it lately. Final verdict was that we need to make it integrate better with information_schema. At that point, our crew kinda ran out of energy and it's been on hold ever since. But we've been talking about reviving it again. However, given the previous experience, I think we'd like some reassurance that if NewSysviews was made conistent with information_schema (and had good code, of course) that Hackers would be interested in taking it. What nobody has any enthusiasm for is spending a few dozen hours refactoring it, and then having to justify why we want it *again*. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SYNONYMs revisited
On Wed, Mar 4, 2009 at 9:34 AM, Joshua Tolley eggyk...@gmail.com wrote: Way back in this thread[1] one of the arguments against allowing some version of CREATE SYNONYM was that we couldn't create a synonym for an object in a remote database. Will the SQL/MED work make this sort of thing a possibility? I realize since it's not standard anyway, there's still a discussion or two to be had about how precisely it should work, but thought I'd raise the possibility. While shaking my head In that movie-like slow-motion used as a precursor to an almost disastrous event, I see myself saying, no... OK, back to reality. SQL/MED does support foreign tables, which are basically synonyms for remote tables. Other than that, it has no real similarity to synonym behavior for other database objects such as views, functions, or local tables. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [HACKERS] Prepping to break every past release...
Well, honestly, this was one of the reasons why AndrewSN, David and I ... and Elein ... pushed NewSysViews. Non-hackers really shouldn't be accessing the system catalogs directly, and if information_schema doesn't give them enough data, we should start adding new views to I_S. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Bill, You could always take a survey ... bosses love surveys ... Done: http://wwwmaster.postgresql.org/community/ --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Josh Berkus wrote: Well, honestly, this was one of the reasons why AndrewSN, David and I ... and Elein ... pushed NewSysViews. Non-hackers really shouldn't be accessing the system catalogs directly, and if information_schema doesn't give them enough data, we should start adding new views to I_S. This is really a long way from JD's original suggestion, though. Back on that track, I'd like to see a facility whereby we could provide an alias (or synonym, to use a nearby subject) columns and other objects. That would help to overcome naming glitches without breaking things quite so much. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Andrew Dunstan and...@dunslane.net wrote: Back on that track, I'd like to see a facility whereby we could provide an alias (or synonym, to use a nearby subject) columns and other objects. That would help to overcome naming glitches without breaking things quite so much. How is that different from what you can do with a view? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Josh Berkus j...@agliodbs.com wrote: You could always take a survey ... bosses love surveys ... Done: http://wwwmaster.postgresql.org/community/ Is there some assumed relationship between those options and whether they'd like to see the names changed? Also, it's hard to pick what option to choose there -- I typically have cause about once or twice a month to poke around in those tables, and we have development tools which reference these tables directly I guess the closest option would be access them all the time. I hope that wouldn't be interpreted as supporting the proposed change. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Andrew, Back on that track, I'd like to see a facility whereby we could provide an alias (or synonym, to use a nearby subject) columns and other objects. That would help to overcome naming glitches without breaking things quite so much. Believe it or not, a large PostgreSQL user in LA just buttonholed me about that particular feature idea at SCALE. So it might be generally useful as well -- not just for the system catalogs, bug to allow businesses with long-use databases to manage change over time. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Kevin Grittner wrote: Josh Berkus j...@agliodbs.com wrote: You could always take a survey ... bosses love surveys ... Done: http://wwwmaster.postgresql.org/community/ Is there some assumed relationship between those options and whether they'd like to see the names changed? Also, it's hard to pick what option to choose there -- I typically have cause about once or twice a month to poke around in those tables, and we have development tools which reference these tables directly I guess the closest option would be access them all the time. I hope that wouldn't be interpreted as supporting the proposed change. I'm not taking a survey on how/whether we want to change it. Just whether people are accessing them directly. --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Mar 4, 2009, at 6:07 PM, Josh Berkus wrote: Andrew, Back on that track, I'd like to see a facility whereby we could provide an alias (or synonym, to use a nearby subject) columns and other objects. That would help to overcome naming glitches without breaking things quite so much. Believe it or not, a large PostgreSQL user in LA just buttonholed me about that particular feature idea at SCALE. So it might be generally useful as well -- not just for the system catalogs, bug to allow businesses with long-use databases to manage change over time. Schema change is a strong motivator for applications to access the database through views and functions only. A column with multiple names would likely make it *more* painful to migrate schemata. Cheers, M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cbrt() broken in AIX
2009/3/4 André Volpato andre.volp...@ecomtecnologia.com.br: I´m trying to install postgres 8.3.6 at AIX 5.3, and I got a declaration error that I presume its related to this [1] thread. Here´s some info: - Bladecenter IBM JS22 - PowerPC_POWER6 quad 4GHZ 64 bits - AIX 5.3.0 - Postgresql 8.3.6 - gcc version 4.2.0 - GNU Make 3.80 (...) Here: postg...@db01 $ id uid=204(postgres) gid=1(staff) postg...@db01 $ export CC=gcc postg...@db01 $ export CFLAGS=-maix64 postg...@db01 $ export LDFLAGS=-Wl,-bbigtoc postg...@db01 $ export OBJECT_MODE=64 postg...@db01 $ export AR=ar -X64 postg...@db01 $ ./configure --enable-integer-datetimes --prefix=/srv/postgresql/8.3.6 postg...@db01 $ make ... ... And all is done fine. Course, all above is for 64 bits compilling. How you are trying that? []s Dickson S. Guedes - mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] V4 of PITR performance improvement for 8.4
Appreciate for your kind help! 2009/3/3 Fujii Masao masao.fu...@gmail.com: On Tue, Mar 3, 2009 at 1:47 PM, Fujii Masao masao.fu...@gmail.com wrote: Hi Suzuki-san, On Thu, Feb 26, 2009 at 5:03 AM, Koichi Suzuki koichi@gmail.com wrote: My reply to Gregory's comment didn't have any objections. I believe, as I posted to Wiki page, latest posted patch is okay and waiting for review. One of your latest patches doesn't match with HEAD, so I updated it. Oops! I failed in attaching the patch. This is second try. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- -- Koichi Suzuki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)
Heikki, Thanks for your comments.
Heikki Linnakangas wrote:
Ok, I've taken a quick look at this too. My first impression is that
this is actually not a very big patch. Much much smaller than I was
afraid of. It seems that dropping the row-level security and the other
change you've already done have helped a great deal.
My first question is, why does the patch need the walker implementation
to gather all the accessed tables and columns? Can't you hook into the
usual pg_xxx_aclcheck() functions? In fact, Peter asked that same
question here:
http://archives.postgresql.org/pgsql-hackers/2009-01/msg02295.php (among
other things). Many things have changed since, but I don't think that
question has been adequately answered. Different handling of permissions
on views was mentioned, but I think that could be handled with just a
few extra checks in the rewriter or executor.
Yes, one major reason is to handle views. SE-PostgreSQL need to check
permissions on after it is extracted.
The other one is it has two kind of reader permissions (select and use).
The select permission is applied when user tries to read tables/columns
and its contents are returned to the user.
The use permission is applied when user tries to read table/columns,
but its contents are consumed internally (not returned to user directly).
For example:
SELECT a, b FROM t WHERE b 10 and c = 'aaa';
In this case,
db_column:{select} permission is applied on t.a.
db_column:{select use} permission is applied on t.b.
db_column:{use} permission is applied on t.c.
db_table:{select use} permission is applied on t
However, I don't absolutely oppose to integrate them into a single
reader select permission, because it was originally a single
permission, then use is added.
The purpose of use permission is to set up a writable table, but
not readable. When we use UPDATE or DELETE statement, it need to
add WHERE clause to make clear its target, but it always requires
reader permission. So, I separated it into two cases.
Thus, it is not a reason as strong as one for views.
I'll check some of corner cases, such as inherited tables, COPY
statement, trigger invocations and others, to consider whether
your suggestion is possible, or not.
Please wait for a while to fix my attitude.
The hooks in simple_heap_insert also seem a bit weird. Perhaps an
artifact of the row-level security stuff that's no longer there. ISTM
that setting the defaults should be done in the same places where the
defaults for acl columns are filled, e.g in ProcedureCreate.
Its purpose is not set a default security label in the current version.
(It is set in ProcedureCreate and others.)
Its purpose is to check user's privileges on tables, columns, procedures
and databases, and raises an error if violated.
Please note that user's privileges are not limited to create/alter/drop
them. One sensitive permission is db_procedure:{install}.
It is checked when user defined functions are set up as a function
internally invoked.
For example, pg_conversion.conproc is internally invoked to translate
a text, but it does not check pg_proc_aclcheck() in runtime.
We consider all user defined functions should be checked either of:
- db_procedure:{execute} permission for the client in runtime
or
- db_procedure:{install} permission for the DBA on installation time
Needless to say, {install} is more sensitive permission because it
means anyones to invoke it implicitly. So, the default policy only
allows it on functions defined by DBA, but the execute permission
is allowed normal users to invoke functions defined by himself.
sepgsqlCheckProcedureInstall() checks this permission called from
the hooks of simple_heap_insert()/_update().
From the viewpoint of security, it is good design to put hooks on
more frequently used point, than checking it many points.
It is same reason why SELinux checks system-calls from applications.
It is the only path to access resources managed by operating system,
so necessary and sufficient.
PS. s/proselabal/proselabel
Oops,
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
Tom == Tom Lane t...@sss.pgh.pa.us writes: Magnus Hagander mag...@hagander.net writes: I think this sounds a lot like another request for a set of system views with nicer names. Tom What's the state of the newsysviews project, anyway? I don't Tom recall hearing much about it lately. At the time it was proposed for inclusion (pre 8.1, when it was mostly but not entirely complete), you personally, as I recall, expressed the opinion that its objective was impossible; that there was no way to produce a sufficiently complete set of views that was more stable and compatible between releases than the system catalogs themselves were. I believe these sum up your response: http://archives.postgresql.org/pgsql-hackers/2005-05/msg00351.php http://archives.postgresql.org/pgsql-hackers/2005-05/msg00891.php http://archives.postgresql.org/pgsql-hackers/2005-05/msg00940.php Another common response at the time was just use information_schema. (Which is of course inadequate for a huge number of reasons - speed, hiding of implementation details, mismatches between pg's behaviour and the SQL spec, and so on.) Given the extent to which this criticism of the project was based on speculation (we might make changes in future releases that would break the views), there was no better answer at the time than we think the design is flexible enough to handle that, and very little of the pushback we got actually showed any signs of having reviewed the design and (admittedly incomplete) implementation. Accordingly I stopped spending any time on it and diverted my attention elsewhere. Now, of course, counting the upcoming 8.4 there have been three (and a bit - the original design predates 8.1, though it did anticipate some 8.1 features) new releases against which the original concept can be tested. And, guess what, nothing in those releases has even come close to invalidating the original design concept (as we knew all along). If you're still not convinced of that fact, it would be possible to take the original design and update it to 8.4 following the original plan. But I'm not prepared to spend any time on this if the only result is going to be more argument. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, Mar 4, 2009 at 3:32 PM, Joshua D. Drake j...@commandprompt.com wrote: Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. Now I am fully and 100% aware that changing this will break things in user land but I want to do it anyway. In order to do that I believe we need to come up with a very loud, extremely verbose method of communicating to people that 8.5 *will* break things. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). Thoughts? Like everyone else who has responded to this thread, I think this is a pretty terrible idea. It's possible that there are some specific columns in some specific tables that could stand to be renamed for consistency, and perhaps if you come up with some specific proposals with careful justifications someone might support the idea of doing some limited renaming. But too much renaming is not likely to be popular with anyone for reasons that are somewhat summed up by your subject line. And, really, how much better would the new names be than the old ones anyway? The idea that a casual user will be able to query the system catalogs and gain some sort of useful information without reading the documentation or at least cracking out a couple of \d commands strikes me as a pipe dream. I'll admit that I'm a little mystified by why we use pg_class to store relations (why not pg_relation?), relnamespace to store the schema oid (why not relschema?), and so on, so some improvement is probably possible. But I'm not sure you're going to be able to come up with a name that's substantially clearer than proargmodes. Sure, you could call it argument_modes, but that's not really any clearer, it's just longer. In fact, it's my experience that exercises of this type almost always end up replacing shorter names with longer names without really making anything any better. In the end you still have to RTFM. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
On Wed, 4 Mar 2009, Joshua D. Drake wrote: Something that continues to grind my teeth about our software is that we are horribly inconsistent with our system catalogs. I'd suggest staring at the gyrations required to do an in-place upgrade, then considering how the changes you're suggesting will make that even more difficult, until this inconsistency no longer seems very important. It seems to me that the best method would be to follow the information_schema naming conventions as information_schema is standard compliant (right?). You could make a case for changing pg_tables.tablename to pg_tables.table_name, so that it better matched the information_schema. But it's not like that general approach makes this problem go away. You'll still have pg_class.rel_name or relname, because pg_class contains several types of relations: tables, indexes, etc. Since that particular mismatch is impossible to resolve, you can't completely simplify this area no matter how hard you try. That makes it hard to get excited about just reducing the number of inconsistencies here. Any DBA worth his salt uses system catalogs. Lowering the barrier on uses these catalogs will lead to better and more useful tools as well. I would wager that putting 1% of the total effort needed to actually change the catalog schema names into a documentation/sample code push in this area would give a bigger payback. I never write catalog stuff from scratch anymore; I go back to the same couple of template pieces I always end up needing for the various types of joins that always pop up and customize from there. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Prepping to break every past release...
* Robert Haas (robertmh...@gmail.com) wrote: Like everyone else who has responded to this thread, I think this is a pretty terrible idea. [...] +1 on Robert's assessment from me. I'm generally a huge fan of doing the right thing, but as my boss often likes to point out, you need to pick your battles wisely. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] [BUG] Column-level privileges on inherited tables
I've observed the behavior of column-level privileges and required permissions with a few elog()s injected. I noticed rte-selectedCols is incorrect when we make a query on inherited tables. See below: - postgres=# CREATE TABLE t1 (a int, b int, c int); CREATE TABLE postgres=# ALTER TABLE t1 DROP COLUMN b; ALTER TABLE postgres=# CREATE TABLE t2 (d int) inherits (t1); CREATE TABLE postgres=# SELECT * FROM t1; NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.a NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = inh = 1 NOTICE: ExecCheckRTEPerms: selectedCols: t1.a NOTICE: ExecCheckRTEPerms: selectedCols: t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = 0002 inh = 0 NOTICE: ExecCheckRTEPerms: selectedCols: t1.a NOTICE: ExecCheckRTEPerms: selectedCols: t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t2 perms = 0002 inh = 0 NOTICE: ExecCheckRTEPerms: selectedCols: t2.a NOTICE: ExecCheckRTEPerms: selectedCols: t2.d --- (*) a | c ---+--- (0 rows) - I injected elog() at the head of ExecCheckRTEPerms() to print requiredPerms and all the columns on selectedCols/modifiedCols. It seems to me the current implementation assumes the parant table and child table have same set of attribute name/number pair, but incorrect. It is necessary to lookup attribute names of t2 when we extract inherited tables. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Column-level privileges on inherited tables
KaiGai Kohei wrote: I've observed the behavior of column-level privileges and required permissions with a few elog()s injected. I noticed rte-selectedCols is incorrect when we make a query on inherited tables. See below: - postgres=# CREATE TABLE t1 (a int, b int, c int); CREATE TABLE postgres=# ALTER TABLE t1 DROP COLUMN b; ALTER TABLE postgres=# CREATE TABLE t2 (d int) inherits (t1); CREATE TABLE postgres=# SELECT * FROM t1; NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.a NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = inh = 1 NOTICE: ExecCheckRTEPerms: selectedCols: t1.a NOTICE: ExecCheckRTEPerms: selectedCols: t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = 0002 inh = 0 NOTICE: ExecCheckRTEPerms: selectedCols: t1.a NOTICE: ExecCheckRTEPerms: selectedCols: t1.c NOTICE: ExecCheckRTEPerms: ACL_SELECT on t2 perms = 0002 inh = 0 NOTICE: ExecCheckRTEPerms: selectedCols: t2.a NOTICE: ExecCheckRTEPerms: selectedCols: t2.d --- (*) a | c ---+--- (0 rows) - I injected elog() at the head of ExecCheckRTEPerms() to print requiredPerms and all the columns on selectedCols/modifiedCols. It seems to me the current implementation assumes the parant table and child table have same set of attribute name/number pair, but incorrect. It is necessary to lookup attribute names of t2 when we extract inherited tables. In addition, the whole-row-reference can be problematic. When we run SELECT t1 FROM t1, the column level privilege tries to check all the columns within t1 and t2. But, I think it should not check on t2.d in this case, because the column is not a target of this query. In the whole-row-reference case, attno==0 on the parent table should be extracted into correct set of columns on the children. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for the MUST time zone (Mauritius Summer Time)
Hi, This trivial patch allows PostgreSQL to understand the MUST (Mauritius Summer Time) time zone that is in used since 2008-11. Before the patch : # SELECT '2009-03-15 14:54:15 MUST'::timestamp; ERROR: invalid input syntax for type timestamp: 2009-03-15 14:54:15 MUST After the patch : # SELECT '2009-03-15 14:54:15 MUST'::timestamp; timestamp - 2009-03-15 14:54:15 (1 row) Index: src/timezone/tznames/Default === RCS file: /projects/cvsroot/pgsql/src/timezone/tznames/Default,v retrieving revision 1.6 diff -c -r1.6 Default *** src/timezone/tznames/Default1 May 2008 20:05:08 - 1.6 --- src/timezone/tznames/Default5 Mar 2009 07:03:19 - *** *** 640,645 --- 640,647 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUT 18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) MVT 18000# Maldives Island Time # (Indian/Maldives) RET 14400# Reunion Time Index: src/timezone/tznames/Indian.txt === RCS file: /projects/cvsroot/pgsql/src/timezone/tznames/Indian.txt,v retrieving revision 1.2 diff -c -r1.2 Indian.txt *** src/timezone/tznames/Indian.txt 25 Jul 2006 13:49:21 - 1.2 --- src/timezone/tznames/Indian.txt 5 Mar 2009 07:05:35 - *** *** 27,32 --- 27,34 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUT 18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) MVT 18000# Maldives Island Time # (Indian/Maldives) RET 14400# Reunion Time -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1668)
KaiGai Kohei wrote:
The other one is it has two kind of reader permissions (select and
use).
The select permission is applied when user tries to read tables/columns
and its contents are returned to the user.
The use permission is applied when user tries to read table/columns,
but its contents are consumed internally (not returned to user directly).
For example:
SELECT a, b FROM t WHERE b 10 and c = 'aaa';
In this case,
db_column:{select} permission is applied on t.a.
db_column:{select use} permission is applied on t.b.
db_column:{use} permission is applied on t.c.
db_table:{select use} permission is applied on t
However, I don't absolutely oppose to integrate them into a single
reader select permission, because it was originally a single
permission, then use is added.
If you have use permisson on c, you can easily use it to find out the
exact value. Just do queries like SELECT 'foo' FROM t WHERE b 10 AND
c = 'aaa' AND c BETWEEN 1 AND 1000 repeatedly with different ranges to
zoom into the exact value. So I think separating those two permissions
is a mistake,
Please note that user's privileges are not limited to create/alter/drop
them. One sensitive permission is db_procedure:{install}.
It is checked when user defined functions are set up as a function
internally invoked.
For example, pg_conversion.conproc is internally invoked to translate
a text, but it does not check pg_proc_aclcheck() in runtime.
We consider all user defined functions should be checked either of:
- db_procedure:{execute} permission for the client in runtime
or
- db_procedure:{install} permission for the DBA on installation time
Needless to say, {install} is more sensitive permission because it
means anyones to invoke it implicitly. So, the default policy only
allows it on functions defined by DBA, but the execute permission
is allowed normal users to invoke functions defined by himself.
Hmm. We normally rely on the fact that a conversion function needs to be
a C-function, and because only superusers can create C-functions we have
assumed that they're safe to call. Which was actually not true until
recently, when we added checks into all the conversion functions to
check that the source and target encoding of the strings passed as
arguments match the ones specified in the CREATE CONVERSION command.
There has been talks of making CREATE CONVERSION superuser-only, so we
could easily just do that. Can you give some other examples of where the
install permission is used?
But if I've understood correctly, one goal is to restrict the actions of
superusers as well. Is there something to disallow superusers from
creating C-functions? If yes, isn't that enough protection from things
like the conversion functions?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for the MUST time zone (Mauritius Summer Time)
Xavier Bugaud wrote: This trivial patch allows PostgreSQL to understand the MUST (Mauritius Summer Time) time zone that is in used since 2008-11. ... *** src/timezone/tznames/Default1 May 2008 20:05:08 - 1.6 --- src/timezone/tznames/Default5 Mar 2009 07:03:19 - *** *** 640,645 --- 640,647 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUT 18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) Shouldn't that new line be MUST then? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUG] Column-level privileges on inherited tables
Stephen,
The attached patch fixes the matter.
It fixes up attribute number of child relation when it is extracted.
(*) Injected elog()s are removed.
postgres=# select * from t1;
NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.a
NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = inh = 1
NOTICE: ExecCheckRTEPerms: selectedCols: t1.a
NOTICE: ExecCheckRTEPerms: selectedCols: t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t1.a
NOTICE: ExecCheckRTEPerms: selectedCols: t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t2 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t2.a
NOTICE: ExecCheckRTEPerms: selectedCols: t2.c
a | c
---+---
(0 rows)
postgres=# select t1 from t1;
NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.t1
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = inh = 1
NOTICE: ExecCheckRTEPerms: selectedCols: t1.t1
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t1.t1
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t2 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t2.a
NOTICE: ExecCheckRTEPerms: selectedCols: t2.c
t1
(0 rows)
KaiGai Kohei wrote:
KaiGai Kohei wrote:
I've observed the behavior of column-level privileges and
required permissions with a few elog()s injected.
I noticed rte-selectedCols is incorrect when we make a query
on inherited tables.
See below:
-
postgres=# CREATE TABLE t1 (a int, b int, c int);
CREATE TABLE
postgres=# ALTER TABLE t1 DROP COLUMN b;
ALTER TABLE
postgres=# CREATE TABLE t2 (d int) inherits (t1);
CREATE TABLE
postgres=# SELECT * FROM t1;
NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.a
NOTICE: markRTEForSelectPriv: ACL_SELECT on t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = inh = 1
NOTICE: ExecCheckRTEPerms: selectedCols: t1.a
NOTICE: ExecCheckRTEPerms: selectedCols: t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t1 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t1.a
NOTICE: ExecCheckRTEPerms: selectedCols: t1.c
NOTICE: ExecCheckRTEPerms: ACL_SELECT on t2 perms = 0002 inh = 0
NOTICE: ExecCheckRTEPerms: selectedCols: t2.a
NOTICE: ExecCheckRTEPerms: selectedCols: t2.d --- (*)
a | c
---+---
(0 rows)
-
I injected elog() at the head of ExecCheckRTEPerms() to print requiredPerms
and all the columns on selectedCols/modifiedCols.
It seems to me the current implementation assumes the parant table and
child table have same set of attribute name/number pair, but incorrect.
It is necessary to lookup attribute names of t2 when we extract
inherited tables.
In addition, the whole-row-reference can be problematic.
When we run SELECT t1 FROM t1, the column level privilege tries to check
all the columns within t1 and t2. But, I think it should not check on t2.d
in this case, because the column is not a target of this query.
In the whole-row-reference case, attno==0 on the parent table should be
extracted into correct set of columns on the children.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
*** base/src/backend/optimizer/prep/prepunion.c 2009-02-26 11:04:20.0 +0900
--- sepgsql/src/backend/optimizer/prep/prepunion.c 2009-03-05 16:24:32.0 +0900
***
*** 30,35
--- 30,36
#include access/heapam.h
+ #include access/sysattr.h
#include catalog/namespace.h
#include catalog/pg_type.h
#include miscadmin.h
***
*** 49,54
--- 50,56
#include utils/lsyscache.h
#include utils/rel.h
#include utils/selfuncs.h
+ #include utils/syscache.h
static Plan *recurse_set_operations(Node *setOp, PlannerInfo *root,
***
*** 1150,1155
--- 1152,1253
}
/*
+ * fixup_column_privileges
+ * Inherited tables have same columns as its parents have,
+ * but these columns may have different attribute numbers,
+ * so we need to lookup attribute numbers of child relation
+ * by its name.
+ */
+ static Bitmapset *
+ fixup_column_privileges(Oid parent, Oid child, Bitmapset *oldmap)
+ {
+ Bitmapset *newmap = NULL;
+ char *attname;
+ int attno, attno_fixup;
+
+ if (!oldmap || parent == child)
+ return oldmap; /* no need to fixup */
+
+ while ((attno = bms_first_member(oldmap)) = 0)
+ {
+ /* remove the column number offset */
+ attno += FirstLowInvalidHeapAttributeNumber;
+
+ /*
+ * The whole-row-reference case need a special care
+ * because child relation has more columns than the
+ * parent, so it need to extract inherited columns
+ * only.
+ */
+ if (attno == InvalidAttrNumber)
+ {
+ HeapTuple reltup;
+ HeapTuple atttup;
+ Form_pg_class classForm;
+ Form_pg_attribute attForm;
+ int
[HACKERS] Use array in a dynamic statement
Hi,
I am trying to implement a PL/PgSQL function as following:
CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR)
RETURNS varchar[]
AS $$
DECLARE
result varchar[];
BEGIN
EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1),
array_upper($1,1)) AS s(i), '
||tbl_name||' t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER
BY length(t.idx))'
INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
I got an error ERROR: there is no parameter $1 when I test the function with:
select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170,
1}}'::int[][], 'd_tree_1');
The error is understandable, but my question is how to supply the int[][] array
into the dynamic SQL?
To help understand the dynamic statement, the structure of d_tree_1 is (rid,
rtid, idx). The PK is (rid, rtid) pair.
If the tbl_name is fixed, the following function works well:
CREATE OR REPLACE FUNCTION sort_by_d_idx2 (nids INT[][])
RETURNS varchar[]
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT t.idx
FROM
generate_series(array_lower($1,1), array_upper($1,1)) AS s(i), d_tree_1
t
WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid
ORDER BY length(t.idx)
);
$$;
Unfortunately, the tbl_name is determined at query time.
Please help.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch for the MUST time zone (Mauritius Summer Time)
On Thursday 05 March 2009 12:31:17 Heikki Linnakangas wrote: Xavier Bugaud wrote: This trivial patch allows PostgreSQL to understand the MUST (Mauritius Summer Time) time zone that is in used since 2008-11. ... *** src/timezone/tznames/Default1 May 2008 20:05:08 - 1.6 --- src/timezone/tznames/Default5 Mar 2009 07:03:19 - *** *** 640,645 --- 640,647 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUT 18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) Shouldn't that new line be MUST then? My mistake, sorry... Please find bellow the new patches. Index: src/timezone/tznames/Default === RCS file: /projects/cvsroot/pgsql/src/timezone/tznames/Default,v retrieving revision 1.6 diff -c -r1.6 Default *** src/timezone/tznames/Default1 May 2008 20:05:08 - 1.6 --- src/timezone/tznames/Default5 Mar 2009 07:34:31 - *** *** 640,645 --- 640,647 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUST18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) MVT 18000# Maldives Island Time # (Indian/Maldives) RET 14400# Reunion Time Index: src/timezone/tznames/Indian.txt === RCS file: /projects/cvsroot/pgsql/src/timezone/tznames/Indian.txt,v retrieving revision 1.2 diff -c -r1.2 Indian.txt *** src/timezone/tznames/Indian.txt 25 Jul 2006 13:49:21 - 1.2 --- src/timezone/tznames/Indian.txt 5 Mar 2009 07:37:12 - *** *** 27,32 --- 27,34 # (Indian/Chagos) MUT 14400# Mauritius Island Time # (Indian/Mauritius) + MUST18000 D # Mauritius Island Summer Time + # (Indian/Mauritius) MVT 18000# Maldives Island Time # (Indian/Maldives) RET 14400# Reunion Time -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Use array in a dynamic statement
Sophie Yang wrote:
I am trying to implement a PL/PgSQL function as following:
CREATE OR REPLACE FUNCTION sort_by_d_idx (nids INT[][], tbl_name VARCHAR)
RETURNS varchar[]
AS $$
DECLARE
result varchar[];
BEGIN
EXECUTE 'SELECT ARRAY(SELECT t.idx FROM generate_series(array_lower($1,1),
array_upper($1,1)) AS s(i), '
||tbl_name||' t WHERE $1[s.i][1] = t.rid and $1[s.i][2] = t.rtid ORDER
BY length(t.idx))'
INTO result;
RETURN result;
END;
$$ LANGUAGE plpgsql;
I got an error ERROR: there is no parameter $1 when I test the function with:
select sort_by_d_idx('{{8148, 1}, {8149, 1}, {300, 2}, {8163, 1}, {8170,
1}}'::int[][], 'd_tree_1');
The error is understandable, but my question is how to supply the int[][] array into the dynamic SQL?
In 8.4, there will be a EXECUTE '...' USING construct that you could
use:
http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN.
I don't quite understand what the function is trying to do, but in
existing releases you could store the input array into a temporary
table, or rewrite the function to do its magic in a for loop instead of
a single query.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
