Re: [GENERAL] UUID-OSSP for windows.
On Wed, Feb 27, 2008 at 3:44 AM, Tim Uckun [EMAIL PROTECTED] wrote: Where can I get uuid-ossp for windows? Also where can I get citext for windows. These two are missing from the windows installer. uuid-ossp is missing because until a few days ago the underlying uuid-ossp wasn't ported to Windows. citext is not part of core PostgreSQL or something we have any intention to include in the Windows distribution at this time. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The HOT PostgreSQL Company ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] citext in windows.
On Wed, Feb 27, 2008 at 11:11:52AM +1300, Tim Uckun wrote: I just downloaded postgres 8.3 for windows and noticed that citext is not an option for columns. The web site has the source code but no binaries for windows. That is something you need to talk to the citext creators about. Ideally, they shold provide a stackbuilder compatible binary download for windows. I downloaded the enterprisedb and noticed that it has the citext.dll in the lib directory so I copied the dll over to the postgres lib directory and tried to run the sql to install the type buy no joy. Postgres complains it can't load the DLL. I triple checked it and it's in the proper place. EnterpriseDB is a different product from PostgreSQL. There may be some moduels that work in both, but don't count on it. For one thing I think the latest EDB is based on 8.2, not 8.3. But there could be a lot of other reasons as well. If you want a module to load in PostgreSQL, it has to be a PostgreSQL module. What do I have to do in order to get citext to work on windows? I think you need to talk to the citext people. Unless somebody else has already made a package available somewhere? //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] win32: how to backup (dump does not work)
On Tue, Feb 26, 2008 at 11:51:34PM -, Joris Dobbelsteen wrote: AFAIK stopping the server, zipping data dir, and restarting the server creates a zip file which is not easily portable to other computers due to some ntfs file system permission problems. IIRC you have to re-assign owner and change permissions after you copy your data dir to the new server. I believe something like NTBackup (or any decent windows backup tool) can properly restore permissions. However, this will only work with centralized authentication (with NT domain) and NO local computer accounts used, when moving between servers (for obvious reasons). I think even WinRAR contains an option to do so, but cannot remember for sure and I never tried it anyways. Actually, it will work for local oens as well, as long as you only use built-in principals. However, the pg install by default uses a custmo account which, as you say, will not be portable to a different machine. In cause you mean the permissions prevent you from making a backup, this should be a smaller problem if you are infact a backup operator and use a windows backup tool (they get more rights when requested). If you are a backup operator, it shouldn't be a problem *at all*. If it is, something is wrong. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year
Bruno Baguette wrote:
Hello !
I have a week number (ISO 8601) and a year, based on theses values, I
would like to get the first and the last dates of that week.
Broken down step-by-step. End of week left as an exercise
SELECT
doy,
EXTRACT(dow FROM doy) AS offset,
(doy - EXTRACT(dow FROM doy) * '1 day'::interval)::date AS start_of_week
FROM
(SELECT ('2008-01-04'::date + 8 * '1 week'::interval)::date AS doy)
AS foo;
doy | offset | start_of_week
++---
2008-02-29 | 5 | 2008-02-24
(1 row)
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] win32: how to backup (dump does not work)
On Tue, Feb 26, 2008 at 10:30:02PM +0100, Gevik Babakhani wrote: AFAIK stopping the server, zipping data dir, and restarting the server creates a zip file which is not easily portable to other computers due to some ntfs file system permission problems. IIRC you have to re-assign owner and change permissions after you copy your data dir to the new server. You don't need to reassign owner normally - we don't care about that. And if you extract into an existing directory, it should inherit it fine :-) But true, if you extract into a nwe directory, you may need to change the permissions. //Magnus ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How can I get the first and last date of a week, based on the week number and the year
On Feb 27, 2008, at 2:32 AM, Bruno Baguette wrote:
Hello !
I have a week number (ISO 8601) and a year, based on theses values,
I would like to get the first and the last dates of that week.
How I can do that ?
The only solution is doing a big generate_series to build a subset
that contains the week of all the dates between the 01/01 || year
and the 31/12 || year. But I find that solution quite dirty and
ressources consumming.
Is there a cleanest way to do that ?
You can use to_date for most of that, like:
development= select to_date('01 02 2008', 'ID IW ') AS start,
to_date('07 02 2008', 'ID IW ') AS end;
start|end
+
2008-01-07 | 2008-01-07
(1 row)
I'm a bit surprised that specifying the weekdays doesn't make any
difference here, maybe it's my version?:
development= select version();
version
PostgreSQL 8.2.4 on i386-portbld-freebsd6.2, compiled by GCC cc
(GCC) 3.4.6 [FreeBSD] 20060305
(1 row)
Anyway, you can solve that by adding an interval '6 days' to the end
result.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,47c525af233091991417831!
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] dump/restore on different locale
I just got aware of the difficulties of restoring db from different locales (money data type). Even if I was aware money was deprecated it was used just for a temp table that I forgot to drop before dump. I do have to move stuff from boxes with different locale. Should I be aware of other problems? -- Ivan Sergio Borgonovo http://www.webthatworks.it ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Slow query with 100% cpu
I just ANALYZEd the database and times are back to normal. Sorry for the noise.
Regards, Clodoaldo Pinto Neto
2008/2/27, Clodoaldo [EMAIL PROTECTED]:
Postgresql 8.2.6, Fedora 8, 2 GB memory.
A query that used to perform in a few seconds is now taking 64 seconds
with 100% cpu:
fahstats= explain analyze
fahstats- select
fahstats- donor::smallInt as new_members,
fahstats- active_members,
fahstats- d.data::date as day,
fahstats- isodow(d.data::date)::smallInt as dow
fahstats- from
fahstats- team_active_members_history as tam
fahstats- inner join
fahstats- datas as d on d.data_serial = tam.serial_date
fahstats- inner join
fahstats- select_new_members(13802, 8) as nm on nm.data::date =
d.data::date
fahstats- where
fahstats- team_number = 13802
fahstats- and
fahstats- d.data_serial = (
fahstats( select data_serial
fahstats( from datas
fahstats( where date_trunc('day', data) = date_trunc('day', d.data)
fahstats( order by data desc
fahstats( limit 1
fahstats( )
fahstats- ;
QUERY PLAN
--
Hash Join (cost=46026.35..120281.84 rows=1020 width=16) (actual
time=64110.099..64404.762 rows=56 loops=1)
Hash Cond: ((d.data)::date = (nm.data)::date)
- Hash Join (cost=46001.35..120154.33 rows=204 width=12) (actual
time=3378.246..63602.774 rows=812 loops=1)
Hash Cond: (d.data_serial = tam.serial_date)
- Index Scan using data_ndx on datas d
(cost=0.00..73990.18 rows=32 width=12) (actual time=19.474..63583.966
rows=879 loops=1)
Filter: (data_serial = (subplan))
SubPlan
- Limit (cost=0.00..11.69 rows=1 width=12) (actual
time=10.076..10.078 rows=1 loops=6302)
- Index Scan Backward using pk_data on datas
(cost=0.00..374.05 rows=32 width=12) (actual time=10.070..10.070
rows=1 loops=6302)
Filter: (date_trunc('day'::text, data) =
date_trunc('day'::text, $0))
- Hash (cost=45499.27..45499.27 rows=40167 width=8)
(actual time=10.398..10.398 rows=821 loops=1)
- Bitmap Heap Scan on team_active_members_history tam
(cost=664.25..45499.27 rows=40167 width=8) (actual time=0.428..9.110
rows=821 loops=1)
Recheck Cond: (team_number = 13802)
- Bitmap Index Scan on
ndx_team_active_members_history (cost=0.00..654.21 rows=40167
width=0) (actual time=0.314..0.314 rows=821 loops=1)
Index Cond: (team_number = 13802)
- Hash (cost=12.50..12.50 rows=1000 width=12) (actual
time=794.528..794.528 rows=56 loops=1)
- Function Scan on select_new_members nm (cost=0.00..12.50
rows=1000 width=12) (actual time=794.356..794.411 rows=56 loops=1)
Total runtime: 64405.040 ms
(18 rows)
I REINDEXed the database before this explain analyze. These are the
non commented lines of postgresql.conf
listen_addresses = '127.0.0.1,10.1.1.101' # what IP
address(es) to listen on;
max_connections = 150 # (change requires restart)
shared_buffers = 64MB # min 128kB or max_connections*16kB
max_prepared_transactions = 150 # can be 0 or more
work_mem = 32MB # min 64kB
maintenance_work_mem = 768MB# min 1MB
max_stack_depth = 4MB # min 100kB
max_fsm_pages = 100 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 1000# 0-1000 milliseconds
vacuum_cost_limit = 5000# 0-1 credits
bgwriter_lru_maxpages = 0 # 0-1000 buffers max written/round
bgwriter_all_maxpages = 0 # 0-1000 buffers max written/round
fsync = off # turns forced synchronization on or
off
full_page_writes = off
wal_buffers = 1024kB# min 32kB
checkpoint_segments = 8 # in logfile segments, min 1, 16MB
each
checkpoint_timeout = 10min # range 30s-1h
effective_cache_size = 768MB
geqo_effort = 8 # range 1-10
default_statistics_target = 50 # range 1-1000
redirect_stderr = on# Enable capturing of stderr into log
log_directory = 'pg_log'# Directory where log files are
written
log_truncate_on_rotation = on # If on, any existing log file of
the same
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
debug_pretty_print = on
datestyle = 'iso, ymd'
timezone = UTC # actually, defaults to TZ
extra_float_digits = 2 # min -15, max 2
Re: [GENERAL] Query meltdown: caching results
On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
Norman Peelman [EMAIL PROTECTED] writes:
My options are, as far as I can tell,
1) replace the Database PDO extending class with something else that
provides query results caching in PHP, or
2) get Postgres itself to cache the results of queries to avoid
running them repeatedly during a session.
You might consider looking at memcached. One way to use it would be to have
the PHP application check for the cached object first and use it rather than
do any database queries. Then you can use pgmemcached to allow triggers to
invalidate cached objects whenever the underlying data changes. (Or you could
even just use pl/php to update or invalidate the cached object through the
same code library)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Thanks for the replies, but the problem is really centered around how
my script interacts with the database. I know what the problem is,
and if I was using a structured approach I could figure out
workarounds fairly quickly, but I chose to do this project in OOP for
a few reasons (the old CMS was hacked together in a hurry by an
employee who is now long gone, is horribly written and completely
unmaintainable, the size of the project seemed to warrant an OOP
approach, we recently upgraded to PHP 5 and I wanted to take advantage
of the new features, PDO and prepared statements appeared from the
documentation to offer major security and performance enhancements,
and I wanted to increase my experience with OOP development as most
work I've done before now has been structured) and the high level of
compartmentalization that OOP demands is coming back to haunt me
now.
The problem comes when a document is published or unpublished. All
the documents that relate to that document need to be republished to
add or remove the link to the document in question. When the document
is published or unpublished the script gets related documents (at the
moment other documents that share the same folder, but this may change
in the future to cover more related content) and republishes them.
Each document has associated data, such as the ID of the template to
apply, its parent, its path back to root, etc, that are generated by
querying the database in various ways. For example, the route back to
path is fetched by iterativly getting the parent folder, getting that
folder's parent, etc until the script hits the route. Templates are
fetched by looking ath the template ID associated with the document.
If this is 0, then the script goes to the parent folder and gets the
template associated with the folder. If that is 0 as well then it
iterativly goes up until it finds a template to apply or until it hits
the root, in which case it applies a default template. The code
fragment from the script that does this looks like this:
$db - beginTransaction ();
if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET ['itm_id']),
$db, $user))
{
if ((($doc - itemProps ['itm_publish']) ($doc - unpublish
()))
|| ($doc- publish ()))
{
// Republish related documents
foreach ($doc - getSiblingObjects () as $thisDoc)
{
if ((get_class ($thisDoc) == 'CmsDoc')
($thisDoc- itemProps ['itm_publish']))
{
$thisDoc- republish ();
}
}
// Item status changed
$db - commit ();
$_SESSION ['messageStack'][]= ($doc - itemProps
['itm_publish']?
'Item published':
'Item unpublished');
}
else
{
// Couldn't change published status
$db - rollback ();
$_SESSION ['messageStack'][]= ($doc - itemProps
['itm_publish']?
'Unable to unpublish item':
'Unable to publish item');
}
}
GetSiblingObjects () runs a query that gets a list of IDs that share
the same parent as the current document. It then iterates the list
and spawns a new CMS item for each item in the list and returns them
as an array. As folders could be returned as well as documents we
only run republish () on those items.
CmsDoc - publish () and CmsDoc - unpublish () toggle a boolean
column in the database between true and false for the item being (un)
published. unpublish () also deletes the concrete file associated
with the DB entry.
publish () and republish () write out a concrete HTML file based on
the content stored in the table for the document in question and the
template that should be applied.
[GENERAL] Slow query with 100% cpu
Postgresql 8.2.6, Fedora 8, 2 GB memory.
A query that used to perform in a few seconds is now taking 64 seconds
with 100% cpu:
fahstats= explain analyze
fahstats- select
fahstats- donor::smallInt as new_members,
fahstats- active_members,
fahstats- d.data::date as day,
fahstats- isodow(d.data::date)::smallInt as dow
fahstats- from
fahstats- team_active_members_history as tam
fahstats- inner join
fahstats- datas as d on d.data_serial = tam.serial_date
fahstats- inner join
fahstats- select_new_members(13802, 8) as nm on nm.data::date = d.data::date
fahstats- where
fahstats- team_number = 13802
fahstats- and
fahstats- d.data_serial = (
fahstats( select data_serial
fahstats( from datas
fahstats( where date_trunc('day', data) = date_trunc('day', d.data)
fahstats( order by data desc
fahstats( limit 1
fahstats( )
fahstats- ;
QUERY PLAN
--
Hash Join (cost=46026.35..120281.84 rows=1020 width=16) (actual
time=64110.099..64404.762 rows=56 loops=1)
Hash Cond: ((d.data)::date = (nm.data)::date)
- Hash Join (cost=46001.35..120154.33 rows=204 width=12) (actual
time=3378.246..63602.774 rows=812 loops=1)
Hash Cond: (d.data_serial = tam.serial_date)
- Index Scan using data_ndx on datas d
(cost=0.00..73990.18 rows=32 width=12) (actual time=19.474..63583.966
rows=879 loops=1)
Filter: (data_serial = (subplan))
SubPlan
- Limit (cost=0.00..11.69 rows=1 width=12) (actual
time=10.076..10.078 rows=1 loops=6302)
- Index Scan Backward using pk_data on datas
(cost=0.00..374.05 rows=32 width=12) (actual time=10.070..10.070
rows=1 loops=6302)
Filter: (date_trunc('day'::text, data) =
date_trunc('day'::text, $0))
- Hash (cost=45499.27..45499.27 rows=40167 width=8)
(actual time=10.398..10.398 rows=821 loops=1)
- Bitmap Heap Scan on team_active_members_history tam
(cost=664.25..45499.27 rows=40167 width=8) (actual time=0.428..9.110
rows=821 loops=1)
Recheck Cond: (team_number = 13802)
- Bitmap Index Scan on
ndx_team_active_members_history (cost=0.00..654.21 rows=40167
width=0) (actual time=0.314..0.314 rows=821 loops=1)
Index Cond: (team_number = 13802)
- Hash (cost=12.50..12.50 rows=1000 width=12) (actual
time=794.528..794.528 rows=56 loops=1)
- Function Scan on select_new_members nm (cost=0.00..12.50
rows=1000 width=12) (actual time=794.356..794.411 rows=56 loops=1)
Total runtime: 64405.040 ms
(18 rows)
I REINDEXed the database before this explain analyze. These are the
non commented lines of postgresql.conf
listen_addresses = '127.0.0.1,10.1.1.101' # what IP address(es)
to listen on;
max_connections = 150 # (change requires restart)
shared_buffers = 64MB # min 128kB or max_connections*16kB
max_prepared_transactions = 150 # can be 0 or more
work_mem = 32MB # min 64kB
maintenance_work_mem = 768MB# min 1MB
max_stack_depth = 4MB # min 100kB
max_fsm_pages = 100 # min max_fsm_relations*16, 6 bytes each
vacuum_cost_delay = 1000# 0-1000 milliseconds
vacuum_cost_limit = 5000# 0-1 credits
bgwriter_lru_maxpages = 0 # 0-1000 buffers max written/round
bgwriter_all_maxpages = 0 # 0-1000 buffers max written/round
fsync = off # turns forced synchronization on or off
full_page_writes = off
wal_buffers = 1024kB# min 32kB
checkpoint_segments = 8 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 10min # range 30s-1h
effective_cache_size = 768MB
geqo_effort = 8 # range 1-10
default_statistics_target = 50 # range 1-1000
redirect_stderr = on# Enable capturing of stderr into log
log_directory = 'pg_log'# Directory where log files are written
log_truncate_on_rotation = on # If on, any existing log file of the
same
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
debug_pretty_print = on
datestyle = 'iso, ymd'
timezone = UTC # actually, defaults to TZ
extra_float_digits = 2 # min -15, max 2
lc_messages = 'en_US.UTF-8' # locale for system error
message
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
Same query
[GENERAL] How to paste two tables side-by-side?
Suppose I have two tables, A and B, with k(A) and k(B) columns respectively, and let's assume to begin with that they have the same number of rows r(A) = r(B) = r. What's the simplest way to produce a table C having r rows and k(A) + k(B) columns, and whose i-th row consists of the k(A) columns of the i-th row of A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)? (By i-th row of A I mean the i-th row of the listing one would get from SELECT * FROM A, and likewise for B.) The question could be generalized slightly to the case where the numbers of rows r(A) and r(B) are not equal. For example, if r(A) r(B), the desired table C would have r(B) rows, and the first k(A) columns of its last r(B) - r(A) rows would be nulls, reminiscent of a table produced by a right outer join. Also, what's the technical term for this type of operation on two tables? Thanks! kynn
Re: [GENERAL] How to paste two tables side-by-side?
On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones [EMAIL PROTECTED] wrote: Suppose I have two tables, A and B, with k(A) and k(B) columns respectively, and let's assume to begin with that they have the same number of rows r(A) = r(B) = r. What's the simplest way to produce a table C having r rows and k(A) + k(B) columns, and whose i-th row consists of the k(A) columns of the i-th row of A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)? (By i-th row of A I mean the i-th row of the listing one would get from SELECT * FROM A, and likewise for B.) Expanding on my own post here, it occurred to me that it would be very nice to have a function (say) index, that, when used in a SELECT list, would yield the position in the corresponding table of the current row. E.g. the expression SELECT index(*) FROM A; would produce the same table as SELECT generate_series( 1, r(A) ); It would also be useful to have a subscripting function s (which may be regarded as somewhat of the inverse of index()) that, given a table expression E, and an index expression I (which could be a single index or range, or a list of such), will return the table consisting of the rows in E designated by the indices in I). For example, either one these queries SELECT s( A, 1, 2, 3 ); SELECT * FROM s( A, 1, 2, 3 ); would produce the same table as SELECT * FROM A LIMIT 3; Does anything like index() or s() exist? If not, are there other functions that may be useful in an implementation of index() or s()? Thanks! Kynn
Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0
Stuart Brooks wrote: Are you measuring index as well as table size? VACUUM FULL is no good at compacting indexes. I am measuring pg_total_relation_size which I believe includes indexes. How does one go about compacting indexes if a VACUUM doesn't do the trick? I see that a recommendation is to drop and recreate the indexes. If one has a system running 24-7, then this might not be feasible. The simplest way is to use REINDEX INDEX, but it needs a strong lock. The more complex way is to do CREATE INDEX CONCURRENTLY index_2 ...-- duplicating the original index DROP INDEX index; which does not need to grab a lock for a long period. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL-installation-problem on Windows XP Home edition
Dear Sirs. I have a problem with installing PostgreSQL 8.3 on my computer with Windows XP Home edition and AVG FREE antivirus 7.5. The installation-program asks for a installing-language and to save the installation-process in a logfile. When I choose English and press the button Start, the installation-program writes the message Prepare to install in a short period, and after that nothing is happening. What is wrong ??? I enclose the logfile. My e-mail-address is : [EMAIL PROTECTED] I hope to hear from you. Best regards. Jan Christensen - Find din nye laptop på kelkoo.dk. Se de gode tilbud her! postgresql-8.3.log Description: 99633930-postgresql-8.3.log ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] autovacuum not freeing up unused space on 8.3.0
Are you measuring index as well as table size? VACUUM FULL is no good at compacting indexes. I am measuring pg_total_relation_size which I believe includes indexes. How does one go about compacting indexes if a VACUUM doesn't do the trick? I see that a recommendation is to drop and recreate the indexes. If one has a system running 24-7, then this might not be feasible. The simplest way is to use REINDEX INDEX, but it needs a strong lock. The more complex way is to do CREATE INDEX CONCURRENTLY index_2 ...-- duplicating the original index DROP INDEX index; which does not need to grab a lock for a long period. That does the trick and gets the table size down to what I'd expect from a 'clean' run. Now I just need to run a few tests to work out what a stable size is for a table with this many rows. Thanks for all the help tracking this down. It's really appreciated :) Kind regards Stuart ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL-installation-problem on Windows XP Home edition
On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote: Jan Christensen wrote: Dear Sirs. I have a problem with installing PostgreSQL 8.3 on my computer with Windows XP Home edition and AVG FREE antivirus 7.5. The installation-program asks for a installing-language and to save the installation-process in a logfile. When I choose English and press the button Start, the installation-program writes the message Prepare to install in a short period, and after that nothing is happening. What is wrong ??? I enclose the logfile. The error seems to be this: DEBUG: Error 2769: Custom Action GetAvailableLocales did not close 1 MSIHANDLEs. The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2769. The argumens are: GetAvailableLocales, 1, Action ended 14:20:50: GetAvailableLocales. Return value 1. I'm guessing that you're missing some of the locale (language) files for your machine. Have you got anything unusual selected in your language settings? That's not actually the error. That happens everywhere :-( For some reason I couldn't view that logfile at all, but look for something that started and never finished. This one has the Action ended statement for GetAvailableLocales, my guess is that there is something further down that has a start but no end. (And for the record, we do close all the MSIHANDLEs, that's an API bug at some point) //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] win32: how to backup (dump does not work)
On Wed, Feb 27, 2008 at 03:59:24PM +0100, [EMAIL PROTECTED] wrote:
On 22:37 Tue 26 Feb , Magnus Hagander wrote:
[EMAIL PROTECTED] wrote:
AFAIK stopping the server, zipping data dir, and restarting the server
creates a zip file which is not easily portable to other computers due
to some ntfs file system permission problems.
What exactly would those problems be? If you can shut your server down like
this, that's absolutely the easiest way to get it done. It should be
portable across all win32 machines at least - and if that's not enough,
FYI (worked around):
issue (1) may have a little relation to the way pg-installer sets the
permissions, issue (2) is just a pitfall:
(1)
I've simply tried to stop the server, copied the 'data' directory,
(renamed old one data.bak), started server on a standard stand-alone
WinXP (SP2) machine: compared to the working 'data.bak' directory,
the copied 'data' folder for some reason gets an additional
permissions entry for the postgres user (see security options of
directory 'data') which is inherited from the pg's parent directory
('8.2' version directory in my case): this entry removes some
permissions so that the effective permissions for user postgres
are not enough anymore. Also, when viewing the directories permissions
of the copied dir, it warns that the order of the permissions are not
correct and therefore probably not usable.
I'm not an expert in win32, but the workaround was to disable
inheritance for the new data dir, remove the extra entry, and set
permissions for the postgres user to somewhat full (including
subfolders).
That is the proper fix. We apply a deny permission on user postgres so it
cannot change it's own binaries, in case of an exploit. Then we explicitly
remove this deny permission on the data directory - but if you delete it
and recreate it (or rename and recreate), it will inherit the deny
permission again.
(2)
The pg service is not started if some empty folders are missing. Some
zip progs, however, do not zip/unzip these folders by default. So, one
has to make sure that these folders are included. The pg server adds a
hint into windows event log.
Ah, that's interesting. That means you need to take care what ZIP program
you use :-)
//Magnus
---(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
[GENERAL] Regarding interval conversion functions and a seeming lack of usefulness
This has come up a few times over the last few months, and I'm not
too keen on the solutions we've been using.
There seems to be a lack of useful functions for converting intervals
to useful representations. For example, I want to display an interval
in hours and fractions of hours only, not hours and minutes. There
are lots of examples of when certain representations are more appropriate
than others (think pregnancy terms, why on earth is the woman never
pregnant for 3 months? It's always 12 weeks!)
Anyway, rant aside, I can't seem to find anything to do this in PG.
I could write a stored procedure easily enough, but I want to make
sure I'm not reinventing the wheel first (I find it hard to believe
I'm the first person ever wanting to do this!)
Something like:
$ SELECT CONVERT('12 days 13 hours'::INTERVAL AS hour);
hour
--
301
$ SELECT CONVERT('6 hours 17 minutes'::INTERVAL AS hour);
hour
--
6.2833
Am I approaching this problem wrong? or is there something out there
and my Google skills are lacking?
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
[EMAIL PROTECTED]
Phone: 412-422-3463x4023
---(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] PostgreSQL-installation-problem on Windows XP Home edition
Jan Christensen wrote: Dear Sirs. I have a problem with installing PostgreSQL 8.3 on my computer with Windows XP Home edition and AVG FREE antivirus 7.5. The installation-program asks for a installing-language and to save the installation-process in a logfile. When I choose English and press the button Start, the installation-program writes the message Prepare to install in a short period, and after that nothing is happening. What is wrong ??? I enclose the logfile. The error seems to be this: DEBUG: Error 2769: Custom Action GetAvailableLocales did not close 1 MSIHANDLEs. The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2769. The argumens are: GetAvailableLocales, 1, Action ended 14:20:50: GetAvailableLocales. Return value 1. I'm guessing that you're missing some of the locale (language) files for your machine. Have you got anything unusual selected in your language settings? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL-installation-problem on Windows XP Home edition
On Wed, Feb 27, 2008 at 02:58:22PM +, Richard Huxton wrote: Magnus Hagander wrote: On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote: I'm guessing that you're missing some of the locale (language) files for your machine. Have you got anything unusual selected in your language settings? That's not actually the error. That happens everywhere :-( Ah - it was near the end of the file, and the only error I could see. For some reason I couldn't view that logfile at all, I had that - I'm guessing there are high-bit chars that shouldn't be there. Didn't stop vi though :-) It stopped my vi :-( but look for something that started and never finished. This one has the Action ended statement for GetAvailableLocales, my guess is that there is something further down that has a start but no end. This is the end of the file. The only un-ended action I can find is CheckAvailable DLLs MSI (c) (A4:AC): Doing action: LaunchConditions Action start 14:20:50: LaunchConditions. Action ended 14:20:50: LaunchConditions. Return value 1. MSI (c) (A4:AC): Doing action: CheckAvailableDLLs Action start 14:20:50: CheckAvailableDLLs. MSI (c) (A4:AC): Creating MSIHANDLE (164) of type 790542 for thread 2988 Ok, that's a known issue. See: http://www.postgresql.org/docs/faqs.FAQ_windows.html#3.1 particularly the part about cygwin - that's most often the case. But the other parts also apply. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL-installation-problem on Windows XP Home edition
Magnus Hagander wrote: On Wed, Feb 27, 2008 at 02:41:09PM +, Richard Huxton wrote: I'm guessing that you're missing some of the locale (language) files for your machine. Have you got anything unusual selected in your language settings? That's not actually the error. That happens everywhere :-( Ah - it was near the end of the file, and the only error I could see. For some reason I couldn't view that logfile at all, I had that - I'm guessing there are high-bit chars that shouldn't be there. Didn't stop vi though :-) but look for something that started and never finished. This one has the Action ended statement for GetAvailableLocales, my guess is that there is something further down that has a start but no end. This is the end of the file. The only un-ended action I can find is CheckAvailable DLLs MSI (c) (A4:AC): Doing action: LaunchConditions Action start 14:20:50: LaunchConditions. Action ended 14:20:50: LaunchConditions. Return value 1. MSI (c) (A4:AC): Doing action: CheckAvailableDLLs Action start 14:20:50: CheckAvailableDLLs. MSI (c) (A4:AC): Creating MSIHANDLE (164) of type 790542 for thread 2988 -- Richard Huxton Archonet Ltd ---(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] Regarding interval conversion functions and a seeming lack of usefulness
Bill Moran [EMAIL PROTECTED] writes: There seems to be a lack of useful functions for converting intervals to useful representations. For example, I want to display an interval in hours and fractions of hours only, not hours and minutes. Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some arithmetic? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Query meltdown: caching results
Gordon wrote: On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote: Norman Peelman [EMAIL PROTECTED] writes: My options are, as far as I can tell, 1) replace the Database PDO extending class with something else that provides query results caching in PHP, or 2) get Postgres itself to cache the results of queries to avoid running them repeatedly during a session. You might consider looking at memcached. One way to use it would be to have the PHP application check for the cached object first and use it rather than do any database queries. Then you can use pgmemcached to allow triggers to invalidate cached objects whenever the underlying data changes. (Or you could even just use pl/php to update or invalidate the cached object through the same code library) -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! ---(end of broadcast)--- TIP 6: explain analyze is your friend The problem is that there is a vast number of redundant queries being run. the principle that objects should not depend on a knowledge of the inner workings of unrelated objects. Results caching would eliminate the problem of the same queries beign run over and over The problem is the mechanics of actually implementing this caching. I'm using prepared statements almost exclusivly throughout the design, meaning that the PDOStatement class probably needs to be extended somehow and my Database prepare() I can't have been the first person to run up against this problem With memcached, your methods to retrieve data go from get data from db to get data from cache, and on cache miss get from db and leave a copy for the next guy in cache. Updating the data is not much more complicated. I don't see why this doesn't work for you? It won't compromise anything on the encapsulation front you are concerned about, and you can still use your prepared statements for hitting the db, etc.? Regards, Paul ---(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] Query meltdown: caching results
On Feb 27, 10:37 am, Gordon [EMAIL PROTECTED] wrote:
On Feb 26, 5:26 pm, [EMAIL PROTECTED] (Gregory Stark) wrote:
Norman Peelman [EMAIL PROTECTED] writes:
My options are, as far as I can tell,
1) replace the Database PDO extending class with something else that
provides query results caching in PHP, or
2) get Postgres itself to cache the results of queries to avoid
running them repeatedly during a session.
You might consider looking at memcached. One way to use it would be to have
the PHP application check for the cached object first and use it rather than
do any database queries. Then you can use pgmemcached to allow triggers to
invalidate cached objects whenever the underlying data changes. (Or you
could
even just use pl/php to update or invalidate the cached object through the
same code library)
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Thanks for the replies, but the problem is really centered around how
my script interacts with the database. I know what the problem is,
and if I was using a structured approach I could figure out
workarounds fairly quickly, but I chose to do this project in OOP for
a few reasons (the old CMS was hacked together in a hurry by an
employee who is now long gone, is horribly written and completely
unmaintainable, the size of the project seemed to warrant an OOP
approach, we recently upgraded to PHP 5 and I wanted to take advantage
of the new features, PDO and prepared statements appeared from the
documentation to offer major security and performance enhancements,
and I wanted to increase my experience with OOP development as most
work I've done before now has been structured) and the high level of
compartmentalization that OOP demands is coming back to haunt me
now.
The problem comes when a document is published or unpublished. All
the documents that relate to that document need to be republished to
add or remove the link to the document in question. When the document
is published or unpublished the script gets related documents (at the
moment other documents that share the same folder, but this may change
in the future to cover more related content) and republishes them.
Each document has associated data, such as the ID of the template to
apply, its parent, its path back to root, etc, that are generated by
querying the database in various ways. For example, the route back to
path is fetched by iterativly getting the parent folder, getting that
folder's parent, etc until the script hits the route. Templates are
fetched by looking ath the template ID associated with the document.
If this is 0, then the script goes to the parent folder and gets the
template associated with the folder. If that is 0 as well then it
iterativly goes up until it finds a template to apply or until it hits
the root, in which case it applies a default template. The code
fragment from the script that does this looks like this:
$db - beginTransaction ();
if ($doc= CmsItem::factory ('CmsDoc', intval ($_GET
['itm_id']),
$db, $user))
{
if ((($doc - itemProps ['itm_publish']) ($doc -
unpublish ()))
|| ($doc- publish ()))
{
// Republish related documents
foreach ($doc - getSiblingObjects () as $thisDoc)
{
if ((get_class ($thisDoc) == 'CmsDoc')
($thisDoc- itemProps ['itm_publish']))
{
$thisDoc- republish ();
}
}
// Item status changed
$db - commit ();
$_SESSION ['messageStack'][]= ($doc - itemProps
['itm_publish']?
'Item published':
'Item unpublished');
}
else
{
// Couldn't change published status
$db - rollback ();
$_SESSION ['messageStack'][]= ($doc - itemProps
['itm_publish']?
'Unable to unpublish item':
'Unable to publish item');
}
}
GetSiblingObjects () runs a query that gets a list of IDs that share
the same parent as the current document. It then iterates the list
and spawns a new CMS item for each item in the list and returns them
as an array. As folders could be returned as well as documents we
only run republish () on those items.
CmsDoc - publish () and CmsDoc - unpublish () toggle a boolean
column in the database between true and false for the item being (un)
published. unpublish () also deletes the concrete file associated
with the DB entry.
publish
Re: [GENERAL] Regarding interval conversion functions and a seeming lack of usefulness
In response to Tom Lane [EMAIL PROTECTED]: Bill Moran [EMAIL PROTECTED] writes: There seems to be a lack of useful functions for converting intervals to useful representations. For example, I want to display an interval in hours and fractions of hours only, not hours and minutes. Perhaps EXTRACT(EPOCH ...) to get total seconds and then do some arithmetic? Well, that's more or less what I've been doing (although the EPOCH thing shortened the code up a bit, thanks!). It just seemed like this would be something so common that there'd be something in existence already. I guess I was wrong. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] help for loading a psql file
hello i am new to psql or any database stuff. i have downloaded an .psql file from internet and wish to open it and see the data inside. i am working on windows xp and have installed the software successfully. please help i am my wits end. -- akshay uday bhat. t.y.c.e. department of chemical engineering university institute of chemical technology mumbai India On action alone be thy interest, Never on its fruits. Let not the fruits of action be thy motive, Nor be thy attachment to inaction. Ask and it shall be given to you; seek and you shall find; knock and it shall be opened to you
Re: [GENERAL] Regarding interval conversion functions and a seeming lack of usefulness
Bill Moran wrote: It just seemed like this would be something so common that there'd be something in existence already. I guess I was wrong. Yeah, I have wished for the same thing myself. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reindexing
Tom Lane wrote: There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. In my experience at various big-iron shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. -- Lew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 27/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote:
Hi Olmec,
I have encountered a similar situation that came about when I
upgraded to postgres 8.3. Now it won't start stating that:
The server must be started under the locale : which does not
exist any more
(it looks like the locale is set to {blank} ???)
Not sure what I should do here. Removing mostprest to an older version
might be the way to go?
That was Richards suggestion for Geoff who had the
problem. What OS are you running PostgreSQL on?
Olmec Sinclair
Cheers,
Andrej
--
Please don't top post, and don't use HTML e-Mail :} Make your quotes concise.
http://www.american.edu/econ/notes/htmlmail.htm
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing
Lew wrote: Tom Lane wrote: There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. In my experience at various big-iron shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. This is completely the opposite of my experience at a very large global financial company. They are extremely concerned with staying current, and in fact audit regulations require it for any software not written in-house. If they were still running Oracle 8, for example, they would fail internal audit precisely because it is no longer a supported Oracle version, and thus security and such patches are no longer available. The same would go for operating system patches, firmware, whatever. The release cycle does tend to be slower (from quarterly to yearly) for, say, things like AIX or z/OS or DB2, but updates are coming out routinely [including security and bug fixes, as well as feature additions], and in my experience these shops are definitely keeping up. The only places I've had direct experience with that tend to run very old versions of things are doing so for all the wrong reasons. They seem to be learning, albeit slowly and painfully, the demerits of not keeping current. Just my $0.02, Paul ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Query meltdown: caching results
(Sorry for the repost but I thought this would be appropriate to both
groups. I did tell Google to delete my first post but odds are some
guys got that copy already anyway)
After a lot of hairpulling, I finally found a mechanism in PHP for
doing what I wanted. I just had to know 2 things:
1) How to get the PDO engine to use my customized prepared statement
class instead of PDOStatement
2) Extending PDOStatement to transparently add results caching is too
difficult and complex in the timeframe required
Once I knew these things I made a PDOStatement extension class that
instead of trying to transparently add caching to the existing methods
added a couple of new ones instead. Code below:
?php
class Statement extends PDOStatement
{
private $resultCache= array ();
private $database = NULL;
public $hits = 0;
public $misses = 0;
public function ask (array $params = array ())
// Executes a prepared statement on the database that fetches
data
{
$hash = md5 (implode (',', $params));
if (!$this - resultCache [$hash])
{
$this - misses++;
// Execute the query
if ($this - execute ($params))
{
// Cache the results
$this - resultCache [$hash] = $this -
fetchAll
(PDO::FETCH_ASSOC);
}
}
else
{
$this - hits++;
}
return ($this - resultCache [$hash]);
}
public function tell (array $params = array ())
// Execute a prepared statement that causes the database to be
modified
{
// Execute the query
if ($this - execute ($params))
{
$rowCount = $this - rowCount ();
if ($rowCount)
{
// Tell the parent Database object to
clear statement caches
$this - database - clearResults ();
}
return ($rowCount);
}
}
public function clearResults ()
// Clear cache
{
$this - resultCache = array ();
}
private function __construct ($db)
// Class constructor
{
$this - database= $db;
//print_r ($this);
}
}
class Database extends PDO
// Adds some extra functionality to the built in PDO class
{
private $statementCache = array ();
private $txCount= 0;
private $txErr = false;
// Prepared statement cacheing
public function prepare ($statement, array $options = array
())
{
$hash = md5 ($statement);
if ((!isset ($this - statementCache [$hash]))
|| (!is_object ($this - statementCache [$hash])))
{
//echo ('Preparing statement '.
$statement .'br');
$this - statementCache [$hash] =
parent::prepare ($statement,
$options);
}
else
{
//echo ('Statement ' . $statement . '
already preparedbr');
}
return ($this - statementCache [$hash]);
}
public function clearResults ()
// Clear the results cache of all associated prepared
statements
{
foreach ($this - statementCache as $thisStatement)
{
$thisStatement - clearResults ();
}
}
// Add support for transaction nesting
public function beginTransaction ()
{
if (($this - txCount == 0) (!$this - txErr))
{
$result = parent::beginTransaction ();
}
$this - txCount ++;
if (DEBUG_TX)
{
echo ('begin: ' . $this - txCount . '
transaction(s)br /');
}
return ($result);
}
public function commit ()
{
$this - txCount --;
if ($this - txCount = 0)
{
$this - txErr? $result =
parent::rollback (): $result =
parent::commit ();
$this - txErr = false;
}
if (DEBUG_TX)
{
echo ('commit: ' . $this - txCount . '
transaction(s)br /');
}
return ($result);
}
public function rollback ()
{
$this - txErr = true;
$this -
Re: [GENERAL] reindexing
Lew [EMAIL PROTECTED] writes: Tom Lane wrote: There never was a 7.1.4 release, so I suspect the OP meant 7.4.1 not that that speaks very much better for his software maintenance habits. Even with the more charitable interpretation, it's a version that was obsoleted four years ago next week. In my experience at various big-iron shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. This is not equivalent to still using Oracle 8. This is still using Oracle 8 and we haven't applied any of Oracle's updates for it. Is it even possible for a shop to do that? I can hardly believe that Oracle would honor a support contract for a version that's missing four years worth of bug fixes. As for the not wanting to adopt too quickly argument, why'd they adopt 7.4.1 in the first place? If you're of the view that no software is acceptably stable till it's been out a couple years, you should be using something with a minor number rather higher than 1. regards, tom lane ---(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] reindexing
On Wed, 27 Feb 2008, Lew wrote: One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. Companies that act so conversatively are already getting nailed by lack of support in the public versions of software. For example, in 2007 DST was moved around in the US for no good reason, requiring an update to the Olson Timezone Database. If you're a Java user, and you're on 1.3, you couldn't get that update unless you have a support contract--the free version won't do it. (ref: http://java.sun.com/javase/timezones/DST_faq.html ) Even there only a small subset of platforms are supported. Getting older Oracle versions to work there obviously requires the appropriate support contract to see the Metalink update, and as I can tell only 8.1 was updated, people running 8.0 were left out. If some big-iron shop who is so blind to security issues that they want to keep 7.4 on life support, they certainly can find someone to deliver such a support agreement on a contract basis. But they shouldn't expect the public project to keep them afloat for free, and saying this project must be ready to handle them is quite debatable. Given the limited resources of the public volunteers here, supporting ancient versions is a drain it's hard to justify outside the context of such a support agreement. Using your own examples, Oracle and Sun sure don't, why should PostgreSQL? -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(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
[GENERAL] RHEL3 configure ssl
How to get ssl configured on RHEL3? Can't seem to hook up with openssl/ssl.h. This is 8.3.0 and it works on RHEL4: $ cat /etc/issue Red Hat Enterprise Linux AS release 3 (Taroon Update 9) Kernel \r on an \m $ rpm -qa | grep ssl docbook-style-dsssl-1.76-8 mod_ssl-2.0.46-70.ent openssl-0.9.7a-33.24 openssl-devel-0.9.7a-33.24 openssl-perl-0.9.7a-33.24 openssl096b-0.9.6b-16.46 $ rpm -ql openssl-devel | grep ssl.h /usr/include/openssl/hmac.h /usr/include/openssl/kssl.h /usr/include/openssl/ssl.h $ ls -l /usr/include/openssl/ssl.h -rw-r--r--1 root root74519 Oct 16 15:37 /usr/include/openssl/ssl.h $ ./configure --prefix=/usr/local/pgsql830 --with-openssl --with-includes=/usr/include/ checking build system type... i686-pc-linux-gnu checking host system type... i686-pc-linux-gnu checking which template to use... linux checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output file name... a.out checking whether the C compiler works... yes checking whether we are cross compiling... no checking for suffix of executables... checking for suffix of object files... o checking whether we are using the GNU C compiler... yes checking whether gcc accepts -g... yes checking for gcc option to accept ANSI C... none needed checking if gcc supports -Wdeclaration-after-statement... yes checking if gcc supports -Wendif-labels... no checking if gcc supports -fno-strict-aliasing... yes configure: using CFLAGS=-O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -fno-strict-aliasing checking whether the C compiler still works... yes checking how to run the C preprocessor... gcc -E checking allow thread-safe client libraries... no checking whether to build with Tcl... no checking whether to build Perl modules... no checking whether to build Python modules... no checking whether to build with GSSAPI support... no checking whether to build with Kerberos 5 support... no checking whether to build with PAM support... no checking whether to build with LDAP support... no checking whether to build with Bonjour support... no checking whether to build with OpenSSL support... yes checking for egrep... grep -E configure: using CPPFLAGS= -D_GNU_SOURCE -I/usr/include/ configure: using LDFLAGS= checking for ld used by GCC... /usr/bin/ld checking if the linker (/usr/bin/ld) is GNU ld... yes checking for ranlib... ranlib checking for strip... strip checking whether it is possible to strip libraries... yes checking for tar... /bin/tar checking whether ln -s works... yes checking for gawk... gawk checking for bison... bison -y configure: using bison (GNU Bison) 1.875c checking for flex... /usr/bin/flex configure: using /usr/bin/flex version 2.5.4 checking for perl... /usr/bin/perl checking for main in -lm... yes checking for library containing setproctitle... no checking for library containing dlopen... -ldl checking for library containing socket... none required checking for library containing shl_load... no checking for library containing getopt_long... none required checking for library containing crypt... -lcrypt checking for library containing fdatasync... none required checking for library containing shmget... none required checking for -lreadline... yes (-lreadline -ltermcap) checking for inflate in -lz... yes checking for CRYPTO_new_ex_data in -lcrypto... yes checking for SSL_library_init in -lssl... yes checking for ANSI C header files... yes checking for sys/types.h... yes checking for sys/stat.h... yes checking for stdlib.h... yes checking for string.h... yes checking for memory.h... yes checking for strings.h... yes checking for inttypes.h... yes checking for stdint.h... yes checking for unistd.h... yes checking crypt.h usability... yes checking crypt.h presence... yes checking for crypt.h... yes checking dld.h usability... no checking dld.h presence... no checking for dld.h... no checking fp_class.h usability... no checking fp_class.h presence... no checking for fp_class.h... no checking getopt.h usability... yes checking getopt.h presence... yes checking for getopt.h... yes checking ieeefp.h usability... no checking ieeefp.h presence... no checking for ieeefp.h... no checking langinfo.h usability... yes checking langinfo.h presence... yes checking for langinfo.h... yes checking poll.h usability... yes checking poll.h presence... yes checking for poll.h... yes checking pwd.h usability... yes checking pwd.h presence... yes checking for pwd.h... yes checking sys/ipc.h usability... yes checking sys/ipc.h presence... yes checking for sys/ipc.h... yes checking sys/poll.h usability... yes checking sys/poll.h presence... yes checking for sys/poll.h... yes checking sys/pstat.h usability... no checking sys/pstat.h presence... no checking for sys/pstat.h... no checking sys/resource.h usability... yes checking sys/resource.h presence... yes
Re: [GENERAL] reindexing
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 27 Feb 2008 11:28:32 -0500 Lew [EMAIL PROTECTED] wrote: In my experience at various big-iron shops (government agencies, large health-care organizations and the like), four years is not a long time for enterprise software - a version often has to be at least four years old before the powers-that-be decide to try it. One has only to look at how many organizations still use Oracle 8, or Java 1.3, for example, to see how conservative many shops are with respect to upgrades. Yes but Tom wasn't talking about upgrades. He was talking about maintenance. You can bet that any respectable enterprise shop is at least running the latest service packs for the respective releases. The community does support 7.4 still. However the version that is supported is service release (or service pack) 19. Thus 7.4.19. I'm not saying they should be that conservative, but many organizations are and we must be ready to deal with that. And we already do, far more than we should IMO. This idea that the volunteer community should somehow provide enterprise class support is a non starter. That is what the companies surrounding the community are for. If companies want the community and not the companies surrounding the community to provide that kind of support, those companies need to start paying for it. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHxbU/ATb/zqfZUUQRAt1hAJ9NCAK6xTQtF6hcI95rwolqlNpXoACdHIoJ IVfVRiN5PTpwxAWH6ohY1us= =L7ov -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 28/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote: Hello again! And please remember to hit reply-to-all so everyone on the list sees what you're saying. Also please note that top-posting is frowned upon here. :) I am running postgres on Ubuntu 7.10 (recent convert from windows) and I think my previous version of postgresql was 8.2 Last night I un installed 8.3 and reinstalled 8.2 but now I can't get that to start so I'm not making much progress. The problem is I don't really know what I am doing :) By which means did you do the installation/un-install of both versions? What data-directory is/was which version using? I am learning slowly though. Olmec Cheers from sunny Wellington, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reindexing
Greg Smith [EMAIL PROTECTED] writes: If some big-iron shop who is so blind to security issues that they want to keep 7.4 on life support, they certainly can find someone to deliver such a support agreement on a contract basis. But they shouldn't expect the public project to keep them afloat for free, and saying this project must be ready to handle them is quite debatable. Well, whether we *must* do it or not is arguable; but the point in this thread is that we *do* do it. The 7.4 branch is up to 7.4.19. But the OP was (apparently) still running 7.4.1, and his failure to take advantage of that free support was what I was lecturing him about ;-) regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] RHEL3 configure ssl
Ray Stell [EMAIL PROTECTED] writes: How to get ssl configured on RHEL3? Can't seem to hook up with openssl/ssl.h. This is 8.3.0 and it works on RHEL4: Weird. Did you look into config.log to see exactly why the probes for ssl.h are failing? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] reindexing
Greg Smith wrote: If some big-iron shop who is so blind to security issues that they want to keep 7.4 on life support, they certainly can find someone to deliver such a support agreement on a contract basis. But they shouldn't expect the public project to keep them afloat for free, and saying this project must be ready to handle them is quite debatable. Given the limited resources of the public volunteers here, supporting ancient versions is a drain it's hard to justify outside the context of such a support agreement. Using your own examples, Oracle and Sun sure don't, why should PostgreSQL? I am not arguing that Postgres, Oracle, Sun or anyone else should have to support such obsolete products, or that they are the only source for that support. I only state the fact that many organizations are slow to move off even obsolete products - this is something I have observed more than once in more than one contract. I only claimed in my post that we must be ready to deal with that, since it is a fact, not that vendors should have to support those products for free. For example, in my work I deal with that by strongly urging my clients not to use obsolete software, after explaining that the software in question is actually obsolete. They don't always agree with my recommendation, then I deal with that in turn. It's not like they make me their decision maker. I agree that no one should have to support obsolete products for free, and that these organizations should upgrade. -- Lew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
Andrej, Thanks for the tips (should I post this in a different way?) Anyway, I uninstalled and installed using the ubuntu package manager. I tried again this morning - removing 8.2 and then reinstalling 8.2 again... still won't start. I'm sure I can work through this (I seem to remember having a starting issue before) but the data locale thing is a worry. Cheers (from Christchurch) Olmec Andrej Ricnik-Bay wrote: On 28/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote: Hello again! And please remember to hit reply-to-all so everyone on the list sees what you're saying. Also please note that top-posting is frowned upon here. :) I am running postgres on Ubuntu 7.10 (recent convert from windows) and I think my previous version of postgresql was 8.2 Last night I un installed 8.3 and reinstalled 8.2 but now I can't get that to start so I'm not making much progress. The problem is I don't really know what I am doing :) By which means did you do the installation/un-install of both versions? What data-directory is/was which version using? I am learning slowly though. Olmec Cheers from sunny Wellington, Andrej
[GENERAL] Can't start Postgres anymore
Hallo, I have Postgres installed as a service on Windows xp pro. It has been working fine but suddenly i couldn't start it anymore. I looked in different forums for a similar problem but didn't find any solution sofar. So any help is very appreciated. one more thing to say is that i am a beginner with Postgres and don't have much administrative experience... As i said above, i can't start my Postgres anymore. I always get the error message could not connect to server: Connection refused (0x274D/10061)Is the server running on host ??? and accepting TCP/IP connections on port 5432?. I checked if the service is running, but it wasn't running. I tried to start ist but i couldn't. Doing this from the console i got the following error: C:\Programme\PostgreSQL\8.1\binpg_ctl.exe runservice -N pgsql-8.1 -D C:\Programme\PostgreSQL\8.1\data\ pg_ctl: could not start service pgsql-8.1: error code 1063 I chekced postgres.conf and the configuration is as follows: # - Connection Settings - listen_addresses = 'localhost' port = 5432 max_connections = 100 The last log file is: 2008-02-08 07:40:38 LOG: could not connect socket for statistics collector: Es konnte keine Verbindung hergestellt werden, da der Zielcomputer die Verbindung verweigerte. 2008-02-08 07:40:38 LOG: disabling statistics collector for lack of working socket 2008-02-08 07:40:38 WARNING: autovacuum not started because of misconfiguration 2008-02-08 07:40:38 HINT: Enable options stats_start_collector and stats_row_level. 2008-02-08 07:40:40 LOG: database system was shut down at 2008-02-08 07:37:57 Westeuropäische Normalzeit 2008-02-08 07:40:40 LOG: checkpoint record is at 0/45AE50 2008-02-08 07:40:40 LOG: redo record is at 0/45AE50; undo record is at 0/0; shutdown TRUE 2008-02-08 07:40:40 LOG: next transaction ID: 5100; next OID: 16515 2008-02-08 07:40:40 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-02-08 07:40:41 LOG: database system is ready 2008-02-08 07:40:41 LOG: transaction ID wrap limit is 2147484148, limited by database postgres I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432. It's been working fine for the last month since installtion and i didn't change any configuration. I stopped both zonealarm and the windows firewall but still success, so i guess it's not a firewall problem. I tried to ping the server using ping 127.0.0.1 which was successful. I tried to connect to the host with telnet using telnet 127.0.0.1 5432 but i couldn't connect. anyone has a suggestion what can i do to get it working again. I dont want to lose the data stored on it! thanks in advance George
Re: [GENERAL] RHEL3 configure ssl
On Wed, Feb 27, 2008 at 03:22:57PM -0500, Tom Lane wrote: Ray Stell [EMAIL PROTECTED] writes: How to get ssl configured on RHEL3? Can't seem to hook up with openssl/ssl.h. This is 8.3.0 and it works on RHEL4: Weird. Did you look into config.log to see exactly why the probes for ssl.h are failing? forgot... | #include openssl/ssl.h configure:10944: result: no configure:10948: checking openssl/ssl.h presence configure:10958: gcc -E -D_GNU_SOURCE -I/usr/include conftest.c In file included from /usr/include/openssl/ssl.h:179, from conftest.c:54: /usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory $ find / -name krb5.h -print 2/dev/null /usr/kerberos/include/krb5.h $ ./configure --prefix=/usr/local/pgsql830 --with-openssl --with-includes=/usr/kerberos/include/ cleans it up, thanks Tom. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to paste two tables side-by-side?
On Feb 27, 2008, at 7:22 AM, Kynn Jones wrote: On Wed, Feb 27, 2008 at 7:39 AM, Kynn Jones [EMAIL PROTECTED] wrote: Suppose I have two tables, A and B, with k(A) and k(B) columns respectively, and let's assume to begin with that they have the same number of rows r(A) = r(B) = r. What's the simplest way to produce a table C having r rows and k(A) + k(B) columns, and whose i-th row consists of the k(A) columns of the i-th row of A followed by the k(B) columns of the i-th row of B (for i = 1,...,r)? (By i-th row of A I mean the i-th row of the listing one would get from SELECT * FROM A, and likewise for B.) Expanding on my own post here, it occurred to me that it would be very nice to have a function (say) index, that, when used in a SELECT list, would yield the position in the corresponding table of the current row. E.g. the expression SELECT index(*) FROM A; would produce the same table as SELECT generate_series( 1, r(A) ); It would also be useful to have a subscripting function s (which may be regarded as somewhat of the inverse of index()) that, given a table expression E, and an index expression I (which could be a single index or range, or a list of such), will return the table consisting of the rows in E designated by the indices in I). For example, either one these queries SELECT s( A, 1, 2, 3 ); SELECT * FROM s( A, 1, 2, 3 ); would produce the same table as SELECT * FROM A LIMIT 3; Does anything like index() or s() exist? If not, are there other functions that may be useful in an implementation of index() or s()? If I'm understanding what you're asking for here, no it doesn't and it doesn't make sense for it to. Table rows are inherently unordered, to get an ordering you supply and ORDER BY clause. To get a certain position in that ordering you take on an OFFSET and/or LIMIT clause. Another way of putting it is that at the base table level, rows do not have position within the table. As soon as you update a row, it moves as the original row is deleted and a new one is inserted. Once vacuum does it's job, then newly inserted rows are inserted over previously deleted rows, i.e in the middle of the table. Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Trouble running PostgreSQL server / Server must be started under certain locale.
On 28/02/2008, Olmec Sinclair [EMAIL PROTECTED] wrote: Anyway, I uninstalled and installed using the ubuntu package manager. I tried again this morning - removing 8.2 and then reinstalling 8.2 again... still won't start. Are you seeing any error messages? W/o more detail it's impossible to even start trying to support you. What does /var/log/postgresql/postgresql-8.2-main.log say? I'm sure I can work through this (I seem to remember having a starting issue before) but the data locale thing is a worry. That, too, is indeed. Now ... gutsy doesn't have 8.3 officially yet (I don't think). What other repositories did you tie in with your apt? Cheers (from Christchurch) Olmec Cheers, Andrej ---(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] UUID-OSSP for windows.
citext is not part of core PostgreSQL or something we have any intention to include in the Windows distribution at this time. Is there an alternative for people wanting a case insensitive collation? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] RHEL3 configure ssl
On Wed, 27 Feb 2008, Ray Stell wrote: /usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory Ah, the RHEL3 Kerberos mess. If you have further problems compiling there's a heap of other paths you can pass through to sort around this issue; the end of http://www.ejabberd.im/node/172 has a good list. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to monitor the progress of a stored procedure?
(Sorry for asking so many questions!) What techniques can one use to monitor the progress of a stored procedure? Specifically, how can I get the procedure to print a progress indicator message to the screen every once in a while? I have a stored procedure that has been running for a very long time. Before doing the current real run, I tested the procedure on a toy case and confirmed that it produced the right result. So I know that when it finishes it will be OK, but I can't tell whether it will finish in an hour or in a century... I have no idea of how far along it is. (In case it matters, the procedure is written in PL/perl. I tried to use Perl's print statement, unbuffered, to print out a message periodically from within the procedure, but I see no output.) Thanks in advance, Kynn
Re: [GENERAL] How to monitor the progress of a stored procedure?
On Feb 27, 2008, at 5:25 PM, Kynn Jones wrote: (Sorry for asking so many questions!) What techniques can one use to monitor the progress of a stored procedure? Specifically, how can I get the procedure to print a progress indicator message to the screen every once in a while? I have a stored procedure that has been running for a very long time. Before doing the current real run, I tested the procedure on a toy case and confirmed that it produced the right result. So I know that when it finishes it will be OK, but I can't tell whether it will finish in an hour or in a century... I have no idea of how far along it is. (In case it matters, the procedure is written in PL/perl. I tried to use Perl's print statement, unbuffered, to print out a message periodically from within the procedure, but I see no output.) Thanks in advance, Read up on this chapter: http://www.postgresql.org/docs/8.2/ interactive/plpgsql-errors-and-messages.html Erik Jones DBA | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to monitor the progress of a stored procedure?
Kynn Jones wrote: I have a stored procedure that has been running for a very long time. Before doing the current real run, I tested the procedure on a toy case and confirmed that it produced the right result. So I know that when it finishes it will be OK, but I can't tell whether it will finish in an hour or in a century... I have no idea of how far along it is. (In case it matters, the procedure is written in PL/perl. I tried to use Perl's print statement, unbuffered, to print out a message periodically from within the procedure, but I see no output.) When I print() from PL/Perl, the messages show up in the PostgreSQL log file. It's not an obvious place, but it makes sense because PL/Perl functions aren't always called from interactive terminals. Did you look there? Colin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL UK 2008 Conference
Just a reminder to everybody to come along to the PostgreSQL UK 2008 Conference in Birmingham, UK on April 2nd. I've just had confirmation that the Early Bird Rate has been extended until March 10th. There are some concessionary places available for students also, so we want *everyone* there. This is a non-profit conference - all fees go towards conference organisation and PostgreSQL funds. Thanks everyone, -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] RHEL3 configure ssl
Hi, On Wed, 2008-02-27 at 16:16 -0500, Ray Stell wrote: /usr/include/openssl/kssl.h:72:18: krb5.h: No such file or directory Add --with-includes=/usr/kerberos/include --with-libraries=/usr/kerberos/lib to your configure parameters. This is what we do while building PostgreSQL RPMs on RHEL 3. Regards, -- Devrim GÜNDÜZ , RHCE PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] How to monitor the progress of a stored procedure?
Kynn Jones wrote: (Sorry for asking so many questions!) What techniques can one use to monitor the progress of a stored procedure? Specifically, how can I get the procedure to print a progress indicator message to the screen every once in a while? [...](In case it matters, the procedure is written in PL/perl. I tried to use Perl's print statement, unbuffered, to print out a message periodically from within the procedure, but I see no output.) elog? (see example in http://www.postgresql.org/docs/8.3/interactive/plperl-database.html) klint. -- Klint Gore Database Manager Sheep CRC A.G.B.U. University of New England Armidale NSW 2350 Ph: 02 6773 3789 Fax: 02 6773 3266 EMail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Can't start Postgres anymore
IIRC, you cannot state runservice runservice -N . from commandline. just try pg_ctl -D _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of George Younan Sent: Wednesday, February 27, 2008 10:08 PM To: pgsql-general@postgresql.org Subject: [GENERAL] Can't start Postgres anymore Hallo, I have Postgres installed as a service on Windows xp pro. It has been working fine but suddenly i couldn't start it anymore. I looked in different forums for a similar problem but didn't find any solution sofar. So any help is very appreciated. one more thing to say is that i am a beginner with Postgres and don't have much administrative experience... As i said above, i can't start my Postgres anymore. I always get the error message could not connect to server: Connection refused (0x274D/10061)Is the server running on host ??? and accepting TCP/IP connections on port 5432?. I checked if the service is running, but it wasn't running. I tried to start ist but i couldn't. Doing this from the console i got the following error: C:\Programme\PostgreSQL\8.1\binpg_ctl.exe runservice -N pgsql-8.1 -D C:\Programme\PostgreSQL\8.1\data\ pg_ctl: could not start service pgsql-8.1: error code 1063 I chekced postgres.conf and the configuration is as follows: # - Connection Settings - listen_addresses = 'localhost' port = 5432 max_connections = 100 The last log file is: 2008-02-08 07:40:38 LOG: could not connect socket for statistics collector: Es konnte keine Verbindung hergestellt werden, da der Zielcomputer die Verbindung verweigerte. 2008-02-08 07:40:38 LOG: disabling statistics collector for lack of working socket 2008-02-08 07:40:38 WARNING: autovacuum not started because of misconfiguration 2008-02-08 07:40:38 HINT: Enable options stats_start_collector and stats_row_level. 2008-02-08 07:40:40 LOG: database system was shut down at 2008-02-08 07:37:57 Westeuropäische Normalzeit 2008-02-08 07:40:40 LOG: checkpoint record is at 0/45AE50 2008-02-08 07:40:40 LOG: redo record is at 0/45AE50; undo record is at 0/0; shutdown TRUE 2008-02-08 07:40:40 LOG: next transaction ID: 5100; next OID: 16515 2008-02-08 07:40:40 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2008-02-08 07:40:41 LOG: database system is ready 2008-02-08 07:40:41 LOG: transaction ID wrap limit is 2147484148, limited by database postgres I'm using Zonealarm firewall but Postgres is authorized on 127.0.0.1:5432. It's been working fine for the last month since installtion and i didn't change any configuration. I stopped both zonealarm and the windows firewall but still success, so i guess it's not a firewall problem. I tried to ping the server using ping 127.0.0.1 which was successful. I tried to connect to the host with telnet using telnet 127.0.0.1 5432 but i couldn't connect. anyone has a suggestion what can i do to get it working again. I dont want to lose the data stored on it! thanks in advance George
Re: [GENERAL] help for loading a psql file
akshay bhat wrote: hello i am new to psql or any database stuff. i have downloaded an .psql file from internet and wish to open it and see the data inside. Drag it into your text editor. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] beginner postgis question lat/lon
Hi,
This may seem like a very simple question...it is...but I can't find
documentation on it to help. I've seen some posts about lat/long but
none that give simple solutions on how to insert lat/long in tables.
I'm a new user to postgis...I've been using mysql for a while but
needed the spatial functions so just recently downloaded postgresql/
postgis.
How do I create a simple table with several linestrings that have
coordinates in latitude/longitude?
Eventually, I'd like to create polygons with coordinates in lat/long
and see if they intersect various linestrings, but I'm trying to start
out simple.
Here's what I've done so far...
postgis=# \d routes_geom;
Table public.routes_geom
Column | Type | Modifiers
+---+---
id | integer |
name | character varying(25) |
geom | geometry |
Check constraints:
enforce_dims_geom CHECK (ndims(geom) = 2)
enforce_geotype_geom CHECK (geometrytype(geom) =
'LINESTRING'::text OR geom IS NULL)
enforce_srid_geom CHECK (srid(geom) = 4326)
postgis=# insert into routes_geom values(1, 'J084',
GeomFromText('LINESTRING(38.20 -121.00, 38.20, -118.00)', 4326));
I receive this error:
ERROR: parse error - invalid geometry
CONTEXT: SQL function geomfromtext statement 1
Do you have a good way to input lat/lon and do spatial relationships
using lat/long coords?
Thanks for any help,
Shad
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)
hello i am new to psql or any database stuff. i have downloaded an .psql file from internet and wish to open it and see the data inside. i am working on windows xp and have installed the software successfully. please help i am my wits end. it is huge file 800mb and is supposed to contain a database. the question is how to load it? it was downloaded from this link http://conceptnet.media.mit.edu/ the description says The ConceptNet 3 databasehttp://conceptnet.media.mit.edu/conceptnet-2007-09-25.tar.gz, as a PostgreSQL input file. You will need to be running a PostgreSQL server to install ConceptNet 3. how do i load it? thanks a lot -- akshay uday bhat. t.y.c.e. department of chemical engineering university institute of chemical technology mumbai India On action alone be thy interest, Never on its fruits. Let not the fruits of action be thy motive, Nor be thy attachment to inaction. Ask and it shall be given to you; seek and you shall find; knock and it shall be opened to you
Re: [GENERAL] Query meltdown: caching results
Gordon wrote:
(Sorry for the repost but I thought this would be appropriate to both
groups. I did tell Google to delete my first post but odds are some
guys got that copy already anyway)
After a lot of hairpulling, I finally found a mechanism in PHP for
doing what I wanted. I just had to know 2 things:
1) How to get the PDO engine to use my customized prepared statement
class instead of PDOStatement
2) Extending PDOStatement to transparently add results caching is too
difficult and complex in the timeframe required
Once I knew these things I made a PDOStatement extension class that
instead of trying to transparently add caching to the existing methods
added a couple of new ones instead. Code below:
?php
class Statement extends PDOStatement
{
private $resultCache= array ();
private $database = NULL;
public $hits = 0;
public $misses = 0;
public function ask (array $params = array ())
// Executes a prepared statement on the database that fetches
data
{
$hash = md5 (implode (',', $params));
if (!$this - resultCache [$hash])
{
$this - misses++;
// Execute the query
if ($this - execute ($params))
{
// Cache the results
$this - resultCache [$hash] = $this -
fetchAll
(PDO::FETCH_ASSOC);
}
}
else
{
$this - hits++;
}
return ($this - resultCache [$hash]);
}
public function tell (array $params = array ())
// Execute a prepared statement that causes the database to be
modified
{
// Execute the query
if ($this - execute ($params))
{
$rowCount = $this - rowCount ();
if ($rowCount)
{
// Tell the parent Database object to
clear statement caches
$this - database - clearResults ();
}
return ($rowCount);
}
}
public function clearResults ()
// Clear cache
{
$this - resultCache = array ();
}
private function __construct ($db)
// Class constructor
{
$this - database= $db;
//print_r ($this);
}
}
class Database extends PDO
// Adds some extra functionality to the built in PDO class
{
private $statementCache = array ();
private $txCount= 0;
private $txErr = false;
// Prepared statement cacheing
public function prepare ($statement, array $options = array
())
{
$hash = md5 ($statement);
if ((!isset ($this - statementCache [$hash]))
|| (!is_object ($this - statementCache [$hash])))
{
//echo ('Preparing statement '.
$statement .'br');
$this - statementCache [$hash] =
parent::prepare ($statement,
$options);
}
else
{
//echo ('Statement ' . $statement . '
already preparedbr');
}
return ($this - statementCache [$hash]);
}
public function clearResults ()
// Clear the results cache of all associated prepared
statements
{
foreach ($this - statementCache as $thisStatement)
{
$thisStatement - clearResults ();
}
}
// Add support for transaction nesting
public function beginTransaction ()
{
if (($this - txCount == 0) (!$this - txErr))
{
$result = parent::beginTransaction ();
}
$this - txCount ++;
if (DEBUG_TX)
{
echo ('begin: ' . $this - txCount . '
transaction(s)br /');
}
return ($result);
}
public function commit ()
{
$this - txCount --;
if ($this - txCount = 0)
{
$this - txErr? $result =
parent::rollback (): $result =
parent::commit ();
$this - txErr = false;
}
if (DEBUG_TX)
{
echo ('commit: ' . $this - txCount . '
transaction(s)br /');
}
return ($result);
}
public function rollback ()
{
$this - txErr = true;
Re: [GENERAL] HELP FOR LOADING a .psql file (same question again but explained neatly)
On the postgres command prompt you can use /i filename ... try if it works Cheers On Thu, Feb 28, 2008 at 9:07 AM, akshay bhat [EMAIL PROTECTED] wrote: hello i am new to psql or any database stuff. i have downloaded an .psql file from internet and wish to open it and see the data inside. i am working on windows xp and have installed the software successfully. please help i am my wits end. it is huge file 800mb and is supposed to contain a database. the question is how to load it? it was downloaded from this link http://conceptnet.media.mit.edu/ the description says The ConceptNet 3 databasehttp://conceptnet.media.mit.edu/conceptnet-2007-09-25.tar.gz, as a PostgreSQL input file. You will need to be running a PostgreSQL server to install ConceptNet 3. how do i load it? thanks a lot -- akshay uday bhat. t.y.c.e. department of chemical engineering university institute of chemical technology mumbai India On action alone be thy interest, Never on its fruits. Let not the fruits of action be thy motive, Nor be thy attachment to inaction. Ask and it shall be given to you; seek and you shall find; knock and it shall be opened to you
