Re: [HACKERS] executing OS programs from pg
Gevik babakhani wrote: Dear people, Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Does such functionality exist or do I have to write my own trigger function in C. Reagrds, Gevik. Gevik, Do something like that ... CREATE OR REPLACE FUNCTION xclock() RETURNS int4 AS ' system(xclock); return 1; ' LANGUAGE 'plperlu'; This should be fairly easy to implement but recall - you cannot rollback xclock ;). best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] executing OS programs from pg
Try the PL/sh project on www.pgfoundry.org. Chris Gevik babakhani wrote: Dear people, Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Does such functionality exist or do I have to write my own trigger function in C. Reagrds, Gevik. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Do we force dependency?
How do we force the dependency according to pg_dependency records? Seems pg_dependency just records them and we follow the records by hand on create or delete ... Regards, Qingqing ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Precedence of %
I wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Does anyone understand why the precedence of % is strange:
test= select -25 % -10;
It's treating it as ((-25) %) - (10), which is probably not so
surprising given the relative precedence of % and - ... though
I have to admit I'm not totally clear why it's not (-(25 %)) - (10)
instead.
Now that I'm fully awake, that last point is easily explained: the
precedence of unary minus is higher than that of %, which in turn
is higher than that of infix minus. So the choice of (-25) % over
-(25 %) is reasonable and correct. Now when the parser is done with
that, it is on the % with a lookahead of - and has to decide whether
to reduce according to
| a_expr '%'
{ $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, %, $1, NULL); }
or shift expecting to later reduce by
| a_expr '%' a_expr
{ $$ = (Node *) makeSimpleA_Expr(AEXPR_OP, %, $1, $3); }
but the precedence of the '-' token is set up for infix minus so the
choice is to reduce (see the Bison manual).
We could possibly fix this by fooling with the precedence of the
productions for postfix '%', but I'm worried that that would have
unintended side-effects. What I'd like to propose instead is that
we remove prefix and postfix '%' entirely --- and also '^', which
is the only other hard-wired operator that appears in all three
forms in the grammar. There are no actual uses of prefix or postfix
'^' in pg_operator, so that loses us nothing. Prefix and postfix '%'
exist, but only for the float8 datatype, not anything else; and I
can't imagine a good reason to write those rather than trunc() or
round(). (Quick: which is which, and how would you remember?)
round() and trunc() also have the virtue that they already have versions
for type numeric. If we keep the operators then we'll be right back
with the complaint that was lodged the other day about exponentiation,
namely unexpected precision loss for numeric inputs:
regression=# select 12345678901234567890.55 %;
?column?
--
1.23456789012346e+19
(1 row)
regression=# select round(12345678901234567890.55);
round
--
12345678901234567891
(1 row)
Comments?
regards, tom lane
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] Precedence of %
Tom Lane wrote: We could possibly fix this by fooling with the precedence of the productions for postfix '%', but I'm worried that that would have unintended side-effects. What I'd like to propose instead is that we remove prefix and postfix '%' entirely --- and also '^', which is the only other hard-wired operator that appears in all three forms in the grammar. There are no actual uses of prefix or postfix '^' in pg_operator, so that loses us nothing. Prefix and postfix '%' exist, but only for the float8 datatype, not anything else; and I can't imagine a good reason to write those rather than trunc() or round(). (Quick: which is which, and how would you remember?) Agreed. I didn't know we even supported unary % and ~, and I doubt anyone else did either. We just need to mark it as a non-backward compatible change in CVS commit so I mention it in the release notes. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Precedence of %
round() and trunc() also have the virtue that they already have versions for type numeric. If we keep the operators then we'll be right back with the complaint that was lodged the other day about exponentiation, namely unexpected precision loss for numeric inputs: regression=# select 12345678901234567890.55 %; ?column? -- 1.23456789012346e+19 (1 row) I don't even grasp what unary modulo actually means??? Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Quick-and-dirty compression for WAL backup blocks
Mark Cave-Ayland [EMAIL PROTECTED] writes: A run-length compressor would be reasonably quick but I think that the omit-the-middle-hole approach gets most of the possible win with even less work. I can't think that a RLE scheme would be much more expensive than a 'count the hole' approach with more benefit, so I wouldn't like to discount this straight away... RLE would require scanning the whole page with no certainty of win, whereas count-the-hole is a certain win since you only examine bytes that are potentially removable from the later CRC calculation. If you do manage to go ahead with the code, I'd be very interested to see some comparisons in bytes written to XLog for old and new approaches for some inserts/updates. Perhaps we could ask Mark to run another TPC benchmark at OSDL when this and the CRC changes have been completed. I've completed a test run for this (it's essentially MySQL's sql-bench done immediately after initdb). What I get is: CVS tip of 6/1: ending WAL offset = 0/A364A780 = 2741282688 bytes written CVS tip of 6/2: ending WAL offset = 0/8BB091DC = 2343604700 bytes written or about a 15% savings. This is with a checkpoint_segments setting of 30. One can presume that the savings would be larger at smaller checkpoint intervals and smaller at larger intervals, but I didn't try more than one set of test conditions. I'd say that's an improvement worth having, especially considering that it requires no net expenditure of CPU time. But the table is certainly still open to discuss more complicated approaches. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Precedence of %
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I don't even grasp what unary modulo actually means??? At some point in the dim mists of prehistory, somebody thought it would be cute to define prefix % as trunc() and postfix % as round(). I'm not aware of any precedent for that; it was probably mostly an exercise in testing out the grammar. Now that I look, it doesn't look like these operators are documented at all in the SGML docs, so it sure seems that removing them should be pretty painless. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Precedence of %
On Jun 5, 2005, at 12:55 AM, Tom Lane wrote: Now that I look, it doesn't look like these operators are documented at all in the SGML docs, so it sure seems that removing them should be pretty painless. I wonder what else is lurking around undocumented and unused? Might be some other nuggets just waiting to be discovered! :) Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Do we force dependency?
Qingqing Zhou [EMAIL PROTECTED] writes: How do we force the dependency according to pg_dependency records? Seems pg_dependency just records them and we follow the records by hand on create or delete ... Deletion scans the entries and either deletes the dependent objects or raises error. It's not by hand particularly, at least not for anything outside dependency.c. If you were to write code that deleted objects directly without going through the dependency mechanism, it wouldn't get accepted ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Precedence of %
Michael Glaesemann [EMAIL PROTECTED] writes: On Jun 5, 2005, at 12:55 AM, Tom Lane wrote: Now that I look, it doesn't look like these operators are documented at all in the SGML docs, so it sure seems that removing them should be pretty painless. I wonder what else is lurking around undocumented and unused? AFAIK, no one has ever gone through pg_proc and pg_operator systematically to determine that every entry is either (a) documented or (b) undocumented for definable reasons. We generally don't document functions separately if they are accessible by a well-used operator; for instance you're supposed to write 2+2 not int4pl(2,2). And stuff that's supposed to be used only internally by the system, such as index access method support functions, doesn't need to be listed. But I wouldn't be at all surprised if some entries have just fallen through the cracks. Anyone want to take on this bit of legwork? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PGDN source browser
Dear all, The PostgreSQL Developer Networks Source Browser (beta1) is ready. If you got the time to check it for a moment, please do not hesitate to send your opinion. Regards, Gevik PGDN can be found at http://www.truesoftware.net/pgdn/
Re: [HACKERS] Added schema selection to pg_restore
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.
---
Richard van den Berg wrote:
Since I needed this feature badly, I added the -n / --schema switch to
pg_restore. It restores the given schemaname only. It can be used in
conjunction with the -t and other switches to make the selection very
fine grained.
This patches works for me, but it could use more testing.
Please Cc me in the discussion, as I am not on these lists.
I used the current CVS tree at
:pserver:[EMAIL PROTECTED]:/projects/cvsroot as a starting
point.
Sincerely,
--
Richard van den Berg, CISSP
Trust Factory B.V. | http://www.trust-factory.com/
Bazarstraat 44a | Phone: +31 70 3620684
NL-2518AK The Hague | Fax : +31 70 3603009
The Netherlands |
Index: doc/src/sgml/ref/pg_restore.sgml
===
RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.47
diff -c -r1.47 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml 13 Jul 2004 02:59:49 - 1.47
--- doc/src/sgml/ref/pg_restore.sgml 19 Aug 2004 13:22:27 -
***
*** 228,233
--- 228,244
/varlistentry
varlistentry
+ termoption-n replaceable
class=parameternamespace/replaceable/option/term
+ termoption--schema=replaceable
class=parameterschema/replaceable/option/term
+ listitem
+para
+ Restore definition and/or data of named schema only. Not to be
+ confused with the --schema-only option.
+/para
+ /listitem
+ /varlistentry
+
+ varlistentry
termoption-O/option/term
termoption--no-owner/option/term
listitem
Index: src/bin/pg_dump/pg_backup.h
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup.h,v
retrieving revision 1.31
diff -c -r1.31 pg_backup.h
*** src/bin/pg_dump/pg_backup.h 13 Jul 2004 03:00:17 - 1.31
--- src/bin/pg_dump/pg_backup.h 19 Aug 2004 13:22:27 -
***
*** 94,99
--- 94,100
char *indexNames;
char *functionNames;
char *tableNames;
+ char *schemaNames;
char *triggerNames;
int useDB;
Index: src/bin/pg_dump/pg_backup_archiver.c
===
RCS file:
/projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.92
diff -c -r1.92 pg_backup_archiver.c
*** src/bin/pg_dump/pg_backup_archiver.c 13 Aug 2004 21:37:28 -
1.92
--- src/bin/pg_dump/pg_backup_archiver.c 19 Aug 2004 13:22:28 -
***
*** 1927,1932
--- 1927,1940
/* Check if tablename only is wanted */
if (ropt-selTypes)
{
+ if (ropt-schemaNames)
+ {
+ /* No namespace but namespace requested means we will
not include it */
+ if (!te-namespace)
+ return 0;
+ if(strcmp(ropt-schemaNames, te-namespace) != 0)
+ return 0;
+ }
if ((strcmp(te-desc, TABLE) == 0) || (strcmp(te-desc,
TABLE DATA) == 0))
{
if (!ropt-selTable)
Index: src/bin/pg_dump/pg_restore.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/pg_dump/pg_restore.c,v
retrieving revision 1.59
diff -c -r1.59 pg_restore.c
*** src/bin/pg_dump/pg_restore.c 13 Jul 2004 03:00:17 - 1.59
--- src/bin/pg_dump/pg_restore.c 19 Aug 2004 13:22:28 -
***
*** 103,108
--- 103,109
{no-reconnect, 0, NULL, 'R'},
{port, 1, NULL, 'p'},
{password, 0, NULL, 'W'},
+ {schema, 1, NULL, 'n'},
{schema-only, 0, NULL, 's'},
{superuser, 1, NULL, 'S'},
{table, 1, NULL, 't'},
***
*** 141,147
}
}
! while ((c = getopt_long(argc, argv,
acCd:f:F:h:iI:lL:Op:P:RsS:t:T:uU:vWxX:,
cmdopts, NULL)) != -1)
{
switch (c)
--- 142,148
}
}
! while ((c = getopt_long(argc, argv,
acCd:f:F:h:iI:lL:n:Op:P:RsS:t:T:uU:vWxX:,
cmdopts, NULL)) != -1)
{
switch (c)
***
*** 220,225
--- 221,231
Re: [HACKERS] [GENERAL] Rollback on Error
This has already been implemented in CVS as a psql \set variable: ON_ERROR_ROLLBACK = 'interactive' and will appear in 8.1. --- Michael Paesold wrote: Tom Lane wrote: Michael Paesold [EMAIL PROTECTED] writes: On the other hand, the scenario of a psql option (read: I have given up the idea of a backend implementation) to rollback only last statement on error is quite different. Sure (and we already have one for autocommit). But I thought you were asking about a backend implementation. I have implemented what I have suggested for psql. I have attached a first patch for review here, because I have a few questions. Also I want to make sure the whole thing is reasonable. I have named the option IMPLICIT_SAVEPOINTS, because that's what it is. If someone has a better name that would describe the purpose of the feature, I am happy to change it. The feature is activated, if * \set IMPLICIT_SAVEPOINTS 'on' * connection is in idle in transaction state * psql session is interactive The code executes an implicit SAVEPOINT pg_internal_psql in common.c/SendQuery to which it will try to rollback to, if the executed query fails. Open questions: * Should psql print a notice in the case of that rollback? Something like Rollback of last statement successful.? * What is currently missing, is a detection of \i ... obviously this feature should not be used for each query in \i. Perhaps only for the whole \i command? So what should I do to detect \i? Add an extra argument to MainLoop, SendQuery and process_file()? (many changes) Add a global variable in common.c/h (e.g. bool deactivate_implicit_savepoints) that can be used in process_file to temporarily deactivate the code path? (more local changes, but rather a hack imho) Please have a look at the patch and comment. Best Regards, Michael Paesold [ Attachment, skipping... ] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_upgrade project: high-level design proposal of in-place
I have added a link to this thread on the TODO list under TODO.detail. --- Serguei A. Mokhov wrote: Hello dear all, [Please CC your replies to me as I am on the digest mode] Here's finally a very high-level design proposal of the pg_upgrade feature I was handwaiving a couple of weeks ago. Since, I am almost done with the moving, I can allocate some time for this for 8.1/8.2. If this topic is of interest to you, please read on until the very end before flaming or bashing the ideas out. I had designed that thing and kept updating (the design) more or less regularly, and also reflected some issues from the nearby threads [1] and [2]. This design is very high-level at the moment and is not very detailed. I will need to figure out more stuff as I go and design some aspects in finer detail. I started to poke around asking for initdb-forcing code paths in [3], but got no response so far. But I guess if the general idea or, rather, ideas accepted I will insist on more information more aggressively :) if I can't figure something out for myself. [1] http://archives.postgresql.org/pgsql-hackers/2004-09/msg0.php [2] http://archives.postgresql.org/pgsql-hackers/2004-09/msg00382.php [3] http://archives.postgresql.org/pgsql-hackers/2004-08/msg01594.php Comments are very welcome, especially _*CONSTRUCTIVE*_... Thank you, and now sit back and read... CONTENTS: = 1. The Need 1. Utilities and User's View of the pg_upgrade Feature 2. Storage Management - Storage Managers and the smgr API 3. Source Code Maintenance Aspects 2. The Upgrade Sequence 4. Proposed Implementation Plan - initdb() API - upgrade API 1. The Need --- It's been a problem for PG for quite awhile now to have a less painful upgrade procedure with every new revision of PostgreSQL, so the dump/restore sequence is required. That can take a while for a production DB, while keeping it offline. The new replication-related solutions, such as Slony I, pg_pool, and others can remedy the problem somewhat, but require to roughly double the storage requirements of a given database while replicating from the older server to a newer one. The proposed implementation of an in-server pg_upgrade facility attempts to address both issues at the same time -- a possibility to keep the server running and upgrading lazily w/o doubling the storage requirements (there will be some extra disk space taken, but far from doubling the size). The in-process upgrade will not take much of down time and won't require that much memory/disk/network resources as replication solutions do. Prerequisites - Ideally, the (maybe not so anymore) ambitious goal is to simply be able to drop in the new binaries of the new server and kick off on the older version of data files. I think is this feasible now a lot more than before since we have those things available, which should ease up the implementation: - bgwriter - pg_autovacuum (the one to be integrated into the backend in 8.1) - smgr API for pluggable storage managers - initdb in C - ... initdb in C, bgwriter and pg_autovacuum, and pluggable storage manager have made the possibility of creation of the Upgrade Subsystem for PostgreSQL to be something more reasonable, complete, feasible, and sane to a point. Utilities and the User's (DBA) View of the Feature -- Two instances exist: pg_upgrade (in C) A standalone utility to upgrade the binary on-disk format from one version to another when the database is offline. We should always have this as an option. pg_upgrade will accept sub/super set of pg_dump(all)/pg_restore options that do not require a connection. I haven't thought through this in detail yet. pg_autoupgrade a postgres subprocess, modeled after bgwriter and pg_autovacuum daemons. This will work when the database system is running on old data directory, and lazily converting relations to the new format. pg_autoupgrade daemon can be triggered by the following events in addition to the lazy upgrade process: SQL level: UPGRADE ALL | relation_name [, relation_name] [NOW | time] While the database won't be offline running over older database files, SELECT/read-only queries would be allowed using older storage managers*. Any write operation on old data will act using write-invalidate approach that will force the upgrade the affected relations to the new format to be scheduled after the relation-in-progress. (* See the Storage Management section.) Availability of the relations while upgrade is in progress is likely to be the same as in VACUUM FULL for that relation, i.e. the entire relation is locked until the upgrade is complete. Maybe we could
Re: [HACKERS] pg_upgrade project: high-level design proposal of
On Sat, 4 Jun 2005, Bruce Momjian wrote: Date: Sat, 4 Jun 2005 19:33:40 -0400 (EDT) I have added a link to this thread on the TODO list under TODO.detail. Thanks Bruce (and Josh Berkus) for reminding me about this. I should be able to follow this up in early July. -s Serguei A. Mokhov wrote: Hello dear all, [Please CC your replies to me as I am on the digest mode] Here's finally a very high-level design proposal of the pg_upgrade feature I was handwaiving a couple of weeks ago. Since, I am almost done with the moving, I can allocate some time for this for 8.1/8.2. If this topic is of interest to you, please read on until the very end before flaming or bashing the ideas out. I had designed that thing and kept updating (the design) more or less regularly, and also reflected some issues from the nearby threads [1] and [2]. This design is very high-level at the moment and is not very detailed. I will need to figure out more stuff as I go and design some aspects in finer detail. I started to poke around asking for initdb-forcing code paths in [3], but got no response so far. But I guess if the general idea or, rather, ideas accepted I will insist on more information more aggressively :) if I can't figure something out for myself. [1] http://archives.postgresql.org/pgsql-hackers/2004-09/msg0.php [2] http://archives.postgresql.org/pgsql-hackers/2004-09/msg00382.php [3] http://archives.postgresql.org/pgsql-hackers/2004-08/msg01594.php Comments are very welcome, especially _*CONSTRUCTIVE*_... Thank you, and now sit back and read... CONTENTS: = 1. The Need 1. Utilities and User's View of the pg_upgrade Feature 2. Storage Management - Storage Managers and the smgr API 3. Source Code Maintenance Aspects 2. The Upgrade Sequence 4. Proposed Implementation Plan - initdb() API - upgrade API 1. The Need --- It's been a problem for PG for quite awhile now to have a less painful upgrade procedure with every new revision of PostgreSQL, so the dump/restore sequence is required. That can take a while for a production DB, while keeping it offline. The new replication-related solutions, such as Slony I, pg_pool, and others can remedy the problem somewhat, but require to roughly double the storage requirements of a given database while replicating from the older server to a newer one. The proposed implementation of an in-server pg_upgrade facility attempts to address both issues at the same time -- a possibility to keep the server running and upgrading lazily w/o doubling the storage requirements (there will be some extra disk space taken, but far from doubling the size). The in-process upgrade will not take much of down time and won't require that much memory/disk/network resources as replication solutions do. Prerequisites - Ideally, the (maybe not so anymore) ambitious goal is to simply be able to drop in the new binaries of the new server and kick off on the older version of data files. I think is this feasible now a lot more than before since we have those things available, which should ease up the implementation: - bgwriter - pg_autovacuum (the one to be integrated into the backend in 8.1) - smgr API for pluggable storage managers - initdb in C - ... initdb in C, bgwriter and pg_autovacuum, and pluggable storage manager have made the possibility of creation of the Upgrade Subsystem for PostgreSQL to be something more reasonable, complete, feasible, and sane to a point. Utilities and the User's (DBA) View of the Feature -- Two instances exist: pg_upgrade (in C) A standalone utility to upgrade the binary on-disk format from one version to another when the database is offline. We should always have this as an option. pg_upgrade will accept sub/super set of pg_dump(all)/pg_restore options that do not require a connection. I haven't thought through this in detail yet. pg_autoupgrade a postgres subprocess, modeled after bgwriter and pg_autovacuum daemons. This will work when the database system is running on old data directory, and lazily converting relations to the new format. pg_autoupgrade daemon can be triggered by the following events in addition to the lazy upgrade process: SQL level: UPGRADE ALL | relation_name [, relation_name] [NOW | time] While the database won't be offline running over older database files, SELECT/read-only queries would be allowed using older storage managers*. Any write operation on old data will act using write-invalidate approach that will force the upgrade the affected relations to the new format to be scheduled after the relation-in-progress. (* See the Storage Management
Re: [HACKERS] timestamp with time zone a la sql99
This thread has been added as a link on the TODO list under TODO.detail.
---
Dennis Bjorklund wrote:
I've made a partial implementation of a datatype timestamp with time
zone as described in the sql standard. The current type timestamptz
does not store the time zone as a standard one should do. So I've made a
new type I've called timestampstdtz that does store the time zone as the
standard demands.
Let me show a bit of what currently works in my implementation:
dennis=# CREATE TABLE foo (
a timestampstdtz,
primary key (a)
);
dennis=# INSERT INTO foo VALUES ('1993-02-04 13:00 UTC');
dennis=# INSERT INTO foo VALUES ('1999-06-01 14:00 CET');
dennis=# INSERT INTO foo VALUES ('2003-08-21 15:00 PST');
dennis=# SELECT a FROM foo;
a
1993-02-04 13:00:00+00
1999-06-01 14:00:00+01
2003-08-21 15:00:00-08
dennis=# SELECT a AT TIME ZONE 'CET' FROM foo;
timezone
1993-02-04 14:00:00+01
1999-06-01 14:00:00+01
2003-08-22 00:00:00+01
My plan is to make a GUC variable so that one can tell PG that constructs
like timestamp with time zone will map to timestampstdtz instead of
timestamptz (some old databases might need the old so unless we want to
break old code this is the easiest solution I can find).
I've made an implicit cast from timestampstdtz to timestamptz that just
forgets about the time zone. In the other direction I've made an
assignment cast that make a timestamp with time zone 0 (that's what a
timestamptz is anyway). Would it be possible to make it implicit in both
directions? I currently don't think that you want that, but is it
possible?
With the implicit cast in place I assume it would be safe to change
functions like now() to return a timestampstdtz? I've not tried yet but I
will. As far as I can tell the cast would make old code that use now() to
still work as before.
Any comments before I invest more time into this subject?
--
/Dennis Bj?rklund
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] WAL bypass for CTAS
Neil Conway wrote: Bruce Momjian wrote: Well, it isn't going to help us for 8.1 because 8.0 will not have it, and if we add the clause we make loading the data into previous releases harder. pg_dump output in general is not compatible with prior releases. It would be a nice feature to have, but until we have it, I don't see that changing or not changing the COPY syntax will make a major difference to dump backward compatibility. Right, usually the schema changes are not backward compatibible, but the COPY commands are. But now that I look at this example: COPY test (x) FROM stdin; 1 \. The column name (x) actually broke backward compatibility when we added it, so yea, we could add a new option now too. No one complained when we added the column names, so another option would be fine. I suppose no one would like adding an option to turn off locking during COPY, so the non-WAL logging would become the default? (Just asking. You know me, I like automatic.) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Suggestion: additional system views
I do like this idea. Can you make a general patch? Do others like the idea of system tables showing error codes and keywords? --- David Fetter wrote: On Mon, Nov 01, 2004 at 12:49:47AM +0100, Gaetano Mendola wrote: Josh Berkus wrote: Neil, pg_functions might be useful, but what would pg_users offer that pg_user does not already do? Show a list of groups that the user belongs to? Same thing with pg_groups; showing the list of users in the group. A pg_sequences view might also be handy. Yes. Anything else? So far I have: pg_users pg_groups pg_functions pg_sequences hmmm ... pg_schemas pg_tablespaces ... as well, just for completeness. This is obviously and 8.1 thing, so I'll put it on my task list for after 8.0 PR is done. I suggest to add on pg_functions and on pg_views too, the list of dependencies with other objects. pg_keywords pg_sqlstates Attached is a rough draft of the latter. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! [ Attachment, skipping... ] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Two-phase commit
I have added this thread as a link on the TODO list under TODO.detail. I know folks are working on this for 8.1 but now they can get the discussions all in one place. --- Heikki Linnakangas wrote: I've been very slowly continuing my work on two-phase commits for a couple months now, and I now have my original patch updated so that it applies to the current CVS tip, with some improvements. The patch introduces three new commands, PREPCOMMIT, COMMITPREPARED and ABORTPREPARED. To start a 2PC transaction, you first do a BEGIN and your updates as usual. At the end of the transaction, you call PREPCOMMIT 'foobar' instead of COMMIT. Now the transaction is in prepared state, ready to commit at a later time. 'foobar' is the global transaction identifier assigned for the transaction. Later, when you want to finish the second phase, you call COMMITPREPARED 'foobar'; There is a system view pg_prepared_xacts that gives you all transactions that are in prepared state waiting for COMMITPREPARED or ABORTPREPARED. I have also done some work on XA-enabling the JDBC drivers, now that we have what it takes in the server side. I have succesfully executed 2PC transactions with JBossMQ and Postgres, using JBoss as the transaction manager, so the basic stuff seems to be working. Please have a look and comment, the patches can be found here: http://www.iki.fi/hlinnaka/pgsql/ What is the schedule for 7.5? Any chance of getting this in? - Heikki ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Solution proposal for TODO item Clients: sequences
It would be nice to add the current sequence value to \ds, but the existing psql query would have to be overhauled to do that. Right now the same query does all the work for most \d commands. --- Gevik Babakhani wrote: Dear People, Hereby a proposal for the TODO item Clients: Have psql show current values for a sequences. I have added a new slash command to psql client \sq for showing the last values of the existing sequences in the public schema. The code is only tested on rh9. The new files are sequence_info.c and sequence_info.h I would like to hear your comments. (Be gentle this is my first) The sources can be downloaded from http://www.truesoftware.net/psql/ Regards, Gevik SCREEN OUTPUT [EMAIL PROTECTED] psql]$ ./psql Welcome to psql 8.0.0beta5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit gevik=# \sq Current sequence values Sequence | Last value ---+ mytableid | 5 seq1 | 1 (2 rows) gevik=# ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
What did we decide on RESET CONNECTION. Do we want an SQL command or something only the protocol can do? --- Oliver Jowett wrote: (cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Bgwriter behavior
Later version of this patch added to the patch queue. Your patch has been added to the PostgreSQL unapplied patches list at: http://momjian.postgresql.org/cgi-bin/pgpatches It will be applied as soon as one of the PostgreSQL committers reviews and approves it. --- Simon Riggs wrote: On Sat, 2005-01-01 at 17:47, Simon Riggs wrote: On Sat, 2005-01-01 at 17:01, Bruce Momjian wrote: Simon Riggs wrote: Well, I think we're saying: its not in 8.0 now, and we take our time to consider patches for 8.1 and accept the situation that the parameter names/meaning will change in next release. I have no problem doing something for 8.0 if we can find something that meets all the items I mentioned. One idea would be to just remove bgwriter_percent. Beta/RC users would still have it in their postgresql.conf, but it is commented out so it should be OK. If they uncomment it their server would not start but we could just tell testers to remove it. I see that as better than having conflicting parameters. Can't say I like that at first thought. I'll think some more though... Another idea is to have bgwriter_percent be the percent of the buffer it will scan. Hmmmwell that was my original suggestion (bg2.patch on 12 Dec) (...though with a bug, as Neil pointed out) We could default that to 50% or 100%, but we then need to make sure all beta/RC users update their postgresql.conf with the new default because the commented-out default will not be correct. ...we just differ/ed on what the default should be... At this point I see these as our only two viable options, aside from doing nothing. I realize our current behavior requires a full scan of the buffer cache, but how often is the bgwriter_maxpages limit met? If it is not a full scan is done anyway, right? Well, if you heavy a very heavy read workload then that would be a problem. I was more worried about concurrency in a heavy write situation, but I can see your point, and agree. (Idea #1 still suffers from this, so we should rule it out...) It seems the only way to really add functionality is to change bgwriter_precent to control how much of the buffer is scanned. OK. I think you've persuaded me on idea #2, if I understand you right: bgwriter_percent = 50 (default) bgwriter_maxpages = 100 (default) percent is the number of shared_buffers we scan, limited by maxpages. (I'll code it up in a couple of hours when the kids are in bed) Here's the basic patch - no changes to current default values or docs. Not sure if this is still interesting or not... -- Best Regards, Simon Riggs [ Attachment, skipping... ] ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Server instrumentation for 8.1
Exactly. In theory it probably works fine to allow one backend to exit via kill -TERM, but it cannot be claimed that that behavior has been tested to any significant extent --- fast shutdown is not stressing it in the same way. I think this is largely a question of someone doing a significant amount of stress testing: gun live server processes with kill -TERM in an active system, and keep an eye out for resource leaks, held locks, and so on. It would be more convincing if the processes getting zapped are executing a wide variety of SQL, too --- I'd not feel very confident given only tests of killing, say, pgbench threads. Cause I know you wont be satisfied with anecdotal evidence, I thought I would just say that I have done kill's on specific backends in a high load OLTP process, with 1000+ active connections, for years and not had a problem with it yet. Not that I wouldn't like to see some specific, thorough testing on the matter, but I'm perfectly comfortable with the previously provided function. I've also used it regularly for a few years with 100 active connections in order to get rid of processes which were doing things they shouldn't be, and have run into problems. It seems about one out of every 20 kills of something holding a heavy lock (VACUUM, ALTER TABLE, etc.) will result in a lock table corruption being reported within the next few hours, although the pg_locks view doesn't show anything interesting, nor do the locks appear to persist as other processes can use the structures. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Server instrumentation for 8.1
Well, that's clear evidence that the only way we are going to be able to SIGTERM a backend is it does a query cancel first, then terminates. I don't think anything else is going to work cleanly. --- Rod Taylor wrote: Exactly. In theory it probably works fine to allow one backend to exit via kill -TERM, but it cannot be claimed that that behavior has been tested to any significant extent --- fast shutdown is not stressing it in the same way. I think this is largely a question of someone doing a significant amount of stress testing: gun live server processes with kill -TERM in an active system, and keep an eye out for resource leaks, held locks, and so on. It would be more convincing if the processes getting zapped are executing a wide variety of SQL, too --- I'd not feel very confident given only tests of killing, say, pgbench threads. Cause I know you wont be satisfied with anecdotal evidence, I thought I would just say that I have done kill's on specific backends in a high load OLTP process, with 1000+ active connections, for years and not had a problem with it yet. Not that I wouldn't like to see some specific, thorough testing on the matter, but I'm perfectly comfortable with the previously provided function. I've also used it regularly for a few years with 100 active connections in order to get rid of processes which were doing things they shouldn't be, and have run into problems. It seems about one out of every 20 kills of something holding a heavy lock (VACUUM, ALTER TABLE, etc.) will result in a lock table corruption being reported within the next few hours, although the pg_locks view doesn't show anything interesting, nor do the locks appear to persist as other processes can use the structures. -- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Precedence of %
Now that I look, it doesn't look like these operators are documented at all in the SGML docs, so it sure seems that removing them should be pretty painless. I'd agree with that Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] [HACKERS] WAL: O_DIRECT and multipage-writer (+
Yes, I assume that the patch to group the writes isn't something we want right now, and the one for O_DIRECT is going to need an additional fsync, and I have asked for testing on that. I have posted a patch that I think fixes the memory leak reported and am waiting for feedback on that. --- Simon Riggs wrote: On Tue, 2005-03-01 at 13:53 -0800, Mark Wong wrote: On Thu, Feb 03, 2005 at 07:25:55PM +0900, ITAGAKI Takahiro wrote: Hello everyone. I fixed two bugs in the patch that I sent before. Check and test new one, please. Ok, finally got back into the office and was able to run 1 set of tests. So the new baseline result with 8.0.1: http://www.osdl.org/projects/dbt2dev/results/dev4-010/309/ Throughput: 3639.97 Results with the patch but open_direct not set: http://www.osdl.org/projects/dbt2dev/results/dev4-010/308/ Throughput: 3494.72 Results with the patch and open_direct set: http://www.osdl.org/projects/dbt2dev/results/dev4-010/312/ Throughput: 3489.69 You can verify that the wall_sync_method is set to open_direct under the database parameters link, but I'm wondering if I missed something. It looks a little odd the the performance dropped. Is there anything more to say on this? Is it case-closed, or is there further work underway - I can't see any further chat on this thread. These results show it doesn't work better on larger systems. The original testing showed it worked better on smaller systems - is there still scope to include this for smaller configs? If not, thanks for taking the time to write the patch and investigate whether changes in this area would help. Not every performance patch improves things, but that doesn't mean we shouldn't try... Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Implementing RESET CONNECTION ...
What would be absolutely ideal is a reset connection command, plus some way of knowing via the protocol if it's needed or not. Chris Bruce Momjian wrote: What did we decide on RESET CONNECTION. Do we want an SQL command or something only the protocol can do? --- Oliver Jowett wrote: (cc'ing -hackers) Karel Zak wrote: I think command status is common and nice feedback for client. I think it's more simple change something in JDBC than change protocol that is shared between more tools. There is a bit of a queue of changes that would be nice to have but require a protocol version change. If we're going to change the protocol for any of those we might as well handle RESET CONNECTION cleanly too. We need some common way how detect on client what's happen on server -- a way that doesn't mean change protocol always when we add some feature/command to backend. The command status is possible use for this. Command status only works if commands are directly executed. If you can execute the command indirectly, e.g. via a PL, then you'll miss the notification. Making RESET a top-level-only command isn't unreasonable, but using command status won't work as a general approach for notifying clients. We have a mechanism for GUC changes that uses a separate message (ParameterStatus). Perhaps that should be generalized to report different sorts of connection-related changes. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] WAL bypass for CTAS
Bruce Momjian pgman@candle.pha.pa.us writes: I suppose no one would like adding an option to turn off locking during COPY, so the non-WAL logging would become the default? When and if we add LOCK or some equivalent option to COPY, I'm sure we'll change pg_dump to specify that option in its output. But trying to get that behavior by default for existing dumps seems to me to be far more dangerous than it's worth. Not every performance improvement has to automatically apply to existing dumps... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Unicode characters above 0x10000 #2
Bruce,
Attached patch replaces the original, applied today against CVS HEAD.
Fixes the surrogates, and limits to 4 byte utf8 as per spec.
Also extends UtfToLocal to 4 byte characters (tho, it does not add any,
just enables the code to handle them. If my interpretation of this code
is wrong, please let me know, and correct it).
... John
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]
Sent: Sunday, June 05, 2005 11:23 AM
To: pgman@candle.pha.pa.us
Cc: John Hansen; pgsql-hackers@postgresql.org; PostgreSQL-patches
Subject: Re: [PATCHES] Unicode characters above 0x1 #2
Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches
It will be applied as soon as one of the PostgreSQL
committers reviews and approves it.
--
-
pgman wrote:
I have backed out this patch. It is unclear it is a bug fix.
It will be saved for 8.1.
--
-
pgman wrote:
Patch applied. Thanks.
---
John Hansen wrote:
3 times lucky?
Last one broke utf8 G
This one works, Too tired, sorry for the inconvenience..
... John
Content-Description: cvs.diff
[ Attachment, skipping... ]
---(end of
broadcast)---
TIP 9: the planner will ignore your desire to choose an
index scan if your
joining column's datatypes do not match
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square,
Pennsylvania 19073
--
Bruce Momjian| http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square,
Pennsylvania 19073
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/mb/wchar.c,v
retrieving revision 1.38
diff -c -r1.38 wchar.c
*** src/backend/utils/mb/wchar.c17 Sep 2004 21:59:57
- 1.38
--- src/backend/utils/mb/wchar.c21 Nov 2004 09:58:36 -
***
*** 343,348
--- 343,373
return (pg_euc_dsplen(s));
}
+ bool isLegalUTF8(const UTF8 *source, int len) {
+ UTF8 a;
+ const UTF8 *srcptr = source+len;
+ if(!source || (pg_utf_mblen(source) != len)) return false;
+ switch (len) {
+ default: return false;
+ /* Everything else falls through when true... */
+ case 6: if ((a = (*--srcptr)) 0x80 || a
0xBF) return false;
+ case 5: if ((a = (*--srcptr)) 0x80 || a
0xBF) return false;
+ case 4: if ((a = (*--srcptr)) 0x80 || a
0xBF) return false;
+ case 3: if ((a = (*--srcptr)) 0x80 || a
0xBF) return false;
+ case 2: if ((a = (*--srcptr)) 0xBF) return false;
+ switch (*source) {
+ /* no fall-through in this inner switch */
+ case 0xE0: if (a 0xA0) return false; break;
+ case 0xF0: if (a 0x90) return false; break;
+ case 0xF4: if (a 0x8F) return false; break;
+ default: if (a 0x80) return false;
+ }
+ case 1: if (*source = 0x80 *source
0xC2) return false;
+ if (*source 0xFD) return false;
+ }
+ return true;
+ }
+
/*
* convert UTF-8 string to pg_wchar (UCS-2)
* caller should allocate enough space for to
***
*** 398,404
* returns the byte length of a UTF-8 word pointed to by s
*/
int
! pg_utf_mblen(const unsigned char *s)
{
int len = 1;
--- 423,429
* returns the byte length of a UTF-8 word pointed to by s
*/
int
! pg_utf_mblen(const UTF8 *s)
{
int len = 1;
***
*** 406,418
len = 1;
else if ((*s 0xe0) == 0xc0)
len = 2;
! else if ((*s 0xe0) == 0xe0)
! len = 3;
return (len);
}
static int
! pg_utf_dsplen(const unsigned char *s)
{
return 1; /* XXX
fix me! */
}
--- 431,449
len = 1;
else if ((*s 0xe0) == 0xc0)
len = 2;
! else if ((*s 0xf0) == 0xe0)
! len = 3;
