Re: [SQL] owner of type 'mmm' apperars to be invalid

[Richard Huxton]

On Tuesday 05 Nov 2002 5:57 am, cristi wrote:
>  When I'm trying to make pg_dump on a data base I have this message:
>
> WARNING: owner of type 'mmm' apperars to be invalid
> WARNING: owner of type 'eee' apperars to be invalid
> WARNING: owner of type 'refcursor' apperars to be invalid
>
> Where are these object and how can I destroy its?

richardh=> select oid,typname,typowner from pg_type where typname='refcursor';
 oid  |  typname  | typowner
--+---+--
 1790 | refcursor |1

richardh=> select * from pg_user where usesysid=1;
 usename  | usesysid | usecreatedb | usetrace | usesuper | usecatupd |  passwd  
| valuntil
--+--+-+--+--+---+--+--
 postgres |1 | t   | t| t| t | 

I'd recommend you add a user with the required id, then dump the database and 
fix the dump as required. The owner of "refcursor" should be postgres (or 
whatever the system user is called) - if that user has been deleted, I'd be 
surprised that's the only errors you get.

-- 
  Richard Huxton

---(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] [SQL] Database Design tool

[Michael Ansley (UK)]

Title: RE: [GENERAL] [SQL] Database Design tool





 
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1


DBVisualizer is pretty good.


>> -Original Message-
>> From: Johannes Lochmann [mailto:[EMAIL PROTECTED]]
>> Sent: 30 October 2002 14:36
>> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
>> Subject: Re: [GENERAL] [SQL] Database Design tool
>> 
>> 
>> On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote:
>> 
>> Hi,
>> 
>> (which list should this go to? I guess it is OT on both...)
>>  
>> > Can anybody take me reference on Database design tool with 
>> PostgreSQL 
>> > support.
>> 
>> Dia and dia2sql (or something similar...) Google knows more :-)
>> 
>> HTH
>> 
>> Johannes Lochmann
>> 
>> 
>> ---(end of 
>> broadcast)---
>> TIP 2: you can get off all lists at once with the unregister
>> command 
>> (send "unregister YourEmailAddressHere" to 
>> [EMAIL PROTECTED])
>> 


-BEGIN PGP SIGNATURE-
Version: PGPfreeware 7.0.3 for non-commercial use 


iQA/AwUBPceuMnympNV/C086EQJqAwCeO9PKMCA5oY5z8YTsiPlyHSAtPmgAoPnD
eSAe+7O2dDeMrkRQwtsSQVXh
=A3fp
-END PGP SIGNATURE-




This e-mail and any attachments are confidential and may also be privileged and/or copyright 
material of Intec Telecom Systems PLC (or its affiliated companies).  If you are not an 
intended or authorised recipient of this e-mail or have received it in error, please delete 
it immediately and notify the sender by e-mail.  In such a case, reading, reproducing, 
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. 
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free 
from computer viruses or other defects. The opinions expressed in this e-mail and any 
attachments may be those of the author and are not necessarily those of Intec Telecom 
Systems PLC. 

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

Re: [SQL] owner of type 'mmm' apperars to be invalid

[cristi]

It shouldn't be this the solution of the problem?

salt=# delete from pg_type where typname='refcursor';
DELETE 1
salt=# delete from pg_type where typname='eee';
DELETE 1
salt=# delete from pg_type where typname='mmm';
DELETE 1

Now, I don't have the error message!

Mr.Richard Huxton, thank you very much!

Cristi



On Tuesday 05 Nov 2002 11:55 am, cristi wrote:
> I'm in this situation:
>
> salt=# select oid,typname,typowner from pg_type where typname='refcursor';
>oid|  typname  | typowner
> --+---+--
>  13701973 | refcursor |0
> (1 row)
>
> salt=# select * from pg_user where usesysid=0;
>  usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd

> (0 rows)

Hmm - try the following (it should show the 5 users with lowest number - the
first should be 'postgres').

SELECT * FROM pg_user ORDER BY usesysid limit 5;

> 1)Which is the user with the required id in my case?
> 2)How can I  add a user with the required id?(the command)

Hmm - assuming the query above is good, I'd change "typowner" in "pg_type"
instead. Also the following should all display the same typowner (but we
know
there is a problem with refcursor). That typowner should be the postgres
user.

SELECT typname,typowner FROM pg_type WHERE typname LIKE 'r%';


--
  Richard Huxton



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

http://archives.postgresql.org

Re: [SQL] owner of type 'mmm' apperars to be invalid

[Richard Huxton]

On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> It shouldn't be this the solution of the problem?
>
> salt=# delete from pg_type where typname='refcursor';
> DELETE 1
> salt=# delete from pg_type where typname='eee';
> DELETE 1
> salt=# delete from pg_type where typname='mmm';
> DELETE 1
>
> Now, I don't have the error message!
>
> Mr.Richard Huxton, thank you very much!

But - you have deleted the "refcursor" type which is not a good idea! If you 
don't use cursors you might get away with it, but I'd be nervous about it.

If you create a new database does it contain the refcursor type? If so, drop 
and recreate the database, restoring the data to it. If not, I think you'll 
need to restore the template1 db - there's an article on 
techdocs.postgresql.org about this (byJosh IIRC).
-- 
  Richard Huxton

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

[SQL] Copying a rowtype variable.

[Rison, Stuart]

Hello,

Just doing a bit of PL/PGSQL so my first question is:

1) should PL/PGSQL questions be posted to the general mailing list, the sql
mailing list or both?

My second question is:

2) I am looking for an elegant way of copying a rowtype variable:

eg.

DECLARE
current_row orf%ROWTYPE;
previous_row orf%ROWTYPE;
BEGIN

LOOP
-- use cursors or FOR SELECT to get values into current_row
-- now try this:

previous_row = current_row;
END LOOP;
END;

Now, as I anticipated, this fails because a rowtype variable is a composite
variable.  One working alternative is to do:

previous_row.pid = current_row.pid;
previous_row.start = current_row.start;
-- and so on so forth for all current_row variables

But this is inconvenient of the row has many fields and impossible if you
want a flexible function which can accomodate rowtypes for which the fields
are not known "a priori".

Any clever workarounds?

Cheers,

Stuart.

PS.  I started using PGSQL five years ago... then I had to leave it alone
for a while... I can't believe how much it's grown and developed.  AMAZING,
congrats to all developers.


--
Stuart C. G. Rison
Department of Pathology and Infectious Diseases
Royal Veterinary College
London.
United Kingdom.

"That's bioinformatics... and we don't want to be doing bioinformatics, we
want to do real science.", Sydney Brenner, UCL Pfizer Lecture, 8 May 2001

--

---(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] [SQL] Database Design tool

[Thomas Good]

On Tue, 5 Nov 2002, Michael Ansley (UK) wrote:

> DBVisualizer is pretty good.

Is there a page at PostgreSQL.org where all these links are listed?
I've been saving each bit of mail that goes by on this thread...but
a page on Pg would be very nice.  ;-)

Cheers,
---
Thomas Good  e-mail: [EMAIL PROTECTED]
Programmer/Analyst   phone:   (+1) 718.818.5528
Residential Services fax: (+1) 718.818.5056
Behavioral Health Services, SVCMC-NY mobile:  (+1) 917.282.7359

--Geistiges Eigentum ist Diebstahl!  --


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

Re: [SQL] owner of type 'mmm' apperars to be invalid

[cristi]

On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> It shouldn't be this the solution of the problem?

>> salt=# delete from pg_type where typname='refcursor';
>> DELETE 1


>> Now, I don't have the error message!
> >Mr.Richard Huxton, thank you very much!

>But - you have deleted the "refcursor" type which is not a good idea! If
you
>don't use cursors you might get away with it, but I'd be nervous about it.

>If you create a new database does it contain the refcursor type?

Ooo
Yes, I see...
You right.

>If so, drop and recreate the database, restoring the data to it. If not, I
think you'll
>need to restore the template1 db - there's an article on
>techdocs.postgresql.org about this (byJosh IIRC).

Would you give me the URL address for this article?
Please!



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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [SQL] owner of type 'mmm' apperars to be invalid

[Richard Huxton]

On Tuesday 05 Nov 2002 2:32 pm, cristi wrote:
> On Tuesday 05 Nov 2002 1:03 pm, cristi wrote:
> >But - you have deleted the "refcursor" type which is not a good idea! If

> Ooo
> Yes, I see...
> You right.
>
> >If so, drop and recreate the database, restoring the data to it. If not, I
> think you'll
> >need to restore the template1 db - there's an article on
> >techdocs.postgresql.org about this (byJosh IIRC).
>
> Would you give me the URL address for this article?
> Please!

It's "Adventures in PostgreSQL, Episode 1 by Josh Berkus"
at http://techdocs.postgresql.org/
Loads of other good stuff there too.

Might not be necessary though - if the template1 db contains the refcursor 
type definition then you just need to drop and recreate the problem database.

-- 
  Richard Huxton

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

Re: [GENERAL] [SQL] Database Design tool

[Josh Berkus]

Thomas,

> > DBVisualizer is pretty good.
> 
> Is there a page at PostgreSQL.org where all these links are listed?
> I've been saving each bit of mail that goes by on this thread...but
> a page on Pg would be very nice.  ;-)

I was planning on writing an article; so far, I have a list of about 14
interfaces.

When done, the article will go up at TechDocs or the Postgres Wiki
(currently under development).

-Josh

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

Re: [SQL] Copying a rowtype variable.

[Josh Berkus]

Stuart,

> Just doing a bit of PL/PGSQL so my first question is:
> 
> 1) should PL/PGSQL questions be posted to the general mailing list,
> the sql
> mailing list or both?

The SQL list is the best place.

> 2) I am looking for an elegant way of copying a rowtype variable:
> 
> eg.
> 
> DECLARE
>  current_row orf%ROWTYPE;
>  previous_row orf%ROWTYPE;
> BEGIN
> 
>  LOOP
>  -- use cursors or FOR SELECT to get values into current_row
>  -- now try this:
> 
>  previous_row = current_row;
>  END LOOP;
> END;
> 
> Now, as I anticipated, this fails because a rowtype variable is a
> composite
> variable.  One working alternative is to do:

I haven't tried this.  One thing I notice above is that you're using
the equality operator "=" instead of the assignment operator ":="  .
  Usually Postgres lets you slack on this, but it would be worth trying
to see whether that has an effect on the problem.

Another thing to try is, instead of a simple variable assignment,

SELECT current_row INTO previous_row;

... and see if that works.   I'll tinker later today; there has to be a
way to do it.

-Josh Berkus

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

Re: [GENERAL] [SQL] Database Design tool

[Robert Treat]

There are actually two sections on the techdocs site now relating to
this at http://techdocs.postgresql.org/oresources.php
look under the sections ERD Tools and Database Design

Not that I'm not looking forward to your article Josh ;-)

Robert Treat

On Tue, 2002-11-05 at 11:33, Josh Berkus wrote:
> Thomas,
> 
> > > DBVisualizer is pretty good.
> > 
> > Is there a page at PostgreSQL.org where all these links are listed?
> > I've been saving each bit of mail that goes by on this thread...but
> > a page on Pg would be very nice.  ;-)
> 
> I was planning on writing an article; so far, I have a list of about 14
> interfaces.
> 
> When done, the article will go up at TechDocs or the Postgres Wiki
> (currently under development).
> 
> -Josh
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster




---(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] [SQL] Database Design tool

[Josh Berkus]

Robert,

> There are actually two sections on the techdocs site now relating to
> this at http://techdocs.postgresql.org/oresources.php
> look under the sections ERD Tools and Database Design
> 
> Not that I'm not looking forward to your article Josh ;-)

Thanks.  This gives me a few more tools to look at.   Boy, do we have an 
answer for the folks who complain "there are no GUI tools for Postgres".

I'll also have Justin update his list on Techdocs when I write my article.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


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

Re: [GENERAL] [SQL] Database Design tool

[Kaare Rasmussen]

> Thanks.  This gives me a few more tools to look at.   Boy, do we have an
> answer for the folks who complain "there are no GUI tools for Postgres".

While there are a number of GUI tools for PostgreSQL, there's still no real 
database design tool like ErWin or so.

-- 
Kaare Rasmussen--Linux, spil,--Tlf:3816 2582
Kaki Datatshirts, merchandize  Fax:3816 2501
Howitzvej 75   Åben 12.00-18.00Email: [EMAIL PROTECTED]
2000 FrederiksbergLørdag 12.00-16.00   Web:  www.suse.dk

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

http://archives.postgresql.org

Re: [GENERAL] [SQL] Database Design tool

[tony]

On Tue, 2002-11-05 at 15:00, Thomas Good wrote:


> Is there a page at PostgreSQL.org where all these links are listed?
> I've been saving each bit of mail that goes by on this thread...but
> a page on Pg would be very nice.  ;-)

you mean like techdocs.postgresql.org ???

The page that is mentionned on the postgresql web site???

nuff said?

Tony


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

Re: [GENERAL] [SQL] Database Design tool

[Patrick Bakker]

Title: RE: [GENERAL] [SQL] Database Design tool






I had gASQL working once but that was awhile ago. gASQL has now been renamed as Mergeant and is being developed in conjunction with the gnome-db libraries as far as I know. I don't think there has been a stable release of it yet, although it is under active development.

Patrick


> -Original Message-

> From: [EMAIL PROTECTED]

> [mailto:[EMAIL PROTECTED]]On Behalf Of Dan Hrabarchuk

> Sent: Wednesday, October 30, 2002 7:31 AM

> To: Johannes Lochmann

> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]

> Subject: Re: [GENERAL] [SQL] Database Design tool

> 

> 

> gASQL is a gnome-db client that looks like it has a lot of 

> promise. The

> only problem is I've never been able to get the application to run

> properly. I'm using RedHat 8.0 on my desktop. The last 

> official version

> does not install properly. If I grab a CVS copy, I go through 

> dependency

> hell. Has anyone ever actually gotten gASQL to work?

> 

> Dan

> 

> On Wed, 2002-10-30 at 06:35, Johannes Lochmann wrote:

> > On Wed, 2002-10-30 at 07:45, Viacheslav N Tararin wrote:

> > 

> > Hi,

> > 

> > (which list should this go to? I guess it is OT on both...)

> >  

> > > Can anybody take me reference on Database design tool 

> with PostgreSQL 

> > > support.

> > 

> > Dia and dia2sql (or something similar...) Google knows more :-)

> > 

> > HTH

> > 

> > Johannes Lochmann

> > 

> > 

> > ---(end of 

> broadcast)---

> > TIP 2: you can get off all lists at once with the unregister command

> > (send "unregister YourEmailAddressHere" to 

> [EMAIL PROTECTED])

> 

> 

> 

> ---(end of 

> broadcast)---

> TIP 6: Have you searched our list archives?

> 

> http://archives.postgresql.org

> 

Re: [GENERAL] [SQL] Database Design tool

[Robert Treat]

Well, actually ERWin does work with PostgreSQL via ODBC.  The version I
tried was I think the last release of the 3.x series, and I had enough
trouble with it to decide against using it. I am told the 4.x series
improved things, though if your already familiar with erwin you probably
could get by. Instead I decided to use Case Studio 2.x, which is a
pretty good replacement imo. (For the record though, it is neither free
nor does it run on linux).   

Robert Treat

On Tue, 2002-11-05 at 15:16, Kaare Rasmussen wrote:
> > Thanks.  This gives me a few more tools to look at.   Boy, do we have an
> > answer for the folks who complain "there are no GUI tools for Postgres".
> 
> While there are a number of GUI tools for PostgreSQL, there's still no real 
> database design tool like ErWin or so.
> 



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