Re: [HACKERS] sum() over (partition by order) question
2008/12/31 Tom Lane : > "Pavel Stehule" writes: >> so I have to modify query to get expected values >> postgres=# select a, b, last_value(a) over (partition by b), >> last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED >> PRECEDING AND UNBOUNDED FOLLOWING) from foo; > >> it should be noticed in doc? > > It is --- see the comments at the bottom of > > http://developer.postgresql.org/pgdocs/postgres/functions-window.html > >regards, tom lane > there is maybe bug. It works well with explicit definition, but it not works when I will window good postgres=# select a, b, sum(a) over (partition by b order by a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from foo; a | b | sum +---+- 1 | 1 | 19 1 | 1 | 19 2 | 1 | 19 2 | 1 | 19 4 | 1 | 19 4 | 1 | 19 5 | 1 | 19 11 | 3 | 93 12 | 3 | 93 16 | 3 | 93 16 | 3 | 93 16 | 3 | 93 22 | 3 | 93 (13 rows) wrong postgres=# select a, b, sum(a) over (w) from foo window w as (partition by b order by a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING); a | b | sum +---+- 1 | 1 | 2 1 | 1 | 2 2 | 1 | 6 2 | 1 | 6 4 | 1 | 14 4 | 1 | 14 5 | 1 | 19 11 | 3 | 11 12 | 3 | 23 16 | 3 | 71 16 | 3 | 71 16 | 3 | 71 22 | 3 | 93 (13 rows) regards Pavel Stehule -- 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] sum() over (partition by order) question
2008/12/31 Tom Lane : > "Pavel Stehule" writes: >> so I have to modify query to get expected values >> postgres=# select a, b, last_value(a) over (partition by b), >> last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED >> PRECEDING AND UNBOUNDED FOLLOWING) from foo; > >> it should be noticed in doc? > > It is --- see the comments at the bottom of > > http://developer.postgresql.org/pgdocs/postgres/functions-window.html > >regards, tom lane > oh, yes, there it is, thank you and Happy New Year regards Pavel Stehule -- 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] sum() over (partition by order) question
"Pavel Stehule" writes: > so I have to modify query to get expected values > postgres=# select a, b, last_value(a) over (partition by b), > last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED > PRECEDING AND UNBOUNDED FOLLOWING) from foo; > it should be noticed in doc? It is --- see the comments at the bottom of http://developer.postgresql.org/pgdocs/postgres/functions-window.html 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] sum() over (partition by order) question
"Pavel Stehule" writes: > wrong > postgres=# select a, b, sum(a) over (w) from foo window w as > (partition by b order by a ROWS BETWEEN UNBOUNDED PRECEDING AND > UNBOUNDED FOLLOWING); Should be "over w". "over (w)" is a that modifies an existing window, not just a reference, and in particular that means we don't copy the framing clause; see SQL2008 7.11 general rule 1) b) i) 6). Hmm... I think 7.11 syntax rule 10) e) actually wants us to throw an error for this case, not just silently ignore the referenced window's framing clause. I had thought that was just being overly anal, but now it seems that this case can result in user confusion, so maybe we'd better throw the error after all. 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] sum() over (partition by order) question
2008/12/31 Tom Lane : > "Pavel Stehule" writes: >> I didn't expect so ORDER can change result of function sum. > > Read the stuff about window frames. The results you show are > exactly per spec. > I have to do it, when I tested last_value and first_value function I was surprised more - order by changing partitions postgres=# select a, b, last_value(a) over (partition by b), last_value(a) over (partition by b order by a) from foo; a | b | last_value | last_value +---++ 1 | 1 | 5 | 1 1 | 1 | 5 | 1 2 | 1 | 5 | 2 2 | 1 | 5 | 2 4 | 1 | 5 | 4 4 | 1 | 5 | 4 5 | 1 | 5 | 5 11 | 3 | 16 | 11 12 | 3 | 16 | 12 16 | 3 | 16 | 16 16 | 3 | 16 | 16 16 | 3 | 16 | 16 22 | 3 | 16 | 22 (13 rows) so I have to modify query to get expected values postgres=# select a, b, last_value(a) over (partition by b), last_value(a) over (partition by b order by a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) from foo; a | b | last_value | last_value +---++ 1 | 1 | 5 | 5 1 | 1 | 5 | 5 2 | 1 | 5 | 5 2 | 1 | 5 | 5 4 | 1 | 5 | 5 4 | 1 | 5 | 5 5 | 1 | 5 | 5 11 | 3 | 16 | 22 12 | 3 | 16 | 22 16 | 3 | 16 | 22 16 | 3 | 16 | 22 16 | 3 | 16 | 22 22 | 3 | 16 | 22 (13 rows) it should be noticed in doc? regards Pavel Stehule >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] About CMake
Andrew Dunstan wrote: Quite so. CMake outputs MSVC Project files, as I understand it. If you know of another cross-platform build tool that will do that then speak up. I think the wxWidgets team have one, and I think scons has some support for doing that, though I haven't tried that part of scons. The first uses Perl, scons uses Python. -- 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] sum() over (partition by order) question
On Wed, Dec 31, 2008 at 4:34 PM, Pavel Stehule wrote: > Hello > > I am play with windows function. I was surprised so these queries has > different results. > > postgres=# select sum(a) over (partition by b), a, b from foo; AFAIUI, this means one sum per b value, the result in the sum column will be equivalent to "select sum(a) from foo group by b" > > postgres=# select sum(a) over (partition by b order by a), a, b from foo; and this means something like accumulate the value of a per b value and for every value of b accumulate per a value... maybe this can be described better... don't know exactly if we can imitate this behaviour without window functions -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] reloptions and toast tables
Alvaro Herrera writes:
> Peter Eisentraut wrote:
>> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
>>> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
>>> ALTER TABLE foo SET (toast.autovacuum_enabled = false);
>>> ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
>>> ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>>
>> The last two don't appear to allow setting TOAST and non-TOAST options in
>> one
>> go. I think it would be handy to allow that, though.
> Agreed -- so I'm now playing with this version:
> ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
> So the grammar modifications needed to accept that are attached. The
> support code is a lot messier than I'd like :-(
This is not only really ugly, but 100% toast-specific. The
qualified-name approach ("toast.autovacuum_enabled") has at least
a chance of being good for something else. Or just make it
toast_autovacuum_enabled and do the translation magic at some low
level in the statement execution code.
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] sum() over (partition by order) question
"Pavel Stehule" writes: > I didn't expect so ORDER can change result of function sum. Read the stuff about window frames. The results you show are exactly per spec. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] sum() over (partition by order) question
Hello I am play with windows function. I was surprised so these queries has different results. postgres=# select sum(a) over (partition by b), a, b from foo; sum | a | b -++--- 19 | 1 | 1 19 | 1 | 1 19 | 2 | 1 19 | 4 | 1 19 | 2 | 1 19 | 4 | 1 19 | 5 | 1 93 | 11 | 3 93 | 12 | 3 93 | 22 | 3 93 | 16 | 3 93 | 16 | 3 93 | 16 | 3 (13 rows) postgres=# select sum(a) over (partition by b order by a), a, b from foo; sum | a | b -++--- 2 | 1 | 1 2 | 1 | 1 6 | 2 | 1 6 | 2 | 1 14 | 4 | 1 14 | 4 | 1 19 | 5 | 1 11 | 11 | 3 23 | 12 | 3 71 | 16 | 3 71 | 16 | 3 71 | 16 | 3 93 | 22 | 3 (13 rows) I didn't expect so ORDER can change result of function sum. Please, can somebody explain it? regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump roles support [Review]
Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: > --role switch for pg_dump and pg_dumpall: sets the role used while > dumping, has no effect on the emitted archive. > > --role switch for pg_restore: sets the role used while restoring, > if it's to be different from what -U says. As one of the original requestors for this capability, just wanted to add my 2c that this will work for me and makes sense to me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] lazy_truncate_heap()
On 31 Dec 2008, at 13:21, Simon Riggs wrote: Both of these bugs are minor, but the effect of either/both of them is to cause more AccessExclusiveLocks than we might expect. For Hot Standby this means that many VACUUMs take AccessExclusiveLocks on relations, which would potentially lead to having queries cancelled for no reason at all. Well by default it would just cause wal to pause briefly until the queries with those locks finish, no? -- 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] lazy_truncate_heap()
Simon Riggs wrote: While watching WAL records float by I noticed some AccessExclusiveLocks occurring unnecessarily during VACUUMs. This is caused by lines 186-189 in lazy_vacuum_rel(), vacuumlazy.c possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; if (possibly_freeable >= REL_TRUNCATE_MINIMUM || possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION) lazy_truncate_heap(onerel, vacrelstats); If you look closely you'll see that if rel_pages is small then we will attempt to truncate the heap even if possibly_freeable == 0. Good catch! And it goes all the way back to 7.4. While looking at this some more, I notice there is another bug. When VACUUM has nothing at all to do, then it appears that vacrelstats->nonempty_pages is zero, so that possibly_freeable is always set to vacrelstats->rel_pages. vacrelstats->nonempty_pages doesn't appear to be used anywhere else, so nobody notices it is wrongly set. Hmm, this is a new issue with the visibility map. For pages at the end that are skipped, we don't know if they're empty or not. Currently we assume that they are, but perhaps it would be better to assume they're not? On the other hand, that makes it much less likely that we even try to truncate a relation, and when we do, we wouldn't truncate it as far as we could. Does anybody think any of the above is intentional? Can I fix? No. Yes please. -- 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] reloptions and toast tables
Peter Eisentraut wrote:
> On Sunday 21 December 2008 01:48:42 Alvaro Herrera wrote:
> > ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
> > ALTER TABLE foo SET (toast.autovacuum_enabled = false);
> > ALTER TABLE foo TOAST SET (autovacuum_enabled = false);
> > ALTER TABLE foo SET TOAST (autovacuum_enabled = false);
>
> The last two don't appear to allow setting TOAST and non-TOAST options in one
> go. I think it would be handy to allow that, though.
Agreed -- so I'm now playing with this version:
> > ALTER TABLE foo SET (TOAST autovacuum_enabled = false);
So the grammar modifications needed to accept that are attached. The
support code is a lot messier than I'd like :-(
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/define.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/define.c,v
retrieving revision 1.101
diff -c -p -r1.101 define.c
*** src/backend/commands/define.c 1 Jan 2008 19:45:48 - 1.101
--- src/backend/commands/define.c 31 Dec 2008 17:53:38 -
*** defGetTypeLength(DefElem *def)
*** 306,319
}
/*
! * Create a DefElem setting "oids" to the specified value.
*/
! DefElem *
defWithOids(bool value)
{
! DefElem*f = makeNode(DefElem);
f->defname = "oids";
f->arg = (Node *) makeInteger(value);
return f;
}
--- 306,320
}
/*
! * Create a TDefElem setting "oids" to the specified value.
*/
! TDefElem *
defWithOids(bool value)
{
! TDefElem*f = makeNode(TDefElem);
f->defname = "oids";
f->arg = (Node *) makeInteger(value);
+ f->toast = false;
return f;
}
Index: src/backend/nodes/copyfuncs.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/nodes/copyfuncs.c,v
retrieving revision 1.418
diff -c -p -r1.418 copyfuncs.c
*** src/backend/nodes/copyfuncs.c 31 Dec 2008 00:08:35 - 1.418
--- src/backend/nodes/copyfuncs.c 31 Dec 2008 17:16:59 -
*** _copyDefElem(DefElem *from)
*** 2112,2117
--- 2112,2130
return newnode;
}
+ static TDefElem *
+ _copyTDefElem(TDefElem *from)
+ {
+ TDefElem *newnode = makeNode(TDefElem);
+
+ COPY_STRING_FIELD(defname);
+ COPY_NODE_FIELD(arg);
+ COPY_SCALAR_FIELD(toast);
+
+ return newnode;
+ }
+
+
static OptionDefElem *
_copyOptionDefElem(OptionDefElem *from)
{
*** copyObject(void *from)
*** 4063,4068
--- 4076,4084
case T_DefElem:
retval = _copyDefElem(from);
break;
+ case T_TDefElem:
+ retval = _copyTDefElem(from);
+ break;
case T_OptionDefElem:
retval = _copyOptionDefElem(from);
break;
Index: src/backend/nodes/makefuncs.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/nodes/makefuncs.c,v
retrieving revision 1.61
diff -c -p -r1.61 makefuncs.c
*** src/backend/nodes/makefuncs.c 19 Dec 2008 16:25:17 - 1.61
--- src/backend/nodes/makefuncs.c 31 Dec 2008 16:51:09 -
*** makeDefElem(char *name, Node *arg)
*** 363,368
--- 363,383
}
/*
+ * makeTDefElem -
+ * build a TDefElem node
+ */
+ TDefElem *
+ makeTDefElem(char *name, Node *arg, bool toast)
+ {
+ TDefElem *res = makeNode(TDefElem);
+
+ res->defname = name;
+ res->arg = arg;
+ res->toast = toast;
+ return res;
+ }
+
+ /*
* makeOptionDefElem -
* build an OptionDefElem node
*/
Index: src/backend/nodes/outfuncs.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/nodes/outfuncs.c,v
retrieving revision 1.348
diff -c -p -r1.348 outfuncs.c
*** src/backend/nodes/outfuncs.c 31 Dec 2008 00:08:36 - 1.348
--- src/backend/nodes/outfuncs.c 31 Dec 2008 17:17:19 -
*** _outDefElem(StringInfo str, DefElem *nod
*** 1807,1812
--- 1807,1822
}
static void
+ _outTDefElem(StringInfo str, TDefElem *node)
+ {
+ WRITE_NODE_TYPE("TDEFELEM");
+
+ WRITE_STRING_FIELD(defname);
+ WRITE_NODE_FIELD(arg);
+ WRITE_BOOL_FIELD(toast);
+ }
+
+ static void
_outLockingClause(StringInfo str, LockingClause *node)
{
WRITE_NODE_TYPE("LOCKINGCLAUSE");
*** _outNode(StringInfo str, void *obj)
*** 2770,2775
--- 2780,2788
case T_DefElem:
_outDefElem(str, obj);
break;
+ case T_TDefElem:
+ _outTDefElem(str, obj);
+ break;
case T_LockingClause:
_outLockingClause(str, obj);
break;
Index: src/backend/parser/gram.y
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.650
diff -c -p -r2.650 gram.y
*** src/backend/parser/gram.y 31 Dec 2008 02:25:04 - 2.650
--- src/backend/parser/gram.y 31 D
Re: [HACKERS] pg_dump roles support [Review]
[ starting to examine this patch now... ] =?UTF-8?B?QmVuZWRlayBMw6FzemzDsw==?= writes: > I also need some feedback about the role support in pg_restore (not > implemented yet). Currently pg_restore sets the role during the > restore process according to the TOC entry in the archive. It may also > support the --role option (just like pg_dump). If specified it can be > used to cancel the effect of the TOC entry and force the emitting of > the SET ROLE ... command. With emtpy argument it can be used to omit > the SET ROLE even if it is specified in the archieve. What do you > think? I think that the entire concept of putting the rolename into the archive is broken, and we should not do that part at all. But we *especially* should not do it if there is no way to override it. I see no good reason to assume that the appropriate role to use during restore is the same as that during dump. We don't reflect the -U setting into the dump file, and --role is really just an auxiliary extension to -U. What would make sense is to have a --role switch in pg_restore, but have that function entirely independently of what happened at dump time, just as is true for -U. So my thought is: --role switch for pg_dump and pg_dumpall: sets the role used while dumping, has no effect on the emitted archive. --role switch for pg_restore: sets the role used while restoring, if it's to be different from what -U says. This ignores the case of plain-text output from pg_dump, but you don't really need any support for that case, as you can do the restore like so: psql -U admin_user target_db target_db=> SET ROLE superuser; target_db=# \i dumpfile.sql Comments? 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] version() output vs. 32/64 bits
On Wed, Dec 31, 2008 at 01:25:34PM -0500, Tom Lane wrote: > "Pavel Stehule" writes: > > 2008/12/31 Alvaro Herrera : > >> Maybe we could have a separate function which returned the info > >> in various columns (OUT params). Maybe it would be useful to > >> normalize the info as reported the buildfarm, which right now is > >> a bit ad-hoc. > > > All should be GUC read only variables - It is cheep. > > Not as cheap as a single added function. If we need to provide > these fields broken out --- and no one has demonstrated any need to > do so --- then I'd support Alvaro's suggestion. +1 for broken-out fields in columns per Alvaro. Cheers, David. -- David Fetter 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] TODO items for window functions
On Wed, Dec 31, 2008 at 11:04:41AM -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Heikki Linnakangas escribi�: > >> Tom Lane wrote: > >>> pg_catalog | nth_value | anyelement | anyelement, integer OVER > >>> window > >> > >> That looks like "OVER window" is associated with the "integer", like > >> DEFAULT. I don't have any better suggestions, though. > > > pg_catalog | nth_value | anyelement | (anyelement, integer) OVER > > window > > Yeah, I had considered that too, and it has a distinct advantage for > parameterless functions like rank(): > >Schema | Name | Result data type | Argument data types > +--+--+- > pg_catalog | rank | bigint | OVER window > pg_catalog | rank | bigint | () OVER window > > The latter is definitely clearer about what you're supposed to do. +1 on the latter. > However, it seems kind of inconsistent to do this for window functions > unless we also make \df start putting parens around the argument lists > for regular functions. Comments? Would parens around all the argument lists really be so bad? I'm thinking not. Cheers, David. -- David Fetter 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
[HACKERS] SET TRANSACTION and SQL Standard
I notice that we allow commands such as SET TRANSACTION read only read write read only; BEGIN TRANSACTION read only read only read only; Unsurprisingly, these violate the SQL Standard: * p.977 section 19.1 syntax (1) * p.957 section 17.3 syntax (2) Not planning on fixing it myself, but others may wish to. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Latest version of Hot Standby patch
On Wed, 2008-12-17 at 15:21 +, Simon Riggs wrote: > http://wiki.postgresql.org/wiki/Hot_Standby > > now contains a link to latest version of this patch. v6 of Hot Standby now uploaded to Wiki (link above), with these changes: * Must ignore_killed_tuples and never kill_prior_tuple during index scans in recovery (v6) * XLOG_BTREE_DELETE records handled correctly (v6) * btree VACUUM code - must scan every block of index (v6) * BEGIN TRANSACTION READ WRITE should throw error (v6) New test cycle starting with this patch over next few days. Work continues on other items. Happy New Year everyone, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] version() output vs. 32/64 bits
"Pavel Stehule" writes: > 2008/12/31 Alvaro Herrera : >> Maybe we could have a separate function which returned the info in >> various columns (OUT params). Maybe it would be useful to normalize the >> info as reported the buildfarm, which right now is a bit ad-hoc. > All should be GUC read only variables - It is cheep. Not as cheap as a single added function. If we need to provide these fields broken out --- and no one has demonstrated any need to do so --- then I'd support Alvaro's suggestion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] lazy_truncate_heap()
While watching WAL records float by I noticed some AccessExclusiveLocks occurring unnecessarily during VACUUMs. This is caused by lines 186-189 in lazy_vacuum_rel(), vacuumlazy.c possibly_freeable = vacrelstats->rel_pages - vacrelstats->nonempty_pages; if (possibly_freeable >= REL_TRUNCATE_MINIMUM || possibly_freeable >= vacrelstats->rel_pages / REL_TRUNCATE_FRACTION) lazy_truncate_heap(onerel, vacrelstats); If you look closely you'll see that if rel_pages is small then we will attempt to truncate the heap even if possibly_freeable == 0. While looking at this some more, I notice there is another bug. When VACUUM has nothing at all to do, then it appears that vacrelstats->nonempty_pages is zero, so that possibly_freeable is always set to vacrelstats->rel_pages. vacrelstats->nonempty_pages doesn't appear to be used anywhere else, so nobody notices it is wrongly set. Both of these bugs are minor, but the effect of either/both of them is to cause more AccessExclusiveLocks than we might expect. For Hot Standby this means that many VACUUMs take AccessExclusiveLocks on relations, which would potentially lead to having queries cancelled for no reason at all. Does anybody think any of the above is intentional? Can I fix? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] version() output vs. 32/64 bits
Hello 2008/12/31 Alvaro Herrera : > Tom Lane wrote: >> Peter Eisentraut writes: >> > On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: >> >> PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit >> >> > Maybe we should separate all that, e.g., >> >> > SELECT version(); => 'PostgreSQL 8.4devel' >> > SELECT pg_host_os();=> 'bsdi4.3.1' >> > SELECT pg_host_cpu(); => 'i386' (although this is still faulty, as >> > per my >> > original argument; needs some thought) >> > SELECT pg_compiler(); => 'GCC 2.95.3' >> > SELECT pg_pointer_size(); => 4 (or 32) (this could also be a SHOW variable) >> >> Seems like serious overkill. No one has asked for access to individual >> components of the version string, other than the PG version number >> itself, which we already dealt with. > > Maybe we could have a separate function which returned the info in > various columns (OUT params). Maybe it would be useful to normalize the > info as reported the buildfarm, which right now is a bit ad-hoc. > All should be GUC read only variables - It is cheep. regards Pavel Stehule > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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] Reformat permissions in \l+ (like \z does)
"Andreas 'ads' Scherbaum" writes: > On Sun, 28 Dec 2008 18:19:48 -0500 Tom Lane wrote: >> If we're going to do this, shouldn't it happen uniformly for *all* >> ACL displays in describe.c? > Makes sense, imho. Done. 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] version() output vs. 32/64 bits
On Wednesday 31 December 2008 18:22:50 Bruce Momjian wrote: > It is true no one asked for this information except Peter (I assume for > just academic reasons), no -- 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] version() output vs. 32/64 bits
Bruce Momjian wrote: > Tom Lane wrote: > > I didn't actually see a user request for finding out the pointer width, > > either, but if there is one then Bruce's proposal seems fine. > > It is true no one asked for this information except Peter (I assume for > just academic reasons), Huh, count us (Command Prompt) as another requestor, and not for academic reasons (in case that adds value to the vote). -- 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] version() output vs. 32/64 bits
Tom Lane wrote: > Peter Eisentraut writes: > > On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: > >> PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > > > Maybe we should separate all that, e.g., > > > SELECT version(); => 'PostgreSQL 8.4devel' > > SELECT pg_host_os();=> 'bsdi4.3.1' > > SELECT pg_host_cpu(); => 'i386' (although this is still faulty, as > > per my > > original argument; needs some thought) > > SELECT pg_compiler(); => 'GCC 2.95.3' > > SELECT pg_pointer_size(); => 4 (or 32) (this could also be a SHOW variable) > > Seems like serious overkill. No one has asked for access to individual > components of the version string, other than the PG version number > itself, which we already dealt with. > > I didn't actually see a user request for finding out the pointer width, > either, but if there is one then Bruce's proposal seems fine. It is true no one asked for this information except Peter (I assume for just academic reasons), and I don't think we care from a bug reporting perspective, so I will just keep the patch around in case we ever want it. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO items for window functions
Alvaro Herrera writes: > Heikki Linnakangas escribió: >> Tom Lane wrote: >>> pg_catalog | nth_value | anyelement | anyelement, integer OVER window >> >> That looks like "OVER window" is associated with the "integer", like >> DEFAULT. I don't have any better suggestions, though. > pg_catalog | nth_value | anyelement | (anyelement, integer) OVER > window Yeah, I had considered that too, and it has a distinct advantage for parameterless functions like rank(): Schema | Name | Result data type | Argument data types +--+--+- pg_catalog | rank | bigint | OVER window pg_catalog | rank | bigint | () OVER window The latter is definitely clearer about what you're supposed to do. However, it seems kind of inconsistent to do this for window functions unless we also make \df start putting parens around the argument lists for regular functions. Comments? 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] pg_pltemplate entries for external PLs
Peter Eisentraut writes: > On Wednesday 31 December 2008 05:50:19 Tom Lane wrote: >> That was part of the original concept for pg_pltemplate, but IIRC there >> was push-back from some folks who thought it was a bad idea. Â I don't >> recall what their arguments were exactly; > Basically, we have no information about what the proper parameters of > external > languages would be. (We have some pretty good ideas, but that's not the > same.) Especially if we override the trusted/untrustedness, we could create > complete disaster. Presumably we'd only insert such entries with the concurrence/approval of the PL's author, so this argument seems pretty darn weak to me. It's true that we could have another fiasco like the trusted-plpython one, where something that we thought was trustworthy turns out not to be; but pg_pltemplate seems unlikely to make such a case much worse than it is already. The people who'd be at risk would be the ones who'd already installed the unsafe language, and where they got the information that it was safe wouldn't be relevant anymore. On the other hand, having entries for non-built-in languages in pg_pltemplate would clearly reduce the chances of DBAs accidentally creating a language as trusted when it should not be. I think the odds are good that this effect would reduce security risks far more than they'd be increased by the chance of bad entries in pg_pltemplate. 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] version() output vs. 32/64 bits
Tom Lane wrote: > Peter Eisentraut writes: > > On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: > >> PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > > > Maybe we should separate all that, e.g., > > > SELECT version(); => 'PostgreSQL 8.4devel' > > SELECT pg_host_os();=> 'bsdi4.3.1' > > SELECT pg_host_cpu(); => 'i386' (although this is still faulty, as > > per my > > original argument; needs some thought) > > SELECT pg_compiler(); => 'GCC 2.95.3' > > SELECT pg_pointer_size(); => 4 (or 32) (this could also be a SHOW variable) > > Seems like serious overkill. No one has asked for access to individual > components of the version string, other than the PG version number > itself, which we already dealt with. Maybe we could have a separate function which returned the info in various columns (OUT params). Maybe it would be useful to normalize the info as reported the buildfarm, which right now is a bit ad-hoc. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Peter Eisentraut writes: > On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: >> PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > Maybe we should separate all that, e.g., > SELECT version(); => 'PostgreSQL 8.4devel' > SELECT pg_host_os(); => 'bsdi4.3.1' > SELECT pg_host_cpu(); => 'i386' (although this is still faulty, as per my > original argument; needs some thought) > SELECT pg_compiler(); => 'GCC 2.95.3' > SELECT pg_pointer_size(); => 4 (or 32) (this could also be a SHOW variable) Seems like serious overkill. No one has asked for access to individual components of the version string, other than the PG version number itself, which we already dealt with. I didn't actually see a user request for finding out the pointer width, either, but if there is one then Bruce's proposal seems fine. 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] Lockfree hashtables
On Wed, Dec 31, 2008 at 7:33 AM, Stephen R. van den Berg wrote: > The other day I bumped into some ideas about lockfree hashtables. > Are these of any use in PostgreSQL? Lock-free and wait-free algorithms have been used in various databases, but most people tend to shy away from them because of their complexity, difficulty to debug, and low-level portability issues. I've used them in the past (lock-free hash tables and skip lists), and they're pretty awesome if used properly, but the majority of PG's current performance problems aren't generally found as part of our hash table implementation (which I'm quite fond of actually). FWIS, I think we'll look more into this sometime in the future. -- Jonah H. Harris, Senior DBA myYearbook.com
[HACKERS] Lockfree hashtables
The other day I bumped into some ideas about lockfree hashtables. Are these of any use in PostgreSQL? http://blogs.azulsystems.com/cliff/2007/03/a_nonblocking_h.html http://video.google.com/videoplay?docid=2139967204534450862 -- Sincerely, Stephen R. van den Berg. "I hate spinach, and I'm glad that I hate it, because if I wouldn't hate it, I would have to eat it, and I hate it!" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO items for window functions
Heikki Linnakangas escribió: > Tom Lane wrote: >> I am not thrilled about inventing a new column for this, but how about >> a display like so: >> >> regression=# \df nth_value >> List of functions >>Schema | Name| Result data type | Argument data types >> +---+--+- >> pg_catalog | nth_value | anyelement | anyelement, integer OVER window >> >> or some other addition that only shows up when needed. > > That looks like "OVER window" is associated with the "integer", like > DEFAULT. I don't have any better suggestions, though. List of functions Schema | Name| Result data type |Argument data types +---+--+--- pg_catalog | nth_value | anyelement | (anyelement, integer) OVER window -- 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] pg_pltemplate entries for external PLs
On Wednesday 31 December 2008 05:50:19 Tom Lane wrote: > That was part of the original concept for pg_pltemplate, but IIRC there > was push-back from some folks who thought it was a bad idea. I don't > recall what their arguments were exactly; Basically, we have no information about what the proper parameters of external languages would be. (We have some pretty good ideas, but that's not the same.) Especially if we override the trusted/untrustedness, we could create complete disaster. Ultimately, as we add more loadable facilities (languages, plugins/modules, foreign-data wrappers), we will need a better upgrade mechanism anyway than maintaining our own override list of everything in the world. So let's just leave this mechanism restricted to the built-in languages for now. -- 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] version() output vs. 32/64 bits
On Wednesday 31 December 2008 04:45:01 Bruce Momjian wrote: > Tom Lane wrote: > > Peter Eisentraut writes: > > > ... Moreover, there does not actually seem to be a > > > way to find out whether you have a 32-bit or a 64-bit build (except by > > > using OS tools). > > > > I think the basic definition of "32 bit" or "64 bit", certainly for > > our purposes, is sizeof(void *). That is something that configure > > could easily find out. Or you could look at sizeof(size_t) which > > it already does find out. > > > > I have no immediate proposal on how to factor that into the version > > string. > > I think the pointer size is part of the compiler, rather than the > platform, so it should go after the compiler mention, e.g.: I'm not really sure about that. > test=> select version(); > version > > -- > >PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > (1 row) Maybe we should separate all that, e.g., SELECT version(); => 'PostgreSQL 8.4devel' SELECT pg_host_os();=> 'bsdi4.3.1' SELECT pg_host_cpu(); => 'i386' (although this is still faulty, as per my original argument; needs some thought) SELECT pg_compiler(); => 'GCC 2.95.3' SELECT pg_pointer_size(); => 4 (or 32) (this could also be a SHOW variable) -- 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] TODO items for window functions
Tom Lane wrote: I am not thrilled about inventing a new column for this, but how about a display like so: regression=# \df nth_value List of functions Schema | Name| Result data type | Argument data types +---+--+- pg_catalog | nth_value | anyelement | anyelement, integer OVER window or some other addition that only shows up when needed. That looks like "OVER window" is associated with the "integer", like DEFAULT. I don't have any better suggestions, though. -- 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] parallel restore
Laurent Coustet wrote: > Andrew Dunstan wrote: >> >> Attached is the latest parallel restore patch. I think this is getting >> fairly close. > > Just some details, you often mix tab and spaces for indentation... > What's the standard in pgsql ? It's tabs, see: http://www.postgresql.org/docs/8.3/static/source-format.html. There's an entry in the dev faq as well. Anyway, we have pgindent to fix any such issues - probably Andrew will run it through that before he applies, if not it will still be run before release. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] version() output vs. 32/64 bits
Bruce Momjian wrote: > Tom Lane wrote: >> Peter Eisentraut writes: >>> ... Moreover, there does not actually seem to be a >>> way to find out whether you have a 32-bit or a 64-bit build (except by >>> using OS tools). >> I think the basic definition of "32 bit" or "64 bit", certainly for >> our purposes, is sizeof(void *). That is something that configure >> could easily find out. Or you could look at sizeof(size_t) which >> it already does find out. >> >> I have no immediate proposal on how to factor that into the version >> string. > > I think the pointer size is part of the compiler, rather than the > platform, so it should go after the compiler mention, e.g.: > > test=> select version(); > version > -- > >PostgreSQL 8.4devel on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3, 32-bit > (1 row) > > The attached patch modifies configure.in and updates a documentation mention. You forgot a certain another build system ;-) Should be trivial to add there though, if we choose to do it this way, so that's not an objection in general. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Hot standby and b-tree killed items
On Tue, 2008-12-30 at 18:31 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > (a) always ignore LP_DEAD flags we see when reading index during > > recovery. > > This sounds simplest, and it's nice to not clear the flags for the > benefit of transactions running after the recovery is done. Agreed. (Also: Transaction hint bits are always set correctly, because we would only ever see a full page write with hints set after the commit/abort record was processed. So I continue to honour transaction hint bit reading and setting during recovery). > You have to be careful to ignore the flags in read-only transactions > that started in hot standby mode, even if recovery has since ended and > we're in normal operation now. Got that. I'm setting ignore_killed_tuples = false at the start of any index scan during recovery. And kill_prior_tuples is never set true when in recovery. Both measures are AM-agnostic. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] parallel restore
Andrew Dunstan wrote: Attached is the latest parallel restore patch. I think this is getting fairly close. Just some details, you often mix tab and spaces for indentation... What's the standard in pgsql ? -- Laurent COUSTET -- 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] Documenting serializable vs snapshot isolation levels
On Mon, 2008-12-29 at 18:13 -0600, Kevin Grittner wrote: > I hope someone can show me something good I've missed so far. You're viewing this in problem-exposed language, unintentionally I'm sure. My viewpoint on this is that database concurrency is a big issue, but that the way we do things round here is a major leap forward on the way things happened previously (and still do in older-style DBMS). Our approach to serializable queries is an optimistic one in two ways: It covers most cases, but not all theoretical cases. It also avoids locks by default. Those are good things, with many benefits. If we put the default the other way around, developers would spend much more time re-tuning queries that had locked each other out. So I would say we choose to avoid locking-on-every-query with good reason. Just look at the facilities DB2 provides to avoid it. Ugh-ly. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] TODO items for window functions
2008/12/31 Tom Lane : > "Robert Haas" writes: >>> Apparently that analogy didn't impress anyone but me. > >> It impressed me. I liked making WINDOW a flag that occurs later in >> the statement a lot better. > > I ended up going with the flag/attribute approach. The other would be > only marginally more work now, but I remain convinced that we'd have to > do more work later to deal with the issue that CREATE WINDOW FUNCTION > looks like "window function" is a distinct kind of SQL object. And > nobody seemed to want to propagate that distinction into all the places > it would logically have to go. > > However ... having said that, there is more to David Fetter's gripe > about \df than I realized at first. Consider > > regression=# \df nth_value >List of functions > Schema | Name| Result data type | Argument data types > +---+--+- > pg_catalog | nth_value | anyelement | anyelement, integer > (1 row) > > Even without any consideration of user-defined window functions, > this seems a bit lacking: the user of nth_value() needs to know that > he has to write an OVER clause, and as things stand \df is not going > to give him the slightest hint about that. So I can see the argument > for reflecting window-ness into \df somehow. > > I am not thrilled about inventing a new column for this, but how about > a display like so: > > regression=# \df nth_value >List of functions > Schema | Name| Result data type | Argument data types > +---+--+- > pg_catalog | nth_value | anyelement | anyelement, integer OVER window > +1 regards Pavel Stehule > or some other addition that only shows up when needed. > >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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
