Re: [HACKERS] Partitioning syntax
Here is a revised partitioning syntax patch. It implements only syntax and on-disk structure mentioned below: Table Partitioning#Syntax http://wiki.postgresql.org/wiki/Table_partitioning#Syntax Table Partitioning#On-disk structure http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure Will 9.1 partitions allow to reference partitioned tables in foreign keys? -- 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] Partitioning syntax
Here is a revised partitioning syntax patch. It implements only syntax and on-disk structure mentioned below: Table Partitioning#Syntax http://wiki.postgresql.org/wiki/Table_partitioning#Syntax Table Partitioning#On-disk structure http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure Will 9.1 partitions allow to reference partitioned tables in foreign keys? Regards, Dmitry -- 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] Partitioning syntax
Dmitry Fefelov fo...@ac-sw.com wrote: Here is a revised partitioning syntax patch. It implements only syntax and on-disk structure mentioned below: Table Partitioning#Syntax http://wiki.postgresql.org/wiki/Table_partitioning#Syntax Table Partitioning#On-disk structure http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure Will 9.1 partitions allow to reference partitioned tables in foreign keys? Not in my first goals, but it might be possible if we could support row locks for UNION plans: =# SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2 FOR SHARE; ERROR: SELECT FOR UPDATE/SHARE is not allowed with UNION/INTERSECT/EXCEPT (in 9.0) 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] Bug in 9.0Alpha4
When we were doing the ordered-aggregates patch, I considered passing all those values as explicit parameters to transformAggregateCall, and having it build the Aggref node from scratch and return it. However having seven or eight parameters to transformAggregateCall (and more in future if we ever add more features here) didn't really seem to be better style than abusing Aggref a bit. But maybe it is the best way after all. Thoughts? I feel it would be good, if we send the parameters explicitly and if that increases, put it inside another structure(data carriage structure) and send it.. But please take my suggestion as a novice one. :)) Thanks, Gokul.
[HACKERS] PQftype implementation
Hello, Pgsql-hackers. The script: CREATE TYPE my_varchar; CREATE OR REPLACE FUNCTION my_varcharout(my_varchar) RETURNS cstring AS 'varcharout' LANGUAGE 'internal' IMMUTABLE STRICT COST 1; CREATE OR REPLACE FUNCTION my_varcharin(cstring, oid, integer) RETURNS my_varchar AS 'varcharin' LANGUAGE 'internal' IMMUTABLE STRICT COST 1; CREATE TYPE my_varchar (INPUT=my_varcharin, OUTPUT=my_varcharout, DEFAULT='', INTERNALLENGTH=-1, ALIGNMENT=int4, STORAGE=EXTENDED, TYPMOD_IN=varchartypmodin, TYPMOD_OUT=varchartypmodout); CREATE TABLE my_varchar_test( id serial primary key, info my_varchar(100) ) Here I created user-defined type my_varchar for internal tests. But PQftype returns 1043 (varchar oid) for the info column. I'm a little bit confused of such behaviour. What am I missing? Ans where in the sources can I find the way server fills res-attDescs[field_num].typid? Thanks in advance. -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Command to prune archive at restartpoints
One awkward omission in the new built-in standby mode, mainly used for streaming replication, is that there is no easy way to delete old archived files like you do with the %r parameter to restore_command. This was discussed at http://archives.postgresql.org/pgsql-hackers/2010-02/msg01003.php, among other things. Per discussion, attached patch adds a new restartpoint_command option to recovery.conf. That's an external shell command just like recovery_end_command that's executed at every restartpoint. You can use the %r parameter to pass the filename of the oldest WAL file that needs to be retained. While developing this I noticed that %r in recovery_end_command is not working correctly: LOG: redo done at 0/14000C10 LOG: last completed transaction was at log time 2000-01-01 02:21:08.816445+02 cp: cannot stat `/home/hlinnaka/pgsql.cvshead/walarchive/00010014': No such file or directory cp: cannot stat `/home/hlinnaka/pgsql.cvshead/walarchive/0002.history': No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/home/hlinnaka/pgsql.cvshead/walarchive/0001.history': No such file or directory LOG: archive recovery complete LOG: checkpoint starting: end-of-recovery immediate wait LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.000 s, sync=0.000 s, total=0.003 s LOG: executing recovery_end_command echo recovery_end_command %r recovery_end_command LOG: database system is ready to accept connections LOG: autovacuum launcher started Note how %r is always expanded to . That's because %r is expanded only when InRedo is true, which makes sense for restore_command where that piece of code was copy-pasted from, but it's never true anymore when recovery_end_command is run. The attached patch fixes that too. Barring objections, I will commit this later today. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index 6404768..7b7a8bd 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -59,14 +59,15 @@ restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /listitem /varlistentry - varlistentry id=recovery-end-command xreflabel=recovery_end_command - termvarnamerecovery_end_command/varname (typestring/type)/term + varlistentry id=restartpoint-command xreflabel=restartpoint_command + termvarnamerestartpoint_command/varname (typestring/type)/term listitem para -This parameter specifies a shell command that will be executed once only -at the end of recovery. This parameter is optional. The purpose of the -varnamerecovery_end_command/ is to provide a mechanism for cleanup -following replication or recovery. +This parameter specifies a shell command that will be executed at +every restartpoint. This parameter is optional. The purpose of the +varnamerestartpoint_command/ is to provide a mechanism for cleaning +up old archived WAL files that are no longer needed by the standby +server. Any literal%r/ is replaced by the name of the file containing the last valid restart point. That is the earliest file that must be kept to allow a restore to be restartable, so this information @@ -79,6 +80,24 @@ restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /para para If the command returns a non-zero exit status then a WARNING log +message will be written. + /para + /listitem + /varlistentry + + varlistentry id=recovery-end-command xreflabel=recovery_end_command + termvarnamerecovery_end_command/varname (typestring/type)/term + listitem + para +This parameter specifies a shell command that will be executed once only +at the end of recovery. This parameter is optional. The purpose of the +varnamerecovery_end_command/ is to provide a mechanism for cleanup +following replication or recovery. +Any literal%r/ is replaced by the name of the file containing the +last valid restart point, like in xref linkend=restartpoint-command. + /para + para +If the command returns a non-zero exit status then a WARNING log message will be written and the database will proceed to start up anyway. An exception is that if the command was terminated by a signal, the database will not proceed with startup. diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 995794a..519526e 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -171,6 +171,7 @@ static bool restoredFromArchive = false; /* options taken from recovery.conf for archive recovery */ static char
Re: [HACKERS] Command to prune archive at restartpoints
On Wed, Mar 17, 2010 at 9:37 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: One awkward omission in the new built-in standby mode, mainly used for streaming replication, is that there is no easy way to delete old archived files like you do with the %r parameter to restore_command. I'm still finding this kind of narrow-minded. I'm picturing a system with multiple replicas -- obvious no one replica can take it upon itself to delete archived log files based only on its own restartpoint. And besides, if you're using the archived log files for backups you also need to take into account the backup policy and only delete files that aren't needed for a consistent backup and aren't needed for the replica. What we need is a program which can take all this information from all your slaves and backup labels into account and implement your backup policies. It probably won't exist in time for the release and in any case doesn't really have to ship with Postgres. There might even be more than one. But do we have all the information that such a program would need? Is there a way to connect to a replica and ask it what the restart point is? I suppose with this new command you could always just make it a command which wakes up this demon and sends it the restart point and the replica id and it can update its internal state and recalculate what archives are needed. It is a bit nerve-wracking that it's dependent on its internal state remembering the restart points it's been given though. -- greg -- 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] Command to prune archive at restartpoints
Greg Stark wrote: On Wed, Mar 17, 2010 at 9:37 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: One awkward omission in the new built-in standby mode, mainly used for streaming replication, is that there is no easy way to delete old archived files like you do with the %r parameter to restore_command. I'm still finding this kind of narrow-minded. I'm picturing a system with multiple replicas -- obvious no one replica can take it upon itself to delete archived log files based only on its own restartpoint. And besides, if you're using the archived log files for backups you also need to take into account the backup policy and only delete files that aren't needed for a consistent backup and aren't needed for the replica. That's why we provide options that take any shell command you want, rather than e.g a path to an archive directory that's pruned automatically. For example, if you have multiple standbys sharing one archive, you could do something like this: In each standby, have a restartpoint_command along the lines of: echo %r archivedirectory/standby1_location; archive_cleanup.sh Where '1' is different for every standby and in archive_cleanup.sh, scan through all the standbyX_location files, take the minimum, and delete all files smaller than that. You'll need some care with locking etc., but the point is that the current hooks allow you to implement complex setups like that. What we need is a program which can take all this information from all your slaves and backup labels into account and implement your backup policies. It probably won't exist in time for the release and in any case doesn't really have to ship with Postgres. There might even be more than one. I guess I just described such a program :-). Yeah, I'd imagine that to become part of toolkits like skytools. But do we have all the information that such a program would need? Is there a way to connect to a replica and ask it what the restart point is? Hmm, Greg Smith opened a thread on exposing the fields in the control file as user-defined functions. IIRC last restartpoint location was the piece of information that triggered the discussion this time. Perhaps we should indeed add a function to expose that in 9.0. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
Fujii Masao wrote: I found another missing feature in new file-based log shipping (i.e., standby_mode is enabled and 'cp' is used as restore_command). After the trigger file is found, the startup process with pg_standby tries to replay all of the WAL files in both pg_xlog and the archive. So, when the primary fails, if the latest WAL file in pg_xlog of the primary can be read, we can prevent the data loss by copying it to pg_xlog of the standby before creating the trigger file. On the other hand, the startup process with standby mode doesn't replay the WAL files in pg_xlog after the trigger file is found. So failover always causes the data loss even if the latest WAL file can be read from the primary. And if the latest WAL file is copied to the archive instead, it can be replayed but a PANIC error would happen because it's not filled. We should remove this restriction? Looking into this, I realized that we have a bigger problem related to this. Although streaming replication stores the streamed WAL files in pg_xlog, so that they can be re-replayed after a standby restart without connecting to the master, we don't try to replay those either. So if you restart standby, it will fail to start up if the WAL it needs can't be found in archive or by connecting to the master. That must be fixed. I'd imagine that the ability to restore WAL files manually copied to pg_xlog will fall out of that fix too. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL
On Wed, 2010-03-17 at 12:35 +0200, Heikki Linnakangas wrote: Looking into this, I realized that we have a bigger problem... A lot of this would be easier if you do the docs first, then work through the problems. The new system is more complex, since it has two modes rather than one and also multiple processes and a live connection. The number of failure cases must be higher than previously. Documenting how it is supposed to work in the event of failure will help everyone check those and comment on them. -- 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] Dyamic updates of NEW with pl/pgsql
On 17.03.10 4:08 , Merlin Moncure wrote: On Tue, Mar 16, 2010 at 5:53 PM, Florian Pflugfgp.phlo@gmail.com wrote: which returns the field namedfield from the record. The expected field type is specified by providing a default value indefval of the expected type. Since that argument's type is ANYELEMENT, just like the return type, the type system copes perfectly with the varying return type. You can choose whether to auto-coerce the field's value if it has a type other thandefval's type or whether to raise an error. So in essence I'm using the ANYELEMENT trick to get a poor man's version of your idea that doesn't require core changes. My post about this module got zero responses though... Why should we use what you've already written when we can just write it ourselves? Next you are going to say you're already using it and it works really well :-). Well, compared to the solution it replaced it works extraordinarily well - but that solution was a mess of plpgsql functions generating other plpgsql functions - so shining in comparison doesn't really prove much :-) I think it's pretty cool. Is it safe to have the main functions immutable and not stable though? I think it's safe - if a table or composite type is modified, a query using that table or type will have to be re-planned anyway, independent from whether fieldvalue() is used or not. Is there any benefit missed by not going through pl/pgsql directly (I'm guessing maybe more elegant caching)? AFAIK in pl/pgsql your only options to retrieve a field by name is to either use hstore which coerces all values to text, or to use EXECUTE 'SELECT %1' || v_fieldname INTO v_fieldvalue USING v_record. The execute query will need to be planned on every execution, while my fieldvalue() function tries to cache as much information as possible. The EXECUTE method will also always coerce the field's value to the type of v_fieldvalue - AFAICS there is no way to get the behaviour of fieldvalue() with coerce set to false. It's a little weird that you can return anyelement from your function in cases that don't guarantee a type from the query. Are there any downsides to doing that? Hm, the type of fieldvalue()'s return value is always the same as the one of the ANYELEMENT input value defvalue. If coerce is true, then the field value's type may be different, but fieldvalue() takes care of coercing it to defvalue's type *before* returning it. So from a type system's perspective, fieldvalue() plays entirely by the rules. The only open issue in my code is the caching of the coercion plans - currently, they're cached in fcinfo-flinfo-fn_extra, and never invalidated. I believe the plan invalidation machinery might make it possible to invalidate those plans should the CAST definitions change, but I haven't really looked into that yet. best regards, Florian Pflug -- 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] Partitioning syntax
On Wed, Mar 17, 2010 at 01:55:45PM +0600, Dmitry Fefelov wrote: Here is a revised partitioning syntax patch. It implements only syntax and on-disk structure mentioned below: Table Partitioning#Syntax http://wiki.postgresql.org/wiki/Table_partitioning#Syntax Table Partitioning#On-disk structure http://wiki.postgresql.org/wiki/Table_partitioning#On-disk_structure Will 9.1 partitions allow to reference partitioned tables in foreign keys? For now, you can do something like this: http://people.planetpostgresql.org/dfetter/index.php?/archives/51-Partitioning-Is-Such-Sweet-Sorrow.html Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQftype implementation
Pavel Golub pa...@microolap.com writes:
Here I created user-defined type my_varchar for internal tests. But
PQftype returns 1043 (varchar oid) for the info column.
Really? I tried it and got 172069, which is about right for where the
OID counter is in my database. I think you messed up your test.
res = PQexec(conn, select * from my_varchar_test);
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, SELECT failed: %s, PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
nFields = PQnfields(res);
for (i = 0; i nFields; i++)
printf(%-15s %d\n, PQfname(res, i), PQftype(res, i));
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] Bug in 9.0Alpha4
Hitoshi Harada umi.tan...@gmail.com writes: 2010/3/17 Tom Lane t...@sss.pgh.pa.us: When we were doing the ordered-aggregates patch, I considered passing all those values as explicit parameters to transformAggregateCall, and having it build the Aggref node from scratch and return it. However having seven or eight parameters to transformAggregateCall (and more in future if we ever add more features here) didn't really seem to be better style than abusing Aggref a bit. But maybe it is the best way after all. Thoughts? Well, I think the point is args and aggorder are hidden in the Aggref passed to transformAggregateCall, although they will be transformed in the function. Isn't it enough to add more parameters for them (agg_distinct is passed separately) and to leave the Aggref pointer passing as present? Yeah, that's probably the least complicated solution. Will fix. 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] Getting to beta1
All, A user at SFPUG last night pointed out why we should release a beta, rather than an alpha, sooner rather than later: because there are no Windows packages for Alphas. Currently, our Windows users are *not* testing 9.0. Which means we're just putting off the day when we hear about Windows-specific bugs. -- -- 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: [HACKERS] Getting to beta1
On Wed, Mar 17, 2010 at 4:54 PM, Josh Berkus j...@agliodbs.com wrote: All, A user at SFPUG last night pointed out why we should release a beta, rather than an alpha, sooner rather than later: because there are no Windows packages for Alphas. Yes there: http://www.enterprisedb.com/products/pgdevdownload.do We've produced them since Alpha 2 iirc. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Getting to beta1
Yes there: http://www.enterprisedb.com/products/pgdevdownload.do We've produced them since Alpha 2 iirc. Oh! Most people don't know about these ... I need to advertise them! BTW, at SFPUG there were reports of some kind of issue with the One-Click installer for 8.4.3. Is that resolved, or is this the first time you've heard about 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: [HACKERS] Getting to beta1
On Wed, Mar 17, 2010 at 5:02 PM, Josh Berkus j...@agliodbs.com wrote: Yes there: http://www.enterprisedb.com/products/pgdevdownload.do We've produced them since Alpha 2 iirc. Oh! Most people don't know about these ... I need to advertise them! They're linked from here, which you may want to update as it was written in the pre-alpha days: http://www.postgresql.org/download/snapshots BTW, at SFPUG there were reports of some kind of issue with the One-Click installer for 8.4.3. Is that resolved, or is this the first time you've heard about it? Not aware of any issues - certainly none cropped up in QA. In fact, this release should fix one of the long standing initdb failures we see occasionally on some secure environments. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com PG East Conference: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Getting to beta1
Not aware of any issues - certainly none cropped up in QA. In fact, this release should fix one of the long standing initdb failures we see occasionally on some secure environments. OK, I'll ask on our mailing list. -- -- 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
[HACKERS] Order of pg_stat_activity timestamp columns
Has anyone ever noticed that the order of pg_stat_activity timestamp columns is illogical: xact_start | timestamp with time zone | query_start | timestamp with time zone | backend_start| timestamp with time zone | query_start is always between the other two timestamps. Moving query_start before xact_start would make the most sense. I wouldn't bring this up except we just added application_name before these columns, so we are already going to have different column locations for these fields in 9.0. Should we move query_start? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] An idle thought
On Tue, 2010-03-16 at 15:29 +, Greg Stark wrote: big batch delete Is one of the reasons for partitioning, allowing the use of truncate. -- 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] Order of pg_stat_activity timestamp columns
On Wed, Mar 17, 2010 at 21:42, Bruce Momjian br...@momjian.us wrote: Has anyone ever noticed that the order of pg_stat_activity timestamp columns is illogical: xact_start | timestamp with time zone | query_start | timestamp with time zone | backend_start | timestamp with time zone | Well, 7.4 had only query start. 8.1 added backend. 8.3 added transaction. So I guess my original guess that things were just added on the end was wrong :-) query_start is always between the other two timestamps. Moving query_start before xact_start would make the most sense. I wouldn't bring this up except we just added application_name before these columns, so we are already going to have different column locations for these fields in 9.0. Should we move query_start? Or perhaps we should consider moving application_name to the end so it *doesn't* break them? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting to beta1
On Sat, 2010-03-13 at 11:26 -0800, Josh Berkus wrote: The list has been reduced greatly in the past week. What about HS/SR open items? I'd like to see vacuum_defer_cleanup_age added to the Archive section of postgresql.conf, Not all parameters are in postgresql.conf.sample. Encouraging people to do this is the wrong approach. and add it to the docs (I'll write something this week). It's already in the docs, so if they read it and understand it they can add it to the postgresql.conf if they so choose. I'd like to get rid of the associated hint-bits bogus error messsage. What are you talking about here? -- 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] Standalone backends run StartupXLOG in an incorrect environment
On Sat, 2009-09-19 at 13:21 -0400, Tom Lane wrote: I realized the truth of $SUBJECT while reading this report: http://archives.postgresql.org/pgsql-general/2009-09/msg00712.php ... Also, does this have any impact on the Hot Standby stuff? It could potentially, but there is not much HS-related code in the early boot sequence. It's mostly StartupXLog() or later. HS won't work in standalone backends, by definition, so I don't see too much to worry me. [This is an open item for 9.0, hence the response to an apparently old hackers thread] -- 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] Order of pg_stat_activity timestamp columns
Magnus Hagander wrote: On Wed, Mar 17, 2010 at 21:42, Bruce Momjian br...@momjian.us wrote: Has anyone ever noticed that the order of pg_stat_activity timestamp columns is illogical: ?xact_start ? ? ? | timestamp with time zone | ?query_start ? ? ?| timestamp with time zone | ?backend_start ? ?| timestamp with time zone | Well, 7.4 had only query start. 8.1 added backend. 8.3 added transaction. So I guess my original guess that things were just added on the end was wrong :-) query_start is always between the other two timestamps. ?Moving query_start before xact_start would make the most sense. ?I wouldn't bring this up except we just added application_name before these columns, so we are already going to have different column locations for these fields in 9.0. Should we move query_start? Or perhaps we should consider moving application_name to the end so it *doesn't* break them? That's a possibility, but we obviously have been adding columns out-of-order for several releases now and no one has complained. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] An idle thought
On Tue, 2010-03-16 at 15:29 +, Greg Stark wrote: I'm picturing storing a bit in the visibility map indicating that *no* records are visible in a given page. I've been thinking for a while that we could store the visibility information in a structure separate from the heap -- sort of like the visibility map, but per-tuple and authoritative rather than a per-page hint. There are all kinds of challenges there, but it might be worth thinking about. Visibility information is highly compressible, and requires constant maintenance (updates, deletes, freezing, etc.). It also might make it possible to move to 64-bit xids, if we wanted to. Regards, Jeff Davis -- 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
Bruce Momjian br...@momjian.us writes: That's a possibility, but we obviously have been adding columns out-of-order for several releases now and no one has complained. On balance I'm for rationalizing this. The query_start time is logically associated with current_query and waiting, so it ought to be next to them. Without the historical fact that we've mucked with the column ordering before, I might've voted differently. 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
Bruce Momjian br...@momjian.us wrote: xact_start | timestamp with time zone | query_start | timestamp with time zone | backend_start| timestamp with time zone | Should we move query_start? It would scan better, to my mind, if we moved backend_start ahead of xact_start. And paint it red. -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] An idle thought
Jeff Davis pg...@j-davis.com writes: There are all kinds of challenges there, but it might be worth thinking about. Visibility information is highly compressible, and requires constant maintenance (updates, deletes, freezing, etc.). It also might make it possible to move to 64-bit xids, if we wanted to. If you want it to be cheaply updatable (or even cheaply readable), compression is not what you're going to do. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Order of pg_stat_activity timestamp columns
Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: xact_start | timestamp with time zone | query_start | timestamp with time zone | backend_start| timestamp with time zone | Should we move query_start? It would scan better, to my mind, if we moved backend_start ahead of xact_start. Yes, that is another idea that would work, though Tom's idea that the query start should be near the query makes sense. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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
Kevin Grittner kevin.gritt...@wicourts.gov writes: It would scan better, to my mind, if we moved backend_start ahead of xact_start. 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) Putting backend_start first doesn't fit at all with that view of the grouping. xact_start is sort of a borderline case, although one could imagine that it might someday grow some friends and become a full-fledged current transaction info grouping. So I'd prefer to see it in between the columns that are clearly #2 and those that are clearly #3. If you believe that argument, there is a case for moving procpid into group #3. I'm more hesitant to mess with the columns that have always been there than those that got added in more recent releases, though. It's possible also that some people might consider procpid as an identity (key) column, in which case it's okay where it is. 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] Getting to beta1
On Mon, 2010-03-15 at 18:20 +0900, Fujii Masao wrote: On Sat, Mar 13, 2010 at 12:28 PM, Bruce Momjian br...@momjian.us wrote: Where are we in getting to beta1? I know people are looking to me for 9.0 release notes and I will have them done in about a week, but what about open issues? I don't see many on the main 9.0 open items page: http://wiki.postgresql.org/wiki/PostgreSQL_9.0_Open_Items#Bugs The list has been reduced greatly in the past week. What about HS/SR open items? I think that at least the following item should be addressed before beta1 I think all the open items should be addressed before beta. -- 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] Order of pg_stat_activity timestamp columns
Bruce Momjian br...@momjian.us wrote: Kevin Grittner wrote: It would scan better, to my mind, if we moved backend_start ahead of xact_start. Yes, that is another idea that would work, though Tom's idea that the query start should be near the query makes sense. Well, in an ideal world, I would put the current_query column at the end, so that long queries wouldn't make it hard to see the other values. I think I'd want to squeeze waiting in between the timestamps and the query. I would generally want items to be close together if related and farther down the field list if they were more volatile. For example, since application_name can be changed but client_* values can't, I'd put application_name later -- possibly right before the timestamps. If we're willing to re-order the existing columns, why not try to make the whole thing sane? -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] An idle thought
On Wed, 2010-03-17 at 17:20 -0400, Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: There are all kinds of challenges there, but it might be worth thinking about. Visibility information is highly compressible, and requires constant maintenance (updates, deletes, freezing, etc.). It also might make it possible to move to 64-bit xids, if we wanted to. If you want it to be cheaply updatable (or even cheaply readable), compression is not what you're going to do. I didn't mean that we'd want to compress it to the absolute minimum size. I had envisioned that it would be a simple scheme designed only to eliminate long runs of identical visibility information (perhaps only the frozen and always visible regions would be compressed). The extra level of indirection would be slower, but if we freeze tuples more aggressively (which would be much cheaper if we didn't have to go to the heap), there might be a small number of tuples with interesting visibility information at any particular time. Regards, Jeff Davis -- 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
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. -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] Order of pg_stat_activity timestamp columns
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? 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
On Wed, Mar 17, 2010 at 05:47:49PM -0400, 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? Grouping these this way will help a lot more people, namely the future ones, than it can possibly confuse :) Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS]
http://uriel.edu.mx/lN6qt08X2v.html _ IM on the go with Messenger on your phone http://go.microsoft.com/?linkid=9712960
Re: [HACKERS] An idle thought
On Wed, 2010-03-17 at 14:09 -0700, Jeff Davis wrote: I've been thinking for a while that we could store the visibility information in a structure separate from the heap -- sort of like the visibility map, but per-tuple and authoritative rather than a per-page hint. A lot of people have been thinking that for a while, but I've yet to see a fully costed assessment of whether its worth doing. It might be. It seems easier to focus on outcomes rather than specifics, then order the possible solutions to those outcomes in cost-benefit sequence. -- 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] Order of pg_stat_activity timestamp columns
Tom Lane wrote: 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 think most reports have the stable information first, and the more dynamic information at the end, so reordering it this way does make sense. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Getting to beta1
Simon Riggs wrote: On Sat, 2010-03-13 at 11:26 -0800, Josh Berkus wrote: The list has been reduced greatly in the past week. What about HS/SR open items? I'd like to see vacuum_defer_cleanup_age added to the Archive section of postgresql.conf, Not all parameters are in postgresql.conf.sample. Encouraging people to do this is the wrong approach. and add it to the docs (I'll write something this week). It's already in the docs, so if they read it and understand it they can add it to the postgresql.conf if they so choose. I agree with Josh Berkus that vacuum_defer_cleanup_age should be in postgresql.conf. We don't stop listing items just because they are dangerous, e.g. fsync, or to discourage their use. I believe Greg Smith also felt it should be included. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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')
Bruce Momjian br...@momjian.us wrote: Takahiro Itagaki wrote: Since 9.0 has GetPlatformEncoding() for the purpose, we could simplify db_encoding_strdup() with the function. Like this: OK, I don't have any Win32 people testing this patch so if we want this fixed for 9.0 someone is going to have to test my patch to see that it works. Can you make the adjustments suggested above to my patch and test it to see that it works so we can apply it for 9.0? Here is a full patch that can be applied cleanly to HEAD. Can anyone test it on Windows? I'm not sure why temporary changes of lc_ctype was required in the original patch. The codes are not included in my patch, but please notice me it is still needed. Regards, --- Takahiro Itagaki NTT Open Source Software Center pg_locale_20100318.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] Getting to beta1
Bruce Momjian wrote: Simon Riggs wrote: On Sat, 2010-03-13 at 11:26 -0800, Josh Berkus wrote: The list has been reduced greatly in the past week. What about HS/SR open items? I'd like to see vacuum_defer_cleanup_age added to the Archive section of postgresql.conf, Not all parameters are in postgresql.conf.sample. Encouraging people to do this is the wrong approach. and add it to the docs (I'll write something this week). It's already in the docs, so if they read it and understand it they can add it to the postgresql.conf if they so choose. I agree with Josh Berkus that vacuum_defer_cleanup_age should be in postgresql.conf. We don't stop listing items just because they are dangerous, e.g. fsync, or to discourage their use. I believe Greg Smith also felt it should be included. The bottom line is that the fact that vacuum_defer_cleanup_age is missing from postgresql.conf is causing confusion because none of the other settings are skipped to discourage their use. If you want to apply that policy, we would have to revisit all the postgresql.conf settings, and I don't think there is much interest in doing that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- 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] Getting to beta1
On Wed, Mar 17, 2010 at 11:42 PM, Bruce Momjian br...@momjian.us wrote: Bruce Momjian wrote: Simon Riggs wrote: On Sat, 2010-03-13 at 11:26 -0800, Josh Berkus wrote: The list has been reduced greatly in the past week. What about HS/SR open items? I'd like to see vacuum_defer_cleanup_age added to the Archive section of postgresql.conf, Not all parameters are in postgresql.conf.sample. Encouraging people to do this is the wrong approach. and add it to the docs (I'll write something this week). It's already in the docs, so if they read it and understand it they can add it to the postgresql.conf if they so choose. I agree with Josh Berkus that vacuum_defer_cleanup_age should be in postgresql.conf. We don't stop listing items just because they are dangerous, e.g. fsync, or to discourage their use. I believe Greg Smith also felt it should be included. The bottom line is that the fact that vacuum_defer_cleanup_age is missing from postgresql.conf is causing confusion because none of the other settings are skipped to discourage their use. If you want to apply that policy, we would have to revisit all the postgresql.conf settings, and I don't think there is much interest in doing that. I agree. If we're going to have the option, it should be in the file. ...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] Partitioning syntax
Will 9.1 partitions allow to reference partitioned tables in foreign keys? For now, you can do something like this: http://people.planetpostgresql.org/dfetter/index.php?/archives/51- Partitioning-Is-Such-Sweet-Sorrow.html Cheers, David. Already did ;) But workable plain references will be useful. Regards, Dmitry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
