Re: [HACKERS] combined indexes with Gist - planner issues?
Hi, Hans-Juergen Schoenig -- PostgreSQL wrote: we did some experiments with doing such a table. the problem is if you want to allow arbitrary combinations of words which can be modeled perfectly with FTI. you would instantly end up with a self join with 5 relations or so - which is again bad. there are too many common words to consider doing with partly with gist and partly with a btree. How about an inverted index, either via GIN or with a custom table, such that you have the cheapest price per existing word. (That's pretty close to how full text searching itself works). Either reduce the number of words with tsearch2's stemming algorithms. Or go for trigrams right away. Split a word or search query in all its trigrams, then look up the (cheapest) price(s) per trigram and return the n least expensive ones. I've done somethings pretty similar for a customer, using the custom table approach, as integration of GIN just started back then. Even now, you need to consider the downside of that index lacking visibility information and having to recreate the index from time to time. OTOH a custom table needs a lot more manual twiddling with triggers and bulk index rebuilding. I guess I'd still go for a custom table today, as it's simply more flexible. Something like: CREATE TABLE cheapest_product_by_word ( trgm TEXT, cheapest_products bigint[] ); However, all of this is assuming that data (i.e. prices, products) change very rarely and it's beneficial to calculate such an intermediate lookup-table in advance. Not sure how much that's the case for you. Regards Markus Wanner -- 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] Triggers on columns
Tom Lane wrote:
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes:
Sure, and I found there might be difference between UPDATE and
UPDATE OF {all-columns} triggers. UPDATE trigger is always fired
when a row is updated even if none of the columns are actually
modified, but UPDATE OF {all-columns} trigger is fired only when
at least one of the columns is modified.
I'm betraying the fact that I haven't read the patch, but ...
exactly how, and when, are you determining whether a column has
been modified? I can't count the number of times somebody
has proposed simplistic and incorrect solutions to that.
Usually they forget about BEFORE triggers changing the row.
It uses heap_tuple_attr_equals() to check whether a certain
column is modified, or not.
Itagaki-san, isn't it more suitable to check rte-modifiedCols
than heap_tuple_attr_equals()? Although, this information is
not delivered to executor...
What is the correct behavior when UPDATE statement set a new
value but it was identical to the original value?
In this case, heap_tuple_attr_equals() cannot detect the column
is used as a target of the UPDATE.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on columns
Tom Lane t...@sss.pgh.pa.us wrote: exactly how, and when, are you determining whether a column has been modified? I can't count the number of times somebody has proposed simplistic and incorrect solutions to that. Usually they forget about BEFORE triggers changing the row. There are some approaches: 1. Just check conditions in alphabetical order. Ignore subsequent modifications after the conditions are examined. 2. Recheck conditions if NEW values are modified, but triggers that have been fired already are not executed twice. 3. Column triggers are called after non-conditional UPDATE triggers and column triggers cannot modify NEW values. I like approach 2. because it is the most user-friendly. There is a possibility that another trigger changes NEW values to unmodified state after some conditional triggers are executed, but it could be admissible. The approach 3. seems to be the most strict, but hard to use because of the restriction. Just for reference: - Oracle Database: They support multiple triggers and UPDATE OF and WHEN clause and can modify NEW values in trigger bodies. So they must have same problems discussing here -- but I cannot find how they work around it... http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2064026 - MySQL: They can modify NEW values, but no problem because they don't support UPDATE OF, WHEN clause, nor multiple triggers for each event. http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html Regards, --- ITAGAKI Takahiro 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] Triggers on columns
KaiGai Kohei kai...@ak.jp.nec.com wrote: Itagaki-san, isn't it more suitable to check rte-modifiedCols than heap_tuple_attr_equals()? Although, this information is not delivered to executor... I'd like to check conditions by comparing actual values but not a target of UPDATE statement because I think almost user expects the former behavior. Unmodified UPDATE-targets are common case if we use a framework that generates SQL statements internally. Anyway, we need to compare the actual values if we want to treat NEW value modifed by another trigger correctly. Regards, --- ITAGAKI Takahiro 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
[HACKERS] Fwd: Copy out wording
Crap, I just realized I sent to pgadmin hackers by mystake. Meh. Our documentation for COPY (http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the following to say: The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL's COPY handles this by quoting. A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. Therefore, using the default settings, a NULL is written as an unquoted empty string, while an empty string is written with double quotes (). Reading values follows similar rules. You can use FORCE NOT NULL to prevent NULL input comparisons for specific columns. Shouldn't that be: A NULL is output as the NULL string and is not quoted, while a data value matching the empty string is quoted? If not, then what really is the difference between a NULL and a NULL string? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Concurrent execution of pg_relation_size and DROP TABLE
I received a trouble-report that a query using pg_relation_size() ends with an error, could not open relation. It came from DROP TABLE was executed concurrently; relation_open() used in pg_relation_size() raised an error. Is it reasonable to replace relation_open() calls to try_relation_open() ? If failed, pg_relation_size() will just return NULLs. Regards, --- ITAGAKI Takahiro 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] Feature request: DEFAULT as input value of function argument
IMHO convenient solution is to make possible to specify something like COLUMN_DEFAULT as input value of function. I wonder if it's possible. So, what do you think of it? -- Regards, Sergey Konoplev -- PostgreSQL articles in english russian http://gray-hemp.blogspot.com/search/label/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] Fwd: Copy out wording
Magnus Hagander wrote: Our documentation for COPY (http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the following to say: The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL's COPY handles this by quoting. A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. Therefore, using the default settings, a NULL is written as an unquoted empty string, while an empty string is written with double quotes (). Reading values follows similar rules. You can use FORCE NOT NULL to prevent NULL input comparisons for specific columns. Shouldn't that be: A NULL is output as the NULL string and is not quoted, while a data value matching the empty string is quoted? If not, then what really is the difference between a NULL and a NULL string? No, it shouldn't. Let's say NULL is represented as foo. Then a null between delimiters will be written as delimiter foo delimiter while the string foo will be delimiter quotechar foo quotechar delimiter and an empty non-null string will be delimiter delimiter unless you have FORCE QUOTE on for it, in which case it will be delimiter quotechar quotechar delimiter We had quite a bit of debate on the shape of CSV output at the time it was done (during 8.0), and that's what we came up with. It has the useful property that we can round-trip the data, i.e. we can read back the data we output without losing information about nulls, no matter what the NULL string is, something we have always been resistant to changing. If you think we could explain it better, by all means have a go at it. But your proposed change isn't accurate. Here is an illustration of the above: andrew=# copy (values (1, 'foo', 2),(3,null,4),(5,'',6 ) ) to stdout null 'foo' csv header; column1,column2,column3 1,foo,2 3,foo,4 5,,6 andrew=# copy (values (1, 'foo', 2),(3,null,4),(5,'',6 ) ) to stdout null 'foo' csv header force quote column2; column1,column2,column3 1,foo,2 3,foo,4 5,,6 HTH 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] Fwd: Copy out wording
On Thu, Sep 3, 2009 at 13:19, Andrew Dunstanand...@dunslane.net wrote: Magnus Hagander wrote: Our documentation for COPY (http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the following to say: The CSV format has no standard way to distinguish a NULL value from an empty string. PostgreSQL's COPY handles this by quoting. A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. Therefore, using the default settings, a NULL is written as an unquoted empty string, while an empty string is written with double quotes (). Reading values follows similar rules. You can use FORCE NOT NULL to prevent NULL input comparisons for specific columns. Shouldn't that be: A NULL is output as the NULL string and is not quoted, while a data value matching the empty string is quoted? If not, then what really is the difference between a NULL and a NULL string? No, it shouldn't. Let's say NULL is represented as foo. Then a null between delimiters will be written as delimiter foo delimiter while the string foo will be delimiter quotechar foo quotechar delimiter and an empty non-null string will be delimiter delimiter unless you have FORCE QUOTE on for it, in which case it will be delimiter quotechar quotechar delimiter We had quite a bit of debate on the shape of CSV output at the time it was done (during 8.0), and that's what we came up with. It has the useful property that we can round-trip the data, i.e. we can read back the data we output without losing information about nulls, no matter what the NULL string is, something we have always been resistant to changing. If you think we could explain it better, by all means have a go at it. But your proposed change isn't accurate. Here is an illustration of the above: Oh, hang on, the NULL string refers to the copy parameter? Not a part of the data? I read it as a string being NULL. Maybe something like the value of the NULL string parameter to be overly clear for people like me? :-) (FWIW, I totally agree with the feature, I was just confused by the docs) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Fwd: Copy out wording
Magnus Hagander wrote: Oh, hang on, the NULL string refers to the copy parameter? Not a part of the data? I read it as a string being NULL. Maybe something like the value of the NULL string parameter to be overly clear for people like me? :-) We could change: A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. to A NULL is output as the NULL parameter and is not quoted, while a non-NULL data value whose text representation matches the NULL parameter is quoted. or something similar. Would that be better? 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] Fwd: Copy out wording
On Thu, Sep 3, 2009 at 13:31, Andrew Dunstanand...@dunslane.net wrote: Magnus Hagander wrote: Oh, hang on, the NULL string refers to the copy parameter? Not a part of the data? I read it as a string being NULL. Maybe something like the value of the NULL string parameter to be overly clear for people like me? :-) We could change: A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. to A NULL is output as the NULL parameter and is not quoted, while a non-NULL data value whose text representation matches the NULL parameter is quoted. or something similar. Would that be better? Yes, much better IMO. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Triggers on columns
On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote: I'd like to check conditions by comparing actual values but not a target of UPDATE statement because I think almost user expects the former behavior. Unmodified UPDATE-targets are common case if we use a framework that generates SQL statements internally. The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. -- 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] Triggers on columns
On Sep 3, 2009, at 7:44 AM, Peter Eisentraut pete...@gmx.net wrote: On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote: I'd like to check conditions by comparing actual values but not a target of UPDATE statement because I think almost user expects the former behavior. Unmodified UPDATE-targets are common case if we use a framework that generates SQL statements internally. The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] suggestion to improve planer
Hi. I hope, that this is right mailing list. SELECT date, value FROM t_event WHERE t_event.id in (SELECT id FROM t_event WHERE date '2009-08-25' ORDER BY date DESC LIMIT 1) ORDER BY date; cost 6.4 SELECT date, value FROM t_event WHERE t_event.id = (SELECT id FROM t_event WHERE date '2009-08-25' ORDER BY date DESC LIMIT 1) ORDER BY date; cost 6.36..6.37 Why that two query dont have equal cost? If it is not problem, try add some planer code to recognize that sublesect HAVE TO return just one row (limit 1) and in plan could be used filter/index scan instead of hash aggregate. I have also some complex query examples where cost difference is more visible. Have a nice day. -- Odborník na všetko je zlý odborník. Ja sa snažím byť výnimkou potvrdzujúcou pravidlo. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ECPG patchset
Hi,
we have updated our patchset to current 8.5 CVS.
The actual patches will be in emails coming as
answers to this one. As two patches were already included,
the remaining patches are as follows:
1. dynamic cursorname
2. sqlda support
3. describe support
4. proper out-of-scope declare/open/fetch for cursors in compat mode
Changes (the numbers indicate the patch it was made in):
1. ECPG auto-generated grammar couldn't deal with
grammar objects having both an addon and a rule
extension, emitting an extra '{' between the addon
and the original rule code blocks. Fix was needed for parse.pl
and now the grammar looks nicer, my previous question was
solved by it.
2. No more ecpg_compare_sqlda_with_PGresult() function, it triggered
most of the time anyway, it's cheaper to unconditionally free() and
malloc().
No more realloc(), it ruined the internal pointers inside the sqlda
structure.
A small leak fix for FETCH when a decimal or numeric was used:
diff -durpN pgsql.dyncursor/src/interfaces/ecpg/ecpglib/data.c
pgsql.sqlda/src/interfaces/ecpg/ecpglib/data.c
--- pgsql.dyncursor/src/interfaces/ecpg/ecpglib/data.c 2009-08-08
17:19:45.0 +0200
+++ pgsql.sqlda/src/interfaces/ecpg/ecpglib/data.c 2009-09-03
12:56:36.0 +0200
@@ -554,7 +554,7 @@ ecpg_get_data(const PGresult *results, i
else
PGTYPESnumeric_to_decimal(nres, (decimal *) (var + offset * act_tuple));
- free(nres);
+ PGTYPESnumeric_free(nres);
break;
case ECPGt_interval:
Now sqlda usage is valgrind-clean.
We added the possibility to extend SQLDA for multi-row FETCH,
using the -desc_next pointer, but not implemented. Multi-row
FETCH is not available in Informix.
3. Only updated to current CVS.
4. Fixed uninitialized pointers that were discovered by valgrind and
were causing problems on HP-UX. (Small malloc()s seem to be
zeroed out by Fedora 9's glibc, but not on HP-UX.)
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/
--
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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan írta: Alvaro Herrera írta: Boszormenyi Zoltan wrote: The vague consensus for syntax options was that the GUC 'lock_timeout' and WAIT [N] extension (wherever NOWAIT is allowed) both should be implemented. Behaviour would be that N seconds timeout should be applied to every lock that the statement would take. In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us Tom argues that lock_timeout should be sufficient. I'm not sure what does WAIT [N] buy Okay, we implemented only the lock_timeout GUC. Patch attached, hopefully in an acceptable form. Documentation included in the patch, lock_timeout works the same way as statement_timeout, takes value in milliseconds and 0 disables the timeout. Best regards, Zoltán Böszörményi New patch attached. It's only regenerated for current CVS so it should apply cleanly. -- 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 pgsql.ooscur/doc/src/sgml/config.sgml pgsql.locktimeout/doc/src/sgml/config.sgml *** pgsql.ooscur/doc/src/sgml/config.sgml 2009-08-26 10:19:48.0 +0200 --- pgsql.locktimeout/doc/src/sgml/config.sgml 2009-09-03 15:41:34.0 +0200 *** COPY postgres_log FROM '/full/path/to/lo *** 4028,4033 --- 4028,4056 /listitem /varlistentry + varlistentry id=guc-lock-timeout xreflabel=lock_timeout + termvarnamelock_timeout/varname (typeinteger/type)/term + indexterm +primaryvarnamelock_timeout/ configuration parameter/primary + /indexterm + listitem +para + Abort any statement that tries to lock any rows or tables and the lock + has to wait more than the specified number of milliseconds, starting + from the time the command arrives at the server from the client. + If varnamelog_min_error_statement/ is set to literalERROR/ or + lower, the statement that timed out will also be logged. + A value of zero (the default) turns off the limitation. +/para + +para + Setting varnamelock_timeout/ in + filenamepostgresql.conf/ is not recommended because it + affects all sessions. +/para + /listitem + /varlistentry + varlistentry id=guc-vacuum-freeze-table-age xreflabel=vacuum_freeze_table_age termvarnamevacuum_freeze_table_age/varname (typeinteger/type)/term indexterm diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/lock.sgml pgsql.locktimeout/doc/src/sgml/ref/lock.sgml *** pgsql.ooscur/doc/src/sgml/ref/lock.sgml 2009-01-16 11:44:56.0 +0100 --- pgsql.locktimeout/doc/src/sgml/ref/lock.sgml 2009-09-03 15:41:34.0 +0200 *** where replaceable class=PARAMETERloc *** 39,46 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. Once obtained, the lock is held for the !remainder of the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para --- 39,49 literalNOWAIT/literal is specified, commandLOCK TABLE/command does not wait to acquire the desired lock: if it cannot be acquired immediately, the command is aborted and an !error is emitted. If varnamelock_timeout/varname is set to a value !higher than 0, and the lock cannot be acquired under the specified !timeout value in milliseconds, the command is aborted and an error !is emitted. Once obtained, the lock is held for the remainder of !the current transaction. (There is no commandUNLOCK TABLE/command command; locks are always released at transaction end.) /para diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/select.sgml pgsql.locktimeout/doc/src/sgml/ref/select.sgml *** pgsql.ooscur/doc/src/sgml/ref/select.sgml 2009-08-31 12:55:43.0 +0200 --- pgsql.locktimeout/doc/src/sgml/ref/select.sgml 2009-09-03 15:41:34.0 +0200 *** FOR SHARE [ OF replaceable class=param *** 1109,1114 --- 1109,1122 /para para + If literalNOWAIT/ option is not specified and varnamelock_timeout/varname + is set to a value higher than 0, and the lock needs to wait more than + the specified value in milliseconds, the command reports an error after + timing out, rather than waiting indefinitely. The note in the previous + paragraph applies to the varnamelock_timeout/varname, too. +/para + +para literalFOR SHARE/literal behaves
Re: [HACKERS] Triggers on columns
On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: On Sep 3, 2009, at 7:44 AM, Peter Eisentraut pete...@gmx.net wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF all columns then it makes some sense. -- 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] Feature request: DEFAULT as input value of function argument
Hello defaults are supported in 8.4 regards Pavel Stehule 2009/9/3 Sergey Konoplev gray...@gmail.com: IMHO convenient solution is to make possible to specify something like COLUMN_DEFAULT as input value of function. I wonder if it's possible. So, what do you think of it? -- Regards, Sergey Konoplev -- PostgreSQL articles in english russian http://gray-hemp.blogspot.com/search/label/postgresql/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gcc versus division-by-zero traps
We have seen several previous reports of regression test failures
due to division by zero causing SIGFPE, even though the code
should never reach the division command:
http://archives.postgresql.org/pgsql-bugs/2006-11/msg00180.php
http://archives.postgresql.org/pgsql-bugs/2007-11/msg00032.php
http://archives.postgresql.org/pgsql-bugs/2008-05/msg00148.php
http://archives.postgresql.org/pgsql-general/2009-05/msg00774.php
It's always been on non-mainstream architectures so it was hard
to investigate. But I have finally been able to reproduce this:
https://bugzilla.redhat.com/show_bug.cgi?id=520916
While s390x is still not quite mainstream, at least I can get
access to one ;-). What turns out to be the case is that
simple test cases like
if (y == 0)
single_function_call(...);
z = x / y;
do not show the problem; you need something pretty complex in the
if-command. Like, say, an ereport() construct. So that's why the gcc
boys haven't already had visits from mobs of villagers about this.
I hope that the bug will get fixed in due course, but even if they
respond pretty quickly it will be years before the problem disappears
from every copy of gcc in the field. So I'm thinking that it would
behoove us to install a workaround, now that we've characterized the
problem sufficiently. What I am thinking is that in the three
functions known to exhibit the bug (int24div, int28div, int48div)
we should do something like this:
if (arg2 == 0)
+ {
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg(division by zero)));
+ /* ensure compiler realizes we don't reach the division */
+ PG_RETURN_NULL();
+ }
/* No overflow is possible */
PG_RETURN_INT64((int64) arg1 / arg2);
Thoughts?
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] Triggers on columns
On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentrautpete...@gmx.net wrote: On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: On Sep 3, 2009, at 7:44 AM, Peter Eisentraut pete...@gmx.net wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF all columns then it makes some sense. Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. ...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] Concurrent execution of pg_relation_size and DROP TABLE
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Is it reasonable to replace relation_open() calls to try_relation_open() ? I don't think so; that's just papering over one form of the problem, and who's to say that an error is not desirable when a wrong OID is given? In general there's always a risk of concurrency problems. 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] Triggers on columns
On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote: If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. Yeah, probably. I didn't make this up; I'm just reading the standard. ;-) But of course you can already do what you do, so you don't lose anything if it turns out that this proposed feature ends up working the other way. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... Well, it might make sense to make this information available within the trigger function through new variables. -- 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] Triggers on columns
Robert Haas robertmh...@gmail.com wrote: It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... Sybase provides an if update(columnname) syntax to allow such tests. Perhaps PostgreSQL could do something similar? Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. Yes. It's a less frequent need, but it does exist. The thing is, if you only fire triggers if something was actually changed to a new value, you can't get to that. If you fire on all updates you can test whether there were actual changes. Of course, ideally, both would be convenient. -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] Triggers on columns
Hi, Robert Haas robertmh...@gmail.com writes: By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. http://www.postgresql.org/docs/8.4/static/functions-trigger.html Currently PostgreSQL provides one built in trigger function, suppress_redundant_updates_trigger, which will prevent any update that does not actually change the data in the row from taking place, in contrast to the normal behaviour which always performs the update regardless of whether or not the data has changed. (This normal behaviour makes updates run faster, since no checking is required, and is also useful in certain cases.) ... The suppress_redundant_updates_trigger function can be added to a table like this: CREATE TRIGGER z_min_update BEFORE UPDATE ON tablename FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Triggers on columns
Robert Haas wrote: On Wed, Sep 2, 2009 at 9:52 PM, Itagaki Takahiroitagaki.takah...@oss.ntt.co.jp wrote: Here is a patch to implement Support triggers on columns in our ToDo list. The syntax is: CREATE TRIGGER name BEFORE UPDATE OF col1, col12, ... ON tbl FOR EACH ROW EXECUTE PROCEDURE func(); I consulted the previous work following: Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04) http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php and completed some under-construction parts. It's still arguable that we should add dependencies from column triggers to referenced columns. In the present patch, dropeed columns are just ignored and always considered as not-modified. Please grep with TODO: (TRIGGER) to check the issue. Comments welcome. Wow, so I wouldn't have to do this any more? IF (TG_OP = 'UPDATE') THEN IF (OLD.foo IS NOT DISTINCT FROM NEW.foo AND OLD.bar IS NOT DISTINCT FROM NEW.bar AND OLD.baz IS NOT DISTINCT FROM NEW.baz) THEN RETURN NULL; END IF; END IF; Apart from any possible gain in efficiency, the sheer savings in typing sound quite awesome. You could make it nicer with something like: row(new.foo,new.bar,new.baz) is distinct from row(old.foo,old.bar,old.baz) couldn't you? I'm actually having trouble thinking of a case where I'd find this feature very useful. 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] Triggers on columns
On Thu, Sep 3, 2009 at 10:37 AM, Peter Eisentrautpete...@gmx.net wrote: On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote: If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. Yeah, probably. I didn't make this up; I'm just reading the standard. ;-) But of course you can already do what you do, so you don't lose anything if it turns out that this proposed feature ends up working the other way. Sure, but I don't think it makes a lot of sense to spend a lot of time implementing the standard behavior unless someone can provide a plausible use case. If that means we have to give our non-standard feature an incompatible syntax or whatever so as not to create confusion with the standard behavior, then let's do that, because it sounds WAY more useful. ...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] community decision-making 8.5
On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: Isn't core supposed to be the release manager? The core team has historically been the release *maker* and has some done management of the final phases of that process. But I think the sentiment is growing that we need more management throughout the entire release cycle. O.k. so a release team. Cool. I am assuming the team would be more directed toward upcoming major release versus minor releases to past revisions. We already pretty much have that under control between -core and -packagers. Yes? Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drakej...@commandprompt.com wrote: On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: Isn't core supposed to be the release manager? The core team has historically been the release *maker* and has some done management of the final phases of that process. But I think the sentiment is growing that we need more management throughout the entire release cycle. O.k. so a release team. Cool. I am assuming the team would be more directed toward upcoming major release versus minor releases to past revisions. We already pretty much have that under control between -core and -packagers. Yes? +1. ...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] community decision-making 8.5
On Thu, 2009-09-03 at 11:41 -0400, Robert Haas wrote: On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drakej...@commandprompt.com wrote: On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: Isn't core supposed to be the release manager? The core team has historically been the release *maker* and has some done management of the final phases of that process. But I think the sentiment is growing that we need more management throughout the entire release cycle. O.k. so a release team. Cool. I am assuming the team would be more directed toward upcoming major release versus minor releases to past revisions. We already pretty much have that under control between -core and -packagers. Yes? +1. O.k. so the second part of this, is I feel it should contain a majority of people who are not already being slammed into the ground by community work. E.g; let's get some fresh blood. It is certainly important to have a couple of long standing contributors involved but we have some people that have cropped up recently (relatively) within our community that could probably be overworked a bit more ;) Joshua D. Drake /me pokes Robert Haas and Kevin Grittner ...Robert -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 11:55 AM, Joshua D. Drakej...@commandprompt.com wrote: On Thu, 2009-09-03 at 11:41 -0400, Robert Haas wrote: On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drakej...@commandprompt.com wrote: On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: Isn't core supposed to be the release manager? The core team has historically been the release *maker* and has some done management of the final phases of that process. But I think the sentiment is growing that we need more management throughout the entire release cycle. O.k. so a release team. Cool. I am assuming the team would be more directed toward upcoming major release versus minor releases to past revisions. We already pretty much have that under control between -core and -packagers. Yes? +1. O.k. so the second part of this, is I feel it should contain a majority of people who are not already being slammed into the ground by community work. E.g; let's get some fresh blood. It is certainly important to have a couple of long standing contributors involved but we have some people that have cropped up recently (relatively) within our community that could probably be overworked a bit more ;) Joshua D. Drake /me pokes Robert Haas and Kevin Grittner Yeah, I'm game, though I'm hoping not to become the guy who spends all his time doing release planning, because I like writing code, too. Hopefully Selena won't mind my mentioning that she sent me a private email expressing some interest in this area, too. And /me pokes Brendan Jurd. :-) ...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] community decision-making 8.5
On Thu, 2009-09-03 at 12:00 -0400, Robert Haas wrote: O.k. so the second part of this, is I feel it should contain a majority of people who are not already being slammed into the ground by community work. E.g; let's get some fresh blood. It is certainly important to have a couple of long standing contributors involved but we have some people that have cropped up recently (relatively) within our community that could probably be overworked a bit more ;) Joshua D. Drake /me pokes Robert Haas and Kevin Grittner Yeah, I'm game, though I'm hoping not to become the guy who spends all his time doing release planning, because I like writing code, too. Of course. Hopefully Selena won't mind my mentioning that she sent me a private email expressing some interest in this area, too. She would definitely be a good option if she has time. I know that I would be interested and I would like to see at least one long time -hacker on board. And /me pokes Brendan Jurd. :-) Hah! I almost listed him. /me adds a poke to Brendan Jurd. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] community decision-making 8.5
Joshua D. Drake j...@commandprompt.com wrote: /me pokes Robert Haas and Kevin Grittner I'm honored to be suggested for such a role. I'm happy to do what I can, but am reluctant to put myself too squarely in any critical path, as I have responsibility for dealing with some family health issues which make unpredictable demands on my free time and energy. If things can be arranged so that what I *can* do contributes, but my absence at times does no harm, I'm happy to help. Robert Haas is obviously an outstanding suggestion. -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] community decision-making 8.5
On Thu, Sep 3, 2009 at 9:00 AM, Robert Haasrobertmh...@gmail.com wrote: Yeah, I'm game, though I'm hoping not to become the guy who spends all his time doing release planning, because I like writing code, too. Hopefully Selena won't mind my mentioning that she sent me a private email expressing some interest in this area, too. Not at all! My schedule is largely open this fall and winter, and I have a patch or two that I aught to finish soonish as well. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] Triggers on columns
On Thu, 3 Sep 2009, Robert Haas wrote: On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentrautpete...@gmx.net wrote: On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: On Sep 3, 2009, at 7:44 AM, Peter Eisentraut pete...@gmx.net wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF all columns then it makes some sense. Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP as instance. ...Robert -- Guillaume (ioguix) de Rorthais -- 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] gcc versus division-by-zero traps
On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote:
We have seen several previous reports of regression test failures
due to division by zero causing SIGFPE, even though the code should
never reach the division command:
http://archives.postgresql.org/pgsql-bugs/2006-11/msg00180.php
http://archives.postgresql.org/pgsql-bugs/2007-11/msg00032.php
http://archives.postgresql.org/pgsql-bugs/2008-05/msg00148.php
http://archives.postgresql.org/pgsql-general/2009-05/msg00774.php
It's always been on non-mainstream architectures so it was hard to
investigate. But I have finally been able to reproduce this:
https://bugzilla.redhat.com/show_bug.cgi?id=520916
While s390x is still not quite mainstream, at least I can get
access to one ;-).
Do you also have access to z/OS with Unix System Services? IBM's
compiler, c89, is amazingly strict, and should help us flush out bugs. :)
What turns out to be the case is that
simple test cases like
if (y == 0)
single_function_call(...);
z = x / y;
do not show the problem; you need something pretty complex in the
if-command. Like, say, an ereport() construct. So that's why the gcc
boys haven't already had visits from mobs of villagers about this.
I hope that the bug will get fixed in due course, but even if they
respond pretty quickly it will be years before the problem disappears
from every copy of gcc in the field. So I'm thinking that it would
behoove us to install a workaround, now that we've characterized the
problem sufficiently. What I am thinking is that in the three
functions known to exhibit the bug (int24div, int28div, int48div)
we should do something like this:
if (arg2 == 0)
+ {
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg(division by zero)));
+ /* ensure compiler realizes we don't reach the division */
+ PG_RETURN_NULL();
+ }
/* No overflow is possible */
PG_RETURN_INT64((int64) arg1 / arg2);
Thoughts?
How big would this change be? How would people know to use that
construct everywhere it's appropriate?
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] Triggers on columns
iog...@free.fr escribió: A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP as instance. No, because you want to update the timestamp in all cases, whatever columns the update actually updates. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] community decision-making 8.5
Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. So I think it would make sense for you guys to do Alpha2. Agreed, Peter? -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] Commitfest Code Sprint with PUGs
Webb, Selena, Gabrielle, September 15th is coming up soon. Will PDXPUG be interested in doing a CommitFest Sprint? When can you do it? Let me know, because we'll want to have the sprinters claim patches early. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] community decision-making 8.5
On Thu, 2009-09-03 at 10:21 -0700, Josh Berkus wrote: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I think we need at least one long term contributor or core member to participate in this. Peter would you be up for a Liaison to Core and active release person? Joshua D. Drake -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 1:21 PM, Josh Berkusj...@agliodbs.com wrote: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I have no interest in that. ...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] gcc versus division-by-zero traps
David Fetter da...@fetter.org writes: On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote: While s390x is still not quite mainstream, at least I can get access to one ;-). Do you also have access to z/OS with Unix System Services? No, Red Hat's machines run RHEL ;-) What I am thinking is that in the three functions known to exhibit the bug (int24div, int28div, int48div) we should do something like this: How big would this change be? How would people know to use that construct everywhere it's appropriate? I'm talking about patching exactly those three functions. We don't have any reports of trouble elsewhere. The long-term fix is in the compiler anyway, this is just a workaround for currently-known issues. Part of my motivation for this is to get rid of an existing hack in the Red Hat RPMs: # use -O1 on sparc64 and alpha %ifarch sparc64 alpha CFLAGS=`echo $CFLAGS| sed -e s|-O2|-O1|g ` %endif I believe that that is only there to prevent exactly this problem. Anyway I'd like to try removing it after patching as proposed, and then we'll find out if there are other trouble spots ... 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 10:21 AM, Josh Berkusj...@agliodbs.com wrote: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I'm up for it! :) Like Robert as expressed on other management roles, I am not interested in doing it forever. I am certainly up for being part of a rotating team, and documenting/automating things as much as possible. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] community decision-making 8.5
Josh Berkus j...@agliodbs.com wrote: So I think it would make sense for you guys to do Alpha2. I'm not really clear on what that means. I'm assuming that part of the goal is for us to become more intimately familiar with the details of putting together a release, documenting the process, and suggesting possible process improvements. But I think I need a little more of a broad outline, at least, of what would fall to this team. Are we talking about taking things from the end of the CF up to the point of publishing the alpha release files? We would try to cover, to the extent we can, the jobs normally done in a major release by Tom, Bruce, and others? (Partly to free them to do other work during the alpha release preparation?) Or am I totally off base in what you're talking about? -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 Code Sprint with PUGs
The CommitFest is scheduled to start 9/15, so doing this on 9/8 might be a bit too soon. I wouldn't object to doing it a few days before the start of the CommitFest to flush out any patches with obvious problems, but I think a week ahead of time is too much. Yeah, I meant Tues 9/15. Also, I don't think anything is going to committed as a result of this - the goal is to post reviewers to pgsql-hackers. Sounds good. ...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] Commitfest Code Sprint with PUGs
On Thu, Sep 3, 2009 at 1:54 PM, Webb Spraguewebb.spra...@gmail.com wrote: Hi Josh et al, I believe we are all still interested (Selena? Gabrielle?) How about this: 6:00 Tuesday evening (Pacific), the three of us (and anybody else) agree to be around a table with laptops on, cellphones ready, listening at an IRC channel. Could you assign us good patches before then (like 10)? And then we commit, commit, commit. I think we should think of this as a dry run where we iron out the details, and then maybe the following saturday do it again on a larger scale? The CommitFest is scheduled to start 9/15, so doing this on 9/8 might be a bit too soon. I wouldn't object to doing it a few days before the start of the CommitFest to flush out any patches with obvious problems, but I think a week ahead of time is too much. Also, I don't think anything is going to committed as a result of this - the goal is to post reviewers to pgsql-hackers. ...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] Commitfest Code Sprint with PUGs
Hi Josh et al, I believe we are all still interested (Selena? Gabrielle?) How about this: 6:00 Tuesday evening (Pacific), the three of us (and anybody else) agree to be around a table with laptops on, cellphones ready, listening at an IRC channel. Could you assign us good patches before then (like 10)? And then we commit, commit, commit. I think we should think of this as a dry run where we iron out the details, and then maybe the following saturday do it again on a larger scale? -W On Thu, Sep 3, 2009 at 10:23 AM, Josh Berkusj...@agliodbs.com wrote: Webb, Selena, Gabrielle, September 15th is coming up soon. Will PDXPUG be interested in doing a CommitFest Sprint? When can you do it? Let me know, because we'll want to have the sprinters claim patches early. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] gcc versus division-by-zero traps
On Thu, Sep 03, 2009 at 01:26:52PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote: While s390x is still not quite mainstream, at least I can get access to one ;-). Do you also have access to z/OS with Unix System Services? No, Red Hat's machines run RHEL ;-) I'm given to understand it's possible to run both on the same hardware. 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] Triggers on columns
On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: Sure, but I don't think it makes a lot of sense to spend a lot of time implementing the standard behavior unless someone can provide a plausible use case. One use case is porting Oracle applications. I see a lot of that used there. The original proposer might had have other ideas. -- 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] Triggers on columns
On Thu, Sep 3, 2009 at 2:16 PM, Peter Eisentrautpete...@gmx.net wrote: On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: Sure, but I don't think it makes a lot of sense to spend a lot of time implementing the standard behavior unless someone can provide a plausible use case. One use case is porting Oracle applications. I see a lot of that used there. The original proposer might had have other ideas. Perhaps so, but his second post to the thread suggests that he didn't have the interpretation you're proposing in mind. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby, max_connections and max_prepared_transactions
We discussed earlier that HS should continue to work even if max_connections was set differently on the primary and the standby. This now gives a situation where snapshots can be allowed, then disallowed for a while, then allowed again. Complication is that this will cause some connections to fail since we take a snapshot in postinit.c. (That is the part I just noticed in my self-review). Some queries will also fail. Sometimes, not all the time. This makes both behaviour and coding more complicated and my feeling is that if we are aiming for simplicity in all areas we should remove this. Currently max_prepared_transactions needs to be set correctly in recovery also, so this complex coding doesn't actually remove the need to set some parameters correctly. Not many people change them from the default in the first place, so I don't think its a big deal. And most people use the same postgresql.conf on the standby anyway. I propose we just accept that both max_connections and max_prepared_transactions need to be set correctly for recovery to work. This will make the state transitions more robust and it will avoid spurious and hard to test error messages. Any objections to me removing this slice of code from the patch? -- Simon Riggs www.2ndQuadrant.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] Commitfest Code Sprint with PUGs
On Thu, Sep 3, 2009 at 11:02 AM, Webb Spraguewebb.spra...@gmail.com wrote: The CommitFest is scheduled to start 9/15, so doing this on 9/8 might be a bit too soon. I wouldn't object to doing it a few days before the start of the CommitFest to flush out any patches with obvious problems, but I think a week ahead of time is too much. Yeah, I meant Tues 9/15. Also, I don't think anything is going to committed as a result of this - the goal is to post reviewers to pgsql-hackers. Sounds good. +1 -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] community decision-making 8.5
Josh Berkus j...@agliodbs.com writes: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. Uh, what's the point of that? The existing core team has that process perfectly well in hand. What I thought this discussion was about was putting more effort into long-range project planning/management. 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 2:27 PM, Tom Lanet...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. Uh, what's the point of that? The existing core team has that process perfectly well in hand. What I thought this discussion was about was putting more effort into long-range project planning/management. Exactly. I don't see much value in investing a lot of time and effort in something that's already working well. If someone has a complaint about the way the process for packaging and bundling releases is working, then let's hear it (on a separate thread!), but all of the discussions that we've had on this thread are about making sure that we set time lines and expectations early, stick to them, and resolve issues that come up in a timely fashion. Those are basically all process issues, and I don't see much value in throwing one technical task into the mix. ...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] Triggers on columns
Peter Eisentraut pete...@gmx.net writes: On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: Sure, but I don't think it makes a lot of sense to spend a lot of time implementing the standard behavior unless someone can provide a plausible use case. One use case is porting Oracle applications. I see a lot of that used there. The original proposer might had have other ideas. That's only a good argument if we are prepared to implement exactly Oracle's semantics for the feature ... which, frankly, I have no reason whatever to assume are exactly like the standard's :-( 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] community decision-making 8.5
On Thu, Sep 3, 2009 at 11:27 AM, Tom Lanet...@sss.pgh.pa.us wrote: Josh Berkus j...@agliodbs.com writes: Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. Uh, what's the point of that? The existing core team has that process perfectly well in hand. What I thought this discussion was about was putting more effort into long-range project planning/management. Ok! I was volunteering for work, not suggesting a process was broken. There's plenty to do around commitfest, so I'll stick to taking tasks there! -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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 Code Sprint with PUGs
On Thu, Sep 3, 2009 at 10:58 AM, Robert Haasrobertmh...@gmail.com wrote: On Thu, Sep 3, 2009 at 1:54 PM, Webb Spraguewebb.spra...@gmail.com wrote: Hi Josh et al, I believe we are all still interested (Selena? Gabrielle?) Heck yes! How about this: 6:00 Tuesday evening (Pacific), the three of us (and anybody else) agree to be around a table with laptops on, cellphones ready, listening at an IRC channel. Could you assign us good patches before then (like 10)? And then we commit, commit, commit. Sounds good, already have at least someone else from #pdxpug interested too. :) Just need to pick a location. Anybody have a quiet house? I think we should think of this as a dry run where we iron out the details, and then maybe the following saturday do it again on a larger scale? Practice run was my expectation as well. Making both dates might be problematic for some people (:ahem: me) but we should have enough attendee overlap to deal with that. gabrielle -- 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] Hot Standby, max_connections and max_prepared_transactions
Simon Riggs wrote: I propose we just accept that both max_connections and max_prepared_transactions need to be set correctly for recovery to work. This will make the state transitions more robust and it will avoid spurious and hard to test error messages. Any objections to me removing this slice of code from the patch? Umm, what slice of code? I don't recall any code trying to make it work. -- 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] Commitfest Code Sprint with PUGs
How about this: 6:00 Tuesday evening (Pacific), the three of us (and anybody else) agree to be around a table with laptops on, cellphones ready, listening at an IRC channel. Could you assign us good patches before then (like 10)? And then we commit, commit, commit. Sounds good, already have at least someone else from #pdxpug interested too. :) Just need to pick a location. Anybody have a quiet house? I can probably finagle a room here at PSU, though I would love it if someone would volunteer a house. I think we should think of this as a dry run where we iron out the details, and then maybe the following saturday do it again on a larger scale? Practice run was my expectation as well. Making both dates might be problematic for some people (:ahem: me) but we should have enough attendee overlap to deal with that. OK, let's play followups by ear, but consider Tues Sept 15 as the date? -- 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] Hot Standby, max_connections and max_prepared_transactions
On Thu, 2009-09-03 at 22:22 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: I propose we just accept that both max_connections and max_prepared_transactions need to be set correctly for recovery to work. This will make the state transitions more robust and it will avoid spurious and hard to test error messages. Any objections to me removing this slice of code from the patch? Umm, what slice of code? I don't recall any code trying to make it work. Well, its there. Perhaps the full functionality has been clipped in recent changes, but there are still unwanted ramifications in the design that I think would be best to remove. No loss of functionality, just HS won't activate unless max_connections is set = value on primary. Since max_prepared_transactions already has this same problem in the current code I see no reason to fuss. We can always put in more flexible code later. State change code in StartupXLog(), Snapshots disabled code in GetSnapshotData(), ProcArray-allowStandbySnapshots etc.. -- Simon Riggs www.2ndQuadrant.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] remove flatfiles.c
On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Wed, Sep 2, 2009 at 12:01 AM, Alvaro Herreraalvhe...@commandprompt.com wrote: The use cases where VACUUM FULL wins currently are where storing two copies of the table and its indexes concurrently just isn't practical. Yeah, but then do you really need to use VACUUM FULL? If that's really a problem then there ain't that many dead tuples around. That's what I want to believe. But picture if you have, say a 1-terabyte table which is 50% dead tuples and you don't have a spare 1-terabytes to rewrite the whole table. But trying to VACUUM FULL that table is going to be horridly painful too, and you'll still have bloated indexes afterwards. You might as well just live with the 50% waste, especially since if you did a full-table update once you'll probably do it again sometime. I'm having a hard time believing that VACUUM FULL really has any interesting use-case anymore. I have a client who uses temp tables heavily, hundreds of thousands of creates and drops per day. They also have long running queries. The only thing that keeps catalog bloat somewhat in check is vacuum full on bloated catalogs a few times a day. Without that pg_class, pg_attribute etc quickly balloon to thousands of pages. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] remove flatfiles.c
daveg wrote: On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: Greg Stark gsst...@mit.edu writes: On Wed, Sep 2, 2009 at 12:01 AM, Alvaro Herreraalvhe...@commandprompt.com wrote: The use cases where VACUUM FULL wins currently are where storing two copies of the table and its indexes concurrently just isn't practical. Yeah, but then do you really need to use VACUUM FULL? If that's really a problem then there ain't that many dead tuples around. That's what I want to believe. But picture if you have, say a 1-terabyte table which is 50% dead tuples and you don't have a spare 1-terabytes to rewrite the whole table. But trying to VACUUM FULL that table is going to be horridly painful too, and you'll still have bloated indexes afterwards. You might as well just live with the 50% waste, especially since if you did a full-table update once you'll probably do it again sometime. I'm having a hard time believing that VACUUM FULL really has any interesting use-case anymore. I have a client who uses temp tables heavily, hundreds of thousands of creates and drops per day. They also have long running queries. The only thing that keeps catalog bloat somewhat in check is vacuum full on bloated catalogs a few times a day. Without that pg_class, pg_attribute etc quickly balloon to thousands of pages. That's a rate of more than one create and drop per second. How does your client handle the fact that VACUUM FULL will exclusively lock those catalog tables? Without knowing more, it looks like a bit of a design issue. 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] remove flatfiles.c
On Thu, Sep 03, 2009 at 07:57:25PM -0400, Andrew Dunstan wrote: daveg wrote: On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: I'm having a hard time believing that VACUUM FULL really has any interesting use-case anymore. I have a client who uses temp tables heavily, hundreds of thousands of creates and drops per day. They also have long running queries. The only thing that keeps catalog bloat somewhat in check is vacuum full on bloated catalogs a few times a day. Without that pg_class, pg_attribute etc quickly balloon to thousands of pages. That's a rate of more than one create and drop per second. How does your client handle the fact that VACUUM FULL will exclusively lock those catalog tables? Without knowing more, it looks like a bit of a design issue. I'd say it is several per second. They wait for the catalog locks sometimes. This is not an interactive application so that is somewhat acceptable. It also occasionally causes deadlocks which is less agreeable. There are various reasons for the heavy use of temps, mainly having to do with loading external feeds or reusing intermediate query results in a series of queries. It would be great if there was a way to have temp tables that did not get cataloged, eg local cache only. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] community decision-making 8.5
* Joshua D. Drake (j...@commandprompt.com) wrote: She would definitely be a good option if she has time. I know that I would be interested and I would like to see at least one long time -hacker on board. I don't presume to be a long time -hacker, but I'm interested in what I can do to help with this. I like the general approach. I'm also up for being involved in 'managing' beta testers. Not sure if that role would be appropriate for this group, though it seems like it'd be a natural fit as it's directly related to the is the release ready question.. Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Elementary dependency look-up
Attached is a patch to add a couple basic dependency look-up capability
functions. They're based off the pg_get_serial_sequence function, and
are kind of the inverse of that function in some respects.
The patch adds two new functions to the backend, pg_get_owner_object and
pg_get_owner_column. These look up the requested object in the
pg_depend table, looking for an 'a' type dependency to another relation,
and resolve either the relation or column names to text.
postgres=# SELECT pg_get_owner_object('tbl_id_seq') AS obj,
postgres-# pg_get_owner_column('tbl_id_seq') AS col,
postgres-# pg_get_serial_sequence(
postgres(# pg_get_owner_object('tbl_id_seq'),
postgres(# pg_get_owner_column('tbl_id_seq')
postgres(# ) AS full_circle;
obj | col |full_circle
+-+---
public.tbl | id | public.tbl_id_seq
(1 row)
I tried not to be too myopic in the design, but apart from sequence
ownership I can't really think of any other uses for this. 'p'in and
'i'nternal relationships wouldn't make much sense, and 'n'ormal ones are
generally exposed in other ways. Anyone have any input there on how
this could be expanded?
Anyway, as an immediate practical example the patch modifies psql's
describe-verbose on sequences to show the ownership information...
postgres=# \d+ tbl_id_seq
(...)
Owner: public.tbl.id
- Josh Williams
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.487
diff -c -r1.487 func.sgml
*** doc/src/sgml/func.sgml 16 Aug 2009 19:55:21 - 1.487
--- doc/src/sgml/func.sgml 2 Sep 2009 23:11:15 -
***
*** 12264,12269
--- 12264,12277
/indexterm
indexterm
+ primarypg_get_owner_object/primary
+/indexterm
+
+indexterm
+ primarypg_get_owner_column/primary
+/indexterm
+
+indexterm
primarypg_tablespace_databases/primary
/indexterm
***
*** 12365,12370
--- 12373,12388
uses/entry
/row
row
+entryfunctionpg_get_owner_object/function(parameterrelation_oid/parameter)/entry
+entrytypetext/type/entry
+entryget name of the relation that owns the specified object, such as a sequence/entry
+ /row
+ row
+entryfunctionpg_get_owner_column/function(parameterrelation_oid/parameter)/entry
+entrytypetext/type/entry
+entryget column name associated with the specified object in its owning relation/entry
+ /row
+ row
entryfunctionpg_get_triggerdef/function(parametertrigger_oid/parameter)/entry
entrytypetext/type/entry
entryget commandCREATE [ CONSTRAINT ] TRIGGER/ command for trigger/entry
***
*** 12478,12483
--- 12496,12513
/para
para
+functionpg_get_owner_object/function returns the name of the relation
+that owns the specified relation object, or NULL if the object isn't owned
+by a relation. The input parameter can be passed as an OID or possibly a
+double-quoted identifier. This can be treated in some respects as the
+inverse of functionpg_get_serial_sequence/function, where the association
+can be modified or removed with commandALTER SEQUENCE OWNED BY/.
+functionpg_get_owner_column/function returns the name of the column
+associated with an owned object, such as the name of a sequence's
+original typeserial/ column.
+ /para
+
+ para
functionpg_get_userbyid/function extracts a role's name given
its OID.
/para
Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 1 Aug 2009 19:59:41 - 1.306
--- src/backend/utils/adt/ruleutils.c 2 Sep 2009 23:11:19 -
***
*** 1446,1451
--- 1446,1601
/*
+ * pg_get_owner_object
+ * Returns the name of the object that owns the specified object
+ * by looking up an auto dependency relationship.
+ * Useful for finding a sequence's parent table.
+ * See pg_get_owner_column for the originating serial column.
+ */
+ Datum
+ pg_get_owner_object(PG_FUNCTION_ARGS)
+ {
+ Oid relId = PG_GETARG_OID(0);
+ Oid ownerId = InvalidOid;
+ RelationdepRel;
+ ScanKeyData key[3];
+ SysScanDesc depScan;
+ HeapTuple tup;
+
+ /* Find the requested object in the dependency table... */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber,
Re: [HACKERS] Triggers on columns
Peter Eisentraut pete...@gmx.net wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. Hmmm, what does the SQL standard say about modification of NEW values? Should we fire column triggers when their columns are mentioned in the UPDATE statement, but modified by another triggers? I believe we should fire them, but it is inconsistent because we will make different decisions whether NEW values are modified or not. Regards, --- ITAGAKI Takahiro 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] Largeobject access controls
KaiGai Kohei wrote: Alvaro Herrera wrote: Tom Lane wrote: KaiGai Kohei kai...@kaigai.gr.jp writes: BTW, currently, the default ACL of largeobject allows anything for owner and nothing for world. Do you have any comment for the default behavior? Mph. I think the backlash will be too great. You have to leave the default behavior the same as it is now, ie, world access. BTW as a default it is pretty bad. Should we have a GUC var to set the default LO permissions? It seems to me a reasonable idea in direction. However, it might be better to add a GUC variable to turn on/off LO permission feature, not only default permissions. It allows us to control whether the privilege mechanism should perform in backward compatible, or not. Now we have two options: 1. A GUC variable to set the default largeobject permissions. SET largeobject_default_acl = [ ro | rw | none ] - ro : read-only - rw : read-writable - none : nothing It can control the default acl which is applied when NULL is set on the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership on the largeobject, so it is not enough compatible with v8.4.x or prior. 2. A GUC veriable to turn on/off largeobject permissions. SET largeobject_compat_dac = [ on | off ] When the variable is turned on, largeobject dac permission check is not applied as the v8.4.x or prior version did. So, the variable is named compat which means compatible behavior. It also does not check ownership on lo_unlink(). My preference is the second approach. What's your opinion? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Largeobject access controls
2009/9/3 KaiGai Kohei kai...@ak.jp.nec.com: KaiGai Kohei wrote: Alvaro Herrera wrote: Tom Lane wrote: KaiGai Kohei kai...@kaigai.gr.jp writes: BTW, currently, the default ACL of largeobject allows anything for owner and nothing for world. Do you have any comment for the default behavior? Mph. I think the backlash will be too great. You have to leave the default behavior the same as it is now, ie, world access. BTW as a default it is pretty bad. Should we have a GUC var to set the default LO permissions? It seems to me a reasonable idea in direction. However, it might be better to add a GUC variable to turn on/off LO permission feature, not only default permissions. It allows us to control whether the privilege mechanism should perform in backward compatible, or not. Now we have two options: 1. A GUC variable to set the default largeobject permissions. SET largeobject_default_acl = [ ro | rw | none ] - ro : read-only - rw : read-writable - none : nothing It can control the default acl which is applied when NULL is set on the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership on the largeobject, so it is not enough compatible with v8.4.x or prior. 2. A GUC veriable to turn on/off largeobject permissions. SET largeobject_compat_dac = [ on | off ] When the variable is turned on, largeobject dac permission check is not applied as the v8.4.x or prior version did. So, the variable is named compat which means compatible behavior. It also does not check ownership on lo_unlink(). My preference is the second approach. What's your opinion? I prefer the first. There's little harm in letting users set the default permissions for themselves, but a GUC that controls system-wide behavior will have to be something only superusers can money with, and that seems like it will reduce usability. Why couldn't lo_unlink() just check write privilege? ...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] [PATCH] Largeobject access controls
Robert Haas wrote: 2009/9/3 KaiGai Kohei kai...@ak.jp.nec.com: KaiGai Kohei wrote: Alvaro Herrera wrote: Tom Lane wrote: KaiGai Kohei kai...@kaigai.gr.jp writes: BTW, currently, the default ACL of largeobject allows anything for owner and nothing for world. Do you have any comment for the default behavior? Mph. I think the backlash will be too great. You have to leave the default behavior the same as it is now, ie, world access. BTW as a default it is pretty bad. Should we have a GUC var to set the default LO permissions? It seems to me a reasonable idea in direction. However, it might be better to add a GUC variable to turn on/off LO permission feature, not only default permissions. It allows us to control whether the privilege mechanism should perform in backward compatible, or not. Now we have two options: 1. A GUC variable to set the default largeobject permissions. SET largeobject_default_acl = [ ro | rw | none ] - ro : read-only - rw : read-writable - none : nothing It can control the default acl which is applied when NULL is set on the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership on the largeobject, so it is not enough compatible with v8.4.x or prior. 2. A GUC veriable to turn on/off largeobject permissions. SET largeobject_compat_dac = [ on | off ] When the variable is turned on, largeobject dac permission check is not applied as the v8.4.x or prior version did. So, the variable is named compat which means compatible behavior. It also does not check ownership on lo_unlink(). My preference is the second approach. What's your opinion? I prefer the first. There's little harm in letting users set the default permissions for themselves, but a GUC that controls system-wide behavior will have to be something only superusers can money with, and that seems like it will reduce usability. I don't intend to allow session users to set up their default acl. Both operation should be always system-wide. If a normal user can change the default acl, it is also equivalent he can grant all permissions to public on all the largeobject with its acl being NULL. Note that PostgreSQL does not set up a certain ACLs on its creation time, so NULL is assigned. The default ACL means an alternarive set of permissions, when it is NULL. Why couldn't lo_unlink() just check write privilege? Because it is inconsistent behavior. PostgreSQL checks its ownership on dropping a certain database objects, such as tabls, procedures and so on. It seems to me quite strange, if only largeobject checks writer permission to drop itself. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] community decision-making 8.5
2009/9/4 Joshua D. Drake j...@commandprompt.com: On Thu, 2009-09-03 at 12:00 -0400, Robert Haas wrote: And /me pokes Brendan Jurd. :-) Hah! I almost listed him. /me adds a poke to Brendan Jurd. /me stirs from sleep to announce huh? whaddyawant? Seriously though, I have been keeping an eye on this thread, and I think it's heading in an interesting and positive direction. Still I'm a little unclear on what would be expected of, and indeed what I could contribute to, the release team effort. So far in the thread I've seen mention of deciding/announcing/enforcing project deadlines, managing commit fests, beta testing and beta-to-release items. Is that about the right idea? Cheers, BJ -- 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
2009/9/3 Pavel Stehule pavel.steh...@gmail.com: 2009/9/3 Joshua Tolley eggyk...@gmail.com: On Thu, Sep 03, 2009 at 01:19:25AM +0400, Олег Царев wrote: After week-lengthed investigation, now i 'm sure - my level of qualification not enough for implementation task GROUPING SETS. I require documentation about the executor and the planner, i can't understand scheme of work by source code. Many code, many cases, but very little information what is it and how thos work. May be i stupid. I doubt you're stupid; a stupid person wouldn't know what GROUPING SETS meant, wouldn't bother finding out, and certainly wouldn't bother trying to implement it. It's very helpful that you've let us know you're not working on it. That way Pavel, if he finds he has time and interest, or someone else, can work on it without fear of conflicting with what you're doing. Thanks for your work; please don't get discouraged! There some ways, how implement GROUPING SETS. Currently I don't would to continue on this topic without some sponsoring. It got too my time - and I am able to implement it only as some special variant of CTE. Second way is total grouping planner refactoring - what is out of me. There supposed to be another way to implement, that is a hybrid way of CTE approach and hash tables approach. As you noticed, in group mode the planner may be untouchable which CTE helps to avoid and in hash mode you've almost done it. I wouldn't like to agree to CTE based approach totally, but the hybrid might be reasonable. Regards, regard Pavel -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkqfM2QACgkQRiRfCGf1UMOHvgCgpzV9cvjhCWhzcmvRDbXjdBQ1 4RYAn2E+ZLRLdho+c+ZFleslPrbyxsZN =66vh -END PGP SIGNATURE- -- 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] Hot Standby, max_connections and max_prepared_transactions
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2009-09-03 at 22:22 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: I propose we just accept that both max_connections and max_prepared_transactions need to be set correctly for recovery to work. This will make the state transitions more robust and it will avoid spurious and hard to test error messages. Any objections to me removing this slice of code from the patch? Umm, what slice of code? I don't recall any code trying to make it work. Well, its there. Just to be clear: you're proposing requiring that these be set the same on master and slave? I don't have a problem with that, but I do suggest that we must provide a mechanism to check it --- I don't want DBAs to be faced with obscure failures when (not if) they mess it up. Perhaps include the values in checkpoint WAL records? 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
