[HACKERS] stack usage in toast_insert_or_update()
Not sure whether its worth optimizing, but had spotted this while browsing the code a while back. So thought would post it anyways. The stack usage for toast_insert_or_update() may run into several KBs since the MaxHeapAttributeNumber is set to a very large value of 1600. The usage could anywhere between 28K to 48K depending on alignment and whether its a 32-bit or a 64-bit machine. Is it very common to have so many attributes in a table ? If not, would it be worth to allocate only as much space as required ? Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
[HACKERS] Bug? CREATE TABLE AS (... UNION ...)
I think I found a bug, or at least a discrepancy. Afaict the
transformSetOperationsStmt function should have identical code to
transformSelectStmt outside of the operations affected by set operations. If
that's the case then the SELECT INTO/CREATE TABLE AS code was not updated when
last it was touched for regular queries.
I think this means WITH[OUT] OIDS and WITH definition won't currently work
correctly if the select query involves a UNION or other set operation. Also
temporary tables created with an ON COMMIT option will ignore it and any
tablespace directive will be ignored.
Should I just copy the same code over or is anyone interested in refactoring
this? Or do I have it wrong somehow?
TransformSelectStmt:
/* handle any SELECT INTO/CREATE TABLE AS spec */
if (stmt-into)
{
qry-into = stmt-into;
if (stmt-intoColNames)
applyColumnNames(qry-targetList, stmt-intoColNames);
qry-intoOptions = copyObject(stmt-intoOptions);
qry-intoOnCommit = stmt-intoOnCommit;
qry-intoTableSpaceName = stmt-intoTableSpaceName;
}
transformSetOperationStmt:
/*
* Handle SELECT INTO/CREATE TABLE AS.
*
* Any column names from CREATE TABLE AS need to be attached to both the
* top level and the leftmost subquery. We do not do this earlier
because
* we do *not* want sortClause processing to be affected.
*/
if (intoColNames)
{
applyColumnNames(qry-targetList, intoColNames);
applyColumnNames(leftmostQuery-targetList, intoColNames);
}
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Bug? CREATE TABLE AS (... UNION ...)
Gregory Stark [EMAIL PROTECTED] writes: Should I just copy the same code over or is anyone interested in refactoring this? Or do I have it wrong somehow? Hm, it appears I have this wrong somehow since I can create tables using CREATE TABLE AS specifying tablespaces just fine. But I do't see how it can work. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Hi! Sorry for the late reply. On Thu, 25 Jan 2007 01:52:32 -0500 Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Bruce Momjian) writes: Fix for plpython functions; return true/false for boolean, This patch has broken a majority of the buildfarm. Is it possible to tell me which python versions you want to support? Just as a hint: 2.5 is the current stable version. Cheers Guido ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Guido Goldstein wrote: Is it possible to tell me which python versions you want to support? The issue isn't so much which versions we want to support. There is certainly some flexibility with that. But when a patch breaks the buildfarm a) unannounced and b) without any apparent feature gain, then people get annoyed. That said, we certainly try to support a few more versions of Python than just the last one, but I'm not sure anyone knows which ones exactly. As a data point: Quite probably, Python 2.5 does *not* work with anything = 8.1, so it would be nice if we could give the Python 2.4 users the option of not having to upgrade to Python 2.5 at the same time as upgrading to PostgreSQL 8.2. This doesn't really govern your 8.3 patch, however. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Guido Goldstein wrote: Is it possible to tell me which python versions you want to support? There are still products shipping with 2.3 (e.g. RHEL4). I'd be surprised if we need to go back before that. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] stack usage in toast_insert_or_update()
Pavan Deolasee [EMAIL PROTECTED] writes: The stack usage for toast_insert_or_update() may run into several KBs since the MaxHeapAttributeNumber is set to a very large value of 1600. The usage could anywhere between 28K to 48K depending on alignment and whether its a 32-bit or a 64-bit machine. So? The routine is not re-entrant so I don't see that the stack space is a big problem. It's coded that way to avoid palloc/pfree cycles... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
On Mon, Jan 29, 2007 at 09:56:16PM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: I'm thinking we need a check in elog.c on the: if ((!Redirect_stderr || am_syslogger) pgwin32_is_service()) write_eventlog(edata-elevel, buf.data); line, that checks if the syslogger process has been started yet. [ shrug... ] None of those other variables are guaranteed correct at process start, either... am_syslogger is inialized to false by default, so that one is pretty safe (it' sonly set to true when inside the actual syslogger, which will of course not happen in the postmaster). And in the syslogger, it's set at the very top. Redirect_stderr is initialized to false by default. Which means that until redirect_stderr is set (=when we read the postgresql.conf file), the above will alrady evaluate to write to the eventlog (per the !Redirect_stderr). Thus, we only need to cover the time between setting Redirect_stderr to true (which happens when we read the config file) to starting of the syslogger. Looking in postmaster.c, there are several errors that happen at this point that will use write_stderr, but others (like SSL) are functoins called that will call elog. So I think we either need to add this check, or we need to start the syslogger much sooner. In fact, we need this check anyway, because there will always be a window between the two where other GUC variables are set and can cause an error to be logged. So I still tthink it's a good idea. Even though it doesn't solve every case, it solves a lot of them I think. And more importantly on that, I don't see how it would *break* anything (given that it still fires only when running as a service, when everything on stderr is just thrown away anyway). Do you see suhc a failure case? //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 10 weeks to feature freeze (Pending Work)
On Mon, Jan 29, 2007 at 12:44:51PM -0800, Henry B. Hotz wrote: On Jan 29, 2007, at 9:49 AM, Magnus Hagander wrote: Henry B. Hotz wrote: Henry B. Hotz: GSSAPI authentication method for C (FE/BE) and Java (FE). Magnus Haglander: SSPI (GSSAPI compatible) authentication method for C (FE) on Windows. (That fair Magnus? Or you want to volunteer for BE support as well?) Seems fair and about what we discussed. And no, I won't volunteer as long as you're on it - not sure I'll have the time to do it all in time. I'm only volunteering BE for Unix, not Windows. Not sure we need BE for Windows for 8.3 though. This is enough. Oh certainly, I'm thinking BE on windows as well, but not sure if we'll have it for 8.3. We need to have frontend, so we have the same support as we have for krb5. Backend is a bonus, but it'd be nice to have it. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Andrew Dunstan [EMAIL PROTECTED] writes: Guido Goldstein wrote: Is it possible to tell me which python versions you want to support? There are still products shipping with 2.3 (e.g. RHEL4). I'd be surprised if we need to go back before that. As far as Red Hat is concerned, we won't be trying to get PG 8.3 and up to run on anything older than RHEL4, so python 2.3 is old enough. Not sure how the release timing has worked out for other distros ... but the presence of python 2.3 in the buildfarm says to me that it's still fairly popular. [ digs a bit more... ] Actually, it looks like Fedora Core 1 shipped with python 2.2.3, which means that's what buildfarm member thrush is running. So you probably don't want to break 2.2 either, at least not for a basically cosmetic patch. I don't say that we'd reject a patch that breaks 2.2 compatibility, but you'd need to put forth a sufficient justification. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
Magnus Hagander [EMAIL PROTECTED] writes: So I still tthink it's a good idea. Even though it doesn't solve every case, it solves a lot of them I think. And more importantly on that, I don't see how it would *break* anything (given that it still fires only when running as a service, when everything on stderr is just thrown away anyway). Do you see suhc a failure case? The case I'm worried about is subprocess startup, where we haven't yet been able to re-set any of these variables correctly. And yes, I think it's an issue: if a DBA is expecting to find PG error messages in the syslogger files, he's unlikely to go look in the eventlog. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
On Tue, Jan 30, 2007 at 10:32:14AM -0500, Tom Lane wrote: Magnus Hagander [EMAIL PROTECTED] writes: So I still tthink it's a good idea. Even though it doesn't solve every case, it solves a lot of them I think. And more importantly on that, I don't see how it would *break* anything (given that it still fires only when running as a service, when everything on stderr is just thrown away anyway). Do you see suhc a failure case? The case I'm worried about is subprocess startup, where we haven't yet been able to re-set any of these variables correctly. And yes, I think it's an issue: if a DBA is expecting to find PG error messages in the syslogger files, he's unlikely to go look in the eventlog. But in that case, the syslogger is already running, right? So it'll pick up the messages and drop them in the log as expected. Because we can't start backends before the syslogger is up, and I think it's the first of our subprocesses to start still? You'll have problems if the syslogger keeps crashing, but if that happens we will at least have the log that the syslogger is crashing. I get the feeling I'm missing something, but I'm not sure what it is :-) But I guess maybe the added check has to be not just (!syslogger_started) but (!syslogger_started is_postmaster)? //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll
Thanks! Tom, We are using the win32 version of the postgres 8.0.10.5031. We need to replace that with the same version (not necessarily to the same point release) without using SSL (without using the openssl libraries mentioned below). We are trying to rebuild the binary as you suggested with ssl flag off. Are there any tips you can provide us on the build process or it is quite straight forward? Again, thank you for the help. Tom -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Saturday, January 27, 2007 9:11 PM To: Tom Dong Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll Tom Dong [EMAIL PROTECTED] writes: I am looking for a way via configuration, not recompilation, = to make Postgres not to use (load) the openssl lib libeay32.dll and = ssleay32.dll. There is none. If you don't want SSL support, rebuild without it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Questions about parser code
Is it considered ok for the pstate data structures to have references to nodes in the query tree? Or should they do copyObject() if they need to refer to them? Is it ok to scribble on and reuse objects from the parse tree when generating the transformed tree? Or should the transformed query object be built from freshly allocated nodes? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] standard_conforming_strings 'on' for 8.3?
Are we going to turn on standard_conforming_strings for 8.3? We discussed the idea when we added it in 8.1, and enabled the backslash warning in 8.2. We have gotten almost no pushback on the warning, so it seems enabling it might be good. Right now, for default postgresql.conf, users are getting warnings for backslashes in non-E'' strings, so once we set standard_conforming_strings to 'on', we would turn off the warning. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] log ssl mode with connections?
I just turned on SSL for a test server and noticed that SSL mode isn't logged with the connection. Should it be? It should be relatively simple to add. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Patch queue
FYI, I have been working all January to process 8.3 held patches/ideas, plus process the items arriving during the month. While I have been able to make some progress, there are still a significant number of items for me to address. I will keep working on it and try to complete it by mid-February. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] Missing error message on missing ssl-key-files
Magnus Hagander [EMAIL PROTECTED] writes: But I guess maybe the added check has to be not just (!syslogger_started) but (!syslogger_started is_postmaster)? That would at least get you out of the problem of having to transmit the syslogger_started flag to the backends... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
On Jan 30, 2007, at 2:43 AM, Guido Goldstein wrote: Is it possible to tell me which python versions you want to support? Just as a hint: 2.5 is the current stable version. I support a lot of python on several platforms. For broad compatibility with pre-installed Python versions on recent OS versions, Python 2.3 support is essentially mandatory and there are few good reasons to not support it. I occasionally see Python 2.2 on really old systems by default, but it takes significantly more effort to support versions that old; the solution in my case is to upgrade Python to 2.3 or 2.4. Python 2.5 may be the current stable version, but vanilla source builds segfault on some Python code that runs fine in 2.3 and 2.4, strongly suggesting that it is not mature enough that I would put it anywhere near anything important (like a database). J. Andrew Rogers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Questions about parser code
Gregory Stark [EMAIL PROTECTED] writes: Is it ok to scribble on and reuse objects from the parse tree when generating the transformed tree? Or should the transformed query object be built from freshly allocated nodes? We do both already; take your pick. If you do the former, though, I suggest designing the code so that it's a no-op on an already-transformed node. It used to be the case that the grammar could generate multiple references to the same subtree (e.g., by transforming x BETWEEN y AND z to x = y AND x = z) and I'm not sure we have removed all such shortcuts. There's some logical cleaniness to using different node types for raw and transformed trees, but when there's a simple one-for-one correspondence this is probably overkill. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] standard_conforming_strings 'on' for 8.3?
Bruce Momjian [EMAIL PROTECTED] writes: Are we going to turn on standard_conforming_strings for 8.3? I'd be inclined to wait a bit longer, i.e., 8.4, seeing that this is intended to be a short release cycle. 8.2 has not been out long enough to draw any meaningful conclusions about whether we have gotten almost no pushback --- for instance it's probably not shipped in any major Linux distros yet. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] log ssl mode with connections?
Andrew Dunstan [EMAIL PROTECTED] writes: I just turned on SSL for a test server and noticed that SSL mode isn't logged with the connection. Should it be? Why? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] standard_conforming_strings 'on' for 8.3?
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Are we going to turn on standard_conforming_strings for 8.3? I'd be inclined to wait a bit longer, i.e., 8.4, seeing that this is intended to be a short release cycle. 8.2 has not been out long enough to draw any meaningful conclusions about whether we have gotten almost no pushback --- for instance it's probably not shipped in any major Linux distros yet. OK, makes sense. I am just noticing some of the documentation isn't clear on mentioning standard_conforming_strings in all places, so I will update that. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bug? CREATE TABLE AS (... UNION ...)
Gregory Stark [EMAIL PROTECTED] writes: Hm, it appears I have this wrong somehow since I can create tables using CREATE TABLE AS specifying tablespaces just fine. But I do't see how it can work. Look at the first few lines of transformSetOperationStmt. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] log ssl mode with connections?
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I just turned on SSL for a test server and noticed that SSL mode isn't logged with the connection. Should it be? Why? If I am allowing both SSL and non-SSL I might like to know which is used by a particular connection. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] SQL to get a table columns comments?
SELECT CASE WHEN sfl.description IS NOT NULL THEN sfl.description WHEN sfl.description IS NULL THEN pa.attname::character varying ELSE pd.description::character varying END AS label FROM ONLY pg_class pc JOIN ONLY pg_attribute pa ON pc.oid = pa.attrelid AND pc.relnamespace = 2200::oid AND pc.reltype 0::oid AND (pc.relkind = 'r'::char OR pc.relkind = 'c'::char OR pc.relkind = 'v'::char) JOIN ONLY pg_type pt ON pa.atttypid = pt.oid LEFT JOIN ONLY pg_description pd ON pc.oid = pd.objoid AND pa.attnum = pd.objsubid LEFT JOIN sys_flex_labels sfl ON pc.oid = sfl.table_oid::oid AND pa.attname::character varying::text = sfl.column_name::text WHERE pa.attnum 0 ORDER BY pc.relname::character varying, pa.attnum; Timasmith [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, What query can I run to get the comments for my table columns. i.e. the ones on my 8.1 database added with this command: COMMENT ON COLUMN addresses.address_id IS 'Unique identifier for the addresses table'; thanks Tim ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] log ssl mode with connections?
On Tue, 30 Jan 2007, Andrew Dunstan wrote: If I am allowing both SSL and non-SSL I might like to know which is used by a particular connection. Other places I've heard people ask for this info: 1) pg_stat_activity to see who's currently connected and how. 2) Via a function (boolean am_i_using_ssl()) so they can make security decisions in views or procedural code. Kris Jurka ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Proposal for partial resove issue of GIN fullscan.
Small introduction: GIN index doesn't support full scan of index now because of disaster performance. Pointer to each heap tuple will be returned several times. Next, if extractQuery doesn't return anything, GIN generates error 'GIN index does not support search with void query'. That is because of different semantic meaning of operations: some operation with void query should returns all tuples, some nothing. Now, support function extractQuery has prototype (pseudocode): Datum *extractQuery( Datum value, uint32 *nentry, StrategyNumber strategy) Proposal: Change extractQuery's prototype to: Datum *extractQuery( Datum value, int32 *nentry, StrategyNumber strategy) And add agreement about meaning nentry's value: nentry 0 - number of entry to search nentry = 0 - query requires full scan nentry 0 - guarantee that any tuple can't satisfy query So, if GIN gets nentry 0 from at least one index quals then gingettuple/gingetmulti can do not actual search, just returns false. Next, modify gincostestimate to call extractQuery to define nentry answer for each clause in indexQuals. In case nentry == 0, gincostestimate should return disable_cost cost estimate of index search to prevent index usage. Disadvantage of this proposal: gincostestimate can't work with queries which are taken from table or subselect, so proposal doesn't resolve all cases of issue, but eliminates most frequent. Void tsquery (from tsearch2) always means empty result and fast working of GIN, so, tsearch2's users will not face a error 'GIN index does not support search with void query' Comments, objections, suggestions? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pgsql: Fix for plpython functions; return true/false for boolean,
Peter Eisentraut wrote:
Guido Goldstein wrote:
Is it possible to tell me which python versions you want to
support?
The issue isn't so much which versions we want to support. There is
certainly some flexibility with that. But when a patch breaks the
buildfarm a) unannounced and b) without any apparent feature gain, then
people get annoyed.
If this breaks the buildfarm it's not my failure.
Except you can tell me what I've got to do with the
buildfarm.
If you mean that plpython didn't compile, fine; simply tell
the people what version they should consider when sending
in patches.
I've checked the patch with postgres 8.1.3 and 8.2.1
with python 2.4 and 2.5 on intel 32 bit and amd 64 bit
systems; all systems running linux.
*And* it's not a feature patch but a bug-fixing one!
Python is a language with strong typing, so silently
converting a datatype is a bug -- not a feature.
Btw, you'll lose the type information of boolean columns in
trigger functions (NEW and OLD dicts, no explicit parameters),
which does cause problems.
That said, we certainly try to support a few more versions of Python
[...]
If you want to support python 2.3 use the attached patch, which also
works for the newer python versions.
The Python 2.3 branch is the oldest _officially_ supported python version.
Anyway, to circumvent the above mentiond point a) I herewith anncounce
that the included patch might break the buildfarm.
Cheers
Guido
--- postgresql-8.2.1.orig/src/pl/plpython/plpython.c 2006-11-21 22:51:05.0 +0100
+++ postgresql-8.2.1/src/pl/plpython/plpython.c 2007-01-17 18:06:58.185497734 +0100
@@ -1580,8 +1580,8 @@
PLyBool_FromString(const char *src)
{
if (src[0] == 't')
- return PyInt_FromLong(1);
- return PyInt_FromLong(0);
+ return PyBool_FromLong(1);
+ return PyBool_FromLong(0);
}
static PyObject *
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] May, can, might
- Original Message - From: Bruce Momjian [EMAIL PROTECTED] -- snip -- I would like to clean up our documentation to consistently use these words. Objections? (Who says were obsessive?) :-) -- more snip -- Did you mean, Who says we're obsessive? ;-) Sean ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Questions about parser code
So I have basic non-recursive queries working. However currently it's essentially inlining the subquery at every call-site which obvious will never handle recursive queries and in fact doesn't even do what people expect from the basic syntax. The use case for the WITH syntax is when you have an expensive query you want to avoid calling multiple times from within your query. postgres=# with frotz(a) as (select * from x) select * from frotz,frotz as x(b); a | b ---+--- 1 | 1 1 | 2 2 | 1 2 | 2 (4 rows) Tom Lane [EMAIL PROTECTED] writes: Gregory Stark [EMAIL PROTECTED] writes: Is it ok to scribble on and reuse objects from the parse tree when generating the transformed tree? Or should the transformed query object be built from freshly allocated nodes? We do both already; take your pick. If you do the former, though, I suggest designing the code so that it's a no-op on an already-transformed node. It used to be the case that the grammar could generate multiple references to the same subtree (e.g., by transforming x BETWEEN y AND z to x = y AND x = z) and I'm not sure we have removed all such shortcuts. I was wondering whether it was necessary to copy the alias node from an existing node or if I could just create more references to it. There's some logical cleaniness to using different node types for raw and transformed trees, but when there's a simple one-for-one correspondence this is probably overkill. Currently I'm storing a lit of RangeSubselects in the pstate. That just happened to be a node with an alias and a subquery which is what I needed. I was considering replacing the SelectStmt node with a Query node directly instead of creating a new RangeSubselect node. However now I'm thinking I probably need to do something more complicated. As it is there's no way to tell when I add a rangetable to a query that it came from a subquery in the common table expression list in the pstate instead of from an inlined subquery. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PL/pgSQL RENAME functionality in TODOs
I was testing the following statement and found it working fine on version 8.2.1. Fix RENAME to work on variables other than OLD/NEW I can rename just any variable declared in a PL block apart from OLD/NEW. Is the TODOs list out of sync or I am missing the point here? --Imad www.EnterpriseDB.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] RI checks during UPDATEs
My understanding is that an UPDATE statement will fire exactly the same number of RI checks as does an INSERT, in all cases. ISTM possible that we could optimise away some RI checks in the case of UPDATEs. This might or might not save some cycles but it will definitely reduce the amount of locking taking place on referenced tables. A heavily updated referencing table can cause a stream of locks against a referenced table. Attempts to UPDATE the row on the referenced table could be severely hampered since only an UPDATE of the PK of the referenced table really needs to cause a cross-check. I see nothing in the SQL Standard that requires these checks to be made for an UPDATE, only that the integrity must not be violated. We know the attribute numbers of the keys for any particular trigger, so it seems possible to make an equality comparison between the old and new attribute values. If the values are similar, we can skip the check altogether. This seems cheaper than executing a statement to compare the new against the value in the referenced table. Any objections to implementing this? It would be even better if there was some way of not executing the trigger at all if we knew that the UPDATE statement doesn't SET the FK columns. That would require us to pass information about the potentially changed columns as part of the TriggerData data structure. That could be passed as an additional bitmap through to constraint triggers, so that they can return immediately if they have nothing to do - though that check makes more sense to perform *before* the trigger is queued for later execution. Comments? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [pgsql-patches] pg_dump pretty_print
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Tom Lane wrote: The original definition of the prettyprint flag was that it'd produce a version that was nice to look at but not guaranteed to parse back exactly the same; in particular it might omit parentheses that perhaps were really needed to ensure the same parsing. (I think there might be some other issues too ... but whitespace is NOT one of them.) It's possible that the current prettyprint code is smart enough to never make such an error --- and then again it's possible that it isn't. Like Peter, I've not got much confidence in that code, and don't want to trust pg_dump's correctness to it. Can we perhaps add to the TODO to get the pretty print functions audited and tested out? I'm sure people are already using the pretty print option today via psql so it seems like this should be a high priority. Plus of course I'd like to see it added to pg_dump once Peter, yourself, and others have more confidence in it working as one would expect. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200701301509 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFv6YcvJuQZxSWSsgRA1ujAKDqfH1lAUcba0ce8wBjN/PIRzfNxACgnVWf XnusK0UcywWnaBDF6KE/x4E= =WoFo -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] RI checks during UPDATEs
Simon Riggs [EMAIL PROTECTED] writes: Any objections to implementing this? Only that it was done a long time ago --- see RI_FKey_keyequal_upd_pk/fk. It would be even better if there was some way of not executing the trigger at all if we knew that the UPDATE statement doesn't SET the FK columns. People keep suggesting that, and the counterexample is always that you can't know what a BEFORE trigger might do to the row. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Modifying and solidifying contrib
Bruce Momjian wrote: Joshua D. Drake wrote: This seems like a good first step in growing a packaging infrastructure. I'd rather grow it organically than try to design it all up front. I am in Denver and have spotty inet access so forgive me. So where does this above leave us? What are we doing? I was kind of unclear on that too. It seems we are trying to address several issues: visibility of contrib, installation of contrib, etc. We discussed whether we put the functions in public, a schema for all contrib, or a schema for each contrib module, and then there was the discussion of how to configure someone using ten /contrib modules, or at least wanting them all to be accessible. And then there was the idea of allowing schema permissions to control access, so perhaps we could install more of /contrib by default, and allow the administrator to just enable/disable them via permissions. Personally, I think that might be the best approach because it allows us to eliminate the install process, but doesn't make the database less secure --- the administrator enables/disables them at runtime, or at least could. The issues I see are: 1. the 'thing name - the only name I have not seen some objection to is extension. 2. namespace - I think the consensus is tending towards one or more per extension. 3. install/uninstall support: Tom's proposal for an extension-schema map in the catalog will deal with that nicely, I think. 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). 5. legacy support - we need an option to load existing extensions to the public schema as now, or support for aliases/synonyms (the latter might be good to have regardless). 6. they all need proper docs. READMEs and the like are nowhere near good enough. Richard mentioned special testing requirements, but I don't see why we can't continue to use our standard regression mechanism. Mention has also been made of autoloading extensions with initdb. A case could perhaps be made for doing it in createdb - maybe not every db needs ltree, say. OTOH, if it's sitting quietly in its own schema than it's probably not doing any harm either, so maybe initdb should just load all the extensions it finds, and as you say make one less hoop to make people jump through. If we do that I think at least we'd need an option to inhibit autoloading. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll
On Tue, Jan 30, 2007 at 11:14:15AM -0500, Tom Dong wrote: Thanks! Tom, We are using the win32 version of the postgres 8.0.10.5031. We need to replace that with the same version (not necessarily to the same point release) without using SSL (without using the openssl libraries mentioned below). We are trying to rebuild the binary as you suggested with ssl flag off. Are there any tips you can provide us on the build process or it is quite straight forward? There is a FAQ avialable for building on win32 at http://www.postgresql.org/docs/faqs.FAQ_MINGW.html. //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] log ssl mode with connections?
On Tue, Jan 30, 2007 at 12:35:01PM -0500, Kris Jurka wrote: On Tue, 30 Jan 2007, Andrew Dunstan wrote: If I am allowing both SSL and non-SSL I might like to know which is used by a particular connection. Other places I've heard people ask for this info: 1) pg_stat_activity to see who's currently connected and how. 2) Via a function (boolean am_i_using_ssl()) so they can make security decisions in views or procedural code. That information is available to the client in the form of the API call PQgetssl(). It will return NULL if no SSL is in use, or something other than NULL if it is (a SSL * pointer, but you don't need to know that if you just want to know if you're on SSL or not). IIRC it was originally disucssed to put it as a function callable, but it was decided that it makes a lot more sense to provide it in the client library. I don't know how many other client libraries provide the SSL information stuff. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll
Thanks! Mangus, Our developers are currently working on the build. Just wondering if there are any builds, without the encryption, we can download. Thanks again for the response! Tom -Original Message- From: Magnus Hagander [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 30, 2007 3:52 PM To: Tom Dong Cc: Tom Lane; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll On Tue, Jan 30, 2007 at 11:14:15AM -0500, Tom Dong wrote: Thanks! Tom, We are using the win32 version of the postgres 8.0.10.5031. We need to replace that with the same version (not necessarily to the same point release) without using SSL (without using the openssl libraries mentioned below). We are trying to rebuild the binary as you suggested with ssl flag off. Are there any tips you can provide us on the build process or it is quite straight forward? There is a FAQ avialable for building on win32 at http://www.postgresql.org/docs/faqs.FAQ_MINGW.html. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] How to configure Postgres NOT to use (load) openssl libraries libeay32.dll and ssleay32.dll
On Tue, Jan 30, 2007 at 03:59:08PM -0500, Tom Dong wrote: Thanks! Mangus, Our developers are currently working on the build. Just wondering if there are any builds, without the encryption, we can download. None that I know of. All the official builds from postgresql.org (wihch are the MSI installer and the binaries-no-installer.zip) are built with SSL enabled. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] May, can, might
Standard English uses may, can, and might in different ways: may - permission, You may borrow my rake. can - ability, I can lift that log. might - possibility, It might rain today. Unfortunately, in conversational English, their use is often mixed, as in, You may use this variable to do X, when in fact, can is a better choice. Similarly, It may crash is better stated, It might crash. I would like to clean up our documentation to consistently use these words. Objections? (Who says were obsessive?) :-) -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] May, can, might
Bruce Momjian [EMAIL PROTECTED] writes: (Who says were obsessive?) :-) I may not fall into your clever trap... -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] May, can, might
On 1/30/07, Gregory Stark [EMAIL PROTECTED] wrote: Bruce Momjian [EMAIL PROTECTED] writes: (Who says were obsessive?) :-) I may not fall into your clever trap... But you certainly can! cymbal_crash/ (sorry...) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [DOCS] May, can, might
Bruce Momjian a écrit : Standard English uses may, can, and might in different ways: may - permission, You may borrow my rake. can - ability, I can lift that log. might - possibility, It might rain today. Unfortunately, in conversational English, their use is often mixed, as in, You may use this variable to do X, when in fact, can is a better choice. Similarly, It may crash is better stated, It might crash. I would like to clean up our documentation to consistently use these words. Objections? No objections at all... it can only ease translations. (Who says were obsessive?) :-) :) -- Guillaume. !-- http://abs.traduc.org/ http://lfs.traduc.org/ http://docs.postgresqlfr.org/ -- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Talks for OSCON? Only 5 days left!
All, We only have five days left to submit talks for OSCON (Portland, last week of July): http://conferences.oreillynet.com/cs/os2007/create/e_sess I'd like to check coordinate what people are submitting from PostgreSQL to make sure we have the strongest possible PostgreSQL content. So far we have: Performance Whack-a-Mole - Josh Berkus Joy of Index - Josh Berkus PostgreSQL analytics with DTrace - Theo Schlossnagle ... this isn't nearly enough. We need some cool talks on building applications with pgcrypto, PostGIS, TSearch2 and more! -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Modifying and solidifying contrib
On 1/30/07, Andrew Dunstan [EMAIL PROTECTED] wrote: [...] 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). As for search_path -- is it really needed to change it? I think it'd be better to leave default search_path even if we have many extensions each sitting in its own schema. If DBA/DBD wants, he can change it himself. The reasons to follow this way are: 1. two or more extensions might have functions with the same name (actually, that's what schemes/namespaces serve for) = we do not know which function should have higher priority (what order for schemas to choose?); 2. originally, when I've proposed to use separate schema name for each contrib module I've forgotten to mention another cause to do it -- this helps in development because everyone always knows what function is used (the code becomes a little bit larger, but understanding and code readability are improved) = so, it's better to not tweak search_path, it's better to encourage DBD to use full function names (if he wants to avoid using schema names, he can set search_path himself, resolving possible names priority issues mentioned above). Finally, AFAIK other DBMSs use the similar approaches (provide additional extensions/packages/extensions/... using separate namespaces and do not try to avoid writing namespace in function calls). -- Best regards, Nikolay
Re: [HACKERS] PL/pgSQL RENAME functionality in TODOs
imad [EMAIL PROTECTED] writes: Fix RENAME to work on variables other than OLD/NEW I can rename just any variable declared in a PL block apart from OLD/NEW. Is the TODOs list out of sync or I am missing the point here? Really? It looks pretty broken to me still: regression=# create function foo() returns int as $$ regression$# declare regression$# x int := 1; regression$# rename x to y; regression$# begin regression$# return y; regression$# end;$$ language plpgsql; ERROR: syntax error at or near x LINE 4: rename x to y; ^ See old discussion here: http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Improving NOT IN
It's a fairly common case to want to improve a query along the lines of TableA intersect ~TableB. We can write this as select * from tableA where key not in (select * from tableB) or we can get more fancy select tableA.* from tableA left outer join tableB on tableA.key = tableB.key where tableB is null; I've worked out a new join method that will improve performance over and above the second case. This is effective where the referenced table (tableB) is fairly large and the join columns are discrete. Currently that mostly means they're integers. The plan seeks to *prove* that there are no matches, rather than taking the exhaustive join approach taken currently. First we need to show that the referenced table's PK values are a fully continuous sequence of integers with no gaps. One this has been proved, we can then use that fact to scan the FK table using the values of the min and max PK to see if any outliers exist. There is no actual comparison of the values, just a proof that none is required. I'll describe this using SQL statements, which execute as SeqScans of the PK and then FK tables. There is no preparatory step - no building a sort table or preparing a hash table, so these SQL statements always execute faster than the fastest current plan. Most importantly there is no step that consumes large amounts of memory, so the case where two tables are very large performs much, much better. 1. Scan referenced table a) select max(aid), min(aid) from accounts; b) select count(*) from accounts; Sometimes this is faster using two queries when the table has a PK. 2. Decision Step if max - min - count == 0 then we have a contiguous range and because we know we have a discrete datatype we can now *prove* that there are no missing values from the set bounded by the min and the max. We can then use that directly in a new query: 3. a) Scan referencing table select aid from history where aid ? or aid ?; using parameters of max and min from step 1 b) normal query Step 1 2 can fail to find a contiguous range, in which case we need to fall back to an existing query plan. So there is only small overhead in the case where we run the first query but fail to use the optimisation at all and need to fall back to existing query. We can estimate whether this is the case by estimating the row count of the table and see if that compares favourably with the expected number of values if the whole range min-max of values is actually present. The min/max query uses the Primary Key index (which must always be present) so takes very little time. So overall this looks like a win, in certain common cases, but not a particular loss in any case. Try this SQL 1. select max(aid), min(aid) from accounts; 2. select count(*) from accounts; 3. select aid from history where aid (select max(aid) from accounts) or aid (select min(aid) from accounts) limit 1; against alter table history add foreign key (aid) references accounts; I get (1) about 0.2secs (2) 6secs (3) 9secs against Alter Table 27secs Using work_mem = 64MB and data that fits in memory We could implement the new SQL directly within ALTER TABLE, or we could actually create this as a new plan type that would then allow the existing SQL to perform better in specific cases. I've not seen such a plan on any other RDBMS and think it might be completely new, which I'm calling a Proof Join, for want of a better description. The preparatory steps are completely excluded, hence the x2 speedup. For larger referenced tables the performance improvement could be much more. ISTM that even though this is a special case it is actually a common one, so would be worth optimising for. Ideas stage at the moment: thoughts? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] RI checks during UPDATEs
On Tue, 2007-01-30 at 15:24 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Any objections to implementing this? Only that it was done a long time ago --- see RI_FKey_keyequal_upd_pk/fk. OK, funny guy. :-) Its not very well documented, is all I can say. The code comments elsewhere in the file are very specific that the code applies to UPDATEs as well as INSERTs, hence my confusion. I'm relieved, actually, but still have a locking problem to resolve. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] parsenodes vs. primnodes
If something from primnodes.h (XmlExpr) needs something from parsenodes.h (TypeName), should I just move the former to the latter, or is there some major semantic dividing line between the two? Or maybe TypeName should really be a primnode? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving NOT IN
Simon Riggs [EMAIL PROTECTED] writes: First we need to show that the referenced table's PK values are a fully continuous sequence of integers with no gaps. Since that is unlikely to be the case, I can't see that this is worth implementing... I'll describe this using SQL statements, which execute as SeqScans of the PK and then FK tables. There is no preparatory step - no building a sort table or preparing a hash table, so these SQL statements always execute faster than the fastest current plan. Except that when you fail to prove it, as you usually will, you have wasted a complete seqscan of the table, and still have to fall back on a regular plan. If the thing were amenable to falling out fairly quickly on proof failure, it would be better, but AFAICS you don't know anything until you've completed the whole scan. I think the NOT IN optimization that *would* be of use is to automatically transform the NOT IN representation to an outer-join-with-null-test type of operation, so as to give us a wider choice of join methods. However, I'm not sure about correct handling of NULLs on the RHS in such a scenario. The existing hashed-IN code has to jump through some really ugly hoops to give spec-compliant answers with NULLs. BTW, your sketch fails in the presence of NULLs on the RHS ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Improving NOT IN
On Tue, 2007-01-30 at 17:34 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: First we need to show that the referenced table's PK values are a fully continuous sequence of integers with no gaps. Since that is unlikely to be the case, I can't see that this is worth implementing... Integers are typically used as keys... I'll describe this using SQL statements, which execute as SeqScans of the PK and then FK tables. There is no preparatory step - no building a sort table or preparing a hash table, so these SQL statements always execute faster than the fastest current plan. Except that when you fail to prove it, as you usually will, you have wasted a complete seqscan of the table, and still have to fall back on a regular plan. If the thing were amenable to falling out fairly quickly on proof failure, it would be better, but AFAICS you don't know anything until you've completed the whole scan. Have some faith, please. It's fairly straightforward to make an estimate of whether the number of rows is approximately correct to make the scan worthwhile. On large queries it seems worth the risk; we might even store the answer as part of stats, so we'd know not to bother with the test in the future. BTW, your sketch fails in the presence of NULLs on the RHS ... Certainly does, but the typical query has PK there, so no NULLs. One of the main use cases is the ALTER TABLE ... ADD FK case. As I said, we could just code that with altered SQL, or we could add a new plan. Anyway, it seemed like the right time to log the thought anyhow. I think the NOT IN optimization that *would* be of use is to automatically transform the NOT IN representation to an outer-join-with-null-test type of operation, so as to give us a wider choice of join methods. However, I'm not sure about correct handling of NULLs on the RHS in such a scenario. The existing hashed-IN code has to jump through some really ugly hoops to give spec-compliant answers with NULLs. Yeh, NOT IN with NULLs is. bizarre. What would be wrong with checking for a NOT NULL constraint? Thats how other planners cope with it. Or are you thinking about lack of plan invalidation? ISTM straightforward to do a search for a ANDed set of IS NOT NULL constraints. I've not found another server that does that, even though it seems like a straightforward win. Let me think on that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving NOT IN
Simon Riggs [EMAIL PROTECTED] writes: On Tue, 2007-01-30 at 17:34 -0500, Tom Lane wrote: Since that is unlikely to be the case, I can't see that this is worth implementing... Integers are typically used as keys... Yeah, in the form of sequences, so you have a hole for every failed insert. If the key isn't coming from a sequence then there's still not any very good reason to suppose it's exactly contiguous. People do delete entries. What would be wrong with checking for a NOT NULL constraint? Thats how other planners cope with it. Or are you thinking about lack of plan invalidation? Yup, without that, depending on constraints for plan correctness is pretty risky. Basically what I see here is a whole lot of work and new executor infrastructure for something that will be a win in a very narrow use-case and a significant loss the rest of the time. I think there are more productive ways to spend our development effort. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Improving NOT IN
On Tue, 2007-01-30 at 18:06 -0500, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: What would be wrong with checking for a NOT NULL constraint? Thats how other planners cope with it. Or are you thinking about lack of plan invalidation? Yup, without that, depending on constraints for plan correctness is pretty risky. Basically what I see here is a whole lot of work and new executor infrastructure for something that will be a win in a very narrow use-case and a significant loss the rest of the time. I think there are more productive ways to spend our development effort. For that part of the email, I was talking about your ideas on NOT IN. Checking for the explicit exclusion of NULLs is worthwhile with/without plan invalidation. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Logging Lock Waits
Right now, I need a way to log the lock wait times for certain queries, to see if they are acceptable. DTrace is not available. I'm dealing with a problem that is either a standard lock wait involving RI trigger locking, or a weirder problem involving lock starvation as a result of soft deadlock queue re-arrangement. I doubt its the latter but neither situation can be straightforwardly enabled to diagnose the situation. Sometime earlier, I proposed log_min_lockwait. A frequent case is where the lockwait is so long that the statement effectively never completes, so the statement duration doesn't appear in the log. That can cause further diagnosis problems. Ideally, I'd like to know both 1. there is a lock wait and it is happening now 2. there was a lock wait and it was THIS bad For 1, I need to log something while the lock wait is happening, not just when it ends - it might not end successfully. For 2 we can log it during or afterwards, not a problem. I'm thinking to write an INFO message, so that people can choose to log this and/or the SQL statement if they choose. e.g. INFO: lock wait time of XXX secs has been exceeded Comments? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] parsenodes vs. primnodes
Peter Eisentraut [EMAIL PROTECTED] writes: If something from primnodes.h (XmlExpr) needs something from parsenodes.h (TypeName), should I just move the former to the latter, or is there some major semantic dividing line between the two? Or maybe TypeName should really be a primnode? Anything in primnodes should never need a TypeName. Post-parse-analysis representation of type info is as a pg_type Oid. If you think you need something else, let's have a discussion about why. (Perhaps you need to split XmlExpr into two different node types, one for raw grammar output and one for parse-analysis output?) regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Logging Lock Waits
Simon Riggs [EMAIL PROTECTED] writes: I'm thinking to write an INFO message, so that people can choose to log this and/or the SQL statement if they choose. e.g. INFO: lock wait time of XXX secs has been exceeded The available timer resources are already overloaded; adding an independent timeout for this will complicate the code more than seems justified. Perhaps you could add a LOG message whenever the deadlock-check code runs (and doesn't detect an error, so is about to go back to sleep). This would take almost no effort, and the granularity could still be adjusted via the deadlock check timeout. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Modifying and solidifying contrib
On Tue, Jan 30, 2007 at 03:49:14PM -0500, Andrew Dunstan wrote: Bruce Momjian wrote: Joshua D. Drake wrote: This seems like a good first step in growing a packaging infrastructure. I'd rather grow it organically than try to design it all up front. I am in Denver and have spotty inet access so forgive me. So where does this above leave us? What are we doing? I was kind of unclear on that too. It seems we are trying to address several issues: visibility of contrib, installation of contrib, etc. We discussed whether we put the functions in public, a schema for all contrib, or a schema for each contrib module, and then there was the discussion of how to configure someone using ten /contrib modules, or at least wanting them all to be accessible. And then there was the idea of allowing schema permissions to control access, so perhaps we could install more of /contrib by default, and allow the administrator to just enable/disable them via permissions. Personally, I think that might be the best approach because it allows us to eliminate the install process, but doesn't make the database less secure --- the administrator enables/disables them at runtime, or at least could. The issues I see are: 1. the 'thing name - the only name I have not seen some objection to is extension. +1 for name extension. 2. namespace - I think the consensus is tending towards one or more per extension. +1 here too. I understand that this may result in schemas that have few functions in them. That's OK :) 3. install/uninstall support: Tom's proposal for an extension-schema map in the catalog will deal with that nicely, I think. +1 :) 4. visibility/searchpath issues. I don't think long search paths are a huge issue, but I think we can make life a bit easier by tweaking searchpath support a bit (David's clever SQL notwithstanding). The only clever bit I added was the CASE statement. Credit for the rest belongs to Andrew at Supernews. It's not a bad thing for people to keep around, either way. :) 5. legacy support - we need an option to load existing extensions to the public schema as now, or support for aliases/synonyms (the latter might be good to have regardless). Hrm. This gets tricky. When things are mandated to be in their own namespace, they need not check what everybody else's things are doing each time, whereas when they go into the public schema... :P 6. they all need proper docs. READMEs and the like are nowhere near good enough. Agreed. I'm thinking a new major section in the SGML docs is in order with a subsection for each contrib/ piece underneath. Richard mentioned special testing requirements, but I don't see why we can't continue to use our standard regression mechanism. A subdirectory in src/tests/regression for each one? Mention has also been made of autoloading extensions with initdb. A case could perhaps be made for doing it in createdb - maybe not every db needs ltree, say. OTOH, if it's sitting quietly in its own schema than it's probably not doing any harm either, so maybe initdb should just load all the extensions it finds, and as you say make one less hoop to make people jump through. If we do that I think at least we'd need an option to inhibit autoloading. I don't think it would be too much trouble to do extensions the way we now do tables and schemas in pg_dump, i.e. with multiple possible regular expression entries like --include-extension= and --exclude-extension= where the includes get evaluated before the excludes. Just my $.02 :) Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Modifying and solidifying contrib
On Jan 31, 2007, at 12:42 , David Fetter wrote: On Tue, Jan 30, 2007 at 03:49:14PM -0500, Andrew Dunstan wrote: 6. they all need proper docs. READMEs and the like are nowhere near good enough. Agreed. I'm thinking a new major section in the SGML docs is in order with a subsection for each contrib/ piece underneath. I agree re: new section. Are you thinking that all contrib docs would be built automatically, even if the individual extensions (neé contrib modules?) aren't installed? I think that would definitely raise awareness of the extensions that are available. I'd also like to see being able to add docs for non-core extensions (e.g., ip4r) to the main documentation. Not sure what that would involve: rebuilding the tocs and index, besides the new pages themselves? Or perhaps just a rebuild of the complete docs? I haven't had docs building on a local system for a couple of years, so I'm not it a position currently to play around with this, but it's something I'd love to learn how to do. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] PL/pgSQL RENAME functionality in TODOs
URL added to TODO. (I didn't have URLs in there at the time). --- Tom Lane wrote: imad [EMAIL PROTECTED] writes: Fix RENAME to work on variables other than OLD/NEW I can rename just any variable declared in a PL block apart from OLD/NEW. Is the TODOs list out of sync or I am missing the point here? Really? It looks pretty broken to me still: regression=# create function foo() returns int as $$ regression$# declare regression$# x int := 1; regression$# rename x to y; regression$# begin regression$# return y; regression$# end;$$ language plpgsql; ERROR: syntax error at or near x LINE 4: rename x to y; ^ See old discussion here: http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] stack usage in toast_insert_or_update()
On 1/30/07, Tom Lane [EMAIL PROTECTED] wrote: Pavan Deolasee [EMAIL PROTECTED] writes: The stack usage for toast_insert_or_update() may run into several KBs since the MaxHeapAttributeNumber is set to a very large value of 1600. The usage could anywhere between 28K to 48K depending on alignment and whether its a 32-bit or a 64-bit machine. So? The routine is not re-entrant so I don't see that the stack space is a big problem. It's coded that way to avoid palloc/pfree cycles... I always thought that it would be costlier to have a repeated stack allocation/deallocation of many KBs than dynamically allocating a small percentage of that. But I might be wrong. In fact, a small test I ran showed that mallloc/free is more costly. So may be are good. Btw, I noticed that the toast_insert_or_update() is re-entrant. toast_save_datum() calls simple_heap_insert() which somewhere down the line calls toast_insert_or_update() again. It looks a bit surprising, haven't look into detail though. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] stack usage in toast_insert_or_update()
Pavan Deolasee [EMAIL PROTECTED] writes: Btw, I noticed that the toast_insert_or_update() is re-entrant. toast_save_datum() calls simple_heap_insert() which somewhere down the line calls toast_insert_or_update() again. The toast code takes pains to ensure that the tuples it creates won't be subject to re-toasting. Else it'd be an infinite recursion. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PL/pgSQL RENAME functionality in TODOs
OK, so renaming does not work in the same block. You can rename a vairable in a nested block and thats why it works for OLD/NEW. BTW, what is the purpose behind it? Declaring a variable in a block and quickly renaming it does not make sense to me. --Imad www.EnterpriseDB.com On 1/31/07, Bruce Momjian [EMAIL PROTECTED] wrote: URL added to TODO. (I didn't have URLs in there at the time). --- Tom Lane wrote: imad [EMAIL PROTECTED] writes: Fix RENAME to work on variables other than OLD/NEW I can rename just any variable declared in a PL block apart from OLD/NEW. Is the TODOs list out of sync or I am missing the point here? Really? It looks pretty broken to me still: regression=# create function foo() returns int as $$ regression$# declare regression$# x int := 1; regression$# rename x to y; regression$# begin regression$# return y; regression$# end;$$ language plpgsql; ERROR: syntax error at or near x LINE 4: rename x to y; ^ See old discussion here: http://archives.postgresql.org/pgsql-hackers/2002-03/msg00591.php regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
