Re: [HACKERS] Some new list.c primitives
On 7/28/05, Neil Conway [EMAIL PROTECTED] wrote: Tom Lane wrote: How about list_append_distinct and list_concat_distinct?Those names are fine with me. list_append_unique and list_concat_unique might be a little clearer, unless you want to retain the sqlism of distinct.
Re: [HACKERS] Feature freeze date for 8.1
On 5/3/05, Dave Held [EMAIL PROTECTED] wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 03, 2005 12:39 PM To: Heikki Linnakangas Cc: Hannu Krosing; Neil Conway; Oliver Jowett; [EMAIL PROTECTED]; Peter Eisentraut; Alvaro Herrera; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Feature freeze date for 8.1 [...] BTW, the upthread proposal of just dropping the message (which is what O_NONBLOCK would do) doesn't work; it will lose encryption sync on SSL connections. How about an optional second connection to send keepalive pings? It could be unencrypted and non-blocking. If authentication is needed on the ping port (which it doesn't seem like it would need to be), it could be very simple, like this: * client connects to main port * server authenticates client normally * server sends nonce token for keepalive authentication * client connects to keepalive port * client sends nonce token on keepalive port * server associates matching keepalive connection with main connection * if server does not receive matching token within a small timeout, no keepalive support enabled for this session This will not work through firewalls. Is it not possible for the server to test the current network connection with the client? ---(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] CSS
On 10/15/2004 1:02 PM Nurlan M. Mukhanov could be overheard saying:: Hello! I wrote this css file 2 years ago. It's very useful when using docs. Can you add it in mainstream? You might want to consider revising it to include fallbacks to standards (or common aliases) such as Helvetica, Times, Courier, and generics such as monospace, sans serif, serif. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hypothetical Indexes
On 10/12/2004 4:02 PM Tom Lane could be overheard saying:: Marcos A Vaz Salles [EMAIL PROTECTED] writes: In order to make index selection possible, we have extended the PostgreSQL DBMS to allow the simulation of hypothetical indexes. We believe these server extensions may be of value for addition to the PostgreSQL code base. This would be of some value if the optimizer's cost estimates were highly reliable, but unfortunately they are far from being so :-( Without the ability to measure *actual* as opposed to estimated costs, I'm not sure you can really do much. Is it possible for the backend to store performance data and try to modify its cost estimates? I was thinking of the statistics analyzer currently in use and whether (query) performance data could piggy back on it or if it would need a different process, something that could try and correlate cost estimates with actual costs. Given sample data the calculation could result in an actual execution time estimation. Would it be worth pursuing, or would it be too invasive? I know the autotune project has similar goals from a different angle, system/memory settings. Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Maximum identifier lengths
The 7.4 release increased the maximum length for column and table identifiers to 64 characters. Are there any plans to increase the maximum length out to the SQL spec of 128 characters? I remembered some performance penalties for the longer name lengths for hash joins but I don't know if any of the many improvements for the 8.0 release have change or reduced the penalties for such identifiers. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Nested Transactions, Abort All
Andreas Pflug wrote: Simon Riggs wrote: ISTM - my summary would be 1. We seem to agree we should support SAVEPOINTs 2. We seem to agree that BEGIN/COMMIT should stay unchanged... With savepoints, it looks pretty strange: BEGIN; SAVEPOINT x1; INSERT INTO ...; SAVEPOINT x2; INSERT INTO ...; SAVEPOINT x3; INSERT INTO ...; This isn't how you would use SAVEPOINTs...look at this... BEGIN display one screen to user - book the flight INSERT INTO ... INSERT INTO ... UPDATE ... SAVEPOINT display another related screen - book the hotel INSERT INTO DELETE UPDATE UPDATE SAVEPOINT offer confirmation screen COMMIT (or ROLLBACK) No, SAVEPOINT is not some kind of intermediate commit, but a point where a rollback can rollback to. Look at this oracle stuff when googling for SAVEPOINT ROLLBACK: BEGIN SAVEPOINT before_insert_programmers; insert_programmers (p_deptno); EXCEPTION WHEN OTHERS THEN ROLLBACK TO before_insert_programmers; END; There's no need for an intermediate commit, because the top level rollback would overrule it (if not, it would be an independent transaction, not nested). I'd opt for BEGIN as a start of a subtransaction (no need for special semantics in plpgsql), the corresponding END simply changes the transaction context to the parent level. BEGIN is an unnamed savepoint in this case, so if we have SAVEPOINT name we'd also have the corresponding ROLLBACK TO [SAVEPOINT] name. For the unnamed savepoint ROLLBACK INNER or ROLLBACK SUB could be used. This would be an extension to oracle's usage, which seems quite reasonable to me. What happens when you use subtransactions? I think there might be a visibility issue and how far do you unwind the depth of subtransactions or transactions? BEGIN UPDATE A SAVEPOINT X BEGIN BEGIN UPDATE B BEGIN UPDATE C ROLLBACK TO SAVEPOINT X COMMIT COMMIT COMMIT COMMIT Or SAVEPOINT X BEGIN UPDATE A ROLLBACK TO SAVEPOINT X COMMIT ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Nested Transactions, Abort All
Scott Marlowe wrote: On Tue, 2004-07-06 at 23:36, Greg Stark wrote: Scott Marlowe [EMAIL PROTECTED] writes: Why not rollback all or commit all? I really really don't like subbegin and subcommit. I get the feeling they'll cause more problems we haven't foreseen yet, but I can't put my finger on it. Well I've already pointed out one problem. It makes it impossible to write generic code or reuse existing code and embed it within a transaction. Code meant to be a nested transaction within a larger transaction becomes non-interchangeable with code meant to be run on its own. Would a rollback N / abort N where N is the number of levels to rollback / abort work? Only, if you know the number of levels you are deep in the transaction. ROLLBACK n and ROLLBACK ALL together would be good alternatives to unwind nested transaction. Perhaps a function for pg_transaction_nested_level( ) or a pg_transaction_nested_level variable could help in this. Again, these are just opinions. ---(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] Nested Transactions, Abort All
Tom Lane wrote: Mike Benoit [EMAIL PROTECTED] writes: On Thu, 2004-07-01 at 18:38 -0400, Alvaro Herrera wrote: If we change the syntax, say by using SUBCOMMIT/SUBABORT for subtransactions, then using a simple ABORT would abort the whole transaction tree. But then we're back to the application having to know if its in a regular transaction or a sub-transaction aren't we? To me that sounds just as bad. Someone (I forget who at this late hour) gave several cogent arguments that that's *exactly* what we want. Please see the prior discussion... Right at the moment I think we have a consensus that we should use SUBBEGIN/SUBEND or some such keywords for subtransactions. (I do not say we've agreed to exactly those keywords, only that it's a good idea to make them different from the outer-level BEGIN/END keywords.) Either approach still needs some mechanism to clear the current stack of transactions and subtransactions. That's why I was thinking ABORT ALL and ROLLBACK ALL would be sufficient to cover that and be clear enough to the user/programmer. There was also some talk of offering commands based around the notion of savepoints, but I'm not sure that we have a consensus on that yet. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Nested Transactions, Abort All
Alvaro Herrera wrote: On Fri, Jul 02, 2004 at 01:14:25PM -0400, Merlin Moncure wrote: If we change the syntax, say by using SUBCOMMIT/SUBABORT for subtransactions, then using a simple ABORT would abort the whole transaction tree. Question: with the new syntax, would issuing a BEGIN inside a already started transaction result in an error? Yes. My concern is about say, a pl/pgsql function that opened and closed a transation. This could result in different behaviors depending if called from within a transaction, which is not true of the old syntax. Then again, since a statement is always transactionally wrapped, would it be required to always issue SUBBEGIN if issued from within a function? This would address my concern. Yes, I was thinking about this because the current code behaves wrong if a BEGIN is issued and not inside a transaction block. So we'd need to do something special in SPI -- not sure exactly what, but the effect would be that the function can't issue BEGIN at all and can only issue SUBBEGIN. Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT should be sufficient regardless of the level. If you are inside a current transaction those commands start a new transaction inside of the current transaction level, just like pushing on and popping off elements on a stack. I'm not trying to be argumentative, but the notation seems orthogonal to the issue. Some functions and procedures may not be called inside of transactions or subtransactions.Having to start with a SUBBEGIN and SUBCOMMIT/SUBABORT is equally problematic if you don't know where you begin. Taking the extreme everything should be a SUBBEGIN and a SUBCOMMIT/SUBABORT so why have BEGIN and END? Unless you have some way to tell (by query) the state you are in is a subtransaction and how many levels you are deep into the nested transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs the traditional BEGIN COMMIT/ABORT becomes nondeterministic. ---(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] Nested Transactions, Abort All
Alvaro Herrera wrote: On Fri, Jul 02, 2004 at 01:37:46PM -0500, Thomas Swan wrote: Alvaro Herrera wrote: Then again, since a statement is always transactionally wrapped, would it be required to always issue SUBBEGIN if issued from within a function? This would address my concern. Isn't this counterintuitive. It seems that BEGIN and COMMIT/ABORT should be sufficient regardless of the level. If you are inside a current transaction those commands start a new transaction inside of the current transaction level, just like pushing on and popping off elements on a stack. No, the first level is quite different from any other, and that's why it should use a different syntax. Really any level above level 1 is not a transaction at all; it's a unit that you can rollback independently but nothing more; you can't commit it independently. I think a better term than subtransaction or nested transaction is rollback unit or some such. Some functions and procedures may not be called inside of transactions or subtransactions. No. Some functions cannot be called inside a transaction block. Whether you are or not inside a subtransaction within the transaction block is not important. In fact, the application doesn't care what nesting level it is in; the only thing that it cares about is if it is in a transaction block or not. Please note that I'm using the term transaction block and not transaction. The distinction is important because everything is always inside a transaction, though it may be an implicit one. A transaction block, on the other hand, is always an explicit thing. And a subtransaction is also an explicit thing. This is the reason the outermost block is irrelevant to the point. Inner transactions (including the implicit ones mentioned) commit only if their parent transactions commit. If there is an implicit begin/commit, then everything underneath should be subbegin/subcommit. If it is sometimes implicit then the subbegin/begin state is non-deterministic. Without the underlying or stack depth, it is difficult to predict. In psql, autocommit (on/off) behavoir becomes a little muddy if you go with the SUBBEGIN and SUBCOMMIT construct. Below should BEGIN (1) be a SUBBEGIN or a BEGIN? Both examples would give equivalent results. -- BEGIN (implicit) BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) COMMIT (implicit) -- BEGIN (1) BEGIN SOMETHING BEGIN SOMETHING ROLLBACK ROLLBACK COMMIT (1) -- Unless you have some way to tell (by query) the state you are in is a subtransaction and how many levels you are deep into the nested transaction, deciding whether to use SUBBEGIN and SUBCOMMIT/SUBABORT vs the traditional BEGIN COMMIT/ABORT becomes nondeterministic. The application always has to keep track if it is inside a transaction block or not. This has always been true and it continues to be so. Whether you are inside a subtransaction or not is not really important. If you want to commit the whole transaction block just issue COMMIT, and all levels will be committed. psql will tell me how deep I am in transactions? Similarly if you want to abort. But if you want to retry a subtransaction which has just failed you better know whether you are on a subtransaction or not ... I mean if the app doesn't know that then it isn't using subtransactions, is it? That's an rather big assumption? It may not be the app, it may include stored procedures and functions as well. Imagine a little function called dance( ). Dance begins a transaction, does a little work, and then aborts. If I am not in a transaction and I write dance as a subtransaction then I have the problem and call it then I have a problem. If I am in a transaction and I write dance as a transaction, then I have a problem. There's no universal way to write the function, without having to refer to an external state unless I make the scope universal. Hence, SUBBEGIN and SUBCOMMIT are bad ideas. Knowing just the nesting level will not help you -- the app has to know _what_ to retry. And if it isn't going to retry anything then there's no point in using subtransactions at all. If you have the nesting level, then you know how many commits/rollbacks to perform to get to an entrance state. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[HACKERS] Nested Transactions, Abort All
Is there going to be an option to abort the complete transaction without knowing how deep you are? Perhaps something like ABORT ALL. The reason I suggest this, is that I can foresee an application or user leaving nested transactions open inadvertently, or not knowing how deeply nested they are when they are called. It's just a thought, and I didn't recall any mention of something like it on the list. Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL pre-fork speedup
sdv mailer wrote: Hi Bruce, Sorry for the confusion because Rod asked a question and I answered too quickly. This is what I mean. 15x Slower: --- Client --TCP-- PgPool --UNIX-- PostgreSQL Client --TCP-- PgPool --TCP-- PostgreSQL 5x Faster: -- Client --UNIX-- PgPool --UNIX-- PostgreSQL Client --UNIX-- PgPool --TCP-- PostgreSQL Hope this helps! Pgpool speeds up connection time by 5x with UNIX socket due to pre-fork and connection pooling. However, pgpool slows down by 15x under TCP socket for some unknown reason. Do you have SSL enabled on the postgresql server? If you do, this would account for the slower connect time over TCP/IP. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] inconsistent owners in newly created databases?
Fabien COELHO wrote:
Dear hackers,
It seems to me that the current default setup for a new database which is
given to some user is not consistent (createdb -O calvin foo or
CREATE DATABASE foo WITH OWNER calvin).
Indeed, although the database belongs to the owner, the public schema
still belongs to the database super user, as it was the case in template1.
As a consequence, the owner of the database CANNOT change the rights of
the schema, hence he cannot prevent anyone from creating a new table in
the public schema! However, has he owns the database, he can prevent user
from creating temporary tables... Not really consistent.
This is a real problem if that owner wants to drop or create types,
operators, or precreated tables in the template that was copied. It
seems that you would want to go through and give the owner all the
ownership on items that were possible. I've used a database template
with the pg_crypto added in and some other custom routines and found
that the owner of the database couldn't update or access those copied
tables because of the permission on those tables and objects.
* create the database with the new owner specified.
-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
update pg_type set typowner = {userid}
Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.
Dropping (the owner of a database can do that) and recreating the schema
is not a real fix, because all installation performed on template1
(plpgsql, functions...) would be lost.
So it seems to me that the public schema should also belong to the owner
of the database. I cannot foresee all consequences, but the current
situation is really inconsistent.
Any comment?
---(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] inconsistent owners in newly created databases?
Fabien COELHO wrote:
Dear Thomas,
* create the database with the new owner specified.
-- As a superuser in the newly created database
update pg_am set amowner = {userid}
update pg_class set relowner = {userid}
You don't want to update ownership of tables in system schemas.
AFAICS, any changes they make are localized to their database not the
whole database system. In other words, they can change add drop types,
procs, tables as if they were a superuser but only in their database.
A normal account (the db owner in this case) still cannot select against
pg_shadow or add users (without createdb privilege).
update pg_conversion set conowner = {userid}
update pg_namespace set nspowner = {userid}
As for SCHEMAs, I would not do that for system schemas
(pg_%, information_schema)...
It doesn't seem any different than running as the superuser and changing
those. Again, I think it would be restricted to someone frying their
own database, but not the whole system.
update pg_opclass set opcowner = {userid}
update pg_operator set oprowner = {userid}
update pg_proc set proowner = {userid}
I'm not sure system functions owner should be change. Also, call handlers
for languages should not change owner.
Without this the db owner cannot drop types that may have been copied
from the template.
update pg_type set typowner = {userid}
Are there any security problems that this would cause? Perhaps these
should be done by the system automatically.
I think that something along the line you describe should be done by the
system. However database creation does not actually connect to the new
database, the template base directory is simply copied with a cp -r.
It is unclear to me at the time when these updates should be performed.
After the createdb? Deferred to the first connection to the database?
It seems the logical place is for the createdb routine to connect to the
new database and make the ownership changes.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] inconsistent owners in newly created databases?
Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: Fabien COELHO wrote: You don't want to update ownership of tables in system schemas. AFAICS, any changes they make are localized to their database not the whole database system. A database owner who is not a superuser should *not* be able to fool with the built-in catalog entries. Database owner != superuser, and I don't want us blurring the distinction... With regards to changing ownership, is there a way to determine what is a built-in catalog entry? If the database that was the template was modified, can the createdb routine determine what is core and what was added? ---(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] What can we learn from MySQL?
Bruce Momjian wrote: My question is, What can we learn from MySQL? I don't know there is anything, but I think it makes sense to ask the question. MySQL became popular at my university when the students discovered they could install it on their personal computers. Just the exposure for personal development and trial is enough to win a following. Win32 installations are a big deal. With win32 machines outnumbering *nix operating systems by more than 10 to 1 (more on personal computers), the unix only restriction reduced the number of possible people testing and developing with it by at least that amount. Most developers I know work primarily on Windows workstations and asking for a machine to run Postgresql on unix is just not practical. With the win32 port, they can run it on their computers and at least test or evaluate their projects. I and a number of my friends are exceptionally please at the progress of the win32 port. Thank you! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_autovacuum crashes when query fails for temp tables
Bruce Momjian wrote: I looked into this and I see a number of cases where pg_autovacuum calls send_query(), but doesn't test for a NULL return from the function. Matthew, would you look into this and submit a patch? Thanks. Should pg_autovacuum be vacuuming temporary tables? Secondly, why would a temporary table for another session be visible to pg_autovacuum? I know these may sound like stupid questions, but I'm a little shocked that it found a temp table. Did someone make a permanent table, delete it, and pg_autovacuum found it in between those operations? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] rotatelogs integration in pg_ctl
quote who=Andrew Hammond Tom Lane wrote: Andrew Hammond [EMAIL PROTECTED] writes: I've attached a patch for pg_ctl which integrates the Apache project's rotatelogs for logging. Why bother? You just pipe pg_ctl's output to rotatelogs and you're done. It's not difficult to do, once you know how and once you know that there aren't any gotchas. However, the question comes up often enough it's clear that not everybody knows how. This provides a simple, clean, standardized way of using rotatelog. The patch is simple, low risk, and limited impact. So, why not? Is there a reason the postmasters cannot just close/reopen-recreate the log file when a SIGHUP or other signal is issued like apache? This would allow for almost any scheme for log rotation to be handled by the system or third party like logrotate and removes any duplicate effort between projects. I know on most distributions /var/log is not world writeable, so renaming and and opening a file as postgres will not succeed. If the log files are put in, for example, /var/log/pgsql with pgsql being rwx by postgres, then it will work. The current packaging may need to be redone if you want to enable loggin by default (if only startup and shutdown messages) ---(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] rotatelogs integration in pg_ctl
quote who=Peter Eisentraut Thomas Swan wrote: Is there a reason the postmasters cannot just close/reopen-recreate the log file when a SIGHUP or other signal is issued like apache? Yes, because there is no log file. The postmaster writes to stdout or stderr. Ok, my misunderstanding. stdout/stderr are redirected to a file on startup. This is why when I move/rename the logfile I have to stop/start postgresql to start appending to the empty file. Would there be any interest in modifying postmaster to support native file logging in addition to stderr and stdout output? Are there any terrible drawbacks that you could foresee? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] PostgreSQL configuration
quote who=Bruce Momjian The only other idea I can think of is to create a new pg_path.conf file. It would have the same format as postgresql.conf, but contain information about /data location, config file location, and perhaps pg_xlog location. The file would be created by special flags to initdb, and once created, would have to be used instead of pgdata for postmaster startup. Bruce, I thought the idea was to *reduce* the number of config files and provide a unified configuration file. Ideally, the unified configuration file could eliminate the need for environment variables altogether. If I understand this correctly, the author was adding the ability to do this, not remove the default behavior. A single configuration point (which can be changed with a commandline switch) with the ability to include would be an exceptionally versatile asset for postgresql. Maybe relocating PID would be a bad idea and someone could clobber their database, but that could be addressed with LARGE WARNING in that config file where the option is available. Outside of the unified config file argument. Configuration includes give postgresql the ability to have shared settings. You could have a shared pg_hba.conf and test all other manner of settings with a set of config files (sort_mem, shared_buffers, etc.) that say include a standard_pg_hba.conf to control access. The single config file argument has the capacity to emulate the existing default behavior. # SINGLE DEFAULT CONFIG FILE Include /var/lib/data/postgresql/postgresql.conf Include /var/lib/data/postgresql/pg_hba.conf Include /var/lib/data/postgresql/pg_ident.conf or #SINGLE DEFAULT CONFIG FILE include options /var/lib/postgresql/data/postgresql.conf include access /var/lib/postgresql/data/pg_hba.conf include identity_map /var/lib/postgresql/data/pg_ident.conf ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PostgreSQL configuration
Bruce Momjian wrote: Thomas Swan wrote: I thought the idea was to *reduce* the number of config files and provide a unified configuration file. Ideally, the unified configuration file could eliminate the need for environment variables altogether. If I understand this correctly, the author was adding the ability to do this, not remove the default behavior. A single configuration point (which can be changed with a commandline switch) with the ability to include would be an exceptionally versatile asset for postgresql. Maybe relocating PID would be a bad idea and someone could clobber their database, but that could be addressed with LARGE WARNING in that config file where the option is available. Outside of the unified config file argument. Configuration includes give postgresql the ability to have shared settings. You could have a shared pg_hba.conf and test all other manner of settings with a set of config files (sort_mem, shared_buffers, etc.) that say include a standard_pg_hba.conf to control access. I suggested a new pg_path configuration file because it would enable centralized config only if it was used. By adding /data location to postgresql.conf, you have the postgresql.conf file acting sometimes via PGDATA and sometimes as a central config file, and I thought that was confusing. Understandably.I think that using a config file that can specify all of this would be a big win. Imagine a simple start of the postmaster with only a pointer to a config file, and not having to rely on special environment variables or other command line switches. ---(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] PostgreSQL configuration
[EMAIL PROTECTED] wrote: I just had a thought about this: seems like a big part of the objection is the risk of specifying -C and -D that don't go together. Well, what if they were the same switch? Consider the following simplification of the proposed patch: I was really excited about this idea, then I thought about it, and while it would answer some of the issues I mean to address, I find myself a little disappointed that some of the functionality I wanted, i.e. multiple databases with the same configuration, was not possible. However, compromise is good. 1. Postmaster has just one switch, '-D datadir' with fallback to environmental variable PGDATA, same as it ever was. I like this, I think, ... but it removes the posibility to run the same configuration with the same database. This scenario is one of my best case reasons why I think my patch is good, but, I think I can get 99% of what I'm looking for with my modification outlined at the bottom of this post. 2. The files that must be found in this directory are the configuration files, namely postgresql.conf, pg_hba.conf, pg_ident.conf. (And any files they include are taken as relative to this directory if not specified with absolute path. We'll still add the #include facility to postgresql.conf; the others have it already IIRC.) My patch *already* has this functionality if it is a directory. I agree with this, it was suggested (maybe even by you) over a year ago. [snip -- good stuff] Tom, this is great! I think we are almost there and I really appreciate your flexibility in view of my obstinance. :-) I like what you suggest, While I don't get the -D and -C functionality (which I don't use, but thought was cool), I think I would like to add one thing: postmaster -D /etc/postgres/postgresql.conf If the path specified is a config file, then data_dir MUST address a valid PostgreSQL data directory. This is exceptionally confusing. Why not do a test and say that you cannot specify a -C and a -D option at the same time. This would still assure backwards compatability and safeguard future installations. If the -C option is specified the datadir must be present in the config file. If someone wants to specify the config file from a startup option, then they must follow the new rules. And, as this is new functionality, the rules can be set now. Adding one command line switch with the future possibility of eliminating the others is a good tradeoff, IMHO. So, here is (how I see) the logical breakdown of the feature: postmaster -D /somedir/data works as it always has, it points to the data dirtectory in which all the various config files live. If No data_dir is specified, then /somedir/data is assumed to be where base, pg_xlog, pg_clog, and etc. reside. If, however, data_dir is specified, the data oriented elements like global, base, pg_clog, and pg_xlog are contained within that directory. (In the future, we may be able to specify these locations separately) If postmaster -D /etc/postgresql.conf points to a file, then that file MUST specify the location of data_dir, hba_conf, and ident_conf. Like I said, while I don't get the convenience of combining -D ... and -C ... I do get most of what I'm asking for. If this works for all you guys, I'll submit a patch Wednesday. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Function to kill backend
Bruce Momjian wrote: pgman wrote: Josh Berkus wrote: Tom, I don't think it's an open-and-shut decision as to whether people actually *need* to do session kills (as opposed to query/transaction kills). The arguments presented so far are not convincing to my mind, certainly not convincing enough to buy into a commitment to do whatever it takes to support that. Hmmm ... well, I can make a real-world case from my supported apps for transaction/statement kills. But my support for session kills is just hypothetical; any time I've had to kill off sessions, it's because I had to shut the database down, and that's better done from the command line. My web apps which need to manage the number of connections do it through their connection pool. So I would vote for Yes on SIGINT by XID, but No on SIGTERM by PID, if Tom thinks there will be any significant support troubleshooting involved for the latter. Unless, of course, someone can give us a real business case that they have actually encountered in production. Someone already posted some pseudocode where they wanted to kill idle backends, perhaps as part of connection pooling. Tom, if you have concerns about SIGTERM while other backends keep running, would you share those. (Holding locks, shared memory?) I looked at die(), and it seemed pretty safe to me. It just sets some variables and returns. It is not like quickdie that calls exit(). If there is a problem, maybe we can fix it, or perhap have the kill function use SIGINT, then wait for the query to cancel, then SIGTERM. One other option to consider while your looking at killing off children as an admin is the ability to switch the database to single user mode for the admin via psql or other connection interface. In essence shutdown all other transactions and session and limit it to only one so that maintenance issues (reindexing, etc) can be done remotely and do not require shell access on the host machine. I think, ultimately, this may be what is being asked for: a way to manage postgresql internally via SQL or other functions rather than relying on user access to the machine (ie sending signals to processes). This is what some people have wanted to do with managing the connection settings in pg_hba.conf via a system table rather than or in addition to a file. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: subversion vs cvs (Was: Re: [HACKERS] linked list rewrite)
quote who=Dustin Sallings On Mar 25, 2004, at 1:21, Neil Conway wrote: I think the lack of good Win32 support (unless rectified before the release of 7.5) is a pretty major problem with Arch -- that alone might be sufficient to prevent us from adopting it. I don't do Windows, but my understanding is that tla is as well supported on Windows as postgres is. It that like the best beach volleball player in Antarctica? The Windows port of Postgresql is still in its infancy. It's coming along, but its not a finished product. The design is fundamentally easy enough that a Windows user who cares could probably make a more suitable port for Windows than the UNIX guys are interested in making. I've seen such discussions on the list. -- Dustin Sallings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(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] COPY formatting
quote who=Andrew Dunstan Karel Zak wrote: The problem with CSV is that it will correctly work with new protocol only. Because old versions of clients are newline sensitive. And CSV can contains newline in by quotation marks defined attributes: John, Smith, The White House 1600 Pennsylvania Avenue NW Washington, DC 20500, male, open source software office It is one record. (Long Live President Smith!) I have never seen such a beast, and if I had been asked off the cuff would have said that it was probably illegal, except that I know of no standard actually defining the format. Perhaps others (Joshua Drake or Josh Berkus?) have wider experience. I think in any case we should ignore those for now and handle the straightforward case. I *have* seen monstrosities like fields that do not begin with the quote character but then break into a quote, e.g.: 1,2,a,123abcdef,6,7,8 I have dealt with both, frequently. The CSV format allows you to begin a block of text with the quote. Newlines are included in that quoted space. If qoutes are included in the field then the quotes are double quotes to denote they are not part of the quoted space. Also, the following is valid. 1,2,, is empty. 1,2,3,, The 4 quotes denote a single double quote. Writing simple CSV converts that just explode on commas and newlines miss these oddities. Try exporting an Access table with a Memo field (containg multiple lines) to CSV. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [pgsql-www] [HACKERS] The Name Game: postgresql.net vs.
quote who=Tom Lane My feeling is that we want people to consider these projects as closely tied to the Postgres community and so postgresql.something is just right. I can see there are different opinions out there though... foundry.postgresql.org? ---(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] [GENERAL] Shouldn't B'1' = 1::bit be true?
quote who=Tom Lane Bill Moran [EMAIL PROTECTED] writes: Am I missing something here? Hmm. It seems like int-to-bit casting ought to be aware of the bit-width one is casting to, and take that number of bits from the right end of the integer. This would make it be the inverse of the other direction. Right now it's only an inverse when you cast to and from bit(32). For shorter bitfield widths, we're effectively inserting at the right end of the integer, but removing bits from the left, which is not consistent. regression=# select B'11000'::bit(5)::int; int4 -- 24 (1 row) regression=# select 24::int::bit(32); bit -- 00011000 (1 row) regression=# select 24::int::bit(32)::bit(5); bit --- 0 (1 row) regression=# select 24::int::bit(5); bit --- 0 (1 row) If we made int-to-bit-N take the rightmost N bits, then the last two cases would yield different results, but that doesn't seem unreasonable to me. Or at least it's less unreasonable than bit(5)-to-int not being the inverse of int-to-bit(5). Comments? Tom, I would suggest looking at char to int to char conversions for correctness examples. This can be looked at as bit(8)::bit(32)::bit(8) operations. To convert low bits ot high bits you pad 0 bits on the left. To convert from high to low you strip bits off the left hand side. This allows reasonable behavior. Coverting from low precision to high precision and back to low precision should be lossless for bits. High to low to high should be lossy for bits because you lost bits in the smaller container. So the conversion should be symmetric for values that fit in the bitspace. Even the MS calculator does this. 65534::bit(8)::int should yield 254 just as 254::int::bit(8) should be 254 and 254::int::bit(32)::bit(8)::int should yield 254. I would say the current way of handling bit conversions is broken. I for one would like to see the bitwise behavior be more correct. It's almost as if the bit(32)::bit(8) operation is thinking of string truncation. Is this endian related? Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [pgsql-hackers-win32] [HACKERS] Tablespaces
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: [EMAIL PROTECTED] wrote: Zeugswetter Andreas SB SD [EMAIL PROTECTED] writes: My feeling is that we need not support tablespaces on OS's without symlinks. To create symlinked directories on Win2k NTFS see: http://www.sysinternals.com/ntw2k/source/misc.shtml#junction I think Win2000 or XP would be a reasonable restriction for Win32 PG installations that want tablespaces. Oh, good --- symlinks for directories are all that we need for this design. I think that settles it then. What archival tools are there that would restore this to this back to the filesystem: tar? zip? What would happen if a symlink were removed or pointed to an invalid location while the postmaste was running? Well, for backup, just run tar or find on /data with a flag to follow symlinks, and you are done. Can't get much easier than that. I'm ruferring to NTFS and the win32 platforms. How does tar handle these symlinks on the NTFS filesystem? What about if someone finds that FAT32 is significantly better for the database? tar doesn't know anything about PostgreSQL system catalogs. If we use symlinks for tablespaces then it would be possible to backup downed databases with a simple tar command on every platform *I* care about (and probably Windows too). Using system catalogs for this stuff would simply guarantee that I would have to read the system catalogs and then back up each tablespace manually. In short, your idea would trade off (maybe) having to backup tablespaces manually on a few platforms for the certainty of having to backup tablespaces manually on all platforms. How is that a win? Apparently, I have failed tremendously in addressing a concern. The question is does PostgreSQL need to rely on symlinks and will that dependency introduce problems? There is an active win32 port underway (see this mailing list). One proposal was to try to use an OS specific filesystem feature to perform a symlink on NTFS. Can the special symlink that NTFS allegedly supports be archived the same way symlinks are archived on Unix? If so, is there a utility that can do this (zip, tar, etc). The backup operator would still need to know what directories needed to be archived in addtion to the pgdata directory.Is this symlink structure a normal/special file that can be archived by normal means (tar,zip, etc)? Example: PGDATA is C:\pgdata I have a tablespace in Z:\1\ and Z:\2\ There exists an alleged symlink in C:\pgdata\data\base\tablespaces\schmoo - Z:\1 Can I archive [ C:\pgdata, Z:\1, Z:\2 ], restore them, and have postgresql working just as before? It seems a little insane to introduce an OS/filesystem dependency at the onset of a porting effort especially if you hope to be OS agnostic for feature sets. I think someone would be crying foul if a new feature only worked on Linux and not on FreeBSD. First of all, symlinks are a pretty popular feature. Even Windows supports what would be needed. Second of all, PostgreSQL will still run on OSes without symlinks, tablespaces won't be available, but PostgreSQL will still run. Since we are all using PostgreSQL without tablespaces now, it can hardly be argued that tablespaces are a critical feature. We aren't talking about a feature that work[s] on Linux on not on FreeBSD. We are talking about a feature that works on every OS that suports symlinks (which includes even operating systems like Windows that PostgreSQL doesn't currently support). Hello? What was this response from Tom Lane? My feeling is that we need not support tablespaces on OS's without symlinks. That seems to be indicative of a feature set restriction base on platform. Additionally, another developer noted the advantage of a text file is that it would be easy for someone to develop tools to help if it became difficult to edit or parse. Additionally, there could be a change away from a flat file format to an XML format to configure the tablespace area. The advantage of symlinks is that no tools would have to be written and 'ls -l' would show everything you would need to know about where your tablespaces actually were. Where is 'ls -l' on a win32 box? If you will follow the discussion of symlinks under MinGW you will see that they don't work as commanded. And, postgresql is supposed to be compiled under MinGW, but not require it to run. From Windows 2000, 'ls' is not recognized as an internal or external command, operable program or batch file. XML files are relatively easy to parse, but they certainly aren't as easy as simply letting PostgreSQL follow a symlink. Why reinvent the wheel with what would essentially be PostgreSQL's own implementation of a symlink? Is opening a file recreating a symlink? If you are opening file descriptors why rely on symlinks. If you know the location either from the system catalog, a or
Re: [HACKERS] Tablespaces
Bruce Momjian wrote: Greg Stark wrote: Bruce Momjian [EMAIL PROTECTED] writes: Greg Stark wrote: I am expecting to hear some bleating about this from people whose preferred platforms don't support symlinks ;-). However, if we don't Well, one option would be to have the low level filesystem storage (md.c?) routines implement a kind of symlink themselves. Just a file with a special magic number followed by a path. On further contemplation it doesn't seem like using symlinks really ought to be necessary. It should be possible to drive everything off the catalog tables while avoidin having the low level filesystem code know anything about them. Instead of having the low level code fetch the pg_* records themselves, some piece of higher level code would do the query and call down to storage layer to inform it of the locations for everything. It would have to do this on database initialization and on any subsequent object creation. Basically maintain an in-memory hash table of oid - path, and call down to the low level code whenever that hash changes. (Or more likely oid-ts_id and a separate list of ts_id - path.) The advantage of symlinks is that an administrator could see how things are laid out from the command line. That's a poor reason to require symlinks. The administrator can just as easily open up psql and query pg_tablespace to see that same information. Besides, the postgres doesn't know where to look on the filesystem for the /path/to/oid without a system catalog lookup. There doesn't seem to be any sensible reason to force a filesystem requirement when the core operations are diffferent to begin with. If a more global view of all databases is necessary, perhaps there ought to be a system wide view which could display all of that information at once: dbname, relation name, and physical location. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Tablespaces
Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: Bruce Momjian wrote: The advantage of symlinks is that an administrator could see how things are laid out from the command line. That's a poor reason to require symlinks. The administrator can just as easily open up psql and query pg_tablespace to see that same information. Something to keep in mind here is that one of the times you would most likely need that information is when the database is broken and you *can't* simply open up psql and inspect system catalogs. I like the fact that a symlink implementation can be inspected without depending on a working database. That's a sufficient argument, to allow for it. Recoverability would be one reason. If we were going to build a non-symlink implementation, I'd want the highlevel-to-lowlevel data transfer to take the form of a flat ASCII file that could be inspected by hand, rather than some hidden in-memory datastructure. But given the previous discussion in this thread, I cannot see any strong reason not to rely on symlinks for the purpose. We are not in the business of building replacements for OS features. I do like the flat file output at least for a record of what went where. Regardless of whether or not symlinks are used, the admin would need to know what directories/files/filesystems are to be backed up. I am concerned as to what extent different filesystems do when you back the directories up.Would NTFS containing symlinks be able to be backed up with a tar/zip command, or is something more elaborate needed? In the past, before upgrading, I have had to tar the pgdata directory with the postmaster shutdown to insure a quick restoration of the database in case an upgrade didn't proceed uneventfully. Also, in the event of a major version upgrade the restored information may or may not proceed uneventfully.I just wanted to point out something I thought might be an issue further down the road. Perhaps the system catalog / flat file approach would be a more solid approach, both of which would not involve replacing or duplicating OS features. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Preventing duplicate vacuums?
Robert Treat wrote: On Thu, 2004-02-05 at 16:51, Josh Berkus wrote: Tom, Yes we do: there's a lock. Sorry, bad test. Forget I said anything. Personally, I would like to have the 2nd vacuum error out instead of blocking. However, I'll bet that a lot of people won't agree with me. Don't know if I would agree for sure, but i the second vacuum could see that it is being blocked by the current vacuum, exiting out would be a bonus, since in most scenarios you don't need to run that second vacuum so it just ends up wasting resources (or clogging other things up with it lock) What about a situation where someone would have lazy vacuums cron'd and it takes longer to complete the vacuum than the interval between vacuums. You could wind up with an ever increasing queue of vacuums. Erroring out with a vacuum already in progress might be useful. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] nomenclature
Marc G. Fournier wrote: On Fri, 16 Jan 2004, Michael Glaesemann wrote: On Jan 16, 2004, at 9:39 PM, Jeff Davis wrote: I can't point to any OSS project that completely renames its parts. I think a shortened version of the name makes sense (in this case postgres works well, but so does pgsql), and other projects do similar things. Psql for the client and postmaster for the daemon are the ones that really confuse people, I think. I'd agree with you there. I think they may be confusing for two different reasons: postmaster because there's no obvious connection (besides POSTmaster and POSTgreSQL) This one I have to agree with also ... 'postmaster' always makes me think of the mail system ... *but* ... for those that are dealing with the database server, and who many never have seen a mail system in their life, the same may not be true ... In all honesty, when I first installed Linux system with Postgresql I couldn't help but wonder why I had two different MTA's. The funny thing is that the postmaster doesn't really do anything, its the postgres process that does all the work ... if you think about it, the postmaster is actually aptly named, since it is the process that sorts out the incoming connections and assigns them to backend processes ... just like the postmaster does with your mail ... Perhaps postgresd, postgresqld, or pg_daemon might be a little more intuitive? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] nomenclature
Marc G. Fournier wrote: On Fri, 16 Jan 2004, Thomas Swan wrote: Perhaps postgresd, postgresqld, or pg_daemon might be a little more intuitive? I think at this late stage in the game (almost 10 years), changing could be a bit difficult and confusing, no? :) I'd go with something like pgsqld myself though, keeps it short ... or we could go even shorter with just pgd ... But, I'm not, in any stretch of the imagination, advocating for change on this ... Agreed, to change it would be a bit insane. Although a little insanity often surrounds a major version release... I just thought the anecdote of confusing it for an MTA was a little funny. Thomas ---(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] psql \d option list overloaded
Bruce Momjian wrote:
Alex J. Avriette wrote:
On Sun, Jan 04, 2004 at 07:59:02PM -0600, D. Dante Lorenso wrote:
Anything other than simple, short commands is a waste, IMHO. I can easily
remember SHOW DATABASES and SHOW TABLES and DESC table, because they
reflect
my intensions directly and 'make sense'.
What makes sense to me in csh doesn't make sense in a bourne shell.
You can't expect all applications to work correctly. I'd like to second
Peter's yep when asked if he could remember all the various \d*
commands. It really comes down to whether you're trying. New software
(even though you may have been using it for a year) requires some
adjustment.
OK, I will drop the idea. Thanks.
Bruce,
The idea is not without merit. What you are looking at is a way to get
this information as a query without having to know all the intricasies
of all the pg_* internals or duplicating complex queries. psql -E
shows you just how tricky this is. Secondly, if this information
changes in a release, then the end user has to rewrite all of the
queries to work. Being able to issue a query to the dbms and get the
information as a normal SQL result makes sense and is definately convenient.
The \d* commands work from psql but not from anywhere else.Try
getting the information from a PHP script by sending a \dS query. It
doesn't work. If the same queries were stored in the backend and
referenced by psql and also could be referenced by other scripts, this
would be a good thing and keep the work centralized. If the queries
were in the backend, the psql users could keep the \dS command but it
would call an internal function or execute a queried stored in the
system tables.
One option is to get the information via a function like
SELECT * FROM pg_info('tables');
SELECT * FROM pg_info('indexes');
psql -E would show the same query being executed for \dt
Another option if no one wanted a language construct, perhaps one option
would be to store the queries themselves in a table like pg_queries.
This also has the advantage of exposing the queries used so that they
can used as examples for other purposes.
++--+
|pg_info_type|pg_query |
++--+
|tables |SELECT n.nspname as Schema, c.relname |
||as Name, CASE c.relkind WHEN 'r' THEN |
||'table' WHEN 'v' THEN 'view' WHEN 'i' THEN|
||'index' WHEN 'S' THEN 'sequence' WHEN 's' |
||THEN 'special' END as Type, u.usename as|
||Owner FROM pg_catalog.pg_class c LEFT |
||JOIN pg_catalog.pg_user u ON u.usesysid = |
||c.relowner LEFT JOIN |
||pg_catalog.pg_namespace n ON n.oid = |
||c.relnamespace WHERE c.relkind IN ('r','')|
||AND n.nspname NOT IN ('pg_catalog', |
||'pg_toast') AND |
||pg_catalog.pg_table_is_visible(c.oid) |
||ORDER BY 1,2; |
++--+
|indexes |SELECT n.nspname as Schema, c.relname as|
||Name, CASE c.relkind WHEN 'r' THEN |
||'table' WHEN 'v' THEN 'view' WHEN 'i' THEN|
||'index' WHEN 'S' THEN 'sequence' WHEN 's' |
||THEN 'special' END as Type, u.usename as|
||Owner, c2.relname as Table FROM |
||pg_catalog.pg_class c JOIN|
||pg_catalog.pg_index i ON i.indexrelid = |
||c.oid JOIN pg_catalog.pg_class c2 ON |
||i.indrelid = c2.oid LEFT JOIN |
||pg_catalog.pg_user u ON u.usesysid = |
||c.relowner LEFT JOIN |
||pg_catalog.pg_namespace n ON n.oid = |
||c.relnamespace WHERE c.relkind IN ('i','')|
||AND n.nspname NOT IN ('pg_catalog', |
||'pg_toast') AND |
||pg_catalog.pg_table_is_visible(c.oid) |
||ORDER BY 1,2; |
++--+
Again, this is just food for thought. Perhaps it is a way to satisfy
both arguments.
Thomas
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Resurrecting pg_upgrade
Matthew T. O'Connor wrote: On Fri, 2003-12-12 at 15:42, Tom Lane wrote: Alternative thought: just recommend that if possible, people take a filesystem dump of their old PGDATA directory after stopping the old postmaster. This would be sufficient for retreating to the prior version if needed. It might or might not be slower than copying all the files to a new PGDATA ... Certainly the easier path code wise :-) Being the belt, suspenders and steel tip boots (foot gun protection) type that I am, I would make a backup even if pg_upgrade copies all the data files. Having pg_upgrade copy the data files give you an extra layer of protection if desired, and can possibly save an admin who fails to get a good backup of the old PGDATA for what ever reason. I'd be in favor of a prompt at the beginning of the script. Have made a copy of the PGDATA directory? If answered no, then ask for a confirmation to proceed without backup? To skip the prompt have an option for '--skip-prompt' for those who are a little more sure of themselves or want to write a more automated script for this process. This approach gives more flexibility as there may not be sufficient storage available for double the existing database size for conversion on that mount point / disk. The admin doing the upgrade can copy the existing database wherever they need it: tape, another filesystem, NFS mount, etc. -- Thomas Swan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Resurrecting pg_upgrade
Tom Lane wrote: Dave Smith [EMAIL PROTECTED] writes: Why not go the other way. 1) Dump the schemas. 2) Initdb with the new schemas in a tmp PGDATA 3) backup the schemas in the current PGDATA 4) move the new schemas from the new db into the current one. This seems like approximately the same thing except you lose the property of not having modified the old DB if you fail partway through. What's the advantage exactly? I do not think that approach buys you much. More than just the schemas change from each major release. The binary (on-disk) format of the relations can change as well, hence the need for the upgrade program. A schema with corrupt data is worthless. ** Warning the user to backup the PGDATA directory, should be sufficient, IMHO. Perhaps even echo a URL to the postgresql.org site for specific backup and upgrade procedures and recommendations. With a full copy of the PGDATA directory an admin can copy the data back reinstall the old version of postgresql and do a postmortem while the old version is still operational without having to keep the service unavailable. If someone is absolutely certain the upgrade will work without an errors then they can holster their loaded gun with the safety off. If there is an error the data can be copied back, old postmaster started, and possibly correct the problem (maybe a reindex operation or the like). Then repeat the upgrade procedure. This approach seems much more simple and flexible as the admin could backup the database to tape or some other medium, possibly multiple volumes, and then do the upgrade in place. ** If the pg_upgrade program were to read/copy old data and output a new data doubling the storage requirements, then you have a quick way to restart the upgrade procedure on failure without having to load the old data again. It seems to me that an error in the upgrade program would likely happen again at the same point on a repeat attempt, so I don't think there are any significant advantages to the upgrade program doing the copy/backup operation. Exceptionally large databases would have to find additional storage for the copy operation. If the copy and upgrade approach were to be followed, it would be advantageous to the admin to be able to specify where the copy of the existing PGDATA would go or the newly generated files could go before they could be moved back to the PGDATA directory. This means that doing an update you would only have to have space for the system catalogs not the whole database. That's true either way. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(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] postgres --help-config
Bruce Momjian wrote: Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Agreed. I like --dump-config. Better to have the verb first. My only objection to that is that dump suggests you will get some kind of snapshot of current settings, which is not what this facility does. I think people will associate dump with pg_dump, meaning dump out the data. I don't think the snapshot idea will not occur to them. Copy has an in/out capability that doesn't match. --display-config might have the more accurate meaning you were looking for. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
Tom Lane wrote: Sean Chittenden [EMAIL PROTECTED] writes: Are there any objections to me increasing the block size for FreeBSD installations to 16K for the upcoming 7.4 release? I'm a little uncomfortable with introducing a cross-platform variation in the standard block size. That would have implications for things like whether a table definition that works on FreeBSD could be expected to work elsewhere; to say nothing of recommendations for shared_buffer settings and suchlike. Also, there is no infrastructure for adjusting BLCKSZ automatically at configure time, and I don't much want to add it. Has anyone looked at changing the default block size across the board and what the performance improvements/penalties might be? Hardware has changed quite a bit over the years. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Bumping block size to 16K on FreeBSD...
Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: Tom Lane wrote: I'm a little uncomfortable with introducing a cross-platform variation in the standard block size. Has anyone looked at changing the default block size across the board and what the performance improvements/penalties might be? Hardware has changed quite a bit over the years. Not that I know of. That might actually be a more reasonable proposal than changing it only on one platform. It would take a fair amount of legwork to generate enough evidence to convince people, though ... I know that you can specify different block sizes for different fs/OS combinations, notably there were discussions before about running the WAL on a fat16/32 disks with different performance characteristics. Also, it's not just an OS abstraction; hardware has changed and evolved in such a way that the physical disks are reading and writing in larger chunks. To me it would seem wasteful to not use that bandwidth that is available for little or no extra cost. Perhaps testing it for 8K, 16K, 32K, and 64K blocksizes would be a worthwhile venture. I will have time this weekend with the holiday to work on some benchmarking for these sizes if only on a linux system. Tom, what would you consider to be acceptable for a preliminary investigation? What should I look at: runtime, disk space required before and after, fsync (on/off)? -- Thomas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PostgreSQL 7.4 Beta 1 + SSL + Cygwin
On 8/8/2003 5:49 AM, Jason Tishler wrote: Carlos, On Fri, Aug 08, 2003 at 09:20:01AM +0200, Carlos Guzman Alvarez wrote: I want to know if postgresql 7.4 beta 1 can be configured under Cygwin with SSL support ?? If the answer is positive how can i do it ?? or where can i found documentation about this ( under linux or cygwin :) ) ?? I have found this on PostgreSQL docs :) i have it configured yet. Is this just the --with-openssl option? Does it build cleanly under Cygwin? If so, would you like me to include this in the next Cygwin PostgreSQL release? 7.4beta1 would not compile under Cygwin with or without SSL. However, the CVS tip for 2003/08/20 did compile and run under Cygwin both with and without SSL. I had to adjust some path variables to include cygwin/usr/lib and cygwin/usr/lib/postgresql. postgresql was configured with ./configure --prefix=/usr --with-openssl make make install I used ipc-daemon2, and I had to use cygwin.dll v1.5.x. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Can't find thread on Linux memory overcommit
On 8/20/2003 1:02 PM, Josh Berkus wrote: Hackers, I've been searching the archives, but I can't find the thread from last month where we discussed the problem with Linux memory overcommits in kernel 2.4.x. Can someone point me to the right thread? I think maybe the subject line was something deceptive Re: [HACKERS] Pre-allocation of shared memory ... On 6/11/2003 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] SELECT FOR UPDATE question
When a SELECT FOR UPDATE query is executed, are the row level locks on a table acquired in any specific order such as to enhance deadlock prevention? ( primary key, oid, etc. ) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] SELECT FOR UPDATE
Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: When a SELECT FOR UPDATE query is executed, are the row level locks on a table acquired in any specific order Nope, just whatever order the chosen plan happens to visit the tuples in. I had remembered several readings on ordered locking as a method to prevent deadlocks, and associated that with select for update methodology. In theory if you aquired locks in the following order, for each table/relation (in oid order) get rows/tuples (in oid order), you could help avoid deadlock by never gaining a lock ahead of someone else. Hmmm this would only help for situations where all the locks of interest are grabbed in a single scan. I suppose that has some usefulness, but it can hardly be said to eliminate deadlocks. I kinda doubt it's worth the trouble. If you sort the locks before acquiring them, then you could prevent a deadlock conditions. Proper planning from the programmer can help to alleviate most of the rest. Hypothetically, if I knew the relations that contained the tuples I was locking, I could conceivably visit them in, let's say, alphabetical order or oid order or any predictable order, and then select the rows for update (using oid order or primary key order). It would be hard to induce a deadlock condition if the locks being acquired where in a consistently reproducible order by all processes. Perhaps it's just an academic discussion, but I think it could work in terms of performance. I'm just not sure how much work is necessary to sort the locks prior to acquiring them. ---(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] SELECT FOR UPDATE
On 7/25/2003 8:10 AM, Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: Hypothetically, if I knew the relations that contained the tuples I was locking, I could conceivably visit them in, let's say, alphabetical order or oid order or any predictable order, and then select the rows for update (using oid order or primary key order). It would be hard to induce a deadlock condition if the locks being acquired where in a consistently reproducible order by all processes. Given that this requires programmer discipline anyway, I think it's okay to leave the whole thing in the hands of the programmer. My original comment that the locks are acquired in an unpredictable order wasn't right --- they are acquired by the executor's top level, therefore in the order the rows come out of the execution engine, and therefore you can make the order predictable if you want to. Just use ORDER BY. What I'm referring to is a single select for update statement (could be a join, a single table, etc.) If the programmer is going to do their locks on multiple tables in several steps then that's the programmers responsibility. If OIDs are unique per database then sort by them. If OIDs are unique per table and each table has a unique OID then use a hash of the two values, sort and then acquire. This would prevent two single queries from resulting in deadlock mid-execution. ---(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] SELECT FOR UPDATE
When a SELECT FOR UPDATE query is executed, are the row level locks on a table acquired in any specific order such as to enhance deadlock prevention? ( primary key, oid, etc. ) ---(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] SELECT FOR UPDATE
Bruce Momjian wrote: Thomas Swan wrote: When a SELECT FOR UPDATE query is executed, are the row level locks on a table acquired in any specific order such as to enhance deadlock prevention? ( primary key, oid, etc. ) Interesting question, because in a join, you could have multiple tables involved. Sorry, I don't know the answer. I had remembered several readings on ordered locking as a method to prevent deadlocks, and associated that with select for update methodology. In theory if you aquired locks in the following order, for each table/relation (in oid order) get rows/tuples (in oid order), you could help avoid deadlock by never gaining a lock ahead of someone else. Locks could be released in the same order. The system should be predictable even with oid wrap arounds. I'm quite sure that someone has done something like this for postgres though Perhaps table/row oids are a good idea? ---(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] php with postgres
On 7/22/2003 11:18 AM, scott.marlowe wrote: On Mon, 21 Jul 2003, Bruce Momjian wrote: Jan Wieck wrote: Bruce Momjian wrote: Marcus B?rger wrote: BM Marcus, would you check if PHP is using RESET ALL when passing BM persistent connection to new clients? We added that capability a few BM releases ago, specifically for PHP persistent connections, but I don't BM think that ever got into the PHP code. Unfortunately we don't do so yet. Do i need to check for errors or can i do it unconditionally on conenction start? And i'd need to know how to check if it is available (like starting with which version). It first appeared in PostgreSQL version 7.2. It doesn't generate any failures. It just resets all SET settting to their defaults, in case the previous client modified them. It does generate the usual error if the current transaction block is in ABORT state. So the correct querystring to send would be something like ROLLBACK; RESET ALL Oh, I remember that now as part of the persistent connection code. As I remember, we told them to do BEGIN;COMMIT; to clear any open transaction state passed to the new client. Is that in there? If not, it has to be added too. ROLLBACK will generate an error if you are not in a transaction, so it would fill the logs with errors. Won't that break when we have nested transactions implemented? i.e. begin;commit; would just open a sub transaction and have no effect on the outer transaction... I was just about to mention that one. Perhaps a ROLLBACK ALL would be of benefit to allow a clean state and start to work again. Doesn't autocommit behavior affect this as well? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] dblink_ora - a first shot on Oracle ...
On 7/21/2003 9:16 AM, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: I don't see the problem. I tend to agree with Peter: if dblink is going to start depending on stuff outside Postgres, it ought to be become a separate project, if only to simplify distribution and configuration issues. The ability to optionally link to another library does not necessitate a functional dependency on it. Perhaps it could be split into two parts, a PG-specific part and a cross-DBMS part? regards, tom lane PS: Has anyone looked any further at the SQL-MED standard? ISTM that's where we ought to head in the long run. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Two weeks to feature freeze
Peter Eisentraut wrote: Thomas Swan writes: I just am really concerned about the uninstall/clean up phase and how that can be done in an orderly fashion. Unless the process can start from a clean state again, then it won't be valid. The only clean state is if you remove the entire source tree and check it out again. (Of course to save bandwidth, you copy the checked out source tree to a temporary location, do your testing, and then remove that temporary tree.) Relying on make clean or make uninstall is flawed, because those are among the things you want to test. That sounds plausible. Should we let everything stay in the compilers directory. Something like the configure --prefix=$TEST_ROOT and that way we can have the whole thing run as one user in one directory so that system wide impact is minimal.I guess what I'm concerned with is running this on a clean system, and then leaving unknown artifacts behind. Can/does make install output each file it's copying and where to. Capturing that output would make life easier for clean up of things installed outside of the work directory, and provide a more controlled environment. ---(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] Two weeks to feature freeze
Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Thomas Swan writes: Have you considered something similar to the Mozilla tinderbox approach where you have a daemon checkout the cvs, compile, run regression tests, and report a status or be able to report a status? Even if you could achieve near complete coverage of the platforms, platform versions, and auxilliary software versions and combinations that PostgreSQL runs with, in most cases, something breaks on a new version or combination of these things. Still, whenever we're doing something that interacts at all with the OS, it seems we get breakages that don't show in the original author's testing, but only pop up days to months later when some beta tester tries the code on platform P or using option Q. The current difficulties with the IPv6 patches are a fine case in point. If we could get feedback more easily about whether a proposed patch compiles and passes regression on a variety of platforms, we could reduce the pain involved by a great deal, simply because the problems could be fixed while the code is still fresh in mind. I don't think there is any company involved with Postgres that is willing to commit the resources to run a Mozilla-style tinderbox setup singlehanded. But I wonder whether we couldn't set up something that is community-based: get a few dozen people with different platforms to volunteer to check the code regularly on their own machines. I'm imagining a cron job that fires daily in the wee hours, pulls the latest CVS tip, does make distclean; configure; make; make check, and mails the results to someplace that puts 'em up on our website. It's possible that we could adapt the tinderbox software to work this way, but even if we had to write our own, it seems like a fairly simple task. And it'd give *much* better feedback on porting problems than we have now. Sure, there will always be corner cases you don't catch, but the first rule of testing is the sooner you find a bug the cheaper it is to fix. Is it possible the sourceforge compile farms could be used for some of the automated testing? I'm not sure how that system works, but it could be worth looking into. 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]) ---(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] Two weeks to feature freeze
Nigel J. Andrews wrote: On Thu, 26 Jun 2003, Thomas Swan wrote: Is it possible the sourceforge compile farms could be used for some of the automated testing? I'm not sure how that system works, but it could be worth looking into. Isn't the sourceforge license very scary and along the lines of whatever you put on here we own it's just we tend not to persue that at the moment as there's not much money in it for us but that doesn't stop us from claiming it at some indeterminate time in the future? If it's that intrusive, then it was a bad idea. But, I didn't find anything like that on their Terms of Use http://sourceforge.net/docman/display_doc.php?docid=6048group_id=1 page. The compiler farm has a relatively small number of platforms, but perhaps it would be enough to get started with at least verifying an automated test would work. See Guide to the Sourceforge Compile Farm http://sourceforge.net/docman/display_doc.php?docid=762group_id=1. In terms of implementation, I was thinking of something like the following. * clean the source, destination directories * pull latest CVS tip down. * record environment / installed packages * loop - on different options ( w/ or w/o krb5, w/ or w/o ssl, etc. ) o make clean o configure with sets of options o compile + log messages + analyze errors ( perhaps gather statitistics: warnings, failures, notices, etc.) o (run / install) if successful o run tests + output results (perhaps to HTML) + compare results with expected + record differences if any | gather aggregate information o uninstall / clean up * end loop Perhaps there could be an occasion where the test would be able to put in a corrupt WAL or a corrupt table to do regression tests for recovery of errors. Of course, these are just ideas and I'm not sure how practical it is to do any of them. I just am really concerned about the uninstall/clean up phase and how that can be done in an orderly fashion. Unless the process can start from a clean state again, then it won't be valid. At one point I had even given thought, vainly, to purchasing VMWare for such an occasion. Suggestions? ---(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] Two weeks to feature freeze
The Hermit Hacker wrote: On Thu, 26 Jun 2003, Thomas Swan wrote: Of course, these are just ideas and I'm not sure how practical it is to do any of them. I just am really concerned about the uninstall/clean up phase and how that can be done in an orderly fashion. Unless the process can start from a clean state again, then it won't be valid. At one point I had even given thought, vainly, to purchasing VMWare for such an occasion. Suggestions? Personally ... if you could build up the test script, I think there are enough ppl with more platforms on these lists that would be willing ot run it ... the problem isn't getting the farm together, its coming up with the automated (or even semi-automated) tests :( I'll see what I can do... my shell script skills are pretty good, but I'm not sure how to handle the noting changes in the gcc output. My best guess is to just do it a couple of times and force something to change (make an intentional mistake) and see if it can catch it, or at least what changes. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Two weeks to feature freeze
Larry Rosenman wrote: --On Saturday, June 21, 2003 11:43:17 -0400 Tom Lane [EMAIL PROTECTED] wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Thomas Swan writes: Have you considered something similar to the Mozilla tinderbox approach where you have a daemon checkout the cvs, compile, run regression tests, and report a status or be able to report a status? Even if you could achieve near complete coverage of the platforms, platform versions, and auxilliary software versions and combinations that PostgreSQL runs with, in most cases, something breaks on a new version or combination of these things. Still, whenever we're doing something that interacts at all with the OS, it seems we get breakages that don't show in the original author's testing, but only pop up days to months later when some beta tester tries the code on platform P or using option Q. The current difficulties with the IPv6 patches are a fine case in point. If we could get feedback more easily about whether a proposed patch compiles and passes regression on a variety of platforms, we could reduce the pain involved by a great deal, simply because the problems could be fixed while the code is still fresh in mind. I don't think there is any company involved with Postgres that is willing to commit the resources to run a Mozilla-style tinderbox setup singlehanded. But I wonder whether we couldn't set up something that is community-based: get a few dozen people with different platforms to volunteer to check the code regularly on their own machines. I'm imagining a cron job that fires daily in the wee hours, pulls the latest CVS tip, does make distclean; configure; make; make check, and mails the results to someplace that puts 'em up on our website. It's possible that we could adapt the tinderbox software to work this way, but even if we had to write our own, it seems like a fairly simple task. And it'd give *much* better feedback on porting problems than we have now. Sure, there will always be corner cases you don't catch, but the first rule of testing is the sooner you find a bug the cheaper it is to fix. 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]) I'm willing to run such a job on UnixWare 7.1.3 and OpenUnix 8, as well as FreeBSD 4.8 I'll have a machine shortly where I can run RH9 SMP tests.. ---(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] Two weeks to feature freeze
Tom Lane wrote: Alvaro Herrera [EMAIL PROTECTED] writes: It would be nice to have a system which could receive a patch and compile and verify that it passes the tests before it goes to Bruce's queue; or compile on multiple platforms to check for portability problems, for example. *snip* Another point is that passing on one platform doesn't ensure passing on another. Here we really rely on the willingness of the pghackers community to update to CVS tip regularly and run the regression tests when they do. Again, tests that take a couple minutes to run are ideal; if they took a week then the uptake would drop to zero, and we'd not be ahead. Have you considered something similar to the Mozilla tinderbox approach where you have a daemon checkout the cvs, compile, run regression tests, and report a status or be able to report a status? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] OPAQUE and 7.2-7.3 upgrade
Oliver Elphick wrote: On Thu, 2002-09-12 at 15:54, Tom Lane wrote: Oliver Elphick [EMAIL PROTECTED] writes: On Thu, 2002-09-12 at 15:31, Tom Lane wrote: Does anyone see a cleaner answer than re-allowing OPAQUE for PL handlers? Can't you just special case the language handlers when dumping 7.3 and change 'RETURNS opaque' to 'RETURNS language_handler'? That's all that is needed to let them be restored OK into 7.3. Only if people dump their old databases with 7.3 pg_dump; which is an assumption I'd rather not make if we can avoid it. I don't understand. The only pg_dump we can fix is 7.3. You can't backport such a change into 7.2 or it won't work for 7.2 restore. If you are using 7.3 pg_dump it isn't an assumption but a certainty that it is being used. If someone restores into 7.3 with a 7.2 dump they are going to have other problems, such as turning all their functions private. Since they are going to need to edit the dump anyway, they might as well edit this bit too. Surely we should be advising them to use 7.3's pg_dump to do the upgrade. The alternative approach is to build a set of kludges into =7.3 to change opague to language_handler when a language function is installed. That doesn't sound like a good idea. Is it possible to build a standalone 7.3 dump/dump_all program that can be run on a server with an existing 7.2.x installation and not be linked against 7.3 libraries? Call it a migration agent if you will. A notice of somekind would help: Before upgrading, dump the database using this program.
Re: [HACKERS] pg_dump, pg_dumpall and createdb privilege
Neil Conway wrote: Thomas Swan [EMAIL PROTECTED] writes: 1. create a user with createdb privilege. 2. create a database as that user (allowing that user full reign over that particular db) 3. drop the createdb from the user. 4. pg_dumpall the databases to a single file 5. either use pg_restore or psql infile to restore the databases and it fails. This is a known problem, I remember raising it on hackers several months ago. If there was a 7.2.2 release it would be wonderful for this to be knocked out, too. Is it possible to address this before the 7.3 release or is this concern no longer a problem in the 7.3 branch? It's fixed in CVS. Databases are now created using CREATE DATABASE xxx WITH OWNER yyy: this technique can also be used by the DBA in the first place, avoiding the need to manually add and then remove CREATEDB privs from the new user account. This is a wonderful thing for those of us running postgresql in a hosting environment.
Re: [HACKERS] SET LOCAL again
Bruce Momjian wrote: Tom Lane wrote: Peter Eisentraut [EMAIL PROTECTED] writes: Tom Lane writes: As an alternative syntax I can suggest SET name TO value [ ON COMMIT RESET ]; Ugh. Why can't we stick with SET LOCAL? SET LOCAL is already used for something else in the SQL standard. Not sure if we'll ever implement that, but it's something to be concerned about. Actually, it looks to me like the spec's SET LOCAL has a compatible interpretation: it only affects the current transaction. My main gripe with "ON COMMIT RESET" is that it's a misleading description of what will happen --- RESETting a variable is quite different from allowing it to revert to the pre-transaction state. I don't like stuff trailing off at the end, especially three words. That SET command is getting so big, it may fall over. ;-) Perhaps ON COMMIT REVERT would be more intuitive.
Re: [HACKERS] ATTN: Tom Lane
Tom Lane wrote: David Ford [EMAIL PROTECTED] writes: Tom, if you block everyone on cable, dialup, dsl, and adsl, then you're probably blocking a lot of legitimate mail. David, let me explain this in words of one syllable: I am currently rejecting upwards of 2000 spam messages per day. If I did not have extremely stringent filters in place, email would be completely useless to me. Advice suggesting that I weaken my filters will be ignored with as much grace as I can muster, which on most days is not a lot. This is what comes of having several well-publicized email addresses :-( I sympathize with your pain. However, I've found that the five-ten-sg.com list is ofter overly aggressive. There are many other RBL's that are not as aggressive and used in combination provide very good results. Also, you could even try SpamCop's RBL, if your so inclined. I could not post from my work address to any of the lists strictly because of the five-ten-sg.com RBL. They blocked everything from BellSouth's IP allocation blocks. They only way around it is to beg them to allow you a static IP and the ask to have that IP unbanned from the RBL. It's a lot of work. RBL's are good, but I think the one that blocked David Ford and myself is perhaps a little too strong. Just my two cents. Thomas
Re: [HACKERS] timeout implementation issues
Bruce Momjian wrote: Hiroshi Inoue wrote: Bruce Momjian wrote: I guess it's a matter of definition: Do you consider SET variablesdatabase state or session metadata? I think some are this and some arethat. I'm not sure how to draw the line, but throwing everything from onecategory into the other isn't my favorite solution. You seem to be suggesting that we should make a variable-by-variabledecision about whether SET variables roll back on ABORT or not. I thinkthat way madness lies; we could spend forever debating which vars arewhich, and then who will remember without consulting the documentation?I feel we should just do it. Yeah, there might be some corner caseswhere it's not the ideal behavior; but you haven't convinced me thatthere are more cases where it's bad than where it's good. You surehaven't convinced me that it's worth making SET's behaviornigh-unpredictable-without-a-manual, which is what per-variable behaviorwould be. I am with Tom on this one. (Nice to see he is now arguing on my side.) I vote against you. If a variable is local to the session, youcan change it as you like without bothering any other user(session).Automatic resetting of the varibales is rather confusing to me. I don't see how this relates to other users. All SET commands that canbe changed in psql are per backend, as far as I remember. Per backend or per session?
Re: [HACKERS] Bulkloading using COPY - ignore duplicates?
Zeugswetter Andreas SB SD wrote: [EMAIL PROTECTED]"> IMHO, you should copy into a temporary table and the do a select distinct from it into the table that you want. Which would be way too slow for normal operation :-(We are talking about a "fast as possible" data load from a flat filethat may have duplicates (or even data errors, but that is another issue).Andreas Then the IGNORE_DUPLICATE would definitely be the way to go, if speed is the question...
[HACKERS] Re: bugs - lets call an exterminator!
Tom Lane wrote: [EMAIL PROTECTED]"> Vince Vielhaber [EMAIL PROTECTED] writes: On Thu, 23 Aug 2001, Colin 't Hart wrote: 5. I think Bugzilla's concepts of products, components and versions fitthe way we work.I envisage that 'Postgres', 'Interfaces', 'Languages' might be productsthat we would have.Within 'Postgres' we would have the various subsystems that make up thecore.Within 'Interfaces' we would have 'JDBC', 'ODBC' etc.Within 'Languages' we would have 'PL/pgSQL' etc. I can see a little benefit to this, but for the most part the samepeople that are working on the core pieces of PostgreSQL are alsoworking on the interfaces and languages. I would argue against subdividing a bug database at all. I don't thinkthe project is large enough to require it (we are in no danger ofbecoming the size of Mozilla anytime soon). But more importantly,subdivision introduces the risk of misclassification of a bug --- andin my experience the initial reporter of a bug *very* frequentlymisidentifies where the problem is. So unless additional effort isexpended to reclassify bugs (is that even possible in Bugzilla?), theclassification will degenerate to the point of being a hindrance ratherthan a help in locating things. Overall I just don't see that muchbenefit from a classification system. Bugzilla does provide for the reclassification bugs. I have misidentified where bugs were in Mozilla and have had them reclassified into different areas/components of that project.
[HACKERS] Re: plpgsql.
Bill Shui wrote: Hi there, I remember that in earlier versions of Postgres. You have to do something (which I cannnot remember) to enable a user to create plpgsql functions. which versions of postgres were they? thanks in advance. Bill CREATELANG as a command ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: New data type: uniqueidentifier
Peter Eisentraut wrote:
Dmitry G. Mastrukov writes:
I've developed new data type for PostgreSQL -unique identifier - 128-bitvalue claims to be unique across Universe. It depends on libuuid frome2fsprogs by Theodore Ts'o.
ISTM that this should be a function, not a data type.
I'd second the function idea: function uuid( ) returns an int8 value; don't
create a bazillion datatypes. Besides, 128 bit numbers are 7 byte integers.
PostgreSQL has an int8 (8 byte integer) datatype. While I like the UUID
function idea, I'd recommend a better solution to creating an "unique" identifier.
Why not create a serial8 datatype: int8 with an int8 sequence = 256bit "unique"
number. {Yes, I know I'm violating my first sentence.} Then, you'd have
the same thing (or better) AND your not relying on randomness.
Re: [HACKERS] Re: New data type: uniqueidentifier
I sit corrected.
*slightly humbled*
Why not do an unsigned int16 to hold your UUID generated numbers. Ultimately,
this would seem to be a more general solution and accomplish your goals at
the sametime. Or, am I completely missing something.
Christopher Kings-Lynne wrote:
don't create a bazillion datatypes. Besides, 128 bit numbers are 7byte integers.
Hang on: 128 div 8 = 16 byte integer
PostgreSQL has an int8 (8 byte integer) datatype.
And therefore it is a _64_ bit integer and you can't have a 256bit uniquenumber in it...
While I like the UUID function idea, I'd recommend a better solution tocreating an "unique" identifier. Why not create a serial8 datatype:int8 with an int8 sequence = 256bit "unique" number. {Yes, I knowviolating my first sentence.} Then, you'd have the same thing (orbetter) AND your not relying on randomness.
Chris
[HACKERS] Re: Good name for new lock type for VACUUM?
Tom Lane wrote: Awhile ago I said that I wanted to create a new flavor of table-level lock for concurrent VACUUM to get on a table. RowExclusiveLock is not the right thing because it is not self-exclusive, whereas we don't want more than one VACUUM mangling a table at a time. But anything higher locks out concurrent writers, which we don't want either. So we need an intermediate lock type that will conflict with itself as well as with ShareLock and above. (It must conflict with ShareLock since we don't want new indexes being created during VACUUM either...) *snip* BTW, I'm assuming that I should make the new lock type available at the user level as a LOCK TABLE option. Any objections to that? I think that type of lock would best be kept to the system level. *thinking out loud* If your goal is to have it used more often, then user level might provide more opportunities for testing. However, I can't really think of any situation where it would be beneficial to a user. The rest of the locks seem to take care of everything else. Is it going to timeout? If a connection is dropped by a user, will the lock release? ---(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] Re: Universal admin frontend
Michael Meskes wrote:
On Wed, Jun 20, 2001 at 09:13:13AM +0200, Pedro Abelleira Seco wrote:
- Phppgadmin is a web based tool. You need a PHPenabled web server. Most end users/admins don't wantto have to configure a web server, PHP ("what isPHP?") and to have a poor interface (I'm talking aboutweb based interfaces in general, not the phppgadmin inparticular).
Maybe, but then you are platform independent.
First, we need a set of tasks that the software would need to be able to
do. These tasks, may answer your questions or at least help decide which
environment would best suit your admin tool.
AFIAA, there exists a port of Java for just about every OS that PostgreSQL
supports, not that it should be the only reason for choosing it. Not that
my vote counts, but I'd go for the java approach and be willing to code a
lot on the interface, anyone else interested?
To start this list off, the Good Idea (tm):
User Management
Create
List
Modify
Change Password
Grant permissions
Group Membership
Delete
Database Management
Create
List
Modify
Tables
Constraints
Rules
Owners/Permissions
Delete
Maintenance
Vacuum
Analyze
Monitoring
Statistics
This is one of the big things that PostgreSQL has been missing for sometime.
Personally, I believe that it would benefit both developers and users.
Regardless, that's my two bits...
[HACKERS] Re: Primary Key
Tom Lane wrote: [EMAIL PROTECTED]">After fixing the several obvious syntax errors, it works fine for me:regression=# CREATE TABLE messageregression-# (regression(# msgid int4 not null,regression(# msgtext text,regression(# CONSTRAINT cons_001_pk PRIMARY KEY (msgid)regression(# );NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'cons_001_pk' for table 'message'CREATEregression=# \d messageTable "message" Attribute | Type | Modifier---+-+-- msgid | integer | not null msgtext | text|Primary Key: cons_001_pkregression=#Is Cold Fusion perhaps doing strange things to the query behind yourback? None of those CREATE TABLE commands are legal SQL accordingto my references. I've been using the syntax "PRIMARY KEY (column_name [, column_name ])," without the constraint name, and the "COLUMN_NAME TYPE PRIMARY KEY" syntax for sometime now. I may be admitting to SQL heresy in saying that; but, that's the syntax I've seen in MySQL and in quite a few SQL/database books. AFIAK, it's a legal table creation statement.
Re: [HACKERS] BLOBs
Bruce Momjian wrote:
Thomas Swan [EMAIL PROTECTED] writes:
I know that BLOBs are on the TODO list, but I had an idea.
I think you just rediscovered TOAST.
We have TOAST and people want to keep large objects for performance. Ithink we could us an API that allows TOAST binary access and largeobject access using the same API, and hopefully an improved one.
I think I missed what I was trying to say in my original statement. I think
there's a way to use the existing API with performance benefits left intact.
Take for example the table :
create table foo {
foo_id serial,
foo_name varchar(32),
foo_object BLOB,
);
On the insert statement "insert into foo (foo_name,foo_object) values
('My Object','{some escaped arbitrary string of binary data}');", flush
the {some escaped arbitrary string of binary data} to disk as a temporary
file. Then do the lo_import operation transparent to the user.
On a select, do the same thing (transparently) and return the data back to
user.
Personally, I like LO's being stored separately from the actual table.
[HACKERS] Feature request : Remove identifier length constraints
I just got bit by the identifier name is too long and will be truncated limitation in Postgresql. AFIAA there is a limit of 64 characters for identifiers (names of tables, sequences, indexes, etc...) I had just started to get in the habit of using serial data types until I made to tables with long names and the automatic sequence names that were generated conflicted, *ouch* ... Is there the possibility of a name conflict resolution during the table creation phase similar to the name I want to assign is already taken, so I'll pick a different name... on the serial data type? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] BLOBs
I know that BLOBs are on the TODO list, but I had an idea. I think the storage of a BLOB outside of the table is an elegant solution and keeps table sizes down without the bloat of the stored object. Granted, if you are searching with a regular expression or using like or ilike clauses, you're likely to be a little slower but it shouldn't be by much. More than likely, you won't be searching for patterns in the BLOB but rather the fields in the table associated with the BLOB. Wouldn't it be wonderful if you used the methods you had already implemented and instead create a behavoir similar to the following. on an insert take the data that was to be the blob... create your externally to be referenced file save the data to the file store the reference to that file on an update take the data that was to be the blob... create your externally to be referenced file save the data to the file store the reference to that file delete the old referenced file on a delete delete the reference to your file delete the external file I was thinking that the BLOB column type might be a trigger for a macro that could handle the lo_import, lo_export juggling... I know it seems overly simplified, but having fought with MySQL and then trying to wrestle with postgresql and importing,exporting BLOBs, it seemed there might be a little more room for discussion, although I doubt this may have added anything to it... I'd love to see something done with BLOB support during 7.2.x *hint* :) Besides, if someone could give me some pointers as to where I might be able to start, I might try to contribute something myself. Thomas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: New Linux xfs/reiser file systems
mlw wrote: [EMAIL PROTECTED]">Bruce Momjian wrote: Just put a note in the installation docs that the place where the databaseis initialised to should be on a non-Reiser, non-XFS mount...Sure, we can do that now. What do we do when these are the default filesystems for Linux? We can tell them to create other types of filesystems, but that is a pretty big hurdle. I wonder if it would beeasier to get reiser/xfs to make some modifications. I have looked at Reiser, and I don't think it is a file system suited for verylarge files, or applications such as postgres. The Linux crowd should lobbyagainst any such trend. It is ok for many moderately small files. ReiserFSwould be great for a cddb server, but poor for a database box.XFS is a real big file system project, I'd bet that there are file propertiesor management tools to tell it to leave directories and files alone. Theyshould have addressed that years ago.One last mention..Having better control over WHERE various files in a database are located canmake it easier to deal with these things. I think it's worth noting that Oracle has been petitioning the kernel developers for better raw device support: in other words, the ability to write directly to the hard disk and bypassing the filesystem all together. If the db is going to assume the responsibility of disk write verification it seems reasonable to assume you might want to investigate the raw disk i/o options. Telling your installers that a major performance gain is attainable by doing so might be a start in the opposite direction. I've monitored a lot of discussions and from what I can gather, postgresql does it's own set of journaling operations. I don't think that it's necessary for writes to be double journalled anyway. Again, just my two cents worth...
[HACKERS] Re: Indexes not used in 7.1RC4: Bug?
At 4/10/2001 02:42 PM, Thomas Lockhart wrote: Hmm. The problem is as you describe, but the requirements for a solution are more severe than you (or I) would hope. We would like to have an extensible mechanism for type promotion and demotion, but it is not (yet) clear how to implement it. In this case, we must demote a constant assigned as "int4" by the parser into an "int2" to be directly comparable to the indexed column. We could probably do this with some hack code as a brute-force exercise, but no one has yet bothered (patches welcome ;) But in general, we must handle the case that the specified constraint is *not* directly convertible to the indexed type (e.g. is out of range) even though this would seem to reduce to a choice between a trivial noop or a sequential scan of the entire table. If we can do this without cluttering up the code too much, we should go ahead and do it, but it has apparently been a low priority. What about going the other way around... Promote the int2 to an int4 (lossless). Actually for all int1,int2 datatypes (regardless of whether it was the constant or the column) you could promote all to a common int4 and then do comparisons. Promoting all to int8 and then doing a comparison would be excessively slow. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Re: Feature Request: ALTER FUNCTION (or something like that)
At 3/28/2001 09:52 AM, Joel Burton wrote: On Wed, 28 Mar 2001, Bruce Momjian wrote: Actually, This reminds me of something I have been meaning to ask. All the new performance features are cool, but what I really need are all the ALTER TABLE ... functions implemented. In 7.0.x you could only add columns and foreign keys. You couldn't drop anything or add stuff like CHECK constraints. Has this situation changed for 7.1? Not done in 7.1. I hope for 7.2. If you're curious, Christopher, on the TODO list is a whole page on the DROP COLUMN bit. There's been some headscratching about how we want to DROP COLUMNs (whether to hide them but keep them there, which may be easier to undo, and probably easier to develop, and doesn't require any time to process), or whether to actually recreate w/o the offending column. You could hide and then delete on a vacuum. On a personal note, one "work around" for altering a table column is to rename the column, then do an update where you set the new columns value to the renamed columns value thus copying its data over to the new column with any casting if necessary. It doesn't always work, but it's one way to try. But then you're left with the renamed column dangling... Selecting the table(with your preferred columns) into a new table can clean it Delete the old table and rename the new table to the old name... You lose the constraints/triggers you had in place though... And you must pray that no one is altering the DB while your doing this... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: RPM building (was regression on RedHat)
At 3/20/2001 09:24 PM, Thomas Lockhart wrote: It's a good start to test with for the purposes for which I think you want to test for. (and I'm an English teacher by night -- argh). :) Mandrake (as of 7.2) still does a brain-dead mix of "-O3" and "-ffast-math", which is a risky and unnecessary combination according to the gcc folks (and which kills some of our date/time rounding). From the man page for gcc: -ffast-math This option should never be turned on by any `-O' option since it can result in incorrect output for programs which depend on an exact implementation of IEEE or ANSI rules/specifications for math functions. I'd like to get away from having to post a non-brain-dead /root/.rpmrc file which omits the -ffast-math flag. Can you suggest mechanisms for putting a "-fno-fast-math" into the spec file? Isn't there a mechanism to mark things as "distro specific"? Suggestions? I don't know if it helps. But, a stock install has the environment MACHTYPE=i586-mandrake-linux. If you hunt for mandrake in the MACHTYPE variable you could reset those variables. Also, I think those are set in the rpmrc file of the distro for the i386 target. If you specify anything else like i486, i686, you don't have that problem. It would be in the RPM_OPT_FLAGS or RPM_OPTS part of the build environment. I don't think there would be a problem overriding it, in fact, I would recommend the following : RPM_OPTS="$RPM_OPTS -fno-fast-math". Since gcc will take the last argument as overriding the first, it would be a nice safeguard. Even setting CFLAGS="$CFLAGS -fno-fast-math" might be good idea. Hope this helps, Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Re: AW: Shutdown term
OK, I phoned Tom and we agreed on this wording:
This connection has been terminated by the administrator
Comments?
This connection has been terminated by an administrator
(there may be more than one...) :)
Other than that it's informative enough.
OTOH, I had a small thought on this.
If you had a messaging scheme to print to clients when a signal was
received, is there the possibility of more informative messages perhaps
that could be sent by the pg_ctl program through the postmaster (or
backends) on shutdowns? This would allow for some decent scripting. For
example, the database is shutdown without the system going down or the
whole system is going down for maintenance or scheduled reboot.
It may seem stupid but I was thinking the reason could be an argument to
the pg_ctl program with a default of (Database Shutdown).
pg_ctl stop --message="System going down for a reboot"
or
pg_ctl stop -msg "System upgrade. System will be available again at 5:00am"
The client would receive
The connection has been terminated
[System Shutdown|Database Shutdown|Unknown Reason|"some string as an argument"]
Also, it allows for more informative messages.
Scheduled downtime (System will be online again at {whenever})
Idle Timeout
You are using too much CPU...
You are using too little CPU...
These message can be set by the scripts for "run level" changes and the like.
---(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] Re: AW: Shutdown term
At 3/14/2001 11:13 AM, Peter Eisentraut wrote: Thomas Swan writes: It may seem stupid but I was thinking the reason could be an argument to the pg_ctl program with a default of (Database Shutdown). pg_ctl stop --message="System going down for a reboot" or pg_ctl stop -msg "System upgrade. System will be available again at 5:00am" I foresee a PQmotd(PGconn *) function ... ;-) Well, I also thought you could use the same method to do a warning. pg_ctl --message="Database going offline in 5 minutes" or something along those lines... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Re: Performance monitor signal handler
On reciept of the info signal, the backends collaborate to piece together a status file. The status file is given a temporay name. When complete the status file is rename(2)'d over a well known file. Reporting to files, particularly well known ones, could lead to race conditions. All in all, I think your better off passing messages through pipes or a similar communication method. I really liked the idea of a "server" that could parse/analyze data from multiple backends. My 2/100 worth... ---(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] Re: How to shoot yourself in the foot: kill -9 postmaster
At 3/5/2001 04:30 PM, you wrote: Now, killing the postmaster -9 and not cleaning up the backends has always been a good way to shoot yourself in the foot, but up to now the worst thing that was likely to happen to you was isolated corruption in specific tables. In the brave new world of WAL the stakes are higher, because the system will refuse to start up if it finds a corrupted checkpoint record. Clueless admins who resort to kill -9 as a routine admin tool *will* lose their databases. Moreover, the init scripts that are running around now are dangerous weapons if used with 7.1. I think we need a stronger interlock to prevent this scenario, but I'm unsure what it should be. Ideas? Is there anyway to see if the other processes (child) have a lock on the log file? On a lot of systems, when a daemon starts, will record the PID in a file so it/'the admin' can do a 'shutdown' script with the PID listed. Can child processes list themselves like child.PID in a configurable directory, and have the starting process look for all of these and shut the "orphaned" child processes down? Just thoughts... Thomas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] BETWEEN patch
At 1/24/2001 10:19 AM, Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: A patch to gram.y in src/backend/parser Provides for the SQL99 expected behavior of select * from foo where fo_num between 1 and 5 yields the same result as select * from foo where fo_num between 5 and 1 This is NOT correct under either SQL92 or SQL99. Read the spec again. regards, tom lane After sending it... I realized that it was not correct either. So, I'm back to figuring how to do it... so, um, ignore the previous patch... Thanks..
[HACKERS] BETWEEN patch
A patch to gram.y in src/backend/parser Provides for the SQL99 expected behavior of select * from foo where fo_num between 1 and 5 yields the same result as select * from foo where fo_num between 5 and 1 Granted this is brute force and not very elegant, however it does provide the correct behavior. Optimally it would be nice to do a comparison on the values after between and then sort the two limiters and do a single rewrite leaving only one pass or scan. In other words in pseudo SQL: select * from foo where fo_num between a and b becomes select * from foo where ((fo_num = min_value(a, b)) and (fo_num = max_value(a,b)) This would yield only two comparisons or resolutions and then a single sequential or index scan to find the correct tuples. This was done against beta1... between.patch -- - Thomas Swan - Network Administrator - Graduate Student - Computer Science - - The Institute for Continuing Studies - The University of Mississippi - - http://www.ics.olemiss.edu - http://www.olemiss.edu
[HACKERS] Re: postgresql.conf and postgres options
I think the list is great, show what can be configured rather than
guessing/digging to find it, where it belongs, in what order (if any), etc.
etc. etc.
The only addition I could think would be to label (default value).
Needless, I like it.. :)
At 1/24/2001 01:03 PM, Bruce Momjian wrote:
Bruce Momjian writes:
I have added all possible config options to postgresql.conf.sample.
It was actually fully intentional that there was *no* list of all possible
config options in the sample file, because
1) Who's going to maintain this?
2) People should read the documentation before messing with options.
(" is not the correct string delimiter either.)
Changed to ''. Thanks.
I have bad experiences with sample config files. The first thing I
usually do is delete them and dig up the documentation.
Do other people have comments on this issue?
I have marked all places where these defaults are set in the C code,
pointing them to update postgresql.conf.sample.
I found it is nice to see a nice list of all options for quick review.
It makes the file much more useful, I think.
[HACKERS] Re: SIGTERM - elog(FATAL) - proc_exit() is probably a bad idea
I'll take care of fixing what I broke, but does anyone have suggestions for good names for the two concepts? The best I could come up with offhand is BEGIN/END_CRIT_SECTION and BEGIN/END_SUPER_CRIT_SECTION, but I'm not pleased with that... Ideas? Let CRITICAL be critical. If the other section are there just to be cautious. Then the name should represent that. While I like the BEGIN/END_OH_MY_GOD_IF_THIS_GETS_INTERRUPTED_YOU_DONT_WANT_TO_KNOW markers.. They are a little hard to work with. Possibly try demoting the NON_CRITICAL_SECTIONS to something like the following. BEGIN/END_CAUTION_SECTION, BEGIN/END_WATCH_SECTION
[HACKERS] Re: Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
At 1/10/2001 09:10 PM, you wrote: Thomas Swan [EMAIL PROTECTED] writes: Actually if it were possible to look at the values before expanding. You could reorder the expression so that it was always the case that B C, then your cost would only be one comparison plus the sequential scan. Uh ... what if B and C are not constants? Hmmm... I see your point. I was looking back through the sources and was thinking. I'd hate doing the work twice. Is there something in place to reorder or sort or compare results? Possibly expanding to something like a = max(b,c) and a = min(b,c)
[HACKERS] Re: BETWEEN [SYMMETRIC | ASYMMETRIC]
At 1/9/2001 10:29 PM, Tom Lane wrote: Thomas Swan [EMAIL PROTECTED] writes: Shouldn't be much of problem... where would I start to look... :) Well, the Right Way To Do It would be to invent a new expression node type that implements both kinds of BETWEEN. Right now, the parser expands A BETWEEN B AND C into "A = B AND A = C", which is perfectly correct according to the letter of the spec, but it implies evaluating the subexpression A twice, which sucks. Besides which, this doesn't Actually if it were possible to look at the values before expanding. You could reorder the expression so that it was always the case that B C, then your cost would only be one comparison plus the sequential scan. readily generalize to the SYMMETRIC case. I'd make a new expr node type with three subexpressions and a SYMMETRIC bool flag. If you chase down all the places where CaseExpr nodes are processed, and add a BetweenExpr case in parallel, you'll have it made. regards, tom lane
[HACKERS] Install Failure [7.1beta2 tarballs]
After configuring with ./configure --enable-multibyte --enable-unicode-conversion --enable-odbc --prefix=/usr --sysconfdir=/etc --localstatedir=/var make make install When trying to run initdb I get the following error: The program '/usr/bin/postgres' needed by initdb does not belong to PostgreSQL version 7.1beta2. Check your installation. I'll see if I can track down what happened a little later on... -- - Thomas Swan - Graduate Student - Computer Science - The University of Mississippi - - People can be categorized into two fundamental - groups, those that divide people into two groups - and those that don't.
[HACKERS] Re: problems with query
INSERT INTO ciudad (ciudad) VALUES (Villa Guillermina) Use single quotes instead of double quotes. -- - Thomas Swan - Graduate Student - Computer Science - The University of Mississippi - - People can be categorized into two fundamental - groups, those that divide people into two groups - and those that don't.
