Re: [GENERAL] SQL query question

[Jonel Rienton]

you're right it's late, i better to get to bed myself, i forgot to 
throw in the parameter for the user_id in there, i'm sure you can 
figure that one out.

regards,
-
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Maybe it's to late for me to think correctly (actually I'm sure of 
that). I'm
going to ask anyways.
I have a table like

id int4
user_id int4
photo varchar
image_type char(1)
where image_type is either G or X
What I want to do is have ONE query that gives me the count of images 
of each
type per user_id.
So if user 3 has 5 photos of type G and 3 photos of type X
I basically want to have a result 5,3
It got to be possible to get a query like that, but somehow it eludes 
me
tonight.

Any pointers are greatly appreciated.
UC
- --
Open Source Solutions 4U, LLC   2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7JrqQqQ5GgCgkTQN
pavTkx47QUb9nr7XO/r/v5k=
=B3DH
-END PGP SIGNATURE-
---(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 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] pgpool 2.5b2 released

[Tatsuo Ishii]

> > Pgpool 2.5b2 supports "master slave mode" which can cope with
> > master/slave replication softwares such as Slony-I. In this mode
> > pgpool sends non SELECT queries to master only. SELECTs are load
> > balanced by pgpool.
> 
> Sounds good!

Thanks. Yesterday I have put offcial release of pgpool 2.5 on
pgfoundry.org. The news release has not come up yet on the top page of
pgfoundry.org for some reason I don't know, but I hope it will appear
soon.

pgpool 2.5 has the capabilty to perform periodical health checking to
PostgreSQL.

> Does it attempt any interaction with Slony when it detects a failure of the
> master?  It would seem a pity to have pgpool watching the pair to detect
> failure but having to have a separate watcher process to tell Slony to
> failover.

If pgpool detects PostgreSQL failure, Slony should detect it as well,
no?
--
Tatsuo Ishii

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

[Jonel Rienton]

Hi Uwe,
I did a solution for you using PLPgSQL,
create or replace function countem() returns varchar as $$
declare
   gcount integer;
   xcount integer;
   result varchar;
begin
select count(*) into gcount
from pix where image_type = 'G';
select count(*) into xcount
from pix where image_type = 'X';
select gcount || ', ' || xcount
into result;
return result;
end;
$$ LANGUAGE plpgsql;
hope this helps, it's simple and always, there's another (better) 
solution
it's my first stab at plpgsql so please bear with me.

-
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate
On Feb 3, 2005, at 1:32 AM, Uwe C. Schroeder wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Maybe it's to late for me to think correctly (actually I'm sure of 
that). I'm
going to ask anyways.
I have a table like

id int4
user_id int4
photo varchar
image_type char(1)
where image_type is either G or X
What I want to do is have ONE query that gives me the count of images 
of each
type per user_id.
So if user 3 has 5 photos of type G and 3 photos of type X
I basically want to have a result 5,3
It got to be possible to get a query like that, but somehow it eludes 
me
tonight.

Any pointers are greatly appreciated.
UC
- --
Open Source Solutions 4U, LLC   2570 Fleetwood Drive
Phone:  +1 650 872 2425 San Bruno, CA 94066
Cell:   +1 650 302 2405 United States
Fax:+1 650 872 2417
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFCAdOMjqGXBvRToM4RApgvAJsEUsdl6hrVGqRwJ+NI7JrqQqQ5GgCgkTQN
pavTkx47QUb9nr7XO/r/v5k=
=B3DH
-END PGP SIGNATURE-
---(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 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Problem with the sequence

[Richard Huxton]

sid tow wrote:
HI
 
 I have a problem locating the documentation for "sequence". I want to get the detailed information about the columns present in a sequence table ie when I do 
 
psql=# select * from foo_seq; 
 sequence_name   | last_value | increment_by |  max_value  | min_value | cache_value | log_cnt | is_cycled | is_called 
---++--+-+---+-+-+---+---
 foo_seq| 11 |1 | 9223372036854775807 | 1 |   1 |   0 | f | t
(1 row)

I need to know where do i find documentation to know what the columns specify and I have already checked the man pages of create_sequence where I did not find much. 
Can somebody give me ref to a link where I get such information.
From psql:
\d foo_seq
Sequence "public.foo_seq"
Column |  Type
---+-
 sequence_name | name
 last_value| bigint
 increment_by  | bigint
 max_value | bigint
 min_value | bigint
 cache_value   | bigint
 log_cnt   | bigint
 is_cycled | boolean
 is_called | boolean
From the manuals:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
 [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
 [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
For the ones not mentioned in the manual:
 last_value - is the last value given out by the sequence
 is_called  - says whether nextval() has been called in this session
  (and so whether it is safe to call currval())
 log_cnt- don't know, googling suggests it's to do with WAL logging.
Does that help?
--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] change table to view problem

[Richard Huxton]

Sim Zacks wrote:
I merged 2 tables into 1 table and created a view  for each of the tables so
as not to break existing applications. I renamed the old tables with the
_old suffix in case there was a problem so I could verify against the old
data.
The problem is that all the views and functions switched the name of the
table to the renamed table and do not use the new views I have created. Is
there a recommended way of updating all the views and functions that  use
the table to start using the view?
This is how it's supposed to work. System objects (views, foreign-keys, 
some functions etc) track other objects by their OID. Otherwise changes 
to table-names, or schema search-paths could wreak havoc.

If you want to attach the views to the new tables then you'll need to 
recreate them.

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

Re: [GENERAL] Problem with the sequence

[Carlos Costa]

I wrote a very basic entry about this at improveyourweb some weeks ago
(http://www.improveyourweb.com/?q=node/2). Hope it helps.

In the source code, sequence.c, you can read something like:

   /*
* Decide whether we should emit a WAL log record.  If so, force up
* the fetch count to grab SEQ_LOG_VALS more values than we actually
* need to cache.  (These will then be usable without logging.)
*
* If this is the first nextval after a checkpoint, we must force a new
* WAL record to be written anyway, else replay starting from the
* checkpoint would fail to advance the sequence past the logged
* values.  In this case we may as well fetch extra values.
*/
 if (log < fetch)...

log is log_cnt (or log_cnt-1 if is_called equals false)
fetch is cache_value (or cache_value-1 if is_called equals false)

If someone more can help, it would interesting for all understand this better.


On Thu, 03 Feb 2005 09:22:55 +, Richard Huxton  wrote:
> sid tow wrote:
> > HI
> >
> >  I have a problem locating the documentation for "sequence". I want to 
> > get the detailed information about the columns present in a sequence table 
> > ie when I do
> >
> > psql=# select * from foo_seq;
> >  sequence_name   | last_value | increment_by |  max_value  | 
> > min_value | cache_value | log_cnt | is_cycled | is_called
> > ---++--+-+---+-+-+---+---
> >  foo_seq| 11 |1 | 
> > 9223372036854775807 | 1 |   1 |   0 | f | t
> > (1 row)
> >
> > I need to know where do i find documentation to know what the columns 
> > specify and I have already checked the man pages of create_sequence where I 
> > did not find much.
> > Can somebody give me ref to a link where I get such information.
> 
>  From psql:
> \d foo_seq
> Sequence "public.foo_seq"
>  Column |  Type
> ---+-
>   sequence_name | name
>   last_value| bigint
>   increment_by  | bigint
>   max_value | bigint
>   min_value | bigint
>   cache_value   | bigint
>   log_cnt   | bigint
>   is_cycled | boolean
>   is_called | boolean
> 
>  From the manuals:
> CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
>   [ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ]
>   [ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]
> 
> For the ones not mentioned in the manual:
>   last_value - is the last value given out by the sequence
>   is_called  - says whether nextval() has been called in this session
>(and so whether it is safe to call currval())
>   log_cnt- don't know, googling suggests it's to do with WAL logging.
> 
> Does that help?
> --
>Richard Huxton
>Archonet Ltd
> 
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>

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

   http://archives.postgresql.org

Re: [GENERAL] basic pg lock question

[Alban Hertroys]

Scott Marlowe wrote:
Method 2 often provides all the protection you need and is quite easy to
program.  You basically do something like:
To use this to prevent simultaneous inserts of the same data (for 
example if two employees try to insert the same contact into the DB), I 
suppose you could use a constraint (before insert) that checks that 
there is no data matching the md5 checksum, right?

--
Alban Hertroys
MAG Productions
T: +31(0)53 4346874
F: +31(0)53 4346876
E: [EMAIL PROTECTED]
W: http://www.magproductions.nl
---(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] pgpool 2.5b2 released

[Julian Scarfe]

> > Does it attempt any interaction with Slony when it detects a failure of
the
> > master?  It would seem a pity to have pgpool watching the pair to detect
> > failure but having to have a separate watcher process to tell Slony to
> > failover.
>
> If pgpool detects PostgreSQL failure, Slony should detect it as well, no?

Others are much better place to answer this, but my understanding is that
Slony does not do so:

http://gborg.postgresql.org/project/slony1/genpage.php?howto_overview
"What Slony-I is not:

Slony-I is not a network management system.  Slony-I does not have any
functionality within it to detect a node failure, or automatically promote a
node to a master or other data origin.  Slony-I is not multi-master; it's
not
a connection broker, and it doesn't make you coffee and toast in the
morning."

> pgpool 2.5 has the capabilty to perform periodical health checking to
> PostgreSQL.

Since pgpool has this capability, how about including a hook that allows a
script to be run when pgpool detects a problem with the master?  That would
allow action to be taken to investigate further and, if required, switchover
or failover and promote the slave to master.

Julian Scarfe



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

   http://archives.postgresql.org

Re: [GENERAL] Problem with the sequence

[sid tow]

Thanks that should help but what about the rest of the columns can u explain what is "is_cycled" and "cache_value". Thanks in advance.Richard Huxton  wrote:
sid tow wrote:> HI> > I have a problem locating the documentation for "sequence". I want to get the detailed information about the columns present in a sequence table ie when I do > > psql=# select * from foo_seq; > sequence_name | last_value | increment_by | max_value | min_value | cache_value | log_cnt | is_cycled | is_called > ---++--+-+---+-+-+---+---> foo_seq | 11 | 1 | 9223372036854775807 | 1 | 1 | 0 | f | t> (1 row)> > I need to know where do i find documentation to know what the columns specify and I have already checked the man pages of create_sequence where I did not find much. > Can somebody give me ref to a link where I get such information.From psql:\d
 foo_seqSequence "public.foo_seq"Column | Type---+-sequence_name | namelast_value | bigintincrement_by | bigintmax_value | bigintmin_value | bigintcache_value | bigintlog_cnt | bigintis_cycled | booleanis_called | booleanFrom the manuals:CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ][ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO MAXVALUE ][ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]For the ones not mentioned in the manual:last_value - is the last value given out by the sequenceis_called - says whether nextval() has been called in this session(and so whether it is safe to call currval())log_cnt - don't know, googling suggests it's to do with WAL logging.Does that help?-- Richard HuxtonArchonet Ltd
		Do you Yahoo!? 
Yahoo! Search presents - Jib Jab's 'Second Term'

Re: [GENERAL] basic pg lock question

[PFC]

To use this to prevent simultaneous inserts of the same data (for  
example if two employees try to insert the same contact into the DB), I  
suppose you could use a constraint (before insert) that checks that  
there is no data matching the md5 checksum, right?

CREATE TABLE blah
(
mymd5   TEXT NOT NULL,
UNIQUE( mymd5 )
);
will do this for you automatically and create an index to enforce it  
(don'tcreate another index !)

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Problem with the sequence

[Richard Huxton]

sid tow wrote:
Thanks that should help but what about the rest of the columns can u
explain what is "is_cycled" and "cache_value". Thanks in advance.
See the manuals. Section "SQL Commands", "CREATE SEQUENCE"
--
  Richard Huxton
  Archonet Ltd
---(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] modifying views

[Mike Rylander]

On Wed, 2 Feb 2005 11:16:56 +0200, Sim Zacks <[EMAIL PROTECTED]> wrote:
> I read the following thread from Nov 2002 on the impossibilities of
> modifying a view and I was wondering if anything had changed in this regard
> since then?
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00609.php
> 
> Basically I want to remove a column from a table. The column is used in a
> view. The view, but not the column that I want to remove, is used in 24
> other views. A number of those views are also used in other views...
> 
> I can't remove the columns from the view without dropping over 100 other
> views, removing the columns from this view and recreating them. I was able
> to remove the columns from the table by modifying the view so instead of
> "table1.field1" it has "null::varchar as field1"
> The problem is that the field is still in the view and I don't want it there
> (obviously).
> 
> I read somewhere that a possible solution would be to do a pg_dump, manually
> change the text file and then do a pg_restore. Unfortunately, that means
> taking the system offline, which I can't do.

Actually, you shouldn't have to take the system down at all.

1) Do a 'pg_dump -s', which will give you just the schema of the DB.

2) Trim this file down to the create statements for the table and all
the dependant views, add the "DROP VIEW baseview CASCADE" and "ALTER
TABLE ... DROP COLUMN ..." statements to the top, and change the base
view's definition.

2a) (this is the REALLY important part!) put "BEGIN;" at the top and
DO NOT(!!!) put "COMMIT;" at the bottom, but DO put some test SELECTs
that will touch the base view and the dependant views to make sure
they are intact, and that the column is gone.

3) Use \i from within the psql console to run that SQL script.  The
script will be run inside a transaction, and the changes wont be
visible to anyone else until you type COMMIT;.

If the output of the test SELECTs looks good just commit the change. 
If you are unsure, or something seems to have gone wrong just type
"ROLLBACK;" and everything will be back the way is was before!

The only downtime will be the few seconds it takes to alter the table,
recreate the views, and inspect that everything is still OK.  If you
want to test safely you could do a full load of 'pg_dump -s' (again,
just the schema) into a temp database and test the script there.


> I could use any help that you can give me.
> Thanks
> Sim

Hope that counts as help!

-- 
Mike Rylander
[EMAIL PROTECTED]
GPLS -- PINES Development
Database Developer
http://open-ils.org

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

[Roman Neuhauser]

# [EMAIL PROTECTED] / 2005-02-02 23:32:28 -0800:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
> 
> 
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm going to ask anyways.  I have a table like
> 
> id int4
> user_id int4
> photo varchar
> image_type char(1)
> 
> where image_type is either G or X
> What I want to do is have ONE query that gives me the count of images
> of each type per user_id.
> So if user 3 has 5 photos of type G and 3 photos of type X 
> I basically want to have a result 5,3

SELECT COUNT(*) FROM t GROUP BY t.user_id, t.image_type

-- 
If you cc me or remove the list(s) completely I'll most likely ignore
your message.see http://www.eyrie.org./~eagle/faqs/questions.html

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

   http://archives.postgresql.org

Re: [GENERAL] Invalid headers and xlog flush failures

[Bricklen Anderson]

Bricklen Anderson wrote:
Tom Lane wrote:
Bricklen Anderson <[EMAIL PROTECTED]> writes:
Tom Lane wrote:
I would have suggested that maybe this represented on-disk data
corruption, but the appearance of two different but not-too-far-apart
WAL offsets in two different pages suggests that indeed the end of WAL
was up around segment 972 or 973 at one time.


Nope, never touched pg_resetxlog.
My pg_xlog list ranges from 000100730041 to 
0001007300FE, with no breaks. There are also these: 
00010074 to 00010074000B

That seems like rather a lot of files; do you have checkpoint_segments
set to a large value, like 100?  The pg_controldata dump shows that the
latest checkpoint record is in the 73/41 file, so presumably the active
end of WAL isn't exceedingly far past that.  You've got 200 segments
prepared for future activity, which is a bit over the top IMHO.
But anyway, the evidence seems pretty clear that in fact end of WAL is
in the 73 range, and so those page LSNs with 972 and 973 have to be
bogus.  I'm back to thinking about dropped bits in RAM or on disk.
IIRC these numbers are all hex, so the extra "9" could come from just
two bits getting turned on that should not be.  Might be time to run
memtest86 and/or badblocks.
regards, tom lane

Yes, checkpoint_segments is set to 100, although I can set that lower if 
you feel that that is more appropriate. Currently, the system receives 
around 5-8 million inserts per day (across 3 primary tables), so I was 
leaning towards the "more is better" philosophy.

We ran e2fsck with badblocks option last week and didn't turn anything 
up, along with a couple of passes with memtest. I will run a full-scale 
memtest and post any interesting results.

I've also read that kill -9 postmaster is "not a good thing". I honestly 
can't vouch for whether or not this may or may not have occurred around 
the time of the initial creation of this database. It's possible, since 
this db started it's life as a development db at 8r3 then was bumped to 
8r5, then on to 8 final where it has become a dev-final db.

Assuming that the memtest passes cleanly, as does another run of 
badblocks, do you have any more suggestions on how I should proceed? 
Should I run for a while with zero_damaged_pages set to true and accpet 
the data loss, or just recreate the whole db from scratch?

memtest86+ ran for over 15 hours with no errors reported.
e2fsck -c completed with no errors reported.
Any ideas on what I should try next? Considering that this db is not in production yet, I _do_ have 
the liberty to rebuild the database if necessary. Do you have any further recommendations?

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

Re: [GENERAL] Invalid headers and xlog flush failures

[Tom Lane]

Bricklen Anderson <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> But anyway, the evidence seems pretty clear that in fact end of WAL is
>>> in the 73 range, and so those page LSNs with 972 and 973 have to be
>>> bogus.  I'm back to thinking about dropped bits in RAM or on disk.

> memtest86+ ran for over 15 hours with no errors reported.
> e2fsck -c completed with no errors reported.

Hmm ... that's not proof your hardware is ok, but it at least puts the
ball back in play.

> Any ideas on what I should try next? Considering that this db is not
> in production yet, I _do_ have the liberty to rebuild the database if
> necessary. Do you have any further recommendations?

If the database isn't too large, I'd suggest saving aside a physical
copy (eg, cp or tar dump taken with postmaster stopped) for forensic
purposes, and then rebuilding so you can get on with your own work.

One bit of investigation that might be worth doing is to look at every
single 8K page in the database files and collect information about the
LSN fields, which are the first 8 bytes of each page.  In a non-broken
database all of these should be less than or equal to the current ending
WAL offset (which you can get with pg_controldata if the postmaster is
stopped).  We know there are at least two bad pages, but are there more?
Is there any pattern to the bad LSN values?  Also it would be useful to
look at each bad page in some detail to see if there's any evidence of
corruption extending beyond the LSN value.

regards, tom lane

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

[GENERAL] Hiding databases

[Martin Balint]

Hello,
is it somehow possible to hide databases from users?
Example:
I am user postgres1 and I created a database 'pg1db' with owner postgres1.
I log into phppgadmin as user postgres2, and I can see pg1db in the 
list. But I don't want to, I want pg1db to be private for user 
postgres1, so no one else is allowed to see it.
Is it possible?

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

[GENERAL] Upgrade from 7.4 -> 8.0.1 - problem with dump/restore

[Karl Denninger]

Hi folks;

Trying to move from 7.4.1 to 8.0.1

All goes well until I try to reload after installation.  

Dump was done with the 8.0.1 pg_dumpall program

On restore, I get thousands of errors on the console, and of course the
data doesn't end up back in the system.

The only two "non-standard" things about my 7.4.1 DBMS is that I do have
significant amonuts of binary data stored in the dbms itself, and in
addition I have "tsearch" loaded.

Any ideas?

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



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

Re: [GENERAL] Hiding databases

[Alvaro Herrera]

On Thu, Feb 03, 2005 at 07:54:22PM +0100, Martin Balint wrote:

> is it somehow possible to hide databases from users?

You can of course hide the database contents, through the HBA mechanism
(pg_hba.conf).  You can't really hide the fact that the database exists.

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"No single strategy is always right (Unless the boss says so)"
  (Larry Wall)

---(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] Hiding databases

[Michael Fuhr]

On Thu, Feb 03, 2005 at 07:54:22PM +0100, Martin Balint wrote:

> is it somehow possible to hide databases from users?

To what end?  Are you trying to keep users out of certain databases?
If so, then you can configure pg_hba.conf accordingly.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(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] Upgrade from 7.4 -> 8.0.1 - problem with dump/restore

[Michael Fuhr]

On Thu, Feb 03, 2005 at 01:03:57PM -0600, Karl Denninger wrote:

> Trying to move from 7.4.1 to 8.0.1
> 
> All goes well until I try to reload after installation.  
> 
> Dump was done with the 8.0.1 pg_dumpall program
> 
> On restore, I get thousands of errors on the console, and of course the
> data doesn't end up back in the system.

Please show the commands you ran and examples of some of the errors --
without knowing more we have no way of figuring out what's wrong.

> The only two "non-standard" things about my 7.4.1 DBMS is that I do have
> significant amonuts of binary data stored in the dbms itself, and in
> addition I have "tsearch" loaded.

Are you using tsearch, or tsearch2?  README.tsearch says that as
of 7.4, tsearch is deprecated in favor of tsearch2.  Did you install
the 8.0.1 version of whatever module you're using?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] Upgrade from 7.4 -> 8.0.1 - problem with dump/restore

[Karl Denninger]

On Thu, Feb 03, 2005 at 01:03:57PM -0600, Karl Denninger wrote:
> Hi folks;
> 
> Trying to move from 7.4.1 to 8.0.1
> 
> All goes well until I try to reload after installation.  
> 
> Dump was done with the 8.0.1 pg_dumpall program
> 
> On restore, I get thousands of errors on the console, and of course the
> data doesn't end up back in the system.
> 
> The only two "non-standard" things about my 7.4.1 DBMS is that I do have
> significant amonuts of binary data stored in the dbms itself, and in
> addition I have "tsearch" loaded.
> 
> Any ideas?

PS: The binary data is in a "BYTEA" field, not a BLOB.  It looks ok in the
dump file.

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



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

[GENERAL] Indexed leading substring searches - worked, now doesn't

[Wes]

I know my leading substring searches used to be done via indexes.  We
specifically tested that.  Since the last time I tested it, the database has
probably been reloaded to fix a corruption problem.  Now all I can get is
sequential leading substring searches.  In the examples below, the database
was vacuumed last night.

The database is very large (currently about 100 GB, and will be 100's of
gigabytes), so performance is important.  This particular table in the
example has only about 2.8 million rows.

PostgreSQL version is 7.4.5.  Prior to the reload, it was 7.4.1.

The locale is showing up as en_US.iso885915.  As far as I know, it was
always this (default RedHat install), so I don't understand why it worked
before.  Did something change between 7.4.1 and 7.4.5?  I supposed it's
possible that I specified locale=C on the original database and don't
remember that...

I'm not going to have to "initdb --locale=C" and am I?  I looked at index
classes, and that doesn't appear to be something I want to do, due to
performance.  What kind of performance hit do you actually take by using an
index class?

Wes


Pg_controldata shows:

Maximum length of locale name:128
LC_COLLATE:   en_US.iso885915
LC_CTYPE: en_US.iso885915


narc=> \d addresses
 Table "public.addresses"
   Column|  Type  | Modifiers
-++---
 address_key | numeric(12,0)  | not null
 address | character varying(255) | not null
Indexes:
"addresses_pkey" primary key, btree (address_key)
"addresses_i_address" btree (address)

narc=> select count(*) from addresses;
  count  
-
 2829640
(1 row)

narc=> explain select * from addresses where address = 'blah';
  QUERY PLAN

--
 Index Scan using addresses_i_address on addresses  (cost=0.00..2.81 rows=1
width=40)
   Index Cond: ((address)::text = 'blah'::text)
(2 rows)

narc=> explain select * from addresses where address like 'blah%';
  QUERY PLAN
--
 Seq Scan on addresses  (cost=0.00..61244.68 rows=2 width=40)
   Filter: ((address)::text ~~ 'blah%'::text)
(2 rows)

narc=> explain analyze select * from addresses where address like 'blah%';
  QUERY PLAN

--
 Seq Scan on addresses  (cost=0.00..61244.68 rows=2 width=40) (actual
time=1445.386..8913.435 rows=6 loops=1)
   Filter: ((address)::text ~~ 'blah%'::text)
 Total runtime: 8913.504 ms
(3 rows)


Something else doesn't make sense..  I did the same query a few minutes ago
and the cost was totally different:


narc=> explain select * from addresses where address like 'blah%';
QUERY PLAN
--
 Seq Scan on addresses  (cost=1.00..100061244.67 rows=2 width=40)
   Filter: ((address)::text ~~ 'blah%'::text)
(2 rows)




---(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] Hiding databases

[Martin Balint]

So they will see each others database name? I really don't want this
To what end?  Are you trying to keep users out of certain databases?
If so, then you can configure pg_hba.conf accordingly.
Yes, I don't even want them to see the database name.

Alvaro Herrera napsal(a):
On Thu, Feb 03, 2005 at 07:54:22PM +0100, Martin Balint wrote:
 

is it somehow possible to hide databases from users?
   

You can of course hide the database contents, through the HBA mechanism
(pg_hba.conf).  You can't really hide the fact that the database exists.
 


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

[GENERAL] Select from function for part of column results

[Rory Campbell-Lange]

I've written a function to provide information relating to each row in
an inbox. I'd like to do a query returning some information from each
row of the inbox + some columns from the function pertinent to each row
returned.

I've tried the following on 7.4.6:

t4=> select * from function_message_context(inbox.rowid), inbox; 
ERROR:  function expression in FROM may not refer to other 
relations of same query level

Is there a way I can get the function to provide some columns in the
query?

Thanks
Rory

-- 
Rory Campbell-Lange 
<[EMAIL PROTECTED]>


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

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

Re: [GENERAL] Select from function for part of column results

[Oisin Glynn]

I have something simlar working on windows in V 8.0  
My select loks like:

SELECT *,function_message_context(inbox.rowid) from inbox; 

Hope this helps.

Oisin
- Original Message - 
From: "Rory Campbell-Lange" <[EMAIL PROTECTED]>
To: "Postgresql General List" 
Sent: Thursday, February 03, 2005 15:04
Subject: [GENERAL] Select from function for part of column results


> I've written a function to provide information relating to each row in
> an inbox. I'd like to do a query returning some information from each
> row of the inbox + some columns from the function pertinent to each row
> returned.
> 
> I've tried the following on 7.4.6:
> 
> t4=> select * from function_message_context(inbox.rowid), inbox; 
> ERROR:  function expression in FROM may not refer to other 
> relations of same query level
> 
> Is there a way I can get the function to provide some columns in the
> query?
> 
> Thanks
> Rory
> 
> -- 
> Rory Campbell-Lange 
> <[EMAIL PROTECTED]>
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq
> 
> 


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

Re: [GENERAL] Indexed leading substring searches - worked, now doesn't

[Tom Lane]

Wes <[EMAIL PROTECTED]> writes:
> The locale is showing up as en_US.iso885915.  As far as I know, it was
> always this (default RedHat install), so I don't understand why it worked
> before.  Did something change between 7.4.1 and 7.4.5?  I supposed it's
> possible that I specified locale=C on the original database and don't
> remember that...

You got it in one ...

> I'm not going to have to "initdb --locale=C" and am I?

You could use the alternative operator classes for pattern searches, but
if you want the same indexes to also serve for normal text sorting,
C locale is the better bet.

regards, tom lane

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

[GENERAL] Eeek! Major problem after reload with tsearch2

[Karl Denninger]

Ok, I found out what was going on with the tsearch2 module and reloading
after an upgrade to 8.0.1 from 7.4.1

The data now loads cleanly, and selects are fine.

HOWEVER, when I attempt an update, I issue the following SQL command (this
table has a tsearch2 vector in it:)

update post set invisible='0' where ordinal='2843'

And get back:

ERROR:  cache lookup failed for function 36476 CONTEXT:  SQL statement
"select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map,
public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias =
map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by
lt.tokid desc;" 

Ai!  

A reindex did nothing.

What did I miss?  Looks like there's something missing, but what?!

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



---(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] Eeek! Major problem after reload with tsearch2

[Karl Denninger]

If I remove the full-text indexing from the table involved (remove the
indices and triggers) then the system is fine.

Somehow, it appears that there's some kind of internal consistency problem
with the tsearch2 package.

Attempts to recreate the indices (after dropping them) fail with the same
error; it looks like something is badly mangled internally in the tsearch2
module... even though it DOES appear that it loaded properly.

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind

On Thu, Feb 03, 2005 at 02:37:04PM -0600, Karl Denninger wrote:
> Ok, I found out what was going on with the tsearch2 module and reloading
> after an upgrade to 8.0.1 from 7.4.1
> 
> The data now loads cleanly, and selects are fine.
> 
> HOWEVER, when I attempt an update, I issue the following SQL command (this
> table has a tsearch2 vector in it:)
> 
> update post set invisible='0' where ordinal='2843'
> 
> And get back:
> 
> ERROR:  cache lookup failed for function 36476 CONTEXT:  SQL statement
> "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map,
> public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias =
> map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by
> lt.tokid desc;" 
> 
> Ai!  
> 
> A reindex did nothing.
> 
> What did I miss?  Looks like there's something missing, but what?!
> 
> --
> -- 
> Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
> http://www.denninger.net  My home on the net - links to everything I do!
> http://scubaforum.org Your UNCENSORED place to talk about DIVING!
> http://www.spamcuda.net   SPAM FREE mailboxes - FREE FOR A 
> LIMITED TIME!
> http://genesis3.blogspot.com  Musings Of A Sentient Mind
> 
> 
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
> 
> 
> %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok



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

Re: [GENERAL] Eeek! Major problem after reload with tsearch2

[Tom Lane]

Karl Denninger <[EMAIL PROTECTED]> writes:
> Ok, I found out what was going on with the tsearch2 module and reloading
> after an upgrade to 8.0.1 from 7.4.1

> ERROR:  cache lookup failed for function 36476

I think that tsearch2's control tables contain function OIDs, which
means that you can't just dump them out of one installation and load
into a new one ... you have to regenerate the table contents by running
the tsearch2.sql script.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Select from function for part of column results

[Rory Campbell-Lange]

Hi Oisin

On 03/02/05, Oisin Glynn ([EMAIL PROTECTED]) wrote:
> I have something simlar working on windows in V 8.0  
> My select loks like:
> 
> SELECT *,function_message_context(inbox.rowid) from inbox; 

This works great for a function returning a single column. I'm not sure
how to do it with a function returning more than one column.

st4=> select *, fn_message_context(n_id) from inbox;
ERROR:  cannot display a value of type record

Rory

> - Original Message - 
> From: "Rory Campbell-Lange" <[EMAIL PROTECTED]>
> To: "Postgresql General List" 
> Sent: Thursday, February 03, 2005 15:04
> Subject: [GENERAL] Select from function for part of column results
> 
> > I've written a function to provide information relating to each row in
> > an inbox. I'd like to do a query returning some information from each
> > row of the inbox + some columns from the function pertinent to each row
> > returned.
> > 
> > I've tried the following on 7.4.6:
> > 
> > t4=> select * from function_message_context(inbox.rowid), inbox; 
> > ERROR:  function expression in FROM may not refer to other 
> > relations of same query level
> > 
> > Is there a way I can get the function to provide some columns in the
> > query?

-- 
Rory Campbell-Lange 
<[EMAIL PROTECTED]>


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

   http://archives.postgresql.org

Re: [GENERAL] Eeek! Major problem after reload with tsearch2

[Oleg Bartunov]

This is know issue with OIDS.
You,probably, needed to apply regprocedure_7.4.patch.gz
patch from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
Oleg
On Thu, 3 Feb 2005, Karl Denninger wrote:
Ok, I found out what was going on with the tsearch2 module and reloading
after an upgrade to 8.0.1 from 7.4.1
The data now loads cleanly, and selects are fine.
HOWEVER, when I attempt an update, I issue the following SQL command (this
table has a tsearch2 vector in it:)
update post set invisible='0' where ordinal='2843'
And get back:
ERROR:  cache lookup failed for function 36476 CONTEXT:  SQL statement
"select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map,
public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias =
map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by
lt.tokid desc;"
Ai!
A reindex did nothing.
What did I miss?  Looks like there's something missing, but what?!
--
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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] Eeek! Major problem after reload with tsearch2

[Karl Denninger]

The patch is in the 8.0.1 version of Tsearch2 already.

The problem is that I have a dump from a 7.4.1 database taken with the
8.0.1 pg_dumpall that I need to be able to get back online on 8.0.x.

Is the only option to find all the functions in the tsearch.sql file, drop
them by hand, remove all the tsearch2 index fields, then reload
tsearch2.sql and re-create the indices?

That's not impossible, but a shizload of work, as there's no good way that
I can see to drop all the tsearch2 functions in a single step (e.g. I'd
have to go through the tsearch2.sql file individually, find all the
entries, drop them, etc.)

Another possibility

Does a pg_dumpall dump functions as well?  It appears not from the
documentation - so if I drop the columns and then dump the database, I
should have a "clean" dump without the OID stuff in it.

If I then re-init and reload the data, I should then be able to do so
without the tsearch2.sql stuff.  I can then reload the tsearch2.sql
functions and re-create the indices.

Sound plausible?

-
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind

On Fri, Feb 04, 2005 at 12:23:02AM +0300, Oleg Bartunov wrote:
> This is know issue with OIDS.
> You,probably, needed to apply regprocedure_7.4.patch.gz
> patch from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
> 
>   Oleg
> On Thu, 3 Feb 2005, Karl Denninger wrote:
> 
> > Ok, I found out what was going on with the tsearch2 module and reloading
> > after an upgrade to 8.0.1 from 7.4.1
> >
> > The data now loads cleanly, and selects are fine.
> >
> > HOWEVER, when I attempt an update, I issue the following SQL command (this
> > table has a tsearch2 vector in it:)
> >
> > update post set invisible='0' where ordinal='2843'
> >
> > And get back:
> >
> > ERROR:  cache lookup failed for function 36476 CONTEXT:  SQL statement
> > "select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map,
> > public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias =
> > map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by
> > lt.tokid desc;"
> >
> > Ai!
> >
> > A reindex did nothing.
> >
> > What did I miss?  Looks like there's something missing, but what?!
> >
> > --
> >
> 
>   Regards,
>   Oleg
> _
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
> 
> ---(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
> 
> 
> %SPAMBLOCK-SYS: Matched [EMAIL PROTECTED], message ok



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

Re: [GENERAL] dumping and restoring user information.

[Pritesh Shah]

Thanks, it worked.


On Tue, 1 Feb 2005 00:30:31 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Tue, Feb 01, 2005 at 12:11:06AM -0700, Pritesh Shah wrote:
> >
> > Is there any way of copying/dumping/restoring the user and group
> > information and permissions from the old machine to the new one so
> > that i do not have to do a createuser everytime??
> 
> You can dump just users and groups with "pg_dumpall --globals-only"
> (or -g).
> 
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>

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

Re: [GENERAL] pgpool 2.5b2 released

[Jan Wieck]

On 2/2/2005 11:57 AM, Bruce Momjian wrote:
Tatsuo Ishii wrote:
Pgpool 2.5b2 supports "master slave mode" which can cope with
master/slave replication softwares such as Slony-I. In this mode
pgpool sends non SELECT queries to master only. SELECTs are load
balanced by pgpool.
Other features of 2.5b2 include:
- ability to add timestamp to each log entry
- control to whether cache connection info or not
pgpool 2.5b2 is available at:
http://pgfoundry.org/projects/pgpool/
Wow, that is great!  I know Jan was waiting for this.
This is incredible! I definitely did wait for this and some people at 
the Solutions Linux here in Paris did ask about it. Too bad that I 
didn't read this earlier, it would have made their day.

Thank you Tatsuo. I will check this out when I'm back home.
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(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] Indexed leading substring searches - worked, now

[Wes]

On 2/3/05 2:29 PM, "Tom Lane" <[EMAIL PROTECTED]> wrote:

> You could use the alternative operator classes for pattern searches, but
> if you want the same indexes to also serve for normal text sorting,
> C locale is the better bet.

As an interim solution until I can reload the database (takes a weekend),
can I define two indexes on the same field, one using operator classes and
one not (and have them automatically used as appropriate)?

Because of the time involved, I'm trying to hold off on another reload until
we upgrade to 8.x.

Wes



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

[GENERAL] errors while restoring data.

[Pritesh Shah]

hi,

I'm trying to restore some databases from the plain text format dumps.
To restore I'm using:

psql -e -f nmayande.sql template1 

I captured a small part while restoring the database which has an error, 

CREATE OPERATOR >= (
PROCEDURE = daterange_greater_than_equals,
LEFTARG = daterange,
RIGHTARG = daterange,
NEGATOR = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);
psql:/var/tmp/backup/pgsql/20050202/nmayande.sql:1272: ERROR: 
function daterange_greater_than_equals(daterange, daterange) does not
exist


These are some of the lines from the *.sql plain text dump file:

..
..

CREATE OPERATOR >= (
PROCEDURE = daterange_greater_than_equals,
LEFTARG = daterange,
RIGHTARG = daterange,
NEGATOR = <,
RESTRICT = scalargtsel,
JOIN = scalargtjoinsel
);

...
...

CREATE FUNCTION daterange_greater_than_equals(daterange,
daterange)RETURNS boolean
 AS '$libdir/daterange.so', 'daterange_greater_than_equals'
LANGUAGE c;

...
...


I know that the CREATE FUNCTION part should come before using it in
CREATE OPERATOR. If it was one or two databases i would have edited
the plain text dump file by hand appropriately. But the problem is
this error spreads to lot of other database's plain text dump files
that i'm trying to restore. Please help me out so that i do not have
to do all this editing manually.

Thanks,
Pritesh

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

Re: [GENERAL] Indexed leading substring searches - worked, now doesn't

[Tom Lane]

Wes <[EMAIL PROTECTED]> writes:
> As an interim solution until I can reload the database (takes a weekend),
> can I define two indexes on the same field, one using operator classes and
> one not (and have them automatically used as appropriate)?

Certainly.

regards, tom lane

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

Re: [GENERAL] errors while restoring data.

[Tom Lane]

Pritesh Shah <[EMAIL PROTECTED]> writes:
> I know that the CREATE FUNCTION part should come before using it in
> CREATE OPERATOR. If it was one or two databases i would have edited
> the plain text dump file by hand appropriately.

If you can use the pg_dump from the 8.0 release, it should get this
right.  Prior versions don't do real dependency analysis but rely on
dumping in OID order, which is not a very reliable guide.

regards, tom lane

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

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

[GENERAL] Lost rows/data corruption?

[Andrew Hall]

Hello,
We have a long running DB application using PG7.4.6. We do a VACUUM FULL
every night and a normal 'maintenance' VACUUM every hour. We do nothing with
any indexes. Every now and then we get errors from the database whereby an
update will fail on a table saying that there is duplicate violation on a
primary keyed row. Theoretically this is impossible as the constraint should
prevent any duplication, ever. When the next vacuum is run, we see an error
saying that there are more rows than the database can account for (or words
to that effect) and we should either REINDEX or TRUNCATE the table.
Firstly, how and why could this be happening, and above all how, do we
prevent it.
Thanks and regards,
Andrew. 

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Lost rows/data corruption?

[Tom Lane]

"Andrew Hall" <[EMAIL PROTECTED]> writes:
> We have a long running DB application using PG7.4.6. We do a VACUUM FULL
> every night and a normal 'maintenance' VACUUM every hour. We do nothing with
> any indexes. Every now and then we get errors from the database whereby an
> update will fail on a table saying that there is duplicate violation on a
> primary keyed row. Theoretically this is impossible as the constraint should
> prevent any duplication, ever. When the next vacuum is run, we see an error
> saying that there are more rows than the database can account for (or words
> to that effect) and we should either REINDEX or TRUNCATE the table.

Could we see the exact log trace, rather than handwaving?  Also the
schemas of the table(s)/index(es) involved might be useful.

regards, tom lane

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

   http://archives.postgresql.org

[GENERAL] Start problem on OSX

[Philippe Schmid]

Dear list,
I have a problem starting PG 8.0 on a OSX machine, getting this
	FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission 
denied

I checked the privs on /tmp
ls -ld /tmp
lrwxr-xr-x  1 root  admin  11  4 Oct  2003 /tmp -> private/tmp
ls -ld /private/tmp
drwxrwxrwt  21 root  wheel  714  4 Feb 00:09 /private/tmp
and of course, PG 8.0 starts on another machine, with the same OS 
version 10.3.7, PG version 8.0, configuration.
Nothing found in system, console logs.

Did someone already experienced this ?
Many thanks for some ideas, I can't see what's wrong... (ok, its 
late...)

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

[GENERAL] Applications that leak connections

[Paul Tillotson]

Does anyone have any useful info about dealing with applications which 
"leak" connections, eventually causing "connection limit exceeded for 
non superusers?"

Obviously fixing the app is the best choice, but I have a feeling that 
this is going to recur again and again since the app is poorly 
maintained but still under active development (code base too large for 
the developer assigned to it).  The developer is likely to accidentally 
cause connection leaks in the future.

This problem is also very easy to cause because doing this:
c = sqlconnect("database")
c = sqlconnect("database")
Causes TWO connections to be made, and the first connection made is now 
(as far as I can see) impossible to close without closing the 
application.  The language (Visual Foxpro) does not garbage collect 
connections and so any connection whose handle is lost still takes up 
one of the slots.

On the server side, I can detect this condition using ps -A or lsof; but 
I have not thought of a good solution to use when this is noticed.  One 
possibility is to just shut down the database and restart it when this 
happens.  I did some research into finding out if it was possible to 
break a specific TCP connection (this is linux 2.6 kernel--platform 
specific tools/commands ok).  For example, could I use hping to break 
the connections?

Does anyone know a safe way to shutdown just one backend (if it is idle)?
Another possibility that I can think of is per ip address connection 
limits.  How hard would that be?

Paul Tillotson

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

[GENERAL] Backing up and restoring a database with the SELinux pg_user problem.

[Joseph Kiniry]

Hello everyone,
I have a Postgresql 7.4 database that was created on a Fedora 3 box 
*prior* to turning off enforcing mode in SELinux, but then has since 
been used for some time in permissive mode. Since everything seemed to 
be working, I never knew there was a problem until I attempted to set 
up backups a few days later and found that the pg_user table/view did 
not exist. Now, I am seemingly unable to backup any database or table, 
since any use of pg_dump fails due to the missing pg_user table.

Does anyone have any suggestions on this problem? How can I recreate 
pg_user? I would expect this should be possible given there should only 
be the default postgres super user and my gforge user installed, and 
the latter works fine, as this is a running GForge db that I wish to 
shutdown over the weekend and repair.

I have read all prior discussions on the topic on various 
postgresql.org mailing lists and none of their suggestions helped me, 
nor seemed to help any previously afflicted user. E.g., these threads
 http://archives.postgresql.org/pgsql-general/2004-08/msg01286.php
 http://archives.postgresql.org/pgsql-sql/2005-01/msg00167.php

I have also read the Fedora 3 bug report on this issue.
 https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=142607
Thanks very much,
Joe Kiniry
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[GENERAL] Is there a peer-to-peer server solution with PG?

[Mike Nolan]

I have need to set up a 2nd database server for a client in their new
offices in another state this month.  We will be shutting down the old 
offices later this year but we really don't want to have 2-3 days of
downtime while we physically transfer equipment 800 miles.  

We should have decent data connections between the two offices starting
next week, but I was wonding if there is a good peer-to-peer option for 
PostgreSQL at this time.

As I understand Slony, it is master-slave only.
--
Mike Nolan

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

Re: [GENERAL] Start problem on OSX

[Jonel Rienton]

hi, how did you install PostgreSQL? from source or from dmg? which user are 
you using to start the instance?

On Fri, 4 Feb 2005 00:22:47 +0100, Philippe Schmid wrote
> Dear list,
> 
> I have a problem starting PG 8.0 on a OSX machine, getting this
>   FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": 
> Permission denied
> 
> I checked the privs on /tmp
> ls -ld /tmp
>   lrwxr-xr-x  1 root  admin  11  4 Oct  2003 /tmp -> private/tmp
> 
> ls -ld /private/tmp
>   drwxrwxrwt  21 root  wheel  714  4 Feb 00:09 /private/tmp
> 
> and of course, PG 8.0 starts on another machine, with the same OS 
> version 10.3.7, PG version 8.0, configuration.
> Nothing found in system, console logs.
> 
> Did someone already experienced this ?
> Many thanks for some ideas, I can't see what's wrong... (ok, its 
> late...)
> 
> Philippe
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


--
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate


---(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] Start problem on OSX

[Tom Lane]

Philippe Schmid <[EMAIL PROTECTED]> writes:
> I have a problem starting PG 8.0 on a OSX machine, getting this
>   FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": Permission 
> denied

Perhaps that file already exists?

regards, tom lane

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

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

Re: [GENERAL] Backing up and restoring a database with the SELinux pg_user problem.

[Tom Lane]

Joseph Kiniry <[EMAIL PROTECTED]> writes:
> Does anyone have any suggestions on this problem? How can I recreate 
> pg_user?

Sure, just run the CREATE VIEW command executed by initdb; it's in the
initdb shell script.  Note that all the objects created that way (with
"postgres <

Re: [GENERAL] Is there a peer-to-peer server solution with PG?

[David Fetter]

On Thu, Feb 03, 2005 at 06:25:50PM -0600, Mike Nolan wrote:
> I have need to set up a 2nd database server for a client in their
> new offices in another state this month.  We will be shutting down
> the old offices later this year but we really don't want to have 2-3
> days of downtime while we physically transfer equipment 800 miles.  
> 
> We should have decent data connections between the two offices
> starting next week, but I was wonding if there is a good
> peer-to-peer option for PostgreSQL at this time.
> 
> As I understand Slony, it is master-slave only.

Slony-1 is perfectly capable of replicating to a slave database, then
letting you decide to promote it to master, which is just what you'd
need.  Why are you asking about multi-master?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

[GENERAL] Problem resolved (tsearch2 inhibiting migration)

[Karl Denninger]

Got it fixed.

As it happens, there's an "untsearch2.sql" script in the contrib directory.
Removing the transaction block around it allowed it to kill all the "dregs"
from the 7.4.x database entries (on 8.0.1) and a subsequent "tsearch2.sql"
was then able to rebuild them.

I had to reinsert the columns and indices, but that's not a big deal.

All fixed...  thanks to the pointer to the OID issue, that got me on the
right track.

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



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

Re: [GENERAL] Is there a peer-to-peer server solution with PG?

[Mike Nolan]

> Slony-1 is perfectly capable of replicating to a slave database, then
> letting you decide to promote it to master, which is just what you'd
> need.  Why are you asking about multi-master?

I am concerned that if I have to support the traffic to keep the slave 
unit in sync PLUS support general database use from the 'slaved' office
to the master one, on the same comm line, I might start running into 
congestion issues. 

We will have people actively working the database in both office for 
a period of several weeks to several months, depending on how the final
transfer plan unfolds.  

Master/Slave is probably an acceptable solution, I was just wondering if 
there was a multi-master one available yet.
--
Mike Nolan

---(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] Is there a peer-to-peer server solution with PG?

[David Fetter]

On Thu, Feb 03, 2005 at 07:03:36PM -0600, Mike Nolan wrote:
> > Slony-1 is perfectly capable of replicating to a slave database,
> > then letting you decide to promote it to master, which is just
> > what you'd need.  Why are you asking about multi-master?
> 
> I am concerned that if I have to support the traffic to keep the
> slave unit in sync PLUS support general database use from the
> 'slaved' office to the master one, on the same comm line, I might
> start running into congestion issues. 

Slony-1 does its level best to ship transactions in a compact way.
Any write operations are done as the net result of the write
transaction, not necessarily all the steps in between.  IOW, don't
worry too much :)

> We will have people actively working the database in both office for
> a period of several weeks to several months, depending on how the
> final transfer plan unfolds.  

Sounds like a fit for Slony-1.  Just make sure that nobody tries to
write to a slave, as such writes will fail.

> Master/Slave is probably an acceptable solution, I was just
> wondering if there was a multi-master one available yet.

Not really.  If you *must* have multi-master, you probably have to get
Oracle or DB2 and pay /mucho dinero/.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

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

   http://archives.postgresql.org

[GENERAL] How to delete duplicate rows?

[Clodoaldo Pinto]

This one must be obvious for most here.

I have a 170 million rows table from which I want to eliminate
duplicate "would be" keys and leave only uniques.

I found a query in http://www.jlcomp.demon.co.uk/faq/duplicates.html
for the oracle database but can't figure out how to refer to the row
id in postgresql:

delete from test where rowid not in 
(select min(rowid) from test group by a,b);

How to refer to the row id? Any better way to do it?

Regards, Clodoaldo Pinto

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

Re: [GENERAL] Applications that leak connections

[Jonel Rienton]

you are perfectly right, fixing the source of the problem is the best way to 
fix this. i always make it a habit to close my connections as soon as i'm 
done with it. if foxpro won't do it for you, you have to manage your own 
resources, always a good practice for unmanaged applications.

i suggest you parse your code for all lines that opens the connection and 
close it where it's not needed anymore.

good luck.

regards,


On Thu, 03 Feb 2005 18:27:46 -0500, Paul Tillotson wrote
> Does anyone have any useful info about dealing with applications 
> which "leak" connections, eventually causing "connection limit 
> exceeded for non superusers?"
> 
> Obviously fixing the app is the best choice, but I have a feeling 
> that this is going to recur again and again since the app is poorly 
> maintained but still under active development (code base too large 
> for the developer assigned to it).  The developer is likely to 
> accidentally cause connection leaks in the future.
> 
> This problem is also very easy to cause because doing this:
> 
> c = sqlconnect("database")
> c = sqlconnect("database")
> 
> Causes TWO connections to be made, and the first connection made is 
> now 
> (as far as I can see) impossible to close without closing the 
> application.  The language (Visual Foxpro) does not garbage collect 
> connections and so any connection whose handle is lost still takes 
> up one of the slots.
> 
> On the server side, I can detect this condition using ps -A or lsof; 
> but I have not thought of a good solution to use when this is 
> noticed.  One possibility is to just shut down the database and 
> restart it when this happens.  I did some research into finding out 
> if it was possible to break a specific TCP connection (this is linux 
> 2.6 kernel--platform specific tools/commands ok).  For example,
>  could I use hping to break the connections?
> 
> Does anyone know a safe way to shutdown just one backend (if it is 
> idle)?
> 
> Another possibility that I can think of is per ip address connection 
> limits.  How hard would that be?
> 
> Paul Tillotson
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> 
> (send "unregister YourEmailAddressHere" to 
> [EMAIL PROTECTED])


--
Jonel Rienton
http://blogs.road14.com
Software Developer, *nix Advocate


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

   http://archives.postgresql.org

[GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters

[Postgre . News . Firma]

Hi,

How do I call a StoredProcdure, written in PlPqSql 
which returns a set of records (or tableWhatever)
and wants parameters,
from ADO while using "adCmdStoredProc" ?

ERROR:  set-valued function called in context that cannot accept a set 


-- VERY LONG VERSION OF THIS QUESTION
-


When I use Microsoft SQL-Server I make a SP like

CREATE PROCEDURE _test AS
select * from evt_event
return 42
GO

I call it just via name.






When I use PostgreSql I make a SQL-SP like

CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
AS
$body$
select * from t1;
$body$
LANGUAGE 'sql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

I call it just via name too.



When I use PostgreSql I make a PLPG-SQL-SP like
CREATE OR REPLACE FUNCTION "public"."_test" () RETURNS SETOF "public"."t1"
AS
$body$
declare
   rs record;
begin
 for rs in select * from t1 loop
 return next rs;
 end loop;
 RAISE NOTICE '42';
 return;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

How do I call this one ? (using adCmdStoredProc)


In the SQL-Editor I may call both pg-SPs via "select * from _test()"


EMS PostgreSql Manager SQL-Editor:
VariantePG1: select * from _test() : ok
VariantePG2: select * from _test() : ok

Ado:
RECORDSET rs = adoRecordSet.open ,, adCmdStoredProc

VarianteMS : ok
VariantePG1: ok
VariantePG2: ERROR:  set-valued function called in context that cannot
accept a set 


Help,
Andreas






---(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] Problem resolved (tsearch2 inhibiting migration)

[Michael Fuhr]

On Thu, Feb 03, 2005 at 06:44:55PM -0600, Karl Denninger wrote:
>
> As it happens, there's an "untsearch2.sql" script in the contrib directory.

That reminds me: it would be useful if all contributed modules had
an unmodule.sql file.  That would simplify reloading the module if
the definitions changed, as recently happened when STRICT was added
to functions in chkpass and a few other modules; it would also make
it easy to remove the module from a particular database if it were
no longer needed.  Or is there already a way of doing this that
doesn't require you to figure out the DROP statements yourself?
Running "gmake uninstall" in the module's source directory only
removes the .so, .sql, and other installed files -- it doesn't DROP
any objects that have been created in databases.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

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

Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)

[Karl Denninger]

On Thu, Feb 03, 2005 at 06:59:55PM -0700, Michael Fuhr wrote:
> On Thu, Feb 03, 2005 at 06:44:55PM -0600, Karl Denninger wrote:
> >
> > As it happens, there's an "untsearch2.sql" script in the contrib directory.
> 
> That reminds me: it would be useful if all contributed modules had
> an unmodule.sql file.  That would simplify reloading the module if
> the definitions changed, as recently happened when STRICT was added
> to functions in chkpass and a few other modules; it would also make
> it easy to remove the module from a particular database if it were
> no longer needed.  Or is there already a way of doing this that
> doesn't require you to figure out the DROP statements yourself?
> Running "gmake uninstall" in the module's source directory only
> removes the .so, .sql, and other installed files -- it doesn't DROP
> any objects that have been created in databases.

No, there is no good way to do that.  What's worse, is that if it goes into
the template directory, it "pollutes" every database on the system from
that point forward, so you have to back it out of each in turn 

I agree with this - what would be even better would be a way to create
'subclasses' for things like this, which could then be 'included' easily.

I don't know if the latter is possible - I haven't seen an obvious way to
do that, but there may be a way.

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



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

Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)

[Tom Lane]

Karl Denninger <[EMAIL PROTECTED]> writes:
> I agree with this - what would be even better would be a way to create
> 'subclasses' for things like this, which could then be 'included' easily.

We could decree that a contrib module's script should create a schema
and shove everything it makes into that schema.  Then "DROP SCHEMA CASCADE"
is all you need to get rid of it.  However, you'd probably end up having
to add this schema to your search path to use the module conveniently.

regards, tom lane

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

Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)

[Karl Denninger]

On Thu, Feb 03, 2005 at 10:20:47PM -0500, Tom Lane wrote:
> Karl Denninger <[EMAIL PROTECTED]> writes:
> > I agree with this - what would be even better would be a way to create
> > 'subclasses' for things like this, which could then be 'included' easily.
> 
> We could decree that a contrib module's script should create a schema
> and shove everything it makes into that schema.  Then "DROP SCHEMA CASCADE"
> is all you need to get rid of it.  However, you'd probably end up having
> to add this schema to your search path to use the module conveniently.
> 
>   regards, tom lane

I would prefer that vastly over what I had to deal with this time.  Until I
discovered the "untsearch2.sql" script I was seriously considering the
trouble of backing this out  either in a dump or in the online 
database.

Either would have been a stupendous amount of work.

Please consider this change in approach - someone else has to have been bit
in the butt by this one other than me by now.

--
-- 
Karl Denninger ([EMAIL PROTECTED]) Internet Consultant & Kids Rights Activist
http://www.denninger.netMy home on the net - links to everything I do!
http://scubaforum.org   Your UNCENSORED place to talk about DIVING!
http://www.spamcuda.net SPAM FREE mailboxes - FREE FOR A LIMITED TIME!
http://genesis3.blogspot.comMusings Of A Sentient Mind



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

[GENERAL] Postgresql 8.0 beta 5 setup in windowsxp

[Art Fore]

Have a winXP machine with the above program installed, however, the 
service will not start, even when logged in as administrator.

Need a step by step setup procedure for WINDOWS. Is sthere one available 
or is there anything available on setup after install? All I have found 
is for lilnux-unix-bsd or windows with cygn, not the native install for 
windows.

Art
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] Eeek! Major problem after reload with tsearch2

[Oleg Bartunov]

On Thu, 3 Feb 2005, Karl Denninger wrote:
The patch is in the 8.0.1 version of Tsearch2 already.
The problem is that I have a dump from a 7.4.1 database taken with the
8.0.1 pg_dumpall that I need to be able to get back online on 8.0.x.

you had to apply patch to 7.4.1 db before dumping or use regprocedure_update.sql
to update your live 7.4.1 database ! pg_dump (pg_dumpall) has nothing with 
the problem. After applying patch and dumping db you have OID-free db and
should have no problem !

Is the only option to find all the functions in the tsearch.sql file, drop
them by hand, remove all the tsearch2 index fields, then reload
tsearch2.sql and re-create the indices?
That's not impossible, but a shizload of work, as there's no good way that
I can see to drop all the tsearch2 functions in a single step (e.g. I'd
have to go through the tsearch2.sql file individually, find all the
entries, drop them, etc.)
Another possibility
Does a pg_dumpall dump functions as well?  It appears not from the
documentation - so if I drop the columns and then dump the database, I
should have a "clean" dump without the OID stuff in it.
If I then re-init and reload the data, I should then be able to do so
without the tsearch2.sql stuff.  I can then reload the tsearch2.sql
functions and re-create the indices.
Sound plausible?
No, if you have OIDs in db pg_dump* will dump them. Try 
regprocedure_update.sql
and read 
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
If you have 7.4.1 db running you might go way described earlier.
-
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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] Problem resolved (tsearch2 inhibiting migration)

[Oleg Bartunov]

On Thu, 3 Feb 2005, Karl Denninger wrote:
On Thu, Feb 03, 2005 at 10:20:47PM -0500, Tom Lane wrote:
Karl Denninger <[EMAIL PROTECTED]> writes:
I agree with this - what would be even better would be a way to create
'subclasses' for things like this, which could then be 'included' easily.
We could decree that a contrib module's script should create a schema
and shove everything it makes into that schema.  Then "DROP SCHEMA CASCADE"
is all you need to get rid of it.  However, you'd probably end up having
to add this schema to your search path to use the module conveniently.
			regards, tom lane
I would prefer that vastly over what I had to deal with this time.  Until I
discovered the "untsearch2.sql" script I was seriously considering the
trouble of backing this out  either in a dump or in the online
database.
As I already pointed you should apply  regprocedure_update.sql to your
7.4.1 database before dumping to get OIDs free tsearch2 functions !
This is the way most people dump their db without any problem. Unfortunately,
this was not documented in 7.4  readme, but it does in current documentations,
web site and discussed in mailing list.
Either would have been a stupendous amount of work.
Please consider this change in approach - someone else has to have been bit
in the butt by this one other than me by now.
--
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(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] Postgresql 8.0 beta 5 setup in windowsxp

[Dann Corbit]

You cannot install the service as administrator, because of security
risks.

Try this thing:
http://pgfoundry.org/projects/pginstaller

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Art Fore
Sent: Thursday, February 03, 2005 9:20 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgresql 8.0 beta 5 setup in windowsxp

Have a winXP machine with the above program installed, however, the 
service will not start, even when logged in as administrator.

Need a step by step setup procedure for WINDOWS. Is sthere one available

or is there anything available on setup after install? All I have found 
is for lilnux-unix-bsd or windows with cygn, not the native install for 
windows.

Art

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

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

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

Re: [GENERAL] Is there a peer-to-peer server solution with PG?

[Christopher Browne]

Martha Stewart called it a Good Thing when [EMAIL PROTECTED] (Mike Nolan) wrote:
>> Slony-1 is perfectly capable of replicating to a slave database,
>> then letting you decide to promote it to master, which is just what
>> you'd need.  Why are you asking about multi-master?
>
> I am concerned that if I have to support the traffic to keep the
> slave unit in sync PLUS support general database use from the
> 'slaved' office to the master one, on the same comm line, I might
> start running into congestion issues.
>
> We will have people actively working the database in both office for
> a period of several weeks to several months, depending on how the
> final transfer plan unfolds.
>
> Master/Slave is probably an acceptable solution, I was just
> wondering if there was a multi-master one available yet.

There is an effort under way; in planning stages at this point.  Don't
expect that to be "productized" next month...

Let me wag a finger at one of your assumptions...

You should re-examine assumptions with great care if you start
imagining that you'll get more throughput out of a general purpose
"multimaster" system.  (Something designed specifically for your
application is quite another matter, particularly if your application
turns out to be, in some fashion "embarassingly parallelizable.")

Synchronization can't _conceivably_ come for free; it has _got_ to
have some cost in terms of decreasing overall performance.  

If you have so much update load that one server cannot accomodate that
load, then you should wonder why you'd expect that causing every one
of these updates to be applied to (say) 3 servers would "diminish"
this burden.

Each of the 3 servers may only have to take on 1/3 of the updates from
the outside, but they surely have to accomodate the other 2/3 as well.

This not to say that there can't be some benefits from multimaster
replication; that's why such projects are proceeding.

But it's NOT a panacea; it's NOT an easy "general purpose solution."

I was in a room with The Thinkers; I got the sense that the lights
dimmed for blocks around when they put their thinking caps on :-).  To
this group of Rather Smart Folk, perceiving the array of concurrency
and locking problems required great attention on their part.  'Easy'
is definitely not the right word...
-- 
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://linuxdatabases.info/info/slony.html
Rules of the Evil Overlord #31. "All naive, busty tavern wenches in my
realm  will be replaced  with surly,  world-weary waitresses  who will
provide no  unexpected reinforcement  and/or romantic subplot  for the
hero or his sidekick." 

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

   http://archives.postgresql.org

[GENERAL] REPLICATION Solution for WINDOWS OS

[Tope Akinniyi]

Hi,
 
Is there a replication solution for PostgreSQL?  I learnt Slony 1 is for Linux OS.  
 
Also, has anyone used the tablellog contrib in Windows environment before?
 
Best regards.
 
Tope.Jesus said, I am the way, the truth and the life.
		 ALL-NEW 
Yahoo! Messenger 
- all new features - even more fun! 
 

Re: [GENERAL] pgpool 2.5b2 released

[Joshua D. Drake]

pgpool 2.5 has the capabilty to perform periodical health checking to
PostgreSQL.
   

Since pgpool has this capability, how about including a hook that allows a
script to be run when pgpool detects a problem with the master?  That would
allow action to be taken to investigate further and, if required, switchover
or failover and promote the slave to master.
 

Because it is not the place of a connection management software
to failover. Failover should happen only if:
1. You have a specific set of criteria that is matched via a network/system
management software.
2. You do it yourself.
The last thing in the world you need is to fail over to a slave because
somebody accidently tripped over a network cord.
Sincerely,
Joshua D. Drake

Julian Scarfe

---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org
 


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


---(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] REPLICATION Solution for WINDOWS OS

[Joshua D. Drake]

Tope Akinniyi wrote:
Hi,
 
Is there a replication solution for PostgreSQL?  I learnt Slony 1 is 
for Linux OS. 
 
Also, has anyone used the tablellog contrib in Windows environment before?
Hello,
Mammoth Replicator is due to hit in two weeks for Win32. It can be found at:
http://www.commandprompt.com/
Sincerely,
Joshua D. Drake

 
Best regards.
 
Tope.

Jesus said, I am the way, the truth and the life.

*ALL-NEW Yahoo! Messenger* 
* 
- all new features - even more fun!** * 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL
begin:vcard
fn:Joshua Drake
n:Drake;Joshua
org:Command Prompt, Inc.
adr:;;PO Box 215 ;Cascade Locks;OR;97014;US
email;internet:[EMAIL PROTECTED]
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
x-mozilla-html:FALSE
url:http://www.commandprompt.com
version:2.1
end:vcard


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

[GENERAL] Lost rows / corrupt data?

[Andrew Hall]

Hello,
We have a long running DB application using PG7.4.6. We do a VACUUM FULL 
every night and a normal 'maintenance' VACUUM every hour. We do nothing with 
any indexes. Every now and then we get errors from the database whereby an 
update will fail on a table saying that there is duplicate violation on a 
primary keyed row. Theoretically this is impossible as the constraint should 
prevent any duplication, ever. When the next vacuum is run, we see an error 
saying that there are more rows than the database can account for (or words 
to that effect) and we should either REINDEX or TRUNCATE the table.

Firstly, how and why could this be happening, and above all how, do we 
prevent it.

Thanks and regards,
Andrew. 

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

Re: [GENERAL] [OT] PostgreSQL: bytea help needed.

[True211]

"Mike Cox" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Rob Kelk <[EMAIL PROTECTED]> writes:
>
> > On 03 Feb 2005 16:04:44 -0800, Mike Cox <[EMAIL PROTECTED]> wrote:
> >
> > >Rob Kelk <[EMAIL PROTECTED]> writes:
> > >
> > >> On 03 Feb 2005 13:34:40 -0800, Mike Cox <[EMAIL PROTECTED]>
wrote:
> > >>
> > >> >"True211" <[EMAIL PROTECTED]> writes:
> > >> >
> > >> >
> > >> >> >
> > >> >> > > This isn't really a Linux question, by the way...
> > >> >> >
> > >> >> > There are *no* postgresql usenet newsgroups.  Not a one.
> > >>
> > >> I see 26 postgresql Usenet newsgroups on the news.individual.net
Usenet
> > >> server.
> > >>
> > >
> > >Ok genuis, why don't you try to post to one of those 26 newsgroups on
> > >individual.net and see what happens.
> >
> > I don't care about the topic; thus, I have nothing relevant to post to
> > them.
> >
> > > Get back to me with an apology
> > >when you have done so.
> >
> > Are you implying that the groups don't exist and I'm lying to you?
> >
> > Or are you having problems with the decades-old concept of a "gated
> > newsgroup"?  If that's the case, then perhaps you owe me an apology
> > after you've done some research on the topic.
>
> Well, yes.  You have to be a member of the mailing list you want to
> post to even if you are posting through usenet.  Otherwise your post
> will bounce to you *email* account.  Half of those who respond even
> when you are a member of the list, respond via email only!  And you
> must be a member of the 26 different mailing lists in order to post to
> each one!
>
> For example, if I have a question about JDBC, I must first sign up for
> the mailing list and go through the confirmation steps and then after
> my problem is solved, I then have to unsubscribe in order to stop my
> inbox from being flooded with emails! Is this how usenet should work
> in your opinion?
>
> > Followups set to comp.os.linux.misc only, *again*.  Next time, they'll
> > be set to the killfile.
>
> Since we are discussing USENET, I feel comp.os.linux.misc is an
> inappropriate place for a followup.

Then take in to PGSQL* where it is on-topic.



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

[Markus Schulz]

Am Donnerstag, 3. Februar 2005 08:32 schrieb Uwe C. Schroeder:
> Maybe it's to late for me to think correctly (actually I'm sure of
> that). I'm going to ask anyways.
> I have a table like
>
> id int4
> user_id int4
> photo varchar
> image_type char(1)
>
> where image_type is either G or X
> What I want to do is have ONE query that gives me the count of images
> of each type per user_id.
> So if user 3 has 5 photos of type G and 3 photos of type X
> I basically want to have a result 5,3
> It got to be possible to get a query like that, but somehow it eludes
> me tonight.
>
> Any pointers are greatly appreciated.
>
>  UC

select user_id,image_type, count(id)
from 
group by user_id,image_type

should do it.

-- 
Markus Schulz

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

[GENERAL] mysql load_file() function

[Ben-Nes Yonatan]

Hi all,
Does anyone know if PostgreSQL got a function which work like
load_file() of mySQL?
I need it for uploading of big files... i encounter a memory limit when
i try to upload a query with the file data in it and i recieved this
response for a question about it at php-general maillist:
"load_file() is mysql internal function. the file is read by mysql 
server, so it completely bypass php and also client libraries. you need

to have file priviledge."
Or maybe there is another way to upload big queries without running off
the memory limit?
Another recommendation that i received is to just upload the files
to the file system... but then ill lose the integrity of the database
(though i can make safety checks to know that everything is as it
should be) so i prefer to find a way to insert it to the DB.

My server is as follows:
1. PHP 4.3.9
2. DB - Postgresql 7.4
3. Apache 1.3.26
Thanks in advance,
    Ben-Nes Yonatan

Re: [GENERAL] How to delete duplicate rows?

[John Sidney-Woollett]

Can you not use your table's primary key value instead?
If you table is created with OIDs you may be able to use those - 
although I don't know if that this advisable or not since I never use 
OIDs...

John Sidney-Woollett
Clodoaldo Pinto wrote:
This one must be obvious for most here.
I have a 170 million rows table from which I want to eliminate
duplicate "would be" keys and leave only uniques.
I found a query in http://www.jlcomp.demon.co.uk/faq/duplicates.html
for the oracle database but can't figure out how to refer to the row
id in postgresql:
delete from test where rowid not in 
(select min(rowid) from test group by a,b);

How to refer to the row id? Any better way to do it?
Regards, Clodoaldo Pinto
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(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] Start problem on OSX

[Philippe Schmid]

I have a problem starting PG 8.0 on a OSX machine, getting this
	FATAL:  could not open lock file "/tmp/.s.PGSQL.5432.lock": 
Permission
denied
Perhaps that file already exists?
regards, tom lane
Huu, I feel really stupid... forgot to check invisible files...
Many thanks, it is now working :-)

Regards,
Philippe Schmid


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