Steve Crawford | 1 Mar 01:06 2006

Re: plsql / time statement

Daniel Caune wrote:
> Hi,
> 
>  
> 
> Is there any option to set so that psql provides the execution time of 
> each SQL statement executed?

\timing (either as a manual command or as a default in your .psqlrc file).

Cheers,
Steve

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

               http://archives.postgresql.org

Ken Hill | 1 Mar 02:26 2006

Re: Slow update SQL

bungsu,

That does make the query work a bit faster. Thanks!

On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote:
Hi Ken, I used to have the same problem. In my computer, using date_part in WHERE clause will always slow down the system when the table has plenty of rows. My suggestion is try to change the query to: ... WHERE dxdate >= '2001-01-01'; I hope this helps. Regards, bungsu ----- Original Message ----- From: Ken Hill To: Postgres Help Sent: Tuesday, February 14, 2006 8:48 AM Subject: [SQL] Slow update SQL I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table: VACUUM ANALYZE ncccr10; SELECT count(*) FROM ncccr10; count -------- 611564 (1 row) When I try to analyze the query plan with: EXPLAIN ANALYZE UPDATE ncccr10 SET key = facilityno||'-'|| lastname||'-'|| sex||'-'|| ssno||'-'|| birthdate||'-'|| primarysit||'-'|| dxdate||'-'|| morphology3 WHERE date_part('year',dxdate) > '2000'; The query just never finishes (even 1 hour later). The colum key100 is indexed, and I'm setting the value of this column from other columns. Why is this so slow?
Stephan Szabo | 1 Mar 03:53 2006

Re: how to add primary key to existing table with multiple

On Thu, 23 Feb 2006, Daniel Joo wrote:

> I am trying to add another primary key to an existing table with two
> other primary keys.  I got the following error when I tried this
> command:

You only can have one primary key.  The table you gave has a single
primary key with two columns.  Are you trying to guarantee that expid
along is unique or that the set (probeid, tissueid, expid) is unique?

You can make expid UNIQUE and NOT NULL for the first case which is similar
to a primary key (although it's not a default foreign key target).  In the
second you can drop the current constraint and add one on the three
columns.

> Indexes:
>
>     "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid)
>     "extprobe2tissue_probeid" btree (probeid)
>     "extprobe2tissue_tissueid" btree (tissueid)

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

Michael Glaesemann | 1 Mar 04:05 2006
Picon

Re: pg reserved words


On Feb 23, 2006, at 0:27 , Irina Gadassik wrote:

> I am trying to create a table freeze and it seems "freeze" is a  
> reserved word, but I don't see it in
> the list. Also in a different table I cann't create a column freeze.

http://www.postgresql.org/docs/current/interactive/sql-keywords- 
appendix.html

I see FREEZE right between FREE and FROM. It's not an SQL keyword,  
but it is used in PostgreSQL withe VACUUM command.

http://www.postgresql.org/docs/current/interactive/sql-vacuum.html

Hope this helps.

Michael Glaesemann
grzm myrealbox com

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

AKHILESH GUPTA | 1 Mar 05:20 2006
Picon

regarding grant option

hi all....
this is akhilesh from india.i just want to ask one thing regarding grant operation??
i am using pgsql 8.0 on ubuntu 5.10 linux.
just tell me the procedure that how can i grant all permissions for a database to any of the other user???
i am using :-
:->> grant all privileges on database <db_name> to <user_name>;
GRANT
:->> grant all ON DATABASE <db_name> to <user_name>;
GRANT

and the query executes succesfully.
but at the other user end, she is not able to access the database given in the above query.
here i have to grant permissions to that user individually for each and every table by using:
:->> grant ALL ON <tab_name> to <user_name>;
GRANT

and all the permissions are granted to that user for that particular table.

i want same results for the entire database.................
plz help me it's urgent

--
Thanks & Regards,
Akhilesh
DAV Institute of Management
Faridabad(Haryana)
GSM:-(+919891606064)
          (+911744293789)

"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"

Mauricio Fernandez A. | 1 Mar 11:17 2006

Re: Slow update SQL

You can try this too:

...
WHERE EXTRACT(YEAR FROM dxdate::Date) > 2000



Mauricio Fernández A.
Ingeniero de Sistemas
Universidad Autónoma de Manizales (Colombia)



-----Mensaje original-----
De: pgsql-sql-owner <at> postgresql.org
[mailto:pgsql-sql-owner <at> postgresql.org]En nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para: ken <at> scottshill.com; Postgres Help
Asunto: Re: [SQL] Slow update SQL


Hi Ken,

I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.

My suggestion is try to change the query to:
... WHERE dxdate >= '2001-01-01';

I hope this helps.

Regards,
bungsu

----- Original Message -----
From: Ken Hill
To: Postgres Help
Sent: Tuesday, February 14, 2006 8:48 AM
Subject: [SQL] Slow update SQL


I'm experiencing a very slow query. The table contains 611,564 rows of data.
I vaccumed the table:

VACUUM ANALYZE ncccr10;

SELECT count(*) FROM ncccr10;
count
--------
611564
(1 row)

When I try to analyze the query plan with:

EXPLAIN ANALYZE
UPDATE ncccr10
SET key = facilityno||'-'||
lastname||'-'||
sex||'-'||
ssno||'-'||
birthdate||'-'||
primarysit||'-'||
dxdate||'-'||
morphology3
WHERE date_part('year',dxdate) > '2000';

The query just never finishes (even 1 hour later). The colum key100 is
indexed, and I'm setting the value of this
column from other columns. Why is this so slow?


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

Mauricio Fernandez A. | 1 Mar 11:17 2006

Re: how to add primary key to existing table with multiple primary keys

Your table has a primary key yet, so you can´t add a second primary key.  You only can define one pk, however it can be composed as your table has (it now has 2 columns), so I think you need to drop the current pk and add again the new pk with your 3 columns (probeid, tissueid, expid).
 
 

Mauricio Fernández A.
Ingeniero de Sistemas
Universidad Autónoma de Manizales (Colombia)

-----Mensaje original-----
De: pgsql-sql-owner <at> postgresql.org [mailto:pgsql-sql-owner <at> postgresql.org]En nombre de Daniel Joo
Enviado el: viernes, 24 febrero, 2006 2:45
Para: pgsql-sql <at> postgresql.org
Asunto: [SQL] how to add primary key to existing table with multiple primary keys

Hi there,

 

I am trying to add another primary key to an existing table with two other primary keys.  I got the following error when I tried this command:

 

alter table extprobe2tissue ADD primary key (expid);

ERROR:  multiple primary keys for table "extprobe2tissue" are not allowed

 

The details of the extprobe2tissue table is:

 

\d extprobe2tissue;

          Table "public.extprobe2tissue"

   Column    |          Type          | Modifiers

-------------+------------------------+-----------

 probeid     | integer                | not null

 tissueid    | integer                | not null

 value       | double precision       | not null

 entrydate   | date                   | not null

 description | character varying(200) |

 qval        | double precision       |

 expid       | integer                | not null

Indexes:

    "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid)

    "extprobe2tissue_probeid" btree (probeid)

    "extprobe2tissue_tissueid" btree (tissueid)

Foreign-key constraints:

    "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid)

    "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid)

 

Any suggestion would be much appreciated.

 

Thanks! 

Florian Weimer | 1 Mar 08:19 2006
Picon

Re: Index to support LIKE '%suffix' queries

* Alvaro Herrera:

>> > Florian Weimer <fw <at> deneb.enyo.de> writes:
>> >> Is it possible to create an index to support queries of the form
>> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
>> >> 'prefix%', which I also need)?
>
> It is possible to create a functional index on the reverse of the
> string.

Okay.  Is there a predefined reverse function?  I couldn't find one
and I'm wondering if I just missed it.

> Whether or not this beats tsearch2 is something you should investigate ...

It's also possible that for this type of query, sequential scans are
good enough.  I forgot that they are quite fast.

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

               http://archives.postgresql.org

A. Kretschmer | 1 Mar 12:33 2006

Re: Index to support LIKE '%suffix' queries

am  01.03.2006, um  8:19:40 +0100 mailte Florian Weimer folgendes:
> * Alvaro Herrera:
> 
> >> > Florian Weimer <fw <at> deneb.enyo.de> writes:
> >> >> Is it possible to create an index to support queries of the form
> >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE
> >> >> 'prefix%', which I also need)?
> >
> > It is possible to create a functional index on the reverse of the
> > string.
> 
> Okay.  Is there a predefined reverse function?  I couldn't find one
> and I'm wondering if I just missed it.

simple to write one, http://a-kretschmer.de/diverses.shtml

HTH, Andreas
--

-- 
Andreas Kretschmer    (Kontakt: siehe Header)
Heynitz:  035242/47215,      D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    === 

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

               http://archives.postgresql.org

Simon Kinsella | 1 Mar 14:30 2006
Picon

Help with trigger that updates a row prior to a potentially aborted deletion?

Hi all,

I have a situation where a DELETE operation may (correctly) fail due to a
RESTRICT FK constraint. If so, I need to set a flag in the row indicating
that it has been marked for deletion so that I can disregarded in subsequent
queries.

I'm trying to achieve this with a BEFORE DELETE trigger, which would set the
'marked_for_deletion' field to TRUE before attempting the delete proper.
Then if the DELETE fails the row would still be tagged and I'd be happy.
Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error
the entire operation is rolled back, including the BEFORE triggers, leaving
me back where I started.

Is there anyway to get the DELETE operation, or more specifically the FK
constraint, to fail silently, i.e. to skip over the failed operation and not
throw an exception? I'm really racking my brains on this one but not really
getting anywhere!

I have sample data defs to play with if that would be helpful.

Thanks in advance!

Simon Kinsella

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

               http://archives.postgresql.org


Gmane