Re: [GENERAL] How to change the file encoding of a 3gb file?
Hi, I'm just guessing. If you have a database in UTF8 and your SQL-file in some encoding so what about doing this: 1. Connect to your database using psql $ psql -p ... -h ... -U ... your_database 2. Set client encoding to the encoding of your SQL-file yourdb=# \encoding your_sql_encoding 3. Load the SQL-file \i your_sql_file On 19 July 2010 01:51, Andre Lopes lopes80an...@gmail.com wrote: Hi, I'am trying to import an SQL file with 3gb of INSERTS. I must to change the encode of the file to UTF8, how can I change the encode of the file without open it? This 3gb file crashes every program... Give me a clue. Best Regards, -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Rescue data after power off
Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Please help me. I am very confuse. Tuan Hoang Anh
Re: [GENERAL] Full Text Search ideas
On 19 July 2010 01:46, Howard Rogers h...@diznix.com wrote:
On Mon, Jul 19, 2010 at 6:16 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Howard Rogers h...@diznix.com writes:
ims=# select count(*) from search_rm
where to_tsvector('english', textsearch) @@ to_tsquery('english','bat
sb12n');
count
---
3849
(1 row)
Time: 408.962 ms
ims=# select count(*) from search_rm
where to_tsvector('english', textsearch) @@ to_tsquery('english','bat
!sb12y');
count
---
3849
(1 row)
Time: 11.533 ms
Yeah, I imagine that the first of these will involve examining all the
index entries for sb12n. There's not a lot of smarts about that inside
the GIN index machinery, AFAIK: it'll just fetch all the relevant TIDs
for both terms and then AND them.
I'm wondering firstly if there's any way I can configure PostgreSQL
FTS so that it produces the sort of results we've gotten used to from
Oracle, i.e., where search speeds do not go up wildly when a 'search
term' is applied that happens to be used by the vast majority of
document records.
If you're willing to split out the search terms that are like this,
you could probably get better results with something like
select count(*) from search_rm
where to_tsvector('english', textsearch) @@ to_tsquery('english','bat')
AND
to_tsvector('english', textsearch) @@ to_tsquery('english','sb12n');
That will put it on the optimizer's head as to whether to use the index
for one term or both terms.
It might be worth noting that the optimizer will of course not get this
right unless it has decent statistics about both search terms --- and
there is an as-yet-unreleased patch about tsvector stats gathering:
http://archives.postgresql.org/pgsql-committers/2010-05/msg00360.php
I am not sure that the situation addressed by that patch applies in
your case, but it might.
regards, tom lane
Thanks, Tom.
The breaking out into separate search terms does make a difference,
but not much:
ims=# select count(*) from search_rm
where to_tsvector('english',textsearch) @@ to_tsquery('english','bat
sb12n');
count
---
3849
(1 row)
Time: 413.329 ms
ims=# select count(*) from search_rm
ims-# where to_tsvector('english',textsearch) @@
to_tsquery('english','bat') AND
ims-# to_tsvector('english',textsearch) @@ to_tsquery('english','sb12n');
count
---
3849
(1 row)
Time: 352.583 ms
So it's shaving about a sixth of the time off, which isn't bad, but
not spectacularly good either!
I'd also thought of trying something like this:
ims=# select count(*) from
(
select * from search_rm where
to_tsvector('english',textsearch) @@ to_tsquery('english','bat')
) as core
where to_tsvector('english',textsearch) @@ to_tsquery('english','sb12n');
count
---
3849
(1 row)
Time: 357.248 ms
...in the hope that the sb12n test would only be applied to the set of
'bat' records acquired by the inner query. But as you can tell from
the time, that's not particularly better or worse than your suggestion
(bearing mind that 'bat' on its own is a 12ms search).
I'm currently constructing a separate column containing a single
bitmask value for about 15 of the 45 attributes, just to see if
evaluating the bits with a bitand test for the bat records is faster
than trying to FTS them in the first place. Something like
select count (*) from
(
select * from search_rm where
to_tsvector('english',textsearch) @@ to_tsquery('english','bat')
) as core
where bitand(searchbits,4096)0;
But it's taking a while to get that extra column constructed in the
original table!
Fingers crossed, because if not, it's all a bit of a show-stopper for
our migration effort, I think. :-(
Regards thanks
HJR
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Howard,
As well as trying the bitand test, have you tried a plain %like% or a regex
match/extraction on the results of performing the fts search purely on the
search terms? I'm guessing here it would involve more calculation in the
search than the bitand approach, but might require less maintenance and,
along the direction you are already heading, place more emphasis on the
refinement of candidate matches rather than the retrieval of better matches
in the first instance, and perhaps more so for non-exhaustive searching.
Regards,
Steve
Re: [GENERAL] Rescue data after power off
In response to tuanhoanganh : Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Incorrect FTS result with GIN index
Hello Oleg,
my results are different. The analysis looks like this (please note the
different numbers of rows):
Aggregate (cost=104.05..104.06 rows=1 width=0) (actual
time=152.133..152.135 rows=1 loops=1)
- Bitmap Heap Scan on search_tab (cost=5.39..103.98 rows=25 width=0)
(actual time=76.546..151.834 rows=116 loops=1)
Recheck Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
- Bitmap Index Scan on idx_keywords_ger (cost=0.00..5.38 rows=25
width=0) (actual time=76.292..76.292 rows=506 loops=1)
Index Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
Total runtime: 152.389 ms
I have no idea, what could be the reason for different behaviour on your and
my machine (windows xp, postgreSQL 8.4.3)?
I reproduced the same wrong behaviour on a machine of my co-worker (windows
xp, postgreSQL 8.4.4).
--
View this message in context:
http://old.nabble.com/Incorrect-FTS-results-with-GIN-index-tp29172750p29203020.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Rescue data after power off
Yes, I don't have backup. On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to tuanhoanganh : Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Rescue data after power off
When I use pgadmin to view data of table dmvt, it have data. But when I select * from dmvt there is error on log 2010-07-19 17:32:17 ICTWARNING: invalid page header in block 207 of relation dmvt; zeroing out page 2010-07-19 17:32:35 ICTLOG: server process (PID 3480) was terminated by exception 0xC005 2010-07-19 17:32:35 ICTHINT: See C include file ntstatus.h for a description of the hexadecimal value. 2010-07-19 17:32:35 ICTLOG: terminating any other active server processes 2010-07-19 17:32:35 ICTWARNING: terminating connection because of crash of another server process 2010-07-19 17:32:35 ICTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-07-19 17:32:35 ICTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-07-19 17:32:35 ICTWARNING: terminating connection because of crash of another server process 2010-07-19 17:32:35 ICTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2010-07-19 17:32:35 ICTHINT: In a moment you should be able to reconnect to the database and repeat your command. 2010-07-19 17:32:36 ICTLOG: all server processes terminated; reinitializing 2010-07-19 17:32:46 ICTFATAL: pre-existing shared memory block is still in use 2010-07-19 17:32:46 ICTHINT: Check if there are any old server processes still running, and terminate them. Is there anyway to fix it. On Mon, Jul 19, 2010 at 5:00 PM, tuanhoanganh hatua...@gmail.com wrote: Yes, I don't have backup. On Mon, Jul 19, 2010 at 4:24 PM, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to tuanhoanganh : Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Restore the data from your backup. You haven't a backup? Your problem. Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] Rescue data after power off
Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. The Power Off - is it a planned/regular one? If yes, it makes sense to have a normal shutdown of the database before the Power Off. You said some of table is blank. If those tables just got populated before the Power Off and the transactions were committed, PostgreSQL might have used WALS and recovered. Some information on what data was expected to be in those tables and how they were populated , messages you got when you restarted PostgreSQL server etc might give a better idea to those in forum who are in a position to help. Regards, Jayadevan From: tuanhoanganh hatua...@gmail.com To: pgsql-general@postgresql.org Date: 07/19/2010 02:45 PM Subject:[GENERAL] Rescue data after power off Sent by:pgsql-general-ow...@postgresql.org Is there anyway to rescue data afer power off. I have postgres database version 8.3.9 on windows 2003. Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. Please help me. I am very confuse. Tuan Hoang Anh DISCLAIMER: The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect. -- 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] Incorrect FTS result with GIN index
Artur,
I don't know, but could you try linux machine ?
Oleg
On Mon, 19 Jul 2010, Artur Dabrowski wrote:
Hello Oleg,
my results are different. The analysis looks like this (please note the
different numbers of rows):
Aggregate (cost=104.05..104.06 rows=1 width=0) (actual
time=152.133..152.135 rows=1 loops=1)
- Bitmap Heap Scan on search_tab (cost=5.39..103.98 rows=25 width=0)
(actual time=76.546..151.834 rows=116 loops=1)
Recheck Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
- Bitmap Index Scan on idx_keywords_ger (cost=0.00..5.38 rows=25
width=0) (actual time=76.292..76.292 rows=506 loops=1)
Index Cond: ((to_tsvector('german'::regconfig, keywords) @@
'''ee'':*'::tsquery) AND (to_tsvector('german'::regconfig, keywords) @@
'''dd'':*'::tsquery))
Total runtime: 152.389 ms
I have no idea, what could be the reason for different behaviour on your and
my machine (windows xp, postgreSQL 8.4.3)?
I reproduced the same wrong behaviour on a machine of my co-worker (windows
xp, postgreSQL 8.4.4).
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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] pg_dump and --inserts / --column-inserts
On Sat, Jul 17, 2010 at 07:46:23PM +0200, Thomas Kellerer wrote: Tom Lane wrote on 17.07.2010 19:35: I'd dismiss those numbers as being within experimental error, except it seems odd that they all differ in the same direction. And it's reproducable (at least on my computer). As I said I ran it 20 times (each run did it for 5,10,... columns) and the values I posted were averages of those runs. You couldn't give us the standard deviation as well could you? If the deviation within a test is larger than that between tests then you can't say much, but without the numbers this can't be determined. -- Sam http://samason.me.uk/ -- 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] Rescue data after power off
On 19/07/10 19:02, Jayadevan M wrote: Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. The Power Off - is it a planned/regular one? If yes, it makes sense to have a normal shutdown of the database before the Power Off. ... but even if you yank the power plug out of the back of the server, PostgreSQL should *NEVER* lose comitted data (unless you've told it its allowed to with a commit delay) and should certainly NEVER damage the database structure. It's one of PostgreSQL's most important and basic features. Unless you have set fsync=off in postgresl.conf, in which case the data loss is entirely your own fault, this should not happen. If you do not know if fsync is on or off, check by running SHOW fsync; in psql or PgAdmin. It should say on. If it says off then you or someone else manually told the database not to try to protect your data from power loss or other failures. If you have lost data and fsync is on, then most likely your RAID controller/disks are doing something they should not be like caching writes in volatile storage. Make sure your RAID controller has write caching disabled or has a tested and working battery backup unit. If in doubt, your raid controller should have its write cache turned off, ie it should be in write-through mode and NOT in write-back mode. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can not change log_min_duration_statement parameter on PG 8.2.4
Hello,
I am running a 8.2.4 PostgreSQL instance on a debian etch server.
I have a problem trying to change the parameter log_min_duration_statement.
Its actuel value in the postgresql.conf is -1 (log off) :
log_min_duration_statement = -1# -1 is disabled, 0 logs all
statements
# and their durations.
I reloaded (even restarted) the service, but when I connect to any
database (even a newly created one), the log_min_duration_statement is
still 0.
And effectively, all the queries executed on the server are logged.
The parameter does not seem to be set for the database though :
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
(1 ligne)
If I set a new value for the database with an ALTER DATABASE command,
then disconnect and reconnect, the value is still 0 :
postgres=# ALTER DATABASE postgres SET log_min_duration_statement to 2000;
ALTER DATABASE
postgres=# \q
12:29| r...@myserver:~ # psql -U postgres
Bienvenue dans psql 8.2.4, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
{log_min_duration_statement=2000}
(1 ligne)
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
0
(1 ligne)
postgres=#
Though I can set a new value for one session :
postgres=# SET log_min_duration_statement to 2500;
SET
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
2500ms
(1 ligne)
Am I missing something ?
Thanks for your help !
Nico
--
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] Rescue data after power off
On 19/07/10 18:37, tuanhoanganh wrote: When I use pgadmin to view data of table dmvt, it have data. But when I select * from dmvt there is error on log 2010-07-19 17:32:17 ICTWARNING: invalid page header in block 207 of relation dmvt; zeroing out page 2010-07-19 17:32:35 ICTLOG: server process (PID 3480) was terminated by exception 0xC005 If that message is always the same, it's likely that there's a bug somewhere in the PostgreSQL database backend's handling of damaged database files. This almost certainly has nothing to do with whatever damaged the data in the first place. Fixing it is very unlikely to bring your data back. Your database file is still damaged, and if this error wasn't happening it'd probably just give you an error saying it can't read the relation instead of terminating like that. Nonetheless, it'd be nice to know where it is crashing. Since you can make the crash happen reliably every time, please consider following the instructions at: http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Windows to collect some debugging information that might help. It is unlikely that fixing this problem will bring your data back, but it might provide more information that could help. Please also post your postgresql.conf . -- Craig Ringer -- 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] Can not change log_min_duration_statement parameter on PG 8.2.4
Ooops, I just realized I set the parameter to the ROLE postgres a few
weeks ago...
ALTER ROLE postgres SET log_min_duration_statement TO DEFAULT;
solved my problem... which was not a problem in fact, juste a mistake :|
--
Nico
Le 19/07/2010 12:36, Nico a écrit :
Hello,
I am running a 8.2.4 PostgreSQL instance on a debian etch server.
I have a problem trying to change the parameter
log_min_duration_statement.
Its actuel value in the postgresql.conf is -1 (log off) :
log_min_duration_statement = -1# -1 is disabled, 0 logs all
statements
# and their durations.
I reloaded (even restarted) the service, but when I connect to any
database (even a newly created one), the log_min_duration_statement is
still 0.
And effectively, all the queries executed on the server are logged.
The parameter does not seem to be set for the database though :
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
(1 ligne)
If I set a new value for the database with an ALTER DATABASE command,
then disconnect and reconnect, the value is still 0 :
postgres=# ALTER DATABASE postgres SET log_min_duration_statement to
2000;
ALTER DATABASE
postgres=# \q
12:29| r...@myserver:~ # psql -U postgres
Bienvenue dans psql 8.2.4, l'interface interactive de PostgreSQL.
Tapez: \copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
postgres=# SELECT datconfig from pg_database where datname = 'postgres' ;
datconfig
---
{log_min_duration_statement=2000}
(1 ligne)
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
0
(1 ligne)
postgres=#
Though I can set a new value for one session :
postgres=# SET log_min_duration_statement to 2500;
SET
postgres=# SHOW log_min_duration_statement ;
log_min_duration_statement
2500ms
(1 ligne)
Am I missing something ?
Thanks for your help !
Nico
--
Nicolas PAYART
Administrateur de bases de données
Benchmark Group
Atalis 2 - Bât D
3, rue de Paris
35510 Cesson Sévigné
France
Int : 3014
Tél : +33 2 23 45 20 19
Fax : +33 2 99 83 39 24
http://www.journaldunet.com
http://www.linternaute.com
http://www.benchmark.fr
http://www.copainsdavant.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] index scan and functions
Hi,
In a table, I've some geoip informations with indexes to two colums
\d geoip
Table « public.geoip »
Colonne | Type | Modificateurs
--+--+---
begin_ip | bigint |
end_ip | bigint |
country | character(2) |
Index :
geoip_begin_idx btree (begin_ip)
geoip_end_idx btree (end_ip)
when I try to select stuffs form this table, request is fast:
syj= explain select * from geoip where begin_ip = 2130706433 and end_ip =
2130706433;
QUERY PLAN
---
Index Scan using geoip_end_idx on geoip (cost=0.00..1448.46 rows=26967
width=19)
Index Cond: (end_ip = 2130706433)
Filter: (begin_ip = 2130706433)
(3 lignes)
But when using a custom function to compute my where parameter, request is slow:
syj= explain select * from geoip where begin_ip =
inet_to_bigint('127.0.0.1') and end_ip = inet_to_bigint('127.0.0.1');
QUERY PLAN
---
Seq Scan on geoip (cost=0.00..67654.95 rows=14418 width=19)
Filter: ((begin_ip = inet_to_bigint('127.0.0.1'::inet)) AND (end_ip =
inet_to_bigint('127.0.0.1'::inet)))
(2 lignes)
inet_to_bigint is a function that transform an inet address its integer
representation.
Is there a way, either to put function return value in a variable, or to tell
postgres to still use a sequential scan ?
thanks
signature.asc
Description: Digital signature
Re: [GENERAL] index scan and functions
On Mon, Jul 19, 2010 at 05:55:48PM +0200, arno wrote: But when using a custom function to compute my where parameter inet_to_bigint is a function that transform an inet address its integer representation. Is there a way, either to put function return value in a variable, or to tell postgres to still use a sequential scan ? I'd guess your function is labeled as VOLATILE. This is saying that the function has side effects, but from the function's name I'd guess that it doesn't and the only purpose of calling the function is to get its return value. I'd expect that labeling it as STABLE would cause PG to do what you're expecting. More details here: http://www.postgresql.org/docs/current/static/xfunc-volatility.html -- Sam http://samason.me.uk/ -- 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] index scan and functions
Le lundi 19 juillet 2010, à 17:09:02 +0100, Sam a écrit : On Mon, Jul 19, 2010 at 05:55:48PM +0200, arno wrote: But when using a custom function to compute my where parameter inet_to_bigint is a function that transform an inet address its integer representation. Is there a way, either to put function return value in a variable, or to tell postgres to still use a sequential scan ? I'd guess your function is labeled as VOLATILE. This is saying that the function has side effects, but from the function's name I'd guess that it doesn't and the only purpose of calling the function is to get its return value. I'd expect that labeling it as STABLE would cause PG to do what you're expecting. More details here: http://www.postgresql.org/docs/current/static/xfunc-volatility.html Thanks, that's exactly what I was looking for. signature.asc Description: Digital signature
[GENERAL] Create table if not exists ... how ??
I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes ( area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH ( OIDS=FALSE ); ALTER TABLE post_codes OWNER TO postgres; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ERROR: syntax error at or near IF LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab... ^ ** Error ** ERROR: syntax error at or near IF SQL state: 42601 Character: 1 How should this be written ? Thanks, Jen
Re: [GENERAL] Create table if not exists ... how ??
On 19/07/2010 17:33, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes ( area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH ( OIDS=FALSE ); ALTER TABLE post_codes OWNER TO postgres; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ERROR: syntax error at or near IF LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab... ^ How should this be written ? I don't think you can use the IF like this in a normal query. You could write a pl/pgsql function instead to do this.. Ray. -- 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] Create table if not exists ... how ??
Hello you can use IF statement only inside plpgsql function. CREATE TABLE doesn't support clause IF. Regards Pavel Stehule 2010/7/19 Jennifer Trey jennifer.t...@gmail.com: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes ( area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH ( OIDS=FALSE ); ALTER TABLE post_codes OWNER TO postgres; - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ERROR: syntax error at or near IF LINE 1: IF NOT EXISTS (SELECT table_name FROM information_schema.tab... ^ ** Error ** ERROR: syntax error at or near IF SQL state: 42601 Character: 1 How should this be written ? Thanks, Jen -- 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] Create table if not exists ... how ??
How should this be written ? I don't think you can use the IF like this in a normal query. You could write a pl/pgsql function instead to do this.. You can write such a query inline in 9.0, by use of DO...but you probably just want to define a function for now -- Regards, Peter Geoghegan -- 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] Create table if not exists ... how ??
You all make it sound so easy :) How do I write the above using a function? Cheers, Jen
Re: [GENERAL] Rescue data after power off
I have checked SHOW fsync, It is ON. When I view table dmvt on PgAdmin, it only has 1332 rows, but command SELECT count(*) FROM dmvt return 2449 rows. My postgresql.conf is default of EnterpriseDB Postgres 8.3.9. Please help me. Sorry for my English. Tuan Hoang Anh. On Mon, Jul 19, 2010 at 9:36 PM, Craig Ringer cr...@postnewspapers.com.auwrote: On 19/07/10 19:02, Jayadevan M wrote: Yesterday my server is power off, when i start server, some of table is blank. Is there anyway to rescue it. The Power Off - is it a planned/regular one? If yes, it makes sense to have a normal shutdown of the database before the Power Off. ... but even if you yank the power plug out of the back of the server, PostgreSQL should *NEVER* lose comitted data (unless you've told it its allowed to with a commit delay) and should certainly NEVER damage the database structure. It's one of PostgreSQL's most important and basic features. Unless you have set fsync=off in postgresl.conf, in which case the data loss is entirely your own fault, this should not happen. If you do not know if fsync is on or off, check by running SHOW fsync; in psql or PgAdmin. It should say on. If it says off then you or someone else manually told the database not to try to protect your data from power loss or other failures. If you have lost data and fsync is on, then most likely your RAID controller/disks are doing something they should not be like caching writes in volatile storage. Make sure your RAID controller has write caching disabled or has a tested and working battery backup unit. If in doubt, your raid controller should have its write cache turned off, ie it should be in write-through mode and NOT in write-back mode. -- Craig Ringer
Re: [GENERAL] Create table if not exists ... how ??
On 07/19/2010 09:33 AM, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes Probably better to do: DROP TABLE IF EXISTS post_codes; CREATE TABLE post_codes(...); See: http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [GENERAL] NASA needs Postgres - Nagios help
Thank you for the time and thought. I've added Brian Martin, who is my project lead for this effort. He's a better person to converse with than I am. -Original Message- From: Michael Friedrich [mailto:michael.friedr...@univie.ac.at] Sent: Sunday, July 18, 2010 4:35 PM To: Duncavage, Daniel P. (JSC-OD211) Cc: pgsql-general@postgresql.org; Stefan Kaltenbrunner Subject: Re: [GENERAL] NASA needs Postgres - Nagios help Hi there, On 2010-07-15 07:06, Stefan Kaltenbrunner wrote: well - there was direct database support in nagios ages ago(nagios 1.x is ancient) and replaced with a plugin based approach based on their eventbroker architecture called NDOutils. Based on tracking internal state it can be used to export current and historical monitoring data from nagios for later postprocessing (or for usin a GUI or whatever). NODutils however has no real working support for PostgreSQL, IDOutils (which I mentioned elsewhere in the thread) from the icinga fork does have basic support. The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique constraints is a bunch of work to be resolved. Next to that, the normal insert statements are not normalized (insert into ... set foo=bar instead of insert into ... () values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an adaption on sequences in Postgresql and Oracle. Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will happen since there is no C source code for that available via #ifdef. Some of those mentioned things have been resolved in Icinga IDOUtils, but not all since I had to focus on 1/ make IDOUtils more stable, less blocking and 2/ provide initial improved Oracle support. THe Postgresql support is quite basic, but based on libdbi it still works. In regard of bigger monitoring environments it will lack of performance for sure. Main reason is that the current query implementation first tries and update, and then inserts - which basically forms the on duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure in the worst situation. An UPSERT or MERGE procedure should replace that - sth like this: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql (a far more better approach would be a common rewrite with a better db schema but that's future sound for existing database setups). If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current 1.4b9 consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping during startup and running. IDOUtils provides an extended housekeeping thread not to interfere with the insertions. Some blogposts on Icinga's improvements, especially on IDOUtils: http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/ http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/ http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/ http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/ Our plans are to improve Postgresql support of Icinga IDOUtils within the next months mainly regarding the upsert procedure, but also by dropping the current db abstraction layer (libdbi) in order to use direct prepared statements and binded params (which is not possible with libdbi). This will be done right after some bigger core changes are finished, imho not in 1.0.3 but 1.0.4 in October would be possible. Postgresql is next to MySQL and Oracle part of RDBMS section of the unified Icinga API (written in PHP), and provides the current Icinga Core data source for the newly developed Icinga Web. For more information: http://www.icinga.org/architecture/ http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/ That's the thing in Icinga's perspective - it's still a fork of Nagios, but as you can see a lot of things happened lately. If Icinga can be of help for you getting better Postgresql support with Icinga IDOUtils, please get in touch. We'd love to work together on a satisfying solution for you and the community :) Kind regards, Michael (Icinga Core IDOUtils Developer) -- DI (FH) Michael Friedrich michael.friedr...@univie.ac.at Tel: +43 1 4277 14359 Vienna University Computer Center Universitaetsstrasse 7 A-1010 Vienna, Austria -- 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] Create table if not exists ... how ??
No I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time. Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is. Cheers, Jen On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway m...@joeconway.com wrote: On 07/19/2010 09:33 AM, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes Probably better to do: DROP TABLE IF EXISTS post_codes; CREATE TABLE post_codes(...); See: http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support
Re: [GENERAL] Inheritance and trigger/FK propagation
On Thu, Jul 15, 2010 at 4:05 AM, Davor J. dav...@live.com wrote: It seems no secret that a child table will not fire a trigger defined on it's parent table. Various posts comment on this. But nowhere could I find a reason for this. Do you want your trigger that redirects insert on parent table to the proper child table should run on child tables too? -- 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] Create table if not exists ... how ??
On Jul 19, 2010, at 10:43 AM, Jennifer Trey wrote: No I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time. Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is. Cheers, Jen Try something like this: create or replace function build_foo_table() returns void as $$ create table foo (bar int); $$ language sql; select case when (select count(*) from information_schema.tables where table_name='foo')=0 then build_foo_table() end; drop function build_foo_table(); Cheers, Steve -- 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] Create table if not exists ... how ??
Assuming you know the schema name, you could always check the catalog table, something like select count(*) from pg_tables where schemaname= 'foo' and tablename='bar' If it returns, then you know a table by the name foo.bar exists. if not you can create it. -Said Jennifer Trey wrote: No I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time. Should I just let Java throw and exception and catch it ? Write a function for this would be optimal, although I have no idea what the correct syntax is. Cheers, Jen On Mon, Jul 19, 2010 at 5:58 PM, Joe Conway m...@joeconway.com wrote: On 07/19/2010 09:33 AM, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes Probably better to do: DROP TABLE IF EXISTS post_codes; CREATE TABLE post_codes(...); See: http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
On 07/19/2010 10:43 AM, Jennifer Trey wrote: No I don't want to drop it ... there is valuable data in there! I only want to create it if it doesn't already exist... likely going to happen first time the application will run. I want to create the table then and populate. But not the next time. Sorry -- didn't understand that from your original post. How 'bout something like: 8- CREATE OR REPLACE FUNCTION conditional_create_table(schemaname text, tablename text, create_sql text, tbl_owner text) RETURNS text AS $$ DECLARE tbl_cnt int; fqtn text := schemaname || '.' || tablename; BEGIN SELECT COUNT(*) INTO tbl_cnt FROM information_schema.tables WHERE table_schema= schemaname AND table_name=tablename; IF tbl_cnt 1 THEN EXECUTE 'CREATE TABLE ' || fqtn || create_sql; EXECUTE 'ALTER TABLE ' || fqtn || ' OWNER TO ' || tbl_owner; RETURN 'CREATE'; ELSE RETURN 'SKIP'; END IF; END $$ LANGUAGE plpgsql STRICT; SELECT conditional_create_table( 'public', 'post_codes', '(area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH (OIDS=FALSE)', 'postgres' ); conditional_create_table -- CREATE (1 row) SELECT conditional_create_table( 'public', 'post_codes', '(area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH (OIDS=FALSE)', 'postgres' ); conditional_create_table -- SKIP (1 row) contrib_regression=# \d public.post_codes Table public.post_codes Column | Type | Modifiers --+---+--- area | character varying(10) | not null district | character varying(10) | not null sector | character varying(10) | not null Indexes: post_codes_pkey PRIMARY KEY, btree (area, district, sector) 8- Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Create table if not exists ... how ??
On Mon, Jul 19, 2010 at 10:33 AM, Jennifer Trey jennifer.t...@gmail.com wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes ( area character varying(10) NOT NULL, district character varying(10) NOT NULL, sector character varying(10) NOT NULL, CONSTRAINT post_codes_pkey PRIMARY KEY (area, district, sector) ) WITH ( OIDS=FALSE ); How about something like this: create function create_table_if_not_exists () returns bool as $$ BEGIN BEGIN Create table test001 (i int, t text); Exception when duplicate_table THEN RETURN FALSE; END; RETURN TRUE; END; $$ Language plpgsql; When you run it the first time, it comes back true, then false after that. It's easy enough to wrap that function in another function that does the data loading. -- 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] Inheritance and trigger/FK propagation
On Thu, Jul 15, 2010 at 4:05 AM, Davor J. dav...@live.com wrote: It seems no secret that a child table will not fire a trigger defined on it's parent table. Various posts comment on this. But nowhere could I find a reason for this. Do you want your trigger that redirects insert on parent table to the proper child table should run on child tables too? Well, inheritance is not used for partitioning ONLY. So, yes, for *my* use cases I would appreciate being able to tell triggers defined on parent tables to run on child tables when an insert/update/delete happens on a child table. (We use inheritance for auditing and for data aggregation.) But since I am not in a position to code the necessary infrastructure I won't complain about the status quo. Karsten wiki.gnumed.de -- GMX DSL: Internet-, Telefon- und Handy-Flat ab 19,99 EUR/mtl. Bis zu 150 EUR Startguthaben inklusive! http://portal.gmx.net/de/go/dsl -- 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] NASA needs Postgres - Nagios help
On Jul 13, 12:10 pm, ste...@kaltenbrunner.cc (Stefan Kaltenbrunner) wrote: Duncavage, Daniel P. (JSC-OD211) wrote: We are implementingNagioson Space Station and want to use PostgreSQL to store the data on orbit and then replicate that db on the ground. The problem is, most people use MySQL withNagios. We need an addon to ingestNagiosdata into PostgreSQL. It looks like the most reasonable implementation is to update the NDOUtils addon to support PostgreSQL. Does anyone have such an addon, or want to write one? Cool project :) I once did some work on adding proper PostgreSQL support to NDOutils but the problem is that the current code is really not too well structured for a real RDBMS(prepared statements, transactions,...) However thehttp://www.icinga.org/fork of NDOutils (IDOutils) does have some basic PostgreSQL support - maybe that will get you started. I'm theNASAproject manager for the set of computers on Space Station and we plan to deploy this capability this year. If have to write our own addon, we will, but I'd rather use something already out there. Yeah reusing code is always easier and you also don't have to maintain it one your own as well :) Stefan -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Try ZenOSS - http://community.zenoss.org/docs/DOC-3389 -- 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] NASA needs Postgres - Nagios help
Ignore the previous link. -- 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] Rescue data after power off
On Mon, Jul 19, 2010 at 5:57 PM, tuanhoanganh hatua...@gmail.com wrote: I have checked SHOW fsync, It is ON. When I view table dmvt on PgAdmin, it only has 1332 rows, but command SELECT count(*) FROM dmvt return 2449 rows. By default pgAdmin shows the number of rows listed in pg_class, which is not always accurate (for all but the smallest of tables). Right-click the table and select the count options to get an exact count. Normally, a large mismatch between those two values is a sign you need to vacuum the table. That may well not be the case if you're suffering from corruption though. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] NASA needs Postgres - Nagios help
NODutils however has no real working support for PostgreSQL, IDOutils (which I mentioned elsewhere in the thread) from the icinga fork does have basic support. The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique constraints is a bunch of work to be resolved. Next to that, the normal insert statements are not normalized (insert into ... set foo=bar instead of insert into ... () values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an adaption on sequences in Postgresql and Oracle. Fine, so there will be a lot of boring modifying of the src and associated scripts (if the license permits), but Not Supported doesn't mean it can't be done. It all depends on how much hacking one wants to do. Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will happen since there is no C source code for that available via #ifdef. Maybe I am reading it wrong, but nagios/ndoutils-1.4b9/src/db.c is loaded with #ifdef USE_PGSQL connection functions. Some of the PGSQL specific functions in ndo2db.c are commented out, but are at least there. Sean
Re: [GENERAL] NASA needs Postgres - Nagios help
Original Message Subject: Re: [GENERAL] NASA needs Postgres - Nagios help From: Sean E. Connolly connoll...@yahoo.com To: Michael Friedrich michael.friedr...@univie.ac.at, daniel.p.duncav...@nasa.gov, brian.d.mar...@nasa.gov Date: 2010-07-19 21:23 Fine, so there will be a lot of boring modifying of the src and associated scripts (if the license permits), but Not Supported doesn't mean it can't be done. It all depends on how much hacking one wants to do. Well depends if boring or not - more refreshing than libdbi it will be, just like ocilib was on Oracle. I am familiar with the code, so let's see. I've started a little research today on libpq and also prepared the IDOUtils source for usage with libpq. https://git.icinga.org/?p=icinga-core.git;a=shortlog;h=refs/heads/mfriedrich/pgsql Licensing problems shouldn't happen in case of GPL on *DOUtils. Maybe I am reading it wrong, but nagios/ndoutils-1.4b9/src/db.c is loaded with #ifdef USE_PGSQL connection functions. Some of the PGSQL specific functions in ndo2db.c are commented out, but are at least there. Yep you are right. I remembered a commit where this has been completely dropped, but in that case it was just the configure detection and AC_DEFINE routines. In IDOUtils this was gone, but as mentioned above, I've re-added that and prepared the code for libpq in order to bring this todo a bit more to reality. Kind regards, Michael -- DI (FH) Michael Friedrich Vienna University Computer Center Universitaetsstrasse 7 A-1010 Vienna, Austria email: michael.friedr...@univie.ac.at phone: +43 1 4277 14359 fax:+43 1 4277 14279 web:http://www.univie.ac.at/zid Icinga Core IDOUtils Developer http://www.icinga.org
Re: [GENERAL] NASA needs Postgres - Nagios help
Original Message Subject: Re: [GENERAL] NASA needs Postgres - Nagios help From: Duncavage, Daniel P. (JSC-OD211) daniel.p.duncav...@nasa.gov To: Michael Friedrich michael.friedr...@univie.ac.at, Martin, Brian D. (JSC-OD)[UNITED SPACE ALLIANCE LLC] brian.d.mar...@nasa.gov Date: 2010-07-19 19:35 Thank you for the time and thought. I've added Brian Martin, who is my project lead for this effort. He's a better person to converse with than I am. Ok, fine. If you need anything special (e.g. on Icinga development), you can also drop me an email offlist. Kind regards, Michael -Original Message- From: Michael Friedrich [mailto:michael.friedr...@univie.ac.at] Sent: Sunday, July 18, 2010 4:35 PM To: Duncavage, Daniel P. (JSC-OD211) Cc: pgsql-general@postgresql.org; Stefan Kaltenbrunner Subject: Re: [GENERAL] NASA needs Postgres - Nagios help Hi there, On 2010-07-15 07:06, Stefan Kaltenbrunner wrote: well - there was direct database support in nagios ages ago(nagios 1.x is ancient) and replaced with a plugin based approach based on their eventbroker architecture called NDOutils. Based on tracking internal state it can be used to export current and historical monitoring data from nagios for later postprocessing (or for usin a GUI or whatever). NODutils however has no real working support for PostgreSQL, IDOutils (which I mentioned elsewhere in the thread) from the icinga fork does have basic support. The SQL queries used in NDOUtils are highly MySQL specific, mostly the ON DUPLICATE KEY functionality based on unique constraints is a bunch of work to be resolved. Next to that, the normal insert statements are not normalized (insert into ... set foo=bar instead of insert into ... () values ()), some missing time conversion procedures and naturally the last insert id on MySQL, which needs an adaption on sequences in Postgresql and Oracle. Which means, just by changing the .sql files and the column attributes, this won't work. Not even the connection will happen since there is no C source code for that available via #ifdef. Some of those mentioned things have been resolved in Icinga IDOUtils, but not all since I had to focus on 1/ make IDOUtils more stable, less blocking and 2/ provide initial improved Oracle support. THe Postgresql support is quite basic, but based on libdbi it still works. In regard of bigger monitoring environments it will lack of performance for sure. Main reason is that the current query implementation first tries and update, and then inserts - which basically forms the on duplicate key insert or update from MySQL, but it's not really good causing two queries instead of one procedure in the worst situation. An UPSERT or MERGE procedure should replace that - sth like this: http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql (a far more better approach would be a common rewrite with a better db schema but that's future sound for existing database setups). If you are planning to use NDOUtils as basis for re-implementation for Postgresql, please be advised that the current 1.4b9 consists of some major bugs, next to mentioned performance issues with concurrent data inserts and housekeeping during startup and running. IDOUtils provides an extended housekeeping thread not to interfere with the insertions. Some blogposts on Icinga's improvements, especially on IDOUtils: http://www.icinga.org/2009/09/01/playing-with-idoutils-and-postgresql/ http://www.icinga.org/2009/10/20/icinga-idoutils-will-support-oracle-rdbm-in-1-0-rc/ http://www.icinga.org/2010/02/17/icinga-idoutils-more-improvements-part-ii/ http://www.icinga.org/2010/06/16/news-from-core-cgis-idoutils-part-i/ Our plans are to improve Postgresql support of Icinga IDOUtils within the next months mainly regarding the upsert procedure, but also by dropping the current db abstraction layer (libdbi) in order to use direct prepared statements and binded params (which is not possible with libdbi). This will be done right after some bigger core changes are finished, imho not in 1.0.3 but 1.0.4 in October would be possible. Postgresql is next to MySQL and Oracle part of RDBMS section of the unified Icinga API (written in PHP), and provides the current Icinga Core data source for the newly developed Icinga Web. For more information: http://www.icinga.org/architecture/ http://www.icinga.org/faq/icinga-vs-nagios-whats-the-difference/ That's the thing in Icinga's perspective - it's still a fork of Nagios, but as you can see a lot of things happened lately. If Icinga can be of help for you getting better Postgresql support with Icinga IDOUtils, please get in touch. We'd love to work together on a satisfying solution for you and the community :) Kind regards, Michael (Icinga Core IDOUtils Developer) -- DI (FH) Michael Friedrich michael.friedr...@univie.ac.at Tel: +43 1 4277 14359 Vienna University Computer Center
Re: [GENERAL] NASA needs Postgres - Nagios help
Peter C. Lai wrote: From the roll-your-own side, have you looked at an alternative Nagios event broker called livestatus? It's written by Matthias Kettner as part of his client-centric mk-check Nagios plugin suite. Regarding this in reflection of this email livestatus won't make that much sense. Earth is asking Space for some livedata, Space answers? Duncavage, Daniel P. (JSC-OD211) wrote: Correct. We are looking to use Nagios to monitor various parameters on our network, then store them in postgresql, which we will then synch to the ground and distribute as a quasi realtime telemetry system. But anyhow... Peter C. Lai wrote: At the moment it only brokers live data (hence livestatus), but it is intended to replace NDO as the general event broker. You can read from the socket and do whatever you want with the data... Depends on the use case. If you want something that continuously spits out data, and stores that elsewhere, without the need of initiating the output, you'd better use IDO (compared to NDO it has ~35% performance increase). If you prefer to demand data by a client application (like a web ui e.g.), livestatus fits best and performs better. You might use livestatus as a data poller too, but that implies bidirectional communication and can lead into performance issues and problems. Regarding this situation, and basically the amount of data being generated and reworked, I would consider that NASA chose Postgresql wisely as RDBMS - maybe even the monitoring backend depends on unified APIs for alerting and reporting and so on. It would be interesting how many hosts/services will be monitored and how this relates to the check rates. Kind regards, Michael -- DI (FH) Michael Friedrich Vienna University Computer Center Universitaetsstrasse 7 A-1010 Vienna, Austria email: michael.friedr...@univie.ac.at phone: +43 1 4277 14359 fax:+43 1 4277 14279 web:http://www.univie.ac.at/zid Icinga Core IDOUtils Developer http://www.icinga.org -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Create table if not exists ... how ??
-Original Message- From: Joe Conway [mailto:m...@joeconway.com] Sent: Monday, July 19, 2010 12:59 PM To: Jennifer Trey Cc: pgsql-general@postgresql.org Subject: Re: Create table if not exists ... how ?? On 07/19/2010 09:33 AM, Jennifer Trey wrote: I can't figure out the correct syntax... I have this, but it just keeps complaining about the IF IF NOT EXISTS (SELECT table_name FROM information_schema.tables where table_name = 'post_codes') THEN CREATE TABLE post_codes Probably better to do: DROP TABLE IF EXISTS post_codes; CREATE TABLE post_codes(...); See: http://www.postgresql.org/docs/8.4/interactive/sql-droptable.html HTH, Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support Joe, What you suggest is completely different from what OP asked. Jen wants to avoid getting error on CREATE TABLE in case her table already exists (but proceed with CREATE TABLE, if it doesn't). What you suggest, will drop the table (IF EXISTS), and then create it anew - what if there is already data in the table? Regards, Igor Neyman -- 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] Create table if not exists ... how ??
On 07/19/2010 01:54 PM, Igor Neyman wrote: What you suggest is completely different from what OP asked. Jen wants to avoid getting error on CREATE TABLE in case her table already exists (but proceed with CREATE TABLE, if it doesn't). What you suggest, will drop the table (IF EXISTS), and then create it anew - what if there is already data in the table? Read on -- we are way past that already... Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
[GENERAL] Insert and Retrieve unsigned char sequences using C
Hi All, I'm developing a system in C and I have a unsigned char pointer that represents a struct and I like to store it in a bytea column in postgreSQL. How can I do it? Example: str_t temp; unsigned char *ptr; ptr = (unsigned char *)temp; store(ptr); I've already tried some examples, but I didnt have success. Could you help me? Thanks! -- View this message in context: http://old.nabble.com/Insert-and-Retrieve-unsigned-char-sequences-using-C-tp29210308p29210308.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Insert and Retrieve unsigned char sequences using C
vinicius_bra wrote: I'm developing a system in C and I have a unsigned char pointer that represents a struct and I like to store it in a bytea column in postgreSQL. The pointer does not represent the struct. How can I do it? Example: str_t temp; unsigned char *ptr; ptr = (unsigned char *)temp; store(ptr); I've already tried some examples, but I didnt have success. Could you help me? You won't have any joy storing the raw pointer value, because when you restore it it'll most likely be into a different memory map and the structure to which it used to point will no longer be at the same address, if anywhere. That's because a C pointer doesn't represent a struct, or anything else other than an address. It *points to* the struct. You need to serialize the struct itself then allocate the pointer when you deserialize the struct. -- Lew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] parameterized views or return-type-inferring SQL functions?
Hi folks I've noticed a pattern in my SQL and am curious to know if others face the same thing, and if so how they're handling it. I often have the need to wrap up some complex query 'x' into a reusable unit, so I don't copy it repeatly all over the place. This query often requires one or more parameters that aren't simple WHERE clause filters, so it's not useful to make it a regular view. This query is a join between two or more tables, or has some other result for which there is no rowtype already defined, so I can't just wrap it in an SQL function that returns a particular predefined rowtype. Essentially, what I want is the behaviour of a view, which has an implicit/inferred row type, combined with the parameterization of a SQL function. I'm after a sort of parameterized view, I guess. Consider, as a simple example that I perhaps could express another way, but kind of shows the point: SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1'); I can't CREATE VIEW for that, as there's no way to provide the param $1, and a WHERE clause filtering the view's results can't have the same effect. I can't: CREATE OR REPLACE FUNCTION ab_ondate(DATE) RETURNS SETOF RECORD AS $$ SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1'); $$ LANGUAGE 'sql'; ... without having to specify an explicit column-list wherever ab_ondate is used, as even SELECT * FROM ab_ondate(current_date) will fail with: ERROR: a column definition list is required for functions returning record As far as I can tell, my options are to use an SQL function that 'RETURNS RECORD' and go through the pain of defining the column-list wherever the function is used, or CREATE TYPE to make a custom rowtype for it to return, which I then have to maintain. Both these options are far from ideal if the function wraps up a join on one or more other tables, as I might want to alter the column-list or column types of those tables at some later point. So, I'm curious about how practical it'd be to infer the type for (at least inline-able) SQL functions that return 'RECORD', avoiding the need to declare the return column-list in that case. In many cases even the ability to express a join type as a return would help, eg: CREATE OR REPLACE FUNCTION ab_ondate(DATE) RETURNS SETOF (A JOIN B) AS $$ SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON (a.id = b.a_id AND b.rundate = '$1'); $$ LANGUAGE 'sql'; ... or a way of dynamically including the column-list of one or more rowtypes in the type specifier given when calling the query, eg: SELECT * FROM ab_ondate(current_date) AS (a.*, b.*); Thoughts? Ideas? Anyone running into this regularly? Found any good solutions? -- Craig Ringer -- 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] parameterized views or return-type-inferring SQL functions?
Oh, sorry for the reply-to-self, but I know I can write an SQL function with an OUT parameter list to do this. Like creating a custom rowtype for the job, though, this gets cumbersome if the column-list is long, or changes to the input tables might ever result in a change to column-list types, add columns, etc. Being able to write: CREATE OR REPLACE FUNCTION blah(IN DATE, OUT a.*, OUT b.*) AS $$ SELECT * FROM a, b; $$ LANGUAGE 'sql' ... would be somewhat helpful as another form of the ability to expand rowtypes for use in declarations, but having to list all the OUT parameters explicitly as I currently do is no better than using CREATE TYPE to make a custom rowtype or listing all the colums at call-sites for a RECORD returning function. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
