Re: [SQL] vacuum analyze slows sql query
have you reindexes your tables. When I was running 7.1.4, I ran a vacuum and reindex
nightly. Otherwise your index
files will keep getting bigger and bigger (this has been fixed in 7.4).
Jim
-- Original Message ---
From: patrick ~ <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Tue, 2 Nov 2004 18:50:31 -0800 (PST)
Subject: [SQL] vacuum analyze slows sql query
> Greetings pgsql-sql,
>
> I have a very strange problem. Our production database is a fair
> sized db, structure wise, and quite huge data wise. We have a web/php
> based UI for our customer to manage the data in our application db.
> The customer complains that the UI is sluggish accessing certain
> pages and completely times-out on certain other pages.
>
> We have a nightly "garbage collection" process that runs and purges
> any old data. After this process a 'vacuum analyze' is kicked off
> (regardless of whether or not any data was actually purged).
>
> At this point I should mention that our customer sites are running
> PostgreSQL 7.1.3; however, I am able to reproduce the issue on 7.4.2.
> If it at all matters, customer is running the db on a Dell PowerEdge
> 2550 equiped with 1gig of ram. My personal test box is a 700Mhz Intel
> with 512mb ram.
>
> I have in the past made modifications to our SQL statements to make
> queries more efficient. At this point I have given up and set out
> to strip down our database and data enough to be able to post to
> the list and ask for help from more qualified SQL experts.
>
> In the process of "stripping down" our database I noticed some very
> strange behavior which I could not explain. I started to reformulate
> my original to-be post to to the list to ask assistence in explaining
> this strange behavior I was observing. Next I noticed yet another
> strange issue with PostgreSQL.
>
> I noticed that a freshly created db with freshly inserted data (from
> a previous pg_dump) would result in quite fast results. However,
> after running 'vacuum analyze' the very same query slowed down about
> 1250x (Time: 1080688.921 ms vs Time: 864.522 ms).
>
> Following is a paste from a psql shell after a dropdb, createdb
> and populate db. The query is fast. I next run 'explain' and 'explain
> verbose' on the query. Then you see a 'vacuum analyze' followed by
> the the 'explain', 'explain verbose' and lastly the query again which
> is now extremely slow!
>
> -- begin
> orig=# select offer_id, pkk_offer_has_pending_purch( offer_id ) from pkk_offer
> ;
>
> (618 rows)
>
> Time: 864.522 ms
> orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
> pkk_offer ;
> QUERY PLAN
> -
> Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
> (1 row)
>
> Time: 24.251 ms
> orig=# explain verbose select offer_id, pkk_offer_has_pending_purch( offer_id )
> from pkk_offer ;
>
> QUERY PLAN
> -
> {SEQSCAN
> :startup_cost 0.00
> :total_cost 22.50
> :plan_rows 1000
> :plan_width 4
> :targetlist (
>{TARGETENTRY
>:resdom
> {RESDOM
> :resno 1
> :restype 23
> :restypmod -1
> :resname offer_id
> :ressortgroupref 0
> :resorigtbl 34965071
> :resorigcol 1
> :resjunk false
> }
>
>:expr
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
>}
>
>{TARGETENTRY
>:resdom
> {RESDOM
> :resno 2
> :restype 16
> :restypmod -1
> :resname pkk_offer_has_pending_purch
> :ressortgroupref 0
> :resorigtbl 0
> :resorigcol 0
> :resjunk false
> }
>
>:expr
> {FUNCEXPR
> :funcid 34965096
> :funcresulttype 16
> :funcretset false
> :funcformat 0
> :args (
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
> )
> }
>}
> )
>
> :qual <>
> :lefttree <>
> :righttree <>
> :initPlan <>
> :extParam ()
>
> :allParam ()
>
> :nParamExec 0
> :scanrelid 1
> }
>
> Seq Scan on pkk_offer (cost=0.00..22.50 rows=1000 width=4)
> (78 rows)
>
> Time: 10.915 ms
> orig=# vacuum analyze ;
> VACUUM
> Time: 504701.795 ms
> orig=# explain select offer_id, pkk_offer_has_pending_purch( offer_id ) from
> pkk_offer ;
> QUERY PLAN
> -
Re: [ADMIN] [SQL] Postgres schema comparison.
Stef, I use dblink to attach to both databases and query pg_namespace, pg_class, pg_attribute ... to get the diffs. See attached as an example. look for the dblink_connect lines to specify your database. You will need to install contrib/dblink. I used this with 7.4.X series and have NOT tested yet with 8.0.X. You can adjust the output to fit your needs. Jim -- Original Message --- From: Stef <[EMAIL PROTECTED]> To: Tom Lane <[EMAIL PROTECTED]> Cc: pgsql-ADMIN@postgresql.org, pgsql-sql@postgresql.org Sent: Mon, 7 Mar 2005 17:31:55 +0200 Subject: Re: [ADMIN] [SQL] Postgres schema comparison. > Tom Lane mentioned : > => > The problem I have with this, is that I have to run the command per > table, > => > => Why? > => > => If the problem is varying order of table declarations, try 8.0's > => pg_dump. > > Yes, this will solve the global schema check, but I will still need to split > it into "per table" dumps , to do "per table" comparisons. > > Kind Regards > Stefan > > ---(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 --- End of Original Message --- db_compare.sql Description: Binary data ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
give this a try
CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
RETURNS VARCHAR
AS '
DECLARE
_pid ALIAS FOR $1;
c text;
BEGIN
SELECT decrypt(crypted_content, decode(''password''::text,
''escape''::text), ''aes''::text) into c
FROM crypto
WHERE pid = _pid;
RETURN c;
END;
' LANGUAGE 'plpgsql';
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 08:41:34 -0800
Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> Hello there,
>
> What's the preferred and most efficient way to obtain PGCrypto encrypted
> data from a plpgsql function?
>
> 1. Imagine the following simple table:
> CREATE TABLE crypto (
> pid SERIAL PRIMARY KEY,
> title VARCHAR(50),
> crypted_content BYTEA
> );
>
> 2. Now insert the following 3 rows of data:
>
> INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE', 'password',
> 'aes'));
> INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF', 'password',
> 'aes'));
>
> 3. Using the psql tool, selecting * from the crypto table yields the
> following:
>
> # select * from crypto;
> id | title |crypted_content
> +---+
> 1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
> 2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
> 3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266
>
> Pretty straight forward, right?
>
> Now how about doing this in a simple plpgsql Function. That's where we
> encounter problems. I want to get DECRYPTED data based on an input ID. So...
>
> 4. Imagine the following simple plpgsql function (note I'm trying to decrypt
> the table's encrypted BYTEA column into a decrypted VARCHAR for return):
>
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
> crypto_cursor CURSOR (input INTEGER) FOR SELECT
> encode(decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id =
> input;
> crypto crypto.crypted_content%TYPE;
> tid ALIAS FOR $1;
>
> BEGIN
> OPEN crypto_cursor( tid );
> LOOP
> FETCH crypto_cursor INTO crypto;
> EXIT WHEN NOT FOUND;
> END LOOP;
> CLOSE crypto_cursor;
> RETURN ( encode(crypto, ''escape''::text)::VARCHAR );
> END;
> ' LANGUAGE 'plpgsql';
>
> 5. When I use the above function (in the tool, psql) to get the decrypted
> contents for ID = 2, it says I get 1 row returned, but the contents are
> blank:
>
> # select * from selectFromCrypto(1);
> selectfromcrypto1
> ---
>
> (1 row)
>
> Notice the blank row returned... So what am I doing wrong?
>
> I suspect it has something to do with
> converting/encoding/decoding/decrypting the BYTEA column for return... but
> what is the problem with the above Function?
>
> I'm using PostgreSQL 7.4.6, on a Fedora Core 1 system.
>
> Best regards and thank you very much in advance,
> Michael Moran
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
--- End of Original Message ---
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
I would change the return type to TEXT, I believe your original example had it
as a varchar and I didn't change it.
Also, I believe that "under the hood" text does equal varchar.
Glad I could help
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 09:43:18 -0800
Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
> Thank you, Jim. You rock!
>
> This worked although I'm a bit confused:
>
> Your function below says it returns VARCHAR, yet the variable that holds the
> contents of my SELECT which we ultimately return is of type TEXT.
>
> When I return the TEXT variable, it works. Does this mean TEXT = VARCHAR in
> plpgsql Functions?
>
> Yet if you cast the TEXT to VARCHAR in your RETURN line below (because, it
> seems logical to try to match the declared return type)... it fails
>
> So, this works:return c;
> This doesn't: return c::VARCHAR;
>
> I always thought matching my return type to my funtion's RETURN declaration
> is logical... but now I know that if want a VARCHAR, I gotta manipulate it
> as a TEXT within my function when using PGCrypto. Any idea why?
>
> Thank again, Jim!
>
> _
>
> From: Jim Buttafuoco [mailto:[EMAIL PROTECTED]
> Sent: Thu 3/24/2005 9:14 AM
> To: Moran.Michael; pgsql-sql@postgresql.org
> Subject: Re: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
>
> give this a try
>
> CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> RETURNS VARCHAR
> AS '
> DECLARE
>_pid ALIAS FOR $1;
>c text;
>
> BEGIN
>
> SELECT decrypt(crypted_content, decode(''password''::text,
> ''escape''::text), ''aes''::text) into c
> FROM crypto
> WHERE pid = _pid;
>
>RETURN c;
> END;
> ' LANGUAGE 'plpgsql';
>
> -- Original Message ---
> From: "Moran.Michael" <[EMAIL PROTECTED]>
> To: pgsql-sql@postgresql.org
> Sent: Thu, 24 Mar 2005 08:41:34 -0800
> Subject: [SQL] Funtions + plpgsql + contrib/pgcrypto = ??
>
> > Hello there,
> >
> > What's the preferred and most efficient way to obtain PGCrypto encrypted
> > data from a plpgsql function?
> >
> > 1. Imagine the following simple table:
> > CREATE TABLE crypto (
> > pid SERIAL PRIMARY KEY,
> > title VARCHAR(50),
> > crypted_content BYTEA
> > );
> >
> > 2. Now insert the following 3 rows of data:
> >
> > INSERT INTO crypto VALUES (1, 'test1', encrypt( 'ABCD',
> 'password',
> > 'aes'));
> > INSERT INTO crypto VALUES (2, 'test2', encrypt( 'BCDE',
> 'password',
> > 'aes'));
> > INSERT INTO crypto VALUES (3, 'test3', encrypt( 'CDEF',
> 'password',
> > 'aes'));
> >
> > 3. Using the psql tool, selecting * from the crypto table yields the
> > following:
> >
> > # select * from crypto;
> > id | title |crypted_content
> > +---+
> > 1 | test1 | \307\266xI\235"\210a\363=\201\222\247\2660\215
> > 2 | test2 | D\211\256E\372^Y\0259\341\215\271\030]\234\017
> > 3 | test3 | 4\263\251y\006_N\343\0270I\207;\370\270\266
> >
> > Pretty straight forward, right?
> >
> > Now how about doing this in a simple plpgsql Function. That's where we
> > encounter problems. I want to get DECRYPTED data based on an input ID.
> So...
> >
> > 4. Imagine the following simple plpgsql function (note I'm trying to
> decrypt
> > the table's encrypted BYTEA column into a decrypted VARCHAR for return):
> >
> > CREATE OR REPLACE FUNCTION selectFromCrypto(INTEGER)
> > RETURNS VARCHAR
> > AS '
> > DECLARE
> > crypto_cursor CURSOR (input INTEGER) FOR SELECT
> > encode(decrypt(crypted_content, decode(''password''::text,
> > ''escape''::text), ''aes''::text), ''escape''::text) FROM crypto WHERE id
> =
> > input;
> > crypto crypto.crypted_content%TYPE;
> > tid ALIAS FOR $1;
> >
> > BEGIN
> > OPEN crypto_cursor( tid );
> > LOOP
> > FETCH crypto_cursor INTO crypto;
> > EXIT WHEN NOT FOUN
Re: [SQL] Calling functions from Delete View (+ contrib/pgcrypto) = madness ??
Mike,
I posted this RULE also on hackers
CREATE or replace RULE crypto_view_delete_rule
AS ON DELETE
TO crypto_view
DO INSTEAD
(
select func_delFromCrypto( OLD.id,OLD.crypted_content);
);
Jim
-- Original Message ---
From: "Moran.Michael" <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Thu, 24 Mar 2005 17:30:33 -0800
Subject: [SQL] Calling functions from Delete View (+ contrib/pgcrypto) =
madness ??
> Hello there,
>
> I have a View with a Delete rule and I would like the Delete rule to call a
> function (and pass-in a few of the the underlying View's/Table's column
> values). How do you do this?
>
> When I do it, I keep getting the following error:
> ERROR: function expression in FROM may not refer to other relations of same
> query level
>
> This is my new Delete View that attempts to call a Function but yields the
> above-mentioned error:
>
> CREATE RULE crypto_view_delete_rule
> AS ON DELETE
> TO crypto_view
> DO INSTEAD
> --Original (working) code:
> -- DELETE FROM crypto
> -- WHERE id = OLD.id;
>
> --NEW (non-working) code:
> select * from func_delFromCrypto( crypto.id, encode( decrypt(
> crypto.crypted_content, decode ('password'::text, 'escape'::text),
> 'aes'::text), 'escape'::text) );
>
> The above may seem complicated, but the crypto portion DOES work (as you'll
> see below), and I've broken down the steps below so that it's easy to see
> what's going on:
>
> 1. Imagine the following simple table:
>
> CREATE TABLE crypto (
> id SERIAL PRIMARY KEY,
> title VARCHAR(50),
> crypted_content BYTEA
> );
>
> 2. Imagine the following simple working View:
>
> CREATE VIEW crypto_view AS
> SELECT
> id,
> title,
> --Decrypt the BYTEA column and convert result to TEXT type:
> encode(decrypt(crypted_content, decode('password','escape'::text),
> 'aes'::text), 'escape'::text) as crypted_content
> FROM
> crypto;
>
> 3. Imagine my original, simple Delete Rule (that works -- it was my original
> version prior to changing it to call the Function):
>
> CREATE RULE crypto_view_delete_rule
> AS ON DELETE
> TO crypto_view
> DO INSTEAD
> DELETE FROM crypto
> WHERE id = OLD.id;
>
> 4. Let's load (and encrypt) some test data into the above-mentioned table:
>
> insert into crypto VALUES (1, 'test1', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (2, 'test2', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (3, 'test3', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (4, 'test4', encrypt('', 'password',
> 'aes') );
> insert into crypto VALUES (5, 'test5', encrypt('', 'password',
> 'aes') );
>
> 5. Let's SELECT from the table to see its contents (note the encrypted
> values):
>
> select * from crypto;
> id | title | crypted_content
> +---+
> 1 | test1 | \026\206I93\327\315\376t\243\006~J\177{\301
> 2 | test2 | \257\212\260\321\217\274c\210d \361\225\212\327\317\206
> 3 | test3 | 6\345:\224dp\002\206<\007k\344\302\347V\214
> 4 | test4 | VH)\023\303\0239\363\323\362\22734\204R\357
> 5 | test5 | \216Np\235\026\362\277\246\026\027\221\266\021\361\224\256
> (5 rows)
>
> 6. Let's run the View (which nicely decrypts the encrypted columns):
>
> # select * from crypto_view;
> id | title | crypted_content
> +---+-
> 1 | test1 |
> 2 | test2 |
> 3 | test3 |
> 4 | test4 |
> 5 | test5 |
> (5 rows)
>
> 7. Let's test the old DELETE RULE (the one that doesn't call a function,
> hence works):
>
> delete from crypto_view where crypted_content = ;
> DELETE 1
>
> Check that the DELETE RULE worked (there is one less row of data now):
> # select * from crypto_view;
> id | title | crypted_content
> +---+-
> 1 | test1 |
> 2 | test2 |
> 3 | test3 |
> 4 | test4 |
> (4 rows)
>
> It works! Pretty straight forward, right?
>
> Now let's make things a little more interesting...
>
> 8. Now here is the following (working) function that I want my rule to call:
>
> CREATE OR REPLACE FUNCTION func_delFromCrypto(INTEGER, VARCHAR)
> RETURNS INTEGER
> AS '
> DECLARE
> _id ALIAS FOR $1;
> _crypto_dataALIAS FOR $2;
> ret INTEGER:=0;
> BEGIN
> DELETE FROM crypto WHERE id = _id AND encode( decrypt( crypted_content,
> decode(''password''::text, ''escape''::text), ''aes''::text),
> ''escape''::text) = _crypto_data;
> GET DIAGNOSTICS ret = ROW_COUNT;
> RETURN ret;
> END;
> ' LANGUAGE 'plpgsql';
>
> 9. The function works, stand-alone, as you can see below:
>
> # select * from func_delFromCrypto(4, '');
> func_delfromcrypto
>
> 1
> (1 row)
>
> Check the output (there is one less row now):
>
> # select * from crypt
Re: [SQL] funny update, say update 1, updated 1 added 2nd.
works fine for me. Do you have any triggers on the tables or other rules? Can you provide a complete SQL script that starts from an empty database. Jim -- Original Message --- From: Neil Dugan <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Thu, 16 Jun 2005 13:38:58 +1000 Subject: [SQL] funny update, say update 1, updated 1 added 2nd. > I have been having some trouble with a particular table view. An UPDATE > command is not only changing the applicable record it is also creating a > new record as well. > > wholesale=# select * from accounts_supplier; > id | name | contact | addr| addr2 | town | > postcode | state | phone | fax | account_type > +--+-+---+---++--+---+---+-+-- > 1 | ABC construction | TOM | | || >| NSW | | | Cash Only > 2 | test | | | || >| | | | 7 Day > 3 | build-4-U| boss| somewhere | | back of beyond | >| | | | 7 Day > (3 rows) > > wholesale=# update accounts_supplier set addr='nowhere' where id=3; > UPDATE 1 > wholesale=# select * from accounts_supplier; > id | name | contact | addr | addr2 | town | > postcode | state | phone | fax | account_type > +--+-+-+---++--+---+---+-+-- > 1 | ABC construction | TOM | | || > | NSW | | | Cash > Only 2 | test | | | || > | | | | 7 > Day 6 | build-4-U| boss| nowhere | | back of beyond | > | | | | 7 > Day 3 | build-4-U| boss| nowhere | | back of beyond | > | | | | 7 Day > (4 rows) > > Can anyone tell me why this is happening and how to fix it. > > Here are the table and view definitions. > > CREATE TABLE account_type ( > number smallint, > name character varying(20) > ); > > CREATE TABLE address ( > addr character varying(40), > addr2 character varying(40), > town character varying(20), > postcode character varying(10), > state character(4) > ); > > CREATE TABLE supplier ( > id bigserial NOT NULL, > name character varying(40), > phone character varying(20), > fax character varying(20), > contact character varying(40), > account_type smallint DEFAULT 0 > ) > INHERITS (address); > > CREATE VIEW accounts_supplier AS > SELECT supplier.id, > supplier.name, > supplier.contact, > supplier.addr, > supplier.addr2, > supplier.town, > supplier.postcode, > supplier.state, > supplier.phone, > supplier.fax, > account_type.name AS account_type > FROM supplier, account_type > WHERE (account_type.number = supplier.account_type); > > CREATE RULE accounts_supplier_update > AS ON UPDATE TO accounts_supplier > DO INSTEAD UPDATE supplier > SET name = new.name, > contact = new.contact, > addr = new.addr, > addr2 = new.addr2, > town = new.town, > postcode = new.postcode, > state = upper((new.state)::text), > phone = new.phone, > fax = new.fax, > account_type = (SELECT account_type.number > FROM account_type > WHERE ((account_type.name)::text = (new.account_type)::text)) > WHERE (supplier.id = new.id); > > wholesale=# select version(); > version > -- > PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC > i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red > Hat 3.4.3-22) > (1 row) > > wholesale=# select * from account_type; > number | name > +--- > 0 | Cash Only > 1 | 7 Day > 2 | 30 Day > 3 | 60 Day > 4 | 90 Day > (5 rows) > > Thanks for any help > Regards Neil. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] multiple PK with a non UNIQUE field
if you need a multi column fk don't use the "references" keyword on your create table, instead use the "FOREIGN KEY" keyword for the table, see the "create table" help. so for example (untested) change CREATE TABLE appalto ( cod_op int not null references Opere, cod_com int not null references Opere, scadenza date not null, importoint not null, PRIMARY KEY (cod_op,cod_com) ); to CREATE TABLE appalto ( cod_op int not null, cod_com int not null, scadenza date not null, importoint not null, PRIMARY KEY (cod_op,cod_com), FOREIGN KEY (cod_op,cod_com) REFERENCES Opere(cod_op,cod_com) ); -- Original Message --- From: davide <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Sun, 26 Jun 2005 16:58:50 +0200 Subject: [SQL] multiple PK with a non UNIQUE field > Sorry, I 'm a beginner of postgres and I found a problem when I was > passed from MySQL: > I have to create a referential constraint table APPALTO from a table > OPERE that as a composed PK(cod_op,cod_com) where cod_op ISN'T unique. > in MySQL: > CREATE TABLE opere ( > cod_op int NOT NULL , > cod_com int NOT NULL , > costo int , > data_inizio date , > data_fine date , > tipo char(6) NOT NULL , > PRIMARY KEY (cod_op,cod_com) > ) ; > > CREATE TABLE committenti ( > cod_com int NOT NULL , > nome char(30) NOT NULL, > indirizzo char(60) NOT NULL, > CF char(16) unique, > P_IVA char(11) unique, > tipo char(8) NOT NULL , > PRIMARY KEY (cod_com) > ); > > CREATE TABLE appalto ( > cod_op int not null references Opere, > cod_com int not null references Opere, > scadenza date not null, > importoint not null, > PRIMARY KEY (cod_op,cod_com) > ); > > But when I try to insert it: > ERROR: number of referencing and referenced columns for foreign key > disagree > > another table connected at OPERE give instead another error: > > CREATE TABLE direzione ( > CF char(16) not null references Salariati(CF), > cod_op int not null references Opere (cod_op), > cod_com int not null references Opere (cod_com), > --opere_pkeyintreferences Opere, > --PRIMARY KEY (opere_pkey) > PRIMARY KEY (CF,cod_op,cod_com) > ) ; > ERROR: there is no unique constraint matching given keys for referenced > table "opere" > > If I try to use the index "opere_pkey" (automatic created) > NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index > "opere_pkey" for table "opere" > > Why MySQL let me do these and PostgreSQL no? > There's another way? > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster --- End of Original Message --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How can I simply substatue a value in a query?
try case for example select case when bool_column then 'Yes' else 'No end from your_table; -- Original Message --- From: Roy Souther <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Mon, 27 Jun 2005 11:16:58 -0600 Subject: [SQL] How can I simply substatue a value in a query? > I want to do a simple substatution of a value in a query. For example I > have a boolean field that wil return t or f for True and False. I would > like it to return Yes or No. I don't want to have a 2x2 table to look up > the output. I don't want to use stored procedue. > > I think there is a better way, somthing very simple but I cannot > remember what it is. > > Roy Souther > www.SiliconTao.com > Let Open Source help your business move beyond. > > For security this message is digitally authenticated by GnuPG. --- End of Original Message --- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] 'show full processlist' in postgres?
did you restart postgresql and use the pg_stat_activity view instead (just to save some typing). Jim -- Original Message --- From: Erik Wasser <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Fri, 1 Jul 2005 15:58:46 +0200 Subject: [SQL] 'show full processlist' in postgres? > Hallo pgsql-sql@postgresql.org, > > I'm looking for a way to display the active querys of postgres. The > mysql way is a 'show [full] processlist'. > > After I've read http://pgsqld.active-venture.com/monitoring-stats.html I > set STATS_COMMAND_STRING, STATS_BLOCK_LEVEL, STATS_ROW_LEVEL and > STATS_START_COLLECTOR to 'true'. > > Then I executed the query from the bottom of the page: > > > SELECT pg_stat_get_backend_pid(S.backendid) AS procpid, > >pg_stat_get_backend_activity(S.backendid) AS current_query > >FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; > > The result was: > > procpid | current_query > -+--- > 1367 | >27387 | > 930 | >28425 | > (4 rows) > > Why there isn't any query in the 'current_query' row? Calling the query > multiple times just changes the PID (sometimes) but 'current_query' was > always empty. How do I show up the a list of querys that will be > processed right in this moment? > > P.S: I'm using postgresql 7.4.7 and I was logged I as 'postgres'. > > -- > So long... Fuzz > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] --- End of Original Message --- ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Generating a range of integers in a query
I use the following function which returns a date series. You can modify it to
return an int series instead
create or replace function alldates(date,date) returns setof date
as
'
declare
s alias for $1;
e alias for $2;
d date;
begin
d := s;
while d <= e
LOOP
return next d;
select d + \'1 day\'::interval into d;
END LOOP;
return null;
end;
'
LANGUAGE 'plpgsql'
;
select * from alldates('2004-07-01','2004-08-10');
-- Original Message ---
From: Aaron Bingham <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Wed, 13 Jul 2005 11:13:06 +0200
Subject: [SQL] Generating a range of integers in a query
> Hello,
>
> I've got an interesting problem: I need to select all possible values
> of an attribute that do /not/ occur in the database.
>
> This would be easy (in my case at least) if there were a way to
> generate a table containing all integers between 1 and n, where n is
> the result of a subquery. In my case, n will be at most a few
> hundred. I would like to be able to generate this table as a
> subquery. Any ideas?
>
> Thanks,
>
> --
>
> Aaron Bingham
> Senior Software Engineer
> Cenix BioScience GmbH
>
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
--- End of Original Message ---
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Dumping table definitions
use pg_dump with the --schema-only and --table= params -- Original Message --- From: "Mark Fenbers" <[EMAIL PROTECTED]> To: Pg SQL Discussion Group Sent: Mon, 18 Jul 2005 12:50:54 -0400 Subject: [SQL] Dumping table definitions > I am looking for a way to reformat the information that is generated from > \d mytable > into SQL syntax, such that the table can be recreated with 'psql -f > mytable.sql' complete with index and constraint definitions. I can do > awk and sed commands to do this if I need to, but first wanted to check > if Pg already had tools to export the table structure (without the > data). Does it? > > Also, Is there a way to export the structure of all tables at once > instead of one table at a time? > > Mark --- 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
Re: [SQL] Multi-column returns from pgsql
Mark, Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN NEXT rec; then your select statement would be select * from my_func() as (txt1 text,txt2 text); Jim -- Original Message --- From: "Mark R. Dingee" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Fri, 22 Jul 2005 11:49:21 -0400 Subject: [SQL] Multi-column returns from pgsql > Hi Everyone, > > Does anyone know if/how it's possible to return multi-column sets from a > pgsql > function? Right now I'm using something like the following as a work around > > CREATE OR REPLACE FUNCTION my_func() returns SETOF TEXT AS ' > DECLARE > rec record; > BEGIN > FOR rec IN SELECT txt1, txt2 FROM mytable LOOP >RETURN NEXT rec.txt1; >RETURN NEXT rec.txt2; > END LOOP; > RETURN; > END;' language 'plpgsql'; > > which leaves me parsing multiple records to achieve the desired end result. > > Anyone have any thoughts? > > Thanks, > Mark > > ---(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 --- End of Original Message --- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Multi-column returns from pgsql
or just return setof RECORD (version 7.4 +) -- Original Message --- From: Tony Wasson <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Cc: "Mark R. Dingee" <[EMAIL PROTECTED]>, pgsql-sql@postgresql.org Sent: Fri, 22 Jul 2005 11:11:09 -0700 Subject: Re: [SQL] Multi-column returns from pgsql > On 7/22/05, Jim Buttafuoco <[EMAIL PROTECTED]> wrote: > > Mark, > > > > Instead of RETURN NEXT rec.txt1; RETURN NEXT rec.txt2; just use RETURN > > NEXT rec; > > > > then your select statement would be > > select * from my_func() as (txt1 text,txt2 text); > > > > Jim > > Besides a simple RETURN NEXT, you'll need to return a SETOF some > composite type. You can do something like > > CREATE TYPE twotexts_t AS (txt1 TEXT, txt2 TEXT); > > CREATE OR REPLACE FUNCTION my_func() returns SETOF twotexts_t AS ' > DECLARE >rec record; > BEGIN >FOR rec IN SELECT txt1, txt2 FROM mytable LOOP > RETURN NEXT >END LOOP; >RETURN; > END;' language 'plpgsql'; > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend --- End of Original Message --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] using pg_tables and tablename in queries
do you mean UNION ALL instead of JOIN, if you mean UNION ALL , I would go with
a set returning function passing it
the necessary WHERE clause to be applied to all of your tables. You might be
able to wrap the whole thing into a view
-- Original Message ---
From: solarsail <[EMAIL PROTECTED]>
To: Tom Lane <[EMAIL PROTECTED]>
Cc: pgsql-sql@postgresql.org
Sent: Tue, 4 Oct 2005 14:40:54 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> The current behavior is by design.
>
> We use the table as a logging repository. It can get very large 250 000
> records. Because of the large number of records that we have in the table we
> found it was much faster to perform inserts on a smaller table. Our current
> system rolls the tables over every 12 hours or so, creating a new table with
> the following behavior:
>
> CREATE TABLE mytable_temp {...}
>
> ALTER TABLE mytable RENAME TO mytable_back_datetime;
> ALTER TABLE mytable_temp RENAME TO mytable;
>
> I want to join the mytable_back_datetime tables together in order to perform
> queries against my huge set of data to generate some reports. I'm probably
> going to create a temporary table with a few indexes to make the reports run
> faster... however I need to join the tables all together first.
>
> On 10/4/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> >
> > solarsail <[EMAIL PROTECTED]> writes:
> > > I have a large number of tables with a common naming convention
> >
> > > mytable001, mytable002, mytable003 ... mytable00n
> >
> > > I would like to do a query across all of the tables, however I do not
> > know
> > > all of the tables before hand, and I do not want to ( cant ) manually
> > > generate a query like
> >
> > > select * from mytable001, mytable002, mytable003
> >
> > > I have a query that returns the names of the tables I want to query:
> >
> > > select tablename from pg_tables where tablename like 'mytable%'
> >
> > This looks to me like a situation in which you should rethink your
> > data design. Those tables should all get merged into one big table,
> > adding one extra column that reflects what you had been using to
> > segregate the data into different tables.
> >
> > regards, tom lane
> >
--- End of Original Message ---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] using pg_tables and tablename in queries
again, do you really want to join the tables or do a UNION ALL. From one of
your posts you said the table were the
same.
you need to do something like
select * from table_001
union all
select * from table_002
...
select * from table_999
I would do this in a set returning function looping of an EXECUTE. If you need
help, post the schema for a couple of
your tables and I will help with the function
Jim
-- Original Message ---
From: solarsail <[EMAIL PROTECTED]>
To: Yasir Malik <[EMAIL PROTECTED]>
Cc: PostgreSQL
Sent: Tue, 4 Oct 2005 15:50:39 -0400
Subject: Re: [SQL] using pg_tables and tablename in queries
> I managed to make this work as sub query before... I wish I had
> written it down somewhere...
>
> Regarding the creation of a function. I do have a function that
> almost does that. I'm having a hard time getting it to return a set
> of records from the EXECUTE command ( more than one row returned by
> the select * ...).
>
> If I generate a temporary table instead of returning the results how
> long will that table exist for? Excuse the OOP terminology but would
> it be correct to create a 'Singleton' to access the temporary table,
> where if it exists and is less than 30 minutes old use that one,
> otherwise drop the table and recreate it?
>
> Thanks
>
> -- sample function..
>
> CREATE OR REPLACE FUNCTION testfunc_jointables()
> RETURNS SETOF record AS
> $BODY$
> DECLARE
> query TEXT;
> BEGIN
> query := 'auditrecord';
>
> FOR
> atablename IN select * from pg_tables where tablename like
> 'mytable_%'
> LOOP
>
> query := query || ', ' || quote_ident(atablename.tablename);
>
> END LOOP;
>
> EXECUTE ' SELECT * from ' || query;
>
> END;
>
> On 10/4/05, Yasir Malik <[EMAIL PROTECTED]> wrote:
> > > The current behavior is by design.
> > >
> > > We use the table as a logging repository. It can get very large 250 000
> > > records. Because of the large number of records that we have in the table
> > > we
> > > found it was much faster to perform inserts on a smaller table. Our
> > > current
> > > system rolls the tables over every 12 hours or so, creating a new table
> > > with
> > > the following behavior:
> > >
> > > CREATE TABLE mytable_temp {...}
> > >
> > > ALTER TABLE mytable RENAME TO mytable_back_datetime;
> > > ALTER TABLE mytable_temp RENAME TO mytable;
> > >
> > > I want to join the mytable_back_datetime tables together in order to
> > > perform
> > > queries against my huge set of data to generate some reports. I'm probably
> > > going to create a temporary table with a few indexes to make the reports
> > > run
> > > faster... however I need to join the tables all together first.
> > >
> >
> > I would create a function that creates a string with a query that includes
> > all the tables you need, and call execute on the string. You would loop
> > through the all tables from pg_tables and keep on appending the table name
> > you need.
> >
> > Regards,
> > Yasir
> >
> > ---(end of broadcast)---
> > TIP 3: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/docs/faq
> >
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
--- End of Original Message ---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] new rule syntax?
Try this rule instead create rule checks_d0 as on delete to checks do delete from checkitems where ckid = OLD.ckid; -- Original Message --- From: "Milen A. Radev" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Sun, 05 Feb 2006 15:10:23 +0200 Subject: Re: [SQL] new rule syntax? [UTF-8?]> A. R. Van Hook напиÑа: > > I have two tables defined as: > > checks > >(ckidint NOT null PRIMARY KEY, > > payto text, > > notes text, > > ckdate date, > > printed int default 0, > > tdate timestamp not null) > > checkitems > >(itemint not null, > > ckidint NOT null references checks, > > itemtypeint not null, > > amt numeric(7,3), > > primary key (item, ckid)) > > > > in previous versions (<8.1) the following rule declaration seemed to > > work fine > > create rule checks_d0 as > > on delete to checks > >do delete from checkitems > > where ckid = checks.ckid; > > in 8.1.2 I get > > > > ERROR: missing FROM-clause entry from table "checks" > > > > any idea? > > May be you are bitten by the change of the default value of > "add_missing_from" setting > (http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION). > > -- > Milen A. Radev > > ---(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 6: explain analyze is your friend
Re: [SQL] Sum If
try select ...,sum(case when sales.itemname = 'some' then sales.count else 0 end) as "Sales Candies" from your_table_here group by ... -- Original Message --- From: "Daniel Hernandez" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Thu, 23 Feb 2006 12:46:44 -0500 (EST) Subject: [SQL] Sum If > Hi Guys, I'm new on this group, and I have a question, is there a way to do > a "sum if" (kind of) > ?What i want to do is the following.Select customers.custid, if > (sales.itemname = 'candy', sum(sales.count)) > as "Sales candies", if (sales.itemname = 'some', sum(sales.count)) > as "Sales Some"from > ...join ...where .group by customers.custid ...Thanks in advanced, and > best regards,Daniel > Hernández.Tijuana, BC, México."More you learn, more you earn". > > ___ > Join Excite! - http://www.excite.com > The most personalized portal on the Web! --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Query from shell
or psql db < To: Sent: Thu, 6 Apr 2006 14:37:51 -0700 Subject: Re: [SQL] Query from shell > Judith wrote: > > >Hi every body, somebody can show me hot to execute a > > query from a shell > > echo QUERY HERE | psql databasename > > Or, if you want to run several queries, run psql and run your queries there. > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster --- End of Original Message --- ---(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: [SQL] Problems Testing User-Defined Function
You will have to use the "CALLED ON NULL INPUT" option to "create function"
(Postgresql 8.1, I don't know about other
versions) if you expect NULL arguments.
Jim
-- Original Message ---
From: "Rommel the iCeMAn" <[EMAIL PROTECTED]>
To: "PostgreSQL SQL Mailing List"
Sent: Fri, 9 Jun 2006 16:01:26 -0400
Subject: [SQL] Problems Testing User-Defined Function
> Hi list,
>
> I'm a newbie and I have a problem. I've defined the following function using
> pgAdmin but I am clueless as to how I can test it. I will eventually be
> calling this function from a .NET application but I want to test it using
> raw SQL first. Here's the function definition:
>
> CREATE OR REPLACE FUNCTION sp_insert_manifest(_sender varchar(255),
>_sender_email varchar(255),
>_reply_to varchar(255),
>_filename varchar(255),
>_file oid,
>_datetime_sent timestamp) RETURNS integer AS $$
>
> -- blah blah --
>
> $$ LANGUAGE plpgsql;
>
> I am trying to pass the following values to the function but I have been so
> far unsuccessful.
>
> SELECT sp_insert_manifest('me', [EMAIL PROTECTED]', '[EMAIL PROTECTED]',
> 'test.txt', NULL,
> '2006/06/09')
>
> Can anyone help me here?
>
> Thanks,
> Rommel the iCeMAn.
>
> ---(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: [SQL] Good examples of calling slony stored procedures
try www.slony.info -- Original Message --- From: "Mark Adan" <[EMAIL PROTECTED]> To: Sent: Wed, 14 Jun 2006 08:50:23 -0700 Subject: Re: [SQL] Good examples of calling slony stored procedures > Hi > > Can somebody direct me to the mailing list for slony. I couldn't find > it anywhere on the postgres.org website (which is where I found this > list to begin with). Thanks > > Mark > > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Tuesday, June 13, 2006 20:27 > To: Mark Adan > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Good examples of calling slony stored procedures > > "Mark Adan" <[EMAIL PROTECTED]> writes: > > I was wondering if where can I find some examples of calling the slony > > stored procedures instead of using slonik? I want to be able to for > > example add a table into slony. Thanks > > This is likely the wrong bunch to ask --- there's a slony project > mailing list where the right people to ask hang out. Don't have > the address at hand. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- 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
Re: [SQL] create aggregate function 'count_bool( column_name, boolean )'
James, I know Postgresql doesn't have 2 arg aggregate functions. what you could do is the following (untested) select distict product_id, sum(case when purchased then 1 else 0 end) as purchased, sum(case when was_selected then 1 else 0 end) as was_selected from some_table group by product_id; Jim -- Original Message --- From: "James Moliere" <[EMAIL PROTECTED]> To: Sent: Fri, 7 Jul 2006 06:53:45 -0700 Subject: [SQL] create aggregate function 'count_bool( column_name, boolean )' > Hello, > I'd like to create a function called count_bool( column_name, boolean ) in > PostgreSQL. > > this function is similar to the count( column_name ) function but will only > count the trues or falses based on the other input parameter boolean. e.g. > if you pass in a 'true', all the trues will be counted but not the falses -- > it's the same but opposite if the 'false' was passed in. > > I'd like this aggregate function to be developed with the SQL language > instead of 'C' (for maintenance reasons). From the surface, it appears to > be an incredibly simple job only to find that it's difficult. > > In some respects, I can't believe this function doesn't exist in SQL > > so now I can create the statement > select distict product_id, count_bool(purchased, true), > count_bool(was_selected, true) from some_table group by product_id; > > instead of breaking the query into 3 seperate queries > > select distict product_id from some_table; > select count(purchased) from product_id where purchased = true; > select count(was_selected) from some_table where was_selected = true; > > Am I missing a detail with SQL based aggregate function development? > > Any help would be appreciated. > > Thanks! --- End of Original Message --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] SELECT substring with regex
use plperl
-- Original Message ---
From: T E Schmitz <[EMAIL PROTECTED]>
To: pgsql-sql@postgresql.org
Sent: Fri, 07 Jul 2006 20:23:50 +0100
Subject: Re: [SQL] SELECT substring with regex
> Rodrigo De Leon wrote:
> > On 7/7/06, T E Schmitz <[EMAIL PROTECTED]> wrote:
> >
> >> But that takes me to the next problem:
> >>
> >> For the sake of the example I simplified the regular pattern.
> >> In reality, BASE_NAME might be:
> >>
> >> 28mm
> >> 28-70mm
> >>
> >> So the reg. expr. requires brackets:
> >>
> >> substring (NAME, '^(\\d+(-\\d+)?mm)' ) as BASE_NAME
> >>
> >> Actually, the pattern is more complex than that and I cannot see how I
> >> can express it without brackets.
> >
> >
> > Maybe:
> >
> > select
> > substring ('28-70mm LD AD Asp XR Macro', '^[\\d-]*mm' ) as BASE_NAME
>
> Sorry, but that would also capture something like
> 10-30-59mm
>
> The pattern describes either a single length (120 millimeters) or a
> range (30 to 70 millimetres), hence:
>
> \\d+(-\\d+)?mm
>
> The ? quantifier refers to the combination of '-' and digits and has to
> be bracketed.
>
> If the brackets cannot be avoided in the expression, your original
> suggestion might come in handy though:
>
> SELECT
> substring (NAME, '^\\d+(-\\d+)?mm') AS BASE_NAME ,
> substr(
> NAME
> , char_length(
> substring (NAME, '^\\d+(-\\d+)?mm')
> ) + 2
> ) AS SUFFIX
>
> Still, I'd be interested to know whether there is a 'more elegant' solution.
>
> --
>
> Regards,
>
> Tarlika Elisabeth Schmitz
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org
--- End of Original Message ---
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] DBD::Pg ... how would I format this prepare?
try now() + (? || ' day')::interval -- Original Message --- From: "Marc G. Fournier" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Wed, 26 Jul 2006 18:29:32 -0300 (ADT) Subject: [SQL] DBD::Pg ... how would I format this prepare? > I need to do: > > NOW() + '2 day'::interval > > where 2 is a variable ... > > if I do: > > NOW() + '? day'::interval > > it, of course, takes the ? as a literal ... so is there some way I can do > this such that I can do the placeholder? > > Thx > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email . [EMAIL PROTECTED] MSN . [EMAIL PROTECTED] > Yahoo . yscrappy Skype: hub.orgICQ . 7615664 > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] finding unused indexes?
check out pg_stat_user_indexes, you will need to turn on the stats collection in your postgresql.conf file first. Jim -- Original Message --- From: "George Pavlov" <[EMAIL PROTECTED]> To: Sent: Tue, 1 Aug 2006 09:05:34 -0700 Subject: [SQL] finding unused indexes? > Anybody have a clever way to quickly find whether there are any unused > indexes in a PG DB? One way I have done is to take queries from the DB > log, prepend an explain to each and grep the results, but I am wondering > if there are either any index usage stats maintained somewhere inside > Postgres or if there is a slicker/less cumbersome way of doing it. Also > indexes used by functions are hard to simulate that way. > > George > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend --- End of Original Message --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Deleting Functions
Scott,
I use the following query with psql \o option. Change the schema name from
public to whatever. I am sure you could put
this into a plpgsql function using execute as well.
Jim
\o drops.sql
select 'drop function ' || nspname || '.' || proname || '(' ||
pg_catalog.oidvectortypes(p.proargtypes) || ');' from
pg_proc p join pg_namespace b on (p.pronamespace=b.oid) where nspname='public';
\o
\!vi drops.sql # <
To: pgsql-sql@postgresql.org
Sent: Wed, 23 Aug 2006 13:29:12 -0600
Subject: [SQL] Deleting Functions
> Folks,
>
> I am using PGSQL do do all of my schema changes, but have run into a
> problem. I need to be able to DROP all of the USER FUNCTIONS that are
> defined in a schema. The problem is that I do not know the names of all
> of the functions and parameters. I know that I could use '\df' and then
> use perl or some other scripting language to parse this, but I need to
> be able to perform this action on multiple platforms (without rewriting
> the process for each platform).
>
> I have written a script that incorporates '\df' and appropriate parsing
> to perform this action for LINUX and it works fine/lasts a long time. I
> need to make this more transparent so it will run on all platforms (in
> particular windows).
>
> I know that in the past (version 7) there was a way to drop the
> functions by directly accessing some of the system tables. The
> question/problem is that we have updated to version 8.1 and need the
> same functionality.
>
> Any help or directions would be greatly appreciated.
>
> Thanks Scott.
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
--- End of Original Message ---
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Rule for multiple entries
Use a trigger instead, the rule is only run once per insert/update/delete while the trigger is run for each row. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of William Scott Jordan Sent: Wednesday, December 13, 2006 9:05 PM To: pgsql-sql@postgresql.org Subject: [SQL] Rule for multiple entries Hi all! I have a rule in place that is supposed to adjust a value in one table based on how many rows are added or deleted to another table, but I'm not getting the results that I hoped for. If a single sql statement adds or deletes multiple entries, the rule still only adjusts the value by one, when I want it to adjust the value by the number of rows that were added or deleted. To recreate this problem, first set up the schema: CREATE TABLE counter ( counter_key int, counter_value int ) ; INSERT INTO counter (counter_key, counter_value) VALUES (1, 0) ; CREATE TABLE entries ( entry_key int ) ; CREATE RULE entries_insert AS ON INSERT TO entries DO UPDATE counter SET counter_value = counter_value + 1 WHERE counter_key = 1 ; CREATE RULE entries_delete AS ON DELETE TO entries DO UPDATE counter SET counter_value = counter_value - 1 WHERE counter_key = 1 ; Then enter some values: INSERT INTO entries (entry_key) VALUES (1) ; INSERT INTO entries (entry_key) VALUES (2) ; INSERT INTO entries (entry_key) VALUES (3) ; INSERT INTO entries (entry_key) VALUES (4) ; INSERT INTO entries (entry_key) VALUES (5) ; At this point the counter table should show a counter_value of 5, because there are 5 entries in the entries table. Now, if you delete 3 of those entries, the counter table should show a value of 2, but instead it only counts it as a single transaction. DELETE FROM entries WHERE entry_key > 2 ; Why doesn't the entries_delete rule hit for each row that's being adjusted. Or, more importantly, how can I adjust the rule so that it will count each row that's be added/deleted? Any suggestions would be appreciated. -Scott ---(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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with quotes in plpgsql
Try select into a now() - interval ($1 || ' day') -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Ray Sent: Tuesday, December 19, 2006 3:10 PM To: pgsql-sql@postgresql.org Subject: [SQL] Help with quotes in plpgsql How should this be properly quoted create or replace function test(integer) returns setof text as $$ declare a record; begin select into a now() - interval '$1 day'; return next a; return; end $$ language 'plpgsql'; I'm not having a lot of luck Thanks Richard ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate ---(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: [SQL] Related tables to a view
Try this query
select a.relname as base,a.relkind
from
pg_class a
join pg_depend d on (a.oid = d.refobjid)
join pg_class c on (d.classid = c.oid)
join pg_rewrite r on (objid = r.oid)
join pg_class v on (ev_class = v.oid)
where a.relkind in('r', 'v')
and a.relname <> v.relname
and v.relname='YOUR VIEW NAME HERE'
order by 1
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of Jan Meyland Andersen
Sent: Tuesday, December 26, 2006 3:11 PM
To: Michael Fuhr
Cc: Jan Meyland Andersen; pgsql-sql@postgresql.org
Subject: Re: [SQL] Related tables to a view
> You could query pg_depend to find out which tables and columns the
> view's rewrite rule depends on but that's not as specific as what
> you're requesting. I'm not aware of a way to associate a particular
> table column with a particular view column short of parsing the view
> definition or rule action; doing so would have to allow for the
> possibility of a view column deriving its value from an arbitrarily
> complex expression involving multiple tables, subqueries, etc.
I have been thinking the problem through again, and I think I am able to
solve the problem if I just know which tables the view is using.
But how do I get this information from the pg_depend table?
Regards
Jan
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [SQL] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres?
Use case statement and sum to get a count where status=20... For example Select sum(case when status=20 then 1 else 0 end) as status20, Sum(case when status=30 then 1 else 0 end) as status30 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bryce Nesbitt Sent: Monday, May 14, 2007 6:56 PM To: pgsql-sql@postgresql.org Subject: [SQL] Doing a conditional aggregate (e.g. count(*) if x=y) in postgres? All; Is there a way to get a conditional aggregate? I have this two column view: SELECT count(*) AS count, xx_plan.plan_name FROM xx_membership JOIN xx_account USING (account_id) JOIN xx_plan USING (plan_id) WHERE xx_membership.status = 10 GROUP BY xx_plan.plan_name; And would like to add additional columns (not rows) breaking out "status=20" and "status=30" totals. Is this possible without a stored procedure? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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
