[HACKERS] pgmex, a MATLAB interface for PostgreSQL
DerTech LLC has developed a MATLAB interface for PostgreSQL. It's essentially mex wrappers for the functions in libpq. We're releasing it for free with source code included. Here's the website link: http://www.dertech.com/neurodb/pgmex.html Could this be added to the related projects page? Thanks. -Tony ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] with vs without oids in pg_catalog.*
Dear Tom, So my question still is: Given the fact that I have some use for these oids, would it make sense to submit a patch to add them? It will be rejected. That's a simple a direct answer as I like them. So I won't bother to submit a patch;-) BTW, maybe you could reject some of the patches I submitted earlier, rather than to simply ignore them? We removed pg_attribute OIDs some time ago, and we aren't going to put them back without a much better reason than this. If you need a specific counterargument, here is one: pg_attribute is normally much the largest catalog. If we required its rows to have unique OIDs, the probability of collisions after OID-counter wraparound would be much greater than it is in other catalogs. Mmh. Maybe you could have considered sequences. -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] with vs without oids in pg_catalog.*
Fabien COELHO wrote: Dear Tom, So my question still is: Given the fact that I have some use for these oids, would it make sense to submit a patch to add them? It will be rejected. That's a simple a direct answer as I like them. So I won't bother to submit a patch;-) BTW, maybe you could reject some of the patches I submitted earlier, rather than to simply ignore them? The only outstanding patch I see from you is: [PATCHES] [NOT] (LIKE|ILIKE) (ANY|SOME|ALL) (subquery...) from March 29. I will put it in the queue now. Are there others we missed? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] with vs without oids in pg_catalog.*
Dear Bruce, BTW, maybe you could reject some of the patches I submitted earlier, rather than to simply ignore them? The only outstanding patch I see from you is: [PATCHES] [NOT] (LIKE|ILIKE) (ANY|SOME|ALL) (subquery...) from March 29. I will put it in the queue now. Are there others we missed? Sure:-) Date: Wed, 17 Mar 2004 09:09:40 +0100 (CET) From: Fabien COELHO [EMAIL PROTECTED] To: PostgreSQL Patches [EMAIL PROTECTED] Subject: [PATCHES] hint infrastructure setup (v3) Have a nice day, -- Fabien Coelho - [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] with vs without oids in pg_catalog.*
Fabien COELHO [EMAIL PROTECTED] writes: Sure:-) Subject: [PATCHES] hint infrastructure setup (v3) Oh, I was intending to review that but got caught up in functions- returning-rowtypes hacking. I'll take a look as soon as I'm done with the rowtypes project (should be in a day or two). regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] pgmex, a MATLAB interface for PostgreSQL
Last link was wrong. Here's the correct one. http://www.dertech.com/pgmex/pgmex.html ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] pre-loading a user table.
I am trying a pre-load a user table during InitPostgres. I tried to mimic
the relevant actions in ReverifyDatabase to carry this out. I manage to
load the first block of the table. However, if a table is more than 1
block, I end up getting warnings about relcache reference leaks. It looks
like I need to increase the size of the relcache. Can anyone comment on
the approach and/or give me any advanced warnings about messing with the
relcache?
Thanks!
Code follows:
/**
* APC 4/1/04
*
*/static void
PreLoadUserTable(Oid relationId)
{
Relationpgdbrel;
HeapScanDescpgtblscan;
HeapTuple tup;
int i;
fprintf(stdout, APC: PreLoadTable for relation(%d).\n,
relationId);
//
pgdbrel = heap_open(relationId, AccessShareLock);
/* APC numKeys seems to be 0 for user tables */
/* pgdbscan = heap_beginscan(pgdbrel, SnapshotNow, 1, key); */
pgtblscan = heap_beginscan(pgdbrel, SnapshotNow, 0, NULL);
fprintf(stdout, APC: the number of blocks (%d)\n,
pgtblscan-rs_rd-rd_nblocks);
/* XXX how to really load all the blocks, this approach is wrong */
for (i=0; i=ipgtblscan-rs_rd-rd_nblocks; i++) {
fprintf(stdout, APC: the number of blocks (%d) for
round(%d)\n, pgtblscan-rs_rd-rd_nblocks, i);
tup = heap_getnext(pgtblscan, ForwardScanDirection);
if (!HeapTupleIsValid(tup))
{
/* OOPS */
heap_close(pgdbrel, AccessShareLock);
/* ereport */
fprintf(stderr, APC: FATAL ERROR unable to load
the table during round(%d).\n, i);
}
}
heap_endscan(pgtblscan);
heap_close(pgdbrel, AccessShareLock);
fprintf(stdout, APC: PreLoadTable finished for relation(%d).\n,
relationId);
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
[HACKERS] Problems Vacuum'ing
Hi, [Just so y'all know: This has been discussed extensively in #PostgreSQL and I tried asking the question in both -admin and -general, first. Also did some web searching.] Environment: PostgreSQL 7.4.2 Locally built with GCC 3.3.1 Solaris 8 (Sparc) I have a relatively simple database created with... create table ethers ( hostname varchar(64) unique not null, macmacaddr not null, createdtimestamp (0) not null default current_timestamp, changedtimestamp (0), last_seen timestamp (0) not null default current_timestamp ); create table host_mac_hist ( hostname varchar(64) not null, macmacaddr not null, createdtimestamp(0) not null default current_timestamp, last_seen timestamp(0) not null ); I'm populating the data from bunches of existing flat files in such a manner that the ethers table, in particular, is getting updated literally thousands of times. It got slow, so I stopped the updating and went to vacuum. (Using psql as the user/owner of the db.) The problem is that attempts to vacuum these tables resulted in NNN dead row versions cannot be removed yet. Went through a lot of analysis (e.g.: Any hanging txns?) and trying different things with folks on the #PostgreSQL IRC channel, all to no avail. There is a WebObjects application that is the only other thing accessing pgsql. It is not accessing the same database, much-less those tables. (This was confirmed by enabling connection logging and checking the log.) Yet the only way I can successfully vacuum these tables is to shut-down WebObjects *or* if I vacuum before there are too many dead rows. (Or so I thought! Additional info later...) Output of one attempt... $ vacuumdb -U sysagent -t ethers --verbose --analyze sysagent Password: INFO: vacuuming public.ethers INFO: index ethers_hostname_key now contains 114002 row versions in 2389 pages DETAIL: 1865 index pages have been deleted, 1865 are currently reusable. CPU 0.18s/0.09u sec elapsed 0.41 sec. INFO: ethers: found 0 removable, 114002 nonremovable row versions in 1114 pages DETAIL: 113590 dead row versions cannot be removed yet. There were 2184 unused item pointers. 0 pages are entirely empty. CPU 0.20s/0.18u sec elapsed 0.54 sec. INFO: analyzing public.ethers INFO: ethers: 1114 pages, 412 rows sampled, 412 estimated total rows VACUUM And... $ vacuumdb -U sysagent -t ethers --verbose --analyze --full sysagent Password: INFO: vacuuming public.ethers INFO: ethers: found 0 removable, 114002 nonremovable row versions in 1114 pages DETAIL: 113590 dead row versions cannot be removed yet. Nonremovable row versions range from 64 to 88 bytes long. There were 2184 unused item pointers. Total free space (including removable row versions) is 169880 bytes. 0 pages are or will become empty, including 0 at the end of the table. 816 pages containing 162192 free bytes are potential move destinations. CPU 0.06s/2.03u sec elapsed 2.11 sec. INFO: index ethers_hostname_key now contains 114002 row versions in 2389 pages DETAIL: 0 index row versions were removed. 1865 index pages have been deleted, 1865 are currently reusable. CPU 0.22s/0.45u sec elapsed 0.73 sec. INFO: ethers: moved 1745 row versions, truncated 1114 to 1114 pages DETAIL: CPU 0.39s/0.80u sec elapsed 2.79 sec. INFO: index ethers_hostname_key now contains 115740 row versions in 2389 pages DETAIL: 7 index row versions were removed. 1856 index pages have been deleted, 1856 are currently reusable. CPU 0.30s/0.15u sec elapsed 0.53 sec. INFO: analyzing public.ethers INFO: ethers: 1114 pages, 412 rows sampled, 412 estimated total rows VACUUM I can understand how a non-full vacuum might fail if I have insufficient FSM. But full should get around that, should it not? Besides: I did a new test today. I added to my Perl code a bit that would vacuum every 10 files read-in. This would amount to about 5000 dead rows and, IIRC, less than 300 pages. Much less than the default FSM. So even a non-full vacuum should be succeeding, no? This new script would succeed in getting everything vacuumed-up for a while and then, at some point (failed to notice when): The dead row versions cannot be removed yet came back and steadily incremented each time vacuum was run. If I were going to guess, I'd *guess* maybe this started happening about the time somebody queried the WebObjects application, thus causing it to connect, but I've no way of knowing after-the-fact. (Sorry for the vagueness here.) Any idea of what might be going on here? TIA, Jim -- Jim Seymour | PGP Public Key available at: [EMAIL PROTECTED] | http://www.uk.pgp.net/pgpnet/pks-commands.html http://jimsun.LinxNet.com| ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Josh Berkus wrote:
I'm noticing some inconsistent behavior regarding empty arrays and IS NULL
status.For example:
net_test=# select array_upper('{}'::INT[], 1) IS NULL;
?column?
--
t
(1 row)
This is correct. There are no dimensions to an empty array by
definition. The only other way to handle this would be an ERROR. I
followed the lead of (the pre-existing function) array_dims() when
creating array_upper() and array_lower().
net_test=# select '{}'::INT[] IS NULL;
?column?
--
f
(1 row)
This is also correct, and completely orthogonal to the first example.
There is a difference between an empty array and NULL, just like there
is between an empty string and NULL.
I feel that this is confusing; an empty array should be considered NULL
everywhere or nowhere.
As I said above, that makes no more sense than saying '' == NULL
For that matter, the new array declaration syntax does not support
empty arrays:
net_test=# select ARRAY[ ]::INT[];
ERROR: syntax error at or near ] at character 15
This is a known issue, and will not be easily fixed. We discussed it at
some length last June/July. See especially:
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01174.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01195.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01196.php
http://archives.postgresql.org/pgsql-hackers/2003-06/msg01298.php
Joe
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PITR for replication?
I may be completely missing the point here, but it looks to me as though the PITR archival mechanism is also most of a native replication facility. Is there anyone reason this couldn't be extended to replication, and if so, is anyone planning on using it as such? My memory is fuzzy on this point, but I seem to recall that this is (was?) how replication is more-or-less done for many of the big commercial RDBMS. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Joe, This is correct. There are no dimensions to an empty array by definition. The only other way to handle this would be an ERROR. I followed the lead of (the pre-existing function) array_dims() when creating array_upper() and array_lower(). What about a 0?That seems more consistent to me. If the array is empty, its dimensions are not NULL, meaning unknown, but in fact zero elements, which is a known value. The way it works now, array_upper on a NULL array produces the same results as array_upper on an empty-but-non-null array. Or is there some concept I'm missing? -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] PITR for replication?
On Fri, 1 Apr 2004, J. Andrew Rogers wrote: I may be completely missing the point here, but it looks to me as though the PITR archival mechanism is also most of a native replication facility. Is there anyone reason this couldn't be extended to replication, and if so, is anyone planning on using it as such? My memory is fuzzy on this point, but I seem to recall that this is (was?) how replication is more-or-less done for many of the big commercial RDBMS. Logfile shipping is one replication option some commercial vendors offer. It doesn't solve all problems however. It is mostly used for hot backups in my experience. Gavin ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Josh Berkus wrote:
What about a 0?That seems more consistent to me. If the array
is empty, its dimensions are not NULL, meaning unknown, but in
fact zero elements, which is a known value.
They cannot be 0 because 0 is a real index. They are undefined, because
an empty array has no dimensions. It is entirely possible to have a real
array that starts at a lower bound of 0 (or even an upper bound of 0).
regression=# select f[0] from (select 99 || array[1,2,3] as f) as t;
f
99
(1 row)
regression=# create table a1 (f int[]);
CREATE TABLE
regression=# insert into a1 values('{}');
INSERT 18688045 1
regression=# update a1 set f[0] = 99;
UPDATE 1
regression=# select array_upper(f,1) from a1;
array_upper
-
0
(1 row)
The way it works now, array_upper on a NULL array produces the same
results as array_upper on an empty-but-non-null array.
Sure, and in both cases array_upper is undefined because there are no
array dimensions to speak of. I guess you might argue that array_upper,
array_lower, and array_dims should all produce an ERROR on null input
instead of NULL. But that would have been an un-backward compatible
change for array_dims at the time array_lower and array_upper were
created. I don't really believe they should throw an ERROR on an empty
array though.
Joe
---(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] Inconsistent behavior on Array Is Null?
Joe, Sure, and in both cases array_upper is undefined because there are no array dimensions to speak of. I guess you might argue that array_upper, array_lower, and array_dims should all produce an ERROR on null input instead of NULL. But that would have been an un-backward compatible change for array_dims at the time array_lower and array_upper were created. I don't really believe they should throw an ERROR on an empty array though. OK, I understand the logic now. Thanks. I guess this is another case where we're haunted by the ANSI committee's failure to define both and unknown and a not applicable value instead of the unitary NULL. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Josh Berkus [EMAIL PROTECTED] writes: Joe, This is correct. There are no dimensions to an empty array by definition. The only other way to handle this would be an ERROR. I followed the lead of (the pre-existing function) array_dims() when creating array_upper() and array_lower(). What about a 0?That seems more consistent to me. If the array is empty, its dimensions are not NULL, meaning unknown, but in fact zero elements, which is a known value. The way it works now, array_upper on a NULL array produces the same results as array_upper on an empty-but-non-null array. Or is there some concept I'm missing? I would certainly second that. Consider all that making it NULL breaks: length(a) != array_upper(a)-array_lower(a) array_upper(a||b) == array_upper(a)+length(b) If someone implements pop and push it sure makes things weird that push doesn't always increment the length pop doesn't decrement the length until 0. Perhaps you're worried that you have pick an arbitrary lower and upper bound and, strangely, that the upper bound would actually be one less than the lower bound such as [1,0]. However this isn't really any different than the normal case. All arrays in postgres have arbitrary lower bounds. Fwiw, the int_aggregate function from int_aggregate.c in the contrib section makes arrays that don't violate these invariants. For empty arrays the dimensions are [0,-1]. This isn't hypothetical for me. Whenever I end up replacing int_aggregate with a standard function that constructs your style arrays my app would break. I guess I'll have to add a coalesce(...,0) to my array_length() function to work around it. Which only seems like strong evidence it's the wrong behaviour. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] PITR for replication?
J. Andrew Rogers [EMAIL PROTECTED] writes: I may be completely missing the point here, but it looks to me as though the PITR archival mechanism is also most of a native replication facility. Is there anyone reason this couldn't be extended to replication, and if so, is anyone planning on using it as such? My memory is fuzzy on this point, but I seem to recall that this is (was?) how replication is more-or-less done for many of the big commercial RDBMS. Well replication is one of those things that means different things to different people. IMHO, this is one of the simpler, more reliable, mechanisms and would be nice to have. And you're right that it shouldn't require much more work, in fact it's probably easier than a lot of other cases that PITR requires. For a long time Oracle has supported this mechanism for running warm standby databases. Basically you maintain a second database and every time an archived log is finished you ship it over and immediately restore it on the standby machine. Whenever you have a failure you can quickly fail over to the standby database which is all ready to go and up-to-date within minutes of your failure. Since 8i Oracle has also supported a more advanced version where you can open the standby database in read-only mode. This makes it useful for running batch reports and so on. In postgres this wouldn't work nearly so well since even read-only queries require write access to the database in postgres. Perhaps it's not nearly so urgent since running long-running batch queries on a busy system in postgres doesn't pose all the same dangers it does in Oracle (the dreaded snapshot too old error) -- though there are other analogous dangers (fsm settings being too small unexpectedly). -- greg ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] i18n of PostgreSQL - part 1
Hello folks, just wanted to mention that the first part of the slovak translation of Pg is available - the psql strings - at the following address: http://de.geocities.com/bartkozo/psql-sk.po.gz It is the 7.4 branch/newer. Thanks for reading Zoltan
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Greg Stark wrote:
length(a) != array_upper(a)-array_lower(a)
[You really meant array_upper(a) - array_lower(a) + 1 I'd guess]
length(A) is a missing function at the moment; the spec actually calls
it CARDINALITY. Once available, you would use it to determine array
length. SQL2003 says:
The result of cardinality expression is the number of elements of
the result of the collection value expression.
So, when A is an empty array, CARDINALITY(A) = 0, by definition.
array_upper(a||b) == array_upper(a)+length(b)
Same here; this would be:
array_upper(a || b) == array_upper(a) + CARDINALITY(b)
and would work just fine. Note that if array-a is NULL, then the spec
defines a || b as NULL. See section 6.35:
2) If array concatenation is specified, then let AV1 be the value of
array value expression 1 and let AV2 be the value of
array primary.
Case:
a) If either AV1 or AV2 is the null value, then the result of the
array concatenation is the null value.
b) If the sum of the cardinality of AV1 and the cardinality of AV2
is greater than IMDC, then an exception condition is raised:
data exception array data, right truncation.
c) Otherwise, the result is the array comprising every element of
AV1 followed by every element of AV2.
If someone implements pop and push it sure makes things weird that push
doesn't always increment the length pop doesn't decrement the length until 0.
I have no idea what you're trying to say here. Current behavior
certainly increments length by one when you push an element (which is
what array || element effectively does). An empty array has length 0
before pushing an element on to it, and length 1 afterward. Pushing an
element onto a NULL array yields NULL, which is not explicitly defined
by the spec (that I can find), but is certainly consistent with the above.
As far as array_pop is concerned, we discussed the fact that it makes no
sense in the context of Postgres arrays -- see the archives from last
year in May.
Perhaps you're worried that you have pick an arbitrary lower and upper bound
and, strangely, that the upper bound would actually be one less than the lower
bound such as [1,0]. However this isn't really any different than the normal
case. All arrays in postgres have arbitrary lower bounds.
Again, I have no idea what you mean here.
Fwiw, the int_aggregate function from int_aggregate.c in the contrib section
makes arrays that don't violate these invariants. For empty arrays the
dimensions are [0,-1].
Seems rather arbitrary to me. As I said to Josh, an empty array has
undefined bounds, literally.
This isn't hypothetical for me. Whenever I end up replacing int_aggregate with
a standard function that constructs your style arrays my app would break. I
guess I'll have to add a coalesce(...,0) to my array_length() function to work
around it. Which only seems like strong evidence it's the wrong behaviour.
Sorry, but these are not my style arrays, they are Berkley style ;-).
Anyway, CARDINALITY is what you really need -- hopefully I'll be able to
find time to address that and some ather array items before the 7.5
freeze. In the meantime, if you have a custom array_length function
already, why not make it return 0 for empty arrays -- then your problems
disappear:
create or replace function array_length(anyarray)
returns int as '
select
case
when $1 = ''{}'' then 0
else array_upper($1, 1) - array_lower($1, 1) + 1
end
' language sql;
CREATE FUNCTION
regression=# select array_length(array[1,2,3]);
array_length
--
3
(1 row)
regression=# select array_length('{}'::int4[]);
array_length
--
0
(1 row)
regression=# select array[1,2,3] || '{}'::int4[];
?column?
--
{1,2,3}
(1 row)
regression=# select array_upper(array[1,2,3], 1) +
array_length('{}'::int4[]);
?column?
--
3
(1 row)
HTH,
Joe
---(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] Inconsistent behavior on Array Is Null?
Joe Conway [EMAIL PROTECTED] writes:
Same here; this would be:
array_upper(a || b) == array_upper(a) + CARDINALITY(b)
and would work just fine. Note that if array-a is NULL, then the spec defines a
|| b as NULL. See section 6.35:
Why are you talking about when a is NULL? The original question was for when a
was an empty array. That's not an unknown value, it's known to be an array
containing no elements.
It sounds like this is the same type of confusion that led to Oracle treating
empty strings as NULL, which causes no end of headaches.
Anyway, CARDINALITY is what you really need -- hopefully I'll be able to find
time to address that and some ather array items before the 7.5 freeze. In the
meantime, if you have a custom array_length function already, why not make it
return 0 for empty arrays -- then your problems disappear:
create or replace function array_length(anyarray)
returns int as '
select
case
when $1 = ''{}'' then 0
else array_upper($1, 1) - array_lower($1, 1) + 1
end
' language sql;
My argument was that having to write a special case here makes it pretty clear
the idea of equating {} with NULL is the wrong interface.
--
greg
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pre-loading a user table.
Ana Cerejo [EMAIL PROTECTED] writes: I am trying a pre-load a user table during InitPostgres. I tried to mimic the relevant actions in ReverifyDatabase to carry this out. I manage to load the first block of the table. However, if a table is more than 1 block, I end up getting warnings about relcache reference leaks. It looks like I need to increase the size of the relcache. Can anyone comment on the approach and/or give me any advanced warnings about messing with the relcache? You've broken something and you haven't got the foggiest idea what :-( ... but increasing the size of the relcache definitely isn't the solution, because it isn't fixed-size. Why do you think it useful to preload something during InitPostgres, anyway? Any heavily used table will certainly be present in shared buffers already, and even more surely present in kernel buffers. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Inconsistent behavior on Array Is Null?
Greg Stark wrote:
Joe Conway [EMAIL PROTECTED] writes:
Same here; this would be:
array_upper(a || b) == array_upper(a) + CARDINALITY(b)
and would work just fine. Note that if array-a is NULL, then the spec defines a
|| b as NULL. See section 6.35:
Why are you talking about when a is NULL? The original question was for when a
was an empty array. That's not an unknown value, it's known to be an array
containing no elements.
Did you even look at my examples at the end of the post? I showed your
example, with an empty array, handled correctly. The mention of a NULL
array was only for completeness.
It sounds like this is the same type of confusion that led to Oracle treating
empty strings as NULL, which causes no end of headaches.
ISTM that you're the one who's confused. There is a very clear
distinction between a NULL array and an empty array in the present
implementation. They are *not* treated the same:
regression=# select '{}'::int[], NULL::int[];
int4 | int4
--+--
{} |
(1 row)
regression=# select '{}'::int[] is NULL, NULL::int[] is NULL;
?column? | ?column?
--+--
f| t
(1 row)
You seem to be saying that because the output of certain functions that
operate on empty arrays is NULL, it somehow implies that the array is
being treated as NULL -- that's just plain incorrect.
My argument was that having to write a special case here makes it pretty clear
the idea of equating {} with NULL is the wrong interface.
But they're not being equated (see above), so I don't see where there's
an issue.
Joe
---(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] Inconsistent behavior on Array Is Null?
Joe Conway [EMAIL PROTECTED] writes:
You seem to be saying that because the output of certain functions that operate
on empty arrays is NULL, it somehow implies that the array is being treated as
NULL -- that's just plain incorrect.
Not incorrect, but a sign something weird is going on. It should only happen
if the certain functions really are looking for some property that isn't known
for that peculiar value of their parameters. iscomputable(n) perhaps, sqrt(-1)
if we abuse the rules a bit. But array_upper for a non-null array?
array_lower() and array_upper() are returning NULL for a non-null input, the
empty array, even though lower and upper bounds are known just as well as they
are for any other sized array. They are behaving as if there's something
unknown about the empty array that makes it hard to provide a lower bound or
upper bound.
slo= select array_lower('{}'::int[],1), array_upper('{}'::int[],1);
array_lower | array_upper
-+-
|
(1 row)
I know it's possible to work around this special case, but I'm saying it's odd
to have an irregularity in the interface. What justification is there for
breaking the invariant length = upper-lower+1 ?
Yes I read the examples you gave, but you a) had to work around the nit with a
special case in your function and b) still have corner cases where one of the
invariants I named fails, namely:
Same here; this would be:
array_upper(a || b) == array_upper(a) + CARDINALITY(b)
and would work just fine. Note that if array-a is NULL, then the spec
defines a || b as NULL. See section 6.35:
test=# select array_upper(a||b, 1), array_upper(a,1)+array_length(b) from (select
array[1,2] as a, array[1,2] as b) as x;
array_upper | ?column?
-+--
4 |4
(1 row)
test=# select array_upper(a||b, 1), array_upper(a,1)+array_length(b) from (select
'{}'::int[] as a, array[1,2] as b) as x;
array_upper | ?column?
-+--
2 |
(1 row)
--
greg
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
