[SQL] Database recovery in postgres 7.2.4.
Hi All. My setup is as follows: OS: Sun Solaris 5.8. Postgres: 7.2.4 I have very large database, which contain 15 tables and each table is contain more than 10,00,000 records. My application is parsing text data files and inserting records into database. When this process was running last night, machine was got down because of power failure. Today when I come back to office and I have compaired record count in data files and in database and find that some records are missing in database. Then I have checked postgres log and found log similar to as follows: = DEBUG: The DataBase system was not properly shut down Automatic recovery is in progress... DEBUG: Redo starts at (0, 1064) = I have read some WAL related stuff on postgres site but not found any solution to recover those uncommited records. Can any one tell me how to recover those missing records? Thanks, Santosh ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Queyring for columns which are exist in table.
Hi All,
I want to fire a query such that if the particular column does not exist
then query should return some default value.
For that I have tried following experiment.
CREATE TABLE tbl (
c1 integer,
c2 integer,
c3 integer
);
INSERT INTO tbl VALUES (1, 2, 3);
INSERT INTO tbl VALUES (2, 3, 4);
INSERT INTO tbl VALUES (3, 4, 5);
INSERT INTO tbl VALUES (4, 5, 6);
INSERT INTO tbl VALUES (5, 6, 7);
INSERT INTO tbl VALUES (6, 7, 8);
INSERT INTO tbl VALUES (7, 8, 9);
INSERT INTO tbl VALUES (8, 9, 10);
CREATE OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS
INTEGER AS E'
DECLARE columnCount INTEGER;
BEGIN
SELECT COUNT (pg_attribute.attname) into columnCount FROM
pg_attribute,pg_class, pg_type WHERE
((pg_attribute.attrelid=pg_class.oid) AND
(pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname = $1) AND
(pg_attribute.attname = $2));
IF columnCount = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION checkColumn(name,name,name);
CREATE OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS
E'
DECLARE isColumnExist INTEGER;
BEGIN
SELECT ColumnAlreadyExists ($1,$2) into isColumnExist;
IF isColumnExist = 0 THEN
RETURN name($3);
ELSE
RETURN name($2);
END IF;
END;
' LANGUAGE 'plpgsql';
Function checkColumn should return proper column name (second parameter)
if column exist and third parameter if column not exist.
NOW when I try to execute following command it returns improper result.
I expect proper column values as a output of query.
SELECT(checkColumn('tbl','c2','0'))::name FROM tbl;
mydb=# SELECT (checkColumn('tbl','c2','0'))::name FROM tbl;
checkcolumn
-
c2
c2
c2
c2
c2
c2
c2
c2
(8 rows)
mydb=#
Above query should return actual values present for c2 column in tbl.
But it's not working as desired.
Please help me in this.
Thanks in advance,
Santosh.
[SQL] Queyring for columns which are exist in table.
Hi All,
I want to fire a query such that if the particular column does not exist
then query should return some default value.
For that I have tried following experiment.
SETUP details: Platform : Sun Solaris 5.10
Postgres : 8.3.7
CREATE TABLE tbl (
c1 integer,
c2 integer,
c3 integer
);
INSERT INTO tbl VALUES (1, 2, 3);
INSERT INTO tbl VALUES (2, 3, 4);
INSERT INTO tbl VALUES (3, 4, 5);
INSERT INTO tbl VALUES (4, 5, 6);
INSERT INTO tbl VALUES (5, 6, 7);
INSERT INTO tbl VALUES (6, 7, 8);
INSERT INTO tbl VALUES (7, 8, 9);
INSERT INTO tbl VALUES (8, 9, 10);
CREATE OR REPLACE FUNCTION ColumnAlreadyExists(name, name) RETURNS
INTEGER AS E'
DECLARE columnCount INTEGER;
BEGIN
SELECT COUNT (pg_attribute.attname) into columnCount FROM
pg_attribute,pg_class, pg_type WHERE
((pg_attribute.attrelid=pg_class.oid) AND
(pg_attribute.atttypid=pg_type.oid) AND (pg_class.relname = $1) AND
(pg_attribute.attname = $2));
IF columnCount = 0 THEN
RETURN 0;
END IF;
RETURN 1;
END;
' LANGUAGE 'plpgsql';
DROP FUNCTION checkColumn(name,name,name);
CREATE OR REPLACE FUNCTION checkColumn(name, name, name) RETURNS name AS
E'
DECLARE isColumnExist INTEGER;
BEGIN
SELECT ColumnAlreadyExists ($1,$2) into isColumnExist;
IF isColumnExist = 0 THEN
RETURN name($3);
ELSE
RETURN name($2);
END IF;
END;
' LANGUAGE 'plpgsql';
Function checkColumn should return proper column name (second parameter)
if column exist and third parameter if column not exist.
NOW when I try to execute following command it returns improper result.
I expect proper column values as a output of query.
SELECT(checkColumn('tbl','c2','0'))::name FROM tbl;
mydb=# SELECT (checkColumn('tbl','c2','0'))::name FROM tbl;
checkcolumn
-
c2
c2
c2
c2
c2
c2
c2
c2
(8 rows)
mydb=#
Above query should return actual values present for c2 column in tbl.
But it's not working as desired.
Please help me in this.
Thanks in advance,
Santosh.
Re: [SQL] [GENERAL] Queyring for columns which are exist in table.
Hi Thom, Thank you for your response. I have a application which is periodically gathering diff stats from diff devices and put them into database. Tables are created per stat, per device and per day. e.g. stat1_dev1_20100125, stat1_dev1_20100126, stat1_dev1_20100127, etc. stat1_dev2_20100125, stat1_dev2_20100126, stat1_dev2_20100127, etc. stat2_dev1_20100125, stat2_dev1_20100126, stat2_dev1_20100127, etc. stat2_dev2_20100125, stat2_dev2_20100126, stat2_dev2_20100127, etc. Now when I am upgrading my application with new version then there are some tables which are having some additional columns. In this case I have to alter each and every old tables in database with new column and it's default value. As there are large number of tables, the upgrade process is taking too much time (in days). To avoid above upgrade process I want to write a SQL statements such that it take care of newly added columns. Thanks, Santosh. -Original Message- From: Thom Brown [mailto:t...@linux.com] Sent: Thursday, January 27, 2011 3:09 PM To: Santosh Bhujbal (sabhujba) Cc: pgsql-sql@postgresql.org; pgsql-gene...@postgresql.org Subject: Re: [GENERAL] Queyring for columns which are exist in table. On 27 January 2011 07:52, Santosh Bhujbal (sabhujba) wrote: > Hi All, > > > > I want to fire a query such that if the particular column does not exist > then query should return some default value. Why do you want to do this? What is it you using this for? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
