Re: [GENERAL] How to find the number of rows deleted
After an application updates, deletes, or inserts rows, it can call SQLRowCount to determine how many rows were affected. SQLRowCount returns this value whether or not the rows were updated, deleted, or inserted by executing an UPDATE, DELETE, or INSERT statement, by executing a positioned update or delete statement, or by calling SQLSetPos. If a batch of SQL statements is executed, the count of affected rows might be a total count for all statements in the batch or individual counts for each statement in the batch. For more information, see Batches of SQL Statements and Multiple Results. The number of affected rows is also returned in the SQL_DIAG_ROW_COUNT diagnostic header field in the diagnostic area associated with the statement handle. However, the data in this field is reset after every function call on the same statement handle, whereas the value returned by SQLRowCount remains the same until a call to SQLBulkOperations, SQLExecute, SQLExecDirect, SQLPrepare, or SQLSetPos. And Tom is right Andrus [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I ran DELETE command from my ODBC client application. I want to get the number of rows deleted by this DELETE command. I read the DELETE command docs but havent found any function. Any idea ? Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to obtain the list of data table name only
Here is the definition of a view I use to retrieve all of the tables and a list of columns for tables that appear in the public schema ... I have others that only retrieve a list of the table names and the views in the public schema as well... -- View: vcat_pgcolumns -- DROP VIEW vcat_pgcolumns; CREATE OR REPLACE VIEW vcat_pgcolumns AS SELECT psut.relid AS tbloid, pa.attnum AS colid, psut.relname AS tblname, pa.attname AS colname FROM pg_stat_user_tables psut JOIN pg_attribute pa ON psut.relid = pa.attrelid WHERE psut.schemaname = 'public'::name AND pa.attstattarget = -1 ORDER BY psut.relname, pa.attnum; ALTER TABLE vcat_pgcolumns OWNER TO postgres; Sunny [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi, I am wondering if anyone can tell me how I can obtain only the list of data table in postgresql without function and other ancillary tables. I hope that I can add a tag that can filter only data table. I am using the following SQL Statement: SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES I would appreciate if anyone can enligten me on how this can be achieve. Sunny ---(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] Transparent encryption in PostgreSQL?
The point of a data base is storing ASCII or unicode not encypting the data... encrypting the data IN the database is a bad idea what happens if you ever lose the key ??? you lose ALL your data... Additionally -- encryption keys are usually machine-dependent so you lose the ability to migrate to new hardware and possibly the ability to upgrade the RDBMS engine itself... It sounds to me like your issues are really about security and access control You'd be better off using an ACL and locking down your server... ""Matt McNeil"" [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED]... Greetings, I need to securely store lots of sensitive contact information andnotes in a freely availabledatabase (eg PostgreSQL or MySQL) that will bestored on a database server which I do not have direct access to. This database will be accessed by a PHP application that I amdeveloping. However, I also need to be able to search/sort these datawith the database functions (SELECT, ORDER BY, etc) soencrypting on the client side (web application) or using encryption of specific fields would not work. (For example, I need to encryptcontacts' names, but need to be able tosearch for results by name). (Irealize I could load the entire table into memory with PHP andprocess/search/sort it there, butthat's obviously not a very good solution). Ideally I would like toencrypt entire tables.I read something about the pgcrypto contrib module, but have't been able to discern if it can do ecryption in a transparent way (e.g. so that I can do regex searches on the data). My sense is that this is a difficult problem. However, I made themistake of promising this functionality, so I'm scrambling to figure out some kind of solution. Anysuggestions? Thanks so much! Matt
Re: [GENERAL] Temp tables...
ROW.previous) THEN SELECT (ROW.amount::float / ROW.previous::float)::NUMERIC(10, 2) INTO F1; RAISE LOG 'USING FORMULA: (a b) %', F1; END IF; IF (ROW.amount = ROW.previous) THEN SELECT 1.00::NUMERIC(10, 2) INTO F1; RAISE LOG 'USING FORMULA: (a = b)%', F1; END IF; -- LOOP THROUGH THE TEMP TABLE AND UPDATE l_store_hours... FOR C IN SELECT * FROM tmphours LOOP UPDATE ONLY l_store_hours SET amount = C.amount * F1 WHERE l_store_hours.id = C.id; END LOOP; END LOOP; -- CLEAN UP AFTER YOURSELF... RAISE LOG 'LOOP COMPLETE! Dropping TEMPORARY TABLE tmphours...'; DROP TABLE tmphours; -- AND RETURN... RETURN TRUE; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; Regards, Greg Patnude - Manager, Dynamic Applications Group Data Mosaics, Inc. 2406 South Dishman-Mica Road / Suite # 6 Spokane Valley, WA 99206-6429 VOICE: (866) 904-DMSF FAX: (509) 928-4236 Michael Fuhr [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, Jul 12, 2005 at 10:52:24AM -0700, Greg Patnude wrote: Performing an update to an inherited table system from inside of a stored procedure (PLPGSQL) seems to be unusually sluggish... Is the update slower when done inside a function than when doing it directly (e.g., from psql)? That is, is the use of a function relevant, or is the update equally slow in any case? Could you post the EXPLAIN ANALYZE output for the update? The message subject is Temp tables. Are you using temporary tables, and if so, are you seeing different behavior with temporary tables than with real tables? Again, is that relevant to the problem? Does anyone have a faster solution ? I am updating 50 records and it takes approximately 4.375 seconds + or - The inherited table has an ON INSERT DO INSTEAD and there are approximately 2 million rows in the inherited table structure... Could you post the table definitions, including all indexes, rules, etc.? Do all the child tables have indexes on the column(s) used to restrict the update? As the documentation states, indexes aren't inherited, so you might need to create additional indexes on the children, indexes that you'd think would be redundant. Example: CREATE TABLE parent (id serial PRIMARY KEY); CREATE TABLE child (x integer) INHERITS (parent); INSERT INTO child (x) SELECT * FROM generate_series(1, 10); ANALYZE parent; ANALYZE child; EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50; QUERY PLAN --- Seq Scan on child (cost=0.00..1991.00 rows=41 width=14) (actual time=0.059..307.234 rows=50 loops=1) Filter: ((id = 1) AND (id = 50)) Total runtime: 309.350 ms (3 rows) EXPLAIN ANALYZE UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50; QUERY PLAN - Append (cost=0.00..2006.37 rows=52 width=14) (actual time=304.838..306.252 rows=50 loops=1) - Index Scan using parent_pkey on parent (cost=0.00..15.37 rows=11 width=10) (actual time=0.110..0.110 rows=0 loops=1) Index Cond: ((id = 1) AND (id = 50)) - Seq Scan on child parent (cost=0.00..1991.00 rows=41 width=14) (actual time=304.705..305.619 rows=50 loops=1) Filter: ((id = 1) AND (id = 50)) Total runtime: 307.935 ms (6 rows) Notice the sequential scans on child, even though we have an index on parent.id, a column that child inherits. We need to create an index on child.id as well: CREATE INDEX child_id_idx ON child (id); EXPLAIN ANALYZE UPDATE child SET id = id WHERE id BETWEEN 1 AND 50; QUERY PLAN --- Index Scan using child_id_idx on child (cost=0.00..3.65 rows=41 width=14) (actual time=0.369..1.371 rows=50 loops=1) Index Cond: ((id = 1) AND (id = 50)) Total runtime: 6.100 ms (3 rows) EXPLAIN ANALYZE UPDATE parent SET id = id WHERE id BETWEEN 1 AND 50; QUERY PLAN Append (cost=0.00..19.02 rows=52 width=14) (actual time=0.119..1.895 rows=50 loops=1) - Index Scan using parent_pkey on parent (cost=0.00..15.37 rows=11 width=10) (actual time=0.037..0.037 rows=0 loops=1) Index Cond: ((id = 1) AND (id = 50)) - Index Scan using child_id_idx on child parent (cost=0.00..3.65 rows=41 width=14) (actual time=0.066..1.320 rows=50 loops=1) Index Cond: ((id = 1) AND (id = 50)) Total runtime: 7.820 ms (6 rows) If that's not the problem
[GENERAL] Temp tables...
Performing an update to an inherited table system from inside of a stored procedure (PLPGSQL) seems to be unusually sluggish... Does anyone have a faster solution ? I am updating 50 records and it takes approximately 4.375 seconds + or - The inherited table has an ON INSERT DO INSTEAD and there are approximately 2 million rows in the inherited table structure... Any ideas ? Suggestions ? Regards, Greg Patnude - Manager, Dynamic Applications Group Data Mosaics, Inc. 2406 South Dishman-Mica Road / Suite # 6 Spokane Valley, WA 99206-6429 VOICE: (866) 904-DMSF FAX: (509) 928-4236 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Checkpoints are occurring too frequently...
LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checkpoints are occurring too frequently...
Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. G. Patnude ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checkpoints are occurring too frequently...
-Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 11:40 AM To: Greg Patnude Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Checkpoints are occurring too frequently... On Tue, 2005-07-12 at 13:29, Greg Patnude wrote: Scott Marlowe [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On Tue, 2005-07-12 at 13:04, Greg Patnude wrote: LOG: checkpoints are occurring too frequently (19 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (11 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. LOG: checkpoints are occurring too frequently (12 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. What does this mean and what causes it ??? Lots of updates or inserts. What are you doing with your database? Are you running some application that is doing a lot of small inserts maybe? ---(end of broadcast)--- TIP 6: explain analyze is your friend Sort of... I have a stored procedure that gets called and updates either 50 or 75 records in a table. The parent table has an inherited table (history) derived from it with an update rule on the parent table that inserts a copy of the unchanged record (prior to the update) into the history table. So -- for every 50 updates -- I expect to see 50 inserts into the child. So, how often is this running? Once a second, once a minute, once and hour? If it's only running once an hour, then something else is wrong. [GP-] I've been running it about 2 or 3 times a minute on average... I increased the checkpoint_segments param in postgreSQL.conf from the factory default' of 1 to 5... this seems to clear up most of the noise Greg Patnude ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Passing a table name to a function for dynamic queries....
I know I've seen the answer to this here before but cannot seem to find
it Can anyone give me the quick dirty answer to passing a table name
to a function like this:
CREATE OR REPLACE FUNCTION tbl_toarray(name) RETURNS text
$BODY$
DECLARE ROW RECORD;
BEGIN
SELECT ARRAY(SELECT ''id:'' || id || '', type_desc:'' || type_desc FROM
$1) INTO ROW;
RETURN NEXT ROW;
END;
$BODY$
LANGUAGE PLPGSQL;
I'd like to be able to pass the function a table name and have the contents
of the table returned as an array of text
THIS WORKS:
dmconfig=# SELECT ARRAY(SELECT '[id:' || id || ', type_desc:' || type_desc
|| ']' FROM lu_user_type WHERE active_flag);
?column?
{[id:1, type_desc:System],[id:2, type_desc:Company],[id:3,
type_desc:Administrator],[id:4, type_desc:Employee],[id:5,
type_desc:User (standalone)],[id:6, type_desc:Guest / Demo]}
(1 row)
TIA
Regards,
Greg Patnude - Manager, Dynamic Applications Group
Data Mosaics, Inc.
2406 South Dishman-Mica Road / Suite # 6
Spokane Valley, WA 99206-6429
VOICE: (866) 904-DMSF
FAX: (509) 928-4236
---(end of broadcast)---
TIP 3: 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
[GENERAL] CONVOLUTED CROSS-TAB ? UNION ? INTERSECT ? 16.9 second performance.... Is there a better way ???
Hey all I need to join two tables and retrieve a 75 X 15 column matrix for all the records in table 1 across a 13 week range so the output should look like this. I would like the missing records in Table 2 to populate the structure with 0.00... Week à Type Code Description 14 15 16 17 18 19 20 21 22 23 24 25 26 AC1 Activity 1 0.0 0.0 1.35 2.10 2.56 3.12 4.00 5.00 12.10 17.50 21.90 25.00 45.50 AC1 Activity 2 2.00 1.34 1.35 2.10 2.56 8.00 8.00 12.11 0.00 0.00 0.00 5.00 4.00 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . GROUP TOTALS 2.00 1.34 2.70 4.20 5.12 11.12 12.00 17.11 12.10 17.50 21.90 30.00 49.5 B1 Billing 1 B2 Billing 2 GROUP TOTALS The two table definitions: CREATE TABLE l_activity_type ( id serial NOT NULL PRIMARY KEY, activity_group int4 NOT NULL DEFAULT 0, type_code varchar(5) NOT NULL DEFAULT ''::character varying, type_desc varchar(50) NOT NULL DEFAULT ''::character varying, display_order int4 NOT NULL DEFAULT 0, calc_order int4 NOT NULL DEFAULT 0, create_dt timestamp NOT NULL DEFAULT now(), change_dt timestamp NOT NULL DEFAULT now(), active_flag bool DEFAULT true ) WITH OIDS; l_activity_type has 75 rows CREATE TABLE l_store_hours ( id serial NOT NULL PRIMARY KEY, l_store_id int4 NOT NULL DEFAULT 0, l_activity_type int4 NOT NULL REFERENCES l_activity_type(id), week_code int4 NOT NULL DEFAULT 0, year_code int4 NOT NULL DEFAULT 0, amount numeric(10,2) DEFAULT 0.00, create_dt timestamp NOT NULL DEFAULT now(), change_dt timestamp NOT NULL DEFAULT now(), change_id int4 DEFAULT 0, active_flag bool DEFAULT true ) WITH OIDS; l_store_hours has 2,131,250 rows approximately... So far -- I have written the following function which returns a type of storehours: DROP TYPE storehours CASCADE; CREATE TYPE storehours AS ( activity_group INTEGER, type_code VARCHAR(50), display_order INTEGER, type_desc VARCHAR(50), week_code INTEGER, amount NUMERIC(10,2) ); -- DROP FUNCTION f_storehours(); CREATE OR REPLACE FUNCTION f_storehours(integer, integer, integer, integer) RETURNS SETOF storehours AS ' DECLARE STORE ALIAS FOR $1; DECLARE START ALIAS FOR $2; DECLARE STOP ALIAS FOR $3; DECLARE YEAR ALIAS FOR $4; DECLARE ROW RECORD; DECLARE WEEK INTEGER; BEGIN FOR WEEK IN START .. STOP LOOP FOR ROW IN SELECT * FROM (SELECT LT.activity_group, LT.type_code, LT.display_order, LT.type_desc, WEEK as week_code, 0.00 AS amount FROM l_activity_type LT UNION ALL SELECT LT.activity_group, LT.type_code, LT.display_order, LT.type_desc, LH.week_code, LH.amount FROM l_activity_type LT JOIN l_store_hours LH ON LT.id = LH.l_activity_type AND LH.l_store_id = STORE AND LH.week_code = WEEK AND LH.year_code = YEAR) AS A LOOP RETURN NEXT ROW; END LOOP; END LOOP; RETURN; END; ' LANGUAGE PLPGSQL; SELECT * FROM f_storehours(365, 14, 26, 2006) ORDER BY activity_group, display_order, type_code, week_code; EXPLAIN ANALYZE yields the following: Sort (cost=62.33..64.83 rows=1000 width=211) (actual time=16726.014..16726.286 rows=1618 loops=1) Sort Key: activity_group, display_order, week_code Regards, Greg Patnude - Manager, Dynamic Applications Group Data Mosaics, Inc. 2406 South Dishman-Mica Road / Suite # 6 Spokane Valley, WA 99206-6429 VOICE: (866) 904-DMSF FAX: (509) 928-4236 - Function Scan on f_storehours (cost=0.00..12.50 rows=1000 width=211) (actual time=16720.839..16722.757 rows=1618 loops=1) Total runtime: 16726.841 ms (4 rows) ---(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] Table audit system
I use a modified form of option 3 with an ON UPDATE RULE the update rule copies the row to an inherited table... CREATE TABLE dm_user ( id SERIAL NOT NULL PRIMARY KEY, lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id), dm_user_address INTEGER NOT NULL DEFAULT 0, dm_user_email INTEGER NOT NULL DEFAULT 0, f_name VARCHAR(50) NOT NULL, m_name VARCHAR(50) NOT NULL, l_name VARCHAR(50) NOT NULL, uname VARCHAR(20) NOT NULL, upwd VARCHAR(20) NOT NULL, pwd_change_reqd BOOLEAN DEFAULT FALSE, login_allowed BOOLEAN DEFAULT TRUE, lost_passwd BOOLEAN DEFAULT FALSE, create_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_id INTEGER NOT NULL DEFAULT 0, active_flag BOOLEAN NOT NULL DEFAULT TRUE ) WITH OIDS; CREATE TABLE dm_user_history ( history_id SERIAL NOT NULL PRIMARY KEY, hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW() ) INHERITS (dm_user); CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO dm_user_history SELECT * FROM dm_user WHERE id = old.id; CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE dm_user SET active_flag = FALSE WHERE id = old.id; Scott Frankel [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is there a canonical form that db schema designers use to save changes to the data in their databases? For example, given a table with rows of data, if I UPDATE a field in a row, the previous value is lost. If I wanted to track the changes to my data over time, it occurs to me that I could, 1) copy the whole row of data using the new value, thus leaving the old row intact in the db for fishing expeditions, posterity, c. -- awfully wasteful, especially with binary data 2) enter a new row that contains only new data fields, requiring building a full set of data through heavy lifting and multiple queries through 'n' number of old rows -- overly complex query design probably leading to errors 3) create a new table that tracks changes -- the table is either wide enough to mirror all columns in the working table, or uses generic columns and API tricks to parse token pair strings, ... 4) other? Thanks Scott ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster josue [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello list, I need to define an audit system that would be easyli include or exclude certain tables, the process is a purchase order where many users changes the info in diferent ways, the requerimient is to log the stamp and user of the change on a table and additionaly log a snapshot of the the order at the time it was change, that must include any child table too, generally the order document includes the order header main table, the order detail child table, the order costs child table and the order comment history child table. So given the need to log a full snapshot not only the change of a column I ask you for ideas or suggestion to get this properly done in Postgresql. Thanks in advance, -- Sinceramente, Josué Maldonado. ... Toda violación de la verdad no es solamente una especie de suicidio del embustero, sino una puñalada en la salud de la sociedad humana. Ralph Waldo Emerson. Filósofo, ensayista, poeta y político EE.UU. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] preserving data after updates
I use a modified form of option 3 with an ON UPDATE RULE the update rule copies the row to an inherited table... CREATE TABLE dm_user ( id SERIAL NOT NULL PRIMARY KEY, lu_user_type INTEGER NOT NULL REFERENCES lu_user_type(id), dm_user_address INTEGER NOT NULL DEFAULT 0, dm_user_email INTEGER NOT NULL DEFAULT 0, f_name VARCHAR(50) NOT NULL, m_name VARCHAR(50) NOT NULL, l_name VARCHAR(50) NOT NULL, uname VARCHAR(20) NOT NULL, upwd VARCHAR(20) NOT NULL, pwd_change_reqd BOOLEAN DEFAULT FALSE, login_allowed BOOLEAN DEFAULT TRUE, lost_passwd BOOLEAN DEFAULT FALSE, create_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_dt TIMESTAMP NOT NULL DEFAULT NOW(), change_id INTEGER NOT NULL DEFAULT 0, active_flag BOOLEAN NOT NULL DEFAULT TRUE ) WITH OIDS; CREATE TABLE dm_user_history ( history_id SERIAL NOT NULL PRIMARY KEY, hist_create_dt TIMESTAMP NOT NULL DEFAULT NOW() ) INHERITS (dm_user); CREATE RULE dm_user_upd_history AS ON UPDATE TO dm_user DO INSERT INTO dm_user_history SELECT * FROM dm_user WHERE id = old.id; CREATE RULE dm_user_nodelete AS ON DELETE TO dm_user DO INSTEAD UPDATE dm_user SET active_flag = FALSE WHERE id = old.id; Scott Frankel [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Is there a canonical form that db schema designers use to save changes to the data in their databases? For example, given a table with rows of data, if I UPDATE a field in a row, the previous value is lost. If I wanted to track the changes to my data over time, it occurs to me that I could, 1) copy the whole row of data using the new value, thus leaving the old row intact in the db for fishing expeditions, posterity, c. -- awfully wasteful, especially with binary data 2) enter a new row that contains only new data fields, requiring building a full set of data through heavy lifting and multiple queries through 'n' number of old rows -- overly complex query design probably leading to errors 3) create a new table that tracks changes -- the table is either wide enough to mirror all columns in the working table, or uses generic columns and API tricks to parse token pair strings, ... 4) other? Thanks Scott ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: 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] preserving data after updates
-Original Message- From: Berend Tober [mailto:[EMAIL PROTECTED] Sent: Friday, March 04, 2005 8:47 AM To: Greg Patnude Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] preserving data after updates I use a modified form of option 3 with an ON UPDATE RULE the update rule copies the row to an inherited table... I just gotta say that THAT is one COOL use of PG inheritance! Do you find that it works well and is robust and all the good stuff it seems like would be the case? -- Berend [GP-] Thank you... ! [GP-] I find it VERY effective and completely transparent to both the programmer and the end-user... I don't use it on ALL of the tables in a given schema... ONLY the tables where end-users can manipulate / change data... [GP-] What it boils down to is that I can use it as a sort of a virtual rollback system by querying the inherited table and updating the parent table with an original value from the child -- Of course... this results in another change to the child but it can also be undone... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Database Name
Envbop [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi I've just inherited a PostgreSQL database, for which I do not have any details of, like database name or the users. This used to be a library database which was managed via a web page written in php. Its running on a Linux box. The front end was also written in php. The original writers of this application is no longer. I've just started to get familiarisd with postgresql in the last week. and have installed PostgreSQL v8.0.1 on a Windows server. And I am trying to connect to the linux box via the phppgadmin tool. as well as trying an odbc connection. Can someone tell me where I can find the database names. FWIW: phppgAdmin is a good interface if you ONLY have web-based access to the database if you need a real functionality -- I recommend pgAdmin III -- much more functional and feature rich with a better interface and the ability to extract SQL the constructs used to create the tables, etc ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pgadmin3 / postgresql newbie question
That particular error message is typically associated with a missing pg_hba.conf entry that tells the postgreSQL server which remote machines to allow connections FROM # If you want to allow non-local connections, you need to add more # host records. Also, remember TCP/IP connections are only enabled # if you enable tcpip_socket in postgresql.conf. # TYPE DATABASEUSERIP-ADDRESSIP-MASK METHOD local all all trust # IPv4-style local connections: hostall all 127.0.0.1 255.255.255.255 trust # IPv6-style local connections: hostall all ::1 :::::::trust host all all 10.1.10.23 255.255.255.255 trust Regards Jonathan Schreiter [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] hi all, running amd64 fedora core 3 w/ default postgresql 7.4.7-3. did asu - , su postgres and createdb mydb as explained in the postgresql tutorial. installed the latest pgadmin3 and am trying to connect to this database. as i wasn't sure what the FC3 default password was for postgres, i changed it to something i could remember. i can't seem to connect to the new database using pgadmin3. i have the correct IP address of the local computer, default port 5432, mydb as the initaldb, postgres as the username, and my new password as the password. i keep getting the error Error connecting to the server: could not connect to server: Connection refused Is the server running on host 192.168.1.24 and accepting TCP/IP connections on port 5432? i also verified the postgresql service is running, and that i've added the following to /etc/raddb/postgresql.conf: login = postgres password = mynewpassword and right underneath it: tcpip = true i've also disabled my local firewall and SELINUX just for kicks. and yes, i did a reboot. so...anyone know what else i can look at? many thanks! jonathan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Splitting tables or databases? That's the problem
Get a better computer to run it on in teh long-term -- that will be your best investment [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] On a 128MB RAM, 450 MHz pentium 3 server with linux gentoo and postgresql 7.4.6 on an office lan we can manage satisfactorily a db containing few tables with a cumbersome amount of data (each table is around 650 thousand records with 98 columns) relating to the year 2002. We use M$-Access and ODBC to access those data. Now similar data and amounts of records for the year 2003 need to be added. Then queries to contrast 2003 data vs. 2002 ones will be needed. In view of the poor hardware at our disposal, is it better from the standpoint of efficiency to: 1) Create a new database for 2003 with the same structure of that for 2002; 2) Appending new 2003 data to 2002 data in the same tables; 3) Creating in the original database new, separate tables to contain data for 2003. Thanks Vittorio ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] PLSQL Question regarding multiple inserts
That's the hard way
You'd be better off redefining your table structures so that postgreSQL
handles the primary keys automatically...
CREATE TABLE test (
id integer primary key not null default nextval('test_seq'),
log varchar(32) NOT NULL,
message text
) WITH OIDS;
Using this type of table def will automatically create the sequence for
you -- and always ge thte next value when you do an insert -- ensuring that
you dont have duplicate...
so you would:
INSERT INTO test ('log', 'message');
then
SELECT * FROM test;
would give you
id, log and message.
--
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
(208) 762-0762
Humble Geek [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi all. Quick and perhaps silly question, but...
I am using Pg 7.3. I am writing a function using pgplsql. This function
will
perform multiple inserts. Let's say two of the inserts are as follows:
-- id is primary key
insert into users (id, username) values (nextval('someSeq'),'somename');
-- id is also a PK
insert into log (id, uid, message) values
(nextval('someOtherSeq'),XXX,'New
Account');
Assume XXX is the id from the first insert. How do I get that number? Not
currval('someSeq') - 'cause someone else may have performed an insert -
but
the id for that specific insert.
Thanks,
HG
PS: Sorry for the cross-post...
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Moving from MySQL to PGSQL....some questions
In PGAdmin III -- you might want to UNCHECK the Display system objects option under the Display menu option -- this will prevent you from seeing all of the non-public schema's and limit your view in PGAdmin to just the databases you created... Most people dont really need to dink around with the system tables anyway... As you probably noticed -- postgreSQL is a different beast than MS Access and mySQL -- postgreSQL is a true RDBMS like Sybase, Orale, and SQL Server... postgrSQL is a true 'client/server' RDBMS -- it does not contain it's own GUI client like MS Access postgreSQL is NOT just a high-powered version of MS Access or mySQL -- there are quite a few differences -- Not to be rude -- but the postgreSQL docs (the Preface, Tutorial, and SQL Language sections) would be good for you to read... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 Karam Chand [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello I have been working with Access and MySQL for pretty long time. Very simple and able to perform their jobs. I dont need to start a flame anymore :) I have to work with PGSQL for my companies current project. I have been able to setup postgresql in my rh box and i can connect and work with psql. I even downloaded pgadmin III so that i can get to work with a GUI interface. As I starting...I see the architecture of PGSQL is quite complex...or thats what I feelmaybe its for good :) Here are some of my doubts : 1.) What is template1 and template0? I assume these are system databases. Am I right? 2.) When I create a database using CREATE DATABASE stmt. a new DB is created where it has 4 schemas and around 100 tables. These are the system tables keeping information about everything in the database? I hope I am correct :) 3.) To get all the database is the server we use query like - select datname from pg_database I means that there exists a table pg_database in all the database and all the pg_database table(s) are updated whenever a user issues CREATE DATABASE stmt. Why I am saying so coz in PgAdmin III i can see these tables in all the databases? 4.) I couldnot find any query to change the context of database like in MySQL : use database; or am i missing something? 5.) In MySQL, there are many command like show tables, show databases etc. to get object details. I cant see anything similar in PGSQL. After searching the net i find that i have to execute certain queries to fetch those queries. Is this the only way? Any help would be appreciated. Regards Karam __ Do you Yahoo!? Yahoo! Mail SpamGuard - Read only the mail you want. http://antispam.yahoo.com/tools ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Repost: Syntax - or unavailability of same - for variable join??? Can anyone help?
You might have better success with the form of HAVING and appropriate use of OR IS NULL as opposed to strict JOIN and WHERE conditions... Similar to... SELECT A.a, B.b, C.c FROM A, B, C HAVING (A.b = B.b OR B.b IS NULL) GROUP BY A.b; -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762 Ben [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I want to say: SELECT tableA.stuff,tableB.morestuff,tableC.stillmorestuff FROM tableA, LEFT OUTER JOIN tableB ON (AB match conditions) LEFT OUTER JOIN tableC ON (AC match conditions) WHERE etc However, in some cases, tableB does not have rows where the other two do (it contains credit card records... but when an order is paid by check, there is no record.) What happens with the above syntax is I don't get a row at all. Is there a way to say that if tableB has no row, I get blank columns? Maybe I'm just looking at the wrong, but I can't seem to find out how, or if, one can do this. Thanks for any input. --Ben ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
