Re: [HACKERS] random system table corruption ...
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: in the past we have faced a couple of problems with corrupted system tables. this seems to be a version independent problem which occurs on hackers' from time to time. i have checked a broken file and i have seen that the corrupted page has actually been zeroed out. That sounds to me like a hardware problem --- disk or disk controller momentarily writing zeroes instead of what it should write. Have you seen this on more than one physical machine? Do you have any evidence for the implication that it only happens to system tables and not user tables? Also, you don't have zero_damaged_pages turned on by any chance? regards, tom lane tom, well, there is some evidence that this is not a hardware related issue. we have only seen this problem from time to time but it happened on different machines. it cannot be reproduced. it can even happen when somebody runs a script which has been called million times before. in my current scenario the page header only consists of 0x00 bytes and therefore the page checks fails when reading the system table. i have never seen this in data files up to now (at least not when the hardware was still intact). did anybody face similar problems? maybe on sun? by the way: currently the broken system is running PostgreSQL 7.4 but as I said - we have also seen that on 8.0 once. best regards, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] random system table corruption ...
Alvaro Herrera wrote: On Sun, Sep 11, 2005 at 01:12:34PM +0200, Hans-Jürgen Schönig wrote: in the past we have faced a couple of problems with corrupted system tables. this seems to be a version independent problem which occurs on hackers' from time to time. i have checked a broken file and i have seen that the corrupted page has actually been zeroed out. IIRC the XFS filesystem zeroes out pages that it recovers from the journal but did not have a fsync on them (AFAIK XFS journals only metadata, so page creation but not the content itself). I don't think this would be applicable to your case, because we do fsync modified files on checkpoint, and rewrite them completely from WAL images after that. But I thought I'd mention it. alvora, thanks a lot. we have some reports about sun systems. meanwhile i got the impression that the filesystem might be doing something wrong. i have seen that the page is not completely zeroed out. at some strange positions there are 2 bytes of crap (i have overlooked that at first glance). the first couple hundreds of bytes are crap, however. very strange ... best regards, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Hide source code
Márcio A. Sepp wrote: Hi, I'm looking for a way to hide the souce code of my system (functions). In Oracle, I can wrap it. Is there something that I can use to hide and/or wrap my source code? Att. Márcio A. Sepp ZYON TECNOLOGIA LTDA Currently there is no way to do that. Best regards, Hans ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] commit_delay, siblings
Tom Lane wrote: Josh Berkus josh@agliodbs.com writes: I've been trying to get a test result for 8.1 that shows that we can eliminate commit_delay and commit_siblings, as I believe that these settings no longer have any real effect on performance. I don't think they ever did :-(. The theory is good, but useful values for commit_delay would probably be under a millisecond, and there isn't any portable way to sleep for such short periods. We've been leaving them there just in case somebody can find a use for 'em, but I wouldn't object to taking them out. 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]) We have done extensive testing some time ago. We could not see any difference on any platform we have tested (AIX, Linux, Solaris). I don't think that there is one at all - at least not on common systems. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Autovacuum in the backend
* Reduces the total amount of time the system spends vacuuming since it only vacuums when needed. Can be easily done with cron. * Keeps stats up-to-date automatically Which can be done with cron * Eliminates newbie confusion RTFM * Eliminates one of the criticisms that the public has against PostgreSQL (justifed or not) Agreed. I few weeks ago I have set up a database with more than 1.800 tables (some complex business thing). inventing a clever cron-vacuum strategy is almost impossible (or at least very painful). there should be a mechanism (fortunately there is pg_autovacuum) to make this a bit more practical. in case of small databases this is not an issue. small is always simple. complex and large are the major challenges. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Autovacuum in the backend
2) By no fault of its own, autovacuum's level of granularity is the table level. For people dealing with non-trivial amounts of data (and we're not talking gigabytes or terabytes here), this is a serious drawback. Vacuum at peak times can cause very intense IO bursts -- even with the enhancements in 8.0. I don't think the solution to the problem is to give users the impression that it is solved and then vacuum their tables during peak periods. I cannot stress this enough. I completly agree with Gavin - integrating this kind of thing into the backend writer or integrate it with FSM would be the ideal solution. I guess everybody who has already vacuumed a 2 TB relation will agree here. VACUUM is not a problem for small my cat Minka databases. However, it has been a real problem on large, heavy-load databases. I have even seen people splitting large tables and join them with a view to avoid long vacuums and long CREATE INDEX operations (i am not joking - this is serious). postgresql is more an more used to really large boxes. this is an increasing problem. gavin's approach using a vacuum bitmap seems to be a good approach. an alternative would be to have some sort of vacuum queue containing a set of pages which are reported by the writing process (= backend writer or backends). best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] Autovacuum in the backend
Alvaro Herrera wrote: On Thu, Jun 16, 2005 at 04:20:34PM +1000, Gavin Sherry wrote: 2) By no fault of its own, autovacuum's level of granularity is the table level. For people dealing with non-trivial amounts of data (and we're not talking gigabytes or terabytes here), this is a serious drawback. Vacuum at peak times can cause very intense IO bursts -- even with the enhancements in 8.0. I don't think the solution to the problem is to give users the impression that it is solved and then vacuum their tables during peak periods. I cannot stress this enough. People running systems with petabyte-sized tables can disable autovacuum for those tables, and leave it running for the rest. Then they can schedule whatever maintenance they see fit on their gigantic tables. Trying to run a database with more than a dozen gigabytes of data without expert advice (or at least reading the manual) would be extremely stupid anyway. professional advice won't help you here because you still have to vacuum this giant table. this is especially critical in case of 24x7 systems (which are quite frequent). in many cases there is no maintenance window anymore (e.g. a wastewater system will be only 24x7). reducing the impact of vacuum and create index would be important to many people. to me improving vacuum it is as important as Jan's bgwriter patch (it reduces the troubles people had with checkpoints). best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] executing OS programs from pg
Gevik babakhani wrote: Dear people, Does anyone know how to execute an OS command from pgsql. I would like to create a trigger that op on firing would run/execute an external program. Does such functionality exist or do I have to write my own trigger function in C. Reagrds, Gevik. Gevik, Do something like that ... CREATE OR REPLACE FUNCTION xclock() RETURNS int4 AS ' system(xclock); return 1; ' LANGUAGE 'plperlu'; This should be fairly easy to implement but recall - you cannot rollback xclock ;). best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] Tablespaces
Christopher Kings-Lynne wrote: I'm interested if anyone is using tablespaces? Do we have any actual reports of people actually using them, to advantage, in the field?? Maybe the next postgresql.org survey could be on tablespace usage? Chris I have seen that tablespaces are widely used and highly appreciated. I have not seen people complaining about the current implementation. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] NOLOGGING option, or ?
Personally I don't think that it is a good idea to do that. People will tend to corrupt their systems because they want speed (sometimes without thinking about the consequences). I can only think of one scenario where nologging would actually make sense: Many people use session tables to keep track of user level information on a website. corrupting a session table (usually not very large) would not cause a lot of problems. Doing it for COPY would be fatal. I can tell you from experience that 80% of all users will use that if the manual says that PostgreSQL will beform better this way. This is a key feature to make people think that PostgreSQL is reliable. Best regards, Hans Simon Riggs wrote: Recent test results have shown a substantial performance improvement (+25%) if WAL logging is disabled for large COPY statements. This is to be expected, though has a price attached: losing the ability to crash recover data loaded in this manner. There are two parts to this proposal. First, when and whether to do this at all. Second, syntax and invocation. Why? Performance. The performance gain has a price and so should only be enabled if requested explicitly by the user. It is up to the user whether they accept this price, since in many useful cases it is a small price against a huge saving. The price is that if a crash occurs, then any table that was not empty to begin with would not be in a transactionally consistent state following crash recovery. It may have data in it, but it would be up to the user to determine whether that was satisfactory or not. It could be possible to sense what to do in this situation automatically, by putting the table into a needs-recovery type state... I don't propose to handle this *at this stage*. Syntax and invocation: Previously I had discussed adding a NOLOGGING option onto both COPY and CREATE TABLE AS SELECT that would bypass the creation of wal logging data. That is still a possibility, but would require manual code changes to much of the SQL submitted. Now, I would like to discuss adding an enable_logging USERSET GUC, that would apply *only* to COPY and CREATE TABLE AS SELECT. The default of this would be false. How can we gain this performance benefit for those willing to accept the restrictions imposed? Your comments are sought and are most welcome. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] regarding storage in postgres
Nithin Sontineni wrote: Hi, i want to know how postgres will store a relation in pages and where can i see the code related to this in the source code.Plz help me . S.Nithin __ Discover Yahoo! Use Yahoo! to plan a weekend, have fun online and more. Check it out! http://discover.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Nithin, You can find the code in src/backend/storage. There you will find a couple of directories containing code of various parts of the storage system. I guess you will be most interested in page/bufpage.c. There you can see how a page works. I highly suggest to read the corresponding header file [EMAIL PROTECTED] storage]$ pwd /usr/src/pg/postgresql-8.0.2/src/include/storage [EMAIL PROTECTED] storage]$ ls -l bufpage.h -rw-r--r-- 1 hs hs 10255 Dec 31 23:03 bufpage.h There you can find information about the layout of pages. best regards, hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] WAL replay failure after file truncation(?)
Tom Lane wrote: Manfred Koizar [EMAIL PROTECTED] writes: [...] Is it sufficient to remember just the relation and the block number or do we need the contents a well? I meant the contents of the WAL record, not the original block contents. Anyway, I think it's not needed. Oh, I see. Yes, it might be worth hanging onto for debugging purposes. If we did get a report of such a failure, I'm sure we'd wish to know what sort of WAL record triggered it. One trusts there won't be so many that storing 'em all is a problem ... 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 I guess I am having the same problem here: I am just dealing with a truncated table after a hard kill. The symptoms are: The storage file of the table is missing while the system tables can still see the table. Looking at TRUNCATE (this is the only command which could potentially have caused this problem in my case) it seems as if the system tables are actually changed propery before the file on disk is truncated. My question is: What happens if the system is killed inside rebuild_relation or inside swap_relfilenodes which is called by rebuild_relation? many thanks and best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] WAL replay failure after file truncation(?)
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: My question is: What happens if the system is killed inside rebuild_relation or inside swap_relfilenodes which is called by rebuild_relation? Nothing at all, because the system catalog updates aren't committed yet, and we haven't done anything to the relation's old physical file. This is actually what I expected. I have gone through the code and it looks correct. TRUNCATE is the only command in this application which can potentially cause the problem (it is very unlikely that INSERT removes a file). If I were you I'd be looking into whether your disk hardware honors write ordering properly. This sounds like something allowed the directory change to reach disk before the transaction commit WAL record did; which is impossible if fsync is doing what it's supposed to. regards, tom lane We are on sun Solaris (x86) box here. I am not sure what Sun has corrupted to make this error happen. Obviously it happens only once per 1.000.000 tries ... I am just trying to figure out whether the bug could potentially be inside PostgreSQL. It would have been surprised if somebody had overseen a problem like that. many thanks and best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(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] REINDEX ALL
Joshua D. Drake wrote:
Hello,
We are considering submitting a patch for REINDEX ALL. What syntax would
we like?
REINDEX ALL?
REINDEX DATABASE ALL?
Sincerely,
Joshua D. Drake
-- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564
Custom programming, 24x7 support, managed services, and hosting
Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG
Reliable replication, Mammoth Replicator - http://www.commandprompt.com/
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Joshua,
What is this patch supposed to do?
As far as I can see, there is already a reindex command ...
test=# \h reindex
Command: REINDEX
Description: rebuild indexes
Syntax:
REINDEX { DATABASE | TABLE | INDEX } name [ FORCE ]
test=#
Best regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/664/393 39 74
www.cybertec.at, www.postgresql.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] [HACKERS] plPHP in core?
In the past couple of years a lot of stuff has been removed from the core - even the ODBC driver (which is ways more important than, let's say, PL/PHP) has been removed from the core - so why should a new PL be integrated now if considerably more important components will remain external? Best regards, Hans Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: I'm not convinced that PLs are more tied to the core than say OpenFTS, and if we can't maintain that kind of thing externally, then this whole extension thing sounds like a failure to me. It's *possible* to do it. Whether it's a net savings of effort is questionable. For instance, I've had to hack plperl and plpgsql over the past couple days to support OUT parameters, and the only reason I didn't have to hack the other two standard PLs is that they are a few features shy of a load already. I'm pretty sure pl/r and pl/java will need changes to support this feature too. If they were in core CVS then I'd consider it part of my responsibility to fix 'em ... but they aren't, so it isn't my problem, so it falls on Joe and Thomas to get up to speed on what I've been doing and do likewise. Is that really a win? The point here is really that we keep finding reasons to, if not flat-out change the interface to PLs, at least expand their responsibilities. Not to push it too hard, but we still have only one PL with a validator procedure, which IIRC was your own addition to that API. How come they don't all have validators? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/664/393 39 74 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Cost of XLogInsert CRC calculations
One of the things I was thinking about was whether we could use up those cycles more effectively. If we were to include a compression routine before we calculated the CRC that would - reduce the size of the blocks to be written, hence reduce size of xlog - reduce the following CRC calculation I was thinking about using a simple run-length encoding to massively shrink half-empty blocks with lots of zero padding, but we've already got code to LZW the data down also. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Simon, I think having a compression routine in there could make real sense. We have done some major I/O testing involving compression for a large customer some time ago. We have seen that compressing / decompressing on the fly is in MOST cases much faster than uncompressed I/O (try a simple cat file | ... vs. zcat file.gz | ...) - the zcat version will be faster on all platforms we have tried (Linux, AIX, Sun on some SAN system, etc. ...). Also, when building up a large database within one transaction the xlog will eat a lot of storage - this can be quite annoying when you have to deal with a lot of data). Are there any technical reasons which would prevent somebody from implementing compression? Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Question about Unrecognized SPI code ...
I just found an interesting issue in recent PostgreSQL releases: CREATE VIEW view_nonsense AS SELECT 1 AS a, 2 AS b; CREATE RULE myrule AS ON INSERT TO view_nonsense DO INSTEAD NOTHING; CREATE OR REPLACE FUNCTION debug() RETURNS boolean AS ' DECLARE BEGIN INSERT INTO view_nonsense VALUES (10, 20); RETURN true; END; ' LANGUAGE 'plpgsql'; SELECT debug(); The INSERT statement is not doing something useful here [EMAIL PROTECTED] tmp]$ psql test view.sql CREATE VIEW CREATE RULE CREATE FUNCTION ERROR: SPI_execute_plan failed executing query INSERT INTO view_nonsense VALUES (10, 20): Unrecognized SPI code 0 CONTEXT: PL/pgSQL function debug line 4 at SQL statement SPI_result_code_string(int code) and PL/pgSQL don't seem to be aware of DO NOTHING rules. Is it desired behaviour? Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Connect By for 8.0
Neil Conway wrote: Robert Treat wrote: Actually i believe people want both syntax's as the former is used by oracle and the latter by db2 (iirc) I think the past consensus has been to adopt the SQL standard syntax. Is there any reason to also support the Oracle syntax other than for compatibility? (And if that is it, I think it's a pretty flimsy reason.) -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) Neil, Compatibility is the ONLY reason and it is related to money (the only language people understand). We have done a lot of migration here and I can tell you that support for Oracle style CONNECT BY would make more people happy than the SQL style syntax. The reason for that is very simple: Code can be migrated without any changes (= without introducing potential bugs). I know that SQL standards are tremendously important but if there is an easy way to support Oracle syntax as well this is definitely the preferred way to go. I think it is important not to think in dogmas (in this case this means SQL syntax is always better) - there should be a reasonable compromise between compatibility and standard. My compromise would be: Support both syntaxes if possible. Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] some linker troubles with rc5 on sun studio 9 ...
Tom Lane wrote:
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes:
tuptoaster.c, line 966: member can not have variably modified type: data
We've seen that before. Apparently there are some versions of Sun's
compiler that are too stupid to reduce this constant expression to a
constant. File a bug with Sun.
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
As Tom pointed it this is truly a compiler bug of zthe compiler included
in Sun Studio 9. It seems as if Sun is attempting to fix that.
Here is a test case which will fail on Sun CC 5.6.
#include stddef.h
#include stdio.h
typedef struct {
int s1;
char data[10];
unsigned char bits[1];
} Ss;
int main(int argc, char **argv){
struct {
int ap;
char data[offsetof(Ss,bits)];
}s1;
printf(offsetof: %d, sizeof %d\n,
offsetof(Ss, bits),
sizeof(s1.data));
return 0;
}
When I'm using Studio 9 cc, compilation failed:
/opt/F9/SUNWspro/bin/cc -V -Xa -o test test.c
cc: Sun C 5.6 2004/07/15
acomp: Sun C 5.6 2004/07/15
test.c, line 14: member can not have variably modified type: data
cc: acomp failed for test.c
8.0.0 compiles now with --disable-spinlocks.
When spinlocks are enabled we will see one more problem ...
gmake[4]: Entering directory
`/usr/share/src/pg/postgresql-8.0.0/src/backend/storage/lmgr'
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o lmgr.o lmgr.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o lock.o lock.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o proc.o proc.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o
deadlock.o deadlock.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o lwlock.o
lwlock.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o spin.o spin.c
/opt/SUNWspro/bin/cc -Xa -v -g -I../../../../src/include -c -o s_lock.o
s_lock.c
/opt/SUNWspro/prod/bin/fbe: /tmp/yabeAAAQ.aaFl, line 277: error: can't
compute difference between symbols in different segments
Failure in /opt/SUNWspro/prod/bin/fbe, status = 0x100
Fatal Error exec'ing /opt/SUNWspro/prod/bin/fbe
cc: acomp failed for s_lock.c
gmake[4]: *** [s_lock.o] Error 2
gmake[4]: Leaving directory
`/usr/share/src/pg/postgresql-8.0.0/src/backend/storage/lmgr'
gmake[3]: *** [lmgr-recursive] Error 2
gmake[3]: Leaving directory
`/usr/share/src/pg/postgresql-8.0.0/src/backend/storage'
gmake[2]: *** [storage-recursive] Error 2
gmake[2]: Leaving directory `/usr/share/src/pg/postgresql-8.0.0/src/backend'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/share/src/pg/postgresql-8.0.0/src'
gmake: *** [all] Error 2
The nice thing is: This error is not documented and we have not found a
flag to preserve /tmp/yabeAAAQ.aaFl (which makes this error very useful).
google does not contain information about this issue because all four
sites I have found are down.
Has anybody ever seen something like that before?
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/660/816 40 77
www.cybertec.at, www.postgresql.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Two-phase commit for 8.1
Heikki, What is still missing to complete the 2PC patch?. Regards, Hans Heikki Linnakangas wrote: On Wed, 19 Jan 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: If the patch is ready to be committed early in the cycle, I'd say most definitely ... just depends on how late in the cycle its ready ... My recollection is that it's quite far from being complete. I had hoped to spend some time during the 8.1 cycle helping Heikki finish it up, but if we stick to the 2-month-dev-cycle idea I'm afraid there's no way it'll be done in time. I thought that some time would probably amount to a solid man-month or so, and there's no way I can spend half my time on just one feature for this cycle. If Heikki wants this in for 8.1, the right thing to do is vote against the short-dev-cycle idea. But we need a plausible answer about what to do about ARC to make that credible... I'm not sure what I want. If the 8.1 cycle really is a short one, say 3 months, then I have no problem waiting for 8.2. But we have a very bad track record regarding short-dev-cycles. I honestly don't believe we can get 8.1 released before July. - Heikki ---(end of broadcast)--- TIP 8: explain analyze is your friend -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ARC patent
I think the ARC issue is the same with any other patent ... Recently somebody pointed me to a nice site showing some examples: http://www.base.com/software-patents/examples.html Looking at the list briefly I can find at least five patent problems using any operating system with PostgreSQL. From my point of view having the ARC in there is just as safe / unsafe as using Hello World and compile it with GCC. I don't think it possible to sue a community anyway. Best regards and have fun reading those examples, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/660/816 40 77 www.cybertec.at, www.postgresql.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] some linker troubles with rc5 on sun studio 9 ...
i have encountered some problems with sun studio 9 (version 8 always
worked for me).
obviously it does not like my linker flags ...
when running the following script I get ...
#!/bin/sh
LD_LIBRARY_PATH=/opt/sfw/lib:/usr/local/lib:$LD_LIBRARY_PATH
# PATH=/opt/SUNWspro/bin/:/opt/sfw/bin:$PATH:/usr/ccs/bin
PATH=/usr/ccs/bin/:/opt/SUNWspro/bin/:/opt/sfw/bin::/usr/sbin:/usr/bin
gmake distclean
CC='/usr/ucb/cc'
export CC
export PATH
export LD_LIBRARY_PATH
./configure --prefix=/usr/local/pgsql --disable-rpath --enable-debug
--without-readline --without-gnu-ld
the right compiler and linker are chosen ...
checking whether we are using the GNU C compiler... no
checking whether /usr/ucb/cc accepts -g... yes
configure: using CFLAGS=-v -g
checking whether the C compiler still works... yes
checking how to run the C preprocessor... /usr/ucb/cc -Xa -E
...
checking for flex... /opt/sfw/bin/flex
checking whether ln -s works... yes
checking for non-GNU ld... /usr/ccs/bin//ld
checking if the linker (/usr/ccs/bin//ld) is GNU ld... no
checking for ranlib... ranlib
running gmake will leave me with an error ...
/opt/SUNWspro/prod/bin/acomp -Qy -y-o -yhio.o -I../../../../src/include
-I/usr/ucbinclude -g /opt/SUNWspro/prod/bin/acc -Xs
-YP,:/usr/ucblib:/opt/SUNWspro/prod/bin/../lib:/opt/SUNWspro/prod/bin:/usr/ccs/lib:/usr/lib
-Xa -v -g -I../../../../src/include -c -I/usr/ucbinclude -c -b -y-fbe
-y/opt/SUNWspro/prod/bin/fbe -y-xarch=generic -y-xmemalign=4s -y-verbose
-Xa -D__SunOS_5_8 -D__SUNPRO_C=0x560 -Dunix -Dsun -Dsparc -D__unix
-D__sun -D__sparc -D__BUILTIN_VA_ARG_INCR -D__SVR4 -D__RESTRICT
-D__PRAGMA_REDEFINE_EXTNAME -dg -y-g -I/opt/SUNWspro/prod/include/cc
-destination_ir=yabe -i hio.c
/usr/ucbinclude/sys/signal.h, line 223: warning: typedef redeclared:
sig_atomic_t
/usr/ucb/cc -Xa -v -g -I../../../../src/include -c -o tuptoaster.o
tuptoaster.c
ucbcc: Warning: Option
-YP,:/usr/ucblib:/opt/SUNWspro/prod/bin/../lib:/opt/SUNWspro/prod/bin:/usr/ccs/lib:/usr/lib
passed to ld, if ld is invoked, ignored otherwise
ucbcc: Warning: -Xa redefines compatibility mode from SunC
transition to ANSI
### command line files and options (expanded):
### -v -g -I../../../../src/include -c -o tuptoaster.o tuptoaster.c
-I/usr/ucbinclude -lucb -lsocket -lnsl -lelf -laio
### ucbcc: Note: NLSPATH =
/opt/SUNWspro/prod/bin/../lib/locale/%L/LC_MESSAGES/%N.cat:/opt/SUNWspro/prod/bin/../../lib/locale/%L/LC_MESSAGES/%N.cat
/opt/SUNWspro/prod/bin/acomp -Qy -y-o -ytuptoaster.o
-I../../../../src/include -I/usr/ucbinclude -g
/opt/SUNWspro/prod/bin/acc -Xs
-YP,:/usr/ucblib:/opt/SUNWspro/prod/bin/../lib:/opt/SUNWspro/prod/bin:/usr/ccs/lib:/usr/lib
-Xa -v -g -I../../../../src/include -c -I/usr/ucbinclude -c -b -y-fbe
-y/opt/SUNWspro/prod/bin/fbe -y-xarch=generic -y-xmemalign=4s -y-verbose
-Xa -D__SunOS_5_8 -D__SUNPRO_C=0x560 -Dunix -Dsun -Dsparc -D__unix
-D__sun -D__sparc -D__BUILTIN_VA_ARG_INCR -D__SVR4 -D__RESTRICT
-D__PRAGMA_REDEFINE_EXTNAME -dg -y-g -I/opt/SUNWspro/prod/include/cc
-destination_ir=yabe -i tuptoaster.c
/usr/ucbinclude/sys/signal.h, line 223: warning: typedef redeclared:
sig_atomic_t
tuptoaster.c, line 966: member can not have variably modified type: data
tuptoaster.c, line 1075: cannot recover from previous errors
gmake[4]: *** [tuptoaster.o] Error 10
gmake[4]: Leaving directory
`/usr/share/src/pg/postgresql-8.0.0rc5/src/backend/access/heap'
gmake[3]: *** [heap-recursive] Error 2
gmake[3]: Leaving directory
`/usr/share/src/pg/postgresql-8.0.0rc5/src/backend/access'
gmake[2]: *** [access-recursive] Error 2
gmake[2]: Leaving directory
`/usr/share/src/pg/postgresql-8.0.0rc5/src/backend'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/usr/share/src/pg/postgresql-8.0.0rc5/src'
gmake: *** [all] Error 2
I cannot find something unusual in tuptoaster.c ...
static Datum
toast_save_datum(Relation rel, Datum value)
{
Relationtoastrel;
Relationtoastidx;
HeapTuple toasttup;
InsertIndexResult idxres;
TupleDesc toasttupDesc;
Datum t_values[3];
chart_nulls[3];
varattrib *result;
struct
{
struct varlena hdr;
chardata[TOAST_MAX_CHUNK_SIZE];
} chunk_data;
int32 chunk_size;
int32 chunk_seq = 0;
when using the following configure options I will get a different error:
./configure --prefix=/usr/local/pgsql --enable-debug --without-readline
/usr/ucbinclude/sys/signal.h, line 223: warning: typedef redeclared:
sig_atomic_t
/usr/ucb/cc -Xa -v -g zic.o ialloc.o scheck.o localtime.o
-L../../src/port -Wl,-R/usr/local/pgsql/lib -lpgport -lz -lrt -lresolv
-lgen -lsocket -lnsl -ldl -lm -o zic
ucbcc: Warning: Option
-YP,:/usr/ucblib:/opt/SUNWspro/prod/bin/../lib:/opt/SUNWspro/prod/bin:/usr/ccs/lib:/usr/lib
passed to ld, if ld is invoked, ignored otherwise
ucbcc: Warning: -Xa redefines
[HACKERS] Interesting parsing problem ...
I am willing to add NOWAIT to a couple of commands and I have tried to resolve a bison problem for quite some time now: As a first step I wanted to add NOWAIT to DELETE: DELETE FROM ... WHERE ... NOWAIT; Therefore I used: /* * * QUERY: * DELETE STATEMENTS * */ DeleteStmt: DELETE_P FROM relation_expr where_clause opt_nowait opt_nowait is already defined and used by Tatsuo's LOCK TABLE NOWAIT. I got ... [EMAIL PROTECTED] parser]$ bison -y -d -v gram.y conflicts: 6 shift/reduce ... 6 errors: State 1197 conflicts: 1 shift/reduce State 1198 conflicts: 1 shift/reduce State 1201 conflicts: 1 shift/reduce State 1852 conflicts: 1 shift/reduce State 1853 conflicts: 1 shift/reduce State 1855 conflicts: 1 shift/reduce ... 1196 | BCONST 1197 | XCONST ... 1201 | TRUE_P ... state 1852 1024 b_expr: b_expr '%' . 1030 | b_expr '%' . b_expr ABORT_Pshift, and go to state 146 ABSOLUTE_P shift, and go to state 147 ACCESS shift, and go to state 148 ACTION shift, and go to state 149 The interesting thing here is - if I change opt_nowait to DeleteStmt: DELETE_P FROM relation_expr where_clause opt_lock (just for tesing), I still get an error ... [EMAIL PROTECTED] parser]$ bison -y -d -v gram.y conflicts: 1 shift/reduce In my understanding of bison DELETE ... WHERE ... IN some_mode MODE; should work ... Can anybody provide me a fix? Basically all I wanted to do was SELECT FOR UPDATE NOWAIT, DELETE NOWAIT and UPDATE ... NOWAIT. The rest of the patch seems to be fairly simple but can anybody lead me out of parser's hell? Best regards, Hans ---(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] is it a known issue or just a bug?
Folks,
Last week one of my students confronted me with a nice little SQL
statement which made me call gdb ...
Consider the following scenario:
[EMAIL PROTECTED] bug]$ cat q1.sql
create temporary sequence seq_ab;
select * from (Select nextval('seq_ab') as nv,
* from( select
t_product.id,t_text.value,t_price.price
fromt_product,t_price,t_text
where t_product.id = t_price.product_id
and t_product.name = t_text.id
and t_text.lang='de'
and t_price.typ = 'default'
order by price desc ) as t ) as u
-- WHERE nv = 1
;
[EMAIL PROTECTED] bug]$ psql test q1.sql
CREATE SEQUENCE
nv | id | value | price
++-+---
1 | 3 | Banane |12
2 | 1 | T-Shirt |10
3 | 2 | Apfel | 7
(3 rows)
this query returns the right result.
however, when uncommenting the WHERE clause things look different:
[EMAIL PROTECTED] bug]$ cat q2.sql
create temporary sequence seq_ab;
select * from (Select nextval('seq_ab') as nv,
* from( select
t_product.id,t_text.value,t_price.price
fromt_product,t_price,t_text
where t_product.id = t_price.product_id
and t_product.name = t_text.id
and t_text.lang='de'
and t_price.typ = 'default'
order by price desc ) as t ) as u
WHERE nv = 1
;
[EMAIL PROTECTED] bug]$ psql test q2.sql
CREATE SEQUENCE
nv | id | value | price
++-+---
4 | 1 | T-Shirt |10
(1 row)
Obviously nv = 4 is wrong ...
Looking at the execution plan of the second query the problem seems
quite obvious:
QUERY PLAN
Subquery Scan t (cost=69.24..69.26 rows=1 width=68)
- Sort (cost=69.24..69.25 rows=1 width=68)
Sort Key: t_price.price
- Hash Join (cost=22.51..69.23 rows=1 width=68)
Hash Cond: (outer.name = inner.id)
Join Filter: (nextval('seq_ab'::text) = 1)
- Nested Loop (cost=0.00..46.68 rows=5 width=40)
- Seq Scan on t_price (cost=0.00..22.50 rows=5
width=36)
Filter: (typ = 'default'::text)
- Index Scan using t_product_pkey on t_product
(cost=0.00..4.82 rows=1 width=8)
Index Cond: (t_product.id = outer.product_id)
- Hash (cost=22.50..22.50 rows=5 width=36)
- Seq Scan on t_text (cost=0.00..22.50 rows=5
width=36)
Filter: (lang = 'de'::text)
(14 rows)
nextval() is called again when processing the WHERE clause.
this was fine if nextval() would return the same thing again and again
(which is not the job of nextval).
if the planner materialized the subquery things would materialize the
subquery in case of unstable functions things would work in this case.
I know I temp table would easily fix this query and it is certainly not
the best query I have ever seen but still it seems like a bug and I just
wanted to know whether it is a know issue or not.
Looking at the code I did not quite know whether this is something which
should / can be fixed or not.
here is the data:
--
CREATE TABLE t_text (
id int4,
langtext,
value text
);
CREATE TABLE t_group (
id int4,
nameint4, -- mehrsprachig in t_text
valid boolean
);
INSERT INTO t_group VALUES (1, 1, 't');
INSERT INTO t_text VALUES (1, 'de', 'Obst');
INSERT INTO t_text VALUES (1, 'en', 'Fruits');
INSERT INTO t_group VALUES (2, 2, 't');
INSERT INTO t_text VALUES (2, 'de', 'Kleidung');
INSERT INTO t_text VALUES (2, 'en', 'Clothes');
CREATE UNIQUE INDEX idx_group_id ON t_group (id);
CREATE TABLE t_product (
id int4,
nameint4, -- mehrsprachig in t_text
active boolean,
PRIMARY KEY (id)
);
INSERT INTO t_product VALUES (1, 3, 't');
INSERT INTO t_text VALUES (3, 'de', 'T-Shirt');
INSERT INTO t_text VALUES (3, 'en', 'T-Shirt');
INSERT INTO t_product VALUES (2, 4, 't');
INSERT INTO t_text VALUES (4, 'de', 'Apfel');
INSERT INTO t_text VALUES (4, 'en', 'Apple');
INSERT INTO t_product VALUES (3, 5, 't');
INSERT INTO t_text VALUES (5, 'de', 'Banane');
INSERT INTO t_text VALUES (5, 'en', 'Banana');
CREATE TABLE t_product_group (
product_id int4REFERENCES t_product(id)
ON UPDATE CASCADE
[HACKERS] Interesting issue with SFR in PL/pgSQL ...
I am about to port a large database application from 7.4.x* to 8.0 (mainly to test 8.0). There is an interesting thing I have come across: CREATE OR REPLACE FUNCTION xy(int4) RETURNS SETOF RECORD AS ' DECLARE v_isALIAS FOR $1; v_loop int4; v_rec RECORD; BEGIN v_loop := 0; SELECT INTO v_rec 0; WHILE (v_loop v_is) LOOP SELECT INTO v_rec v_loop; v_loop := v_loop + 1; RETURN NEXT v_rec; END LOOP; RETURN NEXT v_rec; RETURN v_rec; END; ' LANGUAGE 'plpgsql'; SELECT * FROM xy(0) AS (id int4); SELECT * FROM xy(1) AS (id int4); This function works nicely in 7.4.x (even without the last RETURN NEXT). 8.0 returns an error. [EMAIL PROTECTED] tmp]$ psql microtec -h epi t.sql ERROR: RETURN cannot have a parameter in function returning set; use RETURN NEXT at or near v_rec at character 324 ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. ERROR: function xy(integer) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. 7.4.1 works nicely ... [EMAIL PROTECTED] tmp]$ psql microtec -h epi -p t.sql CREATE FUNCTION id 0 (1 row) id 0 0 (2 rows) I have tried to look it up in the source code (gramy.y line 1144) but I don't really know what the new check which has been added in 8.0 is good for. Can anybody give me a hint how it is supposed to be? Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Regression failure with PostgreSQL 8beta1 and Intel
Robert,
Are you planning to use Intel's C compiler in production?
We tried that some time ago and corrupted our database cluster almost
instantly (for some reason we have not investigated any further).
I highly recommend to do some stress testing to see if everything works
nicely.
I'd be pleased to get some feedback.
Regard,
Hans
Robert E. Bruccoleri wrote:
Dear All,
I built PG 8.0 beta1 on an Itanium 2 platform using the Intel compilers
version 8, and got one real difference in the regression tests that affected
int2, int4, union, and numerology. Here's the key difference:
horta postgres 177 diff -c int4.out ../expected/
*** int4.outTue Aug 10 18:41:48 2004
--- ../expected/int4.outWed Mar 10 21:11:13 2004
***
*** 22,27
--- 22,28
INSERT INTO INT4_TBL(f1) VALUES (' asdf ');
ERROR: invalid input syntax for integer:asdf
INSERT INTO INT4_TBL(f1) VALUES ('- 1234');
+ ERROR: invalid input syntax for integer: - 1234
INSERT INTO INT4_TBL(f1) VALUES ('123 5');
ERROR: invalid input syntax for integer: 123 5
INSERT INTO INT4_TBL(f1) VALUES ('');
PG 8.0 beta1 is accepting - 1234 as a valid integer. Further investigation
reveals that this is a peculiarity of the Intel compilers. The following
program,
#include stdio.h
#include stdlib.h
main() {
char st[] = - 1234;
int l;
char *endp;
l = strtol(st, endp, 10);
printf(l = %d st = %lx endp = %lx\n, l, st, endp);
}
using the Intel compiler provided libraries prints
l = -1234 st = 6fffb720 endp = 6fffb726
whereas gcc and glibc yields
l = 0 st = 6fffb710 endp = 6fffb710
Boo hiss...
+-++
| Robert E. Bruccoleri, Ph.D. | email: [EMAIL PROTECTED]|
| President, Congenair LLC| URL: http://www.congen.com/~bruc |
| P.O. Box 314| Phone: 609 818 7251|
| Pennington, NJ 08534||
+-++
---(end of broadcast)---
TIP 8: explain analyze is your friend
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/720/10 1234567 or +43/660/816 40 77
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Version Numbering -- The great debate
Joshua D. Drake wrote: Hello, Version 7.5 is as close to a major release as I have seen in the almost 9 years I have been using PostgreSQL. This release brings about a lot of enterprise features that have been holding back PostgreSQL in a big way for for a long time. All of my serious customers; potential, existing and past has all at one point or another requested most if not all of the features being released onto the world with 7.5. In fact the only ones that I can think of off the top of my head that isn't in the current list of availables is table partitioning and to a lesser extent two phase commit. This release definately deserves a major version jump. If it were up to me it would be more than one (I would call it 10h for obvious reasons. O.k. the h is a joke but I am serious about the 10) just from a marketing standpoint. I could argue a major version jump just from the fact that we finally have a port to the most used operating system (regardless if that is good or bad) in the world. Sincerely, Joshua D. Drake They have tried to do the same for With Naked Gun (I think it is called in English). They called the second film With Naked Gun 2 1/2. The third version was called 33 1/3 then ... Maybe the tenth film would be 10^256 then ... 8.0 would be ok but I am pretty against jumping version number - they have such a pure marketing flavour (we have a high version number but we don't know what else we should tell you about the new release). Database work should be conservative which means slowly but surely ... - from my point of view this conflicts with pumping version numbers. I don't think there will be one more user just because of a different version number. Maybe a hostily overtake of Oracle (not Firebird as mentioned by Peter) would justify 10.0.0 ;). Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] storage engine , mysql syntax CREATE TABLE t (i INT)
Pierre Emmanuel Gros wrote: In mysql, we can wrote a create table like CREATE TABLE t (i INT) ENGINE = INNODB||BDB|; where the storage engine is the innodb one. This allow to have differents kind of storage format, and allow to easly implements memory table or remote table. I try to make the same thing for postgresql but i do not understand where the logical storage engine is in the source code. May i have somme help to find it . Thank you. pierre Pierre, Basically the code related to the storage engine can be found in src/backend/storage. There you will find some important parts of PostgreSQL such as the free space map, the lock manager and so forth. The storage system is implemented there. The code should be extendable although nobody has implemented different storage systems in the past (at least I can't remember) because it has never been important. Personally I don't think that real memory tables are important. In 7.5 you can even model them with the help of RAM drives and tablespaces (in case you really need it). As far as remote tables are concerned: To do database links properly you will need some mutli-phase commit algorithm. I don't think it is that easy. How would you ensure integrity? Did you already think about transaction isolation related issues? How would the planner treat those issues and how should recovery in case of disaster work? Recall, you are in a distributed system then ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] parameter hints to the optimizer
Merlin Moncure wrote: Merlin, This will most likely never be accepted by the core team because it is better to spend more time on fixing the planner than to invent some non-standard. As far as I know some databases support a syntax where hints can be hidden in comments or something like that. Meanwhile I think that hints are more of a burdon than a help. Regards, Hans I thought as much. Still, the parameter problem is a huge headache. Maybe if it was possible to use the statistics to gather a 'pseudovariable' to feed to the parameter based on some algorithm, the planner could give better results without exposing the planner inner workings to the user. Thanks for the feedback. Merlin Using statistics is exactly what the planner does ... So why should data coming from the planner being given back to the planner? Doesn't make sense. For more information I highly recommend Tom's talk at Oreilly's some time ago. I think it is called recent improvements in 7.4 ... regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Call for 7.5 feature completion
Josh Berkus wrote: People, So, why tie it into the PostgreSQL source tree? Won't it be popular enough to live on its own, that it has to be distributed as part of the core? Personally, I find it rather inconsistent to have any PL, other than PL/pgSQL, as part of the core distribution -- when we are pushing the interfaces, such as JDBC and libpqxx to seperate modules in pgFoundry. Either we're trying to lighten up the core, or we're not.But right now there seems to be no logic in operation. I do think, though, that we need some system to build RPMs for all the pgFoundry stuff ... As far as this discussion is concerned I personally think that there is just one way to satisfy everybody. I we had a PostgreSQL most wanted distribution including PL/* as well as some other modules we could save people compiling PostgreSQL from source a lot of work. The core itself would be cleaner (which is the target of moving things out) and everybody would be happy? If people think this is a good idea I could compile and maintain this (source) distribution ... Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Call for 7.5 feature completion
Marc G. Fournier wrote: On Mon, 17 May 2004, Bruce Momjian wrote: Marc G. Fournier wrote: Agreed, but you are a me too, not a huge percentage of our userbase. How do you know? Have you polled our complete userbase? Basically, after 6-7 months of development, I want more than a vacuum patch and a new cache replacement policy. I want something big, in fact, several big things. Most likely won't happen, since what is considered big by you isn't necessarily what is considered big by someone else ... as Hannu, and I believe, Jan, have so far pointed out to you ... I can't poll for everything. I make my own educated guesses. Based on what though? All the clients that I deal with on a daily basis generally care about is performance ... that is generally what they upgrade for ... so, my 'educated guess' based on real world users is that Win32, PITR and nested transactions are not important ... tablespaces, I have one client that has asked about something *similar* to it, but tablespaces, for him, doesn't come close to what they would like to see ... So, my 'educated guess' is different then yours is ... does that make yours wrong? Nope ... just means we have different sample sets to work with ... Interesting. We have made COMPLETELY different experiences. There is one question people ask me daily: When can we have sychronous replication and PITR?. Performance is not a problem here. People are more interested in stability and enterprise features such as those I have mentioned above. I am still wondering about two things: Somebody has posted a 2PC patch - I haven't seen too many comments Somebody has posted sync multimaster replication (PgCluster) - nobody has commented on that. Maybe I am the only one who has ever tried it ... Most likely this is not very encourageing for the developers involved ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Call for 7.5 feature completion
Not being the author, I don't know. And in the case of PITR, the pre-7.4 author is different than the post-7.4 author. However, if I was personally responsible for holding up the release of a project due to a feature that I had vowed to complete, I would feel morally compelled to get it done. If I had then asked for, and was granted, an extra 15-30 days I would feel even more personally responsible and under greater pressure. If, however, the project made the release without waiting, I would feel simultaneously relieved and possibly a little bitter. Possibly a little bitter in that either what I was working on wasn't perceived as sufficiently valuable to hold up a release for 15-30 days, or that my word regarding the completion status was insufficient for the project to trust me. Let me reiterate the words possibly and little. But in open source projects, a developer willing to contribute hundreds, possibly thousands of hours of his own time is particularly invaluable. I can tell you that, in economic models that have studied human behavior with respect to unemployment insurance, for example, the re-employment rates are clustered at the tails: when someone is first unemployed and when the insurance is about to expire. It's an inappropriate analogy because the project lives on from release to release, instead of having a drop-dead date at which point no future changes would be made ad infinitum, but it paints a useful picture. I'm willing to bet that CVS commit rates mirror the above behavior. Unlike unemployment benefits, releasing the software without the feature essentially just extends the development period another 6 months, the work will intensify at the new perceived tails, and the process repeated. There are probably econometric papers that model the software development release cycle that could give quantitative arguments. I'm not arguing I'm right and your wrong, btw. I'm just pointing out some of the possibilities. In fact, for one developer it might be the code production maximizing condition to give them another 6 months and for another, creating the pressure associated with a 15-30 day extension where the world is standing still awaiting their patch... Mike Mascari Yesterday I have issued a posting which had to do with motivation. This is Open Source - there is no boss which tells somebody to finish something. Therefore we must MOTIVATE people. Has anybody read Sim Riggs posting earlier in the thread. There is one paragraph which makes my alarm bells ring VERY LOUD: This is all rather disheartening, having laid out a time plan months back, noting some of this. Yes, I am working on it, and no, I'm not hand waving, but I do take time off every million keystrokes or so. If somebody who has done a GREAT JOB is disheartened by the way his work is treated it is really time to start thinking ... From my very personal point of view Mike absolutely right; why not give it a try. I guess Simon and Alvaro deserve some more time and we should give those guys a limited time frame to finish their work. Recall, it's all about motivation ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/720/10 1234567 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Aggregation question
if you want to put 1000 columns into one table, your data structure needs some further investigation. you are trying to solve the wrong problem. Regards, Hans Dilip Angal wrote: Hi I have a situation that I need flexible number columns to model the business requirements. It could go up to 1000 columns and will be a sparse matrix. One option I was considering was to have a table with col1..col1000 Other option I can consider is store all of them as name values in single column as a string option 1 col1 | col2 |col 3 ||col56|.|col77| 10| 2 | || 4 | | | 2 | | 4 || | |6| option 2 col1=10,col2=2,col56=4 col1=2,col3=4,col77=6 I will have about 50Million such records in a table. I need to aggregate the column values for a given day by taking sum of all the rows col1=12,col2=2,col3=4,col56=4,col77=6 Second option looks very elegant but aggregation is hard Besides, second option may have performance penalty (Especially when you have 50 Million records) Any one can give any pointers or comments on how to model this an how to aggregate it? Thanks Dilip -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Call for 7.5 feature completion
Andrew Dunstan wrote: Marc G. Fournier wrote: Personally, I think there are alot of large features that ppl have been hard at getting complete in time for June 1st that we should stick to it, else we're going to end up with 'yet another release' delayed in hopes that the outstanding bugs in Win32 will get fixed in a reasonable amount of time ... June 1st, let's do beta for 7.5 and then branch onto 8.0, with 8.0 key'd to the Win32 Native port being finished ... If that means 8.0 happens to be September 1st, so be it ... Bruce agreed that this had been vague before today, so if people have had this date in mind and have been working to it, perhaps they have some telepathic abilities I lack ... We missed on PITR *and* Win32 last year. ISTM there's a bit of a credibility issue at stake, so it might well be worth taking a couple of weeks leeway if that's what is required. The other point, especially about Win32, is to see if we can spread the load a bit. Perhaps Claudio, Magnus, Merlin and Bruce should start trying to farm out specific tasks. I for one will be very upset if it misses this release. cheers andrew This is exactly the point ... If you go to a conference you will ALWAYS face the same questions: - when can we have sync. replication and failover - when can we have PITR - when can we have win32 People won't believe us anymore if you keep telling them in the next release. If a feature freeze is made on August 1st or even later it would be ok because nobody is doing major database changes in summer anyway. Currently I cannot see a major reason why people should upgrade to 7.5 (ARC and so forth are great but they are no killer features). Maybe in this case it is worth waiting for 2 major features to make it into the release (let's say PITR + nested transactions or win32 and pitr or 2pc and nested transactions). This would point out that significant progress is made. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PITR Phase 2 - Design Planning
Simon Riggs wrote: Since Phase1 is functioning and should hopefully soon complete, we can now start thinking about Phase 2: full recovery to a point-in-time. Previous thinking was that a command line switch would be used to specify recover to a given point in time, rather than the default, which will be recover all the way to end of (available) xlogs. Recovering to a specific point in time forces us to consider what the granularity is of time. We could recover: 1.to end of a full transaction log file 2.to end of a full transaction Transaction log files currently have timestamps, so that is straightforward, but probably not the best we can do. We would rollforward until the xlog file time desired point in time. To make (2) work we would have to have a timestamp associated with each transaction. This could be in one of two places: 1. the transaction record in the clog 2. the log record in the xlog We would then recover the xlog record by record, until we found a record that had a timestamp desired point-in-time. Currently, neither of these places have a timestamp. H. We can't use pg_control because we are assuming that it needs recovery... I can't see any general way of adding a timestamp in any less than 2 bytes. We don't need a timezone. The timestamp could refer to a number of seconds since last checkpoint; since this is limited already by a GUC to force checkpoints every so often. Although code avoids a checkpoint if no updates have taken place, we wouldn't be too remiss to use a forced checkpoint every 32,000 seconds (9 hours). Assuming that accuracy of the point-in-time was of the order of seconds?? If we went to 0.1 second accuracy, we could checkpoint (force) every 40 minutes or so. All of that seems too restrictive. If we went to milliseconds, then we could use a 4 byte value and use a checkpoint (force) every 284 hours or 1.5 weeks. Thoughts? Clog uses 2 bits per transaction, so even 2 bytes extra per transaction will make the clog 9 times larger than originally intended. This could well cause it to segment quicker, but I'm sure no one would be happy with that. So, lets not add anything to the clog. The alternative is to make the last part of the XlogHeader record a timestamp value, increasing each xlog write. It might be possible to make this part of the header optional depending upon whether or not PITR was required, but then my preference is against such dynamic coding. So, I propose: - appending 8 byte date/time data into xlog file header record - appending 4 bytes of time offset onto each xlog record - altering the recovery logic to compare the calculated time of each xlog record (file header + offset) against the desired point-in-time, delivered to it by GUC. Input is sought from anybody with detailed NTP knowledge, since the working of NTP drift correction may have some subtle interplay with this proposal. Also, while that code is being altered, some additional log records need to be added when recovery of each new xlog starts, with timing, to allow DBAs watching a recovery to calculate expected completion times for the recovery, which is essential for long recovery situations. I am also considering any changes that may be required to prepare the way for a future implementation of parallel redo recovery. Best regards, Simon Riggs, 2ndQuadrant http://www.2ndquadrant.com ---(end of broadcast)--- TIP 8: explain analyze is your friend Simon, I have one question which might be important: If we use timestamps inside the WAL system to find out where to stop. What happens if somebody changes the time of the system? (e.g. correcting the system clock by calling ntpdate). Wouldn't it confuse the PITR system? How do you plan to handle that? Unfortunately time is nothing which can be used as a key (at least not from my point of view). Just some lousy ideas early in the morning ... Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] What can we learn from MySQL?
Karel Zak wrote: On Fri, Apr 23, 2004 at 01:05:21PM +0700, David Garamond wrote: So in my opinion, as long as the general awareness about RDBMS (on what tasks/responsibilities it should do, what features it generally has to have, etc) is low, people will be looking at MySQL as good enough and will not be motivated to look around for something better. As a comparison, I'm always amazed by people who use Windows 95/98/Me. They find it normal/good enough that the system crashes every now and then, has to be rebooted every few hours (or every time they install something). They don't know of anything better. Agree. People don't know that an RDBMS can be more better. A lot of users think speed is the most important thing. And they check the performance of SQL server by time mysql -e SELECT... but they don't know something about concurrency or locking. Even worse: They benchmark SELECT 1+1 one million times. The performance of SELECT 1+1 has NOTHING to do with the REAL performance of a database. Has anybody seen the benchmarks on MySQL??? They have benchmarked CREATE TABLE and so forth. This is the most useless thing I have ever seen. It is so annoying _ I had to post it ;). Regards, Hans BTW, is the current MySQL target (replication, transactions, ..etc) what typical MySQL users expect? I think they will lost users who love classic, fast and simple MySQL. The trade with advanced SQL servers is pretty full. I don't understand why MySQL developers want to leave their current possition and want to fight with PostgreSQL, Oracle, DB2 .. etc. Karel -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Socket communication for contrib
Paul Tillotson wrote: Hans et al: People asked me to put a simple extension for PostgreSQL Open Source. The attached package contains a simple functions whichs tells a remote TCP socket that somebody is about to modify a certain table. I would very much appreciate being able to receive notifications over the network. Besides helping machines which are not directly connected to the database, this is very useful when one is using a deficient API/wrapper which does not provide a block until a notify arrives. (Such as the pg_xx functions in PHP.) Doesn't this encourage violation of the basic notion of a transaction? The message will be sent immediately, whether or not the sending transaction actually commits. [ ... thinks ... ] Good point, but I think I see another problem with it--changes to a table are not visible until a transaction commits. Depending on the speed of your network, you might often get the notification BEFORE the transaction commits, and so your SELECT new rows SQL statement might miss the very change that it was notified of. The only way to tell would be to wait for a reasonable amount of time and try again. (And of course, if the change were rolled back then you would never see a changed row.) It seems that one would be almost reduced to polling again. Yes, It might happen that you cannot see changes. Instead of this, what do the hackers think of a NOTIFY forwarder? One could make a small C program which connects to the database, executes LISTEN for the proper notifies, goes to sleep using select(), and then forwards each notify received over the network to the proper hosts? It seems that this would accomplish the same result while not violating the basic notion of a transaction. It would permanently tie up one backend, though. : ( Could your extension be modified to work this way, Hans? Paul Tillotson Well, sacrifycing one backend would not be a problem. If you are using one connection to do the LISTEN / NOTIFY work (maybe including some configuration schema), you had a good chance to see the changes which have been made. Basically this should not be a problem. However, my time is very limited at the moment. I hope that I will finde some spare time within the next few months to SELECT FOR UPDATE NOWAIT and you idea. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Socket communication for contrib
Community, People asked me to put a simple extension for PostgreSQL Open Source. The attached package contains a simple functions whichs tells a remote TCP socket that somebody is about to modify a certain table. Why would anybody do that? Currently PostgreSQL provides a nice LISTEN / NOTIFY mechanism. However, this mechanism is obsolete when the machine which should receive a message is not connected to PostgreSQL directly. In this case it is possible to define a trigger on a table and let the database tell those machines via TCP that something interesting might have happened. In our case this package has helped us to get rid of permanent, speed consuming polling. Maybe some people might find it useful and want to see this feature in contrib. Please let us know. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at tecwing.tgz Description: GNU Zip compressed data ---(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] Socket communication for contrib
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: People asked me to put a simple extension for PostgreSQL Open Source. The attached package contains a simple functions whichs tells a remote TCP socket that somebody is about to modify a certain table. Doesn't this encourage violation of the basic notion of a transaction? The message will be sent immediately, whether or not the sending transaction actually commits. regards, tom lane Yes, absolutely - it is damn hard to ROLLBACK a TCP connection. Unfortunately there are no ON COMMIT triggers or something like that - this would have been a better solution. I am very well aware of this problem because I share your concerns. However, sometimes it can be interesting to know if somebody ATTEMPTS to modify the database. Also, you can use it to send data in the database to somebody where. In this case there are in most cases 1-line transactions: eg. SELECT tellsomebody() WHERE id = someid; In our special case it makes sense when various clients which are NOT connected to the database (because they are somewhere else on this planet) receive some sort of database driven notification in case of some events. Depending on the return value a user can see whether a message has been delivered or not. Sending a message to many clients has always the same problem: Unfortunately TCP does not support transactions the way people would use it inside a database. Nested transactions: I don't think nested transactions will really help to resolve the core problem. Committing a subtransaction will most likely not imply that a parent transaction can be committed as well. As I said: Some people MIGHT find it useful in some special cases. If the community decides that it does not enough sense to integrate it into contrib I can live with that. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Socket communication for contrib
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Nested transactions: I don't think nested transactions will really help to resolve the core problem. Committing a subtransaction will most likely not imply that a parent transaction can be committed as well. Agreed. As I said: Some people MIGHT find it useful in some special cases. If the community decides that it does not enough sense to integrate it into contrib I can live with that. I won't take a position on whether it's useful enough to put in contrib, but if people want it there, I'd just ask that the README be extended to point out the transactional risks. this should not be a problem. I can intregrate all necessary information there. folks, let's do a poll ... who is for it - who is against it ... regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Socket communication for contrib
Is it better in /contrib or gborg? I have learned (please correct me if I am wrong) that people tend to look in contrib before they look at gborg. Also, when people ask for training most of them ask for stuff in contrib. It is people's mind that contrib is somehow a source of additional, small software. Again, correct me if I am wrong. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Socket communication for contrib
Jeff wrote: On Apr 5, 2004, at 12:35 PM, Bruce Momjian wrote: For me, /contrib is for things closely tied to the backend code, like GIST stuff, and for key tools, like conversion programs. something that would be useful (and perhaps may be part of that pgfoundry or whatever its called movement) would be making gborg's existance more clear. Maybe putting a file in doc/ or contrib/ mentioning it or including an index of things on it (Or at least the more popular items). Often when I talk to people at work about PG they ask about stuff and I say Silly, thats on gborg! and they look at me strangely and have no idea about it. You get less of it with contrib/ items.. just my $0.02 You have hit an important point here: What is gborg?. That's what people think. Maybe we should have a contrib package (any better ideas?) or at least a pretty obvious place inside our .tar.gz files mentioning gborg. If you want more - gborg. Otherwise people won't use gborg too much. I have seen that too often. Maybe thinking about that makes sense about that's more an advocacy issue I think. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] COPY formatting
Karel Zak wrote:
Hi,
in TODO is item: * Allow dump/load of CSV format. I don't think
it's clean idea. Why CSV and why not something other? :-)
A why not allow to users full control of the format by they own
function. It means something like:
COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ FORMAT funcname ] ]
The formatting function API can be pretty simple:
text *my_copy_format(text *attrdata, int direction,
int nattrs, int attr, oid attrtype, oid relation)
-- it's pseudocode of course, it should be use standard fmgr
interface.
It's probably interesting for non-binary COPY version.
Comments?
Karel
Karel,
This seems to be an excellent idea.
People have already asked for many different formats.
Usually I recommend them to use psql -c COPY ... dbname | awk
Since Windows will be supported soon, it will be hard to pipe data to a
useful program (awk, sed, ...). Maybe this feature would help a lot in
this case.
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Schoengrabern 134, A-2020 Hollabrunn, Austria
Tel: +43/2952/30706 or +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Regression tests on Nintendo Game Cube
Merlin Moncure wrote: Today Michael Steil and I have tested PostgreSQL 7.4.1 on Nintendo Game Cubes. All regression test (but stats - stats collector was off instead of on) have passed successfully. What about the XBOX? :-) Merlin as far as i know the xbox is based on x86 hardware so it should run straight away (it has even more ram than the game cube). by the way; we don't use game cubes for clusters and high-performance systems :). the regression test took around 3 hours *g*. feel free to regression test on an Xbox (or give me access to one running *nix). cheers, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Regression tests on Nintendo Game Cube
Folks, Today Michael Steil and I have tested PostgreSQL 7.4.1 on Nintendo Game Cubes. All regression test (but stats - stats collector was off instead of on) have passed successfully. [EMAIL PROTECTED] root]# uname -a Linux 192.168.0.47 2.6.3 #20 Wed Mar 3 12:22:07 CET 2004 ppc unknown unknown GNU/Linux [EMAIL PROTECTED] root]# cat /proc/cpuinfo processor : 0 cpu : 745/755 revision: 50.20 (pvr 0008 3214) bogomips: 968.70 vendor : IBM machine : Nintendo GameCube It is damn hard to compile PostgreSQL on 24 mb ram + 16 mb swap (stolen from my sound buffer provided by Nintendo). gram.c and preproc.c have been compiled on an imac maschine - the rest has been compiled on Michael's Game Cube. Compiling and regression testing takes around 30 years (wow, this kind of stuff is damn slow - it is not a good database server *g*). Game Cubes are now fully supported by us ;). Cheers, Michael and Hans = starting postmaster== running on port 65432 with pid 24018 == creating database regression == CREATE DATABASE ALTER DATABASE == dropping regression test user accounts == == installing PL/pgSQL== == running regression test queries== parallel group (13 tests): char int2 name boolean varchar text int8 oid int4 float4 float8 bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... ok test numerology ... ok parallel group (20 tests): point lseg box date circle time path reltime abstime type_sanity timestamptz interval tinterval timestamp timetz comments polygon inet opr_sanity oidjoins point... ok lseg ... ok box ... ok path ... ok polygon ... ok circle ... ok date ... ok time ... ok timetz ... ok timestamp... ok timestamptz ... ok interval ... ok abstime ... ok reltime ... ok tinterval... ok inet ... ok comments ... ok oidjoins ... ok type_sanity ... ok opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... ok parallel group (7 tests): create_operator create_aggregate vacuum inherit triggers constraints create_misc constraints ... ok triggers ... ok create_misc ... ok create_aggregate ... ok create_operator ... ok inherit ... ok vacuum ... ok parallel group (2 tests): create_view create_index create_index ... ok create_view ... ok test sanity_check ... ok test errors ... ok test select ... ok parallel group (17 tests): select_distinct select_into select_distinct_on select_having case update union random aggregates transactions select_implicit arrays subselect hash_index join portals btree_index select_into ... ok select_distinct ... ok select_distinct_on ... ok select_implicit ... ok select_having... ok subselect... ok union... ok case ... ok join ... ok aggregates ... ok transactions ... ok random ... ok portals ... ok arrays ... ok btree_index ... ok hash_index ... ok update ... ok test privileges ... ok test misc ... ok parallel group (5 tests): portals_p2 select_views cluster rules foreign_key select_views ... ok portals_p2 ... ok rules... ok foreign_key ... ok cluster ... ok parallel group (14 tests): sequence limit polymorphism without_oid truncate copy2 prepare temp rangefuncs conversion domain stats plpgsql alter_table limit... ok plpgsql ... ok copy2... ok
Re: [HACKERS] Tablespaces
Gavin Sherry wrote: Hi all, I've been looking at implementing table spaces for 7.5. Some notes and implementation details follow. -- Type of table space: There are many different table space implementations in relational database management systems. In my implementation, a table space in PostgreSQL will be the location of a directory on the file system in which files backing database objects can be stored. Global tables and non 'data' objects (WAL, CLOG, config files) will all remain in $PGDATA. $PGDATA/base will be the default table space. Is it possible to put WALs and CLOGs into different tablespaces? (maybe different RAID systems). Some companies want that ... A given table space will be identified by a unique table space name. I haven't decided if 'unique' should mean database-wide unique or cross-database unique. It seems to me that we might run into problems with CREATE DATABASE ... TEMPLATE = database with table spaces if the uniqueness of table spaces is limited to the database level. I strongly vote for database cluster wide unique names because somebody could have a tablespace webusers or something like that. To me this makes far more sense. A table space parameter will be added to DDL commands which create physical database objects (CREATE DATABASE/INDEX/TABLE/SEQUENCE) and to CREATE SCHEMA. The associated routines, as well as the corresponding DROP commands will need to be updated. Adding the ability to ALTER object TABLESPACE name seems a little painful. Would people use it? Comments? I think people won't need it in first place because this seems to be really painful. What really matters is that the number of tablespaces and file / tablespace is unlimited. SAP DB has limited the number of devspaces to 32 (I think). This is real bull because if your database grows unexpectedly you are in deep trouble (expert database design by SAP, MySQL and 10 others). When an object is created the system will resolve the table space the object is stored in as follows: if the table space paramater is passed to the DDL command, then the object is stored in that table space (given validation of the table space, etc). If it is not passed, the object inherits its parent's table space where the parent/child hierarchy is as follows: database schema table [index|sequence]. So, if you issued: create table foo.bar (...); We would first not that there is no TABLESPACE name, then cascade to the table space for the schema 'foo' (and possibly cascade to the table space for the database). A database which wasn't created with an explicit table space will be created under the default table space. This ensures backward compatibility. Will users automatically be assigned to a certain table space? How is this going to work? Creating a table space: A table space is a directory structure. The directory structure is as follows: [EMAIL PROTECTED] /path/to/tblspc]$ ls OID1/ OID2/ OID1 and OID2 are the OIDs of databases which have created a table space against this file system location. In this respect, a table space resembles $PGDATA/base. I thought it useful to keep this kind of namespace mechanism in place so that administrators do not need to create hierarchies of names on different partitions if they want multiple databases to use the same partition. The actual creation of the table space will be done with: CREATE TABLE SPACE name LOCATION /path/to/tblspc; Before creating the table space we must: 1) Check if the directory exists. If it does, create a sub directory as the OID of the current database. 2) Alternatively, if the directory doesn't exist, attempt to create it, then the sub directory. I wonder if a file, such as PG_TBLSPC, should be added to the table space directory so that, in the case of an existing non-empty directory, we can attempt to test if the directory is being used for something else and error out. Seems like: CREATE TABLESPACE tbl1 LOCATION '/var/' which will result in something like '/var/123443' is a bad idea. Then again, the user should know better. Comments? If everything goes well, we add an entry to pg_tablespace with the table space location and name (and and OID). Tying it all together: The catalogs pg_database, pg_namespace, and pg_class will have a 'tblspc' field. This will be the OID of the table space the object resides in, or 0 (default table space). Since we can then resolve relid/relname, schema and database to a tablespace, there aren't too many cases when extra logic needs to be added to the IO framework. In fact, most of it is taken care of because of the abstraction of relpath(). The creation of table spaces will need to be recorded in xlog in the same way that files are in heap_create() with the corresponding delete logic incase of ABORT. Postmaster startup: Ideally, the postmaster at startup should go into each tblspc/databaseoid directory and check for a postmaster.pid file to see if some other instance is touching the files we're
Re: [HACKERS] Tablespaces
Gavin Sherry wrote: Is it possible to put WALs and CLOGs into different tablespaces? (maybe different RAID systems). Some companies want that ... I wasn't going to look at that just yet. There is of course the temporary hack of symlinking WAL else where. that's what we do now. we symlink databases and wals ... I'd be interested to see the performance difference between WAL and data on the same RAID/controller and WAL and data on different RAID/controller with Jan's improvements to the buffer management. Gavin yes, that's what i am looking for. i should do some testing. in case of enough i/o power additional cpus scale almost linearily (depending on the application of course; i have done some testing on a customer's aix box ...). it would be interesting to see what jan's buffer strategy does (and bg writer) ... -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] [SQL] Materialized View Summary
Richard Huxton wrote: On Tuesday 24 February 2004 16:11, Jonathan M. Gardner wrote: I've written a summary of my findings on implementing and using materialized views in PostgreSQL. I've already deployed eagerly updating materialized views on several views in a production environment for a company called RedWeek: http://redweek.com/. As a result, some queries that were taking longer than 30 seconds to run now run in a fraction of a millisecond. You can view my summary at http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html Interesting (and well written) summary. Even if not a built in feature, I'm sure that plenty of people will find this useful. Make sure it gets linked to from techdocs. If you could identify candidate keys on a view, you could conceivably automate the process even more. That's got to be possible in some cases, but I'm not sure how difficult it is to do in all cases. Are there any plans to rewrite that in C and add proper support for SQL commands? (e.g. CREATE MATERIALIZED VIEW, DROP VIEW, ...). Best regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] NO WAIT ...
Tatsuo Ishii wrote: I agree with Tom here. I have used the Oracle NOWAIT feature in the past and think it is a great feature IMHO. But when you need to use it, you want it to apply very specifically to a single statement. Using a sledge hammer when you need a tweezers isn't the right way to go. Once I have written patches for 7.3 to implement this feature for LOCK statement. For example: test=# LOCK TABLE sales NO WAIT; ERROR: Cannot aquire relation lock If there's enough interest, I will modify and submit it for 7.5. -- Tatsuo Ishii ---(end of broadcast)--- TIP 8: explain analyze is your friend That would be great. Many people are asking for that. Maybe I have time to implement that for SELECT FOR UPDATE. Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] No Timeout in SELECT..FOR UPDATE
Anthony, What you need is a NO WAIT option. This is already on the TODO list. This feature should be implemented as GUC (see TODO list). I don't think that a timeout would be accepted by the core team (doesn't make too much sense to me either). Telling PostgreSQL not to wait for certain locks is definitely better (at least from my point of view). We might work on a patch like that in the near future but don't hesitate to send a patch yourself. Best regards, Hans Anthony Rich wrote: Hi Guys, I have a suggestion for fixing a long-term and painful problem in PostgreSQL that is holding up many very important commercial projects, including ours! This problem has been reported numerous times: When one process has a row lock on one or more rows in a table, using SELECT...FOR UPDATE in default lock mode, another process has NO WAY of aborting from the same request, and reporting to the user that this record is already locked, reserved, or whatever you want to call it. In other words, by the time the second process has run the SELECT...FOR UPDATE statement, it's too late!! This second process is now locked forever, waiting for the first process to commit, rollback, or some other function that will release the lock. Yes, the first process will eventually release the lock by commiting or rollback, bu this is for a commercial environment with users, not processes, and the user needs to be informed about the error immediately, or within a second or so, and be given the chance to retry the update with lock, or just abort and go find another record to change. This problem is *fundamental*, and *very typical* in a commercial, accounting, or mission-critical environment. The only solution to this problem in PostgreSQL seems to be to: (1) Re-write the SELECT...FOR UPDATE SQL code, to return with an exception or error if it cannot immediately secure the lock, OR: (2) Add a TIMEOUT N clause to this statement, so that the timeout can be controlled on a per-statement basis - this is probably better. For example: [1] if I want the second process to give up within 1 millisecond, and return if the lock is not possible, then write: SELECT ... FOR UPDATE TIMEOUT 1 [0] If I want the default behaviour, (for a process that is prepared to wait forever for the record(s)), then: SELECT... FOR UPDATE TIMEOUT 0 OR, simply: SELECT... FOR UPDATE (as it is now) I hope that this suggestion will be taken seriously, since it is clear that a large number of developpers have made comments on this problem, dated all the way back to 2001 or earlier. Many thanks, Tony Rich, Richcorp Technology, Sydney, Australia. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Recursive queries?
Christopher Kings-Lynne wrote: There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Found it. Check it out: http://gppl.terminal.ru/index.eng.html Patch is current for 7.4, Oracle syntax. Chris I had a look at the patch. It is still in development but it seems to work nicely - at least I have been able to get the same results with Oracle. I will try it with a lot of data this afternoon so that we can compare Oracle vs. Pg performance. I expect horrible results ;). Does this patch have a serious chance to make it into Pg some day? I think Oracle's syntax is not perfect but is easy to handle and many people are used to it. In people's mind recursive queries = CONNECT BY and many people (like me) miss it sadly. If this patch has a serious chance I'd like to do some investigation and some real-world data testing. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Recursive queries?
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Does this patch have a serious chance to make it into Pg some day? I think Oracle's syntax is not perfect but is easy to handle and many people are used to it. In people's mind recursive queries = CONNECT BY and many people (like me) miss it sadly. I would prefer to see us supporting the SQL-standard syntax (WITH etc), as it is (1) standard and (2) more flexible than CONNECT BY. The Red Hat work mentioned earlier in the thread was aimed at supporting the standard syntax. regards, tom lane I have already expected an answer like that. In my very personal opinion (don't cut my head off) I'd vote for both syntaxes. The reasons for that are fairly easy to explain: - I have to agree with Tom (1, 2). - CONNECT BY makes sense because it is easier to build applications supporting Oracle and PostgreSQL. In case of more complex applications (CONNECT BY is definitely more than pure storage of simple data) Oracle-Pg compliance is really important (I have seen that a dozen times). From a marketing point of view both versions make sense - Oracle-Pg migration is an increasing market share. From a technical point of view I completely agree with Tom (I have learned in the past that Tom us usually right). Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Bitmap index
Hello ... I remember that somebody was working on some sort of bitmap index some time ago (maybe 1 year or two). Does anybody know if there is some sort of half-ready code or so around? Does anybody know if there is somebody working on that? Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] planner, how difficult to ...
Peter Eisentraut wrote: Keith Bottner wrote: How integrated is the planner into the database? Put another way, how hard would it be to separate the planner from the core database in such a way that it could be easily replaced either during compilation or even better with a runtime setting? The planner is configurable at run time in many ways. But maybe you want to start by explaining your problem instead of presuming a solution. you can already change the planner. just use GEQO instead of exhaustive searching, why would you want to change the existing planner? implementing a rule based planner like the one Oracle had is not a good idea ;). regards, Hans ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] statistics about tamp tables ...
Alvaro Herrera wrote: On Wed, Nov 26, 2003 at 05:34:28PM +0100, Hans-Jürgen Schönig wrote: The reason why I came up with this posting is slightly different: Assume a JDBC application which works with PostgreSQL + some other database. If you want to use both databases without PostgreSQL being unnecessarily slow an implicit mechanism would be better. Because otherwise you will have an SQL command in there which is off standard - putting a switch into the application seems to be a fairly ugly solution. That's why you delegate the job to something else, like pg_autovacuum or cron ... If you are in the middle of a data mining application using a tmp table you don't want to wait for cron ;). You might want the statistics to be correct as soon as the table has been created. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ObjectWeb/Clustered JDBC
Dave, I know that the backend does - it is an essential feature. Clustered JDBC parses the statement sent to it in order to find out what to do with it. I have played around a little (mostly interactive shell). You will find out that Clustered JDBC will complain in this case because it doesn't know what to do with it. If you are a tool support load balancing and this kind of stuff DECLARE CURSOR can be painful to implement - especially across multiple transactions. Is is a very weak point of the current beta version. Regards, Hans Dave Cramer wrote: Hans, I don't understand the statement about missing DECLARE CURSOR ? The backend supports it? Dave On Sun, 2003-11-23 at 12:12, Hans-Jürgen Schönig wrote: Peter Eisentraut wrote: I was at the ObjectWeb Conference today; ObjectWeb (http://www.objectweb.org) being a consortium that has amassed quite an impressive array of open-source, Java-based middleware under their umbrella, including for instance our old friend Enhydra. And they regularly kept mentioning PostgreSQL in their presentations. To those that are interested in distributed transactions/two-phase commit, I recommend taking a look at Clustered JDBC (http://c-jdbc.objectweb.org/). While this is not exactly the same thing, it looks to be a pretty neat solution for a similar class of applications. In particular, it provides redundancy, load balancing, caching, and even database independence. It is indeed a nice solution but it is far from ready yet. Especially the disaster recovery mechanism and things such as adding new masters need some more work. What I really miss is DECLARE CURSOR. Maybe it will be in there some day :). However, we have done some real testing with sync replication (4 x pg, 1 x oracle). It performed surprisingly well (the JDBC part, not the Oracle one ;) ). Maybe this will be something really useful within the next few months. Cheers, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] statistics about tamp tables ...
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= [EMAIL PROTECTED] writes: Recently I have come across a simple issue which made me think about it. When we create a tmp table (SELECT INTO, CREATE TABLE AS) the planner won't know anything about its content after creating it. Run ANALYZE on the temp table, if you intend to use it enough to justify gathering stats about it. VACUUM is more work than needed. regards, tom lane Of course, VACUUM is on overkill (there is no use to shrink something minimal ;) ). The reason why I came up with this posting is slightly different: Assume a JDBC application which works with PostgreSQL + some other database. If you want to use both databases without PostgreSQL being unnecessarily slow an implicit mechanism would be better. Because otherwise you will have an SQL command in there which is off standard - putting a switch into the application seems to be a fairly ugly solution. regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] statistics about tamp tables ...
Recently I have come across a simple issue which made me think about it. When we create a tmp table (SELECT INTO, CREATE TABLE AS) the planner won't know anything about its content after creating it. Many people use temp tables heavy when the amount of data for a certain analysis has to be reduced significantly. Frequently the same tmp table is queried quite frequently. In order to speed those scenarios up it can be useful to vacuum those tmp tables so that the planner will find more clever joins. Is it possible and does it make sense to generate those statistics on the fly (during CREATE TABLE AS)? Maybe we could have a GUC which tells the system whether to generate statistics or not. test=# select * from test; id 4 4 (2 rows) test=# VACUUM test ; VACUUM test=# explain select * from test ; QUERY PLAN Seq Scan on test (cost=0.00..1.02 rows=2 width=4) (1 row) test=# select * into tmp from test; SELECT test=# explain select * from tmp; QUERY PLAN --- Seq Scan on tmp (cost=0.00..20.00 rows=1000 width=4) (1 row) Best regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Commercial binary support?
Joshua D. Drake wrote: Hello, I think what the person is looking for is: COMPANY PostgreSQL for Red Hat Enterprise 3.0. They probably have some commercial mandate that says that they have to have a commercial company backing the product itself. This doesn't work for most PostgreSQL companies because they back the Open Source version of PostgreSQL. Where someone like Command Prompt, although we happily support the Open Source version, we also sell Command Prompt PostgreSQL. It is purely a business thing, liability and the like. Sincerely, Joshua Drake Hello Tell me if I am significantly wrong but Command Prompt PostgreSQL is nothing more than Open Source PostgreSQL including some application server stuff, some propriertary PL/Perl || PL/PHP and not much more. Your anwer to this statement will be: But it is supported. Can you tell me a reason why somebody should use a closed source version of an Open Source product unless it contains some really significant improvement (say native Win32 or something like that)? Can you tell me ONE reason why this does not work for other PostgreSQL companies such as `eval LONG LIST`? Personally I think everybody can have its business strategy but what REALLY sets me up is that this mail seems to mean that Command Prompt is the only support company around which is actually WRONG! In my opinion everybody who has enough skills can do this kind of job. Being a support company has nothing to do with making a good Open Source product a closed source product. In my opinion giving something a new name and hiding away some code does not mean commercial backing and it does not mean being the god of all support companies. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Commercial binary support?
Robert Treat wrote: If by up to date you mean 7.4, your probably going to have to wait, but I believe that Command Prompt, dbExperts, Red Hat, and SRA all have some type of binary based support available. Don't forget to mention us ... ;). Cheers, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Commercial binary support?
Nigel J. Andrews wrote: On Wed, 19 Nov 2003, Bruce Momjian wrote: Marc G. Fournier wrote: On Wed, 19 Nov 2003, Michael Meskes wrote: On Tue, Nov 18, 2003 at 04:19:35PM -0600, Austin Gonyou wrote: I've been looking all over but I can't seem to see a company that is providing *up-to-date* postgresql support and provides their own supported binaries. Am I barking up the wrong tree entirely here? Why do you insist on their own binaries? I think there are several companies out there providing support for a given version of PostgreSQL and doubt they all ask for their own binaries. At least we do not. We don't either, nor do we worry about specific platforms ... And I know CommandPrompt doesn't care either. I don't even know what it means. If I were to build the 7.4 source, install it somewhere, tarball it up would that then count as providing our own supported binaries (assuming the support service is also offered of course)? Surely it's fairly common for someone to sell support and be happy to include the service of supplying the binaries so if requested, what's so special about it? Nigel Andrews Nigel, The name of the game is warranty. PostgreSQL is BSD license and therefore there is no warranty. A good support company will pick up the risk and fix bugs, backport bugs and features, and provide improved tarballs. There is nothing special - it's just a service. However, it is a service which is necessary because larger companies have to be sure that things are working properly. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Proposal for a cascaded master-slave replication system
Jan, This is EXACTLY what we have been waiting for (years) :) :) :). If you need somebody for testing or documentation just drop me a line. Cheers, Hans Jan Wieck wrote: Hans-Jürgen Schönig wrote: Jan, First of all we really appreciate that this is going to be an Open Source project. There is something I wanted to add from a marketing point of view: I have done many public talks in the 2 years or so. There is one question people keep asking me: How about the pgreplication project?. In every training course, at any conference people keep asking for synchronous replication. We have offered this people some async solutions which are already out there but nobody seems to be interested in having it (my person impression). People keep asking for a sync approach via email but nobody seems to care about an async approach. This does not mean that async is bad but we can see a strong demand for synchronous replication. Meanwhile we seem to be in a situation where PostgreSQL is rather competing against Oracle than against MySQL. In our case there are more people asking for Oracle - Pg migration than for MySQL - Pg. MySQL does not seem to be the great enemy because most people know that it is an inferior product anyway. What I want to point out is that some people want an alternative Oracle's Real Application Cluster. They want load balancing and hot failover. Even data centers asking for replication did not want to have an async approach in the past. Hans-Jürgen, we are well aware of the high demand for multi-master replication addressing load balancing and clustering. We have that need ourself as well and I plan to work on a follow-up project as soon as Slony-I is released. But as of now, we see a higher priority for a reliable master slave system that includes the cascading and backup features described in my concept. There are a couple of different similar product out there, I know. But show me one of them where you can failover without becoming the single point of failure? We've just recently seen ... or better where not able to see anything any more how failures tend to ripple through systems - half of the US East Coast was dark. So where is the replication system where a slave becomes the master, and not a standalone server. Show me one that has a clear concept of failback, one that has hot-join as a primary design goal. These are the features that I expect if something is labeled Enterprise Level. As far as my ideas for multi-master go, it will be a synchronous solution using group communication. My idea is group commit instead of 2-Phase ... and an early stage test hack has replicated some update 3 weeks ago. The big challange will be to integrate the two systems so that a node can start as an asynchronous Slony-I slave, catch up ... and switch over to synchronous multimaster without stopping the cluster. I have no clue yet how to do that, but I refuse to think smaller. Jan -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] bug in 7.4 ...
I have seen that a bug related to duplicated keys is in 7.4rc2. As far
as I have seen a bug like that has already been discovered during the
7.3 era. Is this bug going to be fixed?
Here s the description:
DROP TABLE public.testtabelle;
begin;
CREATE TABLE public.testtabelle
(
c000 varchar(20),
c001 int4 NOT NULL
) WITH OIDS;
create unique index prim_index_testtabelle on public.testtabelle
(c001);
delete from public.testtabelle;
insert into public.testtabelle values ('a', 1);
insert into public.testtabelle values ('b', 2);
insert into public.testtabelle values ('c', 3);
-- insert into public.testtabelle values ('d', 4);
insert into public.testtabelle values ('e', 5);
insert into public.testtabelle values ('d', 4);
delete from public.testtabelle where c001 = 3;
update public.testtabelle set c001 = c001 - 1 where c001 3;
select * from public.testtabelle;
commit;
this will fail on Pg 7.3.3 and Pg 7.4 rc2. However, if 4 is inserted
before 5 it will work for some reason.
does anybody have an explanation for this behaviour?
Cheers,
Hans
--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706 or +43/660/816 40 77
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Call for port reports
Regression testing on AIX 5 using 7.4beta5: polymorphism ... ok stats... ok == shutting down postmaster == == All 93 tests passed. == bash-2.05$ uname -a AIX sn2 1 5 0044276A4C00 checking build system type... powerpc-ibm-aix5.1.0.0 checking host system type... powerpc-ibm-aix5.1.0.0 checking which template to use... aix bash-2.05$ gcc -v Reading specs from /usr/bin/../lib/gcc-lib/powerpc-ibm-aix5.1.0.0/2.9-aix43-010414/specs gcc version 2.9-aix43-010414 Good work :) Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Parallel postgresql
Bruce Momjian wrote: Martin Rusoff wrote: I was just contemplating how to make postgres parallel (for DSS applications)... Has anyone done work on this? It looks to me like there are a couple of obvious places to add parallel operation: Stage 1) I/O , perhaps through MPIO - would improve tablescanning and load/unload operations. One (or more) Postgresql servers would use MPIO/ROMIO to access a parallel file system like PVFS or GPFS(IBM). Stage 2) Parallel Postgres Servers, with the postmaster spawning off the server on a different node (possibly borrowing some code from GNU queue) and doing any buffer twiddling with RPC for that connection, The client connection would still be through the proxy on the postmaster node? (kind of like MOSIX) One idea would be to throw parts of the executor (like a table sort) to different machines or to different processors on the same machine, perhaps via dblink. You could use threads to send several requests and wait for their results. Threading the entire backend would be hard, but we could thread some parts of it by having slave backends doing some of the work in parallel. This would be nice - especially for huge queries needed in warehouses. Maybe it could even make sense to do things in par. if there is just one machine (e.g. computing a function while a sort process is waiting for I/O or so). Which operations can run in par.? What do you think? I guess implementing something like that means 20 years more work on the planner ... By the way: NCR has a quite nice solution for problems like that. Teradata has been designed to run everything on multiple nodes (they call it AMPs). Teradata has been designed for A LOT OF data and reporting purposes. There are just three problems: - not Open Source - ~$70k / node - runs on Windows and NCR's UNIX implementation. Is anybody familiar with Teradata? Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] 2-phase commit
I'm tired of this kind of 2PC is too slow arguments. I think Satoshi, the only guy who made a trial implementation of 2PC for PostgreSQL, has already showed that 2PC is not that slow. Where does Satoshi's implementation sit right now? Will it patch to v7.4? Can it provide us with a base to work from, or is it complete? It is not ready yet. You can find it at ... http://snaga.org/pgsql/ It is based on 7.3 * the 2-phase commit protocol (precommit and commit) * the multi-master replication using 2PC * distributed transaction (distributed query) current work * restarting (from 2nd phase) when the session is disconnected in 2nd phase (XLOG stuffs) * XA compliance future work * hot failover and recovery in PostgreSQL cluster * data partitioning on different servers I have compiled it a while ago. Seems to be pretty nice :). Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 2-phase commit
Why would you spent time on implementing a mechanism whose ultimate benefit is supposed to be increasing reliability and performance, when you already realize that it will have to lock up at the slightest sight of trouble? There are better mechanisms out there that you can use instead. If you want cross-server transactions, what other methods are there that are more reliable? It seems network unreliability is going to be a problem no matter what method you use. I guess we need something like PITR to make this work because otherwise I cannot see a way to get in sync again. Maybe I should call the desired mechanism Entire cluster back to transaction X recovery. Did anybody hear about PITR recently? How else would you recover from any kind of problem? No matter what you are doing network reliability will be a problem so we have to live with it. Having some going back to something consistent is necessary anyway. People might argue now that committed transactions might be lost. If people knew which ones, its ok. 90% of all people will understand that in case of a crash something evil might happen. Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Thoughts on maintaining 7.3
Joshua D. Drake wrote: eh.. i could see some things, like tsearch2 or pg_autovacuum, which afaik are almost if not completely compatible with 7.3, which will not get back ported. Also fixes in some of the extra tools like psql could be very doable, I know I had a custom psql for 7.2 that back patched the \timing option and some of the pager fixes. now, weather that could be done with stuff closer to core, i don't know... Sure but businesses don't like to upgrade unless they have too. If we really want to attract more business to using PostgreSQL then they need to feel like they don't have to upgrade every 12 months. Upgrading is expensive and it rarely goes as smoothly as a dump/restore. I have made the following experience: If a new application is deployed and if it stays unchanged 99% of all bugs in the database or the software itself will be found within a comparatively short amount of time. If a business partner decides to continue to work on his application (which means changing it) he will accept new PostgreSQL releases. Up to now upgrading PostgreSQL has never been a problem because have expected major releases to be stable. In addition to that dump/restore worked nicely. I remember having slightly more work when we switched to 7.3 because somehow type casts are handled differently (less implicit casts - I think that was the problem) but for that purpose intelligent customers have testing environments so that nothing evil can happen on the production system. I don't think back porting features is a good idea. As Marc said: PostgreSQL is the kernel and not an ordinary package. Personally I think that a database product should always be a rock solid product. Unless applications such as, let's say, xclock, database are truly critical and customers won't forget about releases eating data. However, in my opinion they can understand that maintenance is necessary. When you deal with the systems I do, the cost to a customer to migrate to 7.4 would be in the minimum of 10,000-20,000 dollars. They start to ask why were upgrading with those numbers. What did you do to cause these costs? We have several huge and critical customers as well but none of them would cause costs like that. If everything works nicely: Why would you change the release anyway? Why would you back-port new features if you don't accept downtimes? If something has been working for months there are not that many bugs you can expect. In case of disaster there are still options to fix bugs. That's what commercial guys are here for. Fortunately we haven't ever seen a situation in which something really severe has been broken. Buffer overflows: Usually this kind of bugs can be fixed within just a few lines. I have been working with PostgreSQL for 4 years now. All together I have encountered 3-4 bugs which caused me some headache and which I haven't known. I guess 1 per year is more than acceptable. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] 2-phase commit
Marc G. Fournier wrote: On Sat, 27 Sep 2003, Bruce Momjian wrote: I have been thinking it might be time to start allowing external programs to be called when certain events occur that require administrative attention --- this would be a good case for that. Administrators could configure shell scripts to be run when the network connection fails or servers drop off the network, alerting them to the problem. Throwing things into the server logs isn't _active_ enough. Actually, apparently you can do this now ... there is apparently a mail module for PostgreSQL that you can use to have the database send email's out ... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster I guess someting such as CREATE TRIGGER my_trig ON BEGIN / COMMIT EXECUTE ... would be nice. I think this can be used for many perposes (not necessarily 2PC). If a trigger could handle database events and not just events on tables. ON BEGIN ON COMMIT ON CREATE TABLE , ... We could have used that so often in the past in countless applications. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Question regarding coopting Database Engine
Steve Yalovitser wrote: Hello, I'd like to know if its possible to coopt the postgres storage subsystem to rely entirely on ram based structures, rather than disk. Any documentation or ideas would be appreciated. Cheers, Steve ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Why don't you just define a RAM disk and run PostgreSQL on it? ;) Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706 or +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Building from CVS issues
[EMAIL PROTECTED] wrote: Hi All, I'm attempting to build from CVS so I can get into a bit of PG dev work. Are there any special tricks with the CVS build or is this a common error? bison -y -d preproc.y preproc.y:5276: warning: previous rule lacks an ending `;' preproc.y:6294: fatal error: maximum table size (32767) exceeded make[4]: *** [preproc.h] Error 1 make[4]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces/ecpg/preproc' make[3]: *** [all] Error 2 make[3]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/home/jason/pgsql/pgsql/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/home/jason/pgsql/pgsql/src' make: *** [all] Error 2 You will need a more recent version of Bison in order to build. Older versions of Bison cannot handle packages that huge anymore. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ecpg build on AIX 4.2.1
Michael Meskes wrote: On Tue, Sep 23, 2003 at 04:21:10PM -0400, Samuel A Horwitz wrote: I am getting Undefined symbols in build ecpg ... ld: 0711-317 ERROR: Undefined symbol: .PQfinish ld: 0711-317 ERROR: Undefined symbol: .PQexec ld: 0711-317 ERROR: Undefined symbol: .PQclear ld: 0711-317 ERROR: Undefined symbol: .PQresultErrorField ld: 0711-317 ERROR: Undefined symbol: .PQsetdbLogin ld: 0711-317 ERROR: Undefined symbol: .PQstatus ld: 0711-317 ERROR: Undefined symbol: .PQsetNoticeReceiver ld: 0711-317 ERROR: Undefined symbol: .PQerrorMessage ld: 0711-317 ERROR: Undefined symbol: .last_path_separator collect2: ld returned 8 exit status gmake[4]: *** [libecpg_compat.so] Error 1 gmake[4]: Leaving directory `/usr/local/postgres/pgsql/src/interfaces/ecpg/compatlib' Looks like a missing -lpq but i wonder why you get it in compatlib. No libpq function is called there directly. Michael Correct. I had the same error on AIX 5.1 last week (see hackers' list). As far as 7.4beta is referred two additional patches are needed (see postings by Tom Lane on this topics). Adding the linker options will solve your problems and PostgreSQL will work nicely. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] ecpg build on AIX 4.2.1
Samuel A Horwitz wrote: Are these patches going to be applied soon? Correct. I had the same error on AIX 5.1 last week (see hackers' list). As far as 7.4beta is referred two additional patches are needed (see postings by Tom Lane on this topics). Adding the linker options will solve your problems and PostgreSQL will work nicely. Regards, Hans puh, good question. I think I have seen that Tom has applied at least one. I think the one you have requested has not been applied yet. You have to check the COMMITTERS' list to find out. To be honest I don't have it in mind. Tom or Michael Meskes will know for sure. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PostgreSQL 7.4beta3 does not compile on AIX 5 ...
I have tried to perform a regression test on AIX 5.1 (PostgreSQL 7.4beta3). I have encountered an error. gmake[3]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/libpq' gmake[3]: Entering directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg' gmake -C include all gmake[4]: Entering directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg/include' gmake[4]: Nothing to be done for `all'. gmake[4]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg/include' gmake -C pgtypeslib all gmake[4]: Entering directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg/pgtypeslib' gcc -O2 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/interfaces/ecpg/include -I../../../../src /include/utils -I../../../../src/include -g -c -o numeric.o numeric.c gcc -O2 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/interfaces/ecpg/include -I../../../../src /include/utils -I../../../../src/include -g -c -o datetime.o datetime.c gcc -O2 -pipe -Wall -Wmissing-prototypes -Wmissing-declarations -I../../../../src/interfaces/ecpg/include -I../../../../src /include/utils -I../../../../src/include -g -c -o common.o common.c common.c: In function `pgtypes_fmt_replace': common.c:94: union has no member named `int64_val' gmake[4]: *** [common.o] Error 1 gmake[4]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg/pgtypeslib' gmake[3]: *** [all] Error 2 gmake[3]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces/ecpg' gmake[2]: *** [all] Error 2 gmake[2]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src/interfaces' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/src' PostgreSQL 7.3.4 works nicely (gmake, GCC 2.95) for me (production system). Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/660/816 40 77 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL 7.4beta3 does not compile on AIX 5 ...
Tom Lane wrote: we have fixed the first problem. here is the next one ... libm seems to be missing although it is installed (I have installed it for running 7.3.4). It looks like -lm needs to be added to SHLIB_LINK in ecpglib/Makefile. I had already proposed this patch for SSL-enabled builds: *** src/interfaces/ecpg/ecpglib/Makefile.orig Fri Aug 1 12:46:18 2003 --- src/interfaces/ecpg/ecpglib/Makefile Tue Sep 16 01:29:43 2003 *** *** 21,27 OBJS= execute.o typename.o descriptor.o data.o error.o prepare.o memory.o \ connect.o misc.o ! SHLIB_LINK = -L../pgtypeslib -lpgtypes $(libpq) $(THREAD_LIBS) all: all-lib --- 21,27 OBJS= execute.o typename.o descriptor.o data.o error.o prepare.o memory.o \ connect.o misc.o ! SHLIB_LINK = -L../pgtypeslib -lpgtypes $(libpq) $(filter -lssl, $(LIBS)) $(THREAD_LIBS) all: all-lib and based on your report I guess it actually needs to be filter -lssl -lm. Please try it? regards, tom lane i have applied the patch below. i have added -lm to ecpglib/Makefile in addition to that i had to add -lm -L../../libpq -lpq to compatlib/Makefile now it seems to compile properly. can anybody verify that? regression test on RS/6000 (2 x 375 Mhz), AIX 5.1; GCC 2.95: gmake[3]: Leaving directory `/usr/src/shopnet/postgresql-7.4beta3/contrib/spi' /bin/sh ./pg_regress --temp-install --top-builddir=../../.. --schedule=./parallel_schedule --multibyte=SQL_ASCII == removing existing temp installation== == creating temporary installation== == initializing database system == == starting postmaster== running on port 65432 with pid 17596 == creating database regression == CREATE DATABASE ALTER DATABASE == dropping regression test user accounts == == installing PL/pgSQL== == running regression test queries== parallel group (13 tests): int2 int4 text name int8 boolean float4 oid char float8 varchar bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... ok test numerology ... ok parallel group (20 tests): point comments path reltime date interval lseg abstime time tinterval polygon circle timetz box inet timestamptz timestamp type_sanity oidjoins opr_sanity point... ok lseg ... ok box ... ok path ... ok polygon ... ok circle ... ok date ... ok time ... ok timetz ... ok timestamp... ok timestamptz ... ok interval ... ok abstime ... ok reltime ... ok tinterval... ok inet ... ok comments ... ok oidjoins ... ok type_sanity ... ok opr_sanity ... ok test geometry ... ok test horology ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... ok parallel group (7 tests): create_aggregate create_operator vacuum triggers inherit constraints create_misc constraints ... ok triggers ... ok create_misc ... ok create_aggregate ... ok create_operator ... ok inherit ... ok vacuum ... ok parallel group (2 tests): create_view create_index create_index ... ok create_view ... ok test sanity_check ... ok test errors ... ok test select ... ok parallel group (17 tests): select_distinct_on select_into select_distinct transactions update aggregates select_having case random subselect union select_implicit hash_index btree_index arrays portals join select_into ... ok select_distinct ... ok select_distinct_on ... ok select_implicit ... ok select_having... ok subselect... ok union... ok case ... ok join ... ok aggregates ... ok transactions ... ok random ... failed (ignored) portals
Re: [HACKERS] Is it a memory leak in PostgreSQL 7.4beta?
This is the Pg backend line from top after about 90 minutes runtime : PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 16083 postgres 17 0 9172 9172 8524 R 94.7 2.4 84:59.68 postmaster No sign of the shared growth stopping at this stage... Pg built with --disable-debug --without-zlib Platform is Linux 2.4.21+ xfs (Mandrake 9.2beta) regards Mark I can hardly imagine that the backend started working with 9mb of memory. what did you do that PostgreSQL needed so much memory from the beginning??? are you using the default settings? usually the postmaster does not need more than 3mb at startup (in this scenario). Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Is it a memory leak in PostgreSQL 7.4beta?
I am seeing a slow but steady growth of the backend process on a Linux box (RHL 8.0) --- top shows it growing a few K every few seconds. But I see *zero* growth with the same test on HPUX 10.20. A possible wild card is that the Postgres build I'm using on the Linux box is compiled for profiling (-pg, no --enable-debug or --enable-cassert) whereas the HPUX build has --enable-debug and --enable-cassert but no profiling. I'm not aware that there's any known memory leakage in Linux' profiling support, though. Can anyone else reproduce this, or confirm they don't see it? What platform, and what configure options? I have tried it on our Ultra Sparc 10 running Debian (Woody). Same problem. The postmaster starts at around 2.2mb and keeps allocating memory :( Maybe I can test it on AIX within the next two weeks (still waiting for the machine to come). Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Is it a memory leak in PostgreSQL 7.4beta?
I have done some beta testing with PostgreSQL 7.4beta2.
I have run a simple set of SQL statements 1 million times:
-- START TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO t_data (data) VALUES ('2500');
UPDATE t_data SET data = '2500' WHERE data = '2500';
DELETE FROM t_data WHERE data = '2500';
-- COMMIT;
The interesting thing was that my postmaster needed around 4mb of RAM
when I started running my test script using ...
psql test script.sql
After about 2 1/2 hours the backend process already needed 11mb of ram.
looking at the output of top you can see that it seems to be in the
shared memory area:
PID USER PRI NISIZE RSS SHARE STAT%CPU%MEM TIME
COMMAND
28899 hs39 19 11456 11M 10620R N 89.8 2.9
150:23 postmaster
this seems very surprising to me because I have no explanation why
PostgreSQL should consume so much more memory than at the beginning of
the test.
There are no trigger or something like that around.
The table I am working on consist of two columns (one timestamp, one int4).
In addition to that I have made a test with a different set of SQL
statements. I have tried 1500 concurrent transaction on my good old AMD
Athlon 500 box running RedHat Linux. It worked out pretty fine. The
thing I came across was that my memory consumption raised during the
first two hours of my test (from about 1 1/2 gigs to 1.7 gigs ram).
pretty surprising as well.
does anybody have an explanation for this behaviour?
Regards,
Hans
I have run 1500 concurrent transactions on an AMD Athlon box (RedHat 9).
--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(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] dblink_ora - a first shot on Oracle ...
There seems to be some disagreement on whether the Oracle lib checks should be in configure for a /contrib module, and I don't know how far Hans is. I will say we are probably looking at 7/28 for beta. I am afraid I won't make it until 7.4beta1. The problem is that I have not managed to have more than just one connection to oracle at the same time. For some Oracle reason I don't know what went wrong. I will have a closer look at that. However, named connections don't make too much sense with just one connection ;). I have troubles testing it as long as the connect fails ... There is some other Oracle specific stuff which seems to be more painful than I have expected. Have I already told you that I hate Oracle? Let's wait for the next major release and prepare something really useful. I am still not quite satisfied with what we have at the moment. Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
They do the backend protocol using a custom implementation. Why would they do that? It seems as if their implemenation provides 20% more throughput. I haven't benchmarked with lib pq personally so I cannot tell you more. Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
But the snapshots only are grabbing the xids from each proc, right? Doesn't seem that would take very long. If this is the bottleneck, maybe we need a shared proc lock. I had a hard day testing and verifying this kind of stuff. We have run several hundred benchmarks at the customer using many different settings. SERIALIZABLE was the key to high-performance. I have run dozens of different benchmarks today (cursors, simple selects, concurrent stuff, ...). I have not found a difference. I have no idea why the customer's system was so much faster in SERIALIZABLE mode. They use a native C++ implementation of the FE/BE protocol but as far as I have seen their database layer does not care about transaction isolation too much. I will continue testing this kind of stuff because this is a very strange yet important issue. I will try to get some code from the customer. This is mostly non-disclosure stuff so I am not sure what we can use. I just wanted to ask if somebody has a reasonable explanation and if somebody can verify this behaviour. Maybe we will find the reason some day :(. Sorry that I cannot provide more information at the moment. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostgreSQL 7.3.3 and Intel C compiler
Bruce Momjian wrote: Hans-J?rgen Sch?nig wrote: This week I have done some performance tuning at a customer's office. We have beaten (demoralized) MS SQL and DB2 in serializable mode and DB2 in any transaction isolation level :). By the way: In case of very simple statements SERIALIZABLE is about 3 times faster than READ COMMITTED. I expected it to be faster but I did not expect this difference. Why was SERIALIZABLE faster? I know SERIALIZABLE doesn't have the rollback penalty in read-only queries, but I don't understand why it would be faster. To be honest I don't have the slightest idea. Maybe it has to do with snapshotting but I don't know precisely. In case of SERIALIZABLE all snapshots inside a transaction are the same - maybe this makes the big difference. I have no other explanation for that. There is one nifty detail which seems VERY strange to me: If serializable mode is set in postgresql.conf the system was 3 times faster (~ 7.5 sec. vs. 2.5sec). If serializable mode was set for every transaction (using set at the beginning of the transaction) serializable mode was as fast as read committed. We have done 90% cursor work and very simple queries (mostly queries such as DECLARE CURSOR x FOR SELECT * FROM ... WHERE a = b). I have no idea why PostgreSQL behaves like that but it seems to be a really good tweak because in this mode we beat any other database including SQL server on Windows 2003 (2.9sec) and IBM DB2 on Linux (12.6 seconds). I am sorry but I cannot provide you the tools we have used because we have a non disclosure agreement with the customer. I will try to verify this with my machines and a simple self-made benchmark. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
I think for that very reason (SQL-MED) we need to come to terms with this issue. If/when connections to external data sources is in the backend, you'll have those exact same dependencies. And in fact, we do today: consider '--with-openssl' or '--with-tcl'. I had always assumed we would need '--with-oracle', '--with-jdbc', etc (or whatever you want to call them) to support backend connections to external sources. And this discussion is the very reason I was hesitant to pursue dblink_ora or jdbclink now, because I didn't think people would be comfortable with configure options to support a contrib library. Joe If dblink was a core module I would say that adding the configure stuff would be very natural. Since this is contrib stuff I was not that sure about configure anymore. We will need additional flags for external data sources in the (hopefully) near future so I think we should add it. Personally I tend to think about a solution like that. dblink has a great future and many people simply love it (I cannot think of a customer who does not like it - it is a killer feature): - implement the concepts proposed by Joe on this list yesterday (I am talking about the functions dblink should provide) - add support to configure - merge dblink with dblink_ora as soon as the changes are ready - adapt jdbc_link and merge it with dblink - implement dblink_db2, dblink_csv, dblink_xml, and maybe some others - leave it in contrib because this way it will be shipped with the core distribution and people will use it more frequently I hope that I will finish the Oracle stuff (named connections, ...) within the next 3 days. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
Bruce Momjian wrote: Hans, I am a little confused about what you are suggesting. Are you suggesting flag to the make of the contrib module rather than configure tests? I agree this is a killer feature for many people and would like to have it in 7.4. Under these circumstances I was thinking of integrating it into the main configuration mechanism - not just for contrib. We will need it for cross db suff later on anyway. Sorry for the confusion. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
Bruce Momjian wrote: Joe, I can do the configure detection of the Oracle library needed for /contrib. I don't think we follow the beta freeze as much for /contrib stuff, but this particular /contrib is more integrated into the main system than most. If you want to merge it in during the next month, I can do the configure work for you. If I am wrong about the /contrib deadlines, someone please let me know. As I remember, we allow /contrib authors to change things up to the end because it is no an integral part of the system. Bruce, Thanks a lot. I will integrate named connections as proposed by the most recent version of dblink as soon as possible. Thanks for doing the configure stuff. What we need is Oracle's OCI interface and libsqlora (http://www.poitschke.de/libsqlora8/). Also; I have done some work on an improved version of jdbclink but it does not work yet (supports persistent connections and so forth). Joe, you have told me some time ago that you are planning some sort of connection table which tells dblink to establish some connections automatically. Did you work on that? I hope we can merge it in a useful way. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Exception table ...
I have just seen a nice feature provided by DB2 which seems very useful to me. When importing huge amounts of data (dozens of gigs) with the help of COPY errors might occur from time to time (especially when migrating). The problem with COPY is that it stops after the first error. So if the first problem occurs after 200.000.000 records it is somehow annoying to do the entire stuff again. If we had an option telling COPY to log all problems into a logtable or into a separate logfile we could finish the import and rollback the transaction after trying to import everything. This would help a lot when migrating or importing a lot of data because all problems with an import could be fixed at once based on the exception table. Did anybody think about a feature like that in the past? Does it make sense to the group? Best regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] DB2's row_number()
I had a brief look at DB2's row_number function which seems to be pretty useful. What it does is: test=# SELECT row_number(), relname FROM pg_class LIMIT 3; row_number |relname + 1 | pg_description 2 | pg_group 3 | pg_proc (3 rows) This makes sense to me and I need this feature from time to time. My question is: How do I find out when a query starts? Inside a table function I can call SRF_IS_FIRSTCALL() to see when it is called first. Is there an easy way to check that inside an ordinary C function returning just one value? Currently my function counts the number of times it has been called per connection. I could write a second function for resetting the counter but this is not too smart ... Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] dblink for Oracle - question ...
A few days ago I have posted a pre-beta version of dblink_ora which is supposed to solve some problems we had here at Cybertec (getting data from an Oracle DB and merge it with PostgreSQL). I have implemented a simple piece of code (more proof of concept than production). Since I have not got too much response (just one posting off list) I expect that there are not too many people who are in need of this feature. Am I right or is there somebody out there who wants to see it in contrib? If there is serious interest in this feature we'd make it work with PostgreSQL's build system and we'd add some documentation as well as some more code. If not we will keep using it for internal purposes. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] dblink_ora - a first shot on Oracle ...
Joe Conway wrote: Bruce Momjian wrote: OK, can you take ownership of it? You mean a TODO entry? Sure, as long as Hans is OK with it. Joe I am ok with it. The only problem I have at the moment is that I don't know how to build properly and to check for the libs needed by Oracle. The entire code is built on a library by a German developer because the OCI interface itself is more than just insane (you will need 1 lines of code to establish a connection to the server). As soon as I have all I need it should not be a huge task to finish the code. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Table functions and AS clauses ...
Currently I am able to do ...
SELECT dblink_oraconnect('scott/[EMAIL PROTECTED]');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
The problem is: If I wanted to do a
SELECT * FROM dblink_ora('SELECT * FROM emp');
It won't work because dblink_ora returns SETOF RECORD. If could define a
type representing the remote data structure.
However, my question is: Is there a way to implement it in a way that
does not depend on the remote data structure?
Is there a way to get rid of the AS without having to define a type?
Maybe somebody can provide me some information about that or point to me
to some material about this topic.
If we had a solution for that, we could go ahead and do some basic OLAP
operations. Imagine we had a cube generated by the means of SQL.
We could do
SELECT * FROM some_olap_operation(cube, ...);
without worrying about the elements in a certain dimension which should
be displayed in the x-axis (as columns).
If there was a nice workaround or a solution I'd be really happy :).
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] dblink_ora - a first shot on Oracle ...
Hi there ...
I have spent some time working on an Oracle version of dblink. It works
quite nicely for me and I hope it does for others.
It already supports some basic features such as persistent connection
and fetching data. This is not a perfect piece of software and there is
lot of room for enhancing this stuff.
If there is somebody out there who is interesting in this kind of stuff
I would be glad.
Maybe I will have some time in the next few days so that I can provide
an interface for flat files and some other database such as Berkley DB
as well. Maybe there will also be a version for MySQL but this one will
be used for MIGRATION purposes only. In other words: I won't touch MySQL
- just for migration and to get rid of it.
Personal thanks to Joe Conway, most of the code has been stolen from him.
Here is what you can do with the Oracle version:
SELECT dblink_oraconnect('scott/[EMAIL PROTECTED]');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'BEGIN', dblink_oraexec('BEGIN');
SELECT 'UPDATE emp SET sal = sal - 1',
dblink_oraexec('UPDATE emp SET sal = sal - 1');
SELECT 'ROLLBACK', dblink_oraexec('ROLLBACK');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'BEGIN', dblink_oraexec('BEGIN');
SELECT 'UPDATE emp SET sal = sal + 1',
dblink_oraexec('UPDATE emp SET sal = sal + 1');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'UPDATE emp SET sal = sal - 1',
dblink_oraexec('UPDATE emp SET sal = sal - 1');
SELECT 'COMMIT', dblink_oraexec('COMMIT');
SELECT dblink_oradisconnect();
[EMAIL PROTECTED] dblink_ora]$ psql test func.sql
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
dblink_oraconnect
---
OK
(1 row)
NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
+--
SMITH | 798
ALLEN | 1598
WARD | 1248
JONES | 2973
MARTIN | 1248
BLAKE | 2848
CLARK | 2448
SCOTT | 2998
KING | 4998
TURNER | 1498
ADAMS | 1098
JAMES | 948
FORD | 2998
MILLER | 1298
(14 rows)
NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
--+
UPDATE emp SET sal = sal - 1 | 14
(1 row)
NOTICE: Affected: 0
?column? | dblink_oraexec
--+
ROLLBACK | 0
(1 row)
NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
+--
SMITH | 798
ALLEN | 1598
WARD | 1248
JONES | 2973
MARTIN | 1248
BLAKE | 2848
CLARK | 2448
SCOTT | 2998
KING | 4998
TURNER | 1498
ADAMS | 1098
JAMES | 948
FORD | 2998
MILLER | 1298
(14 rows)
NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
--+
UPDATE emp SET sal = sal + 1 | 14
(1 row)
NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
+--
SMITH | 799
ALLEN | 1599
WARD | 1249
JONES | 2974
MARTIN | 1249
BLAKE | 2849
CLARK | 2449
SCOTT | 2999
KING | 4999
TURNER | 1499
ADAMS | 1099
JAMES | 949
FORD | 2999
MILLER | 1299
(14 rows)
NOTICE: Affected: 14
?column? | dblink_oraexec
--+
UPDATE emp SET sal = sal - 1 | 14
(1 row)
NOTICE: Affected: 0
?column? | dblink_oraexec
--+
COMMIT | 0
(1 row)
dblink_oradisconnect
--
OK
(1 row)
Regards,
Hans
--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at
dblink_ora.tar.gz
Description: GNU Zip compressed data
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Pre-allocation of shared memory ...
Yeah, I see it in the Mandrake kernel. But it's not in stock 2.4.19, so you can't assume everybody has it. We had this problem on a recent version of good old Slackware. I think we also had it on RedHat 8 or so. Doing this kind of killing is definitely a bad habit. I thought it had it had to do with something else so my proposal for pre-allocation seems to be pretty obsolete ;). Thanks a lot. Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Pre-allocation of shared memory ...
There is a problem which occurs from time to time and which is a bit nasty in business environments. When the shared memory is eaten up by some application such as Apache PostgreSQL will refuse to do what it should do because there is no memory around. To many people this looks like a problem relatd to stability. Also, it influences availability of the database itself. I was thinking of a solution which might help to get around this problem: If we had a flag to tell PostgreSQL that XXX Megs of shared memory should be preallocated by PostgreSQL. The database would the sure that there is always enough memory around. The problem is that PostgreSQL had to care more about memory consumption. Of course, the best solution is to put PostgreSQL on a separate machine but many people don't do it so we have to live with memory leaks caused by other software (we have just seen a nasty one in mod_perl). Does it make sense? Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Groups and roles
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: ... Therefore I ask whether everyone agrees that groups and roles are basically equivalent concepts (and perhaps that we might in the future strive to make groups more compatible with the roles as defined in the SQL standard). Or does anyone see that roles might be implemented separately from groups sometime? Just reading section 4.31.3 of the SQL99 draft, it seems that roles are pretty much interchangeable with groups, except that a role can be a member of another role while we don't presently allow groups to be members of other groups. So it seems that your question breaks down to: 1. Do we want to someday allow groups to have groups as members? (Seems reasonable to me.) Makes sense ... 2. Are there any other differences between groups and roles? (I'm not sure about this one.) To me some differences would make sense sense if we had additional priviledges. In Oracle a user needs a punch of rights to connect, to execute SQL, and so forth. If we had these features it would make sense to treat roles and groups seperately because: Imagine having groups having rights on dozens of tables. If these groups were assigned to a role it would be an easy task to block numerous groups from executing SQL at once. Currently a user has all rights of all groups he belongs to so it is damn hard to say that 1000 users should not be allowed to do anything for a period of time (because of maintenance or so). If all those users (but the superuser) had a certain role, the role could be modified instead of those 1000 users/groups (eg. REVOKE login, execute_sql FROM some_role). Currently roles don't make too much sense to me because we don't have the permissions for making roles useful (personally I don't think that it makes to have this stuff anyway). I guess adding a simple field to the system tables would be enough. If we had a field active (bool) in pg_shadow and pg_group we could solve some basic problems such as banning 1 groups at once more easily. This is the only problem we have from time to time. If there is too much user rights stuff around administering will be more painful (see Oracle). Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] SAP and MySQL ...
Hardly. SAP failed on the attempt to open source ADABAS even more miserably than Borland with Interbase. Now it looks like they found someone who said we know open source, we can do that, oh pick me, me, me, pick mee! that's what i think as well. by the way: did you see that MySQL AB has got $19.5mio of new cash. MySQL on the other hand has for sure a big user community and is one of the favorite open source projects of the IT press. What all the lemming-like humpty-dumpty article writers fail to understand is the difference between a user- and a core developer community. The latter mainly consists of 2 people in the MySQL case, Monty and David. i think i haven't seen a single patch for SAP DB on the mailing list in months. i don't think they have a single open source developer. we thought chosing SAP DB as a second database platform (just to be more independent from PostgreSQL) would be a good idea. we have invested a lot of time to find out that they cannot compete with PostgreSQL. Writing precompiler code was painful. Thanks to Michael Meskes and others doing it for PostgreSQL is easy. We tried to port stored procedures returning cursors but it took me a week to find out how to ACCESS this cursor returned by the stored procedure. i tried to install SAP DB on RedHat - it did not even start. have fun, Monty ;). I doubt that those two can drop the support for the existing MySQL user base anytime soon. And while sure converting everything from MySQL to SAPDB would be a good idea, there are probably more people in the world who know how to convert MySQL to PostgreSQL than to SAPDB ... hehe. exactly. converting to sap db is some sort of pain. not so much the data but things such as stored procedures and so forth. still, i think selling postgresql might be more difficult in the future because you don't have real features won't work that nicely anymore. let's hope for the best and let's hope that we can keep kicking their butts in the future. Regards, Hans @ MySQL free zone -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] DATA-CUBE in Postgresql.
Srikanth M wrote: Hi! I am interested in adding DATA-CUBE operator(Related to Data Warehousing) to postgresql. I just want to know wheather it is already added to postgres or not. please reply to this mail if you have already worked or still working on it. Bye Srikanth ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Currently there is just one solution I am aware of. The EFEU package provides a simple interface to PostgreSQL (via its Esh interpreter language) which allows you to do some basic operations related to cubes. If there was a solution which is distributed along with PostgreSQL this would be a damn good thing. How is it supposed to work? I have done a lot of work with EFEU so maybe I can give you some hints since the way EFEU treats cubes makes sense to me. Regards, Hans -- Cybertec Geschwinde u Schoenig Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/2952/30706; +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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] Backends created by ODBC live forever ...
We have an application which syncs Access databases with PostgreSQL (I know that this is VERY ugly). It is a simple script based Access application. People wanted that application because they are familiar with Microsoft stuff. When Access establishes a connection to PostgreSQL everything is just fine. If Access is closed properly everything is still fine - the server side backend dies just the way it is supposed to die. However, if Access crashes or if the dial-up connections goes down the connection is not closed explicitly and the server side backend stays alive forever - even if the Windows client is rebooted. As you can easily imagine that there are MANY dead backends around at the end of the day. Is there a way for PostgreSQL to found out that the connection is lost? This problem occurs in combination with ODBC - I haven't seen it anywhere else up to now. Did anybody encouter similar problems? Regards, Hans http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] System Tables and Triggers
Hi Stef I had the same problem some time ago. I wanted to define a trigger firing on CREATE TABLE (pg_class). This won't work because in most cases system tables are not accessed using the standard way for processing queries (parse - rewrite - plan - execute). Therefore triggers are not allowed. Triggers could lead to conflicts and corruption. I have done some code digging and I have removed the warning you have shown. It does not help - I guess there is no way to get rid of the problem. All you can do is to write a function doing the job for you. System tables don't care about events too much. There used to be a whole thread on this subject matter called Triggers and System Tables - maybe digging the archives will give you a broader view of the problem. Too bad - it would be a nice feature :). In my case I have solved the problem with the help of a view and a set of rules - maybe this will work for you as well. Also, functions are a good choice. Regards, Hans Stef Telford wrote: Hello, First of all, kudos on postgreSQL. That being said, am having a slight problem with it, namely, System tables appear to be 'special tables' and disallow triggers. Perhaps its jst the way i am doing it, however, here is a rather trite example to illustrate (file called system.trig): CREATE OR REPLACE FUNCTION action_upd() RETURNS opaque AS ' BEGIN RAISE NOTICE ''User % added/updated'',NEW.usename; RETURN new; END;' LANGUAGE 'plpgsql'; CREATE TRIGGER mysys_trig AFTER INSERT OR UPDATE on pg_shadow FOR EACH ROW EXECUTE PROCEDURE action_upd(); test1=# \i /data_raid/stefs_home/postgres/system.trig CREATE FUNCTION psql:/data_raid/stefshome/postgres/system.trig:10: ERROR: CreateTrigger: can't create trigger for system relation pg_shadow Now, i know that this is largely a useless example, however the theory was that if this -was- possible, then it would be child's play to create a session table for each user. This would allow the system to store values which would make things easier for the rest of the database and apache system i am using (it would allow me to improve and normalise my schema somewhat). I can also see the above being useful for when you have more than one DBA and would like to log user creation (actually, am thinking more of using pl/perl here and then emailing out each user/modification to all admins) Last time this was tried (around 7.2) it allowed triggers on system tables, but pg_user and pg_shadow and most other tables would have values inserted in outside of the 'INSERT' logic. I can understand that this is great for speed, but it seriously hampers the usefulness of triggers (they wouldnt fire :) This would seem to be a step backwards to me though, as now we cant even put triggers on the system tables. Is there any chance of this being lifted ? (actually, can understand not allowing pg_trigger mainly because it could end up a cyclic trigger very easily :) Thanks and hope this makes some sort of sense :) regards Stef Telford [EMAIL PROTECTED] ---(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 -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL Tuning Results
Gavin Sherry wrote: Hi Chris, On Wed, 12 Feb 2003, Christopher Kings-Lynne wrote: Machine: 256MB RAM, FreeBSD 4.7, EIDE HDD, 1 Ghz Seems like a small amount of memory to be memory based tests with. What about testing sort_mem as well. It would system to me that there would be no negative to having infinite sort_mem given infinite memory, though. Gavin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Be careful with sort_mem - this might lead to VERY unexpected results. I did some testing on my good old Athlon 500 with a brand new IBM 120 Gigs HDD. Reducing the sort_mem gave me significantly faster results when sorting/indexing 20.000.000 randon rows. However, it would be nice to see the results of concurrent sorts. Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] COUNT and Performance ...
But pgstattuple does do a sequential scan of the table. You avoid a lot of the executor's tuple-pushing and plan-node-traversing machinery that way, but the I/O requirement is going to be exactly the same. I have tried it more often so that I can be sure that everything is in the cache. I thought it did some sort of stat on tables. Too bad :(. If people want to count ALL rows of a table. The contrib stuff is pretty useful. It seems to be transaction safe. Not entirely. pgstattuple uses HeapTupleSatisfiesNow(), which means you get a count of tuples that are committed good in terms of the effects of transactions committed up to the instant each tuple is examined. This is in general different from what count(*) would tell you, because it ignores snapshotting. It'd be quite unrepeatable too, in the face of active concurrent changes --- it's very possible for pgstattuple to count a single row twice or not at all, if it's being concurrently updated and the other transaction commits between the times pgstattuple sees the old and new versions of the row. Interesting. I have tried it with concurrent sessions and transactions - the results seemed to be right (I could not see the records inserted by open transactions). Too bad :(. It would have been a nice work around. The performance boost is great (PostgreSQL 7.3, RedHat, 166Mhz I think your test case is small enough that the whole table is resident in memory, so this measurement only accounts for CPU time per tuple and not any I/O. Given the small size of pgstattuple's per-tuple loop, the speed differential is not too surprising --- but it won't scale up to larger tables. Sometime it would be interesting to profile count(*) on large tables and see exactly where the CPU time goes. It might be possible to shave off some of the executor overhead ... regards, tom lane I have tried it with the largest table on my testing system. Reducing the overhead is great :). Thanks a lot, Hans -- *Cybertec Geschwinde u Schoenig* Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria Tel: +43/1/913 68 09; +43/664/233 90 75 www.postgresql.at http://www.postgresql.at, cluster.postgresql.at http://cluster.postgresql.at, www.cybertec.at http://www.cybertec.at, kernel.cybertec.at http://kernel.cybertec.at ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
