[HACKERS] Query plan optimization for CHECK NO INHERIT and single table?
Hey, I tried out the new CHECK NO INHERIT feature for inherited tables. There seems to be an opportunity to generate slightly better query plans sometimes. E.g. when I do SELECT * FROM base WHERE partition_id = 3 and there exists only one child table for which partition_id = 3 is true I guess the query plan could just do a seq/index/whatever scan on that table. Right now the query plan has an intermediate "Append" node. This seems only useful if the results of multiple child tables would need to be included. -Matthias -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] HEAD crashes on windows when doing VACUUM ANALYZE
Hello, when running VACUUM ANALYZE on my database built on win32-x86 from yesterday's git checkout I always get this at some point during VACUUM ANALYZE: LOG: server process (PID 5880) was terminated by exception 0xC094 DETAIL: Failed process was running: VACUUM VERBOSE ANALYZE HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes I am not sure if it's useful to report it here, but I thought I'd do it anyway :) -Matthias -- 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] Draft release notes complete
On 08/29/2012 11:20 PM, Peter Eisentraut wrote: On Wed, 2012-08-29 at 22:23 -0400, Alvaro Herrera wrote: Where are we on building the development docs more frequently? Still waiting for details on how it works to set that up on the buildfarm client. Where are we on this? Waiting on Andrew. As far as I can see, we need to update the machine to release 4.7, and then install a "skip file" or something like that. Andrew, can you please explain how is that to be used? I don't see it documented anywhere. Why does this need to be tied into the build farm? Someone can surely set up a script that just runs the docs build at every check-in, like it used to work. What's being proposed now just sounds like a lot of complication for little or no actual gain -- net loss in fact. It doesn't just build the docs. It makes the dist snapshots too. And the old script often broke badly, IIRC. The current setup doesn't install anything if the build fails, which is a distinct improvement. And you can see the causes of any failure via the buildfarm logs. But I don't really have any stake in this, I just created a tiny Module to help Magnus do what he wanted. In answer to Alvaro's question, you can disable most steps via a command line switch --skip-steps, the value of which is a space separated list of names. I haven't documented it mainly because it was really developed for use in development. The allowed values are: install make make-doc install make-contrib install install-check contrib-install-check pl-install-check isolation-check check ecpg-check 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] Draft release notes complete
On Wed, 2012-08-29 at 22:23 -0400, Alvaro Herrera wrote: > > > > Where are we on building the development docs more frequently? > > > > > > Still waiting for details on how it works to set that up on the > > > buildfarm client. > > > > Where are we on this? > > Waiting on Andrew. > > As far as I can see, we need to update the machine to release 4.7, and > then install a "skip file" or something like that. Andrew, can you > please explain how is that to be used? I don't see it documented > anywhere. Why does this need to be tied into the build farm? Someone can surely set up a script that just runs the docs build at every check-in, like it used to work. What's being proposed now just sounds like a lot of complication for little or no actual gain -- net loss in fact. -- 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] wal_buffers
On 19 February 2012 05:24, Robert Haas wrote: > I have attached tps scatterplots. The obvious conclusion appears to > be that, with only 16MB of wal_buffers, the buffer "wraps around" with > some regularity: we can't insert more WAL because the buffer we need > to use still contains WAL that hasn't yet been fsync'd, leading to > long stalls. More buffer space ameliorates the problem. Incidentally, I wondered if we could further improve group commit performance by implementing commit_delay with a WaitLatch call, and setting the latch in the event of WAL buffers wraparound (or rather, a queued wraparound request - a segment switch needs WALWriteLock, which the group commit leader holds for a relatively long time during the delay). I'm not really sure how significant a win this might be, though. There could be other types of contention, which could be considerably more significant. I'll try and take a look at it next week. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] Draft release notes complete
Excerpts from Bruce Momjian's message of mié ago 29 21:25:11 -0400 2012: > On Thu, May 31, 2012 at 05:58:58PM +0200, Magnus Hagander wrote: > > On Thu, May 31, 2012 at 5:55 PM, Bruce Momjian wrote: > > > On Tue, May 15, 2012 at 12:57:37PM -0400, Magnus Hagander wrote: > > >> On Fri, May 11, 2012 at 11:44 AM, Andrew Dunstan > > >> wrote: > > >> > If it's of any use to you I have made some adjustments to the > > >> > buildfarm code > > >> > which would let you do *just* the docs build (and dist make if you > > >> > want). It > > >> > would still pull from git, and only do anything if there's a (relevant) > > >> > change. So using that to set up a machine that would run every few > > >> > minutes > > >> > might work. Of course, building the docs can itself be fairly compute > > >> > intensive, so you still might not want to run every few minutes if > > >> > that's a > > >> > limiting factor. > > >> > > >> that would definitely be useful. Compute intensive is not really a > > >> problem, we can easily shape the box on that (and I think we already > > >> do). > > >> > > >> Do you have some details of what to do and how to do it to use that, > > >> so Stefan can set it up for us ? ;) > > > > > > Where are we on building the development docs more frequently? > > > > Still waiting for details on how it works to set that up on the > > buildfarm client. > > Where are we on this? Waiting on Andrew. As far as I can see, we need to update the machine to release 4.7, and then install a "skip file" or something like that. Andrew, can you please explain how is that to be used? I don't see it documented anywhere. Please note that we have cron jobs that run every branch (we use run_build.pl for each branch separately, not run_branches.pl) and a signle build-farm.conf. It would be good if we could continue to use a single config file for all builds, including a build that *only* does the docs. If we can have a second file that only #includes the other one somehow and just tweaks %skip_step, that would work. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Pre-alloc ListCell's optimization
Excerpts from Stephen Frost's message of mié ago 29 21:46:06 -0400 2012: > Based on the test runs that I did using Josh's box (thanks!), the > performance with the pre-allocation patch and an pre-alloc of 8 ends up > being about a wash. Allocating less (4) or more (16) actually makes > things worse. I've been playing with perf to see if I can figure out > what's going on. That hasn't been terribly productive thus far. It's a > bit frustrating. Rest assured, I'll post to the list if I'm able to > make any good headway on improving performance with this approach. FWIW I've been wondering if it would make sense to make use of Andres' embedded list stuff in some places to alleviate part of this problem. https://commitfest.postgresql.org/action/patch_view?id=859 -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Pre-alloc ListCell's optimization
Bruce, * Bruce Momjian (br...@momjian.us) wrote: > On Fri, May 18, 2012 at 06:19:52PM -0400, Stephen Frost wrote: > > Dropping it to 4 improved performance a bit: 9476 tps. > > > > Going to keep playing around and see where this goes. > > Any status on this? Based on the test runs that I did using Josh's box (thanks!), the performance with the pre-allocation patch and an pre-alloc of 8 ends up being about a wash. Allocating less (4) or more (16) actually makes things worse. I've been playing with perf to see if I can figure out what's going on. That hasn't been terribly productive thus far. It's a bit frustrating. Rest assured, I'll post to the list if I'm able to make any good headway on improving performance with this approach. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] LISTEN/NOTIFY Security and the docs
On Thu, May 24, 2012 at 01:03:18PM +0200, Magnus Hagander wrote: > On Fri, May 18, 2012 at 5:08 PM, Chander Ganesan wrote: > > Hi All, > > > > I just realized that anyone can listen for notifications (using listen) so > > long as they know the "channel" name. This means that a user could receive > > and view the payload for another user. > > > > Perhaps it would be good to note this in the documentation (i.e., there > > should be no expectation of privacy/security when using listen/notify, so > > any user that can connect to a database could issue and receive > > notifications for any channel.) > > Might be worth a note, yes. The lack of a note really should tell you > that it's a broadcast, but it wouldn't hurt to have an extra one. > > Want to prepare a patch? Attached documentation patch applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/notify.sgml b/doc/src/sgml/ref/notify.sgml new file mode 100644 index 4480706..a9405fd *** a/doc/src/sgml/ref/notify.sgml --- b/doc/src/sgml/ref/notify.sgml *** NOTIFY ch *** 33,39 with an optional payload string to each client application that has previously executed LISTEN channel !for the specified channel name in the current database. --- 33,40 with an optional payload string to each client application that has previously executed LISTEN channel !for the specified channel name in the current database. !Notifications are visible to all users. -- 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] Pre-alloc ListCell's optimization
On Fri, May 18, 2012 at 06:19:52PM -0400, Stephen Frost wrote: > * Stephen Frost (sfr...@snowman.net) wrote: > > > llist_opt: 9289 tps > > > HEAD: 9286 tps > > > > llist_opt: 9407 tps > > > > Which gives us ~1.3% improvment. > > Trying out some different options- going with 32 pre-allocated > ListCell's actually reduced performance, according to these tests. > > Dropping it to 4 improved performance a bit: 9476 tps. > > Going to keep playing around and see where this goes. Any status on this? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Draft release notes complete
On Thu, May 31, 2012 at 05:58:58PM +0200, Magnus Hagander wrote: > On Thu, May 31, 2012 at 5:55 PM, Bruce Momjian wrote: > > On Tue, May 15, 2012 at 12:57:37PM -0400, Magnus Hagander wrote: > >> On Fri, May 11, 2012 at 11:44 AM, Andrew Dunstan > >> wrote: > >> > > >> > > >> > On 05/11/2012 05:32 AM, Magnus Hagander wrote: > >> >> > >> >> > >> >> But in the interest of actually being productive - what *is* the > >> >> usecase for needing a 5 minute turnaround time? I don't buy the "check > >> >> what a patch looks like", because that should be done *before* the > >> >> commit, not after - so it's best verified by a local docs build anyway > >> >> (which will also be faster). > >> >> > >> >> I'm sure we can put something in with a pretty quick turnaround again > >> >> without too much strain on the system, but it does, as I mentioned > >> >> before, require decoupling it from the buildfarm which means it's not > >> >> just tweaking a config file. > >> > > >> > > >> > If it's of any use to you I have made some adjustments to the buildfarm > >> > code > >> > which would let you do *just* the docs build (and dist make if you > >> > want). It > >> > would still pull from git, and only do anything if there's a (relevant) > >> > change. So using that to set up a machine that would run every few > >> > minutes > >> > might work. Of course, building the docs can itself be fairly compute > >> > intensive, so you still might not want to run every few minutes if > >> > that's a > >> > limiting factor. > >> > >> that would definitely be useful. Compute intensive is not really a > >> problem, we can easily shape the box on that (and I think we already > >> do). > >> > >> Do you have some details of what to do and how to do it to use that, > >> so Stefan can set it up for us ? ;) > > > > Where are we on building the development docs more frequently? > > Still waiting for details on how it works to set that up on the > buildfarm client. Where are we on this? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] What is the current status of FOR UPDATE cursors ?
On Sun, May 6, 2012 at 10:55:54PM +0200, Hannu Krosing wrote:
> On Sun, 2012-05-06 at 13:17 -0400, Tom Lane wrote:
> > Hannu Krosing writes:
> > > What is the current status of FOR UPDATE cursors ?
> >
> > Same as it's been for a long time: FOR UPDATE [ OF table_name ]
> > works fine.
>
> Ok, then it is just documentation fix
>
> currently it is missing from \h declare and docs
>
> hannu@hvost:~/work/pg92$ TESTINST/bin/psql -p 9200 -h /tmp
> psql (9.2devel)
> Type "help" for help.
>
> hannu=# \h declare
> Command: DECLARE
> Description: define a cursor
> Syntax:
> DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
> CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
>
> hannu=#
>
> > > older docs claim that the syntax is supported, but gives an error
> > > http://www.postgresql.org/docs/8.2/static/sql-declare.html
> >
> > Old docs incorrectly said that it was FOR UPDATE [ OF column_name ]
> > but we have never implemented it that way.
What are you suggesting is missing from the docs?
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] multi-master pgbench?
> Tatsuo Ishii writes: >> I am thinking about to implement "multi-master" option for pgbench. > > Please consider using Tsung, which solves that problem and many others. > > http://tsung.erlang-projects.org/ Thank you for introducing Tsung. I have some questions regarding it. Does it support extended query? Does it support V3 protocol? -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] MySQL search query is not executing in Postgres DB
Robert Haas writes: > On the more general issue, I continue to see minimal risk of harm in > allowing things like LPAD() to implicitly cast the first argument to > text. Well, I see your point about LPAD(), but the problem is how to tell the difference between a harmless cast omission and an actual mistake that the user will be very grateful if we point out. If we allow implicit casts to text in the general case in function/operator calls, we are definitely going to re-introduce a lot of room for mistakes. Upthread you were complaining about how we'd reject calls even when there was only one possible interpretation. I wonder whether there'd be any value in taking that literally: that is, allow use of assignment rules when there is, in fact, exactly one function with the right number of parameters visible in the search path. This would solve the LPAD() problem (at least as stated), and probably many other practical cases too, since I admit your point that an awful lot of users do not use function overloading. The max() example I mentioned earlier would not get broken since there's more than one max(), and in general it seems likely that cases where there's a real risk would involve overloaded names. The main downside I can see is that code that used to work is likely to stop working as soon as someone creates a potential overloading situation. Worse, the error message could be pretty confusing, since if you had been successfully calling f(smallint) with f(42), you'd get "f(integer) does not exist", not something like "f() is ambiguous", after adding f(float8) to the mix. This seems related to the confusing changes in regression test cases that I got in my experiments yesterday. This may be sufficient reason to reject the idea, since the very last thing we need in this area is any degradation in the relevance of the error messages. > ... as long as I work for a company that helps > people migrate from other database systems, I'm not going to be able > to stop caring about this issue even in cases where I don't personally > think implicit casting is a good idea, because other people who are > not me have tens of thousands of lines of procedural code written for > those other systems and if you tell them they've got to go through and > add hundreds or thousands of casts before they can migrate, it tends > to turn them off. Maybe there's no perfect solution to that problem, > but the status quo is definitely not perfect either. Meh. I tend to think that a better solution to those folks' problem is a package of add-on casts that they could install for use with their legacy code; not dumbing down the system's error detection capability for everyone. Peter's original try at re-adding implicit text casts in that way didn't work very well IIRC, but maybe we could try harder. 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] MySQL search query is not executing in Postgres DB
On Wed, Aug 29, 2012 at 3:54 PM, Tom Lane wrote: > Peter Eisentraut writes: >> On 8/29/12 11:40 AM, Tom Lane wrote: >>> regression=# select lpad(42,8); > >> We are not seriously intending to make the above query work, are we? > > Well, *I* don't want to, but apparently Robert does. > > I don't really want to go much further than finding a way to handle the > "integer constant passed to smallint function argument" case. Maybe we > should consider a narrow fix for that rather than opening up the general > assignment-cast scenario. If we could just do that, it would be a huge improvement. I'm not very sanguine about the possibility of a clean fix in the lexer, but maybe there is some other tweak to the system that would make it work. On the more general issue, I continue to see minimal risk of harm in allowing things like LPAD() to implicitly cast the first argument to text. I wrote code that did that for years (to pad numeric fields out to a specific width by adding leading zeros) and until I upgraded to 8.3 it caused me no problems. I knew what I meant, and so did the database, and we were both happy. The argument seems to be that we shouldn't have been happy, but we were. Some of the other examples Tom mentions are, indeed, icky, and I don't know what to do about that, but LPAD() does indeed seem pretty harmless to me. And, on a more pragmatic level, as long as I work for a company that helps people migrate from other database systems, I'm not going to be able to stop caring about this issue even in cases where I don't personally think implicit casting is a good idea, because other people who are not me have tens of thousands of lines of procedural code written for those other systems and if you tell them they've got to go through and add hundreds or thousands of casts before they can migrate, it tends to turn them off. Maybe there's no perfect solution to that problem, but the status quo is definitely not perfect either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] FATAL: bogus data in lock file "postmaster.pid": ""
Applied.
---
On Wed, Aug 29, 2012 at 08:51:40AM -0400, Bruce Momjian wrote:
> On Wed, Aug 29, 2012 at 12:56:26AM -0400, Tom Lane wrote:
> > Bruce Momjian writes:
> > > On Wed, Aug 29, 2012 at 12:24:26AM -0400, Alvaro Herrera wrote:
> > >> It's a pretty strange line wrap you got in this version of the patch.
> > >> Normally we just let the string run past the 78 char limit, without
> > >> cutting it in any way. And moving the start of the string to the line
> > >> following "errhint(" looks very odd to me.
> >
> > > OK, updated patch attached.
> >
> > I agree with Alvaro's complaint that moving the whole string literal to
> > the next line isn't conformant to our usual coding style. Definitely
> > nitpicky, but why would you do it like that?
>
> I remember now why I added "\n". I am used to writing pg_upgrade output
> strings, which are obviously not sent to log files. Seems I forgot that
> distinction. As far as moving the string to the next line, I was trying
> to keep the line from getting too long.
>
> Updated patch has everyone on the same line. I am fine with nitpicky.
> Frankly, I have applied so many patches in the past few weeks, I am glad
> someone is watching.
>
> --
> Bruce Momjian http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + It's impossible for everything to be true. +
> diff --git a/src/backend/utils/init/miscinit.c
> b/src/backend/utils/init/miscinit.c
> new file mode 100644
> index 775d71f..9a0f92c
> *** a/src/backend/utils/init/miscinit.c
> --- b/src/backend/utils/init/miscinit.c
> *** CreateLockFile(const char *filename, boo
> *** 766,771
> --- 766,779
> filename)));
> close(fd);
>
> + if (len == 0)
> + {
> + ereport(FATAL,
> + (errcode(ERRCODE_LOCK_FILE_EXISTS),
> + errmsg("lock file \"%s\" is empty",
> filename),
> + errhint("Either another server is
> starting, or the lock file is the remnant of a previous server startup
> crash.")));
> + }
> +
> buffer[len] = '\0';
> encoded_pid = atoi(buffer);
>
> diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
> new file mode 100644
> index af8d8b2..81ba39e
> *** a/src/bin/pg_ctl/pg_ctl.c
> --- b/src/bin/pg_ctl/pg_ctl.c
> *** get_pgpid(void)
> *** 292,299
> }
> if (fscanf(pidf, "%ld", &pid) != 1)
> {
> ! write_stderr(_("%s: invalid data in PID file \"%s\"\n"),
> ! progname, pid_file);
> exit(1);
> }
> fclose(pidf);
> --- 292,304
> }
> if (fscanf(pidf, "%ld", &pid) != 1)
> {
> ! /* Is the file empty? */
> ! if (ftell(pidf) == 0 && feof(pidf))
> ! write_stderr(_("%s: the PID file \"%s\" is empty\n"),
> ! progname, pid_file);
> ! else
> ! write_stderr(_("%s: invalid data in PID file \"%s\"\n"),
> ! progname, pid_file);
> exit(1);
> }
> fclose(pidf);
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] splitting *_desc routines
Alvaro Herrera writes: > I looked at Andres' patch and the general idea is rather horrible: it > links all backend files into the output executable. This is so that the > *_desc functions can be used from their respective object files, which > obviously have a lot of requirements for backend infrastructure. Check. > So it is my intention to split out the desc() functions out of their > current files, into their own file, allowing xlogdump to link against > that and not be forced to link the rest of the backend. Meh. Can we compromise on one file per xlog rmgr? I don't much care for the "one big file" idea. > To solve the stringinfo problem, we would provide a separate > implementation of it, one that doesn't depend on palloc and elog. Shim on top of PQExpBuffer seems like the way to go. An alternative thing that might be worth considering before you go all in on this is whether the xlogdump functionality shouldn't just be part of the regular server executable, ie you'd call it with postgres --xlogdump and the only extra code needed is two lines for another redirect in main.c. We've already done that for things like --describe-config. This'd likely be a lot less work than getting the _desc routines to be operable standalone ... 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] MySQL search query is not executing in Postgres DB
Peter Eisentraut writes: > On 8/29/12 11:40 AM, Tom Lane wrote: >> regression=# select lpad(42,8); > We are not seriously intending to make the above query work, are we? Well, *I* don't want to, but apparently Robert does. I don't really want to go much further than finding a way to handle the "integer constant passed to smallint function argument" case. Maybe we should consider a narrow fix for that rather than opening up the general assignment-cast scenario. 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] splitting *_desc routines
So Andres Freund floated a proposal to create an xlogdump sort of tool,
in core, and posted a patch.
One advantage of having xlogdump in core is that we can have buildfarm
test that part of the Xlog code, which currently sees no testing at all.
In fact we could use it as a testbed for XLog in general, say by having
it run during "make installcheck" or some such.
I looked at Andres' patch and the general idea is rather horrible: it
links all backend files into the output executable. This is so that the
*_desc functions can be used from their respective object files, which
obviously have a lot of requirements for backend infrastructure.
However, after looking at the _desc routines themselves, they turn out
to be quite light on requirements: the only thing they really care about
is having a StringInfo around. (There are also two of them that call
relpathperm(), but I think that's easily fixable.)
(There is also a bug of sorts in gin_desc, which calls elog(PANIC) when
finding a record it doesn't know about; but all other routines simply
call appendStringInfo("unkwown") instead, so I think the right fix here
is to change gin to do the same -- and this should be backpatched all the
way back.)
So it is my intention to split out the desc() functions out of their
current files, into their own file, allowing xlogdump to link against
that and not be forced to link the rest of the backend.
To solve the stringinfo problem, we would provide a separate
implementation of it, one that doesn't depend on palloc and elog.
Initially I would just put it in src/bin/xlogdump, but if people think
we could use it elsewhere, we could do that too. Alternatively we could
use a shim layer on top of PQExpBuffer. In fact, we could change the
desc() routines, so that #ifdef FRONTEND they use PQExpBuffer, and
StringInfo otherwise.
One further decision here is whether the various desc() routines should
be each in its own file, or have them all in a single file. If we use a
single file (which is what I'm inclined to do) we'd need to rename some
static routines that have the same name, such as "out_target"; but this
seems a rather trivial exercise.
Thoughts?
--
Álvaro Herrera
--
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] MySQL search query is not executing in Postgres DB
On 8/29/12 11:40 AM, Tom Lane wrote: regression=# select lpad(42,8); >>ERROR: failed to find conversion function from integer to text >>I'm not sure why that's happening, either, but evidently some >>additional coercion laxity would required. >This, however, is a trivial problem; make_fn_arguments just didn't get >the memo that it might now need to look for assignment casts. See >attached. Meh. I'm a bit worried about whether that might have unexpected consequences, too. We are not seriously intending to make the above query work, are we? -- 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] splitting htup.h
On Wednesday, August 29, 2012 05:47:14 PM Tom Lane wrote: > Alvaro Herrera writes: > > Excerpts from Tom Lane's message of mar ago 28 17:27:51 -0400 2012: > >> Also, is there any reason to consider just moving those defs into > >> heapam.h, instead of inventing a new header? I'm not sure if there's > >> any principled distinction between heap.h and heapam.h, or any > >> significant differences between their sets of consumers. > > > > [ yeah, there's quite a few files that would need heap.h but not heapam.h > > ] > > OK, scratch that thought then. So we seem to be down to choosing a new > name for what we're going to take out of htup.h. If you don't like > heap.h, maybe something like heap_tuple.h? I'm not terribly excited > about it either way though. Any other ideas out there? htup_details.h? That doesn't have the sound of "fringe details" that htup_private.h has. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] splitting htup.h
Alvaro Herrera writes: > ... we already have catalog/heap.h, so that would be one reason to avoid > that name. Yeah, good point. We do have some duplicate file names elsewhere, but it's not a terribly good practice. 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] splitting htup.h
Alvaro Herrera writes: > Excerpts from Tom Lane's message of mar ago 28 17:27:51 -0400 2012: >> Also, is there any reason to consider just moving those defs into >> heapam.h, instead of inventing a new header? I'm not sure if there's >> any principled distinction between heap.h and heapam.h, or any >> significant differences between their sets of consumers. > [ yeah, there's quite a few files that would need heap.h but not heapam.h ] OK, scratch that thought then. So we seem to be down to choosing a new name for what we're going to take out of htup.h. If you don't like heap.h, maybe something like heap_tuple.h? I'm not terribly excited about it either way though. Any other ideas out there? 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] splitting htup.h
Excerpts from Alvaro Herrera's message of mié ago 29 11:32:04 -0400 2012: > Excerpts from Tom Lane's message of mar ago 28 17:27:51 -0400 2012: > > This should > > reduce the number of changes needed, not only in our code but third > > party code. Not sure if the new file could sanely be called > > "htup_private.h"; it seems a bit widely used for that. Maybe "heap.h"? > > Not sure I really like heap.h, but let's put that aside for a moment. ... we already have catalog/heap.h, so that would be one reason to avoid that name. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] multi-master pgbench?
Tatsuo Ishii writes: > I am thinking about to implement "multi-master" option for pgbench. Please consider using Tsung, which solves that problem and many others. http://tsung.erlang-projects.org/ Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] MySQL search query is not executing in Postgres DB
Robert Haas writes: > The upshot here appears to be that we're kind of schizophrenic about > what we want. With things like text || anyelement, anyelement || > text, and concat(variadic "any") we are basically asserting that we > want to treat anything that we don't recognize as a string. But then > we have other functions (like max and length) where we don't want that > behavior. I suppose that more than anything this is based on a > perception that || won't be ambiguous (though whether that perception > is entirely correct is debatable, given the array-related meanings of > that operator) but there might be more than one possible sense for > length() or max(). Is there any principled way of distinguishing > these cases, or even a rule for what we ought to do by hand in future > cases of this type, or is it totally arbitrary? I would not claim that the situation around || is principled in any fashion. Rather, || was identified as being a key pain point for the removal of implicit-casts-to-text during 8.3 development, and we agreed we would reduce the pain by adding operators that effectively reintroduced the implicit casts *for that one operator only*. I felt that was still a big step forward compared to implicit casts everywhere. But if we'd been doing this in a green field, I doubt that you'd see text || anyelement or anyelement || text in there. I would vote against introducing more such things in future, on the grounds that there would be no backwards-compatibility argument for it. As for the concat() function, IMO it's an ugly kluge. But again, it's not introducing any global behavior that might have side-effects on the resolution of other function or operator names. >> regression=# select lpad(42,8); >> ERROR: failed to find conversion function from integer to text >> I'm not sure why that's happening, either, but evidently some >> additional coercion laxity would required. > This, however, is a trivial problem; make_fn_arguments just didn't get > the memo that it might now need to look for assignment casts. See > attached. Meh. I'm a bit worried about whether that might have unexpected consequences, too. 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] splitting htup.h
Excerpts from Tom Lane's message of mar ago 28 17:27:51 -0400 2012: > Alvaro Herrera writes: > > This patch is mainly doing four things: > > > 1. take some typedefs and the HeapTupleData struct definition from > > access/htup.h, and put them in access/tupbasics.h. This new file is > > used as #include in all headers instead of htup.h. > > > I'm unsure about the "tupbasics.h" file name. I'm open to better ideas. > > I'd be inclined to keep the name "htup.h" for the more widely used file, > and invent a new name for what we're splitting out of it. Meh. When seen in that light, it makes a lot of sense. I was actually thinking it the other way around. > This should > reduce the number of changes needed, not only in our code but third > party code. Not sure if the new file could sanely be called > "htup_private.h"; it seems a bit widely used for that. Maybe "heap.h"? Not sure I really like heap.h, but let's put that aside for a moment. > Also, is there any reason to consider just moving those defs into > heapam.h, instead of inventing a new header? I'm not sure if there's > any principled distinction between heap.h and heapam.h, or any > significant differences between their sets of consumers. To test this idea, I created a separate "scandesc.h" file that contains struct declarations for HeapScanDesc and IndexScanDesc, and included that one into execnodes.h instead of heapam.h and genam.h. After fixing the fallout in the .c files, it turns out that heapam.h is used by 116 files (some of them through relscan.h or hio.h). Per my previous count, heap.h would be used by 182 files. So here's a list that depends on heap.h but not heapam.h (the reverse is obviously empty because heapam.h itself depends on heap.h). So I'm inclined to keep both files separate. ./access/common/heaptuple.c ./access/common/reloptions.c ./access/common/tupconvert.c ./access/common/tupdesc.c ./access/gin/gininsert.c ./access/gist/gistbuild.c ./access/heap/visibilitymap.c ./access/nbtree/nbtsort.c ./access/nbtree/nbtxlog.c ./access/spgist/spginsert.c ./access/transam/rmgr.c ./access/transam/twophase.c ./access/transam/xlog.c ./access/transam/xlogfuncs.c ./bootstrap/bootparse.c ./catalog/indexing.c ./catalog/namespace.c ./commands/variable.c ./executor/execAmi.c ./executor/execQual.c ./executor/execTuples.c ./executor/functions.c ./executor/nodeAgg.c ./executor/nodeHash.c ./executor/nodeHashjoin.c ./executor/nodeSetOp.c ./executor/nodeSubplan.c ./executor/nodeWindowAgg.c ./executor/spi.c ./executor/tstoreReceiver.c ./foreign/foreign.c ./nodes/tidbitmap.c ./optimizer/path/costsize.c ./optimizer/plan/planagg.c ./optimizer/plan/planner.c ./optimizer/plan/subselect.c ./optimizer/util/clauses.c ./parser/parse_coerce.c ./parser/parse_func.c ./parser/parse_oper.c ./parser/parse_type.c ./snowball/dict_snowball.c ./storage/freespace/freespace.c ./storage/lmgr/predicate.c ./storage/page/bufpage.c ./tcop/fastpath.c ./tcop/utility.c ./tsearch/ts_selfuncs.c ./tsearch/wparser.c ./utils/adt/acl.c ./utils/adt/arrayfuncs.c ./utils/adt/array_selfuncs.c ./utils/adt/array_typanalyze.c ./utils/adt/datetime.c ./utils/adt/format_type.c ./utils/adt/genfile.c ./utils/adt/json.c ./utils/adt/lockfuncs.c ./utils/adt/pg_locale.c ./utils/adt/pgstatfuncs.c ./utils/adt/rangetypes_selfuncs.c ./utils/adt/rowtypes.c ./utils/adt/trigfuncs.c ./utils/adt/tsgistidx.c ./utils/adt/varbit.c ./utils/adt/varchar.c ./utils/adt/varlena.c ./utils/cache/inval.c ./utils/cache/lsyscache.c ./utils/cache/syscache.c ./utils/fmgr/fmgr.c ./utils/init/miscinit.c ./utils/misc/superuser.c ./utils/sort/tuplesort.c ./utils/sort/tuplestore.c ./utils/time/combocid.c ./utils/time/tqual.c -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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 #6572: The example of SPI_execute is bogus
From: pgsql-bugs-ow...@postgresql.org [pgsql-bugs-ow...@postgresql.org] on behalf of Bruce Momjian [br...@momjian.us] Sent: Wednesday, August 29, 2012 8:46 AM To: Tom Lane Cc: Robert Haas; Hitoshi Harada; pgsql-b...@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [BUGS] BUG #6572: The example of SPI_execute is bogus On Sun, Apr 15, 2012 at 12:29:39PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada wrote: > >> On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane wrote: > >>> Given the lack of complaints since 9.0, maybe we should not fix this > >>> but just redefine the new behavior as being correct? But it seems > >>> mighty inconsistent that the tuple limit would apply if you have > >>> RETURNING but not when you don't. In any case, the ramifications > >>> are wider than one example in the SPI docs. > > >> To be honest, I was surprised when I found tcount parameter is said to > >> be applied to even INSERT. I believe people think that parameter is > >> to limit memory consumption when returning tuples thus it'd be applied > >> for only SELECT or DML with RETURNING. So I'm +1 for non-fix but > >> redefine the behavior. Who wants to limit the number of rows > >> processed inside the backend, from SPI? > > > Yeah. > > Okay, apparently nobody cares about RETURNING behaving differently from > non-RETURNING, so the consensus is to redefine the current behavior as > correct. That means what we need is to go through the docs and see what > places need to be updated (and, I guess, back-patch the changes to 9.0). > I will get to this if nobody else does, but not right away. > Would someone make the doc change outlined above? Thanks. I would like to work on this documentation bug. As per analysis I am planning to update following SPI function: 1. SPI_Execute: Here we will mention that argument count is used only for the kind of command which returns result i.e. all kind of SELECT and DML with returning clause. count is ignored for any other kind of commands. I will add one example also to indicate the difference. 2. SPI_execute_plan_with_paramlist: Here we can give just reference to SPI_execute i.e. I will mention that count has same interpretation as in SPI_execute. 3. SPI_execp: Here we can give just reference to SPI_execute i.e. I will mention that count has same interpretation as in SPI_execute. Please provide your feedback. Thanks and Regards, Kumar Rajeev Rastogi Cell No - +91 8971367787 -- 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] MySQL search query is not executing in Postgres DB
On 29/08/12 23:34, Robert Haas wrote: On Wed, Aug 29, 2012 at 12:27 AM, Tom Lane wrote: To put some concreteness into what so far has been a pretty hand-wavy discussion, I experimented with the attached patch. I'm not sure that it exactly corresponds to what you proposed, but I think this is the only place the consideration could be injected without a substantial amount of code rearrangement. Yeah, this is what I was thinking of. This results in half a dozen regression test failures (see second attachment), which mostly consist of "function/operator does not exist" errors changing to "function/operator is not unique". I've not looked into exactly why each one happens --- possibly the code is now finding multiple string-category matches where before it found none. But it definitely illustrates my point that this would not be without surprises. Well, the good news is that nothing fails that would have succeeded before, or for that matter visca versa. But after playing around with it a little, I agree that there's danger lurking. The fact that length(42) fails due to the ambiguity between length(text) and length(bpchar) is mostly happy coincidence. It's hard to get excited about the possibility of that managing to return "2". The situation with || is even worse. If I remove textanycat and anytextcat on the theory that textcat itself ought to be enough under the new rules, then a whole bunch of regression test failures occur because we end up bogusly matching the array concatenation operator somehow, and fail to interpret an unknown literal as an array (ouch!). The upshot here appears to be that we're kind of schizophrenic about what we want. With things like text || anyelement, anyelement || text, and concat(variadic "any") we are basically asserting that we want to treat anything that we don't recognize as a string. But then we have other functions (like max and length) where we don't want that behavior. I suppose that more than anything this is based on a perception that || won't be ambiguous (though whether that perception is entirely correct is debatable, given the array-related meanings of that operator) but there might be more than one possible sense for length() or max(). Is there any principled way of distinguishing these cases, or even a rule for what we ought to do by hand in future cases of this type, or is it totally arbitrary? regression=# select lpad(42,8); ERROR: failed to find conversion function from integer to text so this doesn't actually solve the problem you want to solve. I'm not sure why that's happening, either, but evidently some additional coercion laxity would required. This, however, is a trivial problem; make_fn_arguments just didn't get the memo that it might now need to look for assignment casts. See attached. You realize of course, that '42' is the answer to Life, the Universe, and Everything? :-) Cheers, Gavin
Re: [HACKERS] TRUE/FALSE vs true/false
On Sat, Aug 25, 2012 at 12:26:30PM -0400, Robert Haas wrote: > > So a patch of 1K lines would by itself represent about 2% of the typical > > inter-branch delta. Maybe that's below our threshold of pain, or maybe > > it isn't. I'd be happier about it if there were a more compelling > > argument for it, but to me it looks like extremely trivial neatnik-ism. > > I wouldn't mind a bit if we devoted 2% of our inter-branch deltas to > this sort of thing, but I've got to admit that 2% for one patch seems > a little on the high side to me. It might not be a bad idea to > establish one formulation or the other as the one to be used in all > new code, though, to avoid making the problem worse. Patch withdrawn. If we ever do a major code churn, it might be good to revisit this cleanup. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] FATAL: bogus data in lock file "postmaster.pid": ""
On Wed, Aug 29, 2012 at 12:56:26AM -0400, Tom Lane wrote:
> Bruce Momjian writes:
> > On Wed, Aug 29, 2012 at 12:24:26AM -0400, Alvaro Herrera wrote:
> >> It's a pretty strange line wrap you got in this version of the patch.
> >> Normally we just let the string run past the 78 char limit, without
> >> cutting it in any way. And moving the start of the string to the line
> >> following "errhint(" looks very odd to me.
>
> > OK, updated patch attached.
>
> I agree with Alvaro's complaint that moving the whole string literal to
> the next line isn't conformant to our usual coding style. Definitely
> nitpicky, but why would you do it like that?
I remember now why I added "\n". I am used to writing pg_upgrade output
strings, which are obviously not sent to log files. Seems I forgot that
distinction. As far as moving the string to the next line, I was trying
to keep the line from getting too long.
Updated patch has everyone on the same line. I am fine with nitpicky.
Frankly, I have applied so many patches in the past few weeks, I am glad
someone is watching.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/src/backend/utils/init/miscinit.c b/src/backend/utils/init/miscinit.c
new file mode 100644
index 775d71f..9a0f92c
*** a/src/backend/utils/init/miscinit.c
--- b/src/backend/utils/init/miscinit.c
*** CreateLockFile(const char *filename, boo
*** 766,771
--- 766,779
filename)));
close(fd);
+ if (len == 0)
+ {
+ ereport(FATAL,
+ (errcode(ERRCODE_LOCK_FILE_EXISTS),
+ errmsg("lock file \"%s\" is empty", filename),
+ errhint("Either another server is starting, or the lock file is the remnant of a previous server startup crash.")));
+ }
+
buffer[len] = '\0';
encoded_pid = atoi(buffer);
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
new file mode 100644
index af8d8b2..81ba39e
*** a/src/bin/pg_ctl/pg_ctl.c
--- b/src/bin/pg_ctl/pg_ctl.c
*** get_pgpid(void)
*** 292,299
}
if (fscanf(pidf, "%ld", &pid) != 1)
{
! write_stderr(_("%s: invalid data in PID file \"%s\"\n"),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--- 292,304
}
if (fscanf(pidf, "%ld", &pid) != 1)
{
! /* Is the file empty? */
! if (ftell(pidf) == 0 && feof(pidf))
! write_stderr(_("%s: the PID file \"%s\" is empty\n"),
! progname, pid_file);
! else
! write_stderr(_("%s: invalid data in PID file \"%s\"\n"),
! progname, pid_file);
exit(1);
}
fclose(pidf);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Doc Patch and test for Patch to compute Max LSN of Data Pages
>>> Based on the discussion and suggestions in this mail chain, following >>> features can be implemented: >>> >>> 1. To compute the value of max LSN in data pages based on user input >>> whether he wants it for an individual >>> file, a particular directory or whole database. >> >>> 2a. To search the available WAL files for the latest checkpoint record and >>> prints the value. >>> 2b. To search the available WAL files for the latest checkpoint record and >>> recreates a pg_control file pointing at >>> that checkpoint. >>> I have kept both options to address different kind of corruption scenarios. >> I think I can see all of those things being potentially useful. There >> are a couple of pending patches that will revise the WAL format >> slightly; not sure how much those are likely to interfere with any >> development you might do on (2) in the meantime. > Based on above conclusion, I have prepared a patch which implements Option-1 This mail contains doc patch Option-1 and test cases. Below are test scenarios corresponding to which testcases are in Test_find_max_lsn_from_datafiles Scenario-1: Validation of the maximum LSN number & in data base directory Steps: 1. Start the server, create table, insert some records into the table. 2. Shutdown the server in normal mode. 3. ./pg_resetxlog -P data to find the maximum LSN number and validate with the current pg_xlog directory and in pg_control file Expected behavior: Displayed maximum LSN number should to same as in pg_control file & WAL segment number displayed (fileid, segnum) should be same current file in pg_xlog directory. Scenario-2: Validation of the maximum LSN number & in specific directory Steps: 1. Start the server, create table, insert some records into the table. 2. Shutdown the server in normal mode. 3. ./pg_resetxlog -p base/1/12557/ data Expected behavior: Displayed maximum LSN number should to same as in pg_control file & WAL segment number displayed (fileid, segnum) should be same current file in pg_xlog directory. Scenario-3: Validation of the maximum LSN number & in specific file Steps: 1. Start the server, create table, insert some records into the table. 2. Shutdown the server in normal mode. 3. ./pg_resetxlog -p base/1/12557/16384 data Expected behavior: Displayed maximum LSN number should to same as in pg_control file & WAL segment number displayed (fileid, segnum) should be same current file in pg_xlog directory. With Regards, Amit Kapila. diff --git a/doc/src/sgml/ref/pg_resetxlog.sgml b/doc/src/sgml/ref/pg_resetxlog.sgml index 27b9ab41..b5c6b3c 100644 --- a/doc/src/sgml/ref/pg_resetxlog.sgml +++ b/doc/src/sgml/ref/pg_resetxlog.sgml @@ -30,6 +30,8 @@ PostgreSQL documentation -m mxid -O mxoff -l timelineid,fileid,seg + -P + -p file-name | folder-name datadir @@ -78,7 +80,7 @@ PostgreSQL documentation The -o, -x, -e, - -m, -O, + -m, -O, -P, -p, and -l options allow the next OID, next transaction ID, next transaction ID's epoch, next multitransaction ID, next multitransaction offset, and WAL @@ -135,6 +137,16 @@ PostgreSQL documentation largest entry in pg_xlog, use -l 00010032004B or higher. + + If pg_resetxlog complains that it cannot determine + valid data for pg_control, and if you do not have or corrupted + WAL segment files in the directory pg_xlog under the data directory, + then to identify larger WAL segment file from data files we can use the -P + for finding maximum LSN from the data directory or for from specific + file or folder -p file-name | folder-name. Once larger WAL segment + file is found use -l option for setting the value. + + pg_resetxlog itself looks at the files in @@ -145,6 +157,11 @@ PostgreSQL documentation entries in an offline archive; or if the contents of pg_xlog have been lost entirely. + + + -p file-name | folder-name file-name or floder-name + should be absolute path, or relative from data directory. + -- Test case 1 drop table if exists tbl; create table tbl(f1 int, f2 varchar(10), f3 float, f4 char(200)); insert into tbl values(1,'postgres',2.1,'test'); insert into tbl values(3,'bangalore',4.1,'test'); insert into tbl values(2,'kiran',3.1,'test'); insert into tbl values(4,'india',5.1,'test'); checkpoint; -- stop the server -- run the following command pg_resetxlog -P data -- Validate the LSN number and WAL fileid, segid currently running in pg_xlog folder. -- start the server and execute the following drop table tbl; -- Test case 2 drop table if exists tbl; create table tbl(f1 int, f2 varchar(10), f3 float, f4 char(200)); insert into tbl values(1,'postgres',2.1,'test'); insert into tbl values(3,'bangalore',4.1,'test'); insert into tbl values(2,'kiran',3.1,'test'); insert into tbl values(4,'india',5.1,'test'); checkpoint; -- stop the
Re: [HACKERS] MySQL search query is not executing in Postgres DB
On Wed, Aug 29, 2012 at 12:27 AM, Tom Lane wrote: > To put some concreteness into what so far has been a pretty hand-wavy > discussion, I experimented with the attached patch. I'm not sure that > it exactly corresponds to what you proposed, but I think this is the > only place the consideration could be injected without a substantial > amount of code rearrangement. Yeah, this is what I was thinking of. > This results in half a dozen regression > test failures (see second attachment), which mostly consist of > "function/operator does not exist" errors changing to "function/operator > is not unique". I've not looked into exactly why each one happens --- > possibly the code is now finding multiple string-category matches where > before it found none. But it definitely illustrates my point that this > would not be without surprises. Well, the good news is that nothing fails that would have succeeded before, or for that matter visca versa. But after playing around with it a little, I agree that there's danger lurking. The fact that length(42) fails due to the ambiguity between length(text) and length(bpchar) is mostly happy coincidence. It's hard to get excited about the possibility of that managing to return "2". The situation with || is even worse. If I remove textanycat and anytextcat on the theory that textcat itself ought to be enough under the new rules, then a whole bunch of regression test failures occur because we end up bogusly matching the array concatenation operator somehow, and fail to interpret an unknown literal as an array (ouch!). The upshot here appears to be that we're kind of schizophrenic about what we want. With things like text || anyelement, anyelement || text, and concat(variadic "any") we are basically asserting that we want to treat anything that we don't recognize as a string. But then we have other functions (like max and length) where we don't want that behavior. I suppose that more than anything this is based on a perception that || won't be ambiguous (though whether that perception is entirely correct is debatable, given the array-related meanings of that operator) but there might be more than one possible sense for length() or max(). Is there any principled way of distinguishing these cases, or even a rule for what we ought to do by hand in future cases of this type, or is it totally arbitrary? > regression=# select lpad(42,8); > ERROR: failed to find conversion function from integer to text > > so this doesn't actually solve the problem you want to solve. > I'm not sure why that's happening, either, but evidently some > additional coercion laxity would required. This, however, is a trivial problem; make_fn_arguments just didn't get the memo that it might now need to look for assignment casts. See attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company assignment-casting.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] wal_buffers
On Tuesday, August 28, 2012 9:33 PM Bruce Momjian wrote: On Tue, Aug 28, 2012 at 09:40:33AM +0530, Amit Kapila wrote: > From: pgsql-hackers-ow...@postgresql.org > [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Bruce Momjian > >>> Added to TODO: > >>> Allow reporting of stalls due to wal_buffer wrap-around > >>> >>> http://archives.postgresql.org/pgsql-hackers/2012-02/msg00826.php > >> Isn't this indicates that while writing XLOG, it needs some tuning such that >> when some thresh hold buffers(2/3) are full, then trigger LOGWriter. > I assumed the LOGWriter was already working as fast as it could, but > couldn't keep up. I have doubt that in some cases it might not, for example 1. Assume 16M Xlog buffers 2. 6M or less than that is filled. 3. Background writer decides how much to flush and starts writing and at the same time backends start filling remaining 10M of the buffers. 4. Background writer goes to sleep after flushing 6M. 5. Backends have filled all 16M, in this case it may so happen that some backends might need to do I/O. Some other cases where I think it can be useful to wake LogWriter Case-1 --- 1. Log writer delay is default 200ms or set to some higher value by user. 2. All the configured buffers got filled, backend might need to do I/O. Case-2 --- The case-1 scenario can also happen even if user has set wal_buffers = -1 (Auto tuning of wal buffers), Because it reserves XLog buffers equivalent to one segment file and in high load if that gets filled, backends might need to do I/O. --- > > On Sun, Feb 19, 2012 at 12:24:12AM -0500, Robert Haas wrote: > > Just for kicks, I ran two 30-minute pgbench tests at scale factor 300 > > tonight on Nate Boley's machine, with -n -l -c 32 -j 32. The > > configurations were identical, except that on one of them, I set > > wal_buffers=64MB. It seemed to make quite a lot of difference: > > > > wal_buffers not set (thus, 16MB): > > tps = 3162.594605 (including connections establishing) > > > > wal_buffers=64MB: > > tps = 6164.194625 (including connections establishing) > > > > Rest of config: shared_buffers = 8GB, maintenance_work_mem = 1GB, > > synchronous_commit = off, checkpoint_segments = 300, > > checkpoint_timeout = 15min, checkpoint_completion_target = 0.9, > > wal_writer_delay = 20ms > > > > I have attached tps scatterplots. The obvious conclusion appears to > > be that, with only 16MB of wal_buffers, the buffer "wraps around" with > > some regularity: we can't insert more WAL because the buffer we need > > to use still contains WAL that hasn't yet been fsync'd, leading to > > long stalls. More buffer space ameliorates the problem. This is not > > very surprising, when you think about it: it's clear that the peak tps > > rate approaches 18k/s on these tests; right after a checkpoint, every > > update will force a full page write - that is, a WAL record > 8kB. So > > we'll fill up a 16MB WAL segment in about a tenth of a second. That > > doesn't leave much breathing room. I think we might want to consider > > adjusting our auto-tuning formula for wal_buffers to allow for a > > higher cap, although this is obviously not enough data to draw any > > firm conclusions. > > > > -- > > Robert Haas > > EnterpriseDB: http://www.enterprisedb.com > > The Enterprise PostgreSQL Company > > > > > > > -- > > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-hackers > > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > With Regards, Amit Kapila. -- 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] MySQL search query is not executing in Postgres DB
On Tue, Aug 28, 2012 at 11:23 PM, Tom Lane wrote: > That argument would hold water if we got rid of every single usage of > overloading in the system-defined operators/functions, which as you well > know is not an attractive idea. Since that's not going to happen, > arguing for this on the basis that your customers don't overload > function names is missing the point. Any loosening of the rules is > going to create issues for system-function resolution ... unless you're > going to propose that we somehow do this differently for user and system > defined functions. Obviously not. > An example of the sort of problem that I don't want to hear about > ever again is somebody trying to use max() on a "point" column. > We don't have linear sort ordering for points, so this is nonsensical > and should draw an error. Which it does, today. Much as I hate to say it, I have to admit I find this to be a compelling argument. > Really? You've not had experience with very many programming languages, > then. Just about every one I've ever dealt with that's at a higher > conceptual level than C or BASIC *is* sticky about this sort of thing. In terms of type-strictness, it runs the gamut. You have things like Perl where datatypes barely exist at all and silent (sometimes confusing) conversions are performed nary a second thought, and at the other end of the spectrum you have things like ML which are incredibly fanatic about type-checking. But both Perl and ML and, as far as I know, most of what's in between make a virtue of terseness. The exceptions are things like Ada and Cobol, which are not IMHO the sorts of thing we ought to be trying to emulate. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [v9.3] writable foreign tables
2012/8/28 David Fetter : > On Tue, Aug 28, 2012 at 06:08:59PM +0200, Kohei KaiGai wrote: >> 2012/8/28 David Fetter : >> > On Tue, Aug 28, 2012 at 05:18:34PM +0200, Kohei KaiGai wrote: >> >> 2012/8/28 David Fetter : >> >> > On Tue, Aug 28, 2012 at 10:58:25AM -0400, Tom Lane wrote: >> >> >> Kohei KaiGai writes: >> >> >> > It seems to me TargetEntry of the parse tree can inform us >> >> >> > which column should be modified on UPDATE or INSERT. If it has >> >> >> > just a Var element that reference original table as-is, it >> >> >> > means here is no change. >> >> >> >> >> >> Only if you're not going to support BEFORE triggers modifying the >> >> >> row... >> >> > >> >> > +1 for supporting these. >> > >> > Generated identifiers and whole-row matching are two ways to approach >> > this. There are likely others, especially in cases where people have >> > special knowledge of the remote source. >> > >> One major problem is how to carry the generated identifiers on run-time, >> even though we have no slot except for system and regular columns >> defined in TupleDesc of the target foreign tables. >> It may need a feature to expand TupleDesc on demand. > > Could be. You know a lot more about the implementation details than I do. > >> Of course, I don't deny the benefit of trigger support on foreign-tables. >> Both writable-feature and trigger-support can be supported simultaneously. > > Do you see these as independent features, or is there some essential > overlap? > If we stand on the viewpoint that foreign-tables should perform as if regular tables, I don't think its writer feature should depend on trigger stuff. They can work independently. On the other hand, trigger feature gives users flexibility to control the data to be written, as if regular tables. We shouldn't miss the point. At least, I don't think we have some technical differences to support row-level triggers on foreign tables. Thanks, -- KaiGai Kohei -- 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] [v9.3] writable foreign tables
2012/8/28 Kohei KaiGai : > 2012/8/28 Tom Lane : >> Kohei KaiGai writes: Would it be too invasive to introduce a new pointer in TupleTableSlot that is NULL for anything but virtual tuples from foreign tables? >> >>> I'm not certain whether the duration of TupleTableSlot is enough to >>> carry a private datum between scan and modify stage. >> >> It's not. >> >>> Is it possible to utilize ctid field to move a private pointer? >> >> UPDATEs and DELETEs do not rely on the ctid field of tuples to carry the >> TID from scan to modify --- in fact, most of the time what the modify >> step is going to get is a "virtual" TupleTableSlot that hasn't even >> *got* a physical CTID field. >> >> Instead, the planner arranges for the TID to be carried up as an >> explicit resjunk column named ctid. (Currently this is done in >> rewriteTargetListUD(), but see also preptlist.c which does some related >> things for SELECT FOR UPDATE.) >> >> I'm inclined to think that what we need here is for FDWs to be able to >> modify the details of that behavior, at least to the extent of being >> able to specify a different data type than TID for the row >> identification column. >> > Hmm. It seems to me a straight-forward solution rather than ab-use > of ctid system column. Probably, cstring data type is more suitable > to carry a private datum between scan and modify stage. > > One problem I noticed is how FDW driver returns an extra field that > is in neither system nor regular column. > Number of columns and its data type are defined with TupleDesc of > the target foreign-table, so we also need a feature to extend it on > run-time. For example, FDW driver may have to be able to extend > a "virtual" column with cstring data type, even though the target > foreign table does not have such a column. > I tried to investigate the related routines. TupleDesc of TupleTableSlot associated with ForeignScanState is initialized at ExecInitForeignScan as literal. ExecAssignScanType assigns TupleDesc of the target foreign- table on tts_tupleDescriptor, "as-is". It is the reason why IterateForeignScan cannot return a private datum except for the columns being declared as regular ones. Confrontation between ForeignScan and SubqueryScan tell us the point to be extended. It assigns TupleDesc of the subplan generated at run-time. It seems to me ForeignScan will be able to adopt similar idea; that allows to append "pseudo-column" onto TupleDesc to carry identifier of remote-rows to be updated / deleted, if FDW driver can control TupleDesc being set, instead of the one come from relation's definition as-is. Any comment please. Thanks, -- KaiGai Kohei -- 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] SP-GiST micro-optimizations
On 28.08.2012 22:50, Ants Aasma wrote:
On Tue, Aug 28, 2012 at 9:42 PM, Tom Lane wrote:
Seems like that's down to the CPU not doing "rep stosq" particularly
quickly, which might well be chip-specific.
AMD optimization manual[1] states the following:
For repeat counts of less than 4k, expand REP string instructions
into equivalent sequences of simple
AMD64 instructions.
Intel optimization manual[2] doesn't provide equivalent guidelines,
but the graph associated with string instructions states about 30
cycles of startup latency. The mov based code on the other hand
executes in 6 cycles and can easily overlap with other non-store
instructions.
[1] http://support.amd.com/us/Processor_TechDocs/25112.PDF
[2]
http://www.intel.com/content/dam/doc/manual/64-ia-32-architectures-optimization-manual.pdf
Hmm, sounds like gcc just isn't doing a very good job then. I also tried
replacing the memset with variable initialization: "spgChooseOut out = {
0 }" (and I moved that to where the memset was). In that case, gcc
produced the same (fast) sequence of movq's I got with
-mstringop=unrolled_loop.
Out of curiosity, I also tried this on clang. It produced this,
regardless of whether I used MemSet or memset or variable initializer:
pxor%xmm0, %xmm0
.loc1 2040 4# spgdoinsert.c:2040:4
movaps %xmm0, -1280(%rbp)
movaps %xmm0, -1296(%rbp)
movaps %xmm0, -1312(%rbp)
So, it's using movaps to clear it in 16-byte chunks. perf annotate shows
that that's comparable in speed to the gcc's code produced for MemSet.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
