Re: [GENERAL] PG secure for financial applications ...
Micah Yoder wrote: > I was also thinking a bit more broad than just finance. Could PG be used, for > example, as a multiplayer strategy game server where clients can directly > connect without another middleware daemon? Seems to me like it has > everything necessary, except for this problem. Each software serves certain purposes. Databases provide fast, reliable, consistent, and concurrent storage and retrieval of data. That's all they try to accomplish. If you want something else, you'll have to use different software. You are dreaming of "the one program that does everything". This animal only exists in marketing brochures. Fortunately. >> You cannot manage transactions inside functions. A function always >> runs inside a single transaction. > > Actually from the pl/pgsql manual it looks like you can raise an error and > have it abort the surrounding transaction. If that's true it should be > robust. It is - once the transaction is aborted due to an error it will remain in that state until you terminate the transaction (implicitly or explicitly). Actually, exception *handling* is implemented using savepoints, so you could say that one can do limited transaction management inside a function. But you cannot start or end a transaction inside a function. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to silence psql notices, warnings, etc.?
Kynn Jones wrote: > How does one silence NOTICE and WARNING messages in psql? > I've tried \set QUIET on, \set VERBOSITY terse, and even \o /dev/null, > but I still get them! Have you tried SET client_min_messages = ERROR; ? Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] restore_command %r option
So I had been using the "Time of latest checkpoint" value from pg_controldata, and freely deleting any archive files that were archived prior to this time, but it appears as though this is not safe since after restoring the ".backup" archive file at the start of recovery, I've accidentally deleted some (many) needed archive files. The "Time of latest checkpoint" seemed to move to a date just prior to the time the ".backup" file was archived, which was at the end of a 63-hour file-system-backup of the primary server. Hence most files archived during the time of the base backup were immediately deleted, and now recovery is hanging waiting for a file that was just removed. How else programmatically can I determine when it is safe to remove archive files? Do I need to look at the "Minimum recovery ending location"? Your help is much appreciated. Steve On Mon, Mar 10, 2008 at 12:23 PM, Steven Flatt <[EMAIL PROTECTED]> wrote: > Well, after some testing, it appears as though the %r option is not > supported on 8.2.4. > > It also looks as though pg_controldata may have the answer I want, but > what is the best programmatic way to determine the earliest archive file > that I need to keep? > > Thanks, > Steve > > On Mon, Mar 10, 2008 at 11:32 AM, Steven Flatt <[EMAIL PROTECTED]> > wrote: > > > Is the %r option of the restore_command available in PG 8.2.4? If not, > > is there any other way to know how many archive files need to be kept on the > > standby server when in recovery mode? > > > > Thanks, > > Steve > > > >
Re: [GENERAL] LOCK TABLE HELP
Sorry for delay in my answer. The problem is that with the lock
instructions my app remain in a freeze state. It resembling a MUTEX
deadlock.Anyway, as soon as possible I'll test yuor idea to use a
separate PQexec.
Thanks.Luca. - Original Message Da: "Alvaro
Herrera" <[EMAIL PROTECTED]> To: "Richard
Huxton" <[EMAIL PROTECTED]> Cc:
pgsql-general@postgresql.orgOggetto: Re: [GENERAL] LOCK TABLE HELP Data:
14/03/08 17:51 > [EMAIL PROTECTED] wrote: > My action
are: > > void *Execute(void *pParam) > { >
>
> > & nbsp; string
tableLock = "BEGIN WORK;"; > tableLock.append("
LOCK TABLE "); > tableLock.append(actorTable); >
tableLock.append(" IN ACCESS EXCLUSIVE MODE;"); > res =
PQexec(connection, tableLock.c_str()); > Well, all this doesn't
work (the connection is th e always the same in > all methods and
functions). Have I to Lock the table, perform some > operation on this
table, and unlock the table all in the same function > scope? What do
you mean it doesn't work? How exactly it fails? If anything, I'd
suggest to send the LOCK TABLE in a separate PQexec() call from BEGIN WORK.
-- Alvaro Herrera
http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom
Development, 24x7 support -- Sent via pgsql-general mailing list
(pgsql-general@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Email.it, the professional e-mail, gratis per te: http://www.email.it/f
Sponsor:
Scopri le tue passioni con Leonardo.it!
Clicca qui: http://adv.email.it/cgi-bin/foclick.cgi?mid=7615&d=20080317
[GENERAL] 8.3.0 upgrade
I just finished upgrading my production DB to 8.3.0. Everything went smoothly, but I thought of a few questions. After the upgrade, while restoring my backup to the new version, I got this error message: ERROR: role "postgres" already exists I assume this is nothing to be concerned about. But is there something I could have done to avoid this error? (I think I followed the upgrade instructions to the letter). Is there any scenario where the "postgres" role wouldn't exist? (should pg_dumpall exclude it?) Moving on... In step 6 of the upgrade instructions, it says: "Restore your previous pg_hba.conf and any postgresql.conf modifications." Perhaps this should also mention pg_ident.conf since I restored the two mentioned files, but still couldn't connect. The third completely escaped my mind until I ran a "diff" on the old & new data directories. Next, one of my apps failed because of a dependency on libpq.so.4. During previous upgrades, I remedied that by installing this package: compat-postgresql-libs-4-2PGDG.rhel4 But it seems under the 8.3.0 binary downloads, this package is no longer available. The only compat package is "compat-postgresql-libs-3" which of course includes only libpq.so.3 so I had to browse older releases to find the missing version which I thought seemed a little odd. Am I missing something? Finally, regarding the new "HOT" feature. The release notes say that benefits are realized "if no changes are made to indexed columns". If my updates include *all columns* (the SQL is generated dynamically) but the new value matches the old value for all *indexed* columns, do I still reap the benefits of HOT? Thanks! Adam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating
Adrian Klaver wrote: CREATE FUNCTION foo() RETURNS trigger AS $Body$ BEGIN IF NEW.colname != OLD.colname THEN ..."Do something"..; RETURN whatever; ELSE RETURN NEW: END IF; END; $Body$ LANGUAGE plpgsql; Beware that the "Do something" code path will not be taken when the column goes from NULL to non-NULL or non-NULL to NULL. In the general case where the column is nullable, better use "IS DISTINCT FROM" instead of inequality: IF NEW.colname IS DISTINCT FROM OLD.colname Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.0 upgrade
On Mon, Mar 17, 2008 at 12:43 PM, Adam Rich <[EMAIL PROTECTED]> wrote: > > Finally, regarding the new "HOT" feature. The release notes say that > benefits are realized "if no changes are made to indexed columns". > If my updates include *all columns* (the SQL is generated dynamically) > but the new value matches the old value for all *indexed* columns, > do I still reap the benefits of HOT? > Yes. At the execution time, a binary comparison of old and new index column values is performed and if the old and new value is same for all index columns, HOT update is feasible. So even if the UPDATE statement sets value to one of the index columns, HOT update is possible as long as the old and the new value is same. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] LOCK TABLE HELP
[EMAIL PROTECTED] wrote: > Sorry for delay in my answer. The problem is that with the lock > instructions my app remain in a freeze state. It resembling a MUTEX > deadlock.Anyway, as soon as possible I'll test yuor idea to use a > separate PQexec. Freeze state? Oh, you mean like somebody already holds the lock, so your LOCK TABLE is just waiting for the holder to release it ... Have a look around the pg_locks view. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot Install PostgreSQL on Windows 2000 Server
Were you ever able to install PostgreSQL on windows 2000? I am having similar problems on 2000 Pro and have absolutely zero luck with it. Dee - Looking for last minute shopping deals? Find them fast with Yahoo! Search.
Re: [GENERAL] Updating
On Monday 17 March 2008 4:54 am, Daniel Verite wrote: > Adrian Klaver wrote: > > CREATE FUNCTION foo() RETURNS trigger AS > > $Body$ > > BEGIN > > IF NEW.colname != OLD.colname THEN > > ..."Do something"..; > > RETURN whatever; > > ELSE > > RETURN NEW: > > END IF; > > END; > > $Body$ LANGUAGE plpgsql; > > Beware that the "Do something" code path will not be taken when the > column goes from NULL to non-NULL or non-NULL to NULL. > > In the general case where the column is nullable, better use "IS > DISTINCT FROM" instead of inequality: > IF NEW.colname IS DISTINCT FROM OLD.colname > > Best regards, > -- > Daniel > PostgreSQL-powered mail user agent and storage: > http://www.manitou-mail.org Thanks for the heads up. This is a case I usually only remember when I start testing the function. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] shared memory/max_locks_per_transaction error
On Fri, Mar 14, 2008 at 7:12 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kynn Jones" <[EMAIL PROTECTED]> writes: > > Initially I didn't know what our max_locks_per_transaction was (nor even > a > > typical value for it), but in light of the procedure's failure after > 3500 > > iterations, I figured that it was 3500 or so. In fact ours is only 64 > (the > > default), so I'm now thoroughly confused. > > The number of lock slots available system-wide is > max_locks_per_transaction times max_connections, and your procedure was > chewing them all. I suggest taking the hint's advice if you really need > to create 3500 tables in a single transaction. Actually, you'd better > do it if you want to have 3500 tables at all, because pg_dump will > certainly try to acquire AccessShare lock on all of them. OK, in light of this, I'll have to either change my strategy (and schema) significantly or greatly increase max_locks_per_transaction. I'm leaning towards the re-design option, primarily because I really don't really understand the consequences of cranking up max_locks_per_transaction. E.g. Why is its default value 2^6, instead of, say, 2^15? In fact, why is there a ceiling on the number of locks at all? I'm guessing that the fact that the default value is relatively small (i.e. a couple of orders of magnitude below the number of tables I have in mind) suggests that setting this value to a huge number would be a terrible idea. Is that so? Thanks! Kynn
Re: [GENERAL] How to silence psql notices, warnings, etc.?
Tom, Albe, Thanks for the client_min_messages pointer; it did the trick. On Sun, Mar 16, 2008 at 2:53 PM, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > If you start postgresql from the pg_ctl command line and it's set to > log to stdout, then continue to use that terminal for psql afterwards, > you will continue to get the warnings and notices because they're > coming out of the postmaster to the terminal you're on. I don't use pg_ctl much, but thanks for the heads-up. Kynn
Re: [GENERAL] postgre vs MySQL
On Mar 15, 2008, at 8:58 AM, Ron Mayer wrote: Greg Smith wrote: On Fri, 14 Mar 2008, Andrej Ricnik-Bay wrote: A silly question in this context: If we know of a company that does use PostgreSQL but doesn't list it anywhere ... can we take the liberty to publicise this somewhere anyway? I notice Oracle (and sleepycat before them) had a lot of fun pointing out when Microsoft uses BDB. http://www.oracle.com/technology/oramag/oracle/07-jan/o17opensource.html You'll find Oracle Berkeley DB "under the hood" in everything from Motorola cell phones, Microsoft/Groove's collaboration suite and it seems unlikely Microsoft gave them their blessings. Bad idea. There are companies who consider being listed as a user of a product a sort of recommendation of that technology, and accordingly Other reasons a company might get offended by this: * They might consider it a trade secret and a competitive advantage over competitors; and internally enjoy giggling when they see their competitors sign deals with expensive databases. * They might have a close business partnership with Microsoft or Oracle that could be strained if they support other databases. I suspect my employer would not like it announced for both reasons. they will get really annoyed...asked to be removed from the list of those using PostgreSQL. ... PostgreSQL inside, it's best not to publish the results unless you like to collect cease & desist letters. While I agree companies are likely to get annoyed - just like fast food companies do when you say how much trans-fats their products contain; I'm rather curious what such a cease&desist letter would say. Probably just a firm, but polite, request to quit it. I'd say that with a completely open piece of software like Postgres, i.e. where no commercial licensing is involved, the question is more ethical than legal. In fact, I can't think of a situation where "mind your own business" could be take more literally :) Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Catch-22
Hi: After running intoa problem last week where I cannot rename an existing DB because it's reported to not exist (I can attach to it and query OK, but rename gives... "database foo does not exist"), I've done a full dump of the DB and want to delete/recreate it with the backup. But I can't "dropdb" because "database foo does not exist". What's the course I should take at this point? I could "rm -r" the DB root (the piece after the "-D" in pg_ctl commands), but that may screw things up even worse. Again, I have a backup and want to delete the DB, but can't because the "dropdb" tool says the DB does not exist. v8.2.0 on Linux (and yes, I've requested an upgrade to 8.2.6) -dave
Re: [GENERAL] shared memory/max_locks_per_transaction error
Kynn Jones escribió: > I'm leaning towards the re-design option, primarily because I really don't > really understand the consequences of cranking up max_locks_per_transaction. > E.g. Why is its default value 2^6, instead of, say, 2^15? It's because it (partly) defines how much shared memory the server will use. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] shared memory/max_locks_per_transaction error
"Kynn Jones" <[EMAIL PROTECTED]> writes: > I'm leaning towards the re-design option, primarily because I really don't > really understand the consequences of cranking up max_locks_per_transaction. > E.g. Why is its default value 2^6, instead of, say, 2^15? In fact, why is > there a ceiling on the number of locks at all? Because the size of the lock table in shared memory has to be set at postmaster start. There are people running DBs with a couple hundred thousand tables, but I don't know what sorts of performance problems they face when they try to run pg_dump. I think most SQL experts would suggest a redesign: if you have lots of essentially identical tables the standard advice is to fold them all into one table with one more key column. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Get info about indexes
Hello, there. I need a method of extracting information about indexes of any table from information_schema. Have you any suggestions? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql performance tuning tools
hi all, I want this mail to be continued about summary of performance tuning tools... or other postgres related tools.. I ll start with saying there is a tool SCHEMASPY ( i got to know about this from another group ), this will draw ER diagram and gives interesting informations about our postgres database.. What are all the other opensource tools available like this for seeing informations about our postgres database... and tools for finetuning our postgres database Please join with me and summarize the names and usage of the tools Use SchemaSpy a very easily installable and usable tool...
[GENERAL] Get index information from information_schema?
Hello, there. I need a method of extracting information about indexes of any table from information_schema. Have you any suggestions? Thank you. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get info about indexes
AlannY <[EMAIL PROTECTED]> writes: > I need a method of extracting information about indexes of any table > from information_schema. There is nothing about indexes in the information_schema (this is not a bug, it's an intentional decision by the standards committee). If you want to know about indexes you'll need to look directly at the system catalogs. You can find out about unique/primary-key constraints from information_schema, but that's not quite the same ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] beginner: what permissions required to install on windows 2000+
Anybody else have any other suggestions? Please CC me on any responses, so I can respond promptly. Details: == The error occurs during the installation of version postgresql-8.3.msi (8.3.0-1). - I was logged in as a user with admin rights. Firewall s/w was disabled. - In case that was not sufficient, I also tried the installation while logged in as the Adminstrator and after completely uninstalling firewall s/w. The same errors occur. I have tried 2 approaches: 1) I accepted all of the default settings and allowed the installation program to create the windows-user postgres. An error occurs when the program reaches the status point "Starting services": "The application failed to initialize properly (0xc022). Click on OK to terminate the application." I have included a snippet from the installation log and a message from the windows event log below. 2) I also tried creating the local user acccount before the installation. - I deleted the previous windows account and created a new one - I granted the account "Log on Locally" and "Log on as service rights". - When I try and run the installation again, I receive the error message "Invalid username specified: A required privilege is not held by the client" start: snippet from error log == Action 0:17:48: StartServices. Starting services MSI (s) (BC:98) [00:17:48:875]: Executing op: ProgressTotal(Total=2,Type=1,ByteEquivalent=130) MSI (s) (BC:98) [00:17:48:875]: Executing op: ServiceControl(,Name=pgsql-8.3,Action="">StartServices: Service: PostgreSQL Database Server 8.3 MSI (c) (E4:E0) [00:20:45:062]: Note: 1: 2205 2: 3: Error MSI (c) (E4:E0) [00:20:45:062]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2888 DEBUG: Error 2888: Executing the TextStyle view failed The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2888. The arguments are: TextStyle, , Action 0:20:46: Cancel. Dialog created MSI (c) (E4:E0) [00:21:01:421]: Note: 1: 2205 2: 3: Error MSI (c) (E4:E0) [00:21:01:421]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2888 DEBUG: Error 2888: Executing the TextStyle view failed The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2888. The arguments are: TextStyle, , MSI (s) (BC:98) [00:21:20:843]: Note: 1: 2205 2: 3: Error MSI (s) (BC:98) [00:21:20:843]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1920 MSI (s) (BC:98) [00:21:20:875]: Note: 1: 2205 2: 3: Error MSI (s) (BC:98) [00:21:20:875]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709 MSI (s) (BC:98) [00:21:20:875]: Product: PostgreSQL 8.3 -- Error 1920. Service 'PostgreSQL Database Server 8.3' (pgsql-8.3) failed to start. Verify that you have sufficient privileges to start system services. MSI (s) (BC:98) [00:21:20:890]: Note: 1: 2205 2: 3: Error MSI (s) (BC:98) [00:21:20:890]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1602 MSI (c) (E4:E0) [00:21:20:890]: Note: 1: 2205 2: 3: Error MSI (c) (E4:E0) [00:21:20:890]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2835 DEBUG: Error 2835: The control ErrorIcon was not found on dialog ErrorDlg The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2835. The arguments are: ErrorIcon, ErrorDlg, MSI (c) (E4:E0) [00:21:20:890]: Note: 1: 2205 2: 3: Error MSI (c) (E4:E0) [00:21:20:890]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2888 DEBUG: Error 2888: Executing the TextStyle view failed The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2888. The arguments are: TextStyle, , Are you sure you want to cancel? end: snippet from error log == start: event log message == Event Type:Error Event Source:Service Control Manager Event Category:None Event ID:7005 Date:3/4/2008 Time:12:20:50 AM User:N/A Computer:DEV Description: The LoadUserProfile call failed with the following error: Access is denied. end: event log message == - Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.
Re: [GENERAL] shared memory/max_locks_per_transaction error
On Mar 17, 2008, at 9:55 AM, Tom Lane wrote: "Kynn Jones" <[EMAIL PROTECTED]> writes: I'm leaning towards the re-design option, primarily because I really don't really understand the consequences of cranking up max_locks_per_transaction. E.g. Why is its default value 2^6, instead of, say, 2^15? In fact, why is there a ceiling on the number of locks at all? Because the size of the lock table in shared memory has to be set at postmaster start. There are people running DBs with a couple hundred thousand tables, but I don't know what sorts of performance problems they face when they try to run pg_dump. I think most SQL experts would suggest a redesign: if you have lots of essentially identical tables the standard advice is to fold them all into one table with one more key column. That's me! Our dumps currently take about 36 hours but what's more alarming is that vanilla restore takes about 4 days. And, yes, a redesign is currently in the works :) However, for Kynn's case, I doubt he'll have too much trouble with 35000 tables as long as that number stays fairly static and his design doesn't rely on that number growing, which is what we currently have. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using PL/R for predictive analysis of data.
Hi Sam, Thankyou for the suggestions. They make perfect sense to me. I appreciate your time and input. The lack of optimiser usage was something that I had not considered, and I thank you for making me aware of it. Cheers The Frog -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get info about indexes
am Mon, dem 17.03.2008, um 17:07:20 +0300 mailte AlannY folgendes: > Hello, there. > > I need a method of extracting information about indexes of any table > from information_schema. > > Have you any suggestions? Yes, http://www.alberton.info/postgresql_meta_info.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
"Gauthier, Dave" <[EMAIL PROTECTED]> writes: > After running intoa problem last week where I cannot rename an existing > DB because it's reported to not exist (I can attach to it and query OK, > but rename gives... "database foo does not exist"), Would you show the results of select oid,ctid,xmin,xmax,datname from pg_database as well as the contents of $PGDATA/global/pg_database and a directory listing of $PGDATA/base? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] shared memory/max_locks_per_transaction error
Tom, Alvaro: Thank you much for the clarification. It's "back to the drawing board" for me! Kynn On Mon, Mar 17, 2008 at 10:55 AM, Tom Lane <[EMAIL PROTECTED]> wrote: > "Kynn Jones" <[EMAIL PROTECTED]> writes: > > I'm leaning towards the re-design option, primarily because I really > don't > > really understand the consequences of cranking up > max_locks_per_transaction. > > E.g. Why is its default value 2^6, instead of, say, 2^15? In fact, why > is > > there a ceiling on the number of locks at all? > > Because the size of the lock table in shared memory has to be set at > postmaster start. > > There are people running DBs with a couple hundred thousand tables, > but I don't know what sorts of performance problems they face when > they try to run pg_dump. I think most SQL experts would suggest > a redesign: if you have lots of essentially identical tables the > standard advice is to fold them all into one table with one more > key column. > >regards, tom lane >
Re: [GENERAL] postgre vs MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 17 Mar 2008 09:26:35 -0500 Erik Jones <[EMAIL PROTECTED]> wrote: > > While I agree companies are likely to get annoyed - just like fast > > food companies do when you say how much trans-fats their products > > contain; I'm rather curious what such a cease&desist letter would > > say. > > Probably just a firm, but polite, request to quit it. I'd say that > with a completely open piece of software like Postgres, i.e. where > no commercial licensing is involved, the question is more ethical > than legal. In fact, I can't think of a situation where "mind your > own business" could be take more literally :) Sometimes they may also claim trademark or trade secret issues. Sincerely, Joshua D. Drkae - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH3pQoATb/zqfZUUQRAt8iAJ9yLSuV4LQXeUl238VOk6k9VLwdYACgqdkW bGvcvjIUVMj0VZetffDhYhY= =91uz -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
stdb=# select oid,ctid,xmin,xmax,datname from pg_database stdb-# ; oid | ctid | xmin | xmax | datname ++-+--+-- 10819 | (0,1) | 592 |0 | postgres 1 | (0,6) | 585 |0 | template1 10818 | (0,7) | 586 |0 | template0 823888 | (0,13) | 761678 |0 | cells_dev 19810 | (0,49) | 497579 |0 | stdb2 597974 | (3,2) | 2346578 |0 | stdb 19882 | (3,3) | 2346580 |0 | stdb_standby 16384 | (3,4) | 2364457 |0 | cells (8 rows) mmdcc228> dropdb stdb ERROR: database "stdb" does not exist STATEMENT: DROP DATABASE stdb; dropdb: database removal failed: ERROR: database "stdb" does not exist mmdcc228> -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2008 11:33 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Catch-22 "Gauthier, Dave" <[EMAIL PROTECTED]> writes: > After running intoa problem last week where I cannot rename an existing > DB because it's reported to not exist (I can attach to it and query OK, > but rename gives... "database foo does not exist"), Would you show the results of select oid,ctid,xmin,xmax,datname from pg_database as well as the contents of $PGDATA/global/pg_database and a directory listing of $PGDATA/base? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
"Gauthier, Dave" <[EMAIL PROTECTED]> writes: After running intoa problem last week where I cannot rename an existing DB because it's reported to not exist (I can attach to it and query OK, but rename gives... "database foo does not exist"), Would you show the results of select oid,ctid,xmin,xmax,datname from pg_database as well as the contents of $PGDATA/global/pg_database and a directory listing of $PGDATA/base? regards, tom lane On Mar 17, 2008, at 10:45 AM, Gauthier, Dave wrote: stdb=# select oid,ctid,xmin,xmax,datname from pg_database stdb-# ; oid | ctid | xmin | xmax | datname ++-+--+-- 10819 | (0,1) | 592 |0 | postgres 1 | (0,6) | 585 |0 | template1 10818 | (0,7) | 586 |0 | template0 823888 | (0,13) | 761678 |0 | cells_dev 19810 | (0,49) | 497579 |0 | stdb2 597974 | (3,2) | 2346578 |0 | stdb 19882 | (3,3) | 2346580 |0 | stdb_standby 16384 | (3,4) | 2364457 |0 | cells (8 rows) mmdcc228> dropdb stdb ERROR: database "stdb" does not exist STATEMENT: DROP DATABASE stdb; dropdb: database removal failed: ERROR: database "stdb" does not exist mmdcc228> You left out the directory listings Tom asked for. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
Woops, sorry mmdcc228> more global/pg_database "postgres" 10819 1663 524 "template1" 1 1663 524 "template0" 10818 1663 524 "cells_dev" 823888 1663 524 "stdb2" 19810 1663 524 "stdb" 597974 1663 524 "stdb_standby" 19882 1663 524 "cells" 16384 1663 524 mmdcc228> ls base/ 1 10818 10819 16384 16460 19810 19882 597974 823888 -Original Message- From: Erik Jones [mailto:[EMAIL PROTECTED] Sent: Monday, March 17, 2008 12:20 PM To: Gauthier, Dave Cc: Tom Lane; pgsql-general@postgresql.org Subject: Re: [GENERAL] Catch-22 > "Gauthier, Dave" <[EMAIL PROTECTED]> writes: >> After running intoa problem last week where I cannot rename an > existing >> DB because it's reported to not exist (I can attach to it and query > OK, >> but rename gives... "database foo does not exist"), > > Would you show the results of > > select oid,ctid,xmin,xmax,datname from pg_database > > as well as the contents of $PGDATA/global/pg_database and a directory > listing of $PGDATA/base? > > regards, tom lane On Mar 17, 2008, at 10:45 AM, Gauthier, Dave wrote: > stdb=# select oid,ctid,xmin,xmax,datname from pg_database > stdb-# ; > oid | ctid | xmin | xmax | datname > ++-+--+-- > 10819 | (0,1) | 592 |0 | postgres > 1 | (0,6) | 585 |0 | template1 > 10818 | (0,7) | 586 |0 | template0 > 823888 | (0,13) | 761678 |0 | cells_dev > 19810 | (0,49) | 497579 |0 | stdb2 > 597974 | (3,2) | 2346578 |0 | stdb > 19882 | (3,3) | 2346580 |0 | stdb_standby > 16384 | (3,4) | 2364457 |0 | cells > (8 rows) > > mmdcc228> dropdb stdb > ERROR: database "stdb" does not exist > STATEMENT: DROP DATABASE stdb; > > dropdb: database removal failed: ERROR: database "stdb" does not > exist > mmdcc228> You left out the directory listings Tom asked for. Erik Jones DBA | Emma(r) [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
On Mon, Mar 17, 2008 at 08:45:59AM -0700, Gauthier, Dave wrote: > stdb=# select oid,ctid,xmin,xmax,datname from pg_database > stdb-# ; > oid | ctid | xmin | xmax | datname > ++-+--+-- > 10819 | (0,1) | 592 |0 | postgres > 1 | (0,6) | 585 |0 | template1 > 10818 | (0,7) | 586 |0 | template0 > 823888 | (0,13) | 761678 |0 | cells_dev > 19810 | (0,49) | 497579 |0 | stdb2 > 597974 | (3,2) | 2346578 |0 | stdb > 19882 | (3,3) | 2346580 |0 | stdb_standby > 16384 | (3,4) | 2364457 |0 | cells > (8 rows) > > mmdcc228> dropdb stdb > ERROR: database "stdb" does not exist > STATEMENT: DROP DATABASE stdb; > dropdb: database removal failed: ERROR: database "stdb" does not exist > mmdcc228> try this: while connected to stdb (in psql): \connect template1 drop database stdb; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Catch-22
"Gauthier, Dave" <[EMAIL PROTECTED]> writes: > Woops, sorry > mmdcc228> more global/pg_database > "postgres" 10819 1663 524 > "template1" 1 1663 524 > "template0" 10818 1663 524 > "cells_dev" 823888 1663 524 > "stdb2" 19810 1663 524 > "stdb" 597974 1663 524 > "stdb_standby" 19882 1663 524 > "cells" 16384 1663 524 > mmdcc228> ls base/ > 1 10818 10819 16384 16460 19810 19882 597974 823888 Huh. That matches up with the OID shown in pg_database, so it's sure not clear what the problem is. Could you grab a copy of the appropriate version of pg_filedump from http://sources.redhat.com/rhdb/ and dump out pg_database with it? The best results would be from pg_filedump -i -f $PGDATA/global/1262 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] identify database process given client process
Hi, I have to find a Postgres database process pid (or other identification) for a given client process pid. Or client processes for a database process. How are they connected? I was suggested maybe netstat could give me the answer and I think those are two pf_unix processes. But maybe there are some PostgreSQL functions that do this? How should I approach this topic? Thanks in advance, -- Agata Krawcewicz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [GENERAL] large object import
> Původní zpráva > Od: Albe Laurenz <[EMAIL PROTECTED]> > Předmět: Re: [GENERAL] large object import > Datum: 10.3.2008 08:44:30 > > > [EMAIL PROTECTED] wrote: > > I am having a stored function in plperlU which is called from > > php script then. It select data from a table, export them to > > a file and zips the file. The problem is that it should store > > this file into temporary table. Then it should return some > > identificator to php, so that the user can download it via > > php. Problem is that postgreSQL doesn't supports server-side > > large object operations for non superuser roles. > > PostgreSQL supports server-side large object operations for non-superusers. > > Functions that access the file system are restricted to superusers. > > > Can someone please give me a suggestion how can I solve this > > problem. I mean if I can import the file some other way or if > > there are some other usual procedures how to do this? > > You can create a function with SECURITY DEFINER that is owned > by a superuser. That way you can make certain restricted functionality > available to regular users. You should be careful and as restrictive > as possible when writing such functions. > > Yours, > Laurenz Albe > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > > Hi, thank you very much, this helped a lot. It works fine. Lukas Houf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fragments in tsearch2 headline
Teodor, Oleg, do we want this?
http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php
---
Sushant Sinha wrote:
> I wrote a headline generation function for my app and I have attached
> the patch (against the cvs head). It generates multiple contexts in
> which the query appears. Essentially, it uses the cover function to
> generate all covers, chooses smallest covers and stretches each
> selected cover according to the chosen parameters. I think ideally
> changes should be made to prsd_headline function but I couldn't
> understand that segment of code well.
>
> The sql interface is
>
> headline_with_fragments(text parser, tsvector docvector, text doc,
> tsquery queryin, int4 maxcoverSize, int4 mincoverSize, int4 maxWords)
> RETURNS text
>
> This will generate headline that contain maxWords and each cover
> stretched to maxcoverSize. It will not add any fragment with less than
> mincoverSize.
> I am running my app with maxcoverSize = 20, mincoverSize = 5, maxWords = 40.
> So it shows roughly two fragments per query.
>
> If Teoder or Oleg want to add this to main branch, I will be happy to
> clean it up and test it better.
>
> -Sushant.
>
>
>
>
> On Oct 31, 2007 6:26 PM, Catalin Marinas <[EMAIL PROTECTED]> wrote:
> > On 30/10/2007, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
> > > ok, then you have to formalize many things - how long should be excerpts,
> > > how much excerpts to show, etc. In tsearch2 we have get_covers() function,
> > > which produces all excerpts like:
> > >
> > > =# select get_covers(to_tsvector('1 2 3 4 5 3 4 abc x y z 2 3'),
> > > '2&3'::tsquery);
> > > get_covers
> > >
> > > 1 {1 2 3 }1 4 5 {2 3 4 abc x y z {3 2 }2 3 }3
> > > (1 row)
> >
> > This function generates the lexemes, so cannot be used directly, but
> > it is probably a good starting point.
> >
> > > Once you formalize your requirements, you can look on it and adapt to your
> > > needs (and share with people). I think it could be nice contrib module.
> >
> > It seems that Sushant already wants to implement this function. He
> > would probably be faster than me :-) (I'm relatively new to db stuff).
> > Since I mainly rely on whatever a web hosting company provides, I'll
> > probably stick with a Python implementation outside the SQL query.
> >
> > Thanks for your answers.
> >
> > --
> > Catalin
> >
> > ---(end of broadcast)---
> >
> > TIP 5: don't forget to increase your free space map settings
> >
[ Attachment, skipping... ]
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating
I am attempting to use the following code but I get - "ERROR: NEW used in query that is not in a rule". This implies that I create a rule for NEW and OLD (which I haven't needed before). Could someone point me to the proper synatx for such a rule? BTW I noticed that Adrian used != . Is this symbol the same as <> ? Bob - Original Message - From: "Adrian Klaver" <[EMAIL PROTECTED]> To: Cc: "Daniel Verite" <[EMAIL PROTECTED]>; "Bob Pawley" <[EMAIL PROTECTED]> Sent: Monday, March 17, 2008 7:16 AM Subject: Re: [GENERAL] Updating On Monday 17 March 2008 4:54 am, Daniel Verite wrote: Adrian Klaver wrote: > CREATE FUNCTION foo() RETURNS trigger AS > $Body$ > BEGIN > IF NEW.colname != OLD.colname THEN > ..."Do something"..; > RETURN whatever; > ELSE > RETURN NEW: > END IF; > END; > $Body$ LANGUAGE plpgsql; Beware that the "Do something" code path will not be taken when the column goes from NULL to non-NULL or non-NULL to NULL. In the general case where the column is nullable, better use "IS DISTINCT FROM" instead of inequality: IF NEW.colname IS DISTINCT FROM OLD.colname Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org Thanks for the heads up. This is a case I usually only remember when I start testing the function. -- Adrian Klaver [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] identify database process given client process
On Mon, Mar 17, 2008 at 6:58 AM, hogcia <[EMAIL PROTECTED]> wrote: > Hi, > I have to find a Postgres database process pid (or other > identification) for a given client process pid. Or client processes > for a database process. How are they connected? I was suggested maybe > netstat could give me the answer and I think those are two pf_unix > processes. But maybe there are some PostgreSQL functions that do this? > How should I approach this topic? > Thanks in advance, > Try select pg_stat_activity; Joey
Re: [GENERAL] Fragments in tsearch2 headline
Teodor, Oleg, do we want this? http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php I suppose, we want it. But there are a questions/issues: - Is it needed to introduce new function? may be it will be better to add option to existing headline function. I'd like to keep current layout: ts_headline provides some common interface to headline generation. Finding and marking fragments is deal of parser's headline method and generation of exact pieces of text is made by ts_headline. - Covers may be overlapped. So, overlapped fragments will be looked odd. In any case, the patch was developed for contrib version of tsearch. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with async notifications of table updates
Hi, I am trying to set up a PostGreSQL database to send asynchronous notifications when certain inserts or updates are performed on the tables. The idea is I want to have publish / subscirbe model with the database in the centre as the information hub. An application will insert a record into a table and then a notification message is sent to all registered subscribers telling them that record number X has been added to table Y. Each subscriber can then chose to retrieve the record or ignore the notification. This should be near real-time (< 0.5 sec from insert / update to notification reception). To do the notification I have ported the Spread (www.spread.org) interface for MySQL to Postgres (actually only the send_mesg() part of it). I then have a trigger function which calls the send_mesg() function on an insert or update to the table. All good -- except that when another application receives the message and queries the table the record that caused the notification is not there. It would appear that it only becomes available AFTER the trigger function that fired the message returns. So the question is - how can I get my trigger function to flush the row so that I can be sure it is available for use prior to the return of the trigger function? Alternatively - how can I tell the trigger function to only execute the send_mesg() after the row is available? I have not used the LISTEN / NOTIFY interface because: a) It does not easily support sending any information (yes I know you can set up another table and insert a pointer to the record however that is far from ideal) b) There is no guarantee on message delivery. Spread allows you to ensure that message is delivered. Thanks in advance - Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email.
Re: [GENERAL] Updating
Bob Pawley wrote: I am attempting to use the following code but I get - "ERROR: NEW used in query that is not in a rule". This implies that I create a rule for NEW and OLD (which I haven't needed before). No, but are you sure you're using these keywords in the context of a plpgsql function? Can you post the entire CREATE statement that fails? BTW I noticed that Adrian used != . Is this symbol the same as <> ? Yes it's the same. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
Tyler, Mark escribió: Hi, I am trying to set up a PostGreSQL database to send asynchronous notifications when certain inserts or updates are performed on the tables. The idea is I want to have publish / subscirbe model with the database in the centre as the information hub. An application will insert a record into a table and then a notification message is sent to all registered subscribers telling them that record number X has been added to table Y. Each subscriber can then chose to retrieve the record or ignore the notification. This should be near real-time (< 0.5 sec from insert / update to notification reception). To do the notification I have ported the Spread (_www.spread.org_ ) interface for MySQL to Postgres (actually only the send_mesg() part of it). I then have a trigger function which calls the send_mesg() function on an insert or update to the table. All good -- except that when another application receives the message and queries the table the record that caused the notification is not there. It would appear that it only becomes available AFTER the trigger function that fired the message returns. So the question is - how can I get my trigger function to flush the row so that I can be sure it is available for use prior to the return of the trigger function? Alternatively - how can I tell the trigger function to only execute the send_mesg() after the row is available? I am almost sure you've defined a BEFORE trigger and you need and AFTER trigger, so it's fired after commiting. smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Updating
Following is the code that gives me the error. CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$ Begin If NEW.p_id.association.monitoring_fluid is distinct from Old.p_id.association.monitoring_fluid Then INSERT INTO p_id.devices (device_number) (Select mon_function from p_id.association, p_id.devices Where (p_id.association.mon_function <> p_id.devices.device_number and (p_id.association.monitoring_fluid <> p_id.devices.fluid_id or p_id.association.monitoring_fluid <> p_id.devices.pipe_id)) and p_id.association.monitor is null); RETURN NULL; END; $$ LANGUAGE plpgsql; create trigger monitorinstall before update on p_id.association for each row execute procedure monitor_install(); - Original Message - From: "Daniel Verite" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: "Adrian Klaver" <[EMAIL PROTECTED]>; Sent: Monday, March 17, 2008 2:42 PM Subject: Re: [GENERAL] Updating Bob Pawley wrote: I am attempting to use the following code but I get - "ERROR: NEW used in query that is not in a rule". This implies that I create a rule for NEW and OLD (which I haven't needed before). No, but are you sure you're using these keywords in the context of a plpgsql function? Can you post the entire CREATE statement that fails? BTW I noticed that Adrian used != . Is this symbol the same as <> ? Yes it's the same. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Moving pgstat.stat and pgstat.tmp
Added to TODO: * Reduce file system activity overhead of statistics file pgstat.stat http://archives.postgresql.org/pgsql-general/2007-12/msg00106.php --- Erik Jones wrote: > Hi, I'm currently doctoring a situation wherein we've got table > inheritance scheme that over the years that has ballooned like only > in your nightmares (think well over 100K tables + indexes on those). > The obvious solution is to re-design the schema with a better > partitioning scheme in mind (see another msg from me later today on > that) but that's a big project that's just getting underway and an > immediate concern is the I/O on out data partition due in large part > to the stats file(s) getting hammered. We can verify this by looking > at our write volume 45+ Mbits/s and watching it drop to well below 10 > on average when we disable stat_row_level as well as watching the > insane amounts of writes to pgstat.tmp when running the rwsnoop > dtrace script. > > So, for the interim we're looking to move where the stats files are > written to. I've made the changes to the file paths for pgstat.stat > and pgstat.tmp in src/backend/postmaster/pgstat.c, recompiled and > verified that everything seems to be working ok on our test machine. > However, seeing as how I'm not all that familiar with the code base, > I'm asking here: is that all I need to do? Is there anything I've > missed? > > Erik Jones > > Software Developer | Emma? > [EMAIL PROTECTED] > 800.595.4401 or 615.292.5888 > 615.292.0777 (fax) > > Emma helps organizations everywhere communicate & market in style. > Visit us online at http://www.myemma.com > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] storage size of "bit" data type..
Added to TODO:
* Reduce BIT data type overhead using short varlena headers
http://archives.postgresql.org/pgsql-general/2007-12/msg00273.php
---
Decibel! wrote:
> On Dec 5, 2007, at 7:23 PM, Michael Glaesemann wrote:
> > On Dec 5, 2007, at 14:19 , Alex Mayrhofer wrote:
> >> i'm trying to find out the storage size for bit(n) data. My
> >> initial assumption would be that for any 8 bits, one byte of
> >> storage is required.
> >
> > select pg_column_size(B'1') as "1bit",
> >pg_column_size(B'') as "4bits",
> >pg_column_size(B'') as "1byte",
> >pg_column_size(B'') as "12bits",
> >pg_column_size(B'') as "2bytes",
> >pg_column_size(B'1') as "17bits",
> >pg_column_size(B'') as "3bytes";
> > 1bit | 4bits | 1byte | 12bits | 2bytes | 17bits | 3bytes
> > --+---+---++++
> > 9 | 9 | 9 | 10 | 10 | 11 | 11
> > (1 row)
> >
> > Looks like there's 8 bytes of overhead as well, probably because a
> > bit string is a varlena type.
>
> Wow, that's screwed up... that's a lot more than varlena overhead:
>
> select pg_column_size('a'::text), pg_column_size(1::numeric),
> pg_column_size(3111234::numeric);
> pg_column_size | pg_column_size | pg_column_size
> ++
>5 | 10 | 12
>
> Apparently it's something related to numeric.
> --
> Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED]
> Give your computer some brain candy! www.distributed.net Team #1828
>
>
--
Bruce Momjian <[EMAIL PROTECTED]>http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is autovacuum on?
Hi all, I've just upgraded to 8.3 and am looking at using autovacuum. We have a long running application with high update frequency that periodically issues vacuum commands itself. I'd like to be able to add code to the app like: if pg.autovacuum == "on": self.routine_vacuuming = False else: self.routine_vacuuming = True so that we can avoid manually issuing vacuum commands at sites where Postgresql is running autovacuum. But so far I haven't been able to find a way for a non-privileged user to query the autovacuum status, is this possible? Also, the routine-vacuuming section of the manual states that the purpose of the autovacuum daemon is to periodically issue VACUUM and ANALYZE commands - am I correct in thinking this implies that it will not issue VACUUM FULL commands? Cheers, -Blair -- In science one tries to tell people, in such a way as to be understood by everyone, something that no one ever knew before. But in poetry, it's the exact opposite. - Paul Dirac -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Updating
Bob Pawley <[EMAIL PROTECTED]> writes: > If NEW.p_id.association.monitoring_fluid is distinct from > Old.p_id.association.monitoring_fluid Then Surely this should just be if new.monitoring_fluid is distinct from old.monitoring_fluid then Also, I think you forgot an "end if" and a "return new" at the end. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
Rodrigo Gonzalez wrote:
> I am almost sure you've defined a BEFORE trigger and
> you need and AFTER trigger, so it's fired after commiting.
No - I am definitely using an AFTER trigger. Following is a simplified
version of what I am trying to do.
/* messages - log messages */
CREATE TABLE messages
(id SERIAL PRIMARY KEY,
timeTIMESTAMP DEFAULT
CURRENT_TIMESTAMP,
severity_level INTEGER NOT NULL,
severityTEXT NOT NULL, /*
ENUM('Info','Warning','Critical') */
facilityCHAR(10) NOT NULL,
msg TEXT NOT NULL);
CREATE OR REPLACE FUNCTION message_alert() RETURNS TRIGGER AS
$message_alert$
BEGIN
PERFORM send_mesg('notify_channel', 'DB:Log:' || NEW.id
|| ':');
RETURN NULL;
END;
$message_alert$ LANGUAGE plpgsql;
CREATE TRIGGER message_alert AFTER INSERT ON messages
FOR EACH ROW
EXECUTE PROCEDURE message_alert();
I have a Python program which is waiting on the message being sent via
send_mesg(). The message is received correctly but if I do an immediate
"SELECT msg FROM messages WHERE id=;" then it returns a NULL set. If I put a small sleep
between receiving the message and doing the select then I get the data.
What I want to do is to guarantee that the row is available for
selection prior to sending the message.
Mark
IMPORTANT: This email remains the property of the Australian Defence
Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT
1914. If you have received this email in error, you are requested to contact
the sender and delete the email.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using PL/R for predictive analysis of data.
On Mon, Mar 17, 2008 at 2:27 AM, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hi Sam, > > Thankyou for the suggestions. They make perfect sense to me. I > appreciate your time and input. The lack of optimiser usage was > something that I had not considered, and I thank you for making me > aware of it. > > Cheers > > The Frog On the subject of the planner and optimizer, as of 8.3 (I think it's new to 8.3...) you can tell the planner somewhat about how it might expect your function to behave. See http://www.postgresql.org/docs/8.3/interactive/sql-createfunction.html - Josh/eggyknap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 8.3.0 upgrade
Thanks to Pavan for the answer regarding HOT. Does anybody have an answer regarding the postgres role or compat lib ? > > * From: "Adam Rich" > * To: > * Subject: 8.3.0 upgrade > * Date: Mon, 17 Mar 2008 02:13:55 -0500 > > > I just finished upgrading my production DB to 8.3.0. Everything went > smoothly, but I thought of a few questions. > > After the upgrade, while restoring my backup to the new version, > I got this error message: > > ERROR: role "postgres" already exists > > I assume this is nothing to be concerned about. But is there something > I could have done to avoid this error? (I think I followed the upgrade > instructions to the letter). Is there any scenario where the "postgres" > role wouldn't exist? (should pg_dumpall exclude it?) > > Moving on... In step 6 of the upgrade instructions, it says: > > "Restore your previous pg_hba.conf and any postgresql.conf modifications." > > Perhaps this should also mention pg_ident.conf since I restored the > two mentioned files, but still couldn't connect. The third completely > escaped my mind until I ran a "diff" on the old & new data directories. > > Next, one of my apps failed because of a dependency on libpq.so.4. > During previous upgrades, I remedied that by installing this package: > > compat-postgresql-libs-4-2PGDG.rhel4 > > But it seems under the 8.3.0 binary downloads, this package is no longer > available. The only compat package is "compat-postgresql-libs-3" > which of course includes only libpq.so.3 so I had to browse older > releases to find the missing version which I thought seemed a little odd. > Am I missing something? > > Finally, regarding the new "HOT" feature. The release notes say that > benefits are realized "if no changes are made to indexed columns". > If my updates include *all columns* (the SQL is generated dynamically) > but the new value matches the old value for all *indexed* columns, > do I still reap the benefits of HOT? > > Thanks! > > Adam > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
"Tyler, Mark" <[EMAIL PROTECTED]> writes: > What I want to do is to guarantee that the row is available for > selection prior to sending the message. You cannot do that with an AFTER trigger, because whatever it does necessarily happens before your transaction commits. I suggest rethinking your dislike of NOTIFY. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
Tom Lane wrote: >"Tyler, Mark" <[EMAIL PROTECTED]> writes: >> What I want to do is to guarantee that the row is available for >> selection prior to sending the message. > > You cannot do that with an AFTER trigger, because whatever it > does necessarily happens before your transaction commits. I somehow suspected that this was the answer. It would be nice to have some sort of FINALLY style of clause for the trigger which was able to be initiated after the transaction was committed. Of course there would be very large restrictions on what sort of things could be done in such a clause. Clearly NOTIFY itself works around this very problem. I have not looked at the code but I suspect the NOTIFY command sets a flag that tells the server to fire the notification as soon as the transaction commits - thus the command can be inside the trigger context but have an effect after the trigger completes. > I suggest rethinking your dislike of NOTIFY. I have thought very hard about using NOTIFY for this but it has two large problems (from my point of view). The first is that it forces me to put far more smarts and state into the subscriber applications. This is because I cannot pass any information with the NOTIFY apart from the fact that "something happened". Due to this restriction my subscriber apps would have to go and look up some secondary table to get sufficient information to construct the real query. That is just plain ugly in my view. Secondly, the lack of any delivery guarantee means my subscriber applications may miss event notifications. This is a very bad thing for my particular application. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
"Tyler, Mark" <[EMAIL PROTECTED]> writes: > Secondly, the lack of any delivery guarantee means my subscriber > applications may miss event notifications. This is a very bad thing for > my particular application. What makes you think NOTIFY doesn't guarantee delivery? If the transaction commits then the notify update has happened. Perhaps more to the point, have you reflected on the fact that your technique has the opposite problem? Once you've given the message to Spread, it'll deliver it whether your transaction subsequently commits or not. If you're really intent on re-inventing NOTIFY, you could use the same synchronization trick it does: take out a lock on some otherwise unused table just before sending the message, and have recipients lock the same table on receipt of the message, before they go looking for any effects in the database. The NOTIFY-side lock is held past commit of its transaction, so once recipients can lock the table they must be able to see the results of the NOTIFY's transaction. This is not insanely great from a concurrency standpoint of course, but as long as you keep the lock hold durations short it's workable. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Is autovacuum on?
2008/3/18, Blair Bethwaite <[EMAIL PROTECTED]>: > Hi all, > > I've just upgraded to 8.3 and am looking at using autovacuum. We have > a long running application with high update frequency that > periodically issues vacuum commands itself. I'd like to be able to add > code to the app like: > if pg.autovacuum == "on": > self.routine_vacuuming = False > else: > self.routine_vacuuming = True > so that we can avoid manually issuing vacuum commands at sites where > Postgresql is running autovacuum. > But so far I haven't been able to find a way for a non-privileged user > to query the autovacuum status, is this possible? yes. select setting from pg_settings where name = 'autovacuum'; > > Also, the routine-vacuuming section of the manual states that the > purpose of the autovacuum daemon is to periodically issue VACUUM and > ANALYZE commands - am I correct in thinking this implies that it will > not issue VACUUM FULL commands? Yes, you're correct. -- Filip Rembiałkowski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with async notifications of table updates
Tom Lane wrote: >"Tyler, Mark" <[EMAIL PROTECTED]> writes: >> Secondly, the lack of any delivery guarantee means my subscriber >> applications may miss event notifications. This is a very bad thing >> for my particular application. > > What makes you think NOTIFY doesn't guarantee delivery? If the > transaction commits then the notify update has happened. The description of NOTIFY in the manual led me to think this - especially the bit "if the same notification name is signaled multiple times in quick succession, recipients might get only one notification event". Re-reading the sentence I can see that I should be interpreting it as "guaranteed notification of one of a stream of signals". Is there any chance of loosing a notification if it occurs when I am handling a previous signal? I guess not but I am not that used to signal behaviour. My original thought was to use a single NOTIFY channel for notifications of all changes and then have some secondary table to carry the payload of the signalled message. If I don't get a notify for every change then I have to do more work at the app end to try and work out what actually happened. > Perhaps more to the point, have you reflected on the fact that your > technique has the opposite problem? Once you've given the message > to Spread, it'll deliver it whether your transaction subsequently > commits or not. Which is why I would like to be able to fire the Spread message after the transaction commits. If I can do that then all is good (I think). Mind you if the transaction does not commit then that is a relatively easy case to handle - any recipients of the message will just get a NULL set when they do a query on the key in the message. Given that I have to have that path in my subscriber apps anyway it is no overhead. > If you're really intent on re-inventing NOTIFY, you could use the > same synchronization trick it does: take out a lock on some > otherwise unused table just before sending the message, and have > recipients lock the same table on receipt of the message, before > they go looking for any effects in the database. The NOTIFY-side > lock is held past commit of its transaction, so once recipients can > lock the table they must be able to see the results of the NOTIFY's > transaction. This is not insanely great from a concurrency standpoint > of course, but as long as you keep the lock hold durations short it's workable. Thanks for the explanation of how NOTIFY and LISTEN work. I could take the same approach as you suggest but it would again put too much database-trickery into the subscriber apps for my taste. There is no a big advantage between doing this and using NOTIFY directly. Mark IMPORTANT: This email remains the property of the Australian Defence Organisation and is subject to the jurisdiction of section 70 of the CRIMES ACT 1914. If you have received this email in error, you are requested to contact the sender and delete the email. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] identify database process given client process
Joey K. wrote: On Mon, Mar 17, 2008 at 6:58 AM, hogcia <[EMAIL PROTECTED]> wrote: Hi, I have to find a Postgres database process pid (or other identification) for a given client process pid. Or client processes for a database process. How are they connected? I was suggested maybe netstat could give me the answer and I think those are two pf_unix processes. But maybe there are some PostgreSQL functions that do this? How should I approach this topic? Thanks in advance, Try select pg_stat_activity; Joey That would be select * from pg_stat_activity; The columns that interest you would be datname,procpid,usename and client_addr The other way would be using ps (for a *nix server) Depending on your system something similar to ps aux will give the process details so the command column will give you something like - postgres: mydbuser mydbname 192.168.0.3(49438) idle which is the info you are after - pgsql is the dbusername, postgres is the db they are connected to then the ip address and port they are connecting from. The idle at the end will be replaced with the query they are running. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
