Re: [HACKERS] navigation menu for documents
On Jul 16, 2009, at 12:49 PM, Andrew Dunstan wrote: I'm using jQuery to pull the proper doc into a div. I'm still noodling with it, trying to fix encoding issues on Windows, but it's pretty close to done. Yes, that's nice, it's just the sort of thing I had in mind - if you can do it with a div instead of frames I'm fine with that. Yep. If I can solve the bloody encoding issue with Windows, it'll be good to go with Pod docs, and easily portable to any HTML-based docs. Best, David -- 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] slow count in window query
2009/7/17 Pavel Stehule pavel.steh...@gmail.com: Hello look on: postgres=# explain select count(*) over () from x; QUERY PLAN - WindowAgg (cost=0.00..265.00 rows=1 width=0) - Seq Scan on x (cost=0.00..140.00 rows=1 width=0) (2 rows) Time: 1,473 ms postgres=# explain select count(*) over (order by a) from x; QUERY PLAN WindowAgg (cost=0.00..556.25 rows=1 width=4) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) (2 rows) but query1: 160ms query2: 72ms Well, how about select a from x order by a? I wonder if index scan affects more than windowagg. -- Hitoshi Harada -- 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] Using results from INSERT ... RETURNING
On Tuesday 07 July 2009 23:31:54 Marko Tiikkaja wrote: Here's a patch(WIP) that implements INSERT .. RETURNING inside a CTE. Could you supply some test cases to illustrate what this patch accomplishes? -- 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] Docbook toolchain interfering with patch review?
On Thursday 16 July 2009 23:50:14 Greg Smith wrote: On Thu, 16 Jul 2009, Josh Berkus wrote: Well, after an hour of tinkering with docbook DTDs and openjade I've given up on building docs for the patch I was reviewing on my Mac. It's easier to get the whole chain working under Linux, but even that isn't trivial. I think one useful step here would be to write up some practical docs on the package setup side here for various popular platforms on the wiki. http://www.postgresql.org/docs/current/static/docguide-toolsets.html -- 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] fmgroids.h not installed by make install in VPATH
On Wednesday 08 July 2009 02:09:20 Alvaro Herrera wrote: It seems our makefiles fail to install fmgroids.h by make install in a VPATH build. I think the solution is to treat fmgroids.h just like pg_config_os.h, i.e. add a line like this: $(INSTALL_DATA) utils/fmgroids.h '$(DESTDIR)/$(includedir_server)/utils' The fix looks right. Has it been applied? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Higher TOAST compression.
Friendly greetings ! I'd like to have a higher compression ratio on our base. From pg 8.3 documentation : The TOAST code is triggered only when a row value to be stored in a table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The TOAST code will compress and/or move field values out-of-line until the row value is shorter than TOAST_TUPLE_TARGET bytes (also normally 2 kB) or no more gains can be had. According to the source code : TOAST_TUPLE_THRESHOLD = a page (8KB) divided by TOAST_TUPLES_PER_PAGE (4 by default) = 2KB. TOAST_TUPLE_TARGET = TOAST_TUPLE_THRESHOLD = 2KB If i understood correctly, the compression stop when the data to toast is equal to TOAST_TUPLE_TARGET What about trying to change the TOAST_TUPLE_TARGET to get a higher compression (by having more toasted record) ? I'd like to change the TOAST_TUPLES_PER_PAGE. Maybe from 4 to 8 ? Is that correct ? Did i missed something ? I did some statistics and i will have much more TOASTed record as most of them are between 1KB and 2KB. The servers have a lot of free cpu (2x4 core) and are running out of IO, i hope to save some IO. PS : The tables are clustered and all required index are present. Any tought ? idea ? Thank you. -- F4FQM Kerunix Flan Laurent Laborde -- 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] pg_stat_activity.application_name
On Thursday 16 July 2009 22:08:25 Kevin Grittner wrote: On the admin list there was a request for an application name column in pg_stat_activity. http://archives.postgresql.org/pgsql-admin/2009-07/msg00095.php This is available in a lot of other DBMS products, can be useful to DBAs, and seems pretty cheap and easy. Could we get that onto the TODO list? A facility to show it in the logs (via log_line_prefix probably) would also be useful. -- 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] Lock Wait Statistics (next commitfest)
Jaime Casanova wrote: i did it myself, i think this is something we need... this compile and seems to work... something i was wondering is that having the total time of lock waits is not very accurate because we can have 9 lock waits awaiting 1 sec each and one awaiting for 1 minute... simply sum them all will give a bad statistic or am i missing something? Thank you Jaime - looks good. I seem to have been asleep at the wheel and missed *both* of your emails until now, belated apologies for that - especially the first one :-( With respect to the sum of wait times being not very granular, yes - quite true. I was thinking it is useful to be able to answer the question 'where is my wait time being spent' - but it hides cases like the one you mention. What would you like to see? would max and min wait times be a useful addition, or are you thinking along different lines? Cheers Mark -- 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: support for multiplexing SIGUSR1
Hi Jaime, On Fri, Jul 17, 2009 at 6:31 AM, Jaime Casanovajcasa...@systemguards.com.ec wrote: I'm reviewing this patch: http://archives.postgresql.org/message-id/3f0b79eb0907022341m1d36a841x19c3e2a5a6906...@mail.gmail.com Thanks for reviewing the patch! something that make me nervous is this: /* * Note: Since there's no locking, it's possible that the target * process detaches from shared memory and exits right after this * test, before we set the flag and send signal. And the signal slot * might even be recycled by a new process, so it's remotely possible * that we set a flag for a wrong process. That's OK, all the signals * are such that no harm is done if they're mistakenly fired. */ can we signal a wrong process and still be fine? Umm... the old flag might be set to a new process wongly as follows. 1. The target process exits right after SendProcSignal passed that test. 2. A new process is assigned to the same ProcSignalSlot, and reset it. 3. SendProcSignal sets the old flag to the slot. I think that locking is required here to get around this problem. How about introducing a new slock variable slock_t pss_lck into ProcSignalSlot strust, which protects pss_pid and pss_signalFlags? SendProcSignal and ProcSignalInit should use the slock. besides, seems like SendProcSignal is still attached to SIGUSR1 only, is this fine? Yes, I think that multiplexing of one signal is enough. Why do you think that SendProcSignal should be attached to also the other signals? Another thing that took my attention, i don't think this is safe (it assumes only one auxiliary process of any type, don't know if we have various of the same kind but...): + /* +* Assign backend ID to auxiliary processes like backends, in order to +* allow multiplexing signal to auxiliary processes. Since backends use +* ID in the range from 1 to MaxBackends (inclusive), we assign +* auxiliary processes with MaxBackends + AuxProcType + 1 as an unique ID. +*/ + MyBackendId = MaxBackends + auxType + 1; + MyProc-backendId = MyBackendId; That assumption is OK for now, but might be unacceptable in the near future. So, I'll use the index of AuxiliaryProcs instead of auxType, which is assigned in InitAuxiliaryProcess. Is this OK? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] DefaultACLs
Hi, No, DefaultACLs applies to objects created in the future while GRANT ON ALL affects existing objects. I see. DefaultACLs is more important functionality so it should probably take precedence in review process. There is however one thing that needs some attention. Both patches add distinction between VIEW and TABLE objects for acls into parser and they both do it differently. GRANT ON ALL works by adding ACL_OBJECT_VIEW and tracks that object type in code (that was my original method in both patches) while DefaultACLs uses method suggested by Stephen Frost which is creating new enum with relation, view, function and sequence members (those are object types for which both DefaultACLs and GRANT ON ALL are applicable). The second method has advantage of minimal changes to existing code. I briefly looked at the DefaultACLs patch. Can you not re-use the GrantStmt structure for the defaults purpose too? You might have to introduce an is_default boolean similar to the is_schema boolean that you have added in the GRANT ON ALL patch. If you think you can re-use the GrantStmt structure, then we might as well stick with the existing object type code and not add the enums in the DefaultACLs patch too.. Regards, Nikhils -- 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] Synch Rep for CommitFest 2009-07
Hi, On Thu, Jul 16, 2009 at 6:00 PM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: The archive should not normally contain partial XLOG files, only if you manually copy one there after primary has crashed. So I don't think that's something we need to support. You are right. And, if the last valid record exists in the middle of the restored file (e.g. by XLOG_SWITCH record), begin should indicate the head of the next file. Hmm. You only need the timeline history file if the base backup was taken in an earlier timeline. That situation would only arise if you (manually) take a base backup, restore to a server (which creates a new timeline), and then create a slave against that server. At least in the 1st phase, I think we can assume that the standby has access to the same archive, and will find the history file from there. If not, throw an error. We can add more bells and whistles later. Okey, I hold the problem about a history file for possible later consideration. As the patch stands, new walsender connections are refused when one is active already. What if the walsender connection is in a zombie state? For example, it's trying to send WAL to the slave, but the network connection is down, and the packets are going to a black hole. It will take a while for the TCP layer to declare the connection dead, and close the socket. During that time, you can't connect a new slave to the master, or the same slave using a better network connection. The most robust way to fix that is to support multiple walsenders. The zombie walsender can take its time to die, while the new walsender serves the new connection. You could tweak SO_TIMEOUTs and stuff, but even then the standby process could be in some weird hung state. And of course, when we get around to add support for multiple slaves, we'll have to do that anyway. Better get it right to begin with. Thanks for the detailed description! I was thinking that a new GUC replication_timeout and some keepalive parameters would be enough to help with such trouble. But I agree that the support multiple walsenders is better solution, so I'll try this problem. Even in synchronous replication, a backend should only have to wait when it commits. You would only see the difference with very large transactions that write more WAL than fits in wal_buffers, though, like data loading. That's right. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] Synch Rep for CommitFest 2009-07
Hi, On Fri, Jul 17, 2009 at 2:09 AM, Greg Starkgsst...@mit.edu wrote: Only the sysadmin is actually going to know which makes more sense. Unless we start tieing WAL parameters to the database size or something like that. Agreed. And, if a user doesn't want to make a new base backup because of a large database, s/he can manually copy the archived WAL files to the standby before starting it, and make it use them for its recovery. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] GRANT ON ALL IN schema
Hi, Attached is v2 with slightly improved code, nothing has changed feature-wise. Here are some comments on this patch from my side: grant.sgml * Maybe we should use replaceable class=parameterschemaname/replaceable in the sgml references instead of just replaceableschemaname/replaceable +There is also the posibility of granting permissions to all objects of +given type inside one or multiple schemas. This functionality is supported +for tables, views, sequences and functions and can done by using +ALL TABLES IN schemanema syntax in place of object name. + /para + + para typo posibility It should be ALL [TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemaname (note the other typo here) syntax to be precise IMHO. aclchk.c + elog(ERROR, unrecognized GrantStmt.objtype: %d, +(int) objtype); Kinda funny to mention the C structure name in the error. But I see that the other functions in the file do the same, so should be ok. I doubt if the syntax allows any other object type to reach upto this function anyways :) parsenodes.h GrantObjectType objtype;/* kind of object being operated on */ + boolis_schema; /* if true we want all objects +* of objtype in schema */ You forgot to make changes in _copyGrantStmt and _equalGrantStmt to account for this new field. Rest of the changes look straightforward and ok to me. make installcheck passes cleanly too. I also do not see any new warnings due to this patch. As an aside, I was just wondering the behaviour for RELKIND_INDEX? Regards, Nikhils -- 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] GRANT ON ALL IN schema
Nikhil Sontakke wrote:
grant.sgml
* Maybe we should use
replaceable class=parameterschemaname/replaceable in the sgml
references instead of just replaceableschemaname/replaceable
+There is also the posibility of granting permissions to all objects of
+given type inside one or multiple schemas. This functionality is supported
+for tables, views, sequences and functions and can done by using
+ALL TABLES IN schemanema syntax in place of object name.
+ /para
+
+ para
typo posibility
It should be ALL [TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemaname
(note the other typo here) syntax to be precise IMHO.
Right, fixed.
aclchk.c
+ elog(ERROR, unrecognized GrantStmt.objtype: %d,
+(int) objtype);
Kinda funny to mention the C structure name in the error. But I see
that the other functions in the file do the same, so should be ok. I
doubt if the syntax allows any other object type to reach upto this
function anyways :)
It's copy paste :)
But it seemed a bit strange to me too as this kind of thing is not
recommended in developer guide. On the other hand ordinary user should
not ever see this unless something is horribly wrong with bison.
parsenodes.h
GrantObjectType objtype;/* kind of object being operated on */
+ boolis_schema; /* if true we want all objects
+* of objtype
in schema */
You forgot to make changes in _copyGrantStmt and _equalGrantStmt to
account for this new field.
Fixed.
As an aside, I was just wondering the behaviour for RELKIND_INDEX?
Indexes don't have permissions afaik so nothing.
I attached modified patch per your comments and also updated to current
HEAD.
Thanks for your review.
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..51aad15 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*** PostgreSQL documentation
*** 23,39
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
[,...] | ALL [ PRIVILEGES ] ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE replaceable class=PARAMETERsequencename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] replaceable
class=PARAMETERtablename/replaceable [, ...] }
! | ALL [ TABLES | VIEWS ] IN replaceable
class=PARAMETERschemaname/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
[,...] | ALL [ PRIVILEGES ] ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) }
! ON [ TABLE | VIEW ] replaceable
class=PARAMETERtablename/replaceable [, ...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE replaceable class=PARAMETERsequencename/replaceable
[, ...]
! | ALL SEQUENCES IN replaceable
class=PARAMETERschemaname/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION replaceablefuncname/replaceable ( [ [ replaceable
class=parameterargmode/replaceable ] [ replaceable
class=parameterargname/replaceable ] replaceable
class=parameterargtype/replaceable [, ...] ] ) [, ...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
[HACKERS] ECPG support for struct in INTO list
Hi,
one of our clients wants to port their application suite
from Informix to PostgreSQL, they use constructs like
SELECT * INTO :tablerec FROM table ...
where tablerec mirrors the table fields in a C struct.
Currently ECPG dumps core on this, more exactly aborts on it
in ecpg_type_name().
Patch is attached that solves it by introducing add_struct_to_head()
called from rule coutputvariable that also catches C unions now,
emitting an error because unions cannot be unambiguously unrolled.
It tries to handle NULL indicator, expecting a struct with at least
the same amount of members.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
diff -dcrpN pgsql85dev.4string/src/interfaces/ecpg/preproc/ecpg.trailer pgsql85dev.5struct/src/interfaces/ecpg/preproc/ecpg.trailer
*** pgsql85dev.4string/src/interfaces/ecpg/preproc/ecpg.trailer 2009-07-14 21:36:58.0 +0200
--- pgsql85dev.5struct/src/interfaces/ecpg/preproc/ecpg.trailer 2009-07-17 12:24:30.0 +0200
*** c_args: /*EMPTY*/ { $$ = EMPTY; }
*** 1835,1843
;
coutputvariable: cvariable indicator
! { add_variable_to_head(argsresult, find_variable($1), find_variable($2)); }
| cvariable
! { add_variable_to_head(argsresult, find_variable($1), no_indicator); }
;
--- 1835,1873
;
coutputvariable: cvariable indicator
! {
! struct variable *var = find_variable($1);
!
! switch (var-type-type)
! {
! case ECPGt_struct:
! add_struct_to_head(argsresult, var, find_variable($2));
! break;
! case ECPGt_union:
! mmerror(PARSE_ERROR, ET_FATAL, variable \%s\ is a union, var-name);
! break;
! default:
! add_variable_to_head(argsresult, var, find_variable($2));
! break;
! }
! }
| cvariable
! {
! struct variable *var = find_variable($1);
!
! switch (var-type-type)
! {
! case ECPGt_struct:
! add_struct_to_head(argsresult, var, no_indicator);
! break;
! case ECPGt_union:
! mmerror(PARSE_ERROR, ET_FATAL, variable \%s\ is a union, var-name);
! break;
! default:
! add_variable_to_head(argsresult, var, no_indicator);
! break;
! }
! }
;
diff -dcrpN pgsql85dev.4string/src/interfaces/ecpg/preproc/extern.h pgsql85dev.5struct/src/interfaces/ecpg/preproc/extern.h
*** pgsql85dev.4string/src/interfaces/ecpg/preproc/extern.h 2009-07-14 21:36:58.0 +0200
--- pgsql85dev.5struct/src/interfaces/ecpg/preproc/extern.h 2009-07-17 12:24:30.0 +0200
*** extern struct descriptor *lookup_descrip
*** 91,96
--- 91,97
extern struct variable *descriptor_variable(const char *name, int input);
extern struct variable *sqlda_variable(const char *name);
extern void add_variable_to_head(struct arguments **, struct variable *, struct variable *);
+ extern void add_struct_to_head(struct arguments **, struct variable *, struct variable *);
extern void add_variable_to_tail(struct arguments **, struct variable *, struct variable *);
extern void remove_variable_from_list(struct arguments ** list, struct variable * var);
extern void dump_variables(struct arguments *, int);
diff -dcrpN pgsql85dev.4string/src/interfaces/ecpg/preproc/variable.c pgsql85dev.5struct/src/interfaces/ecpg/preproc/variable.c
*** pgsql85dev.4string/src/interfaces/ecpg/preproc/variable.c 2009-07-14 21:36:58.0 +0200
--- pgsql85dev.5struct/src/interfaces/ecpg/preproc/variable.c 2009-07-17 12:24:30.0 +0200
*** add_variable_to_head(struct arguments **
*** 382,387
--- 382,461
*list = p;
}
+ /*
+ * Insert a struct's members unrolled into our request list.
+ * This is needed for the case when the user says
+ *
+ * SELECT * INTO :mystruct FROM ...
+ * or
+ * SELECT a.*, b.* INTO :struct_a, :struct_b FROM a, b ...
+ *
+ * Just in case, implement it recursively.
+ */
+ void
+ add_struct_to_head(struct arguments ** list, struct variable * var, struct variable * ind)
+ {
+ struct ECPGstruct_member *member;
+ struct ECPGstruct_member *ind_member = NULL;
+ bool no_ind;
+
+ if (var-type-type != ECPGt_struct)
+ mmerror(PARSE_ERROR, ET_FATAL, variable \%s\ is not a struct, var-name);
+
+ no_ind = (ind == no_indicator);
+
+ if (!no_ind ind-type-type != ECPGt_struct)
+ mmerror(INDICATOR_NOT_STRUCT, ET_FATAL, struct variable \%s\ was associated with a non-struct indicator variable , var-name);
+
+ member = var-type-u.members;
+ if (!no_ind)
+ ind_member = ind-type-u.members;
+
+ while (member (no_ind || ind_member))
+ {
+ char *newvarname;
+ char *newindname;
+ struct variable *newvar;
+
Re: [HACKERS] [PATCH] DefaultACLs
Nikhil Sontakke wrote: There is however one thing that needs some attention. Both patches add distinction between VIEW and TABLE objects for acls into parser and they both do it differently. GRANT ON ALL works by adding ACL_OBJECT_VIEW and tracks that object type in code (that was my original method in both patches) while DefaultACLs uses method suggested by Stephen Frost which is creating new enum with relation, view, function and sequence members (those are object types for which both DefaultACLs and GRANT ON ALL are applicable). The second method has advantage of minimal changes to existing code. I briefly looked at the DefaultACLs patch. Can you not re-use the GrantStmt structure for the defaults purpose too? You might have to introduce an is_default boolean similar to the is_schema boolean that you have added in the GRANT ON ALL patch. If you think you can re-use the GrantStmt structure, then we might as well stick with the existing object type code and not add the enums in the DefaultACLs patch too.. No we can't use the GrantStmt and I wasn't talking about using it. I was talking about the change in GrantObjectType and differentiating VIEW and TABLE in some code inside aclchk.c which people didn't like. We can use the changed GrantObjectType in DefaultACLs and filter inapplicable types inside C code as I do in GRANT ON ALL patch and it's what I did originally, but submitted version of DefaultACLs behaves differently. -- Regards Petr Jelinek (PJMODOS) -- 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] ECPG support for struct in INTO list
On Fri, Jul 17, 2009 at 12:27:49PM +0200, Boszormenyi Zoltan wrote: one of our clients wants to port their application suite from Informix to PostgreSQL, they use constructs like SELECT * INTO :tablerec FROM table ... where tablerec mirrors the table fields in a C struct. Well, this was supposed to work. Currently ECPG dumps core on this, more exactly aborts on it in ecpg_type_name(). Could you please send an example where it dumps core? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- 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] DefaultACLs
Nikhil, * Nikhil Sontakke (nikhil.sonta...@enterprisedb.com) wrote: I briefly looked at the DefaultACLs patch. Can you not re-use the GrantStmt structure for the defaults purpose too? You might have to introduce an is_default boolean similar to the is_schema boolean that you have added in the GRANT ON ALL patch. If you think you can re-use the GrantStmt structure, then we might as well stick with the existing object type code and not add the enums in the DefaultACLs patch too.. Petr and I discussed this. Part of the problem is that the regular grant enums don't distinguish between TABLE and VIEW because they don't need to. We need to with DefaultACL because users see those as distinct types of objects even though we track them in the same catalog. Splitting up RELATION into TABLE and VIEW in the grant enum would increase the changes quite a bit in otherwise unrelated paths. Additionally, not all of the grantable types are applicable for DefaultACL since DefaultACLs are associated with objects in schemas (eg: database permissions, schema permissions, etc). We can certainly do it either way, but I don't see much downside to having a new enum and a number of downsides with modifying the existing grant enums. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
On Friday 17 July 2009 06:10:12 Robert Haas wrote: 2009/7/16 KaiGai Kohei kai...@ak.jp.nec.com: Yes, the tiny version will not give any advantages in security without future enhancements. It is not difficult to add object classes and permissions. If necessary, I'll add checks them with corresponding permissions. One anxiety is PostgreSQL specific object class, such as LANGUAGE. It's not clear for me whether the maintainer of the SELinux security policy accept these kind of object classes, or not. I would like to implement them except for PostgreSQL specific object class in this phase. I'm starting to think that there's just no hope of this matching up well enough with the way PostgreSQL already works to have a chance of being accepted. What I'm understanding here is the apparent requirement that the SEPostgreSQL implementation be done in a way that a generic SELinux policy that has been written for an operating system and file system can be applied to PostgreSQL without change and do something useful. I can see merits for or against that. But in any case, this needs to be clarified, if I understand this requirement correctly anyway. -- 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] DefaultACLs
Hi, I briefly looked at the DefaultACLs patch. Can you not re-use the GrantStmt structure for the defaults purpose too? You might have to introduce an is_default boolean similar to the is_schema boolean that you have added in the GRANT ON ALL patch. If you think you can re-use the GrantStmt structure, then we might as well stick with the existing object type code and not add the enums in the DefaultACLs patch too.. Petr and I discussed this. Part of the problem is that the regular grant enums don't distinguish between TABLE and VIEW because they don't need to. We need to with DefaultACL because users see those as distinct types of objects even though we track them in the same catalog. Splitting up RELATION into TABLE and VIEW in the grant enum would increase the changes quite a bit in otherwise unrelated paths. Additionally, not all of the grantable types are applicable for DefaultACL since DefaultACLs are associated with objects in schemas (eg: database permissions, schema permissions, etc). Ok. We can certainly do it either way, but I don't see much downside to having a new enum and a number of downsides with modifying the existing grant enums. Sure, I understand. But if we want to go the DefaultACLs way, then we need to change the GRANT ON ALL patch a bit too for the sake of uniformity - don't we? There is indeed benefit in managing ACLs for existing objects, so that patch has some value too. Regards, Nikhils -- 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] DefaultACLs
Hey, * Nikhil Sontakke (nikhil.sonta...@enterprisedb.com) wrote: We can certainly do it either way, but I don't see much downside to having a new enum and a number of downsides with modifying the existing grant enums. Sure, I understand. But if we want to go the DefaultACLs way, then we need to change the GRANT ON ALL patch a bit too for the sake of uniformity - don't we? There is indeed benefit in managing ACLs for existing objects, so that patch has some value too. I agree that they should be consistant. The GRANT ON ALL shares alot more of the syntax with GRANT than DefaultACL though, which makes it a more interesting question there. I can understand not wanting to duplicate the GRANT syntax. I think my suggestion would be to add a field to the structure passed around by GRANT which indicates if 'VIEW' was requested or not in the command. This could be used both for GRANT ON ALL and to allow 'GRANT ON VIEW blah' to verify that the relation being granted on is a view. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] GRANT ON ALL IN schema
One more typo fix in docs
--
Regards
Petr Jelinek (PJMODOS)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index bf963b8..6400f9e 100644
*** a/doc/src/sgml/ref/grant.sgml
--- b/doc/src/sgml/ref/grant.sgml
*** PostgreSQL documentation
*** 23,39
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
[,...] | ALL [ PRIVILEGES ] ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON SEQUENCE replaceable class=PARAMETERsequencename/replaceable [,
...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
--- 23,41
synopsis
GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER
}
[,...] | ALL [ PRIVILEGES ] }
! ON { { [ TABLE | VIEW ] replaceable
class=PARAMETERtablename/replaceable [, ...] }
! | ALL [ TABLES | VIEWS ] IN replaceable
class=PARAMETERschemaname/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( replaceable
class=PARAMETERcolumn/replaceable [, ...] )
[,...] | ALL [ PRIVILEGES ] ( replaceable
class=PARAMETERcolumn/replaceable [, ...] ) }
! ON [ TABLE | VIEW ] replaceable
class=PARAMETERtablename/replaceable [, ...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { USAGE | SELECT | UPDATE }
[,...] | ALL [ PRIVILEGES ] }
! ON { SEQUENCE replaceable class=PARAMETERsequencename/replaceable
[, ...]
! | ALL SEQUENCES IN replaceable
class=PARAMETERschemaname/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
*** GRANT { USAGE | ALL [ PRIVILEGES ] }
*** 49,55
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON FUNCTION replaceablefuncname/replaceable ( [ [ replaceable
class=parameterargmode/replaceable ] [ replaceable
class=parameterargname/replaceable ] replaceable
class=parameterargtype/replaceable [, ...] ] ) [, ...]
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
--- 51,58
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { EXECUTE | ALL [ PRIVILEGES ] }
! ON { FUNCTION replaceablefuncname/replaceable ( [ [ replaceable
class=parameterargmode/replaceable ] [ replaceable
class=parameterargname/replaceable ] replaceable
class=parameterargtype/replaceable [, ...] ] ) [, ...]
! | ALL FUNCTIONS IN replaceable
class=PARAMETERschemaname/replaceable [, ...] }
TO { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...] [ WITH GRANT OPTION ]
GRANT { USAGE | ALL [ PRIVILEGES ] }
*** GRANT replaceable class=PARAMETERrol
*** 143,148
--- 146,159
/para
para
+There is also the possibility of granting permissions to all objects of
+given type inside one or multiple schemas. This functionality is supported
+for tables, views, sequences and functions and can done by using
+ALL {TABLES|VIEWS|SEQUENCES|FUNCTIONS} IN schemaname syntax in place
+of object name.
+ /para
+
+ para
The possible privileges are:
variablelist
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 8d62580..ac0905f 100644
*** a/doc/src/sgml/ref/revoke.sgml
--- b/doc/src/sgml/ref/revoke.sgml
*** PostgreSQL documentation
*** 24,44
REVOKE [ GRANT OPTION FOR ]
{ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[,...] | ALL [ PRIVILEGES ] }
! ON [ TABLE ] replaceable class=PARAMETERtablename/replaceable [,
...]
FROM { [ GROUP ] replaceable class=PARAMETERrolename/replaceable |
PUBLIC } [, ...]
[ CASCADE | RESTRICT ]
REVOKE [ GRANT OPTION FOR ]
{ {
Re: [HACKERS] ECPG support for struct in INTO list
Michael Meskes írta:
On Fri, Jul 17, 2009 at 12:27:49PM +0200, Boszormenyi Zoltan wrote:
one of our clients wants to port their application suite
from Informix to PostgreSQL, they use constructs like
SELECT * INTO :tablerec FROM table ...
where tablerec mirrors the table fields in a C struct.
Well, this was supposed to work.
Currently ECPG dumps core on this, more exactly aborts on it
in ecpg_type_name().
Could you please send an example where it dumps core?
Attached is the short example I can reproduce with.
The version I used was final PostgreSQL 8.4.0, without our
extensions posted already. I added an indication to ecpg_type_name():
[z...@db00 ecpg-test]$ ecpg -C INFORMIX test28.pgc
ecpg_type_name: unhandled type 22
Félbeszakítva (core dumped)
Type 22 is exactly ECPGt_struct. gdb cannot get the passed value:
[z...@db00 ecpg-test]$ gdb ~/pgc84pre/bin/ecpg core.850
...
#0 0x003994032215 in raise (sig=value optimized out) at
../nptl/sysdeps/unix/sysv/linux/raise.c:64
64 return INLINE_SYSCALL (tgkill, 3, pid, selftid, sig);
(gdb) bt
#0 0x003994032215 in raise (sig=value optimized out) at
../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1 0x003994033d83 in abort () at abort.c:88
#2 0x00423d65 in ecpg_type_name (typ=value optimized out) at
typename.c:65
#3 0x00402742 in adjust_informix (list=0x1e74560) at preproc.y:272
#4 0x00406ca7 in base_yyparse () at preproc.y:6581
#5 0x00422b22 in main (argc=4, argv=0x7fff24b40aa8) at ecpg.c:456
test28.pgc contains this, ECPG aborts:
EXEC SQL DECLARE mycur CURSOR FOR SELECT * INTO :myvar FROM a1 WHERE id = 1;
EXEC SQL FETCH FROM mycur;
But you are right about the supposed to work part,
if I modify it the way below, it works:
EXEC SQL DECLARE mycur CURSOR FOR SELECT * FROM a1 WHERE id = 1;
EXEC SQL FETCH FROM mycur INTO :myvar;
Thanks,
Zoltán Böszörményi
Michael
--
Bible has answers for everything. Proof:
But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil. (Matthew 5:37) - basics of digital technology.
May your kingdom come - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig Schönig GmbH
http://www.postgresql.at/
struct mytype {
int id;
char t[64];
dec_t d1;
double d2;
char c[30];
};
typedef struct mytype MYTYPE;
/*
* Test DECLARE ... SELECT ... INTO ...
* with string
*/
#include stdio.h
#include stdlib.h
EXEC SQL DEFINE MYDB1 zozo;
EXEC SQL DEFINE MYUSER1 zozo;
EXEC SQL BEGIN DECLARE SECTION;
EXEC SQL include test28.h;
EXEC SQL END DECLARE SECTION;
int main(int argc, char **argv) {
EXEC SQL BEGIN DECLARE SECTION;
MYTYPE myvar;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER SQLWARNING SQLPRINT;
EXEC SQL WHENEVER SQLERROR SQLPRINT;
EXEC SQL connect to MYDB1 USER MYUSER1;
if (sqlca.sqlcode)
{
printf (connect error = %ld\n, sqlca.sqlcode);
exit (sqlca.sqlcode);
}
EXEC SQL DECLARE mycur CURSOR FOR SELECT * INTO :myvar FROM a1 WHERE id
= 1;
EXEC SQL OPEN mycur;
EXEC SQL WHENEVER NOT FOUND GOTO out;
EXEC SQL FETCH FROM mycur;
printf(c = '%s'\n, myvar.c);
out:
EXEC SQL CLOSE mycur;
EXEC SQL DISCONNECT;
return 0;
}
--
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: support for multiplexing SIGUSR1
Hi, On Fri, Jul 17, 2009 at 5:41 PM, Fujii Masaomasao.fu...@gmail.com wrote: I'm reviewing this patch: http://archives.postgresql.org/message-id/3f0b79eb0907022341m1d36a841x19c3e2a5a6906...@mail.gmail.com I updated the patch to solve two problems which you pointed. Here is the changes: * Prevented the obsolete flag to being set to a new process, by using newly-introduced spinlock. * Used the index of AuxiliaryProcs instead of auxType, to assign backend ID to an auxiliary process. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center signal_multiplexer_0717.patch Description: Binary data -- 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] slow count in window query
Pavel Stehule pavel.steh...@gmail.com wrote: postgres=# explain select count(*) over () from x; WindowAgg (cost=0.00..265.00 rows=1 width=0) - Seq Scan on x (cost=0.00..140.00 rows=1 width=0) postgres=# explain select count(*) over (order by a) from x; WindowAgg (cost=0.00..556.25 rows=1 width=4) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) query1: 160ms query2: 72ms EXPLAIN ANALYZE is more telling than just EXPLAIN. Did you run both several times or flush caches carefully between the runs to eliminate caching effects? Is it possible that there are a lot of dead rows in the table (from UPDATEs or DELETEs), and the table has been vacuumed? (Output from VACUUM VERBOSE on the table would show that.) -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] slow count in window query
2009/7/17 Kevin Grittner kevin.gritt...@wicourts.gov: Pavel Stehule pavel.steh...@gmail.com wrote: postgres=# explain select count(*) over () from x; WindowAgg (cost=0.00..265.00 rows=1 width=0) - Seq Scan on x (cost=0.00..140.00 rows=1 width=0) postgres=# explain select count(*) over (order by a) from x; WindowAgg (cost=0.00..556.25 rows=1 width=4) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) query1: 160ms query2: 72ms EXPLAIN ANALYZE is more telling than just EXPLAIN. Query1 QUERY PLAN - Aggregate (cost=931.50..931.51 rows=1 width=4) (actual time=274.423..274.425 rows=1 loops=1) - Subquery Scan p (cost=0.00..931.25 rows=100 width=4) (actual time=220.220..274.388 rows=2 loops=1) Filter: ((p.r = ((p.rc + 1) / 2)) OR (p.r = ((p.rc + 2) / 2))) - WindowAgg (cost=0.00..681.25 rows=1 width=4) (actual time=120.622..247.618 rows=1 loops=1) - WindowAgg (cost=0.00..556.25 rows=1 width=4) (actual time=0.088..89.848 rows=1 loops=1) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) (actual time=0.066..33.962 rows=1 loops Total runtime: 274.934 ms (7 rows) query2: postgres=# explain analyze select avg(a) from (select a, row_number() over (order by a asc) as hi, row_number() over (order by a desc) as lo from x) s where hi in (lo-1,lo+1); QUERY PLAN - Aggregate (cost=1595.89..1595.90 rows=1 width=4) (actual time=215.101..215.103 rows=1 loops=1) - Subquery Scan s (cost=1220.63..1595.63 rows=100 width=4) (actual time=175.159..215.073 rows=1 loops=1) Filter: ((s.hi = (s.lo - 1)) OR (s.hi = (s.lo + 1))) - WindowAgg (cost=1220.63..1395.63 rows=1 width=4) (actual time=136.985..191.231 rows=1 loops=1) - Sort (cost=1220.63..1245.63 rows=1 width=4) (actual time=136.970..151.905 rows=1 loops=1) Sort Key: x.a Sort Method: quicksort Memory: 686kB - WindowAgg (cost=0.00..556.25 rows=1 width=4) (actual time=0.078..106.927 rows=1 loops=1) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) (actual time=0.058..33.594 rows=1 Total runtime: 215.845 ms (10 rows) Did you run both several times or flush caches carefully between the runs to eliminate caching effects? yes, - in both variants data was read from cache. Is it possible that there are a lot of dead rows in the table (from UPDATEs or DELETEs), and the table has been vacuumed? (Output from VACUUM VERBOSE on the table would show that.) table was filled with random numbers and analyzed - you can simple check it - look on begin of the thread. This table wasn't updated. Pavel -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] slow count in window query
2009/7/17 Hitoshi Harada umi.tan...@gmail.com: 2009/7/17 Pavel Stehule pavel.steh...@gmail.com: Hello look on: postgres=# explain select count(*) over () from x; QUERY PLAN - WindowAgg (cost=0.00..265.00 rows=1 width=0) - Seq Scan on x (cost=0.00..140.00 rows=1 width=0) (2 rows) Time: 1,473 ms postgres=# explain select count(*) over (order by a) from x; QUERY PLAN WindowAgg (cost=0.00..556.25 rows=1 width=4) - Index Scan using gg on x (cost=0.00..406.25 rows=1 width=4) (2 rows) but query1: 160ms query2: 72ms Well, how about select a from x order by a? I wonder if index scan affects more than windowagg. select a from x - 42ms select a from x order by a - 50ms all data are from cache. -- Hitoshi Harada -- 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] pg_stat_activity.application_name
Peter Eisentraut pete...@gmx.net wrote: A facility to show it in the logs (via log_line_prefix probably) would also be useful. Agreed. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] OT: Testing - please ignore
-- Richard Huxton Archonet Ltd -- 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] ALTER TABLE ... ALTER COLUMN ... SET DISTINCT
Hi,
Le 3 mai 09 à 22:13, Robert Haas a écrit :
OK, new version of patch, this time with the weird scaling removed and
the datatype changed to float4.
You've been assigning me this patch review, so here it goes :)
I have not changed the minimum value for remoteVersion in pg_dump.c,
as that would make the patch not able to be tested now. So that line
and the comment two lines following will need to be updated prior to
application. Also requires catversion bump.
I guess now would be a good time to fix this part of the patch?
I couldn't apply it to current head because of bitrot. It applies fine
to git commit bcaef8b5a0e2d5c143dabd8516090a09e39b27b8 [1] but from
there the automatic forward merge of git isn't able to merge
pg_attribute.h. As I don't have as much time to give to the review as
I'd like, I'd very much welcome if you could fix this part of your
patch and I'll resume my work thereafter.
I'll change the patch status to Waiting on Author as soon as I'll
have this mail id.
Now I've had time to read the code, here are my raw notes:
pg_dump.c:
tbinfo-attstattarget[j] = atoi(PQgetvalue(res, j,
i_attstattarget));
+ tbinfo-attdistinct[j] = strdup(PQgetvalue(res, j,
i_attdistinct));
...
+ if (atof(tbinfo-attdistinct[j]) != 0
+ !tbinfo-attisdropped[j])
- style issue, convert at PQgetvalue() time
- prefer strtod() over atof? Here's what my local man page has to
say about the case:
The atof() function has been deprecated by strtod() and should
not be used in
new code.
tablecmds.c:
+ switch (nodeTag(newValue))
+ {
+ case T_Integer:
...
+ case T_Float:
...
+ default:
+ elog(ERROR, unrecognized node type: %d,
+(int) nodeTag(newValue));
What about adding the following before the switch, to do like
surrounding code?
Assert(IsA(newValue, Integer) || IsA(newValue, Float));
Given your revised version I'll try to play around with ndistinct
behavior and exercise dump and restore, etc, but for now I'll pause my
work.
I guess I'll have a second look at the code to check that it's all in
the spirit of surrounding code, which I didn't complete yet (wanted to
exercise my abilities to apply the patch from a past commit and
forward-merge from there).
Regards,
--
dim
[1]:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=bcaef8b5a0e2d5c143dabd8516090a09e39b27b8
--
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] ALTER TABLE ... ALTER COLUMN ... SET DISTINCT
ALTER COLUMN SET DISTINCT feels like adding a unique constraint. ALTER COLUMN SET STATISTICS DISTINCT ? ALTER COLUMN SET STATISTICS NDISTINCT ? 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
[HACKERS] Enhancement - code completion when typing set search_path
I use postgres 8.1.X. Is there a way to add code completion when entering: set search_path = xyz I love the code completion for SQL. It would be really nice to have it for the set search_path. Thanks, Lance Campbell Project Manager/Software Architect/DBA Web Services at Public Affairs 217-333-0382 -Original Message- From: Andreas Wenk [mailto:a.w...@netzmeister-st-pauli.de] Sent: Friday, July 17, 2009 8:18 AM To: Campbell, Lance Cc: pgsql-ad...@postgresql.org Subject: Re: [ADMIN] Enhancement - code completion when typing set search_path Campbell, Lance schrieb: I use postgres 8.1.X. Is there a way to add code completion when entering: set search_path = xyz I love the code completion for SQL. It would be really nice to have it for the set search_path. me too ;-) you should send this request to the hackers list ... maybe it will be integrated in future versions ... pgsql-hackers@postgresql.org Cheers Andy -- 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] Psql List Languages
On Thu, Jul 16, 2009 at 10:41 AM, Peter Eisentrautpete...@gmx.net wrote:
On Thursday 16 July 2009 00:38:31 Fernando Ike de Oliveira wrote:
I applied the Tom Lane and Peter considerations, but I had that
remove one column (Owner) of out command \dL to compatibility with 7.4
version.
The mandate is to work as best as they can with older versions, not to provide
only the feature set that works the same across old versions. The correct
behavior should be to show the owner column if the server version supports it.
Thanks for comment, Peter
Follow new version patch, now with version postgresql version.
Regards,
--
Fernando Ike
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
***
*** 1179,1184 testdb=gt;
--- 1179,1194
varlistentry
+ termliteral\dL[S+]/literal/term
+ listitem
+ para
+ Lists all procedural languages. By default, only user-created languages are shown; supply the literalS/literal modifier to include system objects. If literal+/literal is appended to the command line, each language is listed with its associated permissions
+ /para
+ /listitem
+ /varlistentry
+
+
+ varlistentry
termliteral\dn[+] [ replaceable class=parameterpattern/replaceable ]/literal/term
listitem
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
***
*** 390,395 exec_command(const char *cmd,
--- 390,398
case 'l':
success = do_lo_list();
break;
+ case 'L':
+ success = listLanguages(pattern, show_verbose, show_system);
+ break;
case 'n':
success = listSchemas(pattern, show_verbose);
break;
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
***
*** 2261,2266 listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
--- 2261,2332
}
+ /* \dL
+ *
+ * Describes Languages.
+ */
+ bool
+ listLanguages(const char *pattern, bool verbose, bool showSystem)
+ {
+ PQExpBufferData buf;
+ PGresult *res;
+ printQueryOpt myopt = pset.popt;
+
+ initPQExpBuffer(buf);
+
+ printfPQExpBuffer(buf,
+ SELECT l.lanname AS \%s\,\n,
+ gettext_noop(Name));
+ if (pset.sversion = 80300)
+ appendPQExpBuffer(buf,
+ pg_catalog.pg_get_userbyid(l.lanowner) as \%s\,\n,
+ gettext_noop(Owner));
+ appendPQExpBuffer(buf,
+ CASE WHEN l.lanispl = 't' THEN 'Trusted' WHEN l.lanispl = 'f' THEN 'Untrusted' END AS \%s\,\n
+ CASE WHEN l.lanpltrusted='t' THEN 'Trusted' WHEN lanpltrusted='f' THEN 'Untrusted' END AS \%s\,\n
+ CASE WHEN p.oid = 0 THEN NULL ELSE p.proname END AS \%s\,\n
+ CASE WHEN q.oid = 0 THEN NULL ELSE q.proname END AS \%s\\n,
+ gettext_noop(Procedural Language),
+ gettext_noop(Trusted),
+ gettext_noop(Call Handler),
+ gettext_noop(Validator));
+
+ if (verbose)
+ {
+ appendPQExpBuffer(buf, ,\n);
+ printACLColumn(buf, l.lanacl);
+ }
+
+ appendPQExpBuffer(buf,
+ FROM pg_catalog.pg_language l\n);
+ appendPQExpBuffer(buf,
+ LEFT JOIN pg_catalog.pg_proc p on l.lanplcallfoid = p.oid\n);
+ appendPQExpBuffer(buf,
+ LEFT JOIN pg_catalog.pg_proc q on l.lanvalidator = q.oid\n);
+
+ processSQLNamePattern(pset.db, buf, pattern, false, false,
+ NULL, l.lanname, NULL, NULL);
+ if (!showSystem !pattern)
+ appendPQExpBuffer(buf, WHERE lanplcallfoid != 0);
+ appendPQExpBuffer(buf, ORDER BY 1;);
+
+ res = PSQLexec(buf.data, false);
+ termPQExpBuffer(buf);
+ if (!res)
+ return false;
+
+ myopt.nullPrint = NULL;
+ myopt.title = _(List of languages);
+ myopt.translate_header = true;
+
+ printQuery(res, myopt, pset.queryFout, pset.logfile);
+
+ PQclear(res);
+ return true;
+
+ }
+
+
/*
* \dD
*
*** a/src/bin/psql/describe.h
--- b/src/bin/psql/describe.h
***
*** 75,79 extern bool listForeignServers(const char *pattern, bool verbose);
--- 75,81
/* \deu */
extern bool listUserMappings(const char *pattern, bool verbose);
+ /* \dL */
+ extern bool listLanguages(const char *pattern, bool verbose, bool showSystem);
#endif /* DESCRIBE_H */
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
***
*** 213,218 slashUsage(unsigned short int pager)
--- 213,219
fprintf(output, _( \\dg [PATTERN] list roles (groups)\n));
fprintf(output, _( \\di[S+] [PATTERN] list indexes\n));
fprintf(output, _( \\dllist large objects, same as \\lo_list\n));
+ fprintf(output, _( \\dL[S+]list (procedural) languages\n));
fprintf(output, _(
Re: [HACKERS] slow count in window query
Pavel Stehule pavel.steh...@gmail.com wrote: table was filled with random numbers and analyzed - you can simple check it - look on begin of the thread. This table wasn't updated. Confirmed. The ORDER BY consistently speeds up the query. Odd Sort speed varied based on random sequence generated, but typical plan and timings: test=# explain analyze select count(*) over () from x; WindowAgg (cost=0.00..229.00 rows=1 width=0) (actual time=32.435..97.448 rows=1 loops=1) - Seq Scan on x (cost=0.00..104.00 rows=1 width=0) (actual time=0.007..14.818 rows=1 loops=1) Total runtime: 112.526 ms test=# explain analyze select count(*) over (order by a) from x; WindowAgg (cost=768.39..943.39 rows=1 width=4) (actual time=34.982..87.803 rows=1 loops=1) - Sort (cost=768.39..793.39 rows=1 width=4) (actual time=34.962..49.533 rows=1 loops=1) Sort Key: a Sort Method: quicksort Memory: 491kB - Seq Scan on x (cost=0.00..104.00 rows=1 width=4) (actual time=0.006..14.682 rows=1 loops=1) Total runtime: 102.023 ms -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] Duplicate key value error
Em Fri, 03 Apr 2009 04:23:10 -0300, Itagaki Takahiro
itagaki.takah...@oss.ntt.co.jp escreveu:
Vlad Arkhipov arhi...@dc.baikal.ru wrote:
Is it possible to print which key value is duplicated when 'Duplicate
key value violates unique constraint' occurs? Foreign key violation
error reports such kind of information.
I think it is not difficult from a technical standpoint.
The attached patch adds DETAIL messages to duplicate key value error:
postgres=# INSERT INTO tbl(pk1, pk2) VALUES ('A', 1);
ERROR: duplicate key value violates unique constraint tbl_pkey
DETAIL: Key (pk1,pk2)=(A,1) already exists.
If no objection, I'd like to submit the patch to the next commit-fest
(8.5).
Hi Takahiro, i'm the reviewer of your patch, and the following are my
comments about it:
The patch was applied totalty clean to CVS HEAD and compiled in Ubuntu
8.04, Ubuntu 8.10 and AIX 5.3, but failed in follow tests:
src/test/regress/expected/uuid.out
src/test/regress/expected/constraints.out
src/test/regress/expected/create_index.out
src/test/regress/expected/inherit.out
src/test/regress/expected/transactions.out
src/test/regress/expected/arrays.out
src/test/regress/expected/plpgsql.out
src/test/regress/expected/alter_table.out
src/test/regress/expected/tablespace.out
Would be good to modify the outputs to expect a new DETAIL: line.
Another comment is that the patch isn't in the standart context form, but
unified.
About the feature, it work as expected when I've INSERTed in both single
and compound-key or UPDATEd the key values to violates the constraint,
also in concurrently transactions. As expected too, when i INSERT or
UPDATE the key with a value thath overflow the 512 bytes i'm getting the
output as follow:
---
guedes=# INSERT INTO test_dup_char_key VALUES (repeat('x',1024), 'qq');
ERROR: duplicate key value violates unique constraint
test_dup_char_key_pkey
DETALHE: Key (...)=(...) already exists.
---
I'm thinking if could be better to shows Key (my_key)=(...) instead Key
(...)=(...) -- well, i don't know how much people uses a key with more
512B and how often it is to they don't know wich key it is, (just reading
a log, for example) to we consider this important.
On the other hand there is a comment by Tom [1] about to refactor this so
it's not btree-specific, but could be used by other index AMs, so could
be better trying to think about this in a way to find another alternative,
if it is possible.
[1] http://archives.postgresql.org/pgsql-hackers/2009-04/msg00234.php
Thanks for your patch!
[]s
Dickson S. Guedes
http://pgcon.postgresql.org.br
http://www.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] [PATCH] DefaultACLs
Stephen Frost wrote: I agree that they should be consistant. The GRANT ON ALL shares alot more of the syntax with GRANT than DefaultACL though, which makes it a more interesting question there. I can understand not wanting to duplicate the GRANT syntax. I think my suggestion would be to add a field to the structure passed around by GRANT which indicates if 'VIEW' was requested or not in the command. This could be used both for GRANT ON ALL and to allow 'GRANT ON VIEW blah' to verify that the relation being granted on is a view. I arrived into this conclusion too, but it adds a lot of clutter in gram.y (setting that flag to false or something in many places, just to use in in one place). Originally I thought adding ACL_OBJECT_VIEW wasn't such a bad idea. But after I looked more closely at the code, it it seems to me that having same object type for VIEW and TABLE seems like the only logical reason why GRANT uses separate object type enum at all (instead of using subset of ObjectType like other commands do). If we went this path of separating VIEW and TABLE in GRANT code it might be cleaner to remove GrantObjectType and use ObjectType, but I don't think we want to do that. -- Regards Petr Jelinek (PJMODOS) -- 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] more than one index in a single heap pass?
On Jul 15, 2009, at 2:52 PM, Dimitri Fontaine wrote: Le 15 juil. 09 à 02:01, Glen Parker a écrit : Sounds to me like another reason to separate index definition from creation. If an index can be defined but not yet created or valid, then you could imagine syntax like: DEFINE INDEX blahblah1 ON mytable (some fields); DEFINE INDEX blahblah2 ON mytable (some other fields); [RE]INDEX TABLE mytable; ...provided that REINDEX TABLE could recreate all indexes simultaneously as you suggest. Well to me it sounded much more like: BEGIN; CREATE INDEX idx_a ON t(a) DEFERRED; CREATE INDEX idx_b ON t(b) DEFERRED; COMMIT; And at commit time, PostgreSQL would build all the transaction indexes in one pass over the heap, but as Tom already pointed out, using only 1 CPU. Maybe that'd be a way to limit the overall io bandwidth usage while not consuming too many CPU resources at the same time. I mean now we have a choice to either sync scan the table heap on multiple CPU, saving IO but using 1 CPU per index, or to limit CPU to only 1 but then scan the heap once per index. The intermediary option of using 1 CPU while still making a single heap scan sure can be worthwhile to some? Here's an off-the-wall thought... since most of the CPU time is in the sort, what about allowing a backend to fork off dedicated sort processes? Aside from building multiple indexes at once, that functionality could also be useful in general queries. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] commitfest app
I haven't paid much attention to the new commitfest app until now. Generally, it looks good, but I'm wondering if we should have a committer field. I'm thinking of picking up at least the following items if/when they are ready, but such a thing might help us to make sure we don't trip over each other. DROP COLUMN/CONSTRAINT IF EXISTS support https://commitfest.postgresql.org/action/patch_view?id=89 COPY WITH CSV FORCE QUOTE * https://commitfest.postgresql.org/action/patch_view?id=93 hstore enhancements https://commitfest.postgresql.org/action/patch_view?id=122 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] [pgsql-www] Launching commitfest.postgresql.org
On Thu, 2009-07-16 at 21:31 -0700, Josh Berkus wrote: Robert, BTW, the new commitfest software is great. Easily a 75% reduction in time required to track reviewing activity. I agree. It is much better. I have one suggestion. Make the headings sortable (except maybe for patch name). That way if I want to see what patches Tom is working on, I can easily get a grouping or more importantly patches that nobody is working on. Joshua D. Drake -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- 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] Enhancement - code completion when typing set search_path
On Fri, Jul 17, 2009 at 11:34 AM, Campbell, Lancela...@illinois.edu wrote: I use postgres 8.1.X. Is there a way to add code completion when entering: set search_path = xyz I love the code completion for SQL. It would be really nice to have it for the set search_path. It sounds like a good idea to me; maybe you'd be interested it writing a patch to do it? ...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] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: What about trying to change the TOAST_TUPLE_TARGET to get a higher compression (by having more toasted record) ? I'd like to change the TOAST_TUPLES_PER_PAGE. Maybe from 4 to 8 ? Is that correct ? Did i missed something ? I did some statistics and i will have much more TOASTed record as most of them are between 1KB and 2KB. It seems like it might be reasonable to have a separate threshold for compression from that for out-of-line storage. Since I've been in that code recently, I would be pretty comfortable doing something about that; but, as is so often the case, the problem will probably be getting agreement on what would be a good change. Ignoring for a moment the fact that low hanging fruit in the form of *very* large values can be handled first, the options would seem to be: (1) Just hard-code a lower default threshold for compression than for out-of-line storage. (2) Add a GUC or two to control thresholds. (3) Allow override of the thresholds for individual columns. Are any of these non-controversial? What do people like there? What did I miss? -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] Review: support for multiplexing SIGUSR1
On Fri, Jul 17, 2009 at 8:58 AM, Fujii Masaomasao.fu...@gmail.com wrote: Hi, On Fri, Jul 17, 2009 at 5:41 PM, Fujii Masaomasao.fu...@gmail.com wrote: I'm reviewing this patch: http://archives.postgresql.org/message-id/3f0b79eb0907022341m1d36a841x19c3e2a5a6906...@mail.gmail.com I updated the patch to solve two problems which you pointed. Here is the changes: * Prevented the obsolete flag to being set to a new process, by using newly-introduced spinlock. thinking in ways to test the patch i tried this, the test at least try to see if signals are managed correctly: - patch, compile, install, initdb and start the service - open five terminals: on the first: make installcheck on the second: pg_dumpall -p port_to_an_existing_med_size_test_installation | psql on third:psql -f /home/postgres/a_something_small_database.sql on fourth: explain analyze with q as (select * from generate_series(1, 100) select * from q a, q b, q c, q d, q e, q f; on fifth:select procpid from pg_start_activity; and pg_cancel_backend(randomly_choosen_pid); when cancelling backends i got in a situation where i kill the explain analyze in fourth session, execute again the pg_cancel_backend for the same session and if i try to re-execute the same explain analyze it got cancelled immediatly (seems like something don't get cleaned appropiately). once you get in this situation you can repeat that everytime you want; bad enough, i wasn't able to repeat this on a new instalation and of course i can't swear this is your patch fault... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Higher TOAST compression.
On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote: Laurent Laborde kerdez...@gmail.com wrote: (3) Allow override of the thresholds for individual columns. Are any of these non-controversial? What do people like there? What did I miss? I would skip 1 and 2 and have (3). 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
[HACKERS] Review: Patch for contains/overlap of polygons
Teodor, et al, This is a review of the Polygons patch: http://archives.postgresql.org/message-id/4a5761a2.8070...@sigaev.ru There hasn't been any discussion, at least that I've been able to find. Contents Purpose == This small patch primarily fixes a couple polygon functions, poly_overlap (the operator) and poly_contain (@). Previously the functions only performed simple bounding box calculations or checks based on sets of points. That works only for the simplest of cases; this patch accounts for more complex shapes. Included in the patch are new regression test cases, but no changes to documentation. The patch only corrects the behavior of existing functions, though, so perhaps no changes to the documentation are expected. Initial Run === The patch applies cleanly to HEAD. The regression tests all pass successfully against the new patch, but fail against pre-patched HEAD, so the test cases are sane and do cover the new behavior. As far as I can see the math behind the new calculations seems sound. Performance === Despite the functions in question containing an order of magnitude more code the operators performed faster than the previous versions in my test run. Though I have a feeling that may have more to do with this laptop's processor speed and/or the rather trivial test cases being thrown at it. In any case having the operators work correctly should far outweigh the negligible performance impact. Nitpicking Conclusion === The patch splits out and adds a couple helper functions next to the existing ones in geo_ops.c, but would those be better defined down in the Private routines section? There's a #define in the middle of the touched_lseg_inside_poly() function. The macro is only used in that function and a couple of times at that, but it still feels somewhat out of place. Perhaps that'd be better placed with other #define's near the top? I could certainly be wrong in both cases. :) There's also two int is declared in poly_contain(). Otherwise it seems to do exactly what it promises. I could see the correct behavior of these operators being important for GIS applications. +1 for committer review. - Josh Williams -- 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] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3
--On Donnerstag, Mai 21, 2009 11:46:24 -0700 Steve Prentice prent...@cisco.com wrote: Just for the record, you'd have to put the same kluge into the T_RECORD and T_ROW cases if we wanted to do it like this. Patch updated. Steve, it seems there's something broken, patch complains about a broken format. Can you please provide a new diff file? Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] commitfest application question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA256 Forgive me if I'm missing something obvious, but... I am signed up to review, for example: https://commitfest.postgresql.org/action/patch_view?id=107 If I click on the link for patch, I go to here: http://archives.postgresql.org/message-id/162867790905270504m6bb30e2eqa5021e0d60a43...@mail.gmail.com Is there a way for me to extract the patch as the original attachment, or am I supposed to just cat-n-paste into an editor to create one? Joe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iQIcBAEBCAAGBQJKYMuKAAoJEDfy90M199hlcx4P/3p6yNlob9TNeIq7vuuRb+Gi lqWinHse2MVTm4FiTUENT+DISdsfRkdEisMGJWjVg/cw0iRO4RcFfApWMtjZAbE5 Wlg1D4F5gazMf3w1R6000bDrTOeMddB2uz3TIDlEzcVWH2P3gcFpJF5exOiYKltA Dxwjac4KGteujhKuSDTtenVWADaxJ1yYp/DMCmz58Rm7qIew8b0i1DsgDLgnPi74 wHbu90G14HMmKQIhund16ECGA3iYd+4sS/PYAMHI1v7f/qzw7MBYQKiXawInhHNt zD2I6yD4RjhY4lYmsuVxF6njX3wzFpKosbKXFOHVMTHOASw6T+ijSbUEG6jXbgUz c0mM0iEjgHd9ETEWI6JqLIL/RctSltPRDgYGEFD5dgGIxNt4kQ9ttkpUVpDMuhzs amJ7NYNZHRhZUQYTgDZ3abMq4rGRUfLUgb5aEexMQ0fWMzPFiti4pBwThhM4x00l jZGyAnBCa8KS4oRDUNbX1EDU6bQv78ujG4WcAPxYLEA9OmtnAYdoUTuuP4Bc3IGC ZzE+mohOUYI9HdpKMdXuSR8XdhmxD2HtRt6aBSGNS9hXVCCnVUdx9MYphgU9Ks2D uiuQ5rkoU+gHugvH7buTTmx/+U01vURUfJn8HZXP1nAvHBSrk+znUOCt1w9oPoO0 aM2ADI00eTvRImqH/7Cf =359N -END PGP SIGNATURE- -- 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] more than one index in a single heap pass?
decibel wrote: Here's an off-the-wall thought... since most of the CPU time is in the sort, what about allowing a backend to fork off dedicated sort processes? Aside from building multiple indexes at once, that functionality could also be useful in general queries. Sure, that would be cool. And also a lot of work :-). The comparison operators can be arbitrarily complex, potentially querying other tables etc, so you would indeed need pretty much all the infrastrucutre you need to solve the general case. -- 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] Higher TOAST compression.
Joshua D. Drake j...@commandprompt.com wrote: On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote: (3) Allow override of the thresholds for individual columns. I would skip 1 and 2 and have (3). Sure, pick the one which requires new syntax! ;-) How about two new ALTER TABLE actions: ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer Or can you suggest something better? Laurent, would something like this address your needs? I was assuming that if the point is to reduce I/O, you were interested in doing more compression, not in storing more values out-of-line (in the separate TOAST table)? Would it be manageable to tune this on a column-by- column basis? -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] Higher TOAST compression.
On Fri, 2009-07-17 at 14:21 -0500, Kevin Grittner wrote: Joshua D. Drake j...@commandprompt.com wrote: On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote: (3) Allow override of the thresholds for individual columns. I would skip 1 and 2 and have (3). Sure, pick the one which requires new syntax! ;-) Better than the argument for a new GUC :). How about two new ALTER TABLE actions: ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer Or can you suggest something better? Seems reasonable to me. 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
[HACKERS] Subtransactions and resource owners and such
I've been looking into Frank van Vugt's report here:
http://archives.postgresql.org/pgsql-bugs/2009-07/msg00222.php
The cause of the problem is that while printing the value of the
ROW(NEW.*) expression, we acquire a tupledesc refcount on the
table's rowtype descriptor, and this refcount is assigned to the
CurrentResourceOwner, which at that point is the resowner associated
with the Portal the query is executing in. During the later
subtransaction abort, we try to release the refcount. But at that
point CurrentResourceOwner has been reset to the subtransaction's
resowner, and so we get
ERROR: tupdesc reference 0x7ffe74f24ad0 is not owned by resource owner
SubTransaction
which then results in a lot of WARNING bleats that look scary but
I think are not really problems.
The quick-hack patch I suggested to Frank avoids the issue by not
trying to clean up plpgsql's per-subtransaction ExprContexts during
a subtransaction abort. This is not very satisfactory, though, as
it reintroduces the memory leaks I was trying to solve here:
http://archives.postgresql.org/pgsql-committers/2009-04/msg00127.php
For example, this function
create or replace function test1(int) returns void as
$$
declare
xx text;
yy int;
begin
for i in 1..$1 loop
begin
xx := repeat('x',1000);
yy := i / 0;
exception
when division_by_zero then
null;
end;
end loop;
end$$
language plpgsql;
works fine in 8.4.0 but creates a nasty memory leak with that patch.
(Try running it with a repeat count of a million or so and watch
the backend's memory usage.)
I have thought of a number of possible solutions that might avoid
leakage here:
1. Modify FreeExprContext() so that it's told whether this is a normal
or abort cleanup. In the abort case it skips calling any registered
callbacks, but still releases the memory belonging to the context.
2. Pass a normal/abort flag to FreeExprContext as above, but have it
still call the callbacks and pass the flag on to them. This would
provide an opportunity for callbacks to do something during abort,
if they needed to. However an API change here seems a bit invasive.
I'm not sure if any third-party code uses RegisterExprContextCallback.
I'm also unconvinced that we really need to give the callbacks a
chance to do anything there. We've never called them during regular
transaction abort.
3. When aborting a transaction or subtransaction, arrange to fold
all child resowners into the (sub)transaction's topmost resowner;
that is, reassign all resources they own to that parent. Then,
resource cleanup actions would automatically be applied to the correct
resowner during abort cleanup. This would require a bunch of code
in resowner.c that doesn't currently exist, and I'm also a tad concerned
about the cycles it would take.
I'm currently thinking #1 is the most practical answer, though it might
leave us still with some leakage problems if it turns out there are any
ExprContext callbacks that really need to be called in such cases.
We might want to do #2 in HEAD, but committing it into 8.4.x seems to
risk breaking third-party code. #3 seems like overkill.
Any comments or better ideas?
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] [PATCH] DefaultACLs
On Tue, Jul 14, 2009 at 11:10:00PM +0200, Petr Jelinek wrote: Hello, this is first public version of our DefaultACLs patch as described on http://wiki.postgresql.org/wiki/DefaultACL . I've been asked to review this. I can't get it to build, because of a missing semicolon on line 1608. I fixed it in my version, and it applied cleanly to head (with some offset hunks in gram.y). I've not yet finished building and testing; results to follow later. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Higher TOAST compression.
On Fri, Jul 17, 2009 at 9:21 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Joshua D. Drake j...@commandprompt.com wrote: On Fri, 2009-07-17 at 12:50 -0500, Kevin Grittner wrote: (3) Allow override of the thresholds for individual columns. I would skip 1 and 2 and have (3). Sure, pick the one which requires new syntax! ;-) How about two new ALTER TABLE actions: ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer Or can you suggest something better? Laurent, would something like this address your needs? I was assuming that if the point is to reduce I/O, you were interested in doing more compression, not in storing more values out-of-line (in the separate TOAST table)? Would it be manageable to tune this on a column-by- column basis? Certainly ! We already alter storage type on some column, adding some more won't be a problem. :) But... on which version are you planning to do that ? We're still using Postgresql 8.3, because we use Slony-1 1.2.15 and upgrading to 8.4 is a *major* pain (discussed on slony mailling list). Slony-1 1.2.15 won't compile on 8.4, and upgrading to Slony-1 2.0.x require to rebuild the whole cluster (and upgrading to Pg 8.4 require a rebuild too). So we'd need to upgrade both slony and postgresql with an impossible downtime :) We stay on Pg 8.3 until the slony developpers find a better upgrade solution. The proposed solution sound really good to me. But, for now, if i could have a simple patch for 8.3 (eg: changing a #define in the source code), i'd be very happy :) Is it ok to just change TOAST_TUPLES_PER_PAGE ? Thank you for all your replies and proposed solutions :) PS : i'm not a C coder, but if you know some perl to be patched/cleaned, i'm here :) -- Laurent Laborde Sysadmin at http://www.over-blog.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] PL/Python errcontext support
During the review of the patch about improved PL/Python data type support, I
figured this could be somewhat simplified if PL/Python used the errcontext()
mechanisms instead of passing the function name around everywhere in order to
use it in error messages.
I have produced the attached patch. The errcontext stuff is a bit obscure to
me though, so perhaps someone could look it over.
diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c
index 50b48ac..b8e2816 100644
--- a/src/pl/plpython/plpython.c
+++ b/src/pl/plpython/plpython.c
@@ -332,18 +332,33 @@ perm_fmgr_info(Oid functionId, FmgrInfo *finfo)
fmgr_info_cxt(functionId, finfo, TopMemoryContext);
}
+static void
+plpython_error_callback(void *arg)
+{
+ if (PLy_curr_procedure)
+ errcontext(PL/Python function \%s\, PLy_procedure_name(PLy_curr_procedure));
+}
+
Datum
plpython_call_handler(PG_FUNCTION_ARGS)
{
Datum retval;
PLyProcedure *save_curr_proc;
PLyProcedure *volatile proc = NULL;
+ ErrorContextCallback plerrcontext;
if (SPI_connect() != SPI_OK_CONNECT)
elog(ERROR, SPI_connect failed);
save_curr_proc = PLy_curr_procedure;
+ /*
+ * Setup error traceback support for ereport()
+ */
+plerrcontext.callback = plpython_error_callback;
+plerrcontext.previous = error_context_stack;
+error_context_stack = plerrcontext;
+
PG_TRY();
{
if (CALLED_AS_TRIGGER(fcinfo))
@@ -377,6 +392,9 @@ plpython_call_handler(PG_FUNCTION_ARGS)
}
PG_END_TRY();
+ /* Pop the error context stack */
+error_context_stack = plerrcontext.previous;
+
PLy_curr_procedure = save_curr_proc;
Py_DECREF(proc-me);
@@ -2460,9 +2478,7 @@ PLy_spi_prepare(PyObject *self, PyObject *args)
if (!PyErr_Occurred())
PLy_exception_set(PLy_exc_spi_error,
unrecognized error in PLy_spi_prepare);
- /* XXX this oughta be replaced with errcontext mechanism */
- PLy_elog(WARNING, in PL/Python function \%s\,
- PLy_procedure_name(PLy_curr_procedure));
+ PLy_elog(WARNING, NULL);
return NULL;
}
PG_END_TRY();
@@ -2624,9 +2640,7 @@ PLy_spi_execute_plan(PyObject *ob, PyObject *list, long limit)
if (!PyErr_Occurred())
PLy_exception_set(PLy_exc_error,
unrecognized error in PLy_spi_execute_plan);
- /* XXX this oughta be replaced with errcontext mechanism */
- PLy_elog(WARNING, in PL/Python function \%s\,
- PLy_procedure_name(PLy_curr_procedure));
+ PLy_elog(WARNING, NULL);
return NULL;
}
PG_END_TRY();
@@ -2671,9 +2685,7 @@ PLy_spi_execute_query(char *query, long limit)
if (!PyErr_Occurred())
PLy_exception_set(PLy_exc_spi_error,
unrecognized error in PLy_spi_execute_query);
- /* XXX this oughta be replaced with errcontext mechanism */
- PLy_elog(WARNING, in PL/Python function \%s\,
- PLy_procedure_name(PLy_curr_procedure));
+ PLy_elog(WARNING, NULL);
return NULL;
}
PG_END_TRY();
@@ -2987,9 +2999,11 @@ PLy_exception_set_plural(PyObject *exc,
PyErr_SetString(exc, buf);
}
-/* Emit a PG error or notice, together with any available info about the
- * current Python error. This should be used to propagate Python errors
- * into PG.
+/* Emit a PG error or notice, together with any available info about
+ * the current Python error, previously set with PLy_exception_set().
+ * This should be used to propagate Python errors into PG. If fmt is
+ * NULL, the Python error becomes the primary error message, otherwise
+ * it becomes the detail.
*/
static void
PLy_elog(int elevel, const char *fmt,...)
@@ -3000,6 +3014,8 @@ PLy_elog(int elevel, const char *fmt,...)
xmsg = PLy_traceback(xlevel);
+ if (fmt)
+ {
initStringInfo(emsg);
for (;;)
{
@@ -3013,15 +3029,21 @@ PLy_elog(int elevel, const char *fmt,...)
break;
enlargeStringInfo(emsg, emsg.maxlen);
}
+ }
PG_TRY();
{
+ if (fmt)
ereport(elevel,
(errmsg(PL/Python: %s, emsg.data),
(xmsg) ? errdetail(%s, xmsg) : 0));
+ else
+ ereport(elevel,
+ (errmsg(PL/Python: %s, xmsg)));
}
PG_CATCH();
{
+ if (fmt)
pfree(emsg.data);
if (xmsg)
pfree(xmsg);
@@ -3029,6 +3051,7 @@ PLy_elog(int elevel, const char *fmt,...)
}
PG_END_TRY();
+ if (fmt)
pfree(emsg.data);
if (xmsg)
pfree(xmsg);
--
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: support for multiplexing SIGUSR1
On Fri, Jul 17, 2009 at 1:44 PM, Jaime Casanovajcasa...@systemguards.com.ec wrote: i wasn't able to repeat this on a new instalation and of course i can't swear this is your patch fault... this is not your patch fault but an existing bug, i repeat that behaviour in an unpatched source tree... with the steps in the previous mail i canceled 2 or 3 backend before cancell the one with the explain analyze, i execute a second time that pg_cancel_backend and if i try to re-run the query it gets cancelled immediately, next time it runs normally... pg_stat_activity reports that query as running no mather what... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] WIP patch for TODO Item: Add prompt escape to display the client and server versions
Em Thu, 16 Jul 2009 17:40:45 -0300, Peter Eisentraut pete...@gmx.net escreveu: On Thursday 07 May 2009 05:23:41 Dickson S. Guedes wrote: This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. More generally, does anyone actually need this feature? psql complains loudly enough if the version numbers are not the right ones. I don't know why this would need to be repeated in the prompt. An use case that i can figure out is an user that connects in multiples instances in a lot of remote sites (like home-officer for example) and needs this information in the prompt to don't lost the context of your work. Is this valid? Is this and other similar cases quite enough to justify this patch? If yes I can change the patch to satisfy the Peter's suggestions, if no we can just ignore the patch and remove the item from TODO. Thoughts? []s -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://www.postgresql.org.br 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
[HACKERS] MIN/MAX optimization for partitioned table
Consider the following schema: create table foo_archive (a int, b timestamp); create index foo_archive_idx on foo_archive(b); CREATE TABLE foo_archive_2007_01_01 (CONSTRAINT foo_archive_2007_01_01_b_check CHECK (((b = '2007-01-01'::date) AND (b '2007-01-02'::date INHERITS (foo_archive); CREATE INDEX foo_archive_2007_01_01_idx ON foo_archive_2007_01_01 USING btree (b); CREATE TABLE foo_archive_2007_01_02 (CONSTRAINT foo_archive_2007_01_02_b_check CHECK (((b = '2007-01-02'::date) AND (b '2007-01-03'::date INHERITS (foo_archive); CREATE INDEX foo_archive_2007_01_02_idx ON foo_archive_2007_01_02 USING btree (b); ... Currently the optimizer yields the following plan: postgres=# explain select max(b) from foo_archive; QUERY PLAN - Aggregate (cost=18602.00..18602.01 rows=1 width=8) - Append (cost=0.00..16005.00 rows=1038800 width=8) - Seq Scan on foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_01 foo_archive (cost=0.00..1331.99 rows=86399 width=8) - Seq Scan on foo_archive_2007_01_02 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_03 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_04 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_05 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_06 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_07 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_08 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_09 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_10 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_11 foo_archive (cost=0.00..1332.00 rows=86400 width=8) - Seq Scan on foo_archive_2007_01_12 foo_archive (cost=0.00..765.01 rows=49601 width=8) - Seq Scan on foo_archive_2007_01_13 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_14 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_15 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_16 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_17 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_18 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_19 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_20 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_21 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_22 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_23 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_24 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_25 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_26 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_27 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_28 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_29 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_30 foo_archive (cost=0.00..29.40 rows=1940 width=8) - Seq Scan on foo_archive_2007_01_31 foo_archive (cost=0.00..29.40 rows=1940 width=8) (34 rows) As we can see, the optimizer does not take advantage of the indexes on column b in the children relations. Attached is a patch that will take advantage of the indexes (when they're available and if the index path is cheaper) and yield the following plan. postgres=# explain select max(b) from foo_archive; QUERY PLAN --- Aggregate (cost=1.54..1.55 rows=1 width=0) InitPlan 1 (returns $0) - Limit (cost=0.00..0.04 rows=1 width=8) - Index Scan Backward using foo_archive_idx on foo_archive (cost=0.00..73.35 rows=1940 width=8) Filter: (b IS NOT NULL) InitPlan 2 (returns $1)
Re: [HACKERS] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: Kevin Grittnerkevin.gritt...@wicourts.gov wrote: How about two new ALTER TABLE actions: ALTER [ COLUMN ] column SET COMPRESSION_THRESHOLD integer ALTER [ COLUMN ] column SET EXTERNAL_THRESHOLD integer Laurent, would something like this address your needs? Certainly ! We already alter storage type on some column, adding some more won't be a problem. :) But... on which version are you planning to do that ? The patch, if there's consensus that it's a good idea, would be for 8.5. Since it is new functionality, there wouldn't be a back-port to prior releases. Of course, I wouldn't be starting to work on such a patch until after our current code commit phase, which ends August 15th. We stay on Pg 8.3 until the slony developpers find a better upgrade solution. The proposed solution sound really good to me. But, for now, if i could have a simple patch for 8.3 (eg: changing a #define in the source code), i'd be very happy :) Is it ok to just change TOAST_TUPLES_PER_PAGE ? The thing that worries me about that is that it would tend to force more data to be stored out-of-line, which might *increase* your I/O; since the whole point of this exercise is to try to *decrease* it, that seems pretty iffy. However, once we get to the end of code commit, I might be able to give you a little one-off patch that would be more aggressive about compression without affecting out-of-line storage. Hard-coded, like what you're talking about, but with a little more finesse. -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] commitfest application question
--On Freitag, Juli 17, 2009 12:05:46 -0700 Joe Conway m...@joeconway.com wrote: Is there a way for me to extract the patch as the original attachment, or am I supposed to just cat-n-paste into an editor to create one? This has bitten me one or two times in the past, too. Fortunately i archive all emails on local storage, but it would be nice if attached plain text diffs wouldn't get inlined in the archives. Another possible solution is to use the mbox files. -- Thanks Bernd -- 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] Higher TOAST compression.
On Fri, Jul 17, 2009 at 10:40 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Laurent Laborde kerdez...@gmail.com wrote: But... on which version are you planning to do that ? The patch, if there's consensus that it's a good idea, would be for 8.5. Since it is new functionality, there wouldn't be a back-port to prior releases. Of course, I wouldn't be starting to work on such a patch until after our current code commit phase, which ends August 15th. Sure, no problem. We stay on Pg 8.3 until the slony developpers find a better upgrade solution. The proposed solution sound really good to me. But, for now, if i could have a simple patch for 8.3 (eg: changing a #define in the source code), i'd be very happy :) Is it ok to just change TOAST_TUPLES_PER_PAGE ? The thing that worries me about that is that it would tend to force more data to be stored out-of-line, which might *increase* your I/O; since the whole point of this exercise is to try to *decrease* it, that seems pretty iffy. However, once we get to the end of code commit, I might be able to give you a little one-off patch that would be more aggressive about compression without affecting out-of-line storage. Hard-coded, like what you're talking about, but with a little more finesse. Awesome ! Yes, i understand the problem. What about SET STORAGE MAIN then ? To prevent out-of-line storage ? We use PLAIN on some specific column (i don't know why, it was here before i join overblog) And the default extended storage for all other columns. Thank you :) -- Laurent Laborde Sysadmin @ http://www.over-blog.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] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: What about SET STORAGE MAIN then ? To prevent out-of-line storage ? Well, that doesn't try as hard as you might think to keep from storing data out-of-line. It uses the same threshold as the default EXTENDED storage, but saves the out-of-line option for such columns as the last thing to try to get it within the threshold. It is because I wrote a very small patch to address that issue that I jumped in on your issue. If you wanted to try my patch here: http://archives.postgresql.org/message-id/4a363853022500027...@gw.wicourts.gov you could adjust both TOAST_TUPLES_PER_PAGE_MAIN and TOAST_TUPLES_PER_PAGE to suit your needs and set storage for columns to MAIN as needed. Be very cautious if you try this, because this patch has not yet been reviewed or accepted. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql - small fix in \du
Hi, attached you can find a very small patch for the help in psql (\?). It's possible to use \du also as \du+ . The [+] was missing in help. I was asking about this at the general list and Peter E. was asking me to provide a patch. I sent the patch there but realized now, that this was the wrong place to do so. Sorry for the flood ... Cheers Andy diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index 5f13b8a..8a541e6 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -219,7 +219,7 @@ slashUsage(unsigned short int pager) fprintf(output, _( \\ds[S+] [PATTERN] list sequences\n)); fprintf(output, _( \\dt[S+] [PATTERN] list tables\n)); fprintf(output, _( \\dT[S+] [PATTERN] list data types\n)); - fprintf(output, _( \\du [PATTERN] list roles (users)\n)); + fprintf(output, _( \\du[+] [PATTERN] list roles (users)\n)); fprintf(output, _( \\dv[S+] [PATTERN] list views\n)); fprintf(output, _( \\l[+] list all databases\n)); fprintf(output, _( \\z [PATTERN] same as \\dp\n)); -- 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] WIP patch for TODO Item: Add prompt escape to display the client and server versions
Dickson S. Guedes lis...@guedesoft.net writes: Em Thu, 16 Jul 2009 17:40:45 -0300, Peter Eisentraut pete...@gmx.net escreveu: More generally, does anyone actually need this feature? psql complains loudly enough if the version numbers are not the right ones. I don't know why this would need to be repeated in the prompt. An use case that i can figure out is an user that connects in multiples instances in a lot of remote sites (like home-officer for example) and needs this information in the prompt to don't lost the context of your work. Is this valid? It seems unlikely that the DB version number would be worth the prompt space. In situations like that you'd much more likely need identifying info like the DB hostname and port number. 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] Non-blocking communication between a frontend and a backend (pqcomm)
Fujii Masao wrote: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00191.php In line with Robert's suggestion, I submit non-blocking pqcomm patch as a self-contained one. Here's my initial review of the non-blocking pqcomm patch. The patch applies cleanly and passes regression. Generally looks nice and clean. Couple of remarks from the department of nitpicking: * In secure_poll() the handling of timeouts is different depending whether poll(), select() or SSL_pending() is used. The latter doesn't use the timeout value at all, and for select() it is impossible to specify indefinite timeout. * occasional blank lines consisting of a single tab character -- maybe a left-over from editor auto-indent. Not sure of how much a problem this is, given that the blanks will be removed by pg_indent. * Comment on pq_wait() seems to have a typo: -1 if an error directly. I have done limited testing on Linux i686 (HAVE_POLL only) -- the non-blocking functions behave as expected. regards, Martin -- 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] MIN/MAX optimization for partitioned table
Neat! I haven't read the patch yet but I have some questions. Does this handle the case where some partitions have an index and others don't? Ie. Does it just apply the regular optimization to each partition and then slap on the aggregate node? I think that's actually a realistic case because people often don't have indexes on empty partitions like the parent partition or a new partition which has just been added and doesn't have indexes yet. Is there any overlap with the ordered-append patch which is also in the pipeline? afaict it covers similar cases but doesn't actually overlap since the min/max optimization avoids having to do a sort anywhere. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Enhancement - code completion when typing set search_path
On Fri, Jul 17, 2009 at 10:34:34AM -0500, Campbell, Lance wrote: I use postgres 8.1.X. Is there a way to add code completion when entering: set search_path = xyz I love the code completion for SQL. It would be really nice to have it for the set search_path. Thanks, You'll want to patch 8.5-to-be's psql, as we do not add new features into back-patches. Fortunately, 8.4 and up have backward-compatible psqls, so much of the infrastructure is already there :) 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] MIN/MAX optimization for partitioned table
On Fri, Jul 17, 2009 at 2:45 PM, Greg Stark gsst...@mit.edu wrote: Neat! I haven't read the patch yet but I have some questions. Does this handle the case where some partitions have an index and others don't? Ie. Does it just apply the regular optimization to each partition and then slap on the aggregate node? I think that's actually a realistic case because people often don't have indexes on empty partitions like the parent partition or a new partition which has just been added and doesn't have indexes yet. Is there any overlap with the ordered-append patch which is also in the pipeline? afaict it covers similar cases but doesn't actually overlap since the min/max optimization avoids having to do a sort anywhere. -- greg http://mit.edu/~gsstark/resume.pdf http://mit.edu/%7Egsstark/resume.pdf Hi Greg, My colleague, Jeff Davis, just pointed me to the work that you're doing with MergeAppend. I didn't know about it. Yes, it does handle the case where no index exists in the child partition. It defaults to the Seqscan plan for that particular partition because it still depends on the aggregate node on top of the append node. I haven't looked at your MergeAppend patch so I don't know how much overlap there is. Based on my limited understanding of it, I think it may be two different approaches to optimizing the same problem with yours being a more general solution that solves a wider set of optimizations for partitioned tables while I'm trying to solve a very specific problem. You are also correct that my patch will not have to sort on partitions without the appropriate index, so the plan it generates should be cheaper. Any more thoughts about my patch or ways of making the two patches work together would be greatly appreciated. Thanks, Alan
[HACKERS] make check failure for 8.4.0
I took the 8.4.0 release tarball and tried to build it on one of our production servers which is currently running 8.3.7. We routinely build multiple versions of PostgreSQL on a machine, using --prefix to place them. Something seems broken for 8.4.0. Not sure how best to proceed. I ran: -- tar -xjf postgresql-8.4.0.tar.bz2 cd postgresql-8.4.0/ ./configure --prefix=/usr/local/pgsql-8.4.0 --enable-integer-datetimes --enable-debug --disable-nls --with-libxml make make check --- and had these failures: -- test create_function_1... FAILED triggers ... FAILED -- Files attached. It appears that some phase of the make or make check picked up production libraries instead of libraries from the build. Hmmm I tried on another machine with very similar configuration and it worked. Both machines report (substituting hostname for the actual name): SUSE Linux Enterprise Server 10 (x86_64) VERSION = 10 PATCHLEVEL = 2 Linux hostname 2.6.16.60-0.39.3-smp #1 SMP Mon May 11 11:46:34 UTC 2009 x86_64 x86_64 x86_64 GNU/Linux What should I check? -Kevin regression.out Description: Binary data regression.diffs Description: Binary data -- 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 check failure for 8.4.0
Kevin Grittner wrote: I took the 8.4.0 release tarball and tried to build it on one of our production servers which is currently running 8.3.7. We routinely build multiple versions of PostgreSQL on a machine, using --prefix to place them. Something seems broken for 8.4.0. Not sure how best to proceed. I ran: -- tar -xjf postgresql-8.4.0.tar.bz2 cd postgresql-8.4.0/ ./configure --prefix=/usr/local/pgsql-8.4.0 --enable-integer-datetimes --enable-debug --disable-nls --with-libxml make make check --- and had these failures: -- test create_function_1... FAILED triggers ... FAILED -- Files attached. It appears that some phase of the make or make check picked up production libraries instead of libraries from the build. Hmmm I tried on another machine with very similar configuration and it worked. Both machines report (substituting hostname for the actual name): SUSE Linux Enterprise Server 10 (x86_64) VERSION = 10 PATCHLEVEL = 2 Linux hostname 2.6.16.60-0.39.3-smp #1 SMP Mon May 11 11:46:34 UTC 2009 x86_64 x86_64 x86_64 GNU/Linux What should I check? Can we look at the make log? 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] [PATCH] 8.5 plpgsql change for named notation: treat word following AS keyword as label v3
On Jul 17, 2009, at 11:56 AM, Bernd Helmle wrote: it seems there's something broken, patch complains about a broken format. Can you please provide a new diff file? Sorry about that--probably got messed up as I pasted it into the message. I've attached the patch this time. plpgsql_keyword_as.patch Description: Binary data -- 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 check failure for 8.4.0
Kevin Grittner kevin.gritt...@wicourts.gov writes: ERROR: incompatible library /home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so: version mismatch DETAIL: Server is version 8.4, library is version 8.3. That's just bizarre. Could you try strace'ing the backend while doing that CREATE FUNCTION command (or it should be enough to just try to LOAD that file by name)? That should provide some evidence about what's happening. It seems like the dynamic linker must be ignoring the file it's told to load and loading something else instead, but that's weird enough that I want strace proof of 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
[HACKERS] pg_migrator 8.4.1 alpha 1 released with bug mention
To more clearly identify that pg_migrator now has known bugs, I have released pg_migrator 8.4.1 alpha1, and mentioned in the README that there are known bugs related to migrating sequences and large objects. I have removed the 8.4 source file from pgfoundry. --- Alvaro Herrera wrote: Jamie Fox wrote: Hi - REINDEX INDEX pg_largeobject_loid_pn_index; This seems to have fixed the problem, lo_open of lob data is working again - now to see how vacuumlo likes it. So did it work? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Sort commitfest tables
Folks,
I was just looking over the commitfest, and wanted to be able to sort
tables. Fortunately, Somebody Else(TM) has already written and tested
the code.
Please find enclosed a patch to do same.
It's untested because I couldn't quite figure out what to install and
configure in order to test it. Any hints?
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
diff --git a/html/layout/js/sorttable.js b/html/layout/js/sorttable.js
new file mode 100644
index 000..25bccb2
--- /dev/null
+++ b/html/layout/js/sorttable.js
@@ -0,0 +1,493 @@
+/*
+ SortTable
+ version 2
+ 7th April 2007
+ Stuart Langridge, http://www.kryogenix.org/code/browser/sorttable/
+
+ Instructions:
+ Download this file
+ Add script src=sorttable.js/script to your HTML
+ Add class=sortable to any table you'd like to make sortable
+ Click on the headers to sort
+
+ Thanks to many, many people for contributions and suggestions.
+ Licenced as X11: http://www.kryogenix.org/code/browser/licence.html
+ This basically means: do what you want with it.
+*/
+
+
+var stIsIE = /*...@cc_on!@*/false;
+
+sorttable = {
+ init: function() {
+// quit if this function has already been called
+if (arguments.callee.done) return;
+// flag this function so we don't do the same thing twice
+arguments.callee.done = true;
+// kill the timer
+if (_timer) clearInterval(_timer);
+
+if (!document.createElement || !document.getElementsByTagName) return;
+
+sorttable.DATE_RE = /^(\d\d?)[\/\.-](\d\d?)[\/\.-]((\d\d)?\d\d)$/;
+
+forEach(document.getElementsByTagName('table'), function(table) {
+ if (table.className.search(/\bsortable\b/) != -1) {
+sorttable.makeSortable(table);
+ }
+});
+
+ },
+
+ makeSortable: function(table) {
+if (table.getElementsByTagName('thead').length == 0) {
+ // table doesn't have a tHead. Since it should have, create one and
+ // put the first table row in it.
+ the = document.createElement('thead');
+ the.appendChild(table.rows[0]);
+ table.insertBefore(the,table.firstChild);
+}
+// Safari doesn't support table.tHead, sigh
+if (table.tHead == null) table.tHead =
table.getElementsByTagName('thead')[0];
+
+if (table.tHead.rows.length != 1) return; // can't cope with two header
rows
+
+// Sorttable v1 put rows with a class of sortbottom at the bottom (as
+// total rows, for example). This is BR, since what you're supposed
+// to do is put them in a tfoot. So, if there are sortbottom rows,
+// for backwards compatibility, move them to tfoot (creating it if needed).
+sortbottomrows = [];
+for (var i=0; itable.rows.length; i++) {
+ if (table.rows[i].className.search(/\bsortbottom\b/) != -1) {
+sortbottomrows[sortbottomrows.length] = table.rows[i];
+ }
+}
+if (sortbottomrows) {
+ if (table.tFoot == null) {
+// table doesn't have a tfoot. Create one.
+tfo = document.createElement('tfoot');
+table.appendChild(tfo);
+ }
+ for (var i=0; isortbottomrows.length; i++) {
+tfo.appendChild(sortbottomrows[i]);
+ }
+ delete sortbottomrows;
+}
+
+// work through each column and calculate its type
+headrow = table.tHead.rows[0].cells;
+for (var i=0; iheadrow.length; i++) {
+ // manually override the type with a sorttable_type attribute
+ if (!headrow[i].className.match(/\bsorttable_nosort\b/)) { // skip this
col
+mtch = headrow[i].className.match(/\bsorttable_([a-z0-9]+)\b/);
+if (mtch) { override = mtch[1]; }
+ if (mtch typeof sorttable[sort_+override] == 'function') {
+ headrow[i].sorttable_sortfunction = sorttable[sort_+override];
+ } else {
+ headrow[i].sorttable_sortfunction =
sorttable.guessType(table,i);
+ }
+ // make it clickable to sort
+ headrow[i].sorttable_columnindex = i;
+ headrow[i].sorttable_tbody = table.tBodies[0];
+ dean_addEvent(headrow[i],click, function(e) {
+
+ if (this.className.search(/\bsorttable_sorted\b/) != -1) {
+// if we're already sorted by this column, just
+// reverse the table, which is quicker
+sorttable.reverse(this.sorttable_tbody);
+this.className = this.className.replace('sorttable_sorted',
+
'sorttable_sorted_reverse');
+this.removeChild(document.getElementById('sorttable_sortfwdind'));
+sortrevind = document.createElement('span');
+sortrevind.id = sorttable_sortrevind;
+sortrevind.innerHTML = stIsIE ? 'nbspfont
face=webdings5/font' :
[HACKERS] 'Could not attach to shared memory' bug in Windows
I've just had a case of the 'could not reattach to shared memory' problem, and I thought I'd pass on my findings in case it helps. I found that it was trying to access shared memory at 0x161 I used Process Explorer to have a look at the DLLs used by existing copies of Postgres to see if I could get any clues from those I found that one DLL (SystemState.dll FWIW - part of PicoBackup) was loading at different base addresses - eg 0x155 or 0x15B in the different instances of Postgres. This was because whoever had compiled that DLL had set the image base to 0x40 which is guaranteed to clash, so Windows was having to relocate it every time. The relocated base address was NOT identical in the different instances of Postgres - don't ask me why. I guess at some point it was being relocated to 0x15F or similar, so it would extend into 0x161, but those instances of postgres were dying so I couldn't see those. (I have some screenshots if anyone wants them) Notably, things like LIBEAY32.DLL which are used by Postgres are also relocated to different addresses (all the OpenSLL DLLs seem to be compiled with base addresses at 0x1000 or 0x1C00) - eg I've looked here, and in one instance LIBEAY32.DLL is at 0xBE, in another instance it's at 0x23 I'm not really sure what Postgres can do about this, other than to allow a moveable shared memory location - but I've seen from previous messages that that's basically not going to be possible - but that's the 'proper' solution. If Postgres controls the loading of the OpenSSL DLLs (using LoadModule rather than 'static' dynamic linking) then it can load the OpenSSL DLLs after getting the right shared memory area, but that won't solve the problem for DLLs which inject themselves into the application. Maybe it could allocate a large temporary chunk of shared memory and then allocate the real chunk above that, then free the temporary chunk. That would hopefully put the real shared memory well above the space where Windows will put relocated DLLs - Windows seems to store relocated DLLs at the bottom of memory, working upwards. -- Paul Smith VPOP3 - POP3/SMTP/IMAP4/Webmail Email server for Windows -- 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] [pgsql-www] Launching commitfest.postgresql.org
On Fri, Jul 17, 2009 at 10:36:14AM -0700, Joshua D. Drake wrote:
On Thu, 2009-07-16 at 21:31 -0700, Josh Berkus wrote:
Robert,
BTW, the new commitfest software is great. Easily a 75% reduction in
time required to track reviewing activity.
I agree. It is much better. I have one suggestion. Make the headings
sortable (except maybe for patch name).
Here's a patch for that :) Untested, because I couldn't figure out
how to set up a commitfest on my machine :(
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
diff --git a/html/layout/js/sorttable.js b/html/layout/js/sorttable.js
new file mode 100644
index 000..25bccb2
--- /dev/null
+++ b/html/layout/js/sorttable.js
@@ -0,0 +1,493 @@
+/*
+ SortTable
+ version 2
+ 7th April 2007
+ Stuart Langridge, http://www.kryogenix.org/code/browser/sorttable/
+
+ Instructions:
+ Download this file
+ Add script src=sorttable.js/script to your HTML
+ Add class=sortable to any table you'd like to make sortable
+ Click on the headers to sort
+
+ Thanks to many, many people for contributions and suggestions.
+ Licenced as X11: http://www.kryogenix.org/code/browser/licence.html
+ This basically means: do what you want with it.
+*/
+
+
+var stIsIE = /*...@cc_on!@*/false;
+
+sorttable = {
+ init: function() {
+// quit if this function has already been called
+if (arguments.callee.done) return;
+// flag this function so we don't do the same thing twice
+arguments.callee.done = true;
+// kill the timer
+if (_timer) clearInterval(_timer);
+
+if (!document.createElement || !document.getElementsByTagName) return;
+
+sorttable.DATE_RE = /^(\d\d?)[\/\.-](\d\d?)[\/\.-]((\d\d)?\d\d)$/;
+
+forEach(document.getElementsByTagName('table'), function(table) {
+ if (table.className.search(/\bsortable\b/) != -1) {
+sorttable.makeSortable(table);
+ }
+});
+
+ },
+
+ makeSortable: function(table) {
+if (table.getElementsByTagName('thead').length == 0) {
+ // table doesn't have a tHead. Since it should have, create one and
+ // put the first table row in it.
+ the = document.createElement('thead');
+ the.appendChild(table.rows[0]);
+ table.insertBefore(the,table.firstChild);
+}
+// Safari doesn't support table.tHead, sigh
+if (table.tHead == null) table.tHead =
table.getElementsByTagName('thead')[0];
+
+if (table.tHead.rows.length != 1) return; // can't cope with two header
rows
+
+// Sorttable v1 put rows with a class of sortbottom at the bottom (as
+// total rows, for example). This is BR, since what you're supposed
+// to do is put them in a tfoot. So, if there are sortbottom rows,
+// for backwards compatibility, move them to tfoot (creating it if needed).
+sortbottomrows = [];
+for (var i=0; itable.rows.length; i++) {
+ if (table.rows[i].className.search(/\bsortbottom\b/) != -1) {
+sortbottomrows[sortbottomrows.length] = table.rows[i];
+ }
+}
+if (sortbottomrows) {
+ if (table.tFoot == null) {
+// table doesn't have a tfoot. Create one.
+tfo = document.createElement('tfoot');
+table.appendChild(tfo);
+ }
+ for (var i=0; isortbottomrows.length; i++) {
+tfo.appendChild(sortbottomrows[i]);
+ }
+ delete sortbottomrows;
+}
+
+// work through each column and calculate its type
+headrow = table.tHead.rows[0].cells;
+for (var i=0; iheadrow.length; i++) {
+ // manually override the type with a sorttable_type attribute
+ if (!headrow[i].className.match(/\bsorttable_nosort\b/)) { // skip this
col
+mtch = headrow[i].className.match(/\bsorttable_([a-z0-9]+)\b/);
+if (mtch) { override = mtch[1]; }
+ if (mtch typeof sorttable[sort_+override] == 'function') {
+ headrow[i].sorttable_sortfunction = sorttable[sort_+override];
+ } else {
+ headrow[i].sorttable_sortfunction =
sorttable.guessType(table,i);
+ }
+ // make it clickable to sort
+ headrow[i].sorttable_columnindex = i;
+ headrow[i].sorttable_tbody = table.tBodies[0];
+ dean_addEvent(headrow[i],click, function(e) {
+
+ if (this.className.search(/\bsorttable_sorted\b/) != -1) {
+// if we're already sorted by this column, just
+// reverse the table, which is quicker
+sorttable.reverse(this.sorttable_tbody);
+this.className = this.className.replace('sorttable_sorted',
+
'sorttable_sorted_reverse');
+this.removeChild(document.getElementById('sorttable_sortfwdind'));
+sortrevind =
Re: [HACKERS] make check failure for 8.4.0
On Sat, Jul 18, 2009 at 12:30 AM, Tom Lanet...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: ERROR: incompatible library /home/kgrittn/postgresql-8.4.0/src/test/regress/refint.so: version mismatch DETAIL: Server is version 8.4, library is version 8.3. That's just bizarre. Could you try strace'ing the backend while doing that CREATE FUNCTION command (or it should be enough to just try to LOAD that file by name)? That should provide some evidence about what's happening. It seems like the dynamic linker must be ignoring the file it's told to load and loading something else instead, but that's weird enough that I want strace proof of it... Really? That's not how I read it. I read it as the build process in the contrib directory built these modules using the pgxs configuration from his 8.3 install. -- greg http://mit.edu/~gsstark/resume.pdf -- 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 check failure for 8.4.0
Greg Stark gsst...@mit.edu writes: Really? That's not how I read it. I read it as the build process in the contrib directory built these modules using the pgxs configuration from his 8.3 install. Hm, maybe, but it's not supposed to do that (and I would think we'd have noticed such a problem before --- surely most hackers have pre-existing installations of PG in their PATH when they build test versions). Anyway I'd like to have some hard data before speculating too much. 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] make check failure for 8.4.0
Tom Lane wrote: Greg Stark gsst...@mit.edu writes: Really? That's not how I read it. I read it as the build process in the contrib directory built these modules using the pgxs configuration from his 8.3 install. Hm, maybe, but it's not supposed to do that (and I would think we'd have noticed such a problem before --- surely most hackers have pre-existing installations of PG in their PATH when they build test versions). Anyway I'd like to have some hard data before speculating too much. That's why I asked to see the make log. Maybe some environment setting affected things? 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] [PATCH] DefaultACLs
On Tue, Jul 14, 2009 at 11:10:00PM +0200, Petr Jelinek wrote:
Hello,
this is first public version of our DefaultACLs patch as described on
http://wiki.postgresql.org/wiki/DefaultACL .
Ok, here's my first crack at a comprehensive review. There's more I need to
look at, eventually. Some of these are very minor stylistic comments, and some
are probably just because I've much less of a clue, in general, than I'd like
to think I have.
First, as you've already pointed out, this needs documentation.
Once I added the missing semicolon mentioned earlier, it applies and builds
fine. The regression tests, however, seem to assume that they'll be run as the
postgres user, and the privileges test failed. Here's part of a diff between
expected/privileges.out and results/privileges.out as an example of what I
mean:
ALTER SCHEMA regressns DROP DEFAULT PRIVILEGES ON TABLE ALL FROM
regressuser2;
***
*** 895,903
(1 row)
SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
! relname |relacl
! --+--
! acltest2 | {postgres=arwdDxt/postgres,regressgroup1=r/postgres}
(1 row)
CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;
--- 895,903
(1 row)
SELECT relname, relacl FROM pg_class WHERE relname = 'acltest2';
! relname | relacl
! --+--
! acltest2 | {josh=arwdDxt/josh,regressgroup1=r/josh}
(1 row)
CREATE FUNCTION regressns.testfunc1() RETURNS int AS 'select 1;' LANGUAGE
sql;
Very minor stylistic or comment issues:
* There's a stray newline added in pg_class.h (no other changes were made to
that file by this patch)
* It feels to me like the comment Continue with standard grant in aclchk.c
interrupts the flow of the code, though such a comment was likely useful
when the patch was being written.
* pg_namespace_default_acl.h:71 should read objects stored *in* pg_class
* The comment at the beginning of InsertPgClassTuple() in catalog/heap.c
should probably be updated to say that relation's ACLs aren't always NULL by
default
* copy_from in gram.y got changed to to_from, but to_from isn't ever used in
the default ACL grammar. I wondered if this was changed so you could use the
same TO/FROM code as COPY uses, and then you decided to hardcode TO and FROM?
In my perusal of the patch, I didn't see any code that screamed at me as
though it were a bad idea; quite likely there weren't any really bad ideas but
I can't say with confidence I'd have spotted them if there were. The addition
of both the NSPDEFACLOBJ_* defines alongside the NSP_ACL_OBJ_* defines kinda
made me think there were too many sets of constants that had to be kept in
sync, but I'm not sure that's much of an issue in reality, given how unlikely
it is that schema object types to which default ACLs should apply are likely
to be added or removed.
I don't know how patches that require catalog version changes are generally
handled; should the patch include that change?
More testing to follow.
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
signature.asc
Description: Digital signature
Re: [HACKERS] [PATCH] DefaultACLs
Joshua Tolley wrote: I don't know how patches that require catalog version changes are generally handled; should the patch include that change? The committer should handle that. 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] [pgsql-www] Launching commitfest.postgresql.org
On Fri, Jul 17, 2009 at 04:13:58PM -0700, David Fetter wrote: On Fri, Jul 17, 2009 at 10:36:14AM -0700, Joshua D. Drake wrote: On Thu, 2009-07-16 at 21:31 -0700, Josh Berkus wrote: Robert, BTW, the new commitfest software is great. Easily a 75% reduction in time required to track reviewing activity. I agree. It is much better. I have one suggestion. Make the headings sortable (except maybe for patch name). Here's a patch for that :) Untested, because I couldn't figure out how to set up a commitfest on my machine :( Sorry about the duplicate. It looked like the first had fallen into a black hole :P 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] Using results from INSERT ... RETURNING
On Fri, Jul 17, 2009 at 10:42:02AM +0300, Peter Eisentraut wrote: On Tuesday 07 July 2009 23:31:54 Marko Tiikkaja wrote: Here's a patch(WIP) that implements INSERT .. RETURNING inside a CTE. Could you supply some test cases to illustrate what this patch accomplishes? postgres:54321=# CREATE TABLE t(i INTEGER); CREATE TABLE postgres:54321=# WITH t1 AS ( INSERT INTO t VALUES (1),(2),(3) RETURNING 'INSERT', i ) SELECT * FROM t1; ?column? | i --+--- INSERT | 1 INSERT | 2 INSERT | 3 (3 rows) Not working yet: CREATE TABLE t(i SERIAL PRIMARY KEY); NOTICE: CREATE TABLE will create implicit sequence t_i_seq for serial column t.i NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index t_pkey for table t CREATE TABLE postgres:54321=# WITH t1 AS (INSERT INTO t VALUES (DEFAULT),(DEFAULT),(DEFAULT) RETURNING 'INSERT', i) SELECT * FROM t1; ERROR: unrecognized node type: 337 Also planned, but no code written yet: UPDATE ... RETURNING DELETE ... RETURNING UNION [ALL] of each of INSERT, UPDATE, and DELETE...RETURNING inside the CTE, analogous to recursive CTEs with SELECT. Way Out There Possibility: mix'n'match recursion. 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] navigation menu for documents
OK, if you untar the attached in the docs dir there are a three separate sets of changes in it. It all functions, but consider it a discussion point rather than a patch. Presumably we'd need to discuss a patch over on the docs mailing-list. 1. Fixed navigation Copy STYLING/stylesheet.css over the existing one and you will have static navigation links top and bottom of the page. 2. Titles on navigation links. Run ./STYLING/title_links.pl and it should add title attributes to the navigation links. This means hovering over the top links gives the title of the page they will go to. Presumably we could do this directly from the sgml source, and I think it's probably worthwhile. With 1+2 I think there's an argument in favour of removing the bottom navigation - it's only useful if you can't see the top links. 3. Javascript popup menu. This uses jquery, but that's just for convenience during discussion. You could rework this without it. Copy STYLING/*.js and STYLING/menu.inc to the docs dir and then run ./STYLING/include_javascript.pl to include the popup script. The central chapter heading section of the top navigation area should now be a link that toggles the menu on/off. The menu could be as simple/complex as you like - this is just what I hacked together by parsing the TOC on index.html I've tested it on Firefox, Opera, IE7 and Safari. Realistically, the only real problem platforms will be IE6 and perhaps iphones. -- Richard Huxton Archonet Ltd STYLING.tgz Description: application/compressed-tar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] race condition in CatchupInterruptHandler was:(Re: [HACKERS] Review: support for multiplexing SIGUSR1)
On Fri, Jul 17, 2009 at 3:30 PM, Jaime Casanovajcasa...@systemguards.com.ec wrote: i wasn't able to repeat this on a new instalation and of course i can't swear this is your patch fault... this is not your patch fault but an existing bug, i repeat that behaviour in an unpatched source tree... ok, i reproduced this again and again (i have tried only in 8.4.0) with the following steps: - open five terminals, and create empty databases pgbench1 and pgbench2 - on first execute: make installcheck - on second: pgbench -i -s1000 pgbench1 - on third: pgbench -i -s1000 pgbench2 - on fourth: explain analyze with q as (select * from generate_series(1, 100)) select * from q a, q b, q c, q d, q e, q f; - on fifth: execute two pg_cancel_backend for killing things happen in terminals first, second or third (they have to actually cancel something, eg: return true) then pg_cancel_backend the explain analyze and repeat the pg_cancel_backend, that interrupt will be pending and when you rerun the explain analyze (or any other query in that same session) it will be get cancelled immediately ok, that's as far as i can go with this... now, i'm going to return to my assignment as rrr -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] slow count in window query
2009/7/18 Kevin Grittner kevin.gritt...@wicourts.gov: Pavel Stehule pavel.steh...@gmail.com wrote: table was filled with random numbers and analyzed - you can simple check it - look on begin of the thread. This table wasn't updated. Confirmed. The ORDER BY consistently speeds up the query. Odd Sort speed varied based on random sequence generated, but typical plan and timings: Kevin's result is quite odd. I confirmed that using IndexScan looked fater in Pavel's result but yours is with Sort node. I found that those results are seen in relatively small set. I increased the source table up to 10 rows and the OVER (ORDER BY a) case got slower. What really suprised me is in any case without ORDER BY clause in the window, WindowAgg node starts quite later than the lower node finishes. test=# explain analyze select count(*) over () from x; WindowAgg (cost=0.00..229.00 rows=1 width=0) (actual time=32.435..97.448 rows=1 loops=1) - Seq Scan on x (cost=0.00..104.00 rows=1 width=0) (actual time=0.007..14.818 rows=1 loops=1) Total runtime: 112.526 ms I had thought WindowAgg actual time would be 14.xxx ... 97.448 but actually 32.435 97.448. ORDER BY case returns the first result as soon as underneath Sort (or IndexScan) returns the first (actually the second), because window frame has only a row. But even the frame contains all the row (i.e. OVER() case) can return the first row not so later than the underneath node returns the last. If I understand exlain analyze correctly and it tells us the fact, WindowAgg without ORDER BY clause gets unreasonably slow. Let me see. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
