Re: [SQL] help with version checking
In GNUmed we have created a function gm_concat_table_structure() in http://cvs.savannah.gnu.org/viewcvs/gnumed/gnumed/server/sql/gmSchemaRevisionViews.sql?rev=1.6&root=gnumed&view=log which returns a reproducable, human-readable TEXT concatenation of all the relevant parts of the schema. We then do select md5(gm_concat_table_structure()); and compare the output to known hashes for certain schema versions. That way we don't simply "believe" what is in a table "current_version" but rather actually *detect* (within reasonable limits) the version. It works well so far, no noticably delay even during client startup (which does the check and complains on mismatches). It may not scale particularly well to very large schemata, possibly. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] help with version checking
Hi all, Thanks for all replies, taking into account all your suggestions and my google research I arrived to the next script. I'd like to know your opinion. Hopefully this will be useful for somebody else. --used to stop the script execution on any error \set ON_ERROR_STOP 1 --disable the autocommit \set AUTOCOMMIT off BEGIN; /* Helper function used to check the current version. If it isn't the expected then raise an error an abort the installation. */ CREATE OR REPLACE FUNCTION check_version() RETURNS void AS ' DECLARE current_version VARCHAR; needed_version VARCHAR; BEGIN --define the expected version needed_version := ''1.0.0.0''; SELECT version INTO current_version FROM agenda_version WHERE id = 1; IF current_version <> needed_version THEN RAISE EXCEPTION ''This script needs Agenda version %, detected version %'', needed_version, current_version; RETURN; END IF; RETURN; END; ' LANGUAGE 'plpgsql'; /* Helper function used update the version to the current version. */ CREATE OR REPLACE FUNCTION update_version() RETURNS void AS' DECLARE current_version VARCHAR; BEGIN current_version := ''1.0.0.1''; UPDATE agenda_version set version = current_version where id = 1; RETURN; END; ' LANGUAGE 'plpgsql'; /* The first action ALWAYS MUST BE SELECT check_version() to ensure that the current version is the one needed for this changes script. */ SELECT check_version(); /* All the actions that must be performed by the changes script */ /* The last actions ALWAYS MUST BE: SELECT update_version(); DROP FUNCTION check_version(); DROP FUNCTION update_version(); to update the script version and remove the helper functions */ SELECT update_version(); DROP FUNCTION check_version(); DROP FUNCTION update_version(); --close the transaction END; -- Arnau ---(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
[SQL] How to reduce a database
Hi list, I have an openNMS server that uses a Postgres database. For those who are not familiar, openNMS is an open source network management product. Anyway, the openNMS database is very large now, more than 25GB (considering all tables) and I am starting to have disk space issues. The openNMS product has a vacuumdb procedure that runs every 24 hours and reads a vacuumd-configuration.xml file for parameters on what to do. The problem is that this process is not reducing the database size. What I need to do is to delete some records based on timestamp fileds or something like that. I don't know how to do it though. Can you guys help me with some command line examples? There is this table, called EVENTS, that have the following structure: eventid | integer | not null eventuei| character varying(256) | not null nodeid | integer | eventtime | timestamp without time zone | not null eventhost | character varying(256) | eventsource | character varying(128) | not null ipaddr | character varying(16) | eventdpname | character varying(12) | not null eventsnmphost | character varying(256) | serviceid | integer | eventsnmp | character varying(256) | eventparms | text| eventcreatetime | timestamp without time zone | not null eventdescr | character varying(4000) | eventloggroup | character varying(32) | eventlogmsg | character varying(256) | eventseverity | integer | not null eventpathoutage | character varying(1024) | eventcorrelation| character varying(1024) | eventsuppressedcount| integer | eventoperinstruct | character varying(1024) | eventautoaction | character varying(256) | eventoperaction | character varying(256) | eventoperactionmenutext | character varying(64) | eventnotification | character varying(128) | eventtticket| character varying(128) | eventtticketstate | integer | eventforward| character varying(256) | eventmouseovertext | character varying(64) | eventlog| character(1)| not null eventdisplay| character(1)| not null eventackuser| character varying(256) | eventacktime| timestamp without time zone | I was thinking about using a DELETE FROM EVENTS WHERE eventtime = ..but I am kind of worried on what this could cause on other tables, if there is some relations between them or something. Here is the vacuumd-configuration.xml file: DELETE FROM node WHERE node.nodeType = 'D'; DELETE FROM ipInterface WHERE ipInterface.isManaged = 'D'; DELETE FROM if Services WHERE ifServices.status = 'D'; DELETE FROM even ts WHERE NOT EXISTS (SELECT svclosteventid FROM outages WHERE svclosteventid = events.eventid UNION SELECT svcregainedeventid FROM out ages WHERE svcregainedeventid = events.eventid UNION SELECT eventid FROM notifications WHERE eventid = events.eventid) AND eventtime & lt; now() - interval '6 weeks'; Any help is appreciated. Thank you. Mario __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [SQL] How to reduce a database
On fös, 2006-12-29 at 07:09 -0800, Mario Behring wrote: > Anyway, the openNMS database is very large now, more than 25GB > (considering all tables) and I am starting to have disk space issues. > The openNMS product has a vacuumdb procedure that runs every 24 hours > and reads a vacuumd-configuration.xml file for parameters on what to > do. > The problem is that this process is not reducing the database size. > What I need to do is to delete some records based on timestamp fileds > or something like that. I don't know how to do it though. before you start deleting random rows in a database you are not too familiar with, let us start by trying to determine your actual problem. you should issue a manual VACUUM VERBOSE, and look at the output of that first. possibly you will find that the database is suffering from bloat due to too small fsm settings. if that is the case, a VACUUM FULL might be indicated, but note that it will take exclusive locks, so you should schedule that for some time where you can afford downtime. if this is not enough, then you can take a look at deleting rows, but you would probably get better advice on that from the OpenNMS community. I assume they have mailinglists or forums. > > DELETE FROM node > WHERE node.nodeType = 'D'; > DELETE FROM > ipInterface WHERE ipInterface.isManaged = 'D'; > DELETE FROM if > Services WHERE ifServices.status = 'D'; > DELETE FROM even > ts WHERE NOT EXISTS (SELECT svclosteventid FROM outages WHERE > svclosteventid = events.eventid UNION SELECT svcregainedeventid FROM > out ages WHERE svcregainedeventid = events.eventid UNION SELECT > eventid FROM notifications WHERE eventid = events.eventid) AND > eventtime & lt; now() - interval '6 weeks'; > these all seem to be deletes (no VACUUMs), so you might want to check if vacuumd is actually running to be 100% sure. also, what version postgres is this ? gnari > ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] I would like to alter the COPY command
What I have is data with two different characters for "start quote" and "end quote". In my case it's '[' and ']', but it could be anything from "smart quotes", to parentheses, to brackets, braces, ^/$ in regexps, etc. I think this isn't too unreasonable a feature to have to make copy more functional when importing data that is difficult to transform properly beforehand (in my case is about half a terabyte of log files, which takes hours and hours, just to cat, let alone reparse and dump into COPY). Now, in my case I can just say "cat file | tr '[]' '""' | psql -f import.sql", but then I lose the ability for psql to do anything smart like using mmap (I'm making assumptions that it does anything smart like that, but even if it doesn't now, it could some day). So, I'm a passable c/c++ programmer, when I have to be, so theoretically I can do the work myself, but I have never touched postgres before, so I don't know where to begin. Any ideas how to add this? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[SQL] domains and serial
Is it not possible to set up a domain using this construct? I will have a lot of tables using integer and big integer types as primary key identifier fields. It would make things simpler if I can use the serial construct in a domain instead of using an int or bigint in the domain, and then change each field in each table requiring a serial or bigserial construct. I just tried to create a domain using a bigserial type. CREATE DOMAIN Identifier_DM AS bigserial; I received the error message: ERROR: type "bigserial" does not exist SQL state: 42704 I tried to run this in both pgadminIII and in data architect 3.5. Thanks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] help with version checking
I'd probably make a small change to make this a little cleaner.
Specifically, change check_version() to take an argument, which is the
needed version, and check this against the current value in
agenda_version, throwing the exception if they don't match. Once you've
written this, you'll never need to touch it again (no more DROP
FUNCTIONs required).
Then, at the end of your update script, you update the version in the
table via normal SQL (no need for a single-use function that does this).
With these tweaks, your update scripts could be simpler, like this:
BEGIN;
SELECT check_version('1.0.0.0');
-- Do all your updates etc. here --
UPDATE agenda_version SET version = '1.0.0.1' WHERE id =1;
COMMIT;
HTH. Good luck...
-chris
Arnau wrote:
Hi all,
Thanks for all replies, taking into account all your suggestions and
my google research I arrived to the next script. I'd like to know your
opinion. Hopefully this will be useful for somebody else.
--used to stop the script execution on any error
\set ON_ERROR_STOP 1
--disable the autocommit
\set AUTOCOMMIT off
BEGIN;
/*
Helper function used to check the current version. If it isn't
the expected then raise an error an abort the installation.
*/
CREATE OR REPLACE FUNCTION check_version() RETURNS void AS '
DECLARE
current_version VARCHAR;
needed_version VARCHAR;
BEGIN
--define the expected version
needed_version := ''1.0.0.0'';
SELECT version INTO current_version FROM agenda_version WHERE id
= 1;
IF current_version <> needed_version THEN
RAISE EXCEPTION ''This script needs Agenda version %, detected
version %'', needed_version, current_version;
RETURN;
END IF;
RETURN;
END;
' LANGUAGE 'plpgsql';
/*
Helper function used update the version to the current version.
*/
CREATE OR REPLACE FUNCTION update_version() RETURNS void AS'
DECLARE
current_version VARCHAR;
BEGIN
current_version := ''1.0.0.1'';
UPDATE agenda_version set version = current_version where id = 1;
RETURN;
END;
' LANGUAGE 'plpgsql';
/*
The first action ALWAYS MUST BE SELECT check_version() to ensure
that the current version is the one needed for this changes script.
*/
SELECT check_version();
/*
All the actions that must be performed by the changes script
*/
/*
The last actions ALWAYS MUST BE:
SELECT update_version();
DROP FUNCTION check_version();
DROP FUNCTION update_version();
to update the script version and remove the helper functions
*/
SELECT update_version();
DROP FUNCTION check_version();
DROP FUNCTION update_version();
--close the transaction
END;
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] [GENERAL] NEED URGENT HELP....
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record
it would match ur query against '1,4' for the corressponding field in the
table.
do u really have one such value for that field in your table, i mean '1,4'
??
it won't search for 1 and 4 separately if that is what you want your query
to work.
~Harpreet
On 12/21/06, Sandip G <[EMAIL PROTECTED]> wrote:
I am using PostgreSql 8.1 with pgAdmin III. OS is XP.
this is my function:
CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROMT_PHASE
WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
LANGUAGE 'sql' VOLATILE;
When I run
select * from sp_get_phase ('sandip', 'oms', '4') returns 1
record.this works fine
select * from sp_get_phase ('sandip', 'oms', '1') returns 1
record.this also works fine... BUT
select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record.
I tried to execute the SQL statement from the function
SELECT BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
AddInfo1, AddInfo2
FROMT_PHASE
WHERE (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany')
AND BOOK_NO IN (1,4)
- This Works fine... returns 2 records. What may be the problem?
Thanks in advance.
Regards,
Sandip.
--
Re: [SQL] domains and serial
BillR wrote:
> Is it not possible to set up a domain using this construct? I will have
> a lot of tables using integer and big integer types as primary key
> identifier fields. It would make things simpler if I can use the serial
> construct in a domain instead of using an int or bigint in the domain,
> and then change each field in each table requiring a serial or bigserial
> construct.
>
> I just tried to create a domain using a bigserial type.
>
> CREATE DOMAIN Identifier_DM AS bigserial;
>
> I received the error message:
>
> ERROR: type "bigserial" does not exist
> SQL state: 42704
>
> I tried to run this in both pgadminIII and in data architect 3.5.
Yea, that isn't going to work because if you did that, all columns
created with that domain name would have the same default sequence.
See:
test=> CREATE TABLE test(x bigserial);
NOTICE: CREATE TABLE will create implicit sequence "test_x_seq" for
serial column "test.x"
CREATE TABLE
test=> \d test
Table "public.test"
Column | Type |Modifiers
++--
x | bigint | not null default nextval('test_x_seq'::regclass)
I don't see how we would ever get that working for domains. The best we
could do would be to have each new domain reference create a new
sequence and default string, but then you just have the bigserial
behavior in a domain, which doesn't seem worth it.
--
Bruce Momjian [EMAIL PROTECTED]
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
