Re: [HACKERS] BIN()
create or replace function bin(bigint) returns text language plperl as $$
my $arg = $_[0] + 0;
my $res = "";
while($arg)
{
$res = ($arg % 2) . $res;
$arg >>= 1;
}
return $res;
$$;
Any reason not to use sprintf("%b", $_[0])?
All very well and good, but it has to be PL/SQL preferably or PL/pgSQL.
I can write it in PL/PGSQL easily enough I guess but I was hoping
there was a neato shortcut.
Chris
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] BIN()
Or something like this in SQL or PL/pgSQL: test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0'); ltrim --- 1100 (1 row) Swet. Good old i/o functions. Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] BIN()
Tom Lane wrote: Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: test=> SELECT ltrim(textin(bit_out(12::bit(64))), '0'); ltrim --- 1100 (1 row) Swet. Good old i/o functions. Who needs the I/O functions? Just cast int to bit(n). Then how do you remove all leading zeros, and make sure you choose a high enough 'n'? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Problem with COPY CSV
Attached is a small test extract from the USDA nutrient database. The problem is that the script won't load the COPY data correctly. This is with CVS HEAD (and 8.1). It is the 4th column in the table that gives the problem (nutr_no integer). Each of the 3 COPY rows has a different way of specifying a number: * As an unquoted empty value * As a quoted number * As a quoted empty value Now, I'm specifying "NULL AS ''" in the COPY command but it refuses to recognize the quoted empty value as NULL. Anyone have any ideas? Is this a bug? The manual even says that using "NULL AS ''" lets you make no distinction between empty and quoted empty values. Chris test.sql.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Docs misspelling
36.7.3.5. FOR (integer variant) In the 8.1 docs. "Label" has been spelt "Labal". Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Reducing relation locking overhead
4. The only reason we need to take relation-level locks on indexes at all is to make the world safe for REINDEX being done concurrently with read-only accesses to the table (that don't use the index being reindexed). If we went back to requiring exclusive lock for reindex we could forget all about both #2 and #3. Particularly for updates of relations with lots of indexes, this could be a pretty significant win. However we'd definitely be giving up something that was seen as a feature at one point, so I'm not sold on this idea ... unless someone can see a way to reduce the overhead without giving up concurrent REINDEX. Surely in the real world REINDEX is run so rarely compared to all those other operations it'd be a win... Chris ---(end of broadcast)--- TIP 1: 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] SERIAL type feature request
I think nobody would object to implementing support for the SQL2003 syntax. Most of that would be providing all the values that will get forwarded into the internal sequence generation during CREATE TABLE. Someone also pointed out on IRC the other day that Oracle and DB2 list 'identity' as the column type of identity columns in the information_schema, whereas we just put 'integer'. Maybe we could change that to match in the future... Chris ---(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] Replication on the backend
replication (master/slave, multi-master, etc) implemented inside postgres...I would like to know what has been make in this area. It's not in the backend, check out things like Slony (www.slony.info) and various other commercial solutions. Chris ---(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
[HACKERS] Bug in pg_dump -c with casts
Hi, Playing around with this MySQL compatibility library, I noticed that pg_dump -c does not emit DROP commands for casts. Seems like a bug...? Chris ---(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
[HACKERS] inet to bigint?
OK, I give up - how do I convert an INET type to a NUMERIC representation of its network address? Is there a quick and easy way? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] inet to bigint?
PL/SQL or PL/PGSQL...
Chris
Michael Fuhr wrote:
On Tue, Dec 06, 2005 at 03:31:59PM +0800, Christopher Kings-Lynne wrote:
OK, I give up - how do I convert an INET type to a NUMERIC
representation of its network address?
How about:
CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$
use Socket;
return unpack("N", inet_aton($_[0]));
$$ LANGUAGE plperlu IMMUTABLE STRICT;
SELECT inet2num('127.0.0.1');
inet2num
2130706433
(1 row)
---(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] inet to bigint?
Sheesh, arbitrary restrictions ;-) Something like this then: CREATE FUNCTION inet2num(inet) RETURNS numeric AS $$ DECLARE a text[] := string_to_array(host($1), '.'); BEGIN RETURN a[1]::numeric * 16777216 + a[2]::numeric * 65536 + a[3]::numeric * 256 + a[4]::numeric; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; Cool, and now the reverse? :D (I'll credit you in the MySQL Compat Library code btw) If you're interested, you'd be welcome to join the project btw... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Bug in pg_dump -c with casts
Actually, scratch that - I'm wrong... It appeared separately from the other DROP commands... Chris Christopher Kings-Lynne wrote: Hi, Playing around with this MySQL compatibility library, I noticed that pg_dump -c does not emit DROP commands for casts. Seems like a bug...? Chris ---(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 ---(end of broadcast)--- TIP 1: 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
[HACKERS] Oddity with extract microseconds?
Does anyone else find this odd: mysql=# select extract(microseconds from timestamp '2005-01-01 00:00:00.123'); date_part --- 123000 (1 row) mysql=# select extract(microseconds from timestamp '2005-01-01 00:00:01.123'); date_part --- 1123000 (1 row) No other extracts include other fields. eg, minutes: mysql=# select extract(minutes from timestamp '2005-01-01 00:10:00'); date_part --- 10 (1 row) mysql=# select extract(minutes from timestamp '2005-01-01 10:10:00'); date_part --- 10 So how come microseconds includes the microseconds from the 'seconds' field and not just after the '.'? And if it's supposed to include 'seconds', then why doesn't it include minutes, hours, etc.? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Oddity with extract microseconds?
OK, AndrewSN just pointed out that it's "documented" to work like that... ...still seems bizarre... Chris Christopher Kings-Lynne wrote: Does anyone else find this odd: mysql=# select extract(microseconds from timestamp '2005-01-01 00:00:00.123'); date_part --- 123000 (1 row) mysql=# select extract(microseconds from timestamp '2005-01-01 00:00:01.123'); date_part --- 1123000 (1 row) No other extracts include other fields. eg, minutes: mysql=# select extract(minutes from timestamp '2005-01-01 00:10:00'); date_part --- 10 (1 row) mysql=# select extract(minutes from timestamp '2005-01-01 10:10:00'); date_part --- 10 So how come microseconds includes the microseconds from the 'seconds' field and not just after the '.'? And if it's supposed to include 'seconds', then why doesn't it include minutes, hours, etc.? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Oddity with extract microseconds?
Why aren't 'minutes' considered too? Because they aren't 'seconds'. Well, seconds aren't microseconds either. Yeah, they are: it's just one field. The other way of looking at it (that everything is seconds) is served by "extract(epoch)". Well, it's different in MySQL unfortunately - what does the standard say? Out of interest, can someone try this for me in MySQL 5: SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123'); SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:10.00123'); Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Optimizer oddness, possibly compounded in 8.1
One of the easier cases would be non-overlapping (exclusive) constraints on union subtables on the joined column. This could serve as a "partition key", or in case of many nonoverlapping columns (ex.: table is partitioned by date and region), as many partition keys. Yes, thats my planned direction. In case you didn't know btw, MySQL 5.1 is out with rather extensive table partition support. So get coding :D Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Oddity with extract microseconds?
MySQL 5.0.16 gives an error: mysql> SELECT EXTRACT (MICROSECOND FROM '2003-01-02 10:30:00.00123'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM '2003-01-02 10:30:00.00123')' at line 1 Odd, that example is straight from the MySQL 5 manual: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Oddity with extract microseconds?
Looks like MySQL doesn't allow a space before the open parenthesis (there isn't one in the manual's example): mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123'); +---+ | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:00.00123') | +---+ | 1230 | +---+ 1 row in set (0.01 sec) Ok, and what does this give: SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123'); Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Oddity with extract microseconds?
mysql> SELECT EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123'); +---+ | EXTRACT(MICROSECOND FROM '2003-01-02 10:30:01.00123') | +---+ | 1230 | +---+ 1 row in set (0.00 sec) Does contrary behavior from MySQL count as evidence that PostgreSQL's behavior is correct? :-) No...I happen to think that their way is more consistent though. Pity it's not in the spec. At least PostgreSQL is consistent with seconds/microseconds: mysql=# select extract(seconds from timestamp '2005-01-01 00:00:01.01'); date_part --- 1.01 (1 row) Chris ---(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
[HACKERS] HOOKS for Synchronous Replication?
Anyone remember this patch? http://gorda.di.uminho.pt/community/pgsqlhooks/ The discussion seems to be pretty minimal: http://archives.postgresql.org/pgsql-hackers/2005-06/msg00859.php Does anyone see a need to investigate it further? Chris ---(end of broadcast)--- TIP 1: 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] Another encoding issue
If we're bringing up odd encoding issues, why not talk about the mystery
encoding of the shared catalogs? :)
Basically depending on which database you're logged into when you alter
a catalog will affect what encoding the new object appears as in the
shared catalog.
This for one makes it impossible for us in phpPgAdmin to display a list
of databases, where some database names are in EUC and some are in UTF-8
and some are in LATIN5...
I bring it up as I notice that in MySQL 5 at least, all system object
names (in our case that'd be all strings in the shared catalogs) are
stored in UTF-8, always.
Chris
Gavin Sherry wrote:
Hi all,
Here's another interesting encoding issue. I cannot recall having seen it
on the lists.
---
[EMAIL PROTECTED] build7]$ bin/createdb -E LATIN1 test
CREATE DATABASE
[EMAIL PROTECTED] build7]$ cat break.sh
dat=`echo -en "\245\241"`
echo "create table test (d text);"
echo "insert into test values('$dat');"
[EMAIL PROTECTED] build7]$ sh break.sh | bin/psql test
CREATE TABLE
INSERT 0 1
[EMAIL PROTECTED] build7]$ bin/createdb -T test test2
CREATE DATABASE
[EMAIL PROTECTED] build7]$ bin/createdb -T test -E UTF-8 test2
CREATE DATABASE
[EMAIL PROTECTED] build7]$ bin/pg_dump -C test2 > test2.dmp
[EMAIL PROTECTED] build7]$ bin/dropdb test2
DROP DATABASE
[EMAIL PROTECTED] build7]$ bin/psql template1 -f test2.dmp
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "test2".
[...]
CREATE TABLE
ALTER TABLE
psql:test2.dmp:345: ERROR: invalid UTF-8 byte sequence detected near byte
0xa5
CONTEXT: COPY test, line 1, column d: " "
[...]
---
Until createdb() is a lot more sophisticated, we cannot translate
characters between encodings. I don't think this is a huge issue though,
as most people are only going to be creating empty databases anyway.
Still, it probably requires documentation.
Thoughts?
Thanks,
Gavin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] int to inet conversion [or Re: inet to bigint?]
Hi Kai, There are some rather simplistic functions to convert 32bit inet values to and from bigints in the mysql compatibility project: http://pgfoundry.org/projects/mysqlcompat/ In the miscellaneous.sql. Chris Kai wrote: Hello All, I've been pondering the discussed subject a few times, and came along a few things that I think are missing from the default set of typeconversions within postgres. After working regularly with inet values in sql, it would be nice to be able to do this: => select '192.168.1.1'::inet + 1 as result; result - 192.168.1.2 (1 row) => select '192.168.1.255'::inet - '192.168.1.0'::inet as difference; difference 255 (1 row) or simply this: => select '192.168.1.1'::inet::bigint bigint 3232235777 In the old postgres 7.3 the data was stored in the database being a big integer anyway, but in the new ipv6 compatible stuff I lost track. I can probably write the functions in C if theres more interest in them, but I'm not on track on how to define all the casting stuff in the postgresql system tables, nor the sticky subject on how to handle ipv6. Or maybe someone else was pondering the idea too and is far better at writing C? :-) My conclusion is that the selects above should be among the default set of operations on inet values in PostgreSQL, being subtraction and addition. If not I'd like to be proven wrong. Regards, Kai ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Different length lines in COPY CSV
Hi, Is there any way to force COPY to accept that there will be lines of different length in a data file? I have a rather large file I'm trying to import. It's in CSV format, however, they leave off trailing empty columns on most lines. Any way to do this? Should it be supported by CSV mode? Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Cost-based optimizers
A vaguely interesting interview with IBM and MS guys about cost-based optimizers. http://www.acmqueue.com/modules.php?name=Content&pa=showpage&pid=297 Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Different length lines in COPY CSV
Anyway, that's history now. Where would you want this file conversion utility? bin? contrib? pgfoundry? How about a full SQL*Loader clone? :D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Cost-based optimizers
I saw it in print; the only thing that seemed interesting about it was the recommendation that query optimization be biased towards the notion of "stable plans," query plans that may not be the most "aggressively fast," but which don't fall apart into hideous performance if the estimates are a little bit off. And the answer is interesting as well: "I think we have to approach it in two ways. One is that you have to be able to execute good plans, and during the execution of a plan you want to notice when the actual data is deviating dramatically from what you expected. If you expected five rows and you’ve got a million, chances are your plan is not going to do well because you chose it based on the assumption of five. Thus, being able to correct mid-course is an area of enhancement for query optimizers that IBM is pursuing." Hmmm dynamic re-planning! Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Refactoring psql for backward-compatibility
Neil Conway suggested something like a get_function_list(), which I presume would be called on connect, and would be version-aware. Does this seem like a good idea? If so, what might an implementation look like? I know C isn't all that great for function overloading, so do we want to keep all the SQL for all previous versions in memory, or...? If it was me I'd just copy the pg_dump way of doing things... Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql and COPY BINARY
I submitted a patch a while back to change that but I withdrew it because I wasn't 100% confident I'd done it right. Here is the link to it: http://archives.postgresql.org/pgsql-patches/2005-03/msg00242.php It's probably 99% there - just a bit of checking. Chris Andreas Pflug wrote: Examining why psql won't do sensible stuff with COPY BINARY, I realized that psql still uses PQgetline, which is marked obsolete since 7.4. Is this intentional or just a "never reviewed because it works"? Unfortunately, psql/copy.c also states "if you want to use copy in your app, this is the code to steal". Seems not a good template code any more. Regards, Andreas ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] psql and COPY BINARY
Examining why psql won't do sensible stuff with COPY BINARY, I realized that psql still uses PQgetline, which is marked obsolete since 7.4. Is this intentional or just a "never reviewed because it works"? There wasn't any obvious bang for the buck in rewriting it. The obvious one (and why I submitted a patch - see previous email) was for people who use the psql source code as sample code, which is what I was doing at the time for PHP 5.1's PostgreSQL module. Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Immodest Proposal: pg_catalog.pg_ddl
What I would like to see is some builtin functions that give me the table's DDL, just as pg_dump does. Extra nice would be complementary functions that also give me skeleton select statements for each table or view. Yeah, what I first thought David was proposing was a consolidated view similar to pg_indexes, that could give you an up-to-date DDL definition for anything in the system. This has been proposed in the past as a way to migrate pg_dump functionality into the backend. I don't think it will actually work for that (pg_dump needs more understanding of what it's doing than just blindly copying complete CREATE commands) --- but it still seems potentially useful for manual operations. We have many pg_get_blahdef() functions already, but we really should flesh them all out so that they are available for every database object, eg: pg_get_tabledef() pg_get_domaindef() pg_get_functiondef() etc. That would also be cool because then I'd have an easy way of dumping individual objects from phpPgAdmin, or pgAdmin ,etc. Chris ---(end of broadcast)--- TIP 1: 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] Immodest Proposal: pg_catalog.pg_ddl
There were quite some proposals about additional triggers (on connect/disconnnect) around, I wonder if some kind of schema/database-level trigger could be used for DDL logging. Or, "global triggers" where you can have a trigger that is executed upon ANY DML or DDL... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Refactoring psql for backward-compatibility
If it was me I'd just copy the pg_dump way of doing things...
To the extent possible, I'd like to preserve the exact functionality
(or lack thereof) of previous versions. Would this be possible that
way?
Don't see it'd be too hard. All pg_dump basically does is this:
if (version <= 7.3) {
query = "..";
else if (version == 7.4) {
query = "..";
else
query = "..";
Other than that you might need some smarts in the output table display
logic so that it doesn't "assume" what columns are available from the
queries.
Or something.
Chris
---(end of broadcast)---
TIP 1: 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] Improving planning of outer joins
I'm not sure whether we'd need any additional planner knobs to control this. I think that the existing join_collapse_limit GUC variable should continue to exist, but its effect on left/right joins will be the same as for inner joins. If anyone wants to force join order for outer joins more than for inner joins, we'd need some other control setting, but I don't currently see why that would be very useful. Does this seem like a reasonable agenda, or am I thinking too small? I think it's fantastic - it's a big complaint about planning we see often in the IRC channel. Chris ---(end of broadcast)--- TIP 1: 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] Improving planning of outer joins
I'm not sure whether we'd need any additional planner knobs to control this. I think that the existing join_collapse_limit GUC variable should continue to exist, but its effect on left/right joins will be the same as for inner joins. If anyone wants to force join order for outer joins more than for inner joins, we'd need some other control setting, but I don't currently see why that would be very useful. Does this seem like a reasonable agenda, or am I thinking too small? Oh, and if you wanted to go bigger - the next planning issue people seem to have is with our union planning... Chris ---(end of broadcast)--- TIP 1: 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] [pgadmin-hackers] Client-side password encryption
So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Is it different to normal md5? How is this helpful to the phpPgAdmin project? Chris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
By the way, I've already implemented this in phpPgAdmin trivially using the md5() function. I can't be bothered using a C library function :D Chris Dave Page wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 19 December 2005 05:37 To: Christopher Kings-Lynne Cc: Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug; Dave Page Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: So it appears that pg_md5_encrypt is not officially exported from libpq. Does anyone see a problem with adding it to the export list and the header file? Is it different to normal md5? How is this helpful to the phpPgAdmin project? It would be better to export an API that is (a) less random (why one input null-terminated and the other not?) and (b) less tightly tied to MD5 --- the fact that the caller knows how long the result must be is the main problem here. Something like char *pg_gen_encrypted_passwd(const char *passwd, const char *user) with malloc'd result (or NULL on failure) seems more future-proof. Changing the API is likely to cause fun on Windows for new apps that find an old libpq.dll. Perhaps at this point it should become libpq82.dll? Regards, Dave. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
I've already implemented this in phpPgAdmin trivially using the md5() function. I can't be bothered using a C library function :D IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... Yes... However of course in phpPgAdmin the password has already been sent cleartext to the webserver from your browser, and the database connection password parameter is still sent in the clear so... Chris ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Improving planning of outer joins
I'm not sure whether we'd need any additional planner knobs to control this. I think that the existing join_collapse_limit GUC variable should continue to exist, but its effect on left/right joins will be the same as for inner joins. If anyone wants to force join order for outer joins more than for inner joins, we'd need some other control setting, but I don't currently see why that would be very useful. Does this seem like a reasonable agenda, or am I thinking too small? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] [pgadmin-hackers] Client-side password encryption
IIRC the whole point of this exercise was to avoid passing the password to the server in the first place. Unless you are talking about a PHP md5() password of course ... ---(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] [pgadmin-hackers] Client-side password encryption
Where are we on this? In general I agree with Tom, but I have no time to do the work. Unless someone has an immediate implementation, I suggest that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt, which is the minimum needed to unbreak Windows builds, while this gets sorted out properly. I had forgotten that the Windows build is broken. I'll see what I can do with throwing together the cleaner-API function. Another question about these encrypted passwords. phpPgAdmin needs to connect to databases that are sometimes on other servers. I use the pg_connect() function to do this. This is passed down to PQconenct() I presume. So, can I specify the password to pg_connect() as 'md5127349123742342344234'? ie. Can I CONNECT using an md5'd password? Also, does this work for non-md5 host lines on the server, and how can I avoid doing it on older (pre-7.2) PostgreSQL?? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [pgadmin-hackers] Client-side password encryption
So, can I specify the password to pg_connect() as 'md5127349123742342344234'? Certainly not. We'd hardly be worrying about obscuring the original password if the encrypted version were enough to get in with. AndrewSN can't post at the moment, but asked me to post this for him: "Knowing the md5 hash is enough to authenticate via the 'md5' method in pg_hba.conf, even if you don't know the original password. Admittedly you have to modify libpq to do this, but this isn't going to stop an attacker for more than 5 seconds." I'll add my own note that never sending the cleartext password does not necessarily improve PostgreSQL security, but certainly stops someone who sniffs the password from then using that cleartext password to get into other applications. If all they can get is the md5 hash, then all they can get into is PostgreSQL. Chris ---(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] Fixing row comparison semantics
I've gotten interested again in the issue of row comparisons, eg (a, b, c) >= (1, 2, 3) We've discussed this before, the most comprehensive thread being http://archives.postgresql.org/pgsql-performance/2004-07/msg00188.php but nothing's gotten done. Unless someone's already working on this I think I will take it up. Can someone explain to me how: (a, b) < (1, 2) is different to a < 1 and b < 2 ? Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Fixing row comparison semantics
Now, since COLLATE support is still in progress, I'm not sure how much any of this helps you. I'm up to modifying the scankeys but it's hard when you jave to keep rgrepping the tree to work out what is called from where... src/tools/make_ctags is your friend... Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [COMMITTERS] pgsql: Minor doc tweak: "NOT NULL" is redundant
I hope you mean 'redundant with "PRIMARY KEY" in example'... Works out the same way though. Chris Neil Conway wrote: Log Message: --- Minor doc tweak: "NOT NULL" is redundant with "SERIAL" in example. Modified Files: -- pgsql/doc/src/sgml/ref: create_domain.sgml (r1.26 -> r1.27) (http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/create_domain.sgml.diff?r1=1.26&r2=1.27) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [COMMITTERS] pgsql: Minor doc tweak: "NOT NULL" is
Why? SERIAL implies NOT NULL (although PRIMARY KEY does as well, of course). Ah yes you're right. I mixed up with the fact that SERIAL no longer implies UNIQUE... Chris ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Open Sourcing pgManage
D'oh, just clued into the 'java' aspect ... Joshua, will this run as a JSP, remotely, through Jakarta-Tomcat? One of the limitations of pgAdmin, as far as I'm concerned, is the fact that you can run it remotely Then use phpPgAdmin... Chris ---(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
[HACKERS] weird regression test issue CVS HEAD
I keep getting this: pg_regress: initdb failed Examine ./log/initdb.log for the reason. rm regress.o gmake[2]: Leaving directory `/home/chriskl/pgsql/src/test/regress' gmake[1]: Leaving directory `/home/chriskl/pgsql/src/test' -bash-2.05b$ more src/test/regress/log/initdb.log Running in noclean mode. Mistakes will not be cleaned up. initdb: input file "/home/chriskl/pgsql/src/test/regress/./tmp_check/install//ho me/chriskl/local/share/postgresql/postgres.bki" does not belong to PostgreSQL 7. 5devel Check your installation or specify the correct path using the option -L. -bash-2.05b$ I wiped the src/test dir, re cvs up'd and did gmake test again and it still happens. What's going on? Chris ---(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] \xDD patch for 7.5devel
This is my first patch for PostgreSQL against the 7.5devel cvs (please advise if this is the wrong place to post patches). This patch simply enables the \xDD (or \XDD) hexadecimal import in the copy command (im starting with the simple stuff first). I did notice that there may be a need to issue an error if an invalid octal or hex character is found following a \ or \x. No errors are currently flagged by the octal (or this hex) import. I think this belongs into the string literal parser (at least in addition to COPY). That's what always happens when I start working on something - someone points out something that makes it 100 times harder :P Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Open Issues for 7.4
* Fix uselessly executable files in the source tree. See my recent post. Any ideas on that? As far as I'm aware, the only way to fix this is to get into the cvsroot and chmod them by hand. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [pgsql-www] [HACKERS] Changes to Contributor List
I think we had agreed that formerly-listed contributors would not be deleted, but would be moved to a new section titled "Contributors Emeritus" or some such. Please make sure that Tom Lockhart and Vadim get listed that way, at least. I think the "Emeritus" word might be too hard for non-native English speakers, and even for less educated English speakers. Isn't that an even better reason to use it? :) Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_stat
Just wondering how often the stats collector resets it self. Is this a parameter i can change? At my knowledge each time that you do an analyze on your db your statistics are changed ( are not incremental I mean), anyway you can set to reset statistics at the start of postgres. I think you're mixed up there. Stats collector is totally different thing. The stats collector is never reset. You can reset it manually by going: select pg_stat_reset(); And you can specify in the postgresql.conf that it should be reset on server restart if you like. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] CVS open for development?
Hey - now that we have a branch, is Bruce going to start committed the pgpatches2 list? Chris ---(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] Timestamps on schema objects
People occasionally seem to ask for keeping time stamps on schema objects (tables, functions, etc.) about when they were created and last altered (in their structure, not the data in the tables). I think that this would be a relatively useful and painless feature. What do others think? It has actually occurred to me before that that would be a vaguely useful feature. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bugzilla (Was: What do you want me to do?)
The "doesn't quite make the best use of PG" quote is one of the best examples of buck-passing I've seen in awhile. If Bugzilla had been designed with some thought to DB independence to start with, we'd not be having this discussion. You have to laugh at an app that actually uses MySQL's replication to get around not having row locks!!! And it actually has a sanity check feature for 'checking' your referential integrity. I laughed so hard I cried. And yet we use it at work :P Chris ---(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] bugzilla (Was: What do you want me to do?)
Seriously, I have wondered if it might be a good idea to assemble a small "hit team" that would take some high profile open source projects and make sure they worked with Postgres. Bugzilla would be the most obvious candidate, but there are certainly others. I suspect that could be quite productive, though. Thoughts? Count me out - I spend way too much of my time working on phpPgAdmin as it is :) Chris ---(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] Coding help
The only idea I have is to call oidin() to do the conversion from string
to oid. I see this in copy.c:
loaded_oid = DatumGetObjectId(DirectFunctionCall1(oidin,
CStringGetDatum(string)));
if (loaded_oid == InvalidOid)
ereport(ERROR,
(errcode(ERRCODE_BAD_COPY_FILE_FORMAT),
errmsg("invalid OID in COPY data")));
I would give that a try.
Yes but in this case, the Node is parsed like this:
| COMMENT ON LARGE OBJECT NumericOnly IS comment_text
{
...
n->objname = makeList1(makeAConst($5));
...
$$ = (Node *) n;
}
So there is no CString to convert to a Datum...
Chris
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Coding help
| COMMENT ON LARGE OBJECT NumericOnly IS comment_text n->objname = makeList1(makeAConst($5)); Forget the makeAConst step --- it's just wasted cycles. In the execution routine, you can use code comparable to define.c's defGetInt64() to convert the Value node into a numeric OID, ie, either do intVal() or a call of oidin(). I thought the whole problem here is that OIDs are unsigned ints, hence intVal() won't allow the highest OIDs? If that's not the case, then the easy solution is to just use an IConst or whatever it's called. (Which I had working before). Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Coding help
I thought the whole problem here is that OIDs are unsigned ints, hence intVal() won't allow the highest OIDs? Exactly. That's why you need to handle T_Float too. See the int8 example, which has just the same problem. It occurs to me then that I could just then use FloatOnly in the grammar and then just attempt to coerce that to an Oid using oidin? Floats are stored as strings, so that'd be OK, and the oidin function would barf if it was an illegal uint? Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Backup problems with tsearch2
Is the problem with backing up and restoring a database which has tsearch2 installed and enabled delt with in Version 7.4 of PostgreSQL? If it's the problem with restoring the tsearch2-related functions, then no, and I'm not sure whether it's "fixable" (in the sense that a tsearch2 enabled database will do a painless dump/restore). I've had some success by making sure all tsearch2-related functions are in their own schema, which I don't dump or use for restoring; before restoring I recreate the schema from a script, then reload the other schemas. There's a slight gotcha though which I can't recall offhand. I'll try and write it up next time I got through the process. What I did is I edited my dump and removed all the tsearch stuff. Then I copied the tsearch2.sql just after the CREATE DATABASE statement. This ensured that all the dependencies work fine. Since then, I think PostgreSQL's default dump order has just worked. The main situation that causes complete breakage is: CREATE TABLE... CREATE TYPE... ALTER TABLE / ADD COLUMN newtype Basically, any object that you can add dependencies to after it has been initially created can cause problems. eg. all the CREATE OR REPLACE commands, etc. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] About the partial tarballs
Even if they weren't useful for anything else, I think there's value in the developers having to consider what is optional and what is not. This need for constant review probably reduces the chance of bloat, over time even in the full tarball. How about dropping the partial tarballs and using the space savings to distribute a .tar.bz2 archive as well. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] cvs head? initdb?
Jan checked in the ARC changes last night, and he and Tom ran into some problems, leading to that being pulled back out, while he revisits the code. It's back in again, and appears to work now (at least the regression tests pass ... no idea about performance ...) I actually managed to hang my process when I first did a make check. After cleaning all that up, I did a make installcheck and that was fine. Then I did a make check again and this time it didn't hang. It hung on select_views/portals_p2, but I haven't been able to reproduce it... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] conversion dumping patch
Hey Bruce, When you get around to it, can you commit the patch I submitted that dumps conversions in pg_dump. I need that in to complete my COMMENT ON patch. I think it's pretty safe to apply, and I'm not 100% sure I still have a local copy of the patch, so I can't let it drift too much :( Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] unsupported platforms
I anyone going to email the people who last reported the unsupported platforms to see if they'll re-test? Shall I? Or should someone more official? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [CORE] 7.4RC2 regression failur and not running stats
Check that you don't need to use the -p option at all. Also, make sure you remove any ^M (DOS CR) characters from the line endings. That always happens to me if I receive the emailon a windows machine and save the attachment, windows sometimes likes to rewrite all the line endings, causing the problem below. Chris Glenn Wiorek wrote: Hmm I know it's been a while since I used patch but I seem to be having problems applying it. Perhaps my patch is outdated?? patch -b pgstat.c < patchfile Looks like a new-style context diff. Hunk#2failed at line 203. Hunk#2failed at line 210. Hunk#3failed at line 284. 3 out of 3 hunks ailed: saving reject to pgstat.c.rej ---(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] oh dear ...
I propose the attached patch to fix the problem. It doesn't break any regression tests, and it appears to fix the cases noted in its comment. Opinions on whether to apply this to 7.4? I think it should be fixed, since it could cause applications to break. Shouldn't you also add a regression test to catch this in the future? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] oh dear ...
I made up a more thorough regression test for date input formats, and found that there were still some cases that were rejected :-(. Attached is a more complete patch that handles all month-name cases, and explicitly can not change the behavior when there's not a textual month name. Documentation addition and regression test included. I'd like some further review of this before I risk applying it to 7.4 though ... anyone have time today? Hi Tom, Everything passes for me on FreeBSD 4.8, latest CVS, "gmake check", with your patch applied. Chris ---(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
[HACKERS] commenting on polymorphic aggregates possible?
-- value-independent transition function CREATE AGGREGATE newcnt ( sfunc = int4inc, basetype = 'any', stype = int4, initcond = '0' ); COMMENT ON AGGREGATE newcnt (any) IS 'an any agg comment'; ERROR: syntax error at or near "any" at character 30 COMMENT ON AGGREGATE newcnt (any) IS NULL; ERROR: syntax error at or near "any" at character 30 Is there any way of commenting that aggregate? Chris ---(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] logical column position
Right -- AFAICS, the only change in COPY compatibility would be if you COPY TO'd a table and then changed the logical column order in some fashion, you would no longer be able to restore the dump (unless you specified a column list for the COPY FROM -- which, btw, pg_dump does). I don't think it will be a problem, I just thought I'd mention it. Well it's the same problem as if you'd dropped a column in the middle of the table. BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release cycle length
The time from release 7.3 to release 7.4 was 355 days, an all-time high. We really need to shorten that. We already have a number of significant improvements in 7.5 now, and several good ones coming up in the next few weeks. We cannot let people wait 1 year for that. I suggest that we aim for a 6 month cycle, consisting of approximately 4 months of development and 2 months of cleanup. So the start of the next beta could be the 1st of March. What do you think? So long as pg_dump object ordering is an early fix to make upgrades rather more painless, I'm all for it :) Does anyone have a comparison of how many lines of code were added in this release compared to previous? Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Release cycle length
Everyone on -hackers should have been aware of it, as its always discussed at the end of the previous release cycle ... and I don't think we've hit a release cycle yet that has actually stayed in the 4 month period :( Someone is always 'just sitting on something that is almost done' at the end that pushes it further then originally planned ... I think that the core just need to be tough on it, that's all. If we have pre-published target dates, then everyone knows if they can get their code in or not for that date. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] help!
Wait for confirmation from at least one other developer perhaps, buy you can try this: 1. Set attisdropped to false for the attribute 2. Set the atttypid back to whatever the oid of the type of that column is/was (Compare to an undropped similar column) 3. Use ALTER TABLE/SET NOT NULL on the column if it was originally NOT NULL 4. Set attstattarget to -1 to re-enable stat gathering 5. Rename the column (attname field) back to whatever it was. 6. Re set the default on the column 7. Done. (I think) By the way, vacuuming doesn't necessarily have much to do with it - updating rows does though. I'm not 100% sure what will happen exactly when you follow the steps above (reversing what's in RemoveAttributeById). Chris Larry Rosenman wrote: I screwed up, and dropped a column when I shouldn't have. I have *not* vacuumed this DB yet. Is there any catalog mucking I can do to bring it back? LER ---(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] Release cycle length
Right now, I believe we are looking at an April 1st beta, and a May 1st related ... those are, as always, *tentative* dates that will become more fine-tuned as those dates become nearer ... April 1st, or 4 mos from last release, tends to be what we aim for with all releases ... as everyone knows, we don't necessarily acheive it, but Make it April 2nd, otherwise everyone will think it's a joke :P Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] logical column position
BTW, one main consideration is that all the postgres admin apps will now need to support ORDER BY attlognum for 7.5+. But that is only really important if they've also used the ALTER TABLE RESHUFFLE COLUMNS feature. So if they make one alteration for 7.5, they need to do another. That seems fair. Good point. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Release cycle length
That said, I'm not really sure how we can make better use of the beta period. One obvious improvement would be making the beta announcements more visible: the obscurity of the beta process on www.postgresql.org for 7.4 was pretty ridiculous. Does anyone else have a suggestion on what we can do to produce a more reliable .0 release in less time? I can think of a few things. 1. Try to encourage list members to actually test stuff. For example, I decided to find stuff that might be broken. So I checked the tutorial scripts (no-one ever looks at them) and found heaps of bugs. I thought about some new features and tried to break them. I also tend to find bugs by coding phpPgAdmin and delving into the nitty gritty of stuff. Maybe we could actually ask for people for the 'beta team'. Then, once we have volunteers, they are each assigned a set of features to test by the 'testing co-ordinator' (a new core position, say?) What you are asked to test depends on your skill, say. eg. Someone who just knows how to use postgres could test my upcoming COMMENT ON patch. (It's best if I myself do not test it) Someone with more skill with a debugger can be asked to test unique hash indexes by playing with concurrency, etc. The test co-ordinator could also manage the testing of new features as they are committed to save time later. The co-ordinator should also maintain a list of what features have been committed, which have been code reviewed (what Tom usually does) and which have been tested. Of course, I'm not talking about exhaustive testing here, just better and more organised that what we currently have. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Release cycle length
eg. Someone who just knows how to use postgres could test my upcoming COMMENT ON patch. (It's best if I myself do not test it) Someone with more skill with a debugger can be asked to test unique hash indexes by playing with concurrency, etc. I forgot to mention that people who just have large, complex production databases and test servers at their disposal should be given the task of: 1. Dumping their old version database 2. Loading that into the dev version of postgres 3. Dumping that using dev pg_dump 4. Loading that dump back in 5. Dumping it again 6. Diffing 3 and 5 Chris ---(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] [pgsql-advocacy] Not 7.5, but 8.0 ?
Oh, and yeah, a win32 port. Yay, another OS port. Postgres runs on dozens of OSes already. What's so exciting about one more? Even if it is a pathologically hard OS to port to. Just because it was hard doesn't mean it's useful. I don't call porting Postgres to run well on something like 40% of the world's servers (or whatever it is) "just another port". It could conveivably double Postgres's target audience, could attract heaps of new users, new developers, new companies and put us in a better position to compete with MySQL. I think it's actually a necessary port to keep the project alive in the long term. Chris ---(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] logical column position
Will adding the logical attribute number break all of the external tools? pg_dump, etc are all dependent on attnum now? Would it be possible to keep the meaning of attnum the same externally and add another column internally to represent the physical number? Interesting idea. It would require a lot of code renaming in the backend, but it could be done. Given that the interfaces comprise pgadmin, phppgadmin, jdbc and odbc and all the main developers for those lists read all these posts, I think the massive amount of effort to maintain the external interface isn't worth it. I can vouch that it would take me exactly 2 minutes to add support for attlognums in phpPgAdmin. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-www] [HACKERS] Release cycle length
HOWEVER, a release cycle of *less than 6 months* would kill the advocacy vols if we wanted the same level of publicity. I do support the idea of "dev" releases. For example, if there was a "dev" release of PG+ARC as soon as Jan is done with it, I have one client would would be willing to test it against a simulated production load on pretty heavy-duty hardware. Can't we have nightly builds always available? Why can't they just use the CVS version? (Oddly enough, my problem in doing more testing myself is external to PostgreSQL; most of our apps are PHP apps and you can't compile PHP against two different versions of PostgreSQL on the same server. Maybe with User Mode Linux I'll be able to do more testing now.) I'd be willing to give testing coordination a go, not sure where I'd begin though. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Is there going to be a port to Solaris 9 x86 in the
PostgreSQL most definitely works great on Solaris x86 ! At UC Berkeley, we have our undergraduate students hack on the internals of PostgreSQL in the upper-division "Introduction to Database Systems" class .. http://www-inst.eecs.berkeley.edu/~cs186/ Hi Sailesh, You know what would be kind of cool? If you could write a "Guide to PostgreSQL to Teach Databases". eg. You could cover how to set up the server securely (eg. schemas for each person), etc. How to manage it all, handle upgrades, etc. Mention what things are good to get students to hack on in the internals, etc. Could be a good techdocs.postgresql.org article. Just a thought :) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Is there going to be a port to Solaris 9 x86 in the
Marcel Kornacker did implement concurrency for GiST - I confirmed as much with Joe Hellerstein (his advisor). I know there's a paper he wrote with C.Mohan on it. I don't know which version his implementation was for. The 7.4 GiST docs have a link to Kornacker's thesis that details how to implement concurrent GiST and unique GiST: http://citeseer.nj.nec.com/448594.html I have been reading it, but I think my skills aren't really sufficient to implement it :P Chris ---(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] logical column position
Why should ALTER COLUMN change the column number, i.e. position? Because it creates a NEW column. It may be that programmers should not rely on this, but it happens, and in very large projects. If we can avoid unexpected side-affects like moving the columns position, then I think we should. This is *expected* if behaviour if you delete and add columns; is there any DB system out there that allows to reshuffle the column ordering? MySQL Chris ---(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] Release cycle length
Yeah, I think the main issue in all this is that for real production sites, upgrading Postgres across major releases is *painful*. We have to find a solution to that before it makes sense to speed up the major-release cycle. Well, I think one of the simplest is to do a topological sort of objects in pg_dump (between object classes that need it), AND regression testing for pg_dump :) Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Anyone working on pg_dump dependency ordering?
I'm thinking about attacking pg_dump's lack of knowledge about using dependencies to determine a safe dump order. But if there's someone out there actively working on the problem, I don't want to tread on your toes ... anyone? I've done a whole lot of _thinking_, but basically no _doing_, so go right ahead :) I may as well let you know my thoughts: There are two levels (sort of) of dependency. The first is that whole classes of objects can be dependent on whole other classes. eg. databases depend on users, or ALL FK's can be dumped after ALL tables, etc.. It would make the dump more readable if you dumped those definite dependencies in that order, rather than shuffling everything up. The second level of dependency is when a bunch of object types can depend on each other. The current solution for that is to sort by OID, but this fails when it is possible to add a dependency to an object after it has been created. eg: - Adding a column (with a type) to a table - All the CREATE OR REPLACE commands - etc. Hence, a full db wide topological sort might not be necessary. Lastly, I presume it's possible to create a system of circular dependencies (eg create or replace view), which really cannot be solved without a system of 'shells', similar to that needed to dump types and their i/o functions. Views seem to be by far the nastiest object. They can be dependent on almost everything in the database. Also, if you've got uncommitted patches for pg_dump, please let me know. Yes, my 'COMMENT ON' mega patch in the queue contains dumping of conversions and comments on a bunch of objects. BTW, if you commit that patch - you might want to change my comment on type patch to put the "" around "any", and change the results file appropriately. I noticed I accidentally included that in the patch, and was about to mention it. CHris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_dump dependency / physical hot backup
Verifying zero rows in the freshly created table should be quite fast... It's hundreds of times faster to add an index to a full table than add rows to a table with an index. Chris ---(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] Executable files in CVS
The other things that are executable look like they legitimately are scripts. If we consider that group-writability is bad (which ISTM we ought to) then there are a *ton* of files with the wrong permissions. I'd recommend getting Marc to fix it instead of hacking about with a one-file-at-a-time method. You could consider adding a script to CVSROOT module to fix permissions upon commit? Chris ---(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
[HACKERS] ALTER SEQUENCE enchancement
Hi, Is there demand for this syntax: ALTER SEQUENCE ON table(col) CYCLE 100; It would allow us to become sequence-name independent... Chris ---(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] ALTER SEQUENCE enchancement
Is there demand for this syntax: ALTER SEQUENCE ON table(col) CYCLE 100; It would allow us to become sequence-name independent... The above is an operation that would not help me a lot, but a way of performing currval() without knowing the sequence name would be good. It will help in cases such as the 7.3-7.4 upgrade where a few of my sequence names will get renamed because they maxed out at 32 characters... I'll see about versions of currval() and nextval() that are sequence name independent as well... Chris ---(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] Build farm
Would it be reasonable to promote users testing daily snapshots with popular applications? I'm guessing there's not many applications that have automated test frameworks, but any that do would theoretically provide another good test of PGSQL changes. May I quote Joel on Software here? http://www.joelonsoftware.com/articles/fog43.html The Joel Test 1. Do you use source control? 2. Can you make a build in one step? 3. Do you make daily builds? 4. Do you have a bug database? 5. Do you fix bugs before writing new code? 6. Do you have an up-to-date schedule? 7. Do you have a spec? 8. Do programmers have quiet working conditions? 9. Do you use the best tools money can buy? 10. Do you have testers? 11. Do new candidates write code during their interview? 12. Do you do hallway usability testing? "The neat thing about The Joel Test is that it's easy to get a quick yes or no to each question. You don't have to figure out lines-of-code-per-day or average-bugs-per-inflection-point. Give your team 1 point for each "yes" answer. The bummer about The Joel Test is that you really shouldn't use it to make sure that your nuclear power plant software is safe. A score of 12 is perfect, 11 is tolerable, but 10 or lower and you've got serious problems. The truth is that most software organizations are running with a score of 2 or 3, and they need serious help, because companies like Microsoft run at 12 full-time. " Not everything there applies to us, of course. Chris ---(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] A rough roadmap for internationalization fixes
About storing data in the database, I would expect it to work with any encoding, just like I would expect pg to be able to store images in any format. What's stopping us supporting the other Unicode encodings, eg. UCS-16 which could save Japansese storage space. Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pg_restore and create FK without verification check
A common mistake, can't count how often I created this one... And not easy to find, because EXPLAIN won't explain triggers. I'm planning to create some kind of fk index wizard in pgAdmin3, which finds out about fks using columns that aren't covered by an appropriate index. Maybe this check could be performed (as a NOTICE) when the fk is created? Weird - I'm planning the exact same thing for phpPgAdmin! Great minds think alike :P Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.5 Plans
Hi everyone, I'm just interested in what everyone's personal plans for 7.5 development are? Shridar, Gavin and myself are trying to get the tablespaces stuff off the ground. Hopefully we'll have a CVS set up for us to work in at some point (we didn't think getting a branch and commit privs was likely). Changing all our $Id$ tags to $Postgres$ would make a separate CVS a lot easier, hint hint :) What's everyone else wanting to work on? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Encoding problem with 7.4
After installing 7.4 I created database completely from scratch with cyrillic locale: su postgres export LC_CTYPE=ru_RU.KOI8-R export LC_COLLATE=ru_RU.KOI8-R /usr/local/pgsql/bin/initdb -D /db2/pgdata You need to go: /usr/local/pgsql/bin/initdb -D /db2/pgdata -E KOI8 To set the default encoding to KOI8. Then I switch off to my normal account. At this point I have: /e:1>psql -l List of databases Name| Owner | Encoding ---+--+--- template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII (2 rows) Locale and encoding are two quite different things. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Call for pg_dump testing
Hey Tom, I have committed some fairly wide-ranging revisions to pg_dump to make it dump database objects in a "safe" order according to the dependency information available from pg_depend. While I know that I have fixed a lot of previously-broken cases, it's hardly unlikely that I've broken some things too. Please give it a try if you can. CVS-tip pg_dump should be fully compatible with 7.4 installations, and reasonably compatible with 7.3 servers as well (but its output is unlikely to load into pre-7.3 servers because of syntax changes). Awesome effort! I'm interested to know how you deal with circular dependencies in Views and Functions? Also, what happens if I delete a key dependency from my pg_depend table manually? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Build error?
I just made distclean and then reconfigured with --with-openssl and I get this in HEAD: gcc -O2 -fno-strict-aliasing -g -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../src/interfaces/libpq -I../../../src/include -DBINDIR=\"/home/chriskl/local/bin\" -c -o common.o common.c -MMD In file included from /usr/include/openssl/comp.h:5, from /usr/include/openssl/ssl.h:171, from ../../../src/interfaces/libpq/libpq-fe.h:33, from pg_backup.h:30, from pg_backup_archiver.h:58, from common.c:21: /usr/include/openssl/crypto.h:358: syntax error before `free_func' /usr/include/openssl/crypto.h:358: syntax error before `)' /usr/include/openssl/crypto.h:363: syntax error before `free_func' /usr/include/openssl/crypto.h:363: syntax error before `)' In file included from /usr/include/openssl/rsa.h:62, from /usr/include/openssl/evp.h:134, from /usr/include/openssl/x509.h:67, from /usr/include/openssl/ssl.h:177, from ../../../src/interfaces/libpq/libpq-fe.h:33, from pg_backup.h:30, from pg_backup_archiver.h:58, from common.c:21: /usr/include/openssl/asn1.h:802: syntax error before `free_func' /usr/include/openssl/asn1.h:802: `d2i_ASN1_SET' declared as function returning a function /usr/include/openssl/asn1.h:803: syntax error before `int' /usr/include/openssl/asn1.h:907: syntax error before `free_func' /usr/include/openssl/asn1.h:907: syntax error before `)' In file included from /usr/include/openssl/evp.h:145, from /usr/include/openssl/x509.h:67, from /usr/include/openssl/ssl.h:177, from ../../../src/interfaces/libpq/libpq-fe.h:33, from pg_backup.h:30, from pg_backup_archiver.h:58, from common.c:21: /usr/include/openssl/objects.h:990: syntax error before `free_func' /usr/include/openssl/objects.h:990: syntax error before `)' gmake[3]: *** [common.o] Error 1 Is that something broken on my system??? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for pg_dump testing
There's not currently any code for that, though I imagine we could invent some at need. Please provide example cases. create view v1 as select 1; create view v2 as select 1 + (select * from v1); create or replace view v1 as select * from v2; It seems to me that the only way to solve that one is to dump 'view shells'. eg. for text columns return '' and numeric columns return 0: eg: create view v1 as select 0::integer; create view v2 as select 1 + (select * from v1); create or replace view v1 as select * from v2; Also, what happens if I delete a key dependency from my pg_depend table manually? Postgres has always allowed you to shoot yourself in the foot by manually diddling the system catalogs. I place this in the "if it hurts, don't do it" category ... Is there any reason for us to still allow that? What is there left that requires manual twiddling? Also shouldn't we really separate out the 'can modify catalogs manually' privilege from the 'superuser' privilege? That way dbas could make people superusers who couldn't to extremely bad things to the catalogs? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for pg_dump testing
and a dump that orders the two views arbitrarily. We can certainly add code to do something different, but are there any real-world cases where this is needed? The above example seems more than slightly made-up. The views aren't actually functional anyway (trying to use either would result in an "infinite recursion" error). Can you show me a non-broken situation where pg_dump needs to resort to view shells? Well then shouldn't we just ban you from creating a view that creates a circular dependency? Thinks... How about if the views were using each others 'table type' to do something? Although you cannot change the return type definition can you? Hmmm. Also shouldn't we really separate out the 'can modify catalogs manually' privilege from the 'superuser' privilege? See pg_shadow.usecatupd. This could stand to be better supported maybe (like with ALTER USER support)? Sounds like this should be a TODO... Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
I get the following error when vacuuming a db or inserting a big value in a column of a toastable datatype (GEOMETRY). ERROR: Index pg_toast_8443892_index is not a btree My last action has been killing a psql that was getting mad about receiving too much input and beeping as hell (readline issue ?). Is there anything stopping us going through the code and finding all ereports that can be fixed by a REINDEX, and issue a HINT with all of them saying that they should REINDEX the broken index? That would seem to me to be really helpful for people. Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ERROR: Index pg_toast_8443892_index is not a btree
I couldn't agree more. Look at this very instance. He now found the right reindex command and the corrupted file is gone. We don't have the slightest clue what happened to that file. Was it truncated? Did some other process scribble around in the shared memory? How do you tell now? The end user just could not care less. They want their machine running again as soon as is humanly possible without going through a back and forth process of subscribing to some lists they don't care about, etc. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Resurrecting pg_upgrade
Per prior discussion, we will enforce some sort of limit on how often the representation of user tables/indexes can be changed. The idea will be to "batch" such changes so that you only have to do a dump/reload every N major releases instead of every one. In other words, pg_upgrade will work for most version upgrades but we reserve the right to occasionally make releases where it doesn't work. How large N will be in practice remains to be seen, of course, but I'd expect something on the order of 4 or 5. In theory pg_upgrade could be made to apply changes in user data representation, but I'm unconvinced that such a process would be a big improvement over dump/reload. Will we now have to be careful to NEVER re-use OIDs in the system catalogs. Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
