Re: [HACKERS] Can't load a 7.3.4 dump into 7.4CVS
On Sat, 9 Aug 2003, Larry Rosenman wrote: I tried(!) to load my 7.3.4 data into 7.4CVS. the Bricolage folks have managed to make a circular definition (at least not loadable). why does each setval() call invoke the pager? the dump I used is at: http://www.lerctr.org/~ler/pg.dump.gz $ ls -l pg.dump* -rw-r--r--1 ler isis 10989689 Aug 9 11:43 pg.dump.gz $ you need to have contrib/dbsize, contrib/pgstattuple, contrib/tsearch, contrib/dblink installed. Any ideas, folks? Unfortunately this looks like a case that the dump needs to be edited for to move the constraint into an ALTER TABLE ADD CONSTRAINT after the function is defined. In general, this looks like one of those things that will eventually be fixed by ordering the dumped objects by dependencies. In general, check constraints that call functions that do subselects (to get around the check constraint limitations currently) can be problematic because they don't actually make a strong constraint and can get you into situations that are not reloadable even if we ordered all the objects correctly without knowing more than the current state of the data in the table. ---(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] Farewell
Good luck, Vadim ! Thanks for your work. Oleg On Sun, 10 Aug 2003, Vadim Mikheev wrote: FarewellIt's time for formal acknowledgement that I'm not in The Project any more. I'm not interested in small features/fixes and have no time for big ones. It was this way for very long time and I don't see how/when that could change. My participation in The Project was one of the greatest adventures in my life. Thanks to everyone! Good luck on your ways. And - long live to Postgres!!! Vadim 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] dropping a user causes pain (#2)
Ah OK, I must have been thinking of the database owner check. I'd vote for (1) checking that they own no objects and by default owning all their stuff to the database owner. Plus add an optional clause: DROP USER foo OWNER TO bob; Chris - Original Message - From: Andrew Dunstan [EMAIL PROTECTED] To: Hackers [EMAIL PROTECTED] Sent: Monday, August 11, 2003 10:49 AM Subject: 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Farewell
-Original Message- From: The Hermit Hacker [mailto:[EMAIL PROTECTED] Sent: 11 August 2003 14:03 To: Vadim Mikheev Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] Farewell I never know what to say in response to stuff like this ... its like having to sign a card when a co-worker leaves ... then again, unlike most co-workesr, I can definitely say its been a great pleasure to have known, and worked, with you ... you brought, and gave, alot to the project, and for that, we thank you ... thanks :) Remind me never to get a job where you work :-) Seriously though, good luck, Vadim, in all your future ventures. Your work on PostgreSQL has most certainly been of great benefit to all of us - I cannot speak for others, but I certainly appreciate your efforts. Regards, Dave. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] PostgreSQL 7.4 Beta 1 + SSL + Cygwin
Hello: I want to know if postgresql 7.4 beta 1 can be configured under Cygwin with SSL support ?? If the answer is positive how can i do it ?? or where can i found documentation about this ( under linux or cygwin :) ) ?? Thanks in advance :) -- Best regards Carlos Guzmán Álvarez Vigo-Spain ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Farewell
On Monday 11 August 2003 00:36, Vadim Mikheev wrote: FarewellIt's time for formal acknowledgement that I'm not in The Project any more. I'm not interested in small features/fixes and have no time for big ones. It was this way for very long time and I don't see how/when that could change. My participation in The Project was one of the greatest adventures in my life. Thanks to everyone! I know that a lot of what makes PostgreSQL what it is today is can be directly atttributed to your work. Thank you. And thank you for the personal help back when I was working on the PostgreSQL trigger documentation. -- D'Arcy J.M. Cain [EMAIL PROTECTED]|vex}.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. ---(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] Farewell
D'Arcy J.M. Cain wrote: On Monday 11 August 2003 00:36, Vadim Mikheev wrote: FarewellIt's time for formal acknowledgement that I'm not in The Project any more. I'm not interested in small features/fixes and have no time for big ones. It was this way for very long time and I don't see how/when that could change. My participation in The Project was one of the greatest adventures in my life. Thanks to everyone! I know that a lot of what makes PostgreSQL what it is today is can be directly atttributed to your work. Thank you. And thank you for the personal help back when I was working on the PostgreSQL trigger documentation. It's hard to imagine PostgreSQL with out MVCC, WAL, subselects, etc. You know, maybe on the Developer's page there should be a PostgreSQL Hall of Fame with Vadim and Thomas Lockhart being the first two inductees. Mike Mascari [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Release changes: statement level triggers
Bruce Momjian wrote: How are statement level triggers supposed to work? Are they just triggers deferred until the end of the statement? You mentioned access to the affected rows, but I don't understand how that is supposed to happen. Statement level isn't about deferring or grouping the trigger execution, but about the triggering event. While a row level trigger will be called exactly once for every row being affected, a statement level trigger will be called exactly once per statement, whatever the result set may be, so even on a zero-row result set it will run. A standard way to handle recursivity on this is to check for a zero-row rowset, and aborting then. But how to obtain information about the rowset? What's needed is an equivalent to the OLD and NEW variableTriggerData.tg_newtuple / TriggerData.tg_trigtuple, so that the result set being modified can be accessed in queries. Thus statement level triggers are a combination of rules (where OLD and NEW define the affected rowset), and row-level triggers, enabling conditial programming etc. For systems where big chunks of data is inserted or updated in a single statement, a row-level trigger, while being much easier to implement, could be a big performance hit. Regards, Andreas ---(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
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? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH HOLD and pooled connections
Bruce Momjian [EMAIL PROTECTED] writes: TODO item? Yeah. I'm leaning towards the idea that a protocol addition is what to do. 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 ---(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] Correlation in cost_index()
AFAICS (part of) the real problem is in costsize.c:cost_index() where IO_cost is calculated from min_IO_cost, pages_fetched, random_page_cost, and indexCorrelation. The current implementation uses indexCorrelation^2 to interpolate between min_IO_cost and max_IO_cost, which IMHO gives results that are too close to max_IO_cost. The indexCorrelation^2 algorithm was only a quick hack with no theory behind it :-(. I've wanted to find some better method to put in there, but have not had any time to research the problem. Could we quick hack it to a geometric mean instead since a mean seemed to yield better results than indexCorrelation^2? As nobody knows how each of these proposals performs in real life under different conditions, I suggest to leave the current implementation in, add all three algorithms, and supply a GUC variable to select a cost function. I don't think it's really a good idea to expect users to pick among multiple cost functions that *all* have no guiding theory behind them. I'd prefer to see us find a better cost function and use it. Has anyone trawled the database literature on the subject? Hrm, after an hour of searching and reading, I think one of the better papers on the subject can be found here: http://www.cs.ust.hk/faculty/dimitris/PAPERS/TKDE-NNmodels.pdf Page 13, figure 3-12 is the ticket you were looking for Tom. It's an interesting read with a pretty good analysis and conclusion. The author notes that his formula begins to fall apart when the number of dimensions reaches 10 and suggests the use of a high dimension random cost estimate algo, but that the use of those comes at great expense to the CPU (which is inline with a few other papers that I read). The idea of precomputing values piqued my interest and I thought was very clever, albeit space intensive. *shrug* -sc -- Sean Chittenden ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Batch Operations
On Fri, Aug 08, 2003 at 15:32:05 +0530, Rahul_Iyer [EMAIL PROTECTED] wrote: hi, 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? Usually you just use psql to run a script with the commands in it. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 7.4beta1 build problem on unixware
--On Saturday, August 09, 2003 12:31:14 -0400 Tom Lane [EMAIL PROTECTED] wrote: Kurt Roeckx [EMAIL PROTECTED] writes: If they don't have it defined, it's not going to do what we expect and we might be better of using our replacement functions. We will if we don't find struct addrinfo. See notes at top of getaddrinfo.h. regards, tom lane and it BREAKS on UnixWare. I can connect on unix domain sockets, but NOT on an IP socket. I'm futzing with #undef'ing HAVE_GETADDRINFO for a test. -- 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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Correlation in cost_index()
On Fri, 08 Aug 2003 18:25:41 -0400, Tom Lane [EMAIL PROTECTED] wrote: Two examples: [...] One more example: X Y A A a B A C b A B B b C C A c B C C Correlation for column X is something less than 1.0, OTOH correlation for an index on upper(X) is 1.0. I don't really see a way to do this without actually examining the multi-column ordering relationship during ANALYZE. So did we reach consensus to add a TODO item? * Compute index correlation on CREATE INDEX and ANALYZE, use it for index scan cost estimation Servus Manfred ---(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] test beta build
Bruce Momjian [EMAIL PROTECTED] writes: Yes, I see those regularly too, and are related to bison issues. The two 'defined but not used' warnings in pgc.c are a longstanding flex (not bison) issue. (Hmm ... I wonder if they still happen in flex 2.5.31?) The other warning is not flex's fault --- looks like a real bug, ie, mistaken assumption about datatype widths. Will fix. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] WITH HOLD and pooled connections
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. 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. regards, tom lane ---(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...
Of course, now I've just gone to some trouble to accomodate funky characters in user and dbnames in logging I'd have to kill him ... :-) Seriously, I think there's a good case for banning a few characters in at least some names - like []'~#*|\ , say andrew Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Is it useful to allow these special chars at all? Seems this creates a lot of work, and most admins will probably stick to normal user names anyway. Well, the reason it's been left unfixed for so long is exactly that it didn't seem pressing. But if Chris wants to do the work, I won't stand in his way ... ---(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] src/interfaces/cli
Peter Eisentraut [EMAIL PROTECTED] writes: Does anyone mind if I remove src/interfaces/cli? It's clearly outdated and useless. Looking at the CVS history, it seems that Lockhart had an idea of building a CLI-spec-compatible interface on top of ecpg, but never went further than an initial commit. If ecpg itself now satisfies that spec then there's certainly no need for src/interfaces/cli ... 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] Release changes
Bruce, I know it's a bit picky but both below should be along the lines of Allow thread-safe library since they are not threaded per se. L. Bruce Momjian writes: Libpq Allow threaded with --enable-thread-safety (Lee Kindness, Bruce) Miscellaneous Interfaces Allow threaded ecpg with --enable-thread-safety (Lee Kindness, Bruce) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Change Request: \pset pager off in pg_dumpall
--On Monday, August 11, 2003 20:36:11 -0400 Tom Lane [EMAIL PROTECTED] wrote: Larry Rosenman [EMAIL PROTECTED] writes: On Mon, 11 Aug 2003, Bruce Momjian wrote: Larry Rosenman wrote: Can we modify pg_dumpall (or pg_dump?) to include a \pset pager off to prevent the setval() calls from halting an interactive \i of the dump file? Your pg_dump's actually invoke the pager? Seriously, sometimes it's useful. Are you using any nonstandard settings in ~/.psqlrc? I've never seen this happen myself. Nope. There is no .psqlrc. It seems to be new with 7.4cvs. (dunno about earlier 7.4), but it definitely did NOT happen with 7.3.x 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] threads/UnixWare
Of course, I was wrong. In fact, the patch below actually said it was
the pre-POSIX version of getpwuid_r().
---
Bruce Momjian wrote:
Actually, your getpwuid_r is the old, pre-POSIX format. The attached
email has the configure tests. I was hoping we wouldn't need them, but
it seems we may.
---
Larry Rosenman wrote:
In src/port, we have in threads.c:
/*
* Wrapper around getpwuid() or getpwuid_r() to mimic POSIX getpwuid_r()
* behaviour, if it is not available.
*/
int
pqGetpwuid(uid_t uid, struct passwd * resultbuf, char *buffer,
size_t buflen, struct passwd ** result)
{
#if defined(USE_THREADS) defined(HAVE_GETPWUID_R)
/*
* broken (well early POSIX draft) getpwuid_r() which returns
'struct
* passwd *'
*/
*result = getpwuid_r(uid, resultbuf, buffer, buflen);
#else
/* no getpwuid_r() available, just use getpwuid() */
*result = getpwuid(uid);
#endif
return (*result == NULL) ? -1 : 0;
}
Which BREAKS if you have the correct getpwuid_r() like UnixWare does.
Can someone help me with the configure checks/macros I need?
$ grep getpwuid_r /usr/include/pwd.h
int getpwuid_r(uid_t, struct passwd *, char *, size_t, struct passwd **);
$
--
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
--
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
From [EMAIL PROTECTED] Tue Aug 5 06:13:10 2003
Return-path: [EMAIL PROTECTED]
Received: from internet.csl.co.uk (internet.csl.co.uk [194.130.52.3])
by candle.pha.pa.us (8.11.6/8.11.6) with ESMTP id h75AD8r29374
for [EMAIL PROTECTED]; Tue, 5 Aug 2003 06:13:09 -0400 (EDT)
Received: from euphrates.csl.co.uk (host-194-67.csl.co.uk [194.130.52.67])
by internet.csl.co.uk (8.12.8/8.12.8) with ESMTP id h75AD284032695;
Tue, 5 Aug 2003 11:13:02 +0100
Received: from kelvin.csl.co.uk by euphrates.csl.co.uk (8.9.3/ConceptI 2.4)
id LAA21628; Tue, 5 Aug 2003 11:13:00 +0100 (BST)
Received: from kelvin.csl.co.uk (localhost.localdomain [127.0.0.1])
by kelvin.csl.co.uk (8.12.8/8.12.8) with ESMTP id h75ACxU1028659;
Tue, 5 Aug 2003 11:12:59 +0100
Received: (from [EMAIL PROTECTED])
by kelvin.csl.co.uk (8.12.8/8.12.8/Submit) id h75ACsBW028655;
Tue, 5 Aug 2003 11:12:54 +0100
From: Lee Kindness [EMAIL PROTECTED]
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-ID: [EMAIL PROTECTED]
Date: Tue, 5 Aug 2003 11:12:54 +0100
To: Bruce Momjian [EMAIL PROTECTED]
Subject: --enable-thread-safety broken + patch regressions
X-Mailer: VM 7.07 under 21.4 (patch 12) Portable Code XEmacs Lucid
cc: Lee Kindness [EMAIL PROTECTED], [EMAIL PROTECTED]
Status: OR
Bruce, the changes you made yesterday to configure for
--enable-thread-safety have broken the build, at least for Linux on
Redhat 9.
Also, I took the opportunity to look at port/threads.c. It is missing
important functionality compaired to the patch I originally
submitted. For getpwuid_r, gethostbyname_r and strerror_r there are
three possible scenarios:
1. The OS doesn't have it (but the non _r function can still be thread
safe (i.e. HPUX 11)).
2. The OS has it, but the implmentation doesn't match the POSIX spec.
3. The OS has it, and the implmentation matches the POSIX spec.
Case 3 is not being considered. In my original patch this was handled
by the pqGetpwuid etc functions simply being defined to getpwuid_r
(except for pqStrerror).
I remember discussing with you that the implementation of pqStrerror
didn't really need the distinction between the two _r
versions. However I think the others do, and the native/correct _r
calls should be #defined in if they match the POSIX spec.
It's also worth considering that when the _r function is available AND
the normal function is also thread-safe then the _r version should
still be used since it has a clean API which removes unneeded locking
within the old function.
I've still got the latest (and earlier with some configure work)
patches I submitted up at:
http://services.csl.co.uk/postgresql/
Thanks, Lee.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
--
Re: [HACKERS] pgstats_initstats() cost
- Original Message - From: Gavin Sherry [EMAIL PROTECTED] I am still researching ways of increasing performance of yacc parsers -- there is a very small amount of information on the Web concerning this -- I know some people who will tell you that the best way of improving performance in this area is not to use yacc (or bison) parsers ... OTOH we need to understand exactly what you were profiling - if it is 1 dynamic sql statement per insert then it might not be too close to the real world - a high volume program is likely to require 1 parse per many many executions, isn't it? cheers andrew ---(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)
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. +1 (Hey I've seen other people do that :P ) Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
