[HACKERS] message at end of install
IIRC the message at the end of install used to echo out the startup
command (pg_ctl or postmaster), but now it gives some nice information
on how to get help. Should the startup message be put back in? Seems
like it is the most likely thing someone who just installed would want
to know.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Oversight?
On Tue, 12 Aug 2003, Christopher Kings-Lynne wrote: rbt=3D# ALTER USER rbt SET CONSTRAINTS ALL DEFERRED; ERROR: syntax error at or near ALL at character 32 rbt=3D# ALTER USER rbt SET CONSTRAINTS =3D DEFERRED; ERROR: constraints is not a recognized option SET CONSTRAINTS ALL DEFERRED is a SQL-spec-mandated command syntax. Any similarity to Postgres' SET var = value syntax ends with the initial keyword. I assume his point is how do we set all of a user's constraints deferred by default? I don't think that'd make all of a user's constraints deferred, I'd think it would make all constraints that are deferrable deferred for that user's transactions (as if the user did a set constraints all deferred at the beginning of every transaction). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Parsing speed (was Re: [HACKERS] pgstats_initstats() cost)
On Tue, 12 Aug 2003, Tom Lane wrote: I have just finished running some experiments that compared a series of INSERTs issued via PQexec() versus preparing an INSERT command and then issuing new-FE-protocol Bind and Execute commands against the prepared statement. With a test case like the above (one target column and a prepared statement like insert into abc values($1)), I saw about a 30% speedup. (Or at least I did after fixing a couple of bottlenecks in the backend's per-client-message loop.) [snip] This leaves us with a bit of a problem, though, because there isn't any libpq API that allows access to this speedup. I put in a routine to support Parse/Bind/Execute so that people could use out-of-line parameters for safety reasons --- but there's no function to do Bind/Execute against a pre-existing prepared statement. (I had to make a hacked version of libpq to do the above testing.) I'm beginning to think that was a serious omission. I'm tempted to fix it, even though we're past feature freeze for 7.4. Comments? I think it would be well worth waiting for this feature. I often bind parameters in Perl's DBD::Pg, looking to the future when that doesn't just handle quoting, but also uses faster cached prepared queries. It'd be great to see that in 7.4. Jon ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] address family failure message
On Sat, Aug 09, 2003 at 07:06:58PM -0400, Andrew Dunstan wrote: I am seeing this (RH8 - cvs tip): 2003-08-09 18:55:14 [6680] LOG: failed to create socket: Address family not supported by protocol Probably harmless - presumably refers to IPv6 not running, but annoying nevertheless, and I don't recall seeing it before. I can still connect on IP4 socket and Unix socket. This is because we removed AI_ADDRCONF from the getaddrinfo() call because it breaks on older glibc versions. Kurt ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
Your pg_dump's actually invoke the pager? Are you manually starting psql, then doing \i dumpfile? Why would you do that rather than psql template1 dumpfile? Because I'm a dork :-). Seriously, sometimes it's useful. The most useful reason (and I wish you could turn it on with psql file) is the line number in the file where any errors occur. (TODO item?) 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] 7.4 beta1 plpgsql regression
The below function works in 7.3 (returns nothing, but no errors). rbttest=# begin; BEGIN rbttest=# create or replace function service.test(integer) rbttest-# returns setof service.service rbttest-# as ' rbttest'# declare rbttest'# v_service service.service%rowtype; rbttest'# rbttest'# begin rbttest'# rbttest'# rbttest'# return; rbttest'# end; rbttest'# ' language plpgsql; CREATE FUNCTION rbttest=# select * from service.test(1); ERROR: cache lookup failed for type 0 CONTEXT: compile of PL/pgSQL function test near line 2 signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [BUGS] 7.4 beta 1: SET log_statement=false
Bruce Momjian [EMAIL PROTECTED] writes: /* Limit non-superuser changes */ if (record-context == PGC_USERLIMIT source PGC_S_UNPRIVILEGED newval conf-session_val ^^^ I had in mind s/session_val/reset_val/ right here. Why wouldn't that work? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Speeding up operations
Rahul_Iyer [EMAIL PROTECTED] writes: im on a project using Postgres. The project involves, at times, upto 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts into a 2 column table (one col=integer, 2nd col=character). I used the Prepare... and execute method, so i basically had 5M execute statements and 1 prepare statement. Postgres took 144min for this... is there any way to improve this performance? COPY, perhaps. Have you read http://www.postgresql.org/docs/view.php?version=7.3idoc=0file=populate.html regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [BUGS] 7.4 beta 1: SET log_statement=false
On Wed, Aug 13, 2003 at 06:05:46PM -0400, Bruce Momjian wrote:
I tried adding this line:
record-session_source PGC_S_UNPRIVILEGED
and it does allow you to set the variable to false if you have set it to
true in your session, but it also allows you to set it to false if it is
set to true in postgresql.conf. You do this by setting it to 'true'
_then_ to 'false' in your session. The reason this works is that there
is no history in the GUC code to record the value this variable was set
at various locations. Once you set it to 'true' in your session, the
system remembers that, and forgets it was also set to true in
postgresql.conf, and therefore allows you to set it to false.
The following describes what I would do if I had the controls
on the postgres source code:
I would put the lambda-user protected configuration parameters
under the control of a system similar to what is used for the
{get|set}rlimit system calls. At the begining of a session, a snapshot
of the parameters is taken, it is kept appart from the live
parameters. The snapshot would be used by the parameters controller to
permit or deny a parameter change depending on the snaphot value, the
current value, the desired new value and a set of rules. A rule could
express things like theses:
* boolean values:
* deny change if the new value would transition from true to
false and the snapshot value is true;
* allow change if the new value would transition from true to
false and the snapshot value is false;
* and any the the negation and/or inverse of both of these rules.
* numerical values:
* deny the change if the new value would be less than the snapshot
value and the current value is larger than the snapshot value;
* deny the change if the new value would be greater than the
snapshot value and the current value is less than the snapshot
value;
* new textual values would be matched against a set of acceptable or
unacceptable user values, the status returned by the countroller
would be according the matched set.
A scheme like this would allow a tolerance from the user point
of view while fixing limits on what can be done by non-superuser
accounts.
It could also be interesting to tie the parameters set to
transactions: parameters changed while inside a transaction could be
reset to their default or pre-transactions values on a commit or
rollback. This could look like the behavior of the save/restore
postscript operators.
Regards.
--
%!PS
297.6 420.9 translate 90 rotate 0 setgray gsave 0 1 1{pop 0 180 moveto 100
180 170 100 170 -10 curveto 180 -9 180 -9 190 -10 curveto 190 100 100 180
0 180 curveto fill 180 rotate}for grestore/Bookman-LightItalic findfont
240 scalefont setfont -151.536392 -63.7998886 moveto (bp)show showpage
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] 7.4 beta binaries
On Tuesday 05 August 2003 03:15, Shridhar Daithankar wrote: I am willing to build 7.4beta binaries on slackware and upload them someplace. This is just to add to binary packages readily available. Can anybody tell me what flags etc. are to be used. I have a slackware 9.0 installation with most of the developer tools I believe. I can give it a shot. Ok. If you want LSB-compliant locations, feel free to use the RPM locations as a model; I realize slack is going to have different locations for things. Is there an existing slack .tgz of PostgreSQL 7.3 or even 7.2 to use as a model? If there is, you would want to build it that way; principle of least surprise. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Windows on SuSE? 7.4
Yes, this is the right fix. I never suspected wsock32 would exist on a non-MS WIn machine. --- Tom Lane wrote: elein [EMAIL PROTECTED] writes: It sounds like Joe's guess on this was right. I've committed this fix in configure.in: *** *** 631,637 AC_CHECK_LIB(gen, main) AC_CHECK_LIB(PW, main) AC_CHECK_LIB(resolv, main) - AC_CHECK_LIB(wsock32, main) AC_SEARCH_LIBS(getopt_long, [getopt gnugetopt]) # QNX: AC_CHECK_LIB(unix, main) --- 636,641 *** *** 645,650 --- 649,659 AC_SEARCH_LIBS(fdatasync, [rt posix4]) # Cygwin: AC_CHECK_LIB(cygipc, shmget) + # WIN32: + if test $PORTNAME = win32 + then + AC_CHECK_LIB(wsock32, main) + fi if test $with_readline = yes; then PGAC_CHECK_READLINE regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Release changes
Andreas Pflug wrote: But PostgreSQL may be better than Oracle, don't you think? In the named document, snip MSSQL2000 still doesn't have row level triggers, and I doubt that 2003 has. Right, so as you've pointed out, Postgres trigger implementation is at least in some ways more flexible than Oracle, and offers row level triggers which MSSQL doesn't even have. All I said was that you're being too harsh by suggesting that statement level triggers don't even deserve mention. You are assuming that everyone migrating to Postgres will miss the MSSQL feature when lots of people (in fact, the majority) don't even use MSSQL. I agree that having the equiv. of MSSQL's inserted and deleted pseudo tables, would be nice, but I wouldn't allow lack thereof to denigrate a useful new feature. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] getting confused parsing ACLITEMS...
The situation seems to be a bug that this patch would address. It seems to
me that when a username is considered unsafe due to containing double
quotes, the double quotes should be escaped (and the backslashes)!
Does this look alright?
Chris
Index: src/backend/utils/adt/acl.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/utils/adt/acl.c,v
retrieving revision 1.94
diff -c -r1.94 acl.c
*** src/backend/utils/adt/acl.c 4 Aug 2003 02:40:04 - 1.94
--- src/backend/utils/adt/acl.c 8 Aug 2003 09:03:19 -
***
*** 124,131
}
if (!safe)
*p++ = '';
! for (src = s; *src; src++)
*p++ = *src;
if (!safe)
*p++ = '';
*p = '\0';
--- 124,134
}
if (!safe)
*p++ = '';
! for (src = s; *src; src++) {
! if (!safe (*src == '' || *src == '\\'))
! *p++ = '\\';
*p++ = *src;
+ }
if (!safe)
*p++ = '';
*p = '\0';
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] LOCK.tag(figuring out granularity of lock)
Jenny - [EMAIL PROTECTED] writes: how do we check whether blockId and tupleId of LOCK.tag are valid or invalid? Look at how LockRelation and LockPage (in src/backend/storage/lmgr/lmgr.c) set up the tags --- it might be clearer then. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] WITH HOLD and pooled connections
Bruce Momjian [EMAIL PROTECTED] writes: Seems we have a problem with pooled connections and WITH HOLD cursors. We have code to reset transaction state and variables via RESET ALL, but how do we remove WITH HOLD cursors when we pass a connection to a new client? Prepared statements would be just as much of a problem. I think the correct answer is simply don't use those features in a pooled environment. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] ecpg thread test program
I have updated ecpg/test/test_thread.pgc to do an automated thread test. First, update to current CVS. Then, run configure with --enable-thread-safety, compile/install, then go to ecpg/test, and do a 'gmake', then run 'test_thread dbname'. It should return success or failure. I hope this program can be used to test threading on our various platforms. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.4 beta binaries
Hi all, I am willing to build 7.4beta binaries on slackware and upload them someplace. This is just to add to binary packages readily available. Can anybody tell me what flags etc. are to be used. I have a slackware 9.0 installation with most of the developer tools I believe. I can give it a shot. I don't have any webspace though. Somebody has to offer that. Bye Shridhar -- Menu, n.: A list of dishes which the restaurant has just run out of. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PROCEDURES was: TODO items
Joe, This isn't isolated to just PL/pgSQL, just like the ability to create and call functions isn't. Support for PROCEDUREs in the backend is a prerequisite to being able to use PL/pgSQL to create procedures. It is necessary but not sufficient. Similarly, if we want to support IN/OUT or named parameters, it isn't a PL/pgSQL issue per se, it is a general one. Sure. But the ability to call in/out parameters (which would also be tied to calling the parameters by name, etc) is pretty useless without supporting them in one of the PLs. And PL/pgSQL is the natural place to start, since it gives a migration path to DBAs with Oracle or MSSQL applications which make heavy use of procedures. FWIW, my vision of how procedures are different from functions goes: 1) no overloading, permitting the calling of an SP with some but not all of its params; 2) no implicit transaction, allowing (maybe requiring?) begin/commit/rollback, and even vacuum, in an SP. 3) named parameters, callable by name from the client; 4) exception handling of some sort (either T-SQL's immediate-response model or the more robust on exception model from PL/SQL). 5) Cannot be called as a part of a larger query (required by (2) above) -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] v7.4 Bundled ... please test ...
On Tue, 5 Aug 2003, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Please test and let me know if there are any problems ... The .gz tarball matches what I have here. Didn't check the .bz2 one. All the .bz2 one is is 'gunzip *.gz;bzip2 *.tar', so should be good :) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Select distinct question ... complicated
On Wed, Aug 06, 2003 at 12:05:47 -0700, The Pennant Shop [EMAIL PROTECTED] wrote: Hi , I have a table: item location aaa 10 aaa 20 bbb 10 bbb 10 ccc 10 ccc 20 I need to select distinct items where locations are the same. So result set should look like: item loation bbb 10 Already spent 7 hours on this one. select item, location from table group by item, location having count(*) 1; ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] TODO: trigger features
Andreas Pflug wrote: YATS (yet another TODO suggestion): provide an official and reliable way to temporarily enable/disable triggers. ALTER TABLE xxx ENABLE/DISABLE TRIGGER ALL/trgName We still have that nasty not presently checked everywhere it should be comment in the doc for pg_trigger... Yes, this could be achieved by dropping and recreating the trigger after importing, which I expect to be suggested by you ;-) We already have that TODO: * Allow triggers to be disabled [trigger] -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] When did we get to be so fast?
Tom Lane wrote:
Bruce Momjian [EMAIL PROTECTED] writes:
I was just testing the threaded ecpg, and ran some performance tests.
Without using threads, I am seeing 100,000 inserts of a single word into
a simple table take 12 seconds:
CREATE TABLE test_thread(message TEXT);
giving me 8333 inserts per second. That seems very high.
Single transaction, or one transaction per INSERT?
This is ecpg, and I didn't have AUTOCOMMIT on, so it was a single
transaction. I had forgotten that.
Also, I was wrong in my computations. It is 4166 inserts per second,
not 8333. Sorry.
I am now seeing more reasonable numbers:
one INSERT per transaction, fsync true 934
one INSERT per transaction, fsync false 1818
one INSERT per transaction, fsync true 4166
With the present WAL design, it's not possible for one backend to commit
more than one transaction per disk rotation --- unless fsync is off, or
your disk drive lies about write-complete. Given that you recently
updated your hardware, I'm betting on the last item ...
Yep.
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
/*
* Thread test program
* by Philip Yarra
*/
#include stdlib.h
voidins1(void);
voidins2(void);
EXEC SQL BEGIN DECLARE SECTION;
char *dbname;
int iterations;
EXEC SQL END DECLARE SECTION;
int
main(int argc, char **argv)
{
if (argc != 3)
{
fprintf(stderr, Usage: %s dbname iterations\n, argv[0]);
return 1;
}
dbname = argv[1];
iterations = atoi(argv[2]);
EXEC SQL CONNECT TO:dbname AS test0;
/* DROP might fail */
EXEC SQL AT test0 DROP TABLE test_thread;
EXEC SQL AT test0 COMMIT WORK;
EXEC SQL AT test0 CREATE TABLE test_thread(message TEXT);
EXEC SQL AT test0 COMMIT WORK;
EXEC SQL DISCONNECT test0;
ins1();
return 0;
}
void
ins1(void)
{
int i;
EXEC SQL WHENEVER sqlerror sqlprint;
EXEC SQL CONNECT TO:dbname AS test1;
/* EXEC SQL AT test1 SET AUTOCOMMIT = ON;*/
for (i = 0; i iterations; i++)
{
EXEC SQL AT test1 INSERT INTO test_thread VALUES('thread1');
}
EXEC SQL AT test1 COMMIT WORK;
EXEC SQL DISCONNECT test1;
}
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Passing server_encoding to the client is not future-proof
Tom Lane writes: One of the reasons for not doing conversion in binary mode is to have an escape hatch for unconvertible characters, eg for dump purposes. That functionality is already provided by setting the client encoding to SQL_ASCII. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Windows on SuSE? 7.4
elein [EMAIL PROTECTED] writes: Yes, I actually have a libwsock32 because my system has wine on it. Wine is a windows emulator. And they drop windows-only libraries into /usr/lib? Yech. Anyway, I can't see a need to include libwsock32 on non-win32 platforms. Will modify configure. regards, tom lane ---(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] consistency check on SPI tuple count failed
On Fri, 8 Aug 2003, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: I got the same thing as Gaetano on my just prior to beta1 system. Well, we couldn't have fixed it since beta1 --- there's been no changes anywhere near SPI. I'm thinking it must be platform-dependent. What are you guys using, exactly? I'm using RedHat 9. ---(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] Can't load a 7.3.4 dump into 7.4CVS
--On Saturday, August 09, 2003 12:31:06 -0500 Larry Rosenman [EMAIL PROTECTED] wrote: Another issue: plpgsql.so needs the elog() function, but it's not exported... actually, this is because of a hardcoded path in my old db, it was picking up the 7.3.4 plpgsql.so. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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] error making man docs
RH8, cvs tip. I get this:
[EMAIL PROTECTED] src]$ make man.tar.gz
D2MDIR=/usr/share/sgml/docbook/utils-0.6.11/helpers
make -C sgml man
make[1]: Entering directory `/home/andrew/foo/pgsql/doc/src/sgml'
{ \
echo !entity version \7.4beta1\; \
echo !entity majorversion \`expr 7.4beta1 :
'\([0-9][0-9]*\.[0-9][0-9]*\)'`\; \
} version.sgml
/usr/bin/perl ./mk_feature_tables.pl YES
../../../src/backend/catalog/sql_feature_packages.txt
../../../src/backend/catalog/sql_features.txt features-supported.sgml
/usr/bin/perl ./mk_feature_tables.pl NO
../../../src/backend/catalog/sql_feature_packages.txt
../../../src/backend/catalog/sql_features.txt features-unsupported.sgml
onsgmls postgres.sgml | sgmlspl
/usr/share/sgml/docbook/utils-0.6.11/helpers/docbook2man-spec.pl
--lowercase --section l --date `date '+%Y-%m-%d'`
Unknown SDATA: [pi] at
/usr/share/sgml/docbook/utils-0.6.11/helpers/docbook2man-spec.pl line
1219, STDIN line 100883.
make[1]: *** [man] Error 25
make[1]: Leaving directory `/home/andrew/foo/pgsql/doc/src/sgml'
make: *** [man.tar] Error 2
[EMAIL PROTECTED] src]$
If I don't define D2MDIR it tells me it can't find the script (should it
be looked for in configure, or supplied?).
cheers
andrew
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Release changes
Bruce Momjian [EMAIL PROTECTED] writes: On a philosophical note, I usually don't add core folks to release items _with_ other folks because we want to encourage non-core contributors, and because there is already the assumption that core is involved in many patches. I agree with this. For the current release, I'd be happy if my name were on a couple of the larger tasks that I did (such as hash aggregation). Seeing it plastered throughout the rev history is mildly embarrassing, actually. I added all the 'Tom' attributions you mentioned below. Please take 'em out again. I'm happy to give Joe the credit on all these items. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Windows on SuSE? 7.4
Yes, I actually have a libwsock32 because my system has wine on it. Wine is a windows emulator. So the assumption that any system with that file is a windows system will break on systems with windows emulators. It sounds like Joe's guess on this was right. --elein On Mon, Aug 11, 2003 at 01:29:19PM -0400, Tom Lane wrote: elein [EMAIL PROTECTED] writes: configure knows it is a linux box. Should it be trying to link to libwsock32.so or not? If this is a legitimate link, then the problem is different than if it is trying to link it in erroneously. configure is unconditionally including libwsock32 if it can find one. AFAICT from the CVS logs, this was only expected to happen on win32 (Bruce, that was your commit, configure.in v1.250; please confirm). So it would probably make sense to not look for libwsock32 unless PORTNAME is win32. I take it you actually have a libwsock32? What's it supposed to do? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] consistency check on SPI tuple count failed
I forgot to say to do a: select bar() at the end! Gaetano ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Proper Unicode support
On Mon, 11 Aug 2003, Peter Eisentraut wrote: Alexey Mahotkin writes: AFAIK, currently the codepoints are sorted in their numerical order. I've searched the source code and could not find the actual place where this is done. I've seen executor/nodeSort.c and utils/tuplesort.c. AFAIU, they are generic sorting routines. PostgreSQL uses the operating system's locale routines for this. So the sort order depends on choosing a locale that can deal with Unicode. sort order works, but upper/lower are broken. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] 7.4 COPY BINARY Format Change
I've just sent off patches to pgsql-patches to: 1. Slight clarification to the COPY BINARY format docs 2. A contrib/binarycopy module which wraps-up the detail of creating a file which can be used as input to COPY BINARY. User can create either 7.1 or 7.4 format files using the same API, without needing to know the file format, without needing to know the individual binary format of each field and without needing to explicitly byte-swap. #2 will be used extensively within Concept Systems code which interfaces to PostgreSQL. It really simplifies the creation of the binary files. Thanks, Lee. Lee Kindness writes: Tom Lane writes: Lee Kindness [EMAIL PROTECTED] writes: Well in that case the docs need attention. They describe the envelope surrounding the tuples, but no mention is made of the format they are in. It is reasonable to assume that this format was the native binary format, as in earlier releases. Yeah, there should be some mention of that in the COPY ref page I guess --- it's mentioned in the frontend protocol chapter, but not under COPY. In my defense I'd point out that the contents of individual fields have never been documented under COPY. True, the docs have always skipped the specifics for the tuples. But now that the format has evolved beyond a simple dump of the bytes the tuple format does need discussing. What do I need to do to make this code work with 7.4? Is there any docs describing the binary format for each of the datatypes or do I need to reverse-engineer a dump file or look in the source? ATM, I'd recommend looking in the sources to see what the datatype send/receive routines do. I have been thinking about documenting the binary formats during beta, but am unsure where to put the info. We never documented the internal formats before either, so there's no obvious place. Perhaps the documentation of the binary format should be taken out of the COPY docs and moved into the client interfaces documentation? the COPY docs would of course reference the new location. Just now the tuples could be documented simply by referring the reader to the relevant functions in the relevant source files. After all the source is the best documentation for this sort of thing. Are the routines in libpq/pqformat.c intended to be used by client applications to read/write the binary COPY files? They are not designed to be used outside the backend environment, although possibly some enterprising person could adapt them. I am not sure there's any value in it though. Copying the backend code helps only if what you want to get out of the transmission is the same as the backend's internal format, which for anything more complex than int/float/text seems a bit dubious. I think there is a lot of use for a binary COPY file API within libpq - routines to open a file, write/read a header and write/read common datatypes. This would remove the need for most people using the binary version of COPY to even know the file format. This would also isolate people who use this API from any future changes. Would libpq or contrib be the best place for this? Would you agree this is a good idea for 7.4? I've already got something along these lines: extern FILE *lofsdb_Bulk_Open(char **filename); extern void lofsdb_Bulk_Close(FILE *f, char *filename); extern void lofsdb_Bulk_Write_NCols(FILE *f, short ncols); extern void lofsdb_Bulk_Write(FILE *f, void *data, size_t sz, size_t count, short ind); extern void lofsdb_Bulk_WriteText(FILE *f, char *data, short ind); extern void lofsdb_Bulk_WriteBytea(FILE *f, char *data, size_t len, short ind); extern void lofsdb_Bulk_WriteTime(FILE *f, double t, short ind); extern void lofsdb_Bulk_WriteTimeNow(FILE *f); which could form the basis of a contrib module to handle writing out 7.1 through to 7.4 format files. Naturally lofsdb_Bulk_Write needs to go and be replaced by specific functions. Well as pointed out in my earlier message nothing has changed which requires the format to change - there is no real reason it's now PGCOPY and the integer layout field has disappeared. Given that the interpretation of the field contents has changed drastically, I thought it better to make an obvious incompatible change. We could perhaps have kept the skeleton the same, but to what end? An app trying to read or write the file as if it were pre-7.4 data would fail miserably anyway. Yeah, but someone (actually you!) went to the effort of making the 7.1 format extensible and documenting it as such... It could have handled the changes. I am still willing to make a patch which does this (to aid those writing COPY format files) and to fully support the reading of the old format tuples. However i'm not going to waste both our
Re: [HACKERS] Building beta packaging fails ...
'k, removed and trying build again ... On Tue, 5 Aug 2003, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: GNUMakefile.in: opt_files := \ src/tools src/corba src/data src/tutorial \ Ah. I take it then, that src/data shoudl be removed from there too? Yep. Sorry I missed it. regards, tom lane Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy Systems Administrator @ hub.org primary: [EMAIL PROTECTED] secondary: [EMAIL PROTECTED]|postgresql}.org ---(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] Another day, another SCO Compiler Error...
On Sun, 10 Aug 2003, Andrew Dunstan wrote: Time to install gcc? I believe it's doable for UW. Yeah, SCO even supplies same. I do get fast turn around, I know the compiler guys. They are already on the case, and fixing these 2. I haven't touched UW in about 10 years, but I see not much has changed (I regularly blew up the compiler back then). hehe. andrew Larry Rosenman wrote: I managed to blow the SCO compiler up again with /contrib/cube. the only workaround (from SCO already) is to disable -O on that module. Fair warning. LER ---(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 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] TODO: trigger features
Andreas Pflug [EMAIL PROTECTED] writes: Consider this: Table with one column that is maintained by a trigger for this rule: - Only one row in a group of rows may have a foo-value of true, all others must be false. - If foo=true is inserted/updated, other members of that data group must be set to false. - If foo=false, designate one row for foo=true - If not touched, use true if first member of that group, or false Why would the not touched case need to change anything? Now we have another column: ts timestamp, that should contain the timestamp when the row was inserted/updated the last time by the *user*, not the trigger which is considered to work in the background. On INSERT, a DEFAULT current_timestamp will be the selected option, on UPDATE you would use NEW.TS := current_timestamp. But how to update the row, and retain the old timestamp value? Normally, a user's query wouldn't touch the ts column at all, leaving it to the backend to insert the correct values. But in the maintain foo trigger case, we could use SET ts=ts to signal to the trigger that we explicitely want to set the value. That's not an argument for SET ts=ts. There are many possible kluges for detecting whether an update came from a trigger or directly from the user, and using ts=ts is only one (not a very appealing one either IMHO). The most obvious alternative is to have an additional boolean column from_trigger defaulting to FALSE. The trigger that sets the timestamp can do this: if new.from_trigger then new.from_trigger = false; else new.timestamp = now(); Then, the stored value of from_trigger is always false, and any update will cause the timestamp column to get updated --- unless the update explicitly sets from_trigger=true. This would also provide a solution for your other concern about being able to override the timestamp on insert. Same applies for the import case, when we want to insert a ts value coming from elsewhere but not from the trigger. This could also be done if there was something like UPDATE ... WITH OPTION NOTRIGGER(trg_update_timestamp) or so. Yet another messy kluge :-(. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] 7.4 Beta1: variable not found in subplan target lists
Hey again,
Received this error:
Aug 6 16:24:55 thunder postgres[7835]: [11-1] ERROR: variable not
found in subplan target lists
during this query:
$dbh-do( qq/
DELETE FROM temp_obs_v
WHERE file_id IN
(SELECT file_id
FROM temp_obs_v NATURAL JOIN files
WHERE group_id = $group_id
AND pair_id = $pair_id) /);
on the 15th iteration (the previous 14 worked fine).
It is repeatable. Something stupid again (other than the duplicate
indexes)?
tassiv=# explain DELETE FROM temp_obs_i
tassiv-# WHERE file_id IN
tassiv-#(SELECT file_id
tassiv(# FROM temp_obs_i NATURAL JOIN files
tassiv(# WHERE group_id = 3
tassiv(#AND pair_id = 25)
tassiv-# ;
ERROR: variable not found in subplan target lists
tassiv=# \d temp_obs_i
Table public.temp_obs_i
Column | Type | Modifiers
-+-+
x | real| not null
y | real| not null
imag| real| not null
smag| real| not null
loc | spoint | not null
obs_id | integer | not null default nextval('obs_id_seq'::text)
file_id | integer | not null
use | boolean | default false
solve | boolean | default false
star_id | integer |
mag | real|
Indexes:
temp_obs_i_file_id_index btree (file_id)
temp_obs_i_index gist (loc)
temp_obs_i_loc_index gist (loc)
temp_obs_i_obs_id_index btree (obs_id)
Foreign-key constraints:
temp_obs_i_files_constraint FOREIGN KEY (file_id) REFERENCES
files(file_id) ON DELETE CASCADE Inherits: obs_root
tassiv=# \d files
Table public.files
Column | Type | Modifiers
--+--+-
-- file_id | integer | not null
default nextval('files_file_id_seq'::text) group_id | integer
| pair_id | integer |
date | timestamp with time zone | not null
name | character varying| not null
ra_min | real | default 0
ra_max | real | default 0
dec_min | real | default 0
dec_max | real | default 0
Indexes:
files_pkey primary key, btree (file_id)
files_name_key unique, btree (name)
files_id_index btree (file_id, group_id, pair_id)
files_range_index btree (ra_min, ra_max, dec_min, dec_max)
Foreign-key constraints:
$1 FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE
CASCADE$2 FOREIGN KEY (pair_id) REFERENCES pairs(pair_id) ON
DELETE CASCADE
--
19:43:52 up 5 days, 12:30, 3 users, load average: 2.11, 2.07, 2.01
pgp0.pgp
Description: PGP signature
Re: [HACKERS] boolean defaults
Doh - forget I even asked that! Setting default to something involving current_time easily gives a variable default... Chris - Original Message - From: Christopher Kings-Lynne [EMAIL PROTECTED] To: Hackers [EMAIL PROTECTED] Sent: Tuesday, August 05, 2003 9:46 AM Subject: [HACKERS] boolean defaults Hi, Is it possible to get a default value on a boolean column to be anything other than 't', 'f', true or false? eg. If I go 'default 3 1', that's resolved to default true. I just need to know for phpPgAdmin... 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 ---(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] poorly written builtin functions
Neil Conway [EMAIL PROTECTED] writes: Since the culprit functions all seem related (I believe they're all generated by src/backend/utils/mb/conversion_procs), I'd imagine this should be pretty easy to fix. They should all be marked STRICT. Not sure how this got missed before; thanks for catching it. regards, tom lane ---(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] consistency check on SPI tuple count failed
On Fri, 2003-08-08 at 11:55, Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] writes: the following code was working properly under Postgres 7.3.X I'm now running my regression test with Postgres 7.4beta1 and I'm having the error in subj. I tried this and got regression=# select bar(); bar - 0 (1 row) regression=# Anyone else see the problem? Bar gives 0 for me as well. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Correlation in cost_index()
Which suggests to me that line 3964 in ./src/backend/utils/adt/selfuncs.c isn't right for multi-column indexes, esp for indexes that are clustered. I don't know how to address this though... Tom, any hints? Yes, we knew that already. Oliver had suggested simply dropping the division by nKeys, thus pretending that the first-column correlation is close enough. That seems to me to be going too far in the other direction, But is it really? xbut clearly dividing by nKeys is far too pessimistic. I'd change this in a moment if someone could point me to a formula with any basis at all ... Got it, alright. I'd never paid attention to prior discussions as the planner had generally did the right thing (with a lowered random_page_cost ::grin::). In terms of statistics and setting indexCorrelation correctly, something like Spearman's rho calculation comes to mind, though I don't know how applicable that is to database theory. indexCorrelation is 1.0 for the 1st key in a multi-column index. The only thing different about a multi-column index and a single column index is the multi-column index takes up more space per key, resulting in fewer index entries per page and more pages being fetched than would be in a single column index, but the current cost_index() function takes increased number of page fetches into account when calculating cost. As things stand, however, if a multi-column key is used, the indexCorrelation is penalized by the size of the number of keys found in the multi-column index. As things stand the qual user_id = 42, on a CLUSTER'ed multi-column index (user_id,utc_date) has an indexCorrelation of 0.5, when in fact the correlation is 1.0. indexCorrelation == number of random page fetches, which could be next to free on a solid state drive, in this case, the page fetches aren't random, they're perfectly sequential. If it were 'user_id = 42 AND utc_date = NOW()', the correlation of a lookup of the user_id would still be 1.0 and the utc_date would be 1.0 because both values are looked up in the index key. A lookup of just the utc_date can never use the index and the planner correctly uses a sequential scan. Cost != Correlation. They're proportional, but not the same and indexCorrelation is the wrong place to handle cost as that's done by the Mackert and Lohman formula. Under what circumstances would it be correct to pessimize the use of indexCorrelation? An indexCorrelation of 0.0 doesn't mean that the index is useless either, just that we take the full hit of a completely random page read as opposed to some fraction of a random page cost. I tossed a different index on my test table to see how well things fare with a low correlation, and this was a bit disturbing: # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE rucc.html_bytes 800::BIGINT; INFO: cost_seqscan: run_cost: 21472.687500 startup_cost: 0.00 INFO: cost_index: run_cost: 112165.065458 startup_cost: 0.00 indexCorrelation: 0.183380 QUERY PLAN -- Seq Scan on report_user_cat_count rucc (cost=0.00..21472.69 rows=31893 width=64) (actual time=444.25..2489.27 rows=514 loops=1) Filter: (html_bytes 800::bigint) Total runtime: 2492.36 msec (3 rows) # SET enable_seqscan = false; SET # EXPLAIN ANALYZE SELECT * FROM report_user_cat_count AS rucc WHERE rucc.html_bytes 800::BIGINT; INFO: cost_seqscan: run_cost: 21472.687500 startup_cost: 1.00 INFO: cost_index: run_cost: 112165.065458 startup_cost: 0.00 indexCorrelation: 0.183380 QUERY PLAN - Index Scan using report_user_cat_count_html_bytes_idx on report_user_cat_count rucc (cost=0.00..112165.07 rows=31893 width=64) (actual time=68.64..85.75 rows=514 loops=1) Index Cond: (html_bytes 800::bigint) Total runtime: 97.75 msec (3 rows) *shrug* A low indexCorrelation overly pessimizes the cost of an index, but I'm not sure where to attribute this too. :-/ -sc -- Sean Chittenden ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Moved simple_prompt()/sprompt.c
I have moved simple_prompt()/sprompt.c into /port. It was used by psql, pg_dump, and scripts, and had already gotten out of sync with a recent Win32 password patch. If a file is needed by three non-backend directories, /port seems to be the proper place for it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/Makefile.global.in === RCS file: /cvsroot/pgsql-server/src/Makefile.global.in,v retrieving revision 1.165 diff -c -c -r1.165 Makefile.global.in *** src/Makefile.global.in 8 Aug 2003 02:48:24 - 1.165 --- src/Makefile.global.in 8 Aug 2003 04:38:40 - *** *** 341,347 # # substitute implementations of the C library ! LIBOBJS = @LIBOBJS@ path.o thread.o ifneq (,$(LIBOBJS)) LIBS += -lpgport --- 341,347 # # substitute implementations of the C library ! LIBOBJS = @LIBOBJS@ path.o sprompt.o thread.o ifneq (,$(LIBOBJS)) LIBS += -lpgport Index: src/bin/pg_dump/Makefile === RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/Makefile,v retrieving revision 1.39 diff -c -c -r1.39 Makefile *** src/bin/pg_dump/Makefile12 Dec 2002 21:02:00 - 1.39 --- src/bin/pg_dump/Makefile8 Aug 2003 04:38:41 - *** *** 13,21 top_builddir = ../../.. include $(top_builddir)/src/Makefile.global ! OBJS= pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o \ ! pg_backup_files.o pg_backup_null.o pg_backup_tar.o \ ! sprompt.o dumputils.o EXTRA_OBJS = $(top_builddir)/src/backend/parser/keywords.o --- 13,21 top_builddir = ../../.. include $(top_builddir)/src/Makefile.global ! OBJS= pg_backup_archiver.o pg_backup_db.o pg_backup_custom.o \ ! pg_backup_files.o pg_backup_null.o pg_backup_tar.o \ ! dumputils.o EXTRA_OBJS = $(top_builddir)/src/backend/parser/keywords.o *** *** 31,37 $(CC) $(CFLAGS) pg_restore.o $(OBJS) $(EXTRA_OBJS) $(libpq) $(LDFLAGS) $(LIBS) -o $@ pg_dumpall: pg_dumpall.o $(libpq_builddir)/libpq.a ! $(CC) $(CFLAGS) pg_dumpall.o dumputils.o sprompt.o $(EXTRA_OBJS) $(libpq) $(LDFLAGS) $(LIBS) -o $@ .PHONY: submake-backend submake-backend: --- 31,37 $(CC) $(CFLAGS) pg_restore.o $(OBJS) $(EXTRA_OBJS) $(libpq) $(LDFLAGS) $(LIBS) -o $@ pg_dumpall: pg_dumpall.o $(libpq_builddir)/libpq.a ! $(CC) $(CFLAGS) pg_dumpall.o dumputils.o $(EXTRA_OBJS) $(libpq) $(LDFLAGS) $(LIBS) -o $@ .PHONY: submake-backend submake-backend: Index: src/bin/pg_dump/dumputils.h === RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/dumputils.h,v retrieving revision 1.7 diff -c -c -r1.7 dumputils.h *** src/bin/pg_dump/dumputils.h 4 Aug 2003 02:40:09 - 1.7 --- src/bin/pg_dump/dumputils.h 8 Aug 2003 04:38:41 - *** *** 18,25 #include pqexpbuffer.h - extern char *simple_prompt(const char *prompt, int maxlen, bool echo); - extern const char *fmtId(const char *identifier); extern void appendStringLiteral(PQExpBuffer buf, const char *str, bool escapeAll); --- 18,23 Index: src/bin/pg_dump/pg_dump.h === RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.h,v retrieving revision 1.103 diff -c -c -r1.103 pg_dump.h *** src/bin/pg_dump/pg_dump.h 4 Aug 2003 02:40:09 - 1.103 --- src/bin/pg_dump/pg_dump.h 8 Aug 2003 04:38:41 - *** *** 235,241 const bool schemaOnly, const bool dataOnly); extern void dumpIndexes(Archive *fout, TableInfo *tbinfo, int numTables); - /* sprompt.h */ - extern char *simple_prompt(const char *prompt, int maxlen, bool echo); - #endif /* PG_DUMP_H */ --- 235,238 Index: src/bin/psql/Makefile === RCS file: /cvsroot/pgsql-server/src/bin/psql/Makefile,v retrieving revision 1.36 diff -c -c -r1.36 Makefile *** src/bin/psql/Makefile 6 Jun 2003 22:17:42 - 1.36 --- src/bin/psql/Makefile 8 Aug 2003 04:38:42 - *** *** 17,25 override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) -DFRONTEND ! OBJS=command.o common.o help.o input.o stringutils.o mainloop.o copy.o \ startup.o prompt.o variables.o large_obj.o print.o describe.o \ ! sprompt.o tab-complete.o mbprint.o all: submake-libpq submake-libpgport psql --- 17,25 override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) -DFRONTEND ! OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \
Re: [HACKERS] new psql \d command
Bruce Momjian [EMAIL PROTECTED] writes: It just seemed complex to figure out which operators needed parens and which didn't. The fact that the first attempt was wrong doesn't improve my faith in that code one bit ;-). I don't want pg_dump invoking it, even as an option. Someone will get burnt. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] test
gnore ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4Beta1 hang?
What is the index temp_obs_i_obs_id_index on, exactly? Is it a serial
column or some such?
Yup:
Okay, that explains it then. In a serial column's index, essentially
all splits will be on the rightmost page of the tree. This means that
when bt_split tries to get a new free page, it will almost always be
holding lock on the most recently acquired free page (since that was
the righthand side of the previous split). That's the factor that makes
the coincidence likely. A vacuum running concurrently with a page split
may mistakenly place a just-used page back into FSM (if the page gets
used between the time vacuum examines it and the time vacuum finishes
and loads its results into FSM). So if that happens, and said page is
the first to be returned by FSM for the next split, you lose.
I've committed the attached fix.
regards, tom lane
*** src/backend/access/nbtree/nbtpage.c.origFri Aug 8 17:47:01 2003
--- src/backend/access/nbtree/nbtpage.c Sun Aug 10 15:32:16 2003
***
*** 409,414
--- 409,430
* that the page is still free. (For example, an already-free
* page could have been re-used between the time the last VACUUM
* scanned it and the time the VACUUM made its FSM updates.)
+*
+* In fact, it's worse than that: we can't even assume that it's
+* safe to take a lock on the reported page. If somebody else
+* has a lock on it, or even worse our own caller does, we could
+* deadlock. (The own-caller scenario is actually not improbable.
+* Consider an index on a serial or timestamp column. Nearly all
+* splits will be at the rightmost page, so it's entirely likely
+* that _bt_split will call us while holding a lock on the page most
+* recently acquired from FSM. A VACUUM running concurrently with
+* the previous split could well have placed that page back in FSM.)
+*
+* To get around that, we ask for only a conditional lock on the
+* reported page. If we fail, then someone else is using the page,
+* and we may reasonably assume it's not free. (If we happen to be
+* wrong, the worst consequence is the page will be lost to use till
+* the next VACUUM, which is no big problem.)
*/
for (;;)
{
***
*** 416,431
if (blkno == InvalidBlockNumber)
break;
buf = ReadBuffer(rel, blkno);
! LockBuffer(buf, access);
! page = BufferGetPage(buf);
! if (_bt_page_recyclable(page))
{
! /* Okay to use page. Re-initialize and return it */
! _bt_pageinit(page, BufferGetPageSize(buf));
! return buf;
}
- elog(DEBUG2, FSM returned nonrecyclable page);
- _bt_relbuf(rel, buf);
}
/*
--- 432,455
if (blkno == InvalidBlockNumber)
break;
buf = ReadBuffer(rel, blkno);
! if (ConditionalLockBuffer(buf))
{
! page = BufferGetPage(buf);
! if (_bt_page_recyclable(page))
! {
! /* Okay to use page. Re-initialize and return
it */
! _bt_pageinit(page, BufferGetPageSize(buf));
! return buf;
! }
! elog(DEBUG2, FSM returned nonrecyclable page);
! _bt_relbuf(rel, buf);
! }
! else
! {
! elog(DEBUG2, FSM returned nonlockable page);
! /* couldn't get lock, so just drop pin */
! ReleaseBuffer(buf);
}
}
/*
*** src/backend/storage/buffer/bufmgr.c.origSun Aug 3 23:00:55 2003
--- src/backend/storage/buffer/bufmgr.c Sun Aug 10 15:17:28 2003
***
*** 1937,1942
--- 1937,1973
}
/*
+ * Acquire the cntx_lock for the buffer, but only if we don't have to wait.
+ *
+ * This assumes the caller wants BUFFER_LOCK_EXCLUSIVE mode.
+ */
+ bool
+ ConditionalLockBuffer(Buffer buffer)
+ {
+ BufferDesc *buf;
+
+ Assert(BufferIsValid(buffer));
+ if (BufferIsLocal(buffer))
+ return true;
Re: [HACKERS] logging stuff
Another interesting idea, if we do a single log variable with parameters separated by commas, is to allow some syntax where you could specify the delimiter between fields, so it could be: log_line: |, dbname, username or log_line:, dbname, username --- Josh Berkus wrote: Tom, I'm prepared to be guided by concensus, though. I'm not dead set on it either, just wanted to raise a flag. Who else has an opinion? From my perspective, we could really use a delimiter between the fields of log output which is unlikely to appear within those fields instead of parsing by character count, rather than making dbname a special case. Or do we already do this and I'm not parsing my log right? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] this is in plain text (row level locks)
From: Tom Lane [EMAIL PROTECTED] To: Jenny - [EMAIL PROTECTED] Subject: Re: [HACKERS] this is in plain text (row level locks) Date: Sat, 02 Aug 2003 23:28:30 -0400 if row-level locks are not recorded in proclock or any other shared memory datastructuers, then why does lockmode (array or ints) of proclock indicate that an AccessShareLock is acquired when a row is locked by application.? That's a table lock --- it's independent of row locks. It's there mostly to ensure someone doesn't delete the whole table out from under you. regards, tom lane so even though the application locks a row in a table, table-level locks are automatically taken by postgesql ? why is that? thanks _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] session level locks
hi, does anyone know what session level locks mean in postgresql..i've heard of table-level locks and row level locks but not session level thanks Jenny _ The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] TODO items
Bruce Momjian wrote: I am marking the completed TODO items. Are these done? Can we mark this one complete? * Allow easy display of usernames in a group regression=# SELECT g.grosysid, g.groname, s.usesysid, s.usename FROM pg_shadow s, pg_group g WHERE s.usesysid = any (g.grolist); grosysid | groname | usesysid | usename --+-+--+- 100 | g1 | 100 | user1 101 | g2 | 100 | user1 100 | g1 | 101 | user2 101 | g2 | 101 | user2 101 | g2 | 102 | user3 (5 rows) This one isn't done: * -Delay resolution of array expression type so assignment coercion can be performed on empty array expressions (Joe) This one I don't understand: o Support construction of array result values in expressions I thought Peter did something with this one: * Allow LIKE indexing optimization for non-ASCII locales Joe ---(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] consistency check on SPI tuple count failed
Stephan Szabo [EMAIL PROTECTED] writes: I got the same thing as Gaetano on my just prior to beta1 system. Well, we couldn't have fixed it since beta1 --- there's been no changes anywhere near SPI. I'm thinking it must be platform-dependent. What are you guys using, exactly? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] When did we get to be so fast?
On 7 Aug 2003 at 19:54, Bruce Momjian wrote: Man, I can't do anything right; should be: one INSERT per transaction, fsync true 934 one INSERT per transaction, fsync false 1818 INSERTs all in one transaction, fsync true 4166 Just curiousity, what will all inserts in one transaction with fsync false would yield? Bye Shridhar -- philosophy: The ability to bear with calmness the misfortunes of our friends. ---(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] dropping a user causes pain (#2)
The docs (new and old) explicitly state you can do this; see for example http://www.postgresql.org/docs/7.3/static/sql-dropuser.html But ISTM that in such a case the user's objects should possibly be reassigned to the database owner (who can't be dropped), in kinda the same way that a *nix process that is orphaned is reparented to init. I guess that might break other things, or would it? Or maybe we need 'drop user foo with cascade'. Or both. cheers andrew Christopher Kings-Lynne wrote: Hi, I dropped the owner of a table (with no complaints), and now I get this: psql: asdf=# \dt List of relations Schema | Name | Type | Owner +--+---+ public | a1 | table | pg_dump: pg_dump: WARNING: owner of data type a1 appears to be invalid pg_dump: WARNING: owner of table a1 appears to be invalid Didn't there used to be a check that occurred, preventing you from dropping a user who owned objects? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] pgstats_initstats() cost
On Tue, 12 Aug 2003, Tom Lane wrote:
Gavin Sherry [EMAIL PROTECTED] writes:
but pgstat_initstats() caught my eye. This gets called about 6 times per
insert (I did 10 inserts) and the major cost appears to relate to the
linear pgStatTabstatMessages. The comparative performance of
hash_search() suggests that pgStatTabstatMessages may benefit from use of
a hash. However, it seems unreasonable that we're doing work at all in
pgstat_initstats() if the user is not interested in query/block/tuple
stats.
The coding in the search loop could perhaps be tightened a little, but
I'd think the last point should be addressed by dropping out via the
no_stats exit if stats aren't being gathered.
I doubt a hash is worth maintaining, because the active tabstat entries
should only be for tables that are being touched in the current command
(thus, there are not more than six in your example). I'm not sure why
it takes so much time to look through six entries though ...
Neither. I might look into it further later, but here's a patch to exit
out of pgstat_initstats() if we're not collecting stats (attached).
Thanks,
Gavin
Index: src/backend/postmaster/pgstat.c
===
RCS file: /usr/local/cvsroot/pgsql-server/src/backend/postmaster/pgstat.c,v
retrieving revision 1.42
diff -2 -c -r1.42 pgstat.c
*** src/backend/postmaster/pgstat.c 4 Aug 2003 00:43:21 - 1.42
--- src/backend/postmaster/pgstat.c 11 Aug 2003 02:14:53 -
***
*** 829,832
--- 829,838
stats-index_scan_counted = FALSE;
+ if (!pgstat_collect_querystring !pgstat_collect_tuplelevel
+ !pgstat_collect_blocklevel) {
+ stats-no_stats = TRUE;
+ return;
+ }
+
if (pgStatSock 0)
{
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 beta1 plpgsql regression
I think I have a catalog corruption (self inflicted I'm sure). A dump / reload of the database corrects the issue here as well. On Thu, 2003-08-14 at 08:33, Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: The below function works in 7.3 (returns nothing, but no errors). rbttest=3D# select * from service.test(1); ERROR: cache lookup failed for type 0 CONTEXT: compile of PL/pgSQL function test near line 2 Hmm, works fine here ... regression=# create schema service; CREATE SCHEMA regression=# create table service.service (f1 int, f2 text); CREATE TABLE regression=# create or replace function service.test(integer) regression-# returns setof service.service regression-# as ' regression'# declare regression'# v_service service.service%rowtype; regression'# begin regression'# return; regression'# end; regression'# ' language plpgsql; CREATE FUNCTION regression=# select * from service.test(1); f1 | f2 + (0 rows) regression=# regards, tom lane signature.asc Description: This is a digitally signed message part
Re: [HACKERS] logging stuff
Andrew Dunstan [EMAIL PROTECTED] writes: The dbname patch is now done. If nobody objects to the format ([db:yourdbname]) I'll submit it - I did it that way to make it fairly easy to split a log file based on it, although you would have to be careful with multiline log entries such as query strings. I'd tend to just put the dbname in a known column, and not bother with the decoration --- decoration adds up fast when it's on every log line, and I don't think it helps make the log more parseable. Compare what we do with timestamps and pids. regards, tom lane ---(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 on unixware 713
Nope, but Tom is actively on my box :-) LER --On Thursday, August 07, 2003 19:35:05 +0200 [EMAIL PROTECTED] wrote: Did you find a work around? On Thu, 7 Aug 2003, Larry Rosenman wrote: Date: Thu, 07 Aug 2003 12:28:52 -0500 From: Larry Rosenman [EMAIL PROTECTED] To: [EMAIL PROTECTED], pgsql-hackers list [EMAIL PROTECTED] Subject: Re: [HACKERS] build on unixware 713 forwarded to the compiler guys at SCO. LER --On Thursday, August 07, 2003 19:10:04 +0200 [EMAIL PROTECTED] wrote: Hi Tom, Hi Larry After updating from cvs I'm going a little further. But still have problems: UX:acomp: ERROR: fe-protocol3.c, line 1402: internal compiler error: can't deal with op BMOVE gmake[3]: *** [fe-protocol3.o] Error 1 gmake[2]: *** [all] Error 2 gmake[1]: *** [all] Error 2 gmake: *** [all] Error 2 UX:make: ERROR: fatal error. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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] WITH HOLD and pooled connections
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Seems we have a problem with pooled connections and WITH HOLD cursors. We have code to reset transaction state and variables via RESET ALL, but how do we remove WITH HOLD cursors when we pass a connection to a new client? Prepared statements would be just as much of a problem. I think the correct answer is simply don't use those features in a pooled environment. Yuck. I can't think of any other option. The pooled connections are all the same user, so there isn't any permission issue here. Well, one answer for cursors would be to offer a CLOSE ALL sort of command. I'm not sure it's worth the trouble though. I can't really visualize a reason to use held cursors in a pooled environment. A held cursor is pointless unless you intend to use it for more than one transaction, and in a pooled environment that would be difficult, no? When you commit one transaction and start another, you might not have the same connection anymore. I am thinking more of someone using WITH HOLD cursors and getting disconnected before being able to close it. Rod's right that sharing prepared statements among all users of a pooled connection might be interesting. However, I wonder whether anyone would actually use a list prepared statements feature to implement it. Seems like checking that way would just be a wasted roundtrip for most transactions. I'd be inclined to set up the app so that all the required statements are prepared the moment it opens a new connection, and the pool users just assume the statements are available. What would be interesting is a CREATE OR REPLACE functionality for prepared cursors, where you could ask for it to be prepared, but if it already existed, it would do nothing, or something like that. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] DROP TYPE/DROP DOMAIN
Tom Lane wrote: Christopher Kings-Lynne [EMAIL PROTECTED] writes: But should the CREATE DOMAIN manual page refer to DROP TYPE? Should DROP DOMAIN be able to drop a type? shrug Don't care much about either of those; the current state of affairs is fine with me. What happens in the future if for some reason we need to add some special case to dropDomain() and the coder neglects to add it to dropType()? That would be a bug without regard for any of this discussion, because both RemoveDomain and RemoveType are simply user interface routines; they do no actual work. If someone put actual work into either, it'd be wrong because it would not get done during a cascaded drop. While implementing the new ALTER DOMAIN ... OWNER TO stuff, I found that there's no corresponding command for TYPE (and ALTER DOMAIN will reject a TYPE). IMHO this should go on TODO for symmetry reasons. And how about AGGREGATE, CONVERSION, SEQUENCE? (the latter can be changed by ALTER TABLE). Regards, Andreas ---(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] contrib/vacuumlo problem in cvs
Do a CVS update and you should be fine. I modified that about 20 minutes ago. --- Bruno Wolff III wrote: I get the error message below when trying to make inside of the contrib subdirectory. It was happening last night as well. I removed all of the source and got a clean copy about an hour ago and am still having the problem. gcc -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../../src/interfaces/libpq -I. -I../../src/include -c vacuumlo.c -o vacuumlo.o vacuumlo.c:47: conflicting types for `simple_prompt' ../../src/include/port.h:26: previous declaration of `simple_prompt' make[1]: *** [vacuumlo.o] Error 1 make[1]: Leaving directory `/usr/local/src/postgresql-7.4/pgsql/contrib/vacuumlo' make: *** [all] Error 2 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] no of affected rows in prepared stmts
hi whenever i call an execute on a prepared statement, i get the return value of PQcmdTuples() as NULL even if the query did modify tuples... how can i get the number of affected tuples? thanx in adv. rahul ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] TODO items
Bruce Momjian [EMAIL PROTECTED] writes: * Use index to restrict rows returned by multi-key index when used with non-consecutive keys or OR clauses, so fewer heap accesses Not sure what this means. This is a Vadim idea. The idea was that if you had a multi-key index on col1,col2,col3, and you wanted to do a lookup on col1,col3, you could still use the index, and just run through all the matching col1 values looking for a matching col3 in the index, rather than going to the heap and looking for a col3 match? Is this item worth keeping? Hmm. Maybe. Might as well leave it there awhile longer. regards, tom lane ---(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] TODO items
Josh Berkus wrote: Bruce, o Allow array declarations and other data types in PL/PgSQL DECLARE o Allow PL/PgSQL to support array element assignment AFAIK, these two are not done, but they are redundant. Either one requires the implementation of the other. OK. o Add PL/PgSQL PROCEDURES that can return multiple values Hmmm ... I know how this got on the TODO, but it's a fragment of a larger suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much sense on its own without the other elements; maybe we should take it off until I can make a full proposal? Removed. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] problem with RH7.3 Pg7.3.4 binaries
On Tuesday 05 August 2003 08:14, Andrew Dunstan wrote: Will check later today. When you do, let me know, so that I can post them. -- Lamar Owen Director of Information Technology Pisgah Astronomical Research Institute ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Remove spaces in pg_dump
Seems pg_dump dumps funcions with a space between the function name and
opening paren. It looks strange:
SELECT pg_catalog.setval ('test_x_seq', 1, false);
This patch removes the space:
SELECT pg_catalog.setval('test_x_seq', 1, false);
I didn't even realize the space worked, but it does:
test= select version ();
version
--
PostgreSQL 7.4beta1 on i386-pc-bsdi4.3.1, compiled by GCC 2.95.3
(1 row)
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
Index: src/bin/pg_dump/pg_dump.c
===
RCS file: /cvsroot/pgsql-server/src/bin/pg_dump/pg_dump.c,v
retrieving revision 1.342
diff -c -c -r1.342 pg_dump.c
*** src/bin/pg_dump/pg_dump.c 4 Aug 2003 02:40:09 - 1.342
--- src/bin/pg_dump/pg_dump.c 8 Aug 2003 01:11:38 -
***
*** 3698,3706
initPQExpBuffer(fn);
if (honor_quotes)
! appendPQExpBuffer(fn, %s (, fmtId(finfo-proname));
else
! appendPQExpBuffer(fn, %s (, finfo-proname);
for (j = 0; j finfo-nargs; j++)
{
char *typname;
--- 3698,3706
initPQExpBuffer(fn);
if (honor_quotes)
! appendPQExpBuffer(fn, %s(, fmtId(finfo-proname));
else
! appendPQExpBuffer(fn, %s(, finfo-proname);
for (j = 0; j finfo-nargs; j++)
{
char *typname;
***
*** 6007,6013
if (!schemaOnly)
{
resetPQExpBuffer(query);
! appendPQExpBuffer(query, SELECT pg_catalog.setval ();
appendStringLiteral(query, fmtId(tbinfo-relname), true);
appendPQExpBuffer(query, , %s, %s);\n,
last, (called ? true : false));
--- 6007,6013
if (!schemaOnly)
{
resetPQExpBuffer(query);
! appendPQExpBuffer(query, SELECT pg_catalog.setval();
appendStringLiteral(query, fmtId(tbinfo-relname), true);
appendPQExpBuffer(query, , %s, %s);\n,
last, (called ? true : false));
***
*** 6384,6393
/* In 7.3, result of regproc is already quoted */
if (g_fout-remoteVersion = 70300)
! appendPQExpBuffer(query, EXECUTE PROCEDURE %s (,
tgfname);
else
! appendPQExpBuffer(query, EXECUTE PROCEDURE %s (,
fmtId(tgfname));
for (findx = 0; findx tgnargs; findx++)
{
--- 6384,6393
/* In 7.3, result of regproc is already quoted */
if (g_fout-remoteVersion = 70300)
! appendPQExpBuffer(query, EXECUTE PROCEDURE %s(,
tgfname);
else
! appendPQExpBuffer(query, EXECUTE PROCEDURE %s(,
fmtId(tgfname));
for (findx = 0; findx tgnargs; findx++)
{
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] test beta build
Christopher Kings-Lynne [EMAIL PROTECTED] writes: Cool. It's just down to this now: gcc -pipe -O -g -Wall -Wmissing-prototypes -Wmissing-declarations -Wno-error -I./../include -I. -I../../../../src/include -DMAJOR_VERSION=3 -DMINOR_VE RSION=0 -DPATCHLEVEL=0 -DINCLUDE_PATH=\/home/chriskl/local/include\ -c - o preproc.o preproc.c -MMD In file included from preproc.y:6278: pgc.c: In function `yylex': pgc.c:1387: warning: label `find_rule' defined but not used preproc.y: At top level: pgc.c:3367: warning: `yy_flex_realloc' defined but not used Right, everybody gets those. ps. Don't get the impression that I actually use the Alpha - it's just an old machine lying around that I use to keep pgsql honest :) Don't consider it a platform that you need to support because of me :P Not at all --- I think keeping the Alpha port alive is important, if only because there are not that many 64-bit platforms that PG gets tested on routinely. You always manage to find some portability bugs... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4beta1 build problem on unixware
On Thu, Aug 07, 2003 at 05:20:58PM +0200, [EMAIL PROTECTED] wrote: Hi Tom, I have NI_NUMERICHOST defined in netdb.h That's for getnameinfo(). getnameinfo() is older than getaddrinfo() ... If you have that, I assume you have AI_NUMERICHOST in the same file too ... ? Kurt ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] DROP TYPE/DROP DOMAIN
According to that logic, a view is a table, but we still require DROP VIEW to drop a view. No, a view is not a table. Try putting an index or trigger on it. It seems to me to be more correct that we make DROP TYPE not work on domains. I refer to the principle of least surprise... People EXPECT it to not work, therefore it shouldn't :) There exists a perfectly good other command (drop domain) that works, and you can't go alter type..add check(...) on a domain. Also, we don't want to encourage people to use commands that maybe we might remove in the future... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Problem building contrib/array in current CVS
Bruno Wolff III wrote: array_iterator.c:30: utils/fmgroids.h: No such file or directory make: *** [array_iterator.o] Error 1 I must have caught something in the middle of an update. I grabbed a fresh copy a little while ago and now it builds properly. I believe utils/fmgroids.h is a derived file. Were you building contrib without first building Postgres? Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] new psql \d command
Christopher Kings-Lynne wrote: I assume we don't want them always quoted. Problem with that is that someone has to move the to-quote-or-not function from pg_dump into psql... Attached is a psql/Makefile patch that makes fmtId() available to psql C code. I tried a test and it seemed to work. Keep in mind the return value of fmtId() has to be used before fmtId() is called again, so places that pass two parameters to printf will need to call fmtId(), save the result, then call it again and pass them both to printf. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 Index: src/bin/psql/Makefile === RCS file: /cvsroot/pgsql-server/src/bin/psql/Makefile,v retrieving revision 1.37 diff -c -c -r1.37 Makefile *** src/bin/psql/Makefile 8 Aug 2003 04:52:21 - 1.37 --- src/bin/psql/Makefile 8 Aug 2003 20:48:41 - *** *** 15,25 REFDOCDIR= $(top_srcdir)/doc/src/sgml/ref ! override CPPFLAGS := -I$(libpq_srcdir) $(CPPFLAGS) -DFRONTEND OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \ startup.o prompt.o variables.o large_obj.o print.o describe.o \ ! tab-complete.o mbprint.o all: submake-libpq submake-libpgport psql --- 15,26 REFDOCDIR= $(top_srcdir)/doc/src/sgml/ref ! override CPPFLAGS := -I$(top_srcdir)/src/bin/pg_dump -I$(libpq_srcdir) $(CPPFLAGS) -DFRONTEND OBJS= command.o common.o help.o input.o stringutils.o mainloop.o copy.o \ startup.o prompt.o variables.o large_obj.o print.o describe.o \ ! tab-complete.o mbprint.o \ ! dumputils.o $(top_builddir)/src/backend/parser/keywords.o all: submake-libpq submake-libpgport psql *** *** 27,32 --- 28,36 $(CC) $(CFLAGS) $(OBJS) $(libpq) $(LDFLAGS) $(LIBS) -o $@ help.o: $(srcdir)/sql_help.h + + dumputils.c: % : $(top_srcdir)/src/bin/pg_dump/% + rm -f $@ $(LN_S) $ . ifdef PERL $(srcdir)/sql_help.h: create_help.pl $(wildcard $(REFDOCDIR)/*.sgml) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] logging stuff
Tom, I'm prepared to be guided by concensus, though. I'm not dead set on it either, just wanted to raise a flag. Who else has an opinion? From my perspective, we could really use a delimiter between the fields of log output which is unlikely to appear within those fields instead of parsing by character count, rather than making dbname a special case. Or do we already do this and I'm not parsing my log right? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] TODO items
Josh Berkus wrote:
o Allow array declarations and other data types in PL/PgSQL DECLARE
o Allow PL/PgSQL to support array element assignment
AFAIK, these two are not done, but they are redundant. Either one requires
the implementation of the other.
They are done (at least the array declarations and array element
assignment part):
create or replace function test() returns integer[] as '
declare
v_ret integer[] := ''{}'';
begin
v_ret[1] := 1;
v_ret[2] := 2;
return v_ret;
end;
' language plpgsql;
CREATE FUNCTION
regression=# select test();
test
---
{1,2}
(1 row)
o Add PL/PgSQL PROCEDURES that can return multiple values
Hmmm ... I know how this got on the TODO, but it's a fragment of a larger
suggestion about PROCEDURES vs. FUNCTIONS. I don't think it makes much
sense on its own without the other elements; maybe we should take it off
until I can make a full proposal?
Is this somehow different from table functions (SRFs)?
o Add table function support to pltcl, plperl, plpython
If this was done, I would dearly love to know about it ...
Pretty much sure this has not been done. I'll be happy to work with
someone if they want to pick this up, but I don't use them enough to
feel comfortable doing it myself.
Joe
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Batch Operations
On Fri, Aug 08, 2003 at 03:32:05PM +0530, Rahul_Iyer wrote: im currently working on a project that requires batch operations - eg. Batch insert/update etc. The database im using is PostgreSQL. However, i cannot find any documentation for batch opeartions on PostgreSQL. Does anyone know how to do this, or if it is possible? If I understand you correctly, batch inserts are available via COPY. I'm not sure what you mean by batch updates -- can you elaborate? -Neil ---(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] changes to support logging
To support some of the logging changes I'm working on, I propose to add the following members to struct Port, unless anyone objects - these are all calculated in postmaster/postmaster.c: struct tm session_start; char * remote_host; char * remote_port; Question: In setting these is it better to call strdup() or pstrdup() - the code comments aren't entirely clear on that. Another thing: I've noticed an inconsistency in calls to gettimeofday(): sometimes it is passed a struct tz (which is ignored) as the second argument, and sometimes NULL as the second argument. My understanding is that the second argument is basically useless anyway - on my Linux box man gettimeofday says this: The use of the timezone struct is obsolete; the tz_dsttime field has never been used under Linux - it has not been and will not be supported by libc or glibc. I therefore propose to clean this up in the following places: backend/commands/vacuum.c backend/utils/adt/nabstime.c backend/postmaster/postmaster.c (3) (I know it's a minor nit, but what the heck?) cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] dropping a user causes pain (#2)
Tom Lane writes: The advantage here is that the sysid assigned to the user would remain present in pg_shadow and couldn't accidentally be assigned to a new user. This would prevent the problem of new users inheriting permissions and even object ownership from deleted users due to chance coincidence of sysid. But how does one actually get rid of the privileges? Btw., the problem is going to get worse if we get nested roles, roles with grant options, and possibly other parts of the enhanced privilege facilities. For example, if you remove a user from a role/group, you would need to search the entire database cluster for any privileges granted through that group that this user had used to create some kind of permanent state. I'm not sure if we want to cover all of these cases with various this link no longer exists flags, especially since later on the link could be reestablished. -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Oversight?
Rod Taylor [EMAIL PROTECTED] writes: rbt=3D# ALTER USER rbt SET CONSTRAINTS ALL DEFERRED; ERROR: syntax error at or near ALL at character 32 rbt=3D# ALTER USER rbt SET CONSTRAINTS =3D DEFERRED; ERROR: constraints is not a recognized option SET CONSTRAINTS ALL DEFERRED is a SQL-spec-mandated command syntax. Any similarity to Postgres' SET var = value syntax ends with the initial keyword. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: Parsing speed (was Re: [HACKERS] pgstats_initstats() cost)
On Tue, 12 Aug 2003, Tom Lane wrote: This leaves us with a bit of a problem, though, because there isn't any libpq API that allows access to this speedup. I put in a routine to support Parse/Bind/Execute so that people could use out-of-line parameters for safety reasons --- but there's no function to do Bind/Execute against a pre-existing prepared statement. (I had to make a hacked version of libpq to do the above testing.) I'm beginning to think that was a serious omission. I'm tempted to fix it, even though we're past feature freeze for 7.4. Comments? Can you give an example of this usage of this API? I am wondering whether releasing this specific feature would be eclipsed by a generalised bound variables solution in a future release... still, that's a nice speed up :-). Thanks, Gavin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Statement-level Triggers
Hello everybody, how far have you got with statement-level triggers development? Regards, Claudio. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Farewell
Retired? May be is better: Hall of fame. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.4Beta1 hang?
I appear to have a hang on my system (40 minutes so far, it's now
17:40). The code is from CVS updated 8/6 if I remember correctly.
The machine is idle, with a vacuum waiting and an INSERT doing nothing.
The vacuum is being generated via pg_autovacuum.
The output from the perl script leading up to the hang is:
2755/kir_27551925.fits.apm... 1775 stars imported 3421 per second
2755/kvr_27551925.fits.apm... 1219 stars imported 4639 per second
.Kept 925 (75.9%) pairs of stars at 249.9 per second ( 3.7)
2755/kir_27551989.fits.apm... 1727 stars imported 3564 per second
2755/kvr_27551989.fits.apm... 918 stars imported 3518 per second
.Kept 694 (75.6%) pairs of stars at 635.3 per second ( 1.1)
2755/kir_27552015.fits.apm... 1817 stars imported 4549 per second
2755/kvr_27552015.fits.apm... 958 stars imported 4197 per second
As you this shows, the insert that is hung is part of a series of
operaqtions which usually completes in under 4 seconds.
The insert is:
INSERT INTO obs_i
SELECT *
FROM temp_obs_i
WHERE obs_id IN (SELECT i_obs_id FROM obs_keep)
The PostgreSQL processes:
postgres 32242 1 0 Aug07 ?00:00:01
/usr/local/pgsql/bin/postmaster -D /var/lib/pgsql/data postgres 32246
32242 0 Aug07 ?00:02:38 postgres: stats buffer process
postgres 32247 32246 0 Aug07 ?00:02:30
postgres: stats collector process
postgres 6426 32242 32 16:36 ?00:09:21
postgres: robert tassiv 192.168.0.250 INSERT
postgres 6427 32242 1 16:36 ?00:00:21
postgres: robert tassiv 192.168.0.250 idle
postgres 6615 32242 3 16:48 ?00:00:34
postgres: robert tassiv localhost VACUUM waiting
postgres 6824 32242 0 17:01 ?00:00:00
postgres: checkpoint subprocess
Anything to look at before I kick it? It's not built with debug, but I
can still get a backtrace.
Tables:
obs_keep is a temp table generated via perl script
SELECT i.obs_id AS i_obs_id,
v.obs_id AS v_obs_id
INTO TEMP obs_keep
FROM
(SELECT obs_id, file_id, loc
FROM temp_obs_v NATURAL JOIN files
WHERE group_id = $group_id
AND pair_id = $pair_id) AS v,
(SELECT obs_id, file_id, loc
FROM temp_obs_i NATURAL JOIN files
WHERE group_id = $group_id
AND pair_id = $pair_id) AS i
WHERE i.loc @ scircle( v.loc, $MATCH_RADIUS )
tassiv=# \d temp_obs_i
Table public.temp_obs_i
Column | Type | Modifiers
-+-+
x | real| not null
y | real| not null
imag| real| not null
smag| real| not null
loc | spoint | not null
obs_id | integer | not null default nextval('obs_id_seq'::text)
file_id | integer | not null
use | boolean | default false
solve | boolean | default false
star_id | integer |
mag | real|
Indexes:
temp_obs_i_file_id_index btree (file_id)
temp_obs_i_index gist (loc)
temp_obs_i_loc_index gist (loc)
temp_obs_i_obs_id_index btree (obs_id)
Foreign-key constraints:
temp_obs_i_files_constraint FOREIGN KEY (file_id) REFERENCES
files(file_id) ON DELETE CASCADE Inherits: obs_root
tassiv=# \d obs_i
Table public.obs_i
Column | Type | Modifiers
-+-+
x | real| not null
y | real| not null
imag| real| not null
smag| real| not null
loc | spoint | not null
obs_id | integer | not null default nextval('obs_id_seq'::text)
file_id | integer | not null
use | boolean | default false
solve | boolean | default false
star_id | integer |
mag | real|
Indexes:
obs_i_file_id_index btree (file_id)
obs_i_loc_index gist (loc)
obs_i_obs_id_index btree (obs_id)
obs_i_star_id_index btree (star_id)
obs_i_use_index btree (use)
Foreign-key constraints:
obs_i_files_constraint FOREIGN KEY (file_id) REFERENCES
files(file_id) ON DELETE CASCADEobs_i_star_id_constraint FOREIGN
KEY (star_id) REFERENCES catalog(star_id) ON DELETE SET NULL Triggers:
obs_i_trig BEFORE INSERT OR DELETE OR UPDATE ON obs_i FOR EACH ROW
EXECUTE PROCEDURE observations_trigger()
Inherits: obs_root
--
17:05:52 up 8 days, 9:51, 4 users, load average: 0.03, 0.51, 0.92
pgp0.pgp
Description: PGP signature
Re: [HACKERS] new psql \d command
could be \dn for describe namespace for ei : \dn pg_catalog, or only \dn to list all namespaces ? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] TODO items
Tom Lane kirjutas R, 08.08.2003 kell 16:56: Bruce Momjian [EMAIL PROTECTED] writes: o Add optional textual message to NOTIFY Not done, but there is room in the FE/BE protocol now for something like this. Were there any other changes to NOTIFY - there was talk about making NOTIFY use some other structure instead of ordinary PG tables in backend. -- Hannu ---(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] dropping a user causes pain (#2)
Andreas Pflug [EMAIL PROTECTED] writes: Andrew Dunstan wrote: OTOH I'm not sure how much harm this causes, other than aesthetic. Dropping a user could merely set a dropped flag to disable login, and some VACUUM action could cleanup databases. Not sure I care for the vacuum part of that, but how about this variant: DROP USER sets a flag in pg_shadow to disable login, and the pg_shadow entry isn't removed, ever. (We could tweak the pg_user view to hide dropped users, but anything looking directly at pg_shadow would have to be taught about the flag, analogous to what happened with attisdropped in the last release.) The advantage here is that the sysid assigned to the user would remain present in pg_shadow and couldn't accidentally be assigned to a new user. This would prevent the problem of new users inheriting permissions and even object ownership from deleted users due to chance coincidence of sysid. I suppose one could delete the pg_shadow row once one is darn certain there is no trace of the user's sysid anywhere, but it's not clear to me it's worth the trouble. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] consistency check on SPI tuple count failed
Mendola Gaetano [EMAIL PROTECTED] writes: Again the error: kalman=# select bar(); ERROR: consistency check on SPI tuple count failed CONTEXT: PL/pgSQL function bar line 5 at for over select rows kalman=# select bar(); ERROR: consistency check on SPI tuple count failed CONTEXT: PL/pgSQL function bar line 5 at for over select rows server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. After adding a second row to the test table, I am able to reproduce the above (including the core dump after second try) on an intel/linux box, but *not* on HPUX. I now suspect a memory-stomp kind of problem, like someone writing one too many bytes in a struct. HPUX tends to mask these in situations where intel will not, because it uses MAXALIGN 8 rather than 4. I have also just traced through _SPI_cursor_operation() in spi.c, watched PortalRunFetch return 2, and then watched _SPI_checktuples read zero from _SPI_current-processed. How the heck could that happen? Compiler bug, or am I just crazy? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] encoding question
Christopher Kings-Lynne kirjutas N, 07.08.2003 kell 04:33: My other question is we play around with bytea fields to escape nulls and chars 32 and stuff so that when someone browses the table, they get '\000unknown\000...', etc. actually bytea *stores* char(0), you get \000 or \x0 or @ or whatever depending on whatever you use for displaying it. the escaping i's done only to fit the data into a SQL statement when inserting the data into the database. select returns straight bytes from bytea. However, are the other field types for which we have to do this? Can you put nulls and stuff in text/varchar/char fields? No. Nulls are not allowed in text/varchar fields. - Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] PITR in 7.4
hi, Tom and Momjian Is PITR also delayed to 7.5?Right? 3x Jinqiang Han [EMAIL PROTECTED] 2003-08-05 ---(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] status of dbf2pg
I received the following note from the original author of dbf2pg: Date: Tue, 05 Aug 2003 18:43:22 +0400 From: Maarten Boekhold [EMAIL PROTECTED] Subject: Re: status of dbf2pg To: [EMAIL PROTECTED] On 08/03/2003 06:55:01 AM nolan wrote: What is the status of dbf2pg. There do not appear to have been any updates to it in quite some time. No status. I created this around 1995/96 and haven't looked at it since. There is a dbf2pg package on debian that appears to have a higher version number, but I don't know what to do with a .deb file extension and I'm getting ready to go out of town so I won't have time to look into it until mid-August at the earliest. -- Mike Nolan ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH HOLD and pooled connections
TODO item? --- Tom Lane wrote: Rod Taylor [EMAIL PROTECTED] writes: On Fri, 2003-08-08 at 16:00, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Seems we have a problem with pooled connections and WITH HOLD cursors. We have code to reset transaction state and variables via RESET ALL, but how do we remove WITH HOLD cursors when we pass a connection to a new client? Prepared statements would be just as much of a problem. I think the correct answer is simply don't use those features in a pooled environment. Actually, prepared statements in a pooled environment would be very useful if you could ask what the currently prepared names for that connection are. Hm. Good thought. Maybe the same is true of held cursors. It would be a simple addition at either the protocol or SQL level to list the names of existing prepared statements and portals. Probably too late for 7.4 though. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logging stuff
Gathering some of the threads, here's what I see: 1. There is some demand for per database logging. Doing that is rather beyond the scope of what I intended - in fact what I intended was to enable sensible log splitting out of band. I'll even write a perl script to do it for you if you like :-) 2. There is a suggestion of custom log formats, beyond the current on/off flags used, similar to Apache's logging mechanism. Of course, there are some problems - web logs cover one thing - http transactions. Even there, custom logging has led people to grief, and led to the common logging format that web log analysers usually use. And Pg logs cover various kinds of events. Some don't have a sensible db name, for example (like server startup logs messages). Writing code that would allow administrators to specify the log format almost arbitrarily, or even just to order the fields arbitrarily, would be quite a bit of work, and I suspect a constant source of irritating bugs, and I have no interest in doing the work. There are far more valuable things that need to be done. 3. There is debate about field delimiters in the logs. Currently, timestamp when used is a fixed length field at the start of the record, and pid when used is inside [ ]. This is complicated in the case of dbname by the facts that a) dbnames can be almost anything and b) not all records will have a (sensible) dbname to report. I chose [ ] originally to conform to what was done with pid, but now I'm leaning towards something else - too many M$ people have used [ ] in db work for years, so it is likely to crop up in names somehow. Maybe or | | - whatever seems least liketo to occur inside a dbname. I want to logs to be readable as well as parseable, or I'd use something like ^ or ~ or `. The other reason to move away from [ ] is that Tom (quite reasonably) wants to keep the char count down, and that would let me drop the db: prefix, so the fixed overhead would be 3 chars per line instead of 6 (if you count the trailing space). The bottom line for me is to get the info needed in the logs, and then make them look pretty, split them, or whatever, out of band - that isn't (and shouldn't, IMNSHO) be the server's job. andrew ---(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] truncate all?
Hi there, At 17:55 05.08.2003, Josh Berkus wrote: Another way to specify a safe but efficient TRUNCATE ALL command that might be easier to implement than above TRUNCATE table [CASCADE|RESTRICT] might be to implement the functionality of the originally suggested TRUNCATE ALL through a psql meta-command. Any suggestions for a safe syntax of such a TRUNCATE ALL meta-command? How about \rtuples *? I'm not clear on the usefulness of this idea. If we agree that TRUNCATE ... CASCADE is needed, then doing it in SQL makes sense. Your suggested command would leave itself open to typo-death. How about using a command name that is long enough so that mistyping becomes highly unlikely? The following might be exaggerated, but why not call it something like \removealltuplesofalltableswithoutcheckingreferentialintegrity (please take it with a grain of salt 8-). Both this meta-command and the TRUNCATE table CASCADE command make sense separately as extensions to psql, as their motivations are quite different. For the purpose of unit-tests, only the first would make sense since there we need to truncate all tables anyway and as the latter would only generate unnecessary overhead. Unit-tests must run as fast as possible as we want to rely on them for every editing step. And if we have TRUNCATE ... CASCADE, then truncating all tables is a matter of a very simple perl or C script looping through pg_class. Sure, but I would imagine that a truncation of all tuples of every table in the db, for (1), would be very simple to implement since we can forget about all dependencies, and, for (2), would be decidedly faster than calling TRUNCATE table CASCADE repeatedly, because there is no need to switch languages and no need to find out about dependencies as a first step. As an aside, I wonder whether a cascading truncate needs to be made safe against table-level cyclicity w.r.t. referential integrity? I also don't think that outside of your particular case that there's much demand for it; I would like to believe that there is a need for efficient unit-testing (unit-tests can be seen as a kind of regression tests for applications) in postgresql beyond our project. If developers are not using unit-tests (yet), I would also like to believe that this should not be interpreted as an indication that they do not want to use them, but maybe that they do not know about them. Thanks for your time, Andi. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] new psql \d command
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: It just seemed complex to figure out which operators needed parens and which didn't. The fact that the first attempt was wrong doesn't improve my faith in that code one bit ;-). It was posted expressively with request for comment/review to locate bogus/non-fail-safe assumptions. That operator thing was introduced last-minute before feature freeze, coded late at night. I don't want pg_dump invoking it, even as an option. Someone will get burnt. Yes, even if we get it right now, it might break in the future by a change somewhere else, and we may not discover the breakage until it is too late. Doesn't this apply to any change? pg_dump can be used as a kind of reverse-engineer tool, that's why user-readability can make sense. I wonder when somebody wishes pgAdmin3 to do that for a complete db (effectively duplicating pg_dump's feature)... Regards, Andreas ---(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] dropping a user causes pain (#2)
If people want to remove a user, I assume they don't want to keep old objects around. How about if we created a script that goes through all the databases and reports items owned by a specific user, or orphaned items not owned by anyone? --- Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Andrew Dunstan wrote: OTOH I'm not sure how much harm this causes, other than aesthetic. Dropping a user could merely set a dropped flag to disable login, and some VACUUM action could cleanup databases. Not sure I care for the vacuum part of that, but how about this variant: DROP USER sets a flag in pg_shadow to disable login, and the pg_shadow entry isn't removed, ever. (We could tweak the pg_user view to hide dropped users, but anything looking directly at pg_shadow would have to be taught about the flag, analogous to what happened with attisdropped in the last release.) The advantage here is that the sysid assigned to the user would remain present in pg_shadow and couldn't accidentally be assigned to a new user. This would prevent the problem of new users inheriting permissions and even object ownership from deleted users due to chance coincidence of sysid. I suppose one could delete the pg_shadow row once one is darn certain there is no trace of the user's sysid anywhere, but it's not clear to me it's worth the trouble. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] LOCK.tag(figuring out granularity of lock)
Alvaro Herrera wrote: Right now the sectors on the hard disk run clockwise, but I heard a rumor that you can squeeze 0.2% more throughput by running them counterclockwise. It's worth the effort. Recommended. (Gerry Pourwelle) In relation to your signature, I assume you have seen this joke: http://www.netfunny.com/rhf/jokes/95q1/jpreviews.html The helicopter part is the funniest. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.4beta1 on unixware 713
Hi all, I've put my hands on 7.4beta1 and it doesn't compile on unixware 7.1.3 (Tom, I know you don't like it) Here's the logs: I did'nt have enough time to have a closer look, but I *think* unixware redefines some symbols like shutdown to _shutdown.. Here are the logs checking build system type... i686-unknown-sysv5UnixWare7.1.3 checking host system type... i686-unknown-sysv5UnixWare7.1.3 checking which template to use... unixware checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for C compiler default output... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... no checking whether cc accepts -g... yes configure: using CFLAGS=-O -K inline checking whether the C compiler still works... yes checking how to run the C preprocessor... cc -E checking allow thread-safe libpq and ecpg... no checking whether to build with Tcl... yes checking whether to build with Tk... yes checking whether to build Perl modules... yes checking whether to build Python modules... no checking whether to build Java/JDBC tools... yes checking for jakarta-ant... no checking for ant... /usr/local/jakarta/ant/bin/ant checking whether /usr/local/jakarta/ant/bin/ant works... yes checking whether to build with Kerberos 4 support... no checking whether to build with Kerberos 5 support... no checking whether to build with PAM support... no checking whether to build with Rendezvous support... no configure: using CPPFLAGS= -I/usr/local/include configure: using LDFLAGS= -L/usr/local/lib checking for gawk... no checking for mawk... no checking for nawk... nawk checking for flex... /usr/local/bin/flex checking whether ln -s works... yes checking for non-GNU ld... /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... no checking whether ld -R works... yes checking for ranlib... : checking for lorder... lorder checking for tar... /usr/bin/tar checking for strip... strip checking whether it is possible to strip libraries... no checking for bison... bison -y checking for perl... /usr/bin/perl checking for Perl archlibexp... /usr/local/lib/perl5/5.8.0/i386-svr5 checking for Perl privlibexp... /usr/local/lib/perl5/5.8.0 checking for Perl useshrplib... true checking for flags to link embedded Perl... -L/usr/local/lib -L/usr/gnu/lib /usr/local/lib/perl5/5.8.0/i386-svr5/auto/DynaLoader/DynaLoader.a -L/usr/local/lib/perl5/5.8.0/i386-svr5/CORE -lperl -lsocket -lnsl -ldl -lld -lm -lcrypt -lutil checking for main in -lbsd... no checking for setproctitle in -lutil... no checking for main in -lm... yes checking for main in -ldl... yes checking for main in -lnsl... yes checking for main in -lsocket... yes checking for main in -lipc... no checking for main in -lIPC... no checking for main in -llc... no checking for main in -ldld... no checking for main in -lld... yes checking for main in -lcompat... no checking for main in -lBSD... no checking for main in -lgen... yes checking for main in -lPW... no checking for main in -lresolv... yes checking for main in -lwsock32... no checking for library containing getopt_long... no checking for main in -lunix... no checking for library containing crypt... none required checking for library containing fdatasync... no checking for shmget in -lcygipc... no checking for readline... yes (-lreadline -ltermcap) checking for inflate in -lz... yes checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking crypt.h usability... yes checking crypt.h presence... yes checking for crypt.h... yes checking dld.h usability... no checking dld.h presence... no checking for dld.h... no checking endian.h usability... no checking endian.h presence... no checking for endian.h... no checking fp_class.h usability... no checking fp_class.h presence... no checking for fp_class.h... no checking getopt.h usability... no checking getopt.h presence... no checking for getopt.h... no checking ieeefp.h usability... yes checking ieeefp.h presence... yes checking for ieeefp.h... yes checking poll.h usability... yes checking poll.h presence... yes checking for poll.h... yes checking pwd.h usability... yes checking pwd.h presence... yes checking for pwd.h... yes checking sys/ipc.h usability... yes checking sys/ipc.h presence... yes checking for sys/ipc.h... yes checking sys/poll.h usability... yes checking sys/poll.h presence... yes checking for sys/poll.h... yes checking sys/pstat.h usability... no checking sys/pstat.h presence... no checking for sys/pstat.h... no checking sys/select.h usability... yes checking
Re: [HACKERS] build on unixware 713
--On Friday, August 08, 2003 16:04:47 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: To fix it, remove -K inline from src/port/unixware's CFLAGS. Dave Prosser of SCO has a fix, but this is the most expedient fix for now. Do you think we should make that change for 7.4? Or will the workaround be obsolete by the time 7.4 is released? I'd suggest making the change for 7.4, and we can do a version check for 7.5. LER regards, tom lane -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] logging stuff
I think we need a more general variable that can take several values, separated by commas, like: log_line: dbname,user or something like that. In fact, looking at the postgresql.conf file, I see only two setting that print on every line: log_pid and log_timestamp. Perhaps those two should be merged into log_line. Of course, this is all for 7.5. --- scott.marlowe wrote: On Tue, 5 Aug 2003, Andrew Dunstan wrote: (Responding to the deafening silence regarding my posts a couple of days ago about logging dbnames and disconnections) ;-) The dbname patch is now done. If nobody objects to the format ([db:yourdbname]) I'll submit it - I did it that way to make it fairly easy to split a log file based on it, although you would have to be careful with multiline log entries such as query strings. It is intentionally minimalist. I had some thoughts about logging disconnections - I can see a way to do it via an on_proc_exit handler, I think. Then I started wondering if it might be useful to log session times instead of just noting a disconnect and letting the user have to calculate the time. But I won't bother with this if there's no interest. *I* have no current use for it, but I could well imagine others might. (I might too in the future if I wanted to debug my connection pooling app). Actually, I'd certainly like to see it done (both dbname and disconnect). I'd guess the deafening silence was more because of no objctions than lack of interest. I know for me it was. ---(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 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 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] PostgreSQL 7.3.4 code coverage with OSDL DBT-2
On Thu, Aug 07, 2003 at 03:43:45PM -0700, [EMAIL PROTECTED] wrote: I used lcov to generate some fancy webpages that shows code coverage of PostgreSQL 7.3.4 from running our DBT-2 workload (TPC-C derivative) against it. Cool -- one thing I've been meaning to do for a while is to get some code coverage data for the regression test suite. I'll try to set that up fairly soon. -Neil ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] sql99 compat list
Christopher Kings-Lynne wrote: Has anyone reviewed the compatibility list for 7.4 yet? http://developer.postgresql.org/docs/postgres/unsupported-features-sql99.html I seem to remember something about us having the unique predicate now or something? Array support is now better, and cursors? String ops for LOBs? Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Doesn't appear to be on this list, but shouldn't the fact that PSQL translates undoublequoted identifiers to lower instead of upper case be mentioned? Also, I think a pretty simple workaround would be to have PSQL search for the upper case identifier, and if not found, search for the lower case. This should allow a migration path while the tools and functions are being translated (and should, perhaps, stay around forever as a session option for old databases) Shachar -- Shachar Shemesh Open Source integration consultant Home page resume - http://www.shemesh.biz/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] build on unixware 713
Already done, but knowing that it's the same on the latest released compiler is interesting. I'll pass it on. LER --On Thursday, August 07, 2003 16:59:50 -0400 Tom Lane [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: Larry just given me his own compiler and I still have the errors... [experiments a little...] Hmm. It works okay with -g, but fails with -O. I suggest filing a bug report. I'm not planning to spend any time looking for workarounds for SCO's compiler bugs. regards, tom lane ---(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 -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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] logging stuff
Bruce Momjian [EMAIL PROTECTED] writes: ... And of course, we already have pid and timestamp, so once we are done, we will have seven possible data items on each line, and with booleans there will be no control over their order on the line. Which is exactly the way I want it ;-). I can't see any use that would justify the amount of extra logic needed to allow user-specified ordering of the entries. This feature discussion seems to be degenerating into a gild-the-lily contest ... regards, tom lane ---(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] dropping a user causes pain
Hi, I dropped the owner of a table, and now I get this: pg_dump: ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] logging stuff
I could see that being nice so you could create a couple of different custom log types, then use one for one database, and another for a different database. Point them to different log files and you've got yourself a great feature. For people running large numbers of databases on a single cluster, it might be quite useful to be able to log each database differently. You bet. Hosting companies give clients the option to pick up their weblogs. This would be a good feature for those. Make the log come out similarly as apache, and you've got yourself some webalizer screens with funny URLs (queries ;). signature.asc Description: This is a digitally signed message part
