[HACKERS] Prepared Xacts and Vacuum question
Hi all,
When I was playing with VACUUM, I found that if I have prepared xacts
on the database A, I can't vacuum full on the database B.
Scenario:
1.) Prepare some transaction on "testdb" database.
2.) Create database "pgbench".
3.) Run "pgbench -i" to load pgbench data on "pgbench" database
4.) Delete all records from "accounts" table.
5.) Do VACUUM FULL on "pgbench" database.
6.) "accounts" table will not be shrinked.
7.) Rollback the prepared xacts on "testdb" database.
8.) Do VACUUM FULL on "pgbench" database.
9.) "accounts" table is shrinked.
For more details, please see the attached file.
According to my investigation, when the transaction is prepared,
PROC->xmin always set from the prepared transaction id,
even if it is another database.
So vacuum can't collect the deleted row between current xid and
prepared transaction's xid, and detect them as "nonremovable rows".
I found this on 8.1.0 and current cvs.
I think the prepared xacts on any database mustn't affect to another database.
Is this bug or spec?
Any comments?
Thanks.
--
NAGAYASU Satoshi <[EMAIL PROTECTED]>
[EMAIL PROTECTED]:~% createdb testdb
CREATE DATABASE
[EMAIL PROTECTED]:~% createdb pgbench
CREATE DATABASE
[EMAIL PROTECTED]:~% psql testdb
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testdb=# create table t1 ( uid integer primary key );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for
table "t1"
NOTICE: GetOldestXmin: MyDatabaseId=45468, allDbs=0
NOTICE: GetOldestXmin: pid=2742, xid=11866, xmin=11866, dboid=45468
CREATE TABLE
testdb=# begin;
BEGIN
testdb=# insert into t1 values ( 1 );
INSERT 0 1
testdb=# prepare transaction 'hoge';
PREPARE TRANSACTION
testdb=# \q
[EMAIL PROTECTED]:~% pgbench -i pgbench
creating tables...
1 tuples done.
2 tuples done.
3 tuples done.
4 tuples done.
5 tuples done.
6 tuples done.
7 tuples done.
8 tuples done.
9 tuples done.
10 tuples done.
set primary key...
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"branches_pkey" for table "branches"
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2764, xid=11889, xmin=11867, dboid=45469
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"tellers_pkey" for table "tellers"
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
"accounts_pkey" for table "accounts"
vacuum...done.
[EMAIL PROTECTED]:~% psql testdb
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
testdb=# select * from pg_prepared_xacts;
transaction | gid | prepared| owner | database
-+--+---+---+--
11867 | hoge | 2006-02-19 18:46:44.472652+09 | snaga | testdb
(1 row)
testdb=# \connect pgbench
You are now connected to database "pgbench".
pgbench=# SELECT count(*) FROM accounts;
count
10
(1 row)
pgbench=# select pg_relation_size('accounts');
pg_relation_size
--
13434880
(1 row)
pgbench=# delete from accounts;
DELETE 10
pgbench=# SELECT count(*) FROM accounts;
count
---
0
(1 row)
pgbench=# select pg_relation_size('accounts');
pg_relation_size
--
13434880
(1 row)
pgbench=# VACUUM FULL accounts;
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2779, xid=12001, xmin=11867, dboid=45469
NOTICE: full_vacuum_rel: OldestXmin=11867, currentXid=12001
VACUUM
pgbench=# VACUUM FULL VERBOSE accounts;
NOTICE: GetOldestXmin: MyDatabaseId=45469, allDbs=0
NOTICE: GetOldestXmin: pid=0, xid=11867, xmin=0, dboid=45468
NOTICE: GetOldestXmin: pid=2779, xid=12004, xmin=11867, dboid=45469
NOTICE: full_vacuum_rel: OldestXmin=11867, currentXid=12004
INFO: vacuuming "public.accounts"
INFO: "accounts": found 0 removable, 10 nonremovable row versions in 1640
pages
DETAIL: 10 dead row versions cannot be removed yet.
Nonremovable row versions range from 128 to 128 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 202080 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 5400 free bytes are potential move destinations.
CPU 0.00s/0.02u sec elapsed 0.01 sec.
INFO: index "accounts_pkey" now contains 10 row versions in 221 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
Re: [HACKERS] Pgfoundry and gborg: shut one down
-Original Message- From: [EMAIL PROTECTED] on behalf of Joshua D. Drake Sent: Sun 2/19/2006 12:35 AM To: Bruce Momjian Cc: Christopher Browne; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Pgfoundry and gborg: shut one down > This is not "get everything everyone wants before shutting down a site" > time. We should move to one site, and if the new site is not to > someone's liking, there is always sourceforge and other hosting sites. > > > I do agree with Bruce here but... we need to make sure that > > we give everyone their data. If Gborg does CVS like Gforge > > we may have a problem in that there is only one cvs repository. Moving CVS is not a problem - each project has their own repo on both systems. The problem is moving all the database stuff such as the bug trackers and todo lists, for which I'm told there are no working scripts. The other one that caused me great pain when I moved psqlODBC over was the GBorg genpages. I ended up manually pulling the code out of them and into plain HTML files as there is no equivalent area on pgFoundry. FWIW, in both the moves I have done (psqlODBC and Npgsql), only the CVS was actually moved. Regards, Dave ---(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] possible design bug with PQescapeString()
* Tatsuo Ishii: > Users can input value for "var" from a web form. The attacker inputs > following string: > > (0x95+0x27);DELETE FROM members;-- > > where 0x95+0x27 is actually a SJIS mutibyte KANJI. Programmer applies > PQescapeString() to it and gets: > > 0x95+0x27+0x27;DELETE FROM members;-- Uh-oh, this is my fault. PQescapeString should escape all characters greater than 126. Unfortunately, there is nothing we can do about this in the current function because tha twould need four times the lenggth of the input string (plus one). Drat. (I don't think you should have to consider the encoding in the client; strange things may happen if there is an interpretation conflict between the client and the backend.) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] possible design bug with PQescapeString()
> * Tatsuo Ishii: > > > Users can input value for "var" from a web form. The attacker inputs > > following string: > > > > (0x95+0x27);DELETE FROM members;-- > > > > where 0x95+0x27 is actually a SJIS mutibyte KANJI. Programmer applies > > PQescapeString() to it and gets: > > > > 0x95+0x27+0x27;DELETE FROM members;-- > > Uh-oh, this is my fault. PQescapeString should escape all characters > greater than 126. Unfortunately, there is nothing we can do about > this in the current function because tha twould need four times the > lenggth of the input string (plus one). Drat. Please don't do that. That would break all applications those use the mutibyte encodings including UTF-8. > (I don't think you should have to consider the encoding in the client; > strange things may happen if there is an interpretation conflict > between the client and the backend.) No. For the sake PQmblen() is provided. What I (and I guess Tom too) am thinking is like this: attacker's input: (0x95+0x27);DELETE FROM members;-- new-PQescapeString() treats this: 0x95+0x27;DELETE FROM members;-- because the encoding is SJIS. And the result SQL will be: SELECT * FROM members WHERE member_name = '0x95+0x27;DELETE FROM members;--'; The attacker loses. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] possible design bug with PQescapeString()
* Tatsuo Ishii: >> Uh-oh, this is my fault. PQescapeString should escape all characters >> greater than 126. Unfortunately, there is nothing we can do about >> this in the current function because tha twould need four times the >> lenggth of the input string (plus one). Drat. > > Please don't do that. That would break all applications those use > the mutibyte encodings including UTF-8. Why? Doesn't the server perform unquoting *before* multi-byte processing? -- Ah, it doesn't. Perhaps this is the part which should be fixed? >> (I don't think you should have to consider the encoding in the client; >> strange things may happen if there is an interpretation conflict >> between the client and the backend.) > > No. For the sake PQmblen() is provided. What I (and I guess Tom too) > am thinking is like this: > > attacker's input: > > (0x95+0x27);DELETE FROM members;-- > > new-PQescapeString() treats this: > > 0x95+0x27;DELETE FROM members;-- But this still needs knowledge of SJIS at the client side (and both client and backend must have the same notion of SJIS). ---(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] possible design bug with PQescapeString()
> >> Uh-oh, this is my fault. PQescapeString should escape all characters > >> greater than 126. Unfortunately, there is nothing we can do about > >> this in the current function because tha twould need four times the > >> lenggth of the input string (plus one). Drat. > > > > Please don't do that. That would break all applications those use > > the mutibyte encodings including UTF-8. > > Why? Doesn't the server perform unquoting *before* multi-byte > processing? -- Ah, it doesn't. Perhaps this is the part which should > be fixed? No no. Probably you misunderstand why we need quoting. If special characters such as "'" or "\" appears, it should be quoted. But you should not if it's a part of multibyte characters. > >> (I don't think you should have to consider the encoding in the client; > >> strange things may happen if there is an interpretation conflict > >> between the client and the backend.) > > > > No. For the sake PQmblen() is provided. What I (and I guess Tom too) > > am thinking is like this: > > > > attacker's input: > > > > (0x95+0x27);DELETE FROM members;-- > > > > new-PQescapeString() treats this: > > > > 0x95+0x27;DELETE FROM members;-- > > But this still needs knowledge of SJIS at the client side (and both > client and backend must have the same notion of SJIS). No problem. We have the client encoding in PGConn. That's why Tom suggests PQescapeString() should have the PGCConn argument. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Adding an ignore list to pg_restore
Hi Tom! Tom Lane [2006-02-18 14:34 -0500]: > Hm. Rather than a variant of the -L facility (which is hard to use, > and I don't see your proposal being much easier), maybe what's wanted > is just a flag saying "don't try to restore data into any table whose > creation command fails". Maybe that should even be the default ... > and you could extend it to indexes and constraints on such tables too, > as those would likely end up being duplicated as well. This comes close to my alternative proposal, it sounds fine to me. I'll try to come up with a reasonably clean implementation and report back then. Thank you, and have a nice Sunday, Martin -- Martin Pitt http://www.piware.de Ubuntu Developer http://www.ubuntulinux.org Debian Developerhttp://www.debian.org signature.asc Description: Digital signature
Re: [HACKERS] Generating config stuff from single source
On Thu, Feb 16, 2006 at 10:52:19AM -0500, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > Am Donnerstag, 16. Februar 2006 02:50 schrieb Tom Lane: > >> That's fine for users, but what new demands are you about to place on > >> developers? Does this require tools not already needed in order to > >> build from a CVS pull? (There's sure no xsltproc on this machine...) > > > It is to be expected that sooner or later we'll move from SGML to XML > > documentation builds, at which point xsltproc will become a > > semi-requirement > > anyway. I don't think this requirement is too onerous; libxslt is portable > > and easy to install. > > Forgot to mention, but: I don't find the above argument very convincing. > The buildfarm machines are not expected to build documentation, and many > developers seem not to have installed doc tools either. So I think this > would be raising the bar another notch in terms of what's required to do > development or testing, even if it does overlap with docs-build needs. >From what I've seen it's not terribly difficult to install some sort of XSLT processor now-a-days. It's certainly less involved than installing docbook in any case. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Pgfoundry and gborg: shut one down
Dave Page wrote: Moving CVS is not a problem - each project has their own repo on both systems. The problem is moving all the database stuff such as the bug trackers and todo lists, for which I'm told there are no working scripts. The other one that caused me great pain when I moved psqlODBC over was the GBorg genpages. I ended up manually pulling the code out of them and into plain HTML files as there is no equivalent area on pgFoundry. FWIW, in both the moves I have done (psqlODBC and Npgsql), only the CVS was actually moved. Perhaps that's the general solution. Forget about the database, genpages etc. and ask respective project administrators to move them manually? The two really important things are the CVS and the mailing-list. On my part, It'd be sufficient if those two where moved. My html content stems from my CVS and I plan to restructure it a bit anyway. My bug-tracking can be moved manually if need be. I too would be happy if I could somehow migrate to SVN but that can be done later. Regards, Thomas Hallgren ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains and supporting functions
On Sat, Feb 18, 2006 at 09:27:47PM -0800, elein wrote: > I've got a domain based on a text type. > I've overridden the equal operator with > lower(text) = lower(text). > > I created a table containing my new domain type > and can see that the equals operator is not > being used to determine uniqueness. What you want is citext. http://gborg.postgresql.org/project/citext/projdisplay.php It is a case-insensetive type with indexing and conparison support. Domains arn't going to do what you want... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Adding an ignore list to pg_restore, prototype patch #1
Hi again, Tom Lane [2006-02-18 14:34 -0500]: > >>> The core problem is that we want to not restore objects (mainly > >>> tables) in the destination database which already exist. > >> > >> Why is this a problem? It's already the default behavior --- the > >> creation commands fail but pg_restore keeps going. > > > The problem is that pg_restore would restore the TABLE DATA object, > > although we don't want that (the postgis specific tables are > > pre-populated by PostGIS itself, and should not be altered by the > > upgrade. > > Hm. Rather than a variant of the -L facility (which is hard to use, > and I don't see your proposal being much easier), maybe what's wanted > is just a flag saying "don't try to restore data into any table whose > creation command fails". Maybe that should even be the default ... > and you could extend it to indexes and constraints on such tables too, > as those would likely end up being duplicated as well. My first stab at this is a patch which only does the minimal changes, just to get me going. If the restoration of a TABLE object fails, it marks the corresponding TABLE DATA object as to be ignored. Do you think the current patch is a valid approach? Since this changes the behaviour of pg_restore, this should probably become an option, e. g. -D / --ignore-existing-table-data. I'll do this if you agree to the principle of the current patch. For convenience, I wrote a small test script which demonstrates the behaviour. The table 'userdata' should be restored, while the table 'auxdata' is already present in the destination db, and its contents should not be modified. Output with pg_restore from 8.1.3: --- snip $ LC_ALL=C sudo -u postgres ./test-pg_restore-existing.sh === create empty databases === === populating old database === === pre-creating auxdata in new database === === restoring old to new === pg_restore: connecting to database for restore pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating TABLE auxdata pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 17184 TABLE auxdata postgres pg_restore: [archiver (db)] could not execute query: FEHLER: Relation »auxdata« existiert bereits Command was: CREATE TABLE auxdata ( x integer ); pg_restore: creating TABLE userdata pg_restore: restoring data for table "auxdata" pg_restore: restoring data for table "userdata" pg_restore: setting owner and privileges for SCHEMA public pg_restore: setting owner and privileges for COMMENT SCHEMA public pg_restore: setting owner and privileges for ACL public pg_restore: setting owner and privileges for TABLE auxdata pg_restore: setting owner and privileges for TABLE userdata WARNING: errors ignored on restore: 1 pg_restore failed with 1 === new/userdata: === 42 256 === new/auxdata: === -1 -2 1 2 --- snip Output with patched pg_restore: --- snip $ LC_ALL=C sudo -u postgres ./test-pg_restore-existing.sh === create empty databases === === populating old database === === pre-creating auxdata in new database === === restoring old to new === pg_restore: connecting to database for restore pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating TABLE auxdata pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 1183; 1259 17194 TABLE auxdata postgres pg_restore: [archiver (db)] could not execute query: FEHLER: Relation »auxdata« existiert bereits Command was: CREATE TABLE auxdata ( x integer ); pg_restore: table auxdata could not be created, will not restore its data pg_restore: creating TABLE userdata pg_restore: restoring data for table "userdata" pg_restore: setting owner and privileges for SCHEMA public pg_restore: setting owner and privileges for COMMENT SCHEMA public pg_restore: setting owner and privileges for ACL public pg_restore: setting owner and privileges for TABLE auxdata pg_restore: setting owner and privileges for TABLE userdata WARNING: errors ignored on restore: 1 pg_restore failed with 1 === new/userdata: === 42 256 === new/auxdata: === -1 -2 --- snip Thus, with the patch, auxdata is not restored (which produced the additional entries '1' and '2'). Thanks, Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates? test-pg_restore-existing.sh Description: Bourne shell script diff -ruN postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver.c --- postgresql-8.1.3-old/src/bin/pg_dump/pg_backup_archiver.c 2006-02-05 21:58:57.0 +0100 +++ postgresql-8.1.3/src/bin/pg_dump/pg_backup_archiver
Re: [HACKERS] Adding an ignore list to pg_restore, prototype patch #1
Hi again, Meh, the list server didn't like the attached test script, so I put it here: http://people.debian.org/~mpitt/test-pg_restore-existing.sh Martin -- Martin Pitthttp://www.piware.de Ubuntu Developer http://www.ubuntu.com Debian Developer http://www.debian.org In a world without walls and fences, who needs Windows and Gates? signature.asc Description: Digital signature
[HACKERS] pg_service.conf
> On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote:
>> Like I said, in this thread of posts, yes there are ways of doing this,
>> and I've been doing it for years. It is just one of the rough eges that
>> I
>> think could be smoother.
>>
>> (in php)
>> pg_connect("dbname=geo host=dbserver");
>>
>> Could connect and query the dbserver, if the db is not on it, connect to
>> a
>> database of known servers, find geo, and use that information to
>> connect.
>> It sounds like a simple thing, for sure, but to be useful, there needs
>> to
>> be buy in from the group otherwise it is just some esoteric hack.
>
> It turns out what you like actually exists, lookup the "service"
> parameter in the connectdb string. It will read the values for the
> server, port, etc from a pg_service.conf file.
>
> There is an example in the tree but it looks something like the following:
>
> [servicename]
> dbname=blah
> user=blah
> pass=blah
>
> So all you need to specify is "service=servicename" and it will grab
> the parameters. This allows you to change the connection without
> changeing the code.
>
This is a great feature!!
It doesn't seem to be documented in the administrators guide. Its
mentioned in the libpq section, and only a reference to
pg_service.conf.sample
IMHO we should push for this to be the mainstream connection
methodology!!! The variables: host, port, and dbname are very problematic
for admins and developers who often live in different worlds.
The developers "should" just use the "servicename" of a database, and the
admins should maintain pg_service.conf. This moves the responsibility of
the wheres and hows of connecting to the database to the admin away from
the developer.
Should there be a section of the administration manual for this?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_service.conf
On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote:
> > On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote:
> >> Like I said, in this thread of posts, yes there are ways of doing this,
> >> and I've been doing it for years. It is just one of the rough eges that
> >> I
> >> think could be smoother.
> >>
> >> (in php)
> >> pg_connect("dbname=geo host=dbserver");
> >>
> >> Could connect and query the dbserver, if the db is not on it, connect to
> >> a
> >> database of known servers, find geo, and use that information to
> >> connect.
> >> It sounds like a simple thing, for sure, but to be useful, there needs
> >> to
> >> be buy in from the group otherwise it is just some esoteric hack.
> >
> > It turns out what you like actually exists, lookup the "service"
> > parameter in the connectdb string. It will read the values for the
> > server, port, etc from a pg_service.conf file.
> >
> > There is an example in the tree but it looks something like the following:
> >
> > [servicename]
> > dbname=blah
> > user=blah
> > pass=blah
> >
> > So all you need to specify is "service=servicename" and it will grab
> > the parameters. This allows you to change the connection without
> > changeing the code.
> >
>
> This is a great feature!!
Yes, it is, but there is a distinct difference between what you asked
for and what have been described as solutions (good though they are).
Both services and pg_service.conf are client-side solutions. So if you
have 20,000 clients to worry about you have some problems. What was
proposed was a central naming service (described as a database of known
servers) that would allow a server-side name to service mapping.
A server-side (i.e. centrally managed) name server seems like an
improvement over the client-side solutions described, IMHO, but I'd
leave it to others to describe how that might work. (e.g. DNS is a
better solution than multiple distributed /etc/hosts files).
Best Regards, Simon Riggs
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 10:00:01AM -0500, Mark Woodward wrote: > > It turns out what you like actually exists, lookup the "service" > > parameter in the connectdb string. It will read the values for the > > server, port, etc from a pg_service.conf file. > > > > There is an example in the tree but it looks something like the following: > > > > [servicename] > > dbname=blah > > user=blah > > pass=blah > > > > So all you need to specify is "service=servicename" and it will grab > > the parameters. This allows you to change the connection without > > changeing the code. > > > > This is a great feature!! > > It doesn't seem to be documented in the administrators guide. Its > mentioned in the libpq section, and only a reference to > pg_service.conf.sample Indeed, I only just found out about it yesterday. It's a very little known feature that needs some advertisement. Right now we need to work up some documentation patches so people come across it easier. Where do you think it should be mentioned? -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
Simon Riggs <[EMAIL PROTECTED]> writes: > A server-side (i.e. centrally managed) name server seems like an > improvement over the client-side solutions described, IMHO, but I'd > leave it to others to describe how that might work. (e.g. DNS is a > better solution than multiple distributed /etc/hosts files). Funnily enough, you could *use* DNS for this--you could define a custom RR type containing hostname, port, database etc and have entries in DNS for each "service" (e.g. 'production-db.mycorp.com'). I think HESIOD used this mechanism. Of course, you'd need an internal DNS server that you had full control over... -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 09:58:01AM -0500, Douglas McNaught wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > > A server-side (i.e. centrally managed) name server seems like an > > improvement over the client-side solutions described, IMHO, but I'd > > leave it to others to describe how that might work. (e.g. DNS is a > > better solution than multiple distributed /etc/hosts files). > > Funnily enough, you could *use* DNS for this--you could define a > custom RR type containing hostname, port, database etc and have > entries in DNS for each "service" (e.g. 'production-db.mycorp.com'). > I think HESIOD used this mechanism. Well, there exist such things as SRV records already for describing how to find services. In theory you could create an entry like: _postgres._tcp.example.com SRV 10 5 5432 db1.example.com So that if you typed "psql example.com" it would lookup the server and port number. You may be able to put a dbname after that, not sure. And you can always put whatever you like into a TXT record. In any case, someone still needs to write the code for it. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
> On Sun, Feb 19, 2006 at 10:00:01AM -0500, Mark Woodward wrote: >> > It turns out what you like actually exists, lookup the "service" >> > parameter in the connectdb string. It will read the values for the >> > server, port, etc from a pg_service.conf file. >> > >> > There is an example in the tree but it looks something like the >> following: >> > >> > [servicename] >> > dbname=blah >> > user=blah >> > pass=blah >> > >> > So all you need to specify is "service=servicename" and it will grab >> > the parameters. This allows you to change the connection without >> > changeing the code. >> > >> >> This is a great feature!! >> >> It doesn't seem to be documented in the administrators guide. Its >> mentioned in the libpq section, and only a reference to >> pg_service.conf.sample > > Indeed, I only just found out about it yesterday. It's a very little > known feature that needs some advertisement. Right now we need to work > up some documentation patches so people come across it easier. > > Where do you think it should be mentioned? As it was mentioned in another reply, this is not "everything" I wanted, but it is a big step closer that makes the rest managable. As for the "central" administration issue, yes, it is not a central administration solution, but files like these fall into the category of one to many "push" strategies, something like "bulkcopy -f targets pg_service.conf /usr/local/etc" I think it should be clearly in the administration section of the manual. A DBA is not going to look at the libpq section, similarly, PHP or Java developers won't either. I use libpq all the time, the last time I looked at pq_connect was years ago. Like I said, this is a REALLY USEFULL feature that should be presented as the "best method" for specifying databases, in the administration manual. It should also be mentioned in the PHP API as well. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_service.conf
> On Sun, 2006-02-19 at 10:00 -0500, Mark Woodward wrote:
>> > On Fri, Feb 03, 2006 at 08:05:48AM -0500, Mark Woodward wrote:
>> >> Like I said, in this thread of posts, yes there are ways of doing
>> this,
>> >> and I've been doing it for years. It is just one of the rough eges
>> that
>> >> I
>> >> think could be smoother.
>> >>
>> >> (in php)
>> >> pg_connect("dbname=geo host=dbserver");
>> >>
>> >> Could connect and query the dbserver, if the db is not on it, connect
>> to
>> >> a
>> >> database of known servers, find geo, and use that information to
>> >> connect.
>> >> It sounds like a simple thing, for sure, but to be useful, there
>> needs
>> >> to
>> >> be buy in from the group otherwise it is just some esoteric hack.
>> >
>> > It turns out what you like actually exists, lookup the "service"
>> > parameter in the connectdb string. It will read the values for the
>> > server, port, etc from a pg_service.conf file.
>> >
>> > There is an example in the tree but it looks something like the
>> following:
>> >
>> > [servicename]
>> > dbname=blah
>> > user=blah
>> > pass=blah
>> >
>> > So all you need to specify is "service=servicename" and it will grab
>> > the parameters. This allows you to change the connection without
>> > changeing the code.
>> >
>>
>> This is a great feature!!
>
> Yes, it is, but there is a distinct difference between what you asked
> for and what have been described as solutions (good though they are).
Well, true, it isn't what I want, but it makes a big step.
>
> Both services and pg_service.conf are client-side solutions. So if you
> have 20,000 clients to worry about you have some problems. What was
> proposed was a central naming service (described as a database of known
> servers) that would allow a server-side name to service mapping.
True, but the one to many cluster push solution has been dealt with so
many times that as a datacenter solution isn't too troubling.
>
> A server-side (i.e. centrally managed) name server seems like an
> improvement over the client-side solutions described, IMHO, but I'd
> leave it to others to describe how that might work. (e.g. DNS is a
> better solution than multiple distributed /etc/hosts files).
DNS isn't always a better solution than /etc/hosts, both have their pros
and cons. The /etc/hosts file is very useful for "instantaneous,"
reliable, and redundent name lookups. DNS services, espcially in a large
service environment can get bogged down. 20,000 hosts doing a lot of
lookups can require a dedicated single point of failure. OK, so you add
two DNS machines and load balance across them with a fault tollerant load
balancer, how many thousands of dollars? For how much information? A
simple "clustercpy -f targets pg_service.conf /etc" would save thousands
of dollars, increase efficiency, increase reliability, decrease electrical
costs, etc.
Don't get me wrong, DNS, as it is designed, is PERFECT for the distributed
nature of the internet, but replication of fairly static data under the
control of a central authority (the admin) is better.
>
> Best Regards, Simon Riggs
>
---(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] pg_service.conf
Mark Woodward wrote: > Don't get me wrong, DNS, as it is designed, is PERFECT for the > distributed nature of the internet, but replication of fairly static > data under the control of a central authority (the admin) is better. What about this zeroconf/bonjour stuff? I'm not familiar with it, but it sounds like it could tie into this discussion. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_service.conf
On Sun, Feb 19, 2006 at 04:56:11PM +0100, Peter Eisentraut wrote: > Mark Woodward wrote: > > Don't get me wrong, DNS, as it is designed, is PERFECT for the > > distributed nature of the internet, but replication of fairly static > > data under the control of a central authority (the admin) is better. > > What about this zeroconf/bonjour stuff? I'm not familiar with it, but > it sounds like it could tie into this discussion. I think the major issue is that most such systems (like RFC2782) deal only with finding the hostname:port of the service and don't deal with usernames/passwords/dbname. What we want is a system that not only finds the service, but tells you enough to connect. You can't connect to a postgres server without a dbname and these discovery protocols don't generally provide that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
Peter Eisentraut <[EMAIL PROTECTED]> writes: > Mark Woodward wrote: >> Don't get me wrong, DNS, as it is designed, is PERFECT for the >> distributed nature of the internet, but replication of fairly static >> data under the control of a central authority (the admin) is better. > > What about this zeroconf/bonjour stuff? I'm not familiar with it, but > it sounds like it could tie into this discussion. That's a possibility, but I think it's hard to make it work outside a single LAN (as in, it's not zero-conf anymore :) because it relies on broadcasts. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_service.conf
"Mark Woodward" <[EMAIL PROTECTED]> writes: > DNS isn't always a better solution than /etc/hosts, both have their pros > and cons. The /etc/hosts file is very useful for "instantaneous," > reliable, and redundent name lookups. DNS services, espcially in a large > service environment can get bogged down. 20,000 hosts doing a lot of > lookups can require a dedicated single point of failure. OK, so you add > two DNS machines and load balance across them with a fault tollerant load > balancer, how many thousands of dollars? For how much information? A > simple "clustercpy -f targets pg_service.conf /etc" would save thousands > of dollars, increase efficiency, increase reliability, decrease electrical > costs, etc. Um, is there something wrong with having multiple DNS servers in resolv.conf? Other than having to time out on #1 before you try #2? I'm genuinely curious. > Don't get me wrong, DNS, as it is designed, is PERFECT for the distributed > nature of the internet, but replication of fairly static data under the > control of a central authority (the admin) is better. You're probably right; clustercpy or rsync would work better if you have admin access to all the machines in question. The nice thing about the DNS method is that you wouldn't necessarily have to have that access on an ongoing basis. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared Xacts and Vacuum question
"Satoshi Nagayasu" <[EMAIL PROTECTED]> writes: > When I was playing with VACUUM, I found that if I have prepared xacts > on the database A, I can't vacuum full on the database B. A prepared xact is the same as an open xact as far as vacuum is concerned. It's a bad idea to sit on either open or prepared xacts for long periods ... > I think the prepared xacts on any database mustn't affect to another database. Wrong, consider updates to shared catalogs. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] possible design bug with PQescapeString()
Florian Weimer <[EMAIL PROTECTED]> writes: > Uh-oh, this is my fault. PQescapeString should escape all characters > greater than 126. No, that doesn't work, because the de-escaping on the backend side happens *after* conversion to the backend encoding. If you insert escapes into the middle of multibyte characters then you break the conversion. Tatsuo's description of the problem is accurate (though I'm not sure I agree with his solution ;-)) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Config file for psql
On Sat, Feb 18, 2006 at 02:49:08PM -0500, Tom Lane wrote: > Perhaps we should make a concerted effort to split the libpq docs into a > section "for programmers" vs one "for users", the latter part covering > the libpq behavior that is interesting to users of a libpq-based app. > .pgpass, pg_service, the environment vars, SSL behavior, maybe some > other things belong in the "for users" part. +1. Is there a docs TODO? -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] possible design bug with PQescapeString()
On Sun, Feb 19, 2006 at 12:13:48PM -0500, Tom Lane wrote: > Florian Weimer <[EMAIL PROTECTED]> writes: > > Uh-oh, this is my fault. PQescapeString should escape all characters > > greater than 126. > > No, that doesn't work, because the de-escaping on the backend side > happens *after* conversion to the backend encoding. If you insert escapes > into the middle of multibyte characters then you break the conversion. Well, most encodings provide an easy way to determine leader and follower characters. The PQmblen() and related functions can help here. Something like: if( PQmblen(enc,ptr) > 1 ) copy bytes else if( SQL_STR_DOUBLE( *ptr ) ) etc... Assuming there are no multibyte string terminators... And assuming you actually know what encoding the server expects. However, the real solution seems to me to be to use something like PQexecParams and ship the arguments outside the query string, thus avoiding the issue entirely. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
Martijn van Oosterhout writes: > I think the major issue is that most such systems (like RFC2782) deal > only with finding the hostname:port of the service and don't deal with > usernames/passwords/dbname. What we want is a system that not only > finds the service, but tells you enough to connect. In other words, anyone on the LAN who asks nicely can get a database password? No thank you. I don't actually believe that a server-side substitute for pg_service would be worth anything at all. First, it just begs the question of how you find the server. Second, pg_service is only really interesting if there are multiple servers you want to connect to. It's not reasonable to assume that one of them will know about any (let alone all) of the others. Once you start to think about security it's even worse: you've got that one storing passwords and so on for the other servers. My complaint about pg_service is actually that it should have been designed to support per-user values more easily. It's a takeoff on the ODBC ini file concept, but we forgot the per-user ~/.odbc.ini part. regards, tom lane ---(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] pg_service.conf
> Martijn van Oosterhout writes: >> I think the major issue is that most such systems (like RFC2782) deal >> only with finding the hostname:port of the service and don't deal with >> usernames/passwords/dbname. What we want is a system that not only >> finds the service, but tells you enough to connect. > > In other words, anyone on the LAN who asks nicely can get a database > password? No thank you. > > I don't actually believe that a server-side substitute for pg_service > would be worth anything at all. First, it just begs the question of > how you find the server. Second, pg_service is only really interesting > if there are multiple servers you want to connect to. It's not > reasonable to assume that one of them will know about any (let alone > all) of the others. Once you start to think about security it's even > worse: you've got that one storing passwords and so on for the other > servers. Tom, mark your calendar, I think in this one instance, we are in 100% total agreement. I'm not sure what this means, does one of have to change our opinion? Actually, pg_service.conf, as I think more about it, is more than just "pg_service is only really interesting if there are multiple servers you want to connect to," it even abstracts the physical database name, which is interesting as well. > > My complaint about pg_service is actually that it should have been > designed to support per-user values more easily. It's a takeoff on > the ODBC ini file concept, but we forgot the per-user ~/.odbc.ini part. I can certainly see that application, and it should be trivial to add any that code. Do you think it is worth doing? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_service.conf
> Mark Woodward wrote: >> Don't get me wrong, DNS, as it is designed, is PERFECT for the >> distributed nature of the internet, but replication of fairly static >> data under the control of a central authority (the admin) is better. > > What about this zeroconf/bonjour stuff? I'm not familiar with it, but > it sounds like it could tie into this discussion. > Perhaps zeroconf is useful for stuff like thin clients, but I'm not sure that it introduces anything into this discussion. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] pg_service.conf
> "Mark Woodward" <[EMAIL PROTECTED]> writes: > >> DNS isn't always a better solution than /etc/hosts, both have their pros >> and cons. The /etc/hosts file is very useful for "instantaneous," >> reliable, and redundent name lookups. DNS services, espcially in a large >> service environment can get bogged down. 20,000 hosts doing a lot of >> lookups can require a dedicated single point of failure. OK, so you add >> two DNS machines and load balance across them with a fault tollerant >> load >> balancer, how many thousands of dollars? For how much information? A >> simple "clustercpy -f targets pg_service.conf /etc" would save thousands >> of dollars, increase efficiency, increase reliability, decrease >> electrical >> costs, etc. > > Um, is there something wrong with having multiple DNS servers in > resolv.conf? Other than having to time out on #1 before you try #2? > I'm genuinely curious. What is the "timeout" of that DNS lookup, before it goes to the second DNS server? > >> Don't get me wrong, DNS, as it is designed, is PERFECT for the >> distributed >> nature of the internet, but replication of fairly static data under the >> control of a central authority (the admin) is better. > > You're probably right; clustercpy or rsync would work better if you > have admin access to all the machines in question. The nice thing > about the DNS method is that you wouldn't necessarily have to have > that access on an ongoing basis. That is, of course, one of DNS' pros, but in an environment where that is not nessisary, why bother? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote: > elein <[EMAIL PROTECTED]> writes: > > I've got a domain based on a text type. > > I've overridden the equal operator with > > lower(text) = lower(text). > > This won't work, you need to make a type instead. > Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) If it doesn't pass the tests, well, I'm hoping it will be functionally close enough for common use. This implementation is all in SQL and plperl--no C code. Now my only complaint is that ORDER BY requires the USING op when it should recognize the information in the opclass for btree > and < for the type of the sort column. I can explain why it doesn't recognize the opclass information, but I think it should. > > If this is the way domains really are, I would strongly suggest > > expanding create domain to merge with create type (under) and > > allow us to list the basic functions. > > IMHO, the exact difference between a domain and a type is you get to > choose your own definitions of the basic operations on a type. There's > no free lunch: as soon as you start substituting operations the > complexity involved goes up by an order of magnitude. Type inheritance for base types should work by inheriting all of the parent's definitions and overriding them only as necessary. What I'm trying to do is to demonstrate that we can weasle this functionality using domains. --elein [EMAIL PROTECTED] > > regards, tom lane > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > ---(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] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: > Actually I can do and have done this. It is being tested now. > I did create an opclass. It creates a UNIQUE index just fine > for the type using the lower() functionality. *If* it passes > all of my testing I'll publish it tomorrow on general bits. > Perhaps folks can help try to break it when I publish (or > if you are around today Sunday, you can test in advance--email me > directly.) How is this different from the citext module I suggested? > Now my only complaint is that ORDER BY requires the USING op > when it should recognize the information in the opclass for > btree > and < for the type of the sort column. I can explain > why it doesn't recognize the opclass information, but I think > it should. ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it is currently. To use ORDER BY by itself you need to call your operators < and >. > Type inheritance for base types should work by inheriting all of the > parent's definitions and overriding them only as necessary. What I'm > trying to do is to demonstrate that we can weasle this functionality > using domains. Well, you can kind of do this by creating an implicit cast from your type to text. Then you can use your type anywhere where text can appear (like strpos, length, etc). Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] pg_service.conf
"Mark Woodward" <[EMAIL PROTECTED]> writes: >> Um, is there something wrong with having multiple DNS servers in >> resolv.conf? Other than having to time out on #1 before you try #2? >> I'm genuinely curious. > > What is the "timeout" of that DNS lookup, before it goes to the second DNS > server? I think on the order of 20-30 seconds, which may or may not be an issue. -Doug ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: > On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: > > Actually I can do and have done this. It is being tested now. > > I did create an opclass. It creates a UNIQUE index just fine > > for the type using the lower() functionality. *If* it passes > > all of my testing I'll publish it tomorrow on general bits. > > Perhaps folks can help try to break it when I publish (or > > if you are around today Sunday, you can test in advance--email me > > directly.) > > How is this different from the citext module I suggested? > My implementation is in SQL and plperl only. Also, the lower case comparisons are only one aspect of the datatype. > > Now my only complaint is that ORDER BY requires the USING op > > when it should recognize the information in the opclass for > > btree > and < for the type of the sort column. I can explain > > why it doesn't recognize the opclass information, but I think > > it should. > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it > is currently. To use ORDER BY by itself you need to call your operators > < and >. > This does not work where x is datatype foo with opclass foo_ops. In this case, it uses the text > instead of the foo >. > > Type inheritance for base types should work by inheriting all of the > > parent's definitions and overriding them only as necessary. What I'm > > trying to do is to demonstrate that we can weasle this functionality > > using domains. > > Well, you can kind of do this by creating an implicit cast from your > type to text. Then you can use your type anywhere where text can appear > (like strpos, length, etc). > Yes, I'm leveraging most of the text functions. > Hope this helps, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. ---(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] postgresql query string length limit
I found any query exceeds 4096 charactors will be pruned automatically. i am wondering which knob should i change to make it larger , say, 1 charactors. i searched for a while but was not able to find it online. so if anyone has a quick nswer that will be highly appreciated.
Re: [HACKERS] postgresql query string length limit
uwcssa <[EMAIL PROTECTED]> writes: > I found any query exceeds 4096 charactors will be pruned automatically. i am > wondering which knob should i change to make it larger , say, 1 > charactors. i searched for a while but was not able to find it online. so if > anyone has a quick nswer that will be highly appreciated. Either you're using a very very old version of PostgreSQL, or a very crappy client library--there hasn't been a query length limitation in the server or in libpq for a long time (I think when there was one it was 8K by default). -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it > > is currently. To use ORDER BY by itself you need to call your operators > > < and >. > > > > This does not work where x is datatype foo with opclass foo_ops. > In this case, it uses the text > instead of the foo >. Huh? You must be doing something unusual because it does work normally. Did you specify the opclass as the default for the type? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Pgfoundry and gborg: shut one down
FYI - as a positive enhancement, Greenplum donated a beefy server to host pgFoundry. - Luke On 2/18/06 10:34 AM, "Tom Lane" <[EMAIL PROTECTED]> wrote: > Thomas Hallgren <[EMAIL PROTECTED]> writes: >> Bruce Momjian wrote: >>> Having run had both pgfoundary and gborg for several years, I think we >>> have to conclude that any clean migration is never going to happen, so >>> let's just pick a server and announce date, and shut one of them off. > >> I've repeatedly asked for help moving my PL/Java stuff over to pgfoundry and >> offered my help >> in the process, > > Indeed, we haven't made any particular effort to encourage gborg > projects to move. I think it's a bit premature to hold a gun to > their heads. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > > ---(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] Domains and supporting functions
Martijn van Oosterhout wrote: On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: Actually I can do and have done this. It is being tested now. I did create an opclass. It creates a UNIQUE index just fine for the type using the lower() functionality. *If* it passes all of my testing I'll publish it tomorrow on general bits. Perhaps folks can help try to break it when I publish (or if you are around today Sunday, you can test in advance--email me directly.) How is this different from the citext module I suggested? Have you looked at the code of citext? Unless I'm misreading, it creates a lowercase copy of each string for each comparison. And it doesn't look to me like it's encoding/locale aware. No doubt it serves the author's needs, but I'd be very careful of using or recommending it for general use. I'm not sure how hard a text type with efficient, encoding and locale aware, case-insensitive comparison would be to create , but it would be a Good Thing (tm) to have available. cheers andrew ---(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] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: > On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: > > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: > > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it > > > is currently. To use ORDER BY by itself you need to call your operators > > > < and >. > > > > > > > This does not work where x is datatype foo with opclass foo_ops. > > In this case, it uses the text > instead of the foo >. > > Huh? You must be doing something unusual because it does work normally. > Did you specify the opclass as the default for the type? > I'll show you my test case if you'll show me yours :) ~e > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. ---(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] Domains and supporting functions
On Sun, Feb 19, 2006 at 04:35:56PM -0500, Andrew Dunstan wrote: > Have you looked at the code of citext? Unless I'm misreading, it creates > a lowercase copy of each string for each comparison. And it doesn't look > to me like it's encoding/locale aware. Its cilower function isn't terribly great and could probably do with some work. toupper/tolower() are encoding/locale sensetive, but the code used doesn't really handle multibyte encodings. But it's an excellent starting point for creating new types because almost all the hard work is done. > I'm not sure how hard a text type with efficient, encoding and locale > aware, case-insensitive comparison would be to create , but it would be > a Good Thing (tm) to have available. Hmm, "case-insensetive match" is a terribly badly defined concept. There's a reason why there's a strcasecmp() but no strcasecoll(). The code currently uses tolower, but if you changed it to do toupper it would be equally valid yet produce different results. If/when we ever get to use a real internationalisation library like ICU, we can do things like convert strings to Normal Form D so we can compare character seperate from their accents, ie accent-insensetive comparison. In any case ICU contains mappings for things like title-case and all the different kinds of space and hyphens so people can specify their own mapping to get whatever they're happy with. Until then, people will just have to rely on their system's support for tolower(). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
[HACKERS] Fix to CVE-2006-0553 for 8.1.1
Does the patch below look like the correct fix to CVE-2006-0553 if
running 8.1.1? I just scanned cvs log from the 8.1 branch, looking for
CVE-2006-0553 and picked out the diffs.
--
albert chin ([EMAIL PROTECTED])
-- snip snip
Index: src/backend/commands/variable.c
===
--- src/backend/commands/variable.c.orig2005-11-22 12:23:08.0
-0600
+++ src/backend/commands/variable.c 2006-02-19 15:24:40.540106000 -0600
@@ -586,7 +586,9 @@
* by the numeric oid, followed by a comma, followed by the role name.
* This cannot be confused with a plain role name because of the NAMEDATALEN
* limit on names, so we can tell whether we're being passed an initial
- * role name or a saved/restored value.
+ * role name or a saved/restored value. (NOTE: we rely on guc.c to have
+ * properly truncated any incoming value, but not to truncate already-stored
+ * values. See GUC_IS_NAME processing.)
*/
extern char *session_authorization_string; /* in guc.c */
Index: src/include/utils/guc_tables.h
===
--- src/include/utils/guc_tables.h.orig 2005-07-14 00:13:44.0 -0500
+++ src/include/utils/guc_tables.h 2006-02-19 15:29:15.187973000 -0600
@@ -126,6 +126,7 @@
#define GUC_DISALLOW_IN_FILE 0x0040 /* can't set in postgresql.conf */
#define GUC_CUSTOM_PLACEHOLDER 0x0080 /* placeholder for custom variable */
#define GUC_SUPERUSER_ONLY 0x0100 /* show only to superusers */
+#define GUC_IS_NAME0x0200 /* limit string to
NAMEDATALEN-1 */
/* bit values in status field */
#define GUC_HAVE_TENTATIVE 0x0001 /* tentative value is defined */
Index: src/backend/utils/misc/guc.c
===
--- src/backend/utils/misc/guc.c.orig 2005-11-22 12:23:24.0 -0600
+++ src/backend/utils/misc/guc.c2006-02-19 15:30:21.625766000 -0600
@@ -48,6 +48,7 @@
#include "optimizer/prep.h"
#include "parser/parse_expr.h"
#include "parser/parse_relation.h"
+#include "parser/scansup.h"
#include "postmaster/autovacuum.h"
#include "postmaster/bgwriter.h"
#include "postmaster/syslogger.h"
@@ -1662,7 +1663,7 @@
{"client_encoding", PGC_USERSET, CLIENT_CONN_LOCALE,
gettext_noop("Sets the client's character set
encoding."),
NULL,
- GUC_REPORT
+ GUC_IS_NAME | GUC_REPORT
},
&client_encoding_string,
"SQL_ASCII", assign_client_encoding, NULL
@@ -1742,7 +1743,8 @@
{
{"default_tablespace", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the default tablespace to create
tables and indexes in."),
- gettext_noop("An empty string selects the database's
default tablespace.")
+ gettext_noop("An empty string selects the database's
default tablespace."),
+ GUC_IS_NAME
},
&default_tablespace,
"", assign_default_tablespace, NULL
@@ -1900,7 +1902,7 @@
{"server_encoding", PGC_INTERNAL, CLIENT_CONN_LOCALE,
gettext_noop("Sets the server (database) character set
encoding."),
NULL,
- GUC_REPORT | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_IS_NAME | GUC_REPORT | GUC_NOT_IN_SAMPLE |
GUC_DISALLOW_IN_FILE
},
&server_encoding_string,
"SQL_ASCII", NULL, NULL
@@ -1922,7 +1924,7 @@
{"role", PGC_USERSET, UNGROUPED,
gettext_noop("Sets the current role."),
NULL,
- GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE
| GUC_DISALLOW_IN_FILE
+ GUC_IS_NAME | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
&role_string,
"none", assign_role, show_role
@@ -1933,7 +1935,7 @@
{"session_authorization", PGC_USERSET, UNGROUPED,
gettext_noop("Sets the session user name."),
NULL,
- GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL |
GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
+ GUC_IS_NAME | GUC_REPORT | GUC_NO_SHOW_ALL |
GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE
},
&session_authorization_string,
NULL, assign_session_authorization, show_session_authorization
@@ -3934,6 +3936,12 @@
newval = guc_strdup(elevel, value);
if (newval == NULL)
return false;
+
Re: [HACKERS] Fix to CVE-2006-0553 for 8.1.1
Albert Chin <[EMAIL PROTECTED]> writes: > Does the patch below look like the correct fix to CVE-2006-0553 if > running 8.1.1? Why in the world would you not install 8.1.3 instead? Or are you hoping to get burnt by one of the *other* bugs in 8.1.1? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_service.conf
On Feb 19, 2006, at 10:59 AM, Mark Woodward wrote: "Mark Woodward" <[EMAIL PROTECTED]> writes: DNS isn't always a better solution than /etc/hosts, both have their pros and cons. The /etc/hosts file is very useful for "instantaneous," reliable, and redundent name lookups. DNS services, espcially in a large service environment can get bogged down. 20,000 hosts doing a lot of lookups can require a dedicated single point of failure. OK, so you add two DNS machines and load balance across them with a fault tollerant load balancer, how many thousands of dollars? For how much information? A simple "clustercpy -f targets pg_service.conf /etc" would save thousands of dollars, increase efficiency, increase reliability, decrease electrical costs, etc. Um, is there something wrong with having multiple DNS servers in resolv.conf? Other than having to time out on #1 before you try #2? I'm genuinely curious. What is the "timeout" of that DNS lookup, before it goes to the second DNS server? Depends on the resolver you use. Often the "timeout" is zero. Other times it's adaptive, depending on history of response time from the servers. Except in the case of horrible misconfiguration, it's rarely a problem. Cheers, Steve ---(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] Fix to CVE-2006-0553 for 8.1.1
On Sun, Feb 19, 2006 at 05:14:32PM -0500, Tom Lane wrote: > Albert Chin <[EMAIL PROTECTED]> writes: > > Does the patch below look like the correct fix to CVE-2006-0553 if > > running 8.1.1? > > Why in the world would you not install 8.1.3 instead? Or are you hoping > to get burnt by one of the *other* bugs in 8.1.1? We've already deployed 8.1.1 to some customers. We will offer 8.1.3 but if they want to upgrade 8.1.1 to fix the security issue, we want this to be an option. -- albert chin ([EMAIL PROTECTED]) ---(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] Domains and supporting functions
On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote: > On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: > > On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: > > > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: > > > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it > > > > is currently. To use ORDER BY by itself you need to call your operators > > > > < and >. > > > > > > > > > > This does not work where x is datatype foo with opclass foo_ops. > > > In this case, it uses the text > instead of the foo >. > > > > Huh? You must be doing something unusual because it does work normally. > > Did you specify the opclass as the default for the type? > > > > I'll show you my test case if you'll show me yours :) Ok, here's a quick example I whipped up and if you run it it clearly shows it's using the comparison function from the operator class. http://svana.org/kleptog/temp/text2.example It basically replicates the entire infrastructure for the text type as a new type, "text2" so there's planty of scope for confusion, but postgresql correctly picks the right function. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] postgresql query string length limit
I am using version 8.0.3. i installed using the --without-readline option. is there a quick workaround? On 2/19/06, Douglas McNaught <[EMAIL PROTECTED]> wrote: > uwcssa <[EMAIL PROTECTED]> writes: > > > I found any query exceeds 4096 charactors will be pruned automatically. i > am > > wondering which knob should i change to make it larger , say, 1 > > charactors. i searched for a while but was not able to find it online. > so if > > anyone has a quick nswer that will be highly appreciated. > > Either you're using a very very old version of PostgreSQL, or a very > crappy client library--there hasn't been a query length limitation in > the server or in libpq for a long time (I think when there was one it > was 8K by default). > > -Doug > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] postgresql query string length limit
uwcssa <[EMAIL PROTECTED]> writes: > I am using version 8.0.3. i installed using the > --without-readline option. What client are you using? > is there a quick workaround? The limit shouldn't be there. If you can post a test case that demonstrates the problem, perhaps someone can help. -Doug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
AC_REPLACE_FUNCS([getaddrinfo]) won't correctly detect getaddrinfo on Tru64 UNIX because the function doesn't exist under that name in libc. >From : #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif The original code in 8.1.1 was: AC_MSG_CHECKING([for getaddrinfo by including ]) AC_TRY_LINK([#include #include #include ], [getaddrinfo(NULL, NULL, NULL, NULL);], [AC_MSG_RESULT([yes]) AC_DEFINE(HAVE_GETADDRINFO, 1, [Define to 1 if you have the `getaddrinfo' function.])], [AC_MSG_RESULT([no]) AC_LIBOBJ(getaddrinfo)]) So, what's the best way to merge the two? If getaddrinfo() is borked on Windows, how about AC_TRY_RUN to test it out? -- albert chin ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Prepared Xacts and Vacuum question
On 2/20/06, Tom Lane <[EMAIL PROTECTED]> wrote: > A prepared xact is the same as an open xact as far as vacuum is > concerned. It's a bad idea to sit on either open or prepared xacts > for long periods ... I completely understand that, however it can be occured... Prepared xacts never die... > > I think the prepared xacts on any database mustn't affect to another > > database. > > Wrong, consider updates to shared catalogs. But my prepared xact did not affect to the system catalogs I think some messages should be shown to the DBA by the backend, because DBA will get in trouble without any information about it. And also it should be noted on the manual. Thanks. -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 11:34:21PM +0100, Martijn van Oosterhout wrote: > On Sun, Feb 19, 2006 at 01:36:41PM -0800, elein wrote: > > On Sun, Feb 19, 2006 at 10:29:35PM +0100, Martijn van Oosterhout wrote: > > > On Sun, Feb 19, 2006 at 12:59:35PM -0800, elein wrote: > > > > On Sun, Feb 19, 2006 at 07:57:42PM +0100, Martijn van Oosterhout wrote: > > > > > ORDER BY x ASC is a synonym for ORDER BY x USING >. That's the way it > > > > > is currently. To use ORDER BY by itself you need to call your > > > > > operators > > > > > < and >. > > > > > > > > > > > > > This does not work where x is datatype foo with opclass foo_ops. > > > > In this case, it uses the text > instead of the foo >. > > > > > > Huh? You must be doing something unusual because it does work normally. > > > Did you specify the opclass as the default for the type? > > > > > > > I'll show you my test case if you'll show me yours :) > > Ok, here's a quick example I whipped up and if you run it it clearly > shows it's using the comparison function from the operator class. > > http://svana.org/kleptog/temp/text2.example > > It basically replicates the entire infrastructure for the text type as > a new type, "text2" so there's planty of scope for confusion, but > postgresql correctly picks the right function. > > Have a nice day, > -- > Martijn van Oosterhout http://svana.org/kleptog/ > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > > tool for doing 5% of the work and then sitting around waiting for someone > > else to do the other 95% so you can sue them. Comparing test cases we found that Martijn was using a true CREATE TYPE while I am using CREATE DOMAIN. That was the only difference that mattered. So far there are only two gotchas with this exercise of making a domain based base type. 1) LIKE doesn't work. Workaround: create and use like-ish operator. Arguably correct behavior. 2) ORDER BY requires the USING op clause. Workaround: use the USING op clause. --elein ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] postgresql query string length limit
uwcssa wrote: I am using version 8.0.3. i installed using the --without-readline option. Platform and os version would be good too :-), along with the client you are using to elicit this behavior (e.g, psql, Pgadmin etc). Cheers Mark ---(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] postgresql query string length limit
i am using psql client. On Fedora core (linux core: 2.4.20-8 ) as well on Suze 10.0 (core: 2.6.13-15.7-smp). Both has the same problem. thanks /19/06, Mark Kirkwood <[EMAIL PROTECTED]> wrote: > uwcssa wrote: > > I am using version 8.0.3. i installed using the > > --without-readline option. > > > > Platform and os version would be good too :-), along with the client you > are using to elicit this behavior (e.g, psql, Pgadmin etc). > > Cheers > > Mark > > > > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] postgresql query string length limit
uwcssa <[EMAIL PROTECTED]> writes: > i am using psql client. On Fedora core (linux core: 2.4.20-8 ) > as well on Suze 10.0 (core: 2.6.13-15.7-smp). Both has the same problem. Please send a test case (shell script that shows the behavior). I can do $ psql -f foo.sql doug where 'foo.sql' inserts an 16384-character string into a table in a single query. This is with PG 8.0.4. The limit clearly does not exist, so we need to find out what you're doing wrong. :) -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Fix to CVE-2006-0553 for 8.1.1
Albert Chin <[EMAIL PROTECTED]> writes: > On Sun, Feb 19, 2006 at 05:14:32PM -0500, Tom Lane wrote: >> Why in the world would you not install 8.1.3 instead? Or are you hoping >> to get burnt by one of the *other* bugs in 8.1.1? > We've already deployed 8.1.1 to some customers. We will offer 8.1.3 > but if they want to upgrade 8.1.1 to fix the security issue, we want > this to be an option. You want an option to leave data-loss-causing bugs unfixed, eh? Make sure you make those customers sign a disclaimer that it's their fault not yours when the ReadBuffer bug eats their data. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Need pointers to "standard" pg database(s) for testing
Not really, but you can check out the sample databases project: http://pgfoundry.org/projects/dbsamples/ Chris Ron wrote: I assume we have such? Ron ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
Albert Chin <[EMAIL PROTECTED]> writes: > AC_REPLACE_FUNCS([getaddrinfo]) won't correctly detect getaddrinfo on > Tru64 UNIX because the function doesn't exist under that name in libc. We changed that code specifically so it *would* work on Tru64 --- see this thread: http://archives.postgresql.org/pgsql-hackers/2006-01/msg00511.php Please explain why you think it's a regression. regards, tom lane ---(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] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
On Sun, Feb 19, 2006 at 09:02:48PM -0500, Tom Lane wrote: > Albert Chin <[EMAIL PROTECTED]> writes: > > AC_REPLACE_FUNCS([getaddrinfo]) won't correctly detect getaddrinfo on > > Tru64 UNIX because the function doesn't exist under that name in libc. > > We changed that code specifically so it *would* work on Tru64 --- see > this thread: > http://archives.postgresql.org/pgsql-hackers/2006-01/msg00511.php > Please explain why you think it's a regression. >From my reading, no completed patch was posted in the thread. AC_REPLACE_FUNCS([getaddrinfo]) will not detect getaddrinfo() on Tru64 UNIX because getaddrinfo is not in libc. Because of this, getaddrinfo isn't detected and the compilation of src/port/thread.c fails: cc -std -O2 -ieee -msym -readonly_strings -I../../src/port -DFRONTEND -I../../src/include -I/opt/TWWfsw/gettext014/include -I/opt/TWWfsw/libopenssl097/include -I/opt/TWWfsw/zlib11/include -I/opt/TWWfsw/tcl84/include -I/opt/TWWfsw/tk84/include -pthread --thread-safe -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS -c thread.c cc: Warning: thread.c, line 80: In this statement, "strerror_r(...)" of type "int", is being converted to "pointer to char". (cvtdiftypes) return strerror_r(errnum, strerrbuf, buflen); ---^ cc: Warning: thread.c, line 141: In this statement, the referenced type of the pointer value "buffer" is "char", which is not compatible with "struct hostent_data". (ptrmismatch) *result = gethostbyname_r(name, resultbuf, buffer, buflen, herrno); ---^ cc: Error: thread.c, line 141: In this statement, "gethostbyname_r" expects 3 arguments, but 5 are supplied. (toomanyargs) *result = gethostbyname_r(name, resultbuf, buffer, buflen, herrno); --^ gmake[2]: *** [thread.o] Error 1 gethostbyname_r() on Tru64 UNIX accepts 3 arguments. -- albert chin ([EMAIL PROTECTED]) ---(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] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
Albert Chin <[EMAIL PROTECTED]> writes: > On Sun, Feb 19, 2006 at 09:02:48PM -0500, Tom Lane wrote: >> We changed that code specifically so it *would* work on Tru64 --- see >> this thread: >> http://archives.postgresql.org/pgsql-hackers/2006-01/msg00511.php > From my reading, no completed patch was posted in the thread. Well, indeed the original reporter doesn't seem to have bothered to test the applied patch :-( > AC_REPLACE_FUNCS([getaddrinfo]) will not detect getaddrinfo() on Tru64 > UNIX because getaddrinfo is not in libc. Hmm, where is it then? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
On Sun, Feb 19, 2006 at 09:56:20PM -0500, Tom Lane wrote: > Albert Chin <[EMAIL PROTECTED]> writes: > > > AC_REPLACE_FUNCS([getaddrinfo]) will not detect getaddrinfo() on Tru64 > > UNIX because getaddrinfo is not in libc. > > Hmm, where is it then? getaddrinfo is a macro in : #if defined (_SOCKADDR_LEN) || defined (_XOPEN_SOURCE_EXTENDED) #define getaddrinfo ngetaddrinfo #else #define getaddrinfo ogetaddrinfo #endif The solution is to either revert to the 8.1.1 code (my recommendation) or check for ngetaddrinfo. The latter is a crude hack though. $ nm /usr/shlib/libc.so | grep getaddrinfo __ngetaddrinfo | 0004395900636352 | T | 0008 __ogetaddrinfo | 0004395900637184 | T | 0008 ngetaddrinfo | 0004395900636352 | T | 0008 ogetaddrinfo | 0004395900637184 | T | 0008 -- albert chin ([EMAIL PROTECTED]) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Pgfoundry and gborg: shut one down
On Sun, February 19, 2006 05:10, Bruce Momjian wrote: > I don't care what direction we go, just kill one. Speaking for libpqxx, my only concern with that is the mailing list. Would those have to move to different addresses--or conversely, would a forced migration make it much easier to move *all* GBorg mailing lists to pgFoundry and maintain their old addresses? Jeroen ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Domains and supporting functions
On Sun, Feb 19, 2006 at 10:34:02AM -0800, elein wrote: > On Sun, Feb 19, 2006 at 01:26:31AM -0500, Tom Lane wrote: > > elein <[EMAIL PROTECTED]> writes: > > > I've got a domain based on a text type. > > > I've overridden the equal operator with > > > lower(text) = lower(text). > > > > This won't work, you need to make a type instead. > > > > Actually I can do and have done this. It is being tested now. > I did create an opclass. It creates a UNIQUE index just fine > for the type using the lower() functionality. *If* it passes > all of my testing I'll publish it tomorrow on general bits. > Perhaps folks can help try to break it when I publish (or > if you are around today Sunday, you can test in advance--email me > directly.) > > If it doesn't pass the tests, well, I'm hoping > it will be functionally close enough for common use. This > implementation is all in SQL and plperl--no C code. > > Now my only complaint is that ORDER BY requires the USING op > when it should recognize the information in the opclass for > btree > and < for the type of the sort column. I can explain > why it doesn't recognize the opclass information, but I think > it should. > > > > > If this is the way domains really are, I would strongly suggest > > > expanding create domain to merge with create type (under) and > > > allow us to list the basic functions. > > > > IMHO, the exact difference between a domain and a type is you get to > > choose your own definitions of the basic operations on a type. There's > > no free lunch: as soon as you start substituting operations the > > complexity involved goes up by an order of magnitude. > > Type inheritance for base types should work by inheriting all of the > parent's definitions and overriding them only as necessary. What I'm > trying to do is to demonstrate that we can weasle this functionality > using domains. > > --elein > [EMAIL PROTECTED] > > > > > regards, tom lane > > > > ---(end of broadcast)--- > > TIP 4: Have you searched our list archives? > > > >http://archives.postgresql.org > > OK. My article (and code) is published. It could probably use some more tries to find holes in the implementation. http://www.varlena.com/GeneralBits/128.php Known Problems and Issues: * Creating the table with an email PRIMARY KEY did not use our comparison function. It was necessary to create a unique index which explicitly used the email operator class. * ORDER BY requires USING op clause. * LIKE does not work. Use defined operator % instead. There are convincing arguments for and against this behavior. Feel free to argue one way or the other. [EMAIL PROTECTED] I've always depended on the [QA] of strangers. :) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Pgfoundry and gborg: shut one down
On Mon, 20 Feb 2006, Jeroen T. Vermeulen wrote: On Sun, February 19, 2006 05:10, Bruce Momjian wrote: I don't care what direction we go, just kill one. Speaking for libpqxx, my only concern with that is the mailing list. Would those have to move to different addresses--or conversely, would a forced migration make it much easier to move *all* GBorg mailing lists to pgFoundry and maintain their old addresses? All addresses would have to be changed to the pgfoundry.org one ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
Albert Chin <[EMAIL PROTECTED]> writes: > On Sun, Feb 19, 2006 at 09:56:20PM -0500, Tom Lane wrote: >> Hmm, where is it then? > getaddrinfo is a macro in : Yes, we know that. The question was where does the macro point. > The solution is to either revert to the 8.1.1 code (my recommendation) > or check for ngetaddrinfo. The latter is a crude hack though. Hm, I think both of us are confused: me because I thought we'd changed the getaddrinfo test, which in fact has not happened, and you because you think 8.1.1 is different from 8.1.3 on this point, which it is not. Would you try the patch proposed at http://archives.postgresql.org/pgsql-patches/2006-01/msg00299.php and see if it fixes the problem? That patch has *not* gotten applied, probably because no one confirmed that it worked. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Need pointers to "standard" pg database(s) for
Relating to this. If anyone can find govt or other free db's and convert them into pgsql format, I will host them on the dbsamples page. The dbsamples are _really_ popular! Chris Scott Marlowe wrote: On Fri, 2006-02-17 at 10:51, Ron wrote: I assume we have such? Depends on what you wanna do. For transactional systems, look at some of the stuff OSDL has done. For large geospatial type stuff, the government is a good source, like www.usgs.gov or the fcc transmitter database. There are other ones out there. Really depends on what you wanna test. ---(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 ---(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] postgresql query string length limit
On Mon, Feb 20, 2006 at 01:28:50PM +1300, Mark Kirkwood wrote: > uwcssa wrote: > >I am using version 8.0.3. i installed using the > >--without-readline option. > > Platform and os version would be good too :-), along with the client you > are using to elicit this behavior (e.g, psql, Pgadmin etc). Are you cutting and pasting from one window into another? If so then I wonder if something like the terminal's buffer size is the problem. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] AC_REPLACE_FUNCS([getaddrinfo]) in 8.1.3
On Sun, Feb 19, 2006 at 11:32:53PM -0500, Tom Lane wrote: > Would you try the patch proposed at > http://archives.postgresql.org/pgsql-patches/2006-01/msg00299.php > and see if it fixes the problem? That patch has *not* gotten applied, > probably because no one confirmed that it worked. This test is different, it checks that getaddrinfo has four args. I'm not sure if it's important but if you just want to check the function exists, just checking for: [return getaddrinfo ? 0 : 1;], Will check for existance. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. signature.asc Description: Digital signature
Re: [HACKERS] Pgfoundry and gborg: shut one down
On Mon, February 20, 2006 11:00, Marc G. Fournier wrote: >> Speaking for libpqxx, my only concern with that is the mailing list. >> Would those have to move to different addresses--or conversely, would a >> forced migration make it much easier to move *all* GBorg mailing lists >> to >> pgFoundry and maintain their old addresses? > > All addresses would have to be changed to the pgfoundry.org one ... Ouch! Moving my project off GBorg wasn't so hard, but forcing all mailing list subscribers to move to a different address does hurt. If the same goes for many other projects on there, wouldn't it be possible to move all mail handling for gborg.postgresql.org over to pgFoundry at once, but preserve the domain name and list names? It may help people make the jump if mailing list migration could be decoupled from the other changes. Jeroen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
