Re: [GENERAL] object tracking

[Steve Atkins]

On May 19, 2010, at 11:41 AM, Little, Douglas wrote:

> Hi,
>  
> I have a fairly large data warehouse in Greenplum, and am having trouble 
> tracking object changes. 
> I need to
> 1.Know when an object was changed, by who.
> 2.   Have some logging about when an object was dropped – especially drop 
> cascade.
>  
>  
> Currently I’m having a problem with a set of disappearing views.   We’re 
> still in pre-prod environment, but
>  
> Any suggestions?

Take it up with your vendor. That's what you're paying them for, and they're 
the only ones who are
likely to understand how their product works.

If you were using postgresql I'd suggest looking at the log_statement and 
log_line_prefix
options, to see if tracking DDL that way were adequate for what you need.

Cheers,
  Steve


-- 
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] object tracking

[Joshua D. Drake]

On Wed, 2010-05-19 at 13:41 -0500, Little, Douglas wrote:
> Hi,
> 
>  
> 
> I have a fairly large data warehouse in Greenplum, and am having
> trouble tracking object changes.  
> 

Depends on Greenplum I would assume. I don't know how compatible they
are with .Org anymore. You might look at table_log

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] object tracking

[Little, Douglas]

Hi,

I have a fairly large data warehouse in Greenplum, and am having trouble 
tracking object changes.
I need to

1.Know when an object was changed, by who.

2.   Have some logging about when an object was dropped - especially drop 
cascade.


Currently I'm having a problem with a set of disappearing views.   We're still 
in pre-prod environment, but

Any suggestions?

I'd like

1.PG to timestamp the catalog tables

2.   Permit triggers on catalog objects.


Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CAF759.01607890]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] object dependency workaround

[Little, Douglas]

Hello,
We're migrating to Greenplum, and in our currently portfolio we base everything 
on views.
In our prior system, we could alter tables and then refresh the views, but in 
PG/greenplum most alters are blocked because of the dependent objects.

I understand that I can walk the pg_depends table to see the linkages, but what 
I really want to know
Is there a way to change the system behavior so that  ALTERs are allowed, 
perhaps invalidating dependent objects

Short of this, is there a recommended framework/functions for spooling off the 
dependent objects, performing the alter and then recreating all of the 
dependent objects?

Thanks  in advance.
Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
douglas.lit...@orbitz.com

 [cid:image002.jpg@01CA459B.F991A680]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [GENERAL] "object references" and renaming was: Why Does UPDATE Take So Long?

[Ivan Sergio Borgonovo]

On Wed, 1 Oct 2008 22:19:29 +0200
"Filip Rembiałkowski" <[EMAIL PROTECTED]> wrote:

> > Is this kind of stuff going to affect any reference to the farm
> > table? eg. inside functions, triggers etc?

> no, not in functions source.

I've read somewhere that create *or replace* should be used exactly
because internally postgresql reference functions by id.
dropping and recreating a function will change the id.
Or was I daydreaming and I have memories of another DB?

I think that postgresql store plpgsql functions as "text". I don't
know if it caches plans, "compile" the function somehow etc...
So I was wondering if renaming stuff referenced in a function may
have some unexpected effect.

> only FK references will be affected. FK triggers are handled
> internally. I don't know if any other kind of object references

handled internally?

> are handled this way.

> > what if:
> > create table farm_rel (
> >  farm_id [sometype] references farm(farm_id) on delete cascade,
> > ...
> > );
> >
> > and I
> >
> > alter table farm rename to farm_t;
> > alter table farm2 rename to farm;
> > drop table farm_t;

> well, check it :) I did:

Well I wrote the example not to let someone check for me... ;) just
to give an example of a "renaming" situation where I should be
cautious...
What else could be renamed other than tables and columns?
triggers...?
I'd expect renaming columns follow the same pattern of renaming
tables.

> > or similar situations...
> >
> > where could I incur in troubles using RENAME (for tables, columns
> > etc...)?

> OTOH, your rename trick will work for such functions :)

I think the problem arise from dependencies following the name and
dependencies following the object (table).
It seems that in these cases what is chosen is due to the difficulty
to do otherwise.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
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] "object references" and renaming was: Why Does UPDATE Take So Long?

[Filip Rembiałkowski]

2008/10/1 Ivan Sergio Borgonovo <[EMAIL PROTECTED]>:
> On Wed, 01 Oct 2008 08:32:16 -0600
> Bill Thoen <[EMAIL PROTECTED]> wrote:
>
>> CREATE TABLE farm2 (LIKE farms);
>> INSERT INTO farm2 (farm_id, fips_cd, farm_nbr, prog_year) SELECT
>> farm_id, fips_cd, farm_nbr, '2007' FROM farms;
>> DROP TABLE farms;

this will fail if there are FK references to farms table.

>> ALTER TABLE farm2 RENAME TO farms;
>> CREATE UNIQUE INDEX farms_id_key ON farms(farm_id);
>> CREATE UNIQUE INDEX farms_fips_nbr_key ON farms(fips_cd,farm_nbr);
>
> Is this kind of stuff going to affect any reference to the farm
> table? eg. inside functions, triggers etc?

no, not in functions source.
only FK references will be affected. FK triggers are handled internally.
I don't know if any other kind of object references are handled this way.

> what if:
> create table farm_rel (
>  farm_id [sometype] references farm(farm_id) on delete cascade,
> ...
> );
>
> and I
>
> alter table farm rename to farm_t;
> alter table farm2 rename to farm;
> drop table farm_t;

well, check it :) I did:

[EMAIL PROTECTED] \d farm_rel
Table "public.farm_rel"
 Column  |  Type   | Modifiers
-+-+---
 farm_id | integer |
Foreign-key constraints:
"farm_rel_farm_id_fkey" FOREIGN KEY (farm_id) REFERENCES
farm_t(farm_id) ON DELETE CASCADE

[EMAIL PROTECTED] drop table farm_t;
NOTICE:  constraint farm_rel_farm_id_fkey on table farm_rel depends on
table farm_t
ERROR:  cannot drop table farm_t because other objects depend on it
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

>
> or similar situations...
>
> where could I incur in troubles using RENAME (for tables, columns
> etc...)?


if you reference renamed objects from, say, pl/pgsql function source,
it can effect in broken code.

[EMAIL PROTECTED] create function get_farm_id() returns int as $$SELECT
farm_id from farm limit 1$$ language sql;
CREATE FUNCTION
[EMAIL PROTECTED] alter table farm rename to farm_t;
ALTER TABLE
[EMAIL PROTECTED] select get_farm_id();
ERROR:  relation "farm" does not exist
CONTEXT:  SQL function "get_farm_id" during startup


OTOH, your rename trick will work for such functions :)



-- 
Filip Rembiałkowski

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] "object references" and renaming was: Why Does UPDATE Take So Long?

[Ivan Sergio Borgonovo]

On Wed, 01 Oct 2008 08:32:16 -0600
Bill Thoen <[EMAIL PROTECTED]> wrote:

> CREATE TABLE farm2 (LIKE farms);
> INSERT INTO farm2 (farm_id, fips_cd, farm_nbr, prog_year) SELECT 
> farm_id, fips_cd, farm_nbr, '2007' FROM farms;
> DROP TABLE farms;
> ALTER TABLE farm2 RENAME TO farms;
> CREATE UNIQUE INDEX farms_id_key ON farms(farm_id);
> CREATE UNIQUE INDEX farms_fips_nbr_key ON farms(fips_cd,farm_nbr);

Is this kind of stuff going to affect any reference to the farm
table? eg. inside functions, triggers etc?
what if:
create table farm_rel (
  farm_id [sometype] references farm(farm_id) on delete cascade,
...
);

and I

alter table farm rename to farm_t;
alter table farm2 rename to farm;
drop table farm_t;

or similar situations...

where could I incur in troubles using RENAME (for tables, columns
etc...)?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Object ownership in a new database

[Russell Smith]

Hello all,

When you create a new database, not all objects in that database are 
owned by the database owner.  Now some of those may need to be owned by 
a superuser, eg C functions.  However should other things such as the 
public schema or other general objects be owned by the database owner, 
or the user who created them in the template database?


To create a db with the public schema owned by postgres, just:

$ createdb -h 172.17.72.1 -U postgres -O non_superuser owner_test;

$ psql -h 172.17.72.1 -U postgres owner_test;
owner_test=# \dn
List of schemas
Name|  Owner
+--
 information_schema | postgres
 pg_catalog | postgres
 pg_toast   | postgres
 public | postgres
(4 rows)

owner_test=# \q


Now everything is owned by postgres.

Is this the correct and desired behaviour, or is the behaviour expected 
to be different.  I expected it to be owned by "non_superuser".


Any comments welcome.

Russell Smith

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

  http://archives.postgresql.org

Re: [GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk

[Katsuhiko Okano]

OK.understand.
I'll exclude  relkind IN( 's' , 'c' )  file in backup set.
THANKS Qingqing Zhou & tom lane!

Tom Lane wrote:
> "Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> 
>>Pg_xactlock is always there as a special relation.
> 
> 
> pg_xactlock isn't really a relation.  The way I think about it is that
> it's a dummy entry in pg_class that exists to reserve a relation OID
> for a specific purpose --- namely, we can lock transaction IDs by
> locking what would otherwise be a page of that relation.
> 
> There was some talk recently about reorganizing the locktag design
> so that transaction lock tags would be clearly distinguishable from
> any lock associated with a relation.  If we got that done, there'd
> be no need for the pg_xactlock entry at all.
> 
> 
>>I am not sure about 'c'.
> 
> 
> 'c' entries in pg_class are for composite types.  They don't have
> any associated disk storage either.
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 

Katsuhiko Okano
[EMAIL PROTECTED]
NTT Software Corp. (division "NBRO-PT6")


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

Re: [GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?

[Tom Lane]

"Qingqing Zhou" <[EMAIL PROTECTED]> writes:
> Pg_xactlock is always there as a special relation.

pg_xactlock isn't really a relation.  The way I think about it is that
it's a dummy entry in pg_class that exists to reserve a relation OID
for a specific purpose --- namely, we can lock transaction IDs by
locking what would otherwise be a page of that relation.

There was some talk recently about reorganizing the locktag design
so that transaction lock tags would be clearly distinguishable from
any lock associated with a relation.  If we got that done, there'd
be no need for the pg_xactlock entry at all.

> I am not sure about 'c'.

'c' entries in pg_class are for composite types.  They don't have
any associated disk storage either.

regards, tom lane

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

Re: [GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?

[Qingqing Zhou]

Pg_xactlock is always there as a special relation. It has no footprint on
disk. Transactions will keep a record in pg_xactlock at the beginning and
remove the record at the end. Once any conflicting update happens, the
latter transaction will use this relation to wait for the former
transaction's result. AFAIK, since lock tag always needs a relationalId as a
parameter, so we create this special relation for this usage.

I am not sure about 'c'.

Regards,
Qingqing


"Katsuhiko Okano" <[EMAIL PROTECTED]> writes
> Hi.
> I'm writing backup guide and script.
>
> I will look up on-disk-filename should be backuped
> in a pg_class, like:
> >SELECT oid,relfilenode,relname,relkind FROM pg_class;
> it return like:
> >   17173 |   17173 | sql_packages| r
> >   17182 |   17182 | pg_toast_17178_index| i
> >   17180 |   17180 | pg_toast_17178  | t
> > 376 |   0 | pg_xactlock | s
>
> I'll include backup set relkind='r' or 'i' or 'S' or 'v' or 't'.
> but PostgreSQL 8.0.1 Documentation "41.9. pg_class" say
> >c = composite type, s = special
>
> 1)relkind = 's' is always have not on-disk file?
>
> 2)when exist relkind='s' object except pg_xactlock?
>   user(administrator) can create it?
>
> 3)relkind = 'c' is always have not on-disk file?
>   (I create TYPE. but no file exist.)
>   I will only include pg_type relation in backup set. is it OK ?
>
> 4)any other advice to backup? :-)
>
>
> Regards,
> -- 
> 
> Katsuhiko Okano
> k_okano _at_ po.ntts.co.jp
> NTT Software Corp. (division "NBRO-PT6")
>
>
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>



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

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

[GENERAL] Object like pg_class.relkind = 's' or 'c' have on-disk file?

[Katsuhiko Okano]

Hi.
I'm writing backup guide and script.

I will look up on-disk-filename should be backuped
in a pg_class, like:
>SELECT oid,relfilenode,relname,relkind FROM pg_class;
it return like:
>   17173 |   17173 | sql_packages| r
>   17182 |   17182 | pg_toast_17178_index| i
>   17180 |   17180 | pg_toast_17178  | t
> 376 |   0 | pg_xactlock | s

I'll include backup set relkind='r' or 'i' or 'S' or 'v' or 't'.
but PostgreSQL 8.0.1 Documentation "41.9. pg_class" say
>c = composite type, s = special

1)relkind = 's' is always have not on-disk file?

2)when exist relkind='s' object except pg_xactlock?
  user(administrator) can create it?

3)relkind = 'c' is always have not on-disk file?
  (I create TYPE. but no file exist.)
  I will only include pg_type relation in backup set. is it OK ?

4)any other advice to backup? :-)


Regards,
-- 

Katsuhiko Okano
k_okano _at_ po.ntts.co.jp
NTT Software Corp. (division "NBRO-PT6")


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

Re: [GENERAL] Object Relational, Foreign Keys and Triggers

[Tino Wildenhain]

Am Dienstag, den 25.01.2005, 08:36 -0500 schrieb Alex Turner:
> Actualy max() works just fine.  It's not the solution I use in the
> middle tier, but it works for a functional example.  both max() and
> currval() are bad because they can cause a race condition where the
> sequence has been incremented by another thread.  It's always better
> to get nextval('sequence') and store it in a local var, then use it in
> the main insert and corresponding sub-inserts.

No, thats wrong. If you read the documentation again on that matter, you
will see. currval() works on at least one nextval() in the connection
you are running and therefore keeps showing the last result of nextval()
in this very connection - no matter what other connections/sessions do.
Thats the whole point of sequences anyway.

HTH
Tino


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Object Relational, Foreign Keys and Triggers

[Martijn van Oosterhout]

On Tue, Jan 25, 2005 at 08:36:53AM -0500, Alex Turner wrote:
> Actualy max() works just fine.  It's not the solution I use in the
> middle tier, but it works for a functional example.  both max() and
> currval() are bad because they can cause a race condition where the
> sequence has been incremented by another thread.  It's always better
> to get nextval('sequence') and store it in a local var, then use it in
> the main insert and corresponding sub-inserts.

Like I said, read the docs. currval was explicitly created to avoid the
race condition. It gives you the last number handed out in *this*
connection. It's also a lot faster than max. So different connections
get a different currval() and you get an error if you've not called
nextval() in the current connection (it works across transactions).

Storing in a var works too, but currval is totally safe.

Have a nice day,

> On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
>  wrote:
> > On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> > > I am facing the classic pgsql ORDBMS problem:
> > 
> > 
> > 
> > Why are you using MAX()? That won't work at all. Perhaps you need to
> > look up the documentation for nextval and currval. In particular, that
> > second query should be:
> > 
> > insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
> > 
> > Also, I'm not sure if inheritance works quite the way you think in the
> > example you give, though other people may correct me on that.
> > 
> > Hope this helps,
> > --
> > Martijn van Oosterhout  http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> > 
> > 
> >
> 
> ---(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

-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgp4GtQKl0WML.pgp
Description: PGP signature

Re: [GENERAL] Object Relational, Foreign Keys and Triggers

[Stephan Szabo]

On Mon, 24 Jan 2005, Alex Turner wrote:

> Insert fails with a foreign key constraint error because entity_phone
> points to entity, not person, and the rows aren't physicaly in entity,
> they are in person.
>
> Two questions:
> 1) Why can't we make this work the 'right' way - not doing so either
> breaks OO or brakes RDBMS.  1)a) Whats the point of an RDBMS if you
> can't specify foreign keys that work because you choose to use OO
> features (I somewhat appreciate that there is a trigger inheritance
> problem, can't we just define the rules and order of precident and
> solve it)?

There are multiple problems involved mostly due to the fact that
inheritance really need alot of work. For example, the actual
implementation of the schema you gave has no interlock to prevent
duplicate rows in person and entity (or another entity subclass). The
primary key implementation also only guarantees local uniqueness.
Inheritance really needs some developers who care strongly about it.

> 2) Whats the best way to manage this with triggers.  Obviously one can
> create a trigger on entity and on person for delete so that it removes
> corresponding rows in entity_phone.  But whats the best way to create
> a trigger that ensures that entity_ids that are used in entity_phone
> exist in entity and it's subtables thats fast.  You could do:

There's been discussion about this in the past, so you can get details
from the archives, but using a separate table to store the ids with
triggers between entity and person and the new table which manage the id
list has been proposed as a workaround.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Object Relational, Foreign Keys and Triggers

[Alex Turner]

Actualy max() works just fine.  It's not the solution I use in the
middle tier, but it works for a functional example.  both max() and
currval() are bad because they can cause a race condition where the
sequence has been incremented by another thread.  It's always better
to get nextval('sequence') and store it in a local var, then use it in
the main insert and corresponding sub-inserts.

The example I give has been tested, and works, it's not fake.

Alex Turner
NetEconomist

On Tue, 25 Jan 2005 09:23:31 +0100, Martijn van Oosterhout
 wrote:
> On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> > I am facing the classic pgsql ORDBMS problem:
> 
> 
> 
> Why are you using MAX()? That won't work at all. Perhaps you need to
> look up the documentation for nextval and currval. In particular, that
> second query should be:
> 
> insert into entity_phone select currval('entity_id_seq'),'610 495 5000';
> 
> Also, I'm not sure if inheritance works quite the way you think in the
> example you give, though other people may correct me on that.
> 
> Hope this helps,
> --
> Martijn van Oosterhout  http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
> 
> 
>

---(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] Object Relational, Foreign Keys and Triggers

[Martijn van Oosterhout]

On Mon, Jan 24, 2005 at 07:22:32PM -0500, Alex Turner wrote:
> I am facing the classic pgsql ORDBMS problem:



Why are you using MAX()? That won't work at all. Perhaps you need to
look up the documentation for nextval and currval. In particular, that
second query should be:

insert into entity_phone select currval('entity_id_seq'),'610 495 5000';

Also, I'm not sure if inheritance works quite the way you think in the
example you give, though other people may correct me on that.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpN09KepBluE.pgp
Description: PGP signature

[GENERAL] Object Relational, Foreign Keys and Triggers

[Alex Turner]

I am facing the classic pgsql ORDBMS problem:

create sequence entity_id_seq;
create table entity (
  entity_id int not null default nextval('entity_id_seq'),
  primary key (entity_id)
);

create table person (
  first_name varchar(32) not null,
  last_name varchar(32) not null,
  primary key (entity_id)
) inherits (entity);

create sequence entity_phone_id_seq;
create table entity_phone (
  entity_phone_id int not null default nextval('entity_phone_id_seq'),
  entity_id int not null,
  phone varchar(32),
  constraint entity_phones_entity_id_fk foreign key (entity_id)
references entity on delete cascade,
  primary key (entity_phone_id)
);

-- Insert the person
insert into person (first_name,last_name) values ('Alex','Turner');
-- Attempt to insert phone
insert into entity_phone select max(entity_id), '610 495 5000' from person;


Insert fails with a foreign key constraint error because entity_phone
points to entity, not person, and the rows aren't physicaly in entity,
they are in person.

Two questions:
1) Why can't we make this work the 'right' way - not doing so either
breaks OO or brakes RDBMS.  1)a) Whats the point of an RDBMS if you
can't specify foreign keys that work because you choose to use OO
features (I somewhat appreciate that there is a trigger inheritance
problem, can't we just define the rules and order of precident and
solve it)?

2) Whats the best way to manage this with triggers.  Obviously one can
create a trigger on entity and on person for delete so that it removes
corresponding rows in entity_phone.  But whats the best way to create
a trigger that ensures that entity_ids that are used in entity_phone
exist in entity and it's subtables thats fast.  You could do:

select into foo where entity_id=NEW.entity_id from entity;  
if not found then
  raise exception 'Value for entity_id not found in entity';
endif;
-- is this the fastest way?

currently using 7.4.5 on RHEL3, but will be upgrading to 8.0 this week.

Alex Turner
NetEconomist

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Object Relational features in PostgreSQL

[Robert Abi Saab]

Well in fact we're currently using SQL server 2000, which doesn't
support object oriented technology; however, I was researching the
possibility of migrating to a database that provides the features I
mentioned, as, although not 100% crucial to the application, they fit
pretty well with the nature of the 3d construction framework we're
developing.

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Object

[Richard Huxton]

From: "Renaud Tthonnart" <[EMAIL PROTECTED]>

> Can I create object and methods with postgreSQL.
> And how?
>
> Thank in advance,
> Renaud THONNART

You can define your own types with functions and operators - see chapter 13
of the programmers manual.

- Richard Huxton

[GENERAL] Object

[Renaud Tthonnart]

Can I create object and methods with postgreSQL.
And how?

Thank in advance,
Renaud THONNART

[GENERAL] object features in Oracle 8/9

[Ned Lilly]

All,

Came across a Gartner/Tech Republic research piece on object support
in Oracle 8:

http://www.techrepublic.com/download_item.jhtml?id=dr00220001002jim01.htm

You need to register for the site, but it's free.  I'd be happy to
email the PDF to anyone individually as well.

Anyone know what's new in the new 9i?

Regards,
Ned



Ned Lilly e: [EMAIL PROTECTED]
Vice Presidentw: www.greatbridge.com
Evangelism / Hacker Relationsv: 757.233.5523
Great Bridge, LLCf: 757.233.

[GENERAL] Object syntax

[Michael Ansley]

Title: Object syntax





Given the following table definitions, and data inserts:


dev=# create address (addr varchar(50), postcode varchar(9));
dev=# create client (name varchar(30), addr address);
dev=# insert into client values ('Michael');
dev=# insert into address values ('11 Windsor Close', 'RH16 4QR');
INSERT 18935 1
dev=# update client set addr = 18935::address;


dev=# explain select client.addr.postcode from client; 
NOTICE:  QUERY PLAN: 
Seq Scan on client  (cost=0.00..1.01 rows=1 width=4) 
EXPLAIN 
dev=# select client.addr.postcode from client; 
ERROR:  init_fcache: Cache lookup failed for procedure 18935 


What's happening here?  Bug, or am I doing something wrong?


Cheers... 

Re: [GENERAL] Object oriented features - MISSING

[Chris Bitmead]

Hi,

This has been broken in postgres for many years. But I have fixed it 
recently in current CVS. So you'll either have to wait for the next
release or else risk a development version. Another option is to go
to patches archive and manually apply the patch to 7.0.2 which wouldn't
be that hard.

Chris Bitmead.


Felipe Alvarez Harnecker wrote:
> 
> Hi, Postgresistas
> 
> I'm running 7.0.2 in a Debian system, and a have this problem:
> 
> SELECT * FROM some_base_table*
> 
> works fine, but
> 
> UPDATE some_base_table* SET a = b WHERE some_condition
> 
> and
> 
> DELETE FROM some_base_table* WHERE some_condition
> 
> dosen't even parse: the parser says parser: error at or near "*"
> 
> The question is Is this a bug in Postgres? or just my instalation?
> 
> Thanks.
> 
> PS. I really like the Object oriented features ( at leas as describe
> in the docs ). It's a bad thing tha those dosen't work
> 
> Regards.
> 
> --
> __
> 
> Felipe Alvarez Harnecker.  QlSoftware.
> 
> Tel. 09.874.60.17  e-mail: [EMAIL PROTECTED]
> 
> Potenciado por Ql/Linux  http://www.qlsoft.cl/
> __

Re: [GENERAL] Object-oriented stuff and postgres

[Chris Bitmead]

Umm. I need to know the type of the _object_, not the types of the
attributes contained therein.


José Soares wrote:
> 
> --retrieve column information...
> 
> SELECT a.attnum, a.attname, t.typname, a.attlen,
> a.atttypmod, a.attnotnull, a.atthasdef
> FROM pg_class c, pg_attribute a, pg_type t
> WHERE c.relname = 'comuni'
> and a.attnum > 0
> and a.attrelid = c.oid
> and a.atttypid = t.oid
>   ORDER BY attnum ;
> attnum|attname   |typname|attlen|atttypmod|attnotnull|atthasdef
> --+--+---+--+-+--+-
>  1|istat |bpchar |-1|   10|t |f
>  2|nome  |bpchar |-1|   54|t |f
>  3|provincia |bpchar |-1|6|f |f
>  4|codice_fiscale|bpchar |-1|8|f |f
>  5|cap   |bpchar |-1|9|f |f
>  6|regione   |bpchar |-1|7|f |f
>  7|distretto |bpchar |-1|8|f |f
> (7 rows)
> 
> 
> José
> 
> Chris Bitmead ha scritto:
> 
> > What's the best way to do this in postgres? (basicly finding the
> > type of
> > objects).
> >
> > I want to run a web site with different types of content - question
> > and
> > answers, stories etc. I propose an object hierarchy...
> > webobject (title, body)
> >question inherits webobject
> >story (image) inherits (webobject).
> >
> > The idea being you could have a search screen that searches
> > questions
> > AND stories with the one SELECT query.
> >
> > But then each result would have a link to examine the body of the
> > search
> > result. But different types of objects would have different URLs to
> > display that content.
> >
> > So basicly I need to know the type of objects returned.
> >
> > I am loath to store the object type inside the object because it is
> > wasteful. PG obviously already knows the type of objects, the
> > question
> > is how to get at that info.

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:[EMAIL PROTECTED]

Re: [GENERAL] Object-oriented stuff and postgres

[José Soares]

--retrieve column information...
    SELECT a.attnum, a.attname,
t.typname, a.attlen,
    a.atttypmod, a.attnotnull,
a.atthasdef
    FROM pg_class c, pg_attribute
a, pg_type t
    WHERE c.relname = 'comuni'
   
and a.attnum > 0
   
and a.attrelid = c.oid
   
and a.atttypid = t.oid
  ORDER BY
attnum ;
attnum|attname   |typname|attlen|atttypmod|attnotnull|atthasdef
--+--+---+--+-+--+-
 1|istat
|bpchar |    -1|   10|t
|f
 2|nome 
|bpchar |    -1|   54|t
|f
 3|provincia |bpchar
|    -1|    6|f
|f
 4|codice_fiscale|bpchar |   
-1|    8|f
|f
 5|cap  
|bpchar |    -1|   
9|f |f
 6|regione  
|bpchar |    -1|   
7|f |f
 7|distretto |bpchar
|    -1|    8|f
|f
(7 rows)
 
José
Chris Bitmead ha scritto:
What's the best way to do this in postgres? (basicly
finding the type of
objects).
I want to run a web site with different types of content - question
and
answers, stories etc. I propose an object hierarchy...
webobject (title, body)
   question inherits webobject
   story (image) inherits (webobject).
The idea being you could have a search screen that searches questions
AND stories with the one SELECT query.
But then each result would have a link to examine the body of the search
result. But different types of objects would have different URLs to
display that content.
So basicly I need to know the type of objects returned.
I am loath to store the object type inside the object because it is
wasteful. PG obviously already knows the type of objects, the question
is how to get at that info.

[GENERAL] Object-oriented stuff and postgres

[Chris Bitmead]

What's the best way to do this in postgres? (basicly finding the type of
objects).

I want to run a web site with different types of content - question and
answers, stories etc. I propose an object hierarchy...
webobject (title, body)
   question inherits webobject
   story (image) inherits (webobject).

The idea being you could have a search screen that searches questions
AND stories with the one SELECT query.

But then each result would have a link to examine the body of the search
result. But different types of objects would have different URLs to
display that content. 

So basicly I need to know the type of objects returned.

I am loath to store the object type inside the object because it is
wasteful. PG obviously already knows the type of objects, the question
is how to get at that info.