[GENERAL] Table and index size

[Dan Black]

How can i calculate table and index size on hard disk?

Re: [GENERAL] Problem in creating a table

[Dan Black]

Try this



CREATE TABLE test.TD_ACCESSCOUNT(

   AC_YEAR NUMERIC(4,0)    NOT NULL,

   AC_MONTH    NUMERIC(2,0)    NOT NULL,

   AC_DAYS NUMERIC(2,0)    NOT NULL,

   OFFICE_ID   VARCHAR(7)    NOT NULL,
   AC_COUNT   
NUMERIC(6,0)  
DEFAULT 0,

   PRIMARY KEY(AC_YEAR,AC_MONTH,AC_DAYS,OFFICE_ID)

)

2005/5/17, Wen Guangcheng <[EMAIL PROTECTED]>:
Hi Klint,Thanks for your help.But the error message still exists even though the 2 was droped  from the varchar.--Wen- Original Message -From: "Klint Gore" <
[EMAIL PROTECTED]>To: "Wen Guangcheng" <[EMAIL PROTECTED]>Cc: 
Sent: Tuesday, May 17, 2005 4:17 PMSubject: Re: [GENERAL] Problem in creating a table> On Tue, 17 May 2005 15:47:55 +0900, "Wen Guangcheng" <[EMAIL PROTECTED]
> wrote:> > OFFICE_ID   VARCHAR2(7)NOT NULL,>> This used to be oracle?  drop the 2 from the varchar.>> klint.>> +---+-+
>
: Klint
Gore:
"Non rhyming:> : EMail   :
[EMAIL PROTECTED]  
:  slang - the:> :
Snail   :
A.B.R.I.:  possibilities  :> : Mail  University of New England   :  are useless"   :>
:   Armidale
NSW 2351 Australia :
L.J.J.  :> :
Fax : +61 2 6772
5376
:
:> +---+-+>---(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] filling database

[Dan Black]

EMS PostgreSQL Data Generator
http://sqlmanager.net/products/postgresql/datagenerator

And   you can write you own pgplsql function that fill you database with any random data.
I think it would be better and cheaper =)

2005/5/20, [EMAIL PROTECTED] <[EMAIL PROTECTED]
>:hi,Does anyone know of a tool for filling the database with alot of random data...
so that I can add millions of rows to test approx how well it will scale ?tia-http://linuxtoday.com/news_story.php3?ltsn=2004-12-08-004-32-OS-BZ-DT-0005
snip> MS Office is popular in the same way as heart disease is the most popular way to die.---(end of broadcast)---TIP 4: Don't 'kill -9' the postmaster
-- Verba volent, scripta manentMy ISP  - http://www.netbynet.ru 

Re: [GENERAL] Missing numbers

[Dan Black]

you can also try this =)

select 
  o1.doc_numero+1 as first,
  ((select doc_numero from bdocs  where id > o1.doc_numero+1 order by doc_numero limit 1))-1 as last
from bdocs  as o1 
where o1.doc_numero+1 not in (select o2.doc_numero from bdocs  as o2) 
order by doc_numero2005/5/31, josue <[EMAIL PROTECTED]>:
Hello list,I need to track down the missing check numbers in a serie, tablecontains a column for check numbers and series like this:dbalm=# select doc_numero,doc_ckseriesfk from bdocs where doc_cta=1
dbalm-# and doc_tipo='CHE' order by doc_numero;  doc_numero | doc_ckseriesfk+  19200  | 856938  19201  | 856938  19215  | 856938  19216  | 856938
  19219  | 856938Missing numbers are:from 19202 to 19214 and 19217,19218Does anyone knows a way to get that done in SQL or plpgsql, thanks inadvance--Sinceramente,Josué Maldonado.
... "De hecho el paso de compilación a objeto suele atravesar una faseintermedia en que se genera un fichero en lenguaje ensamblador y seinvoca al programa ensamblador del sistema." -- Tutorial de C
---(end of broadcast)---TIP 7: don't forget to increase your free space map settings-- Verba volent, scripta manentMy ISP  - 
http://www.netbynet.ru 

[GENERAL] Foreign keys and slow insert

[Dan Black]

I read in documentation  that primary key doesn't require additional indexes but I could find nothing about foreign keys.
Do I need to create additional indexes when I create foreign keys?
Example:
create table master
{
  master_id INT4,
  master_name VARCHAR(64),
  CONSTRAINT master_pkey PRIMARY KEY (master_id)
}
create table slave 
{
  slave_id INT4,
  slave_name VARCHAR(64),
  master_id INT4,
  CONSTRAINT slave_pkey PRIMARY KEY (slave_id),
  CONSTRAINT slave_fkey_master_id FOREIGN KEY (master_id)
REFERENCES master (master_id) ON UPDATE CASCADE ON DELETE RESTRICT
}

Do I need to create index 

CREATE INDEX my_index
  ON slave
  USING btree
  (master_id);

?

Thanks
-- Verba volent, scripta manentMy ISP  - http://www.netbynet.ru 

Re: [GENERAL] Foreign keys and slow insert

[Dan Black]

I've observed that inserts into slave table became slower when I use foreign key than without one.
Can it be related to foreign key? 
And I am interested how much  performance of database with foreign
keys can be different from performance of database without foreign
keys? In other words, how much performance decrease on using foreign
keys?

Thanks :-)-- Verba volent, scripta manentMy ISP  - http://www.netbynet.ru 

Re: [GENERAL] Foreign keys and slow insert

[Dan Black]

I think 21 interns will be enough :)2005/6/8, Scott Marlowe <[EMAIL PROTECTED]>:
On Wed, 2005-06-08 at 12:39, Dan Black wrote:> I've observed that inserts into slave table became slower when I use> foreign key than without one.> Can it be related to foreign key?> And I am interested how much  performance of database with foreign
> keys can be different from performance of database without foreign> keys? In other words, how much performance decrease on using foreign> keys?The problem you're seeing is usually caused by adding records to a table
set that starts out empty, and the planner uses seq scans, and as itgrows, should switch to random seeks, but doesn't know to, because noone has bothered to analyze said tables.Set up the pg_autovacuum daemon or cron vacuumdb -az to run every so
often to help that.On the other hand, foreign keys are never zero cost, so even the mostefficient implementation is gonna be slower than not using them.  Datacoherency costs, either up front (i.e. in the database doing it) or in
the back (i.e. hiring 20 summer interns to go through your data and findthe parts that are bad...)  :)-- Verba volent, scripta manentMy ISP  - 
http://www.netbynet.ru 

Re: [GENERAL] Reliably finding temporary table

[Dan Black]

Help! I have a similar problem. Does anybody know how to solve a problem2005/6/8, Ian Burrell <[EMAIL PROTECTED]>:
We have some functions which need to dynamically create a temporarytable if it does not already exist.  We use the following function:CREATE OR REPLACE FUNCTION test_date_time_exists() RETURNS BOOLEAN AS '
BEGINRETURN EXISTS (SELECT * FROM pg_classWHERE relname = ''test_date_time''AND pg_table_is_visible(oid));END;' LANGUAGE 'plpgsql' STABLE
However, this has been failing occassionally with errors likeERROR: cache lookup failed for relation 3454264547From looking in the list archives, I found a description ofpg_table_is_visible failing because it has different snapshot
semantics than the SELECT.  Is there a solution for this problem?  Isthere another function I can use?  What is a better way to detect temptables visible to the  session? - Ian---(end of broadcast)---
TIP 6: Have you searched our list archives?   http://archives.postgresql.org-- Verba volent, scripta manentMy ISP  - 
http://www.netbynet.ru 

[GENERAL] Index does not working.

[Dan Black]

I created a table

CREATE TABLE my_table
(
  id int4 NOT NULL,
  name varchar(64) NOT NULL,
  deleted bool NOT NULL,
  active bool NOT NULL,
  CONSTRAINT my_table_pkey PRIMARY KEY (id)
) 

and  index

CREATE INDEX my_table_index
  ON my_table
  USING btree
  (deleted, active);


there is two identical queries...

1) select * from my_table where active and deleted

and 
2) select * from my_table where active = true and deleted = true

First query does not use index in contrast to two query.

Why?

Thanks.-- Verba volent, scripta manentMy ISP  - http://www.netbynet.ru 

Re: [GENERAL] Index does not working.

[Dan Black]

1)
select * from my_table where active and deleted
--
Seq Scan on my_table  (cost=1.00..10180.00 rows=2500 width=56)
  Filter: (active AND deleted)

2)
select * from my_table where active = true and deleted = true
-
Index Scan using my_table_index on my_table  (cost=0.00..369.00 rows=2500 width=56)
  Index Cond: ((deleted = true) AND (active = true))
 
2005/6/13, Douglas McNaught <[EMAIL PROTECTED]>:
Dan Black <[EMAIL PROTECTED]> writes:> First query does not use index in contrast to two query.>> Why?You don't give enough information.  Post EXPLAIN ANALYZE output for
both queries and perhaps someone can help.-Doug-- Verba volent, scripta manentMy ISP  - http://www.netbynet.ru 

[GENERAL] how can i view deleted records?

[Dan Black]

Hello,  everybody!
How  can  I  view  deleted  records  in  table?

--
Verba volent, scripta manent
Dan Black

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Ответ: how can i view deleted records?

[Dan Black]

Thanks.  I  thought  that  there  are some  standard  utilities  or 
sql  request  in  postgres  to  view  deleted  or  modified  tuples.


2006/4/30, Steve Atkins <[EMAIL PROTECTED]>:


On Apr 29, 2006, at 4:18 PM, Robby Russell wrote:

>
> On Apr 29, 2006, at 12:49 PM, Dan Black wrote:
>
>> Hello,  everybody!
>> How  can  I  view  deleted  records  in  table?
>
> SELECT * FROM recycle_bin;
>
> ;-)
>
> Just kidding... once you delete your records... they are gone.

That's not true.

Deleted (or modified) records don't go away until the space
they use is recycled by the VACUUM command.

However, there's no support in postgresql for any sort of
"time travel", including viewing deleted tuples. The data
is there on the disk, but there is no clean way to view it
via the database.

It's certainly not something a DBA should even think about
(outside of security issues) but deleted tuples are available
in a forensics situation, as long as vacuum hasn't been run.

Cheers,
   Steve


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

       http://www.postgresql.org/docs/faq




--
Verba volent, scripta manent
Dan Black

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] Charset conversion error

[Dan Black]

Hi, I have an error after updating my database up to 8.1.4 version."SQL Error: ERROR:  character 0xb9 of encoding "WIN1251" has no equivalent in "MULE_INTERNAL"'. "My client program encoding is windows-1251 and database encoding is koi8.
What can I do to rectify the situation?Verba volent, scripta manentDan Black 

Re: [GENERAL] Charset conversion error

[Dan Black]

I think you are right. But everything was alright before I updated my database.So there are a lot of "incorrect" values in tables. And errors appear when I execute "SELECT * FROM table". 
2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:
> Hi, I have an error after updating my database up to 8.1.4 version.> "SQL Error: ERROR:  character 0xb9 of encoding "WIN1251" has no equivalent> in "MULE_INTERNAL"'. "> My client program encoding is windows-1251 and database encoding is koi8.
> What can I do to rectify the situation?It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. Youshould check your Windows-1251 data.--Tatsuo IshiiSRA OSS, Inc. Japan
-- Verba volent, scripta manentDan Black 

Re: [GENERAL] Charset conversion error

[Dan Black]

Previous version converted such characters all right. And there wasn't any bogus ASCII spaces. But I looked at the KOI8 charset table and found out that there is not equivalent symbol (0xb9) in this table.
2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:
> I think you are right. But everything was alright before I updated my> database.Previous version converted such characters to ASCII spaces. Soprobably you have lots of bogus spaces anyway. If you think it's ok,
then you could your own CONVERSION which behaves similar to previousversion.--Tatsuo IshiiSRA OSS, Inc. Japan> So there are a lot of "incorrect" values in tables. And errors appear when I
> execute "SELECT * FROM table".>> 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:> >> > > Hi, I have an error after updating my database up to 
8.1.4 version.> > > "SQL Error: ERROR:  character 0xb9 of encoding "WIN1251" has no> > equivalent> > > in "MULE_INTERNAL"'. "> > > My client program encoding is windows-1251 and database encoding is
> > koi8.> > > What can I do to rectify the situation?> >> > It suggests that Windows-1251's 0xb9 cannot be converted to KOI8. You> > should check your Windows-1251 data.
> > --> > Tatsuo Ishii> > SRA OSS, Inc. Japan> >>>>> --> Verba volent, scripta manent> Dan Black
-- Verba volent, scripta manentDan Black 

Re: [GENERAL] Charset conversion error

[Dan Black]

You are right. I was in a harry when wrote my last message.I solved my problem by changing some source files. But It is not very good because I have to do it every time when I need to update my database. And I can't check all fields in all tables because it take a few days. 
2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:
It seems not.$ cat koi8.shprintf "SELECT '==\xb9=='"|PGCLIENTENCODING=WIN1251 psql -p 5435 koi8$ psql -p 5435 -c 'select version()' koi8 version
- PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2 20031218 (Vine Linux 3.3.2-0vl8)
(1 row)$ sh koi8.sh ?column?-- == ==(1 row)As you can see PostgreSQL 8.1.3 converts 0xb9 in Windows-1251 to aspace of KOI8.--Tatsuo IshiiSRA OSS, Inc. Japan> Yes, character converted to a non-space-char.
> And there is reverse error. It's happened when I try to extract information> from database.> SQL Error: ERROR:  character 0x8bbf of encoding "MULE_INTERNAL" has no> equivalent in "WIN1251""
>> 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:> >> > As far as I know the conversion table was not changed. Are you saying> > that Win-1251 0xb9 is converted to a non-space-char in the previous
> > version?> > --> > Tatsuo Ishii> > SRA OSS, Inc. Japan> >> > > Previous version converted such characters all right. And there wasn't> > any> > > bogus ASCII spaces. But I looked at the KOI8 charset table and found out
> > > that there is not equivalent symbol (0xb9) in this table.> > >> > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]>:> > > >
> > > > > I think you are right. But everything was alright before I updated> > my> > > > > database.> > > >> > > > Previous version converted such characters to ASCII spaces. So
> > > > probably you have lots of bogus spaces anyway. If you think it's ok,> > > > then you could your own CONVERSION which behaves similar to previous> > > > version.> > > > --
> > > > Tatsuo Ishii> > > > SRA OSS, Inc. Japan> > > >> > > > > So there are a lot of "incorrect" values in tables. And errors> > appear
> > > > when I> > > > > execute "SELECT * FROM table".> > > > >> > > > > 2006/5/30, Tatsuo Ishii <[EMAIL PROTECTED]
>:> > > > > >> > > > > > > Hi, I have an error after updating my database up to 8.1.4version.> > > > > > > "SQL Error: ERROR:  character 0xb9 of encoding "WIN1251" has no
> > > > > > equivalent> > > > > > > in "MULE_INTERNAL"'. "> > > > > > > My client program encoding is windows-1251 and database encoding
> > is> > > > > > koi8.> > > > > > > What can I do to rectify the situation?> > > > > >> > > > > > It suggests that Windows-1251's 0xb9 cannot be converted to KOI8.
> > You> > > > > > should check your Windows-1251 data.> > > > > > --> > > > > > Tatsuo Ishii> > > > > > SRA OSS, Inc. Japan
> > > > > >> > > > >> > > > >> > > > >> > > > > --> > > > > Verba volent, scripta manent> > > > > Dan Black
> > > >> > >> > >> > >> > > --> > > Verba volent, scripta manent> > > Dan Black> >>>>> --
> Verba volent, scripta manent> Dan Black-- Verba volent, scripta manentDan Black 

[GENERAL] strange stable function behavior

[Dan Black]

HiI have a stable function test.test_stableCREATE OR REPLACE FUNCTION test.test_stable(int4)  RETURNS int4 AS$BODY$DECLARE  _param ALIAS FOR $1;BEGIN  RAISE NOTICE 'ID: %, TIME: %', _param, timeofday()::timestamp;
  RETURN _param;END$BODY$  LANGUAGE 'plpgsql' STABLE STRICT SECURITY DEFINER;Everything is all right when I execute a simple querySELECT id, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl
NOTICE:  ID: 10, TIME: 2006-06-01 14:57:07.89594NOTICE:  ID: 11, TIME: 2006-06-01 14:57:07.896203NOTICE:  ID: 12, TIME: 2006-06-01 14:57:07.896322NOTICE:  ID: 13, TIME: 2006-06-01 14:57:07.896417NOTICE:  ID: 14, TIME: 2006-06-01 14:57:
07.896494NOTICE:  ID: 15, TIME: 2006-06-01 14:57:07.896623But if I want to display field sid twiceSELECT id, sid, sid FROM (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id) tbl
I can see that function test.test_stable executes twice with  identical parametersNOTICE:  ID: 10, TIME: 2006-06-01 14:58:52.950292
NOTICE:  ID: 10, TIME: 2006-06-01 14:58:52.950485
NOTICE:  ID: 11, TIME: 2006-06-01 14:58:52.950582
NOTICE:  ID: 11, TIME: 2006-06-01 14:58:52.950679
NOTICE:  ID: 12, TIME: 2006-06-01 14:58:52.950765
NOTICE:  ID: 12, TIME: 2006-06-01 14:58:52.950835
NOTICE:  ID: 13, TIME: 2006-06-01 14:58:52.9511
NOTICE:  ID: 13, TIME: 2006-06-01 14:58:52.975477
NOTICE:  ID: 14, TIME: 2006-06-01 14:58:52.992098
NOTICE:  ID: 14, TIME: 2006-06-01 14:58:53.008741
NOTICE:  ID: 15, TIME: 2006-06-01 14:58:53.025425
NOTICE:  ID: 15, TIME: 2006-06-01 14:58:53.058589
Is it bug or special feature?PostgresPostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1 20050727 (Red Hat 4.0.1-5)-- Verba volent, scripta manentDan Black 

Re: [GENERAL] strange stable function behavior

[Dan Black]

Thank you very much. It works!!!Where can I read about such features? 2006/6/1, Martijn van Oosterhout <kleptog@svana.org>:
On Thu, Jun 01, 2006 at 03:09:47PM +0400, Dan Black wrote:> Hi> I have a stable function test.test_stable> But if I want to display field sid twice>> SELECT id, sid, sid FROM
> (SELECT *, test.test_stable(id) AS sid FROM generate_series(10, 100) AS id)> tbl>> I can see that function test.test_stable executes twice with identical> parametersPostgres makes no special effort to avoid multiple calls of the same
function. Especially since you declared it STABLE.> Is it bug or special feature?Not a bug nor a special feature, just the way it is. If you put OFFSET 0in the subquery, that will stop the expansion of the subquery, thus the
function will only be called once.Have a nice day,--Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.1 (GNU/Linux)iD8DBQFEftscIB7bNG8LQkwRAjITAJ9csUN2V8oHtfRk280cJYTqkpopIwCfRVQ0
cToHKTMqSf4HD21f+bo3jn0==/z/0-END PGP SIGNATURE--- Verba volent, scripta manentDan Black