Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default
On Jun 11, 2004, at 1:02 PM, Christopher Kings-Lynne wrote: 3. Or even create a pg_get_sequence() function: SELECT SETVAL(pg_get_sequence(schema.table, col), 17); Actually, this is the best solution :) John Hansen and I worked this up. It works, though it's not schema-aware, afaict. create or replace function last_val( text -- tablename , text -- colname ) returns bigint language 'sql' as ' select currval( (select split_part(adsrc,\'\'\'\',2) as seq from pg_class join pg_attribute on (pg_class.oid = pg_attribute.attrelid) join pg_attrdef on (pg_attrdef.adnum = pg_attribute.attnum and pg_attrdef.adrelid = pg_attribute.attrelid) where pg_class.relname = $1 and pg_attribute.attname = $2) ); '; Might be a starting point. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Nested transactions and tuple header info
On Tue, Jun 01, 2004 at 06:40:07PM -0400, Bruce Momjian wrote: When DELETE a happens, we remove the xmin=1 from the tuple header and replace it with xmin=3. xid=3 will be marked as committed if xid2 aborts, and will be marked as aborted if xid3 commits. So, if xid2 aborts, the insert of xid1 should be honored, and xid3 is marked as committed, and the opposite if xid2 commits. Ok, I've been looking at implementing this. However it just occurred to me that a transaction, different from the one modifying the tuple, could try to see its xmax. Since the xmin signals the tuple as being updated concurrently by another transaction (it's in progress), this can only happen if the other transaction tries to read it using SnapshotDirty. One such possible caller is EvalPlanQual. It could go to sleep using XactLockTableWait() on the SnapshotDirty's xmax. But the tuple has something strange in its xmax -- it's the tuple's cmin actually. Leaving this would be probably a bug. However, if the tuple is new, then EvalPlanQual won't even try to see it. Or maybe it will. And then, maybe there are other callers (I can only see _bt_check_unique). Do I have to worry about this? Maybe (probably) it's not a problem, but I want to be sure. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso. (Ernesto Hernández-Novich) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Accelerating aggregates
Stop me if you've heard this before. I'm looking at fast calculation of aggregates (sum(), max(), count()) across large tables, or across fairly simply defined subsets of those tables. Lets say that, for a given aggregate function on a given table (with a given where clause, perhaps), each postgres process maintains a state variable (stype, in aggregate terms) and there's a also a single state variable available to all backends via shared memory. Each time a transaction starts the process initialises its local state variable to the initcond of the aggregate. Each time a row is inserted into the table the local state variable is updated, using the aggregate update function. Each time a row is removed then the local state variable is either updated, or invalidated, using a reverse-update function, again specific to the aggregate. If the transaction is rolled back, the local state variable is thrown away. If the transaction is commited and the local state variable has been invalidated then the global state variable is invalidated, otherwise the global state variable is updated using a a state merge function, specific to the aggregate. Then, at any point, the correct value of the aggregate function, taking into account transactions that commited before the current transaction started can be found by merging the global state variable and the local one. If the global state variable isn't valid (because it's been explicitly invalidated, or the system has just started up or the caching of the value was just enabled) then it can be calculated by running the real aggregate function against the table. By defining the right functions this approach could significantly accelerate many aggregate queries, including count(), min(), max(), avg() and also simple 'count() where something' or 'sum() where something' calculations. count() update(): local_stype = local_stype+1 reverse_update(): local_stype = local_stype-1 merge(): global_stype = global_stype + local_stype max() update(X): local_stype = max(local_stype, X) reverse_update(X): if X = local_stype then invalidate else nothing merge()global_stype = max(global_stype, local_stype) This would be fairly cheap on updates, no worse than a fairly cheap trigger, and either very cheap to read or at worst no more expensive than calculating the aggregate fully. It's a fairly nasty kludge, and probably a bad idea, but do you think it's feasible (ignoring the whole nested transaction issue, for now)? Cheers, Steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Improving postgresql.conf
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: | Gaetano Mendola wrote: | |Bruce Momjian wrote: | | I understand your points below. However, the group has weighed in the | direction of clearly showing non-default values and not duplicating | documentation. We can change that, but you will need more folks | agreeing with your direction. | |I don't remember the behaviour but tell me what happen if |I comment out a value changing the value. Kill UP the postmater. |Recommenting that value and now re killing the postmaster. | |I believe that postmaster will not run with the default value. |Who will look the configuration file will not understand the right |reality. | | | If you comment a variable in postgresql.conf, it will use the | default value. That's not true at least with the version 7.4.2. Try yourself, I did the experiment changing the cpu_tuple_cost and commenting out the cpu_tuple_cost, after sending the SIGHUP to postmaster the value remain: 0.005 that is not the default value at all. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAyWbI7UpzwH2SGd4RAre5AJ4sakTxqvcjbq8Cz6Qoj2bnDO5/7gCfTWdp nyWvDNTeQNEfwYJWHHL+0W0= =JFgw -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Accelerating aggregates
Steve Atkins wrote: Stop me if you've heard this before. I'm looking at fast calculation of aggregates (sum(), max(), count()) across large tables, or across fairly simply defined subsets of those tables. Lets say that, for a given aggregate function on a given table (with a given where clause, perhaps), each postgres process maintains a state variable (stype, in aggregate terms) and there's a also a single state variable available to all backends via shared memory. Each time a transaction starts the process initialises its local state variable to the initcond of the aggregate. Each time a row is inserted into the table the local state variable is updated, using the aggregate update function. Each time a row is removed then the local state variable is either updated, or invalidated, using a reverse-update function, again specific to the aggregate. If the transaction is rolled back, the local state variable is thrown away. If the transaction is commited and the local state variable has been invalidated then the global state variable is invalidated, otherwise the global state variable is updated using a a state merge function, specific to the aggregate. Isn't this going to have visibility issues wrt other backends? How do I know what transactions have updated the global and what haven't and which I should currently be seeing? I'm not sure that there is a solution simpler than the insert +1/-1 into summary table that gets discussed. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Tom Lane wrote: Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. Admins often don't have a choice, but a company strategy to use win only. Deciding on the platform before examining the app's requirements is always a bad idea, but that's what happens. Respecting this, suggesting don't use win32 for high performance pgsql databasing is equivalent to don't use pgsql. Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Tablespaces
With the rule system and two underlying tables one could make it work by hand I think. The rule system could be used to do this, but there was some discussion of using inherited tables to handle it. However neither handles the really hard part of detecting queries that use only a part of the table and taking that into account in generating the plan. I think the consensus should be to add smarts to the planner to include static constraint information to reduce table access. e.g if you have a constraint acol integer, check acol 5 and you have a query with a where acol = 10 you could reduce that to where false. This would help in all sorts of situations not only partitioned/inherited tables. I am not sure what the runtime cost of such an inclusion would be, so maybe it needs smarts to only try in certain cases ? Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Postgresql JDBC-Driver
This is not a viable solution, as oid's are not guaranteed to be unique, nor are they primary keys; finally tables can be created without oid's, in fact AFAIK, this will be the default in 7.5. Dave On Fri, 2004-03-05 at 08:25, Rudolpho Gian-Franco Gugliotta wrote: Hi, i'm using the jdbc postgresql driver. I need to fetch the oid of a just insertet row (getGeneratedKeys() feature). That' why i ask you to provide me the source code to implement this feature.It would be glad if you tell me how and where to get these sources. Thank you very much, Rudolpho Gugliotta ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster !DSPAM:40c8fe6b9391076077350! -- Dave Cramer 519 939 0336 ICQ # 14675561 ---(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] Another unpleasant surprise using inheritance
I think I found bug related to table inheritance (or at least very weird behavior). Here is simplified example: DROP SCHEMA master CASCADE; DROP SCHEMA skladisno CASCADE; CREATE SCHEMA master; CREATE SCHEMA skladisno; CREATE TABLE master.analiticki_subjekti ( id serial NOT NULL PRIMARY KEY, naziv varchar(60) NOT NULL UNIQUE ); CREATE TABLE master.partneri( djelatnost text, napomene text, ziro_racun varchar(64) ) INHERITS (master.analiticki_subjekti); INSERT INTO master.partneri (id,naziv) VALUES (0,'Fooo'); CREATE TABLE skladisno.skladista ( id int8 NOT NULL UNIQUE, naziv text NOT NULL, id_subjekta int NOT NULL DEFAULT 0, FOREIGN KEY (id_subjekta) REFERENCES master.analiticki_subjekti(id) ON DELETE RESTRICT ON UPDATE RESTRICT ); INSERT INTO skladisno.skladista(id,naziv,id_subjekta) VALUES (1,'Skladite 1',0); Gives error: insert or update on table skladista violates foreign key constraint $1 DETAIL: Key (id_subjekta)=(0) is not present in table analiticki_subjekti. This is not true, because there is record in master.analiticki_subjekti with id set to 0 (this record is inserted into master.partneri), but is clearly visible when execute SELECT * FROM master.nalaiticki_subjekti. Now, if I only change script from: INSERT INTO master.partneri (id,naziv) VALUES (0,'Fooo'); to: INSERT INTO master.analiticki_subjekti (id,naziv) VALUES (0,'Fooo'); insert passes without error. Regards ! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default
3. Or even create a pg_get_sequence() function:
SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
Actually, this is the best solution :)
OK, attached is a pg_get_serial_sequence(schema, table, column) function
. I have tested it with crazy names and it seems to be good. It works
like this:
SELECT setval(pg_get_serial_sequence('public', 'mytable', 'mycol'), 1,
false);
If someone approves it, i'll work on making it a built-in backend
function, and make pg_dump use it.
This will also be great for our app, since we would no longer have to
have hard-coded sequence names in our code. (For getting last sequence
val on oid-less tables)
Chris
CREATE FUNCTION pg_get_serial_sequence(name, name, name) RETURNS text
AS '
SELECT
pg_catalog.quote_ident(pn_seq.nspname) || ''.'' ||
pg_catalog.quote_ident(seq.relname)
FROM
pg_catalog.pg_namespace pn,
pg_catalog.pg_class pc,
pg_catalog.pg_attribute pa,
pg_catalog.pg_depend pd,
pg_catalog.pg_class seq,
pg_catalog.pg_namespace pn_seq
WHERE
pn.nspname=$1
AND pc.relname=$2
AND pa.attname=$3
AND pn.oid=pc.relnamespace
AND pc.oid=pa.attrelid
AND pd.objid=seq.oid
AND pd.classid=seq.tableoid
AND pd.refclassid=seq.tableoid
AND pd.refobjid=pc.oid
AND pd.refobjsubid=pa.attnum
AND pd.deptype=''i''
AND seq.relkind=''S''
AND seq.relnamespace=pn_seq.oid
'
LANGUAGE sql;
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Accelerating aggregates
[snip] I've been harping on this problem myself the last couple days. A summary table with frequent vacuums is your best bet for the existing versions of PostgreSQL. It is, IMHO, suboptimal, but a workable solution depending on the expected database load. Right now I am exploring the possibility of writing a set of msession functions for PostgreSQL. The msession project is a high speed high volume session manager for PHP based websites. Using PostgreSQL's recent ability to return sets of rows from functions, it should be possible to create a set of msession functionality in PostgreSQL that allows really FAST and really temporary variables that, while syntactically different, behave much like simple tables. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Accelerating aggregates
On Fri, Jun 11, 2004 at 09:27:07AM +0100, Richard Huxton wrote: If the transaction is rolled back, the local state variable is thrown away. If the transaction is commited and the local state variable has been invalidated then the global state variable is invalidated, otherwise the global state variable is updated using a a state merge function, specific to the aggregate. Isn't this going to have visibility issues wrt other backends? How do I know what transactions have updated the global and what haven't and which I should currently be seeing? The global is only updated at transaction commit. So, if you take a local snapshot of the global at the beginning of your transaction then the visible changes at any point are those from transactions that commited before your transaction started. That's well-defined, at least, and appears to be pretty much the same as the standard read commited isolation level. I'm not sure that there is a solution simpler than the insert +1/-1 into summary table that gets discussed. That's fairly slow and painful. Cheers, Steve ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] msession for PostgreSQL?
As you may or may not be aware, I've been sort of ranting about high speed
frequently updated tables the last few days. Sorry if I've annoyed anyone.
It occured to me last night that PostgreSQL's recent capability of
returning sets of rows from functions was a feature that a long abandoned
project needed to really work.
Msession is a high speed session manager designed for PHP. It is not MVCC,
is is strictly RAM based. It allows for plugins and other sort of cool
features. Last time I tested it, it easily handled 4000 full
read/process/update sessions a second across hundreds of connections. I
haven't done any real development on it in well over a year, but it still
has a number of users.
Conceptially, it is kind of similar to LDAP, but designed to provide some
database-esque features. To emulate its functionality, you would do
something like this in PostgreSQL:
create table sessions(
session_namevarchar,
session_datavarchar,
last_access timestamp,
created timestamp
);
create table session_variables
(
session_namevarchar,
variable_name varchar,
variable_value varchar
);
Basically, sessions are world-unique. The variable session_data is
used by PHP for storing PHP's internal session information. The table
session_variables is typically used by non-PHP applications. Anyway, if
you are curious, about it, checkout the docs on the PHP website, or
checkout http://www.mohawksoft.com/devel/msession.html
The server is still around, and aside from some cleanup and bug fixes, it
could operate with a set of user loadable functions to provide some neat
features:
Looking at the above table declarations, one can do this:
SELECT * FROM session_variables WHERE session_name = 'foobar' ;
Would looks something like this:
SELECT msession_get_array('session');
SELECT session_name FROM sessions;
Looks like:
SELECT msession_list();
UPDATE session_variables SET session_variable='foo' where
session_name='bar' and variable_name='name';
Looks like:
msession_set('bar', 'name', 'foo');
The best part of it could be that it could replace the whole msession C
API with PostgreSQL. You can join against the various data, and it should
be very fast with no MVCC overhead for those aspects of your project that
don't need it while still allowing them to be incorporated with the data
that does.
Would anyone find this useful?
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Tablespaces
Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: e.g if you have a constraint acol integer, check acol 5 and you have a query with a where acol = 10 you could reduce that to where false. I think part of the question is how much work do you put into checking this. Checking constant known values like above is probably not too expensive. Checking for ranges like where acol between 5 and 10 is probably doable. And that might be enough for partitioned tables. I think that's about all Oracle bothers to check, for example. More complex where clauses and check expressions might be hard to prove are true or false. But then the work's still not done, you still have to add an optimization that prunes members of a UNION ALL (or equivalent if it's done using inherited tables or some other infrastructure) if they are known to provably produce zero rows. And then there are more subtle cases. Like if the query is where acol = ?. Then you know it only has to read one partition, but you don't know which one at compile time. And it's important to handle that case because that might be the only clause. So knowing that you only need one partition might be the difference between a sequential scan of one partition, or an index scan of many thousands of records because they're only a small percentage of the entire table. -- greg ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Tablespaces
I don't think we want features for their own sake, though, and I'm not convinced that raw filesystems are actually useful. Course, it's not my itch, and PostgreSQL _is_ free software. I agree that raw file systems are seldom useful with one caveat, more advanced file systems are sometimes detrimental to database access. Conceptually, a file system and a database are redundant, both are doing their best to preserve data integrity. This is especially true with journalling file systems. Not to mention technologies like reiserfs which attempts to do sub-block allocation. What I think would go a long way to improving database performance on non-raw partitions would be a simplified file system -- SFS anyone? The simplified file system would not track access time. It would not overly try to manage disk space. The target applications are going to allocate disk space on a block level, rather than quibble about 4K here or 8K here, have a user defined standard allocation unit of 64K, 128K, or so on. Reduction on allocation overhead also reduces meta-data updating I/O. I can almost imagine 32BIT FAT with large clusers, only with real inodes. The idea would be that a database, like PostgreSQL, would be managing the data not the file system. The file systems job would only to be the most minimalist interface to the OS. The benefts would be awesome, near-raw partition access and standard OS tools for maintainence. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. This is a prejudice that we should try to avoid. Yes, Windows is lacking on so many levels, but that really isn't the point. A good box running Win2K or XP Server, with no internet connectivity, and no user applications, can really perform and be reliable. Would I choose this? Hell no, but there are HUGE amount of people who either don't know any better or have no real choice. The REAL bonus here is getting PostgreSQL in their hands. Right now, for the small to medium business running Windows, Microsoft has a virtual lock with SQL Server. SQL Server is expensive and a real PAIN. Giving Windows users PostgreSQL with a good set of .NET, ODBC, and JDBC drivers loosens the Microsoft stranglehold, just a little bit. If they develop their application with MSSQL, there is a good chance it will never use any open source software and always run on Windows. If they develop their application using PostgreSQL, there is a better likelyhood that other open source projects will be used, AND that should the requirement be to upgrade the system, a wider range of OS and hardware options will present themselves. ---(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] Accelerating aggregates
Steve Atkins [EMAIL PROTECTED] writes: So, if you take a local snapshot of the global at the beginning of your transaction then the visible changes at any point are those from transactions that commited before your transaction started. That's well-defined, at least, and appears to be pretty much the same as the standard read commited isolation level. no, read committed would see any other updates that have been committed since the start of your transaction. For some linear aggregates you could start with the initcond, apply all the local updates and whenever you have to read the actual value then use the global variable at that time. But not all aggregates can be handled that way. I think all the standard ones could be though, sum(), count(), stddev(), etc. -- greg ---(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] Accelerating aggregates
On Fri, Jun 11, 2004 at 12:17:57PM -0400, Greg Stark wrote: Steve Atkins [EMAIL PROTECTED] writes: So, if you take a local snapshot of the global at the beginning of your transaction then the visible changes at any point are those from transactions that commited before your transaction started. That's well-defined, at least, and appears to be pretty much the same as the standard read commited isolation level. no, read committed would see any other updates that have been committed since the start of your transaction. Uhm... only updates within the current transaction. So if you merge the global state and the local state that's exactly what you'll see. For some linear aggregates you could start with the initcond, apply all the local updates and whenever you have to read the actual value then use the global variable at that time. But not all aggregates can be handled that way. I think all the standard ones could be though, sum(), count(), stddev(), etc. I think all the standard ones can (anything with an associative update function, if I remember my math correctly). And my thought was not that this would be a neato transparent optimization that the parser would use directly in all cases, rather that it would be a hack explicitly setup by the DBA for those specific cases where they need it. Cheers, Steve ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Improving postgresql.conf
Gaetano Mendola wrote: [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: | Gaetano Mendola wrote: | |Bruce Momjian wrote: | | I understand your points below. However, the group has weighed in the | direction of clearly showing non-default values and not duplicating | documentation. We can change that, but you will need more folks | agreeing with your direction. | |I don't remember the behaviour but tell me what happen if |I comment out a value changing the value. Kill UP the postmater. |Recommenting that value and now re killing the postmaster. | |I believe that postmaster will not run with the default value. |Who will look the configuration file will not understand the right |reality. | | | If you comment a variable in postgresql.conf, it will use the | default value. That's not true at least with the version 7.4.2. Try yourself, I did the experiment changing the cpu_tuple_cost and commenting out the cpu_tuple_cost, after sending the SIGHUP to postmaster the value remain: 0.005 that is not the default value at all. Oh, sorry, you are right. Not sure if this is a bug or not. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Improving postgresql.conf
On Fri, 2004-06-11 at 11:02, Bruce Momjian wrote: Gaetano Mendola wrote: [ PGP not available, raw data follows ] -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bruce Momjian wrote: | Gaetano Mendola wrote: | |Bruce Momjian wrote: | | I understand your points below. However, the group has weighed in the | direction of clearly showing non-default values and not duplicating | documentation. We can change that, but you will need more folks | agreeing with your direction. | |I don't remember the behaviour but tell me what happen if |I comment out a value changing the value. Kill UP the postmater. |Recommenting that value and now re killing the postmaster. | |I believe that postmaster will not run with the default value. |Who will look the configuration file will not understand the right |reality. | | | If you comment a variable in postgresql.conf, it will use the | default value. That's not true at least with the version 7.4.2. Try yourself, I did the experiment changing the cpu_tuple_cost and commenting out the cpu_tuple_cost, after sending the SIGHUP to postmaster the value remain: 0.005 that is not the default value at all. Oh, sorry, you are right. Not sure if this is a bug or not. This point has come up before, and I think it's intended behavior. Stopping and restarting the database will, of course, make it load the defaults. ---(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: [pgsql-hackers-win32] [HACKERS] Tablespaces
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 9:39 AM To: Tom Lane Cc: Dann Corbit; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. I know better than to tell people to change their operating system. Linux is a great OS, and people familiar with it will do exceedingly well. But there are 40 million computers sold in a year, most of which have some flavor of Windows installed. People know how to use and administer them, and they have all their applications in Windows. They are not going to change for ideological reasons. Also, it isn't just DBAs that need to implement database systems. Suppose, for instance, that I want to write an accounting package. I can use PostgreSQL as a base and save my customers thousands of dollars. If I tell them, Now, you need to reformat your machine and install Linux that would not be very popular. But they don't even need to know about the database. And they should not have to care about the OS. A database and an operating system are both things to help get work done. Believe it or not, lots of large companies depend on Windows OS. Personally, I am technology neutral. My position is use whatever you like. This is a prejudice that we should try to avoid. Yes, Windows is lacking on so many levels, but that really isn't the point. Every OS has advantages and disadvantages. The applications for Windows are many and mature. The tool sets available for Linux are extensive and usually free. If you want real 24x7x365.25 then MVS cannot be beat. The file versioning and protections of OpenVMS are something that all operating systems should have modeled. A good box running Win2K or XP Server, with no internet connectivity, and no user applications, can really perform and be reliable. Would I choose this? Hell no, but there are HUGE amount of people who either don't know any better or have no real choice. And there are knowledgeable people who understand Windows, Linux and many other operating systems who choose Windows because it is the best choice for their company. The REAL bonus here is getting PostgreSQL in their hands. Right now, for the small to medium business running Windows, Microsoft has a virtual lock with SQL Server. SQL Server is expensive and a real PAIN. It is expensive and a multi-user system ramps the cost. But it is easier to administer than PostgreSQL. Hopefully, autovacuum will remove most of this discrepancy. Giving Windows users PostgreSQL with a good set of .NET, ODBC, and JDBC drivers loosens the Microsoft stranglehold, just a little bit. If they develop their application with MSSQL, there is a good chance it will never use any open source software and always run on Windows. If they develop their application using PostgreSQL, there is a better likelyhood that other open source projects will be used, AND that should the requirement be to upgrade the system, a wider range of OS and hardware options will present themselves. Microsoft dominates because they offer real value (the world is not completely full of idiot CEOs -- they make decisions based on profit). The open source community is closing the gap, but it has a long way to go. I don't see Microsoft as the dark side of the force or anything. Actually, the approach of PostgreSQL and ACE is (too me) the most superior. The GPL approach is far too confining, and getting a black box that will be a terrible mystery if it breaks are not nearly so pleasant. Instead of telling people how to do their jobs, I suggest the approach of providing the best possible tools and letting them decide how to use them. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Another unpleasant surprise using inheritance
On Fri, 11 Jun 2004 14:11:00 +0200, Darko Prenosil [EMAIL PROTECTED] wrote: I think I found bug related to table inheritance (or at least very weird behavior). This is well known and there's a todo for it: # Allow inherited tables to inherit index, UNIQUE constraint, and primary key, foreign key [inheritance] See also http://momjian.postgresql.org/cgi-bin/pgtodo?inheritance. Servus Manfred ---(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] Accelerating aggregates
Steve Atkins [EMAIL PROTECTED] writes: Uhm... only updates within the current transaction. So if you merge the global state and the local state that's exactly what you'll see. The only way this would work is if at every SetQuerySnapshot() you copy *all* of the global variables as part of the snapshot. You'd have to copy them all since you don't know which ones you'll need for the next query. To avoid race conditions, you'd need to lock out transaction commits while you are doing this copying. I think there are also race conditions involved in transaction commit, since there's no way to make the update of the global state be atomic with the actual transaction commit ... unless perhaps you want to hold a lock on the global state area while committing. All in all, I think the overhead of this scheme would be enormous. It implies significant costs during every transaction start and commit, whether or not that transaction is getting any benefit. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] msession for PostgreSQL?
On Fri, Jun 11, 2004 at 11:51:04AM -0400, [EMAIL PROTECTED] wrote: The best part of it could be that it could replace the whole msession C API with PostgreSQL. You can join against the various data, and it should be very fast with no MVCC overhead for those aspects of your project that don't need it while still allowing them to be incorporated with the data that does. I don't get it. If msession is already successful as it is, why do you want to change it? It seems to me you are looking for problems to solve. If you want to code, there are lots of ideas in PostgreSQL's TODO. -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] Compiling libpq with VisualC
[ Thread moved to hackers and win32.]
Andreas Pflug wrote:
Bruce Momjian wrote:
Agreed. My pthread book says pthread_mutex_init() should be called only
once, and we have to guarantee that. If the Windows implentation allows
it to be called multiple times, just create a function to be called only
by Win32 that does that and leave the Unix safe.
Ok, so here's the win32 workaround with the unix stuff left untouched.
There's no memory interlocking api in win32 that wouldn't need some
initializing api call itself, so we'd have to go for assembly level
test-and-set code or introduce a mandatory global libpq initializing
api. Considering the probably quite low usage of kerberos/ssl together
with threads under win32, and the very low probability of two
threads/processors (!) trying to initiate a connection at the same time,
it doesn't seem to be worth the compiler hassle with assembly inline.
What is the recommended way to create mutex objects (CreateMutex) from
Win32 libraries? There must be a clean way like there is in pthreads.
---
In the patch Win32, pthread_mutex_init() == CreateMutex():
+#ifndef WIN32
static pthread_mutex_t singlethread_lock = PTHREAD_MUTEX_INITIALIZER;
+#else
+ static pthread_mutex_t singlethread_lock;
+static int mutex_initialized = 0;
+if (!mutex_initialized)
+{
+mutex_initialized = 1;
+pthread_mutex_init(singlethread_lock, NULL);
+}
+#endif
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Accelerating aggregates
On Fri, Jun 11, 2004 at 01:49:18PM -0400, Tom Lane wrote: Steve Atkins [EMAIL PROTECTED] writes: Uhm... only updates within the current transaction. So if you merge the global state and the local state that's exactly what you'll see. The only way this would work is if at every SetQuerySnapshot() you copy *all* of the global variables as part of the snapshot. You'd have to copy them all since you don't know which ones you'll need for the next query. To avoid race conditions, you'd need to lock out transaction commits while you are doing this copying. Yup, though that's going to be acquire lock, memcpy, release lock and there's unlikely to be more than a few hundred bytes of state. I think there are also race conditions involved in transaction commit, since there's no way to make the update of the global state be atomic with the actual transaction commit ... unless perhaps you want to hold a lock on the global state area while committing. Yeah, that's the implementation detail that's going to really kill the idea in most cases. All in all, I think the overhead of this scheme would be enormous. It implies significant costs during every transaction start and commit, whether or not that transaction is getting any benefit. I think you're right, but it was interesting to consider briefly. Cheers, Steve ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Bruce Momjian wrote: I was thinking of close/reopen so log files could be rotated. Log file rotation is fine, if we find a consensus quite soon how to implement it... Seems as if I might find some time to implement it until feature freeze. The attached patch has the default filename issue fixed, and documentation. Since I don't have a doc build system functional, there might be tag mismatches or other typos; please check. IMHO this should be committed without waiting for log rotation stuff. Regards, Andreas Index: doc/src/sgml/func.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/func.sgml,v retrieving revision 1.206 diff -u -r1.206 func.sgml --- doc/src/sgml/func.sgml 2 Jun 2004 21:34:49 - 1.206 +++ doc/src/sgml/func.sgml 11 Jun 2004 17:58:35 - @@ -7308,6 +7308,53 @@ columns do not have OIDs of their own. /para + indexterm zone=functions-misc + primarypg_logfile/primary + /indexterm + indexterm zone=functions-misc + primarypg_logfile_length/primary + /indexterm + para +The functions shown in xref linkend=functions-misc-logfile + deal with the server log file if configured with log_destination + quotefile/quote. +previously stored with the commandCOMMENT/command command. A +null value is returned if no comment could be found matching the +specified parameters. + /para + + table id=functions-misc-logfile +titleServer Logfile Functions/title +tgroup cols=3 + thead + rowentryName/entry entryReturn Type/entry entryDescription/entry/row + /thead + + tbody + row + entryliteralfunctionpg_logfile/function(parametersize_int4/parameter, parameteroffset_int4/parameter)/literal/entry + entrytypecstring/type/entry + entryget a part of the server log file/entry + /row + row + entryliteralfunctionpg_logfile_length/function()/literal/entry + entrytypeint4/type/entry + entryreturn the current length of the server log file/entry + /row + /tbody +/tgroup +/table +para +The functionpg_logfile/function function will return the + contents of the server log file, limited by the size + parameter. If size is NULL, a server internal limit (currently + 5) is applied. The position parameter determines the + starting position of the server log chunk to be returned. A + positive number or 0 will be counted from the start of the file, + a negative number from the end; if NULL, -size is assumed + (i.e. the tail of the log file). +/para + /sect1 sect1 id=functions-array Index: doc/src/sgml/runtime.sgml === RCS file: /projects/cvsroot/pgsql-server/doc/src/sgml/runtime.sgml,v retrieving revision 1.266 diff -u -r1.266 runtime.sgml --- doc/src/sgml/runtime.sgml 10 Jun 2004 22:26:17 - 1.266 +++ doc/src/sgml/runtime.sgml 11 Jun 2004 17:58:46 - @@ -1721,14 +1721,25 @@ listitem para productnamePostgreSQL/productname supports several methods -for loggning, including systemitemstderr/systemitem and -systemitemsyslog/systemitem. On Windows, -systemitemeventlog/systemitem is also supported. Set this +for logging, including systemitemstderr/systemitem, +systemitemfile/systemitem and systemitemsyslog/systemitem. + On Windows, systemitemeventlog/systemitem is also supported. Set this option to a list of desired log destinations separated by a comma. The default is to log to systemitemstderr/systemitem only. This option must be set at server start. /para /listitem + /varlistentry + + varlistentry id=guc-syslog-facility xreflabel=log_filename + termvarnamelog_filename/varname (typestring/type)/term + listitem +para + This option sets the target filename for the log destination + quotefile/quote option. It may be specified as absolute + path or relative to the applicationcluster directory/application. +/para + /listitem /varlistentry varlistentry id=guc-syslog-facility xreflabel=syslog_facility Index: src/backend/postmaster/postmaster.c === RCS file: /projects/cvsroot/pgsql-server/src/backend/postmaster/postmaster.c,v retrieving revision 1.403 diff -u -r1.403 postmaster.c --- src/backend/postmaster/postmaster.c 11 Jun 2004 03:54:43 - 1.403 +++ src/backend/postmaster/postmaster.c 11 Jun 2004 17:58:52 - @@ -532,6 +532,9 @@ /* If timezone is not set, determine what the OS uses */ pg_timezone_initialize(); +/* open alternate logfile, if any */ + LogFileOpen(); + #ifdef EXEC_BACKEND write_nondefault_variables(PGC_POSTMASTER); #endif Index:
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Andreas Pflug [EMAIL PROTECTED] writes: The attached patch has the default filename issue fixed, and documentation. Since I don't have a doc build system functional, there might be tag mismatches or other typos; please check. IMHO this should be committed without waiting for log rotation stuff. This has got portability issues (fopen(ab)) and I don't care for its use of malloc in preference to palloc either. Also, pg_logfile() will dump core if LogFileName returns null. The bigger issue though is whether this is useful at all, if you cannot solve the file rotation issue (and I don't think you can). As implemented, the secondary log file cannot be truncated without restarting the postmaster. I think that reduces it from a possibly useful feature to a useless toy. (The fact that pg_logfile_length returns int and not something wider is pretty silly in this connection.) My vote is not to apply until and unless something that can rotate the logfile is demonstrated ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-hackers-win32] [PATCHES] Compiling libpq with VisualC
Andreas Pflug wrote: Bruce Momjian wrote: What is the recommended way to create mutex objects (CreateMutex) from Win32 libraries? There must be a clean way like there is in pthreads. It's having a central one-time called routine executing CreateMutex. This can be DllMain, *if* used as DLL, but that's certainly no solution for static linkage. This would require some PQinitThreadStuff() routine, which may be called only once (and ultimately can't check for that itself, if you don't trust a static var). Ewe. Well, I am not excited about adding a thread capability for Win32 that isn't 100% guaranteed to work. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [pgsql-hackers-win32] [PATCHES] Compiling libpq with VisualC
Bruce Momjian wrote: What is the recommended way to create mutex objects (CreateMutex) from Win32 libraries? There must be a clean way like there is in pthreads. It's having a central one-time called routine executing CreateMutex. This can be DllMain, *if* used as DLL, but that's certainly no solution for static linkage. This would require some PQinitThreadStuff() routine, which may be called only once (and ultimately can't check for that itself, if you don't trust a static var). Regards, Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] Configuration patch
Where are we on this? --- Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: One interesting idea would be for SET include to work like this: SET include '/var/run/xx' Notice there is no equals here. This would allow users to create files with various settings and enable them all with one SET command. However, this does open a security issue. More than one, in fact. In the first place, as the code presently works, anything coming in from the file would be treated on an equal footing with values sourced from postgresql.conf, thereby allowing unprivileged users to set things they shouldn't. This is potentially fixable, but the other issue isn't: such a facility would allow anyone to ask the backend to read any file the Postgres user account can access. Not very successfully, perhaps, but even the error messages might give useful info about the file's contents to an attacker. This is the same reason that COPY FROM file is a privileged operation. I think it's important that include be restricted to appear only in config files, and not be in any way shape or form a SETtable thing. In summary, I think we need to treat include specially in postgresql.conf (no equals) and remove it as an actual GUC parameter and just have it do includes immediately. (This will probably require special-casing it in the guc-file grammar.) Yes. In fact, it'll be a less-than-trivial change in guc-file, at least if you want the thing to act intuitively (that is, include acts like the target file is actually included right here). This will mean splitting ProcessConfigFile into a recursive read step followed by a nonrecursive apply step. Also, I think that invoking the flex lexer recursively will take a little bit of work. I would suggest splitting the patch into two separate patches, one that handles include and one that handles the other changes. The other stuff is reasonably close to being ready to apply (modulo docs and fixing the standalone-backend case), but include I think is still a ways off. regards, tom lane -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: The attached patch has the default filename issue fixed, and documentation. Since I don't have a doc build system functional, there might be tag mismatches or other typos; please check. IMHO this should be committed without waiting for log rotation stuff. This has got portability issues (fopen(ab)) and I don't care for its use of malloc in preference to palloc either. Also, pg_logfile() will dump core if LogFileName returns null. The bigger issue though is whether this is useful at all, if you cannot solve the file rotation issue (and I don't think you can). As implemented, the secondary log file cannot be truncated without restarting the postmaster. I think that reduces it from a possibly useful feature to a useless toy. (The fact that pg_logfile_length returns int and not something wider is pretty silly in this connection.) My vote is not to apply until and unless something that can rotate the logfile is demonstrated ... Agreed. Lets see where it goes. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PATCHES] [HACKERS] serverlog function (log_destination file)
Tom Lane wrote:
This has got portability issues (fopen(ab))
My doc says b is ignored on ansi systems, and recommends using it. Do
you have other experiences?
and I don't care for its
use of malloc in preference to palloc either.
Do we already have an applicable memory context in the postmaster at
that early stage of initialization?
Also, pg_logfile() will dump core if LogFileName returns null.
How that?
char *filename=LogFileName();
if (filename)
{
...
free(filename);
}
The bigger issue though is whether this is useful at all, if you cannot
solve the file rotation issue (and I don't think you can). As
implemented, the secondary log file cannot be truncated without
restarting the postmaster. I think that reduces it from a possibly
useful feature to a useless toy.
This patch isn't trying to be better on logfile handling than the
default stderr redirection behavior, besides being able to access it
through the postmaster. Seems you insist to name this a toy, many users
don't.
(The fact that pg_logfile_length
returns int and not something wider is pretty silly in this connection.)
2GB logfile seems pretty big...
Regards,
Andreas
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] msession for PostgreSQL?
On Fri, Jun 11, 2004 at 11:51:04AM -0400, [EMAIL PROTECTED] wrote: The best part of it could be that it could replace the whole msession C API with PostgreSQL. You can join against the various data, and it should be very fast with no MVCC overhead for those aspects of your project that don't need it while still allowing them to be incorporated with the data that does. I don't get it. If msession is already successful as it is, why do you want to change it? It seems to me you are looking for problems to solve. If you want to code, there are lots of ideas in PostgreSQL's TODO. Actually, *all* problems have been more or less solved given enough tools time to piece together the solution. SQL itself didn't really *solve* a problem, it only made an easier solution. In a highly interactive web site or other complex DB project, there are different classes of data storage and services. High volatility, low volatility, temporary, more long lasting. PostgreSQL falls apart on the high volatility temporary data class of problem. In 1999-2002, I was working for a Web company that needed to spread session information across a lot of boxes. The target was thousands of hits per second, tens or hundreds of thousands of active users. Think about the scalability of that problem. Most of the transactions would be wasted. It was a huge waste to try to make sure all these actions were saved. We could lose the temporary information once in a while with no problems, but we had to keep the perminent information. So, one class of data, the instantaneous what they did last and maybe shopping cart information was important, but could be lost once in a very great while without any loss of company revenue or reputation. The next class of problem was the transaction class of problem, credid card numbers, billing, and accounting, you can't screw around with that stuff. The next class of problem were the fixed inventory tables, product IDs, links to jpegs, etc. The next service was replication of persistent data (user info, inventory, etc.) to the slaves. The next service was full text search of the product inventory. The last service was a recommendations system. As Tom and Bruce will probably remember, I've been a real PITA. I was pushing for sets of rows being able to be returned from functions since 7.0. Anyway, what we finally made was a real kludge. I eventually used my msession server, Oracle, PostgreSQL, PHP, my own full text search engine (FTSS), and my own recommendations system (CGR). The hard part of this system was that I was the only one who understood it all. Having written most of it, I was always the one to fix it or debug it. It worked, it didn't cost near a million dollars, but it was way too complicated. Where was the data? In Oracle? in PostgreSQL? In Msession? In FTSS? In CGR? How did you join information in msession to rows in Oracle? How did you join rows in Oracle to rows in PostgreSQL? And so on. The purpose of these various diatribes and rants is to address some issues with PostgreSQL that limited its application and thus forced us to use other technologies for different classes of problem. Currently being a consultant, and advising on these classes of problem, I am still continuing to create what I consider to be overly complicated systems. Ideally, I would like to have a single interface to the required data and functionality. It would be fantastic if users didn't all each have to solve these problems. It would be a huge plus for PostgreSQL as it would become *the* web DB of choice over *any* competitor. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 9:39 AM To: Tom Lane Cc: Dann Corbit; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. I know better than to tell people to change their operating system. Linux is a great OS, and people familiar with it will do exceedingly well. But there are 40 million computers sold in a year, most of which have some flavor of Windows installed. How many billions of cigarettes are sold? How many Big Macs? Popularity does not imply quality or safety. People know how to use and administer them, and they have all their applications in Windows. They are not going to change for ideological reasons. This is interesting, since when is ideology *not* the american way? Have you looked at politics lately? Also, it isn't just DBAs that need to implement database systems. Suppose, for instance, that I want to write an accounting package. I can use PostgreSQL as a base and save my customers thousands of dollars. If I tell them, Now, you need to reformat your machine and install Linux that would not be very popular. But they don't even need to know about the database. And they should not have to care about the OS. A database and an operating system are both things to help get work done. Believe it or not, lots of large companies depend on Windows OS. I've been in the trenches for a while now, and I haven't met a single CIO that is comfortable with Windows. They hate the cost, they hate the viruses, they hate the instability. The only thing they hate more is being isolated on an island. Fortunately Linux is becoming less obscure. Personally, I am technology neutral. My position is use whatever you like. I would call myself neutral to a point, but when I have to give advice, I have to tell the truth. A little Linux goes a long way. This is a prejudice that we should try to avoid. Yes, Windows is lacking on so many levels, but that really isn't the point. Every OS has advantages and disadvantages. Some more than other. The applications for Windows are many and mature. The tool sets available for Linux are extensive and usually free. If you want real 24x7x365.25 then MVS cannot be beat. The file versioning and protections of OpenVMS are something that all operating systems should have modeled. A good box running Win2K or XP Server, with no internet connectivity, and no user applications, can really perform and be reliable. Would I choose this? Hell no, but there are HUGE amount of people who either don't know any better or have no real choice. And there are knowledgeable people who understand Windows, Linux and many other operating systems who choose Windows because it is the best choice for their company. I seriously do not know anyone, including myself, that would choose Windows on technical merrits alone. I know some need to choose it for killer application requirements, but not on merrit. As for best choice for their company, I can't even say that with a straight face. The REAL bonus here is getting PostgreSQL in their hands. Right now, for the small to medium business running Windows, Microsoft has a virtual lock with SQL Server. SQL Server is expensive and a real PAIN. It is expensive and a multi-user system ramps the cost. But it is easier to administer than PostgreSQL. Hopefully, autovacuum will remove most of this discrepancy. Having dealt with both, as well as MySQL, DB2, and Oracle, I not sure I agree with that statement. As long as MSSQL is installed correctly the first time, it may be OK. Giving Windows users PostgreSQL with a good set of .NET, ODBC, and JDBC drivers loosens the Microsoft stranglehold, just a little bit. If they develop their application with MSSQL, there is a good chance it will never use any open source software and always run on Windows. If they develop their application using PostgreSQL, there is a better likelyhood that other open source projects will be used, AND that should the requirement be to upgrade the system, a wider range of OS and hardware options will present themselves. Microsoft dominates because they offer real value (the world is not completely full of idiot CEOs -- they make decisions based on profit). FACT: Microsoft dominates because they break the law. The open source community is closing the gap, but it has a long way to go. I don't see Microsoft as the dark side of
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 1:37 PM To: Dann Corbit Cc: Tom Lane; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: RE: [pgsql-hackers-win32] [HACKERS] Tablespaces -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 9:39 AM To: Tom Lane Cc: Dann Corbit; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. I know better than to tell people to change their operating system. Linux is a great OS, and people familiar with it will do exceedingly well. But there are 40 million computers sold in a year, most of which have some flavor of Windows installed. How many billions of cigarettes are sold? How many Big Macs? Popularity does not imply quality or safety. Right. It implies volume. That was the only point I was making. If everyone is wearing suede shoes, you will have trouble selling shoe polish. People know how to use and administer them, and they have all their applications in Windows. They are not going to change for ideological reasons. This is interesting, since when is ideology *not* the american way? Have you looked at politics lately? I am also politically neutral and have not voted since I was 18 as a matter of conscience. Also, it isn't just DBAs that need to implement database systems. Suppose, for instance, that I want to write an accounting package. I can use PostgreSQL as a base and save my customers thousands of dollars. If I tell them, Now, you need to reformat your machine and install Linux that would not be very popular. But they don't even need to know about the database. And they should not have to care about the OS. A database and an operating system are both things to help get work done. Believe it or not, lots of large companies depend on Windows OS. I've been in the trenches for a while now, and I haven't met a single CIO that is comfortable with Windows. They hate the cost, they hate the viruses, they hate the instability. The only thing they hate more is being isolated on an island. Fortunately Linux is becoming less obscure. Personally, I am technology neutral. My position is use whatever you like. I would call myself neutral to a point, but when I have to give advice, I have to tell the truth. A little Linux goes a long way. For me, if I was going to start a company, Linux is a technically superior solution for a server in my view. This is especially true due to license reasons. If I want a thousand users on a machine, the cost for a Windows solution dwarfs any reasons I can think of not to switch to Linux. However, if a company does not have personnel trained to administrate Linux machines and applications, then something else might be a better choice for them. (Fire all your workers and hire new ones. does not work) This is a prejudice that we should try to avoid. Yes, Windows is lacking on so many levels, but that really isn't the point. Every OS has advantages and disadvantages. Some more than other. And yet each choice can have different weights depending upon who is using it, for what reasons, and other business factors. The applications for Windows are many and mature. The tool sets available for Linux are extensive and usually free. If you want real 24x7x365.25 then MVS cannot be beat. The file versioning and protections of OpenVMS are something that all operating systems should have modeled. A good box running Win2K or XP Server, with no internet connectivity, and no user applications, can really perform and be reliable. Would I choose this? Hell no, but there are HUGE amount of people who either don't know any better or have no real choice. And there are knowledgeable people who understand Windows, Linux and many other operating systems who choose Windows because it is the best choice for their company. I seriously do not know anyone, including myself, that would choose Windows on technical merrits alone. I know some need to choose it for killer application requirements, but not on merrit. Religious arguments are hard to fight when one person is unable to listen. As for best choice for their company, I
TESTING (was: RE: [HACKERS] More vacuum.c refactoring )
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Thursday, June 10, 2004 2:19 PM To: Manfred Koizar Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] More vacuum.c refactoring Manfred Koizar [EMAIL PROTECTED] writes: This code is very similar to vacuum_page(). The major difference is that vacuum_page() uses vacpage-offsets while the code in repair_frag() looks for MOVED_OFF bits in tuple headers. AFAICS the tuples with the MOVED_OFF bit set are exactly those referenced by vacpage-offsets. This does not make me comfortable. You *think* that two different bits of code are doing the same thing, so you want to hack up vacuum.c? This module is delicate code --- we've had tons of bugs there in the past --- and no I have zero confidence that passing the regression tests proves anything, because all those prior bugs passed the regression tests. Then why didn't those bugs get added to the regression? That has been standard procedure in every place that I have ever worked. We have 7000+ tests in our CONNX regression suite that take one week to run, on an array of dozens of computers from micros to mainframes. Besides finding quickly if you have reintroduced a problem, it will also ferret out lots of newly introduced problems. It seems that MySQL has some sort of extensive test, from looking at their site. Maybe the Pgsql group could simply cannibalize it. Perhaps your regression test is really a sanity check, rather than a regression test. After all, the meaning of 'regression' itself demands that you introduce new tests based upon old failures. I seem to recall that someone was porting the NIST suite to PostgreSQL. What ever happened to that effort? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
We should provide people with the right tools, true, but we are bound by our conscience to inform them about Windows' failures. It must be nice to be young and still see everything as black and white with no shades of gray. I wouldn't call 41 very young. For those who think that Windows should be canned, Gates should be burned at the stake, and Linux should rule the world, I have no problem with their opinions. We all get to choose what we like and dislike. I think that the typical Linux fan is WAY over the top both in seeing the advantages with rose colored glasses and turing opposition molehills into mountains. But passion is good, and I like to see it. If it were not for the passion of the Linux crowd, there would be a far less interesting competitor for MS and a far less interesting toolset to use with it. Actually, I am not a wide eyed passionate Linux zealot. Like my support for John Kerry, I gladly choose the better side of mediocrity over extream evil, it is nothing more than pure practicality. A diversity of platforms in the market place creates jobs, increased security (any particular exploit does not wipe out a vast majority of targets.), and feeds innovation and competition. Microsoft has harmed the computing industry more than any single factor that I can remember. I've seen a lot of it, from DEC to Wang, and microsoft has single handedly wiped out more computing innovation in 20 years than any 10 other companies. Stac, Go Computing, Netscape, BeOS, and the list grows for as long as you think about it. It isn't wide eyes passion, I've programed computers since jr high school, on a PDP-8/e. It is what I love to do, and it is what I make my living doing. Some things are important in life. Spending a few extra dollars *NOT* going to Walmart is one small thing you can do to improve the world. Taking advantage of every LEGITIMATE opportunity to move a person or project off Windows is one small step one can do to improve our industry. Maybe the thread should go to some advocacy channel at this point. Yes. My reason for jumping in was to show that: 1. PostgreSQL will have a exponential leap in possible sites when it opens up to Win32 systems 2. There will be huge installations on Win32 systems, like it or not. Some other things to keep in mind: 1. The average Windows user is far, far less computer saavy than a Linux (or other flavor of UNIX user) and hence, there will be a big load of deer in the headlights users coming on board. Total koolaid induced delusion. Dumb users are dumb users, deer in the headlights looks come from flashing VCR clocks. Competent professionals can handle a few twists. The switch from DOS Windows (3x,9x,ME) to XP was just as traumatic. 2. On the plus side, there are millions of good developers familiar with Windows. Some of these may become involved with the PostgreSQL project and give added value. Having been a Windows developer since version 1.03, with DOS and CP/M before that, I can say with complete authority that most Windows developers are not good. The worst I've seen is Charles Petzold, and he sets the bar. ---(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: [pgsql-hackers-win32] [HACKERS] Tablespaces
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 2:41 PM To: Dann Corbit Cc: [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: RE: [pgsql-hackers-win32] [HACKERS] Tablespaces [snip] Microsoft has harmed the computing industry more than any single factor that I can remember. I've seen a lot of it, from DEC to Wang, and microsoft has single handedly wiped out more computing innovation in 20 years than any 10 other companies. Stac, Go Computing, Netscape, BeOS, and the list grows for as long as you think about it. Netscape and BeOS are still around. I think Linux is a bigger blow to BeOS than MS. Stak was unbelievable and MS got a tap on the wrist compared to the harm caused. I will have to look up Go Computing to see what all that is about. It isn't wide eyes passion, I've programed computers since jr high school, on a PDP-8/e. It is what I love to do, and it is what I make my living doing. Some things are important in life. Spending a few extra dollars *NOT* going to Walmart is one small thing you can do to improve the world. Taking advantage of every LEGITIMATE opportunity to move a person or project off Windows is one small step one can do to improve our industry. If they are moved to another platform for their benefit or for the right reasons there is nothing wrong with it. If it is because of your own ideology and not for the benefit of the client then it is harm to them and immoral. IMO-YMMV Maybe the thread should go to some advocacy channel at this point. Yes. My reason for jumping in was to show that: 1. PostgreSQL will have a exponential leap in possible sites when it opens up to Win32 systems 2. There will be huge installations on Win32 systems, like it or not. Some other things to keep in mind: 1. The average Windows user is far, far less computer saavy than a Linux (or other flavor of UNIX user) and hence, there will be a big load of deer in the headlights users coming on board. Total koolaid induced delusion. Dumb users are dumb users, deer in the headlights looks come from flashing VCR clocks. Competent professionals can handle a few twists. The switch from DOS Windows (3x,9x,ME) to XP was just as traumatic. You are totally wrong about that. 'Dumb users' are people who don't care to become computer saavy. Often because they don't need to. Someone who can't program their VCR may be able to do brain surgery on you. Like Will Rogers said, Everyone is ignorant, only in different areas. If people don't want to become computer experts, we should not try to force them to become so. You and I enjoy computers but other people just want what the computer can deliver and don't care to learn how it got there. 2. On the plus side, there are millions of good developers familiar with Windows. Some of these may become involved with the PostgreSQL project and give added value. Having been a Windows developer since version 1.03, with DOS and CP/M before that, I can say with complete authority that most Windows developers are not good. The worst I've seen is Charles Petzold, and he sets the bar. Charles Petzold is a decent programmer. I have read his books and he knows what he's talking about. He no W. Richard Stevens or Donald Knuth, but I would hire him to do a job. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: TESTING (was: RE: [HACKERS] More vacuum.c refactoring )
Dann Corbit [EMAIL PROTECTED] writes: --- and no I have zero confidence that passing the regression tests proves anything, because all those prior bugs passed the regression tests. Then why didn't those bugs get added to the regression? Because there wasn't any reasonable way to make them reproducible. The set of things we can test in the regression tests is only a small fraction of the interesting properties of Postgres. This is unfortunate but ranting about standard practice doesn't change it. I seem to recall that someone was porting the NIST suite to PostgreSQL. What ever happened to that effort? It was done and we fixed a couple of bugs based on it (the one I can think of offhand had to do with semantics of aggregate functions in sub-selects). I don't think there's anything more to be learned there. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
[EMAIL PROTECTED] wrote: We should provide people with the right tools, true, but we are bound by our conscience to inform them about Windows' failures. It must be nice to be young and still see everything as black and white with no shades of gray. I wouldn't call 41 very young. For those who think that Windows should be canned, Gates should be burned at the stake, and Linux should rule the world, I have no problem with their opinions. We all get to choose what we like and dislike. I think that the typical Linux fan is WAY over the top both in seeing the advantages with rose colored glasses and turing opposition molehills into mountains. But passion is good, and I like to see it. If it were not for the passion of the Linux crowd, there would be a far less interesting competitor for MS and a far less interesting toolset to use with it. Actually, I am not a wide eyed passionate Linux zealot. Like my support for John Kerry, I gladly choose the better side of mediocrity over extream evil, it is nothing more than pure practicality. Well, call me extreme evil too. Then I guess PostgreSQL is partly pure evil, or partly extreme evil, or something like that. Of course, if you meet me, I don't appear so. We are taught to hide our evil so effectively. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: TESTING (was: RE: [HACKERS] More vacuum.c refactoring )
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 2:35 PM To: Dann Corbit Cc: Manfred Koizar; [EMAIL PROTECTED] Subject: Re: TESTING (was: RE: [HACKERS] More vacuum.c refactoring ) Dann Corbit [EMAIL PROTECTED] writes: --- and no I have zero confidence that passing the regression tests proves anything, because all those prior bugs passed the regression tests. Then why didn't those bugs get added to the regression? Because there wasn't any reasonable way to make them reproducible. The set of things we can test in the regression tests is only a small fraction of the interesting properties of Postgres. This is unfortunate but ranting about standard practice doesn't change it. I seem to recall that someone was porting the NIST suite to PostgreSQL. What ever happened to that effort? It was done and we fixed a couple of bugs based on it (the one I can think of offhand had to do with semantics of aggregate functions in sub-selects). I don't think there's anything more to be learned there. It is reassuring to know that it passed with flying colors. Can I get the ported version? I would love to play with it. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [COMMITTERS] pgsql-server: Clean up generation of default
On June 11, 2004 05:51 am, Christopher Kings-Lynne wrote:
3. Or even create a pg_get_sequence() function:
SELECT SETVAL(pg_get_sequence(schema.table, col), 17);
Actually, this is the best solution :)
OK, attached is a pg_get_serial_sequence(schema, table, column) function
. I have tested it with crazy names and it seems to be good. It works
like this:
SELECT setval(pg_get_serial_sequence('public', 'mytable', 'mycol'), 1,
false);
I'd be inclined to make it only take 2 args, table, col where table can be
namespace qualified. This allows people who arn't namespace aware to just do
SELECT pg_get_serial_sequence('mytable','mycol') and have it return the
correct item following searchpath.. I would think this would then become
consistant with the standard behavior. Not to mention it would also allow
for easier moving schema form one namespace to another..
If someone approves it, i'll work on making it a built-in backend
function, and make pg_dump use it.
This will also be great for our app, since we would no longer have to
have hard-coded sequence names in our code. (For getting last sequence
val on oid-less tables)
Chris
--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
On Fri, 2004-06-11 at 11:29, Dann Corbit wrote: -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, June 11, 2004 9:39 AM To: Tom Lane Cc: Dann Corbit; Zeugswetter Andreas SB SD; [EMAIL PROTECTED]; [EMAIL PROTECTED]; Bruce Momjian; Greg Stark; [EMAIL PROTECTED]; PostgreSQL Win32 port list Subject: Re: [pgsql-hackers-win32] [HACKERS] Tablespaces Dann Corbit [EMAIL PROTECTED] writes: I expect that one year after release, there will be ten times as many PostgreSQL systems on Win32 as all combined versions now on UNIX flavors I surely hope not. Especially not multi-gig databases. The folks running those should know better than to use Windows, and if they do not, I'll be happy to tell them so. I know better than to tell people to change their operating system. Linux is a great OS, and people familiar with it will do exceedingly well. But there are 40 million computers sold in a year, most of which have some flavor of Windows installed. I think the more important part of Tom's point isn't that Windows in general sucks (even though it does) but that PostgreSQL ON Windows is a brand new thing, and if you're willing to put a multi-gig ERP system on it and bet the company, you shouldn't be in a data center, because right now it simply hasn't been tested enough. Now, setting up a unix box with postgresql for production and becoming a part of the windows testing effort in your spare time, until Windows proves itself ready and worthy, that makes sense. I'm no fan of microsoft or Bill Gates, for the reasons mentioned in books like The Microsoft Files. But my main objection to putting a PostgreSQL on Windows server online right now would be the same one I would have against putting a MS SQL server on Windows online right now, neither one has ever been proven reliable. :-) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
[EMAIL PROTECTED] wrote: Actually, I am not a wide eyed passionate Linux zealot. Like my support for John Kerry, I gladly choose the better side of mediocrity over extream evil, it is nothing more than pure practicality. I don't like dubya either, but he isn't extreme evil. This sort of argument is over the top, and the analogy is out of place. You ought to know by now that there is almost no correlation between technological views and political views (e.g. many FOSS advocates have politics that are anaethema to me). So let's leave the politics out of it. In fact, let's get on with doing actual work. cheers andrew ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: TESTING (was: RE: [HACKERS] More vacuum.c refactoring )
Dann Corbit [EMAIL PROTECTED] writes: It was done and we fixed a couple of bugs based on it (the one I can think of offhand had to do with semantics of aggregate functions in sub-selects). I don't think there's anything more to be learned there. It is reassuring to know that it passed with flying colors. Passed with flying colors might be overstating it --- but the other things it found were stuff we already knew about, eg, unimplemented features, identifier case folding, that kind of thing. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
Having been a Windows developer since version 1.03, with DOS and CP/M before that, I can say with complete authority that most Windows developers are not good. The worst I've seen is Charles Petzold, and he sets the bar. Charles Petzold is a decent programmer. I have read his books and he knows what he's talking about. He no W. Richard Stevens or Donald Knuth, but I would hire him to do a job. Funny story. In Windows 2.x days, a bug was found in Petzolds calculator example having to do with the stupid way Win16 dealt with various aspects of Window properties such as hMenu. When I read the book, I had been programming in Windows 1.x and early 2.x, and thought to myself, that's not right. Well, it turns out that it was a bug that broke a lot of Windows program when Win 3.0 came out in standard mode. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] I just got it: PostgreSQL Application Server -- a new project.
I have been harping for the last few days (years, actually) about tweaks and changes to PostgreSQL for a number of reasons ranging from session management to static tables. I even had a notion to come up with msession on PostgreSQL. I have been incorporating full text search, recommendations, and a slew of other features into PostgreSQL, but you know what? While it does touch Postgre in a real sense, it is not strictly SQL. It is about how to create applications with PostgreSQL. That's what we're missing, Coneptually, PostgreSQL is strictly a database and the core team (rightly so) is fundimentally happy with that aspect of it. Maybe we need a pgfoundary project called PostgreSQL Application Server. Like Apache Tomcat or regular apache or PHP, PostgreSQL could form the SQL base of a far more intricate and flexable framework that encompases a lot of the various features that could provide application sever features from PostgreSQL. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Release 7.4.3 branded
I have completed branding 7.4.3, and updated the release notes: http://candle.pha.pa.us/main/writings/pgsql/sgml/release.html#RELEASE-7-4-3 Release is scheduled for Monday. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
