[PATCHES] plperl better array support
The attached patch (submitted for comment) is somewhat adapted from one
submitted last October. This allows returning a perl array where a
postgres array is expected.
example:
andrew=# create function blurfl() returns text[] language plperl as $$
andrew$# return ['a','b','c','ab\c'];
andrew$# $$;
CREATE FUNCTION
andrew=# select blurfl();
blurfl
---
{a,b,c,a\b\\c}
Unlike the patch from October, this patch does not implement ANYARRAY or
ANYELEMENT pseudotypes. However it does escape/quote array elements
where necessary. It also preserves the old behaviour (if the plperl
function returns a string it is just passed through).
I'm not happy about constructing a string which we then parse out again
into an array - that strikes me as quite inefficient. (And there are
other inelegancies that I'd like to get rid of.) Much better would be to
use some array contruction calls directly - any pointers on how to do
that would be apprciated :-)
cheers
andrew
Index: plperl.c
===
RCS file: /projects/cvsroot/pgsql/src/pl/plperl/plperl.c,v
retrieving revision 1.76
diff -c -r1.76 plperl.c
*** plperl.c 5 Jun 2005 03:16:35 - 1.76
--- plperl.c 20 Jun 2005 08:54:15 -
***
*** 80,85
--- 80,86
bool lanpltrusted;
bool fn_retistuple; /* true, if function returns tuple */
bool fn_retisset; /* true, if function returns set */
+ boolfn_retisarray; /* true if function returns array */
Oid result_oid; /* Oid of result type */
FmgrInfo result_in_func; /* I/O function and arg for result type */
Oid result_typioparam;
***
*** 323,328
--- 324,408
return tup;
}
+ /* substitute(string, pattern)
+ *
+ * Used for =~ operations that modify their left-hand side (s/// and tr///)
+ *
+ * Returns the number of successful matches, and
+ * modifies the input string if there were any.
+ *
+ * (almost) straight from perlembed man page.
+ */
+
+ static I32
+ plperl_substitute(SV **string, char *pattern)
+ {
+ SV *command = NEWSV(1099, 0);
+ I32 retval;
+ STRLEN n_a;
+
+ sv_setpvf(command, $_plp_string = '%s'; ($_plp_string =~ %s),
+ SvPV(*string,n_a), pattern);
+
+ retval = eval_sv(command, TRUE);
+
+ *string = get_sv(_plp_string, FALSE);
+ return retval;
+ }
+
+ /*
+ * convert perl array to postgres string representation
+ */
+ static SV*
+ plperl_convert_to_pg_array(SV *src)
+ {
+ SV* rv;
+ SV**val;
+ AV* internal;
+ int len,
+ i;
+
+ internal=(AV*)SvRV(src);
+ len = av_len(internal)+1;
+
+ rv = newSVpv({ ,0);
+ for(i=0; ilen; i++)
+ {
+ val = av_fetch(internal, i, FALSE);
+ if (SvTYPE(*val)==SVt_RV)
+ {
+ /*
+ * If there's a reference type val, call this func
+ * recursively to handle a nested array, and error out on any
+ * other reference type.
+ */
+
+ if (SvTYPE(SvRV(*val))==SVt_PVAV)
+ sv_catpvf(rv, %s,
+ SvPV(plperl_convert_to_pg_array(*val),PL_na) );
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg(returned array contains non-array ref)));
+ }
+ else
+ {
+ /* non-reference case - append the stringified value */
+ SV * copyval;
+
+ copyval= newSVpv(SvPV(*val,PL_na),0);
+ plperl_substitute(copyval,s/([\])/$1/g);
+ sv_catpvf(rv, \%s\, SvPV(copyval,PL_na));
+ }
+
+ if (i != len-1) sv_catpvf(rv, ,);
+ }
+
+ sv_catpvf(rv, });
+
+ return rv;
+ }
+
/* Set up the arguments for a trigger call. */
***
*** 817,823
rsi = (ReturnSetInfo *)fcinfo-resultinfo;
! if (prodesc-fn_retisset) {
if (!rsi || !IsA(rsi, ReturnSetInfo) ||
(rsi-allowedModes SFRM_Materialize) == 0 ||
rsi-expectedDesc == NULL)
--- 897,904
rsi = (ReturnSetInfo *)fcinfo-resultinfo;
! if (prodesc-fn_retisset)
! {
if (!rsi || !IsA(rsi, ReturnSetInfo) ||
(rsi-allowedModes SFRM_Materialize) == 0 ||
rsi-expectedDesc == NULL)
***
*** 838,844
int i = 0;
SV **svp = 0;
AV *rav = (AV *)SvRV(perlret);
! while ((svp = av_fetch(rav, i, FALSE)) != NULL) {
plperl_return_next(*svp);
i++;
}
--- 919,926
int i = 0;
SV **svp = 0;
AV *rav = (AV *)SvRV(perlret);
! while ((svp = av_fetch(rav, i, FALSE)) != NULL)
! {
plperl_return_next(*svp);
i++;
}
***
*** 852,858
}
rsi-returnMode = SFRM_Materialize;
! if (prodesc-tuple_store) {
rsi-setResult = prodesc-tuple_store;
rsi-setDesc = prodesc-tuple_desc;
}
--- 934,941
}
rsi-returnMode =
[PATCHES] Default database patch
The attached patch modifies initdb to create a default database called 'postgres' when the cluster is initialised. Documentation updates are included, including updates to relevant examples to encourage users to use this database in place of template1. I have not modified utilities like createuser however - this is to ensure there are no dependencies on the postgres database within the server itself, thus giving users with limited disk space the option of removing the postgres database if required. Please apply for 8.1. Regards, Dave default-db.patch Description: default-db.patch ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] code cleanup for tz
Neil Conway [EMAIL PROTECTED] writes: This patch makes various cosmetic improvements to the timezone code: remove the use of the register qualifier, make some function declaration syntax a bit more consistent, etc. I think mostly what you are doing here is causing code drift from the upstream zic code. I don't think that's a very good idea, since we do need to be able to track and apply bug fixes from them from time to time ... regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Default database patch
Attached is an updated version of this patch which /does/ update utilities to use the postgres database by default, per comments on -hackers. Regards, Dave -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Page Sent: 20 June 2005 13:07 To: PostgreSQL-patches Subject: [PATCHES] Default database patch The attached patch modifies initdb to create a default database called 'postgres' when the cluster is initialised. Documentation updates are included, including updates to relevant examples to encourage users to use this database in place of template1. I have not modified utilities like createuser however - this is to ensure there are no dependencies on the postgres database within the server itself, thus giving users with limited disk space the option of removing the postgres database if required. Please apply for 8.1. Regards, Dave pgsql.patch Description: pgsql.patch ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] plperl better array support
On Mon, Jun 20, 2005 at 05:06:32AM -0400, Andrew Dunstan wrote:
The attached patch (submitted for comment) is somewhat adapted from one
submitted last October. This allows returning a perl array where a
postgres array is expected.
example:
andrew=# create function blurfl() returns text[] language plperl as $$
andrew$# return ['a','b','c','ab\c'];
andrew$# $$;
CREATE FUNCTION
andrew=# select blurfl();
blurfl
---
{a,b,c,a\b\\c}
Unlike the patch from October, this patch does not implement ANYARRAY or
ANYELEMENT pseudotypes. However it does escape/quote array elements
where necessary. It also preserves the old behaviour (if the plperl
function returns a string it is just passed through).
I'm not happy about constructing a string which we then parse out again
into an array - that strikes me as quite inefficient. (And there are
other inelegancies that I'd like to get rid of.) Much better would be to
use some array contruction calls directly - any pointers on how to do
that would be apprciated :-)
Here's some pointers I'm qualified to point out, for what that's worth. ;)
In src/backend/utils/adt/arrayfuncs.c there are direct
array-manipulation functions. In the perlapi docs for perl, there are
ways to manipulate arrays directly. It should be possible to go
between perl arrays and postgresql arrays this way, except...perl
arrays can contain undef, which usually translates as NULL. Would it
be easier to add NULL support to postgresql arrays than to handle
undefs in perl arrays automagically?
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [PATCHES] plperl better array support
David Fetter wrote: In src/backend/utils/adt/arrayfuncs.c there are direct array-manipulation functions. And in other places - I had already found that stuff :-) . But a worked example would help. If it's not available I'll muddle through some time. In the perlapi docs for perl, there are ways to manipulate arrays directly. The patch is already using them. In fact, you can't delve even mildly into plperl.c without having the perlapi docs close at hand. It should be possible to go between perl arrays and postgresql arrays this way, except...perl arrays can contain undef, which usually translates as NULL. Would it be easier to add NULL support to postgresql arrays than to handle undefs in perl arrays automagically? We should have NULL support in arrays. In the absence of that I'm not particularly inclined to make special provsion for undef in an array. cheers andrew ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] WAL bypass for CTAS
Tom has applied this patch. Thanks. --- Simon Riggs wrote: I enclose a complete patch for avoiding WAL usage for CREATE TABLE AS SELECT, when not in archive mode (PITR). The main use case for this is large BI environments that create summary tables or prejoined tables, though there are many general applications. There is no user interface for this. The speed gain is automatic, when archiving is not enabled. This contains all the lower level machinery required to do the same thing for COPY, as discussed on hackers. The machinery includes some additional freespace thinkery, aimed mainly at the forthcoming COPY patch, which solely needs to be integrated with Alon's work. Patch is diff -c format, compiles and make checks on cvstip as of now. No performance tests *on this patch*, though the general principle has already been proven via a similar prototype patch not published on list. Best Regards, Simon Riggs [ 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 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PATCHES] WAL bypass for CTAS
Simon Riggs [EMAIL PROTECTED] writes:
I enclose a complete patch for avoiding WAL usage for CREATE TABLE AS
SELECT, when not in archive mode (PITR). The main use case for this is
large BI environments that create summary tables or prejoined tables,
though there are many general applications.
Applied after heavy corrections --- there were a number of things wrong
with this simple patch, starting with having gotten the tests
backwards :-(, and extending to not having actually flushed the data
before commit (smgrimmedsync isn't enough, you have to
FlushRelationBuffers).
A consideration we had all missed in the original discussions is that
if the transaction doesn't emit any WAL records at all,
RecordTransactionCommit will think that it need not WAL-log the
transaction commit, leading to the possibility that the commit is lost
even though all the data is preserved :-(
This is not a hazard for CREATE TABLE AS, since it will certainly have
emitted WAL records while creating the table's catalog entries. It will
be a very real hazard for COPY however. The cleanest solution I can
think of is that the COPY code should emit a WAL record for the first
tuple copied in, but not for later ones. To this end, I separated the
use_wal and use_fsm aspects of what the patch was doing.
I didn't apply the freespace.c changes either; that struck me as a
serious kluge with no real benefit. We can just omit updating the FSM's
running average, if it even has one. (ISTM there's a reasonable
argument to be made that the tuple sizes during CREATE/COPY might not be
representative of later requests anyway.)
Patch as applied is attached.
regards, tom lane
*** src/backend/access/heap/heapam.c.orig Wed Jun 8 11:50:21 2005
--- src/backend/access/heap/heapam.cMon Jun 20 13:50:16 2005
***
*** 1034,1042
*
* The new tuple is stamped with current transaction ID and the specified
* command ID.
*/
Oid
! heap_insert(Relation relation, HeapTuple tup, CommandId cid)
{
TransactionId xid = GetCurrentTransactionId();
Buffer buffer;
--- 1034,1053
*
* The new tuple is stamped with current transaction ID and the specified
* command ID.
+ *
+ * If use_wal is false, the new tuple is not logged in WAL, even for a
+ * non-temp relation. Safe usage of this behavior requires that we arrange
+ * that all new tuples go into new pages not containing any tuples from other
+ * transactions, that the relation gets fsync'd before commit, and that the
+ * transaction emits at least one WAL record to ensure RecordTransactionCommit
+ * will decide to WAL-log the commit.
+ *
+ * use_fsm is passed directly to RelationGetBufferForTuple, which see for
+ * more info.
*/
Oid
! heap_insert(Relation relation, HeapTuple tup, CommandId cid,
! bool use_wal, bool use_fsm)
{
TransactionId xid = GetCurrentTransactionId();
Buffer buffer;
***
*** 1086,1092
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation, tup-t_len, InvalidBuffer);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
--- 1097,1104
heap_tuple_toast_attrs(relation, tup, NULL);
/* Find buffer to insert this tuple into */
! buffer = RelationGetBufferForTuple(relation, tup-t_len,
!
InvalidBuffer, use_fsm);
/* NO EREPORT(ERROR) from here till changes are logged */
START_CRIT_SECTION();
***
*** 1096,1102
pgstat_count_heap_insert(relation-pgstat_info);
/* XLOG stuff */
! if (!relation-rd_istemp)
{
xl_heap_insert xlrec;
xl_heap_header xlhdr;
--- 1108,1119
pgstat_count_heap_insert(relation-pgstat_info);
/* XLOG stuff */
! if (relation-rd_istemp)
! {
! /* No XLOG record, but still need to flag that XID exists on
disk */
! MyXactMadeTempRelUpdate = true;
! }
! else if (use_wal)
{
xl_heap_insert xlrec;
xl_heap_header xlhdr;
***
*** 1151,1161
PageSetLSN(page, recptr);
PageSetTLI(page, ThisTimeLineID);
}
- else
- {
- /* No XLOG record, but still need to flag that XID exists on
disk */
- MyXactMadeTempRelUpdate = true;
- }
END_CRIT_SECTION();
--- 1168,1173
***
*** 1183,1189
Oid
simple_heap_insert(Relation relation, HeapTuple tup)
{
! return heap_insert(relation, tup, GetCurrentCommandId());
}
/*
--- 1195,1201
Oid
simple_heap_insert(Relation relation, HeapTuple tup)
{
! return
Re: [PATCHES] WAL bypass for CTAS
On Mon, 2005-06-20 at 14:50 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I enclose a complete patch for avoiding WAL usage for CREATE TABLE AS SELECT, when not in archive mode (PITR). The main use case for this is large BI environments that create summary tables or prejoined tables, though there are many general applications. Applied Thanks after heavy corrections --- there were a number of things wrong with this simple patch, starting with having gotten the tests backwards :-( Sorry, I thought I had corrected that error before submission. I was aware that I had made that error earlier. and extending to not having actually flushed the data before commit (smgrimmedsync isn't enough, you have to FlushRelationBuffers). I followed the logic as seen in nbtsort.c as you suggested. That code doesn't perform a FlushRelationBuffers and it looks like I fooled myself into thinking the CTAS/SELECT INTO case was also in local. Perhaps we should be building CTAS/SELECT INTO in local buffers anyway? It looks like we could save time by avoiding shared_buffers completely and build up a whole page before writing it anywhere. (But thats a story for another day). Perhaps this is also related to metapage errors, since the metapage is always the last page to be written? A consideration we had all missed in the original discussions is that if the transaction doesn't emit any WAL records at all, RecordTransactionCommit will think that it need not WAL-log the transaction commit, leading to the possibility that the commit is lost even though all the data is preserved :-( This is not a hazard for CREATE TABLE AS, since it will certainly have emitted WAL records while creating the table's catalog entries. It will be a very real hazard for COPY however. OK, but I haven't written that patch yet! The cleanest solution I can think of is that the COPY code should emit a WAL record for the first tuple copied in, but not for later ones. To this end, I separated the use_wal and use_fsm aspects of what the patch was doing. Not very clean, but will do as you suggest. I didn't apply the freespace.c changes either; that struck me as a serious kluge with no real benefit. We can just omit updating the FSM's running average, if it even has one. (ISTM there's a reasonable argument to be made that the tuple sizes during CREATE/COPY might not be representative of later requests anyway.) I was striving for completeness only. I was doubtful about that part of the patch, but thought I'd add that rather than have you say I hadn't thought about the FSM avg_request_size. I put those changes in mainly for COPY. If you don't make any request at all to FSM then a relation never gets to the MRU relation FSM list. I agree that it is not strictly necessary, but leaving it off would be a change in behaviour, since COPY did previously cause the relation to get to the MRU. That could be a problem, since a relation might not then be allocated any FSM pages following a vacuum. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] WAL bypass for CTAS
On Mon, 2005-06-20 at 17:09 -0400, Alvaro Herrera wrote: On Mon, Jun 20, 2005 at 09:55:12PM +0100, Simon Riggs wrote: I put those changes in mainly for COPY. If you don't make any request at all to FSM then a relation never gets to the MRU relation FSM list. I agree that it is not strictly necessary, but leaving it off would be a change in behaviour, since COPY did previously cause the relation to get to the MRU. That could be a problem, since a relation might not then be allocated any FSM pages following a vacuum. Is that a problem? Not for me, but I wanted to explain the change in behaviour that implies. If the pages don't fit in FSM, then maybe the system is misconfigured anyway. The person running the DW should just increase the FSM settings, which is hardly a costly thing because it uses so little memory. If you aren't on the relation list you don't get any more pages than the minimum. No matter how many fsm_pages you allocate. If fsm_pages covers everything, then you are right, there is no problem. Best Regards, Simon Riggs ---(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: [PATCHES] WAL bypass for CTAS
On Mon, Jun 20, 2005 at 09:55:12PM +0100, Simon Riggs wrote: I put those changes in mainly for COPY. If you don't make any request at all to FSM then a relation never gets to the MRU relation FSM list. I agree that it is not strictly necessary, but leaving it off would be a change in behaviour, since COPY did previously cause the relation to get to the MRU. That could be a problem, since a relation might not then be allocated any FSM pages following a vacuum. Is that a problem? If the pages don't fit in FSM, then maybe the system is misconfigured anyway. The person running the DW should just increase the FSM settings, which is hardly a costly thing because it uses so little memory. -- Alvaro Herrera (alvherre[a]surnet.cl) No renuncies a nada. No te aferres a nada. ---(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: [PATCHES] code cleanup for tz
Tom Lane wrote: I think mostly what you are doing here is causing code drift from the upstream zic code. I don't think that's a very good idea, since we do need to be able to track and apply bug fixes from them from time to time ... Why run pgindent on the timezone code, then? That seems guaranteed to cause a lot more merge headaches... -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] Optional REFERENCES Feature in CREATE TRIGGER Command
[EMAIL PROTECTED] writes: Below were the communications between Tom and me before I implemented this project. I just did what he asked me to do. Part of it, maybe --- my point was that without any support in (at least) plpgsql, the feature is of only academic interest. There's not a lot of point in applying the patch when it does not do anything. Also, we tend to look with suspicion on such stuff because once you actually write code that uses the feature, you often find that you should have designed it a little differently. Nailing down the catalog representation in advance of having working code that does something useful with it is a good recipe for making mistakes. (To take one example, why does the patch only support one name? Don't you need two for the UPDATE case?) In any case the patch is missing documentation and pg_dump support, making it even less possible to use it for anything. It's project policy that all system catalog columns be documented in catalogs.sgml, and what's the use of DDL that won't survive a dump and reload? 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: [PATCHES] code cleanup for tz
Tom Lane wrote: Well, it's certainly hopeless to expect patch to fix it :-(. But the further the code drifts the harder it gets to compare manually. Sure, but I don't see how removing a few register qualifiers and so forth is going to make the slightest difference to a manual comparison. If the code was intentionally being kept as close to upstream as possible, I wouldn't have made the changes in the first place -- but since there is little prospect of doing a machine-assisted merge after pgindent, I don't think this patch makes things appreciably worse. -Neil ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[PATCHES] pg_restore: fix bogosity
The Coverity tool picked up some rather bizarre code in _tarGetHeader in
pg_backup_tar.c:
(1) The code doesn't initialize `sum', so the initial does the checksum
match? test is wrong.
(2) The loop that is intended to check for a null block just checks
the first byte of the tar block 512 times, rather than each of the 512
bytes one time (!), which I'm guessing was the intent.
Attached is a patch that I believe should implement what the author
intended. Barring any objections, I'll apply this to HEAD and back
branches today or tomorrow.
-Neil
Index: src/bin/pg_dump/pg_backup_tar.c
===
RCS file: /var/lib/cvs/pgsql/src/bin/pg_dump/pg_backup_tar.c,v
retrieving revision 1.47
diff -c -r1.47 pg_backup_tar.c
*** src/bin/pg_dump/pg_backup_tar.c 25 Jan 2005 22:44:31 - 1.47
--- src/bin/pg_dump/pg_backup_tar.c 20 Jun 2005 01:44:37 -
***
*** 1155,1161
size_t len;
unsigned long ullen;
off_t hPos;
- int i;
bool gotBlock = false;
while (!gotBlock)
--- 1155,1160
***
*** 1178,1184
hPos = ctx-tarFHpos;
/* Read a 512 byte block, return EOF, exit if short */
! len = _tarReadRaw(AH, h[0], 512, NULL, ctx-tarFH);
if (len == 0) /* EOF */
return 0;
--- 1177,1183
hPos = ctx-tarFHpos;
/* Read a 512 byte block, return EOF, exit if short */
! len = _tarReadRaw(AH, h, 512, NULL, ctx-tarFH);
if (len == 0) /* EOF */
return 0;
***
*** 1188,1207
(unsigned long) len);
/* Calc checksum */
! chk = _tarChecksum(h[0]);
/*
! * If the checksum failed, see if it is a null block. If so, then
! * just try with next block...
*/
-
if (chk == sum)
gotBlock = true;
else
{
for (i = 0; i 512; i++)
{
! if (h[0] != 0)
{
gotBlock = true;
break;
--- 1187,1208
(unsigned long) len);
/* Calc checksum */
! chk = _tarChecksum(h);
! sscanf(h[148], %8o, sum);
/*
! * If the checksum failed, see if it is a null block. If so,
! * silently continue to the next block.
*/
if (chk == sum)
gotBlock = true;
else
{
+ int i;
+
for (i = 0; i 512; i++)
{
! if (h[i] != 0)
{
gotBlock = true;
break;
***
*** 1213,1219
sscanf(h[0], %99s, tag);
sscanf(h[124], %12lo, ullen);
len = (size_t) ullen;
- sscanf(h[148], %8o, sum);
{
char buf[100];
--- 1214,1219
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [PATCHES] code cleanup for tz
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: Tom Lane wrote: I think mostly what you are doing here is causing code drift from the upstream zic code. I don't think that's a very good idea, since we do need to be able to track and apply bug fixes from them from time to time ... Why run pgindent on the timezone code, then? That seems guaranteed to cause a lot more merge headaches... Well, it's certainly hopeless to expect patch to fix it :-(. But the further the code drifts the harder it gets to compare manually. This isn't an academic concern; I went through it once already, and I expect we'll want to do it again every release cycle when we sync the zic database with upstream. I think the idea is that we will pgindent the new release of the timezone code and then diff that against our current CVS. -- 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: [PATCHES] TODO Item - Return compressed length of TOAST datatypes
I did a few cleanups on the last patch. Please examine this one instead.
The changes are:
1. Add documentation for pg_datum_length builtin.
2. Correct some typos in the code comments.
3. Move the code in toastfuncs.c to varlena.c as it is probably the
correct place.
4. Use ereport instead of elog.
5 Quiet compiler warning in pg_datum_length.
Best wishes
Mark
Mark Kirkwood wrote:
The next iteration -
Hopefully I have got the idea basically right.
I wonder if I have done the am I a varlena the long way.., pls advise
if so!
diff -Nacr ./doc/src/sgml/func.sgml.orig ./doc/src/sgml/func.sgml
*** ./doc/src/sgml/func.sgml.orig Mon Jun 20 15:38:23 2005
--- ./doc/src/sgml/func.sgmlMon Jun 20 15:45:51 2005
***
*** 2187,2192
--- 2187,2200
/row
row
+
entryliteralfunctionpg_datum_length/function(parameterstring/parameter)/literal/entry
+entrytypeinteger/type/entry
+entryNumber of bytes (before toast decompression) in string/entry
+entryliteralpg_datum_length( 'jo\\000se'::bytea)/literal/entry
+entryliteral5/literal/entry
+ /row
+
+ row
entryliteralfunctionposition/function(parametersubstring/parameter
in parameterstring/parameter)/literal/entry
entrytypeinteger/type/entry
entryLocation of specified substring/entry
diff -Nacr ./src/backend/access/heap/tuptoaster.c.orig
./src/backend/access/heap/tuptoaster.c
*** ./src/backend/access/heap/tuptoaster.c.orig Mon Jun 20 17:11:37 2005
--- ./src/backend/access/heap/tuptoaster.c Mon Jun 20 17:11:44 2005
***
*** 1436,1438
--- 1436,1482
return result;
}
+
+ /* --
+ * toast_datum_size
+ *
+ *Show the (possibly compressed) size of a datum
+ * --
+ */
+ Size
+ toast_datum_size(Datum value)
+ {
+
+ varattrib *attr = (varattrib *) DatumGetPointer(value);
+ Sizeresult;
+
+ if (VARATT_IS_EXTERNAL(attr))
+ {
+ /*
+* Attribute is stored externally - If it is compressed too,
+* then we need to get the external datum and calculate its
size,
+* otherwise we just use the external rawsize.
+*/
+ if (VARATT_IS_COMPRESSED(attr))
+ {
+ varattrib *attrext =
toast_fetch_datum(attr);
+ result = VARSIZE(attrext);
+ pfree(attrext);
+ }
+ else
+ {
+ result = attr-va_content.va_external.va_rawsize;
+ }
+ }
+ else
+ {
+ /*
+* Attribute is stored inline either compressed or not, just
+* calculate the size of the datum in either case.
+*/
+ result = VARSIZE(attr);
+ }
+
+ return result;
+
+ }
diff -Nacr ./src/backend/utils/adt/varlena.c.orig
./src/backend/utils/adt/varlena.c
*** ./src/backend/utils/adt/varlena.c.orig Mon Jun 20 14:28:03 2005
--- ./src/backend/utils/adt/varlena.c Mon Jun 20 17:17:58 2005
***
*** 28,33
--- 28,34
#include utils/builtins.h
#include utils/lsyscache.h
#include utils/pg_locale.h
+ #include utils/syscache.h
typedef struct varlena unknown;
***
*** 2330,2333
--- 2331,2396
result_text = PG_STR_GET_TEXT(hexsum);
PG_RETURN_TEXT_P(result_text);
+ }
+
+ /*
+ * Show the (possibly compressed) length of a datum.
+ */
+ Datum
+ pg_datum_length(PG_FUNCTION_ARGS)
+ {
+
+ Datum value = PG_GETARG_DATUM(0);
+ int result;
+
+
+ if (fcinfo-flinfo-fn_extra == NULL)
+ {
+ /*
+* On the first call lookup the datatype of the supplied
argument
+* and check if is a varlena.
+*/
+ Oid argtypeid =
get_fn_expr_argtype(fcinfo-flinfo, 0);
+ HeapTuple tp;
+ int typlen = 0;
+
+
+ tp = SearchSysCache(TYPEOID,
+
ObjectIdGetDatum(argtypeid),
+ 0, 0, 0);
+ if (HeapTupleIsValid(tp))
+ {
+ Form_pg_type typtup = (Form_pg_type) GETSTRUCT(tp);
+ typlen = typtup-typlen;
+ ReleaseSysCache(tp);
+ }
+ else
+ {
+ /* Oid not in pg_type, should never happen. */
+ ereport(ERROR,
+ (errcode(ERRCODE_INTERNAL_ERROR),
+errmsg(invalid typid: %u,
argtypeid)));
+ }
+
+
+
[PATCHES] thousands comma numeric formatting in psql
Hello,
This is my first post to this list.
Sorry if my english it's not so good. It's not my native language.
I'm interrested to now if someone think that having a feature like
'thousands comma delimited numeric formatting' in psql it's a
usefull thing.
I've made a patch for psql that adds this feature, so issuing a
select like this:
my_database= select 1234567.89;
results in:
?column?
--
1,234,567.89
This feature is toggle on/off with a backslash command ('\n'):
my_database= \n
Numeric formatting is off.
my_database= select 1234567.89;
?column?
--
1234567.89
For me, psql it's still the best client for postgres, faster and
flexible than graphic ones. And having tables with many numeric
columns witch contain huge numbers make difficult to read this
numbers.
One solution to deal with this is to use to_char function, but for
complex selects against multiple tables it's not a good option.
Another one is to make a custom function that works like this:
select my_function(... complex subselect ...);
but this seems ugly to me.
By adding the '\n' switch to psql I can make any complex select and
have all numeric fields in result formatted in easy readable form.
I'm not an expert in postgresql, so if someone thinks there is an
easier way to deal with numeric fields, please share.
If my idea is considered usefull I can post the patch to this list.
Best regards,
Eugen.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
