Re: [HACKERS] Cursor support in pl/pg
Jan Wieck [EMAIL PROTECTED] writes: IIRC the patch only provides the syntax for CURSOR to PL/pgSQL. Not real cursor support on the SPI level. So it's still the same as before, the backend will try to suck up the entire resultset into the SPI tuple table (that's memory) and die if it's huge enough. What we really need is an improvement to the SPI manager to support cursor (or cursor like behaviour through repeated executor calls). Agreed, but as I may have said before, 1) the problem you describe already exists in PL/pgSQL when using the FOR x IN SELECT statement, 2) the PL/pgSQL cursor patch is useful without the improvement to the SPI layer, 3) I would argue that the PL/pgSQL cursor patch is still needed after the SPI layer is improved. So I do not think that is a valid argument against installing the PL/pgSQL cursor patch. Ian ---(end of broadcast)--- TIP 83: The only thing cheaper than hardware is talk. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Important news about PgAdmin Drop/create functions, triggers and views and relink the whole system without restarting PostgreSQL
Dear friends, PgAdmin provides new features for dropping/creating functions, triggers and views and relinking the whole system without restarting PostgreSQL. By now, this new feature is only available as a patch to PgAdmin http://www.greatbridge.org/project/pgadmin/patch/patchlist.php. If you are curious reading the code, download and install the patch. Don't use the patch on production systems, because there are still a lot of bugs. Otherwise, wait until Dave Page integrates the code in the CVS. I will inform you by mail when you can start using this new feature. When all this is implemented in PgAdmin, I will also provide you with PL/pgSQL code to perform the same things server-side. By now, I don't really know if we can run all this stuff in a single transaction. Any idea? Help welcome on PgAdmin project, we need volunteers and feedback http://www.greatbridge.org/pipermail/pgadmin-hackers/. We are always looking for beta testers, new members and developers. Some great new features that will transform PgAdmin into the most advanced IDE for PostgreSQL: - query/code loader in WDDX format for use in Php/C++/Perl/VB. - postgresql packages. - syntax checking and highlighting. And also: - PL/SQL Universal Schema: a set of PL/SQL functions to administer/migrate from/to Oracle, PostgreSQL and Ms SQL Server transparently. If you are interested by PL/SQL Universal Schema, why not open a new branch on PostgreSQL CVS, otherwise I will host the project on Greatbridge. Environments such as Php or Klyx should be able to administer databases without the use of odbc and Microbsoft stuff. - PostgreSQL WDDX support (seems easy, is it really?). - I do not agree that the ALTER FUNCTION etc... should be delayed to 7.2 release. By now, it is impossible to do serious development without breaking dependencies somewhere. PostgreSQL is intended for end-users, right ? Other information: I do not work for Greatbridge, these are complete and pure GPL open source contributions. So, don't hesitate to visit http://www.greatbridge.org which provides excellent tools for developers. Greetings from Jean-Michel POURE, Paris, France Axitrad, CEO ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] refusing connections based on load ...
On Wed, 25 Apr 2001, The Hermit Hacker wrote: On Wed, 25 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. It's part of the standard C library in FreeBSD. Any other platforms have it built in? As has been mentioned, Solaris and Linux also have it ... But what's in FreeBSD's standard library isn't GNU. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] refusing connections based on load ...
On Thu, 26 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, The Hermit Hacker wrote: On Wed, 25 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. It's part of the standard C library in FreeBSD. Any other platforms have it built in? As has been mentioned, Solaris and Linux also have it ... But what's in FreeBSD's standard library isn't GNU. Wouldn't matter if it was, its part of the OSs standard library ... unless you mean to pull it in and use it with the distribution, which I think might be a bad idea ... if we pull anything in, sendmail's would be best ... FreeBSD's will have had anything required for non-FreeBSD systems yanked out, if it was ever there, while sendmail's already has all the 'hooks' in it ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] refusing connections based on load ...
On Thu, 26 Apr 2001, The Hermit Hacker wrote: On Thu, 26 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, The Hermit Hacker wrote: On Wed, 25 Apr 2001, Vince Vielhaber wrote: On Wed, 25 Apr 2001, Tom Lane wrote: The Hermit Hacker [EMAIL PROTECTED] writes: Autoconf has a 'LOADAVG' check already, so what is so problematic about using that to enabled/disable that feature? Because it's tied to a GNU getloadavg.c implementation, which we'd have license problems with using. It's part of the standard C library in FreeBSD. Any other platforms have it built in? As has been mentioned, Solaris and Linux also have it ... But what's in FreeBSD's standard library isn't GNU. Wouldn't matter if it was, its part of the OSs standard library ... unless you mean to pull it in and use it with the distribution, which I think might be a bad idea ... if we pull anything in, sendmail's would be best ... FreeBSD's will have had anything required for non-FreeBSD systems yanked out, if it was ever there, while sendmail's already has all the 'hooks' in it ... That wasn't what I was saying at all. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] concurrent postgres in NUMA cluster postgres - design OK ?
Folks: I'm planning a port of Postgres to a multiprocessor architecture in which all nodes have both local memory and fast access to a shared memory. Shared memory it more expensive than local memory. My intent is to put the shmem lock structures in shared memory, but use a copy-in / copy-out approach to maintain coherence in the buffer cache: - copy buffer from shared memroy on buffer allocate - write back buffer to shared memory when it is dirtied. Is that enough ? The idea sketch is as follows (mostly, changes contained in storage/buffer/bufmgr.c): -change BufferAlloc, etc, to create a node-local copy of the buffer (from shared memory). Copy both the BufferDesc entry and the buffer-data array -change WriteBuffer to copy the (locally changed) buffer to shared memory (this is the point in which the BM_DIRTY bit is set). [ I am assuming the buffer is locked this is a safe time to make the buffer visible to other backends]. [Assume, for this discussion, that the sem / locks structs in shared memory have been ported work ]. Ditto for the hash access. My concern is whether that is enough to maintain consistency in the buffer cache (i.e, are there other places in the code where a backend might have a leftover pointer to somewhere in the buffer cache ? ) Because, in the scheme above, the buffer cache is not directly accessible to the backend except via this copy in / copy -out approach. [BTW, I think this might be a way of providing a 'cluster' version of Postgers, by using some global communication module to obtain/post the 'buffer cache' values] thanks regards Mauricio Mauricio Breternitz Jr, Ph.D. Times N Systems Inc. 1908 Kramer Ln, Braker Building B, Suite P Austin, TX 78758 phone (512) 977 5368 [EMAIL PROTECTED] Mauricio Breternitz Jr, Ph.D. Times N Systems Inc. 1908 Kramer Ln, Braker Building B, Suite P Austin, TX 78758 phone (512) 977 5368 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] scaling multiple connections
I am getting a bit concerned about Postgres 7.1 performance with multiple connections. Postgres does not seem to scaling very well. Below there is a list of outputs from pgbench with different number of clients, you will see that postgres' performance in the benchmark drops with each new connection. Shouldn't the tps stay fairly constant? I am using pgbench because I saw this performance issue with a project I was developing. I decided to try doing operations in parallel, thinking that postgres would scale. What I found was the more machines I added to the task, the slower the processing was. Anyone have any ideas? Is this how it is supposed to be? My postmaster start line looks like: /usr/local/pgsql/bin/postmaster -A0 -N 24 -B 4096 -i -S -D/sqlvol/pgdev -o -F -fs -S 2048 The database is on a dedicated PCI-IDE/66 promise card, with a 5400rpm maxtor drive, not the best hardware, I grant you, but that should have little to do with the scaling aspect. I am running redhat linux 7.0, kernel 2.4.3. 512M ram, dual PIII 600mhz. [markw@snoopy pgbench]$ ./pgbench -v -c 1 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 1 number of transactions per client: 30 number of transactions actually processed: 30/30 tps = 218.165952(including connections establishing) tps = 245.062001(excluding connections establishing) [markw@snoopy pgbench]$ ./pgbench -v -c 2 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 2 number of transactions per client: 30 number of transactions actually processed: 60/60 tps = 200.861024(including connections establishing) tps = 221.175326(excluding connections establishing) [markw@snoopy pgbench]$ ./pgbench -v -c 3 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 3 number of transactions per client: 30 number of transactions actually processed: 90/90 tps = 144.053242(including connections establishing) tps = 154.083205(excluding connections establishing) [markw@snoopy pgbench]$ ./pgbench -v -c 4 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 4 number of transactions per client: 30 number of transactions actually processed: 120/120 tps = 129.709537(including connections establishing) tps = 137.852284(excluding connections establishing) [markw@snoopy pgbench]$ ./pgbench -v -c 5 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 5 number of transactions per client: 30 number of transactions actually processed: 150/150 tps = 103.569559(including connections establishing) tps = 108.535287(excluding connections establishing) ... [markw@snoopy pgbench]$ ./pgbench -v -c 20 -t 30 pgbench starting vacuum...end. starting full vacuum...end. transaction type: TPC-B (sort of) scaling factor: 1 number of clients: 20 number of transactions per client: 30 number of transactions actually processed: 600/600 tps = 40.600209(including connections establishing) tps = 41.352773(excluding connections establishing) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] refusing connections based on load ...
Nathan Myers wrote: On Mon, Apr 23, 2001 at 03:09:53PM -0300, The Hermit Hacker wrote: Anyone thought of implementing this, similar to how sendmail does it? If load n, refuse connections? ... If nobody is working on something like this, does anyone but me feel that it has merit to make use of? I'll play with it if so ... I agree that it would be useful. Even more useful would be soft load shedding, where once some load average level is exceeded the postmaster delays a bit (proportionately) before accepting a connection. Or have the load check on AtXactStart, and delay new transactions until load is back below x, where x is configurable per user/group plus some per database scaling factor. How is this different than limiting the number of backends that can be running at once? It would seem to me that a user that has a delayed startup is going to think there's something wrong with the server and keep trying, where as a message like too many clients - try again later explains what's really going on. len morgan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] pg_log file corrupted
Hi, I think the pg_log file of my postgreSQL is corrupted. I can;t access to tables in my database now. The error message appear when I tried to list all tables is as below : cannot flush block 8 of pg_log to stable store Anybody know how can I restore back the pg_log file. Thanks. Emmanuel Wong ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] ERROR: parser: parse error at or near JOIN
Is anyone else seeing this? I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: *** ./expected/join.out Thu Dec 14 17:30:45 2000 --- ./results/join.out Mon Apr 23 20:23:15 2001 *** *** 1845,1851 -- UNION JOIN isn't implemented yet SELECT '' AS xxx, * FROM J1_TBL UNION JOIN J2_TBL; ! ERROR: UNION JOIN is not implemented yet -- -- Clean up -- --- 1845,1851 -- UNION JOIN isn't implemented yet SELECT '' AS xxx, * FROM J1_TBL UNION JOIN J2_TBL; ! ERROR: parser: parse error at or near JOIN -- -- Clean up -- == -- Fernando Nasser Red Hat Canada Ltd. E-Mail: [EMAIL PROTECTED] 2323 Yonge Street, Suite #300 Toronto, Ontario M4P 2C9 ---(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] refusing connections based on load ...
Tom Lane wrote:
A less dangerous way of approaching it might be to have an option
whereby the postmaster invokes 'uptime' via system() every so often
(maybe once a minute?) and throttles on the basis of the results.
The reaction time would be poorer, but security would be a whole lot
better.
Rather than do system('uptime') and incur the process start-up each time,
you could do fp = popen('vmstat 60', 'r'), then just read the fp.
I believe vmstat is fairly standard. For those systems
which don't support vmstat, it could be faked with a shell script.
You could write the specific code to handle each arch, but it's
a royal pain, because it's so different for many archs.
Another possibility could be to read from /proc for those systems
that support /proc. But I think this will be more variable than
the output from vmstat. Vmstat also has the added benefit of
providing other information.
I agree with Tom about not wanting to open up /dev/kmem,
due to potential security problems.
Neal
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] ERROR: parser: parse error at or near JOIN
Please disregard this. This message was held by Majordomo for a couple of days and I have already resent it. Tom Lane has already solved my problem (I had a miscompiled version of bison in my machine). Regards to all, Fernando Fernando Nasser wrote: Is anyone else seeing this? I have the current CVS sources and make check ends up with one failure. My regression.diffs shows: *** ./expected/join.out Thu Dec 14 17:30:45 2000 --- ./results/join.out Mon Apr 23 20:23:15 2001 *** *** 1845,1851 -- UNION JOIN isn't implemented yet SELECT '' AS xxx, * FROM J1_TBL UNION JOIN J2_TBL; ! ERROR: UNION JOIN is not implemented yet -- -- Clean up -- --- 1845,1851 -- UNION JOIN isn't implemented yet SELECT '' AS xxx, * FROM J1_TBL UNION JOIN J2_TBL; ! ERROR: parser: parse error at or near JOIN -- -- Clean up -- -- Fernando Nasser Red Hat Inc. E-Mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] unanswered: Schema Issue
I want to extract tables schema information, i've looked at src/bin/psql/describe.c but i cannot determine the datatype 'serial' and 'references' from pg_*, i understand that triggers are generated for serial and references, so how i can understand from my perl application the full schema ? thanks, valter _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(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] RI oddness
Jan Wieck wrote: Just discussed it with Tom Lane while he'd been here in Norfolk and it's even more ugly. We couldn't even pull out the FK's column defaults at this time to check if we are about to delete the corresponding PK because they might call all kinds of functions with tons of side effects we don't want. Seems the only way to do it cleanly is to have the parser putting the information which TLEs are *OLD* and which are *NEW* somewhere and pass it all down through the executor (remembering it per tuple in the deferred trigger queue) down into the triggers. While we know about the *right* way to fix it, that's a far too big of a change for 7.1.1. But I'd like to fix the likely deadlocks caused by referential integrity constraints. What'd be easy is this: - We already have two entry points for INSERT/UPDATE on FK table, but the one for UPDATE is fortunately unused. - We change analyze.c to install the RI_FKey_check_upd trigger if the constraint has an ON DELETE SET DEFAULT clause. Otherwise it uses RI_FKey_check_ins as it does now. - We change ri_triggers.c so that RI_FKey_check_ins will skip the PK check if the FK attributes did not change while RI_FKey_check_upd will enforce the check allways. This way it'll automatically gain a performance win for everyone using referential integrity. The bad side effect is, that these changes will require a dump/reload FOR DATABASES, where ON DELETE SET DEFAULT is used. If they don't dump/reload, it'll open the possibility of violating constraints that are defined ON DELETE SET DEFAULT by deleting the PK that consists of the column defaults of an existing FK reference. The DELETE would succeed and the stall references remain. I think the usage of ON DELETE SET DEFAULT is a very rare case out in the field. Thus the dump/reload requirement is limited to a small number of databases (if any). It is easy to detect if a DB's schema contains this clause by looking up pg_trigger for usage of RI_FKey_setdefault_del. We could provide a small script telling which databases need dump/reload. Comments? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: [GENERAL] Re: Hardcopy docs available
If someone wants to run the A4 docs through a PDF converter, send 'em to me and I'll post them too. I just did the userA4 pdf from hub with this: $ gzcat userA4.ps.gz | ps2pdf - userA4.pdf Do you want me to do the rest of them? Or we can probably have the makindex script do it for us. Sure. Anything is fine. - Thomas ---(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] scaling multiple connections
mlw [EMAIL PROTECTED] writes: I am getting a bit concerned about Postgres 7.1 performance with multiple connections. Postgres does not seem to scaling very well. Below there is a list of outputs from pgbench with different number of clients, you will see that postgres' performance in the benchmark drops with each new connection. Shouldn't the tps stay fairly constant? There was quite a long thread about this in pghackers back in Jan/Feb (or so). You might want to review it. One thing I recall is that you need a scaling factor well above 1 if you want meaningful results --- at scale factor 1, all of the transactions want to update the same row, so of course there's no parallelism and a lot of lock contention. The default WAL tuning parameters (COMMIT_DELAY, WAL_SYNC_METHOD, and friends) are probably not set optimally in 7.1. We are hoping to hear about some real-world performance results so that we can tweak them in future releases. I do not trust benchmarks as simplistic as pgbench for doing that kind of tweaking, however. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Cursor support in pl/pg
Jan Wieck [EMAIL PROTECTED] writes: I don't object if we can be sure that it's implementing the syntax a final version with *real* cursor support will have. Can we? I don't know, and I don't know what the decision criteria are. I intentionally implemented the Oracle cursor syntax. PL/pgSQL is very similar to PL/SQL, and I didn't see any reason to introduce a spurious difference. Note in particular that simply passing OPEN/FETCH/CLOSE through to the Postgres SQL parser does not implement the Oracle cursor syntax, so I wouldn't have done that even if it would have worked. (I have a vested interest here. For various reasons, my company, Zembu, has an interest in minimizing the strain of porting applications from Oracle to Postgres. I assume that the Postgres team also has that interest, within reason. But I don't know for sure.) Ian ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: scaling multiple connections
Tom Lane wrote:
mlw [EMAIL PROTECTED] writes:
I am getting a bit concerned about Postgres 7.1 performance with
multiple connections. Postgres does not seem to scaling very
well. Below there is a list of outputs from pgbench with different
number of clients, you will see that postgres' performance in the
benchmark drops with each new connection. Shouldn't the tps stay
fairly constant?
There was quite a long thread about this in pghackers back in Jan/Feb
(or so). You might want to review it. One thing I recall is that
you need a scaling factor well above 1 if you want meaningful results
--- at scale factor 1, all of the transactions want to update the same
row, so of course there's no parallelism and a lot of lock contention.
The default WAL tuning parameters (COMMIT_DELAY, WAL_SYNC_METHOD, and
friends) are probably not set optimally in 7.1. We are hoping to hear
about some real-world performance results so that we can tweak them in
future releases. I do not trust benchmarks as simplistic as pgbench for
doing that kind of tweaking, however.
I agree with you about the benchmarks, but it does behave similar to what I
have in my app, which is why I used it for an example.
If you are familiar with cddb (actually freedb.org) I am taking that data in
putting it into postgres. The steps are: (pseudo code)
select nextval('cdid_seq');
begin;
insert into titles (...) values (...);
for(i=0; i tracks; i++)
insert into tracks (...) values (...);
commit;
When running stand alone on my machine, it will hovers around 130 full CDs per
second. When I start two processes it drops to fewer than 100 inserts per
second. When I add another, it drops even more. The results I posted with
pgbench pretty much showed what I was seeing in my program.
I hacked the output of pgbench to get me tabbed delimited fields to chart, but
it is easier to look at, see the results below. This is the same build and same
startup scripts on the two different machines. I know this isn't exactly
scientific, but I have a few bells going off suggesting that postgres has some
SMP scaling issues.
My Dual PIII 600MHZ, 500M RAM, Linux 2.4.3 SMP
pg_xlog is pointed to a different drive than is base.
I/O Promise dual IDE/66, xlog on one drive, base on another.
count transaction time (excluding connection)
1 32 175.116
2 32 138.288
3 32 102.890
4 32 88.243
5 32 77.024
6 32 62.648
7 32 61.231
8 32 60.017
9 32 56.034
10 32 57.854
11 32 50.812
12 32 53.019
13 32 50.289
14 32 46.421
15 32 44.496
16 32 45.297
17 32 41.725
18 32 46.048
19 32 45.007
20 32 41.584
21 32 43.420
22 32 39.640
23 32 43.250
24 32 41.617
25 32 42.511
26 32 38.369
27 32 38.919
28 32 38.813
29 32 39.242
30 32 39.859
31 32 37.938
32 32 41.516
Single processor PII 450, 256M, Linux 2.2.16
pg_xlog pointing to different drive than base
I/O Adaptec 2940, Two seagate barracudas.
count transaction time (excluding connection)
1 32 154.539
2 32 143.609
3 32 144.608
4 32 141.718
5 32 128.759
6 32 154.388
7 32 144.097
8 32 149.828
9 32 143.092
10 32 146.548
11 32 141.613
12 32 139.692
13 32 137.425
14 32 137.227
15 32 134.669
16 32 128.277
17 32 127.440
18 32 121.224
19 32 121.915
20 32 120.740
21 32 118.562
22 32 116.271
23 32 113.883
24 32 113.558
25 32 109.293
26 32 108.782
27 32 108.796
28 32 105.684
29 32 103.614
30 32 102.232
31 32 100.514
32 32 99.339
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RI oddness
Jan Wieck [EMAIL PROTECTED] writes: What'd be easy is this: - We already have two entry points for INSERT/UPDATE on FK table, but the one for UPDATE is fortunately unused. - We change analyze.c to install the RI_FKey_check_upd trigger if the constraint has an ON DELETE SET DEFAULT clause. Otherwise it uses RI_FKey_check_ins as it does now. Unfortunately, such a fix really isn't going to fly as a patch release. Not only does it not work for existing tables, but it won't work for tables created by dump and reload from a prior version (since they won't have the right set of triggers ... another illustration of why the lack of an abstract representation of the RI constraints was a Bad Move). In fact I'm afraid that your proposed change would actively break tables imported from a prior version; wouldn't RI_FKey_check_ins do the wrong thing if applied as an update trigger? I think the usage of ON DELETE SET DEFAULT is a very rare case out in the field. Thus the dump/reload requirement is limited to a small number of databases (if any). But dump/reload won't fix the tables' triggers. Given that ON DELETE SET DEFAULT isn't used much, I think we should not waste time creating an incomplete hack solution for 7.1.*, but just write it off as a known bug and move forward with a real solution for 7.2. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] RI oddness
Tom Lane wrote: Jan Wieck [EMAIL PROTECTED] writes: What'd be easy is this: - We already have two entry points for INSERT/UPDATE on FK table, but the one for UPDATE is fortunately unused. - We change analyze.c to install the RI_FKey_check_upd trigger if the constraint has an ON DELETE SET DEFAULT clause. Otherwise it uses RI_FKey_check_ins as it does now. Unfortunately, such a fix really isn't going to fly as a patch release. Not only does it not work for existing tables, but it won't work for tables created by dump and reload from a prior version (since they won't have the right set of triggers ... another illustration of why the lack of an abstract representation of the RI constraints was a Bad Move). In fact I'm afraid that your proposed change would actively break tables imported from a prior version; wouldn't RI_FKey_check_ins do the wrong thing if applied as an update trigger? I think the usage of ON DELETE SET DEFAULT is a very rare case out in the field. Thus the dump/reload requirement is limited to a small number of databases (if any). But dump/reload won't fix the tables' triggers. Ech - you're right. It wouldn't fix 'em. Given that ON DELETE SET DEFAULT isn't used much, I think we should not waste time creating an incomplete hack solution for 7.1.*, but just write it off as a known bug and move forward with a real solution for 7.2. It's not the rarely used ON DELETE SET DEFAULT case that's currently broken. It's ALL the other cases that can easily cause you to end up in deadlocks if you just update another field in a table having foreign keys and you don't lock all referenced rows properly first. Given the table: CREATE TABLE sample ( a integer REFERENCES t1, b integer REFERENCES t2, c integer REFERENCES t3, d integer REFERENCES t4, data text ); you'd have to SELECT ... FOR UPDATE tables t1, t2, t3 and t4 (while NOT having a lock on sample) before you can safely update data. Otherwise, another transaction could lock one of those and try to lock your sample row and you have a deadlock. We could provide another script fixing it. It is run after the restore of a dump taken from a pre-7.1.1 database fixing the tgfoid for those triggers that use RI_FKey_check_ins where a matching RI_FKey_setdefault_del row exist with same arguments and constraint name. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: unanswered: Schema Issue
On Thu, 26 Apr 2001, V. M. wrote: ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. Yes you can. Read my tutorial on Referential Integrity in the top section at techdocs.postgresql.org. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Cursor support in pl/pg
Ian Lance Taylor wrote: Jan Wieck [EMAIL PROTECTED] writes: I don't object if we can be sure that it's implementing the syntax a final version with *real* cursor support will have. Can we? I don't know, and I don't know what the decision criteria are. I intentionally implemented the Oracle cursor syntax. PL/pgSQL is very similar to PL/SQL, and I didn't see any reason to introduce a spurious difference. Note in particular that simply passing OPEN/FETCH/CLOSE through to the Postgres SQL parser does not implement the Oracle cursor syntax, so I wouldn't have done that even if it would have worked. Maybe it's very similar because I had an Oracle PL/SQL language reference at hand while writing the grammar file, maybe it's just by accident :-) (I have a vested interest here. For various reasons, my company, Zembu, has an interest in minimizing the strain of porting applications from Oracle to Postgres. I assume that the Postgres team also has that interest, within reason. But I don't know for sure.) Who hasn't? O.K., you convinced me. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: unanswered: Schema Issue
ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. From: Joel Burton [EMAIL PROTECTED] To: V. M. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: unanswered: Schema Issue Date: Thu, 26 Apr 2001 13:51:26 -0400 (EDT) On Thu, 26 Apr 2001, V. M. wrote: I want to extract tables schema information, i've looked at src/bin/psql/describe.c but i cannot determine the datatype 'serial' and 'references' from pg_*, i understand that triggers are generated for serial and references, so how i can understand from my perl application the full schema ? SERIALs are just integers (int4). They don't use a trigger, but use a sequence as a default value. REFERENCES are not a type of data, but a foreign key/primary key relationship. There's still a data type (int, text, etc.) You can derive schema info from the system catalogs. Use psql with -E for examples, or look in the Developer Manual. HTH, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: unanswered: Schema Issue
read it, but i can determine only the related tables and not the fields of these tables that are related. valter From: Joel Burton [EMAIL PROTECTED] To: V. M. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: [HACKERS] Re: unanswered: Schema Issue Date: Thu, 26 Apr 2001 14:42:31 -0400 (EDT) On Thu, 26 Apr 2001, V. M. wrote: ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. Yes you can. Read my tutorial on Referential Integrity in the top section at techdocs.postgresql.org. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] status after 7.1 and pgcrypto update / crypt(table.field) ?
On Thu, Apr 26, 2001 at 05:20:53PM +0200, Peter Eisentraut wrote: will trillich writes: i know password can be used in creating/altering user information (as used via GRANT and REVOKE) but is there any facility within postgres to CRYPT() a value? See contrib/pgcrypto for hashing functions. Problem is the hashing functions are not good for password storage. A general question: what is the status on patch acceptance now, after 7.1 is successfully released? I did not want to fuzz around with new code when 7.1 was in freeze, but what is the status now? Specifically - pgcrypto current state: In the pgsql/contrib: * digest() / encode() - stable. In my pgcrypto separate release: * digest() / encode() / hmac() - stable. I have changed the internal interfaces compared to main CVS. * crypt() / gen_salt() - stable. DES/MD5/Blowfish crypt() (Blowfish is unreleased). Code seems to be working quite well. * encrypt() / decrypt() - unstable. Not in the 'buggy'-sense, but the 0.3 encrypt() is unsatisfactory for long-term storage and security and compatibility. Also their spec is confusing to users. In the next release they will be renamed raw_encrypt() / raw_decrypt() as they really are interfaces to raw ciphers. I keep them coz they are good for testing pgcrypto code ;) and also they are ok for crypting short strings. * future: encrypt() / decrypt() will be minimal implementation of OpenPGP standard (RFC2440). Symmetrically Encrypted Data with passwords. (Is it too big? - The crypted data needs some structure and I dont think inventing some own format is good.) Now for this OpenPGP stuff I dont have ATM not even alpha-quality code. So full release takes some time. But hmac() and crypt() code is quite ok and there is no point on me sitting on it alone. So I would like to submit the mostly ready parts to main tree. When is the right time for it? -- marko ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: unanswered: Schema Issue
perhaps adding t.tgargs to your view enable me to extract parameters that are the related fields --- CREATE VIEW dev_ri AS SELECT * t.tgargs , t.oid as trigoid, c.relname as trig_tbl, t.tgfoid, f.proname as trigfunc, t.tgenabled, t.tgconstrname, c2.relname as const_tbl, t.tgdeferrable, t.tginitdeferred FROM pg_trigger t, pg_class c, pg_class c2, pg_proc f WHERE t.tgrelid=c.oid AND t.tgconstrrelid=c2.oid AND tgfoid=f.oid AND tgname ~ '^RI_' ORDER BY t.oid; a tgargs example is: fk_provincie_id_paesi_id_provin\000paesi\000province\000UNSPECIFIED\000id_provincia\000id\000 first field (fk_provincie_id_paesi_id_provin) is constraint name, and i can understand that: paesi(id_provincia) references provincia(id). valter From: Joel Burton [EMAIL PROTECTED] To: V. M. [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: [HACKERS] Re: unanswered: Schema Issue Date: Thu, 26 Apr 2001 14:42:31 -0400 (EDT) On Thu, 26 Apr 2001, V. M. wrote: ok for serials, now i can extract from psql (\d tablename). But i'm not able to extract foreign keys from the schema. Yes you can. Read my tutorial on Referential Integrity in the top section at techdocs.postgresql.org. -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] zpbit_in does not receive correct atttypmod
When I create a table create table test (a bit(4)); and insert a value insert into test values (b'11'); the zpbit_in() function gets an atttypmod (arg 2 (of 2)) of -1. Is there somewhere the system needs to be told that this type uses the atttypmod field? -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] status after 7.1 and pgcrypto update / crypt(table.field) ?
Marko Kreen [EMAIL PROTECTED] writes: A general question: what is the status on patch acceptance now, after 7.1 is successfully released? I did not want to fuzz around with new code when 7.1 was in freeze, but what is the status now? We're still in bug-fixes-only mode. I think the plan is to fork off a 7.1 stable branch next week, and after that the floodgates will be open for 7.2 development. 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] zpbit_in does not receive correct atttypmod
Peter Eisentraut [EMAIL PROTECTED] writes: When I create a table create table test (a bit(4)); and insert a value insert into test values (b'11'); the zpbit_in() function gets an atttypmod (arg 2 (of 2)) of -1. Is there somewhere the system needs to be told that this type uses the atttypmod field? No. I don't believe this is broken, either --- the call is coming from make_const() which has no reason to try to coerce the constant to a particular length. Coercion to the target column width will happen later (quite a bit later), when zpbit() is called. See coerce_type_typmod in parse_coerce.c. 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] PAM Authentication for PostgreSQL...
A couple {days,weeks} ago, someone sent an email to one of the pgsql-*
lists asking if anybody had thought about implementing the glue to use PAM
as authentication method for PostgreSQL. Having thought about being able
to easily drop in various external authentication agents, I've been
thinking about using PAM for PostgreSQL for a while... The recent thread
inspired me, and I have now finished (and tested - imagine that :) the
code.
I vaguely remember there were a few points brought up for discussion
during the short thread - unfortunately I was unable to find it in the
archives (the search somehow seems not to be working (anymore)) - and I
deleted all but one email - the one from Peter:
Peter Eisentraut writes:
Konstantinos Agouros writes:
I would really like to be able to use external authentication-methods
(the password not the itself) to avoid setting up pass- words.
What particular method that does not use passwords are you interested in?
I think is question should be read as ... to avoid having to set up local
postgresql passwords.
... Imagine the following scenario:
Relatively large enterprise (6000+ employees), where several departments
have a need to use databases of various kinds. (Currently, unfortunately,
all Access Shared filesystem databases... Yuk.)
Nice shiny PostgreSQL server sitting in the corner with lots of
(currently) free disk space on it - places where, through ODBC, we could
stuff the data from all these access databases, and 1) get them off the
network (and off IPX), and 2) central repository that is easy to back up,
administrate, etc...
Now, it would be annoying to have to maintain local passwords for
PostgreSQL for all of the X number of users who will be having tablespace
on this server. This would be an excellent place for PAM, in cooperation
with something like pam_ldap - the module that lets PAM authenticate into
LDAP (which, in our case, sits on top of NDS, and contains all the
user/etc information.)
I have several other examples where this could come in handy (Oddly
enough, most of them involving LDAP... imagine that. :)
Is there any discussion before I submit the patch to -patches?
-Dominic
--
Dominic J. Eidson
Baruk Khazad! Khazad ai-menu! - Gimli
---
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: PAM Authentication for PostgreSQL...
Is there any discussion before I submit the patch to -patches? Since we can, or should be able to, run postgres as a backend to ldap, this seems to give a wonderfully circular system (which probably works just fine). Just a comment... - Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] applications hang when calling 'vacuum'
On Thursday 26 April 2001 23:36, Barnes, Sandy (Sandra) wrote: We are currently calling 'vacuum' from within our code, using the psql++ PgDatabase ExecCommandOk() to send the SQL statement 'vacuum'. I seems to work when we only have one process running, but when two processes (both invoking the vacuum) are running it immediately hangs. Some postgres processes exist... one 'idle' and one 'VACUUM'. The applications hang. I tried using the PgTransaction class to invoke the SQL but received an error stating that vacuum cannot be run in a transaction. Any ideas? Can confirm this. This is also the case on my systems. I already told about this some time ago. There was no reply... -- Sincerely Yours, Denis Perchine -- E-Mail: [EMAIL PROTECTED] HomePage: http://www.perchine.com/dyp/ FidoNet: 2:5000/120.5 -- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] 7.1 startup recovery failure
Hi, There's a report of startup recovery failure in Japan. Redo done but ... Unfortunately I have no time today. regards, Hiroshi Inoue KAMI wrote: DEBUG: database system shutdown was interrupted at 2001-04-26 22:15:00 JST DEBUG: CheckPoint record at (1, 3923829232) DEBUG: Redo record at (1, 3923829232); Undo record at (0, 0); Shutdown TRUE DEBUG: NextTransactionId: 7473265; NextOid: 2550911 DEBUG: database system was not properly shut down; automatic recovery in progress... DEBUG: redo starts at (1, 3923829296) DEBUG: ReadRecord: record with zero len at (1, 3923880136) DEBUG: redo done at (1, 3923880100) FATAL 2: XLogFlush: request is not satisfied postmaster: Startup proc 4228 exited with status 512 - abort ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] 7.1 vacuum
How does 7.1 work now with the vacuum and all? Does it go for indexes by default, even when i haven't run a vacuum at all? Does vacuum lock up postgres? It says the analyze part shouldn't, but how's that for all of the vacuum? An 7.0.3 db we have here we are forced to run vacuum every hour to get an acceptable speed, and while doing that vacuum (5-10 minutes) it totaly blocks our application that's mucking with the db. Just curious Magnus Naeslund ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] 7.1 startup recovery failure
There's a report of startup recovery failure in Japan. Redo done but ... Unfortunately I have no time today. Please ask to start up with wal_debug = 1... Vadim ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Re: [HACKERS] Re: unanswered: Schema Issue
On Thu, 26 Apr 2001, V. M. wrote: (moving this conversation back to pgsql-general, followups to there) perhaps adding t.tgargs to your view enable me to extract parameters that are the related fields At SCW, we use a naming convention for RI triggers, to allow us to easily extract that, and deal with error messages. We use: CREATE TABLE p (id INT); CREATE TABLE c (id INT CONSTRAINT c__ref_id REFERENCES p); This allows us at a glance to see in error messages what field of what table we were referencing. In an Access front end, we can trap this error message to a nice statement like You're trying to change a value in the table c, using information in table p, id, but...) If you don't have this, yes, you can look at in the tgargs, but, given that its a bytea field, it's hard to programmatically dig anything out of it. HTH, -- Joel Burton [EMAIL PROTECTED] Director of Information Systems, Support Center of Washington ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: [GENERAL] Re: Hardcopy docs available
On Tue, 24 Apr 2001, Thomas Lockhart wrote: If someone wants to run the A4 docs through a PDF converter, send 'em to me and I'll post them too. I just did the userA4 pdf from hub with this: $ gzcat userA4.ps.gz | ps2pdf - userA4.pdf Do you want me to do the rest of them? Or we can probably have the makindex script do it for us. Vince. -- == Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net 56K Nationwide Dialup from $16.00/mo at Pop4 Networking Online Campground Directoryhttp://www.camping-usa.com Online Giftshop Superstorehttp://www.cloudninegifts.com == ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] applications hang when calling 'vacuum'
We are currently calling 'vacuum' from within our code, using the psql++ PgDatabase ExecCommandOk() to send the SQL statement 'vacuum'. I seems to work when we only have one process running, but when two processes (both invoking the vacuum) are running it immediately hangs. Some postgres processes exist... one 'idle' and one 'VACUUM'. The applications hang. I tried using the PgTransaction class to invoke the SQL but received an error stating that vacuum cannot be run in a transaction. Any ideas? Sandy Barnes email[EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
