[HACKERS] improve plpgsql's EXECUTE 'select into' message with a hint
Hi, while TFM says that we can use EXECUTE 'select ' INTO instead of the non implemented EXECUTE 'select ... into ', the message in plpgsql fails to say the same thing... seems like a HINT to me -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 Index: src/pl/plpgsql/src/pl_exec.c === RCS file: /home/postgres/pg_repo/pgsql/src/pl/plpgsql/src/pl_exec.c,v retrieving revision 1.257 diff -r1.257 pl_exec.c 3036c3036,3037 errmsg(EXECUTE of SELECT ... INTO is not implemented))); --- errmsg(EXECUTE of SELECT ... INTO is not implemented), errhint(You may want to use EXECUTE ... INTO instead.))); -- 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] standbycheck was:(Re: [HACKERS] testing hot standby
On Wed, Apr 14, 2010 at 9:16 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Apr 10, 2010 at 12:23 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Fri, Apr 9, 2010 at 3:39 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: i think make standbycheck needs a little more work, why it isn't accesible from top of source dir? what i want to do. i started to make this, this weekend 3) it should execute the existing set of tests (the ones installcheck execute) but with a new set of expected results, that way we can be sure that what should be disallowed is disallowed and that the database is returning consistent values. i've thought about having expected/normal (or expected/primary) and expected/standby and check actual results against the appropiate one depending if we use installcheck and standbycheck the real question here is how pg_regress.c should know that it should compare against expected/primary or expected/standby? i mean, could i add an --standby option (my preferred) to pg_regress.c or should i try to guess it from current options and/or asking to the server? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing HS/SR - 1 vs 2 performance
On Mon, Apr 26, 2010 at 3:25 AM, Erik Rijkers e...@xs4all.nl wrote: FWIW, here are some more results from pgbench comparing primary and standby (both with Simon's patch). Was there a difference in CPU utilization between the primary and standby? 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] testing HS/SR - 1 vs 2 performance
On Sun, 2010-04-25 at 19:18 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: [ v2 patch ] I've been studying this some more while making notes for improved comments, and I've about come to the conclusion that having readers move the tail pointer (at the end of KnownAssignedXidsGetAndSetXmin) is overly tricky and probably not a performance improvement anyway. The code is in fact wrong as it stands: it's off-by-one about setting the new tail value. And there's potential for contention with multiple readers all wanting to move the tail pointer at once. OK, since contention was my concern, I want to avoid that. And most importantly, KnownAssignedXidsSearch can't move the tail pointer so we might expend many inefficient searches while never moving the tail pointer. I think we should get rid of that and just have the two functions that can mark entries invalid (which they must do with exclusive lock) advance the tail pointer when they invalidate the current tail element. OK Then we have the very simple rule that only the startup process ever changes this data structure. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
Jaime Casanova wrote: On Wed, Apr 14, 2010 at 9:16 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: 3) it should execute the existing set of tests (the ones installcheck execute) but with a new set of expected results, that way we can be sure that what should be disallowed is disallowed and that the database is returning consistent values. i've thought about having expected/normal (or expected/primary) and expected/standby and check actual results against the appropiate one depending if we use installcheck and standbycheck the real question here is how pg_regress.c should know that it should compare against expected/primary or expected/standby? i mean, could i add an --standby option (my preferred) to pg_regress.c or should i try to guess it from current options and/or asking to the server? How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing HS/SR - 1 vs 2 performance
On Sun, 2010-04-25 at 23:52 +0200, Erik Rijkers wrote: I'll try to repeat this pattern on other hardware; although if my tests were run with faulty hardware I wouldn't know how/why that would give the above effect (such a 'regular aberration'). testing is more difficult than I thought... Thanks again for your help. Please can you confirm: * Are the standby tests run while the primary is completely quiet? * What OS is this? Can we use dtrace scripts? Can anyone else confirm these test results: large scale factor and small number of sessions? -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] standbycheck was:(Re: [HACKERS] testing hot standby
On Mon, Apr 26, 2010 at 2:32 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: How many of the tests in the regular regression suite do anything useful when run against a standby server? They all have to set up a bunch of objects before they run queries, so you just get a lot of errors complaining that you can't do X in standby mode, followed by errors about missing objects. That doesn't sound very useful. granted. what i'm looking for is a way of continually see that the standby will return consistent values and yes, i want to be sure that we disallow everything that we need to... maybe just a new set of tests? maybe i just should make the hs_* tests use regression's database tables intead of the ones it is using? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] recovery_connections cannot start
Tom Lane t...@sss.pgh.pa.us writes: The only workable alternative I can see to keeping archive_mode is to tell people to set archive_command to something like /usr/bin/true ... which is not simpler, especially not on Windows. Would it be possible to have internal commands there, as for example cd is in my shell, or test, or time, or some more ? That would allow for providing a portable /usr/bin/true command as far as archiving is concerned (say, pg_archive_bypass), and will allow for providing a default archiving command in the future, like pg_archive_cp /location or something. Regards, -- dim -- 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] CIText and pattern_ops
Rod Taylor p...@rbt.ca wrote: Is there any particular reason why the citext module doesn't have citext_pattern_ops operator family? Specifically, I wish to index for this type of query: ... WHERE citext_column LIKE 'Foo%'; I think it is a reasonable suggestion. =# \d tbl Table public.tbl Column | Type | Modifiers ++--- t | text | c | citext | Indexes: tbl_c_idx btree (c) tbl_t_idx btree (t) =# SET enable_seqscan = off; SET =# EXPLAIN SELECT * FROM tbl WHERE t LIKE 'abc%'; QUERY PLAN -- Index Scan using tbl_t_idx on tbl (cost=0.00..8.27 rows=1 width=64) Index Cond: ((t = 'abc'::text) AND (t 'abd'::text)) Filter: (t ~~ 'abc%'::text) (3 rows) =# EXPLAIN SELECT * FROM tbl WHERE c LIKE 'abc%'; QUERY PLAN Seq Scan on tbl (cost=100.00..101.01 rows=1 width=64) Filter: (c ~~ 'abc%'::citext) (2 rows) Regards, --- Takahiro Itagaki 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] recovery_connections cannot start
On Mon, 2010-04-26 at 10:41 +0200, Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: The only workable alternative I can see to keeping archive_mode is to tell people to set archive_command to something like /usr/bin/true ... which is not simpler, especially not on Windows. Would it be possible to have internal commands there, as for example cd is in my shell, or test, or time, or some more ? That would allow for providing a portable /usr/bin/true command as far as archiving is concerned (say, pg_archive_bypass), and will allow for providing a default archiving command in the future, like pg_archive_cp /location or something. I think making a special case here is OK. If command string == 'true' then we don't bother to call system(3) at all, we just assume it worked fine. That way we have a simple route on all platforms. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] including PID or backend ID in relpath of temp rels
On Sun, Apr 25, 2010 at 10:19 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sun, Apr 25, 2010 at 8:07 PM, Robert Haas robertmh...@gmail.com wrote: 1. We could move the responsibility for removing the files associated with temp rels from the background writer to the owning backend. I think the reason why we initially truncate the files and only later remove them is because somebody else might have 'em open, so it mightn't be necessary for temp rels. what happens if the backend crash and obviously doesn't remove the file associated with temp rels? Currently, they just get orphaned. As I understand it, if the catalog entry survives the crash, autovacuum will remove them 2 BILLION transactions later (and emit warning messages in the meantime); otherwise we won't even know they're there. As I further understand it, the main point of this change is that if temporary tables have a distinctive name of some kind, then when we can run through the directory and blow away files with those names without fearing that it's *permanent* table data that somehow got orphaned. ...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] CIText and pattern_ops
On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor p...@rbt.ca wrote: Is there any particular reason why the citext module doesn't have citext_pattern_ops operator family? You forgot to send in 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Fri, Apr 23, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Well, I think the real hole is that turning archive_mode=on results in
WAL never being deleted unless it's successfully archived.
Hm, good point. And at least in principle you could have SR setups
that don't care about having a backing WAL archive.
But we might be able to handle that like this:
wal_mode={standby|archive|crash} # or whatever
wal_segments_always=integer # keep this many segments always, for
SR - like current wal_keep_segments
wal_segments_unarchived=integer # keep this many unarchived
segments, -1 for infinite
max_wal_senders=integer # same as now
archive_command=string # same as now
So we always retain wal_segments_always segments, but if we have
trouble with archiving we'll retain up to wal_segments_archived.
And when that limit is reached, what happens? Panic shutdown?
Silently drop unarchived data? Neither one sounds very good.
Silently drop unarchived data. I agree that isn't very good, but
think about it this way: if archive_command is failing, then our log
shipping slave is not going to work. But letting the disk fill up on
the primary does not make it any better. It just makes the primary
stop working, too. Obviously, all of this stuff needs to be monitored
or you're playing with fire, but I don't think having a safety valve
on the primary is a stupid idea.
...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] recovery_connections cannot start
On Mon, Apr 26, 2010 at 6:08 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, 2010-04-26 at 10:41 +0200, Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: The only workable alternative I can see to keeping archive_mode is to tell people to set archive_command to something like /usr/bin/true ... which is not simpler, especially not on Windows. Would it be possible to have internal commands there, as for example cd is in my shell, or test, or time, or some more ? That would allow for providing a portable /usr/bin/true command as far as archiving is concerned (say, pg_archive_bypass), and will allow for providing a default archiving command in the future, like pg_archive_cp /location or something. I think making a special case here is OK. If command string == 'true' then we don't bother to call system(3) at all, we just assume it worked fine. That way we have a simple route on all platforms. Separating wal_mode and archive_mode, as we recently discussed, might eliminate the need for this kludge, if archive_mode can then be made changeable without a restart. ...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] Re: don't allow walsender to consume superuser_reserved_connection slots, or during shutdown
On Wed, Apr 21, 2010 at 9:11 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 21, 2010 at 1:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: ...shouldn't we move the tests, plural, rather than just the one? It seems right to reject new SR connections during shutdown. Yeah; you'd also need to adjust both of them to consider am_walsender. (IOW, we want to treat SR connections as non-superuser for both tests.) [ subject changed, recipient list trimmed ] Here's the fine patch. The actual code changes are simple and seem to work as expected, but I struggled a bit with the phrasing of the messages. Feel free to suggest improvements. Also, I wasn't sure if there was somewhere in the documentation where we discussed the restriction that only superusers can connect during shutdown. If there is such a place, we should update that, too. I have committed this as-is. We can further change the error messages if we like, but there didn't seem to be a clear consensus on any particular change from what I have here. ...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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Tom Lane wrote: Personally I agree with your objection to crash but not with the objection to standby. Maybe this would be appropriate: wal_mode = minimal | archive | hot_standby Ok, here's a patch implementing this proposal. It adds a new wal_mode setting, leaving archive_mode as it is. If you try to enable archive_mode when wal_mode is 'minimal', you get a warning and archive_mode is silently ignored. Likewise streaming replication connections are not allowed if wal_mode is 'minimal'. recovery_connections now does nothing in the master. A bit more bikeshedding before I commit this: * Should an invalid combination throw an ERROR and refuse to start, instead of just warning? * How about naming the parameter wal_level instead of wal_mode? That would better convey that the higher levels add stuff on top of the lower levels, instead of having different modes that are somehow mutually exclusive. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index eb5765a..6c6a504 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -689,8 +689,7 @@ archive_command = 'test ! -f /mnt/server/archivedir/%f amp;amp; cp %p /mnt/ser /para para -When varnamearchive_mode/ is literaloff/ and xref -linkend=guc-max-wal-senders is zero some SQL commands +When varnamewal_mode/ is literalminimal/ some SQL commands are optimized to avoid WAL logging, as described in xref linkend=populate-pitr. If archiving or streaming replication were turned on during execution of one of these statements, WAL would not diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index c5692ba..63ca749 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -1353,6 +1353,43 @@ SET ENABLE_SEQSCAN TO OFF; titleSettings/title variablelist + varlistentry id=guc-wal-mode xreflabel=wal_mode + termvarnamewal_mode/varname (typeenum/type)/term + indexterm + primaryvarnamewal_mode/ configuration parameter/primary + /indexterm + listitem + para +varnamewal_mode/ determines how much information is written +to the WAL. The default value is literalminimal/, which writes +only minimal information needed to recover from a crash or immediate +shutdown. literalarchive/ adds logging required for WAL archiving, +and literalhot_standby/ further adds extra information about +running transactions required to run read-only queries on a standby +server. +This parameter can only be set at server start. + /para + para +In literalminimal/ mode, WAL-logging of some bulk operations, like +commandCREATE INDEX/, commandCLUSTER/ and commandCOPY/ on +a table that was created or truncated in the same transaction can be +safely skipped, which can make those operations much faster, but +minimal WAL does not contain enough information to reconstruct the +data from a base backup and the WAL logs, so at least +literalarchive/ level must be used to enable WAL archiving +(xref linkend=guc-archive-mode) and streaming replication. See +also xref linkend=populate-pitr. + /para + para +In literalhot_standby/ mode, the same information is logged as +in literalarchive/ mode, plus information needed to reconstruct +the status of running transactions from the WAL. To enable read-only +queries on a standby server, varnamewal_mode/ must be set to +literalhot_standby/ on the primary. + /para + /listitem + /varlistentry + varlistentry id=guc-fsync xreflabel=fsync indexterm primaryvarnamefsync/ configuration parameter/primary @@ -1726,7 +1763,9 @@ SET ENABLE_SEQSCAN TO OFF; varnamearchive_mode/ and varnamearchive_command/ are separate variables so that varnamearchive_command/ can be changed without leaving archiving mode. -This parameter can only be set at server start. +This parameter can only be set at server start. It is ignored +unless varnamewal_mode/ is set to literalarchive/ or +literalhot_standby/. /para /listitem /varlistentry @@ -1884,16 +1923,14 @@ SET ENABLE_SEQSCAN TO OFF; /indexterm listitem para -Parameter has two roles. During recovery, specifies whether or not -you can connect and run queries to enable xref linkend=hot-standby. -During normal running, specifies whether additional information is written -to WAL to allow recovery connections on a standby server that reads -WAL data generated by this server. The default value is +During recovery, specifies whether or not you can connect and run +queries to enable xref linkend=hot-standby. The default value is
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas wrote:
On Fri, Apr 23, 2010 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Well, I think the real hole is that turning archive_mode=on results in
WAL never being deleted unless it's successfully archived.
Hm, good point. And at least in principle you could have SR setups
that don't care about having a backing WAL archive.
But we might be able to handle that like this:
wal_mode={standby|archive|crash} # or whatever
wal_segments_always=integer # keep this many segments always, for
SR - like current wal_keep_segments
wal_segments_unarchived=integer # keep this many unarchived
segments, -1 for infinite
max_wal_senders=integer # same as now
archive_command=string# same as now
So we always retain wal_segments_always segments, but if we have
trouble with archiving we'll retain up to wal_segments_archived.
And when that limit is reached, what happens? Panic shutdown?
Silently drop unarchived data? Neither one sounds very good.
Silently drop unarchived data. I agree that isn't very good, but
think about it this way: if archive_command is failing, then our log
shipping slave is not going to work. But letting the disk fill up on
the primary does not make it any better. It just makes the primary
stop working, too. Obviously, all of this stuff needs to be monitored
or you're playing with fire, but I don't think having a safety valve
on the primary is a stupid idea.
hmm not sure I agree - you need to monitor diskspace usage in general on
a system for obvious reasons. I think dealing with that kind of stuff is
not really in our realm. We are a relational database and we need to
guard the data, silently dropping data is imho not a good idea.
Just picture the typical scenario of maintenance during night times on
the standby done by a sysadmin with some batch jobs running on the
master just generating enough WAL to exceed the limit that will just
cause the sysadmin to call the DBA in.
In general the question really is will people set this to something
sensible or rather to an absurdly high value just to avoid that their
replication will ever break - I guess people will do that later in
critical environments...
Stefan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] INSERT and parentheses
Hi,
This came up on IRC today and I recall several instances of this during
the last two months or so, so I decided to send a patch. The problem in
question occurs when you have extra parentheses in an INSERT list:
INSERT INTO foo(a,b,c) SELECT (a,b,c) FROM ..; or
INSERT INTO foo(a,b,c) VALUES((0,1,2));
Both of these give you the same error:
ERROR: INSERT has more target columns than expressions
The first version is a lot more common and as it turns out, is sometimes
very hard to spot. This patch attaches a HINT message to these two
cases. The message itself could probably be a lot better, but I can't
think of anything.
Thoughts?
Regards,
Marko Tiikkaja
*** a/src/backend/parser/analyze.c
--- b/src/backend/parser/analyze.c
***
*** 730,742 transformInsertRow(ParseState *pstate, List *exprlist,
list_length(icolumns));
if (stmtcols != NIL
list_length(exprlist) list_length(icolumns))
! ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg(INSERT has more target columns than
expressions),
parser_errposition(pstate,
exprLocation(list_nth(icolumns,
list_length(exprlist));
/*
* Prepare columns for assignment to target table.
*/
--- 730,761
list_length(icolumns));
if (stmtcols != NIL
list_length(exprlist) list_length(icolumns))
! {
! /*
!* If the expression only has a single column of type record,
it's
!* possible that that wasn't intended.
!*/
! if (list_length(exprlist) == 1
! (IsA(linitial(exprlist), Var)
! ((Var *) linitial(exprlist))-vartype == RECORDOID) ||
! IsA(linitial(exprlist), RowExpr))
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
!errmsg(INSERT has more target columns than
expressions),
!errhint(Did you accidentally use extra
parentheses?),
!parser_errposition(pstate,
!
exprLocation(list_nth(icolumns,
!
list_length(exprlist));
! else
! ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg(INSERT has more target columns than
expressions),
parser_errposition(pstate,
exprLocation(list_nth(icolumns,
list_length(exprlist));
+ }
+
/*
* Prepare columns for assignment to target table.
*/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Mon, Apr 26, 2010 at 8:05 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Tom Lane wrote: Personally I agree with your objection to crash but not with the objection to standby. Maybe this would be appropriate: wal_mode = minimal | archive | hot_standby Ok, here's a patch implementing this proposal. It adds a new wal_mode setting, leaving archive_mode as it is. If you try to enable archive_mode when wal_mode is 'minimal', you get a warning and archive_mode is silently ignored. Likewise streaming replication connections are not allowed if wal_mode is 'minimal'. recovery_connections now does nothing in the master. A bit more bikeshedding before I commit this: * Should an invalid combination throw an ERROR and refuse to start, instead of just warning? I think so. Otherwise silent breakage is a real possibility. * How about naming the parameter wal_level instead of wal_mode? That would better convey that the higher levels add stuff on top of the lower levels, instead of having different modes that are somehow mutually exclusive. That works for me. ...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] INSERT and parentheses
On Mon, Apr 26, 2010 at 8:57 AM, Marko Tiikkaja marko.tiikk...@cs.helsinki.fi wrote: Hi, This came up on IRC today and I recall several instances of this during the last two months or so, so I decided to send a patch. The problem in question occurs when you have extra parentheses in an INSERT list: INSERT INTO foo(a,b,c) SELECT (a,b,c) FROM ..; or INSERT INTO foo(a,b,c) VALUES((0,1,2)); Both of these give you the same error: ERROR: INSERT has more target columns than expressions The first version is a lot more common and as it turns out, is sometimes very hard to spot. This patch attaches a HINT message to these two cases. The message itself could probably be a lot better, but I can't think of anything. Thoughts? I suggest adding it to the next CommitFest. Since I've never been bitten by this, I can't get excited about the change, but I'm also not arrogant enough to believe that everyone else's experiences are the same as my own. ...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] INSERT and parentheses
* Robert Haas (robertmh...@gmail.com) wrote: The first version is a lot more common and as it turns out, is sometimes very hard to spot. This patch attaches a HINT message to these two cases. The message itself could probably be a lot better, but I can't think of anything. Thoughts? I suggest adding it to the next CommitFest. Since I've never been bitten by this, I can't get excited about the change, but I'm also not arrogant enough to believe that everyone else's experiences are the same as my own. Not to be a pain, but the hint really is kind of terrible.. It'd probably be better if you included somewhere that the insert appears to be a single column with a record-type rather than multiple columns of non-composite type.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
On Fri, Apr 23, 2010 at 4:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: No intention of doing that. This change allows people to see what the dependency actually is once the bug has been fixed. Change needs to start from here, not from where we were before. Well, actually, now that I've looked at the patch I think it's starting from a fundamentally wrong position anyway. Checkpoint records are a completely wrong mechanism for transmitting this data to slaves, because a checkpoint is emitted *after* we do something, not *before* we do it. In particular it's ludicrous to be looking at shutdown checkpoints to try to determine whether the subsequent WAL will meet the slave's requirements. There's no connection at all between what the GUC state was at shutdown and what it might be after starting again. A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Then, slaves could check whether the master's wal_mode is high enough by looking at pg_control when they start plus any wal_mode_change records they come across. If we did this then we could get rid of those WAL record types that were added to signify that information had been omitted from WAL at specific times. dons project manager hat I notice that Heikki's patch doesn't include doing the above. Should we? If so, who's going to do it? ...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] [GENERAL] trouble with to_char('L')
Hiroshi Inoue wrote: Bruce Momjian wrote: Takahiro Itagaki wrote: Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: Revised patch attached. Please test it. I applied this version of the patch. Please check wheter the bug is fixed and any buildfarm failures. Great. I have merged in my C comments into the code with the attached patch so we remember why the code is setup as it is. One thing I am confused about is that, for Win32, our numeric/monetary handling sets lc_ctype to match numeric/monetary, while our time code in the same file uses that method _and_ uses wcsftime() to return the value in wide characters. So, why do we do both for time? Is there any value to that? Unfortunately wcsftime() is a halfway conveniece function which uses ANSI version of functionalities internally. AFAIC the only way to remove the dependency to LC_CTYPE is to call GeLocaleInfoW() directly. Thanks. I have documented this fact in a C comment; patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com Index: src/backend/utils/adt/pg_locale.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/pg_locale.c,v retrieving revision 1.55 diff -c -c -r1.55 pg_locale.c *** src/backend/utils/adt/pg_locale.c 24 Apr 2010 22:54:56 - 1.55 --- src/backend/utils/adt/pg_locale.c 26 Apr 2010 13:30:03 - *** *** 627,633 save_lc_time = pstrdup(save_lc_time); #ifdef WIN32 ! /* See the WIN32 comment near the top of PGLC_localeconv() */ /* save user's value of ctype locale */ save_lc_ctype = setlocale(LC_CTYPE, NULL); if (save_lc_ctype) --- 627,641 save_lc_time = pstrdup(save_lc_time); #ifdef WIN32 ! /* ! * On WIN32, there is no way to get locale-specific time values in a ! * specified locale, like we do for monetary/numeric. We can only get ! * CP_ACP (see strftime_win32) or UTF16. Therefore, we get UTF16 and ! * convert it to the database locale. However, wcsftime() internally ! * uses LC_CTYPE, so we set it here. See the WIN32 comment near the ! * top of PGLC_localeconv(). ! */ ! /* save user's value of ctype locale */ save_lc_ctype = setlocale(LC_CTYPE, NULL); if (save_lc_ctype) -- 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] CIText and pattern_ops
On 26 April 2010 11:19, Robert Haas robertmh...@gmail.com wrote: On Fri, Apr 23, 2010 at 11:27 PM, Rod Taylor p...@rbt.ca wrote: Is there any particular reason why the citext module doesn't have citext_pattern_ops operator family? You forgot to send in the patch. :-) ...Robert Yes, someone implementing this would be greatly appreciated, especially since I've just started using this datatype. ;) Thom
Re: [HACKERS] INSERT and parentheses
Stephen Frost sfr...@snowman.net writes: Not to be a pain, but the hint really is kind of terrible.. It'd probably be better if you included somewhere that the insert appears to be a single column with a record-type rather than multiple columns of non-composite type.. I don't much care for the test, either. AFAICS, a hint like this would only be appropriate for a RowExpr item, *not* a Var. It might also be worth checking the number of items in the RowExpr before deciding that the hint is appropriate. 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] Order of pg_stat_activity timestamp columns
Applied; catalog version bumped. --- Bruce Momjian wrote: Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: The current column ordering can be rationalized to some extent as 1. identity info (user id, db id, application name) 2. current query info 3. session info (backend start time, client addr/port) OK. I guess that trumps my idea, although it would sure be nice if it were possible to swap 2 and 3 so that we could put the query text at the end. Well, the current ordering is definitely historical rather than designed, but I'm hesitant to do more than minor tweaking. Even if we think/hope it won't break applications, people are probably used to seeing a particular ordering. I'm not necessarily dead set against it though. I guess if we were to do what you suggest, we'd end up with identity: datid| oid | datname | name | procpid | integer | usesysid | oid | usename | name | application_name | text | session: client_addr | inet | client_port | integer | backend_start| timestamp with time zone | transaction: xact_start | timestamp with time zone | query: query_start | timestamp with time zone | waiting | boolean | current_query| text | or possibly that plus relocate procpid somewhere else. Anyone think this is sufficiently better to justify possible confusion? I implemented Tom's suggested ordering above: test= SELECT * FROM pg_stat_activity; -[ RECORD 1 ]+ datid| 16384 datname | test procpid | 22216 usesysid | 10 usename | postgres application_name | psql client_addr | client_port | -1 backend_start| 2010-04-24 22:35:21.683308-04 xact_start | 2010-04-24 22:47:19.53821-04 query_start | 2010-04-24 22:47:19.53821-04 waiting | f current_query| SELECT * FROM pg_stat_activity; Patch attached. It will require a catversion bump too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com Index: src/backend/catalog/system_views.sql === RCS file: /cvsroot/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.65 diff -c -c -r1.65 system_views.sql *** src/backend/catalog/system_views.sql 2 Jan 2010 16:57:36 - 1.65 --- src/backend/catalog/system_views.sql 25 Apr 2010 02:47:39 - *** *** 335,347 S.usesysid, U.rolname AS usename, S.application_name, ! S.current_query, ! S.waiting, S.xact_start, S.query_start, ! S.backend_start, ! S.client_addr, ! S.client_port FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U WHERE S.datid = D.oid AND S.usesysid = U.oid; --- 335,347 S.usesysid, U.rolname AS usename, S.application_name, ! S.client_addr, ! S.client_port, ! S.backend_start, S.xact_start, S.query_start, ! S.waiting, ! S.current_query FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U WHERE S.datid = D.oid AND S.usesysid = U.oid; Index: src/test/regress/expected/rules.out === RCS file: /cvsroot/pgsql/src/test/regress/expected/rules.out,v retrieving revision 1.154 diff -c -c -r1.154 rules.out *** src/test/regress/expected/rules.out 29 Dec 2009 20:11:45 - 1.154 --- src/test/regress/expected/rules.out 25 Apr 2010 02:47:40 - *** *** 1289,1295 pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename '_RETURN'::name); pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val, a.enumvals, a.boot_val, a.reset_val, a.sourcefile, a.sourceline FROM pg_show_all_settings()
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Robert Haas robertmh...@gmail.com writes: On Mon, Apr 26, 2010 at 8:05 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * How about naming the parameter wal_level instead of wal_mode? That would better convey that the higher levels add stuff on top of the lower levels, instead of having different modes that are somehow mutually exclusive. That works for me. What happens in the future if we have more options and they don't fall into a neat superset order? 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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Mon, Apr 26, 2010 at 10:23 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Apr 26, 2010 at 8:05 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: * How about naming the parameter wal_level instead of wal_mode? That would better convey that the higher levels add stuff on top of the lower levels, instead of having different modes that are somehow mutually exclusive. That works for me. What happens in the future if we have more options and they don't fall into a neat superset order? We'll decide on the appropriate solution based on whatever our needs are at that time? ...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] Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct
Robert Haas wrote: On Fri, Apr 23, 2010 at 4:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, actually, now that I've looked at the patch I think it's starting from a fundamentally wrong position anyway. Checkpoint records are a completely wrong mechanism for transmitting this data to slaves, because a checkpoint is emitted *after* we do something, not *before* we do it. In particular it's ludicrous to be looking at shutdown checkpoints to try to determine whether the subsequent WAL will meet the slave's requirements. There's no connection at all between what the GUC state was at shutdown and what it might be after starting again. A design that might work is (1) store the active value of wal_mode in pg_control (but NOT as part of the last-checkpoint-record image). (2) invent a new WAL record type that is transmitted when we change wal_mode. Then, slaves could check whether the master's wal_mode is high enough by looking at pg_control when they start plus any wal_mode_change records they come across. If we did this then we could get rid of those WAL record types that were added to signify that information had been omitted from WAL at specific times. dons project manager hat I notice that Heikki's patch doesn't include doing the above. Should we? If so, who's going to do it? I'll give it a shot. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] testing HS/SR - 1 vs 2 performance
On Mon, April 26, 2010 08:52, Fujii Masao wrote: On Mon, Apr 26, 2010 at 3:25 AM, Erik Rijkers e...@xs4all.nl wrote: FWIW, here are some more results from pgbench comparing primary and standby (both with Simon's patch). Was there a difference in CPU utilization between the primary and standby? I haven't monitored 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] testing HS/SR - 1 vs 2 performance
On Mon, April 26, 2010 09:43, Simon Riggs wrote: On Sun, 2010-04-25 at 23:52 +0200, Erik Rijkers wrote: I'll try to repeat this pattern on other hardware; although if my tests were run with faulty hardware I wouldn't know how/why that would give the above effect (such a 'regular aberration'). testing is more difficult than I thought... Thanks again for your help. Please can you confirm: * Are the standby tests run while the primary is completely quiet? autovacuum was on. Which is probably not a good idea - I'll try a few runs without it. * What OS is this? Can we use dtrace scripts? Centos 5.4. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Discarding the resulting rows
Hello Hackers: Two questions. 1. I would like to execute queries such as select * from part and time the query. But I want to ignore how much time is taken for printing the result to a file or the screen. Basically, I would like to discard the result rows after doing all the work required to execute the query. I looked at the documentation and I saw something about using the keyword PERFORM rather than SELECT. I tried PERFORM * from part; But this gave me a syntax error. Please let me know how this can be done. 2. How do I clear the buffer caches between two query runs? I believe this is not possible in Postgres. Can someone please confirm this or tell me how it may be done. Thanks, Murali. - Please visit NumberFest.com for educational number puzzles mind exercises for all ages! And please tell your friends about it. Thank You!
Re: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
Folks, (a) is this checked in yet? (b) should we delay Beta to test it?\ -- -- Josh Berkus PostgreSQL Experts Inc. http://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: recovery_connections cannot start (was Re: [HACKERS] master in standby mode croaks)
On Mon, Apr 26, 2010 at 2:15 PM, Josh Berkus j...@agliodbs.com wrote: (a) is this checked in yet? No. (b) should we delay Beta to test it?\ I suspect it's going to be checked in pretty soon, so that may not be necessary. Not my call, though. ...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] global temporary tables
On Sun, 2010-04-25 at 11:35 -0400, Robert Haas wrote: And I don't think you can even get that far, because I don't think too many people here are going to say that we shouldn't add global temporary tables unless we can also make them work with Hot Standby. The policy round here for some time has been that when we implement things we make them work fully and seamlessly. I don't see why Hot Standby would be singled out any more than any other feature, say Windows support or tablespaces should be occasionally ignored. People need to get used to the new feature set, just as we had to with HOT, subtransactions, prepared transactions, Gist etc.. That may require a thwack from various people, but the responsibility lies with the new feature implementor, not the person supporting existing code. I fully understand your wish to implement a partial feature with caveats because I have argued that many times myself. But I've come to realise that the best way is to build things so they work cleanly across the board. Other developers can plan projects in the knowledge that they can build directly on firm foundations, not fill in the cracks. In the end this comes down to a choice as developers, do we help each other by doing a full job, or do we leave unexploded bombs for each other through short-termism? Now I understand this better myself, I act differently and accept objections if people think a fuller, more complete design is what is needed. Recent demonstrations of that available, both objecting and accepting. Don't see this as an extra task, just see it as one of the many aspects that will need to be considered when developing it. If you do that it need not be additional work. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Discarding the resulting rows
Murali M. Krishna murali1...@yahoo.com wrote: Basically, I would like to discard the result rows after doing all the work required to execute the query. I would use EXPLAIN ANALYZE SELECT ... I looked at the documentation and I saw something about using the keyword PERFORM rather than SELECT. I don't remember ever seeing anything like that. Do you have a URL? How do I clear the buffer caches between two query runs? The easiest way to clear the PostgreSQL cache is to restart the service. PostgreSQL goes through the OS cache; so you'll need to clear that, too. How you do that is dependent on your OS. Of course, in most real use cases, a significant portion of the database would be cached, so unless you're dealing with a very unusual situation, it's hard to see what the value would be of such a benchmark, unless you're trying to create an artificial worst case scenario for bounding purposes. Oh, and most serious database servers have 256MB or more of battery backed cache on the RAID controller; don't forget to deal with that somehow. -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] global temporary tables
Simon Riggs si...@2ndquadrant.com writes: On Sun, 2010-04-25 at 11:35 -0400, Robert Haas wrote: And I don't think you can even get that far, because I don't think too many people here are going to say that we shouldn't add global temporary tables unless we can also make them work with Hot Standby. The policy round here for some time has been that when we implement things we make them work fully and seamlessly. I don't see why Hot Standby would be singled out any more than any other feature, say Windows support or tablespaces should be occasionally ignored. The current definition of Hot Standby is that it's a *read only* behavior. Not read mostly. What you are proposing is a rather fundamental change in the behavior of HS, and it doesn't seem to me that it should be on the head of anybody else to make it work. IOW: I agree with Robert that this is not an essential part of global temp tables. If it happens to fall out that it works like that, great, but it isn't a requirement. 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] Discarding the resulting rows
On Mon, Apr 26, 2010 at 2:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Murali M. Krishna murali1...@yahoo.com wrote: I looked at the documentation and I saw something about using the keyword PERFORM rather than SELECT. I don't remember ever seeing anything like that. Do you have a URL? i guess he is refering to the plpgsql's PERFORM statement, which of course he can't use outside a plpgsql function... mmm... well, IIRC, in 9.0 he will be able to do DO $$ PERFORM * FROM tabla; $$ LANGUAGE plpgsql; but i think DO is not an EXPLAINing statement -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] global temporary tables
On Mon, 2010-04-26 at 15:40 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: On Sun, 2010-04-25 at 11:35 -0400, Robert Haas wrote: And I don't think you can even get that far, because I don't think too many people here are going to say that we shouldn't add global temporary tables unless we can also make them work with Hot Standby. The policy round here for some time has been that when we implement things we make them work fully and seamlessly. I don't see why Hot Standby would be singled out any more than any other feature, say Windows support or tablespaces should be occasionally ignored. The current definition of Hot Standby is that it's a *read only* behavior. Not read mostly. What you are proposing is a rather fundamental change in the behavior of HS, and it doesn't seem to me that it should be on the head of anybody else to make it work. That's a dangerous precedent you just set. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] global temporary tables
On Mon, Apr 26, 2010 at 3:30 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, 2010-04-25 at 11:35 -0400, Robert Haas wrote: And I don't think you can even get that far, because I don't think too many people here are going to say that we shouldn't add global temporary tables unless we can also make them work with Hot Standby. The policy round here for some time has been that when we implement things we make them work fully and seamlessly. I don't see why Hot Standby would be singled out any more than any other feature, say Windows support or tablespaces should be occasionally ignored. People need to get used to the new feature set, just as we had to with HOT, subtransactions, prepared transactions, Gist etc.. That may require a thwack from various people, but the responsibility lies with the new feature implementor, not the person supporting existing code. I fully understand your wish to implement a partial feature with caveats because I have argued that many times myself. But I've come to realise that the best way is to build things so they work cleanly across the board. Other developers can plan projects in the knowledge that they can build directly on firm foundations, not fill in the cracks. In the end this comes down to a choice as developers, do we help each other by doing a full job, or do we leave unexploded bombs for each other through short-termism? Now I understand this better myself, I act differently and accept objections if people think a fuller, more complete design is what is needed. Recent demonstrations of that available, both objecting and accepting. Don't see this as an extra task, just see it as one of the many aspects that will need to be considered when developing it. If you do that it need not be additional work. I think you're looking at this the wrong way. If temporary tables have to work with Hot Standby in order for it to be committable, then we should never have committed Hot Standby in the first place because our current flavor of temporary tables doesn't. Was that an oversight on your part, or a recognition that you can't solve every problem in one commit? ...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] Discarding the resulting rows
On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Murali M. Krishna murali1...@yahoo.com wrote: Basically, I would like to discard the result rows after doing all the work required to execute the query. I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. 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] global temporary tables
Simon Riggs si...@2ndquadrant.com writes: On Mon, 2010-04-26 at 15:40 -0400, Tom Lane wrote: The current definition of Hot Standby is that it's a *read only* behavior. Not read mostly. What you are proposing is a rather fundamental change in the behavior of HS, and it doesn't seem to me that it should be on the head of anybody else to make it work. That's a dangerous precedent you just set. [ shrug... ] If you have near-term solutions for all the *other* problems that would be involved (like what XID to put into rows you insert in the temp tables) then I might think that what you're asking Robert to do is reasonable. Personally I think non-read-only HS is entirely pie in the sky, and therefore it's not reasonable to saddle unrelated development tasks with an expectation that they should work with a behavior that probably won't ever happen. 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] Discarding the resulting rows
On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Murali M. Krishna murali1...@yahoo.com wrote: Basically, I would like to discard the result rows after doing all the work required to execute the query. I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. he could see the actual time in the very first row of the EXPLAIN ANALYZE... isn't that a value that is more close to what the OP is looking for? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] global temporary tables
Tom Lane escribió: [ forgot to respond to this part ] Robert Haas robertmh...@gmail.com writes: ... I don't see the problem with DROP. Under the proposed design, it's approximately equivalent to dropping a table that someone else has truncated. You just wait for the necessary lock and then do it. And do *what*? You can remove the catalog entries, but how are you going to make the physical storage of other backends' versions go away? (To say nothing of making them flush their local buffers for it.) Maybe we could add a sinval message to that effect. -- 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] Discarding the resulting rows
Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Murali M. Krishna murali1...@yahoo.com wrote: Basically, I would like to discard the result rows after doing all the work required to execute the query. I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. Good point. At the moment I can't think how to do better, though. Other suggestions, anyone? -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] Discarding the resulting rows
Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. he could see the actual time in the very first row of the EXPLAIN ANALYZE... isn't that a value that is more close to what the OP is looking for? Well, it will include the instrumentation overhead of EXPLAIN ANALYZE, which can be nontrivial depending on your hardware and the query plan. On the other hand, EXPLAIN skips the cost of converting the result data to text form, not to mention the network overhead of delivering it; so in another sense it's underestimating the work involved. I guess the real question is exactly what the OP is hoping to measure and why. 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] Discarding the resulting rows
Hello All: The optimizer assumes that data is disk resident when computing the cost of a query plan. I am trying to ascertain what the correlation is between times and costs of some benchmark queries to see how good the cost model is. Since I have more than 100 queries, it would be painful to stop and start the server each time to force all the buffer pages out. Also, some of these queries have large number of result rows. I don't want the time to be skewed by the output time. Cheers, Murali. - Please visit NumberFest.com for educational number puzzles mind exercises for all ages! And please tell your friends about it. Thank You! --- On Mon, 4/26/10, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [HACKERS] Discarding the resulting rows To: Jaime Casanova jcasa...@systemguards.com.ec Cc: Robert Haas robertmh...@gmail.com, Kevin Grittner kevin.gritt...@wicourts.gov, pgsql-hackers@postgresql.org, Murali M. Krishna murali1...@yahoo.com Date: Monday, April 26, 2010, 1:25 PM Jaime Casanova jcasa...@systemguards.com.ec writes: On Mon, Apr 26, 2010 at 3:03 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 26, 2010 at 3:36 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I would use EXPLAIN ANALYZE SELECT ... There's some overhead to that, of course. he could see the actual time in the very first row of the EXPLAIN ANALYZE... isn't that a value that is more close to what the OP is looking for? Well, it will include the instrumentation overhead of EXPLAIN ANALYZE, which can be nontrivial depending on your hardware and the query plan. On the other hand, EXPLAIN skips the cost of converting the result data to text form, not to mention the network overhead of delivering it; so in another sense it's underestimating the work involved. I guess the real question is exactly what the OP is hoping to measure and why. 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] pg_migrator
There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do we still want to do that? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator
On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do we still want to do that? I think you articulated some pretty good reasons previously for keeping it separate and, at any rate, I'm not eager to do it at the 11th hour without due consideration and adequate engineering time. So I vote for holding off for this release and possibly revisiting at some point down the road. ...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] pg_migrator
Robert Haas wrote: On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: There was talk of including pg_migrator in Postgres 9.0 in /contrib. ?Do we still want to do that? I think you articulated some pretty good reasons previously for keeping it separate and, at any rate, I'm not eager to do it at the 11th hour without due consideration and adequate engineering time. So I vote for holding off for this release and possibly revisiting at some point down the road. You might also remember I was outvoted. It will not be hard to put it in /contrib as that is already a valid build option for pg_migrator. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_migrator
On Mon, Apr 26, 2010 at 9:46 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: There was talk of including pg_migrator in Postgres 9.0 in /contrib. ?Do we still want to do that? I think you articulated some pretty good reasons previously for keeping it separate and, at any rate, I'm not eager to do it at the 11th hour without due consideration and adequate engineering time. So I vote for holding off for this release and possibly revisiting at some point down the road. You might also remember I was outvoted. It will not be hard to put it in /contrib as that is already a valid build option for pg_migrator. [shrug...] If that's the consensus I'll go along with it, but I'm not excited about adding more things to our to-do list at this point, even apparently simple ones. ...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] pg_migrator
Robert Haas robertmh...@gmail.com writes: On Mon, Apr 26, 2010 at 9:26 PM, Bruce Momjian br...@momjian.us wrote: There was talk of including pg_migrator in Postgres 9.0 in /contrib. Do we still want to do that? I think you articulated some pretty good reasons previously for keeping it separate and, at any rate, I'm not eager to do it at the 11th hour without due consideration and adequate engineering time. I concur; it's about a month too late to propose this. 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] testing HS/SR - 1 vs 2 performance
On Sun, 2010-04-25 at 13:51 -0400, Tom Lane wrote:
Simon Riggs si...@2ndquadrant.com writes:
On Sun, 2010-04-25 at 13:33 -0400, Tom Lane wrote:
If you like I'll have a go at rewriting the comments for this patch,
because I am currently thinking that the problem is not so much with
the code as with the poor explanation of what it's doing. Sometimes
the author is too close to the code to understand why other people
have a hard time understanding it.
That would help me, thank you.
OK. You said you were currently working some more on the patch, so
I'll wait for v3 and then work on it.
v3 attached
Changes:
* Strange locking in KnownAssignedXidsAdd() moved to RecordKnown...
* KnownAssignedXidsAdd() reordered, assert-ish code added
* Tail movement during snapshots no longer possible
* Tail movement during xid removal added to KnownAssignedXidsSearch()
* Major comment hacking
Little bit rough, definitely needs a re-read of all comments, so good
time to send over.
--
Simon Riggs www.2ndQuadrant.com
*** a/src/backend/access/transam/twophase.c
--- b/src/backend/access/transam/twophase.c
***
*** 1200,1205 StandbyTransactionIdIsPrepared(TransactionId xid)
--- 1200,1208
Assert(TransactionIdIsValid(xid));
+ if (max_prepared_xacts = 0)
+ return false; /* nothing to do */
+
/* Read and validate file */
buf = ReadTwoPhaseFile(xid, false);
if (buf == NULL)
*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***
*** 6454,6459 CheckRecoveryConsistency(void)
--- 6454,6465
}
}
+ bool
+ XLogConsistentState(void)
+ {
+ return reachedMinRecoveryPoint;
+ }
+
/*
* Is the system still in recovery?
*
*** a/src/backend/storage/ipc/procarray.c
--- b/src/backend/storage/ipc/procarray.c
***
*** 52,57
--- 52,58
#include access/twophase.h
#include miscadmin.h
#include storage/procarray.h
+ #include storage/spin.h
#include storage/standby.h
#include utils/builtins.h
#include utils/snapmgr.h
***
*** 64,73 typedef struct ProcArrayStruct
int numProcs; /* number of valid procs entries */
int maxProcs; /* allocated size of procs array */
! int numKnownAssignedXids; /* current number of known assigned
! * xids */
! int maxKnownAssignedXids; /* allocated size of known assigned
! * xids */
/*
* Highest subxid that overflowed KnownAssignedXids array. Similar to
--- 65,84
int numProcs; /* number of valid procs entries */
int maxProcs; /* allocated size of procs array */
! /*
! * Known assigned xids handling
! */
! int maxKnownAssignedXids; /* allocated size */
!
! /*
! * Callers must hold either ProcArrayLock in Exclusive mode or
! * ProcArrayLock in Shared mode *and* known_assigned_xids_lck
! * to update these values.
! */
! int numKnownAssignedXids; /* currrent # valid entries */
! int tailKnownAssignedXids; /* current tail */
! int headKnownAssignedXids; /* current head */
! slock_t known_assigned_xids_lck; /* shared protection lock */
/*
* Highest subxid that overflowed KnownAssignedXids array. Similar to
***
*** 87,93 static ProcArrayStruct *procArray;
/*
* Bookkeeping for tracking emulated transactions in recovery
*/
! static HTAB *KnownAssignedXidsHash;
static TransactionId latestObservedXid = InvalidTransactionId;
/*
--- 98,105
/*
* Bookkeeping for tracking emulated transactions in recovery
*/
! static TransactionId *KnownAssignedXids;
! static bool *KnownAssignedXidsValid;
static TransactionId latestObservedXid = InvalidTransactionId;
/*
***
*** 142,150 static int KnownAssignedXidsGet(TransactionId *xarray, TransactionId xmax);
static int KnownAssignedXidsGetAndSetXmin(TransactionId *xarray, TransactionId *xmin,
TransactionId xmax);
static bool KnownAssignedXidsExist(TransactionId xid);
! static void KnownAssignedXidsAdd(TransactionId *xids, int nxids);
static void KnownAssignedXidsRemove(TransactionId xid);
! static void KnownAssignedXidsRemoveMany(TransactionId xid, bool keepPreparedXacts);
static void KnownAssignedXidsDisplay(int trace_level);
/*
--- 154,166
static int KnownAssignedXidsGetAndSetXmin(TransactionId *xarray, TransactionId *xmin,
TransactionId xmax);
static bool KnownAssignedXidsExist(TransactionId xid);
! static void KnownAssignedXidsAdd(TransactionId from_xid, TransactionId to_xid,
! bool exclusive_lock);
static void KnownAssignedXidsRemove(TransactionId xid);
! static void KnownAssignedXidsRemoveMany(TransactionId xid);
! static void KnownAssignedXidsRemoveTree(TransactionId xid, int nsubxids,
! TransactionId *subxids);
! static void KnownAssignedXidsCompress(bool full);
static void KnownAssignedXidsDisplay(int trace_level);
/*
***
*** 204,228
Re: [HACKERS] testing HS/SR - 1 vs 2 performance
Simon Riggs si...@2ndquadrant.com writes: v3 attached Thanks, will work on this tomorrow. 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
