Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
Tom Lane wrote:
KaiGai Kohei [EMAIL PROTECTED] writes:
Tom Lane wrote:
* It does not come close to passing the regression tests. I saw a lot of
! ERROR: unrecognized node type: 903
which suggests that something's been screwed up about parse analysis
(903 = T_A_Const, which shouldn't get further than parse analysis),
Could you tell me what queries hit these errors?
I remember seeing it on some EXECUTEs, but you really ought to run the
tests for yourself. A *minimum* requirement on any submitted patch
is that it should pass the regression tests.
Some of the test fails contains minor differences from expected results, like:
| SELECT '' AS xxx, *
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
|xxx | a | b | c | d
| -+---+---+--+---
| - | 0 | | zero |
|| 2 | 3 | two | 2
|| 4 | 1 | four | 2
| + | 0 | | zero |
| (3 rows)
and, some of them are trivial ones, like:
| SELECT p1.oid, p1.typname
| FROM pg_type as p1
| WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT EXISTS
| (SELECT 1 FROM pg_type as p2
|WHERE p2.typname = ('_' || p1.typname)::name AND
| p2.typelem = p1.oid and p1.typarray = p2.oid);
| - oid | typname
| --+-
| - 210 | smgr
| - 705 | unknown
| -(2 rows)
| + oid |typname
| +--+
| + 210 | smgr
| + 705 | unknown
| + 3403 | security_label
| +(3 rows)
Isn't it necessary to consider them as regressions?
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei [EMAIL PROTECTED]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Relatively simple question about getting attributes from the where clause
Hi guys, I am new to postgres hacking, and I have the following question. Say that someone executes a query: SELECT * FROM user u, logins l WHERE u.id = l.id (assume I have tables user, and logins, with id fields). After this query has been parsed and created into a plan tree, how can I get the char* form of the attribute name on which my WHERE clause searches on (in this case, user.id and logins.id)? Thanks a bunch guys -TJ -- 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] Auto-updated fields
On Thu, 2008-05-08 at 00:41 -0400, Tom Lane wrote: Tino Wildenhain [EMAIL PROTECTED] writes: I may be wrong but my feeling is, not to much weirdness in the core please :) +1 ... we have wasted more than enough man-hours trying to get the magic serial type to play nicely. If I had it to do over, we'd never have put that in at all. The underlying mechanisms are perfectly good --- it's the idea that the user shouldn't need to know what they're doing that causes problems. This kind of hiding will mostly hit the Leaky Abstraction pattern http://www.joelonsoftware.com/articles/LeakyAbstractions.html http://en.wikipedia.org/wiki/Leaky_abstraction Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] constraint exclusion analysis caching
Yesterday a client and I were sad to discover that the overhead of constraint exclusion is apparently O(n) in the number of partitions, and that where we had ~180 partitions each with a simple constraint (check (field = nnn)) the overhead appeared to amount to about 0.25s on some quite performant hardware, which is way too high for our application. Actual execution of the query in question was talking one tenth of that time. For now we're going to work around this by directing the queries directly to the child tables, although this does involve fairly large application changes. However, I wondered if we couldn't mitigate this by caching the results of constraint exclusion analysis for a particular table + condition. I have no idea how hard this would be, but in principle it seems silly to keep paying the same penalty over and over again. Thoughts? 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] constraint exclusion analysis caching
On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote: However, I wondered if we couldn't mitigate this by caching the results of constraint exclusion analysis for a particular table + condition. I have no idea how hard this would be, but in principle it seems silly to keep paying the same penalty over and over again. This would be a perfect candidate for the plan-branch based on actual parameters capability, in association with globally cached plans mentioned here: http://archives.postgresql.org/pgsql-hackers/2008-04/msg00920.php Cheers, Csaba. -- 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] Auto-updated fields
On Thu, May 08, 2008 at 08:44:46AM +0200, Martijn van Oosterhout wrote: On Wed, May 07, 2008 at 03:04:49PM -0700, David Fetter wrote: 1. Create a generic (possibly overloaded) trigger function, bundled with PostgreSQL, which sets a field to some value. For example, a timestamptz version might set the field to now(). Doesn't the SQL standard GENERATED BY functionality work for this? Or won't that handle updates? It appears to, at least according to 6WD2_02_Foundation_2007-12.pdf :) 4.14.8 Base columns and generated columns A column of a base table is either a base column or a generated column. A base column is one that is not a generated column. A generated column is one whose values are determined by evaluation of a generation expression, a value expression whose declared type is by implication that of the column. A generation expression can reference base columns of the base table to which it belongs but cannot otherwise access SQL data. Thus, the value of the field corresponding to a generated column in row R is determined by the values of zero or more other fields of R. A generated column GC depends on each column that is referenced by a column reference in its generation expression, and each such referenced column is a parametric column of GC. Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] 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] Table inheritance surprise
On May 7, 2008, at 4:52 PM, David Fetter wrote: When I do CREATE TABLE foo(LIKE bar INCLUDING CONSTRAINTS), it doesn't include foreign key constraints (8.3.1). I believe this is surprising behavior, but maybe not a bug, so I'd like to propose another bit of syntactic sugar, namely LIKE [INCLUDING FOREIGN KEYS] which would do what it looks like it does. It's surprising to me that INCLUDING CONSTRAINTS doesn't include FK constraints... is there a reason not to? Perhaps we should just change INCLUDING CONSTRAINTS to do that... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] constraint exclusion analysis caching
Andrew Dunstan [EMAIL PROTECTED] writes: Yesterday a client and I were sad to discover that the overhead of constraint exclusion is apparently O(n) in the number of partitions, and that where we had ~180 partitions each with a simple constraint (check (field = nnn)) the overhead appeared to amount to about 0.25s on some quite performant hardware, which is way too high for our application. I would think that any sort of formal partitioning feature would fix the problem, because the planner would understand directly about partitioning instead of having to prove the correctness of not scanning each one of the other 179 partitions. The existing feature is cool in the sense of obtaining useful behavior from generalized spare parts, but it was never designed or expected to give great planning speed with large numbers of partitions. TFM points out that constraint exclusion cannot scale beyond perhaps a hundred partitions ... 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] constraint exclusion analysis caching
Andrew Dunstan [EMAIL PROTECTED] writes: Actual execution of the query in question was talking one tenth of that time. ... but in principle it seems silly to keep paying the same penalty over and over again. I would think constraint_exclusion only really makes sense if you're spending a lot more time executing than planning queries. Either that means you're preparing queries once and then executing them many many times or you're planning much slower queries where planning time is insignificant compared to the time to execute them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- 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] [0/4] Proposal of SE-PostgreSQL patches
KaiGai Kohei [EMAIL PROTECTED] writes:
Some of the test fails contains minor differences from expected results, like:
| SELECT '' AS xxx, *
| FROM J1_TBL t1 (a, b, c) NATURAL JOIN J2_TBL t2 (d, a);
|xxx | a | b | c | d
| -+---+---+--+---
| - | 0 | | zero |
|| 2 | 3 | two | 2
|| 4 | 1 | four | 2
| + | 0 | | zero |
| (3 rows)
Yeah, I remember those. What needs to be looked at here is *why* the
output is changing. For a patch that allegedly does not touch the
planner, it's fairly disturbing that you don't get the same results.
and, some of them are trivial ones, like:
| SELECT p1.oid, p1.typname
| FROM pg_type as p1
| WHERE p1.typtype in ('b','e') AND p1.typname NOT LIKE E'\\_%' AND NOT
EXISTS
| (SELECT 1 FROM pg_type as p2
|WHERE p2.typname = ('_' || p1.typname)::name AND
| p2.typelem = p1.oid and p1.typarray = p2.oid);
| - oid | typname
| --+-
| - 210 | smgr
| - 705 | unknown
| -(2 rows)
| + oid |typname
| +--+
| + 210 | smgr
| + 705 | unknown
| + 3403 | security_label
| +(3 rows)
Are you sure that the security_label type should not have an array type?
I do not offhand see a good argument for that. If it really shouldn't,
we can change the expected output here, but you've got to make that
case first.
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] constraint exclusion analysis caching
On Fri, 2008-05-09 at 08:47 -0400, Andrew Dunstan wrote: Yesterday a client and I were sad to discover that the overhead of constraint exclusion is apparently O(n) in the number of partitions, and that where we had ~180 partitions each with a simple constraint (check (field = nnn)) the overhead appeared to amount to about 0.25s on some quite performant hardware, which is way too high for our application. Actual execution of the query in question was talking one tenth of that time. For now we're going to work around this by directing the queries directly to the child tables, although this does involve fairly large application changes. However, I wondered if we couldn't mitigate this by caching the results of constraint exclusion analysis for a particular table + condition. I have no idea how hard this would be, but in principle it seems silly to keep paying the same penalty over and over again. I think the only way forward is to put an index across the constraints, to allow the exclusion time to be O(logN). Currently the constraints are all independent of each other and can even overlap. So we would need a way of * confirming that the partitions are non-overlapping * defining some structure to them, to allow them to be organised in a sequence that allows either a bsearch or an index to exist The latter requires some kind of top-down definition, which hopefully is on the way from Gavin. This can then allow exclusion to take place dynamically within the executor, to allow a form of nested join. My other requirements are noted here... http://wiki.postgresql.org/wiki/Image:Partitioning_Requirements.pdf I'm not working on this at all at the moment. -- Simon Riggs 2ndQuadrant http://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] [PATCHES] [NOVICE] encoding problems
Bruce Momjian [EMAIL PROTECTED] writes: Guillaume Smet wrote: I understand your point of view but I really think it's more a regression fix than a behavior change. If I can get other hackers to say we should backpatch we can consider it. Well, 8.3 is already different from 8.2, and a lot of people will see this particular aspect of it as a regression. I'm okay with backpatching to 8.3 ... though the patch needed rather more testing than you gave it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] [NOVICE] encoding problems
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Guillaume Smet wrote: I understand your point of view but I really think it's more a regression fix than a behavior change. If I can get other hackers to say we should backpatch we can consider it. Well, 8.3 is already different from 8.2, and a lot of people will see this particular aspect of it as a regression. I'm okay with backpatching to 8.3 ... though the patch needed rather more testing than you gave it. OK, so Alvaro and Tom want this backpatched. However, it isn't going to match 8.2 behavior --- is that OK? -- Bruce Momjian [EMAIL PROTECTED]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] [PATCHES] [NOVICE] encoding problems
Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Well, 8.3 is already different from 8.2, and a lot of people will see this particular aspect of it as a regression. I'm okay with backpatching to 8.3 ... though the patch needed rather more testing than you gave it. OK, so Alvaro and Tom want this backpatched. However, it isn't going to match 8.2 behavior --- is that OK? Huh? 8.3 is already hugely different from 8.2 because of the newline formatting changes. 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] [COMMITTERS] pgsql: Improve logic for finding object files on OBJS lines in contrib
Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: Log Message: --- Improve logic for finding object files on OBJS lines in contrib Makefiles. If this unbreaks buildfarm mastodon, apply everywhere. I start to wonder why don't we just implement our own make in Perl ... http://search.cpan.org/~mhosken/Font-Fret-1.202/pmake.bat 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] Re: [COMMITTERS] pgsql: Improve logic for finding object files on OBJS lines in contrib
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: Log Message: --- Improve logic for finding object files on OBJS lines in contrib Makefiles. If this unbreaks buildfarm mastodon, apply everywhere. I start to wonder why don't we just implement our own make in Perl ... http://search.cpan.org/~mhosken/Font-Fret-1.202/pmake.bat The point is not to emulate make. Gmake for windows already exists, anyway. The point is that building for MSVC is so very different from the way you build everywhere else. Our current tools build MSVC project files and then drive the build from there. Having a make equivalent won't help us much. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Improve logic for finding object files on OBJS lines in contrib
Andrew Dunstan wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: Log Message: --- Improve logic for finding object files on OBJS lines in contrib Makefiles. If this unbreaks buildfarm mastodon, apply everywhere. I start to wonder why don't we just implement our own make in Perl ... http://search.cpan.org/~mhosken/Font-Fret-1.202/pmake.bat The point is not to emulate make. Gmake for windows already exists, anyway. The point is that building for MSVC is so very different from the way you build everywhere else. Our current tools build MSVC project files and then drive the build from there. Having a make equivalent won't help us much. Right. The easiest way if you're building something for scratch is to use a system that natively supports msvc, such as cmake. But that means a complete replacement of the build system, which is certainly somewhat invasive.. ;-) //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Bug 3883 revisited
The TRUNCATE table while we're holding references to it bug (3883), is causing an assertion failure on 8.2, when the TRUNCATE is called in a trigger: Script: CREATE TABLE proc(n int); INSERT INTO proc VALUES (9); CREATE OR REPLACE FUNCTION deltrig() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN EXECUTE 'TRUNCATE TABLE proc'; RETURN OLD; end; $$; CREATE TRIGGER trg_proc BEFORE DELETE ON PROC FOR EACH ROW EXECUTE PROCEDURE deltrig(); DELETE FROM proc WHERE n=9; Error message: TRAP: FailedAssertion(!(( ((void) ((bool) ((! assert_enabled) || ! (!(((void*)(lp) != ((void *)0 || (ExceptionalCondition(!(((void*)(lp) != ((void *)0))), (FailedAssertion), heapam.c, 1595), (bool) (((lp)-lp_flags 0x01) != 0) )), File: heapam.c, Line: 1595) With assertions disabled, you get an attempted to delete invisible tuple error, but it seems like good luck that it doesn't lead to a crash. I think we need to backpatch the fix for this... -- 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] Re: [COMMITTERS] pgsql: Improve logic for finding object files on OBJS lines in contrib
Andrew Dunstan wrote: Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Andrew Dunstan wrote: Log Message: --- Improve logic for finding object files on OBJS lines in contrib Makefiles. If this unbreaks buildfarm mastodon, apply everywhere. I start to wonder why don't we just implement our own make in Perl ... http://search.cpan.org/~mhosken/Font-Fret-1.202/pmake.bat The point is not to emulate make. Gmake for windows already exists, anyway. The point is that building for MSVC is so very different from the way you build everywhere else. Our current tools build MSVC project files and then drive the build from there. Having a make equivalent won't help us much. What I was actually thinking was using this make reimplementation to generate the MSVC project files, instead of parsing the makefiles to do the same. So we would need extra Makefiles with infrastructure to do that, but we would get away from the text parsing business. -- 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
[HACKERS] psql wrapped format default for backslash-d commands
Now that psql '\pset format wrapped' is in CVS, we should consider when
we want to use 'wrapped' format by default. I think psql \df and \dT
certainly can benefit from wrapped mode. \df+ even displays, though
there is quite a bit of wrapping.
The attached patch uses wrapped format for \d* output if the current
output format is 'aligned'. It certainly helps the \df and \dT because
those outputs have normally narrow values with a few wide values that
cause aligned output to be much too wide. The downside of the patch is
that you can no longer get 'aligned' output for \d* commands anymore
because there is no way to know if the user specified 'aligned' or if
they are just using the default.
This opens the larger question that if 'wrapped' helps with \df and \dT,
wouldn't it help with other wide user query output.
Now, we can discard this patch and tell people to set their output to
'wrapped' when they issue \d commands, but it is unlikely they will do
so. We can also tell people to just default to 'wrapped' in their
.psqlrc and then their \d commands and user queries will be in
'wrapped', or we can just default 'format' to 'wrapped' for psql.
I have heard a few people say they never way to see 'wrapped' output so
perhaps we should do nothing, but I then question whether we want \df
and \dT to be hard to read by default.
--
Bruce Momjian [EMAIL PROTECTED]http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: src/bin/psql/command.c
===
RCS file: /cvsroot/pgsql/src/bin/psql/command.c,v
retrieving revision 1.188
diff -c -c -r1.188 command.c
*** src/bin/psql/command.c 8 May 2008 17:04:26 - 1.188
--- src/bin/psql/command.c 9 May 2008 17:36:38 -
***
*** 314,319
--- 314,324
{
char *pattern;
bool show_verbose;
+ enum printFormat saved_format = pset.popt.topt.format;
+
+ /* Use wrapped mode in hopes of displaying output cleanly. */
+ if (pset.popt.topt.format == PRINT_ALIGNED)
+ pset.popt.topt.format = PRINT_WRAPPED;
/* We don't do SQLID reduction on the pattern yet */
pattern = psql_scan_slash_option(scan_state,
***
*** 407,412
--- 412,419
status = PSQL_CMD_UNKNOWN;
}
+ pset.popt.topt.format = saved_format; /* restore */
+
if (pattern)
free(pattern);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gsoc08, text search selectivity, pg_statistics holding an array of a different type
Hi, hackers.
I've been fooling around my GSoC project, and here's the first version
I'm not actually ashamed of showing.
There's one fundamental problem I came across while writing a typanalyze
function for tsvectors.
update_attstats() constructs an array that's later inserted into the
appropriate stavaluesN for a given relation attribute. However, it
assumes that the elements of that array will be of the same type as
their corresponding attribute.
It is no longer true with the design that I planned to use. The
typanalyze function for the tsvector type returns an array of
most-frequent lexemes (cstrings actually) from the tsvectors, not an
array of tsvectors. The question is: is this approach OK? Should
typanalyze functions be able to communicate the type of their result to
analyze_rel() ? I'm thinking of extending the VacAttrStats structure, so
a typanalyze func could set the proper fields to the proper values.
The problem is currently worked-around by brute force - I just wanted to
get it working.
The patch as-is makes ANALYZE store the most-frequent lexemes from
tsvectors in pg_statistics and passes all regression tests. It's of
course WIP (yes, throwing NOTICEs all over the place isn't my ultimate
goal), but the XXXs are things I'm really not sure how to implement. Any
comment on them would be appreciated.
You can also browse to
http://git.postgresql.org/?p=~wulczer/gsoc08-tss.git;a=summary or clone
git://git.postgresql.org/git/~wulczer/gsoc08-tss.git, if you're
interested in the progress.
Cheers,
Jan
PS: should I be posting this to -patches, as it has a patch? I figured
no, because it's not something meant to be applied, just a convenient
way of showing what's it all about.
--
Jan Urbanski
GPG key ID: E583D7D2
ouden estin
*** src/backend/commands/analyze.c
--- /tmp/.diff_IHT3Qe 2008-05-09 19:38:06.0 +0200
***
*** 1319,1330
{
ArrayType *arry;
! arry = construct_array(stats-stavalues[k],
!
stats-numvalues[k],
!
stats-attr-atttypid,
!
stats-attrtype-typlen,
!
stats-attrtype-typbyval,
!
stats-attrtype-typalign);
values[i++] = PointerGetDatum(arry);/*
stavaluesN */
}
else
--- 1319,1350
{
ArrayType *arry;
! /*
!* XXX horrible hack - we're creating a
pg_statistic tuple for
!* a tsvector, but need to store an array of
cstrings.
!*
!* Temporary measures...
!*/
! if (stats-stakind[0] == STATISTIC_KIND_MCL)
! {
! elog(NOTICE, severly breaking stuff by
brute force hackage);
! arry =
construct_array(stats-stavalues[k],
!
stats-numvalues[k],
!
CSTRINGOID,
!
-2, /* typlen, -2 for cstring, per
!
* comment from pg_type.h */
!
false,
!
'c');
! }
! else
! {
! arry =
construct_array(stats-stavalues[k],
!
stats-numvalues[k],
!
stats-attr-atttypid,
!
stats-attrtype-typlen,
!
stats-attrtype-typbyval,
!
stats-attrtype-typalign);
! }
values[i++] = PointerGetDatum(arry);/*
stavaluesN */
}
else
***
Re: [HACKERS] psql wrapped format default for backslash-d commands
On Sat, May 10, 2008 at 3:52 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Now that psql '\pset format wrapped' is in CVS, we should consider when we want to use 'wrapped' format by default. I think psql \df and \dT certainly can benefit from wrapped mode. \df+ even displays, though there is quite a bit of wrapping. I for one would definitely like backslash commands with very wide output to be wrapped by default. The attached patch uses wrapped format for \d* output if the current output format is 'aligned'. It certainly helps the \df and \dT because those outputs have normally narrow values with a few wide values that cause aligned output to be much too wide. The downside of the patch is that you can no longer get 'aligned' output for \d* commands anymore because there is no way to know if the user specified 'aligned' or if they are just using the default. Doesn't this suggest that we need an auto formatting mode which has the semantics let psql decide what format mode I want? Then the people who object to wrapped mode can just set formatting to aligned explicitly and be done with it. I seem to recall there was some discussion of an auto mode in the original wrapping thread, but if there was any meaningful conclusion I lost it in amongst the width detection flame war. 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] psql wrapped format default for backslash-d commands
Brendan Jurd escribió: On Sat, May 10, 2008 at 3:52 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Now that psql '\pset format wrapped' is in CVS, we should consider when we want to use 'wrapped' format by default. I think psql \df and \dT certainly can benefit from wrapped mode. \df+ even displays, though there is quite a bit of wrapping. I for one would definitely like backslash commands with very wide output to be wrapped by default. (At least) one place where I would not like it is in \df+, because wrapped function output would be more difficult to read. -- 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] psql wrapped format default for backslash-d commands
On Sat, May 10, 2008 at 4:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Brendan Jurd escribió: I for one would definitely like backslash commands with very wide output to be wrapped by default. (At least) one place where I would not like it is in \df+, because wrapped function output would be more difficult to read. I am a bit conflicted about wrapping on \df. I agree that wrapped function code is difficult to read, but what we've got now is difficult to read too. Which of the two is more difficult is really a matter of personal taste. I guess with \df you have to accept that it's always going to be ugly, unless you have a very wide terminal (or very short function definitions!). 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] psql wrapped format default for backslash-d commands
* Brendan Jurd [EMAIL PROTECTED] [080509 14:43]: On Sat, May 10, 2008 at 4:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Brendan Jurd escribió: I for one would definitely like backslash commands with very wide output to be wrapped by default. (At least) one place where I would not like it is in \df+, because wrapped function output would be more difficult to read. I am a bit conflicted about wrapping on \df. I agree that wrapped function code is difficult to read, but what we've got now is difficult to read too. Which of the two is more difficult is really a matter of personal taste. I guess with \df you have to accept that it's always going to be ugly, unless you have a very wide terminal (or very short function definitions!). Isn't that what $PAGER is for? $PAGER is the main reason why *I* don't want the wrapped format. a. -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
[HACKERS] bloated heapam.h
Hi, I noticed heapam.h is included in way too many places. This is bad IMHO because heapam.h itself includes a lot of other headers. A lot of these are easy to fix; the source files just need to include some other headers. Standard cleanup, I don't think anybody would object to that. For example, Index: src/backend/access/gin/ginvacuum.c === RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/access/gin/ginvacuum.c,v retrieving revision 1.19 diff -c -p -r1.19 ginvacuum.c *** src/backend/access/gin/ginvacuum.c 1 Jan 2008 19:45:46 - 1.19 --- src/backend/access/gin/ginvacuum.c 9 May 2008 18:44:31 - *** *** 15,24 #include postgres.h #include access/genam.h #include access/gin.h - #include access/heapam.h #include miscadmin.h #include storage/freespace.h ! #include storage/freespace.h #include commands/vacuum.h typedef struct --- 15,23 #include postgres.h #include access/genam.h #include access/gin.h #include miscadmin.h #include storage/freespace.h ! #include storage/lmgr.h #include commands/vacuum.h typedef struct Others are more conflictive. For example syncscan.c is keeping the prototypes for its own functions on heapam.h. Also pruneheap.c and rewriteheap.c. As a result, not only themselves need to include heapam.h (without any other need for it), but they force some other files into including heapam.h to get their prototypes. I think this is a mistake; I propose splitting those prototypes to their own files, and #including those as appropriate. Objections? -- 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] constraint exclusion analysis caching
* Gregory Stark ([EMAIL PROTECTED]) wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Actual execution of the query in question was talking one tenth of that time. ... but in principle it seems silly to keep paying the same penalty over and over again. I would think constraint_exclusion only really makes sense if you're spending a lot more time executing than planning queries. Either that means you're preparing queries once and then executing them many many times or you're planning much slower queries where planning time is insignificant compared to the time to execute them. Would it be possible to change the application to use prepared queries? Seems like that'd make more sense the changing it to use the child tables directly.. Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] psql wrapped format default for backslash-d commands
Brendan Jurd wrote: [ email paragraphs reordered.] I seem to recall there was some discussion of an auto mode in the original wrapping thread, but if there was any meaningful conclusion I lost it in amongst the width detection flame war. I wasn't going to bring up the 'auto' idea yet because it might confuse things, but I guess I should address it now. wrapped format doesn't wrap if the column headings don't fit the screen width _without_ wrapping (it then uses 'aligned'). My idea was for an 'auto' format mode to use wrapped, or expanded if wrapped can't be displayed. The attached patch uses wrapped format for \d* output if the current output format is 'aligned'. It certainly helps the \df and \dT because those outputs have normally narrow values with a few wide values that cause aligned output to be much too wide. The downside of the patch is that you can no longer get 'aligned' output for \d* commands anymore because there is no way to know if the user specified 'aligned' or if they are just using the default. Doesn't this suggest that we need an auto formatting mode which has the semantics let psql decide what format mode I want? The big problem is unless we default psql to 'auto' we can't know if they really want 'aligned', 'wrapped', or whatever looks good on the screen, 'auto'. Then the people who object to wrapped mode can just set formatting to aligned explicitly and be done with it. Right, but 'wrapped' is kind of a dramatic change so we have to be sure most people wanted 'wrapped' (or 'auto'). I think the way we did the detection defaults will help because a default of 'auto' will only affect screen output, by default. -- Bruce Momjian [EMAIL PROTECTED]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] gsoc08, text search selectivity, pg_statistics holding an array of a different type
Jan Urbański wrote: I've been fooling around my GSoC project, and here's the first version I'm not actually ashamed of showing. Oh, wow, at this speed you'll be done before the summer even starts ;-) There's one fundamental problem I came across while writing a typanalyze function for tsvectors. update_attstats() constructs an array that's later inserted into the appropriate stavaluesN for a given relation attribute. However, it assumes that the elements of that array will be of the same type as their corresponding attribute. Yep, those stavalues fields are quite a hack... It is no longer true with the design that I planned to use. The typanalyze function for the tsvector type returns an array of most-frequent lexemes (cstrings actually) from the tsvectors, not an array of tsvectors. The question is: is this approach OK? Should typanalyze functions be able to communicate the type of their result to analyze_rel() ? I'm thinking of extending the VacAttrStats structure, so a typanalyze func could set the proper fields to the proper values.re Hmm. One idea is to store an array of tsvectors, with only one lexeme in each tsvector. -- 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] constraint exclusion analysis caching
Stephen Frost wrote: * Gregory Stark ([EMAIL PROTECTED]) wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Actual execution of the query in question was talking one tenth of that time. ... but in principle it seems silly to keep paying the same penalty over and over again. I would think constraint_exclusion only really makes sense if you're spending a lot more time executing than planning queries. Either that means you're preparing queries once and then executing them many many times or you're planning much slower queries where planning time is insignificant compared to the time to execute them. Would it be possible to change the application to use prepared queries? Seems like that'd make more sense the changing it to use the child tables directly.. Just my 2c. This is actually a technique already used elsewhere in the app, so it will fit quite well. Thanks for the suggestion, though. (BTW, why does your MUA set Mail-Followup-To: (and do it badly, what's more) ?) 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] constraint exclusion analysis caching
* Andrew Dunstan ([EMAIL PROTECTED]) wrote: Seems like that'd make more sense the changing it to use the child tables directly.. Just my 2c. This is actually a technique already used elsewhere in the app, so it will fit quite well. Thanks for the suggestion, though. Sure. (BTW, why does your MUA set Mail-Followup-To: (and do it badly, what's more) ?) I'm amazed at the number of people who ask me this.. Guess it's just different for different communities. Basically, I like to keep my mail in the different folders it belongs in, so I'd rather get responses to my emails through the list than directly to me. Additionally, I don't really need to get two copies of every email sent to me on a mailing list. It's actually really frowned upon in the Debian community to not respect MFT and it's common to have it set to just the mailing list. More information about it: http://cr.yp.to/proto/replyto.html Enjoy, Stephen signature.asc Description: Digital signature
Re: [HACKERS] psql wrapped format default for backslash-d commands
Brendan Jurd [EMAIL PROTECTED] wrote: On Sat, May 10, 2008 at 3:52 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Now that psql '\pset format wrapped' is in CVS, we should consider when we want to use 'wrapped' format by default. I think psql \df and \dT certainly can benefit from wrapped mode. \df+ even displays, though there is quite a bit of wrapping. I for one would definitely like backslash commands with very wide output to be wrapped by default. I would prefer the default to be the current 8.2 behavior. As long as I can configure that easily it's not a huge deal, but I may have to deal with some users whose ad hoc queries feed into spreadsheets or some such that will be broken until they do something new. I currently use \x to view results about once a month (on average). I expect to find wrapped columns useful about that often. I cut and paste multi-line unwrapped output from my console window practically daily, and the wrapping would make that painful. That just got me thinking -- has anyone tried this out with EXPLAIN ANALYZE output yet? -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] psql wrapped format default for backslash-d commands
Brendan Jurd wrote: On Sat, May 10, 2008 at 4:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Brendan Jurd escribi?: I for one would definitely like backslash commands with very wide output to be wrapped by default. (At least) one place where I would not like it is in \df+, because wrapped function output would be more difficult to read. I am a bit conflicted about wrapping on \df. I agree that wrapped function code is difficult to read, but what we've got now is difficult to read too. Which of the two is more difficult is really a matter of personal taste. I guess with \df you have to accept that it's always going to be ugly, unless you have a very wide terminal (or very short function definitions!). Oh, good point. I hadn't thought about function bodies being displayed. Here is an example: test= \pset format wrapped Output format is wrapped. test= \pset columns 14 Target width for wrapped format is 14. test= select prosrc from pg_proc where proname = 'xx'; prosrc -- SELECT 'a': :text WHERE 1 = 1 (1 row) -- Bruce Momjian [EMAIL PROTECTED]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] psql wrapped format default for backslash-d commands
Aidan Van Dyk wrote: -- Start of PGP signed section. * Brendan Jurd [EMAIL PROTECTED] [080509 14:43]: On Sat, May 10, 2008 at 4:37 AM, Alvaro Herrera [EMAIL PROTECTED] wrote: Brendan Jurd escribi?: I for one would definitely like backslash commands with very wide output to be wrapped by default. (At least) one place where I would not like it is in \df+, because wrapped function output would be more difficult to read. I am a bit conflicted about wrapping on \df. I agree that wrapped function code is difficult to read, but what we've got now is difficult to read too. Which of the two is more difficult is really a matter of personal taste. I guess with \df you have to accept that it's always going to be ugly, unless you have a very wide terminal (or very short function definitions!). Isn't that what $PAGER is for? $PAGER is the main reason why *I* don't want the wrapped format. Not all pagers scroll width-wise --- in fact I thought most didn't. -- Bruce Momjian [EMAIL PROTECTED]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] psql wrapped format default for backslash-d commands
Kevin Grittner wrote: Brendan Jurd [EMAIL PROTECTED] wrote: On Sat, May 10, 2008 at 3:52 AM, Bruce Momjian [EMAIL PROTECTED] wrote: Now that psql '\pset format wrapped' is in CVS, we should consider when we want to use 'wrapped' format by default. I think psql \df and \dT certainly can benefit from wrapped mode. \df+ even displays, though there is quite a bit of wrapping. I for one would definitely like backslash commands with very wide output to be wrapped by default. I would prefer the default to be the current 8.2 behavior. As long as I can configure that easily it's not a huge deal, but I may have to deal with some users whose ad hoc queries feed into spreadsheets or some such that will be broken until they do something new. I currently use \x to view results about once a month (on average). I expect to find wrapped columns useful about that often. I cut and paste multi-line unwrapped output from my console window practically daily, and the wrapping would make that painful. That just got me thinking -- has anyone tried this out with EXPLAIN ANALYZE output yet? No, but I just tried it: test= \pset format wrapped Output format is wrapped. test= \pset columns 50 Target width for wrapped format is 50. test= explain analyze select * from pg_type, pg_language; QUERY PLAN -- Nested Loop (cost=1.03..25.86 rows=807 width=66 5) (actual time=0.037..4.528 rows=807 loops=1) - Seq Scan on pg_type (cost=0.00..8.69 rows =269 width=555) (actual time=0.018..0.377 rows=2 69 loops=1) - Materialize (cost=1.03..1.06 rows=3 width =110) (actual time=0.001..0.005 rows=3 loops=269 ) - Seq Scan on pg_language (cost=0.00. .1.03 rows=3 width=110) (actual time=0.004..0.00 7 rows=3 loops=1) Total runtime: 5.490 ms (5 rows) Of course, running it on a 50-column display in 'aligned' mode isn't going to look good either. -- Bruce Momjian [EMAIL PROTECTED]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] psql wrapped format default for backslash-dcommands
On Fri, May 9, 2008 at 3:53 PM, in message [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote: test= \pset format wrapped Output format is wrapped. test= \pset columns 50 Target width for wrapped format is 50. test= explain analyze select * from pg_type, pg_language; QUERY PLAN -- Nested Loop (cost=1.03..25.86 rows=807 width=66 5) (actual time=0.037..4.528 rows=807 loops=1) - Seq Scan on pg_type (cost=0.00..8.69 rows =269 width=555) (actual time=0.018..0.377 rows=2 69 loops=1) - Materialize (cost=1.03..1.06 rows=3 width =110) (actual time=0.001..0.005 rows=3 loops=269 ) - Seq Scan on pg_language (cost=0.00. .1.03 rows=3 width=110) (actual time=0.004..0.00 7 rows=3 loops=1) Total runtime: 5.490 ms (5 rows) Of course, running it on a 50-column display in 'aligned' mode isn't going to look good either. With current production software, if I run in a 50 column window and cut and paste into email, there aren't any line-ends in the email as there would be with wrapped format. -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] psql wrapped format default for backslash-dcommands
Kevin Grittner wrote: On Fri, May 9, 2008 at 3:53 PM, in message [EMAIL PROTECTED], Bruce Momjian [EMAIL PROTECTED] wrote: test= \pset format wrapped Output format is wrapped. test= \pset columns 50 Target width for wrapped format is 50. test= explain analyze select * from pg_type, pg_language; QUERY PLAN -- Nested Loop (cost=1.03..25.86 rows=807 width=66 5) (actual time=0.037..4.528 rows=807 loops=1) - Seq Scan on pg_type (cost=0.00..8.69 rows =269 width=555) (actual time=0.018..0.377 rows=2 69 loops=1) - Materialize (cost=1.03..1.06 rows=3 width =110) (actual time=0.001..0.005 rows=3 loops=269 ) - Seq Scan on pg_language (cost=0.00. .1.03 rows=3 width=110) (actual time=0.004..0.00 7 rows=3 loops=1) Total runtime: 5.490 ms (5 rows) Of course, running it on a 50-column display in 'aligned' mode isn't going to look good either. With current production software, if I run in a 50 column window and cut and paste into email, there aren't any line-ends in the email as there would be with wrapped format. I think that depends on what terminal you are using, but I do see some of my terminal programs understand when wrapping happens and adjust the cut/paste behavior. -- Bruce Momjian [EMAIL PROTECTED]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] psql wrapped format default for backslash-d commands
Bruce Momjian escribió: Of course, running it on a 50-column display in 'aligned' mode isn't going to look good either. This is what I get by pasting from a 50-column aligned psql (8.3): QUERY PLAN --- Nested Loop (cost=1.03..25.86 rows=807 width=673) (actual time=4.349..20.973 rows=1897 loops=1) - Seq Scan on pg_type (cost=0.00..8.69 rows=269 width=563) (actual time=0.019..0.798 rows=271 loops=1) - Materialize (cost=1.03..1.06 rows=3 width=110) (actual time=0.018..0.033 rows=7 loops=271) - Seq Scan on pg_language (cost=0.00..1.03 rows=3 width=110) (actual time=4.296..4.312 rows=7 loops=1) Total runtime: 35.041 ms (5 lignes) No unwanted line breaks. Of course, on the terminal it looks narrower, but this doesn't affect cut'n paste behavior. My conclusion is that we have to make very sure that wrapped is not the default for explain. FWIW this is expanded output: alvherre=# \x Affichage étendu activé. alvherre=# explain analyze select * from pg_type, pg_language; -[ RECORD 1 ]- QUERY PLAN | Nested Loop (cost=1.03..25.86 rows=807 width=673) (actual time=0.055..15.648 rows=1897 loops=1) -[ RECORD 2 ]- QUERY PLAN | - Seq Scan on pg_type (cost=0.00..8.69 rows=269 width=563) (actual time=0.019..0.623 rows=271 loops=1) -[ RECORD 3 ]- QUERY PLAN | - Materialize (cost=1.03..1.06 rows=3 width=110) (actual time=0.002..0.016 rows=7 loops=271) -[ RECORD 4 ]- QUERY PLAN | - Seq Scan on pg_language (cost=0.00..1.03 rows=3 width=110) (actual time=0.005..0.021 rows=7 loops=1) -[ RECORD 5 ]- QUERY PLAN | Total runtime: 19.649 ms -- 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] psql wrapped format default for backslash-d commands
Alvaro Herrera [EMAIL PROTECTED] wrote: Bruce Momjian escribió: My conclusion is that we have to make very sure that wrapped is not the default for explain. This will cause me similar pain in other areas. I'm glad I thought of an example with which others could easily identify. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deterministic locking in PostgreSQL
Hi everyone, This question may have an obvious answer I have somehow missed, but to what extent is locking order deterministic in PostgreSQL? For example, if requests from multiple transactions arrive in some deterministic order and acquire locks, can one assume that locks will be granted in the same order if the requests are repeated at different times or on different servers? Lock determinism is an important issue for replication algorithms that depend on database instances to behave as state machines. Here's a simple example of the behavior I'm seeking. Suppose you have transactions T1, T2, and T3 that execute as shown below. Each line represents an increment of time. T1, T2, T3: begin T1: update foo set value='x' where id=25; -- Grabs row lock T2: update foo set value='y' where id=25; -- Blocked T3: update foo set value='z' where id=25; -- Blocked T1: update foo set value='x1' where id=25; T1: commit T2: commit T3: commit T2 and T3 are both blocked until T1 commits. At that point, is the row lock granted to T2 and T3 in some deterministic order? Or can it vary based on load, lock manager state, etc., so that sometimes you get 'y' and sometimes 'z' as the final result? If this case turns out to be deterministic, are there other cases that come to mind that would turn out to be non-deterministic? Thanks, Robert -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm -- 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] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
Alex Hunsaker [EMAIL PROTECTED] writes: [ patch to change inherited-check-constraint behavior ] Applied after rather heavy editorializations. You didn't do very well on getting it to work in multiple-inheritance scenarios, such as create table p (f1 int check (f10)); create table c1 (f2 int) inherits (p); create table c2 (f3 int) inherits (p); create table cc () inherits (c1,c2); Here the same constraint is multiply inherited. The base case as above worked okay, but adding the constraint to an existing inheritance tree via ALTER TABLE, not so much. I also didn't like the choice to add is_local/inhcount fields to ConstrCheck: that struct is fairly heavily used, but you were leaving the fields undefined/invalid in most code paths, which would surely lead to bugs down the road when somebody expected them to contain valid data. I considered extending the patch to always set them up, but rejected that idea because ConstrCheck is essentially a creature of the executor, which couldn't care less about constraint inheritance. After some reflection I chose to put the fields in CookedConstraint instead, which is used only in the table creation / constraint addition code paths. That required a bit of refactoring of the API of heap_create_with_catalog, but I think it ended up noticeably cleaner: constraints and defaults are fed to heap.c in only one format now. I found one case that has not really worked as intended for a long time: ALTER TABLE ADD CHECK ... (that is, ADD CONSTRAINT without specifying a constraint name) failed to ensure that the same constraint name was used at child tables as at the parent, and thus the constraints ended up not being seen as related at all. Fixing this was a bit ugly since it meant that ADD CONSTRAINT has to recurse to child tables during Phase 2 after all, and has to be able to add work queue entries for Phase 3 at that time, which is not something needed by any other ALTER TABLE operation. I'm not sure if we ought to try to back-patch that --- it'd be a behavioral change with non-obvious implications. In the back branches, ADD CHECK followed by DROP CONSTRAINT will end up not deleting the child-table constraints, which is probably a bug but I wouldn't be surprised if applications were depending on the behavior. 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] Deterministic locking in PostgreSQL
Robert Hodges [EMAIL PROTECTED] writes: This question may have an obvious answer I have somehow missed, but to what extent is locking order deterministic in PostgreSQL? For example, if requests from multiple transactions arrive in some deterministic order and acquire locks, can one assume that locks will be granted in the same order if the requests are repeated at different times or on different servers? Yeah, it should be deterministic given consistent arrival order. Lock determinism is an important issue for replication algorithms that depend on database instances to behave as state machines. However, the idea of depending on a replication algorithm that has race conditions gives me the willies ... and that sure sounds like what you are describing. Do not trust your data to the assumption that arrival order will be deterministic. 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] gsoc08, text search selectivity, pg_statistics holding an array of a different type
Heikki Linnakangas [EMAIL PROTECTED] writes: Jan UrbaÅski wrote: It is no longer true with the design that I planned to use. The typanalyze function for the tsvector type returns an array of most-frequent lexemes (cstrings actually) from the tsvectors, not an array of tsvectors. The question is: is this approach OK? Should typanalyze functions be able to communicate the type of their result to analyze_rel() ? I'm thinking of extending the VacAttrStats structure, so a typanalyze func could set the proper fields to the proper values.re Hmm. One idea is to store an array of tsvectors, with only one lexeme in each tsvector. Jan's right: this is an oversight in the design of the VacAttrStats API. The existing pg_statistics slot types all need an array of the same datatype as the underlying column, but it's obvious when you think about it that there could be kinds of statistics that need to be stored as an array of some other type. I'm good with the idea of extending VacAttrStats for the purpose. (Whether it's actually a good idea to store the entries as cstrings is another question...) 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] [PATCHES] [EMAIL PROTECTED]: Re: [BUGS] Problem identifying constraints which should not be inherited]
On Fri, May 9, 2008 at 5:37 PM, Tom Lane [EMAIL PROTECTED] wrote: Alex Hunsaker [EMAIL PROTECTED] writes: [ patch to change inherited-check-constraint behavior ] Applied after rather heavy editorializations. You didn't do very well on getting it to work in multiple-inheritance scenarios, such as create table p (f1 int check (f10)); create table c1 (f2 int) inherits (p); create table c2 (f3 int) inherits (p); create table cc () inherits (c1,c2); Here the same constraint is multiply inherited. The base case as above worked okay, but adding the constraint to an existing inheritance tree via ALTER TABLE, not so much. Ouch. Ok Ill (obviously) review what you committed so I can do a lot better next time. Thanks for muddling through it! I also didn't like the choice to add is_local/inhcount fields to ConstrCheck: that struct is fairly heavily used, but you were leaving the fields undefined/invalid in most code paths, which would surely lead to bugs down the road when somebody expected them to contain valid data. I considered extending the patch to always set them up, but rejected that idea because ConstrCheck is essentially a creature of the executor, which couldn't care less about constraint inheritance. After some reflection I chose to put the fields in CookedConstraint instead, which is used only in the table creation / constraint addition code paths. That required a bit of refactoring of the API of heap_create_with_catalog, but I think it ended up noticeably cleaner: constraints and defaults are fed to heap.c in only one format now. That sounds *way* cleaner and hopefully got rid of some of those gross hacks I had to do. Interestingly enough thats similar to how I initially started doing it. But it felt way to intrusive, so i dropped it. Course I then failed the non-intrusive with the ConstrCheck changes... I found one case that has not really worked as intended for a long time: ALTER TABLE ADD CHECK ... (that is, ADD CONSTRAINT without specifying a constraint name) failed to ensure that the same constraint name was used at child tables as at the parent, and thus the constraints ended up not being seen as related at all. Fixing this was a bit ugly since it meant that ADD CONSTRAINT has to recurse to child tables during Phase 2 after all, and has to be able to add work queue entries for Phase 3 at that time, which is not something needed by any other ALTER TABLE operation. Ouch... I'm not sure if we ought to try to back-patch that --- it'd be a behavioral change with non-obvious implications. In the back branches, ADD CHECK followed by DROP CONSTRAINT will end up not deleting the child-table constraints, which is probably a bug but I wouldn't be surprised if applications were depending on the behavior. Given the lack complaints it does not seem worth a back patch IMHO. 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] [PATCHES] Database owner installable modules patch
Where are we on this?
---
Tom Dunstan wrote:
Hi all
Here is a patch that provides an initial implementation of the module
idea that was kicked around over the last few days. While there
certainly wasn't consensus on list, enough people seemed interested in
the idea of database-owner-installable modules that I thought it was
worth having a play with.
The general idea, to recap, is to have modules, whether included in
the distribution a la contrib or installed separately, installed under
a directory such as $pkglib_dir/modules/foo. A typical module
directory might contain:
- foo.so/foo.dll
- install.sql
- uninstall.sql
- foo.conf
- some-other-file-needed-by-foo-module.dat
The module would be installed on the system, but the necessary scripts
to install it in a particular database have not been run. In
particular, the modules would not usually be install in template1.
Database owners themselves can then opt to enable a particular
installed module in their own database - they do not have to hassle a
sysadmin to do it for them.
Features of the patch:
- A database owner can issue the command INSTALL MODULE foo, and
pgsql will look for a $pkglib_dir/modules/foo/install.sql file to run,
and run it.
- The install script can do pretty much anything - the user is
treated as the superuser for the duration of the script. The main and
obvious usage is to create C language functions required by the
module.
- An entry is created in a new pg_module catalog. This is mainly to
guard against someone trying to install a module twice at this point,
but it may have other uses in the future (see below).
- UNINSTALL MODULE foo looks for and executes
$pkglib_dir/modules/foo/uninstall.sql and cleans up the catalog.
Here is a list of things that are either still to do before I'd
consider it worthy of inclusion (should the general approach be
considered acceptable), or which I'd like some guidance on:
- Currently the script is executed in one big SPI_execute call, and
so errors and NOTICEs print out the entire script as context. I'm not
sure how to break it up without writing a full parser - we must have
something available in the backend to break a string up into multiple
statements to execute, but I'm not sure where to look. Also, is there
a better way to do this than SPI?
- I've hacked in a bit of an end-run around permissions checks to
make the current user look like a super-user while a module script is
running. Is there a better way to do this?
- I can't create C language functions from dlls under the modules
dir. I'd like to be able to specify 'modules/foo/foo' as the library
name, but the backend sees a slash and decides that must mean the path
is absolute. I see two ways to fix this: change the existing code in
dfmgr.c to *really* check for absolute/relative paths rather than the
current hack, or I could stick in a special case for when it starts
with modules/. I thought I'd get some guidance on-list. Do people
think that sticking the dll in with other resources for the module
under $pkglib_dir is a bad thing? (I think having everything in one
place is a very good thing myself).Is the existing check written the
way it is for a particular reason, or is it just good enough?
- It would be nice to create the empty modules dir when we install
pgsql, but while I suppose hacking a Makefile to install it is the way
to go, I'm not sure which one would be appropriate.
- Hack pgxs to install stuff into a modules dir if we give it some
appropriate flag.
- I'd like to add pg_depend entries for stuff installed by the module
on the pd_module entry, so that you can't drop stuff required by the
module without uninstalling the module itself. There would have to be
either a function or more syntax to allow a script to do that, or some
sort of module descriptor that let the backend do it itself.
- Once the issue of loading a dll from inside the module's directory
is done, I'd like to look for an e.g. module_install() function inside
there, and execute that rather than the install.sql if found. Ditto
for uninstall.
- Maybe a basic mechanism to allow a module to require another one.
Even just a SELECT require_module('bar') call at the top of a
script.
- It would be nice to suppress NOTICEs when installing stuff - the
user almost certainly doesn't care.
- Pick up config files in module directories, so that a module can
install and pick up config for itself rather than getting the sysadmin
to hack the global custom_variable_classes setting.
- Should plperl etc be done as modules so that their config can live
independently as well? And to allow modules to require them?
Some other nice to haves for some point in the future:
- Have some sort of install module privilege, rather than just a
check for database ownership
- Allow
Re: [HACKERS] Small TRUNCATE glitch
Added to TODO: o Clear table counters on TRUNCATE http://archives.postgresql.org/pgsql-hackers/2008-04/msg00169.php --- Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Just noticed that TRUNCATE fails to clear the stats collector's counts for the table. I am not sure if it should reset the event counts or not (any thoughts?) but surely it is wrong to not zero the live/dead tuple counts. Agreed, the live/dead counters should be reset. Regarding event counts, my take is that we should have a separate statement count for truncate (obviously not a tuple count), and the others should be left alone. I thought some more about how to do it, and stumbled over how to cope with TRUNCATE being rolled back. That nixed my first idea of just having TRUNCATE send a zero-the-counters-now message. 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 -- Bruce Momjian [EMAIL PROTECTED]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] Setting a pre-existing index as a primary key
Added to TODO: o Allow an existing index to be marked as a table's primary key --- Jonah H. Harris wrote: Hey all, I've run into a couple cases now where it would be helpful to easily assign an already-existing unique index as a primary key. Unless I completely missed something, there's no way to do this now without a bit of catalog hackery. My implementation idea is as follows: Proposed Syntax (based on Oracle's syntax) ALTER TABLE foo ADD CONSTRAINT bar PRIMARY KEY USING INDEX schema.tablename; Proposed Implementation 1. Verify that the index named is a unique index 2. Check index columns for NOT NULL constraints 3. If indexed columns are not already NOT NULL, apply NOT NULL 4. If NOT NULL succeeds, complete the operation (catalogs, dependencies, ...), else bail out. Any comments, ideas, suggestions? -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | 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 -- Bruce Momjian [EMAIL PROTECTED]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] Deterministic locking in PostgreSQL
Hi Tom, First of all thanks for the quick response. No, the arrival order will not be deterministic. Here is how we ensure determinism. 1.) SQL requests are delivered to the replication agent in a specific total order. This could occur either because they were already serialized by a database (master/slave case) or delivery through group communications (master/master case). 2.) Within replication we use advisory table locks at the middleware level to guide scheduling of request execution. This allows non-conflicting SQL statements to proceed in parallel but blocks those that might conflict. The reason I asked about determinism in locking is that this algorithm has a problem with distributed deadlock. If you look back at the example in the original post, you get the following: 1: T1, T2, T3: begin 2: T1: update foo set value='x' where id=25; -- Grabs row lock, grabs and releases middleware table lock 3: T2: update foo set value='y' where id=25; -- Grabs middleware table lock, blocks on row lock 4: T3: update foo set value='z' where id=25; -- DEADLOCKED 5: T1: update foo set value='x1' where id=25; 6: T1: commit 7: T2: commit 8: T3: commit At step 3 we deadlock since the request blocks in the database while holding the middleware table lock. Our plan to alleviate this problem is to look for requests that block (i.e., show up in pg_locks) and release their middleware table lock. As long as locks are granted deterministically this allows the next request to proceed--the ordering is now enforced by the database itself. There are some other possible race conditions, such as results of sub-selects on UPDATE statements, but this optimization will help us avoid a number of unnecessary failures in master/master replication. If anything else about this raises hackles on your neck (or anyone else's for that matter) please let me know. It's better to know now. :) Cheers, Robert On 5/9/08 4:53 PM, Tom Lane [EMAIL PROTECTED] wrote: Robert Hodges [EMAIL PROTECTED] writes: This question may have an obvious answer I have somehow missed, but to what extent is locking order deterministic in PostgreSQL? For example, if requests from multiple transactions arrive in some deterministic order and acquire locks, can one assume that locks will be granted in the same order if the requests are repeated at different times or on different servers? Yeah, it should be deterministic given consistent arrival order. Lock determinism is an important issue for replication algorithms that depend on database instances to behave as state machines. However, the idea of depending on a replication algorithm that has race conditions gives me the willies ... and that sure sounds like what you are describing. Do not trust your data to the assumption that arrival order will be deterministic. regards, tom lane -- Robert Hodges, CTO, Continuent, Inc. Email: [EMAIL PROTECTED] Mobile: +1-510-501-3728 Skype: hodgesrm -- 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] Setting a pre-existing index as a primary key
Bruce Momjian wrote: Added to TODO: Proposed Implementation 1. Verify that the index named is a unique index 2. Check index columns for NOT NULL constraints 3. If indexed columns are not already NOT NULL, apply NOT NULL 4. If NOT NULL succeeds, complete the operation (catalogs, dependencies, ...), else bail out. Any comments, ideas, suggestions? I would add: 5. Modify index name to use appropriate naming style. Joshua D. Drake -- 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] gsoc08, text search selectivity, pg_statistics holding an array of a different type
Tom Lane wrote: Jan's right: this is an oversight in the design of the VacAttrStats API. The existing pg_statistics slot types all need an array of the same datatype as the underlying column, but it's obvious when you think about it that there could be kinds of statistics that need to be stored as an array of some other type. I'm good with the idea of extending VacAttrStats for the purpose. Perhaps we would also want the ability to store the base element type when the column is an array.So for a 1D int[] column, we would store a 1D array in pg_statistics instead of a 2D array. Modules like intagg may find some use to that ability. I point this out because it also says that instead of inventing most common lexeme we want to turn into the more generic most common element or something like that. -- 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
[HACKERS] ecpg crash
Hi,
While i'm working on a ecpg patch I found a bug in ecpg code. The simple
program above could reproduce it. But basically it crashes (segfault)
because it's trying to use a inexistent connection when we're preparing
a statement. Don't know if it deserves a ecpg_log() message. A possible
fix is attached.
#include stdio.h
exec sql whenever sqlerror sqlprint;
int main(void)
{
exec sql begin declare section;
char cmd[128];
exec sql end declare section;
exec sql connect to euler as main;
sprintf(cmd, select * from foo);
exec sql prepare f from :cmd;
exec sql disconnect;
return 0;
}
(gdb) bt
#0 0xb7ebd9cd in find_prepared_statement (name=0x8048819 f, con=0x0,
prev_=0xbfccc6dc) at
/a/pgsql/dev/pgsql/src/interfaces/ecpg/ecpglib/prepare.c:187
#1 0xb7ebd79f in ECPGprepare (lineno=15, connection_name=0x0,
questionmarks=0, name=0x8048819 f, variable=0xbfccc72c select * from
foo limit 1,2)
at /a/pgsql/dev/pgsql/src/interfaces/ecpg/ecpglib/prepare.c:128
#2 0x0804865d in main ()
--
Euler Taveira de Oliveira
http://www.timbira.com/
Index: src/interfaces/ecpg/ecpglib/prepare.c
===
RCS file: /a/pgsql/dev/anoncvs/pgsql/src/interfaces/ecpg/ecpglib/prepare.c,v
retrieving revision 1.26
diff -c -r1.26 prepare.c
*** src/interfaces/ecpg/ecpglib/prepare.c 7 Feb 2008 11:09:13 - 1.26
--- src/interfaces/ecpg/ecpglib/prepare.c 10 May 2008 02:58:36 -
***
*** 124,129
--- 124,132
con = ecpg_get_connection(connection_name);
+ if (!con)
+ return false;
+
/* check if we already have prepared this statement */
this = find_prepared_statement(name, con, prev);
if (this !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this))
--
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] gsoc08, text search selectivity, pg_statistics holding an array of a different type
Alvaro Herrera [EMAIL PROTECTED] writes: Tom Lane wrote: Jan's right: this is an oversight in the design of the VacAttrStats API. Perhaps we would also want the ability to store the base element type when the column is an array. Well, that would be up to the type-specific analyze routine to determine what it wanted to do. 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] Setting a pre-existing index as a primary key
Joshua D. Drake [EMAIL PROTECTED] writes: Bruce Momjian wrote: Any comments, ideas, suggestions? I would add: 5. Modify index name to use appropriate naming style. Why, and exactly what would you define as appropriate naming style? The user has always been free to pick whatever constraint name he wants. 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] psql wrapped format default for backslash-d commands
Bruce Momjian [EMAIL PROTECTED] writes: Now that psql '\pset format wrapped' is in CVS, we should consider when we want to use 'wrapped' format by default. After experimenting for a bit, I'd say never. This output format is extremely ugly. Maybe if it had enough smarts not to break in the middle of words ... 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] Claimed status on Commitfest pages
I see that Brendan has proposed the following definition on
CommitFest:Help:
Reviewing items
If you are reviewing an item, let the other developers know by setting
the claimed parameter of the patch to your name. For example, if you
started reviewing the following patch
{{patch|[EMAIL PROTECTED]|Add bell and/or whistle|Some Guy}}
you would change it to
{{patch|[EMAIL PROTECTED]|Add bell and/or whistle|Some Guy|claimed=Some
Reviewer}}
I'm not too pleased with this because it suggests that reviewing is
something only one person can do at a time. What I'd like to see is
guidelines more along these lines:
* Short one-or-two-lines comments can be put right into a {{comment}}
item on the commitfest page.
* If you have a more substantive review, post it on the mailing lists
as a reply to the original patch. Optionally post a link to it as
a {{comment}} on the commitfest page.
* If you are a committer and are taking up the patch for final review
with intent to commit, then mark the commitfest item as claimed so
that other committers will not step on your toes.
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] Setting a pre-existing index as a primary key
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Bruce Momjian wrote: Any comments, ideas, suggestions? I would add: 5. Modify index name to use appropriate naming style. Why, and exactly what would you define as appropriate naming style? The user has always been free to pick whatever constraint name he wants. Well it should be optional but it would be nice if we had the option to have it renamed per the default... meaning the same output if I were to do this: create table foo (id serial primary key); I end up with foo_pkey PRIMARY KEY, btree (id) Which is nice for consistency. Sincerely, Joshua D. Drake 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] Claimed status on Commitfest pages
On Sat, May 10, 2008 at 2:49 PM, Tom Lane [EMAIL PROTECTED] wrote: I see that Brendan has proposed the following definition on CommitFest:Help: I wouldn't say I did anything so formal as proposing a definition =) Someone mentioned that a column to indicate who's handling each patch would be nice, so I added it. The comments in :Help were really just an example to explain how you might use the feature. I'm not looking to formulate commitfest procedure here (I don't have quite that level of hubris), just providing some tools to get the job done more comfortably. I'm not too pleased with this because it suggests that reviewing is something only one person can do at a time. What I'd like to see is guidelines more along these lines: By all means, go ahead and make these changes. I think your version makes a lot of sense. 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
