Re: [ADMIN] initdb error on windows - urgent, please
On 1 Oct 2003 at 22:19, Mailing List wrote: [...] now on W2K and W2003 When I try to initialize dbspace using initdb, for example: [...] I will get such information: [...] IpcSemaphoreCreate: semget(key=1, num=17, 03600) failed: Function not implemented initdb failed. I think your problem is related to cygwin. [...] Bye, Chris. Thanks, Chris. I haven't noticed, that a service named ipc-daemon (displayed as Cygwin IPC Daemon) had not been installed at all during PostgreSQL installation process! So, after creating this service by hand using Windows' sc utility everything seems to work. But the strange thing occurred: yesterday I have installed PostgreSQL on twin-made computers: the same hardware, the same OS - Win 2003 SE PL installed. The only difference between them were the Windows serial numbers as my coleaques from our service dept told me :-). And in the first case installation ended succesfully, but in the second one problems with initdb ocuured (as described before). Cute, isn't it? :-/. Thanks again for your help! PS. Previously I have sent my answer to your priv by mistake, sorry -- Pozdrawiam Jarek Lubczyski There are 10 kinds of people: Those who understand binary and those who don't ---(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: [ADMIN] initdb error on windows - urgent, please
On 2 Oct 2003 at 10:45, Kerv wrote: Jarek Lubczyn'ski wrote: Hi, everybody, Please help me with an urgent problem. [...] When I try to initialize dbspace using initdb, for example: . initdb -D /cygdrive/d/database/postgres --username=myuser -W -E LATIN2 . I will get such information: [...] initdb failed. Hi, You need the IPC support for cygwin. Find it and download the cygipc package for cygwin. For set up and running PostgreSQL for cygwin please read postgresql-...README from ...cygwin\usr\doc\Cygwin Regards, Kerv I have just read, thanks. But I have noticed that some things fails during PostgreSQL installation on some Win2k, such as: - under bash files in /usr/bin are owned by 'unknown' - I have to chown them to proper user by hand - ipc-daemon service isn't created - I must do it by my own using sc utility - file /usr/bin/postmaster is missing - I must create it - PostgreSQL Server service isn't created as well - I must use script /usr/bin/serviceinstall.bat to create it After performing those commands Postgres really wants to work (tested on only one machine). But the strangest thing is that those problems seems to appear only on Win 2000 2003 and, moreover, not always. For example yesterday my coleaques gave me two _identically_ configured machines - the same hardware and with the same software installed on them. On one machine everything seems to work perfectly, but on the second one problems occurred. Why??? -- Greetings Jarek Lubczyski There are 10 kinds of people: Those who understand binary and those who don't ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [ADMIN] pg_restore fails - postgres 7.3.4
Tom,
Will this be fixed in 7.4?
Thanks for the support.
Rob
- Original Message -
From: Tom Lane [EMAIL PROTECTED]
Date: Thursday, October 2, 2003 6:28 pm
Subject: Re: [ADMIN] pg_restore fails - postgres 7.3.4
Rob Long [EMAIL PROTECTED] writes:
Trying to dump and restore a production database with no success.
pg_restore: [archiver (db)] could not execute query: ERROR:
data type
integer has no default operator class for access method gist
Hm, this is probably a matter of incorrect object restoration
order ...
could we see the output of pg_restore -l for this dump file?
After playing around with this I realize that it's a bug
associated with
schemas --- if you have an operator class that's not in
pg_catalog, the
opclass name has to be explicitly qualified in pg_dump's output,
or it
won't restore correctly. I'm surprised no one reported this before,
since it affects all the contrib operator classes.
Attached is a patch against 7.3.4, in case it helps.
regards, tom lane
Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /cvsroot/pgsql-server/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.124.2.1
diff -c -r1.124.2.1 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 8 Jan 2003 22:54:36 - 1.124.2.1
--- src/backend/utils/adt/ruleutils.c 2 Oct 2003 22:21:03 -
***
*** 2951,2956
--- 2951,2957
Form_pg_opclass opcrec;
char *opcname;
char *nspname;
+ boolisvisible;
/* Domains use their base type's default opclass */
if (OidIsValid(actual_datatype))
***
*** 2962,2972
if (!HeapTupleIsValid(ht_opc))
elog(ERROR, cache lookup failed for opclass %u, opclass);
opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
! if (actual_datatype != opcrec-opcintype || !opcrec-opcdefault)
{
/* Okay, we need the opclass name. Do we need to qualify it? */
opcname = NameStr(opcrec-opcname);
! if (OpclassIsVisible(opclass))
appendStringInfo(buf, %s, quote_identifier(opcname));
else
{
--- 2963,2978
if (!HeapTupleIsValid(ht_opc))
elog(ERROR, cache lookup failed for opclass %u, opclass);
opcrec = (Form_pg_opclass) GETSTRUCT(ht_opc);
!
! /* Must force use of opclass name if not in search path */
! isvisible = OpclassIsVisible(opclass);
!
! if (actual_datatype != opcrec-opcintype || !opcrec-opcdefault ||
! !isvisible)
{
/* Okay, we need the opclass name. Do we need to qualify it? */
opcname = NameStr(opcrec-opcname);
! if (isvisible)
appendStringInfo(buf, %s, quote_identifier(opcname));
else
{
---(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: [ADMIN] pg_restore fails - postgres 7.3.4
Rob Long [EMAIL PROTECTED] writes: Will this be fixed in 7.4? Yes, and also in 7.3.5 if there is such a release. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [ADMIN] Delete accident
Title: RE: [ADMIN] Delete accident Thank you everyone! I got a load of responses, and don't want to keep anyone working at this. the magic is in pgfsck! http://svana.org/kleptog/pgsql/pgfsck.html -anya
[ADMIN] pg_restore fails postgres 7.3.4
Trying to dump and restore a production database with no success. pg_dump database using the following: pg_dump -v -b -C -Fc -U postgres -f servdb.tar.gz servdb pg_restore with with following: pg_restore -v -C -D template1 -Fc -U postgres servdb.tar.gz restore fails with: pg_restore: [archiver (db)] could not execute query: ERROR: data type integer has no default operator class for access method gist You must specify an operator class for the index or define a default operator class for the data type pg_restore: ***aborted because of error From the documentation the following query illustrates that I have four gist acc_methods available: select am.amname as acc_method, opc.opcname as ops_name from pg_am am, pg_opclass opc where opc.opcamid = am.oid order by acc_method, ops_name; acc_method | ops_name - gist | gist_box_ops gist | gist_int4_ops gist | gist_poly_ops gist | gist_timestamp_ops The create index statement that is failing the restore: CREATE INDEX idx_disc_loc ON order_items USING gist (disc, loc); Has anyone battled this one before? Is there a problem with dumping/restoring with blobs? Thanks, Rob ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[ADMIN] Partial indexes ... any good theoretical discussion?
I've only just now noticed that CREATE INDEX accepts a 'WHERE' clause. This is used to create something called a partial index. Hmm, ever being one who sees the world as made of nails when first given a hammer ... One of our tables, with a couple hundred thousand rows) has a date-column index. We expire things out of the table every day (the vast majority, but not exclusively, stuff that's a couple days old). We're frequently running queries against the table, looking for everything since this time yesterday; we hardly ever look back more than 24 hours. If I created the index as something like: CREATE INDEX ix_foo ON foo(the_date) WHERE the_date = now() - interval '24 hours'; what might I expect as the impact? Do index values older than 24 hours drop out? Or must I refresh the index from time to time (in our application, probably a couple dozen times a day)? And, absent pat answers to this, is there anything out there in PG-land that documents partial indexes, and when to use them? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] Partial indexes ... any good theoretical discussion?
Jeff Boes [EMAIL PROTECTED] writes: If I created the index as something like: CREATE INDEX ix_foo ON foo(the_date) WHERE the_date = now() - interval '24 hours'; what might I expect as the impact? You won't be allowed to, because now() isn't immutable, and only immutable functions are allowed in index expressions and predicates. You could imagine a batch job every night creating a new index CREATE INDEX ix_foo_20031003 ON foo(the_date) WHERE the_date = '2003-10-03' and then dropping the prior index. Dunno if this would be worth the trouble, but it might be. The CREATE INDEX should run quite quickly if it only has to pick up a few rows, which it would if you run it shortly after the chosen boundary. And, absent pat answers to this, is there anything out there in PG-land that documents partial indexes, and when to use them? http://developer.postgresql.org/docs/postgres/indexes-partial.html The links at the bottom of the page point to various academic papers (I hope the links all still work...) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[ADMIN] sequence table
Hi all, Is there a script where u can get list of sequences ,the tables associated with it and the column for which these sequences are created. Thanks in advance SG Instant message during games with MSN Messenger 6.0. Download it now FREE!
[ADMIN]
Is there a script that gives a list of sequences and the tables associated with it and the columns associated with it Thanks in advance SG Instant message in style with MSN Messenger 6.0. Download it now FREE!
Re: [ADMIN] Partial indexes ... any good theoretical discussion?
In an attempt to throw the authorities off his trail, Jeff Boes [EMAIL PROTECTED] transmitted: I've only just now noticed that CREATE INDEX accepts a 'WHERE' clause. This is used to create something called a partial index. Hmm, ever being one who sees the world as made of nails when first given a hammer ... One of our tables, with a couple hundred thousand rows) has a date-column index. We expire things out of the table every day (the vast majority, but not exclusively, stuff that's a couple days old). We're frequently running queries against the table, looking for everything since this time yesterday; we hardly ever look back more than 24 hours. If I created the index as something like: CREATE INDEX ix_foo ON foo(the_date) WHERE the_date = now() - interval '24 hours'; what might I expect as the impact? Do index values older than 24 hours drop out? Or must I refresh the index from time to time (in our application, probably a couple dozen times a day)? That won't work, unfortunately. [somedatabase]=# create index partial on transaction_log(trans_on) where trans_on now() - '5 days'::interval; ERROR: functions in index predicate must be marked IMMUTABLE [somedatabase]=# You can't have now() (which is certainly *not* immutable) as part of the index. A better idea would be to have a set of several tables, one for each day, UNION ALL them together to generate a view to support queries, and use a sequence to control which table is inserted to on any given day, alongside some rules for insert/deletes. -- (format nil [EMAIL PROTECTED] aa454 freenet.carleton.ca) http://cbbrowne.com/info/linuxxian.html Waving away a cloud of smoke, I look up, and am blinded by a bright, white light. It's God. No, not Richard Stallman, or Linus Torvalds, but God. In a booming voice, He says: THIS IS A SIGN. USE LINUX, THE FREE Unix SYSTEM FOR THE 386. -- Matt Welsh ---(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: [ADMIN] Partial indexes ... any good theoretical discussion?
It won't work. You could instead have a separate boolean attribute called 'expired' for each row. Set this to true whenever you expire the row, and create the partial index using that attr. Matt -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jeff Boes Sent: 03 October 2003 17:35 To: [EMAIL PROTECTED] Subject: [ADMIN] Partial indexes ... any good theoretical discussion? I've only just now noticed that CREATE INDEX accepts a 'WHERE' clause. This is used to create something called a partial index. Hmm, ever being one who sees the world as made of nails when first given a hammer ... One of our tables, with a couple hundred thousand rows) has a date-column index. We expire things out of the table every day (the vast majority, but not exclusively, stuff that's a couple days old). We're frequently running queries against the table, looking for everything since this time yesterday; we hardly ever look back more than 24 hours. If I created the index as something like: CREATE INDEX ix_foo ON foo(the_date) WHERE the_date = now() - interval '24 hours'; what might I expect as the impact? Do index values older than 24 hours drop out? Or must I refresh the index from time to time (in our application, probably a couple dozen times a day)? And, absent pat answers to this, is there anything out there in PG-land that documents partial indexes, and when to use them? -- Jeff Boes vox 269.226.9550 ext 24 Database Engineer fax 269.349.9076 Nexcerpt, Inc. http://www.nexcerpt.com ...Nexcerpt... Extend your Expertise ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] How to avoid users from viewing functions code
Hi the problem is to avoid users from viewing functions code (in general db structures) when connecting to db by means of pgpadmin client. My db contains several tables and functions and I need to make users viewing only a restricted number of tables. It works for tables, as it's possible to define access policies, but I didn't find something like that for functions. The result is that everyone can see functions code from all pgadmin (II III) clients using basic user account. This is my PostgreSQL Client Authentication Configuration File local all all trust hostall all 127.0.0.1 255.255.255.255 trust hostall all 10.43.13.75 255.255.255.255 trust hosttemplate1 basic 0.0.0.0 0.0.0.0 md5 hostserviziobasic 0.0.0.0 0.0.0.0 md5 Is there any idea? Thanks Paolo Mattioli ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
