[GENERAL] Manipulating Large Object datatype in Postgresql
Hi, We have tables with data type CLOb and BLOBs (in oracle). This needs o be migrated to Postgresql. What data types can be used for this. I've done some resaerch/search in this and found that (correct me if Im wrong) 1. For CLObs the equivalent are TEXT and OID(lob). But streaming(thro' jdbc) is possible only with oids 2. For Blobs the equivalent are oid(lob) and bytea (again straeming is possible only with oid) Questions: 1. Which is the better approach for CLOb/BLOB? I need straeming possible since the data are pretty big 2. How is UPDATE/DELETE handled with the lob datatype? Would it update the referenced data? Or would it create a new lob and update the pointer, keeping the old data somewhere? Or would that depend on our implementation? Would it make a difference if the new value was null? Thanks
[GENERAL] Oracle Help in PG?
Hi, Do we have anything like Oracle Help for Java in Postgresql? Thanks
[GENERAL] Postgres auditing features
Hi Does postgresql have any build-in auditing features like in Oracle's total-recall or fine grained auditing http://www.oracle.com/technology/products/database/oracle11g/pdf/total-recall-datasheet.pdf http://www.oracle.com/technology/deploy/security/database-security/fine-grained-auditing/index.html Thanks
[GENERAL] Good PG Books ?
Hi, I'm trying to get a good postgresql book for reference. I love the Postgresql manual but I would also like to keep a good PG book handy. Any suggestions? Thanks
[GENERAL] Convert Oracle function to PostgreSQL
Hi,
I use this Oracle function(from AskTom -
http://asktom.oracle.com/pls/asktom/f?p=100:11:0P11_QUESTION_ID:210612357425)
SQL create or replace type myTableType as table
of varchar2 (255);
2 /
Type created.
ops$tk...@dev8i create or replace
function in_list( p_string in varchar2 ) return myTableType
2 as
3 l_stringlong default p_string || ',';
4 l_data myTableType := myTableType();
5 n number;
6 begin
7loop
8exit when l_string is null;
9n := instr( l_string, ',' );
10 l_data.extend;
11 l_data(l_data.count) :=
ltrim( rtrim( substr( l_string, 1, n-1 ) ) );
12 l_string := substr( l_string, n+1 );
13end loop;
14
15return l_data;
16 end;
17 /
Function created.
ops$tk...@dev8i select *
2from THE
( select cast( in_list('abc, xyz, 012') as
mytableType ) from dual ) a
3 /
COLUMN_VALUE
abc
xyz
012
How can I convert this function into PostgreSQL ? Any thoughts?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find the query completeion time?
Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks -- 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] How to find the query completeion time?
--- On Thu, 4/2/09, Leif B. Kristensen l...@solumslekt.org wrote: From: Leif B. Kristensen l...@solumslekt.org Subject: Re: [GENERAL] How to find the query completeion time? To: pgsql-general@postgresql.org Date: Thursday, April 2, 2009, 10:53 AM On Thursday 2. April 2009, SHARMILA JOTHIRAJAH wrote: Hi, Is there a way in Postgres to find when a particular query will finish? For example, for a query like this SELECT * FROM TABLE1 Can we find out from any of the catalog tables(or any other way) when this query is likely to complete? Thanks How about EXPLAIN ANALYZE SELECT * FROM TABLE1 ? or just set \timing in the psql and run the query. This will basically execute the query and return the time taken. Is there a way to know when an already-started query will end? In ORACLE I can get that information from V$SESSION_LONGOPS view which will give the approx TIME_REMAINING to complete running queries. Is there a similar way in postgres? -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Space for pg_dump
Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Thanks -- 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] Space for pg_dump
--- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com Subject: Re: [GENERAL] Space for pg_dump To: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Tuesday, March 31, 2009, 11:49 AM On Tue, Mar 31, 2009 at 7:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: Hi, How much space does a pg_dump usually take? One of my databases is 600GB How much space do I need to dump this? Note you can find out by doing: pg_dump dbname | wc Yes...I could find the space used after creating the dump. But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Thanks Sharmila -- 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] Space for pg_dump
--- On Tue, 3/31/09, Scott Marlowe scott.marl...@gmail.com wrote: From: Scott Marlowe scott.marl...@gmail.com Subject: Re: [GENERAL] Space for pg_dump To: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Tuesday, March 31, 2009, 12:07 PM On Tue, Mar 31, 2009 at 9:57 AM, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: But I need to pre-allocate some space for storing these dumps (there are other databases too that needs to be dumped). So Im trying to find a space estimate Do you have a rough estimate of pg_dump in general... like 1/4 th of the database size or something like that...I just need a rough estimate for now Sadly, there is no exact maths for such things. If your database has tons of indexes and such, it might be 20 or 100 times bigger on disk than it will be during backup. If it's all compressible text with few indexes, it might be a 1:1 or so size. You can't really tell without running pg_dump. The advantage of doing pg_dump|wc -l is that the db doesn't have to be stored somewhere. Thanks...I started pg_dump|wc -l and its running now Another question is that wc -l gives you the no of lines...right... What is the size of each line...or how do you get the size from that? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Read a CLOB data from an Oracle table and INSERT it into a BYTEA column in Postgres using jdbc?
Hi,
Im reading from an Oracle table and inserting the values to a postgres table
thro' jdbc. Everything works fine except for the BLOB data in Oracle.
My code snippet ...
while (rs1.next())
{
for (int m=1;m=colCount;m++)
{
pstmt.setObject(m, rs1.getObject(m));
}
pstmt.executeUpdate();
connection2.commit();
System.out.println(DONE);
}
This inserts the data into teh postgres table but gives this error for a BLOB
datatype
Exception in thread main org.postgresql.util.PSQLException: ERROR: column
columnXXX is of type bytea but expression is of type bigint
Any thoughts?
Thanks
Sharmila
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sync 2 tables in 2 databases
Hi,
I have 2 postgres databases with similar structure. I want to keep some tables
in sync in these 2 databases(They can be synced just once a day). Is there a
way to archive this using function ?
Something like
Select syncTable('foo')
where syncTable is a function that compares table 'foo' in db1 with table 'foo'
in db2 and make changes(update/insert/delete) to 'foo' in db1
Thanks
Sharmila
--
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] Sync 2 tables in 2 databases
--- On Wed, 3/11/09, Scott Marlowe scott.marl...@gmail.com wrote:
Hi,
I have 2 postgres databases with similar structure. I
want to keep some tables in sync in these 2 databases(They
can be synced just once a day). Is there a way to archive
this using function ?
Something like
Select syncTable('foo')
where syncTable is a function that compares table
'foo' in db1 with table 'foo' in db2 and make
changes(update/insert/delete) to 'foo' in db1
You can either truncate it on the destination db every so
often, then
dump / restore the data back into it, drop it and restore
it, write a
simple replication script that looks for missing /
updated rows, or
my suggestion, set up replication with slony and be done
with it. Of
course, you don't mention if you need one or two way
synchronization,
which makes a big difference in how you choose to do
things.
It is just 1 way synchronization... replication with slony sounds pretty
good... ill try that out
Thanks
--
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] Sync 2 tables in 2 databases
Hi,
I have 2 postgres databases with similar structure. I
want to keep some tables in sync in these 2 databases(They
can be synced just once a day). Is there a way to archive
this using function ?
Something like
Select syncTable('foo')
where syncTable is a function that compares table
'foo' in db1 with table 'foo' in db2 and make
changes(update/insert/delete) to 'foo' in db1
You can either truncate it on the destination db every so
often, then
dump / restore the data back into it, drop it and restore
it, write a
simple replication script that looks for missing /
updated rows, or
my suggestion, set up replication with slony and be done
with it. Of
course, you don't mention if you need one or two way
synchronization,
which makes a big difference in how you choose to do
things.
Also, Is there a way to archive this using dbi_link? In Oracle I do this using
db_link..is it possible to get this done using postgres' dbi_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] How to pipe the psql copy command to Unix 'Date' command
--- On Thu, 2/19/09, Erik Jones ejo...@engineyard.com wrote: From: Erik Jones ejo...@engineyard.com Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: R Smith 4otherbusin...@comcast.net Cc: General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 11:08 PM On Feb 19, 2009, at 6:30 PM, R Smith wrote: On Feb 19, 2009, at 11:07 AM, SHARMILA JOTHIRAJAH wrote: Thanks all -Sharmila --- On Thu, 2/19/09, Erik Jones ejo...@engineyard.com wrote: From: Erik Jones ejo...@engineyard.com Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: Tom Lane t...@sss.pgh.pa.us, General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 1:31 PM On Feb 19, 2009, at 9:40 AM, SHARMILA JOTHIRAJAH wrote: --- On Thu, 2/19/09, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 12:31 PM SHARMILA JOTHIRAJAH sharmi...@yahoo.com writes: Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test| End_time time java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test Or do you really insist on doing the timestamp subtraction by hand? No...I would definitely prefer to get the time elapsed between the start of the java program and the end of the copy command... Is that possible ? If not at least the start and the end time so tht i can do the calculation myself Tom just showed you how. Use run full piped command with the unix 'time' utility. Erik Jones, Database Administrator I've used UNIX for years and I don't know what you mean by 'use run full piped command'. Sure I know pipes and scripting, but ? I would put everything needed w/ a shell script, calls to java PG, and set a var to unixtime at start and subtract it from the ending unixtime. If nothing else, I might pick up a Linux trick! Sorry, I'd started typing the sentence one say, back spaced and went to type it another and it didn't really come out right. It should have been: Run the full piped command with the unix 'time' utility. I.e. if the full piped command was: cmd1 someargs | cmd2 someargs2 | cmd3 someargs3 then run: time cmd1 someargs | cmd2 someargs2 | cmd3 someargs3 Is it possible to sent this 'time' output to a file... If I try time cmd1 someargs1 | cmd2 someargs2 output.log it doesn't sent the time to the file... Why? Thanks Sharmila -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function that returns Boolean
Hi, This is a simple function that returns a boolean .. create or replace function check_value( newValue IN VARCHAR, oldValue IN VARCHAR ) RETURN BOOLEAN as ' BEGIN IF ( newValue != oldValue) then return true; else return false; END IF; END; ' LANGUAGE 'plpgsql' But I get this error...what is wrong with this ? [Error] Script lines: 1-13 - ERROR: syntax error at or near RETURN Line: 2 Thanks Sharmila -- 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] Function that returns Boolean
Its a typo..it should be RETURNS BOOLEAN and not RETURN BOOLEAN -Sharmila --- On Mon, 2/23/09, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote: From: SHARMILA JOTHIRAJAH sharmi...@yahoo.com Subject: Function that returns Boolean To: General postgres mailing list pgsql-general@postgresql.org Date: Monday, February 23, 2009, 12:39 PM Hi, This is a simple function that returns a boolean .. create or replace function check_value( newValue IN VARCHAR, oldValue IN VARCHAR ) RETURN BOOLEAN as ' BEGIN IF ( newValue != oldValue) then return true; else return false; END IF; END; ' LANGUAGE 'plpgsql' But I get this error...what is wrong with this ? [Error] Script lines: 1-13 - ERROR: syntax error at or near RETURN Line: 2 Thanks Sharmila -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to pipe the psql copy command to Unix 'Date' command
Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test| End_time so that the output is like this Start_time 2009-02-19 11:30:15 End_time 2009-02-19 11:40:15 This should give me the time this process started and the time it ended. Is that possible? Thanks Sharmila -- 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] How to pipe the psql copy command to Unix 'Date' command
--- On Thu, 2/19/09, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 12:31 PM SHARMILA JOTHIRAJAH sharmi...@yahoo.com writes: Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test| End_time time java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test Or do you really insist on doing the timestamp subtraction by hand? No...I would definitely prefer to get the time elapsed between the start of the java program and the end of the copy command... Is that possible ? If not at least the start and the end time so tht i can do the calculation myself Thanks Sharmila -- 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] How to pipe the psql copy command to Unix 'Date' command
Thanks all -Sharmila --- On Thu, 2/19/09, Erik Jones ejo...@engineyard.com wrote: From: Erik Jones ejo...@engineyard.com Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: Tom Lane t...@sss.pgh.pa.us, General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 1:31 PM On Feb 19, 2009, at 9:40 AM, SHARMILA JOTHIRAJAH wrote: --- On Thu, 2/19/09, Tom Lane t...@sss.pgh.pa.us wrote: From: Tom Lane t...@sss.pgh.pa.us Subject: Re: [GENERAL] How to pipe the psql copy command to Unix 'Date' command To: sharmi...@yahoo.com Cc: General postgres mailing list pgsql-general@postgresql.org Date: Thursday, February 19, 2009, 12:31 PM SHARMILA JOTHIRAJAH sharmi...@yahoo.com writes: Hi, I want to find the time taken by this process ...retrieving data from oracle database using java and copying that to postgres using copy. So I need something like this Start_Time|java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test| End_time time java testCode ...|psql -c copy dummy from stdin with delimiter ',' null 'NULL' test Or do you really insist on doing the timestamp subtraction by hand? No...I would definitely prefer to get the time elapsed between the start of the java program and the end of the copy command... Is that possible ? If not at least the start and the end time so tht i can do the calculation myself Tom just showed you how. Use run full piped command with the unix 'time' utility. Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] NOVALIDATE in postgresql?
Hi
In Oracle I can use the NOVALIDATE for constraints... like this
ALTER TABLE employee ADD
CONSTRAINT emp_ck
CHECK (married IN ('Y','N')) NO VALIDATE;
When the table is already populated this will be faster. Can you do the same in
Postgresql?
Thanks
Sharmila
--
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] COPy command question
Thanks all
This is my simple java code
public class copy{
public static void main(String[] args) throws Exception
{
Connection connection1=null;
Connection connection2=null;
Statement stmt;
String driverName1=org.postgresql.Driver;
Class.forName(driverName2);
connection1=DriverManager.getConnection(args[0],args[1],args[2]);
pstmt=connection1.prepareStatement(select employee_id
||','||employee_name from Employee);
ResultSet rs1=pstmt.executeQuery();
while (rs1.next())
{
System.out.println(rs1.getString(1));
}
stmt.close();
connection1.close();
}
}
And I pipe this to the psql like this
ant/bin/ant copy -emacs | sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d'|psql -c copy
employee from STDIN WITH null 'NULL' DELIMITER ',' EMP
ant/bin/ant copy -emacs - I run it using ant
sed '1,3d'|sed '$d'|sed 'N;$!P;$!D;$d trim the unnecessary ant outputs
like the 1st 2 lines and last 2 ines and any blank lines using 'sed'
so that my final output will be just the data with a 'comma' delimiter that I
feed it to the psql COPY command...
It seems to work... I havent checked the performance for big tables...Im not
sure how it scales for big tables... Do you know any other way of improving my
java code to retrieve the data fast or in batches ?
Also does COPY treat timestamp LOBs data different?
Thanks
Sharmila
--- On Thu, 2/12/09, Scott Marlowe scott.marl...@gmail.com wrote:
From: Scott Marlowe scott.marl...@gmail.com
Subject: Re: [GENERAL] COPy command question
To: sharmi...@yahoo.com
Cc: General postgres mailing list pgsql-general@postgresql.org
Date: Thursday, February 12, 2009, 1:35 PM
On Wed, Feb 11, 2009 at 11:22 AM, SHARMILA JOTHIRAJAH
sharmi...@yahoo.com wrote:
Hi,
A question about the Postgresql's COPY command.
This is the syntax of this command from the manual
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
.
I want to migrate my tables from Oracle to Postgres.
The COPY FROM command can take input from 'file' or
'STDIN'.
Is it possible for the COPY command to take its input from a
java program(which contains the oracle resultset) or any other way?
If that java program can provide direct input to postgresql then yes.
If everything has to be a prepared statement etc then no. Assuming
your java framework allows you just throw input at the database, you'd
be able to just give it the input line by line.
I know I could get the Oracle rows in a csv format but
Im trying to get it done without any file in between ?
In short is it possible to use this 'COPY' command to migrate my
tables'
data from Oracle to Postgresql without using any file
in between?
Sure, I can do it in PHP. I've done it in PHP. If your java
connectors have the facility to throw raw sql at pgsql then it should
work.
--
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] Good Delimiter for copy command
My data fields contains commas, tabs,'|' etc. So I cant use t hem as delimiters..so I need a unique may be non-character to use as a delimiter... -Sharmila --- On Tue, 2/10/09, Sam Mason s...@samason.me.uk wrote: From: Sam Mason s...@samason.me.uk Subject: Re: [GENERAL] Good Delimiter for copy command To: pgsql-general@postgresql.org Date: Tuesday, February 10, 2009, 2:41 PM On Tue, Feb 10, 2009 at 07:07:42AM -0800, SHARMILA JOTHIRAJAH wrote: What is a good delimiter to use for COPY command. Im trying to use COPY command to copy data from one table to another in 2 different databases. Can you suggest a unique delimiter that I can use for this COPY command If you're fixed with using COPY then what about just using CSV mode? COPY table TO STDOUT WITH CSV; If not then why not use pg_dump? Sam -- 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] Good Delimiter for copy command
--- On Thu, 2/12/09, Andrew Gould andrewlylego...@gmail.com wrote: From: Andrew Gould andrewlylego...@gmail.com Subject: Re: [GENERAL] Good Delimiter for copy command To: sharmi...@yahoo.com Cc: pgsql-general@postgresql.org, Sam Mason s...@samason.me.uk Date: Thursday, February 12, 2009, 4:15 PM On Thu, Feb 12, 2009 at 2:51 PM, SHARMILA JOTHIRAJAH sharmi...@yahoo.comwrote: My data fields contains commas, tabs,'|' etc. So I cant use t hem as delimiters..so I need a unique may be non-character to use as a delimiter... -Sharmila How are you backing up your data? If your backup method deals appropriately with the characters mentioned above, then backup the data and reload them into the other tables. Im not using this for loading postgres data to postgres. Im trying this method to load my Oracle data to postgresql tables just trying to migrate my data from oracle to postgresql Thanks Sharmila If your backup method does not deal appropriately with the characters you mention, you may have larger problems on your hands. To the list: Does pg_dump escape characters that are the same as the delimiter? Thanks, Andrew -- 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] dbi_link help
Hi,
Another try to get a reply for this dbi-link problem...
I was trying to look into the dbi_link.sql code and try to figure out the
problem
This is from the dbi_link.sql code
CREATE OR REPLACE FUNCTION dbi_link.add_dbi_connection_environment(
in_data_source_id BIGINT,
in_settings YAML
)
RETURNS VOID
LANGUAGE plperlU
AS $$
my ($data_source_id, $settings_yaml) = @_;
return unless (defined $settings_yaml);
my $settings = Load($settings_yaml);
warn Dump($settings) if $_SHARED{debug};
die In dbi_link.add_dbi_connection_environment, settings is a @{[
ref($settings)
]}, not an array reference
unless (ref($settings) eq 'ARRAY');
my $count = 0;
foreach my $setting (@$settings) {
die In dbi_link.add_dbi_connection_environment, setting $count is not even
a hash reference
unless (ref($setting) eq 'HASH');
die In dbi_link.add_dbi_connection_environment, setting $count does have
the proper components
unless (
exists $setting-{env_name}
exists $setting-{env_value}
exists $setting-{env_action}
);
die In dbi_link.add_dbi_connection_environment, setting $count does have
the proper right-hand sides
if (
ref($setting-{env_name}) ||
ref($setting-{env_value}) ||
ref($setting-{env_action})
);
foreach my $setting (qw(env_name env_value env_action)) {
if (defined $setting-{$setting}) {
$setting-{$setting} = $_SHARED{quote_literal}-(
$setting-{$setting}
);
}
else {
$setting-{$setting} = 'NULL';
}
}
my $sql = SQL;
INSERT INTO dbi_link.dbi_connection_environment (
data_source_id,
env_name,
env_value,
env_action
)
VALUES (
$data_source_id,
$settings-{env_name},
$settings-{env_value},
$settings-{env_action}
)
SQL
warn In dbi_link.add_dbi_connection_environment, executing:\n$sql;
my $rv = spi_exec_query($sql);
if ($rv-{status} ne 'SPI_OK_INSERT') {
die In dbi_link.add_dbi_connection_environment, could not insert into
dbi_link.dbi_connection_environment: $rv-{status};
}
}
return;
$$;
Here it is trying to insert rows into dbi_link.dbi_connection_environment table
INSERT INTO dbi_link.dbi_connection_environment (
data_source_id,
env_name,
env_value,
env_action
)
VALUES (
$data_source_id,
$settings-{env_name},
$settings-{env_value},
$settings-{env_action}
)
SQL
When I execute this sql, it runs fine and gives no errors. But if I query this
table
=# select * from dbi_link.dbi_connection_environment;
data_source_id | env_name | env_value | env_action
+--+---+
(0 rows)
it returns no rows...
Why is this table NULL ?
If anyone is using dbi-link and has some solution pls reply
Thanks
Sharmila
--- On Mon, 2/9/09, SHARMILA JOTHIRAJAH sharmi...@yahoo.com wrote:
From: SHARMILA JOTHIRAJAH sharmi...@yahoo.com
Subject: [GENERAL] dbi_link help
To: General postgres mailing list pgsql-general@postgresql.org
Date: Monday, February 9, 2009, 2:11 PM
Hi,
Im new to dbi_link. I had installed dbi_link and run the dbi_link.sql script .
This is the script that I ran after that and it didn't have any errors. Now
the schemas dbi_link and EMPLOYEE are created in my postgres database. The user
is postgres in both the databases with the same password in both.
UPDATE
pg_catalog.pg_settings
SET
setting =
CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
THEN setting
ELSE 'dbi_link,' || setting
END
WHERE
name = 'search_path'
;
SELECT make_accessor_functions(
'dbi:Oracle:database=postgres;host=...;sid=;port=1521',
'postgres',
'postgres',
'---
AutoCommit: 1
RaiseError: 1
',
NULL,
NULL,
NULL,
'employee'
);
When I try to insert into or select from the oracle database I get this error...
ERROR: error from Perl function remote_select: error from Perl function
cache_connection: DBI
connect('database=postgres;host=...;sid=...;port=1521','postgres',...) failed:
ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and
or NLS settings, permissions, etc. at line 137 at line 13.
Im not sure what this error is. I had set my ORACLE_HOME, NLS settings etc. Is
there anything else that needs to be done ?
Thanks
Sharmila
[GENERAL] COPy command question
Hi,
A question about the Postgresql's COPY command.
This is the syntax of this command from the manual
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
.
I want to migrate my tables from Oracle to Postgres.
The COPY FROM command can take input from 'file' or 'STDIN'.
Is it possible for the COPY command to take its input from a
java program(which contains the oracle resultset) or any other way?
I know I could get the Oracle rows in a csv format but
Im trying to get it done without any file in between ?
In short is it possible to use this 'COPY' command to migrate my tables'
data from Oracle to Postgresql without using any file in between?
Thanks
Sharmila
Re: [GENERAL] dbi_link help
Thanks Richard
--- On Tue, 2/10/09, Richard Huxton d...@archonet.com wrote:
From: Richard Huxton d...@archonet.com
Subject: Re: [GENERAL] dbi_link help
To: sharmi...@yahoo.com
Cc: General postgres mailing list pgsql-general@postgresql.org
Date: Tuesday, February 10, 2009, 2:51 AM
SHARMILA JOTHIRAJAH wrote:
When I try to insert into or select from the oracle database I get
this error...
ERROR: error from Perl function remote_select: error from
Perl
function cache_connection: DBI
connect('database=postgres;host=...;sid=...;port=1521','postgres',...)
failed: ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or
PATH (Windows) and or NLS settings, permissions, etc. at line 137 at
line 13.
Im not sure what this error is. I had set my ORACLE_HOME, NLS
settings etc. Is there anything else that needs to be done ?
Is ORACLE_HOME set for the backend process? That's what's trying to
connect. Try setting it globally in /etc/profile if you're having problems.
--
Yes ORACLE_HOME is set at .profile..But I still get the errors
-Sharmila
[GENERAL] Good Delimiter for copy command
Hi, What is a good delimiter to use for COPY command. Im trying to use COPY command to copy data from one table to another in 2 different databases. Can you suggest a unique delimiter that I can use for this COPY command Thanks Sharmial
[GENERAL] dbi_link help
Hi,
Im new to dbi_link. I had installed dbi_link and run the dbi_link.sql script .
This is the script that I ran after that and it didn't have any errors. Now
the schemas dbi_link and EMPLOYEE are created in my postgres database. The user
is postgres in both the databases with the same password in both.
UPDATE
pg_catalog.pg_settings
SET
setting =
CASE WHEN 'dbi_link' = ANY(string_to_array(setting, ','))
THEN setting
ELSE 'dbi_link,' || setting
END
WHERE
name = 'search_path'
;
SELECT make_accessor_functions(
'dbi:Oracle:database=postgres;host=...;sid=;port=1521',
'postgres',
'postgres',
'---
AutoCommit: 1
RaiseError: 1
',
NULL,
NULL,
NULL,
'employee'
);
When I try to insert into or select from the oracle database I get this error...
ERROR: error from Perl function remote_select: error from Perl function
cache_connection: DBI
connect('database=postgres;host=...;sid=...;port=1521','postgres',...) failed:
ERROR OCIEnvNlsCreate. Check ORACLE_HOME (Linux) env var or PATH (Windows) and
or NLS settings, permissions, etc. at line 137 at line 13.
Im not sure what this error is. I had set my ORACLE_HOME, NLS settings etc. Is
there anything else that needs to be done ?
Thanks
Sharmila
[GENERAL] ora2pg or dbi_link ?
Hi, Ive been struggling to query some of my oracle tables from postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila
[GENERAL] ora2pg or dbi_link ?
Hi, Ive been struggling to query some of my oracle tables from postgres using the dbi_link and its not working. Have anyone tried ora2pg for querying the oracle database from postgres? If so whats your feedback on that? Thanks Sharmila
[GENERAL] Heikkki's Visibility Map patch for postgres 8.4 ?
Hi, Is Heikki's Visibility Map patch included for the Postgresql 8.4 version http://archives.postgresql.org/pgsql-hackers/2007-11/msg00142.php If not whats the status of that patch? Im especially interested in the index-only scan mentioned there!!! Thanks Sharmila
[GENERAL] postgres 8.3 betat 1 version download
Hi, I want to download postgresql 8.3 beta 1 version for testing a patch. Im not able to download this from this website http://www.postgresql.org/ftp/source/v8.3beta1/ and its giving an FTP error 550 Failed to change directory Is there any other way I can get this betat1 version? Thanks sharmila Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
[GENERAL] slony error --need help
Hi I had posted this in the slony mailing list but no luck in getting any answers...Pls help me as I'm stuck with this error for the last 4 days Im trying to replicate between postgres version 7.4.18 and version 8.1.10. I configured postgres-7.4 with enable-thread-safety option I configured slony1 with this command ./configure --prefix=/export/home/josh/slony7.4 --enable-thread-safety --with-pgconfigdir=/export/home/josh/postgres7.4/bin --with-pgsourcetree=/export/home/josh/postgresql-7.4.18 When i try to execute the this script #!/bin/sh slonik _EOF_ cluster name = slony_example; node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'; node 2 admin conninfo = 'dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'; init cluster ( id=1, comment = 'Master Node'); create set (id=1, origin=1, comment='All pgbench tables'); set add table (set id=1, origin=1, id=1, fully qualified name = 'public.sample1', comment='accounts table'); store node (id=2, comment = 'Slave node'); store path (server = 1, client = 2, conninfo='dbname=$MASTERDBNAME host=$MASTERHOST user=$REPLICATIONUSER'); store path (server = 2, client = 1, conninfo='dbname=$SLAVEDBNAME host=$SLAVEHOST user=$REPLICATIONUSER'); _EOF_ I get theis error stdin:21: PGRES_FATAL_ERROR load '$libdir/xxid'; - ERROR: could not load library /export/home/josh/postgres7.4/lib/xxid.so: ld.so.1: postgres: fatal: relocation error: file /export/home/josh/postgres7.4/lib/xxid.so: symbol GetTopTransactionId: referenced symbol not found stdin:21: Error: the extension for the xxid data type cannot be loaded in database 'dbname=testdb1 host=172.31.0.67 user=josh' stdin:21: ERROR: no admin conninfo for node 134701624 The same works fine between postgresql versions 8.1.10 and 8.2.5 . Why do I get this error when replicating between versions 7.4 and8.1. Does slony1 replicate between these 2 versions? If so is there any other settings that needs to be done? Thanks in advance josh Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
[GENERAL] slony1 replication question
Hi,
I need some help with slony...
I read the slony manual and tried to replicate the database as given in this...
http://www.slony.info/documentation/firstdb.html
These are the steps that I did.
1. set the shell variables like clustername,masterdbname etc thro export command
2. created a user 'josh' with 'somepassword'
3. createdb -O $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
createdb -O $PGBENCHUSER -h $SLAVEHOST $SLAVEDBNAME
pgbench -i -s 1 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
4.createlang -h $MASTERHOST plpgsql $MASTERDBNAME
5.pg_dump -s -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME | psql -U
$REPLICATIONUSER -h $SLAVEHOST $SLAVEDBNAME
6. pgbench -s 1 -c 5 -t 1000 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
7.
slony1 was configured using --perltools option. I copied
slon_tools.conf-sample to slon_tools.conf and placed it in the /etc
dir. This is my conf file (deleted the commented lines here for
clarity)
-
if ($ENV{SLONYNODES}) {
require $ENV{SLONYNODES};
} else {
$CLUSTER_NAME = 'slony_example';
$LOGDIR = 'export/home/josh/slony1';
$MASTERNODE = 1;
add_node(node = 1,
host = 'localhost',
dbname = 'pgbench',
port = 5432,
user = 'josh',
password = 'somepassword');
add_node(node = 2,
host = 'localhost',
dbname = 'pgbenchslave',
port = 5432,
user = 'josh',
password = 'somepassword');
}
$SLONY_SETS = {
set1 = {
set_id = 1,
table_id= 1,
sequence_id = 1,
pkeyedtables = [
'accounts',
'tellers',
'branches',
],
serialtables = [history],
},
};
if ($ENV{SLONYSET}) {
require $ENV{SLONYSET};
}
1;
--
8. slonik_init_cluster | slonik
stdin:10: Set up replication nodes
stdin:13: Next: configure paths for each node/origin
stdin:16: Replication nodes prepared
stdin:17: Please start a slon replication daemon for each node
9. slon_start 1
..
Slon successfully started for cluster slony_example, node node1
Start the watchdog process as well...
10.slon_start 2
...
Slon successfully started for cluster slony_example, node node2
PID [16697]
Start the watchdog process as well...
11. slonik_create_set 1
cluster name = slony_example;
.
echo 'All tables added';
12. slonik_subscribe_set 1 2 | slonik
stdin:4: PGRES_FATAL_ERROR select _slony_example.subscribeSet(1, 1, 2,
't'); - ERROR: Slony-I: subscribeSet(): set 1 not found
What is this error? What am I doing wrong here? When i check the pgbenchslave
database, the table rows are not replicated?
Please advice
Thanks
josh
Looking for last minute shopping deals?
Find them fast with Yahoo! Search.
http://tools.search.yahoo.com/newsearch/category.php?category=shopping
[GENERAL] Version Upgrade using WAL files ?
Hi, Is it possible to implement postgres version upgrade using WAL files? Currently the WAL file replication donot work between two different postgres version. Is there a posiibility to implement this using WAL files so that when I want to upgrade to the latest version I can use these WAL? Thanks Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [GENERAL] WAL shipping question
The main thing that's improved in 8.3 is the integration of pg_standby as a more rugged restore_command than most people were coding on their own: http://www.postgresql.org/docs/8.3/static/pgstandby.html You should use it instead of the example restore.sh included in the message I referenced above. How do you install pg_standby. I get the following error when i try the Makefile [EMAIL PROTECTED]:~/postgres8.3/pgsql /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 3: PROGRAM: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 4: OBJS: command not found /export/home/sjpostgresql-8.3beta3/contrib/pg_standby/Makefile: line 6: libpq_srcdir: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 6: PG_CPPFLAGS: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 7: libpq_pgport: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 7: PG_LIBS: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 9: ifdef: command not found /export/home/sjpostgresql-8.3beta3/contrib/pg_standby/Makefile: line 10: PG_CONFIG: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 11: PG_CONFIG: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 11: shell: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 11: PGXS: command not found /export/home/sjpostgresql-8.3beta3/contrib/pg_standby/Makefile: line 12: PGXS: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 12: include: command not found /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 13: syntax error near unexpected token `else' /export/home/sj/postgresql-8.3beta3/contrib/pg_standby/Makefile: line 13: `else' Thanks sharmila Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
[GENERAL] record-based log shipping
Hi, Have anyone implemented or tried record-based log shipping? If so is there any other materials in the web other than the documentation (it has very few details about this) Thanks sharmila Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now. http://mobile.yahoo.com/;_ylt=Ahu06i62sR8HDtDypao8Wcj9tAcJ
Re: [GENERAL] WAL shipping question
This basically archives the data in the primary server itself...right!!! But how can I set up continuous archiving from primary to a directory (WAL archive directory) on the stand-by server ? The closest thing to a worked out example of how to do this I'm aware of is at http://archives.postgresql.org/sydpug/2006-10/msg1.php I tried this example I have the primary and standby server setup. The primary server is archiving the wal segments in the standby server's directory. My question is that, as per that example in step 15 the standby server should gives messages in its log whenever some WAL activity is triggered in the master. I see the archive directory in standby server getting filled up, but there are no log messages generated. Whay? What am I missing here? Also what does step 16 mean in that above example? To initiate a failover from the master to the slave, create the 'trigger file': touch ~/pg82demo/trigger This should immediately cause the slave to finish processing archived segments, exit recovery mode, and come up ready for use Does the wal archives are applied to the standby server at this point and it has all the contents(tables,indexes,tabledata etc) at this point? Thanks sharmila Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
[GENERAL] WAL shipping question
Hi, Im trying to play a bit with log shipping between 2 servers primary and standby. These servers are running versions 8.3betat3. I had set up continuous archiving at the primary server. The manuals' example of archive_command is archive_command = 'cp -i %p /mnt/server/archivedir/%f /dev/null' This basically archives the data in the primary server itself...right!!! But how can I set up continuous archiving from primary to a directory (WAL archive directory) on the stand-by server ? Thanks sharmila Looking for last minute shopping deals? Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping
Re: [GENERAL] WAL shipping question
Im trying to play a bit with log shipping between 2 servers primary and standby. These servers are running versions 8.3betat3. I had set up continuous archiving at the primary server. The manuals' example of archive_command is archive_command = 'cp -i %p /mnt/server/archivedir/%f /dev/null' This basically archives the data in the primary server itself...right!!! But how can I set up continuous archiving from primary to a directory (WAL archive directory) on the stand-by server ? a. Mount the remote directory via NFS/SMBFS/... b. Use a remote copy, e.g. scp, FTP... Thanks... I tried this command to both copy in the primary server and scp to standby server. archive_command = 'cp -i %p .../archivedir/%f scp -B %p.../archivedir/%f %p [EMAIL PROTECTED]:/archivedir/%f' cp works but scp doesnt work. Is it becoz scp generally asks for password. I can do scp or ssh without password authentication but Im not sure if it is safe to do that. What is the alternative for getting this done? Thanks sharmila Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] WAL shipping question
This basically archives the data in the primary server itself...right!!! But how can I set up continuous archiving from primary to a directory (WAL archive directory) on the stand-by server ? The closest thing to a worked out example of how to do this I'm aware of is at http://archives.postgresql.org/sydpug/2006-10/msg1.php That uses rsync as the transport mechanism for reasons it explains (the 'atomic copy' feature). You can certainly replicate that using ssh, but you may have to use a secondary directory to hold files while they're being transferred so the stand-by doesn't try to do something with the partial copies. Mounting filesystems and copying the files over Samba/NFS/etc. is another approach with its own issues. It's been my experience that remote filesystems will hang in odd ways when there's a connectivity problem, while copying with ssh/scp gives you a more predictable copied/failed return code without retrying too hard. PostgreSQL can tolerate the archive_command spitting back an error just fine and will retry automatically, I prefer not to expose the server to a situation where the archive_command might not return quickly. The main thing that's improved in 8.3 is the integration of pg_standby as a more rugged restore_command than most people were coding on their own: http://www.postgresql.org/docs/8.3/static/pgstandby.html You should use it instead of the example restore.sh included in the message I referenced above. Thanks for your reply. I will look into the pg_standby and rsync. I currently have this in my config file archive_command = 'cp -i %p /export/home/user/archivedir/%f scp -i userKey %p [EMAIL PROTECTED]:/export/home/user/archivedir/%f' userKey is a public-private key generated without the passphrase. This works... scp -i userKey dummy [EMAIL PROTECTED]:/export/home/user/archivedir and it just copies the dummy file from primary to standby without prompting for a password. It doesnt scp the wal files from primary to stand-by when i add it to archive_command as given above. I get this error Warning: Identity file userKey does not exist. ssh_askpass: exec(/usr/lib/ssh/ssh-askpass): No such file or directory Write failed: Broken pipe lost connection LOG: archive command cp -i pg_xlog/00010039 /export/home/user/archivedir/00010039 scp -i userKey pg_xlog/00010039 [EMAIL PROTECTED]:/export/home/user/archivedir/00010039 failed: return code 256 Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
[GENERAL] postgres 8.3 beta 2 storage question
Hi, 1.How does postgres version 8.3 betat 2 handle varchar and numeric data types in terms of storage I understand for varchar it has 1byte overhead (instead of 4) if length128 How does it handle for numeric? The manual says The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead. I dont understand how the overhead is measured here?...5 to 8 bytes? 2. Does postgres always inserts a complete record into a page unlike oracle whose record can span multiple pages? Does this waste space too? Thanks sharmila Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: [GENERAL] postgres 8.3 beta 2 storage question
1.How does postgres version 8.3 betat 2 handle varchar and numeric data types in terms of storage I understand for varchar it has 1byte overhead (instead of 4) if length128 How does it handle for numeric? The manual says The actual storage requirement is two bytes for each group of four decimal digits, plus five to eight bytes overhead. which manual? ( not these two: ) http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL http://www.postgresql.org/docs/8.3/static/datatype-numeric.html 2. Does postgres always inserts a complete record into a page unlike oracle whose record can span multiple pages? Does this waste space too? records larger than one page are TOASTed http://www.postgresql.org/docs/current/static/storage-page-layout.html Thanks sharmila Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: [GENERAL] Postgres table size
Hi Thanks. Ill post it on the mailing list when I get the results. Im trying to calculate and see how the tablesize works for a simple table. I have a table with 10 cols 5 varchars _ it is declared as varchar(40) but contains data of length 3 5 numeric - declared as numeric(22,0) but contains data of precision 10 There are 1 rows select * from pg_relation_size gives 1548288 bytes as the table's size select relpages from pg_class for that table gives 189 pages Calculation varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes *for 5 varchar cols = 5*7 = 35 bytes numeric (according to manual--- The actual storage requirement is two bytes for each group of four decimal digits, plus eight bytes overhead ) numeric = ( 10/4)*2 +8 = 13 bytes *for 5 numeric cols = 13 *5 = 65 *row overhead= 32 So Bytes per row = 35 + 65 +32 = 132 bytes for 1 rows = 132 The manual says ---The first 20 bytes of each page consists of a page header (PageHeaderData). There are 189 pages *page header cost = 20*189 = 3780 bytes Therefore Total = 132 (row cost) + 3780 (page header cost)=1323780 bytes for this table Is this calculation right? But the size of the table according to pg_relation_size is 1548288 bytes What am I missing in my calculation? Thanks for your help. Thanks sharmila - Original Message From: Jeff Davis [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Sent: Friday, November 16, 2007 2:12:46 PM Subject: Re: [GENERAL] Postgres table size On Fri, 2007-11-16 at 07:36 -0800, SHARMILA JOTHIRAJAH wrote: Hi, You were right. I installed beta2 and the table size now is 4682817536. Thanks How do you estimate the table size generally., ie., what is the storage size of varchar, numeric and timestamp columns and What is the row overhead? For example, If I have a table (in postgres8.2.3 version) with 10 rows and 3 columns (varchar,numeric(22,0),timestamp) , how do I estimate their storage size. How does 8.3Beta-2 handle this? this will be very helpful for me for allocating the space properly Thanks again for your help sharmila That's good news. Please post to pgsql-advocacy to show the reduction in table size that 8.3 brings for you, and the performance difference that means for you. If it helps you to choose PostgreSQL instead of some other database that would be great to mention too (however, some databases don't like you to publish benchmarks, so be careful not to violate your license). The official docs are here: http://developer.postgresql.org/pgdocs/postgres/storage.html (8.3) http://www.postgresql.org/docs/8.2/static/storage.html (8.2) The way I think about it is simple: In 8.2: * 32 bytes of row overhead: 28 byte row header + 4 byte line pointer * 4 bytes of overhead for every variable-width type: to store length In 8.3: * 28 bytes of row overhead: 24 byte row header + 4 byte line pointer * 1-4 bytes of overhead for every variable-width type: only one byte of overhead if length 127 bytes, up to 4 bytes if it is longer. Regards, Jeff Davis Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] Postgres table size
I vacuumed the table before I got this relpages count. When i make the change tat you mentioned the total table size as per my calculation is 1373780 and the result thro pg_relation_size is 1548288. What other overheads are there for the table? Im not sure how to find the free space? But I didnot delete/insert/update any rows after the first insertion. Thanks sharmila - Original Message From: Erik Jones [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 21, 2007 11:38:44 AM Subject: Re: [GENERAL] Postgres table size On Nov 21, 2007, at 9:17 AM, SHARMILA JOTHIRAJAH wrote: Hi Thanks. Ill post it on the mailing list when I get the results. Im trying to calculate and see how the tablesize works for a simple table. I have a table with 10 cols 5 varchars _ it is declared as varchar(40) but contains data of length 3 5 numeric - declared as numeric(22,0) but contains data of precision 10 There are 1 rows select * from pg_relation_size gives 1548288 bytes as the table's size select relpages from pg_class for that table gives 189 pages Calculation varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes *for 5 varchar cols = 5*7 = 35 bytes numeric (according to manual--- The actual storage requirement is two bytes for each group of four decimal digits, plus eight bytes overhead ) numeric = ( 10/4)*2 +8 = 13 bytes *for 5 numeric cols = 13 *5 = 65 Not that it will make much difference, but you need to round up in the 10/4 part so you get 14 bytes, not 13. *row overhead= 32 So Bytes per row = 35 + 65 +32 = 132 bytes for 1 rows = 132 The manual says ---The first 20 bytes of each page consists of a page header (PageHeaderData). There are 189 pages *page header cost = 20*189 = 3780 bytes Therefore Total = 132 (row cost) + 3780 (page header cost) =1323780 bytes for this table Is this calculation right? But the size of the table according to pg_relation_size is 1548288 bytes What am I missing in my calculation? One thing to note is that the relpages value is only exact from the time of a vacuum until the next dml statement on the table, i.e. relpages is not updated after inserts, update, and deletes, just after vacuums. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ
Re: [GENERAL] Postgres table size
Calculation varchar = (overhead) 4 + (actual length of string) 3 = 7 bytes *for 5 varchar cols = 5*7 = 35 bytes numeric (according to manual--- The actual storage requirement is two bytes for each group of four decimal digits, plus eight bytes overhead ) numeric = ( 10/4)*2 +8 = 13 bytes *for 5 numeric cols = 13 *5 = 65 Not that it will make much difference, but you need to round up in the 10/4 part so you get 14 bytes, not 13. Also, this calculation is ignoring the fact that (pre-8.3) varlena values have to be int-aligned, so there's wasted pad space too. The varchar values really need 8 bytes each, and the numeric values 16, so the actual data payload in each row is 120 bytes. Then add HeapTupleHeader (28 bytes), and then MAXALIGN the whole row size. So the rowsize would be either 148 or 152 bytes depending on if you were on a machine with 8-byte MAXALIGN. Then add the per-row item pointer, giving total per-row space of 152 or 156 bytes. That means you can fit either 53 or 52 rows per page, giving either 188 or 192 pages as the minimum possible file size. Evidently, Sharmila is using a MAXALIGN=4 machine and has a few dead rows in there. Thanks for the detailed explanation Tom. You are rigt...The number of pages for this tables as per pg_class is 189. I have a few questions 1. How do you find the MAXALIGN of the machine? And what is that used for? 2. How does null columns account for this space. For example, if I have the same table (10 cols --5 varchars and 5 numerics) in which 8 cols are null and 2 cols(1 varchar and 1 num) are not null, how is the storage space affected for this case? Thanks again sharmila Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: [GENERAL] Postgres table size
Thanks Tom and Erik and all the others who helped. You guys really rock!!! Sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: Erik Jones [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, November 21, 2007 1:14:02 PM Subject: Re: [GENERAL] Postgres table size SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: 1. How do you find the MAXALIGN of the machine? And what is that used for? pg_controldata will show maximum data alignment. A rule of thumb is that it's 4 on 32-bit machines and 8 on 64-bit machines, but there are exceptions. 2. How does null columns account for this space. If there are any nulls in a row then you pay for a null bitmap with 1 bit/column, but the null columns themselves aren't stored and hence take zero space. In your example the bitmap needs 10 bits, but after allowing for alignment the effect is that the heap tuple header gets 4 bytes bigger if there's any nulls. regards, tom lane Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [GENERAL] pg_dump problem
Hi The dump works now after deleting those rows from the pg_rewrite table Thanks for your help sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 3:33:20 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: looks like the OIDs are there Yeah, that makes it look more like the pg_class row went missing than that there was an intentional drop of the view. Does VACUUM VERBOSE pg_class report anything interesting? It's possible also that reindexing pg_class would fix it. regards, tom lane ---(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 Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] pg_dump problem
No...I dont have slony installed. Its not a replica sharmila - Original Message From: Andrew Sullivan [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, November 15, 2007 1:03:48 PM Subject: Re: [GENERAL] pg_dump problem On Wed, Nov 14, 2007 at 10:32:54AM -0800, SHARMILA JOTHIRAJAH wrote: Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found Is there any possibility you have Slony installed? Is this a replica? If so, it's a well-known problem. You can't use pg_dump under those circumstances. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
[GENERAL] pg_dump problem
Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Thanks sharmila Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs
Re: [GENERAL] pg_dump problem
Hi, Thanks...But I still get the same error pg_dump --oids smrs pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found sharmila - Original Message From: Joao Miguel Ferreira [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 1:41:56 PM Subject: Re: [GENERAL] pg_dump problem On Wed, 2007-11-14 at 10:32 -0800, SHARMILA JOTHIRAJAH wrote: Hi I try to use pg_dump to dump my database. pg_dump smrs and it gives me an error pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found check out the --oids option in the manuals (man pg_dump)... could help ?! and try this: pg_dump --oids smrs Cheers jmf What causes this problem? Thanks sharmila __ Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. ---(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 Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] pg_dump problem
Hi Thanks PG version is 8.2.3 I queried the pg_depend using this query select * from pg_depend where objid in (670739,670741) or refobjid in (670739,670741) looks like the OIDs are there classid objid objsubid refclassid refobjid refobjsubid deptype -- --- - --- -- -- 124767074001259 670739 0 i 125967073902615 2200 0 n 261867074101259 670739 0 i 261867074101259 670645 3 n 261867074101259 670648 3 n 261867074101259 670739 0 n so what else can cause tis problem? sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 2:21:03 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: pg_dump: failed sanity check, parent table OID 670739 of pg_rewrite entry OID 670741 not found What causes this problem? Corrupt system tables, looks like :-( What PG version is this? I would suggest checking to see if either of those OIDs appears in either the objid or refobjid columns of pg_depend. If not, the most likely theory is that this pg_rewrite entry somehow didn't get deleted when its parent table was dropped. You could just delete it manually, eg, delete from pg_rewrite where oid = 670741; If you do find other traces of the table it might be better to try to resurrect the table (actually it's most likely a view not a table). regards, tom lane Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
Re: [GENERAL] pg_dump problem
You are right. There are no rows in pg_class with oids 670739 and 670741. Is that the problem? How do I fix that. Will deleting those rows from pg_depend fix this problem? Also I have another question. Should the pg tables (like pg_class etc) generally be vacuumed regularly? Thanks sharmila - Original Message From: Tom Lane [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Wednesday, November 14, 2007 3:33:20 PM Subject: Re: [GENERAL] pg_dump problem SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: looks like the OIDs are there Yeah, that makes it look more like the pg_class row went missing than that there was an intentional drop of the view. Does VACUUM VERBOSE pg_class report anything interesting? It's possible also that reindexing pg_class would fix it. regards, tom lane Be a better pen pal. Text or chat with friends inside Yahoo! Mail. See how. http://overview.mail.yahoo.com/
[GENERAL] Postgres table size
Hi I have a table with 29384048 records in oracle and postgresql. The table has 47 columns (16 numeric and 27 varchar and the rest timestamp). The tablesize in postgresql is twice as much than the tablesize in oracle (for the same number of rows and columns). There are no updates or deletes in this table. It is a test table that is used only for querying. The tables are vacuumed regularly Even a simple seqscan query takes twice as much time in postgres than in oracle. Does postgresql generally occupy more space than oracle tables? Thanks Sharmila Get easy, one-click access to your favorites. Make Yahoo! your homepage. http://www.yahoo.com/r/hs
Re: [GENERAL] Postgresql simple query performance question
Hi, The table has 43 columns. I have attached the columns-list.They have many char() and numeric columns. For the table size, these are the corresponding entries from the pg_class foo is the table and the others are some of its indexes. relname reltuples relpages foo 2.9384E7 825699 foo_idx_pat 2.9384E7 684995 foo_idx_service 2.9384E7 433549 foo_idx_serv 2.9384E7 433435 foo_pk 2.9384E7 109057 Thanks Sharmila - Original Message From: Gregory Stark [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: Pavel Stehule [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Tuesday, November 6, 2007 8:03:48 PM Subject: Re: [GENERAL] Postgresql simple query performance question SHARMILA JOTHIRAJAH [EMAIL PROTECTED] writes: I understand that. But why is that when oracle is given a hint to do full table scan instead of using index to get the count, it is still faster than postgres when both has the same explain plan? Oracle takes 34 sec and postgres takes 1 m10 sec . Is there anything that can be done in postgresql for speeding this up? How large are the actual respective data files? What are the columns in these tables? Do you have many char() and NUMERIC columns? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning ---(end of broadcast)--- TIP 6: explain analyze is your friend __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com rmrs=# \d foo Column |Type | Modifiers ---+-+--- foo_id| numeric(22,0) | not null institution_id| numeric(22,0) | not null patient_id| numeric(22,0) | not null top_parent_service_code | character varying(40) | not null top_parent_service_sys_id | numeric(22,0) | not null physiologic_time | timestamp without time zone | not null top_parent_filler_order_id| numeric(22,0) | dewey_decimal_tree_sort_order | character varying(100) | not null sister_sequence_number| numeric(22,0) | service_code | character varying(40) | not null service_sys_id| numeric(22,0) | not null filler_order_id | numeric(22,0) | immediate_variable_id | numeric(22,0) | data_arrival_time | timestamp without time zone | default now() specimen_id | numeric(22,0) | value_type| character varying(40) | value_text_for_display| character varying(1010) | value_modifier_text | character varying(1000) | value_if_type_is_coded_code | character varying(40) | value_if_type_is_coded_sys_id | numeric(22,0) | value_if_type_is_numeric | double precision| value_if_type_is_provider_id | numeric(22,0) | value_if_type_is_location_id | numeric(22,0) | value_if_type_is_time | timestamp without time zone | status_code | character varying(40) | clinical_status_code | character varying(40) | interpretation_code | character varying(40) | off_scale_exception_code | character varying(40) | delta_check_code | character varying(40) | producer_application_id | numeric(22,0) | producer_location_id | numeric(22,0) | origination_code | character varying(40) | delivering_message_id | numeric(22,0) | deliv_sub_id | character varying(40) | deliv_value | character varying(100) | deliv_value_text | character varying(1000) | deliv_value_code_system | character varying(100) | value_quantitative_form | character varying(40) | deliv_service_code| character varying(40) | deliv_service_code_text | character varying(100) | deliv_service_code_system | character varying(40) | deliv_unit_code | character varying(40) | deliv_unit_text | character varying(100) | deliv_unit_code_system| character varying(40) | deliv_normal_range| character varying(100) | normal_range_text | character varying(100) | value_modifier_text_type_code
Re: [GENERAL] Postgresql simple query performance question
Hi we are testing with version PostgreSQL 8.2.3. We already have a production system in Oracle and we wanted to migrate it to postgresql. If some tests are already done, are the results available for us to see? Ill also check postgres 8.3 beta. Thanks again Sharmila - Original Message From: Simon Riggs [EMAIL PROTECTED] To: Bill Moran [EMAIL PROTECTED] Cc: SHARMILA JOTHIRAJAH [EMAIL PROTECTED]; pgsql-general@postgresql.org Sent: Wednesday, November 7, 2007 6:34:26 AM Subject: Re: [GENERAL] Postgresql simple query performance question On Tue, 2007-11-06 at 09:29 -0500, Bill Moran wrote: In response to SHARMILA JOTHIRAJAH [EMAIL PROTECTED]: Hi We are in the process of testing for migration of our database from Oracle to Postgresql. I hava a simple query Select count(*) from foo This is asked a lot. The quick answer is that PostgreSQL method of MVCC makes it impossible to make this query fast. Perhaps, someday, some brilliant developer will come up with an optimization, but that hasn't happened yet. What release level is being tested? It may already have happened. 8.3 is substantially faster at seq scans, so the tests should be re-run on 8.3 beta. Also, re-run the Postgres test. It should be faster the second time, even if the database server is restarted between tests. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[GENERAL] Postgresql simple query performance question
Hi We are in the process of testing for migration of our database from Oracle to Postgresql. I hava a simple query Select count(*) from foo This table has 29384048 rows and is indexed on foo_id The tables are vacuumed and the explain plan for postgresql is QUERY PLAN -- Aggregate (cost=1194020.60..1194020.61 rows=1 width=0) (actual time=68797.280..68797.280 rows=1 loops=1) - Seq Scan on foo (cost=0.00..1120560.48 rows=29384048 width=0) (actual time=0.232..60657.948 rows=29384048 loops=1) Total runtime: 68797.358 ms The explain plan for oracle is OPERATIONOBJECTACCESS_PREDICATES FILTER_PREDICATES --- SELECT STATEMENT () (null)(null)(null) SORT (AGGREGATE)(null)(null)(null) INDEX (FULL SCAN) foo_IDX_ID (null)(null) Oracle uses index for count(*) query in this case This query in Oracle takes only 5 sec and in postgresql it takes 1 min 10sec The same query in oracle without the index and full table scan(like in postgresql) has the explain plan like this and it takes 34 sec. select /*+ full(foo1) */ count(*) from foo1 OPERATIONOBJECT ACCESS_PREDICATES FILTER_PREDICATES --- -- SELECT STATEMENT () (null) (null)(null) SORT (AGGREGATE)(null) (null)(null) TABLE ACCESS (FULL) foo (null)(null) In short the query Select count(*) from foo takes the following time: Postgresql - 1m 10 sec Oracle(index scan) - 5 sec Oracle (full table scan) - 34 sec How can I speed up this query in postgresql ? The other postgres settings are postgresql max_connections = 100 shared_buffers = 5 temp_buffers = 5000 work_mem = 16384 maintenance_work_mem = 262144 fsync = on wal_sync_method = fsync effective_cache_size = 30 random_page_cost = 4 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.001 cpu_operator_cost = 0.0025 Are there any tuning that need to be done in the OS or database side? I had attached the iostat and vmstat results of postgresql Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com iostat -xz 1 Result extended device statistics device r/sw/s kr/s kw/s wait actv svc_t %w %b sd8 28.10.0 112.30.0 0.0 0.01.2 0 3 sd8 2538.30.0 67831.40.0 0.0 4.01.6 2 95 sd8 74.10.0 69762.00.0 0.0 1.7 22.4 1 99 sd8 75.90.0 66456.90.0 0.0 1.6 21.8 1 99 sd8 78.00.0 71911.60.0 0.0 1.7 21.7 1 100 sd8 84.90.0 75541.30.0 0.0 1.7 19.7 1 99 sd8 58.91.0 45594.38.0 0.0 1.1 18.4 0 65 sd8 43.10.0 37623.10.0 0.0 0.9 21.9 0 56 sd8 78.20.0 71490.00.0 0.0 1.7 21.5 1 100 sd8 76.80.0 74463.90.0 0.0 1.7 21.8 1 100 sd8 80.10.0 73141.80.0 0.0 1.6 20.3 1 100 sd8 73.14.0 66903.6 49.1 0.0 1.7 21.6 1 100 sd8 85.90.0 70751.00.0 0.0 1.7 20.3 1 100 sd8 78.00.0 71935.00.0 0.0 1.7 22.2 1 100 sd8 80.13.0 72106.0 16.5 0.0 1.7 20.2 1 100 sd8 86.70.0 69856.30.0 0.0 1.8 20.3 1 100 sd8 74.20.0 67650.10.0 0.0 1.7 22.7 1 99 sd8 77.70.0 71639.70.0 0.0 1.7 21.7 1 100 sd8 87.11.0 77258.48.0 0.0 1.7 18.9 1 100 sd8 74.00.0 52439.50.0 0.0 1.3 18.0 1 80 sd8 29.00.0 27037.50.0 0.0 0.7 23.7 0 42 sd8 73.00.0 70196.00.0 0.0 1.7 22.8 1 100 sd8 81.00.0 69751.60.0 0.0 1.6 20.5 1 99 sd8 73.10.0 70047.00.0 0.0 1.6 22.4 1 99 sd8 79.00.0 70940.30.0 0.0 1.6 20.2 1 100 sd8 90.90.0 73723.00.0 0.0 1.7 18.4 1 100 sd8 81.80.0 73863.10.0 0.0 1.7 20.7 1 100 sd8 70.20.0 63961.20.0 0.0 1.7 24.0 1 100 sd8
Re: [GENERAL] Postgresql simple query performance question
I understand that. But why is that when oracle is given a hint to do full table scan instead of using index to get the count, it is still faster than postgres when both has the same explain plan? Oracle takes 34 sec and postgres takes 1 m10 sec . Is there anything that can be done in postgresql for speeding this up? Oracle --select /*+ full(foo1) */ count(*) from foo1 OPERATIONOBJECT ACCESS_PREDICATES FILTER_PREDICATES --- -- SELECT STATEMENT () (null) (null) (null) SORT (AGGREGATE)(null) (null) (null) TABLE ACCESS (FULL) foo (null) (null) postgresql --Select count(*) from foo This table has 29384048 rows and is indexed on foo_id The tables are vacuumed and the explain plan for postgresql is QUERY PLAN -- Aggregate (cost=1194020.60..1194020.61 rows=1 width=0) (actual time=68797.280..68797.280 rows=1 loops=1) - Seq Scan on foo (cost=0.00..1120560.48 rows=29384048 width=0) (actual time=0.232..60657.948 rows=29384048 loops=1) Total runtime: 68797.358 ms Thanks again sharmila - Original Message From: Pavel Stehule [EMAIL PROTECTED] To: SHARMILA JOTHIRAJAH [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Sent: Tuesday, November 6, 2007 9:11:02 AM Subject: Re: [GENERAL] Postgresql simple query performance question Hello PostgreSQL doesn't use index for COUN(*) http://www.varlena.com/GeneralBits/18.php http://sql-info.de/en/postgresql/postgres-gotchas.html#1_7 Regards Pavel Stehule On 06/11/2007, SHARMILA JOTHIRAJAH [EMAIL PROTECTED] wrote: Hi We are in the process of testing for migration of our database from Oracle to Postgresql. I hava a simple query Select count(*) from foo This table has 29384048 rows and is indexed on foo_id The tables are vacuumed and the explain plan for postgresql is QUERY PLAN -- Aggregate (cost=1194020.60..1194020.61 rows=1 width=0) (actual time=68797.280..68797.280 rows=1 loops=1) - Seq Scan on foo (cost=0.00..1120560.48 rows=29384048 width=0) (actual time=0.232..60657.948 rows=29384048 loops=1) Total runtime: 68797.358 ms The explain plan for oracle is OPERATIONOBJECTACCESS_PREDICATES FILTER_PREDICATES --- SELECT STATEMENT () (null)(null) (null) SORT (AGGREGATE)(null)(null) (null) INDEX (FULL SCAN) foo_IDX_ID (null)(null) Oracle uses index for count(*) query in this case This query in Oracle takes only 5 sec and in postgresql it takes 1 min 10sec The same query in oracle without the index and full table scan(like in postgresql) has the explain plan like this and it takes 34 sec. select /*+ full(foo1) */ count(*) from foo1 OPERATIONOBJECT ACCESS_PREDICATES FILTER_PREDICATES --- -- SELECT STATEMENT () (null) (null) (null) SORT (AGGREGATE)(null) (null) (null) TABLE ACCESS (FULL) foo (null) (null) In short the query Select count(*) from foo takes the following time: Postgresql - 1m 10 sec Oracle(index scan) - 5 sec Oracle (full table scan) - 34 sec How can I speed up this query in postgresql ? The other postgres settings are postgresql max_connections = 100 shared_buffers = 5 temp_buffers = 5000 work_mem = 16384 maintenance_work_mem = 262144 fsync = on wal_sync_method = fsync effective_cache_size = 30 random_page_cost = 4 cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.001 cpu_operator_cost = 0.0025 Are there any tuning that need to be done in the OS or database side? I had attached the iostat and vmstat results of postgresql Thanks __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(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 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[GENERAL] creation of tables with warnings
Hi Sometimes when I create a table with the CREATE TABLE sql command, it creates a table but with some warnings For example, I create this table in Aqua studio as create table foo(col1 varchar2); gives Warnings: --- W (1): --- 0 record(s) affected The table is created. So what does this warnings mean? where and how do i check them? Thanks in advance - Be a better Globetrotter. Get better travel answers from someone who knows. Yahoo! Answers - Check it out.
Re: [GENERAL] creation of tables with warnings
sorry about that. I copied it.This is proper one create table foo(col1 varchar); i use aqua studio Thanks Scott Marlowe [EMAIL PROTECTED] wrote: On 9/17/07, SHARMILA JOTHIRAJAH wrote: Hi Sometimes when I create a table with the CREATE TABLE sql command, it creates a table but with some warnings For example, I create this table in Aqua studio as create table foo(col1 varchar2); gives Warnings: --- W (1): --- 0 record(s) affected The table is created. So what does this warnings mean? where and how do i check them? Are you sure you're using PostgreSQL? Cause in 8.2.4 I get: create table foo(col1 varchar2); ERROR: type varchar2 does not exist LINE 1: create table foo(col1 varchar2); which is what I expect. Is this a cut and paste or did you copy this by hand. Please cut and paste EXACTLY what you're typing and what postgresql is saying. Also, is this in psql or some other client? - Fussy? Opinionated? Impossible to please? Perfect. Join Yahoo!'s user panel and lay it on us.
Re: [GENERAL] oracle rank() over partition by queries
Thanks Markus Markus Schiltknecht [EMAIL PROTECTED] wrote: Hello Sharmi Joe, sharmi Joe wrote: Is there a way to get the oracle's rank() over partition by queries in postgresql? These are known as window functions. AFAIK Gavin Sherry is working on an implementation for Postgres. Regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ - Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
[GENERAL] CLOB support in postgresql
What is the best way to store clob data in postgresql? Currently I store my clob data as a text. The data consists of large xml files. When I access the text field using getString method in jdbc, the program is not able to hold a huge string in memory .If I can stream this data to and from the database it will be good. Is there a way to do this? Are there any other solution to this problem? Thanks in advance shar joe - Shape Yahoo! in your own image. Join our Network Research Panel today!
Re: [GENERAL] foreign key violation error with partitioned table
The postgresql partitions is done using inheritance . So basically your master table is empty and the child tables(partitions) contains all the records...right. You can check if your master table contains any records by using this query SELECT * FROM ONLY master_table_name This will return zero if your master table is empty. So when you reference your master table from other tables, it will always give FK violation since your master table is empty and the recs are in child tables only. Shar joe gunce orman [EMAIL PROTECTED] wrote: hello, i have a partitioned table t_kayit with 6 partitions and kayit_id is primary key on this table. My other t_vto_sonuclari table use that kayit_id as foreign key. I'm trying to insert values which contains kayit_id to t_vto_sonuclari and i'm sure those kayit_ids are in t_kayit table too but when i'm inserting , i had error. ERROR: insert or update on table t_vto_sonuclari violates foreign key constraint fk_t_kayit_kayit_id DETAIL: Key(kayit_id)=(54168) is not present in table t_kayit I created a new test table which is totally same as t_kayit but non partitioned . I create new foreign key on that t_vto_sonuclari which refers to the new test table. In that case i didn't had that error.I could insert. what am i supposed to do for partitioned table? - Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
[GENERAL] how to find the number of rows inserted into the master table?
Hi, I have a master table 'Master' with 3 partition tables 'child1', 'child2',' child3' which inherits the master table 'Master'. I have check constraints in the child tables to insert the appropriate values and also there are functions and triggers defined to do this. My question is, if I insert a row into master, the row correctly gets inserted into the child tables. But the output always shows '0 rows inserted' since the rows are not actually inserted in to the master. Is there a way to show the number of rows inserted into the master tables (though in reality the rows get inserted into the partitions and not the master) Thanks in advance shar jo - Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
