Re: [GENERAL] Find out encoding of data
joynes wrote:
> What I really want to see is the hexadecimal or octal value
> of the bytes of the retrieved data. Can postgres give me
> this somehow (without exporting tables to files and look at
> the files).
Maybe 'decode' can help you:
test=> SELECT decode('10EUR', 'escape');
decode
10\342\202\254
(1 row)
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] how to ignore invalid byte sequence for encoding without using sql_ascii?
On Thu, Sep 27, 2007 at 02:28:27AM -0700, [EMAIL PROTECTED] wrote: > I am now importing the dump file of wikipedia into my postgresql using > maintains/importDump.php. It fails on 'ERROR: invalid byte sequence > for encoding UTF-8'. Is there any way to let pgsql just ignore the > invalid characters ( i mean that drop the invalid ones ), that the > script will keep going without die on this error. No, postgres does not destroy data. It you want bits of your data removed you need to write your own tool to do it. That said, are you sure that the data you're importing is UTF-8? Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> 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] more problems with count(*) on large table
On Mon, Oct 01, 2007 at 01:34:32PM -0400, Bill Moran wrote: > This sounds like a caching issue. My guess at what's happening is that > other operations are pushing this data out of the shared_buffers, so > when you run it, the system has to pull a bunch of tuples off the disk > to check them. If you run it again immediately, the tuples are still in > memory, and it runs very fast. You should check your ratio of system cache to shared_buffers. Pushing things out of shared buffers isn't bad if you've got a much bigger OS behind it. The cost of pulling something out of the OS cache is negligable compared to really going to disk. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> 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] more problems with count(*) on large table
On Mon, 01 Oct 2007, Mike Charnoky wrote: > I altered the table in question, with "set statistics 100" on the > timestamp column, then ran analyze. This seemed to help somewhat. Now, > queries don't seem to hang, but it still takes a long time to do the count: > * "where evtime between '2007-09-26' and '2007-09-27'" >took 37 minutes to run (result was ~12 million) > * "where evtime between '2007-09-25' and '2007-09-26'" >took 40 minutes to run (result was ~14 million) Maybe it needs several million scattered seeks which basically disable disk cache. If you can afford a database inactivity period you can for example do periodically (for example on nights or weekends): cluster pred_acc_evtime_index on prediction_accuracy; (see http://www.postgresql.org/docs/8.2/static/sql-cluster.html) This would run rather long time for the first time, but will be much faster later. This should make seeks much more local and it would help operating system to cache results. Regards Tometzky -- ...although Eating Honey was a very good thing to do, there was a moment just before you began to eat it which was better than when you were... Winnie the Pooh ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] windows and pg 8.2 (change database to another server)
On Mon, Oct 01, 2007 at 11:38:53PM +0200, Terry Yapt wrote: > Magnus Hagander escribió: > >On Sun, Sep 30, 2007 at 11:36:23PM +0200, Terry Yapt wrote: > >>First of all. I think this method is admisible. Isn't it ? > >> > > > >It is. > > > > Glad to read it :-) > > >>And second question: I think my problem is that some rights are wrong > >>after copying data folder. What are the right rights to apply to data > >>folder ? > >> > > > >Yes, most likely. You need to grant the postgres service account "Change" > >permissions (or Full Control, but Change is recommended) on the data > >directory. If you didn't do anything speicifically, it will just have > >inherited from further up in the tree, which means that the service account > >only has "Read" access. > > > >//Magnus > > > > I have tried a couple of combinations none of them was successful. > > I have tried to assign 'Full Control' to data folder and sub-folders and > files. Varying this 'Full Control' preserving inheritance, deleting > inheritance. I have tried to assign 'Full Control' to Administrators > and SYSTEM accounts/groups too. I have tried to do the same thing over > sub-folders, files and so on. Your errors certainly indicate it's a permissions issue. You should also veryfi that the read-only flag is not set on any of the files. I don't see how it could become that, but if it is that'll give the same error. Also, check the permissions on c:\, C:/Archivos de programa/ and all teh way down the tree. The postgres service account needs read access there, and write to data and below. Permissions set for SYSTEM and/or administrators make no difference at all to the server. And yes, it shoul dbe set on the file and all subdirs. Use the checkbox to overwrite all permissions on subdirs, that's the fastest way. //Magnus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Execution plan caching
Hi, I have recently used the MS SQL Server 2005 database, and found out that there is no mecanism where an execution plan can be reused between two successive calls to a view. This is only true with stored procedures. Is that also true with the Postgresql engine? Philippe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Find min year and min value
Hi there, I am trying to find in a table with different variables, countries and years the lowest year and within that year the lowest value The following SELECT works, but I wonder if it is "elegant". Can you recommend any other solution? SELECT MIN(value) AS minv FROM public_one_table.data WHERE year = ( SELECT MIN(year) AS min_year FROM public_one_table.data WHERE id_variable = 1 ) AND id_variable = 1 Thanks for any help! Stef ---(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] Find min year and min value
Stefan Schwarzer wrote: Hi there, I am trying to find in a table with different variables, countries and years the lowest year and within that year the lowest value The following SELECT works, but I wonder if it is "elegant". Can you recommend any other solution? SELECT value AS minv FROM public_on_table.data WHERE id_variable = 1 ORDER BY year, value LIMIT 1 -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Execution plan caching
Philippe Lang wrote: Hi, I have recently used the MS SQL Server 2005 database, and found out that there is no mecanism where an execution plan can be reused between two successive calls to a view. This is only true with stored procedures. Is that also true with the Postgresql engine? Well, if you prepare "SELECT * FROM my_view" then the plan for that will be cached. In general though, the query will be planned each time. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Find min year and min value
Hi there, I am trying to find in a table with different variables, countries and years the lowest year and within that year the lowest value The following SELECT works, but I wonder if it is "elegant". Can you recommend any other solution? SELECT value AS minv FROM public_on_table.data WHERE id_variable = 1 ORDER BY year, value LIMIT 1 But that brings only the min value, not the min year. I need to know both of them, something like (min year = ) 1972, (min value = ) 20 Stef ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Find min year and min value
Stefan Schwarzer wrote: Hi there, I am trying to find in a table with different variables, countries and years the lowest year and within that year the lowest value The following SELECT works, but I wonder if it is "elegant". Can you recommend any other solution? SELECT value AS minv FROM public_on_table.data WHERE id_variable = 1 ORDER BY year, value LIMIT 1 But that brings only the min value, not the min year. I need to know both of them, something like (min year = ) 1972, (min value = ) 20 SELECT year, value FROM ... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Execution plan caching
"Philippe Lang" <[EMAIL PROTECTED]> writes: > Hi, > > I have recently used the MS SQL Server 2005 database, and found out that > there is no mecanism where an execution plan can be reused between two > successive calls to a view. This is only true with stored procedures. > > Is that also true with the Postgresql engine? To the best of my knowledgle, if you PREPARE a query that uses a view, either explicitly or implicitly via your database driver, the plan will be cached. -Doug ---(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] Strange discrepancy in query performance...
"Jason L. Buberel" <[EMAIL PROTECTED]> writes:
> For reference, when using JasperReports .jrxml files as the basis for
> the query, I only had to do to the following to 'force' postgres to
> treat the jasper report parameter as a number and not text, thereby
> allowing the correct index to be used:
> select * from city summary where city_master_id =
> $P{city_master_id}::bigint ...
> Query times went from 300+ seconds back down to ~100ms.
Hmm ... if Postgres were just given the parameter symbol with no type
information, I believe it would have assumed it was bigint (or in
general, the same type as what it's being compared to). So your
problem suggests that Jasper is deliberately telling the backend that
that parameter is of type text. If that's coming from something you
did in your code, you probably ought to change the code. If not,
it seems like a bug/omission in Jasper.
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] Find min year and min value
SELECT year, value FROM ... I feel ashamed such a simple solution... gush Thanks for that! Unfortunately it doesn't stop there... If I want to find the "common smallest year" for two given variables (say, I have years 1970, 1971, 2005 for variable 1 (GDP) and 1980, 1981,... 2003) for variable 2 (Fish Catch) ). It should come up with 1980 for a given country, if there is a value for that year in both variables. Otherwise 1981, etc... How would I do that? I really have no clue... (my table looks something like this: id_variable |year|value |id_country --- 1 | 2001| 123 | 1 1 | 2002| 125 | 1 1 | 2003| 128 | 1 1 | 2004| 132 | 1 1 | 2005| 135 | 1 1 | 2001| 412 | 2 1 | 2002| 429 | 2 1 | 2003| 456 | 2 1 | 2004| 465 | 2 1 | 2005| 477 | 2 2 | 1980| 83 | 1 2 | 1981| 89 | 1 ) Thanks for any hints, Stef ---(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] Find min year and min value
Stefan Schwarzer wrote: SELECT year, value FROM ... I feel ashamed such a simple solution... gush Thanks for that! Can be easy to over-complicate things when you've been thinking about them too long. Unfortunately it doesn't stop there... If I want to find the "common smallest year" for two given variables (say, I have years 1970, 1971, 2005 for variable 1 (GDP) and 1980, 1981,... 2003) for variable 2 (Fish Catch) ). It should come up with 1980 for a given country, if there is a value for that year in both variables. Otherwise 1981, etc... In that case you will need two subqueries, but it's just a matter of converting your description to SQL. SELECT yr1, gdp.val1 AS gdp_val, fish_catch.val2 AS fish_catch_val FROM (SELECT year AS yr1, value AS val1 FROM data WHERE id_variable = 1 ) AS gdp, (SELECT year AS yr2, value AS val2 FROM data WHERE id_variable = 2 ) AS fish_catch WHERE gdp.yr1 = fish_catch.yr2 ORDER BY gdp.yr1 LIMIT 1; Here I've aliases (renamed) the columns and the sub-queries, but I'd probably just alias the sub-queries in real-life. You could write it as a JOIN if you prefer that style, or use the MIN() aggregate (although I'd guess that the ORDER BY/LIMIT might prove faster). So, I'd perhaps use: SELECT gdp.year, gdp.val AS gdp_val, fish_catch.val AS fish_catch_val FROM (SELECT year,value FROM data WHERE id_variable=1) AS gdp JOIN (SELECT year, value FROM data WHERE id_variable=2) AS fish_catch USING (year) ORDER BY gdp.year LIMIT 1; -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Find min year and min value
On Oct 2, 2007, at 9:29 , Stefan Schwarzer wrote: How would I do that? I really have no clue... The key is to build it up in steps. select id_country, year, var_1, val_1, var_2, val_2 -- Second step: -- value for year for each country of var_1 from (select id_country, year, id_variable as var_1, "value" as val_1 from my_table) as val_1 -- value for year for each country for var_2 natural join (select id_country, year, id_variable as var_2, "value" as val_2 from my_table) as val_2 -- First step -- for each country, find the minimum common year (which the join will do) for the two -- variables you're interested in (var_1 and var_2). natural join (select id_country, var_1, var_2, min(year) as year from (select id_country, year, id_variable as var_1 from my_table) as var_1 natural join (select id_country, year, id_variable as var_2 from my_table) as var_2 group by id_country, var_1, var_2) as min_common_year where id_country = :id_country and var_1 = :var_1 and var_2 = :var_2; Check your explain analyze output: if the planner doesn't push up the :var_1, :var_2, and :id_country_id values up into subqueries, you might want to add them as where clauses. As an aside, I assume you've rewritten the table column names: if you haven't, as it's an SQL keyword, "value" is a particularly poor choice of column name. I'd probably rename "year" as well. Hope this helps. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Strange discrepancy in query performance...
I agree that this is a bug in JasperReports. I've been stepping throgh their code to determine where the paramter type is set to 'java.lang.String', but have not yet figured out how their Java API will allow me to override that with 'java.lang.Integer' or something more appropriate. If I figure something out, I'll post to the list. Regards, jason Tom Lane wrote: Hmm ... if Postgres were just given the parameter symbol with no type information, I believe it would have assumed it was bigint (or in general, the same type as what it's being compared to). So your problem suggests that Jasper is deliberately telling the backend that that parameter is of type text. If that's coming from something you did in your code, you probably ought to change the code. If not, it seems like a bug/omission in Jasper. regards, tom lane
Re: [GENERAL] Partitioned table limitation
> > I've played around with as many as 1,000 child tables. By then, the > planning time becomes noticeably longer than for a single table, but > the response time is still so much faster that it's worth it. Note > I'm talking only a fraction of a second planning time, even at 1,000 > tables. > > If you are going over 100 tables, make sure you're using triggers for > updating the child tables not rules, as rules are far too slow when > there's over 50 or so tables to look choose from. > > ---(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- Hide quoted text - > > - Show quoted text - Scott, Could you share a snippet on how to use trigger for this? TQ, G ---(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] Feature Request - Defining default table space for Indexes in Conf file
Hi All, The default table space defined in db conf file is used for all database tables as well as indexes. So putting the indexes on another table space requires manually dropping and re-creating indexes. It would be nice to have a feature to define a default table space for indexes in db conf file and all indexed are created in that table space. This would allow creating a good database architecture to avoid disc contention easily. Thanks Data_arch - Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games.
Re: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
On 10/1/07, MrKrinkle <[EMAIL PROTECTED]> wrote: > On Oct 1, 7:28 am, "Ben Trewern" <[EMAIL PROTECTED]> wrote: > > > You can use the pgAdmin's grant wizard to do what you want. > > > > I shouldn't need a GUI tool to do something so mundane. And you don't. Very very simple plsql code to do it has been posted dozens of times. You don't have to be a genius to cut and paste it. > > BTW thanks for the polite e-mail. :-/ > > Given that it's been four years and countless requests for this, a > wakeup call style email is justified. Way to go! You tell those guys who you don't pay how to do things! Those uncaring bastards! Now they'll listen right up and do what you tell them! Why, if you shout a little louder, we might have multi-master synchronous replication this time next week! Wait, I've got a better idea. Get out your checkbook and write them a check to do it. Honestly, it's a feature I'd love to have too. But seeing as how I can't implement it, and don't have a fat enough wallet to pay them to do it, I'll just put in my vote for it like everyone else. Minus the F bomb. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Find min year and min value
Stefan Schwarzer wrote: >> SELECT year, value FROM ... > > I feel ashamed such a simple solution... gush Thanks for that! > > Unfortunately it doesn't stop there... > > If I want to find the "common smallest year" for two given variables > (say, I have years 1970, 1971, 2005 for variable 1 (GDP) and 1980, > 1981,... 2003) for variable 2 (Fish Catch) ). It should come up with > 1980 for a given country, if there is a value for that year in both > variables. Otherwise 1981, etc... > > How would I do that? I really have no clue... > > (my table looks something like this: > > id_variable |year|value |id_country > --- > 1 | 2001| 123 | 1 > 1 | 2002| 125 | 1 > > > 2 | 1980| 83 | 1 > 2 | 1981| 89 | 1 > > > ) > > Thanks for any hints, As others have noted, the query *can* be written. But it appears to me that you are struggling against your table layout. Before struggling with ever more complicated queries, I'd consider restructuring your table(s). There are many possibilities depending on the current nature of your data, how you expect it to change and the queries you expect to run against it. For example: country_id data_year gdp fish_catch Then your query may be as simple as, say: select min(year) from your_table where country_id = xxx and gdp is not null and fish_catch is not null; or select year, gdp, fish_catch from your_table where country_id = xxx and gdp is not null and fish_catch is not null order by year desc, gdp desc, fish_catch desc limit 1; Alternately, you could have a gdp table and a fish_catch table which would be easily joined to give the same result. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Select too many ids..
Hi.. I have a id list and id list have 2 million dinamic elements.. I want to select what id have point.. I try: SELECT id, point FROM table WHERE id in (IDLIST) This is working but too slowly and i need to performance.. I'm sorry my bad english. King regards.. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
On Oct 1, 7:28 am, "Ben Trewern" <[EMAIL PROTECTED]> wrote: > You can use the pgAdmin's grant wizard to do what you want. > I shouldn't need a GUI tool to do something so mundane. > > BTW thanks for the polite e-mail. :-/ Given that it's been four years and countless requests for this, a wakeup call style email is justified. ---(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] Find out encoding of data
Hi!
This doesnt work for me but it is exactly what I want. When I run your
example I just get:
>SELECT decode('10EUR', 'escape');
decode
10EUR
(1 rad)
I get the same result, both if the database is UTF8 or ISO-Latin1 and also
with different versions of postgres (7 and 8)
And when I read the documentation for 'decode' it tells that it just decodes
binary strings encoded with 'encode'.
How did you get that result from running decode?
/br joynes
--
View this message in context:
http://www.nabble.com/Find-out-encoding-of-data-tf4549554.html#a12995704
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
[EMAIL PROTECTED] ("Scott Marlowe") writes:
> About 75% of the time I see that response, it comes with the actual
> code to do just that. I.e. cut and paste and voila, you've got the
> functions.
>
>> You write the function. Fuck the standard and
>> wake up.
>
> Me? What about you? The fact is there's a limited number of hackers
> capable of writing what you're asking for cleanly and correctly, and
> they're working on other stuff. Asking them politely has been know to
> work. Using the F word not so much.
"Feel free to contribute build files. Or work on your motivational
skills, and maybe someone somewhere will write them for you..."
-- "Fredrik Lundh" <[EMAIL PROTECTED]>
This is the usual sort of *right* answer to this...
It has tended to turn into recommendations to "write a function"
because the desired functionality is almost never a constant. People
*claim* that they want to grant access to everything, but there are
commonly exceptions.
"Oh, but that table needs to be kept secure from the users..."
- Does it cover all tables? Really?
- How about views?
- How about functions? Operators?
- What about the security definer functions? Are they exceptions?
- How to deal with the exceptions that there are sure to be?
The trouble is that "GRANT ON *.*" seems to be a lazy shortcut for
someone who *thinks* they're trying to secure their system, but that
would rather say "well, everything" as opposed to looking at things
properly.
That is, if you don't know what tables and other objects need to be
secured, how can you have any idea that you're handling the securing
of your application properly???
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://linuxdatabases.info/info/nonrdbms.html
Should vegetarians eat animal crackers?
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Partitioned table limitation
On Oct 2, 1:38 am, [EMAIL PROTECTED] ("paul rivers") wrote:
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > [EMAIL PROTECTED] On Behalf Of Goboxe
> > Sent: Monday, October 01, 2007 2:18 AM
> > To: [EMAIL PROTECTED]
> > Subject: [GENERAL] Partitioned table limitation
>
> > Hi,
>
> > Are there any limitations on number of child tables that can be use
> > in
> > partitioned table?
>
> > [snip]
>
> We currently use partitioning by date and id, with 1/4 a year of dates and
> approximately 10 IDs (and slowly increasing). Each partition runs from
> around 1 million to 20 million rows.
>
> Whether it's recommended or not, I don't know. But for us, the partitioning
> works exactly as advertised. As with anything new, I'd take the time to
> setup a simple test to see if it works for you, too.
>
> In particular, be sure to check the documentation on caveats. You'll find
> these a little stricter than partitioning issues in Oracle or SQL Server.
>
> HTH,
> Paul
>
Thanks Paul for your inputs.
I am not really clear when you said "partitioning by date and id, with
1/4 a year of dates and
approximately 10 IDs". Could you give some examples of your tables?
TQ,
G
---(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] pgcrypto digest_exists replacement?
I see that digest_exists is now gone, but I haven't been able to find anything in the lists about why... I was actually going to make use of that; is there anything that replaces it? -- Decibel! [EMAIL PROTECTED] (512) 569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Strange behavior of TRIGGER
There is a table with unique rows. But before insert trigger checks
data and returns NULL if such record exist and NEW if not.
But from time to time I'm getting an error in my log file
faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
('93701','41719')
context: ERROR: duplicate key violates unique constraint "viewed_search_members"
If to try execute this query manually - everything is ok. But what is
the reason of such behavior? How it can be?
Here is schema:
CREATE TABLE viewed_members
(
member_id integer NOT NULL,
viewed_id integer NOT NULL,
viewed_date timestamp with time zone NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX viewed_search_members
ON viewed_members
USING btree
(member_id, viewed_id);
ALTER TABLE viewed_members CLUSTER ON viewed_search_members;
CREATE OR REPLACE FUNCTION viewed_members()
RETURNS "trigger" AS
$BODY$
DECLARE
viewed RECORD;
BEGIN
IF (TG_OP = 'INSERT') THEN
SELECT * INTO viewed FROM viewed_members WHERE member_id =
NEW.member_id AND viewed_id = NEW.viewed_id;
IF NOT FOUND THEN
--RAISE NOTICE 'Adding new record';
RETURN NEW;
ELSE
--RAISE NOTICE 'Record exist';
RETURN NULL;
END IF;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
CREATE TRIGGER viewed_members
BEFORE INSERT
ON viewed_members
FOR EACH ROW
EXECUTE PROCEDURE viewed_members();
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Feature Request - Defining default table space for Indexes in Conf file
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/01/07 13:22, S Sharma wrote: > Hi All, > > The default table space defined in db conf file is used for all database > tables as well as indexes. So putting the indexes on another table space > requires manually dropping and re-creating indexes. > It would be nice to have a feature to define a default table space for > indexes in db conf file and all indexed are created in that table space. ALTER INDEX foo SET TABLESPACE bar; > This would allow creating a good database architecture to avoid disc > contention easily. How difficult is it to specify tablespace when creating an index? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHAnExS9HxQb37XmcRAiceAJ9vUNKVa8voo2gISHhzDgKY4OOkuQCgxuxG jR6S8CY4INa+fKbOE00oqZk= =3QvI -END PGP SIGNATURE- ---(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] Select too many ids..
On 10/1/07, Abandoned <[EMAIL PROTECTED]> wrote: > Hi.. > I have a id list and id list have 2 million dinamic elements.. > I want to select what id have point.. > I try: > > SELECT id, point FROM table WHERE id in (IDLIST) > > This is working but too slowly and i need to performance.. > > I'm sorry my bad english. > King regards.. Try adding another bit of where clause: SELECT id, point FROM table WHERE id in (IDLIST) and point is not null it may be faster with a partial index " on table(id) where point is null " or something like that. What does explain / explain analyze say about the query? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Select too many ids..
On 10/1/07, Abandoned <[EMAIL PROTECTED]> wrote: > Hi.. > I have a id list and id list have 2 million dinamic elements.. > I want to select what id have point.. > I try: > > SELECT id, point FROM table WHERE id in (IDLIST) > > This is working but too slowly and i need to performance.. > > I'm sorry my bad english. > King regards.. DDL please... ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] create visual query in web applications
Do you know any library or application so that a user could create visually a query in a web application? I think that now with ajax and web 2.0 it should be possible, but I don't know any product that does it. signature.asc Description: OpenPGP digital signature
Re: [GENERAL] more problems with count(*) on large table
The db server is pretty beefy: 2x Xeon 3.20 GHz with 6G RAM. The io subsystem is a 550G 4-disk SATA 150 RAID 10 array connected via a 3ware 9500S-8 controller (Seagate Nearline ST3400632NS drives). Currently, shared_buffers is set to 5 (nearly 400M) As for the data stored in this large table, there are 15 columns. Each row takes roughly 134 bytes to store, not counting the index. So, for one day's worth of data we are talking about 1.5G/day (1.8G with the index). That's about 11.5M rows/day. Although the data isn't stored exactly sequentially by the indexed time field, it is pretty close. If it takes PG ~40 minutes to count(*) one day's worth of records, the avg throughput is 786k/s. Watching iostat during the count(*) operation, I see average read speeds in the range of 1100-1500k/s. I guess I would expect postgres to perform a count(*) faster. When I run benchmarks on the machine with hdparm (with the db insert process running), I see the disk averages > 80MB/sec for reads # hdparm -tT /dev/sdb1 /dev/sdb1: Timing cached reads: 3884 MB in 2.00 seconds = 1942.85 MB/sec Timing buffered disk reads: 248 MB in 3.01 seconds = 82.49 MB/sec Maybe PG has to do a lot of random disk access? I'm running bonnie++ now to get more detailed disk performance info. As Tomasz pointed out maybe using CLUSTER would help, but this probably takes a long time to perform. Again, the only other thing happening with the db: a separate process is inserting data into this table. I have checkpoint_segments set to 64 so that pg is not constantly thrashing the disk with writes. The transaction log is on a separate disk. Mike Bill Moran wrote: > In response to Mike Charnoky <[EMAIL PROTECTED]>: > >> This is strange... count(*) operations over a period of one day's worth >> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the >> first time the data is queried it takes about 40 minutes. If I try the >> query again, it finishes in 1-2 minutes! > > This sounds like a caching issue. My guess at what's happening is that > other operations are pushing this data out of the shared_buffers, so > when you run it, the system has to pull a bunch of tuples off the disk > to check them. If you run it again immediately, the tuples are still in > memory, and it runs very fast. > > If this is the case, you can speed up things by adding RAM/shared_buffers, > or by moving to faster disks. The RAM solution is going to give you the > biggest performance improvement. > > However, if there's enough other data on this system, you may have > difficulty getting enough RAM to mitigate the problem, in which case, > faster disks are going to be your best bet. > > How much RAM do you have, and how much of it is allocated to shared_buffers? > What's your IO subsystem look like? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Find min year and min value
On Tue, Oct 02, 2007 at 04:29:02PM +0200, Stefan Schwarzer wrote: > If I want to find the "common smallest year" for two given variables > (say, I have years 1970, 1971, 2005 for variable 1 (GDP) and > 1980, 1981,... 2003) for variable 2 (Fish Catch) ). It should come up > with 1980 for a given country, if there is a value for that year in > both variables. Otherwise 1981, etc... > How would I do that? I really have no clue... > (my table looks something like this: > id_variable |year|value |id_country > --- > 1 | 2001| 123 | 1 select min(year) from (select year from table where id_variable in (1,2) group by year having count(distinct id_variable) = 2) x; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] more problems with count(*) on large table
In response to Mike Charnoky <[EMAIL PROTECTED]>: > The db server is pretty beefy: 2x Xeon 3.20 GHz with 6G RAM. The io > subsystem is a 550G 4-disk SATA 150 RAID 10 array connected via a 3ware > 9500S-8 controller (Seagate Nearline ST3400632NS drives). Currently, > shared_buffers is set to 5 (nearly 400M) The advice on 8.x systems has been to start with 1/4-1/3 of the available RAM on the system, and fine-tune from there. Unless there are other (non-postgresql) functions this machine serves, you should probably up shared_buffers to about 2G. From there, you may find that your workload benefits from even more, or possibly less, but 400M seems pretty small for a 6G system. > As for the data stored in this large table, there are 15 columns. Each > row takes roughly 134 bytes to store, not counting the index. So, for > one day's worth of data we are talking about 1.5G/day (1.8G with the > index). That's about 11.5M rows/day. Although the data isn't stored > exactly sequentially by the indexed time field, it is pretty close. How much other data is this server pushing around? If there's only that one table in that one database, then something is wrong, as that whole thing should be in the filesystem cache all the time. Otherwise, you have to consider what other operations may be needing memory and moving those tables out of the way. > If it takes PG ~40 minutes to count(*) one day's worth of records, the > avg throughput is 786k/s. Watching iostat during the count(*) > operation, I see average read speeds in the range of 1100-1500k/s. Could be a lot of fragmentation of that table. Keep in mind that if you're deleting records occasionally, that free space will get reused, which means an insert might not insert sequentially, it might go all over the table. > I guess I would expect postgres to perform a count(*) faster. When I > run benchmarks on the machine with hdparm (with the db insert process > running), I see the disk averages > 80MB/sec for reads > > # hdparm -tT /dev/sdb1 > /dev/sdb1: > Timing cached reads: 3884 MB in 2.00 seconds = 1942.85 MB/sec > Timing buffered disk reads: 248 MB in 3.01 seconds = 82.49 MB/sec > > Maybe PG has to do a lot of random disk access? I'm running bonnie++ > now to get more detailed disk performance info. As Tomasz pointed out > maybe using CLUSTER would help, but this probably takes a long time to > perform. If you can spare the time, give it a try to see if it helps. > Again, the only other thing happening with the db: a separate process is > inserting data into this table. I have checkpoint_segments set to 64 so > that pg is not constantly thrashing the disk with writes. The > transaction log is on a separate disk. > > > Mike > > Bill Moran wrote: > > In response to Mike Charnoky <[EMAIL PROTECTED]>: > > > >> This is strange... count(*) operations over a period of one day's worth > >> of data now take ~1-2 minutes to run or ~40 minutes. It seems that the > >> first time the data is queried it takes about 40 minutes. If I try the > >> query again, it finishes in 1-2 minutes! > > > > This sounds like a caching issue. My guess at what's happening is that > > other operations are pushing this data out of the shared_buffers, so > > when you run it, the system has to pull a bunch of tuples off the disk > > to check them. If you run it again immediately, the tuples are still in > > memory, and it runs very fast. > > > > If this is the case, you can speed up things by adding RAM/shared_buffers, > > or by moving to faster disks. The RAM solution is going to give you the > > biggest performance improvement. > > > > However, if there's enough other data on this system, you may have > > difficulty getting enough RAM to mitigate the problem, in which case, > > faster disks are going to be your best bet. > > > > How much RAM do you have, and how much of it is allocated to shared_buffers? > > What's your IO subsystem look like? > > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Find min year and min value
On Oct 2, 2007, at 11:10 , Steve Crawford wrote: As others have noted, the query *can* be written. But it appears to me that you are struggling against your table layout. The current schema he has is commonly called EAV (entity-attribute- value) and is generally frowned upon. Now, in his particular case it may be justified if the "value" column values are actually all of the same type, such as currency amounts for each category. If this is the case, I suggest renaming the column to be more descriptive of what is actually stored: likewise the id_variable column. Before struggling with ever more complicated queries, I'd consider restructuring your table(s). There are many possibilities depending on the current nature of your data, how you expect it to change and the queries you expect to run against it. For example: country_id data_year gdp fish_catch This would be one way to do it. However, each time you add a new category you'd need to add a new column to the table: not very flexible. You can also have the same functionality by adding a new table for each category: Alternately, you could have a gdp table and a fish_catch table which would be easily joined to give the same result. Expanding on this: create table fish_catches (country text not null, data_year date not null, primary key (country, data_year), fish_catch numeric not null); create table gdp (country text not null reference countries data_year date not null, primary key (country, data_year), gdp numeric not null); This makes your queries quite simple: select country, data_year, fish_catch, gdp from fish_catches natural join gdp where country = :country order by data_year limit 1; or select country, data_year, fish_catch, gdp from fish_catches natural join gdp natural join (select country, min(data_year) as data_year from gdp natural join fish_catch group by country) min_data_year where country = :country; Splitting categories into separate tables also eliminates the necessity of worrying about NULL, which can lead to unexpected behavior if you aren't careful. Michael Glaesemann grzm seespotcode net ---(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] Strange behavior of TRIGGER
On Mon, 2007-10-01 at 23:19 +0600, Nurlan Mukhanov wrote:
> There is a table with unique rows. But before insert trigger checks
> data and returns NULL if such record exist and NEW if not.
>
> But from time to time I'm getting an error in my log file
>
> faled query: INSERT INTO viewed_members (member_id, viewed_id) VALUES
> ('93701','41719')
> context: ERROR: duplicate key violates unique constraint
> "viewed_search_members"
>
The trigger you wrote does not lock the table. A concurrent transaction
might insert a record with the same (member_id, viewed_id) that might
not yet be visible at the time of your trigger's SELECT.
To do this kind of thing safely, your trigger needs to lock the table
against writes before the SELECT operation.
That has a performance penalty, of course. What you should probably do
instead is just rely on the unique index to report an error. If you
don't want an error, you should catch the error in pl/pgsql as described
here:
http://www.postgresql.org/docs/current/static/plpgsql-control-
structures.html#PLPGSQL-ERROR-TRAPPING
That will perform better and allow you to disregard records without a
unique (member_id, viewed_id).
Regards,
Jeff Davis
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
On Tue, 2007-10-02 at 12:06 -0400, Chris Browne wrote: > It has tended to turn into recommendations to "write a function" > because the desired functionality is almost never a constant. People > *claim* that they want to grant access to everything, but there are > commonly exceptions. > > "Oh, but that table needs to be kept secure from the users..." > > - Does it cover all tables? Really? > - How about views? > - How about functions? Operators? > - What about the security definer functions? Are they exceptions? > > - How to deal with the exceptions that there are sure to be? > I think that .* would be a bad idea, but a macro for .* might not be so bad. There are already different GRANT commands for TABLE, FUNCTION, SCHEMA, SEQUENCE, etc. I'm sure there are exceptions that would not be covered by such a blunt tool, but granularity is not the point of this feature. > The trouble is that "GRANT ON *.*" seems to be a lazy shortcut for > someone who *thinks* they're trying to secure their system, but that > would rather say "well, everything" as opposed to looking at things > properly. > > That is, if you don't know what tables and other objects need to be > secured, how can you have any idea that you're handling the securing > of your application properly??? A reasonable use case for this feature would be adding a read-only reporting role that needs access to a group of tables that all happen to be within a schema. This isn't critical, but for people who use an ORM that don't want to think about the database, it's handy. That being said, I'm not volunteering to write it, especially not in response to a rude request. Regards, Jeff Davis ---(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] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
On Mon, 2007-10-01 at 10:52 -0700, MrKrinkle wrote: > Given that it's been four years and countless requests for this, a > wakeup call style email is justified. > A "wakeup call" might be justified if: (1) it's on -advocacy (after all, the primary threat in his email is that we would lose users...) (2) it's good advocacy, i.e. not rude, insulting, demanding, or vulgar Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] It's time to support GRANT SELECT,UPDATE,...,...,... ON database.* to username
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Jeff Davis wrote: > On Tue, 2007-10-02 at 12:06 -0400, Chris Browne wrote: >> It has tended to turn into recommendations to "write a function" >> because the desired functionality is almost never a constant. People >> *claim* that they want to grant access to everything, but there are >> commonly exceptions. > A reasonable use case for this feature would be adding a read-only > reporting role that needs access to a group of tables that all happen to > be within a schema. > > This isn't critical, but for people who use an ORM that don't want to > think about the database, it's handy. I could easily argue that this is more a problem than a solution. Don't get me wrong, I understand your point but frankly, if one is willing to take such a lax approach to your data security... they should just run flat files with RAID 0 ;) Joshua D. Drake > > That being said, I'm not volunteering to write it, especially not in > response to a rude request. > > Regards, > Jeff Davis > > > ---(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 > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAqJ6ATb/zqfZUUQRAv8rAJ9Q+36xiJEDSSymkueS/HmJJOlVNwCbBKHY NxC9TbGJLy6qpzYxBwI6vdM= =L5pF -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Partitioned table limitation
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Goboxe
> Sent: Monday, October 01, 2007 11:26 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Partitioned table limitation
>
> On Oct 2, 1:38 am, [EMAIL PROTECTED] ("paul rivers") wrote:
> > > -Original Message-
> > > From: [EMAIL PROTECTED] [mailto:pgsql-general-
> > > [EMAIL PROTECTED] On Behalf Of Goboxe
> > > Sent: Monday, October 01, 2007 2:18 AM
> > > To: [EMAIL PROTECTED]
> > > Subject: [GENERAL] Partitioned table limitation
> >
> > > Hi,
> >
> > > Are there any limitations on number of child tables that can be use
> > > in
> > > partitioned table?
> >
> > > [snip]
> >
> > We currently use partitioning by date and id, with 1/4 a year of dates
> and
> > approximately 10 IDs (and slowly increasing). Each partition runs from
> > around 1 million to 20 million rows.
> >
> > Whether it's recommended or not, I don't know. But for us, the
> partitioning
> > works exactly as advertised. As with anything new, I'd take the time to
> > setup a simple test to see if it works for you, too.
> >
> > In particular, be sure to check the documentation on caveats. You'll
> find
> > these a little stricter than partitioning issues in Oracle or SQL
> Server.
> >
> > HTH,
> > Paul
> >
>
>
> Thanks Paul for your inputs.
>
> I am not really clear when you said "partitioning by date and id, with
> 1/4 a year of dates and
> approximately 10 IDs". Could you give some examples of your tables?
>
>
> TQ,
> G
>
Sure.
The largest logical table has a primary key of fw_id, fw_date, fw_line_nbr.
We partition on fw_id, fw_date.
fw_date ranges from today to about 120 days ago. There are no gaps for any
fw_id in this rolling window. Each fw_id + fw_date has between 1-20 million
rows, though most of them tend toward the smaller end of that scale.
We also generate child tables (partitions) for a few days into the future as
part of a nightly maintenance job. We also drop ones older than the 120
days. So all told, we have around 1400 partitions or so, and around a
trillion rows of data, all told. The rows average about 700 bytes or so,
wide, with date, time, inet, cidr, varchar, bigint smallint, and int types.
There are a variety of different processes loading the data constantly
during the day. This data is used for ad-hoc troubleshooting during the
day, plus some near real-time monitoring alerts. It sees a fair amount of
reading during the day. On a nightly basis, it is rolled up into a
summarized format, and we keep this rollup data for years. These rollup
tables are partitioned too, but it's not on the same scale as the above
table. The rollup data is used for all kinds of trend analysis, further
reporting, etc.
HTH,
Paul
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Finding number of rows deleted in a stored procedure
Newbie to pl/pgsql here. I'm trying to create a function that cleans up the foreign keys referring to a particular row (if any exist), then removes the row (if it exists), and returns the number of rows of br_role that were deleted (0 or 1). Newbie stored procedure: CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ BEGIN DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; RETURN DELETE FROM br_role WHERE role_pk = del_role_pk; END; $$ LANGUAGE plpgsql; Here's what hapens when I call it in psql using the "SELECT proc(...);" syntax: bedrock=> select delete_role(1892); ERROR: column "delete" does not exist CONTEXT: SQL statement "SELECT DELETE FROM br_role WHERE role_pk = $1 " PL/pgSQL function "delete_role" line 4 at return Hm. That's not quite right. It should be returning the result of the DELETE query, not the DELETE query itself. I did come across FOUND, which leads to this: CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ BEGIN DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; DELETE FROM br_role WHERE role_pk = del_role_pk; IF FOUND THEN RETURN 1; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql; But this technique isn't usable in the next use case, where the number of deleted rows may be more than one. Seems nasty to have immediate values in the return statements, too. Seems like there should be some equivalent to FOUND that stores the number of updated/deleted rows, but after reading over the docs a couple of times, I haven't found it. So, how do I discover the number of rows deleted by a DELETE query? Thanks in advance, Ross -- Ross Bagley "Security is mostly a superstition. It does not exist in nature... Life is either a daring adventure or nothing." -- Helen Keller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Finding number of rows deleted in a stored procedure
Ross Bagley wrote: > I did come across FOUND, which leads to this: > > CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ > BEGIN > DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role WHERE role_pk = del_role_pk; > IF FOUND THEN > RETURN 1; > ELSE > RETURN 0; > END IF; > END; > $$ LANGUAGE plpgsql; Right. Use GET DIAGNOSTICS foo = ROW_COUNT -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Finding number of rows deleted in a stored procedure
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ross Bagley wrote: > Newbie to pl/pgsql here. > > I'm trying to create a function that cleans up the foreign keys > referring to a particular row (if any exist), then removes the row (if > it exists), and returns the number of rows of br_role that were > deleted (0 or 1). Maybe I am missing something, but wouldn't ON DELETE CASCADE do what you need? > > Newbie stored procedure: > > CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ > BEGIN > DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; > RETURN DELETE FROM br_role WHERE role_pk = del_role_pk; > END; > $$ LANGUAGE plpgsql; > > Here's what hapens when I call it in psql using the "SELECT proc(...);" > syntax: > > bedrock=> select delete_role(1892); > ERROR: column "delete" does not exist > CONTEXT: SQL statement "SELECT DELETE FROM br_role WHERE role_pk = $1 " > PL/pgSQL function "delete_role" line 4 at return > > Hm. That's not quite right. It should be returning the result of the > DELETE query, not the DELETE query itself. You don't return a query... you return the result of the query. Take a look at: http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT Section 37.6.3. Executing a Query with a Single-Row Result > > I did come across FOUND, which leads to this: > > CREATE OR REPLACE FUNCTION delete_role(del_role_pk bigint) RETURNS int AS $$ > BEGIN > DELETE FROM br_actor_role_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role_permission_mm WHERE role_fk = del_role_pk; > DELETE FROM br_role WHERE role_pk = del_role_pk; > IF FOUND THEN > RETURN 1; > ELSE > RETURN 0; > END IF; > END; > $$ LANGUAGE plpgsql; > > But this technique isn't usable in the next use case, where the number > of deleted rows may be more than one. Seems nasty to have immediate > values in the return statements, too. > > Seems like there should be some equivalent to FOUND that stores the > number of updated/deleted rows, but after reading over the docs a > couple of times, I haven't found it. > > So, how do I discover the number of rows deleted by a DELETE query? > > Thanks in advance, > Ross > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAq9zATb/zqfZUUQRAmiWAJ9SBttz97WqNPcOKCRX8PktneqaGQCfbS09 C6a02LkLzWgko9JuzjzGQaM= =6F9a -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] PITR and Compressed WALS
We have two PostgreSQL 8.2.4 servers. On one database, WALs are archived with a simple script that gzips and transfers them to an NFS file server. The other database is in perpetual recovery mode, ungizipping and processing the WALs as they appear and become complete on the file server. This has been running fine for the past few days. As soon as the gzipped WAL appears in the archived WAL directory, I see an entry in the logs that the file has been restored. Last night, I brought the database out of its perpetual recovery mode. Here are the lines from the log when this was done: [2007-10-01 23:43:03 MDT] LOG: restored log file "000104660060" from archive [2007-10-01 23:45:50 MDT] LOG: could not open file "pg_xlog/ 000104660061" (log file 1126, segment 97): No such file or directory [2007-10-01 23:45:50 MDT] LOG: redo done at 466/6070 Which is all fine, since 000104660060.gz was the last archived WAL file. The next entry in the log follows: [2007-10-01 23:45:50 MDT] PANIC: could not open file "pg_xlog/ 000104660060" (log file 1126, segment 96): No such file or directory [2007-10-01 23:45:51 MDT] LOG: startup process (PID 27624) was terminated by signal 6 [2007-10-01 23:45:51 MDT] LOG: aborting startup due to startup process failure [2007-10-01 23:45:51 MDT] LOG: logger shutting down And the database would not start up. The issue appears to be that the restore_command script itself ungzips the WAL to its destination %p, and the WAL is left in the archive directory as 000104660060.gz. By simply ungzipping the last few WALs manually in the archive directory, the database replayed them and started up successfully. I'm not sure if this should be listed as another caveat on the PITR recovery page but in the very least I wanted to post to the list so that others attempting to archive and recover compressed WALs may be aware of a potential issue. Brian Wipf <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Can't access Cluster
I'm using 7.4 before we upgrade. pg_dumpall worked fine on working cluster. I Imported it all into a virgin install of 7.4 on a different box. Used pg_ctl to restart that box after the import. All went fine. On trying to connect as a valid user on that database I get: DATE TIME FATAL: IDENT authentication failed for user "username" psql FATAL: IDENT authentication failed for user "username" On that box pg_hba.conf has... # TYPE DATABASEUSERIP-ADDRESSIP- MASK METHOD local all all ident sameuser local all all trust # IPv4-style local connections: hostall all 127.0.0.1 255.255.255.255 md5 # IPv6-style local connections: hostall all ::1 :::::::md5 I thought local would allow me w/ 'all'. None of the PG environment variables are set. I thought they would be c/o the import all. Where should I make them permanent? Ralph Smith [EMAIL PROTECTED] =
Re: [GENERAL] Finding number of rows deleted in a stored procedure
On 10/2/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Right. Use GET DIAGNOSTICS foo = ROW_COUNT Works great! Thank you. Ross -- Ross Bagley "Security is mostly a superstition. It does not exist in nature... Life is either a daring adventure or nothing." -- Helen Keller ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Finding number of rows deleted in a stored procedure
In this simplified case, given an open SQL connection, you're correct. That would simplify this query, and I'm a little embarrassed not to have seen that (obexcuse: I've been spending too much time in Java-land lately). There is more to the function than I included in my question, so it does need to be a function and can't be straight SQL. Thanks, Ross On 10/2/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Maybe I am missing something, but wouldn't ON DELETE CASCADE do what you > need? -- Ross Bagley "Security is mostly a superstition. It does not exist in nature... Life is either a daring adventure or nothing." -- Helen Keller ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg_dump
I want to be able to convert a PostgreSQL database to other formats such as Oracle, Access etc. - with, as well as without, the data. Can this task be accomplished by employing pg_dump in SQL? Bob Pawley
Re: [GENERAL] pg_dump
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bob Pawley wrote: > I want to be able to convert a PostgreSQL database to other formats such as > Oracle, Access etc. - with, as well as without, the data. > > Can this task be accomplished by employing pg_dump in SQL? If you dump with inserts, data only, then yes but it will be slow as snot to import. > > Bob Pawley - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAsWQATb/zqfZUUQRAo2nAJ93XacJFk4zNyTjGYVa35TC8WmVKQCfVA76 U2PX23XVDdWkK6E73knPAG0= =pKtH -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump
Is there a better method of transfering the database and data to between DBs? Bob - Original Message - From: "Joshua D. Drake" <[EMAIL PROTECTED]> To: "Bob Pawley" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, October 02, 2007 3:26 PM Subject: Re: [GENERAL] pg_dump -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bob Pawley wrote: I want to be able to convert a PostgreSQL database to other formats such as Oracle, Access etc. - with, as well as without, the data. Can this task be accomplished by employing pg_dump in SQL? If you dump with inserts, data only, then yes but it will be slow as snot to import. Bob Pawley - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAsWQATb/zqfZUUQRAo2nAJ93XacJFk4zNyTjGYVa35TC8WmVKQCfVA76 U2PX23XVDdWkK6E73knPAG0= =pKtH -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] PITR Recovery and out-of-sync indexes
We are running a production server off of a new database that was
synchronized using PITR recovery. We found that many of the btree
indexes were out of sync with the underlying data after bringing the
new server out of recovery mode, but the data itself appeared to be
okay.
Both servers have identical Intel processors and both are running 64-
bit PostgreSQL 8.2.4. The original server is running 64-bit openSUSE
10.2 (Linux 2.6.18.2-34-default #1 SMP Mon Jul 16 01:16:32 GMT 2007
x86_64 x86_64 x86_64 GNU/Linux) and the new server is running Mac OS
X Leopard Server.
The first tip-off that things were amiss was this error in the log:
[2007-10-02 01:12:27 MDT] [EMAIL PROTECTED] host:192.168.0.54(53976)
ERROR: duplicate key violates unique constraint
"fed_product__unique__data_feed_id_prod_id_from_src_idx"
[2007-10-02 01:12:27 MDT] [EMAIL PROTECTED] host:192.168.0.54(53976)
STATEMENT: UPDATE FED_PRODUCT SET FEEDS_TO_MERCHANT_PRODUCT_ID =
5108509 WHERE (PRODUCT_ID = decode
('C0A8003607D22F0001155F68741EFE1555FB','hex') AND
DATA_FEED_ID = decode
('C0A8001207D71800010442E7CCFC929764DE','hex'))
This update threw a duplicate key error that should have been
triggered when the row was inserted. Looking at the row and the
application logs, I verified the conflicting row was inserted in the
new database after it was brought out of recovery mode. (I included
the fed_product's table definition below).
I performed a query qualifying using equals for the data_feed_id and
product_id_from_source to find the original row and the new bogus row
and no rows were returned. I updated the query to qualify using like
'x%' instead of equals forcing a sequential scan and two, albeit
conflicting, rows were returned.
I ran a query to delete any newly inserted bogus rows (there were 85
in all) and reindexed the fed_product table. Subsequent searches and
inserts against this table work as expected.
I ran queries against other tables and many indexes were returning
zero rows for rows that exist. I have now reindexed the complete
database and everything seems okay.
In the Continuous Archiving Point-In-Time Recovery section of the
docs, one of the caveats listed is:
"Operations on hash indexes are not presently WAL-logged, so replay
will not update these indexes. The recommended workaround is to
manually REINDEX each such index after completing a recovery operation"
Is it possible there are issues with btree indexes being maintained
properly as well? Any other ideas?
Brian Wipf
Clickspace Interactive Inc.
<[EMAIL PROTECTED]>
Table "public.fed_product"
data_feed_id | bytea
| not null
date_created | timestamp without time zone
| not null
date_modified | timestamp without time zone
|
feeds_to_merchant_product_id | integer
|
feeds_to_product_id| integer
|
product_id | bytea
| not null
product_id_from_source | character varying(512)
| not null
Indexes:
"fed_product_pk" PRIMARY KEY, btree (product_id)
"fed_product__unique__data_feed_id_prod_id_from_src_idx" UNIQUE,
btree (data_feed_id, product_id_from_source)
"fed_product__additional_1__idx" btree (product_id_from_source)
"fed_product__additional_4__idx" btree
(feeds_to_merchant_product_id)
"fed_product__data_feed_id_fk_idx" btree (data_feed_id)
---(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] pg_dump
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bob Pawley wrote: > Is there a better method of transfering the database and data to between > DBs? Use your favorite language to do so. Joshua D. Drake > > Bob > > > - Original Message - From: "Joshua D. Drake" <[EMAIL PROTECTED]> > To: "Bob Pawley" <[EMAIL PROTECTED]> > Cc: > Sent: Tuesday, October 02, 2007 3:26 PM > Subject: Re: [GENERAL] pg_dump > > > Bob Pawley wrote: I want to be able to convert a PostgreSQL database to other formats such as Oracle, Access etc. - with, as well as without, the data. Can this task be accomplished by employing pg_dump in SQL? > > If you dump with inserts, data only, then yes but it will be slow as > snot to import. > Bob Pawley > > >> - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? >> http://www.postgresql.org/docs/faq - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHAuSwATb/zqfZUUQRAvbQAKCO6YQ7FqLVaZzqaOUlu8H4KF8vFACcD/4u /3d3mr3Xyd/D1e+s6tppopg= =BVWB -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 One table or many? Transactionally consistent? Live connection or intermittent? On 10/02/07 17:37, Bob Pawley wrote: > Is there a better method of transfering the database and data to between > DBs? > > Bob > > > - Original Message - From: "Joshua D. Drake" <[EMAIL PROTECTED]> > To: "Bob Pawley" <[EMAIL PROTECTED]> > Cc: > Sent: Tuesday, October 02, 2007 3:26 PM > Subject: Re: [GENERAL] pg_dump > > > Bob Pawley wrote: I want to be able to convert a PostgreSQL database to other formats such as Oracle, Access etc. - with, as well as without, the data. Can this task be accomplished by employing pg_dump in SQL? > > If you dump with inserts, data only, then yes but it will be slow as > snot to import. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHAukNS9HxQb37XmcRAmedAJ4xvRu85AXf4sjqnOU01mVyN/UVQACeP2TR gHjnN3eU93MY3iOxPX+ec2o= =4/LC -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump
On Tuesday 02 October 2007 3:37 pm, Bob Pawley wrote: > Is there a better method of transfering the database and data to between > DBs? > > Bob > > From the Postgres website PgFoundry project http://pgfoundry.org/projects/dbi-link/ Commercial products http://www.dbconvert.com/ More commercial projects (not all pertain to your question) http://www.postgresql.org/download/commercial -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] datestyle question
El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió:
> Diego Gil wrote:
> > Hi,
> >
> > I have a file to import to postgresql that have an unusual date format.
> > For example, Jan 20 2007 is 20022007, in DDMM format, without any
> > separator. I know that a 20072002 (MMDD) is ok, but I don't know how
> > to handle the DDMM dates.
>
> You could try importing those fields in a text field in a temporary
> table and then convert them from there into your final tables using the
> to_date() function.
>
> If 20022007 really means 20 Jan instead of 20 Feb, try something like:
>
No, it realy means 20 Feb. My mistake !.
> insert into my_table (my_date_field)
> select to_date(my_date_text_field, 'DDMM') - interval '1 month'
> from my_temp_table;
>
> Regards,
I finally ended coding a dirty C program to reverse the order of date
fields. Here is the code, in case anyone need it.
#define _GNU_SOURCE
#include
#include
#include
int main(void)
{
FILE * fp, *f2, *f3;
char * line = NULL;
char * field = NULL;
size_t len = 0;
ssize_t read;
int fc = 1;
fp = fopen("trxs.exp", "r");
f3 = fopen("trxs.ok", "w");
if (fp == NULL)
exit(EXIT_FAILURE);
while (getline(&line, &len, fp) != -1)
{
fc = 1;
while ((field = strsep(&line, "\t")) != NULL)
{
if (fc > 1) fprintf(f3, "\t");
if (strlen(field) == 0) {
fprintf(f3, "\\N");
}
else if ( (fc == 9 || fc == 11 || fc == 12 || fc
== 14 || fc == 16)
&& strlen(field) >= 1)
{
fprintf(f3, "%c", field[4]);
fprintf(f3, "%c", field[5]);
fprintf(f3, "%c", field[6]);
fprintf(f3, "%c", field[7]);
fprintf(f3, "-");
fprintf(f3, "%c", field[2]);
fprintf(f3, "%c", field[3]);
fprintf(f3, "-");
fprintf(f3, "%c", field[0]);
fprintf(f3, "%c", field[1]);
}
else {
fprintf(f3, "%s", field);
}
fc++;
}
}
fclose(fp);
fclose(f3);
if (line)
free(line);
if (field)
free(field);
return EXIT_SUCCESS;
}
/* fc means "field count", only fields 9,11,12,14 and 16 are date
fields. */
Thanks for all suggestions.
Regards,
Diego.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] datestyle question
On Oct 2, 2007, at 8:56 PM, Diego Gil wrote: El jue, 27-09-2007 a las 10:32 +0200, Alban Hertroys escribió: Diego Gil wrote: Hi, I have a file to import to postgresql that have an unusual date format. For example, Jan 20 2007 is 20022007, in DDMM format, without any separator. I know that a 20072002 (MMDD) is ok, but I don't know how to handle the DDMM dates. You could try importing those fields in a text field in a temporary table and then convert them from there into your final tables using the to_date() function. If 20022007 really means 20 Jan instead of 20 Feb, try something like: No, it realy means 20 Feb. My mistake !. insert into my_table (my_date_field) select to_date(my_date_text_field, 'DDMM') - interval '1 month' from my_temp_table; Regards, I finally ended coding a dirty C program to reverse the order of date fields. Here is the code, in case anyone need it. I'm glad you got something working. However, out of morbid curiousity I have to ask: why did you use C for that when you could have done it with at most a three line script or even one line directly from the shell? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Find out encoding of data
joynes wrote:
> This doesnt work for me but it is exactly what I want. When I run your
> example I just get:
>
> >SELECT decode('10EUR', 'escape');
> decode
>
> 10EUR
> (1 rad)
>
> I get the same result, both if the database is UTF8 or
> ISO-Latin1 and also
> with different versions of postgres (7 and 8)
>
> And when I read the documentation for 'decode' it tells that
> it just decodes
> binary strings encoded with 'encode'.
> How did you get that result from running decode?
I suspect that somewhere along the line the Euro symbol I
used in the query got changed to 'EUR'.
Try some other string with weird characters.
It will show all non-ASCII characters in escaped octal
notation, while ASCII characters will remain as they are.
This should help you - if I understood you correctly,
you want to know the actual bytes stored in a database
field. To find our the numeric representation of an ASCII
field, you can use the function ascii().
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Can't access Cluster
Ralph Smith wrote: > I'm using 7.4 before we upgrade. > pg_dumpall worked fine on working cluster. > I Imported it all into a virgin install of 7.4 on a different box. > Used pg_ctl to restart that box after the import. All went fine. > > > On trying to connect as a valid user on that database I get: >DATE TIME FATAL: IDENT authentication failed for user "username" >psql FATAL: IDENT authentication failed for user "username" > > > On that box pg_hba.conf has... >local all all ident sameuser See http://www.postgresql.org/docs/current/static/auth-methods.html#AEN23442 Is your operating system one of Linux, FreeBSD, NetBSD, OpenBSD, or BSD/OS? Is there a database user with the same name as the operating system user? > None of the PG environment variables are set. > I thought they would be c/o the import all. > Where should I make them permanent? Setting environment variables is your responsibility; the procedure varies depending on your operating system. On UNIX variants you usually set it in the shell profile. Yours, Laurenz Albe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Find min year and min value
As others have noted, the query *can* be written. But it appears to me that you are struggling against your table layout. The current schema he has is commonly called EAV (entity-attribute- value) and is generally frowned upon. Now, in his particular case it may be justified if the "value" column values are actually all of the same type, such as currency amounts for each category. If this is the case, I suggest renaming the column to be more descriptive of what is actually stored: likewise the id_variable column. Hmmm I am somewhat surprised to here so. After being told in this forum how "bad" my old table design was, I changed it to the current (which is less than alpha). Perhaps to summarize: Having 500 statistical global national variables for about 240 countries/ territories. Need to do regional aggregations, per Capita calculations and some completeness computations on-the-fly. The design was a table like this for each variable: id_country |1970|1971|...|2004| 2005 --- 1 | NULL| 36 | ... | 42 | 45 2 .. The new like this: id_variable |year|value |id_country --- 1 | 2001| 123 | 1 1 | 2002| 125 | 1 1 | 2003| 128 | 1 1 | 2004| 132 | 1 1 | 2005| 135 | 1 1 | 2001| 412 | 2 1 | 2002| 429 | 2 1 | 2003| 456 | 2 1 | 2004| 465 | 2 1 | 2005| 477 | 2 2 | 1980| 83 | 1 2 | 1981| 89 | 1 I thought (and did ask) about the possibility to put nevertheless - with the new table design - the variables into different tables, but nobody really got my on a track for that. So I thought the most "common" way would be to have this central table. But I am at a stage where I still can change - and would very much like to get your advice. Thanks a lot! Stef Stefan Schwarzer Lean Back and Relax - Enjoy some Nature Photography: http://photoblog.la-famille-schwarzer.de Appetite for Global Data? UNEP GEO Data Portal: http://geodata.grid.unep.ch ---(end of broadcast)--- TIP 6: explain analyze is your friend
