[GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Hi All, new here, and pgsql in general. I currently use MySQL and now need to know pgsql. I have set it up on my Linux box, CentOS 4-3, using an RPM from Dag Wieer's repos. I am getting the following, [EMAIL PROTECTED] lib]$ psql template1 psql: FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. after following here, http://polder-linux.org/modules.php?name=News&file=article&sid=182 as I was eperiencing the same problem as the user at the bottom, Warning: pg_connect() unable to connect to PostgreSQL server: FATAL 1: IDENT authentication failed for user "arjen" Refer to PostgreSQL Administrator's guide, Chapter 4: Client Authentication. You probably have this line in the /var/lib/pgsql/data/pg_hba.conf: local all ident sameuser (I know RedHat 8.0 does this). You need to change this into: local all trust This tells PostgreSQL to allow any UNIX user to log into the database as any database user on a local socket. *** and here, http://serghei.net/docs/database/pgresql-7.1-admin/client-authentication.html I'm a little lost. Could someone steer me in the right direction? Cheers. Mark Sargent.
Re: [GENERAL] sudo-like behavior
Sorry, but you misunderstand- nowhere am I interested in the role's password. My previous suggestion was to add a password to set session authorization itself so that if the authorization were to be reset, it would need to be done with that password; the password itself could be machine-generated. It it would merely allow a secure sandbox to be established between: SET SESSION AUTHORIZATION somerole WITH PASSWORD 'abc'; --arbitrary SQL run as somerole RESET SESSION AUTHORIZATION; --fails- requires password RESET SESSION AUTHORIZATION WITH PASSWORD 'pass'; --fails RESET SESSION AUTHORIZATION WITH PASSWORD 'abc'; --succeeds- we are done with this role The password ensures that the session authorization initiator is the only one that can terminate it as well. -M On Apr 20, 2006, at 10:44 PM, Tom Lane wrote: Agent M <[EMAIL PROTECTED]> writes: I really haven't provided enough details- my fault. What I want to accomplish is a general-purpose timer facility for postgresql. I'm not really sure why you think it'd be a good idea for such a thing to operate as an unprivileged user that gets around its lack of privilege by storing copies of everyone else's passwords. I can think of several reasonable ways to design the privilege handling for a cron-like facility, but giving it cleartext copies of everyone's passwords is not one of them. regards, tom lane ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ AgentM [EMAIL PROTECTED] ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ¬ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to replace rows in table so that foreign key rows
On Thu, 20 Apr 2006, Stephan Szabo wrote: > On Thu, 20 Apr 2006, Andrus wrote: > > > I want to replace ( delete and insert) records in master table . > > I delete and insert record with same primary key. > > I want that foreign key records are not deleted. > > > > I tried > > > > begin; > > create temp table t1 ( pk integer primary key ); > > insert into t1 values(1); > > create temp table t2 (fk integer ); > > alter table t2 add foreign key (fk) references t1 on delete cascade > > deferrable initially deferred; > > insert into t2 values(1); > > -- Howto: set delete_constraint deferred > > delete from t1; > > insert into t1 values(1); > > commit; > > select * from t2; > > > > Observed: no rows > > > > Expected: t2 must contain one row. > > > > foreign key check and deletion should occur only when transaction commits. > > Actually, this looks like a case where SQL99 strongly implies that the > action happens even for non-immediate constraints as part of the delete > but SQL2003 changed that and we didn't notice. This should probably be > reasonably straightforward to change I think (hope). Hmm, actually, it's a little less straightforward than I thought, mostly because I haven't seen something that seems to explicitly say what to do for non-immediate constraints that happened before the commit in the 2003 spec, I'd guess do the action at commit time as well, but the wording of the general rules talk about rows marked for deletion, but by the time of the commit, those rows are not marked for deletion any longer, but actually deleted as far as I can see and there doesn't appear (for non-match partial constraints) seem to be a special case for the referenced row coming back into existance as far as I can tell either. > > Any idea ? > > Is there any generic way to turn off foreign key constraints before delete > > command in transaction ? > > Right now, probably nothing short of dropping and readding the constraint. Or, if you're willing to patch, I think a first order approximation of what you want might be to remove the special cases in trigger.c (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I haven't tested that. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Query to check existence of stored procedure?
then this will not work.
-- Original Message ---
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: Jim Buttafuoco <[EMAIL PROTECTED]>
Cc: Alexander Scholz <[EMAIL PROTECTED]>, pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:51:17 -0500
Subject: Re: [GENERAL] Query to check existence of stored procedure?
> And what happens if you have an overloaded function? :)
>
> On Thu, Apr 20, 2006 at 07:55:50AM -0400, Jim Buttafuoco wrote:
> > Give this function a try, examples at the end, I used Postgresql 8.1.3
> >
> > -- s is the schema to look in
> > -- f is the function name
> >
> > create or replace function isfunctionavailable(s text,f text)
> > returns bool
> > as
> > $$
> > declare
> > ans bool;
> > begin
> > select into ans true
> > from pg_proc p
> > join pg_namespace n on(p.pronamespace = n.oid)
> > where proname = f
> > and nspname = s
> > group by proname
> > having count(*) > 0;
> >
> > return coalesce(ans,false);
> > end;
> > $$
> > language plpgsql
> > ;
> >
> > select IsFunctionAvailable('public'::text,'isfunctionavailable'::text);
> > select IsFunctionAvailable('junk'::text,'isfunctionavailable'::text);
> > select IsFunctionAvailable('public'::text,'junk'::text);
> >
> >
> >
> > -- Original Message ---
> > From: Alexander Scholz <[EMAIL PROTECTED]>
> > To: pgsql-general@postgresql.org
> > Sent: Tue, 18 Apr 2006 17:08:50 +0200
> > Subject: Re: [GENERAL] Query to check existence of stored procedure?
> >
> > > Hi Jim,
> > >
> > > >> select count(*) from pg_proc where proname = 'your_function';
> > > >>
> > > > don't forget about schema's, you will need to join with
> > > > pg_namespace.oid and pg_proc.pronamespace
> > >
> > > your answer looks a little bit cryptic for me being somebody who hasn't
> > > had to dive into the pg_... tables yet. :-)
> > >
> > > What do you exactly mean? Could you provide me a complete query for that
> > > job?
> > >
> > > Is there anything to consider, if the user performing this query is NOT
> > > the owner of the stored prodcedure? (but he needs this info as well!)
> > >
> > > Thank you in advance,
> > >
> > > Alexander.
> > >
> > > ---(end of broadcast)---
> > > TIP 4: Have you searched our list archives?
> > >
> > >http://archives.postgresql.org
> > --- End of Original Message ---
> >
> >
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
> Pervasive Software http://pervasive.comwork: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
--- End of Original Message ---
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Query to check existence of stored procedure?
nice, this could be put into a plpgsql function with error handling.
-- Original Message ---
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: Alexander Scholz <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Sent: Fri, 21 Apr 2006 00:54:51 -0500
Subject: Re: [GENERAL] Query to check existence of stored procedure?
> If you're on a more recent version, you can try and select the procname
> for a proc and trap the error:
>
> decibel=# select 'abs(bigint)'::regprocedure;
> regprocedure
> --
> abs(bigint)
> (1 row)
>
> decibel=# select 'abs(text)'::regprocedure;
> ERROR: function "abs(text)" does not exist
> decibel=#
>
> If you don't care about arguments you can use regproc.
>
> On Tue, Apr 18, 2006 at 08:26:49AM +0200, Alexander Scholz wrote:
> > Hi Newsgroup,
> >
> > I need a query which can check for the existence of a certain stored
> > procedure.
> >
> > (The pendant for MS SQL is
> >
> > IF EXISTS (SELECT * FROM "sysobjects" WHERE "id" =
> > object_id(N'"MyTestStoredProcedure"') and OBJECTPROPERTY("id",
> > N'IsProcedure') = 1)
> > ...
> > )
> >
> > Any help would be appreciated! :-)
> >
> > Thanx in advance,
> >
> > Alexander.
> >
> > ---(end of broadcast)---
> > TIP 9: In versions below 8.0, the planner will ignore your desire to
> >choose an index scan if your joining column's datatypes do not
> >match
> >
>
> --
> Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
> Pervasive Software http://pervasive.comwork: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
--- End of Original Message ---
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Mark,and what exactly is there in server log? psql: FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. Reading your words I assume you made a typing error while editing pg_hba.confHarald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b 70197 Stuttgart0173/9409607-PostgreSQL - supported by a community that does not put you on hold
Re: [GENERAL] GiST index slower than seqscan
--- Teodor Sigaev <[EMAIL PROTECTED]> wrote: > > In case you're unfamiliar with this particular horse, I'm using ltree to > create > > a full text index on some <= 50 char long fields for a lookup table. The > idea > > was to be able to tear through tons of data quickly finding case > insensitive > > substring matches. > > > > Why it is a ltree, not a tsearch? When I said full text, I meant substring. Please correct me if I am wrong, but tsearch would be useful for finding words in a paragraph, not characters in a word (or small group of words) ... If I had fields 'Hello World!', 'Low Tide', and 'Following Day' they would all be hits for a search on 'low' ... > > > > Index Cond: (search_vector ~ '*.6.6.9.3.4.4.*'::lquery) > > That's the problem. Queries which begin with '*' will be slow enough... > Indeed. Substring searches are quite costly... I was hoping that the hiearchical nature of ltree would allow me to be able to sift quickly through the list since every alpha or numeric character would be a branch on the tree. > Try to reduce SIGLENINT in tsearch2/gistidx.h up to 8 (do not forget reindex > !!) > and try it I bet you meant ltree/ltree.h ... I'll give that a try and see what happens! Thank you! __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] setting the environment locale - linux, windows
On Fri, Apr 21, 2006 at 12:49:31AM +0200, Tomi NA wrote: > This is probably somewhat offtopic, but it does relate to postgresql so... > Problem summary: I have a UTF-8 encoded database running on linux on > which upper() and lower() string functions ignore locale specific > characters. You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE are fixed at initdb and constant across the DB. > Are there plans to enable assigning locale at the database level? Plans, yes. Progress, a bit slow... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] setting the environment locale - linux, windows
On 4/21/06, Martijn van Oosterhout wrote: > On Fri, Apr 21, 2006 at 12:49:31AM +0200, Tomi NA wrote: > > This is probably somewhat offtopic, but it does relate to postgresql so... > > Problem summary: I have a UTF-8 encoded database running on linux on > > which upper() and lower() string functions ignore locale specific > > characters. > > You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE > are fixed at initdb and constant across the DB. So, if I were to backup my database, clear the data directory, reinitialize the cluster and restore the database using the correct LC_COLLATE and LC_CTYPE, I'd have one language nailed? > > Are there plans to enable assigning locale at the database level? > > Plans, yes. Progress, a bit slow... No estimates, then? Nevertheless, thanks for the help. Tomislav ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] A few questions about ltree
Yesterday ltree was mentioned to be a good system for tree structured table data. I and a colleague of mine have been playing around with the examples and the (rather sparse) documentation, but we're stuck on a few questions... How does one guarantee referential integrity using ltrees? It doesn't seem to do so by itself, but can it refer a parent node directly? We assume you can do this: CREATE TABLE my_tree ( path ltree PRIMARY KEY, parent ltree REFERENCES my_tree(path) ); In this case a tree would look something like: parent | path -- (NULL) | A A | A.B A.B | A.B.D A | A.C That's the "classical" way, which is also used in our current implementation with integers instead of ltrees, but it's not very easy to query efficiently (at least ordering seems to remain a problem). Maybe something along the lines of the following is possible?: CREATE TABLE my_tree ( path ltree PRIMARY KEY REFERENCES my_tree(path) ); Data would look like: path --- A A.B A.B.D A.C With A.B and A.C referencing A in their parent record and A.B.D referencing A.B What I like about this solution is that only one ltree path per node is required, and that the root node doesn't need a parent reference. The question is whether this is/can-be-made possible... Do ltrees know that a node with path 'A.B.D' references it's parent 'A.B'? I mean, can ltree 'A.B' equal ltree 'A.B.D' somehow while the strings are unequal? Can it be made to know that somehow (functional foreign keys or something - maybe using "ltree_isparent(ltree, ltree)")? I can determine things like this with a few experiments, but I want to know "the right way" to work with ltrees and referential integrity. How do people use this? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] setting the environment locale - linux, windows
On Fri, Apr 21, 2006 at 03:34:27PM +0200, Tomi NA wrote: > On 4/21/06, Martijn van Oosterhout wrote: > > You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE > > are fixed at initdb and constant across the DB. > > So, if I were to backup my database, clear the data directory, > reinitialize the cluster and restore the database using the correct > LC_COLLATE and LC_CTYPE, I'd have one language nailed? Well, you need the reinitialise the cluster with the correct language. Once initiailised it doesn't matter what you restore with. > > > Are there plans to enable assigning locale at the database level? > > > > Plans, yes. Progress, a bit slow... > > No estimates, then? > Nevertheless, thanks for the help. Well, I'm worked on COLLATE support which would get it down to the column level. This is pretty much what you need, since indexes could be shared between databases and each database needs the same definition for those indexes. I'm got a fair way but got a little over my head, so unless someone picks it up and completes it, it could be a while... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] GiST index slower than seqscan
When I said full text, I meant substring. Please correct me if I am wrong, but tsearch would be useful for finding words in a paragraph, not characters in a word (or small group of words) ... If I had fields 'Hello World!', 'Low Tide', and 'Following Day' they would all be hits for a search on 'low' ... Ok, I see I bet you meant ltree/ltree.h ... I'll give that a try and see what happens! Thank you! Ltree index structure is mixed by BTree and signature tree. You don't need Btree part as I understand... I don't know efficient index structure to support queries you want... May be Oleg knows... -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A few questions about ltree
That's the "classical" way, which is also used in our current
implementation with integers instead of ltrees, but it's not very easy
to query efficiently (at least ordering seems to remain a problem).
That (with integer ids) is classic way to support graph structure, ltree was
develop specially for trees.
Maybe something along the lines of the following is possible?:
Exact, it's for what ltree was developed.
Do ltrees know that a node with path 'A.B.D' references it's parent
'A.B'? I mean, can ltree 'A.B' equal ltree 'A.B.D' somehow while the
strings are unequal?
Can it be made to know that somehow (functional foreign keys or
something - maybe using "ltree_isparent(ltree, ltree)")?
Yes, use ltree_isparent or
contrib_regression=# select 'a.b.c' <@ 'a.b'::ltree;
?column?
--
t
(1 row)
contrib_regression=# select 'a.b.c.d' <@ 'a.b'::ltree;
?column?
--
t
(1 row)
contrib_regression=# select 'a.b.c.d'::ltree ~ 'a.b.*{1}';
?column?
--
f
(1 row)
contrib_regression=# select 'a.b.c'::ltree ~ 'a.b.*{1}';
?column?
--
t
(1 row)
I can determine things like this with a few experiments, but I want to
know "the right way" to work with ltrees and referential integrity. How
do people use this?
That's right way.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] setting the environment locale - linux, windows
On 4/21/06, Martijn van Oosterhout wrote: > On Fri, Apr 21, 2006 at 03:34:27PM +0200, Tomi NA wrote: > > On 4/21/06, Martijn van Oosterhout wrote: > > > You need to look at your LC_COLLATE settings. LC_COLLATE and LC_CTYPE > > > are fixed at initdb and constant across the DB. > > > > So, if I were to backup my database, clear the data directory, > > reinitialize the cluster and restore the database using the correct > > LC_COLLATE and LC_CTYPE, I'd have one language nailed? > > Well, you need the reinitialise the cluster with the correct language. > Once initiailised it doesn't matter what you restore with. > > > > > Are there plans to enable assigning locale at the database level? > > > > > > Plans, yes. Progress, a bit slow... > > > > No estimates, then? > > Nevertheless, thanks for the help. > > Well, I'm worked on COLLATE support which would get it down to the > column level. This is pretty much what you need, since indexes could be > shared between databases and each database needs the same definition > for those indexes. > > I'm got a fair way but got a little over my head, so unless someone > picks it up and completes it, it could be a while... I'd be more than happy to lend a hand if I had a 9 to 5 job...*if* I had a 9 to 5 job. A man can dream... :-\ t.n.a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] A few questions about ltree
Teodor Sigaev wrote:
Maybe something along the lines of the following is possible?:
Exact, it's for what ltree was developed.
Cool, looks like it is what I need then.
contrib_regression=# select 'a.b.c' <@ 'a.b'::ltree;
?column?
--
t
(1 row)
How would you use this to constrain a foreign key?
We've been experimenting with a table containing a branch 'a', 'a.b' and
'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"ltree_test_pkey" for table "ltree_test"
CREATE TABLE
SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
SQL> select * from ltree_test;
path
---
a
a.b.c
(2 rows)
Is there some obvious/easy way to prevent this?
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to replace rows in table so that foreign key rows
> ... and there doesn't appear (for > non-match partial constraints) seem to be a special case for the > referenced row coming back into existance as far as I can tell either. > Or, if you're willing to patch, I think a first order approximation of > what you want might be to remove the special cases in trigger.c > (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I > haven't tested that. Thank you. So I must create and maintain special version of PostgreSQL ? Andrus. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Harald Armin Massa wrote: Mark, and what exactly is there in server log? LOG: missing field in file "/var/lib/pgsql/data/pg_hba.conf" at end of line 67 FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. psql: FATAL: missing or erroneous pg_hba.conf file HINT: See server log for details. Reading your words I assume you made a typing error while editing pg_hba.conf No typo. Thought I'd pasted this in the first post. Sorry about that. # TYPE DATABASEUSERCIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all trust # IPv4 local connections: hostall all 127.0.0.1/32 ident sameuser # IPv6 local connections: hostall all ::1/128 ident sameuser Cheers. Mark Sargent. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to replace rows in table so that foreign key rows
On Fri, 21 Apr 2006, Andrus wrote: > > ... and there doesn't appear (for > > non-match partial constraints) seem to be a special case for the > > referenced row coming back into existance as far as I can tell either. > > > Or, if you're willing to patch, I think a first order approximation of > > what you want might be to remove the special cases in trigger.c > > (afterTriggerSetState) and tablecmds.c (createForeignKeyTriggers), but I > > haven't tested that. > > Thank you. > So I must create and maintain special version of PostgreSQL ? If the standard does say it should do what you want, it'll get changed for a later version, but probably not backpatched, so this would be a short term solution. The hardest part about changing it is making sure there aren't any new holes in the constraint. If the standard doesn't match what you want, then it's a bit more involved. Following the standard would still require you to maintain a special version for the rules you want or changing the expectation. Or, alternatively, you could make a case why the standard does say what you want (or allows what you want or is simply wrong) if there's a disagreement. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Mark Sargent <[EMAIL PROTECTED]> writes: > # TYPE DATABASEUSERCIDR-ADDRESS METHOD > # "local" is for Unix domain socket connections only > local all trust That's short one "all": you need type, database, user, method columns (but no address column for "local"). regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] primary keys
In article <[EMAIL PROTECTED]>, Klint Gore <[EMAIL PROTECTED]> wrote: % works for me on version 8.1.3 % % SELECT attname % FROM pg_index %JOIN pg_class ON (indrelid = pg_class.oid) %JOIN pg_attribute ON (attrelid = pg_class.oid) % WHERE indisprimary IS TRUE %AND attnum = any(indkey) %AND relname = $tablename; This will work on 7.4, 8.0, or 8.1 SELECT attname FROM pg_index JOIN pg_class as c1 ON (indrelid = c1.oid) JOIN pg_class as c2 ON (indexrelid = c2.oid) JOIN pg_attribute ON (attrelid = c2.oid) WHERE indisprimary AND c1.relname = $tablename ; No arrays are hurt by this query. -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] HUGE Stack space is gettiing consumed
Hello Martijin, I am not able to make it ..Code what u sent is *not* giving desired result.. That is -when we try to fetch huge data in a thread routine it says "could not receive data from server: Error 0" If it was in main thread then it goes thorugh..I.e It's able to get the huge data. Environment.. Postgres Version:7.4.3 Compiler:aCC 5.0 OS:HP-UX PARISC 11.23 Code has put in the following link.. http://phpfi.com/113850 Will send the tusc output (stack trace) Thx, Prasanna. -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 18, 2006 2:46 PM To: Mavinakuli, Prasanna (STSD) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] HUGE Stack space is gettiing consumed On Tue, Apr 18, 2006 at 12:31:57PM +0530, Mavinakuli, Prasanna (STSD) wrote: > Hello Martijin, > Thx for u'r Suggetions.. > > Here is Complete source code.. Ok, I can't get it to fail. I cleaned up the code because it wouldn't compile as is (g++ 3.3.5 on Debian). I've put the cleaned up version here: http://svana.org/kleptog/temp/test.cpp You might need to change the QUERY_STRING and the CONNECT_STRING back. Could you try running this to see if you can get it to fail? If it's does fail, could you post the output of: strace -f somewhere (*don't* post it to the list). Somewhere like: http://phpfi.com/ should work well. > Desc about following code > > Code has a function named as queryDB, which makes a connection to our > DB and then tries to query Particular table which has huge string in > it's field. > > If this method called from main thread then it *DOES* work properly. > > But if we make that as routine function for a thread it doesn't work > instead we will get that following status ::: > ( PGRES_FATAL_ERROR ) > ***could not receive data from server: Invalid argument*** Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is > a tool for doing 5% of the work and then sitting around waiting for > someone else to do the other 95% so you can sue them. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] temp tables problem
just to add on that, there is only one user for the db. so both application accesses use the same db username and password. the web app automatically logs into the db in with the one username and password for both remote and local access. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Triggers and Transactions
Hi, I have a question about how much of a trigger is in a transaction. I've read the docs and googled around but can't seem to find a concrete answer. I have two triggers that are designed to work together, one is a before trigger and one is an after. If the before trigger succeeds then it will have made some changes to one of my tables, however if the after one fails some how (elog(ERROR, "")? then I would like to rollback the changes of the before one as well as any made by the after one too. Is this possible? Many thanks Chris Coleman. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] temp tables problem
hi all, i am working on a java based web application. this application connects to a postgres DB. now within some pages i am using temp tables to perform some calculation. when i access these pages locally it works fine. however when i access the web app remotely (as in over the web or on the network) those pages fail with an exception saying that, the temp table already exists. those tables work fine when i access the web app locally as many times as i want. but if i access the same page remotely at the same time, it fails and vice versa too. i was hoping that temp tables would fix this problem (two users performing the same calculation at the same time, one locally and one remotely), but it seems to not work. can somebody please help me out. thanks heaps. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Notification EXTRA data
sql-notify.html :
Higher-level mechanisms can be built by using tables in the database
to pass additional data (beyond a mere notification name) from notifier
to listener(s).
How ?
root2=> select * from pg_listener ;
relname | listenerpid | notification
-+-+--
aaa | 723 |0
(1 row)
grep -5 "typedef struct pgNotify" /usr/local/include/libpq-fe.h
typedef struct pgNotify
{
char *relname; /* notification condition name */
int be_pid; /* process ID of server process
*/
char *extra;/* notification parameter */
} PGnotify;
I need to insert "extra" data - uid, ...
PGnotify *notify;
notify->extra;
Thanks
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] tomcat postgresql connectivity error
Yes I did a search on Google but it didnt give me any clues.. Although the database is now working fine. The only thing I did was to change the statement with which i created postgresql logfile... I would do./.../usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data >> /usr/local/pgsql/logs/logfile.txt 2>&1 start now I do /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start the database started working fine. Thanks Danish -- View this message in context: http://www.nabble.com/-GENERAL-tomcat-postgresql-connectivity-error-t1478217.html#a4003642 Sent from the PostgreSQL - general forum at Nabble.com. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Setup for large database
Couple of questions. I have a project that promises to generate a very large database of network-style data (think banners, flows, etc). I was fortunate enough to fall into some kick ass hardware (quad Opteron, 16GB RAM, 3+ TB of fibre channel HDs). As I'm still in the design phase, I was wondering if I could get any decent recommendations on hardware/partition setup, and perhaps some database sanity checks. My use case is mostly datawarehouse-style stuff: scheduled bulk batch inserts and lots of queries. Like I said before, my rows are based on network data and are all keyed by an IP address, and I'm hoping to keep each row under 1K. I'm estimating ending up with about 3TB of total data after a year of operation. 1) How anal should I be about my hardware setup? I have about 15 300GB 10K RPM SCSI drives, 4 of which I can directly attach to the server and the rest one the FC array. Should I just put the OS and transaction logs on the direct attached storage and and then RAID10 the rest of them and be done, or would I significantly benefit from separating out the indexes and partitioning across tablespaces across drives? Would RAID5 across 10+ drives yield acceptable performance numbers? 3) I've currently installed RHEL4 AS for my OS, which I am very comfortable with. I was going to go with EXT3 on everything (noatime) ... sound good? 2) Assuming that my data is roughly evenly distributed among IP addresses, I figured that a naive partitioning based on the first octet of the IP (i.e. ~255 partitions) would suffice for such a table, making each partition ~12GB and keeping the IPs clustered to easily to quickly query network blocks. Would it be wise to go to even more partitions? How does Pg do under a *lot* of partitions (655356)? Would it be wise to put each partition in a separate tablespace? 3) I guess I don't quite understand Bizgres. At the moment, it seems to be just a development beta of Postgres ... is this true? I realize that the focus is on BI/ETL stuff, but the current improvements seem to benefit Postgres as a whole. Is there currently or can you imagine a case where a feature in Bizgres won't get integrated into Postgres? How significant is the fork between Bizgres and Postgres? I've also considered taking a look at Bizgres MPP. I know that its the wrong forum, but any comments? 4) Not to start any sort of flame war, but my company has an Oracle license and there are a bunch of people wanting me to go that way. I've been doing just fine with Postgres at the moment and am quite comfortable with it, but am being pressured to go with our Oracle license. Cost (and prejudices) aside, do you think it would be wise to go with Oracle to begin with, considering the size of the database that I'm planning? Thanks for any comments, -Mike ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Tom Lane wrote: That's short one "all": you need type, database, user, method columns (but no address column for "local"). Hi All, yes, Tom, you're correct. Someone mailed me directly(thank you), with a link to their site's tut on authentication, and I picked up on it straight away. Seems the sites I was looking at were wrong. Anyway, I now get this, [EMAIL PROTECTED] ~]$ psql template1 psql: FATAL: role "racket" does not exist Log output: LOG: database system is ready LOG: transaction ID wrap limit is 2147484146, limited by database "postgres" FATAL: role "racket" does not exist Which confuses me, as local all all trust should allow me to connect, no? Or, is there a particular syntax when connecting locally? I can't imagie that being so, but then agan, I would have expected this to be a little easier than it has been already. Cheers. Mark Sargent. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql: FATAL: missing or erroneous pg_hba.conf file
Mark Sargent <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] ~]$ psql template1 > psql: FATAL: role "racket" does not exist > Which confuses me, as local all all trust should allow me to connect, > no? No, it allows you to claim that you are any database user you want to claim you are. The default assumption is that you want to be the database user with the same name as your OS username. The problem here is that there is no such user. Most likely the only pre-existing database user will be named "postgres", so try psql -U postgres template1 If that lets you in, execute the SQL command "CREATE USER racket;" and then you should be able to connect as "yourself". (Note: you might prefer to make yourself a superuser, see the manual.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Odd transaction timestamp sequence issue
PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC)
3.4.4 [FreeBSD] 20050518
We have triggers on each of our tables that create audit table entries
on each insert/update/delete.
The audit table (in addition to containing information about the change
that was made) contains a timestamp field and a serial as the primary key.
The timestamp is generated by calling now() inside the audit trigger, so
should contain the timestamp of when the transaction (that yielded the
insert/update/delete) began.
We have a single (and very important) table that we perform the majority
of our operations on..I'll simplify the definition of the table to this:
CREATE TABLE thetable
(
id int8 NOT NULL DEFAULT nextval('thetable_id_seq'::regclass),
flag char(1) NOT NULL,
state char(8) NOT NULL
}
we have a scenario where a batch process takes records from this table
in a certain state and one by one, moves them to a new state.
update thetable set state='COMPLETE', flag='X' where state='INITIAL' and
id=?
Another batch process is looking for any records in that new state...and
then updates certain elements of it.
update thetable set flag='Y' where id in (select id from thetable where
state='COMPLETE')
This update statement is run as it's own transaction (there is nothing
else done in the transaction).
The audit logs for some transactions show something very odd.
For example, for id 210210 we have an audit trail that looks like this...
audit_idrecord_idwhen columnold_val
new_val
----- --- ---
---
12102102006-04-20 12:49:03.92 state INITIAL
COMPLETE
22102102006-04-20 12:49:03.74 flag X Y
By looking at the timestamps, the second update started BEFORE the first
update even though the second update cannot occur if the state hasn't
been changed and committed by the first one!
Even weirder is that the order of the sequence (audit_id) shows them
occurring in the opposite order
Am I missing something obvious here?
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Odd transaction timestamp sequence issue
Jeff Amiel <[EMAIL PROTECTED]> writes: > For example, for id 210210 we have an audit trail that looks like this... > audit_idrecord_idwhen columnold_val > new_val > ----- --- --- > --- > 12102102006-04-20 12:49:03.92 state INITIAL > COMPLETE > 22102102006-04-20 12:49:03.74 flag X Y > By looking at the timestamps, the second update started BEFORE the first > update even though the second update cannot occur if the state hasn't > been changed and committed by the first one! How is the "when" column determined? You did not show it in your SQL commands. If it's being driven off now() or CURRENT_TIMESTAMP, then the above isn't all that surprising, because the value is the time of transaction start not the time at which the update was made. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] A few questions about ltree
We've been experimenting with a table containing a branch 'a', 'a.b' and
'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"ltree_test_pkey" for table "ltree_test"
CREATE TABLE
SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
SQL> select * from ltree_test;
path
---
a
a.b.c
(2 rows)
Is there some obvious/easy way to prevent this?
Sorry, only by using triggers on insert/delete/update.
If it was a possible to use function in foreign key then it might looks as
create table foo (
path ltree not null
);
insert into foo values (''); -- root of tree, but it unremovable...
create unique index path_foo_idx on foo ( path ); -- BTree index for constraint
alter table foo add foreign key subpath( path, 0, -1) references foo( path )
deferrable initially deferred,;
But it's impossible...
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Triggers and Transactions
On Thursday 20 April 2006 12:25 pm, "Chris Coleman" <[EMAIL PROTECTED]> thus communicated: --> Hi, --> --> I have a question about how much of a trigger is in a transaction. --> I've read the docs and googled around but can't seem to find a --> concrete answer. --> --> I have two triggers that are designed to work together, one is a --> before trigger and one is an after. If the before trigger succeeds --> then it will have made some changes to one of my tables, however if --> the after one fails some how (elog(ERROR, "")? then I would like to --> rollback the changes of the before one as well as any made by the --> after one too. --> --> Is this possible? --> --> Many thanks --> --> Chris Coleman. --> --> ---(end of broadcast)--- --> TIP 6: explain analyze is your friend --> The whole process is in a transaction and all of it will be rolled back. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Odd transaction timestamp sequence issue
On Fri, Apr 21, 2006 at 09:43:55AM -0500, Jeff Amiel wrote: > PostgreSQL 8.1.2 on i386-portbld-freebsd6.0, compiled by GCC cc (GCC) > 3.4.4 [FreeBSD] 20050518 > > We have triggers on each of our tables that create audit table entries > on each insert/update/delete. > The audit table (in addition to containing information about the change > that was made) contains a timestamp field and a serial as the primary key. > The timestamp is generated by calling now() inside the audit trigger, so > should contain the timestamp of when the transaction (that yielded the > insert/update/delete) began. now() returns the same time throughout the transaction i.e. the transaction start time. If you want a time independant of transaction status, perhaps you want timeofday(). Check the docs for the specifics. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Odd transaction timestamp sequence issue
it is done using now() But what I don't understand is how the transaction that started first could 'see' the record that hadn't been changed yet by the initial update to 'COMPLETE'? I thought: "Each transaction sees a snapshot (database version) as of its starttime, no matter what other transactions are doing while it runs" How is the "when" column determined? You did not show it in your SQL commands. If it's being driven off now() or CURRENT_TIMESTAMP, then the above isn't all that surprising, because the value is the time of transaction start not the time at which the update was made. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setup for large database
On Apr 20, 2006, at 9:02 PM, [EMAIL PROTECTED] wrote: 1) How anal should I be about my hardware setup? I have about 15 300GB 10K RPM SCSI drives, 4 of which I can directly attach to the server and the rest one the FC array. Should I just put the OS and transaction logs on the direct attached storage and and then RAID10 the rest of them and be done, or would I significantly benefit from separating out the indexes and partitioning across tablespaces across drives? Would RAID5 across 10+ drives yield acceptable performance numbers? If you have that many drives, make one RAID1 pair for the OS and a dedicated RAID1 pair for the pg_xlog. I'd put the rest into a RAID10 if you're not willing to do experimentation first... If I had the time I'd put a RAID5 on it and simulate the expected load on it, then compare the RAID10 under same load. It depends a lot on your RAID controller, how much cache (battery backed!!!) it has, and your usage patterns. The pgsql-performance list may have more helpful responses. As for partitioning based on octet, you should look at your distribution of addresses and decide if it scatters the data evenly enough for you. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] A few questions about ltree
Teodor Sigaev wrote:
We've been experimenting with a table containing a branch 'a', 'a.b'
and 'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
Sorry, only by using triggers on insert/delete/update.
Aw, that's a shame... Well, I do have quite a bit of experience writing
triggers (been working on an avalanche of cascading triggers - works
wonderfully), so that's not really a problem.
It does make me wonder though, the foreign key reference was created ok,
but does it do anything this way? I suspect it does, this isn't MySQL
after all :P
If it was a possible to use function in foreign key then it might looks as
create table foo (
path ltree not null
);
insert into foo values (''); -- root of tree, but it unremovable...
Is it really necessary to insert an 'empty' record for the root node?
The 'a' record from my experiments seems to be quite suited for the
task, unless I'm missing something.
alter table foo add foreign key subpath( path, 0, -1) references foo(
path )
deferrable initially deferred,;
IIRC, you can define equality for custom types depending on the
direction of the comparison. Isn't something like that possible for
foreign keys? You'd be able to check whether the left hand of the
comparison is a parent of the right hand and vice versa. That'd be just
what we need...
I must be missing something, you've obviously put a lot of thought in
ltree. Maybe it'll be possible with a future version of PostgreSQL :)
Regards,
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Odd transaction timestamp sequence issue
Jeff Amiel <[EMAIL PROTECTED]> writes: > I thought: > "Each transaction sees a snapshot (database version) as of its > starttime, no matter what other transactions are doing while it runs" That's a correct statement in SERIALIZABLE mode, but in the default READ COMMITTED mode, it's more complicated --- a new snapshot is taken for each command within a transaction. See the docs. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] A few questions about ltree
On Fri, 21 Apr 2006, Alban Hertroys wrote:
> Teodor Sigaev wrote:
> >> Maybe something along the lines of the following is possible?:
> >
> > Exact, it's for what ltree was developed.
>
> Cool, looks like it is what I need then.
>
> > contrib_regression=# select 'a.b.c' <@ 'a.b'::ltree;
> > ?column?
> > --
> > t
> > (1 row)
>
> How would you use this to constrain a foreign key?
>
> We've been experimenting with a table containing a branch 'a', 'a.b' and
> 'a.b.c', but deleting 'a.b' didn't cause a constraint violation.
>
> SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
> ltree_test(path));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "ltree_test_pkey" for table "ltree_test"
> CREATE TABLE
> SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
> INSERT 84117368 1
> SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
> INSERT 84117369 1
> SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
> INSERT 84117370 1
> SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
> DELETE 1
I'm not sure why you expect this to error. Any row that would reference
a.b would be removed by the delete AFAICS.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] A few questions about ltree
Stephan Szabo wrote:
SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
ltree_test(path));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"ltree_test_pkey" for table "ltree_test"
CREATE TABLE
SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
INSERT 84117368 1
SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
INSERT 84117369 1
SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
INSERT 84117370 1
SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
DELETE 1
I'm not sure why you expect this to error. Any row that would reference
a.b would be removed by the delete AFAICS.
Nope, there's no ON DELETE CASCADE on the FK, and RESTRICT is the
default (thankfully).
But the FK constraint apparently doesn't get triggered by the delete, so
neither case matters much here.
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] IDT timezone
What is the best way to handle timestamps with a timezone of IDT? I see that I could modify src/backend/utils/adt/datetime.c to support IDT, but what is the best solution? Basically, I have an application where I'm grabbing the timezone from the output of date(1) and appending that to a timestamp before I do an INSERT. In the situations where the timezone is IDT, the INSERT fails. -- Brandon ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] A few questions about ltree
Is it really necessary to insert an 'empty' record for the root node? The 'a' record from my experiments seems to be quite suited for the task, unless I'm missing something. The root should be and it will be unremovable, because of foreign keys. But it can be, of course, not empty. alter table foo add foreign key subpath( path, 0, -1) references foo( path ) deferrable initially deferred,; IIRC, you can define equality for custom types depending on the direction of the comparison. Isn't something like that possible for foreign keys? You'd be able to check whether the left hand of the comparison is a parent of the right hand and vice versa. That'd be just what we need... Sorry, I don't know. I don't think that pgsql allows to use particular operator for foreign key... I must be missing something, you've obviously put a lot of thought in ltree. Maybe it'll be possible with a future version of PostgreSQL :) Make a patch to allow function in FK :) -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A few questions about ltree
On Fri, 21 Apr 2006, Alban Hertroys wrote:
> Stephan Szabo wrote:
> >>SQL> CREATE TABLE ltree_test (path ltree PRIMARY KEY REFERENCES
> >>ltree_test(path));
> >>NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> >>"ltree_test_pkey" for table "ltree_test"
> >>CREATE TABLE
> >>SQL> INSERT INTO ltree_test VALUES ('a'::ltree);
> >>INSERT 84117368 1
> >>SQL> INSERT INTO ltree_test VALUES ('a.b'::ltree);
> >>INSERT 84117369 1
> >>SQL> INSERT INTO ltree_test VALUES ('a.b.c'::ltree);
> >>INSERT 84117370 1
> >>SQL> DELETE FROM ltree_test WHERE path = 'a.b'::ltree;
> >>DELETE 1
> >
> > I'm not sure why you expect this to error. Any row that would reference
> > a.b would be removed by the delete AFAICS.
>
> Nope, there's no ON DELETE CASCADE on the FK, and RESTRICT is the
> default (thankfully).
The only row that matches 'a.b' that I see in the above is the second
insert which is also the row that is deleted in the delete. And since the
constraint uses equality, any row that matches path='a.b' is a target of
the delete because it's the same operator.
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] IDT timezone
"Brandon Metcalf" <[EMAIL PROTECTED]> writes: > What is the best way to handle timestamps with a timezone of IDT? I > see that I could modify src/backend/utils/adt/datetime.c to support > IDT, but what is the best solution? Right at the moment, that's the only solution. We've wanted for awhile to push the timezone abbreviations out to a configuration file so that people could muck with them without rebuilding the server ... but it's never gotten to the top of anyone's to-do list. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] IDT timezone
t == [EMAIL PROTECTED] writes: t> "Brandon Metcalf" <[EMAIL PROTECTED]> writes: t> > What is the best way to handle timestamps with a timezone of IDT? I t> > see that I could modify src/backend/utils/adt/datetime.c to support t> > IDT, but what is the best solution? t> Right at the moment, that's the only solution. We've wanted for awhile t> to push the timezone abbreviations out to a configuration file so that t> people could muck with them without rebuilding the server ... but it's t> never gotten to the top of anyone's to-do list. OK. Thanks. -- Brandon ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Setup for large database
On Fri, Apr 21, 2006 at 11:34:00AM -0400, Vivek Khera wrote: > As for partitioning based on octet, you should look at your > distribution of addresses and decide if it scatters the data evenly > enough for you. A much more important question: how will you be querying the data? Partitioning is not a magic-bullet to performance, and when done incorrectly it can end up hurting. In this case, if the OP will be querying mostly on things that fit within a class A, then partitioning on the first octet probably makes a lot of sense. In fact, partitioning on the first two octets might make a lot of sense, so long as there's very littly querying across partitions. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Setup for large database
On Thu, Apr 20, 2006 at 06:02:17PM -0700, [EMAIL PROTECTED] wrote: > 1) How anal should I be about my hardware setup? I have about 15 300GB > 10K RPM SCSI drives, 4 of which I can directly attach to the server and > the rest one the FC array. Should I just put the OS and transaction > logs on the direct attached storage and and then RAID10 the rest of > them and be done, or would I significantly benefit from separating out > the indexes and partitioning across tablespaces across drives? Would > RAID5 across 10+ drives yield acceptable performance numbers? My experience is more OLTP than OLAP, but for a warehouse envirenment RAID5 can be a good solution since there's typically not a lot of updating going on. I've yet to see much gain from moving pg_xlog onto it's own seperate set of drives; there's usually not enough traffic from the OS to justify it. But it is possible that you could end up generating enough WAL traffic that pg_xlog would become a performance limiter on only 2 drives, though I suspect you'd have to have over 20-30 drives for data before that happened. > 3) I've currently installed RHEL4 AS for my OS, which I am very > comfortable with. I was going to go with EXT3 on everything (noatime) > ... sound good? There's a data=writeback option for ext3 that can make a big performance difference. > 2) Assuming that my data is roughly evenly distributed among IP > addresses, I figured that a naive partitioning based on the first octet See my other reply... > 3) I guess I don't quite understand Bizgres. At the moment, it seems > to be just a development beta of Postgres ... is this true? I realize > that the focus is on BI/ETL stuff, but the current improvements seem to > benefit Postgres as a whole. Is there currently or can you imagine a > case where a feature in Bizgres won't get integrated into Postgres? > How significant is the fork between Bizgres and Postgres? I've also > considered taking a look at Bizgres MPP. I know that its the wrong > forum, but any comments? You'd probably be better off asking on a bizgres list... > 4) Not to start any sort of flame war, but my company has an Oracle > license and there are a bunch of people wanting me to go that way. > I've been doing just fine with Postgres at the moment and am quite > comfortable with it, but am being pressured to go with our Oracle > license. Cost (and prejudices) aside, do you think it would be wise to > go with Oracle to begin with, considering the size of the database that > I'm planning? There's certainly people out there running multi-terrabyte databases on PostgreSQL. Unless there's a sound technical reason to switch, I'd stick with PostgreSQL, especially because migrating to Oracle from PostgreSQL is fairly easy. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] temp tables problem
On Thu, Apr 20, 2006 at 09:21:27PM -0700, [EMAIL PROTECTED] wrote: > just to add on that, there is only one user for the db. so both > application accesses use the same db username and password. the web app > automatically logs into the db in with the one username and password > for both remote and local access. Users don't matter at all for temp tables. Temp tables are per *session*, so as soon as you come in from a different connection it's a different set of temp tables. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Daylight Savings Time
Hello List:
I need to know if there is a convienient way of establishing whether DST is
active within a function dealing with adjusting timestamps to other time
zones. The problem is that if I have the following timestamp:
'04/21/2006 17:05 EDT'
and I use the timezone() function in the following manner:
return (timezone ('CST', '04/21/2006 17:05 EDT')
I get a two hour difference in time. Note that neither of the two arguments
are hard coded as this example. The "CST" value is stored in the customer
profile because that is their time zone and the timestamp is generated from
argeuments passed into the function.
This is:
rnd=# select version();
version
--
PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
20030502 (Red Hat Linux 3.2.3-49)
TIA
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Daylight Savings Time
Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> I need to know if there is a convienient way of establishing whether DST is
> active within a function dealing with adjusting timestamps to other time
> zones. The problem is that if I have the following timestamp:
> '04/21/2006 17:05 EDT'
> and I use the timezone() function in the following manner:
> return (timezone ('CST', '04/21/2006 17:05 EDT')
> I get a two hour difference in time.
Perhaps you should be using a DST-aware timezone specification? Since
8.1 you could do
regression=# select timezone ('CST6CDT', '04/21/2006 17:05 EDT'::timestamptz);
timezone
-
2006-04-21 16:05:00
(1 row)
regards, tom lane
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Daylight Savings Time
On Friday 21 April 2006 05:47 pm, Tom Lane <[EMAIL PROTECTED]> thus
communicated:
--> Terry Lee Tucker <[EMAIL PROTECTED]> writes:
--> > I need to know if there is a convienient way of establishing whether
DST is --> > active within a function dealing with adjusting timestamps to
other time --> > zones. The problem is that if I have the following
timestamp:
--> > '04/21/2006 17:05 EDT'
--> > and I use the timezone() function in the following manner:
--> > return (timezone ('CST', '04/21/2006 17:05 EDT')
--> > I get a two hour difference in time.
-->
--> Perhaps you should be using a DST-aware timezone specification? Since
--> 8.1 you could do
-->
--> regression=# select timezone ('CST6CDT', '04/21/2006 17:05
EDT'::timestamptz); --> timezone
--> -
--> 2006-04-21 16:05:00
--> (1 row)
-->
-->
--> regards, tom lane
-->
Thanks for the reply Tom. We will be upgrading to version 8.x hopefully in
August. I can implement a work around until then. So, when we can upgrade, we
will change the timezone specification in the customer profiles to the
DST-aware specification, and we will be set.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Debian package for freeradius_postgresql module
Tyler MacDonald wrote: > I see this continuining to be a problem for the postgresql community > given how many GPLed projects use libpq. freeradius might be fixable with a > change in their license, but for postgresql to continue to be reasonably > usable by GPLed projects, either OpenSSL's license needs to change, or we > need to support an alternative secure socket api like GnuTLS. > > GnuTLS is LGPL, which isn't quite as liberal as postgresql's > license, but should still be ubiqutous enough to be worthwhile. As PostgreSQL is participating in Google Summer of Code 2006, perhaps the GnuTLS support could be a student's project. -- Nicolas Baradakis ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] COPY command documentation
I have added the following patch for 8.2 that suggests using E'' strings and doubling backslashes used as path separators, and backpatched the later suggestion to 8.1. Thanks. --- Oisin Glynn wrote: > I have driven myself to distraction for the last 30 minutes trying to > get COPY to work on Windows XP. The Unix style c:/afolder/afile > instead of c:\afolder\afile was a desperation attempt. > > I had tried all sorts of double slashes \\ putting the whole path in > quotes basically all sorts of foolishness. I would suggest the there > should be a Windows example(s) in the documents as well as a *NIX style > one(s) where necessary. Did I miss this somewhere or should I put a > comment on the doc or what can I do to help the next Windows user. > > Oisin > > > P.S. > I just discovered that the comments from 8.0 had the answer I was > looking for but these comments are not in the 8.1 docs. Should the > comments be rolled forward as new versions are created? Or if valid > comments added to the docs themselves? > > http://www.postgresql.org/docs/8.1/interactive/sql-copy.html > > http://www.postgresql.org/docs/8.0/interactive/sql-copy.html > > Now happily using COPY, > Oisin > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/copy.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/copy.sgml,v retrieving revision 1.73 diff -c -c -r1.73 copy.sgml *** doc/src/sgml/ref/copy.sgml 3 Mar 2006 19:54:10 - 1.73 --- doc/src/sgml/ref/copy.sgml 22 Apr 2006 02:58:39 - *** *** 106,112 filename ! The absolute path name of the input or output file. --- 106,114 filename ! The absolute path name of the input or output file. Windows users ! might need to use an E'' string and double backslashes ! used as path separators. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Unexplained lock creating table
I have a C application (libpq) that uses partitioning. I create parent tables 'header' and 'detail'. The application reads opens multiple connections, creates the child tables, and uses COPY to import the data: open connection 1 begin create table header_1 COPY into header_1 open connection 2 begin create table header_2 COPY into header_2 open connection 3 begin create table header_3 COPY into header_3 [ potentially more connections ] end copy 1 end copy 2 end copy 3 commit 1 commit 2 commit 3 After the database is initialized, I run the application. It creates table header_1 and initiates the copy (transaction still pending). However, when it tries to create table header_2, it hangs. A ps shows postgres: test trace [local] COPY postgres: test trace [local] CREATE TABLE waiting However, if I force table header_1 to be created outside the COPY transaction (using psql, manually committing the transaction from within gdb, etc.), then run the application, it works regardless of the number of open connections/transactions. I then drop all the child tables, leaving the parent table, and rerun the application. It again works for all connections. The problem occurs only when the database has been freshly initialized and no child table has ever existed. I confirm this by: 1. Reinitialize database. 2. Run application. Verify hang occurs. I can rerun step 2 any number of times and it continues to hang. 3. Create header_dummy using psql 4. Drop header_dummy 5. Run application - works. I can repeat this with the 'detail' table. It is 100% reproducible. What's going on? Wes ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
