Re: [PATCHES] [PATCH] pgcrypto: pgp_encrypt (v2)
Marko Kreen wrote:
Please use following updated patch instead.
It implements utf8 conversion, fixes couple of bugs and has
many code and comment cleanups.
The regression tests don't pass on my box. With the default Makefile,
there are a lot of errors WRT "no strong random source". After editing
the Makefile to make use the "random" device, I get the attached
regression.diffs.
While I understand the need to make sure people use a reasonably strong
crypto source, it would be nice if the regression tests passed out of
the box.
-Neil
*** ./expected/pgp-armor.outMon Jul 4 16:52:12 2005
--- ./results/pgp-armor.out Mon Jul 4 16:57:55 2005
***
*** 3,9
--
select armor('');
armor
! -
-BEGIN PGP MESSAGE-
=twTO
--- 3,9
--
select armor('');
armor
! ---
-BEGIN PGP MESSAGE-
=twTO
***
*** 13,19
select armor('test');
armor
! -
-BEGIN PGP MESSAGE-
dGVzdA==
--- 13,19
select armor('test');
armor
!
-BEGIN PGP MESSAGE-
dGVzdA==
***
*** 37,43
select armor('0123456789abcdef0123456789abcdef0123456789abcdef
0123456789abcdef0123456789abcdef0123456789abcdef');
armor
! -
-BEGIN PGP MESSAGE-
MDEyMzQ1Njc4OWFiY2RlZjAxMjM0NTY3ODlhYmNkZWYwMTIzNDU2Nzg5YWJjZGVmCjAxMjM0NTY3
--- 37,43
select armor('0123456789abcdef0123456789abcdef0123456789abcdef
0123456789abcdef0123456789abcdef0123456789abcdef');
armor
!
-
-BEGIN PGP MESSAGE-
MDEyMzQ1Njc4OWFiY2RlZjAxMjM0NTY3ODlhYmNkZWYwMTIzNDU2Nzg5YWJjZGVmCjAxMjM0NTY3
==
*** ./expected/pgp-encrypt.out Mon Jul 4 16:52:12 2005
--- ./results/pgp-encrypt.out Mon Jul 4 16:57:55 2005
***
*** 2,12
-- PGP encrypt
--
select pgp_decrypt(pgp_encrypt('Secret.', 'key'), 'key');
! pgp_decrypt
! -
! Secret.
! (1 row)
!
-- check whether the defaults are ok
select pgp_decrypt(pgp_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=aes256,
--- 2,8
-- PGP encrypt
--
select pgp_decrypt(pgp_encrypt('Secret.', 'key'), 'key');
! ERROR: pgp_encrypt: No strong random source
-- check whether the defaults are ok
select pgp_decrypt(pgp_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=aes256,
***
*** 16,26
expect-s2k-digest-algo=sha1,
expect-compress-algo=0
');
! pgp_decrypt
! -
! Secret.
! (1 row)
!
-- maybe the expect- stuff simply does not work
select pgp_decrypt(pgp_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=bf,
--- 12,18
expect-s2k-digest-algo=sha1,
expect-compress-algo=0
');
! ERROR: pgp_encrypt: No strong random source
-- maybe the expect- stuff simply does not work
select pgp_decrypt(pgp_encrypt('Secret.', 'key'),
'key', 'expect-cipher-algo=bf,
***
*** 30,189
expect-s2k-digest-algo=md5,
expect-compress-algo=1
');
! NOTICE: pgp_decrypt: unexpected cipher_algo: expected 4 got 9
! NOTICE: pgp_decrypt: unexpected s2k_mode: expected 0 got 3
! NOTICE: pgp_decrypt: unexpected s2k_digest_algo: expected 1 got 2
! NOTICE: pgp_decrypt: unexpected use_sess_key: expected 1 got 0
! NOTICE: pgp_decrypt: unexpected disable_mdc: expected 1 got 0
! NOTICE: pgp_decrypt: unexpected compress_algo: expected 1 got 0
! pgp_decrypt
! -
! Secret.
! (1 row)
!
-- bytea as text
select pgp_decrypt(pgp_encrypt_bytea('Binary', 'baz'), 'baz');
! ERROR: pgp_decrypt error: Not text data
-- text as bytea
select pgp_decrypt_bytea(pgp_encrypt('Text', 'baz'), 'baz');
! pgp_decrypt_bytea
! ---
! Text
! (1 row)
!
-- algorithm change
select pgp_decrypt(
pgp_encrypt('Secret.
Re: [PATCHES] User's exception plpgsql
Pavel Stehule wrote: Per small recent discussion I corrected patch user's exception. I'll review and apply this in the next day or so. Next ToDo (needs discussion): + Optional message in raise stmt for user's or system exception raise exception division_by_zero; + Possibility rethrown exception raise; Both sound pretty reasonable to me. -Neil ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] PATCH to allow concurrent VACUUMs to not lock each
On P, 2005-07-03 at 12:19 -0400, Tom Lane wrote: > Hannu Krosing <[EMAIL PROTECTED]> writes: > > Ok, this is a new version of the vacuum patch with the following changes > > following some suggestions in this thread. > > The more I look at this, the uglier it looks ... and I still haven't > seen any convincing demonstration that it *works*, ie doesn't have > bad side-effects on the transaction-is-in-progress logic. The function GetOldestXmin is used *only* when determining oldest xmin for transactions. > I'm particularly concerned about what happens to the RecentXmin horizon > for pg_subtrans and pg_multixact operations. How are they affected by this change ? They should still see the vacuum as oldest transaction, unless they Oh, now I see. I'm pretty sure that at the time of original patch, the *only* uses of GetOldestXmin was from VACUUM and catalog/index.c and both for the same purpose, but now I see also a call from access/transam/xlog.c. Perhaps I should separate the function used by vacuum into another function, say GetOldestDataChangingXmin(), to keep the possible impact as localised as possible. Do you have any specific concerns related to this patch after that ? Or should I just back off for now and maybe start a separate project for ironing out patches related to running postgresql in real-world 24/7 OLTP environment (similar to what Bizgres does for OLAP ) ? -- Hannu Krosing <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PATCHES] silence GCC4 warning
This patch silences a GCC 4.0 warning about a potentially-uninitialized
variable in pl_comp.c, and makes some minor associated cleanups. Barring
any objections I'll apply this to HEAD tomorrow.
-Neil
Index: src/pl/plpgsql/src/pl_comp.c
===
RCS file: /var/lib/cvs/pgsql/src/pl/plpgsql/src/pl_comp.c,v
retrieving revision 1.91
diff -c -r1.91 pl_comp.c
*** src/pl/plpgsql/src/pl_comp.c 10 Jun 2005 16:23:11 - 1.91
--- src/pl/plpgsql/src/pl_comp.c 4 Jul 2005 07:48:09 -
***
*** 1369,1375
HeapTuple attrtup = NULL;
Form_pg_attribute attrStruct;
HeapTuple typetup = NULL;
! char *cp[2];
char *colname[1];
int qualified_att_len;
int numdots = 0;
--- 1369,1375
HeapTuple attrtup = NULL;
Form_pg_attribute attrStruct;
HeapTuple typetup = NULL;
! char *cp[2] = {NULL, NULL};
char *colname[1];
int qualified_att_len;
int numdots = 0;
***
*** 1389,1410
{
if (word[i] == '.' && ++numdots == 2)
{
! cp[0] = (char *) palloc((i + 1) * sizeof(char));
! memset(cp[0], 0, (i + 1) * sizeof(char));
memcpy(cp[0], word, i * sizeof(char));
/*
* qualified_att_len - one based position + 1 (null
* terminator)
*/
! cp[1] = (char *) palloc((qualified_att_len - i) * sizeof(char));
! memset(cp[1], 0, (qualified_att_len - i) * sizeof(char));
memcpy(cp[1], &word[i + 1], (qualified_att_len - i - 1) * sizeof(char));
break;
}
}
relvar = makeRangeVarFromNameList(stringToQualifiedNameList(cp[0], "plpgsql_parse_tripwordtype"));
classOid = RangeVarGetRelid(relvar, true);
if (!OidIsValid(classOid))
--- 1389,1411
{
if (word[i] == '.' && ++numdots == 2)
{
! cp[0] = (char *) palloc0((i + 1) * sizeof(char));
memcpy(cp[0], word, i * sizeof(char));
/*
* qualified_att_len - one based position + 1 (null
* terminator)
*/
! cp[1] = (char *) palloc0((qualified_att_len - i) * sizeof(char));
memcpy(cp[1], &word[i + 1], (qualified_att_len - i - 1) * sizeof(char));
break;
}
}
+ /* We must have found a match */
+ Assert(i < qualified_att_len);
+
relvar = makeRangeVarFromNameList(stringToQualifiedNameList(cp[0], "plpgsql_parse_tripwordtype"));
classOid = RangeVarGetRelid(relvar, true);
if (!OidIsValid(classOid))
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 03 July 2005 17:10 > To: Dawid Kuroczko > Cc: Andreas Pflug; Dave Page; Bruce Momjian; > PostgreSQL-patches; PostgreSQL-development > Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration > > Dawid Kuroczko <[EMAIL PROTECTED]> writes: > > Oh, I think pg_dbfile_size is best so far. > > I think it's by far the ugliest suggestion yet :-( Why? It does exactly what it says on the tin! It might not be that nice, but it does describe what it does - and noone yet has come up with anything less ambiguous or misleading imho. > Andreas's suggestion of having just one function with a bool parameter > might be a workable compromise. Aside from the fact that's a change to the API that we had settled on, it doesn't solve the actual problem of needing a suitable name for a function that returns the size of a table /or/ index. pg_relation_size() or pg_table_size() can't be used for precisely the reason they were rejected for that purpose in the first place. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] contrib/pgcrypto patch for OpenSSL 0.9.8
On Mon, Jul 04, 2005 at 12:22:03PM +1000, Neil Conway wrote: > Bruce Momjian wrote: > >Patch applied. Thanks. > > Should either or both of these patches be applied to back branches? That would be a good idea. -- marko ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] Implement support for TCP_KEEPCNT, TCP_KEEPIDLE, TCP_KEEPINTVL
Bruce Momjian wrote:
> Is this patch being worked on?
Here's an updated version. It compiles and appears to work as expected
under Linux (supports TCP_KEEPIDLE etc) and Solaris 9 (no support).
Main changes:
- removed the tcp_keepalives GUC, SO_KEEPALIVE is now always on (as in
current CVS)
- {get,set}sockopt calls are only done when absolutely necessary (no
extra syscalls during backend startup in a default configuration).
I still haven't had a chance to glue in support for the TCP_KEEPALIVE
(Solaris-style) option, but that should be fairly painless to add later.
-O
? postgresql-8.1devel.tar.gz
Index: doc/src/sgml/runtime.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/runtime.sgml,v
retrieving revision 1.335
diff -u -c -r1.335 runtime.sgml
*** doc/src/sgml/runtime.sgml 2 Jul 2005 19:16:36 - 1.335
--- doc/src/sgml/runtime.sgml 4 Jul 2005 10:41:33 -
***
*** 894,899
--- 894,946
+
+ tcp_keepalives_idle
(integer)
+
+tcp_keepalives_idle configuration
parameter
+
+
+
+ On systems that support the TCP_KEEPIDLE socket option, specifies the
+ number of seconds between sending keepalives on an otherwise idle
+ connection. A value of 0 uses the system default. If TCP_KEEPIDLE is
+ not supported, this parameter must be 0. This option is ignored for
+ connections made via a Unix-domain socket.
+
+
+
+
+
+ tcp_keepalives_interval
(integer)
+
+tcp_keepalives_interval configuration
parameter
+
+
+
+ On systems that support the TCP_KEEPINTVL socket option, specifies how
+ long, in seconds, to wait for a response to a keepalive before
+ retransmitting. A value of 0 uses the system default. If TCP_KEEPINTVL
+ is not supported, this parameter must be 0. This option is ignored
+ for connections made via a Unix-domain socket.
+
+
+
+
+
+ tcp_keepalives_count
(integer)
+
+tcp_keepalives_count configuration
parameter
+
+
+
+ On systems that support the TCP_KEEPCNT socket option, specifies how
+ many keepalives may be lost before the connection is considered dead.
+ A value of 0 uses the system default. If TCP_KEEPINTVL is not
+ supported, this parameter must be 0.
+
+
+
+
Index: src/backend/libpq/pqcomm.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/libpq/pqcomm.c,v
retrieving revision 1.176
diff -u -c -r1.176 pqcomm.c
*** src/backend/libpq/pqcomm.c 22 Feb 2005 04:35:57 - 1.176
--- src/backend/libpq/pqcomm.c 4 Jul 2005 10:41:33 -
***
*** 87,93
#include "libpq/libpq.h"
#include "miscadmin.h"
#include "storage/ipc.h"
!
/*
* Configuration options
--- 87,93
#include "libpq/libpq.h"
#include "miscadmin.h"
#include "storage/ipc.h"
! #include "utils/guc.h"
/*
* Configuration options
***
*** 594,599
--- 594,612
elog(LOG, "setsockopt(SO_KEEPALIVE) failed: %m");
return STATUS_ERROR;
}
+
+ /* Set default keepalive parameters. This should also catch
+* misconfigurations (non-zero values when socket options aren't
+* supported)
+*/
+ if (pq_setkeepalivesidle(tcp_keepalives_idle, port) !=
STATUS_OK)
+ return STATUS_ERROR;
+
+ if (pq_setkeepalivesinterval(tcp_keepalives_interval, port) !=
STATUS_OK)
+ return STATUS_ERROR;
+
+ if (pq_setkeepalivescount(tcp_keepalives_count, port) !=
STATUS_OK)
+ return STATUS_ERROR;
}
return STATUS_OK;
***
*** 1158,1160
--- 1171,1369
/* in non-error case, copy.c will have emitted the terminator line */
DoingCopyOut = false;
}
+
+ int
+ pq_getkeepalivesidle(Port *port)
+ {
+ #ifdef TCP_KEEPIDLE
+ if (IS_AF_UNIX(port->laddr.addr.ss_family))
+ return 0;
+
+ if (port->keepalives_idle != 0)
+ return port->keepalives_idle;
+
+ if (port->default_keepalives_idle == 0)
+ {
+ socklen_t size = sizeof(port->default_keepalives_idle);
+ if (getsockopt(port->sock, SOL_TCP, TCP_KEEPIDLE,
+ (char *)
&port->default_keepalives_idle,
+ &size) < 0)
+ {
+ elog(LOG, "getsockopt(TCP_KEEPIDLE) failed: %m");
+ return -1;
+ }
+ }
+
+ return
Re: [PATCHES] [PATCH] pgcrypto: pgp_encrypt (v2)
On Mon, Jul 04, 2005 at 04:59:58PM +1000, Neil Conway wrote: > Marko Kreen wrote: > >Please use following updated patch instead. > > > >It implements utf8 conversion, fixes couple of bugs and has > >many code and comment cleanups. > > The regression tests don't pass on my box. With the default Makefile, > there are a lot of errors WRT "no strong random source". After editing > the Makefile to make use the "random" device, I get the attached > regression.diffs. I don't understand the regression of the dashes in armor test, kinda seems you have different psql than in CVS. But for the regression with 'random = dev', I'd say you did not do 'make clean' after changing Makefile. It only changes flags to random.c, so if you have existing random.o, it wont be recompiled. > While I understand the need to make sure people use a reasonably strong > crypto source, it would be nice if the regression tests passed out of > the box. Look for the 'pgp_encrypt v3' mail I sent. There I use special empty tests for disabled functionality, that succeed, but hopefully show user through naming that the functionality is missing. -- marko ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] PATCH to allow concurrent VACUUMs to not lock each
On E, 2005-07-04 at 10:24 +0300, Hannu Krosing wrote:
> On P, 2005-07-03 at 12:19 -0400, Tom Lane wrote:
> > Hannu Krosing <[EMAIL PROTECTED]> writes:
> > > Ok, this is a new version of the vacuum patch with the following changes
> > > following some suggestions in this thread.
> >
> > The more I look at this, the uglier it looks ... and I still haven't
> > seen any convincing demonstration that it *works*, ie doesn't have
> > bad side-effects on the transaction-is-in-progress logic.
Ok, I changed GetOldestXmin() to use proc->inVacuum only when
determining the oldest visible xid for vacuum and index (i.e. which
tuples are safe to delete and which tuples there is no need to index).
The third use on GetOldestXmin() in xlog.c is changed to use old
GetOldestXmin() logic.
My reasoning for why the patch should work is as follows:
1) the only transaction during which inVacuum is set is the 2nd
transaction (of originally 3, now 4) of lazy VACUUM, which does simple
heap scanning and old tuple removal (lazy_vacuum_rel()), and does no
externally visible changes to the data. It only removes tuples which are
already invisible to all running transactions.
2) That transaction never deletes, updates or inserts any tuples on it
own.
3) As it can't add any tuples or change any existing tuples to have its
xid as either xmin or xmax, it already does run logically "outside of
transactions".
4) The only use made of of proc->inVacuum is when determining which
tuples are safe to remove (in vacuum.c) or not worth indexing (in
index.c) and thus can't affect anything else.
I can easily demonstrate that it "works" in the sense that it allows
several concurrent vacuums to clean out old tuples, and I have thus far
been unable to construct the counterexample where it does anything bad.
Could you tell me which part of my reasoning is wrong or what else do I
overlook.
--
Hannu Krosing <[EMAIL PROTECTED]>
Index: src/backend/access/transam/xact.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.209
diff -c -r1.209 xact.c
*** src/backend/access/transam/xact.c 29 Jun 2005 22:51:53 - 1.209
--- src/backend/access/transam/xact.c 4 Jul 2005 10:57:06 -
***
*** 1402,1407
--- 1402,1416
AfterTriggerBeginXact();
/*
+ * mark the transaction as not VACUUM (vacuum_rel will set isVacuum
+ * to true directly after calling BeginTransactionCommand() )
+ *
+ * this can probably be moved to be done only once when establishing
+ * connection as this is now quaranteedto be reset to false in vacuum.c
+ */
+ MyProc->inVacuum = false;
+
+ /*
* done with start processing, set current transaction state to "in
* progress"
*/
Index: src/backend/access/transam/xlog.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.205
diff -c -r1.205 xlog.c
*** src/backend/access/transam/xlog.c 30 Jun 2005 00:00:50 - 1.205
--- src/backend/access/transam/xlog.c 4 Jul 2005 10:57:07 -
***
*** 5161,5167
* mustn't do this because StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true));
if (!shutdown)
ereport(DEBUG2,
--- 5161,5167
* mustn't do this because StartupSUBTRANS hasn't been called yet.
*/
if (!InRecovery)
! TruncateSUBTRANS(GetOldestXmin(true, false));
if (!shutdown)
ereport(DEBUG2,
Index: src/backend/catalog/index.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/catalog/index.c,v
retrieving revision 1.258
diff -c -r1.258 index.c
*** src/backend/catalog/index.c 25 Jun 2005 16:53:49 - 1.258
--- src/backend/catalog/index.c 4 Jul 2005 10:57:07 -
***
*** 1420,1426
else
{
snapshot = SnapshotAny;
! OldestXmin = GetOldestXmin(heapRelation->rd_rel->relisshared);
}
scan = heap_beginscan(heapRelation, /* relation */
--- 1420,1426
else
{
snapshot = SnapshotAny;
! OldestXmin = GetOldestXmin(heapRelation->rd_rel->relisshared, true);
}
scan = heap_beginscan(heapRelation, /* relation */
Index: src/backend/commands/vacuum.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.310
diff -c -r1.310 vacuum.c
*** src/backend/commands/vacuum.c 14 Jun 2005 22:15:32 - 1.310
--- src/backend/commands/vacuum.c 4 Jul 2005 10:57:08 -
***
*** 37,42
--- 37,43
#include "miscadmin.h"
#include "storage/freespace.h"
#include "storage/procarray.h"
+ #include "storage/proc.h"
#include "storage/smgr.h"
#include "tcop/pquery.h"
#include "utils/acl.h"
***
*** 571,577
{
TransactionId limit;
! *oldes
Re: [PATCHES] per user/database connections limit again
Alvaro Herrera wrote: I was wondering if there was some way to defer the user check till a later time, when the pg_authid relation could be checked? Not sure if that's a good idea, but it may help reduce the impact of the change, and thus chances that it'd be rejected. Well It can, but it would mean one more lock on procarray and I didn't want that and like I said, MyDatabaseId is read from flatfile too. Auth flatfile is used only on two other places which I also patched so I don't see this as a problem (it's used in hba.c to check role membership and in crypt.c for password verification) -- Regards Petr Jelinek (PJMODOS) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] HEAD doesn't cope with libraries in non-default
Bruce Momjian said: > Tom Lane wrote: >> Bruce Momjian writes: >> > Tom Lane wrote: >> >> It appears that somebody has changed things so that the -L switches >> >> appear after the -l switches (ie, too late). I'm too tired to >> >> investigate now, but my money is on Autoconf 2.59 being the problem >> >> ... >> >> > I wonder if it was this commit. I am attaching the patch so you can >> > test to see if it fixes it. If it does, please let us know. >> >> > ! SHLIB_LINK := $(filter -L%, $(LDFLAGS)) $(SHLIB_LINK) >> > ... >> > ! SHLIB_LINK := $(SHLIB_LINK) $(filter -L%, $(LDFLAGS)) >> >> Urgh. This was considered a good idea why exactly? > > I didn't like it myself but it fixed a problem with a build farm > machine, and no one objected, so I applied it, but it seemed pretty > strange to be reversing those. I will reverse the patch. > I was also slightly dubious about it. However, we do still need to solve the problem that the patch addressed. Buildfarm members platypus and cuckoo are currently failing because dblink is picking up the wrong libpq (and it appears that incorrect libraries are also being picked up in the ecpg libraries, although this isn't causing a buildfarm failure.) Alternatively, if we can't say --with-libraries=/foo/bar when /foo/bar contains possibly conflicting libraries, that should be tested for at configure time. cheers andrew ---(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] [PATCHES] Dbsize backend integration
"Dave Page" writes: > Aside from the fact that's a change to the API that we had settled on, > it doesn't solve the actual problem of needing a suitable name for a > function that returns the size of a table /or/ index. pg_relation_size() > or pg_table_size() can't be used for precisely the reason they were > rejected for that purpose in the first place. Rejected by whom? pg_relation_size is an excellent choice for that. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Dbsize backend integration
Tom Lane wrote: > "Dave Page" writes: > > Aside from the fact that's a change to the API that we had settled on, > > it doesn't solve the actual problem of needing a suitable name for a > > function that returns the size of a table /or/ index. pg_relation_size() > > or pg_table_size() can't be used for precisely the reason they were > > rejected for that purpose in the first place. > > Rejected by whom? pg_relation_size is an excellent choice for that. We mostly tell people that table and relation are synonmous. Though there is a distinction, it seems error-prone to rely on that distinction in the API. -- 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: [PATCHES] User's exception plpgsql
Neil Conway <[EMAIL PROTECTED]> writes: > Pavel Stehule wrote: >> Per small recent discussion I corrected patch user's exception. > I'll review and apply this in the next day or so. Have we got a consensus yet on the behavior? There seemed to be no meeting of the minds at all the last time I paid attention to this thread ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [BUGS] BUG #1749: date_trunc('week', ...) is incorrect for some
Nick Johnson wrote:
>
> The following bug has been logged online:
>
> Bug reference: 1749
> Logged by: Nick Johnson
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 8.0.3
> Operating system: FreeBSD
> Description:date_trunc('week', ...) is incorrect for some dates
> Details:
>
> numa=# SELECT date_trunc('week', '2002-12-31'::date);
>date_trunc
>
> 2001-12-31 00:00:00-05
> (1 row)
>
> Obviously this isn't the expected behaviour...
Thanks, patch attached and applied to CVS HEAD and 8.0.X. When we fixed
January dates that were part of the previous year, I thought their might
be December dates in the next year, but I couldn't find any so I figured
they didn't exist.
--
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
Index: src/backend/utils/adt/timestamp.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.128
diff -c -c -r1.128 timestamp.c
*** src/backend/utils/adt/timestamp.c 30 Jun 2005 03:48:58 - 1.128
--- src/backend/utils/adt/timestamp.c 4 Jul 2005 14:02:46 -
***
*** 2793,2801
--- 2793,2804
/*
* If it is week 52/53 and the month is
January,
* then the week must belong to the
previous year.
+* Also, some December dates belong to the
next year.
*/
if (woy >= 52 && tm->tm_mon == 1)
--tm->tm_year;
+ if (woy <= 1 && tm->tm_mon == 12)
+ ++tm->tm_year;
isoweek2date(woy, &(tm->tm_year),
&(tm->tm_mon), &(tm->tm_mday));
tm->tm_hour = 0;
tm->tm_min = 0;
***
*** 2924,2932
--- 2927,2938
/*
* If it is week 52/53 and the month is
January,
* then the week must belong to the
previous year.
+* Also, some December dates belong to the
next year.
*/
if (woy >= 52 && tm->tm_mon == 1)
--tm->tm_year;
+ if (woy <= 1 && tm->tm_mon == 12)
+ ++tm->tm_year;
isoweek2date(woy, &(tm->tm_year),
&(tm->tm_mon), &(tm->tm_mday));
tm->tm_hour = 0;
tm->tm_min = 0;
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] silence GCC4 warning
Neil Conway <[EMAIL PROTECTED]> writes: > This patch silences a GCC 4.0 warning about a potentially-uninitialized > variable in pl_comp.c, AFAICT, gcc4's default behavior is to warn about int foo; somefunc(&foo); which unfortunately is a very common usage that I'm not real thrilled about having to fix every occurrence of. I've been meaning to look into whether they provided a pragma or something to mark function parameters as output-only, so that this message could be suppressed where appropriate without adding a lot of useless initializations. In the meantime, I don't see the point of patching just one place. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] contrib/pgcrypto patch for OpenSSL 0.9.8
On Mon, Jul 04, 2005 at 10:31:21AM -0400, Bruce Momjian wrote: > OK, I have applied the last two diffs for pgcrypto includes to 8.0.X, > attached. > *** contrib/pgcrypto/openssl.c12 Mar 2005 06:54:31 - 1.13.4.1 > --- contrib/pgcrypto/openssl.c4 Jul 2005 14:30:12 - > *** > *** 35,40 > --- 35,54 > > #include > > + #include Why second time? -- marko ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] silence GCC4 warning
Tom Lane wrote: > AFAICT, gcc4's default behavior is to warn about > > int foo; > > somefunc(&foo); A simple test shows that this is not the case, even with lots of -W options. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] contrib/pgcrypto patch for OpenSSL 0.9.8
Marko Kreen wrote: > On Mon, Jul 04, 2005 at 10:31:21AM -0400, Bruce Momjian wrote: > > OK, I have applied the last two diffs for pgcrypto includes to 8.0.X, > > attached. > > > *** contrib/pgcrypto/openssl.c 12 Mar 2005 06:54:31 - 1.13.4.1 > > --- contrib/pgcrypto/openssl.c 4 Jul 2005 14:30:12 - > > *** > > *** 35,40 > > --- 35,54 > > > > #include > > > > + #include > > Why second time? Oh, I see, yea, got duplicated. That is fixed in the committed version. -- 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: [PATCHES] contrib/pgcrypto patch for OpenSSL 0.9.8
Marko Kreen wrote: > On Mon, Jul 04, 2005 at 12:22:03PM +1000, Neil Conway wrote: > > Bruce Momjian wrote: > > >Patch applied. Thanks. > > > > Should either or both of these patches be applied to back branches? > > That would be a good idea. OK, I have applied the last two diffs for pgcrypto includes to 8.0.X, attached. -- 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 Index: contrib/pgcrypto/openssl.c === RCS file: /cvsroot/pgsql/contrib/pgcrypto/openssl.c,v retrieving revision 1.13.4.1 diff -c -c -r1.13.4.1 openssl.c *** contrib/pgcrypto/openssl.c 12 Mar 2005 06:54:31 - 1.13.4.1 --- contrib/pgcrypto/openssl.c 4 Jul 2005 14:30:12 - *** *** 35,40 --- 35,54 #include + #include + #include + #include + #include + + /* + * Does OpenSSL support AES? + */ + #undef GOT_AES + #if OPENSSL_VERSION_NUMBER >= 0x00907000L + #define GOT_AES + #include + #endif + /* * Hashes */ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Disable page writes when fsync off, add GUC
* Bruce Momjian (pgman@candle.pha.pa.us) wrote: > This patch disables page writes to WAL when fsync is off, because with > no fsync guarantee, the page write recovery isn't useful. This doesn't seem quite right to me. What happens with PITR? And Postgres crashes? While many people seriously distrust running w/ fsync off, I'm sure there's quite a few folks which do. > This also adds a full_page_writes GUC to turn off page writes to WAL. > Some people might not want full_page_writes, but still might want fsync. Adding an option to not do page writes to WAL seems fine to me, but I think WAL writes should be on by default, even in the fsync=off case. If people want to turn it off, fine, for either case since we expect they understand what it means to have it turned off, but I don't think the two options should be coupled as is being proposed. Thanks, Stephen signature.asc Description: Digital signature
Re: [PATCHES] enable/disable trigger (Re: Fwd: [HACKERS] Open items)
I am not sure what to do with this patch. It is missing dump
capability, there is no clause to disable all triggers on a table, and
it uses a table owner check when a super user check is required (because
of referential integrity).
Satoshi, are you still working on it? If not does someone else want to
complete it? I realized you just started on it but the deadline is
soon.
---
Satoshi Nagayasu wrote:
> There is one more fix...
>
> > + tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
> > + SnapshotNow, 1,
> > keys);
>
> '1' was incorrect, must be '2'.
>
> > + tgscan = systable_beginscan(tgrel, TriggerRelidNameIndexId, true,
> > + SnapshotNow, 2,
> > keys);
>
> --
> NAGAYASU Satoshi <[EMAIL PROTECTED]>
> diff -cr pgsql.orig/src/backend/commands/tablecmds.c
> pgsql/src/backend/commands/tablecmds.c
> *** pgsql.orig/src/backend/commands/tablecmds.c 2005-06-28
> 14:08:54.0 +0900
> --- pgsql/src/backend/commands/tablecmds.c2005-07-01 15:50:27.0
> +0900
> ***
> *** 236,241
> --- 236,243
>
> Oid newOwnerId);
> static void ATExecClusterOn(Relation rel, const char *indexName);
> static void ATExecDropCluster(Relation rel);
> + static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
> +bool
> enable);
> static void ATPrepSetTableSpace(AlteredTableInfo *tab, Relation rel,
> char *tablespacename);
> static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
> ***
> *** 1993,1998
> --- 1995,2005
> }
> pass = AT_PASS_DROP;
> break;
> + case AT_EnableTrig: /* ENABLE TRIGGER */
> + case AT_DisableTrig:/* DISABLE TRIGGER */
> + ATSimplePermissions(rel, false);
> + pass = AT_PASS_MISC;
> + break;
> case AT_SetTableSpace: /* SET TABLESPACE */
> /* This command never recurses */
> ATPrepSetTableSpace(tab, rel, cmd->name);
> ***
> *** 2155,2160
> --- 2162,2173
>* Nothing to do here; Phase 3 does the work
>*/
> break;
> + case AT_EnableTrig: /* ENABLE TRIGGER */
> + ATExecEnableDisableTrigger(rel, cmd->name, true);
> + break;
> + case AT_DisableTrig:/* DISABLE TRIGGER */
> + ATExecEnableDisableTrigger(rel, cmd->name, false);
> + break;
> default:/* oops */
> elog(ERROR, "unrecognized alter table type: %d",
>(int) cmd->subtype);
> ***
> *** 5465,5470
> --- 5478,5492
> }
>
> /*
> + * ALTER TABLE ENABLE/DISABLE TRIGGER
> + */
> + static void
> + ATExecEnableDisableTrigger(Relation rel, char *trigname, bool enable)
> + {
> + EnableDisableTrigger(rel, trigname, enable);
> + }
> +
> + /*
>* ALTER TABLE SET TABLESPACE
>*/
> static void
> diff -cr pgsql.orig/src/backend/commands/trigger.c
> pgsql/src/backend/commands/trigger.c
> *** pgsql.orig/src/backend/commands/trigger.c 2005-05-30 16:20:58.0
> +0900
> --- pgsql/src/backend/commands/trigger.c 2005-07-01 17:21:44.0
> +0900
> ***
> *** 3063,3065
> --- 3063,3132
> afterTriggerAddEvent(new_event);
> }
> }
> +
> + /* --
> + * EnableDisableTrigger()
> + *
> + * Called by ALTER TABLE ENABLE/DISABLE TRIGGER
> + * to change 'tgenabled' flag in the pg_trigger.
> + * --
> + */
> + void
> + EnableDisableTrigger(Relation rel, const char *tgname, bool enable)
> + {
> + Relation tgrel;
> + SysScanDesc tgscan;
> + ScanKeyData keys[2];
> + HeapTuple tuple;
> +
> + /* Permissions checks */
> + if (!pg_class_ownercheck(RelationGetRelid(rel), GetUserId()))
> + aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
> +RelationGetRelationName(rel));
> +
> + if (!allowSystemTableMods && IsSystemRelation(rel))
> + ereport(ERROR,
> + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
> + errmsg("permission denied: \"%s\" is a system
> catalog",
> + RelationGetRelationName(rel;
> +
> + tgrel = heap_open(TriggerRelationId, RowExclusiveLock);
> +
Re: [PATCHES] User's exception plpgsql
On Mon, 4 Jul 2005, Tom Lane wrote: > Neil Conway <[EMAIL PROTECTED]> writes: > > Pavel Stehule wrote: > >> Per small recent discussion I corrected patch user's exception. > > > I'll review and apply this in the next day or so. > > Have we got a consensus yet on the behavior? There seemed to be no > meeting of the minds at all the last time I paid attention to this > thread ... > There was only one objection against - requirement of uniqueness, and I corrected it. regards Pavel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Disable page writes when fsync off, add GUC
Bruce Momjian wrote: > This patch disables page writes to WAL when fsync is off, because > with no fsync guarantee, the page write recovery isn't useful. > > This also adds a full_page_writes GUC to turn off page writes to WAL. > Some people might not want full_page_writes, but still might want > fsync. Do you have some numbers to suggest that there is a performance benefit to be had? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Disable page writes when fsync off, add GUC
Peter Eisentraut wrote: > Bruce Momjian wrote: > > This patch disables page writes to WAL when fsync is off, because > > with no fsync guarantee, the page write recovery isn't useful. > > > > This also adds a full_page_writes GUC to turn off page writes to WAL. > > Some people might not want full_page_writes, but still might want > > fsync. > > Do you have some numbers to suggest that there is a performance benefit > to be had? Josh reported page writes to be a big hit (which we already knew), but I don't have any with fsync off, though it seems like a no-brainer. However, I am thinking decoupling them is best. -- 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: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: 04 July 2005 14:54 > To: Dave Page > Cc: Dawid Kuroczko; Andreas Pflug; Bruce Momjian; > PostgreSQL-patches; PostgreSQL-development > Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration > > "Dave Page" writes: > > Aside from the fact that's a change to the API that we had > settled on, > > it doesn't solve the actual problem of needing a suitable name for a > > function that returns the size of a table /or/ index. > pg_relation_size() > > or pg_table_size() can't be used for precisely the reason they were > > rejected for that purpose in the first place. > > Rejected by whom? pg_relation_size is an excellent choice for that. Bruce didn't like it (http://archives.postgresql.org/pgsql-hackers/2005-06/msg01410.php), and you seemed to object as well (http://archives.postgresql.org/pgsql-hackers/2005-06/msg01247.php) Personally I'm beyond caring much now as the amount of time spent trying to name these simple functions is wildly disproportionate the the effort take to actually code them. I think we just need to agree there is no perfect name and rely on the comments and docs to guide people. I think the current names work OK, and Bruce and Dawid at least agree! Regards, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian wrote: > Tom Lane wrote: > > "Dave Page" writes: > > > Aside from the fact that's a change to the API that we had settled on, > > > it doesn't solve the actual problem of needing a suitable name for a > > > function that returns the size of a table /or/ index. pg_relation_size() > > > or pg_table_size() can't be used for precisely the reason they were > > > rejected for that purpose in the first place. > > > > Rejected by whom? pg_relation_size is an excellent choice for that. > > We mostly tell people that table and relation are synonmous. Though > there is a distinction, it seems error-prone to rely on that distinction > in the API. I am starting to warm up to the idea of using "relation" as the combined total. Was that the proposal? Are we prepared to make that distinction in other places? -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] Dbsize backend integration
On Monday 04 July 2005 10:11, Dave Page wrote:
> > -Original Message-
> > From: Tom Lane [mailto:[EMAIL PROTECTED]
> > Sent: 04 July 2005 14:54
> > To: Dave Page
> > Cc: Dawid Kuroczko; Andreas Pflug; Bruce Momjian;
> > PostgreSQL-patches; PostgreSQL-development
> > Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration
> >
> > "Dave Page" writes:
> > > Aside from the fact that's a change to the API that we had
> >
> > settled on,
> >
> > > it doesn't solve the actual problem of needing a suitable name for a
> > > function that returns the size of a table /or/ index.
> >
> > pg_relation_size()
> >
> > > or pg_table_size() can't be used for precisely the reason they were
> > > rejected for that purpose in the first place.
> >
> > Rejected by whom? pg_relation_size is an excellent choice for that.
>
> Bruce didn't like it
> (http://archives.postgresql.org/pgsql-hackers/2005-06/msg01410.php), and
> you seemed to object as well
> (http://archives.postgresql.org/pgsql-hackers/2005-06/msg01247.php)
>
> Personally I'm beyond caring much now as the amount of time spent trying
> to name these simple functions is wildly disproportionate the the effort
> take to actually code them. I think we just need to agree there is no
> perfect name and rely on the comments and docs to guide people. I think
> the current names work OK, and Bruce and Dawid at least agree!
>
Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I could
use a filename as an argument. ISTM that if we think that functions like
pg_database_size and pg_tablespace_size all make sense, the natural extension
would be functions called pg_index_size to tell us the size of an index,
pg_table_size to tell us the size of a table (table+toast) without it's
indexes, and some form of pg_table_plus_indexes_size for a table and its
indexes for those that feel we need both. I'm not sold we need a function
that can return either an index or table size, but if so something like
pg_object_size seems ambigious enough to work, and is future proof enough to
handle things like materialized views when and if they arise.
Just my .02 :-)
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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] [PATCHES] Dbsize backend integration
Robert Treat wrote: > Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I > could > use a filename as an argument. ISTM that if we think that functions like > pg_database_size and pg_tablespace_size all make sense, the natural extension > would be functions called pg_index_size to tell us the size of an index, > pg_table_size to tell us the size of a table (table+toast) without it's > indexes, and some form of pg_table_plus_indexes_size for a table and its > indexes for those that feel we need both. I'm not sold we need a function > that can return either an index or table size, but if so something like > pg_object_size seems ambigious enough to work, and is future proof enough to > handle things like materialized views when and if they arise. You are into the cycle we were in. We discussed pg_object size (too vague) and pg_index_size (needs pg_toast_size too, and maybe toast indexes; too many functions). -- 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
[PATCHES] Column-level triggers
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I did not have the time nor the experience to get as far as I hoped in the "column-level triggers" item, but attached is what I have done so far. Look for XXXCT for areas that still need work. Spent a lot of time trying to get gram.y to do what I wanted it to; that much, at least, finally works. This was a nice crash course in PG internals: no doubt there are better ways to do a lot of what I have done, so suggestions and corections welcome. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200507041355 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCyXkSvJuQZxSWSsgRArguAJ4ky2sgWZi1pOz+h+8+87sLQjNOAACgwRvz jNBELzCWf8aZpgY03O9pj+Y= =SLdW -END PGP SIGNATURE- coltrig.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] Fix zic compile
Seems the new platform-portable zic doesn't use libpgport anymore, and the pg_fprintf() calls are causing my compile to fail because my platform uses those pgport functions. I have applied the following patch to fix this. -- 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 Index: src/timezone/pgtz.h === RCS file: /cvsroot/pgsql/src/timezone/pgtz.h,v retrieving revision 1.12 diff -c -c -r1.12 pgtz.h *** src/timezone/pgtz.h 15 Jun 2005 00:34:11 - 1.12 --- src/timezone/pgtz.h 4 Jul 2005 18:20:06 - *** *** 18,23 --- 18,33 #include "tzfile.h" + /* + *Prevent the use of /port functions because + *the are not included in this binary. + */ + #undef vsnprintf + #undef snprintf + #undef sprintf + #undef fprintf + #undef printf + extern char *pg_TZDIR(void); #define BIGGEST(a, b) (((a) > (b)) ? (a) : (b)) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] replace_text() improvement
Patch applied. Thanks. --- Atsushi Ogawa wrote: > > I made the patch that improved the performance of replace_text(). > The content of the patch is as follows: > > (1)Create shortcut when subtext was not found. > > (2)Stop using LEFT and RIGHT macro. > In LEFT and RIGHT macro, TEXTPOS is executed by the same content as > execution immediately before. The execution frequency of TEXTPOS can be > reduced by using text_substring instead of LEFT and RIGHT macro. > > (3)Add appendStringInfoText, and use it instead of appendStringInfoString. > There is an overhead of PG_TEXT_GET_STR when appendStringInfoString is > executed by text type. This can be reduced by appendStringInfoText. > > (4)Reduce execution of TEXTDUP. > > The effect of the patch that I measured is as follows: > > - The Data for test was created by 'pgbench -i'. > > - Test SQL: > select replace(aid, '9', 'A') from accounts; > > - Test results: Linux(CPU: Pentium III, Compiler option: -O2) > original: 1.515s > patched: 1.250s > > regards, > > --- > Atsushi Ogawa [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster -- 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: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [INTERFACES] libecpg (8.0 and CVS) hits a gcc bug on powerpc
Patch applied. Thanks. --- Christof Petig wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Michael Meskes schrieb: > > Patch committed. Thanks. > > I have to admit that I got the case of the preprocessor symbol on amd64 > wrong. __AMD64__ is not defined, __amd64__ is. > > Sorry, corrected patch attached > Christof > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.1 (GNU/Linux) > Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org > > iD8DBQFCsZCmng+R+0ucfO0RAoABAKDKnokBW+tgXMKgeEMecpZsFXRFTQCeKeBu > YsdWP056cqxKtNAx5Gh/Wis= > =b2Og > -END PGP SIGNATURE- [ text/x-patch is unsupported, treating like TEXT/PLAIN ] > Index: src/interfaces/ecpg/ecpglib/execute.c > === > RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/ecpglib/execute.c,v > retrieving revision 1.40 > diff -u -u -r1.40 execute.c > --- src/interfaces/ecpg/ecpglib/execute.c 2 Jun 2005 12:35:11 - > 1.40 > +++ src/interfaces/ecpg/ecpglib/execute.c 16 Jun 2005 14:35:09 - > @@ -70,7 +70,7 @@ > return res; > } > > -#if defined(__GNUC__) && (defined (__powerpc__) || defined(__AMD64__) || > defined(__x86_64__)) > +#if defined(__GNUC__) && (defined (__powerpc__) || defined(__amd64__) || > defined(__x86_64__)) > #define APREF ap > #else > #define APREF *ap > @@ -178,7 +178,7 @@ > if (!(var = (struct variable *) ECPGalloc(sizeof(struct > variable), lineno))) > return false; > > -#if defined(__GNUC__) && (defined (__powerpc__) || defined(__AMD64__) || > defined(__x86_64__)) > +#if defined(__GNUC__) && (defined (__powerpc__) || defined(__amd64__) || > defined(__x86_64__)) > ECPGget_variable(ap, type, var, true); > #else > ECPGget_variable(&ap, type, var, true); > Index: src/interfaces/ecpg/ecpglib/extern.h > === > RCS file: /projects/cvsroot/pgsql/src/interfaces/ecpg/ecpglib/extern.h,v > retrieving revision 1.13 > diff -u -u -r1.13 extern.h > --- src/interfaces/ecpg/ecpglib/extern.h 2 Jun 2005 12:35:11 - > 1.13 > +++ src/interfaces/ecpg/ecpglib/extern.h 16 Jun 2005 14:35:09 - > @@ -125,7 +125,7 @@ > bool ECPGstore_result(const PGresult *results, int act_field, >const struct statement * stmt, struct variable > * var); > bool ECPGstore_input(const int, const bool, const struct variable *, > const char **, bool *); > -#if defined(__GNUC__) && (defined (__powerpc__) || defined(__AMD64__) || > defined(__x86_64__)) > +#if defined(__GNUC__) && (defined (__powerpc__) || defined(__amd64__) || > defined(__x86_64__)) >// work around a gcc/ABI bug with va_lists on ppc+amd64 > void ECPGget_variable(va_list, enum ECPGttype, struct variable *, > bool); > #else > > ---(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 8: explain analyze is your friend
Re: [PATCHES] Python setof patch
Patch applied. Thanks.
---
Gerrit van Dyk wrote:
> Hi,
>
> This patch allows the PL/Python module to do (SRF) functions.
>
> The patch was taken from the CVS version.
>
> I have modified the plpython.c file and have added a test sql script for
> testing the functionality. It was actually the script that was in the
> 8.0.3 version but have since been removed.
>
> In order to signal the end of a set, the called python function must
> simply return plpy.EndOfSet and the set would be returned.
>
> Please feel free to email me if you experience any problems.
>
> My next project I am working in is to get the PL/Python module to return
> records (ie. structured data)
>
> Regards
> Gerrit van Dyk
> AgileWorks (Pty) Ltd
> diff -c -r -P orig/pgsql/src/pl/plpython/plpython.c
> new/pgsql/src/pl/plpython/plpython.c
> *** orig/pgsql/src/pl/plpython/plpython.c 2005-06-15 10:55:02.0
> +0200
> --- new/pgsql/src/pl/plpython/plpython.c 2005-06-14 14:12:01.0
> +0200
> ***
> *** 286,291
> --- 286,294
> static PyObject *PLy_exc_fatal = NULL;
> static PyObject *PLy_exc_spi_error = NULL;
>
> + /* End-of-set Indication */
> + static PyObject *PLy_endofset = NULL;
> +
> /* some globals for the python module
>*/
> static char PLy_plan_doc[] = {
> ***
> *** 770,775
> --- 773,788
> fcinfo->isnull = true;
> rv = (Datum) NULL;
> }
> + /* test for end-of-set condition */
> + else if (fcinfo->flinfo->fn_retset && plrv == PLy_endofset)
> + {
> +ReturnSetInfo *rsi;
> +
> +fcinfo->isnull = true;
> +rv = (Datum)NULL;
> +rsi = (ReturnSetInfo *)fcinfo->resultinfo;
> +rsi->isDone = ExprEndResult;
> + }
> else
> {
> fcinfo->isnull = false;
> ***
> *** 2317,2325
> --- 2330,2340
> PLy_exc_error = PyErr_NewException("plpy.Error", NULL, NULL);
> PLy_exc_fatal = PyErr_NewException("plpy.Fatal", NULL, NULL);
> PLy_exc_spi_error = PyErr_NewException("plpy.SPIError", NULL, NULL);
> + PLy_endofset = PyErr_NewException("plpy.EndOfSet",NULL,NULL);
> PyDict_SetItemString(plpy_dict, "Error", PLy_exc_error);
> PyDict_SetItemString(plpy_dict, "Fatal", PLy_exc_fatal);
> PyDict_SetItemString(plpy_dict, "SPIError", PLy_exc_spi_error);
> + PyDict_SetItemString(plpy_dict, "EndOfSet", PLy_endofset);
>
> /*
>* initialize main module, and add plpy
> diff -c -r -P orig/pgsql/src/pl/plpython/sql/plpython_setof.sql
> new/pgsql/src/pl/plpython/sql/plpython_setof.sql
> *** orig/pgsql/src/pl/plpython/sql/plpython_setof.sql 1970-01-01
> 02:00:00.0 +0200
> --- new/pgsql/src/pl/plpython/sql/plpython_setof.sql 2005-06-14
> 14:12:01.0 +0200
> ***
> *** 0
> --- 1,12
> +
> + CREATE or replace FUNCTION test_setof() returns setof text
> + AS
> + 'if GD.has_key("calls"):
> + GD["calls"] = GD["calls"] + 1
> + if GD["calls"] > 2:
> + del GD["calls"]
> + return plpy.EndOfSet
> + else:
> + GD["calls"] = 1
> + return str(GD["calls"])'
> + LANGUAGE plpythonu;
>
> ---(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 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [PATCHES] Bitmap index AM
I've managed to fix all bugs I've met so far. Attached is an updated patch. Comments: o README should be updated to reflect the changes I've done recently; o I'm still haven't tested ammarkpos and amrestrpos functions --- planner is choosing Nested Loop all the time and my knowledge of query tuning isn't that good to force Merge Join usage; o The main reason no space is freed during ambulkdelete is that that would make inserts and updates really slow. It would take really small amount of time to determine free slot in the List-of-CTIDs, but then I'd have to read and check the majority of bitmap storage pages, as there is no easy way to determine page where bit at given position belongs (or I don't see it at the moment). The cause --- page contains both, plain and compressed blocks. And the number of tuples one page of bitmap storage covers may significantly vary, thus I have to check each page to find the one where newly inserted tuple should belong. And such search should be done for each value in the indexed attributes domain. But I'm willing to discuss any opinions and waiting for the feedback. Maybe we can find a good compromise here. Thanks! -- Victor Y. Yegorov diff-20050705-2.gz Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] silence GCC4 warning
Peter Eisentraut wrote: A simple test shows that this is not the case, even with lots of -W options. Right -- AFAICS we only get the following warnings of this class with GCC 4.0 on current sources: % gcc --version gcc-4.0 (GCC) 4.0.1 20050701 (prerelease) (Debian 4.0.0-12) [...] % make -s clean all [...] In file included from /home/neilc/pgsql/src/backend/regex/regexec.c:1070: /home/neilc/pgsql/src/backend/regex/rege_dfa.c: In function 'getvacant': /home/neilc/pgsql/src/backend/regex/rege_dfa.c:581: warning: 'lastap.ss' may be used uninitialized in this function /home/neilc/pgsql/src/backend/regex/rege_dfa.c:581: warning: 'lastap.co' may be used uninitialized in this function /home/neilc/pgsql/src/backend/utils/adt/inet_net_ntop.c: In function 'inet_net_ntop_ipv6': /home/neilc/pgsql/src/backend/utils/adt/inet_net_ntop.c:427: warning: 'cur.len' may be used uninitialized in this function /home/neilc/pgsql/src/backend/utils/adt/inet_net_ntop.c:427: warning: 'best.len' may be used uninitialized in this function /home/neilc/pgsql/src/interfaces/ecpg/pgtypeslib/datetime.c: In function 'PGTYPESdate_defmt_asc': /home/neilc/pgsql/src/interfaces/ecpg/pgtypeslib/datetime.c:335: warning: 'tm.tm_mday' may be used uninitialized in this function /home/neilc/pgsql/src/interfaces/ecpg/pgtypeslib/datetime.c:335: warning: 'tm.tm_mon' may be used uninitialized in this function /home/neilc/pgsql/src/interfaces/ecpg/pgtypeslib/datetime.c:335: warning: 'tm.tm_year' may be used uninitialized in this function /home/neilc/pgsql/src/pl/plpgsql/src/pl_comp.c: In function 'plpgsql_parse_tripwordtype': /home/neilc/pgsql/src/pl/plpgsql/src/pl_comp.c:1372: warning: 'cp[0]' may be used uninitialized in this function /home/neilc/pgsql/src/pl/plpgsql/src/pl_comp.c:1372: warning: 'cp[1]' may be used uninitialized in this function (Plus many errors regarding signedness, which is a separate matter.) All these warnings like somewhat reasonable, AFAICS: while the code may actually always initialize the variables before using them, it's understandable that the compiler can't infer this automatically. And this is surely a tiny fraction of the changes that would be required if something as fundamental as initialization via out parameters was broken. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] Dbsize backend integration
> -Original Message- > From: Robert Treat [mailto:[EMAIL PROTECTED] > Sent: 04 July 2005 18:21 > To: Dave Page > Cc: Tom Lane; Dawid Kuroczko; Andreas Pflug; Bruce Momjian; > PostgreSQL-patches; PostgreSQL-development > Subject: Re: [HACKERS] [PATCHES] Dbsize backend integration > > > Actually I'd agree with Tom, pg_dbfile_size is ugly, and > suggest to me I could > use a filename as an argument. I won't repeat Bruce's reply to you, but as an FYI, the reason Bruce suggested pg_dbfile_size over the nicer pg_file_size is that our instrumentation patch includes that particular function, which does accept a filename (provided it's under $PGDATA or the log directory). Dbfile was meant to indicate it's a database related file, rather than some arbitrary filename. /D ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] [PATCH] pgcrypto: pgp_encrypt v3
Bruce Momjian wrote: Your patch has been added to the PostgreSQL unapplied patches list That is not the latest version of Marko's patch. But in any case, the patch is not yet ready for application: http://archives.postgresql.org/pgsql-patches/2005-07/msg00077.php -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] Dbsize backend integration
On Monday 04 July 2005 13:25, Bruce Momjian wrote:
> Robert Treat wrote:
> > Actually I'd agree with Tom, pg_dbfile_size is ugly, and suggest to me I
> > could use a filename as an argument. ISTM that if we think that
> > functions like pg_database_size and pg_tablespace_size all make sense,
> > the natural extension would be functions called pg_index_size to tell us
> > the size of an index, pg_table_size to tell us the size of a table
> > (table+toast) without it's indexes, and some form of
> > pg_table_plus_indexes_size for a table and its indexes for those that
> > feel we need both. I'm not sold we need a function that can return
> > either an index or table size, but if so something like pg_object_size
> > seems ambigious enough to work, and is future proof enough to handle
> > things like materialized views when and if they arise.
>
> You are into the cycle we were in. We discussed pg_object size (too
> vague) and pg_index_size (needs pg_toast_size too, and maybe toast
> indexes; too many functions).
Yeah, I read those discussions, and think you were better off then than you
are now, which is why I went back to it somewhat.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] Dbsize backend integration
You are into the cycle we were in. We discussed pg_object size (too vague) and pg_index_size (needs pg_toast_size too, and maybe toast indexes; too many functions). Yeah, I read those discussions, and think you were better off then than you are now, which is why I went back to it somewhat. To be honest, the amount of effort being expended on this naming discussion far outweighs the benefits. Maybe it's time for a core member to step in and just resolve it - one way or the other? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PATCHES] Autovacuum integration patch
Alvaro Herrera wrote: On Thu, Jun 30, 2005 at 12:03:12AM -0400, Matthew T. O'Connor wrote: Alvaro Herrera wrote: Sorry, forgot to mention: - There are no docs I can help here as long as I don't have to have the docs done before July 1. You don't. Ok, so I'm not writing any docs, I leave that to you :-) Ok. - There are no ALTER TABLE commands to change the pg_autovacuum attributes for a table. (Enable/disable, set thresholds and scaling factor) I don't think we need this do we? Mucking around in the autovacuum table shouldn't cause the system any serious problems, if you do mess up your values, it's easy to just reset them all to 0 and start back with the defaults. The problem is you have to be superuser to be able to do it. An ALTER TABLE command would allow the table and database owners to do it. Fair point. However I would still put this in the category of nice additions, but we really don't *NEED* right now. ---(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: [PATCHES] Autovacuum integration patch
XID wraparound: The patch as submitted doesn't handle XID wraparound issues. The old contrib autovacuum would do an XID wraparound check as it's 1st operation upon connecting to a database. If XID wraparound was looks like it's going to be a problem soon, then the whole database would be vacuumed, eliminating the need to check specific tables. Hmm. Yes, this patch doesn't handle Xid wraparound. This should be easy to add though. Anyway, I was thinking that we could add a "last vacuum Xid" to pg_autovacuum, and handle Xid wraparound for each table separately -- this means you don't have to issue huge whole-database VACUUMs, because it will be handled nicely for each table. Storing the last vacuum Xid in pg_database would have to be rethought. The current implementation of XID wraparound requires that the vacuum command be run against the entire database, you can not run it on a per table basis and have it work. At least that is my understanding, it would require some reworking of the vacuum system and I have no idea what is involved in that. For now, we should just do it the simple way. BTW, I think this is a candidate for only being done during the maintenance window. Maybe what we could do is have a separate pg_vacuum table to hold constantly-moving information about the tables: last vacuum Xid, count of tuples at last vacuum/analyze, etc; so pg_autovacuum would only hold the constants for autovacuum equations. This pg_vacuum table would be updated by VACUUM, not autovacuum, so it would be always correct and up- to-date. I'm not sure I see the value in a new pg_vacuum table. reltuples already has the tuple count from the last vacuum and I don't think last XID on a per table basis is helpful. Better logging of autovacuum activity: I think the we could use some more detail in the debug elog statements. For example showing exactly what autovacuum believes the threshold and current count is. Ok. I actually had lots more logging in the original patch, but I removed it because it was too verbose. Again, it's easy to add. Well, I don't know what is best, but it would be nice to be able to get at the information that tells you why autovacuum did or did not take action. Perhaps put back what you had in, but move it up to a higher debug level. FWIW, I think the debug info from the contrib version was sufficient. How to deal with shared relations: As an optimization, the contrib version of autovacuum treated shared relations different than it treated the rest when connected to any database that is not template1. Ah, interesting. Yes, I think that could be done too. Very easy to do. Anyway, the shared relations are not that big usually, so this shouldn't be an issue. Agreed this is not a big issue, it's a bit of a micro optimization. Couple of other thoughts: Do the vacuum commands respect the GUC vacuum delay settings? Huh, I don't know. I just issue a vacuum() call. That function sets the delay settings AFAICS, so I think it should be working. Can someone confirm this? Should we be able to set per table vacuum delay settings? We could set that in the hypotetical pg_vacuum relation. Again, I don't think this would be good for the pg_vacuum table, I think it should be in the autovacuum table, because what a user wants autovacuum to do might be different than what he wants a manually run vacuum to do. This patch doesn't have the "maintenance window" that was discussed a while ago. True. I have several questions about it. Where would that information be stored, in another system catalog? Would it be per-database or per-table? What happens if I'm not able to do all work inside the maintenance window, is it left for the next one? If the maintenance window ends and there is a vacuum running, is it terminated or is it allowed to continue? One could argue that it should be per database, but I think per cluster should be sufficient. I think it could be handled as few GUC settings, such as: autovac_maint_begin = "1AM" autovac_maint_duration = 4 (measured in hours) autovac_maint_factor = .5 (reduce the thresholds by half during the maintenance window, this option might be good to have on a per table basis, if so, then add it to the pg_autovacuum table) If there is still work to do after the maint window expires, then it's left for next time or when the regular threshold is exceeded which ever happens first. I wouldn't terminate an in progress vacuum. There is a very important issue I forgot to mention. This autovacuum process only handles databases that exist in the Stats hash table. However, the stat hash table only has information about databases and tables that have been used in the current postmaster run. So if you don't connect to a database regularly, that database won't get "autovacuumed" after a postmaster restart. I think (but IMBFOS) that this is also true for individual table
[PATCHES] INSERT ... RETURNING
Hi there, Attached is a patch (by Gavin Sherry, fixed up to apply to 8.1 by me) that implements INSERT ... RETURNING functionality. It does work for the common case of RETURNING the value of a serial/sequence column, but gets confused when returning results out-of-order (CREATE TABLE x (a int, b int), INSERT ... RETURNING b, a) and doesn't let you specify the same column multiple times (INSERT ... RETURNING b, b). These will be addressed soon. Regards, Omar Kilani insert-returning-6.patch Description: Binary data ---(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: [PATCHES] silence GCC4 warning
Neil Conway <[EMAIL PROTECTED]> writes: > Peter Eisentraut wrote: >> A simple test shows that this is not the case, even with lots of -W >> options. > Right -- AFAICS we only get the following warnings of this class with > GCC 4.0 on current sources: Hmm ... my comments were based on experiments with a prerelease gcc4 that was circulating inside Red Hat; IIRC it showed some hundreds of warnings of this type on the PG sources :-(. It sounds like they've improved it somewhat, but I'm still not clear on what it is warning about that prior releases did not. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
