[SQL] SQL Query for Top Down fetching of childs

Dear Friends,   Postgres 7.3.4 on RH Linux 7.2.   I need a query to get the Childs of a parent (Top down analysis). Need to list all the departments(Childs) of a parent organization. The table structure is   CREATE TABLE organization(  entity_id int4,  entity_name varchar(100),  entity_type

[SQL] LEFT JOIN on one and/or another column

Hi. I have a table with two foreign keys (1 field each), like in id| serial ext_key_original | integer ext_key_exception | integer They mean different things, as one refers to a typical value, and the other one refers to an exception that applies for that tuple. Each key ref

Re: [SQL] Question about isolation

Samuel Tardieu <[EMAIL PROTECTED]> writes: >>> Do I have the guarantee that, in any event, rows deleted from >>> table t by the delete won't reappear in the select result? >> >> i do not think you have that guarantee in READ COMMITTED mode >> because there is a slight possibility another backend s

Re: [SQL] Question about isolation

> "Chester" == Chester Kustarz <[EMAIL PROTECTED]> writes: > On Wed, 28 Jan 2004, Chester Kustarz wrote: >> On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I >> call an embedded function in Pl/PgSQL, in which > I have: >> > >> > delete from t where condition; > for e in select

Re: [SQL] Question about isolation

On Wed, 28 Jan 2004, Chester Kustarz wrote: > On Wed, 28 Jan 2004, Samuel Tardieu wrote: > > If in a transaction I call an embedded function in Pl/PgSQL, in which > > I have: > > > > delete from t where condition; > > for e in select distinct on (f) * from t where ... loop > > ... > > en

Re: [SQL] Question about isolation

On Wed, 28 Jan 2004, Samuel Tardieu wrote: > If in a transaction I call an embedded function in Pl/PgSQL, in which > I have: > > delete from t where condition; > for e in select distinct on (f) * from t where ... loop > ... > end loop; > > Do I have the guarantee that, in any event, rows

Re: [SQL] Aggregate function error in 7.4

Tom Lane wrote: j knight <[EMAIL PROTECTED]> writes: [ query with GROUP BY on a FULL JOIN USING column ] psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY clause or be used in an aggregate function Argh. This is the result of a thinko in an optimization added in 7.4. The

[SQL] Question about isolation

If in a transaction I call an embedded function in Pl/PgSQL, in which I have: delete from t where condition; for e in select distinct on (f) * from t where ... loop ... end loop; Do I have the guarantee that, in any event, rows deleted from table t by the delete won't reappear in the s

[SQL] managing users in postgresql 7.4.1

hi Can you tell me how to disconnect some users from command line. I mean I want to disconnect some user from database without restarting postmaster   when i do something like this   select * from pg_stat_activity   I had information but how can I use this to disconnect users from databases

Re: [SQL] postgres timeout.

Forgot to mention that I use postgres 7.3.4 Stef mentioned : => Hi all , => => I'm trying to find out if there is a specific setting => to make transactions time out faster in a scenario => where there's an update on a table in a transaction => block, and another update process tries to update

Re: [SQL] postgres timeout. [SOLVED]

Hi all, It seems I always find a solution just after panicking a little bit. Anyway, I found that statement_timeout solved my problem. When I tested it earlier, I actually made an error, and skipped it as a possible solution. Cheers Stef Stef mentioned : => Forgot to mention that I use postgr

Re: [SQL] postgres timeout.

Stef <[EMAIL PROTECTED]> writes: > I'm trying to find out if there is a specific setting > to make transactions time out faster in a scenario > where there's an update on a table in a transaction > block, and another update process tries to update > the same column. > It looks like the second pr

[SQL] postgres timeout.

Hi all , I'm trying to find out if there is a specific setting to make transactions time out faster in a scenario where there's an update on a table in a transaction block, and another update process tries to update the same column. It looks like the second process will wait until you end the t

[SQL] limit 1 and functional indexes

Hi, Postgres choses the wrong index when I add limit 1 to the query. This should not affect the index chosen. I read that functional indexes are sometimes not chosen correctly by optimizer. Is there anything I can do to always use the functional index in the following queries? Query with limi

Re: [SQL] Aggregate function error in 7.4

j knight <[EMAIL PROTECTED]> writes: > [ query with GROUP BY on a FULL JOIN USING column ] > psql:pgsql.dump:301: ERROR: column "r.day" must appear in the GROUP BY > clause or be used in an aggregate function Argh. This is the result of a thinko in an optimization added in 7.4. The patch is att

Re: [SQL] Query TIME ZONE

Thanks Tom it worked for me... yes I got it wrong.. thanks for the correction. thanks a lot.. -- Raman - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Raman" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, January 28, 2004 11:28 AM Subject: Re: [SQL] Query TI