Re: [HACKERS] PGparam timestamp question
On Sun, Dec 09, 2007 at 11:54:25AM -0500, Andrew Chernow wrote: That explains why my libpq code was getting 3AM for without time zone values. I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c timestamp2tm(). That uses localtime() after converting the timestamp to an epoch value. I changed this code so that it calls gmtime() for TIMESTAMPOID and localtime() for TIMESTAMPTZOID. Now it works perfectly :) Does this mean pgtypeslib is buggy? This code has been taken from the backend ages ago, so some changes might have occured that I'm not aware of. Or was the code incorrectly used? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go VfL Borussia! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Note Changes
Hi, Le lundi 10 décembre 2007, Bruce Momjian a écrit : Based on this discussion I think it is clear the release notes chapter needs an introductory section. This would not be for any specific release but the release notes in general. Excellent idea, IMHO. I need help with the CVS section. Do we publish full CVS logs for a release? I like the SVN display because it groups commits but can improvements I listed above be made? I tend to like the git web interface found here: http://repo.or.cz/w/PostgreSQL.git http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=master http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=REL8_2_STABLE Hope this helps, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Problem with ControlFileData structure being ABI depe ndent
Dave Page wrote:
Tom Lane wrote:
Dave Page [EMAIL PROTECTED] writes:
Gregory Stark wrote:
An alternative is leaving it in the project file but putting
something like
this in c.h:
Put it in win32.h, please. c.h shouldn't get cluttered with
platform-specific kluges when there's no need for it.
Is there a good reason not to just #define _USE_32BIT_TIME_T in win32.h?
Yeah, the fact that addons may then end up partially compiled with and
partially without it being defined. It we just have it error as Greg
suggested, then it will force the authors to define it themselves, and
if they get that wrong it's their fault not ours.
Patch attached.
/D
Index: src/include/port/win32.h
===
RCS file: /projects/cvsroot/pgsql/src/include/port/win32.h,v
retrieving revision 1.81
diff -c -r1.81 win32.h
*** src/include/port/win32.h 24 Nov 2007 01:55:26 - 1.81
--- src/include/port/win32.h 10 Dec 2007 09:42:44 -
***
*** 45,50
--- 45,61
#define USES_WINSOCK
+ /*
+ * Ensure that anyone building an extension is using a 32 bit time_t.
+ * On Mingw/Msys, that should always be the case, but MSVC++ defaults
+ * to 64 bits. We set that for our own build in the project files
+ */
+ #ifdef WIN32
+ #ifndef _USE_32BIT_TIME_T
+ #error Postgres uses 32 bit time_t - add #define _USE_32BIT_TIME_T on Windows
+ #endif
+ #endif
+
/* defines for dynamic linking on Win32 platform */
#if defined(WIN32) || defined(__CYGWIN__)
Index: src/tools/msvc/Project.pm
===
RCS file: /projects/cvsroot/pgsql/src/tools/msvc/Project.pm,v
retrieving revision 1.14
diff -c -r1.14 Project.pm
*** src/tools/msvc/Project.pm 21 Aug 2007 15:10:41 - 1.14
--- src/tools/msvc/Project.pm 7 Dec 2007 11:14:29 -
***
*** 489,495
ConfigurationType=$cfgtype UseOfMFC=0 ATLMinimizesCRunTimeLibraryUsage=FALSE CharacterSet=2 WholeProgramOptimization=$p-{wholeopt}
Tool Name=VCCLCompilerTool Optimization=$p-{opt}
AdditionalIncludeDirectories=$self-{prefixincludes}src/include;src/include/port/win32;src/include/port/win32_msvc;$self-{includes}
! PreprocessorDefinitions=WIN32;_WINDOWS;__WINDOWS__;__WIN32__;EXEC_BACKEND;WIN32_STACK_RLIMIT=4194304;_CRT_SECURE_NO_DEPRECATE;_CRT_NONSTDC_NO_DEPRECATE$self-{defines}$p-{defs}
StringPooling=$p-{strpool}
RuntimeLibrary=$p-{runtime} DisableSpecificWarnings=$self-{disablewarnings}
EOF
--- 489,495
ConfigurationType=$cfgtype UseOfMFC=0 ATLMinimizesCRunTimeLibraryUsage=FALSE CharacterSet=2 WholeProgramOptimization=$p-{wholeopt}
Tool Name=VCCLCompilerTool Optimization=$p-{opt}
AdditionalIncludeDirectories=$self-{prefixincludes}src/include;src/include/port/win32;src/include/port/win32_msvc;$self-{includes}
! PreprocessorDefinitions=WIN32;_WINDOWS;__WINDOWS__;__WIN32__;EXEC_BACKEND;WIN32_STACK_RLIMIT=4194304;_CRT_SECURE_NO_DEPRECATE;_CRT_NONSTDC_NO_DEPRECATE;_USE_32BIT_TIME_T$self-{defines}$p-{defs}
StringPooling=$p-{strpool}
RuntimeLibrary=$p-{runtime} DisableSpecificWarnings=$self-{disablewarnings}
EOF
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] Release Note Changes
Dave Page wrote: First-name-only entries represent established developers, while full names represent newer contributors. That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] whats the deal with -u ?
Am Montag, 10. Dezember 2007 schrieb Tom Lane: Joshua D. Drake [EMAIL PROTECTED] writes: As I recall there was a bug under very specific circumstances that a password prompt would not appear. Thus we added the option for -W. I don't see any evidence for that theory in the CVS logs .. Peter seems to have invented -W out of whole cloth. Way back when, there was no way to set the user name on the psql command line. The only way was to use the option -u and type it in. (Well, you could set PGUSER, but that's obviously not quite fun.) But -u also forced a password prompt, even if you didn't need a password. So the functionality of the -u option was split into the -U and -W options. The only difference is that -U does not prompt, but I don't think anyone wants to argue that prompting is better than what -U does. Note that we don't have a way to prompt for host name, database name, etc., and shell scripting this functionality is trivial. Way back when, psql moreover did not have the capability to automatically recognize when a password would be required. That is, if you did not specify one, authentication would simply fail and psql would abort. If you were aware that a password would be required, then the only ways to supply it would be to set PGPASSWORD (which is obviously not quite fun) or use the -u option, which would also prompt you for a user name, even if you did not want to specify one. So the functionality of the -u option was split into the -U and -W options. -W does exactly half of what -u used to do. The functionality to automatically recognize when a password would be required and prompt was added later (or perhaps around the same time) but it was considered a hack (it was a string comparison of the error message). It has obviously worked out quite well anyway. I believe the documentation of the -W option has for its entire lifetime said that it should normally not be necessary. So as far as I can tell, the available options -U and -W serve all the existing use cases. I would have no issue with getting rid of the -W option if someone wants to take responsibility for ensuring that it will really never be necessary. I see no technical or usability merit in reviving the -u option. I hope the above explanations have shed some light on that. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] BUG #3811: Getting multiple values from a sequence generator
Hi, Right, I want to use it with a bulk operation, say importing a million records with COPY. Calling nextval one million times looks to me like an enormous waste of resources. Suppose, you are on an ADSL line: it will cost one million times the ping time of the ADSL line (say 10 milliseconds per call). Well OK, one could write a server function that does this, but then the one million result values must be transported back to the client, because they are not guaranteed to be contiguous. Unneeded complexity compared to a simple nextval increment parameter. The usual way to use nextval() is to use it on the server as an expression in an INSERT or DEFAULT. If you're using COPY and don't have a column default set up then, hm, I guess you're kind of stuck. That would make a good use case for a one-time nextval(increment) or something like that. Coincidently, I very briefly discussed (offline) about supporting expressions while doing loads using COPY FROM with Heikki a while back. From the above mail exchanges, it does appear that adding this kind of functionality will be useful while doing bulk imports into tables using COPY. Heikki's initial suggestion was as follows: COPY table FROM file USING query Where query could be any SELECT query, executed once for row using the values from the input data file. For example: COPY footable (strcol, strcollen, moredata) FROM file USING SELECT $1, length($1), $2; The sql expressions could refer to the columns being read or could be user defined procedures, built-in functions etc too. These expressions would need to be executed per row read from the input data file to form a new set of values[], nulls[] entries before forming the corresponding tuple entry. I think the above will be a very useful enhancement to COPY. The syntax and other details mentioned above are ofcourse subject to discussion and approval on the list. Regards, Nikhils -- EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Release Note Changes
Brendan Jurd wrote: On Dec 10, 2007 10:39 AM, Bruce Momjian [EMAIL PROTECTED] wrote: I like the realease notes intro. You may have already picked up on these, but a couple typos: A names appearing next to an item represents the major developer for that item. Of course all changes involve community discussion and patch review so each item is truely a community activity. First-name-only entries represent established developers, while full names represent newer contributors. A names - Names, represents - represent, developer - developers or developer(s) if you prefer. I could really go a language that doesn't distinguish between singular and plural, but looks like we're stuck with English for the duration. And truely - truly. Thanks, got it. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release Note Changes
Dimitri Fontaine wrote: -- Start of PGP signed section. Hi, Le lundi 10 d?cembre 2007, Bruce Momjian a ?crit?: Based on this discussion I think it is clear the release notes chapter needs an introductory section. This would not be for any specific release but the release notes in general. Excellent idea, IMHO. I need help with the CVS section. Do we publish full CVS logs for a release? I like the SVN display because it groups commits but can improvements I listed above be made? I tend to like the git web interface found here: http://repo.or.cz/w/PostgreSQL.git http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=master http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=REL8_2_STABLE I like the branch option but I don't like the title being duplicated as the first line in log mode. The Next button is returning 403 Forbidden - Project needed. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Note Changes
Heikki Linnakangas wrote: Dave Page wrote: First-name-only entries represent established developers, while full names represent newer contributors. That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) Yes, the confusion there is David Fetter vs. Dave Page. I removed that paragraph completely. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release Note Changes
Joshua D. Drake wrote: Bruce Momjian wrote: Joshua D. Drake wrote: I assumed the white paper would have proper attribution. Right, but is the white paper going to be thorough to mention _all_ changes? Hmmm good question which gets back to where we started :). My very first thought on all of this was that we would list all notable changes but that we wouldn't mention anyone's name. Isn't that listing what is already in the release notes? Then, we would have a Who contributed to this release section that just listed names without attribution to the specific feature. IMO, that is the only fair way. I realize that notable is subjective. Here is the deal :). I think as long as a single person is making the decision as to what goes and stays, there will always be friction. Perhaps it is time for a release team? Odd numbers only, +1/-1 voting etc... I don't know maybe that is too much. Not really. We can have anyone suggest changes to the release notes up until release, and there already have been tons of user-suggested changes. The issue is if I disagree with a suggested change, and no one else backs up the suggested change, it gets rejected, just like the community patch process works. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Note Changes
Bruce Momjian wrote: Heikki Linnakangas wrote: Dave Page wrote: First-name-only entries represent established developers, while full names represent newer contributors. That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) Yes, the confusion there is David Fetter vs. Dave Page. I removed that paragraph completely. David != Dave :-p /D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
Dave Page wrote: Bruce Momjian wrote: Heikki Linnakangas wrote: Dave Page wrote: First-name-only entries represent established developers, while full names represent newer contributors. That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) Yes, the confusion there is David Fetter vs. Dave Page. I removed that paragraph completely. David != Dave Yea, but that is so subtle that is seems too error-prone. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
Bruce Momjian wrote: Dave Page wrote: David != Dave Yea, but that is so subtle that is seems too error-prone. I think you missed the smiley. It doesn't bother me if I'm named in full or not, just that the introduction is accurate - which you've already fixed. /D ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] PGparam timestamp question
Or was the code incorrectly used? Hard for me to say, but I think its about caller context. The way I am using it might be different ... hey the function was static ... copy paster be warned! The code appears to be doing the same thing as the backend (with the exclusion of backend stuff like HasCTZSet and forced conversions). I plan to do an extensive test sometime today. So far, I am getting the correct timestamp conversions across the board. Andrew Michael Meskes wrote: On Sun, Dec 09, 2007 at 11:54:25AM -0500, Andrew Chernow wrote: That explains why my libpq code was getting 3AM for without time zone values. I am using code from src/interfaces/ecpg/pgtypeslib/timestamp.c timestamp2tm(). That uses localtime() after converting the timestamp to an epoch value. I changed this code so that it calls gmtime() for TIMESTAMPOID and localtime() for TIMESTAMPTZOID. Now it works perfectly :) Does this mean pgtypeslib is buggy? This code has been taken from the backend ages ago, so some changes might have occured that I'm not aware of. Or was the code incorrectly used? Michael ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Note Changes
Pavan Deolasee wrote: On Dec 8, 2007 3:42 AM, Andrew Dunstan [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Bruce Momjian wrote: Andrew Dunstan wrote: I still think this needs to be qualified either way. As it stands it's quite misleading. Many update scenarios will not benefit one whit from HOT updates. Doesn't the detail description qualify it enought? The heading isn't suppose to have all the information or it would be unreadable. If you don't want to be more specific I'd say certain updates or some updates or something similar, just some flag to say it's not all of them. Good idea. I added most: Heap-Only Tuples (acronymHOT/) accelerate space reuse for most commandUPDATE/s (Pavan Deolasee, with ideas from many others) But that's not true either. For example, in my current $dayjob app not one significant update will benefit - we have an index rich environment. You have no basis for saying most that I can see. We really should not be in the hyp business in our release notes - that job belongs to the commercial promoters ;-) I don't agree completely. HOT updates is just one significant benefit of HOT and is constrained by the non-index column updates. But the other major benefit of truncating the tuples to their line pointers applies to HOT as well as COLD updates and DELETEs. This should also have a non trivial positive impact on the performance. There might be few scenarios where HOT may not show any improvement such as CPU-bound applications, but I am not sure if its worth mentioning. http://www.enterprisedb.com Um, I don't understand. I freely admit that I haven't kept up with all the nuances of the HOT discussions, but this bit has totally eluded me, so please elucidate. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Well, if we want to cram all that stuff in there, how shall we do it? It seems wrong to put all those lines into one text field, but I'm not sure I want to add six more text fields to the CSV format either. Thoughts? Really? Six? In any case, would that be so bad? It would mean six extra commas per line in the log file, and nothing much in the log table unless there were content in those fields. Yeah --- the lines output in the plain-stderr case that are not covered in the other are DETAIL HINT QUERY (this is an internally-generated query that failed) CONTEXT (think stack trace) LOCATION(reference to code file/line reporting the error) STATEMENT (user query that led to the error) Here is a patch to do this. It emits all of these as separate columns, which are output empty if they are not present. Of course, the commas are emitted all the time. I changed some other things. For example, the original code puts [unknown] when the username or database name is unknown. I think this is pointless; an empty field is fine. I noticed that unknown VXIDs and TransactionIds are printed as xx/-1 and 0 respectively. Should we just leave the columns empty? The error location (file and function name, and line number) are only reported if log_error_verbosity is set, just like the main logs. The commas are emitted anyway. It is emitted as a single field. So it is a single column which looks like transformColumnRef, /pgsql/source/00head/src/backend/parser/parse_expr.c:420 I very much doubt that it is worth separating that any further, but if anyone disagrees, this is the time to change it. I tried importing the resulting file into Gnumeric, and after some fooling around to make it use commas as separators instead of colons (it autodetects the separators and seems to consider colons best for these files for some reason), it works fine, including multiline context fields. It does look a bit weird though, because they are not multiline in the spreadsheet but instead it shows the funny square reserved for non-printable chars with the [0A] byte in it. I imported it with COPY WITH CSV and it looks fine in psql; multiline context is displayed correctly. Another change I did was to change a %.*s to %*s. The precision marker seems useless AFAICT. One issue here is that CONTEXT is potentially multiple lines. I'm not sure that there is much we can do about that, especially not at the last minute. If we had some time to rewrite internal APIs it might be fun to think about emitting that as array of text not just text, but I fear it's much too late to consider that now. In psql it looks like this. The first message is that I forgot to use the CSV option to COPY the first time. The second message is that I renamed a file from under CHECKPOINT to see what it looked like to not have usernames, etc. The third is a nested plpgsql function call to show multiline context. alvherre=# select message, context from postgres_log where context is not null; -[ RECORD 1 ]- message | la sintaxis de entrada no es válida para tipo timestamp with time zone: «2007-12-10 09:43:32.473 CLST,alvherre,alvherre,475d33a0.3c78,[local],15480,3,CREATE TABLE,2007-12-10 09:40:00 CLST,1/4,387,NOTICE,0,CREATE TABLE / PRIMARY KEY creará el índice implícito «postgres_log_pkey» para la tabla «postgres_log»» context | COPY postgres_log, línea 1, columna log_time: «2007-12-10 09:43:32.473 CLST,alvherre,alvherre,475d33a0.3c78,[local],15480,3,CREATE TABLE,2007...» -[ RECORD 2 ]- message | no se pudo abrir la relación 1663/16384/16391: No existe el fichero o el directorio context | escribiendo el bloque 0 de la relación 1663/16384/16391 -[ RECORD 3 ]- message | no existe la columna «foo» context | PL/pgSQL function foo line 1 at SQL statement : sentencia SQL: «select foo()» :
Re: [HACKERS] [BUGS] BUG #3811: Getting multiple values from a sequence generator
NikhilS [EMAIL PROTECTED] writes: Coincidently, I very briefly discussed (offline) about supporting expressions while doing loads using COPY FROM with Heikki a while back. From the above mail exchanges, it does appear that adding this kind of functionality will be useful while doing bulk imports into tables using COPY. Heikki's initial suggestion was as follows: COPY table FROM file USING query Where query could be any SELECT query, executed once for row using the values from the input data file. For example: Another direction to head would be to take away COPY's special logic to insert into tables and instead have something like: COPY FROM file USING query where query is an *INSERT* statement. Or for that matter a DELETE or an UPDATE. It would prepare the query then execute it once per line read from the streamed copy data. It would be much more general but perhaps be harder to optimize the our current COPY can be optimized. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Note Changes
Le lundi 10 décembre 2007, Bruce Momjian a écrit : http://repo.or.cz/w/PostgreSQL.git http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=master http://repo.or.cz/w/PostgreSQL.git?a=shortlog;h=REL8_2_STABLE I like the branch option but I don't like the title being duplicated as the first line in log mode. I'm not that acquainted to git and gitweb to provide authoritative answer, but my guess here is that git (as darcs) is used to have a first line to title patch, then a optional description. It seems the automatic cvs-to-git tool (or maybe cvsps) is not smart enough to extract a good title from cvs log entries (which may or may not provide one). http://www.cobite.com/cvsps/ The Next button is returning 403 Forbidden - Project needed. Ouch. ISTR it used to work well... and replacing the ARRAY(0x...) garbage with PostgreSQL.git makes it render the wanted web page... Should I add I'm not trying to push towards git/gitweb nor trying to have decentralized revision system on the spot; but rather am trying to help finding the right tool for the job at hand. Regards, -- dim signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] whats the deal with -u ?
* Peter Eisentraut ([EMAIL PROTECTED]) wrote: So as far as I can tell, the available options -U and -W serve all the existing use cases. I would have no issue with getting rid of the -W option if someone wants to take responsibility for ensuring that it will really never be necessary. I see no technical or usability merit in reviving the -u option. I hope the above explanations have shed some light on that. I think getting rid of -W would cause a problem w/ PAM in some instances since, iirc, PG will try PAM w/o a password first and only prompt if it doesn't work. That's pretty ugly if you're using things like pam_tally to limit the number of bad attempts allowed. (This is entirely empirical, it's possible there's some other explanation for what's happening, but I recall having to use -W to get PG to not cause PAM to make noisies in my auth.log...). Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Alvaro Herrera wrote: Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: Tom Lane wrote: Well, if we want to cram all that stuff in there, how shall we do it? It seems wrong to put all those lines into one text field, but I'm not sure I want to add six more text fields to the CSV format either. Thoughts? Really? Six? In any case, would that be so bad? It would mean six extra commas per line in the log file, and nothing much in the log table unless there were content in those fields. Yeah --- the lines output in the plain-stderr case that are not covered in the other are DETAIL HINT QUERY (this is an internally-generated query that failed) CONTEXT (think stack trace) LOCATION(reference to code file/line reporting the error) STATEMENT (user query that led to the error) Here is a patch to do this. It emits all of these as separate columns, which are output empty if they are not present. Of course, the commas are emitted all the time. Thanks. I will look at it in detail later today. Not sure I understand what this comment I noticed on a very brief glance is about: /* assume no newlines in funcname or filename... */ If it's about what to quote, we need to quote anything that might contain a newline, quote or comma. Filenames certainly come into that category. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] O(n^2) aggregates
I was trying to test my patch to do posix_fadvise to speed up bitmap heap
scans (with disappointing results so far) and ran into a bit of a gotcha. I'm
not sure where this should be documented but it probably should be somewhere.
In order to test bitmap heap scan I had to build an array and use the = ANY(a)
form. (The natural approach of building a table of values to search for
produces a hash join or nested loop -- it may make sense to add a new kind of
join which uses an outer bitmap heap scan.)
So I defined an aggregate to group up the random values in an array in the
usual fashion:
CREATE AGGREGATE arrayize(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
and ran queries like:
select count(*)
from huge
where h = any ((select arrayize( (1+random()*3)::integer )
from generate_series(1,1000)
)::integer[])
To test the behaviour for larger and larger samples I bumped the 1000 up
further and further and noticed a larger and large pause in disk access. When
I tried 4 the query took over 47 minutes nearly all of which had one cpu
pegged at 100%.
What's going on is that arrayize() is actually a O(n^2) algorithm since each
transition requires creating a copy of the entire array.
The solution to this would analogous to what we did to count(). We would need
to add a field to ArrayMetaState which is stored in fn_extra to remember the
last array returned. Then if array_push notices it has been called from an
aggregate context it can store its result in there. The next time it would
extend that array in place (which is code which doesn't currently exist),
possibly repallocing it and return the same pointer.
It's a bit of a hack but I think this is going to be a pretty common use case
and I don't see any more general solution.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Andrew Dunstan wrote: Not sure I understand what this comment I noticed on a very brief glance is about: /* assume no newlines in funcname or filename... */ If it's about what to quote, we need to quote anything that might contain a newline, quote or comma. Filenames certainly come into that category. Huh, sorry, that's a leftover comment. The inserted string is quoted inside appendCSVLiteral. Thanks, I'll fix it. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J No es bueno caminar con un hombre muerto ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Alvaro Herrera wrote:
Andrew Dunstan wrote:
Not sure I understand what this comment I noticed on a very brief glance is
about:
/* assume no newlines in funcname or filename... */
If it's about what to quote, we need to quote anything that might contain a
newline, quote or comma. Filenames certainly come into that category.
Huh, sorry, that's a leftover comment. The inserted string is quoted
inside appendCSVLiteral. Thanks, I'll fix it.
This part now looks like this (it was a bit duplicative):
/* file error location */
if (Log_error_verbosity = PGERROR_VERBOSE)
{
StringInfoData msgbuf;
initStringInfo(msgbuf);
if (edata-funcname edata-filename)
appendStringInfo(msgbuf, %s, %s:%d,
edata-funcname, edata-filename,
edata-lineno);
else if (edata-filename)
appendStringInfo(msgbuf, %s:%d,
edata-filename, edata-lineno);
appendCSVLiteral(buf, msgbuf.data);
pfree(msgbuf.data);
}
--
Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC
Schwern It does it in a really, really complicated way
crab why does it need to be complicated?
Schwern Because it's MakeMaker.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] whats the deal with -u ?
Stephen Frost [EMAIL PROTECTED] writes: * Peter Eisentraut ([EMAIL PROTECTED]) wrote: So as far as I can tell, the available options -U and -W serve all the existing use cases. I would have no issue with getting rid of the -W option if someone wants to take responsibility for ensuring that it will really never be necessary. I see no technical or usability merit in reviving the -u option. I hope the above explanations have shed some light on that. I think getting rid of -W would cause a problem w/ PAM in some instances since, iirc, PG will try PAM w/o a password first and only prompt if it doesn't work. I'm not very interested in trying to get rid of -W; it's documented, it's orthogonal to all other switches, and we can see at least some marginal use-cases for it. However, I think we should either get rid of -u or find a way to un-deprecate it. Right now, it's undocumented and as far as I can see the main effect of having it is to cause confusion such as that which started this thread. On the whole I'm in favor of removing it. It's been undocumented for long enough that no one could really complain if it disappears. Further down the road, those whose notion of intuitive was formed by mysql might lobby to have -u become an alternate spelling for -U, but that obviously can't happen until the switch has actually been gone for a few releases. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Problem of a server gettext message.
Hi. I think this has many problems. However, by the reason the release is approaching, this is not the situation which I'm looking at leisurely.. Server message has a problem by 8.3beta4 on windows. The situation is this. 1. initdb -E UTF-8 --no-locale This is C locale. http://winpg.jp/~saito/pg83/postgresql-8.3beta4_info2.png 2. Japanese local message of po file to setting(share/locale/ja) . 3. set the client_encoding is SJIS. http://winpg.jp/~saito/pg83/postgresql-8.3beta4_info1.png 4. action error message is made to send from server. It is crash http://winpg.jp/~saito/pg83/postgresql-8.3beta4_crash.png 5. The reason is because the message which a server outputs is SJIS. http://winpg.jp/~saito/pg83/postgresql-8.3beta4_crash.log Version 8.2.x outputs an English message. It did not look at a problem. Then, I consider as LC_MESSAGE for a server message, or wish a back patch. Is there any good solution method? Regards, Hiroshi Saito ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Andrew Dunstan [EMAIL PROTECTED] writes: If it's about what to quote, we need to quote anything that might contain a newline, quote or comma. Filenames certainly come into that category. These are not general file paths, these are base names of our own C source code files. I don't have a problem with legislating that we will never have such characters in backend source file names ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] O(n^2) aggregates
Gregory Stark [EMAIL PROTECTED] writes: The solution to this would analogous to what we did to count(). We would need to add a field to ArrayMetaState which is stored in fn_extra to remember the last array returned. Then if array_push notices it has been called from an aggregate context it can store its result in there. The next time it would extend that array in place (which is code which doesn't currently exist), contrib/intagg regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: If it's about what to quote, we need to quote anything that might contain a newline, quote or comma. Filenames certainly come into that category. These are not general file paths, these are base names of our own C source code files. I don't have a problem with legislating that we will never have such characters in backend source file names ... Yeah, I was about to say the same thing, but the problem is that the field is constructed like function, file:line so there is a comma anyway. (We could split it, but since this is only emitted in the log_error_verbosity=verbose case, I don't think it makes much sense.) -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ I suspect most samba developers are already technically insane... Of course, since many of them are Australians, you can't tell. (L. Torvalds) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] whats the deal with -u ?
* Tom Lane ([EMAIL PROTECTED]) wrote: However, I think we should either get rid of -u or find a way to un-deprecate it. Right now, it's undocumented and as far as I can see the main effect of having it is to cause confusion such as that which started this thread. On the whole I'm in favor of removing it. It's been undocumented for long enough that no one could really complain if it disappears. I agree that it'd be best to remove it and I don't think it'll cause problems for it to go away. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Release Note Changes
Heikki Linnakangas [EMAIL PROTECTED] writes: Dave Page wrote: That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) Yeah, laziness on the part of those preparing the release notes is certainly a factor ;-). I think when I did the first draft this time round, I wrote out Heikki's name in full the first time and then made it just Heikki thereafter. Perhaps a suitable solution would be to adopt that as formal policy --- it would solve the problem of bulk for heavy contributors while still treating everyone alike. Not sure about how to deal with first names that are enough alike to cause confusion, as in Dave vs David. When Tom Lockhart was around the project it was even messier, since he and I shared not only the same first name but all three initials. We got away with me being Tom and him Thomas for release-note purposes, but I think that only worked because we were both core members, so the case came up often enough to keep the distinction fresh in mind. (Or maybe everyone but the two of us was routinely confused...) Anyway, maybe a policy of drop the last name on second and later mentions, unless this might cause confusion would work. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] pg_dump roles support
Greetings, Discussing psql options made me recall an annoying problem that we've run into. There's no way (unless it was added to 8.3 and I missed it, but I don't think so) to tell pg_dump 'switch to this role before doing anything else'. That's very frustrating when you use no-inherit roles for admins. eg: create role admin with noinherit; grant postgres to admin; grant admin to joesysadmin; pg_dump -U joesysadmin mydb; Fails because joesysadmin hasn't got rights to everything directly. It'd be nice if pg_dump could take a '-r postgres' to 'set role' to a role which has the necessary permissions before locking all the tables and whatnot. The same 'set role' would also be included at the top of the resulting dump file. We could have a seperate flag for that but I don't think it's necessary. Comments? I doubt there'd be very much code involved but I'd be willing to write a patch if people agree with the general idea/approach. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Release Note Changes
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Dave Page wrote: That's inaccurate - I've been listed by full name for at least the last 3 or 4 releases. I realise I'm not the biggest contributor to the core code, but 'newer' certainly isn't right. Maybe that's because you have such a short name. ;-) Yeah, laziness on the part of those preparing the release notes is certainly a factor ;-). Anyway, maybe a policy of drop the last name on second and later mentions, unless this might cause confusion would work. You've probably written more in this thread than you would have if you'd used last names everywhere to start with. I suspect that might be the best policy, and that the laziness is best avoided in this case. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
Tom Lane [EMAIL PROTECTED] writes: When Tom Lockhart was around the project it was even messier, since he and I shared not only the same first name but all three initials. Then there's Greg Stark, Greg Smith, and Greg Sabino (Mullane). Perhaps we should just go by uid. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] pg_dump roles support
Stephen Frost [EMAIL PROTECTED] writes: create role admin with noinherit; grant postgres to admin; grant admin to joesysadmin; pg_dump -U joesysadmin mydb; Fails because joesysadmin hasn't got rights to everything directly. Seems like the correct answer to that is use a saner role configuration. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Problem of a server gettext message.
Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: 2. Japanese local message of po file to setting(share/locale/ja) . Could we see the contents of this file? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_dump roles support
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: create role admin with noinherit; grant postgres to admin; grant admin to joesysadmin; pg_dump -U joesysadmin mydb; Fails because joesysadmin hasn't got rights to everything directly. Seems like the correct answer to that is use a saner role configuration. Funny, it's exactly the type of setup described here: http://www.postgresql.org/docs/8.2/interactive/role-membership.html Far as I can tell anyway. What would you suggest? The point here is that joesysadmin shouldn't get full postgres privs on login since most of the time he won't need them. When he does need them, he can do a 'set role postgres', do what he needs to do and then 'reset role' when he's done. Minimizing the amount of time with superuser privs is a good thing in general, I would think. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_dump roles support
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: Seems like the correct answer to that is use a saner role configuration. Far as I can tell anyway. What would you suggest? The point here is that joesysadmin shouldn't get full postgres privs on login since most of the time he won't need them. It's sane to set up a manually-used admin account that way, I agree. What doesn't follow is that an account configured for manual use should be used for non-interactive stuff like pg_dump. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] pg_dump roles support
* Tom Lane ([EMAIL PROTECTED]) wrote: Stephen Frost [EMAIL PROTECTED] writes: Far as I can tell anyway. What would you suggest? The point here is that joesysadmin shouldn't get full postgres privs on login since most of the time he won't need them. It's sane to set up a manually-used admin account that way, I agree. What doesn't follow is that an account configured for manual use should be used for non-interactive stuff like pg_dump. I strongly disagree that pg_dump isn't to be used manually, which I think is what you were getting at. We use it a great deal to dump individual schemas and copy them between systems. We *don't* use it anymore for full database dumps (something which was done in the past) because we use PITR instead. In fact, I encourage my users to use it to copy schema structures around when they need a seperate area for development or testing. What's frustrating is when an admin needs to copy a schema that he doesn't directly have rights to (another user's schema, or a schema used by a site or something) he has to login to the database server (something I'd like to minimize anyway- almost everything can easily be done from psql), su to root, su to postgres, do the pg_dump on the box, and then work out copying it off the box. I suppose I could write my own pg_dump that actually used psql underneath or add it's functionality to our perl toolkit (it's damn handy to be able to change permissions on every table in a schema with one command, and it takes role as an argument, heh), but rewriting the dependency handling and whatnot isn't something I'm really keen to do. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Release Note Changes
Bruce Momjian wrote: Joshua D. Drake wrote: Bruce Momjian wrote: Joshua D. Drake wrote: I assumed the white paper would have proper attribution. Right, but is the white paper going to be thorough to mention _all_ changes? Hmmm good question which gets back to where we started :). My very first thought on all of this was that we would list all notable changes but that we wouldn't mention anyone's name. Isn't that listing what is already in the release notes? No :). What is listed already in the release notes is what you think is notable, which is why I mention the subjective below. What is cool to you may not be cool to others etc... I am not saying you are doing a bad job just that it is subjective. Case in point I think the work that Stefan did for this release is notable. I believe it is notable for several reasons. 1. Usability 2. Recognition (yes I am aware of the thoughts on that) 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Tom and you disagreed. I understand the reasoning and I don't actually disagree with the thought process but I think the thought process is flawed. I do not believe people only look at the release notes for wow cool. I believe they look at them to see who deserves kudos in this release. Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Note Changes
Andrew Dunstan wrote: Yeah, laziness on the part of those preparing the release notes is certainly a factor ;-). Anyway, maybe a policy of drop the last name on second and later mentions, unless this might cause confusion would work. You've probably written more in this thread than you would have if you'd used last names everywhere to start with. I suspect that might be the best policy, and that the laziness is best avoided in this case. *cough* There are at least four Josh's currently active on the lists. Two of them are long time members, two a relatively new. Full names makes the most sense. Sincerely, Joshua D. Drake cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] O(n^2) aggregates
Hello select count(*) from huge where h = any ((select arrayize( (1+random()*3)::integer ) from generate_series(1,1000) )::integer[]) select array(select (1+random()*3):: integer from generate_series(1,4)); Time: 111,807 ms ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Problem of a server gettext message.
Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi Peter-san. It is this. http://winpg.jp/~saito/pg83/ja.zip Sorry, we need the *po* (text) files, not the *mo* (binary) files. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Note Changes
On Mon, 10 Dec 2007, Joshua D. Drake wrote: 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Then why not list Stefan as a contributor to the original feature rather than calling it out as a separate item? Kris Jurka ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Problem of a server gettext message.
Hi. From: Peter Eisentraut [EMAIL PROTECTED] Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi Peter-san. It is this. http://winpg.jp/~saito/pg83/ja.zip Sorry, we need the *po* (text) files, not the *mo* (binary) files. Ooops, Although it is an object for Version 8.2.5. http://www.postgresql.jp/wg/jpugdoc/po/postgresql-8-2-5-nls-patch.gz ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] archive_command failures report confusing exit status
Failures of archive_command calls report a confusing exit status such as:
LOG: archive command cp 'pg_xlog/0001'
'/nonexistent/0001' failed: return code 256
The actual return code is 1; it neglects to apply WEXITSTATUS().
I figured it would make sense if pgarch.c used the same mechanism that
postmaster.c uses to report the various variants of regular and signal
exits.
I have attached a patch in that direction. It obviously needs a bit of
string struggling to get all the cases right, but the idea should be
clear. Comments?
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
diff -ur ../cvs-pgsql/src/backend/postmaster/pgarch.c ./src/backend/postmaster/pgarch.c
--- ../cvs-pgsql/src/backend/postmaster/pgarch.c 2007-11-26 13:29:36.0 +0100
+++ ./src/backend/postmaster/pgarch.c 2007-12-10 17:59:18.0 +0100
@@ -480,15 +480,11 @@
* SIGQUIT while waiting; so a signal is very likely something that
* should have interrupted us too. If we overreact it's no big deal,
* the postmaster will just start the archiver again.
- *
- * Per the Single Unix Spec, shells report exit status 128 when a
- * called command died on a signal.
*/
- bool signaled = WIFSIGNALED(rc) || WEXITSTATUS(rc) 128;
- ereport(signaled ? FATAL : LOG,
-(errmsg(archive command \%s\ failed: return code %d,
- xlogarchcmd, rc)));
+ // FIXME: archive command \%s\, xlogarchcmd
+ // FIXME: get rid of PID = 0
+ LogChildExit(WIFSIGNALED(rc) ? FATAL : LOG, archive command, 0, rc);
return false;
}
diff -ur ../cvs-pgsql/src/backend/postmaster/postmaster.c ./src/backend/postmaster/postmaster.c
--- ../cvs-pgsql/src/backend/postmaster/postmaster.c 2007-11-19 13:38:49.0 +0100
+++ ./src/backend/postmaster/postmaster.c 2007-12-10 18:00:04.0 +0100
@@ -305,8 +305,6 @@
static void dummy_handler(SIGNAL_ARGS);
static void CleanupBackend(int pid, int exitstatus);
static void HandleChildCrash(int pid, int exitstatus, const char *procname);
-static void LogChildExit(int lev, const char *procname,
- int pid, int exitstatus);
static void PostmasterStateMachine(void);
static void BackendInitialize(Port *port);
static int BackendRun(Port *port);
@@ -2477,7 +2475,7 @@
/*
* Log the death of a child process.
*/
-static void
+void
LogChildExit(int lev, const char *procname, int pid, int exitstatus)
{
if (WIFEXITED(exitstatus))
diff -ur ../cvs-pgsql/src/include/postmaster/postmaster.h ./src/include/postmaster/postmaster.h
--- ../cvs-pgsql/src/include/postmaster/postmaster.h 2007-02-19 11:05:59.0 +0100
+++ ./src/include/postmaster/postmaster.h 2007-12-10 18:00:02.0 +0100
@@ -37,6 +37,8 @@
extern int PostmasterMain(int argc, char *argv[]);
extern void ClosePostmasterPorts(bool am_syslogger);
+extern void LogChildExit(int lev, const char *procname,
+ int pid, int exitstatus);
#ifdef EXEC_BACKEND
extern pid_t postmaster_forkexec(int argc, char *argv[]);
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 12:14:58 -0500 (EST) Kris Jurka [EMAIL PROTECTED] wrote: On Mon, 10 Dec 2007, Joshua D. Drake wrote: 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Then why not list Stefan as a contributor to the original feature rather than calling it out as a separate item? *shrug* that is semantic to me. My point was the attribution :) Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFHXXmUATb/zqfZUUQRAlfmAKCYhSEKuarEchkxlJJMf3Z8SVtT9gCY2Hex ufDRdcgXQqEcnnNYh3JiCg== =Qr0T -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 16:12:54 + Gregory Stark [EMAIL PROTECTED] wrote: Tom Lane [EMAIL PROTECTED] writes: When Tom Lockhart was around the project it was even messier, since he and I shared not only the same first name but all three initials. Then there's Greg Stark, Greg Smith, and Greg Sabino (Mullane). Perhaps we should just go by uid. We even have two Gavins. However I think we will truly reach the point of no return with we have two Heikkis. Once we have two Heikkis it will be obvious to anyone that we are the World's most globally developed advanced Open Source database. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXXoWATb/zqfZUUQRAgmEAJ9NQ9kOc5xH93n+1SQOY/gvex4I8wCfW0rs TTdZ/hEgz5Pdlob2bl55o+Y= =Ckqs -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Release Note Changes
Joshua D. Drake wrote: We even have two Gavins. However I think we will truly reach the point of no return with we have two Heikkis. Once we have two Heikkis it will be obvious to anyone that we are the World's most globally developed advanced Open Source database. Hey, we have two Hiroshi's. -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos... (Yo, hablando de sueños eróticos) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem of a server gettext message.
Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi. From: Peter Eisentraut [EMAIL PROTECTED] Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi Peter-san. It is this. http://winpg.jp/~saito/pg83/ja.zip Sorry, we need the *po* (text) files, not the *mo* (binary) files. Ooops, Although it is an object for Version 8.2.5. http://www.postgresql.jp/wg/jpugdoc/po/postgresql-8-2-5-nls-patch.gz OK, you have PO file in EUC-JP server encoding UTF-8 client encoding SJIS When the server wants to send an error message to the client, it will convert them from the server to the client encoding. The English messages are ASCII, so this will work, because server encodings are required to be ASCII compatible. The result of the gettext calls, however, is encoded in EUC-JP, so the server will take the EUC-JP bytes and attempt to do a UTF-8 to SJIS conversion on them. This will cause a crash. What you need to do is set the locale to something compatible with the server encoding (e.g., ja_JP.utf8). Then gettext will recode its EUC-JP data to UTF-8 before it is sent to the server. More specifically, you need to set the LC_CTYPE locale category to make this happen. I understand that users in Japanese environments like to keep the LC_COLLATE setting to C, and you should still be able to do that. But without a proper LC_CTYPE setting, this will not work. (That is the explanation for Linux. Windows might be different in the details, but I suspect it has the same mechanisms.) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] archive_command failures report confusing exit status
Peter Eisentraut wrote: I figured it would make sense if pgarch.c used the same mechanism that postmaster.c uses to report the various variants of regular and signal exits. Hmm. Getting rid of the (PID 0) is going to be a mess enough for translations that I think it is worth pgarch.c having its own routine for this. Furthermore I think the detailed archive command should be reported in an errdetail() field, which makes it even farther off. (Hmm, there is nearly duplicate code in pclose_check already). -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J Nunca confiaré en un traidor. Ni siquiera si el traidor lo he creado yo (Barón Vladimir Harkonnen) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Trigger proglem
Good day,
We have written a trigger that is
We have written a trigger that is associated with the table. When any
changes are submitted, then this trigger reconstruct and writes sql query to
the table querieslog.
We have found a problem: from time to time some sql query is not written
while the query is executed . It is seen: In php where the queries are
formed we see that the query is sent for execution and executed ant the base
was modified, but the trigger seems just to skip it (does not work with the
query). It is seen most in big tables and during big load of the server.
Current version of postgresql is 8.2.5.
Description of the table where the queries are stored
\d querieslog
Table public.querieslog
Column | Type | Modifiers
+--+---
query | text |
query_id | bigint | not null default
nextval('querieslog_query_id_seq'::regclass)
timequery | timestamp with time zone | default now()
timeinsert | integer |
Indexes:
querieslog_query_id_index btree (query_id)
The parameters of summoning the trigger for the table with one key:
pgr_iud_trig BEFORE INSERT OR DELETE OR UPDATE ON phpbb_users FOR EACH ROW
EXECUTE PROCEDURE pgr_iud('user_id')
For the tahbles with several key fields - we write the list of keys with
space between them
pgr_iud_trig BEFORE INSERT OR DELETE OR UPDATE ON mailing_list FOR EACH ROW
EXECUTE PROCEDURE pgr_iud('user_id mailing_id')
cc -I /usr/local/pgsql/include
/server/ -fpic -c -O3 repl.c
cc -shared -o pgr_iud.so repl.o
and repl.c itself - http://www.grebnevs.com/trigger/repl.c
Re: [HACKERS] Release Note Changes
Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: When Tom Lockhart was around the project it was even messier, since he and I shared not only the same first name but all three initials. Then there's Greg Stark, Greg Smith, and Greg Sabino (Mullane). Perhaps we should just go by uid. PGP key fingerprint, maybe? //Magnus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] archive_command failures report confusing exit status
On Mon, 2007-12-10 at 18:27 +0100, Peter Eisentraut wrote: Failures of archive_command calls report a confusing exit status such as: LOG: archive command cp 'pg_xlog/0001' '/nonexistent/0001' failed: return code 256 The actual return code is 1; it neglects to apply WEXITSTATUS(). Yes, you're right. I figured it would make sense if pgarch.c used the same mechanism that postmaster.c uses to report the various variants of regular and signal exits. OK, you could sell me on that. How will you get rid of (PID = 0)? Maybe it would be better to do that on two lines: LOG: archive command . failed FATAL: archive_command was terminated by signal ... -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Note Changes
Alvaro Herrera wrote: Joshua D. Drake wrote: We even have two Gavins. However I think we will truly reach the point of no return with we have two Heikkis. Once we have two Heikkis it will be obvious to anyone that we are the World's most globally developed advanced Open Source database. Hey, we have two Hiroshi's. I just noticed on winsock_strerror that we have two Magnus' too. Are we changing our tagline? -- Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4 Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 18:13:58 -0300 Alvaro Herrera [EMAIL PROTECTED] wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: We even have two Gavins. However I think we will truly reach the point of no return with we have two Heikkis. Once we have two Heikkis it will be obvious to anyone that we are the World's most globally developed advanced Open Source database. Hey, we have two Hiroshi's. I just noticed on winsock_strerror that we have two Magnus' too. Are we changing our tagline? PostgreSQL two for the price of one. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD4DBQFHXayXATb/zqfZUUQRAnReAJ96RoG+IIOxFXfov6webzjE8unGhwCXQnVP 6fBm5R4jEb65M6MjlJoYSA== =6wc4 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] archive_command failures report confusing exit status
Alvaro Herrera [EMAIL PROTECTED] writes: Peter Eisentraut wrote: I figured it would make sense if pgarch.c used the same mechanism that postmaster.c uses to report the various variants of regular and signal exits. Hmm. Getting rid of the (PID 0) is going to be a mess enough for translations that I think it is worth pgarch.c having its own routine for this. Furthermore I think the detailed archive command should be reported in an errdetail() field, which makes it even farther off. I agree with Alvaro --- trying to make LogChildExit serve two masters will be uglier and less maintainable than having two copies of the not-really-so-complex logic involved. Leave postmaster.c alone and just make pgarch.c smarter. (But having said that, +1 for improving the message.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Problem of a server gettext message.
Peter Eisentraut [EMAIL PROTECTED] writes: When the server wants to send an error message to the client, it will convert them from the server to the client encoding. The English messages are ASCII, so this will work, because server encodings are required to be ASCII compatible. The result of the gettext calls, however, is encoded in EUC-JP, so the server will take the EUC-JP bytes and attempt to do a UTF-8 to SJIS conversion on them. This will cause a crash. The problem here basically comes from the fact that gettext looks to LC_CTYPE to decide what encoding it's supposed to convert to (and I suppose it punts when LC_CTYPE = C). Does it have a way by which we could override that, to tell it the actual DB encoding regardless of the locale environment? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Release Note Changes
Kris Jurka wrote: On Mon, 10 Dec 2007, Joshua D. Drake wrote: 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Then why not list Stefan as a contributor to the original feature rather than calling it out as a separate item? That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) Can people thing of other cases? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Release Note Changes
Alvaro Herrera wrote: Alvaro Herrera wrote: Joshua D. Drake wrote: We even have two Gavins. However I think we will truly reach the point of no return with we have two Heikkis. Once we have two Heikkis it will be obvious to anyone that we are the World's most globally developed advanced Open Source database. Hey, we have two Hiroshi's. I just noticed on winsock_strerror that we have two Magnus' too. Are we changing our tagline? What I do in those cases is 'Magnus' is the frequent Magnus Hagander and other Magnus' are given a last name. :-O It seems work because _we_ haven't been confused by it (no saying about our audience). The problem is Dave where both Page and Fetter are common contributors. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Release Note Changes
Joshua D. Drake wrote: Bruce Momjian wrote: Joshua D. Drake wrote: Bruce Momjian wrote: Joshua D. Drake wrote: I assumed the white paper would have proper attribution. Right, but is the white paper going to be thorough to mention _all_ changes? Hmmm good question which gets back to where we started :). My very first thought on all of this was that we would list all notable changes but that we wouldn't mention anyone's name. Isn't that listing what is already in the release notes? No :). What is listed already in the release notes is what you think is notable, which is why I mention the subjective below. What is cool to you may not be cool to others etc... I am not saying you are doing a bad job just that it is subjective. Case in point I think the work that Stefan did for this release is notable. I believe it is notable for several reasons. 1. Usability 2. Recognition (yes I am aware of the thoughts on that) 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Tom and you disagreed. I understand the reasoning and I don't actually disagree with the thought process but I think the thought process is flawed. I do not believe people only look at the release notes for wow cool. I believe they look at them to see who deserves kudos in this release. OK, that was clear to me. You are saying based on the criteria we have used in the past our system of feedback works (good), but the criteria used isn't 100% agreed. My point is that it isn't that I am too subjective and closed to feedback on adding/removing items, but rather the policy used isn't open for feedback; but it really is. Should we open discussion of changing the policy? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Release Note Changes
On Mon, 10 Dec 2007, Bruce Momjian wrote: Kris Jurka wrote: On Mon, 10 Dec 2007, Joshua D. Drake wrote: 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Then why not list Stefan as a contributor to the original feature rather than calling it out as a separate item? That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) Can people thing of other cases? Wait, I think we need more words about original authors ! For example, Full text search (Teodor,Oleg) is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner). Honestly, there were more developers who participated in integration. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 17:16:12 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Kris Jurka wrote: On Mon, 10 Dec 2007, Joshua D. Drake wrote: 3. It was grunt work that should have been done with the original patch that didn't get done. Stefan picked up the ball and ran with it and produced something that will make our product more usable for the end user. Then why not list Stefan as a contributor to the original feature rather than calling it out as a separate item? That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) I do still think we should add last names. AndrewD mentioned that and it is far better than Magnus and his pgp fingerprint idea ;) Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXb9DATb/zqfZUUQRAslwAJ9jxbYCaBUyv6Q1PpJvKbctMmwgFwCfQp5u gGNnlzxi0AbySfKy6M3qMxE= =uzgF -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 17:26:11 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: Tom and you disagreed. I understand the reasoning and I don't actually disagree with the thought process but I think the thought process is flawed. I do not believe people only look at the release notes for wow cool. I believe they look at them to see who deserves kudos in this release. OK, that was clear to me. You are saying based on the criteria we have used in the past our system of feedback works (good), but the criteria used isn't 100% agreed. My point is that it isn't that I am too subjective and closed to feedback on adding/removing items, but rather the policy used isn't open for feedback; but it really is. Should we open discussion of changing the policy? That sounds reasonable. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXb93ATb/zqfZUUQRAvdXAJkBuL3h1ZRA3j40JBJ9V1pWglF1uACdFBYr e+234rZRsjT+2AqFoyoQ2Nc= =33Td -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Note Changes
Oleg Bartunov [EMAIL PROTECTED] writes: On Mon, 10 Dec 2007, Bruce Momjian wrote: That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) Wait, I think we need more words about original authors ! I agree, that is an *utterly* horrid change, as it makes it look like Stefan's contribution was of the same order of magnitude as Teodor's and Oleg's. I don't mind giving Stefan an appropriate amount of credit, but this is not it --- in fact, I read this as a direct insult to the two of them. They have spent years on tsearch; I doubt he spent more than a day. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 17:56:01 -0500 Tom Lane [EMAIL PROTECTED] wrote: Oleg Bartunov [EMAIL PROTECTED] writes: On Mon, 10 Dec 2007, Bruce Momjian wrote: That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) Wait, I think we need more words about original authors ! I agree, that is an *utterly* horrid change, as it makes it look like Stefan's contribution was of the same order of magnitude as Teodor's and Oleg's. I don't mind giving Stefan an appropriate amount of credit, but this is not it --- in fact, I read this as a direct insult to the two of them. They have spent years on tsearch; I doubt he spent more than a day. That's fair. There is a psql section in the release notes. Why not just add it there. Various psql improvements including \d commands for tsearch, \prompt capability, and better \timing functionality. (Andrew Dunstan, Stefan Kaltenbrunner, Tom Lane, Chad Wagner) Sincerely, Joshua D. Drake regards, tom lane - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXcWMATb/zqfZUUQRAkjlAKCotR8AE8SWmvbUPLH8+s0E9LmIzACfSKW1 Zwnth6lhpbah7PJ/NAvY8RE= =d2gZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [BUGS] BUG #3799: csvlog skips some logs
Alvaro Herrera [EMAIL PROTECTED] writes: Another change I did was to change a %.*s to %*s. The precision marker seems useless AFAICT. This is wrong, broken, will cause crashes on platforms where the PS string is not null-terminated. (Hint: .* is a maximum width, * is a minimum width.) Another thing I thought strange was the order of the added columns; why isn't it more like the order in which they appear in the text version? In particular hint should probably come just after detail and before context, and internal-query should also come before context because when relevant it's usually more closely nested than the context stack. Otherwise the patch looks pretty sane to me. I didn't do any testing though. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] PGparam proposal
We will have a 0.6 patch tomorrow. This is not a patch, its a proposal.
The implementation has been adjusted and is now a simple printf-style
interface. This is just a design proposal to see if people like the
idea and interface. Up to this point, we have not provided a formal
proposal; just a few patches with some explainations.
We would appreciate feedback!
DESIGN PROPOSAL
This proposal extends libpq by adding a printf style functions for
sending and recveiving through the paramterized interface. In
addition, a number of structs were introduced for storing the
binary version of built-in pgtypes.
RATIONALE
*) Removes the need to manually convert values to C types.
*) Simplifies use of binary interface, putting or getting values
*) Provide simple structures for many pgtypes, such as polygon,
which are not documented for client use.
*) Promotes use of parameterized API, which has performance and
security benefits.
*) Support for arrays is a major plus; w/o parsing or dealing with
the binary format.
*) Only requires 4 new functions to exports.txt.
INTERFACE
*) PQputf
*) PQgetf
*) PQexecParamsf
*) PQsendQueryParamsf
NOTE: Only PQputf and PQgetf are required for this interface to work.
With that in mind, the other two are really cool :)
int PQputf(PGconn *conn, const char *paramspec, ...);
PQputf offers a way of packing pgtypes for use with the parameterized
functions. One or more values can be put at the same time. The params
are stored within the PGconn struct as a PGparam structure (internal
API only). The paramspec describes the pgtypes that you want to put.
In the paramspec, anything other than a valid conversion specifiers is
ignored. %n4, [EMAIL PROTECTED] %n8 is treated the same way as %n4%n8.
Once all params have been put, one of four paramterized functions that
are aware of PGparam can be used:
* PQexecParams
* PQexecPrepared
* PQsendQueryParams
* PQsendQueryPrepared
For a list of PQputf conversion specifiers, see format_spec.txt.
Example:
PGpoint pt = {1.2, 4.5};
/* This puts an int4, int8, point and a text */
PQputf(conn, %n4 %n8 %gp %cT, 100, 123LL, pt, text);
/* execute: Only the conn, command and resultFormat args are used. */
PQexecParams(conn, INSERT INTO t VALUES ($1,$2,$3,$4),
0, NULL, NULL, NULL, NULL, 1);
int PQgetf(
const PGresult *res,
int tup_num,
const char *fieldspec,
...);
PQgetf offers a way of getting result values from binary results. It
currently offers the ability to get from text results as well, but we
are not sure this should be supported. PQgetf is really a way of
getting binary results. In the fieldspec, anything other than a valid
conversion specifier is ignored. %n4, [EMAIL PROTECTED] %n8 is treated the same
way as %n4%n8.
For a list of PQgetf conversion specifiers, see format_spec.txt.
Example:
int i4;
long long i8;
PGpoint pt;
char *text;
/* From tuple 0, get an int4 from field 0, an int8 from field 1, a point
* from field 2 and a text from field 3.
*/
PQgetf(res, 0, %n4 %n8 %gp %cT, 0, i4, 1, i8, 2, pt, 3, text);
PUT EXEC
We also propose two other functions that allow putting parameters and
executing all in one call. This is basically a wrapper for PQputf +
exec/send. These are the natural evolution of PQputf.
extern PGresult *PQexecParamsf(
PGconn *conn,
const char *cmdspec,
int resultFormat,
...);
extern int PQsendQueryParamsf(
PGconn *conn,
const char *cmdspec,
int resultFormat,
...);
Example:
int format = 1;
PGpoint pt = {1.2, 4.5};
/* 2 step example */
PQputf(conn, %n4 %n8 %gp %cT, 100, 123LL, pt, text);
PQexecParams(conn, INSERT INTO t VALUES ($1,$2,$3,$4),
0, NULL, NULL, NULL, NULL, 1);
/* 1 step example */
PQexecParamsf(conn, INSERT INTO t VALUES (%n4, %n8, %gp, %cT,),
format, 100, 123LL, pt, text);
This causes the four params to be put. Then the parameterized function
arrays are built and the below query is executed.
INSERT INTO t VALUES ($1, $2, $3, $4)
If you use PQputf prior to execf/sendf, then those parameters are included.
Doing this is basically appending more params during the exec/send call.
PQputf(conn, %n4, 100);
PQexecParamsf(conn, INSERT INTO t VALUES (%cT, $1), format, text);
Resulting query assigns an int4 to $1 and a text to $2.
INSERT INTO t VALUES ($2, $1)
andrew merlin
For putf or getf, the conversion specifier is a % followed by a two character
encoding. The first character indicates the type class while the second
character identifies the data type within that class.
The byteaptr and textptr are really bytea and text. The ptr extension
indicates that only a pointer assignment should occur rather than a copy.
Most of the below types are already implemented. Some are still being
worked on.
Character types:
cc char
ct text, varchar, char
cT textptr
Boolean types:
bb bool
Numeric Types:
n2 int2
n4 int4
n8 int8
nf float4
nd float8
nn numeric
Bytea types:
Bb bytea
BB
Re: [HACKERS] Release Note Changes
Release note introduction attached and applied. --- bruce wrote: Based on this discussion I think it is clear the release notes chapter needs an introductory section. This would not be for any specific release but the release notes in general. I have come up with the following text: The release notes contain the significant changes for each PostgreSQL release, with major features or migration issues often listed at the top. The release notes do not contain changes that affect only a few users or changes that are internal and therefore not user-visible. For example, the optimizer is improved in almost every release, but the improvements are usually observed by users as simply faster queries. A complete list of all changes for a release can only be obtained by viewing the CVS logs for each release. The committers email list (http://archives.postgresql.org/pgsql-committers/) contains all source code changes as well. There is also a web interface that shows changes to specific files or directories (http://developer.postgresql.org/cvsweb.cgi/pgsql/). (XXX SVN is good but needs Next button at bottom, no branch filter, https certificate update https://projects.commandprompt.com/public/pgsql/log/?action=stop_on_copyrev=stop_rev=mode=stop_on_copyverbose=on). A names appearing next to an item represents the major developer for that item. Of course all changes involve community discussion and patch review so each item is truely a community activity. First-name-only entries represent established developers, while full names represent newer contributors. I need help with the CVS section. Do we publish full CVS logs for a release? I like the SVN display because it groups commits but can improvements I listed above be made? -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/release.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/release.sgml,v retrieving revision 1.566 diff -c -c -r1.566 release.sgml *** doc/src/sgml/release.sgml 10 Dec 2007 22:14:52 - 1.566 --- doc/src/sgml/release.sgml 10 Dec 2007 23:35:52 - *** *** 35,40 --- 35,71 appendix id=release titleRelease Notes/title + sect1 id=release-introduction + titleIntroduction/title + + para +The release notes contain the significant changes in each PostgreSQL +release, with major features and migration issues often listed at the +top. The release notes do not contain changes that affect only a few +users or changes that are internal and therefore not user-visible. For +example, the optimizer is improved in almost every release, but the +improvements are usually observed by users as simply faster queries. + /para + + para +A complete list of changes for each release can be obtained by +viewing the link linkend=cvsCVS/ logs for each release. The ulink +url=http://archives.postgresql.org/pgsql-committers/;committers email +list/ulink contains all source code changes as well. There is also +a ulink url=http://developer.postgresql.org/cvsweb.cgi/pgsql/;web +interface/ulink that shows changes to specific files. +!-- we need a file containing the CVS logs for each release, and something +like the SVN web interface that groups commits but has branches -- + /para + + para +The name appearing next to each item represents the major developer for +that item. Of course all changes involve community discussion and patch +review so each item is truly a community effort. + /para + + /sect1 + sect1 id=release-8-3 titleRelease 8.3/title ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Release Note Changes
Tom Lane wrote: Oleg Bartunov [EMAIL PROTECTED] writes: On Mon, 10 Dec 2007, Bruce Momjian wrote: That is an excellent suggestion, done: Full text search is integrated into the core database system (Teodor, Oleg, Stefan Kaltenbrunner) Wait, I think we need more words about original authors ! I agree, that is an *utterly* horrid change, as it makes it look like Stefan's contribution was of the same order of magnitude as Teodor's and Oleg's. I don't mind giving Stefan an appropriate amount of credit, but this is not it --- in fact, I read this as a direct insult to the two of them. They have spent years on tsearch; I doubt he spent more than a day. By trying to make one developer happy I have made two unhappy. I have remved Stefan Kaltenbrunner's name from that item. Basically I should have expected this because once we divert from a clear policy we are going to cause an imbalance. Unless we change the policy I am not going to make any adjustments just to give credit. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 18:40:52 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: By trying to make one developer happy I have made two unhappy. I have remved Stefan Kaltenbrunner's name from that item. Basically I should have expected this because once we divert from a clear policy we are going to cause an imbalance. Unless we change the policy I am not going to make any adjustments just to give credit. See my other email in response to Tom. However, I would note that there is no clear policy. There is clear policy to you and Tom but I have been here a lot of years and didn't know about it. If that is the policy, fine :) but it needs to get documented somewhere that is no buried in a list archive that gets thousands of messages a month. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHXdFOATb/zqfZUUQRAhkrAJ45fgaJ0EIw2xZ4XMZMYGeVWHxvLgCeLAcG A9h4i3ULbmZK0+1UZnazeew= =KU7V -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Release Note Changes
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 PostgreSQL two for the price of one. Postgre *and* SQL? :) - -- Greg Sabino Mullane [EMAIL PROTECTED] The first Postgres Greg (3 and counting now...) PGP Key: 0x14964AC8 200712101856 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHXdJnvJuQZxSWSsgRAycCAJ4vTyEwvTT3saHa8Rbk3VSwSdirJgCfUrbr kzAjpXkRPOzyoghqFcu5NUM= =x7uc -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] PGparam proposal
Andrew Chernow [EMAIL PROTECTED] writes: This proposal extends libpq by adding a printf style functions for sending and recveiving through the paramterized interface. I think a printf-style API is fundamentally a bad idea in this context. printf only works well when the set of concepts (datatypes, format specifiers, etc) is small and fixed; neither of which adjectives describe PG's set of datatypes. You've already had to go to two-character format codes in order to have even slightly mnemonic codes for the most commonly used built-in types; that doesn't look like it's going to scale up for long. And what are you going to do about add-on data types, such as contrib stuff, PostGIS and other add-on projects, or user-defined types? PQputf offers a way of packing pgtypes for use with the parameterized functions. One or more values can be put at the same time. The params are stored within the PGconn struct as a PGparam structure (internal API only). The paramspec describes the pgtypes that you want to put. In the paramspec, anything other than a valid conversion specifiers is ignored. %n4, [EMAIL PROTECTED] %n8 is treated the same way as %n4%n8. Once all params have been put, one of four paramterized functions that are aware of PGparam can be used: I find the idea of embedding state like that into the PGconn to be pretty horrid, as well. It makes the design non-reentrant --- consider the example of wanting to execute a query during the process of computing parameters for a later query. If there's merit in the idea at all, expose PGparam as a separate (but opaque) data structure that is explicitly passed into and out of the functions that are concerned with it. * PQexecParams * PQexecPrepared * PQsendQueryParams * PQsendQueryPrepared You can't just randomly change the behavior of existing API functions. Date and time types: dt time, timetz dd date dT timestamp, timestamptz di interval I'm not sure whether timestamp/timestamptz can or should be treated as interchangeable; but time and timetz definitely are not. BTW, how will this code react to the inevitable future changes in binary formats? As examples, show what you'd do with * the 8.2-to-8.3 change in the width of type money * the likely future change to type timestamptz to store original timezone explicitly * the likely future change to type text to store encoding/collation info explicitly If the answer is that libpq will be unable to deal with these events, I think the proposal is dead in the water. There's a reason why we aren't pushing client-side use of binary formats very hard: in many cases those formats are subject to change. There might be some value in the concept of building up parameter values in a PGparam object before passing it to an eventual PQexec-like function. However, I see no reason to tie that concept to the use of binary parameter format. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] whats the deal with -u ?
Stephen Frost [EMAIL PROTECTED] writes: * Tom Lane ([EMAIL PROTECTED]) wrote: However, I think we should either get rid of -u or find a way to un-deprecate it. Right now, it's undocumented and as far as I can see the main effect of having it is to cause confusion such as that which started this thread. On the whole I'm in favor of removing it. It's been undocumented for long enough that no one could really complain if it disappears. I agree that it'd be best to remove it and I don't think it'll cause problems for it to go away. I dug around a bit more and realized that pg_dump and pg_restore have the same -u switch with the same behavior. Theirs are likewise undocumented, but they don't print the annoying deprecation notice when it's used. The use-case for a prompt for username seems even less for these two programs than for psql, so I doubt that removing the switch is likely to break any existing usage. Barring objections, I'll remove all three tomorrow. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Release Note Changes
Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 10 Dec 2007 18:40:52 -0500 (EST) Bruce Momjian [EMAIL PROTECTED] wrote: By trying to make one developer happy I have made two unhappy. I have removed Stefan Kaltenbrunner's name from that item. Basically I should have expected this because once we divert from a clear policy we are going to cause an imbalance. Unless we change the policy I am not going to make any adjustments just to give credit. See my other email in response to Tom. However, I would note that there is no clear policy. There is clear policy to you and Tom but I have been here a lot of years and didn't know about it. If that is the policy, fine :) but it needs to get documented somewhere that is no buried in a list archive that gets thousands of messages a month. Ah, glad you asked. It is now in the release note introduction that I added as part of this discussion: http://momjian.us/main/writings/pgsql/sgml/release-introduction.html I realize it wasn't documented but the issue was always open for discussion, as you have seen. I think this is the first time we really talked about it because in the past no one had an issue with the policy. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Note Changes
Bruce Momjian wrote: Ah, glad you asked. It is now in the release note introduction that I added as part of this discussion: http://momjian.us/main/writings/pgsql/sgml/release-introduction.html I realize it wasn't documented but the issue was always open for discussion, as you have seen. I think this is the first time we really talked about it because in the past no one had an issue with the policy. Sure :). It is good to rehash things occasionally. It keeps us on our toes and up with the times. I do still think we need to figure out a way to resolve the underlying issue though. Did you see my email to Tom on the matter? About grouping? Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] whats the deal with -u ?
On Monday 10 December 2007 10:16, Tom Lane wrote:
Further down the road, those whose notion of intuitive was formed
by mysql might lobby to have -u become an alternate spelling for -U,
crontab, truss, sudo, ps, strace, top, etc...
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] Problem of a server gettext message.
Hi Peter-san. Thank you for various. ! - Original Message - From: Peter Eisentraut [EMAIL PROTECTED] Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi. From: Peter Eisentraut [EMAIL PROTECTED] Am Montag, 10. Dezember 2007 schrieb Hiroshi Saito: Hi Peter-san. It is this. http://winpg.jp/~saito/pg83/ja.zip Sorry, we need the *po* (text) files, not the *mo* (binary) files. Ooops, Although it is an object for Version 8.2.5. http://www.postgresql.jp/wg/jpugdoc/po/postgresql-8-2-5-nls-patch.gz OK, you have PO file in EUC-JP server encoding UTF-8 client encoding SJIS Yes. When the server wants to send an error message to the client, it will convert them from the server to the client encoding. The English messages are ASCII, so this will work, because server encodings are required to be ASCII compatible. The result of the gettext calls, however, is encoded in EUC-JP, so the server will take the EUC-JP bytes and attempt to do a UTF-8 to SJIS conversion on them. This will cause a crash. Probably no. GetText is conversion po(EUC_JP) to SJIS. Then, The stderr output of a server is outputted without an error to log by it. That's right message with it similar to start-up. However, The conversion obstacle of a message is encountered at the time of the conditions returned to a client. Conversion of the step of the following it takes place. 1. iconv(GetText) po(EUC_JP) to SJIS. 2. message to client UTF8(server encoding) to SJIS(client encoding) But, this character that should be UTF-8 is a SJIS message(1.). It causes an error. Therefore, this log is proving. http://winpg.jp/~saito/pg83/postgresql-8.3beta4_crash.log Anyway, the current situation is it although there is a problem.. What you need to do is set the locale to something compatible with the server encoding (e.g., ja_JP.utf8). Then gettext will recode its EUC-JP data to UTF-8 before it is sent to the server. More specifically, you need to set the LC_CTYPE locale category to make this happen. I understand that users in Japanese environments like to keep the LC_COLLATE setting to C, and you should still be able to do that. But without a proper LC_CTYPE setting, this will not work. (That is the explanation for Linux. Windows might be different in the details, but I suspect it has the same mechanisms.) As for message, the current state is not such probably.. It is the problem which arises only by the server with client encoding which can't be used as server encoding. It may be a problem of Japan... If a message text is not used by the server, a problem does not occur. Therefore, It is TODO until it has the margin of time. sorry... I'm very busy now... I am deeply grateful to you for your kindness. Regards, Hiroshi Saito ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Problem of a server gettext message.
Hiroshi Saito [EMAIL PROTECTED] writes: Probably no. GetText is conversion po(EUC_JP) to SJIS. Then, The stderr output of a server is outputted without an error to log by it. That's right message with it similar to start-up. However, The conversion obstacle of a message is encountered at the time of the conditions returned to a client. Conversion of the step of the following it takes place. 1. iconv(GetText) po(EUC_JP) to SJIS. 2. message to client UTF8(server encoding) to SJIS(client encoding) But, this character that should be UTF-8 is a SJIS message(1.). It causes an error. Are you sure about that? Why would gettext be converting to SJIS, when SJIS is nowhere in the environment it can see? I believe that Peter's hypothesis is that gettext is leaving the string in EUC_JP because it sees locale = C and so has no basis for doing any conversion. We still end up with a failure, because the basic problem is that the string isn't UTF8, but it's important to be sure we understand the exact mechanism. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Release Note Changes
On Dec 10, 2007 6:43 PM, Andrew Dunstan [EMAIL PROTECTED] wrote: Pavan Deolasee wrote: I don't agree completely. HOT updates is just one significant benefit of HOT and is constrained by the non-index column updates. But the other major benefit of truncating the tuples to their line pointers applies to HOT as well as COLD updates and DELETEs. This should also have a non trivial positive impact on the performance. There might be few scenarios where HOT may not show any improvement such as CPU-bound applications, but I am not sure if its worth mentioning. Um, I don't understand. I freely admit that I haven't kept up with all the nuances of the HOT discussions, but this bit has totally eluded me, so please elucidate. One of the improvements of HOT is to truncate a DEAD tuple to its line pointer. A DEAD tuple could be an old version of an updated tuple or a deleted tuple. When a tuple is truncated, the space used by the line pointer can not be reused (until the index entries are removed). But the space used by the actual tuple can be reused for a later update, after the page is defragmented. Note that this defragmentation can happen outside of a VACUUM. This gives us an ability to run VACUUM less frequently on a table. We still need to run VACUUM to remove the line pointer bloat, but may be less frequently for the given percentage of bloat. IMHO this should have a positive effect on performance atleast in an IO bound scenario. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] Problem of a server gettext message.
From: Tom Lane [EMAIL PROTECTED] Are you sure about that? Why would gettext be converting to SJIS, when SJIS is nowhere in the environment it can see? I believe that Peter's hypothesis is that gettext is leaving the string in EUC_JP because it sees locale = C and so has no basis for doing any conversion. We still end up with a failure, because the basic problem is that the string isn't UTF8, but it's important to be sure we understand the exact mechanism. Um, It is a simple GetText program. http://winpg.jp/~saito/pg83/message_check/gtext.c for example.. http://winpg.jp/~saito/pg83/message_check/gettext_932.png http://winpg.jp/~saito/pg83/message_check/C_message.txt http://winpg.jp/~saito/pg83/message_check/Non_message.txt http://winpg.jp/~saito/pg83/message_check/UTF8_message.txt http://winpg.jp/~saito/pg83/message_check/Japanese_message.txt All are SJIS outputs. However, chcp 1252 http://winpg.jp/~saito/pg83/message_check/gettext_1252.png Regards, Hiroshi Saito ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
