Re: [SQL] why vacuum

[Mario Splivalo]

On Thu, 2005-10-27 at 11:51 -0400, Andrew Sullivan wrote:

> Well, AFAIK Oracle itself offers no replication, either.  If you want
> it, you have to buy a license for it.  Which means it's an add-on. 
> Heck, most Linux distributions' kernels don't offer support for
> network cards: they're an add-on.  We call them modules.  (In case
> it's not clear, I don't buy the "itself/add-on" distinction.  The
> point is that the whole system works together.  PostgreSQL most
> definitely offers replication.  In fact, you can get warm-standby with
> WAL shipping, or read-only capabilities with Slony or some other
> tools.)

I wish I was Dumbo now. I could hide myself with me ears :) 

> > I'll be glad to, I'm just not that familiar (in fact, i'm not familiar
> > at all) with the 'proof-of-cocept' or 'General Bits' terms, so if you
> > could http-redirect me, I'll be  most thankfull.
> 
> Proof of concept is just a description of what you did, how it
> worked, design and limitations, &c.  Post it to the lists (uh,
> -general or maybe -hackers, I suppose), or put it on a web page or
> whatever.  General Bits is a fairly regular column that Elein Mustain
> puts out.  I bet she'd include a submission on this topic, although
> you'd have to ask her.  You can find GB at
> .

I'll go trough my code, it's been a while since I touched it, I'll write
some documentation and I'll inform the comunity. Thnx for the pointouts.

Mike
-- 
Mario Splivalo
Mob-Art
[EMAIL PROTECTED]

"I can do it quick, I can do it cheap, I can do it well. Pick any two."



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] why vacuum

[Andrew Sullivan]

On Fri, Oct 28, 2005 at 01:09:43PM +0200, Mario Splivalo wrote:
> I'll go trough my code, it's been a while since I touched it, I'll write
> some documentation and I'll inform the comunity. Thnx for the pointouts.

And thank _you_ for proposing to do this.  If everyone contributes
their discoveries and improvements, we all benefit.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
When my information changes, I alter my conclusions.  What do you do sir?
--attr. John Maynard Keynes

---(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

Fwd: Re: [SQL] Referencing

[lucas]

Ok,
But the problem is becouse the "buy" and "send" tables referencing with other
father table, wich is different.
I shoud not create a spent table to put the "buy" and "send" values
becouse the
entire database is more complex than it. look:

create table output(
id serial primary key,
client integer references clientes,
fiscal_number varchar(30),
print_date date,
...
);
  create table SEND(
   id serial primary key,
   output integer references input,
   product_id integer,--references
   value money
  );
create table input(
id serial primary key,
supplier integer references suppliers,
employee varchar(30),
...
);
  create table BUY(
   id serial primary key,
   input integer references input,
   product_id integer,--references
   value money
  );

---and---

create table financial(
  id serial primary key,
  cred_deb smallint,
  value money,
  references integer references ???, --<<-HERE IS THE PROBLEM, it will
reference
to buy OR send table
);

How looked, the "buy" and the "send" table is identical except the father
references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich
has these informations.
And now my question: Is there a way to references (financial) with two
diferents
tables in the some row? Or need I create two diferents rows???

Thanks. (sorry for my english).

Quoting William Leite Araújo <[EMAIL PROTECTED]>:

>  Maybe you need is a table "spent" that has all fields of
> buy/send and one more, a flag to say is the field is a "buy" or a
> "send".
>
>
> 2005/10/27, [EMAIL PROTECTED] <[EMAIL PROTECTED]>:
>> Hi.
>> Is there a way to references dynamic tables? I.E:
>> I have a table called "buy" that create some records in "financial"
>> table, but
>> there is other table called "send" that create other records in "financial".
>> "Financial" table have the moneys' movements and needs to be
>> referenciable by
>> "buy or send". IE:
>>  create table buy (
>>   id serial primary key,
>>   product_id integer,--references
>>   value money
>>  );
>>  create table send (
>>   id serial primary key,
>>   product_id integer, --references...
>>   value money
>>  );
>>  create table financial(
>>   id serial primary key,
>>   cred_deb smallint,
>>   value money,
>>   references integer, --<<-HERE IS THE PROBLEM, it will reference to
>> buy OR send
>> table
>>  );
>> Well, I dont know if I was clean.
>> Thank you.
>>
>> ---(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
>>
>
>
> --
> William Leite Araújo
>


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: Fwd: Re: [SQL] Referencing

[Daryl Richter]

[EMAIL PROTECTED] wrote:

Ok,
But the problem is becouse the "buy" and "send" tables referencing with other
father table, wich is different.
I shoud not create a spent table to put the "buy" and "send" values
becouse the
entire database is more complex than it. look:

create table output(
id serial primary key,
client integer references clientes,
fiscal_number varchar(30),
print_date date,
...
);
  create table SEND(
   id serial primary key,
   output integer references input,
   product_id integer,--references
   value money
  );
create table input(
id serial primary key,
supplier integer references suppliers,
employee varchar(30),
...
);
  create table BUY(
   id serial primary key,
   input integer references input,
   product_id integer,--references
   value money
  );

---and---

create table financial(
  id serial primary key,
  cred_deb smallint,
  value money,
  references integer references ???, --<<-HERE IS THE PROBLEM, it will
reference
to buy OR send table
);

How looked, the "buy" and the "send" table is identical except the father
references (INPUT or OUTPUT)... Then I shoud not create ONE table (spent) wich
has these informations.
And now my question: Is there a way to references (financial) with two
diferents
tables in the some row? Or need I create two diferents rows???

Thanks. (sorry for my english).



It's hard to say without knowing more precisely what you are trying to 
model, but I think this push you in the right direction:


  -- This table takes the place of both SEND and BUY
  create table activity(
 id  serial primary key,
 product_id  integer, --references
 value   money
  );

  create table financial(
id  serial primary key,
cred_debsmallint,
value   money,
activity_id integer references activity
  );

  create table output(
idserial primary key,
clientinteger, --references clientes,
fiscal_number varchar(30),
print_datedate,
activity_id   integer  references activity
  );

  create table input(
id   serial primary key,
supplier integer,   -- references suppliers,
employee varchar(30),
activity_id  integerreferences activity
  );

And then you do the following:

  create view buy
  as
  select
  a.id,
  b.id  as "input_id",
  a.product_id,
  a.value
  from
activity a
  join  inputb on b.activity_id = a.id;


The SELL view is left as an exercise for the reader.

--
Daryl Richter
Platform Author & Director of Technology

(( Brandywine Asset Management  )
 ( "Expanding the Science of Global Investing"  )
 (  http://www.brandywine.com   ))


---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] combining records from a single table and presenting them as one record

[Abhishek]

Using LIMIT 1 does return me a single record but please note that the table can have multiple guids for which I need a unique record with all the digit types combined. Using LIMIT 1 returns me always one record. :-(

On 10/27/05, boinger <[EMAIL PROTECTED]> wrote:
On 10/27/05, Abhishek <[EMAIL PROTECTED]> wrote:> I am tryng to write a query which returns me a record like this
> I do the query as this:>> select callguid , ( select digits from TABEL1 where type='a' ), ( select> digits from TABEL1 where type='b' ), ( select digits from TABEL1 where> type='c' ) from TABLE1;
> Note that I get the records which i wanted, but I get them duplicated. If I> use 'distinct' keyword I get the desired result with no duplication. But> 'distinct' seems to be too expensive.
>> Is there any better way I can do this without compromising the performance ?Just add 'LIMIT 1' to the end of the query.-- Abhishek Jain

Re: Fwd: Re: [SQL] Referencing

[lucas]

Quoting Daryl Richter <[EMAIL PROTECTED]>:
It's hard to say without knowing more precisely what you are trying 
to model, but I think this push you in the right direction:


  -- This table takes the place of both SEND and BUY
  create table activity(
 id  serial primary key,
 product_id  integer, --references
 value   money
  );

  create table financial(
id  serial primary key,
cred_debsmallint,
value   money,
activity_id integer references activity
  );

  create table output(
idserial primary key,
clientinteger, --references clientes,
fiscal_number varchar(30),
print_datedate,
activity_id   integer  references activity
  );

  create table input(
id   serial primary key,
supplier integer,   -- references suppliers,
employee varchar(30),
activity_id  integerreferences activity
  );

And then you do the following:

  create view buy
  as
  select
  a.id,
  b.id  as "input_id",
  a.product_id,
  a.value
  from
activity a
  join  inputb on b.activity_id = a.id;



Okay, but references between (output/input) and ACTIVITY tables is 1 to N.
OUTPUT/INPUT - 1
to
ACTIVITY - N.
And not N to 1 how the example.
Then the reference field need to be on "ACTIVITY (send/buy)" table.


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

Re: [SQL] Complex Query - Data from 3 tables simultaneously

[Muralidharan Ramakrishnan]

SELECT A.SID , A.RECDATE , B.MID , B.MBDATE , C.ISSDATE FROM TableA A LEFT OUTER JOIN TableB B ON   A.SID = B.SIDLEFT OUTER JOIN TableC C ON B.MID = C.MIDORDER BY A.SID[EMAIL PROTECTED] wrote:


All,


 
Using Postgres 8.0 on Windows Server 2003 - 16GB Ram, 3Ghz X 2 Xeons
Accessing through JDBC / JSP
 
I have 3 shipment tables.
Table A - Records arrived Shipments.
Table B - Records Materials (maybe more than one per shipment) in the shipment.
Table C - Records Issuances of material (maybe more than one Issuance per line item of material) in Table B.
 
eg. 
Table A (PK = Shipment ID)
---
shipment ID    Recd Date
12005-XX-XX
10  2005-XX-XX
 
 
Table B (PK = Material ID, FK = Shipment ID, references Table A (shipment ID))
-
shipment ID Material ID Material Bond Date
10    1  2005-XX-XX
10    2  2005-XX-XX
 
Table C (PK = Issue ID, FK = Material ID, references Table B (Material ID))

Material ID    Issue ID    Issue Date

1  1  2005-05-XX 
1  2  2005-05-XX
 
I want to get data (under criteria of recvd date in table A) the following records:
 
shipment ID    Recd Date    MaterialID    Bond Date    Issue ID    Issue Date
 
1    2005-XX-XX   -    -    -    -
10  2005-XX-XX   1 2005-XX-XX   1    2005-05-XX

10  2005-XX-XX   1 2005-XX-XX   2    2005-05-XX 
10  2005-XX-XX   2 2005-XX-XX   -    -
 
 
Basically I want a raw dump of data
- Should have all the shipments regardless of whether they have any material items entered or not
- Should have all Material Items for Every Shipment regardless of whether it was issued or not.
 
I know I need an outer join (Do I Not?), but am confused as to how to implement it.
Because this seems to be a requirement of a reversed outer join (??)
 
Please assist, 
Thanks in advance.
 
 
		 
Enjoy this Diwali with Y! India Click here

Re: [SQL] combining records from a single table and presenting them as one record

[Michael Fuhr]

On Fri, Oct 28, 2005 at 11:47:12AM -0400, Abhishek wrote:
> Using LIMIT 1 does return me a single record but please note that the table
> can have multiple guids for which I need a unique record with all the digit
> types combined. Using LIMIT 1 returns me always one record. :-(

Have you looked at contrib/tablefunc?

-- 
Michael Fuhr

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

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

[SQL] information_schema problem

[Kyle Bateman]

I'm trying to use information_schema.view_column_usage to determine the
native table from which various view columns descend.  This is so my
interface can automatically generate the correct foreign key links from
one view to another.

But in the case where a view references two tables linked by a foreign  
key, the key fields are reported as belonging to both tables.   


The enclosed example shows two tables related by a foreign key relationship.
The view "event_remind_v" really only produces fields from 
event_remind.  But

in order to satisfy the "exists" clause, it also references fields from the
event table.

view_column_usage reports the fields "own_id" and "seq" as belonging to
both table "event_remind" and "event".  My code needs a way to know that
"event_remind" is the table they "really" come from.

1. Is this the correct behavior for information_schema.view_column_usage?
  Should it report a table/column as belonging to more than one table?
  If not, how can I fix it?
  
  The enclosed script includes a (slightly revised) version of   
  view_column_usage that is easier to hack on than the one inside

  information_schema.
  
2. If information_schema.view_column_usage is working right, is there a
  way I can modify my local view_column_usage to distinguish between   
  tables/columns that actually "belong" to the view and related columns

  from a foreign key relationship?

Example code:
-
drop view event_remind_v;
drop table event_remind;
drop table event;
drop view view_column_usage;

-- Contains an entry for each scheduled calendar event
create table event (
   own_id  int4,
   seq int4,
   status  varchar,
   summary varchar,

   primary key (own_id,seq)
);

-- Contains an entry for each reminder for each event
create table event_remind (
   own_id  int4,
   seq int4,
   advance interval,

   primary key (own_id, seq, advance),
   foreign key (own_id, seq) references event on update cascade on 
delete cascade

);

create view event_remind_v as
   select *
   from event_remind r
   where exists (select * from event where own_id = r.own_id and 
seq = r.seq and status = 'open');

;

create view view_column_usage as
 select
   v.relname   as "view_name",
   t.relname   as "table_name",
   at.attname  as "column_name"

   from pg_depend dv, pg_class v, pg_namespace nv,
pg_depend dt, pg_class t, pg_namespace nt, pg_attribute at
   where dv.objid = dt.objid
 and dv.refobjid <> dt.refobjid
 and dv.deptype = 'i'

 and v.relkind = 'v'
 and t.relkind IN ('r', 'v')

 and v.oid = dv.refobjid
 and t.oid = dt.refobjid

 and t.relnamespace = nt.oid
 and v.relnamespace = nv.oid
 and dv.classid= dt.classidand dv.classid= 
'pg_catalog.pg_rewrite'::regclass
 and dv.refclassid = dt.refclassid and dv.refclassid = 
'pg_catalog.pg_class'::regclass


 and t.oid = at.attrelid and dt.refobjsubid = at.attnum
 and nv.nspname = 'public' and nt.nspname = 'public'
;

select view_name,column_name,table_name from
view_column_usage where view_name = 'event_remind_v';
select view_name,column_name,table_name from 
information_schema.view_column_usage where view_name = 'event_remind_v';



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Complex Query - Data from 3 tables simultaneously

[Amit_Wadhwa]

SELECT 
a.*,b.*,c.*,c.issuedate-a.recd_date as age FROM shipments a LEFT JOIN materials b ON a.shipid = b.shipid;LEFT JOIN issuetable c ON 
b.material_id = c.material_id 
WHERE (a.recd_date between cast(' 
"+date1+" 00:00:00' as datetime) and cast(' "+date2 + ' 
") 23:59:59' as datetime)
Did the above, got the expected results, did not 
specify 'Outer Join' only specified Join, is that a problem?
 



From: Muralidharan Ramakrishnan 
[mailto:[EMAIL PROTECTED] Sent: Friday, October 28, 2005 
10:41 PMTo: Wadhwa, Amit; pgsql-sql@postgresql.orgSubject: 
Re: [SQL] Complex Query - Data from 3 tables simultaneously

SELECT A.SID , A.RECDATE , B.MID , B.MBDATE , C.ISSDATE FROM TableA A LEFT 
OUTER JOIN TableB B ON   A.SID = B.SIDLEFT OUTER JOIN TableC C ON 
B.MID = C.MIDORDER BY A.SID[EMAIL PROTECTED] wrote: 


  
  All,
  
  
   
  Using Postgres 8.0 on Windows Server 2003 - 16GB Ram, 
  3Ghz X 2 Xeons
  Accessing through JDBC / JSP
   
  I have 3 shipment tables.
  Table A - Records arrived 
  Shipments.
  Table B - Records Materials (maybe more than one per 
  shipment) in the shipment.
  Table C - Records Issuances of material (maybe more 
  than one Issuance per line item of material) in Table B.
   
  eg. 
  Table A (PK = Shipment ID)
  ---
  shipment 
  ID    Recd 
  Date
  12005-XX-XX
  10  2005-XX-XX
   
   
  Table B (PK = Material ID, FK = Shipment ID, 
  references Table A (shipment ID))
  -
  shipment 
  ID 
  Material ID Material Bond Date
  10    
  1  2005-XX-XX
  10    
  2  2005-XX-XX
   
  Table C (PK = Issue ID, FK = Material ID, references 
  Table B (Material ID))
  
  Material 
  ID    Issue 
  ID    Issue 
  Date
  
  1  
  1  2005-05-XX 

  1  2  2005-05-XX
   
  I want to get data (under criteria of recvd date in 
  table A) the following records:
   
  shipment 
  ID    Recd 
  Date    
  MaterialID    Bond 
  Date    Issue 
  ID    Issue Date
   
  1    
  2005-XX-XX   -    
  -    
  -    
  -
  10  2005-XX-XX   
  1 2005-XX-XX   1    
  2005-05-XX
  
  10  2005-XX-XX   
  1 2005-XX-XX   2    
  2005-05-XX 
  10  2005-XX-XX   2 2005-XX-XX   -    
  -
   
   
  Basically I want a raw dump of 
  data
  - Should have all the shipments regardless of whether 
  they have any material items entered or not
  - Should have all Material Items for Every Shipment 
  regardless of whether it was issued or not.
   
  I know I need an outer join (Do I Not?), but am 
  confused as to how to implement it.
  Because this seems to be a requirement of a reversed 
  outer join (??)
   
  Please assist, 
  Thanks in advance.
   
   


Enjoy this Diwali with Y! India Click 
here