[SQL] cache lookup failed
Hi, I have this function: CREATE FUNCTION add_one (integer) RETURNS integer AS ' BEGIN RETURN $1 + 1; END; ' LANGUAGE 'plpgsql'; from postgres doc. When I try somethin like this: SELECT add_one(4); I get : ERROR: fmgr_info: function 20086: cache lookup failed please tell me what it's rong. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Optimizing view
Hello. I'm running `PostgreSQL 7.2 on i586-pc-linux-gnu, compiled by GCC 2.95.3' and have some problems with queries on a view. I've got two tables: working one and archive and one view, I've attached the description. And here is my problem: queries on a view runs to much time. explain analyze select status, target, attempts from messages_all where message_id = 21823; NOTICE: QUERY PLAN: Subquery Scan messages_all (cost=2410.78..2887.07 rows=1270 width=210) (actual time=5946.24..5948.41 rows=1 loops=1) -> Unique (cost=2410.78..2887.07 rows=1270 width=210) (actual time=4567.78..5765.08 rows=12857 loops=1) -> Sort (cost=2410.78..2410.78 rows=12701 width=210) (actual time=4567.75..5043.40 rows=12857 loops=1) -> Append (cost=0.00..764.01 rows=12701 width=210) (actual time=0.62..2346.45 rows=12857 loops=1) -> Subquery Scan *SELECT* 1 (cost=0.00..287.17 rows=917 width=210) (actual time=0.61..229.99 rows=1073 loops=1) -> Seq Scan on messages (cost=0.00..287.17 rows=917 width=210) (actual time=0.52..157.78 rows=1073 loops=1) -> Subquery Scan *SELECT* 2 (cost=0.00..476.84 rows=11784 width=207) (actual time=3.13..2037.05 rows=11784 loops=1) -> Seq Scan on messages_archive (cost=0.00..476.84 rows=11784 width=207) (actual time=3.04..1216.87 rows=11784 loops=1) Total runtime: 6101.58 msec EXPLAIN Too lo-o-ong for 13 000 tuples. When I UNION 2 queries from this tables, query is pretty fast: explain analyze select status, target, attempts from messages where message_id = 21823 union select status, target, attempts from messages_archive where message_id = 21823; NOTICE: QUERY PLAN: Unique (cost=7.34..7.36 rows=1 width=19) (actual time=224.32..224.34 rows=1 loops=1) -> Sort (cost=7.34..7.34 rows=2 width=19) (actual time=224.31..224.31 rows=1 loops=1) -> Append (cost=0.00..7.33 rows=2 width=19) (actual time=128.29..205.15 rows=1 loops=1) -> Subquery Scan *SELECT* 1 (cost=0.00..4.24 rows=1 width=19) (actual time=128.27..128.47 rows=1 loops=1) -> Index Scan using messages_primary on messages (cost=0.00..4.24 rows=1 width=19) (actual time=128.23..128.42 rows=1 loops=1) -> Subquery Scan *SELECT* 2 (cost=0.00..3.10 rows=1 width=19) (actual time=76.65..76.65 rows=0 loops=1) -> Index Scan using messages_archive_id on messages_archive (cost=0.00..3.10 rows=1 width=19) (actual time=76.63..76.63 rows=0 loops=1) Total runtime: 225.35 msec EXPLAIN I've read manual on REWRITE RULES, but I can't get, how to create RULE to keep the WHERE clause and separate one query into 2, and union them after. Any suggestions? -- Victor Yegorov CREATE TABLE messages ( message_id INT4NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_outTIMESTAMP, time_gotTIMESTAMP, status INT NOT NULL, attemptsINT NOT NULL, priorityINT NOT NULL DEFAULT 0, notify INT, device INT, delay INT, validityINT NOT NULL DEFAULT -1, route_idINT, CONSTRAINT messages_primary PRIMARY KEY(message_id), CONSTRAINT messages_client_id FOREIGN KEY(client_id) REFERENCES clients(client_id), CONSTRAINT messages_status FOREIGN KEY(status) REFERENCES mstatus(status) ); CREATE TABLE messages_archive ( message_id INT4NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_outTIMESTAMP, time_gotTIMESTAMP, status INT NOT NULL, attemptsINT NOT NULL, priorityINT NOT NULL, notify INT, device INT, delay INT, validityINT NOT NULL, route_idINT ); CREATE VIEW messages_all AS SELECT * FROM messages UNION SELECT * FROM messages_archive; pgp0.pgp Description: PGP signature
Re: [SQL] Seeking help with a query....
* Dan Winslow <[EMAIL PROTECTED]> [21.03.2003 21:58]: > Hi folks, seeking help with a query that I thought was simple, but > apparantly isn't, at least for someone with my knowledge level. > > Given a table : > > create table atable ( > code1 char, > code2 char, > costint > ); > > And the rows > > code1code2cost > - > ab2 > de4 > ba6 > fg1 > > I need a ( preferably single ) query that will sum the costs for any > matching pairs of codes regardless of order. That is, row 1 and row 3 > concern the same pair of unordered codes (a,b), and the result should show > that the (a,b) pair had a summed cost of 8. I am not able to change any of > the environment or preconditions other than the query itself. I have tried > so many approaches that they aren't even worth listing. Any suggestions > would be very much appreciated. First thing, that came to my mind: Give each code (if they are not numeric) a number. For this example, that could be ASCII code of chars. Create view on that table: CREATE VIEW aview AS SELECT at.*, code(at.code1) + code(at.code2) AS dbl_code FROM atable at; dbl_code field will have equal values for all groups of codes with same codes involved: a and b, b and a. About function code() I used to create the View: it's just an assumption, you should write one yourself (on C for faster perfomance). Or simply use: code1 + code if your codes are of numeric type and are foreign keys to some other table's primary key. Please, give some feedback on usability of this solution. -- Victor Yegorov pgp0.pgp Description: PGP signature
[SQL] vacuum all but system tables
Hello. I'd like to make a script to automatically vacuum all my DBs nightly. And I'd like to skip system tables, starting with pg_*. I've seen in the list archives somebody's solution using regexps, something like: $> vacuum analyze !~ ^pg_; It doesn't work for me, and I cannot find the original post I've seen some time ago. I'm running: `PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.95.3'. Maybe someone came across with this and could help? -- Victor Yegorov pgp0.pgp Description: PGP signature
[SQL] failing to connect to postgresql with php
i have installed postgres 8.4 server in ubuntu 10.04, clearly following all procedures. what is disappointing me i cant establish connection to the database server when am configuring postgres with php. whenever i use this postgres database function i dont even receive an error message. the server just remains quite. more on than when i execute the command #psql template1 on my terminal i receive this error message psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"? can someone help me out please
[SQL] loosing data in postgres database
hi everyone, i have a problem of loosing data in my postgres database whenever i restart my machine what could be the possible problem help me out?
[SQL] Selecting data from XML
If I have a XML like this can I write a query that will output the columns names and values like this? code;validFrom;validTo -- CLIN102;1980-02-23; CLIN103;1980-02-23;2012-01-01 Thank you -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Need help with a special JOIN
This is a way to do it, but things will change if you have many attributes/object SELECT o.*, COALESCE(a1.value, a2.value) FROM objects AS o LEFT JOIN attributes AS a1 ON (a1.object_id = o.id) LEFT JOIN attributes AS a2 ON (a2.object_id = 0); On 29.09.2012 19:02, Andreas wrote: Hi, asume I've got 2 tables objects ( id int, name text ) attributes ( object_id int, value int ) attributes has a default entry with object_id = 0 and some other where another value should be used. e.g. objects ( 1, 'A' ), ( 2, 'B' ), ( 3, 'C' ) attributes ( 0, 42 ), ( 2, 99 ) The result of the join should look like this: object_id, name, value 1, 'A', 42 2, 'B', 99 3, 'C', 42 I could figure something out with 2 JOINs, UNION and some DISTINCT ON but this would make my real query rather chunky. :( Is there an elegant way to get this? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Trigger triggered from a foreign key
I have this trigger that works fine. The trigger prevents the deletion of the last record. But I want skip this trigger execution when the delete is done from a external key. How can I do this? This is the fk ALTER TABLE focgdepartment ADD CONSTRAINT fk_focgdep_idfocg FOREIGN KEY (idfocg) REFERENCES focg (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE; This is the trigger CREATE FUNCTION check_focgdepartment_delete_restricted() RETURNS trigger AS $check_focgdepartment_delete_restricted$ BEGIN IF ( (SELECT count(*) FROM focgdepartment WHERE idfocg = OLD.idfocg)=1) THEN RAISE EXCEPTION 'Last record can not be deleted'; END IF; RETURN OLD; END; $check_focgdepartment_delete_restricted$ LANGUAGE plpgsql; CREATE TRIGGER focgdepartment_delete_restricted BEFORE DELETE ON focgdepartment FOR EACH ROW EXECUTE PROCEDURE check_focgdepartment_delete_restricted(); Thank you -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] I need to fill up a sparse table in an view
2013/3/2 Andreas
> So the table looks like.
> my_numbers ( object_id int, month int, some_nr int )
>
> ( 17, 201301, 123 ),
> ( 42, 201301, 456 ),
> ( 42, 201303, 789 ),
>
> Now I need a view that fills the gaps up till the current month.
>
>
> ( 17, 201301, 123 ),
> ( 17, 201302, 123 ), <-- filled gap
> ( 17, 201303, 123 ), <-- filled gap
> ( 42, 201301, 456 ),
> ( 42, 201302, 456 ), <-- filled gap
> ( 42, 201303, 789 ),
>
>
> Is this possible?
>
Possible. Slightly different object identifiers used:
CREATE TABLE t(
id int,
mon int,
val int
);
INSERT INTO t VALUES
(17,201301,123),
(42,201301,456),
(42,201303,789);
Then the query (check results here http://sqlfiddle.com/#!12/ce8fa/1 ):
WITH dr AS (
SELECT to_char(generate_series(to_date(min(mon)::text, 'MM'),
greatest(to_date(max(mon)::text,'MM'),
date(date_trunc('mon',
now(, '1 mon'::interval),
'MM')::numeric mon
FROM t
)
, x AS (
SELECT s.id, dr.mon
FROM dr
CROSS JOIN (SELECT DISTINCT id FROM t) s
)
, g AS (
SELECT x.id, x.mon, t.val, CASE WHEN t.val IS NOT NULL THEN 1 ELSE NULL
END grp
FROM x
LEFT JOIN t USING (id, mon)
)
, nr AS (
SELECT g.id, g.mon, g.val, g.grp, sum(g.grp) OVER (ORDER BY id,mon) gnr
FROM g
)
SELECT *,
coalesce(val, min(val) OVER (PARTITION BY gnr)) the_one
FROM nr
ORDER BY 1,2;
1) “dr” is used to generate a range of months from the minimal found in the
“t” table up to either current or the max one found in the “t”, whichever
is bigger. A bit tricky query, if you can get the series of month some
other way — feel free;
2) “x” will create a CROSS join of all the “id” with all the months;
3) “g” will create almost ready result with all the gaps in place, new
service column is introduced to create groups;
4) within “nr” group identifiers are being summed, thus forming a unique
group number for each entry and gap rows that follows it;
5) finally, NULL entries are replaced with the correct ones.
To obtain the desired output, you should “SELECT id, mon, the_one” in the
last query. Feel free to query each of the intermediate steps to see how
data transforms.
You might want to get rid of the CTEs and write a bunch of subqueries to
avoid optimization fences of CTEs, as for bigger tables this construct will
be performing badly.
--
Victor Y. Yegorov
[SQL] ZIP function
Is there a function that will give the resulting zip content for a
string?
Like SELECT zip('test data');?
Thank you.
[SQL] function array_to_string(text[]) does not exist
Hello When I run : SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min AS CHAR), '-', CAST(ltrv1.val_max AS CHAR), ' ', ltrv1.comentarii))) FROM lab_tests_reference_values ltrv1 GROUP BY ltrv1.val_min, ltrv1.val_max, ltrv1.comentarii; I get the error: ERROR: function array_to_string(text[]) does not exist LINE 1: SELECT array_to_string(array_agg(CONCAT(CAST (ltrv1.val_min ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. I tryed to cast but it's still not working. Thanku you.
[SQL] Readline ... a lot of problems...
Hello. I'am Victor, from Brazil. I'am have a problem with postgresql. I can't use the libreadline and i don't know why. It is not working more. What kinds of flags have i put on configure script? how can i know what's the problem? Thanks a lot for your atemption, and sorry my terrible english I'am waiting an answer soon # #Victor Hugo Germano #[EMAIL PROTECTED] #www.inf.ufsc.br/~victorhg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
