Re: [HACKERS] schemapg.h
Tom Lane escribió: > Alvaro Herrera writes: > > I think having schemapg.h be autogenerated is a good idea, so I stripped > > that from Robert Haas' patch. Here's the result. This should be > > relatively uncontroversial since, well, the controversial stuff has been > > stripped. The one problem is that it introduces more complex code than > > it removes dull declarations. > > Indeed, and it fails to get rid of all the dull declarations :-(. Right. I don't think we're going to move forward if we only accept giant steps at a time, and we simultaneously reject patches that are too intrusive. What this says is that we're going to need to accept that a first change to the file generation system is going to be a dwarf step forward; otherwise we're going to stay right where we arej with which I'm not terribly happy. > I thought the idea was to generate all this stuff directly from the C > struct declarations (plus some hardwired knowledge about the > datatypes, comparable to what is in TypInfo in bootstrap.c already). > Removing four out of six Schema_pg_xxx macros while leaving the > equivalent DATA declarations behind isn't my idea of a major step > forward. Hmm, perhaps that's workable. I'll have a look around. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] pgbench: new feature allowing to launch shell commands
Thanks a lot for all of your pieces of advice. I modified the name of the page as well as I deleted the parts linked to the -P option. It just consisted in deleting the right parts. Here is the lighted version. -- Michael Paquier NTT OSSC postgresql-8.4.0-pgbenchshell2.0.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] schemapg.h
On Wed, Aug 12, 2009 at 6:44 PM, Tom Lane wrote: > Alvaro Herrera writes: >> I think having schemapg.h be autogenerated is a good idea, so I stripped >> that from Robert Haas' patch. Here's the result. This should be >> relatively uncontroversial since, well, the controversial stuff has been >> stripped. The one problem is that it introduces more complex code than >> it removes dull declarations. > > Indeed, and it fails to get rid of all the dull declarations :-(. > > I thought the idea was to generate all this stuff directly from the C > struct declarations (plus some hardwired knowledge about the datatypes, > comparable to what is in TypInfo in bootstrap.c already). Removing four > out of six Schema_pg_xxx macros while leaving the equivalent DATA > declarations behind isn't my idea of a major step forward. I never intended anything in this patch to be a major step forward. It's so difficult to get agreement even on a minor step forward that it is a waste of time to try for a major step forward. I can only hope that we'll eventually be able to commit something out of all of this, and that the discussion we have along the way will provide some direction for future improvements. Personally, having relatively recently gone through the pain of making changes to a bootstrap catalog (for ALTER TABLE ... ALTER COLUMN ... SET STATISTICS DISTINCT), I think reducing the number of places that have to be updated from 2 to 1 is a BIG improvement. We can deal with getting it down to 0 in a future patch. Eliminating any DATA() lines at all is a big deal. It means that the current way that we generate postgres.bki will no longer work; that file will have to be generated by a script that has all the same smarts as the script that generates the Schema_pg_* declarations. That is why the patch that I submitted unifies ALL of the things that look at DATA() lines into a single script, so that changes to the logic only need to be made in one place. Although I'm happy to have any portion of this work committed, I think that we should try hard to move toward that as a goal, because I think it will open the door to many future improvements. > The patch as submitted also appears to turn Perl into a hardwired > requirement for all Unix builds. While I'm not necessarily averse to > doing that, I'd like to get more results out of it than this. I haven't looked at Alvaro's patch. Mine turned everything that was generated by perl into a distprep target. The reasons why I thought this was OK were discussed in the email in which I submitted the patch. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] surprising trigger/foreign key interaction
Alvaro Herrera writes: > However I'm guessing that what actually happens is that heap_update is > returning HeapTupleSelfUpdated instead, which the code states as > /* nothing to do */. Yeah. > I imagine this is so because of some old fiddling to get semantics just > right for obscure corner cases, but it feels wrong nevertheless. I suspect it was reluctance to use the EvalPlanQual semantics (which are pretty bogus in their own way) for perfectly deterministic single-transaction cases. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Error message for FK referencing deferrable unique/PK
Dean Rasheed writes: > I spotted another error message which could use improvement: > ... > The attached patch to transformFkeyCheckAttrs() makes the former case > generate a similar error to the latter. Ah, you caught me being lazy ;-). I had actually considered doing this while reviewing the deferrable-uniqueness patch, but ran out of energy and did the one-liner change instead. Patch applied. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] schemapg.h
Alvaro Herrera writes: > I think having schemapg.h be autogenerated is a good idea, so I stripped > that from Robert Haas' patch. Here's the result. This should be > relatively uncontroversial since, well, the controversial stuff has been > stripped. The one problem is that it introduces more complex code than > it removes dull declarations. Indeed, and it fails to get rid of all the dull declarations :-(. I thought the idea was to generate all this stuff directly from the C struct declarations (plus some hardwired knowledge about the datatypes, comparable to what is in TypInfo in bootstrap.c already). Removing four out of six Schema_pg_xxx macros while leaving the equivalent DATA declarations behind isn't my idea of a major step forward. The patch as submitted also appears to turn Perl into a hardwired requirement for all Unix builds. While I'm not necessarily averse to doing that, I'd like to get more results out of it than this. And when we do do it, it needs to be documented and enforced by configure. Plus we ought to get rid of the workarounds we have for not requiring Perl for tarball builds. Or, if people would prefer to continue not requiring Perl, we'd need to make schemapg.h be one of the derived files that's shipped in tarballs. Lastly, it'd be nice if the comments in gen_schemapg.pl had some resemblance to what it is actually doing, rather than talking about a lot of stuff that was stripped out of it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha 1 release notes
On 8/12/09 11:13 AM, Peter Eisentraut wrote: > On Wednesday 12 August 2009 19:27:06 Josh Berkus wrote: >>> Can you export DocBook from that? >> Not without writing some custom perl code, no. >> >> Should we stick your release notes on git somewhere? I'd like to expand >> the and add a couple of things. > > I say just take the file and edit it. Will do. Teaching myself RST now -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] surprising trigger/foreign key interaction
Tom Lane wrote: > Stefan Kaltenbrunner writes: > > the "surprise" here was that the delete is getting silently surpressed > > even though the original Qual still holds and afaik should result in the > > row deleted. > > The delete from foo acts first (since you put it in a BEFORE trigger). > After the trigger comes back, the outer delete finds itself trying > to delete a row that was already updated by a later-starting command in > the same transaction. Hmm, I misread the code when talking to Stefan on IM. I followed the path that heap_update returns HeapTupleUpdated, which proceeds to do EvalPlanQual and attempt to update the updated version of the row. However I'm guessing that what actually happens is that heap_update is returning HeapTupleSelfUpdated instead, which the code states as /* nothing to do */. I imagine this is so because of some old fiddling to get semantics just right for obscure corner cases, but it feels wrong nevertheless. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
Pierre Frédéric Caillaud escribió: > But when I see a big red button, I just press it to see what happens. > Ugly hacks are useful to know how fast the thing can go ; then the > interesting part is to reimplement it cleanly, trying to reach the > same performance... Right -- now that you've shown a 6x speedup increase, it is clear that it makes sense to attempt a reimplementation. It also means it makes sense to have an additional pair or two of input/output functions. > >Maybe add new methods, fastrecv/fastsend etc. Types that don't > >implement them would simply use the slow methods, maintaining > >backwards compatibility. > I considered doing it like this, but it is a lot more work : adding > entries to the system catalogs, creating all the new functions, > deciding what to do with getTypeBinaryOutputInfo (since there would > be 2 variants), etc. Types that don't support the new functions > would need some form of indirection to call the old functions > instead, etc. In a word, doable, but kludgy, and I would need help > from a system catalog expert. Right. > Also, on upgrade, information about the new functions must be inserted > in the system catalogs ? (I don't know how this process works). No, that's not how pg_migrator works. Catalog upgrades are handled by pg_dump/pg_restore, so you don't need to worry about it at all. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] schemapg.h
I think having schemapg.h be autogenerated is a good idea, so I stripped
that from Robert Haas' patch. Here's the result. This should be
relatively uncontroversial since, well, the controversial stuff has been
stripped. The one problem is that it introduces more complex code than
it removes dull declarations.
Ideally this would serve as a basis upon which the rest of the generated
stuff is built. I think it would be good to have a module that contains
common code. In particular we could move process_input_file() to it,
and pass function refs for each subblock. That way each program could
be relatively short, and the regexes that parse the files would all be
in a single place.
Thoughts?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: src/backend/Makefile
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/Makefile,v
retrieving revision 1.132
diff -c -p -r1.132 Makefile
*** src/backend/Makefile 1 Jan 2009 17:23:34 - 1.132
--- src/backend/Makefile 12 Aug 2009 21:47:07 -
*** endif
*** 105,112
endif # aix
# Update the commonly used headers before building the subdirectories
! $(SUBDIRS:%=%-recursive): $(top_builddir)/src/include/parser/gram.h $(top_builddir)/src/include/utils/fmgroids.h $(top_builddir)/src/include/utils/probes.h
!
# The postgres.o target is needed by the rule in Makefile.global that
# creates the exports file when MAKE_EXPORTS = true.
--- 105,111
endif # aix
# Update the commonly used headers before building the subdirectories
! $(SUBDIRS:%=%-recursive): $(top_builddir)/src/include/parser/gram.h $(top_builddir)/src/include/utils/fmgroids.h $(top_builddir)/src/include/utils/probes.h $(top_builddir)/src/include/catalog/schemapg.h
# The postgres.o target is needed by the rule in Makefile.global that
# creates the exports file when MAKE_EXPORTS = true.
*** $(srcdir)/parser/gram.h: parser/gram.y
*** 124,129
--- 123,131
utils/fmgroids.h: utils/Gen_fmgrtab.sh $(top_srcdir)/src/include/catalog/pg_proc.h
$(MAKE) -C utils fmgroids.h
+ catalog/schemapg.h: utils/gen_schemapg.pl $(wildcard $(top_srcdir)/src/include/catalog/pg_*.h)
+ $(MAKE) -C utils schemapg.h
+
utils/probes.h: utils/probes.d
$(MAKE) -C utils probes.h
*** $(top_builddir)/src/include/utils/fmgroi
*** 140,145
--- 142,151
cd $(dir $@) && rm -f $(notdir $@) && \
$(LN_S) ../../../$(subdir)/utils/fmgroids.h .
+ $(top_builddir)/src/include/catalog/schemapg.h: utils/schemapg.h
+ cd $(dir $@) && rm -f $(notdir $@) && \
+ $(LN_S) ../../../$(subdir)/utils/schemapg.h .
+
$(top_builddir)/src/include/utils/probes.h: utils/probes.h
cd $(dir $@) && rm -f $(notdir $@) && \
$(LN_S) ../../../$(subdir)/utils/probes.h .
Index: src/backend/utils/Makefile
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/utils/Makefile,v
retrieving revision 1.28
diff -c -p -r1.28 Makefile
*** src/backend/utils/Makefile 1 Aug 2008 13:16:09 - 1.28
--- src/backend/utils/Makefile 12 Aug 2009 21:47:37 -
*** $(SUBDIRS:%=%-recursive): fmgroids.h
*** 20,25
--- 20,28
fmgroids.h fmgrtab.c: Gen_fmgrtab.sh $(top_srcdir)/src/include/catalog/pg_proc.h
AWK='$(AWK)' $(SHELL) $< $(top_srcdir)/src/include/catalog/pg_proc.h
+ schemapg.h: gen_schemapg.pl $(wildcard $(top_srcdir)/src/include/catalog/*.h)
+ $(PERL) $< -I$(top_srcdir)/src/include --set-version=$(VERSION) $(wildcard $(top_srcdir)/src/include/catalog/*.h)
+
ifneq ($(enable_dtrace), yes)
probes.h: Gen_dummy_probes.sed
endif
Index: src/backend/utils/gen_schemapg.pl
===
RCS file: src/backend/utils/gen_schemapg.pl
diff -N src/backend/utils/gen_schemapg.pl
*** /dev/null 1 Jan 1970 00:00:00 -
--- src/backend/utils/gen_schemapg.pl 12 Aug 2009 22:12:02 -
***
*** 0
--- 1,371
+ #!/usr/bin/perl
+ #
+ # gen_schemapg.pl
+ # The purpose of this script is to generate the schemapg.h header.
+ #
+ # Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group
+ # Portions Copyright (c) 1994, Regents of the University of California
+ #
+ # $PostgreSQL$
+
+ use strict;
+ use warnings;
+
+ my @OUTPUT = (
+ [ 'schemapg.h', \*SCHEMAPG ],
+ );
+
+ #
+ # There are a few types which are given one name in the C source, but a
+ # different name at the SQL level. These are enumerated here.
+ #
+ my %RENAME_ATTTYPE = (
+ 'Oid' => 'oid',
+ 'NameData' => 'name',
+ 'TransactionId' => 'xid'
+ );
+
+ #
+ # Argument parsing. We could use GetOpt::Long or similar here, but doing it
+ # this way avoids depending on any outside modules.
+ #
+ my @include_path;
+ my @input_file;
+ my $output_path = '';
+ my $major_version;
+ while (@ARGV) {
+ m
Re: [HACKERS] surprising trigger/foreign key interaction
Stefan Kaltenbrunner writes: > the "surprise" here was that the delete is getting silently surpressed > even though the original Qual still holds and afaik should result in the > row deleted. The delete from foo acts first (since you put it in a BEFORE trigger). After the trigger comes back, the outer delete finds itself trying to delete a row that was already updated by a later-starting command in the same transaction. So it doesn't do anything to that row (cf the HeapTupleSelfUpdated cases in execMain.c). I think this is documented someplace but don't remember where. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
>> We don't touch datatype APIs >> lightly, because it affects too much code. >> >> regards, tom lane > > I definitely agree with that. Actually, let me clarify: When I modified the datatype API, I was feeling uneasy, like "I shouldn't really touch this". But when I see a big red button, I just press it to see what happens. Ugly hacks are useful to know how fast the thing can go ; then the interesting part is to reimplement it cleanly, trying to reach the same performance... Is there any way to do this that is not as invasive? Maybe add new methods, fastrecv/fastsend etc. Types that don't implement them would simply use the slow methods, maintaining backwards compatibility. Well, this would certainly work, and it would be even faster. I considered doing it like this, but it is a lot more work : adding entries to the system catalogs, creating all the new functions, deciding what to do with getTypeBinaryOutputInfo (since there would be 2 variants), etc. Types that don't support the new functions would need some form of indirection to call the old functions instead, etc. In a word, doable, but kludgy, and I would need help from a system catalog expert. Also, on upgrade, information about the new functions must be inserted in the system catalogs ? (I don't know how this process works). If you want to help... The way I see COPY BINARY is that its speed should be really something massive. COPY foo FROM ... BINARY should be as fast as CREATE TABLE foo AS SELECT * FROM bar (which is extremely fast). COPY foo TO ... BINARY should be as fast as the disk allows. Why else would anyone use a binary format if it's slower than portable text ? So, there are two other ways (besides fastsend/fastrecv) that I can see : 1- The way I implemented I'm not saying it's the definitive solution : just a simple way to see how much overhead is introduced by the current API, returning BYTEAs and palloc()'ing every tuple of every row. I think this approach gave two interesting answers : - once COPY's output buffer has been made more efficient, with things like removing fwrite() for every row etc (see patch), all that remains is the API overhead, which is very significant for binary mode, since I could get massive speedups (3-4x !) by bypassing it. The table scan itself is super-fast. - however, for text mode, it is not so significant, as the speedups bypassing the API were roughly 0-20%, since most of the time is spent in datum to text conversions. Now, I don't think the hack is so ugly. It does make me feel a bit uneasy, but : - The context field in the fcinfo struct is there for a reason, so I used it. - I checked every place in the code where SendFunctionCall() appears (which are quite few actually). - The context field is never used for SendFuncs or ReceiveFuncs (it is always set to NULL) 2- Another way - palloc() could be made faster for short blocks - a generous sprinkling of inline's - a few modifications to pq_send* - a few modifications to StringInfo - bits of my previous patch in copy.c (like not fwriting every row) This would be less fast, but you'd still get a substantial speedup. As a conclusion, I think : - Alvaro's fastsend/fastrecv provides the cleanest solutin - Method 2 is the easiest, but slower - Method 1 is an intermediate, but the use of the context field is a touchy subject. Also, I will work on COPY FROM ... BINARY. I should be able to make it also much faster. This will be useful for big imports. Regards, Pierre -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
* Alvaro Herrera (alvhe...@commandprompt.com) wrote: > No, that's for the workers. The launcher needs all the entries anyway. > (I'm assuming it will be able to check visibility of tuples, correct? > Hmm, it will need to run transactions in order to do that ...) I realize I'm jumping into the middle of this with something unrelated. My apologies. A feature I've wanted a couple of times and which I know other people have asked for is being able to schedule tasks to run at certain times. Similar to cron or scheduler, but in the database instead of having to set up something outside. I would expect it to use something similar to the autovacuum launcher/worker system. I'm wondering if it would be possible to consider how other autovacuum-like systems might be added in the future when doing these changes. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] compilation with libeditpreferred is broken
I attached conservative version of patch which only reorder #define to avoid cross including half from readline and half from editline. Zdenek Tom Lane píše v čt 06. 08. 2009 v 18:13 -0400: > Zdenek Kotala writes: > > It seems to me that editline never distributed history.h file and > > HAVE_EDITLINE_HISTORY_H is nonsense. But I'm not sure. > > I wouldn't count on that, in part because there are so many versions of > editline. On an OS X machine I see > > $ ls -l /usr/include/*line* > /usr/include/editline: > total 16 > -rw-r--r-- 1 root wheel 6882 Feb 19 2008 readline.h > > /usr/include/readline: > total 16 > lrwxr-xr-x 1 root wheel 22 Jul 23 11:31 history.h@ -> > ../editline/readline.h > lrwxr-xr-x 1 root wheel 22 Jul 23 11:31 readline.h@ -> > ../editline/readline.h > > regards, tom lane diff -r dd477d7938da src/bin/psql/input.h --- a/src/bin/psql/input.h Wed Jun 03 00:38:34 2009 + +++ b/src/bin/psql/input.h Wed Aug 12 21:49:49 2009 +0200 @@ -18,17 +18,19 @@ #define USE_READLINE 1 #if defined(HAVE_READLINE_READLINE_H) #include +#if defined(HAVE_READLINE_HISTORY_H) +#include +#endif #elif defined(HAVE_EDITLINE_READLINE_H) #include +#if defined(HAVE_EDITLINE_HISTORY_H) +#include +#endif #elif defined(HAVE_READLINE_H) #include +#if defined(HAVE_HISTORY_H) +#include #endif -#if defined(HAVE_READLINE_HISTORY_H) -#include -#elif defined(HAVE_EDITLINE_HISTORY_H) -#include -#elif defined(HAVE_HISTORY_H) -#include #endif #endif -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Alvaro Herrera writes: > No, that's for the workers. The launcher needs all the entries anyway. > (I'm assuming it will be able to check visibility of tuples, correct? > Hmm, it will need to run transactions in order to do that ...) No, you don't need to be in a transaction to check visibility. flatfiles.c is already doing that without being in a transaction, in some cases. What you need a transaction for is to be able to hold AccessShareLock on the table you're scanning. For pg_database it's not possible for any schema changes (like relfilenode changes) to happen anyway. But it is possible for vacuum to try to truncate away empty end pages, which could result in a failure of a concurrent heapscan without a lock. The code in flatfiles.c is safe because it's only doing that during the startup process when nobody else could be running vacuum, but I don't suppose we can assume that for the AV launcher. I just noticed that we are depending on flatfiles.c to do a couple of things other than generate the flat files. In particular, we piggyback on its scan of pg_database to produce the initial transaction ID wrap limit value, and we rely on it to find all the databases that RelationCacheInitFileRemove needs to be called for. I had been thinking of replacing the latter bit with a directory search, but I don't see how to get rid of scanning pg_database for the wrap limit setting. So it seems like there is still going to need to be a linear scan of pg_database during system startup. Annoying ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha 1 release notes
On Wednesday 12 August 2009 19:27:06 Josh Berkus wrote: > > Can you export DocBook from that? > > Not without writing some custom perl code, no. > > Should we stick your release notes on git somewhere? I'd like to expand > the and add a couple of things. I say just take the file and edit it. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Tom Lane wrote: > Alvaro Herrera writes: > > (BTW I assume there is going to be an index on OID > > available on pg_database after the shared relcache initialization?) > > Yeah. That's not in the patch I sent last night, but the OID index has > to become nailed too in order to allow removing FindMyDatabaseByOid. > However, I'm not sure that the AV launcher could touch it --- were > you thinking that would be important? No, that's for the workers. The launcher needs all the entries anyway. (I'm assuming it will be able to check visibility of tuples, correct? Hmm, it will need to run transactions in order to do that ...) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "Hot standby"?
Robert Haas wrote: > I have also long argued that "Synchronous Replication" should really > be called "Streaming Replication". +1 Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4961: pg_standby.exe crashes with no args
On Wed, Aug 12, 2009 at 19:08, Fujii Masao wrote: > Hi, > > On Tue, Aug 11, 2009 at 3:10 AM, Magnus Hagander wrote: >> I have reproduced this. The problem is: >> (void) signal(SIGUSR1, sighandler); >> (void) signal(SIGINT, sighandler); /* deprecated, use SIGUSR1 */ >> >> >> None of these signals exist on WIN32. I think the only reason it >> compiles at all is that we bring in *some* of our signals emulation >> code, but certainly not all of it. > > SIGINT has been used in pg_standby for a while (e.g., v8.3.7). I wonder > why this problem arises only in v8.4.0. Not sure. Potentially pure luck. SIGINT has never *worked*, though, it just hasn't crashed. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] DECLARE doesn't set/reset sqlca after DECLARE cursor
Hi,
a customer of us complained a behavioural difference
between ESQL/C and ECPG. They check sqlca.sqlcode
almost everywhere in their application currently under
porting to PostgreSQL. Somewhere in their code
however there was a place where a statement error
was ignored and the error was reported when the code
reached the next DECLARE statement.
It seems that this pseudo-statement is the only one that
doesn't set/reset sqlca, I was thinking about adding that
in a way that a call to ecpg_init() gets emitted.
The attached patch implements this. The only downside
is that now DECLARE CURSOR cannot appear outside
of a function, a change in test/preproc/variable.pgc reflects
this fact. The largest part of the patch is the change in the
expected C source outcome of the regression tests. The only
test where the ecpg_log output has changed is variable.pgc.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt
*** pgsql.describe/src/interfaces/ecpg/ecpglib/exports.txt 2008-03-25 13:58:49.0 +0100
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/exports.txt 2009-08-12 18:13:11.0 +0200
*** ECPGstatus 23
*** 26,28
--- 26,29
ECPGtrans24
sqlprint 25
ECPGget_PGconn 26
+ ECPGreset_sqlca 27
diff -dcrpN pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c
*** pgsql.describe/src/interfaces/ecpg/ecpglib/misc.c 2009-08-07 13:06:28.0 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/ecpglib/misc.c 2009-08-12 18:22:35.0 +0200
*** ecpg_gettext(const char *msgid)
*** 489,491
--- 489,499
}
#endif /* ENABLE_NLS */
+
+ bool
+ ECPGreset_sqlca(int lineno, const char *connection_name)
+ {
+ struct connection *con = ecpg_get_connection(connection_name);
+
+ return ecpg_init(con, connection_name, lineno);
+ }
diff -dcrpN pgsql.describe/src/interfaces/ecpg/include/ecpglib.h pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h
*** pgsql.describe/src/interfaces/ecpg/include/ecpglib.h 2009-08-11 14:34:03.0 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/include/ecpglib.h 2009-08-12 18:21:06.0 +0200
*** bool ECPGset_desc(int, const char *, in
*** 84,89
--- 84,90
void ECPGset_noind_null(enum ECPGttype, void *);
bool ECPGis_noind_null(enum ECPGttype, void *);
bool ECPGdescribe(int, bool, const char *, const char *, ...);
+ bool ECPGreset_sqlca(int, const char *);
/* dynamic result allocation */
void ECPGfree_auto_mem(void);
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.addons 2009-08-11 14:34:03.0 +0200
--- pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.addons 2009-08-12 18:59:22.0 +0200
*** ECPG: DeclareCursorStmtDECLAREcursor_nam
*** 298,303
--- 298,306
{
struct cursor *ptr, *this;
char *cursor_marker = $2[0] == ':' ? make_str("$0") : mm_strdup($2);
+ char *command, *comment;
+
+ const char *con = connection ? connection : "NULL";
for (ptr = cur; ptr != NULL; ptr = ptr->next)
{
*** ECPG: DeclareCursorStmtDECLAREcursor_nam
*** 317,326
argsinsert = argsresult = NULL;
cur = this;
if (INFORMIX_MODE)
! $$ = cat_str(5, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), make_str("/*"), mm_strdup(this->command), make_str("*/"));
else
! $$ = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
}
ECPG: ClosePortalStmtCLOSEcursor_name block
{
--- 320,333
argsinsert = argsresult = NULL;
cur = this;
+ command = (char *)mm_alloc(sizeof("ECPGreset_sqlca(__LINE__, );") + strlen(con));
+ sprintf(command, "ECPGreset_sqlca(__LINE__, %s);", con);
+ comment = cat_str(3, make_str("/*"), mm_strdup(this->command), make_str("*/"));
+
if (INFORMIX_MODE)
! $$ = cat_str(4, adjust_informix(this->argsinsert), adjust_informix(this->argsresult), command, comment);
else
! $$ = cat2_str(command, comment);
}
ECPG: ClosePortalStmtCLOSEcursor_name block
{
diff -dcrpN pgsql.describe/src/interfaces/ecpg/preproc/ecpg.trailer pgsql.declare-reset-sqlca/src/interfaces/ecpg/preproc/ecpg.trailer
*** pgsql.describe/src/interfaces/ecpg/preproc/ecpg.t
Re: [HACKERS] [BUGS] BUG #4961: pg_standby.exe crashes with no args
Hi, On Tue, Aug 11, 2009 at 3:10 AM, Magnus Hagander wrote: > I have reproduced this. The problem is: > (void) signal(SIGUSR1, sighandler); > (void) signal(SIGINT, sighandler); /* deprecated, use SIGUSR1 */ > > > None of these signals exist on WIN32. I think the only reason it > compiles at all is that we bring in *some* of our signals emulation > code, but certainly not all of it. SIGINT has been used in pg_standby for a while (e.g., v8.3.7). I wonder why this problem arises only in v8.4.0. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Alvaro Herrera writes: > Are you going to commit the current patch? We can remove the hacks that > support autovacuum later. I was thinking that InitPostgres could be > split in two, with the first half ending just after > RelationCacheInitializePhase2. Then workers could figure out their > database names and go off on the second half; regular backends would > just call the two halves directly. That way, the launcher could use the > first half. Yeah, I intend to commit what I have after I finish cleaning up a few loose ends (just found a bug with CLOBBER_CACHE_ALWAYS), and then we can take a look at making autovacuum play with it. I envision three or four commits before flatfiles.c can disappear. > (BTW I assume there is going to be an index on OID > available on pg_database after the shared relcache initialization?) Yeah. That's not in the patch I sent last night, but the OID index has to become nailed too in order to allow removing FindMyDatabaseByOid. However, I'm not sure that the AV launcher could touch it --- were you thinking that would be important? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha 1 release notes
> Can you export DocBook from that? Not without writing some custom perl code, no. Should we stick your release notes on git somewhere? I'd like to expand the and add a couple of things. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 18:07 +0200, Andrew Dunstan wrote: > > Csaba Nagy wrote: > > On Wed, 2009-08-12 at 17:11 +0200, Andrew Dunstan wrote: > Well, the right solution would actually be NOT to use CDATA but to > replace a literal linefeed with the XML numeric escape , but I > really don't think it's necessary. Yes you're right, with the extraction too... > The extraction tools will be simple whether or not we put everything on > one line. Well I normally avoid anything containing caffeine, and today I had my first coffee of the year and 2 cokes too. I would say ignore the whole discussion of on the grounds of abnormal hyperactivity on my part... Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] postgres-r
On 08/12/2009 12:04 PM, Suno Ano wrote: can anybody tell me, is there a roadmap with regards to http://www.postgres-r.org ? I would love to see it become production-ready asap. Even a breakdown of what is left to do might be useful in case any of us want to pick at it. :-) Cheers, mark -- Mark Mielke -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] postgres-r
Hello, can anybody tell me, is there a roadmap with regards to http://www.postgres-r.org ? I would love to see it become production-ready asap. pgpy65IJnozC6.pgp Description: PGP signature
Re: [HACKERS] "Hot standby"?
Mark Mielke wrote: > To further confuse things, the "temperature" might apply to only a > particular aspect of the solution. For example, "hot swappable disk > drives" are drives that probably do sit on a shelf until they are > needed, and the "hot" aspect only implies that the server does not need > to be shut down to install the drives, and initialize them for service. :-) > > For databases, people seem to be associating "hot" with the ability to > issue read only queries. As somebody else said - under a definition of > hot that includes read-only clones, pg_dump/pg_restore could be > considered a "hot standby" strategy. > > I don't agree with that definition. For the clone to be able to perform > read-only queries does not imply "hot" nor does it imply "standby". It > implies "slave". The original poster correctly raised this concern. Agreed. I think by definition, "standby" is "standing by", meaning it isn't doing anything, so I don't see how a read-only system could be a standby. I agree "slave" is a better name. I understand that Oracle uses the terms differently but I assume their terms were chosen for historical/marketing reasons, and I am not sure following Oracle is a good idea if their terms are confusing for non-Oracle folks. This reminds me of the terminology change we made when we added point-in-time recovery. At some point we started documenting the process of "continuous archiving" separately from the process of "point-in-time recovery". I think that served us well because once pg_standby was available, we had the possibility of continuous-archiving happening without the possibility for point-in-time recovery because the standby was always current. "Continuous archive slave" might be the most accurate terminology; that seems the clearest, and people won't have to ask "what is that, exactly". The streaming WAL log feature should probably be called "streaming continuous archiving". That does allow the possibility of having a "streaming continuous archving slave". -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Csaba Nagy wrote:
On Wed, 2009-08-12 at 17:11 +0200, Andrew Dunstan wrote:
That will just make things worse. And it will break if the XML includes
any expression that contains a line break.
Then escape the expressions using CDATA or such... I'm sure it would be
possible to make sure it's one line and rely on that. That's part of
being machine readable, being able to rely on getting it at all without
too much parsing magic...
Well, the right solution would actually be NOT to use CDATA but to
replace a literal linefeed with the XML numeric escape
, but I
really don't think it's necessary.
The extraction tools will be simple whether or not we put everything on
one line.
Assuming we adopt Alvaro's suggestion of an root, here's
how it would work without putting everything on one line:
{ echo ""; sed -n
'!!,!!p' logfile ; echo
""; } > explain-plans.xml
Putting everything on one line, this becomes:
{ echo ""; sed -n '!!p' logfile ; echo
""; } > explain-plans.xml
Not very hard either way, is it?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] expanding our usage of POSIX_FADVISE
Le mercredi 12 août 2009, Greg Stark a écrit : > On Wed, Aug 12, 2009 at 3:07 PM, Cédric > > Villemain wrote: > > I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still > > innacurate for postgreSQL ? > > > > I find > > «A related problem is that the smgr uses the same FD to access the same > > relation no matter how many scans are in progress. Think about a complex > > query that is doing both a seqscan and an indexscan on the same relation > > (a self-join could easily do this). You'd really need to change this if > > you want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully. > > » (tom lane, 2003) > > I had a version of the POSIX_FADV_SEQUENTIAL patch going which set the > appropriate mode before every block read (skipping it if it was the > same mode as last set -- just like we handle lseek). I couldn't > measure any consistent improvement on sequential scans though which, > at least on Linux, already saturdate any i/o system I tested. Mileage > on other operating systems or i/o systems may vary of course. yes as stated before by Greg Smith, some OS use more or less the POSIX_FADV_* depending on their default. Linux is agresive and the POSIX_FADV_SEQUENTIAL have probably only poor benefit on it. I wonder what happen with the POSIX_FADV_RANDOM one. > > I think the real benefit of this would be avoiding polluting the > filesystem cache with blocks which we have no intention of reading. and be sure we readhead when needed, bypassing system default. > That will be a hard benefit to measure though. Especially since just > because we're doing a random i/o doesn't actually mean we won't read > nearby blocks eventually. If we're scanning an index range and the > table is actually mostly clustered then our random i/o won't be so > random after all... Probably, yes... :/ Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 17:41 +0200, Andrew Dunstan wrote: > > Csaba Nagy wrote: > > Then why you bother calling it "machine readable" at all ? Would you > > really read your auto-explain output on the DB server ? I doubt that's > > the common usage scenario, I would expect that most people would let a > > tool extract/summarize it and definitely process it somewhere else than > > on the DB machine, with the proper tool set. > Sure I would. I look at log files almost every day to find out things. > Why should I have to wade through a pile of utterly unreadable crap to > find it? I look at log files every day too (not almost, but literally every day), but I really can't imagine myself looking at an explain output directly in the log file. Even with the output of an 8.2 server which has less detail than I think the new formats have, I always copy the text from the psql prompt to some friendlier tool where I can play around with it, delete parts of it if needed for the sake of clear overview and where I can easily switch between line-wrap or not and such. I simply don't believe that a remotely human presentation of the thing worths the slightest compromise in machine readability. That said, I would like to finish this discussion here because it gets pointless, I agree to let us disagree :-) > Auto-explain lets you have *one* output format. To follow your approach, > I will have to change that, and have two log files, one machine > processable and one human readable. Triple bleah. By ad-hoc I didn't mean reading the auto-explain log, that's surely no ad-hoc operation... I would make that a mandatory daily routine which is better handled by a tool which serves you directly the plans of the worst performing queries sorted by runtime for example and highlighting the obvious planner mis-estimates. By ad-hoc I mean a query you examine on the psql command line, and there you can expect human readable of course without considerations to machine readability. > I have not suggested anything that would break the machine readability. > You seem to think that making the machine readable output remotely human > friendly is somehow going to detract from its machine processability. > But that's just silly, frankly. I do not want and should not have to > choose between a format that is machine readable and one that is to some > extent human readable. OK, if you can do that it's fine... but I'm afraid that will lead to compromises on the machine readability side and will only delay the whole thing. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
Merlin Moncure escribió: > 2009/8/12 Pierre Frédéric Caillaud : > > > > > >> If you do as much damage to the I/O function API as the other patch > >> did, it will probably be rejected. > > > > You mean, as the COPY patch in my previous message, or as another > > patch ? > > (I just search the archives and found one about CopyReadLine, but > > that's probably not what you are talking about) > > > >> We don't touch datatype APIs > >> lightly, because it affects too much code. > >> > >> regards, tom lane > > > > I definitely agree with that. > > Is there any way to do this that is not as invasive? Maybe add new methods, fastrecv/fastsend etc. Types that don't implement them would simply use the slow methods, maintaining backwards compatibility. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] surprising trigger/foreign key interaction
While working on some code I ran into a problem where some DELETE requests would get seamingly ignored after a while I managed to boil it down to: CREATE TABLE foo (a INT PRIMARY KEY, b int); CREATE TABLE bar (x int PRIMARY KEY, a int references foo(a) ON DELETE SET NULL); INSERT INTO foo VALUES (1,10); INSERT INTO bar VALUES (99,1); CREATE OR REPLACE FUNCTION bar_proc() RETURNS TRIGGER AS $$ BEGIN IF (TG_OP = 'INSERT') THEN RETURN NEW; ELSIF (TG_OP = 'UPDATE') THEN RETURN NEW; ELSIF (TG_OP = 'DELETE') THEN DELETE FROM foo WHERE a=1; RETURN OLD; END IF; RETURN OLD; END; $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE TRIGGER bar_tr BEFORE INSERT OR DELETE ON bar FOR EACH ROW EXECUTE PROCEDURE bar_proc(); DELETE FROM bar where x=99; which results in: CREATE TABLE INSERT 0 1 INSERT 0 1 CREATE FUNCTION CREATE TRIGGER DELETE 0 the "surprise" here was that the delete is getting silently surpressed even though the original Qual still holds and afaik should result in the row deleted. Is that somehow expected behaviour or a bug(or at least something that should get documented somehow)? Stefan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Tom Lane wrote: > Alvaro Herrera writes: > > Hmm, to what database would it connect? > > Well, it wouldn't. As of the patch I'm working on, it's okay to have > PGPROC entries showing zero in databaseId. Normally they'd be backends > that weren't done starting yet, but I see no reason the AV launcher > couldn't stay that way indefinitely. Makes sense. > This would likely mean that the only thing it could safely do is seqscan > pg_database, but that's all we need anyway, no? Right. > I suppose it might be a bit ugly to refactor InitPostgres enough to > support this. I'm not sure which parts of that code we'd actually need > to have run. I'll have a look at that. Are you going to commit the current patch? We can remove the hacks that support autovacuum later. I was thinking that InitPostgres could be split in two, with the first half ending just after RelationCacheInitializePhase2. Then workers could figure out their database names and go off on the second half; regular backends would just call the two halves directly. That way, the launcher could use the first half. (BTW I assume there is going to be an index on OID available on pg_database after the shared relcache initialization?) -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
2009/8/12 Pierre Frédéric Caillaud : > > >> If you do as much damage to the I/O function API as the other patch >> did, it will probably be rejected. > > You mean, as the COPY patch in my previous message, or as another > patch ? > (I just search the archives and found one about CopyReadLine, but > that's probably not what you are talking about) > >> We don't touch datatype APIs >> lightly, because it affects too much code. >> >> regards, tom lane > > I definitely agree with that. Is there any way to do this that is not as invasive? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Csaba Nagy wrote: Then why you bother calling it "machine readable" at all ? Would you really read your auto-explain output on the DB server ? I doubt that's the common usage scenario, I would expect that most people would let a tool extract/summarize it and definitely process it somewhere else than on the DB machine, with the proper tool set. Sure I would. I look at log files almost every day to find out things. Why should I have to wade through a pile of utterly unreadable crap to find it? Auto-explain lets you have *one* output format. To follow your approach, I will have to change that, and have two log files, one machine processable and one human readable. Triple bleah. I have not suggested anything that would break the machine readability. You seem to think that making the machine readable output remotely human friendly is somehow going to detract from its machine processability. But that's just silly, frankly. I do not want and should not have to choose between a format that is machine readable and one that is to some extent human readable. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 17:31 +0200, Csaba Nagy wrote: > On Wed, 2009-08-12 at 17:11 +0200, Andrew Dunstan wrote: > > That will just make things worse. And it will break if the XML includes > > any expression that contains a line break. > > Then escape the expressions using CDATA or such... I'm sure it would be > possible to make sure it's one line and rely on that. That's part of > being machine readable, being able to rely on getting it at all without > too much parsing magic... Looks like today I'm talking consistently stupid... CDATA of course won't help in avoiding line breaks, but it would be still possible to define an XML entity for that. This starts to be too complicated anyway, so probably not worth it. In any case I still think making it easy for a tool to extract/parse the information should be higher priority than human readability - because the information itself is not really human readable without external help except for simple queries, and I doubt the main use case is simple queries. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 12 Aug 2009 09:42:00 -0400 Andrew Dunstan wrote: > One thing I have noticed that we should talk about is that the > explain XML output doesn't contain the query that is being explained. > That's unfortunate - it means that any logfile processor will need to > extract the statement from the surrounding text rather than just > pulling out the XML and passing it to an XML processor. For the purposes I propose using the XML output for, not having the original query in the XML itself is almost a show stopper. I assume the original design of this feature had tools in mind that would actually connect to PostgreSQL directly with commands being issued through the tool itself. I would like to create a website where users can paste or upload just the XML output to and have enough information to do a full analysis of the query that way. Ideally this would include the raw query itself, column statistics targets and ideally even some of the more important query performance related GUC settings. I realize that could be a lot of information that may not be needed otherwise, so if its only included in the VERBOSE explain output, that would be more than fine, but just having this information available increases the usefulness of such an "outside" tool many times. Again though, at the very least I think the raw query needs to be included. -- Mike -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Alvaro Herrera writes: > Tom Lane wrote: >> Well, I meant pseudo-backend in the sense of "just like an AV worker". >> We might not want it to show in pg_stat_activity, but otherwise I think >> it'd be the same. > Hmm, to what database would it connect? Well, it wouldn't. As of the patch I'm working on, it's okay to have PGPROC entries showing zero in databaseId. Normally they'd be backends that weren't done starting yet, but I see no reason the AV launcher couldn't stay that way indefinitely. This would likely mean that the only thing it could safely do is seqscan pg_database, but that's all we need anyway, no? I suppose it might be a bit ugly to refactor InitPostgres enough to support this. I'm not sure which parts of that code we'd actually need to have run. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 17:11 +0200, Andrew Dunstan wrote: > That will just make things worse. And it will break if the XML includes > any expression that contains a line break. Then escape the expressions using CDATA or such... I'm sure it would be possible to make sure it's one line and rely on that. That's part of being machine readable, being able to rely on getting it at all without too much parsing magic... > I repeat, I want to be able to have a log file that is both machine > processable and not utterly unreadable by a human. And I do not accept > at all that this is impossible. Nor do I accept I should need some extra > processing tool to read the machine processable output without suffering > brain damage. If we were to adopt your approach I bet you would find > that nobody in their right mind would use the machine readable formats. Then why you bother calling it "machine readable" at all ? Would you really read your auto-explain output on the DB server ? I doubt that's the common usage scenario, I would expect that most people would let a tool extract/summarize it and definitely process it somewhere else than on the DB machine, with the proper tool set. For ad-hoc explain analysis which I think it's the typical use case for on-the-DB-server inspection of text-format explain output you would surely use something else than what is called "machine readable" format... Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] expanding our usage of POSIX_FADVISE
On Wed, Aug 12, 2009 at 3:07 PM, Cédric Villemain wrote: > > I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still innacurate > for postgreSQL ? > > I find > «A related problem is that the smgr uses the same FD to access the same > relation no matter how many scans are in progress. Think about a complex > query that is doing both a seqscan and an indexscan on the same relation (a > self-join could easily do this). You'd really need to change this if you > want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully. > » (tom lane, 2003) I had a version of the POSIX_FADV_SEQUENTIAL patch going which set the appropriate mode before every block read (skipping it if it was the same mode as last set -- just like we handle lseek). I couldn't measure any consistent improvement on sequential scans though which, at least on Linux, already saturdate any i/o system I tested. Mileage on other operating systems or i/o systems may vary of course. I think the real benefit of this would be avoiding polluting the filesystem cache with blocks which we have no intention of reading. That will be a hard benefit to measure though. Especially since just because we're doing a random i/o doesn't actually mean we won't read nearby blocks eventually. If we're scanning an index range and the table is actually mostly clustered then our random i/o won't be so random after all... -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Alvaro Herrera wrote: Andrew Dunstan escribió: STATEMENT: SELECT 1 AS one; LOG: duration: 0.008 ms plan: Result 0.00 0.01 1 0 I think what this says is that auto-explain should not be sending its output to the regular logfile, but somewhere else. The format you show above is not the most usable thing in the world -- not for machine parsing, and neither for human consumption. Maybe it should fill its own file with something like this: select 1 as one; 0.008 Result 0.00 0.01 1 0 or some such. With a format like this, pulling them out of the log file would be trivial, so I don't see why it couldn't go in the log file. It needs a bit of tweaking, but the idea is right. And to answer Robert's question - yes, I will be submitting a patch or two in due course. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> Is there a real downside to promoting the launcher to be a > >> pseudo-backend? > > > Aside from the fact that we don't have any pseudo-backend yet, I don't > > see any ... > > Well, I meant pseudo-backend in the sense of "just like an AV worker". > We might not want it to show in pg_stat_activity, but otherwise I think > it'd be the same. Hmm, to what database would it connect? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Andrew Dunstan escribió: > STATEMENT: SELECT 1 AS one; > LOG: duration: 0.008 ms plan: > > Result > 0.00 > 0.01 > 1 > 0 > I think what this says is that auto-explain should not be sending its output to the regular logfile, but somewhere else. The format you show above is not the most usable thing in the world -- not for machine parsing, and neither for human consumption. Maybe it should fill its own file with something like this: select 1 as one; 0.008 Result 0.00 0.01 1 0 or some such. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "Hot standby"?
Greg Stark wrote: > I'm not actually certain we can handle streaming synchronous mode to > a standby slave. Does the slave need to have connections enabled to > handle feeding wal sync status to the master? I thought there were major concerns about the interaction of those read-only queries with the replicated database transactions in synchronous mode, too. Following threads on the topic has left me a little dubious that it can be truly synchronous, in the sense that it must commit to the clone before the master returns an indication of success to the client. Are we still shooting for that or has "synchronous" become shorthand for "we'll try really hard to keep the lag minimal when we can"? > I don't see any particular reason to come up with names for each of > these combination of modes. I'm pretty happy just saying there are > three different configuration options and certain options depend on > other options so only certain combinations are legal. Sounds reasonable to me. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Csaba Nagy wrote: On Wed, 2009-08-12 at 15:42 +0200, Andrew Dunstan wrote: Have you actually looked at a logfile with this in it? A simple stylesheet won't do at all. What you get is not an XML document but a text document with little bits of XML embedded in it. So you would need a program to parse that file and either turn it into a single legal XML document or pass each piece of XML individually to your XSLT processor. Bleah. I'm pretty sure you will never find a human readable format which is easily extracted from the logs by a program. But if you format the XML in a (very human unreadable) one-line-without-breaks format then it will be a lot easier extracted by a program and formatted at your will. That will just make things worse. And it will break if the XML includes any expression that contains a line break. And all this because you pose a false dichotomy between correctness and completeness on one hand and human readability on the other. I don't accept that at all. I think we can and should improve human readability without sacrificing anything on the correctness and completeness front. In fact, that also needs improving, and we can do them both at the same time. I really really doubt that. I would go here on the UNIX approach of piping the things through the right tools, each one doing a simple and good job for it's single and well defined purpose. So let the explain spit out a line of XML without much thought about formatting but focusing on completeness, making it easy for tools to get that line, and then let the tools do the formatting depending on what you want to do with the information. Each part will be simpler than you would put in a directly human readable XML (if that's possible at all) approach, which will anyway not cover all the uses and tastes. I think we're going to have to agree to disagree on this. I think you're going in precisely the wrong direction. I repeat, I want to be able to have a log file that is both machine processable and not utterly unreadable by a human. And I do not accept at all that this is impossible. Nor do I accept I should need some extra processing tool to read the machine processable output without suffering brain damage. If we were to adopt your approach I bet you would find that nobody in their right mind would use the machine readable formats. I sure wouldn't. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 16:51 +0200, Csaba Nagy wrote: > I argue that a sufficiently complicated explain output will never be > easily navigated in a text browser, however much you would like it. If > you do a where clause with 100 nested ANDs (which occasionally happens > here), I don't think you'll be able to cleanly show that in a text > browser - it will simply not fit in no matter how you format it. But > using the right GUI tool (even a generic XML one) it would be easy to > just temporarily collapse the whole top AND and have a clean view of the > rest. Just a small note: I don't know how the machine-readable auto-explain output actually looks like, so I just assumed individual conditions will have their own XML node. But even if they don't have and the AND thing would be flat in the explain output, the same argument applies to even a few 10s of joins or sub-selects (and we do have that too). Sometimes collapsing parts of the plan would help big time in having a clean picture, which (starting with a certain complexity of the query) you will have a hard time to do in plain text regardless of the formatting. I do have plenty of explain outputs (from 8.2 postgres) which don't fit on my screen in text form even without further details I gather you can get in the machine readable form. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, 2009-08-12 at 15:42 +0200, Andrew Dunstan wrote: > Have you actually looked at a logfile with this in it? A simple > stylesheet won't do at all. What you get is not an XML document but a > text document with little bits of XML embedded in it. So you would need > a program to parse that file and either turn it into a single legal XML > document or pass each piece of XML individually to your XSLT processor. > Bleah. I'm pretty sure you will never find a human readable format which is easily extracted from the logs by a program. But if you format the XML in a (very human unreadable) one-line-without-breaks format then it will be a lot easier extracted by a program and formatted at your will. > And all this because you pose a false dichotomy between correctness and > completeness on one hand and human readability on the other. I don't > accept that at all. I think we can and should improve human readability > without sacrificing anything on the correctness and completeness front. > In fact, that also needs improving, and we can do them both at the same > time. I really really doubt that. I would go here on the UNIX approach of piping the things through the right tools, each one doing a simple and good job for it's single and well defined purpose. So let the explain spit out a line of XML without much thought about formatting but focusing on completeness, making it easy for tools to get that line, and then let the tools do the formatting depending on what you want to do with the information. Each part will be simpler than you would put in a directly human readable XML (if that's possible at all) approach, which will anyway not cover all the uses and tastes. > I want to be able to have machine readable explain output, but I also > want to be able to browse the logs without wasting more brain cells than > necessary and without having to use external tools other than by > standard text browser (less). As Pooh Bear said, "Both please!" I argue that a sufficiently complicated explain output will never be easily navigated in a text browser, however much you would like it. If you do a where clause with 100 nested ANDs (which occasionally happens here), I don't think you'll be able to cleanly show that in a text browser - it will simply not fit in no matter how you format it. But using the right GUI tool (even a generic XML one) it would be easy to just temporarily collapse the whole top AND and have a clean view of the rest. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
If you do as much damage to the I/O function API as the other patch did, it will probably be rejected. You mean, as the COPY patch in my previous message, or as another patch ? (I just search the archives and found one about CopyReadLine, but that's probably not what you are talking about) We don't touch datatype APIs lightly, because it affects too much code. regards, tom lane I definitely agree with that. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, Aug 12, 2009 at 10:01 AM, Tom Lane wrote: > Andrew Dunstan writes: >> Another design issue is this: The root node of an XML document is >> ideally a distinguished element that can't occur within itself. >> auto-explain doesn't seem to be doing this. > > Huh? I get (for "explain 2+2") > > http://www.postgresql.org/2009/explain";> > > > Result > 0.00 > 0.01 > 1 > 0 > > > He's talking about *auto* explain. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] expanding our usage of POSIX_FADVISE
Hello, I wonder if POSIX_FADV_RANDOM and POSIX_FADV_SEQUENTIAL are still innacurate for postgreSQL ? I find «A related problem is that the smgr uses the same FD to access the same relation no matter how many scans are in progress. Think about a complex query that is doing both a seqscan and an indexscan on the same relation (a self- join could easily do this). You'd really need to change this if you want POSIX_FADV_SEQUENTIAL and POSIX_FADV_RANDOM to get set usefully. » (tom lane, 2003) And also : « Surely POSIX_FADV_SEQUENTIAL is the one intended to hint seq scans, and POSIX_FADV_RANDOM to hint random access. No? ISTM, _WILLNEED seems just right for small random-access blocks. Anyway, for those who want to see what they do in Linux, http://www.gelato.unsw.edu.au/lxr/source/mm/fadvise.c Pretty scary that Bruce said it could make older linuxes dump core - there isn't a lot of code there. » (ron mayer, 2006) But that seems a bit old. Cédric Villemain Administrateur de Base de Données Cel: +33 (0)6 74 15 56 53 http://dalibo.com - http://dalibo.org signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] machine-readable explain output v4
Andrew Dunstan writes: > Another design issue is this: The root node of an XML document is > ideally a distinguished element that can't occur within itself. > auto-explain doesn't seem to be doing this. Huh? I get (for "explain 2+2") http://www.postgresql.org/2009/explain";> Result 0.00 0.01 1 0 regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
Alvaro Herrera writes: > Well, the rule here is simple too (set cinoptions=(0 if you're > Vim-enabled). It's only function prototypes that are a bit weird, and > once you understand how it works it's trivial to reproduce. Yeah. What I normally do if I'm actually trying to reproduce pgindent's handling of a prototype is: static int foo(int ..., bool ...); * temporarily break the line: static int foo(int ..., bool ...); * indent continuation lines to match (in Emacs this just means pressing tab on each line): static int foo(int ..., bool ...); * rejoin the first line static int foo(int ..., bool ...); It's a couple more keystrokes than letting Emacs do what it would like, but not exactly hard. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
On Wed, Aug 12, 2009 at 9:38 AM, Alvaro Herrera wrote: > Robert Haas escribió: >> On Wed, Aug 12, 2009 at 12:27 AM, Tom Lane wrote: > >> > Ah. That's a bit idiosyncratic to pgindent. What it does for a >> > function definition makes sense, I think: it lines up all the >> > parameters to start in the same column: > >> That is truly bizarre. +1 from me for doing something that a >> competent C programmer can figure out without a calculator. I don't >> care what the rule is particularly, as long as it's obvious how to >> follow it. (In my own code I indent all of my continuation lines by >> one additional 4-space tab-stop. I realize this would be a horrible >> idea for PG since we don't want to change anything that's going to >> reindent the entire code base, and you might all hate it for other >> reasons anyway, but the point is that any idiot can look at it and >> figure out how it's supposed to be indented, because the rule is >> simple.) > > Well, the rule here is simple too (set cinoptions=(0 if you're > Vim-enabled). It's only function prototypes that are a bit weird, and > once you understand how it works it's trivial to reproduce. Function prototypes was what I was referring to. I think I understand how to reproduce it now, but it's still bizarre. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
On Wed, Aug 12, 2009 at 9:35 AM, Tom Lane wrote: > Alvaro Herrera writes: >> Tom Lane escribió: >>> Um, sorry, no reason to do which? > >> No reason not to leave prototypes alone in the AWK code. Isn't the >> style emitted by indent good enough already? The comment that ctags >> needs it is probably outdated (I know my ctags, the Exuberant one, >> doesn't). > > Personally I quite like the fact that the function definition is the > only place where the function name appears left-justified. Modern ctags > might or might not care, but there are all sorts of code search tools > out there and we should not presume which ones developers are using. > I vote against changing it. +1000. I use git grep '^function_name' constantly. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Wed, Aug 12, 2009 at 9:42 AM, Andrew Dunstan wrote: > Csaba Nagy wrote: >> >> On Tue, 2009-08-11 at 23:58 +0200, Andrew Dunstan wrote: >> >>> >>> Well, I don't think that the fact that we are producing machine-readable >>> output means we can just ignore the human side of it. It is more than likely >>> that such output will be read by both machines and humans. Obviously, we >>> need to make sure that it meets machine processing needs first, but once we >>> have done that we should not ignore the human requirements. >>> >> >> XML is easy to transform to about anything else. I would vote for the >> XML output to only focus on machine readability and completeness, and >> then if needed provide style sheets to make it human readable. Then >> anybody could have his preferred style to look at it. If there would be >> a tool to format the XML according to some style sheet (to make it easy >> for those who don't care about XML and style sheets), I would volunteer >> to provide the XSL style sheets for different formats on request. >> >> >> > > Have you actually looked at a logfile with this in it? A simple stylesheet > won't do at all. What you get is not an XML document but a text document > with little bits of XML embedded in it. So you would need a program to parse > that file and either turn it into a single legal XML document or pass each > piece of XML individually to your XSLT processor. Bleah. > > And all this because you pose a false dichotomy between correctness and > completeness on one hand and human readability on the other. I don't accept > that at all. I think we can and should improve human readability without > sacrificing anything on the correctness and completeness front. In fact, > that also needs improving, and we can do them both at the same time. > > I want to be able to have machine readable explain output, but I also want > to be able to browse the logs without wasting more brain cells than > necessary and without having to use external tools other than by standard > text browser (less). As Pooh Bear said, "Both please!" > > One thing I have noticed that we should talk about is that the explain XML > output doesn't contain the query that is being explained. That's unfortunate > - it means that any logfile processor will need to extract the statement > from the surrounding text rather than just pulling out the XML and passing > it to an XML processor. > > Another design issue is this: The root node of an XML document is ideally a > distinguished element that can't occur within itself. auto-explain doesn't > seem to be doing this. It outputs fragments that have as the root > element rather than > > Here's a tiny fragment of my logfile with auto_explain.log_min_duration = 0 > and auto_explain.log_format = 'xml' settings: > > > STATEMENT: SELECT 1 AS one; > LOG: duration: 0.008 ms plan: > > Result > 0.00 > 0.01 > 1 > 0 > > > > But a node can appear as the decendant of a node, so this > violates the design principle I enunciated above. auto_explain sort of hooks into the middle of the main EXPLAIN machinery in a strange way. It's not comparable to a real EXPLAIN ANALYZE because, for example, it won't report_triggers(). But we could still put a container around it of some sort by modifying contrib/auto_explain. Perhaps you'd care to propose a patch? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
=?utf-8?Q?Pierre_Fr=C3=A9d=C3=A9ric_Caillau?= =?utf-8?Q?d?= writes: > I've been examining the code path for COPY FROM too, and I think it is > possible to get the same kind of speedups on COPY FROM that the patch in > the previous message did for COPY TO, that is to say perhaps 2-3x faster > in BINARY mode and 10-20% faster in TEXT mode (these figures are > ballparks, only based on very quick checks however). > The idea is to avoid most (actually, all) palloc()'ing and memcpy()'ing > for types that are pass-by-value like INT. > Is there interest in such a patch (for 8.6) ? If you do as much damage to the I/O function API as the other patch did, it will probably be rejected. We don't touch datatype APIs lightly, because it affects too much code. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
Csaba Nagy wrote: On Tue, 2009-08-11 at 23:58 +0200, Andrew Dunstan wrote: Well, I don't think that the fact that we are producing machine-readable output means we can just ignore the human side of it. It is more than likely that such output will be read by both machines and humans. Obviously, we need to make sure that it meets machine processing needs first, but once we have done that we should not ignore the human requirements. XML is easy to transform to about anything else. I would vote for the XML output to only focus on machine readability and completeness, and then if needed provide style sheets to make it human readable. Then anybody could have his preferred style to look at it. If there would be a tool to format the XML according to some style sheet (to make it easy for those who don't care about XML and style sheets), I would volunteer to provide the XSL style sheets for different formats on request. Have you actually looked at a logfile with this in it? A simple stylesheet won't do at all. What you get is not an XML document but a text document with little bits of XML embedded in it. So you would need a program to parse that file and either turn it into a single legal XML document or pass each piece of XML individually to your XSLT processor. Bleah. And all this because you pose a false dichotomy between correctness and completeness on one hand and human readability on the other. I don't accept that at all. I think we can and should improve human readability without sacrificing anything on the correctness and completeness front. In fact, that also needs improving, and we can do them both at the same time. I want to be able to have machine readable explain output, but I also want to be able to browse the logs without wasting more brain cells than necessary and without having to use external tools other than by standard text browser (less). As Pooh Bear said, "Both please!" One thing I have noticed that we should talk about is that the explain XML output doesn't contain the query that is being explained. That's unfortunate - it means that any logfile processor will need to extract the statement from the surrounding text rather than just pulling out the XML and passing it to an XML processor. Another design issue is this: The root node of an XML document is ideally a distinguished element that can't occur within itself. auto-explain doesn't seem to be doing this. It outputs fragments that have as the root element rather than Here's a tiny fragment of my logfile with auto_explain.log_min_duration = 0 and auto_explain.log_format = 'xml' settings: STATEMENT: SELECT 1 AS one; LOG: duration: 0.008 ms plan: Result 0.00 0.01 1 0 But a node can appear as the decendant of a node, so this violates the design principle I enunciated above. (Anecdote: The very first use I even made of Postgres was at the end of 2002, for an Application I designed called "30 minute App" which let people design online and generate a questionnaire application for small devices (Palm, Blackberry, PocketPC) and stored the application metadata as a piece of XML in a text field in Postgres.) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Alvaro Herrera writes: > Tom Lane wrote: >> Is there a real downside to promoting the launcher to be a >> pseudo-backend? > Aside from the fact that we don't have any pseudo-backend yet, I don't > see any ... Well, I meant pseudo-backend in the sense of "just like an AV worker". We might not want it to show in pg_stat_activity, but otherwise I think it'd be the same. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
Robert Haas escribió: > On Wed, Aug 12, 2009 at 12:27 AM, Tom Lane wrote: > > Ah. That's a bit idiosyncratic to pgindent. What it does for a > > function definition makes sense, I think: it lines up all the > > parameters to start in the same column: > That is truly bizarre. +1 from me for doing something that a > competent C programmer can figure out without a calculator. I don't > care what the rule is particularly, as long as it's obvious how to > follow it. (In my own code I indent all of my continuation lines by > one additional 4-space tab-stop. I realize this would be a horrible > idea for PG since we don't want to change anything that's going to > reindent the entire code base, and you might all hate it for other > reasons anyway, but the point is that any idiot can look at it and > figure out how it's supposed to be indented, because the rule is > simple.) Well, the rule here is simple too (set cinoptions=(0 if you're Vim-enabled). It's only function prototypes that are a bit weird, and once you understand how it works it's trivial to reproduce. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: getting rid of the pg_database flat file
Tom Lane wrote: > What was sort of in the back of my mind was to have every n'th AV worker > examine pg_database and report back to the launcher (probably through > shared memory) with an indication of the next few databases that should > be vacuumed and when. Not sure how inefficient that might be though. Hmm, probably we could do this. The one open question is how would it know what to do the first time around when there's no information set up yet, so it cannot start a worker. I guess we could hardcode to start a worker in database with Id 1, but that doesn't seem like an improvement. > Is there a real downside to promoting the launcher to be a > pseudo-backend? Aside from the fact that we don't have any pseudo-backend yet, I don't see any ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
Alvaro Herrera writes: > Tom Lane escribió: >> Um, sorry, no reason to do which? > No reason not to leave prototypes alone in the AWK code. Isn't the > style emitted by indent good enough already? The comment that ctags > needs it is probably outdated (I know my ctags, the Exuberant one, > doesn't). Personally I quite like the fact that the function definition is the only place where the function name appears left-justified. Modern ctags might or might not care, but there are all sorts of code search tools out there and we should not presume which ones developers are using. I vote against changing it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
On Wed, Aug 12, 2009 at 12:27 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Aug 11, 2009 at 10:08 PM, Tom Lane wrote: >>> If that's not it, you'd need to mention details. > >> Well, one thing I've noticed is that when a function prototype wraps >> around to the next line, you often change the number of spaces in the >> hanging indent. > > Ah. That's a bit idiosyncratic to pgindent. What it does for a > function definition makes sense, I think: it lines up all the > parameters to start in the same column: > > static int > myfunction(int foo, > int bar) > > What is not obvious is that the same amount of hanging indent is > used for the function's *prototype*, even though the first line > is not the same: > > static int myfunction(int foo, > int bar); > > ie, the indent is length of function name plus 1. I find this a bit > stupid myself and would prefer that the prototypes looked like this: > > static int myfunction(int foo, > int bar); That is truly bizarre. +1 from me for doing something that a competent C programmer can figure out without a calculator. I don't care what the rule is particularly, as long as it's obvious how to follow it. (In my own code I indent all of my continuation lines by one additional 4-space tab-stop. I realize this would be a horrible idea for PG since we don't want to change anything that's going to reindent the entire code base, and you might all hate it for other reasons anyway, but the point is that any idiot can look at it and figure out how it's supposed to be indented, because the rule is simple.) > which is what my editor will do with it if left to its own devices. > So depending on whether I had occasion to touch the prototype, you might > see it get reindented to the second style. (I know pgindent will change > it again, but I'm not sufficiently anal to override Emacs here.) Also, > I tend to make sure that there are not so many parameters declared on > the same line that it would look bad if pgindent ever starts doing > things my way, ie, if I see > > static someveryverylongtypename *somefunctionname(int foo, > int bar, int baz, int quux, int somethingelse, int more); > > I'll probably split the second line, even though there's room for it > according to pgindent's current rule. > > So, yeah, I'm being a bit anal about this and yet I'm not always > consistent. But that's what pgindent is good at fixing ... I guess all I can say on this point is it might be helpful to people if you avoid changing minor deviations from the pgindent standard unless you're pretty sure that you're matching the standard exactly. >> I am also a bit confused about the rule for indenting the variables in >> variable declarations. I get that the *, if any, is suppose to hang >> to the left of the name, so that the first character of each variable >> name lines up - and in particular, line up on a tab stop. But it's >> not obvious to me how to choose which tab stop to line everything up >> on. > > I'm not too sure either. It's the fourth tab stop for declarations at > the outer level of a function, but I've never entirely figured out what > pgindent's rule is for nested declarations. It seems to be willing to > move the target column over to some extent, but eventually it gives up > and doesn't add any more whitespace. I don't usually try very hard to > align declarations exactly where pgindent will put them, although if > it's visually ragged as-submitted sometimes I'll fix that. > > One thing I frequently *do* do is remove submitted whitespace changes > if I know that pgindent would undo them anyway, just to reduce the size > of the final diff. A case that happened at least once in this > commitfest is that somebody had taken a block of variables aligned to > the fourth tab stop: > > int foo; > int bar; > int baz; > > and realigned them to the fifth stop, because he was adding another > variable with a longer type name: > > int foo; > int bar; > int baz; > longtypename *ptr; > > Well, that looks nice, but pgindent is going to do this anyway: > > int foo; > int bar; > int baz; > longtypename *ptr; > > so I tend to revert such dead-end whitespace changes just as part of > making sure there wasn't an unintentional change of the existing lines. > (This is the sort of thing that having submitters pgindent beforehand > might avoid ...) Yeah, I probably did that, because I didn't understand the rules. > Bored yet? No. This stuff really sucks. To give you another example of why it sucks, consider that I often rebase my patch on top of your commit to see what you changed, in an attempt to learn what I should do differently next time. Of course I get a conflict every place where you've made a change, which is the point, but the ones that are whitespace-only make a big mess that makes it a lot harder
Re: [HACKERS] pgindent timing (was Re: [COMMITTERS] pgsql: Refactor NUM_cache_remove calls in error report path to a PG_TRY)
Tom Lane escribió: > Alvaro Herrera writes: > > The reason this is like this is that the indent binary modifies the > > prototype exactly like the function definition, and then the awk program > > that's used in the pipeline "pulls up" the second line: > > > # Move prototype names to the same line as return type. Useful for ctags. > > # Indent should do this, but it does not. It formats prototypes just > > # like real functions. > > > In this day and age there's probably no reason to do this. > > Um, sorry, no reason to do which? No reason not to leave prototypes alone in the AWK code. Isn't the style emitted by indent good enough already? The comment that ctags needs it is probably outdated (I know my ctags, the Exuberant one, doesn't). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] "Hot standby"?
On Tue, Aug 11, 2009 at 11:19:18PM -0400, Robert Haas wrote: > On Tue, Aug 11, 2009 at 9:44 PM, Greg Stark wrote: > > On Tue, Aug 11, 2009 at 10:13 PM, Robert Haas wrote: > >> On Tue, Aug 11, 2009 at 4:07 PM, Josh Berkus wrote: > >>> So really, the "streaming replication" patch should be called "hot > >>> standby", > >> > >> No. AIUI, hot standby means that when your primary falls over, the > >> secondary automatically promotes itself and takes over. > > > > No! This is *not* what "hot standby" means, at least not in the Oracle > > world. > > I'm perplexed by this. For example: http://en.wikipedia.org/wiki/Hot_standby > > Admittedly, wikipedia is not an authoritative source, but I've always > understood cold/warm/hot just as Peter described them upthread. Cold > means it's on the shelf. Warm means it's plugged in, but you have to > have to do something to get it going. Hot means it just takes over > when needed. After all this, perhaps we can at least conclude that calling it "cold", "warm", or "hot" anything is confusing, because no one can agree on what that means. I propose we leave off finding a naming that includes temperature. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] "Hot standby"?
On Wed, Aug 12, 2009 at 4:19 AM, Robert Haas wrote: > On Tue, Aug 11, 2009 at 9:44 PM, Greg Stark wrote: >> No! This is *not* what "hot standby" means, at least not in the Oracle world. > > I'm perplexed by this. For example: http://en.wikipedia.org/wiki/Hot_standby Well that just links to "Hot Spare" which is a term which existed long before Oracle implemented hot standby or even warm standby for that matter. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Error message for FK referencing deferrable unique/PK
While looking at this report
http://archives.postgresql.org/pgsql-bugs/2009-08/msg00083.php
I spotted another error message which could use improvement:
CREATE TABLE foo(a int PRIMARY KEY DEFERRABLE);
CREATE TABLE bar(a int REFERENCES foo(a));
ERROR: there is no unique constraint matching given keys for
referenced table "foo"
Whereas if you define the FK slightly differently so that it goes
through transformFkeyGetPrimaryKey() instead, you get a much better
error message:
CREATE TABLE bar(a int REFERENCES foo);
ERROR: cannot use a deferrable primary key for referenced table "foo"
The attached patch to transformFkeyCheckAttrs() makes the former case
generate a similar error to the latter.
- Dean
*** ./src/backend/commands/tablecmds.c.orig 2009-08-12 09:47:34.0 +0100
--- ./src/backend/commands/tablecmds.c 2009-08-12 10:08:13.0 +0100
***
*** 5117,5122
--- 5117,5123
{
Oid indexoid = InvalidOid;
bool found = false;
+ bool found_deferrable = false;
List *indexoidlist;
ListCell *indexoidscan;
***
*** 5148,5154
* expressions, too
*/
if (indexStruct->indnatts == numattrs &&
! indexStruct->indisunique && indexStruct->indimmediate &&
heap_attisnull(indexTuple, Anum_pg_index_indpred) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs))
{
--- 5149,5155
* expressions, too
*/
if (indexStruct->indnatts == numattrs &&
! indexStruct->indisunique &&
heap_attisnull(indexTuple, Anum_pg_index_indpred) &&
heap_attisnull(indexTuple, Anum_pg_index_indexprs))
{
***
*** 5198,5203
--- 5199,5219
break;
}
}
+
+ /*
+ * Refuse to use a deferrable unique/primary key. This is per
+ * SQL spec, and there would be a lot of interesting semantic
+ * problems if we tried to allow it.
+ */
+ if (found && !indexStruct->indimmediate)
+ {
+ /*
+ * Remember that we found an otherwise matching index, so
+ * that we can generate a more appropriate error message.
+ */
+ found_deferrable = true;
+ found = false;
+ }
}
ReleaseSysCache(indexTuple);
if (found)
***
*** 5205,5214
}
if (!found)
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_FOREIGN_KEY),
! errmsg("there is no unique constraint matching given keys for referenced table \"%s\"",
! RelationGetRelationName(pkrel;
list_free(indexoidlist);
--- 5221,5238
}
if (!found)
! {
! if (found_deferrable)
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
! errmsg("cannot use a deferrable unique constraint for referenced table \"%s\"",
! RelationGetRelationName(pkrel;
! else
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_FOREIGN_KEY),
! errmsg("there is no unique constraint matching given keys for referenced table \"%s\"",
! RelationGetRelationName(pkrel;
! }
list_free(indexoidlist);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4961: pg_standby.exe crashes with no args
Yeah, the patch I think breaks things isn't included in 8.3.7 - it will be in 8.3.8 though... //Magnus On Wed, Aug 12, 2009 at 11:08, wader2 wrote: > I can't compile nor read source, but can tell you that > pg_standby.exe in 8.3.7 works fine. > > Magnus Hagander wrote: >> >> On Mon, Aug 10, 2009 at 20:44, Tom Lane wrote: >>> >>> Magnus Hagander writes: If I just move those two lines into the #ifndef WIN32 block just around it, it compiles and doesn't crash on running-with-no-arguments. I haven't tried to actually use it though - can someone confirm if this will actually make pg_standby not work properly? >>> >>> It would mean there's no way to trigger failover via signal. >>> >>> I think what we need is for pg_ctl to be able to send these signals... >> >> Those signals don't *exist* on Windows. The whole idea of >> cross-process signals don't *exist* on Windows. >> >> We emulate it in the main backend, by creating a background thread >> that sets a global variable. That is then polled in the >> CHECK_FOR_INTERRUPTS macro. pg_ctl is perfectly capable of sending >> these signals, but pg_standby can't receive them. >> >> We could implement the same type of check in pg_standby, but it >> requires something like CHECK_FOR_INTERRUPTS. And these interrupts >> won't, by default, cause any kind of interruption of the process. In >> the backend, we interrupt socket calls because we have the socket >> wrapper layer, and nothing else. I don't know how doable this would be >> in pg_standby - does it always block on a single thing where we could >> stick some win32 synchronization code? If it's a single, or limited, >> places we could implement something similar to the backend. But if we >> need to interrupt at arbitrary locations, that's just not possible. >> >> > > -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #4961: pg_standby.exe crashes with no args
I can't compile nor read source, but can tell you that pg_standby.exe in 8.3.7 works fine. Magnus Hagander wrote: On Mon, Aug 10, 2009 at 20:44, Tom Lane wrote: Magnus Hagander writes: If I just move those two lines into the #ifndef WIN32 block just around it, it compiles and doesn't crash on running-with-no-arguments. I haven't tried to actually use it though - can someone confirm if this will actually make pg_standby not work properly? It would mean there's no way to trigger failover via signal. I think what we need is for pg_ctl to be able to send these signals... Those signals don't *exist* on Windows. The whole idea of cross-process signals don't *exist* on Windows. We emulate it in the main backend, by creating a background thread that sets a global variable. That is then polled in the CHECK_FOR_INTERRUPTS macro. pg_ctl is perfectly capable of sending these signals, but pg_standby can't receive them. We could implement the same type of check in pg_standby, but it requires something like CHECK_FOR_INTERRUPTS. And these interrupts won't, by default, cause any kind of interruption of the process. In the backend, we interrupt socket calls because we have the socket wrapper layer, and nothing else. I don't know how doable this would be in pg_standby - does it always block on a single thing where we could stick some win32 synchronization code? If it's a single, or limited, places we could implement something similar to the backend. But if we need to interrupt at arbitrary locations, that's just not possible. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table and Index compression
For future reference, and since this keeps appearing every few months: The license of LZO is not acceptable for inclusion or use with PostgreSQL. You need to find a different library if you want to pursue this further. Yes, I know about the license... I used LZO for tests, but since my little experiment with compression didn't give any really motivating results, I won't pursue this further. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] COPY speedup
Replying to myself... I've been examining the code path for COPY FROM too, and I think it is possible to get the same kind of speedups on COPY FROM that the patch in the previous message did for COPY TO, that is to say perhaps 2-3x faster in BINARY mode and 10-20% faster in TEXT mode (these figures are ballparks, only based on very quick checks however). The idea is to avoid most (actually, all) palloc()'ing and memcpy()'ing for types that are pass-by-value like INT. Is there interest in such a patch (for 8.6) ? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table and Index compression
On Tuesday 11 August 2009 13:05:39 Pierre Frédéric Caillaud wrote: > Well, here is the patch. I've included a README, which I paste here. > If someone wants to play with it (after the CommitFest...) feel free to > do so. > While it was an interesting thing to try, I don't think it has enough > potential to justify more effort... > > > * How to test > > - apply the patch > - copy minilzo.c and minilzo.h to > src/backend/storage/smgr For future reference, and since this keeps appearing every few months: The license of LZO is not acceptable for inclusion or use with PostgreSQL. You need to find a different library if you want to pursue this further. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] machine-readable explain output v4
On Tue, 2009-08-11 at 23:58 +0200, Andrew Dunstan wrote: > Well, I don't think that the fact that we are producing machine-readable > output means we can just ignore the human side of it. It is more than > likely that such output will be read by both machines and humans. > Obviously, we need to make sure that it meets machine processing needs > first, but once we have done that we should not ignore the human > requirements. XML is easy to transform to about anything else. I would vote for the XML output to only focus on machine readability and completeness, and then if needed provide style sheets to make it human readable. Then anybody could have his preferred style to look at it. If there would be a tool to format the XML according to some style sheet (to make it easy for those who don't care about XML and style sheets), I would volunteer to provide the XSL style sheets for different formats on request. Cheers, Csaba. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Alpha 1 release notes
On Wednesday 12 August 2009 03:34:22 Josh Berkus wrote: > On 8/11/09 3:27 PM, Peter Eisentraut wrote: > > OK, since there was no clear consensus or volunteer for preparing release > > notes for alpha 1, I have started something. Let me know what you think. > > Actually, the "consensus" was that Bruce was not going to share, so > Robert and I didn't want to bother trying to compose formal release notes. > > Can we put your text file somewhere we can collaborate on it? I started > a page on the wiki, but would gladly use your version as the seed instead: > > http://wiki.postgresql.org/wiki/85AlphaFeatures Can you export DocBook from that? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
