Re: [GENERAL] correlated query as a column and where clause
I am wondering, why I can not add the following ' A 10' in the where clause i.e. 'where nspname !~* 'pg_' and A 10' Select nspname, (SELECT count(*) as count FROM pg_tables where schemaname = nspname) as A FROM pg_namespace where nspname !~* 'pg_' what you are looking for is the having clause Select nspname, count(1) FROM pg_tables where nspname !~* 'pg_' group by nspname having count(1) 10 best wishes Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399
[GENERAL] PostgreSQL documentation on kindle - best practices?
my google-fu lead me to the following receipts: - create HTML documentation as single file, use Calibre to convert - use downloadable HTML-documentation, convert via Calibre (minor problems are reported, as in wrong order of sections) - download PDF and convert via Calibre - download PDF and put on kindle 1-3 and use different conversion tools. So my question: has anyone found a best practice solution to convert the PostgreSQL documentaiton into a kindle-friendly format? Or has even an .azw file downloadable somewhere? Best wishes, Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399 -- 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] postgresql-9.0 service starting problem
Kalai, The postgresql-9.0 service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service most likely problem are unavailable ressources, as in: - PostgreSQL cannot access its data directory (because of changed file/directory permissions) - PostgreSQL cannot open its port for communication (because of other running PostgreSQL / because of zealous firewalls) or wrong configuration files, i.e. errors in pg_hba.conf or postgresql.conf. Start the eventview application and check for entries in the application log. Best wishes Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399
Re: [GENERAL] Database recovery.
Waqar, my windows event viewer says: 2011-03-24 11:24:22 GMT FATAL: could not create lock file postmaster.pid: Permission denied is this helpfull? yes. that says that the windows-user your PostgreSQL Service is running as has no write priv on the data directory. Check your file systems permissions and allow writing to the data directory for the user your PostgreSQL service runs as. Best wishes HArald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399 -- 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 copy for WAL archiving on Windows
Christian, Should PostgreSQL maybe provide its own file-copy utility for Windows that meets the requirements for safe WAL archiving? Microsoft does provide an enterprise-ready webscale copy program ... it is called robocopy and part of the Windows Server Resource Kit Tools. Would you mind trying that utility for copying WAL-files? http://www.microsoft.com/downloadS/en/details.aspx?familyid=9d467a69-57ff-4ae7-96ee-b18c4790cffddisplaylang=en (Microsoft URLs are like a leaf in the wind of change, so better google for robocopy site:microsoft.com) best wishes, Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support 2ndQuadrant Deutschland GmbH i.Gr. GF: Harald Armin Massa
Re: [GENERAL] problem updating from form
Hello Karen,
intermittent problem, but I have been able to replicate the issue
consistently on one particular record. We are using PostgreSQL 8.1.4 on Red
Hat, Microsoft Access 2002 psqlodbc_09_00_0200.
PostgreSQL 8.1 was EndOfLifed in November 2010, you are recommended to plan
for an update ASAP
http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
More important: the highest double-dot of the 8.1 series is 8.1.23; that are
17 generations of critical bugs fixed younger than your system. An update
within the same major release consists of exchanging the binaries and should
be done immediately.
I can update the field successfully in the table in Access, but not in a
form. I have isolated the table, removing any foreign keys from the table.
There aren't any triggers. I created a new Access DB with only the table
a crude form with only two fields, the primary key one other. In ODBC I
made sure that row versioning was set as true and Text as LongVarChar was
set as false. We have vacuumed, reindexed performed a full vacuum on the
Postgres DB multiple times. None of these actions have had a positive
effect. I created an identical copy of the table imported all of the
data, over 194,000 records, into it. I saw the same problem results until I
removed all, but about 20 records. Only then was I able to get the update
from a form to stick on our test record.
To narrow down the problem: please find out what is really ending up at the
database server. In your test-cluster (where you were able to reproduce the
problem), configure your postgresql.conf of log every statement, as in:
log_min_duration_statement = 0
(Warning: that may not be a good idea on production systems, as a lot of
logging is done)
Now do your tests within access, and check what does get logged - i.e.,
which statements really make it down to the database. That should give you
information to narrow down the bug.
Should there be a correct sequence of statements as in begin; insert into
mytable ('value','another_value');end; which does NOT get honoured by
PostgreSQL, then there is something wrong with your database configuration.
That is very, very unlikely.
PostgreSQL is known to perfectly reliable store and update millions of
records, so an error up in the line of command (Access Form Widgets, Access
ODBC-Adapter, PGODBC...) is the more likely culprit.
best wishes,
Harald
P.S.:
Please let me recommend that you thoroughly invest the potential ROI of
paid, onsite one-to-team consulting for your PostgreSQL usage. You can and
will get all information to solve your problem for free out of
documentation, mailing lists and search engines.
I would expect that for you and your team the time saved and knowledge
gained by direct, paid mentoring will give an productivity boost way greater
then the consulting fees of the professional service providers at
http://www.postgresql.org/support/professional_support
(disclaimer: I am with one of them)
--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support
Re: [GENERAL] Issue while using PostgreSql 8.4.
Atul, My issue is, first time I am creating the table and inserting some rows of data. After doing some logic going to delete that table . I am observing application is getting hang while executing statement.execute(). Please suggest me how to fix this issue. I am assuming your statement.execute includes something like drop table yourtable did you make ABSOLUTELY sure that no part of your (or any other running) programm is still interested in that table? as in maybe some ORM-mapper still has a transaction open into that table? Because dropping a locked table has to wait until the lock is gone to complete. (esp. if there is an implicit commit in your execute; which may or may not be, depending on your connection-configuration) Harald -- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Merlin, I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). while agreeing, an additional question: could you please pronounce really, really large in other units, like Gigabytes or Number of rows (with average rowlength in bytes, of course) That is: what table size would you or anybody consider really, really large actually? Harakd -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- 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] postgreSQL amazon ec2 cloud
John, Is it possible to host postgreSQL on Amazon's cloud? What are the issues involved? in theory, sure. anything is possible. in practice, as I understand it from my relatively superficial reading, fast storage is fairly expensive and limited in the EC2 compute cloud, and also not real persistent That also was my understanding. But just today a message from AWS dropped in my inbox: Starting today, you can now launch Amazon EC2 running Windows or SQL Server instances in the the EU Region, So there must be some way to run a relational database with EC2, as the storage requirements of SQL Server and PostgreSQL are not THAT different. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] \l of psql 8.4 looks ... unreadable on 80char default
Hello, testing / using PostgreSQL 8.4, I queried the list of databases via \l in psql I get information about name, owner, coding, Collation, Ctype and access rights. Which wraps right in the middle of ctype, as collation and ctype both are German, Germany As a result: this basic information command is rather unreadable with only 80chars. is this an effect only with long name collations? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] \l of psql 8.4 looks ... unreadable on 80char default
No difference at all. Line breaks are exactly as on screen (looks more crazy in email with non-fixed-with font) But really, linebreak betwwen Zug and riffsrechte, and cutting between =c/pos and tgres postgres=# \l Liste der Datenbanken Name| Eigentümer | Kodierung |Collation| Ctype |Zug riffsrechte ---++---+-+-+--- ibox | ibox | UTF8 | German, Germany | German, Germany | postgres | postgres | UTF8 | German, Germany | German, Germany | template0 | postgres | UTF8 | German, Germany | German, Germany | =c/pos tgres : postgr es=CTc/postgres template1 | postgres | UTF8 | German, Germany | German, Germany | =c/pos tgres : postgr es=CTc/postgres (4 Zeilen) postgres=# \pset format wrapped Ausgabeformat ist »wrapped«. postgres=# \l Liste der Datenbanken Name| Eigentümer | Kodierung |Collation| Ctype |Zug riffsrechte ---++---+-+-+--- ibox | ibox | UTF8 | German, Germany | German, Germany | postgres | postgres | UTF8 | German, Germany | German, Germany | template0 | postgres | UTF8 | German, Germany | German, Germany | =c/pos tgres : postgr es=CTc/postgres template1 | postgres | UTF8 | German, Germany | German, Germany | =c/pos tgres : postgr es=CTc/postgres (4 Zeilen) Harald On Thu, Feb 19, 2009 at 14:11, Bruce Momjian br...@momjian.us wrote: Harald Armin Massa wrote: Hello, testing / using PostgreSQL 8.4, I queried the list of databases via \l in psql I get information about name, owner, coding, Collation, Ctype and access rights. Which wraps right in the middle of ctype, as collation and ctype both are German, Germany As a result: this basic information command is rather unreadable with only 80chars. You might try the new psql option: \pset format wrapped to see if it looks better. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] \l of psql 8.4 looks ... unreadable on 80char default
Thanks! What is your $COLUMNS set to? This should have wrapped to fit into the screen with. Also look at \pset columns: $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works. Thank you very much, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] \l of psql 8.4 looks ... unreadable on 80char default
Gregory, $COLUMNS was not set at all ... guess that is a usual environment variable on bash. Setting it to 80 works. COLUMNS wasn't set and \pset columns wasn't set? What environment were you running this psql command in? Was the output redirected anywhere with \o or with on the commandline? Environment: cmd.exe within Windows 7 Beta 64bit. (should be quite similar to Vista XP) And I did not redirect anything ... Just started a default cmd.exe, to paste it into the email I used select and copy from the system-menu of that window. (this \l is just my ritual after installing PostgreSQL, to see that PostgreSQL is installed and the installer accepted my wish for UTF-8) We had a long argument about how this should work a while back so I suspect people aren't psyched about reopening it, but while I don't think the current logic is right I don't think wrapping to 80 columns when your terminal is wider is one of the current broken cases. It tends to fail in the opposite direction of randomly not wrapping at all so it's kind of surprising to see your experience. It did not wrap at all, so that randomly not wrapping at all was my kind of failure, wasn't it? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] psql: not enogh memory when trying to use utf8 on windows
C:\hamchcp 65001 Aktive Codepage: 65001. C:\hampsql -U postgres Passwort f�r Benutzer postgres: psql (8.4devel) WARNING: Console code page (65001) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. Type help for help. postgres=# \l Nicht genügend Arbeitsspeicher. postgres=# chcp 65001 is the setting for UTF8 on Windows 2000 and up. Thought it would be worth a try, as my default encoding on client and server when attacking PostgreSQL via Python is UTF8. Rather strange: Passwort f�r Benutzer postgres: - the u-umlaut gets scrambled Nicht genügend Arbeitsspeicher. - the u-umlaut does not get scrambled and Nicht genügend Arbeitsspeicher. is roughly not enough RAM, which is really not expected just for changing the code page Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] Multiple postgres.exe On Processes
Just adding: processes. with 102 concurrent logins, 105 processes. the bulk of the memory and code is shared by these processes, with the exception of things like per client work_mem buffers which by definition can't be shared as they please be advised that the default-view of TaskManager (XP) does NOT show this memory as shared, but as multiple multi megabyte processes. In other words: the default view of TaskManager gives the impression that shared memory is used by every process. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] small doubt
Kusuma, On Wed, Feb 11, 2009 at 13:43, Kusuma Pabba kusu...@ncoretech.com wrote: what does postgres management gui mean ? How is it useful ? can i have it on ubuntu? propably pgAdmin III, www.pgadmin.org on ubuntu you can have pgadmin via apt-get install pgadmin best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] updateable view: message
Hello, I am using updateable views to have time-based tables (some status is valid for some time and has to be preserved after updates, inserts and deletes) I created DO INSTEAD rules for update, insert and delete, example: CREATE OR REPLACE RULE formularfeld_update AS ON UPDATE TO formularfeld DO INSTEAD ( UPDATE otformularfeld SET validbis = now(), letztespeicherung = now() WHERE otformularfeld.id_formfeld = old.id_formfeld AND otformularfeld.validbis = '-12-31 00:00:00'::timestamp without time zone AND otformularfeld.quarant = get_quarant(); INSERT INTO otformularfeld (id_formfeld, id_formular, id_bf, sortierung, ebene, gruppe, letztespeicherung, einblenden, ausblenden, drucknr, untergruppe, validvon, validbis, id_user, quarant) VALUES (new.id_formfeld, new.id_formular, new.id_bf, new.sortierung, new.ebene, new.gruppe, now(), new.einblenden, new.ausblenden, new.drucknr, new.untergruppe, now(), 'infinity'::timestamp without time zone, get_user(), get_quarant()); ); (that is: write this line is no longer valid, and from now on this line is valid instead) Everything works fine. Just the feedbacks are ... disturbing: update formularfeld set sortierung=1442 where id_formfeld=13798 leads to: Query returned successfully: 0 rows affected, 234 ms execution time. which is correct in one sence: - within the updateable view there were 0 rows affected - all stuff is done in otformularfeld; the table behind the view on the other hand: the instead rule touched two rows in otformularfeld. And this is reflected nowhere. So my question: can I have influence on that query result message, and if yes, how? My only option so far would be to have some function called within the instead rule that raises NOTICE ... which seems rather hackisch. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] Installing PostgreSQL on Windows 7 Beta Build 7000 64bit - first results
Holger, 4.) it seems to be no longer cool to have your data below Program Files / Program Files (x86) That was never cool or good practice. Yes, we discussed that on pg-installer some time ago. But we were in good bad company, as Microsoft SQL Server and Exchange did similiar things. Quod licet iovi I'm not sure which directory should we create $PGDATA -- My Documents, Application Data, Local Settings or direct child of %USERPROFILE%, but anyway some of them would be better than Program Files. %USERNAME%/%APPDATA% The default for bigger data seems to be \progdata on Windows 7. That is from observation only, as i.e. Ubisoft installs there. Not clear which %XX% that is... Nontheless: I usually pick my own data directory, as there have to be special provisions made concerning backup and viralscanners. THE MORE hurting problem on Windows7 is the inability to start the postgresql service. There must have been some changes to the service framework / security security which prevend the startup... Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing PostgreSQL on Windows 7 Beta Build 7000 64bit - first results
: 0x471c6a Berechtigungen: SeImpersonatePrivilege and after that: Ein Konto wurde abgemeldet. [account was logged of] Antragsteller: Sicherheits-ID: TempleC7\postgres Kontoname: postgres Kontodomäne:TempleC7 Anmelde-ID: 0x471c6a Anmeldetyp: 5 - service does not start. 5.) So, trying as my default user: a) adding postgresql\8.3\bin to my path b) pg_ctl start -D d:\ghum\data83 server starting D:\ghum\data832009-01-24 12:55:27 CET LOG: loaded library $libdir/plugins/plu gin_debugger.dll .. which gives me a FINE running postgresql 8.3 C:\Users\hampsql -U postgres --port=5433 Password for user postgres: Welcome to psql 8.3.5, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit Warning: Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page Notes for Windows users for details. postgres=# 4.) it seems to be no longer cool to have your data below Program Files / Program Files (x86) The default as of know is \ProgramData\application 5.) so: PostgreSQL somehow works on Windows 7 64bit; BUT not running as service I would be willing to conduct more tests or try other things or provide logs, please contact me! :) Best wishes Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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 restart postgresql when increasing max_connections
Thom, I have a server running 8.3.1 with 16Gb of memory and 8x2.5Ghz cores. The max_connections was set to 100 (the default), but we were getting denied connections because it had exceeded the max. We increased this to a modest 250, stopped the service, and then tried to start. It wouldn't. We stopped it several times, made sure all postgres-related processes were killed off but nothing would make it start. Actually, it said it had started, but it hadn't. When setting it back to 100 it was okay again. We tried the same thing on another server, setting it to 1000, and that was fine. Maybe you are missing: # Note: Increasing max_connections costs ~400 bytes of shared memory per # connection slot, plus lock space (see max_locks_per_transaction). You might # also need to raise shared_buffers to support more connections. To help debugging, your report is essentially missing the operating system your computer is running on and the output from the log files. If PostgreSQL does not start, it writes out a reason to its logfiles. For example in default installations on Windows you will find your logfiles within the PostgreSQL-Data-Directory in subdir pg_log best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] version number between pgdump and server
Laurent, Will it not be more reasonable to abort only if the first two numbers mismatch but not the last one which is (AFAIK) only a patch number and does not change the features ? it corrects bugs. Not only security vulnerabilities, but also bugs. And it may be that there is also a fixed bug in pg_dump. So better one warning more then one warning less, best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how can I exclude stuff from template in my dumps?
hello, on restoring dumps I get errors about languages and functions not getting restored: pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 135; 1255 164 39 FUNCTION pldbg_step_over(integer) postgres pg_restore: [Archivierer (DB)] could not execute query: FEHLER: Funktion »pldb g_step_over« existiert bereits mit den selben Argumenttypen Command was: CREATE FUNCTION pldbg_step_over(session integer) RETURNS breakp oint AS '$libdir/pldbgapi', 'pldbg_step_over' LANGUAG... of course ... pldbpg_step_over is allready present, as it is installed by the setup of the template database and thatfore it is on every database. Same with procedural language pl/pgsql and pg_buffercache etc... Result: pg_restore ignores 29 errors, which is harmless, as those functions are allready present. BUT: it would be nicer, to not have these errors, as they can overshaddow REAL error messages within the output. Is there a clever way to exclude them from dumps or restores? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] Session variables (how do I pass session data to a function)
Philip, session variables is the perfect name for this kind of use; and googling it up some times ago lead me to a) a temp table implementation b) some shared memory implementation of these I can present you with a), written to store an integer user-ID; you can adjust it accordingly: CREATE OR REPLACE FUNCTION set_user(myid_user integer) RETURNS integer AS $BODY$ BEGIN perform relname from pg_class where relname = 'icke_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then create temporary table icke_tmp ( id_user integer ); else delete from icke_tmp; end if; insert into icke_tmp values (myid_user); RETURN 0; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; CREATE OR REPLACE FUNCTION get_user() RETURNS integer AS $BODY$ declare ergebnis int4; BEGIN perform relname from pg_class where relname = 'icke_tmp' and case when has_schema_privilege(relnamespace, 'USAGE') then pg_table_is_visible(oid) else false end; if not found then return 0; else select id_user from icke_tmp into ergebnis; end if; if not found then ergebnis:=0; end if; RETURN ergebnis; END; $BODY$ LANGUAGE 'plpgsql' STABLE COST 100; You can adjust that 0 to NULL or whatever should be the default for your application. In mine I default to 0; with 0 being something like testuser. best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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 select rows that are the max for each subcategory?
select x,y,z from t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) best wishes Harald On Thu, Sep 25, 2008 at 20:01, Kynn Jones [EMAIL PROTECTED] wrote: Suppose I have a table T that has, among its columns, the fields X and Y, where Y is an integer, and multiple rows with the same value of X are possible. I want to select the rows corresponding to the greatest values of Y for each value of X. E.g. suppose that T is X Y Z a 1 eenie a 3 meenie a 2 miny b 4 moe b 0 catch ...the result of the desired query would be a 3 meenie b 4 moe TIA! Kynnjo -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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 select rows that are the max for each subcategory?
uups, you need to specify the table in the select, so select t.x,t.y,t.z from t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) On Thu, Sep 25, 2008 at 20:05, Harald Armin Massa [EMAIL PROTECTED] wrote: select x,y,z from t join (select x, max(y) as my from t group by x) t1 on (t.x=t1.x and t.y=t1.my) best wishes Harald On Thu, Sep 25, 2008 at 20:01, Kynn Jones [EMAIL PROTECTED] wrote: Suppose I have a table T that has, among its columns, the fields X and Y, where Y is an integer, and multiple rows with the same value of X are possible. I want to select the rows corresponding to the greatest values of Y for each value of X. E.g. suppose that T is X Y Z a 1 eenie a 3 meenie a 2 miny b 4 moe b 0 catch ...the result of the desired query would be a 3 meenie b 4 moe TIA! Kynnjo -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned! -- 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] Autovacuum, too often?
Hello Glyn, Our legacy apps have some permanent tables that they use for tempory data and constantly clear out, I've kicked the developers and I intend to eradicate them eventually (the tables, not the developers). and what is the problem with this usage? That is a perfectly valid thing to do; PostgreSQL can handle that for centuries; no need to kick the developers :) These tables are constantly being autovacuumed, approximately once a minute, it's not causing any problem and seems to be keeping them vacuumed. That is the right thing to do. pages: 21 removed, 26 remain tuples: 2356 removed, 171 remain system usage: CPU 0.00s/0.00u sec elapsed 0.08 sec As you described, that temp-tables get filled and cleared regularly ... that is insert a lot of stuff delete the same stuff again; so there are lots of unused i.e. deleted tuples, which get recycled by your vacuuming. And that with nearly no CPU usage. Sounds fine to me :) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - EuroPython 2009 will take place in Birmingham - Stay tuned!
Re: [GENERAL] Vacuuming on heavily changed databases
Hello, I would like to ask an opinion on vacuuming general. Imagine situation that you have single table with 5 fields (one varchar). This table has during the day - cca 620 000 inserts - 0 updates - cca 620 000 deletes The table is vacuumed daily, but somehow after several months I got to size of ~50GB do not vacuum DAILY. set up autovacuum to run AT LEAST every minute. autovacuum will flag the deleted rows as to be reusable by next insert. Make sure to use 8.3.latest, it's much more easy to setup autovacuum then before. best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pidgeon - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] Conditional on Select List
Fernando, the IF function is called CASE WHEN condition THEN result [WHEN ...] [ELSE result] END read about it at http://www.postgresql.org/docs/8.3/static/functions-conditional.html best wishes, Harald On Tue, May 13, 2008 at 5:52 PM, Fernando [EMAIL PROTECTED] wrote: Is it possible to do this? SELECT IF(COUNT(colname) 0, TRUE, FALSE) AS colname FROM table; What I want is to return a boolean, but when I tried SELECT COUNT(colname)::BOOLEAN FROM table; it says it cannot cast bigint to boolean. Is there such IF function or do I have to create my own. Thank you. -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:
hello, most crucial information is missing, like: - your operating system - your postgresql version - on which computer is your database server running - is your database server running But most likely your problem is that you did not configure PostgreSQL to listen to TCP/IP-requests. Which again makes it most likely that you are using a way outdated PostgreSQL Version (that ist 8.x) Please update to 8.3.1, and adjust your postgresql.conf, parameter listen_addresses (string) accordingly. Best wishes, Harald On Mon, May 12, 2008 at 11:58 AM, Abdus Samad Ansari [EMAIL PROTECTED] wrote: PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: I have setup PHP/Postgres and is running fine upto document root i.e. /var/www/html, but when i am calling it through a cgi-bin php file it is giving log error as : [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF; what may be the solution. --Abdus Samad Ansari -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] Request for Materialized Views
Jan, we arrived at the conclusion that we really need Materialized Views for our further business. just some information http://wiki.postgresql.org/wiki/Updatable_views are on the wishlist for PostgreSQL 8.4; and (Bernd) is the one coordinating it: http://wiki.postgresql.org/wiki/Todo:WishlistFor84 As the whole VIEW system has to be touched for this AND for materialized views, maybe it is a good idea to contact him? also: http://www.postgresql.org/docs/faqs.TODO.html MATERIALIZED VIEWS have been on the TODO list for quite some time ... someone from the community for the feature to be implemented for Postgres instead of doing it ourselves. I think after asking here on PostgreSQL-General, your next step may be to propose sth along the lines: would like sponsor sb to build MATERIALIZED VIEWS into PostgreSQL on pgsql-hackers: The PostgreSQL developers team lives here. Discussion of current development issues, problems and bugs, and proposed new features. If your question cannot be answered by people in the other lists, and it is likely that only a developer will know the answer, you may re-post your question in this list. You must try elsewhere first! best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] TrueCrypt - Forwarded from advocacy
In a thread on advocacy there was talk about the challenges on some rather strict borders. There are reasons, why it may be advised to hide or store away data. Customer Information on Laptops. i.E. and ... PostgreSQL data is open accessable as soon as somebody has physical access to the hard drive. TrueCrypt supports a Hidden Volume steganography feature where you can have a decoy encrypted volume to give up if forced to enter an encryption key, while the real files you want to secure (those you've put on double-secret probation) are safe via a different password. TrueCrypt is really amazing ... and now to my question: who has experience of PostgreSQL database files on TrueCrypt volumes? Speed? Reliability (additional layers between database and harddrive are allways challenging) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] Which Python library - psycopg2 or pygresql?
Micah, psycopg2 has a license extensions which allows basically to use psycopg2 binaries without distributing source code as long as there are no modifications to the psycopg2 C code best wishes Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] No Return??
Bob, if pumpnumber not in (1,2) that function does not return anything. End If; at this end if it ends so you have to return sth. here END; Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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] Postgresql Conferences/events in Europe?
Simone, one of the main European Python Events will be: EuroPython 2008 in Vilnius, Lithuania Monday 7th July - Saturday 12th July at the Reval Hotel Lietuva do you know of any interesting Postgresql Conferences/events in Europe in 2008? Or PHP/Python events as well? Quite likely there will also be some talk about PostgreSQL and how great 8.3 is Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! -- 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+
Dee, What are the permissions required to install postgres as a service on windows 2000/2003? I have followed the instructions, as I understand them, but the program will not install. It either fails with The application failed to initialize properly (0xc022). Click on OK to terminate the application. or Invalid username specified: A required privlege is not held by the client. 1.) you should prefer windows 2003 for your first PostgreSQL install on windows. Installation on Windows 2000 is a lot more challenging. BTW: main stream support for 2000 by Microsoft ended on 2005-06-30, so unless you have contracts with MS for extended support... 2.) please be very carefull about the different permissions for INSTALLING and RUNNING PostgreSQL on windows To install PostgreSQL on windows you need permissions to: - install software - create a service - change permissions on files / directories To RUN PostgreSQL as a service on windows your serivce user needs LOGON_AS_SERVICE privilege. the usual install: - logon as Administrator - run the installer - the installer creates the windows-user postgres, per default for the local machine, and adds the LOGON_AS_SERVICE privilege to the user So, to drill down to your problem: a) when did you get this error? During installation? That would point more to problems with wrong version of installer, broken MSI-files --- if on running the service: a) what user is the service configured to use to log on? b) what policies are in effect on that user? Please be aware that a lot of defaul policies do take away the LOGON_AS_SERVICE privilege c) whats within the log files? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Bug (?) in JDB
Calum, I do not know about Java and jdbc, but had similiar problems with various Python-Database adapters. Have you double-checked that true and false are valid options? at http://www.postgresql.org/docs/current/interactive/libpq-connect.html you can read sslmode This option determines whether or with what priority an SSL connection will be negotiated with the server. There are four modes: disable will attempt only an unencrypted SSL connection; allow will negotiate, trying first a non-SSL connection, then if that fails, trying an SSL connection; prefer (the default) will negotiate, trying first an SSL connection, then if that fails, trying a regular non-SSL connection; require will try only an SSL connection. If PostgreSQL is compiled without SSL support, using option require will cause an error, while options allow and prefer will be accepted but libpq will not in fact attempt an SSL connection. so probably you should use disable instead of false, and require or prefer instead of true? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] exporting postgre data
Bill, pgadmin3 provides the same: just dig down to the table, use the backup link and give the options. It also calls pg_dump just with the right parameters, you can copy that command for a .cmd for professional automatic backup. Best wishes, Harald MSSQL has a fancy GUI wizard. It basically does all the stuff pg_dump does, but just using a GUI interface to manage all the options. I guess it has the addition of managing the interaction between pg_dump on one server an pg_restore on another as well. Doesn't really have any more features that I'm aware of, just has a nice one stop interface. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] exporting postgre data
JohnF, But how do you backup only a single table or transfer only a signle table. please use pg_dump. You can find excellent documentation at http://www.postgresql.org/docs/8.2/interactive/app-pgdump.html please pay special attention to the --table option. Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] suggested wording improvement in psql
hello, within pgsql in \? the command help there is: \du [PATTERN] list users the answer of list users is List of roles (since the introduction of rules), so I recommend updating the documentation to \du [PATTERN] list roles (users) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] data transfer/migrate from win to linux
Antonio, After looking for a way to transfer PostgreSQL/PostGIS data from windowsXP to linux (Ubuntu 7.10), I did not find it. Please, does anyone know an easy way or free tool for it. I do this via pg_dump on the sender and pg_restore or psql -f on the receiver site. Both are included with PostgreSQL. To avoid challenges, make sure that the PostgreSQL versions on both systems match. best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] count(*) and bad design was: Experiences with extensibility
Ivan, Please forgive my naiveness in this field but what does it mean an exact count and what other DB means with an exact count and how other DB deal with it? PostgreSQL will give you an exact count of the contents of the database as it is in the moment you begin your count. (i.e. the transaction starts) BUT as the table is not locked, in parallel somebody can bulkload MANY items into the database, so at the moment (start of your transaction) + 1msec your count may be invalid allready. I'd expect it perform as good or better than other DB since now the bottleneck should be how efficiently it can filter records... but still a count(*) with a where clause will incur in the same problem of what exact means. I know of 3 concepts to answer count() faster then PostreSQL: 1) just lie, present an estimate 2) do not have MVCC 3) store record deleted info in index, so you can answer count() with only scanning the index Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
large objects,was: [GENERAL] Restoring 8.0 db to 8.1
Not likely to change in the future, no. Slony uses triggers to manage the changed rows. We can't fire triggers on large object events, so there's no way for Slony to know what happened. that leads me to a question I often wanted to ask: is there any reason to create NEW PostgreSQL databases using Large Objects, now that there is bytea and TOAST? (besides of legacy needs) as much as I read, they take special care in dump/restore; force the use of some special APIs on creating, do not work with Slony Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Create Index (Hash) on a Large Table Taking Days...
I have a very large table (~5GB, 100mm rows) on which I am creating an index on an int4 column: CREATE INDEX CONCURRENTLY rums_idx2 ON rums USING hash (user_id); Why are you using a hash index on an int4 column? as you have 100mm (mm= Million?) rows, only 1 GIG ram and the column_name is user_id, I suspect: - you have far less then 100*10E6 Users - there are less distince user_ids than rows I just know hash from general programming, thatfor my belly is announcing hash collisions, hash collisions ... (within PostgreSQL I once read that hash indices are only for some cases I could not match to something that happens in my world; since then I am using default btree and am VERY happy.) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] C-Extenions for PostgreSQL, Call Convention Version 0
Hello, I am researching information about writing C-language-Functions for PostgreSQL. I came accross the slides of an OSCON 2004 tutorial of Joe Conway http://www.joeconway.com/tut_oscon_2004.pdf where he states: Version 0 Calling Convention • Deprecated • Has been since the 7.1 release. • Don't use it. Within the PostgreSQL-Documentation for 8.2, http://www.postgresql.org/docs/8.2/interactive/xfunc-c.html, still I find: Using call conventions version 0, we can define c_overpaid as: That sound very tutorial-like; and so $I wonder: is those version 0 depreciated? Or ist there still value in learning it? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Deploy postgres - upgrade strategy
HKLM\Software\PostgreSQL\Services\Service ID\Service Account Where: [...] Service account is the Windows user account that the pg_ctl service runs under, eg. MYPC\postgres I guess that would be service account that was assigned to the postgres service during installation, and that that key does NOT get updated on changes via services.msc, or? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] HouseKeeping and vacuum Questions
Ow Mun Heng, The current issue which prompted me to do such housekeeping is due to long database wide vacuum time. (it went from 2 hours to 4 hours to 7 hours) If vacuum takes to long, you are doing it not often enough. You should use autovacuum, you should be able to express the delta between two vacuums in seconds or minutes, not hours or days. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Understanding Aliases
Stanislav, SELECT t2.id, (SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10) AS t4_num FROM t2 WHERE t2.active AND ( (SELECT COUNT(id) FROM t4 WHERE t2_id = t2.id AND value=10) = 3 ) select t2.id, count(t4.id) from t2 join t4 using on (t2.id=t4.t2_id) where t2.active and t4.value 10 group by t2.id having count(t4.id) = 3 should do the trick without double select, or? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] CPU
Josh, However, the two extra cores (even if slower), will greatly help if you have any kind of concurrency. as much as I understand with running Postgres in the default configuration, there *will* be concurrency, without an if ? I am thinking of the background writer, the autovacuum process, the log writer and finally the connection serving process. ... quite sure of that default concurrency because I had to explain those basic 5 postgres.exe to at least 8 Windows Admins... My non-benchmarked experience is that multicore and postgres good (experience mainly drawn from windows) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Casting hexadecimal IPs to readable content
Jean-Michel,
to be sure the documentation of that storage would be needed. But my
educated guess is:
select get_byte(decode(substring('52e1fcb6',1,2),'hex'),0)||'.'||
get_byte(decode(substring('52e1fcb6',3,2),'hex'),0)||'.'||
get_byte(decode(substring('52e1fcb6',5,2),'hex'),0)||'.'||
get_byte(decode(substring('52e1fcb6',7,2),'hex'),0)
of course translated to:
select get_byte(decode(substring(columname,1,2),'hex'),0)||'.'||
get_byte(decode(substring(columname,3,2),'hex'),0)||'.'||
get_byte(decode(substring(columname,5,2),'hex'),0)||'.'||
get_byte(decode(substring(columname,7,2),'hex'),0) from yourtable
where columname is the name of the column with the IPs.
Best wishes,
Harald
On Nov 22, 2007 5:13 PM, Jean-Michel Pouré [EMAIL PROTECTED] wrote:
Dear Friends,
My PhpBB forum with 4000 users was hacked in Switzerland.
Enquirers (not my own idea) proposed that I look for certain IPs in my
PostgreSQL 8.2 database. The problem is that PhpBB stored IPs as
strings, which seem to be more or less encoded.
In PhpBB, IPs are stored as Hexa:
54dc0636
52e1fcb6
How to cast these values to readable content?
Any idea ? This is an important issue for me.
Thank you for any help.
Kind regards,
Jean-Michel
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Using Postgres as a embedded / SQL Lite database on Windows
Craig, just reporting my own experience; distributing PostgreSQL on Windows into around 15 companies on Servers AND Laptop-Clients with various restrictive in-House-Rules: - the windows installer is robust and scriptable - the installer uses standard .msi technology, so it can be integrated into nearly every self respecting sofware distribution system - challenges usually concerned user permissions for the postgres user: it definitely needs the logon_as_service permission, and Windows Installer creates it reliably with that permission. Problems only appeared when inhouse Windows permission systems (Active Directory and thelike) were configured wrongfully to take those permissions away. - PostgreSQL is very very robust even on Laptop-Usage and within virtual machines. Laptop-Usage being things like Power Outage, no clean shutdown etc. etc.; even BlueScreening Servers did not lead to data corruption - challenges on Computer Systems came from sloppy Antivirus-Systems and Firewalls, which decided to: a) lock database data-files b) interfere with local TCP/IP communcation from Client - Client and PostgreSQL postmaster to per-connection-backend - challenges with System Administrators arose from multiple postgres.exe processes running on each server (just starting postgres leads to 5 postgres.exe in taskmanagers process-view; that is correct but unusual for Windows) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Dave, It's coming from direct dependencies on user32.dll (from which we use wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath()) and is allocated when ResumeThread() is called to kickstart the new backend, why does every backend need its own heap for user32.dll or shell32.dll? Wasn't the point of shared dlls to be shared? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
why does every backend need its own heap for user32.dll or shell32.dll? Wasn't the point of shared dlls to be shared? The Desktop Heap appears to be a place for processes belonging to the same desktop to allocate shared objects such as GUI elements. These are allocated in shared space so they can be manipulated by any process running in that desktop. Using this knowledge and Daves response, also looking back at 3,2kb per backend, I stumbled upon that KB entry: http://support.microsoft.com/?scid=kb%3Ben-us%3B184802x=15y=14 Please pay special attention to the following parts: %SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows SharedSection=1024,3072,512 Windows=On SubSystemType=Windows ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3 ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off MaxRequestThreads=16 The second SharedSection value (3072) is the size of the desktop heap for each desktop that is associated with the interactive window station WinSta0. and further down: All services that are executed under the LocalSystem account with the Allow Service to Interact with Desktop startup option selected will use Winsta0\Default. All these processes will share the same desktop heap associated with the Default application desktop. Postgres is definitely NOT started as LocalSystem account; so using a logical not on Microsofts Words that could indicate the reason why our service-backends consume that memory? Add to this that MS SQL runs as LocalSystem; and as much as I know also Oracle. Is this a path of thinking to try? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
Replying to myself Postgres is definitely NOT started as LocalSystem account; so using a logical not on Microsofts Words that could indicate the reason why our service-backends consume that memory? Add to this that MS SQL runs as LocalSystem; and as much as I know also Oracle. just some lines further down: Every service process executed under a user account will receive a new desktop in a noninteractive window station created by the Service Control Manager (SCM). Thus, each service executed under a user account will consume the number of kilobytes of desktop heap specified in the third SharedSection value. All services executed under the LocalSystem account with Allow Service to Interact with the Desktop not selected share the desktop heap of the Default desktop in the noninteractive service windows station (Service-0x0-3e7$). it is exactly as suspected ... just starting the service allocates that heap Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Abbreviation list
I prefer the manual. I would think the list would be pretty long and deal with lots of internals terms. Also the manual has more reference character; as we saw with GUC in this discussion it is still possible to find a CORRECT definition of them. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
Bret, I had quick and robust success using pl/python and pyro. Pyro is Python Remote object. All mentioned modules are BSD-like in license. Harald What's the best way to invoke a process on another server from a PG trigger or procedure? I was thinking of using pl/java to invoke a web service on the other box... Does anyone else invoke a process on a remote server? How do you do it? -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Upper and Lower-cased Database names?
Daniel, please try: select * from MyTest.public.cars; mixed cases need those , per SQL-Standard. In my experienced mixed cases in qualifiers which cross OS-barriers cause more trouble then use (... filenames with WebServers ...) Harald postgres=# select * from MyTest.public.cars; ERROR: cross-database references are not implemented: mytest.public.cars -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished
the SQL Server 2005 Express download provides software that is suitable for application embedding or lightweight application development. I never developed more then some queries on SQL Server Express or its different names. But I had to work with some applications which used the various incarnations of SQL Server Express. And EVERYTIME it was a additionally installed application on the system. The only embedding that I could recognise was the Installer being triggered from the applications installer. MY idea of an embedded database would be I link something with my software, so that there is only ONE application. Is my understaning of embedded wrong or oldschool? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Question on INDEX and SQL - stalling my database?
Rishi, I looked up that thread 1st:) p.s. I am using PostgreSQL 7.4.17 Any reason for that? Actual version is 8.2.4; or at least 8.1.9 2nd) your query is: SELECT DISTINCT c.* FROM customer c LEFT OUTER JOIN weborders w ON c.username = w.username WHERE w.username IS NULL AND c.sourceid IS NOT NULL AND c.usertype = 0 AND c.emailrestrict = 1 ORDER BY c.addcountrycode, c.surname, c.initials so you force the poor database to do an left outer join just to find the outer records? Why? I would start with: select c.* from customer c where c.sourceid IS NOT NULL AND c.usertype = 0 AND c.emailrestrict = 1 and c.username not in (select username from weborders) BTW: I have a BAD feeling that your relation seems to be on USERNAME, which should make it a primary key on customer - and a user entered primary key is a bad idea 104% of the time. you can also save on the distinct, as every customer should have exactly one line in table customer. If not, you should dump that complete database and start from scratch. Harald On 8/18/07, Rishi Daryanani [EMAIL PROTECTED] wrote: Hi all, I'm having problems with a query that's just stalling my database. If someone could help me out - I posted a forum topic on http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html There's just this one integer field, which when searched on, stalls my query and I don't get any results. I tried adding an index to that integer, but that didn't seem to help.. Please let me know if there's any solution to this. Any help would be really appreciated! Many thanks! Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV. http://tv.yahoo.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
[GENERAL] Comment for column in view - legal or just working?
Using PostgreSQL 8.1.8 on Windows. I have one named listedi in schema cust; that view has one column name No I used comment on cust.listedi.name is 'MyDescriptive Name'; and the command suceeded. Using \d+ in psql also shows me that comment (also I find no option within pgadmin to view it :) ) My question is: - is this a legal usage of COMMENT, or could this vanish in later releases? (within COMMENTs documentation there is only mentionedcolumn tablename.columname) Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Comment for column in view - legal or just working?
Thanks, Dave. reminds me to run allways newest pgadmin, even on customer sites :) having it used within pgadmin makes it legal enough for me to rely on it within my application Best wishes, Harald You can in 1.8 - that will show a collection of columns under a view, and allow you to set the comment and default value for each. -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Tunning PostgreSQL performance for views on Windows
in addition to the good advise of materialzing the view as in create table whatever as select * from viewwhatever once a day, and to provide more information, PLEASE take notice that Good places to start with tuning: http://www.powerpostgresql.com/PerfList http://www.varlena.com/GeneralBits/Tidbits/perf.html those performance-recommendations are primarly based on Unix-Systems (Linux, BSD, Solaris). Especially shared buffers have totally different effects on win32. Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!
Re: [GENERAL] Using C# to create stored procedures
Tom, Awhile back I read an article claiming that .NET could only host one language, or at least only languages that differed merely in trivial syntactic details --- its execution engine isn't flexible enough for anything truly interesting. Jim Hugunin (creator of Jython, which is Python on Java Virtual Machine) thought similiar: wanted to understand how Microsoft could have screwed up so badly that the CLR was a worse platform for dynamic languages than the JVM. My plan was to take a couple of weeks to build a prototype implementation of Python on the CLR and then to use that work to write a short pithy article called, Why the CLR is a terrible platform for dynamic languages He tried it, wrote Ironpython, was hired by Microsoft... http://blogs.msdn.com/hugunin/archive/2006/09/05/741605.aspx So there is proof that .NET is usable for more then one language. (Not that I want to embrace that platform) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Tracking disk writes? (again)
Erik, on windows you can use performance monitor to filter down diskrequest. With sysinternals tools you can also go down to disk accesses on process basis. google for sysinternals site:microsoft.com on other platforms I have no information. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tracking disk writes? (again)
Erik, for solaris I think dtrace can be of help: http://www.opensolaris.org/os/community/dtrace/ PostgreSQL 8.2 introduced dtrace-ability. From what I read it is specifically created to do this kind of measurement. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Predicted lifespan of different PostgreSQL branches
a feeling I have). But the important part is that either way I think it's way too early to drop 8.1. I agree. Started a project last summer, using 8.1, rollout is now. 1 year for database projects is not unusual, and having the database release dropped during this time is not nice. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] capacity of tables
One table. If you need to split, you can allways do that via inheritance constraint exclusion, thereby creating table partitioning. Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Installing PostgreSQL under Cpanel
Erick, psql -U postgres should help. I hope you remembered the password you gave to the database superuser while installing... best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Installing PostgreSQL under Cpanel
Erick, - psql -U postgres psql: FATAL: Ident authentication failed for user postgres Nice. This gives us some more information about what kind of client authorization is configured by cpanel. You can read everything about client-autorisation on: http://www.postgresql.org/docs/8.2/interactive/client-authentication.html It looks as if cpanel has configured your pg_hba.conf for ident based authorization, please read the appropriate chapter. Would appreciate some insights. Where's the step by step guide to make this work? Cpanel/WHM and Plesk are THE most widespread control panels, and I see zilch on postgresql.org that addresses them. Correct. Also you will find nearly no stuff about using apt-get and rpm, which are some of the most used package systems on Linux on postgresql.org. And nothing about emerge, which is central to Gentoo Linux. And no real documentation to ports, which is prevalent on FreeBsd. PostgreSQL.org has extensive, detailed documentation of the PostgreSQL database. The ways the database is used or crunched by various installation tools is not covered. That's sad, but the way it is; and you can help to improve it: after you learned the hard way how to make cpanel do what you want, document your steps and send it to the list. Thanks for trying PostgreSQL, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Installing PostgreSQL under Cpanel
pg_hba.conf is within the PostgreSQL data directory. That directory can be put everywhere by the installation routine. usual places: - on windows per default below \programme\postgresql\8.2\data - within linux often below /var I tried find and whereis commands, but could not find it. Either your search command was wrong; or you do not have access to that directory. (second one is very unlikely as root) best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] More grist for the PostgreSQL vs MySQL mill
select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...) I usually try to rewrite this kind of queries to select whatever from table t1 join (select table_id from x where x) t2 using (table_id) And 3 out of 4 this performs better on Oracle and PostgreSQL. Would be curious why it does , but usually I am happy that it does:) Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] More grist for the PostgreSQL vs MySQL mill
Chad, select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...) I usually try to rewrite this kind of queries to select whatever from table t1 join (select table_id from x where x) t2 using (table_id) Because the results would be different than a subselect, less work = faster. One thing to point out is that a query of the form: Would normally result in a SORT UNIQUE for the select id from bar where n=27 part. Where as: select ... from foo f1, (select id from bar where n=27) f2 where f1.id = f2.id is the same as... select ... from foo f1, bar f2 where f2.n=27 and f1.id=f2.id which would not result in a sort unique. In order to obtain the same results as a subselect you would need to group or distinct, and I would imagine the results would be the same as the IN..SUBSELECT aaah, you are right. My rewriting only works when the id column is a primary key in the subqueried table; that way guaranteed to be unique, so that select distinct id from whatever where whateverelse yields the same results as select id from whatever where whateverelse thanks for pointing it out, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 fx 01212-5-13695179 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.?
Jeffrey, Not exactly. SQL Analyzer also includes live monitoring of whatever queries are coming into the database. You can achieve something similar by enabling query logging in the settings. that is within Tools / View Server Status of pgAdmin III Of course, Query Command String has to be enabled on the server; and the security implications of that setting have to be considered before :) best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] is there a tracking trace tool like the SQL Analizer in MS sqlserver.?
Ian, Query Analyzer is EXPLAIN with a GUI. Anyway, I have not heard of such a thing for PostgreSQL, On my installation of PostgreSQL from the stock windows installer there was installed PgAdmin, which has quite a GUI for EXPLAIN. The query plan is displayed very nice and I find it easier to understand then text output. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: FW: [GENERAL] Male/female
0 = woman 1 = man This gave me my first good laugh of the day... I will never accuse DBAs of not having a sense of humor albeit unique! Richard, gmail extended my laugh with the sponsored links: How To Be A woman How To Be The Girl That Every Man Secretly Wishes He Was Married To! Relationship-Advice.com PostgreSQL Replication Stable, fast and native replication for PostgreSQL 8.0 and 8.1 www.commandprompt.com/ PostgreSQL GUI admin tool Manage, Sync, Backup, Schedule Task Import/ Export, Report, Download! pgsql.navicat.com Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] starting postgres on windows
Garry, the standard recommendation is to install PostgreSQL as a service on windows; logging in with an own low privilege user account, usually named postgres. That usage of a service is recommended because it solves all the usual problems of services :) (start, shut down, login as seperate user ) However: I guess you have an privileged user (=Member of Administrators), and want to start PostgreSQL as an unprivileged user. So just create a command shell as that user: runas /user:postgres cmd and start and stop PostgreSQL within that shell. (in the long run, you can change that line to immediately start PostgreSQL instead of going via cmd) For 8.2 there is scheduled a drop privilege method of startup on windows, so that starting PostgreSQL as Administrator is no longer a problem, since PostgreSQL will drop it privileges immediately after start. hth Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Development of cross-platform GUI for Open Source DBs
Ritesh, what you are trying to do sound very similiar to dabo: http://dabodev.com/ Maybe have a look at it first? best wishes Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?
John, I'll weigh in my my .02 on this subject. After much pain and agony in the real world, I have taken the stance that every table in my database must have an arbitrary, numeric primary key (generally autogenerated). I feel the same. In the real world there is no such thing as a primary key. At least not over time. Not enough people understand the concept of a primary key to make those things existent in the real world. So we take an artificially primary key - and most reliable way is to create it yourself. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] PostgreSQL doesn't accept connections when Windows Server is rebooted
George, I guess your service is not running after rebooting? You can check this via services.msc Most propable cause: the postgres user does not have logon_as_service privilege. I ran the installer logged in under an Administrator account, and set the service account to postgres. That is the recommended way. The installer will create an low-priv postgres service account and run initdb as that user. During the installation process, when it came time to run initdb, the installer would stop with the error message: Failed to run initdb: 128. Please see the logfile in 'C:\Program Files\PostgreSQL\8.1\tmp\initdb.log. Note! You must read/copy this logfile before you click OK, or it will be automatically removed. Did you read that log? Read and copy it befor clicking OK, just as the message stated? BECAUSE: running THE INSTALLER as Administrator is recommended AND necessary to create the postgres user and to give correct rights to this user. SO: in running initdb.exe something else went wrong, it is NOT because you ran the installer as Administrator. (guessing you installed with port 5432, and initdb tried to contact 5432, failing, because it is an 7.3 ?) If the PostgreSQL service is then restarted manually (via Administrative Tools Component Services Restart), the service runs as expected and everything is normal. How can I correct this? One try to repair this: login as Administrator, open services.msc, and from the postgreSQL service refill the login information. That does reset the LOGON_AS_SERVICE privilege. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] PostgreSQL doesn't accept connections when Windows
George, I guess your service is not running after rebooting? You can check this via services.msc This is what is confusing me - the service IS running after rebooting, but does not appear to be listening on port 5435. If I simply restart the service in the Administrative Tools Component Services section everything is fine. Okay, that is a different situation. I was helping for the serice not running after reboot :) Can you please check the log files produced by the server? If PostgreSQL started up successfully, there should be a new file in data\pg_log (standard stderr redirection assumed) whats in that ? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] IS it a good practice to use SERIAL as Primary Key?
Ron, In the real world there is no such thing as a primary key. At least not over time. Not enough people understand the concept of a primary key to make those things existent in the real world. There are lots of numbers. Credit card numbers, account numbers sales ticket numbers, etc, etc ad nauseum. Julian day and Julian date, even. You can't have lived in the west in the past 30 years without being surrounded by them. Yes. There are a lot of numbers, but I the concept of a primary key is totally different from the concept of number. a Primary Key is supposed to identify something across time and space. Some time people thought that an IP-Number will do this... soonly after there was NAT. Again, somebody thougt, every microsecond is unique, and then there was daylight saving. Who gives guarantees that credit card numbers will not get reused after years of inactivity? Yes, there are natural keys, and it's good to use them to identify things for humans. But I got bitten many times by using them as primary. Many things that will not change, ever, just were changed on the next meeting. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] indexes
Tom, If the real-world primary key is large (say up to 100 characters in length) then the disadvantage is that you are duplicating this referenced key in several other tables, each element taking up 100 characters. Space is wasted when compared to int4 ID's. But not really sure if this is a performance problem for SELECT except for the space required (varchar(128) vs. int4). What is worth a try is to check an md5 or sha hash of those 100chars and use that as a key. with a proper hashing algorythm (proper= fitting to your data) collisions should not happen; and you have a shorter key to connect the tables. best wishes harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords.
Re: [GENERAL] plpython
KM,Can someone hint on resources for using plpython for writing stored procedures ? I have gone thru official docs for8.1.5 for plpythonu but its not in detail/with examples.CREATE OR REPLACE FUNCTION myfunc(text) RETURNS text AS$BODY$eingabe=args[0]hello = Good Morning %s % (eingabe,) return hello$BODY$ LANGUAGE 'plpythonu' VOLATILE;ALTER FUNCTION myfunc(text) OWNER TO postgres;that as a first example to get you started. The other one I could give you is using Pyro, which brings in rather uncommon challenges. When is plpython going to be considered safe ? any targeted version ? Hey, you fell into the same safe and unsafe trap than me!unsafe does not have the information: it is possibly errorprone to use this language unsafe simply says: it is impossible for the database to guarantee, that a bad minded programmer can do harmfull thins with this language For example: with plpython you can read and delete files on the server. There is no way for PostgreSQL to stop plpython from doing harm; so it is considered unsafe.(Btw: there is no restricted mode of execution for Python, google the Python Mailingslists for it; sandbox is a helpfull keyword) So: plpython is only recommended to be used by trustworthy programmers. Not by the general public.Harald-- GHUM Harald Massapersuadere et programmareHarald Armin Massa Reinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] SQL injection in a ~ or LIKE statement
psycopg2 supports parameters which are escaped properly.adding: Judging from the mails of Frederico, developer of psycopg2, he was also in the early notify circle of the 8.13-8.14 escaping improvement. So, if done correctly the DB API way, all escaping with psycopg2 is fine.Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b 70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] why not kill -9 postmaster
If your server is changing the data file on disk and you pull the power cord, what chance do you expect of reading that data file again?1.That's what we have WAL for.The only thing that can really killyou is the use of non-battery-backed write cache. Just for information: I had to suffer numerous BOS (blue screen of death) on an W2k3 Server running PostgreSQL 8.0 and 8.1 for Windows.Every time the database restarted without data loss and without operator invention. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Upgrade 7.4 to 8.1 or 8.2?
John,No I cannot risk live data...please read closely: Ron wrot Can you risk your app on beta software? .. There is allways a risk included in basing an application on beta software, as programming interfaces may change.BUT:My experience is: you can trust your data to PostgreSQL. The elephant never forgets. I started developing an application using some obscure FTP-Download of some obscure 7.x Windows Port. I roled out to pilot users using PostgreSQL 8.0 beta on Windows. That 8.0 BETA was used by salesmen on laptops. And not a single byte was lost by PostreSQL.NO, I do not recommend rolling out with 8.2beta; your way is perfectly sensible:I guess going with 8.1.5 is what we should do. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Can we convert from Postgres to Oracle !!???
Sandeep Kumar, Can we convert from Postgres to Oracle !!??? technically: partial. Oracle supports most of the bleeding edge enterprise features of PostgreSQL. There are some limits however, esp. concerning inheritance, arbitrary length text fields and especially the missing support for pl/python, pl/pgsql and pl/perl. You might be able to replace those advanced features of PostgreSQL using PL/SQL. There are external database development tools available for Oracle if you need to match the functionality of PostgreSQLs integrated clients psql and pgAdmin III. support-wise: please ask your Oracle authorized support contract consultant to find out which Oracle support plan gives you a support comparable to the PostgreSQL one with the possibility to directly correspond with the database core architects and developers as well as unlimited access to the applications source code. economically: not enough information to give a fitting answer. But if you need a high cash burn rate to attract certain kinds of investors, Oracle will propably make that easier. Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fast backup/restore
Gandald,have a look athttp://momjian.us/main/writings/pgsql/administration.pdfpage 44ffThere are descriptions how to do database-backups at the speed of raw file system operations. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] postgresql.conf shared buffers
Jim, list,from your link:ttp://www.varlena.com/GeneralBits/Tidbits/annotated_conf_e.html I quote:As a rule of thumb, observe shared memory usage of PostgreSQL with tools like ipcs and determine the setting. Remember that this is only half the story. You also need to set effective_cache_size so that postgreSQL will use available memory optimally.and add the question (not necessarily to you): -what is the best way to obsere shared memory usage on win32? - which memory-size should be taken for effective_cache_size on windows servers with multpile purposes (i.e.: more then PostgreSQL running on them)Available are (propable ones): physical memory, system cache, available memory (depends on system load) Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Partitioning vs. View of a UNION ALL
Ron,Even though using a view means that it would have to be recreatedeach period as the oldest table is dropped, please keep in mind: views are not really created ... also the command is named create viewVIEWS, at least in PostgreSQL (and Oracle) are nothing else then macros for Queries - the views get substituted within the query with their creation rule. That is: the recreation of a VIEW is a nearly instant process (it just is frustrating to connections using this view) it seems that it wouldstill be easier to work with, since you wouldn't have to worry aboutpreventing a badly behaving user from inserting into the DDLpartition's parent table and create 588 CHECK constraints (12 per year x 7 years x 7 base tables).That is true only if you trust your users not to insert into the wrong table of your 12*7*7 tables.If you have the appropriate check constraints on your parent table, the pushing data into the inherited tables should happen automagically (at least on my databases it does :) ) So... to make sure nobody inserts rubbish you will have to have those 588 check constraints one way or another. a) to make your partitioning workb) to ensure nobody inserts data for 2000 into the table for 1900 The most important issue, though, is query speed.Assumingexcellent index support for query WHERE clauses, regardless of whether partitioning or a viewed UNION ALL, which will the queryoptimizer and constraint_exclusion be more friendly towards?in an optimal world, should'nt those two options be exactly the same? a) the partition solution: query planner decides which of your 12*7*7 tables to access and only scans those. To my undestanding, constraint_exclusion only applies to this solution.b) the union all - or partitioning by hand: at the beginning of each partial query there will be an index scan on your date-column, learning that no data comes from that partial query and planner skipping on to the next.Harald-- GHUM Harald Massa persuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords.
Re: [GENERAL] Restart after poweroutage
Jon,For what it is worth, I created a FC5 VMware installation and loaded mydatabase data into it.I simulated a bunch of power outages by telling VMware to power off the vm.Is this a good simulation of a poweroutage, or is there something inherently flawed about using a VM to testthis?It is inherently flawed. VMware really powers down, that is, the operating system has time to shut down. Or, in other incarnations, VMware freezes the system state. It's nothing near a real power outage, which gives no time for anything.Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b 70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords. -- Harald A. Massa, December 2005 http://groups.google.de/group/comp.lang.python/browse_thread/thread/285b9adeec188fb2
Re: [GENERAL] postgresql rising
Alvaro,I wonder if we could replace the elephant logo with a female elephant logo.That could work wonders ... among the elephant community at least.Are there many elephants among decision makers? Asking google:http://www.google.de/search?q=teach%20an%20elephant%20to%20danceie=UTF-8oe=UTF-8hl=debtnG=Google-Suchemeta= there is at least a big elephant awareness in the softskill and management area. Especially big IT companies were in that business, look at Louis V. Gerstner, Jr.Harald-- GHUM Harald Massa persuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords. -- Harald A. Massa, December 2005 http://groups.google.de/group/comp.lang.python/browse_thread/thread/285b9adeec188fb2
Re: [GENERAL] postgresql rising
Bill,Suing someone is not the real reason.It's the excuse given to one'sboss.The real reason is the Nobody ever got fired for using IBM mentality. Nobody ever got fired for using IBM - today it is translated to (Oracle|Microsoft)And it may still be true. But it gives you only half the story: IF the tools somebody buys for his company do not allow that company to stay competitive, market will decide. And using databases from O or I or M just eats some money - that other companies who use the Elephant that never forgets don't have to spend. So, in the long run, buying O or I or M can get you out of job because of THE MARKET dealing with your company, which is not effective enough anymore. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Python: the only language with more web frameworks than keywords. -- Harald A. Massa, December 2005 http://groups.google.de/group/comp.lang.python/browse_thread/thread/285b9adeec188fb2
[GENERAL] Limits of arrays
I found a rather strange but working method to compare the contents of two tables in different databases: select 'otformularfeld' as which, md5(array_to_string(array( select md5(id_pkff||id_formular||id_formfeld||id_bf) from otformularfeld where quarant=0 order by id_pkff ),'')) So: - basically I take the relevant columns from a table - cast them to text and concattenate the strings - find the md5 hash of this row - then take the md5 of all rows, concattenate them to string - and finally find the md5 of this string That works surprisingly fast and gives a quick check data in those 2 tables is the same or not. Now, the maximum rowcount in one table so far is 18; and still there is no crash. Are there limits for the maximum rows in one arrays? Limits the maximum length of one string? The only near information I could find in the documentations was 1 GB per field, which will propably be the size limit for the array and the string. Are there more limits? Or is that md5 / concattenation process done in an iterative manner, that is: all the intermediate results are consumed and not cached in memory? Harald -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all. -- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] [pgadmin-support] pgAdmin III v1.6 Beta 1 Released
Wow!This really looks good. I especially like the subtile but helpfull improvements to the Query-Window --- that braces-matching really helps!Also the fine change to finish on the maintaince-OK button is a very nice solution. As pgadmin is distributed with PostgreSQL on Win32, maybe a slogan like PostgreSQL 8.2 - the polished release really fits.Harald-- GHUM Harald Massapersuadere et programmare Harald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] Large database design advice
Joe,with a normal serial, without big, you can have 9.223.372.036.854.775.807 records individually numbered. - Few tables but number of objects is tens-hundreds of thousands.- less than 100 queries per second.so you are talking about 10*100*1000=100 in words one million records? That is not very big. Table: item-account_id item_id So you have a combined primary key for item? That is technically totally correct, no problem. Non the less I recommend to have a separate primary key column for item. As you are starting with databases, some things will be easier to do without having a combined key as primary. How generally this design will hold up against this amount of data? Insufficen data to parse this amount :) You did not give us any information about how WIDE your rows are. Some million records are nothing if there just hold social security numbers in them; they can get quite stressfull if every row carries 3 blobs containing Videos. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] UUID as primary key
Vance,nice that it was of help.(When going from Java to the database, for example, it's easiest to compose the value directly into the SQL query rather thanusing a parameter.)This seems quite viable.Please allow me to recommend to you to NOT go this seemingly easy way. I went there myself (but did it in Python and PHP) some years ago, when I was young and needed the money. Those put Data into your SQL Statements with String Substitution is easy to get working in the first step; but gets very very hard to keep correct in the long run. Please google up topics like SQL injection and escaping characters for SQL - it is a source of endless frustration. Another tip to deal with GUIDs in applications: Because GUIDs are rather long when expressed in base16 (hexadecimal notion), I made a transformer to Base62, using numbers and all regular letters. That shortens the typical GUID from 32chars to 21chars. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
[GENERAL] PostgreSQL getting slower over time, restart of service cures the problem
Operating System: Windows 2003 ServerPostgreSQL 8.1.4 from the official installerAfter starting the service, a series of big queries runs blazingly fast ... for around 2 days. Then they get slower by day. After 3-4 days, the postgresql.exe processes have grown to around 120Megabyte Memory Usage and Max Memory Usage in Task ManagerAnd the queries which took 1-8 seconds before now take 30 seconds and up, longest logged 140seconds. Changes in the relevant tables are around 5%, new rows and updates and deletes.VACUUM FULL ANALYZE hangs then, interruptable. Trying to pg_ctl stop the server does not succeed. But with services.msc and stop service, start service the Database comes back up as good as new. The queries are speedy again, back to 1-8 seconds.Autovacuum is activated as:#--- # AUTOVACUUM PARAMETERS#---autovacuum = on # enable autovacuum subprocess?autovacuum_naptime = 1800 # time between autovacuum runs, in secs autovacuum_vacuum_threshold = 400 # min # of tuple updates before # vacuumautovacuum_analyze_threshold = 200 # min # of tuple updates before # analyzeautovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before # vacuumautovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovac, -1 means use # vacuum_cost_delay#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovac, -1 means use # vacuum_cost_limit and within the log there are also statements from autovacuum:2006-08-17 05:59:02 LOG: autovacuum: processing database swcheck2006-08-17 06:29:51 LOG: autovacuum: processing database postgres 2006-08-17 07:00:20 LOG: autovacuum: processing database iboxWith half an hour distance, as written for the naptime.apart from that, there are mainly errors like2006-08-17 06:50:57 ERROR: update or delete on filesall violates foreign key constraint $2 on otformularlink within the lockfile. My application trys to delete records; knowing that it may not be allowed to and deals with the PostgreSQL error.memory relevant parameters from postgresql.conf:max_connections = 250 # 400 #---# RESOURCE USAGE (except WAL)#---# - Memory -shared_buffers = 1 # min 16 or max_connections*2, 8KB each max_fsm_pages = 25000 # min max_fsm_relations*16, 6 bytes eachandeffective_cache_size = 5 # typically 8KB eachall the rest is on defaultThe machine has 1 GB of main memory. What can I do to stop PostgreSQL from getting slower over time? What can I do to help isolating the problem?-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b 70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] Is it possible (postgresql/mysql)
Louis,indizes are simply created outside the create tableCREATE INDEX someTable_Date_create ON someTable USING btree (date_create);As you are working on transferring, maybe you like to drop those varchar(xx) and replace them with text. Saves a lot of hassle lateron. HaraldOn 8/16/06, louis gonzales [EMAIL PROTECTED] wrote: Hello List,PostgreSQL 8.0.1 (on Solaris 9)There is a PERL program that a friend purchased which is used to createtables on a MySQL database, and of course ;) I want to run this on aPostgreSQL database server instead.The below is the code: $sth=runSQL(CREATE TABLE someTable (date_create bigint NOT NULL,date_end bigint NOT NULL,username VARCHAR(20) NOT NULL,$cat_definition id serial PRIMARY KEY,status VARCHAR(20) NOT NULL,$adfields visibility TEXT NOT NULL, priority TEXT NOT NULL, template TEXT NOT NULL, view bigint DEFAULT 0 NOT NULL, reply bigint DEFAULT 0 NOT NULL, save bigint DEFAULT 0 NOT NULL, updated bigint, photo VARCHAR(1) NOT NULL DEFAULT '0', INDEX(username), $cat_index INDEX(date_create) ););What my question is, the INDEX(...) function calls, which work this way on MySQL, don't work in PostgreSQL.Does anybody know what asynonymous way to modify the above code, for compatibility with PostgreSQL?FYI:yourVariableINT UNSIGNED AUTO_INCREMENT(MySQL)can be replaced by yourVariable serialThanks group!--Email:[EMAIL PROTECTED]WebSite:http://www.linuxlouis.net Open the pod bay doors HAL! -2001: A Space OdysseyGood morning starshine, the Earth says hello. -Willy Wonka---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart 0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] Is it possible (postgresql/mysql)
Louis,there may be an a unimportant performance penality for varchar, because of filling up and whatever. But the real reason: as much as I researched, NOBODY stated that varchar2 would be faster or better; but it limits the length. And I spend to much time in working around fixed field lengths in my life; and I saw enough people being frustrated and wasting time, especially for fields like name. i.E.: in germany the ZIP-code was extended from 4 to 5 digits. Can you imagine HOW MANY applications had to be fixed?On the other hand I use the TEXT type to argue when somebody wants me to port my application to Oracle :) ... yes, of course. You need Oracle Enterprise Edition because of Table Partitioning; and we need to refactor all text fields so they can deal with Oracles limited VARCHAR2 type, or you simply accept that we will only use the first xxx chars. Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
Re: [GENERAL] Weird join result
Peter,This returns 1280 rows, none of which are have a NULL value for sum. This surprised me at first as I thought the WHERE clause should apply before theOUTER JOIN but apparently not. I then tried the following:what gives you the impression that WHERE clauses should be applied before the JOINs ? Clearly that is different. First joins, then where propably you want sth like: SELECT phones.CALLINGSTATIONID, sum(radacct.acctinputoctets)FROM phonesLEFT OUTER JOIN (select * from raddact where radacct.ACCTSTARTTIME BETWEEN '2006-05-17 15:16:42' AND '2006-08-16 15:16:42' OR radacct.ACCTSTARTTIME ISNULL) raddact ON (phones.CALLINGSTATIONID =radacct.CALLINGSTATIONID ) WHERE phones.CUSTID = 1 GROUP BY phones.CALLINGSTATIONID; best wishes Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.
