Re: [GENERAL] converting a specified year and week into a date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 01:36, A. Kretschmer wrote: am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes: hi guys, i was just wondering if it was at all possible to turn a year and a given week number into a real date just using postgresql commands? e.g. if i have year = 2004 and week = 1, can i turn that into say 2004-01-01 (so that the specified date is the one for the beginning of week 1 in the year 2004 You can extract the week from a given date with this: SELECT EXTRACT(WEEK FROM '2006-01-01'::date); Be careful, the 1.1. can be in the 52. week in the last year. If you know the first day in the year in week 1, then you can add 7* the given week-1 days to this date. I think she wants to do the opposite: cast 2004w1 to -MM-DD format. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0sHFS9HxQb37XmcRAqClAJ4zkTJU7hT4vSbNM/8HyRqJwbSc1wCfeGJk Tqr6q1YDe+TajGEY50Bl26Y= =8i3I -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Thank you for your email
I will be out of the office starting 02/09/2007 and will not return until 02/21/2007. I am travelling and will be back in the office only after the Lunar New Year. During this time, I will check my email and voicemail from time to time; however, response may be delayed. You can reach me on my mobile for emergencies. For IDC IT related issues, please send a mail to Help Desk or contact Valerie @ +65-6829-7705. For Goldmine or regional website issues, please contact Noopur @ +65-6829-7774. Gong Xi Fa Cai! Best regards, Sujay ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Union Query Improvement
Ray Bannon [EMAIL PROTECTED] writes: Any way to write this more efficiently? UNION - UNION ALL, perhaps? Do you really need UNION's duplicate-row- elimination behavior? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Timestamp/Timezone - does this make sense?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 01:42, Tom Lane wrote: Mike Harding [EMAIL PROTECTED] writes: Where does that extra 8 hours come from? Ellay is 8 hours west of UTC (at least on 1-Jan, at least till our congresscritters see fit to monkey with the DST laws again). What problem have you got with these answers? They look right to me. I think he's asking why: select timestamp with time zone '2007-01-01' at time zone 'America/Los_Angeles'; - 2006-12-31 16:00:00 returns a different value than select timestamp '2007-01-01' at time zone 'America/Los_Angeles'; 2007-01-01 08:00:00+00 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0sNSS9HxQb37XmcRAuFXAJ0Z82uaW7FKKAuCnYbrm/bh8MAyCgCfWUW5 2blMHVkmjhYEjsGzk0o+ybM= =GbW7 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
am Wed, dem 14.02.2007, um 2:01:09 -0600 mailte Ron Johnson folgendes: i was just wondering if it was at all possible to turn a year and a given week number into a real date just using postgresql commands? e.g. if i have year = 2004 and week = 1, can i turn that into say 2004-01-01 (so that the specified date is the one for the beginning of week 1 in the year 2004 You can extract the week from a given date with this: SELECT EXTRACT(WEEK FROM '2006-01-01'::date); Be careful, the 1.1. can be in the 52. week in the last year. If you know the first day in the year in week 1, then you can add 7* the given week-1 days to this date. I think she wants to do the opposite: cast 2004w1 to -MM-DD format. I know, but to do this do you need to know the first day in this week... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
It has come to the attention of the core team of the PostgreSQL project that insecure programming practice is widespread in SECURITY DEFINER functions. Many of these functions are exploitable in that they allow users that have the privilege to execute such a function to execute arbitrary code with the privileges of the owner of the function. The SECURITY DEFINER property of functions is a special non-default property that causes such functions to be executed with the privileges of their owner rather than with the privileges of the user invoking the function (the default mode, SECURITY INVOKER). Thus, this mechanism is very similar to the setuid mechanism in Unix operating systems. Because SQL object references in function code are resolved at run time, any references to SQL objects that are not schema qualified are resolved using the schema search path of the session at run time, which is under the control of the calling user. By installing functions or operators with appropriate signatures in other schemas, users can then redirect any function or operator call in the function code to implementations of their choice, which, in case of SECURITY DEFINER functions, will still be executed with the function owner privileges. Note that even seemingly innocent invocations of arithmetic operators are affected by this issue, so it is likely that a large fraction of all existing functions are exploitable. The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. Note that using the default search path, which includes a reference to the $user schema, is not safe when unqualified references are intended to be found in the public schema and $user schemas exist or can be created by other users. It is also not recommended to rely on rigorously schema-qualifying all function and operator invocations in function source texts, as such measures are likely to induce mistakes and will furthermore make the source code harder to read and maintain. This problem affects all existing PostgreSQL releases since version 7.3. Because this situation is a case of poor programming practice in combination with a design mistake and inadequate documentation, no security releases of PostgreSQL will be made to address this problem at this time. Instead, all users are urged to hastily correct their code as described above. Appropriate technological fixes for this problem are being investigated for inclusion with PostgreSQL 8.3. ---(end of broadcast)--- -To unsubscribe from this list, send an email to: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Timestamp/Timezone - does this make sense?
Ron Johnson [EMAIL PROTECTED] writes: I think he's asking why: select timestamp with time zone '2007-01-01' at time zone 'America/Los_Angeles'; returns a different value than select timestamp '2007-01-01' at time zone 'America/Los_Angeles'; Those are transformations in opposite directions; see the manual. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 02:13, A. Kretschmer wrote: am Wed, dem 14.02.2007, um 2:01:09 -0600 mailte Ron Johnson folgendes: i was just wondering if it was at all possible to turn a year and a given week number into a real date just using postgresql commands? e.g. if i have year = 2004 and week = 1, can i turn that into say 2004-01-01 (so that the specified date is the one for the beginning of week 1 in the year 2004 You can extract the week from a given date with this: SELECT EXTRACT(WEEK FROM '2006-01-01'::date); Be careful, the 1.1. can be in the 52. week in the last year. If you know the first day in the year in week 1, then you can add 7* the given week-1 days to this date. I think she wants to do the opposite: cast 2004w1 to -MM-DD format. I know, but to do this do you need to know the first day in this week... But she does not know the first day of the week. A lookup table would solve OP's question. You'd have to populate it, though. Shouldn't be too hard. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0sjCS9HxQb37XmcRAmeZAJ9gOnxOEIpax+bFgdIQUNxuKwgl/QCfZ5Mt N5+z1KZqRqilpq0HdTVFlLA= =ZJE5 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] [ANNOUNCE] Advisory on possibly insecure security definer functions
It has come to the attention of the core team of the PostgreSQL project that insecure programming practice is widespread in SECURITY DEFINER functions. Many of these functions are exploitable in that they allow users that have the privilege to execute such a function to execute arbitrary code with the privileges of the owner of the function. The SECURITY DEFINER property of functions is a special non-default property that causes such functions to be executed with the privileges of their owner rather than with the privileges of the user invoking the function (the default mode, SECURITY INVOKER). Thus, this mechanism is very similar to the setuid mechanism in Unix operating systems. Because SQL object references in function code are resolved at run time, any references to SQL objects that are not schema qualified are resolved using the schema search path of the session at run time, which is under the control of the calling user. By installing functions or operators with appropriate signatures in other schemas, users can then redirect any function or operator call in the function code to implementations of their choice, which, in case of SECURITY DEFINER functions, will still be executed with the function owner privileges. Note that even seemingly innocent invocations of arithmetic operators are affected by this issue, so it is likely that a large fraction of all existing functions are exploitable. The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. Note that using the default search path, which includes a reference to the $user schema, is not safe when unqualified references are intended to be found in the public schema and $user schemas exist or can be created by other users. It is also not recommended to rely on rigorously schema-qualifying all function and operator invocations in function source texts, as such measures are likely to induce mistakes and will furthermore make the source code harder to read and maintain. This problem affects all existing PostgreSQL releases since version 7.3. Because this situation is a case of poor programming practice in combination with a design mistake and inadequate documentation, no security releases of PostgreSQL will be made to address this problem at this time. Instead, all users are urged to hastily correct their code as described above. Appropriate technological fixes for this problem are being investigated for inclusion with PostgreSQL 8.3. ---(end of broadcast)--- -To unsubscribe from this list, send an email to: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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 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] converting a specified year and week into a date
am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes: hi guys, i was just wondering if it was at all possible to turn a year and a given week number into a real date just using postgresql commands? e.g. if i have year = 2004 and week = 1, can i turn that into say 2004-01-01 (so that the specified date is the one for the beginning of week 1 in the year 2004 I have found this little function, not realy what you want but trivial to adapt to your problem: (it returns a string with first and last day of the week) create or replace function get_week(IN jahr int, IN kw int) returns text as $$ declare datum date; ret text; begin datum = (jahr || '-01-01')::date; loop exit when extract(dow from datum) = 4; datum = datum + '1day'::interval; end loop; ret = to_char(datum+(7*(kw-1)-3||'days')::interval,'dd-mm-') || ' - ' || to_char(datum+(3+7*(kw-1)||'days')::interval,'dd-mm-'); return ret; end; $$ language plpgsql immutable strict; test=*# select get_week(2007,2); get_week - 08-01-2007 - 14-01-2007 (1 row) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 02:52, A. Kretschmer wrote: am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes: [snip] test=*# select get_week(2007,2); get_week - 08-01-2007 - 14-01-2007 (1 row) Is that week #2? If weeks start on Sunday (which is what they do in the US), then week #2 would either start on 04-Jan or 11-Jan (depending on whether the 01-Jan partial week is considered week #1 or week #0). -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0tH5S9HxQb37XmcRAkcwAJkBy2zGzsOoHQYMWpQyy/gWkFMrYwCgvSPh 62eczkEjSH9hf/CqCmHLBzQ= =bhxF -END PGP SIGNATURE- ---(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] converting a specified year and week into a date
am Wed, dem 14.02.2007, um 3:10:17 -0600 mailte Ron Johnson folgendes: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 02:52, A. Kretschmer wrote: am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes: [snip] test=*# select get_week(2007,2); get_week - 08-01-2007 - 14-01-2007 (1 row) Is that week #2? If weeks start on Sunday (which is what they do in the US), then week #2 would either start on 04-Jan or 11-Jan (depending on whether the 01-Jan partial week is considered week #1 or week #0). Depends, there are different definitions. I have a calendar here and in this the 2. week 2007 starts on monday, 08-01-2007. It's like with http://personal.ecu.edu/mccartyr/isowdcal.html, but i know, in america weeks starts with sunday. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] converting a specified year and week into a date
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 03:33, A. Kretschmer wrote: am Wed, dem 14.02.2007, um 3:10:17 -0600 mailte Ron Johnson folgendes: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 02:52, A. Kretschmer wrote: am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes: [snip] test=*# select get_week(2007,2); get_week - 08-01-2007 - 14-01-2007 (1 row) Is that week #2? If weeks start on Sunday (which is what they do in the US), then week #2 would either start on 04-Jan or 11-Jan (depending on whether the 01-Jan partial week is considered week #1 or week #0). Depends, there are different definitions. I have a calendar here and in this the 2. week 2007 starts on monday, 08-01-2007. Brown paper bag time: I was looking at the February calendar and seeing January... It's like with http://personal.ecu.edu/mccartyr/isowdcal.html, but i know, in america weeks starts with sunday. Interesting web site. The ISO 8601 rule is: The first week of the year is the week containing the first Thursday. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0t4JS9HxQb37XmcRArseAJ44Qrh9Jf+GrZoCxKbytbgC+bvbaACgo0sM Tsqq67zsD6oCWukP6B7hjYk= =kYtL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Having a problem with my stored procedure
Laura McCord wrote: To make a long story short, I am archiving data from an original table to a table I created. This is a third party web application that I am doing this with, so I can't revise the structure/code of this application. With this said, if the original table goes through an insert or update action I want to replicate the information to my archive table. I don't want to delete any articles from my archive table so this is why I am not wanting to do anything based on a delete action. The only problem that I am facing is how to tell the function that I want to perform an update if an update occurred and an insert if an insert action occurred. I want to have different actions occur depending on if the trigger was based on an insert or update. Help, I've been stumped for two days. Thanks in advance. This is what I have so far: CREATE TRIGGER archive_articles AFTER INSERT OR UPDATE ON news_content EXECUTE PROCEDURE su_archive_articles(); As suggested earlier, it is probably cleaner to define separate triggers on insert and on update. That is possible, but they can't have the same names. You probably want to name them accordingly too, or you'll get naming conflicts. I suggest: DROP TRIGGER archive_articles ON news_content; CREATE TRIGGER archive_articles_insert AFTER INSERT ON news_content EXECUTE PROCEDURE su_archive_articles_insert(); CREATE TRIGGER archive_articles_update AFTER UPDATE ON news_content EXECUTE PROCEDURE su_archive_articles_update(); CREATE OR REPLACE FUNCTION su_archive_articles() RETURNS TRIGGER LANGUAGE plpgsql AS ' DECLARE tmp_news_id CHARACTER varying(48); tmp_title CHARACTER varying(100); tmp_abstract CHARACTER varying(300); tmp_news_story TEXT; tmp_topic_id CHARACTER varying(10); tmp_create_date DATE; tmp_author CHARACTER varying(50); tmp_begin_date DATE; tmp_end_date DATE; tmp_priority CHARACTER(1); tmp_image_name CHARACTER varying(512); tmp_image_mime_type CHARACTER varying(50); tmp_layout_type CHARACTER varying(10); It is far easier and just as valid to perform an INSERT or an UPDATE with the values from the predefined NEW record, like this: CREATE OR REPLACE FUNCTION su_archive_articles_insert() RETURNS TRIGGER LANGUAGE plpgsql AS ' BEGIN INSERT INTO su_archives (news_id, title, abstract) VALUES (NEW.news_id, NEW.title, NEW.abstract); END; '; I'm sure you can add the rest of the columns to that statement yourself. The update trigger function is similar to this one, but with an UPDATE statement of course. In insert and update triggers there's always a record called NEW. In delete and update triggers there's a record called OLD (and yes, that means in update triggers you get both). If you really have to go the path you took, may I suggest: DECLARE news_rec su_archives%ROWTYPE; BEGIN SELECT INTO news_rec news_id, title, abstract FROM news_content WHERE last_inserted NEW.news_id; BEGIN SELECT INTO tmp_news_id news_id from news_content where last_inserted(news_id); SELECT INTO tmp_title title from news_content where last_inserted(news_id); SELECT INTO tmp_abstract abstract from news_content where last_inserted(news_id); SELECT INTO tmp_news_story news_story from news_content where last_inserted(news_id); SELECT INTO tmp_topic_id topic_id from news_content where last_inserted(news_id); SELECT INTO tmp_create_date create_date from news_content where last_inserted(news_id); SELECT INTO tmp_author author from news_content where last_inserted(news_id); SELECT INTO tmp_begin_date begin_date from news_content where last_inserted(news_id); SELECT INTO tmp_end_date end_date from news_content where last_inserted(news_id); SELECT INTO tmp_priority priority from news_content where last_inserted(news_id); SELECT INTO tmp_image_name image_name from news_content where last_inserted(news_id); SELECT INTO tmp_image_mime_type image_mime_type from news_content where last_inserted(news_id); SELECT INTO tmp_layout_type layout_type from news_content where last_inserted(news_id); //This is to be done if an INSERT action was done on the table INSERT INTO su_archives(news_id, title, abstract, news_story, topic_id, create_date, author, begin_date, end_date, priority, image_name, image_mime_type, layout_type) VALUES (tmp_news_id,tmp_title,tmp_abstract,tmp_news_story,tmp_topic_id,tmp_create_date,tmp_author,tmp_begin_date,tmp_end_date,tmp_priority,tmp_ image_name ,tmp_image_mime_type,tmp_layout_type); //HOW DO I TELL IT TO DO AN UPDATE ON THE ARCHIVE RECORD IF AN UPDATE WAS DONE RETURN NEW; END '; ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500
Re: [GENERAL] converting a specified year and week into a date
vanessa wrote:
hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?
e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004
thanks
vanessa :)
I think you're looking for this:
select to_date('01 2004', 'WW ');
to_date
2004-01-01
(1 row)
select to_date('02 2004', 'WW ');
to_date
2004-01-08
(1 row)
--
Alban Hertroys
[EMAIL PROTECTED]
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
am Wed, dem 14.02.2007, um 11:53:09 +0100 mailte Alban Hertroys folgendes:
vanessa wrote:
hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?
e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004
thanks
vanessa :)
I think you're looking for this:
select to_date('01 2004', 'WW ');
to_date
2004-01-01
(1 row)
cool ;-)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] converting a specified year and week into a date
Am Mittwoch, 14. Februar 2007 11:53 schrieb Alban Hertroys:
I think you're looking for this:
select to_date('01 2004', 'WW ');
to_date
2004-01-01
(1 row)
Or possibly to_date('01 2004', 'IW IYYY'), depending on taste.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(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] help and Links using postgreSQL with ASP
Pankaj wrote: Need help if there is any script source or links to provide help how to you PostgreSQL with ASP I just use ODBC and ADO - set up a DSN, and then it's as simple as: set conn = Server.CreateObject(ADODB.Connection) conn.Open dsn=your_dsn;uid=your_user;pwd=your_pwd; set rs = conn.Execute select * from your_table while not rs.EOF [do something with the results] wend rs.Close set rs = nothing conn.Close set conn = nothing The ODBC driver is here: http://www.postgresql.org/ftp/odbc. There is also an OLE-DB driver for PostgreSQL at http://pgfoundry.org/projects/oledb/, but I haven't used it. HTH. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Robert Grinnell is out of the office on holiday.
I will be out of the office starting 09/02/2007 and will not return until 19/02/2007. I will respond to your message when I return. If you enquiry is regarding Norgren-i please contact Helen Jayne Walker. Regards, ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] suggestions on improving a query
On Tue, 2007-02-13 at 21:44 -0500, Tom Lane wrote: Rajarshi Guha [EMAIL PROTECTED] writes: However the clause: dock.target = '1YC1' and dock.dockid = dockscore_plp.id reduces the number of rows from 4.6M to 96K. The planner seems to be estimating about ten times that many. Perhaps increasing the statistics target for dock.target would help? My original message had a typo: I expected that it should ~ 960K, so postgres is working as expected. However increasing the statistics target for dock.target did lead to an improvement in performance. Could this be because dock.target has only 5 unique values? So though the table has ~4.6M rows, each set of ~960K rows for dock.dockid is associated with a single value of dock.target. Thanks, --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE --- All great ideas are controversial, or have been at one time. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PGSQL 8.2.3 Installation problem
Paul, I installed on a Fat32 partition and gave the data directory path to an NTFS partition. I have not set any file permissions. Installing as a default postgres user. Paul Lambert-2 wrote: marcelo Cortez wrote: hi there same things occurs to me. Any body install win32 version with success??? best regards MDC --- RPK [EMAIL PROTECTED] escribió: When I run the setup of PGSQL 8.2.3, it displays error while initializing database cluster. Error displayed is: Failed to execute initdb. Unable to set file system permissions. I am installing on Windows XP SP2 with administrator log in. -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I've installed it on my WinXP Professional SP2 (32 bit) machine without error. Action start 6:33:07: SetPermissions. 1: Setting filesystem permissions... Action ended 6:33:07: SetPermissions. Return value 1. MSI (s) (F4:44) [06:33:07:312]: Doing action: RunInitdb Action 6:33:07: RunInitdb. Initializing database cluster (this may take a minute or two)... Action start 6:33:07: RunInitdb. 1: Initializing database cluster (this may take a minute or two)... Action ended 6:33:07: RunInitdb. Return value 1. I wasn't installing under administrator, did this under my own account and had the install create the 'postgres' user account. Perhaps something wrong with the default file permissions where you are installing Postgres. I assume Postgres creates directories that inherit the parent directory permissions. If you have given the parent restricted access, the 'postgres' user that PG runs under may not have access to those dirs. Only thing I can think of anyway, if not then I am not sure why you would be having a problem. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(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 -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8964644 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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] suggestions on improving a query
On Tue, 2007-02-13 at 22:04 -0500, Tom Lane wrote: Adam Rich [EMAIL PROTECTED] writes: This line: Index Scan using plp_total_idx on dockscore_plp (cost=0.00..16733229.92 rows=4669988 width=80) (actual time=98.323..322537.605 rows=25197 loops=1) Means the planner did what it did, because it estimated there would be nearly 5 million rows. However, there were only 25,000. Sorry for not doing the obvious beforehand! I increased the statistics target for some of the columns in some of the tables and then did a vacuum analyze. Rerunning the query gives: QUERY PLAN --- Limit (cost=0.00..397.24 rows=10 width=268) (actual time=98322.597..171721.583 rows=10 loops=1) - Nested Loop (cost=0.00..37182572.57 rows=936023 width=268) (actual time=98322.590..171721.543 rows=10 loops=1) - Nested Loop (cost=0.00..31580822.05 rows=936023 width=90) (actual time=98236.963..171379.151 rows=10 loops=1) - Index Scan using plp_total_idx on dockscore_plp (cost=0.00..16858401.83 rows=4669988 width=80) (actual time=54.989..102775.761 rows=25197 loops=1) - Index Scan using dock_pkey on dock (cost=0.00..3.14 rows=1 width=18) (actual time=2.718..2.718 rows=0 loops=25197) Index Cond: (dock.dockid = outer.id) Filter: (target = '1YC1'::text) - Index Scan using pubchem_compound_pkey on pubchem_compound (cost=0.00..5.97 rows=1 width=187) (actual time=34.221..34.223 rows=1 loops=10) Index Cond: ((outer.cid)::text = (pubchem_compound.cid)::text) Total runtime: 171722.964 ms (10 rows) Clearly a big improvement in performance. (One question not directly related to the problem: when looking at the output of explain analyze, I know that one is supposed to start at the bottom and move up. Does that that the index scan on pubchem_compound is being performed first? Or should I start from the innermost line?) However it seems that it could still be improved: - Index Scan using plp_total_idx on dockscore_plp (cost=0.00..16858401.83 rows=4669988 width=80) (actual time=54.989..102775.761 rows=25197 loops=1) It looks like theres a big mismatch on the expected and observed costs and times. The real problem here is that the planner is guessing that it won't take very long to find 10 rows satisfying the target = '1YC1' condition while scanning in dockscore_plp.total order. So it chooses a plan that would have a long total runtime (notice the large cost estimates below the Limit) expecting that only a small fraction of that total will actually be expended. The expectation seems a bit off unfortunately :-(. I can't tell from the given data whether the problem is just an overestimate of the frequency of target = '1YC1', or if there's an additional effect. I think that increasing the statistics has improved that. For example, if that target value tended to only be associated with larger values of dockscore_plp.total, then a plan like this could lose big-time because it will have to scan a long way to find those rows. This is not the case. The value '1YC1' will be associated with both high and low values of dockscore_plp.total What I would like my query to do is this: 1. From dock.target find all rows = '1YC1' 2. Using dock.dockid of these rows, get the corresponding rows in dockscore_plp 3. Using dock.cid from the rows in 2., get the corresponding rows in pubchem_compound 4. Sort and take the top 10 from step 2 (and associated rows in step 3) However now that I have written this it seems that what I really want to do is: 1. From dock.target find all rows = '1YC1' 2. Using dock.dockid of these rows, get the corresponding rows in dockscore_plp 3. Sort and take the top 10 4. Get the corresponding rows from pubchem_compound.cid The problem with this is that step is represented by the dock.cid = pubchem_compound.cid clause. It seems that if I had the cid column in dockscore_plp, then I could do a sort+limit in dockscore_plp and then simply lookup the corresponding (10) rows in pubchem_compound (rather than looking up 960K rows). The downside to this is that there are 4 more tables like dockscore_plp, and I would have to add a cid column to each of them - which seems redundant. Is it useful to increase redundancy to improve performance? Thanks for the pointers, --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE --- There's no problem so bad that you can't add some guilt to it to make
Re: [GENERAL] PGSQL 8.2.3 Installation problem
The installer is not supported in a fat environment. you need to install the binaries manually for that. /Magnus --- Original message --- From: RPK [EMAIL PROTECTED] Sent: 2-14-'07, 5:14 Paul, I installed on a Fat32 partition and gave the data directory path to an NTFS partition. I have not set any file permissions. Installing as a default postgres user. Paul Lambert-2 wrote: marcelo Cortez wrote: hi there same things occurs to me. Any body install win32 version with success??? best regards MDC --- RPK [EMAIL PROTECTED] escribió: When I run the setup of PGSQL 8.2.3, it displays error while initializing database cluster. Error displayed is: Failed to execute initdb. Unable to set file system permissions. I am installing on Windows XP SP2 with administrator log in. -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I've installed it on my WinXP Professional SP2 (32 bit) machine without error. Action start 6:33:07: SetPermissions. 1: Setting filesystem permissions... Action ended 6:33:07: SetPermissions. Return value 1. MSI (s) (F4:44) [06:33:07:312]: Doing action: RunInitdb Action 6:33:07: RunInitdb. Initializing database cluster (this may take a minute or two)... Action start 6:33:07: RunInitdb. 1: Initializing database cluster (this may take a minute or two)... Action ended 6:33:07: RunInitdb. Return value 1. I wasn't installing under administrator, did this under my own account and had the install create the 'postgres' user account. Perhaps something wrong with the default file permissions where you are installing Postgres. I assume Postgres creates directories that inherit the parent directory permissions. If you have given the parent restricted access, the 'postgres' user that PG runs under may not have access to those dirs. Only thing I can think of anyway, if not then I am not sure why you would be having a problem. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(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 -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8964644 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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 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] suggestions on improving a query
On Wed, Feb 14, 2007 at 08:22:42AM -0500, Rajarshi Guha wrote: (One question not directly related to the problem: when looking at the output of explain analyze, I know that one is supposed to start at the bottom and move up. Does that that the index scan on pubchem_compound is being performed first? Or should I start from the innermost line?) There's no concept of nodes being executed before others. Each node is executed as needed. If the case of a nested loop like you have, it reads one tuple from the outer node (the first child) and then as many tuples from the inner node as necessary (an index scan may not return very many). In your case the outer node is another nested loop which will in turn scan its inner and outer nodes as necessary. The Limit up the top means that no more than that many tuples will be requested from child node, so child nodes may be executed once, many times or not at all. There are some more comprehensive writeups around, but hopefully this gives you an idea. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Cast record as text
Hi,
I'm trying to build an audit system for several tables. My idea was to use
triggers and plpgsql to record changes made to important tables to a
special audit table. My problem is that I don't want to create a separate
audit log table for each table that is being monitored. What I would like
to do is just cast the data from NEW.* or OLD.* to text and insert it into
a text column. Is this possible? I'm using version 8.1.5.
Example:
CREATE TABLE t1 (foo text, bar text);
CREATE TABLE t2 (id int, col timestamp);
CREATE TABLE audit (id int, optype char, time timestamp, user text, target
text, oldvalues text, newvalues text);
CREATE FUNCTION audit() RETURNS TRIGGER AS $$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO audit(optype,time,user,target,newvalues) VALUES ('I ',
now(), current_user, TG_RELNAME, NEW.*::text);
RETURN NEW;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
CREATE TRIGGER t1_audit AFTER INSERT OR UPDATE OR DELETE ON t1 FOR EACH
ROW EXECUTE PROCEDURE audit();
CREATE TRIGGER t2_audit AFTER INSERT OR UPDATE OR DELETE ON t2 FOR EACH
ROW EXECUTE PROCEDURE audit();
I guess the explanation is a bit vague, but I hope you got my point!
Regards
MP
---(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] Cast record as text
am Wed, dem 14.02.2007, um 16:38:27 +0200 mailte [EMAIL PROTECTED] folgendes: Hi, I'm trying to build an audit system for several tables. My idea was to use triggers and plpgsql to record changes made to important tables to a special audit table. My problem is that I don't want to create a separate audit log table for each table that is being monitored. What I would like to do is just cast the data from NEW.* or OLD.* to text and insert it into a text column. Is this possible? I'm using version 8.1.5. Why do you want to reinvent the wheel? http://pgfoundry.org/projects/tablelog/ But it use a separate log-table per table. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Having a problem with my stored procedure
As suggested earlier, it is probably cleaner to define separate triggers on insert and on update. That is possible, but they can't have the same names. You probably want to name them accordingly too, or you'll get naming conflicts. I suggest: DROP TRIGGER archive_articles ON news_content; While this is something I'd do if I had absolute control over all the software, in the OP's case, there is a third party application involved that can't be modified. I would therefore worry that the developer of that software may have also placed triggers of that name on that table. This would suggest a little slop in the practices of that developer (i.e. of the third party application, not the OP) since a decent naming convention would make a name collision between that developer's code and the OP's code highly unlikely, but that is another issue. If the OP is getting name collision when trying to create these triggers, the implication is that the developer of the third party app in fact defined triggers of the same names, so dropping previously created triggers may well break that app. It seems to me that dropping something I haven't created is a high risk action. A naming convention similar to what I use would solve that problem without the risk associated with dropping something someone else has developed. If I am working on something to be distributed, I use a naming scheme that prepends a very short string that makes it clear the code was developed by myself or one of my staff, and in languages that support a namespace, such as C++, I make certain there is a namespace ID unique to my organization. This eliminates the risk of a name collision unless some developer actually tries to impersonate me. In some cases, where I am working as part of a team, my preference is to do the same with the developer's ID (since always the developer who developed a given peice of code is responsible for fixing any bugs in it whever possible). Of course, always the fact is fully documented, both in the code and in design documents provided to the client. This is a discipline I impose on myself, as a courtesy to those who come after me, and it involves considerations any library developer necessarily worries about. It is not something I want to impose on those who come after me, but which I would require of those who develop libraries or databases or tools I need to use in order to be productive. Damn. It just occured to me that the OP had to be able to see the structure of the DB using a tool like pgAdmin, in order to just get the names of the tables and columns. Therefore, the OP should have also been able to see the definitions of any existing triggers and trigger functions. Further, the OP should be able to create more functions that could be called at the end of any existing trigger functions, thereby obtaining the functionality desired without compromizing the third party app. You can edit these functions from within pgAdmin. I have done so myself on trigger functions I created myself. This would make the OP's task almost trivially simple. Don't you just hate when you see the obvious solution only after spending time on other options? ;-) Cheers, Ted ---(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] Advisory on possibly insecure security definer functions
On 2/13/07, Peter Eisentraut [EMAIL PROTECTED] wrote: The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. Note that using the default search path, which includes a reference to the $user schema, is not safe when unqualified references are intended to be found in the public schema and $user schemas exist or can be created by other users. It is also not recommended to rely on rigorously schema-qualifying all function and operator invocations in function source texts, as such measures are likely to induce mistakes and will furthermore make the source code harder to read and maintain. Could you clarify what functions are going to get an explicit 'set search_path'? Will this change the behavior of any userland functions? merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Having a problem with my stored procedure
I did check with customer support (they are actual developers of the system not middle men) and they said there aren't any triggers in the db structure so it's safe for me to include triggers. So, that helps me feel reassured because you had a good point. So more than likely, it was my error and I must have not dropped my trigger properly before recreating it. Thanks everyone for your advice. I am going to try this afternoon to take your suggestions and make this work. I never had a problem creating mysql stored procedures and I just learned that with postgres it is so much different. Thanks Again, Laura Ted wrote: As suggested earlier, it is probably cleaner to define separate triggers on insert and on update. That is possible, but they can't have the same names. You probably want to name them accordingly too, or you'll get naming conflicts. I suggest: DROP TRIGGER archive_articles ON news_content; While this is something I'd do if I had absolute control over all the software, in the OP's case, there is a third party application involved that can't be modified. I would therefore worry that the developer of that software may have also placed triggers of that name on that table. This would suggest a little slop in the practices of that developer (i.e. of the third party application, not the OP) since a decent naming convention would make a name collision between that developer's code and the OP's code highly unlikely, but that is another issue. If the OP is getting name collision when trying to create these triggers, the implication is that the developer of the third party app in fact defined triggers of the same names, so dropping previously created triggers may well break that app. It seems to me that dropping something I haven't created is a high risk action. A naming convention similar to what I use would solve that problem without the risk associated with dropping something someone else has developed. If I am working on something to be distributed, I use a naming scheme that prepends a very short string that makes it clear the code was developed by myself or one of my staff, and in languages that support a namespace, such as C++, I make certain there is a namespace ID unique to my organization. This eliminates the risk of a name collision unless some developer actually tries to impersonate me. In some cases, where I am working as part of a team, my preference is to do the same with the developer's ID (since always the developer who developed a given peice of code is responsible for fixing any bugs in it whever possible). Of course, always the fact is fully documented, both in the code and in design documents provided to the client. This is a discipline I impose on myself, as a courtesy to those who come after me, and it involves considerations any library developer necessarily worries about. It is not something I want to impose on those who come after me, but which I would require of those who develop libraries or databases or tools I need to use in order to be productive. Damn. It just occured to me that the OP had to be able to see the structure of the DB using a tool like pgAdmin, in order to just get the names of the tables and columns. Therefore, the OP should have also been able to see the definitions of any existing triggers and trigger functions. Further, the OP should be able to create more functions that could be called at the end of any existing trigger functions, thereby obtaining the functionality desired without compromizing the third party app. You can edit these functions from within pgAdmin. I have done so myself on trigger functions I created myself. This would make the OP's task almost trivially simple. Don't you just hate when you see the obvious solution only after spending time on other options? ;-) Cheers, Ted ---(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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] suggestions on improving a query
Rajarshi Guha [EMAIL PROTECTED] writes: Clearly a big improvement in performance. Huh? It looks like exactly the same plan as before. Any improvement you're seeing must be coming from cache effects. It looks like theres a big mismatch on the expected and observed costs and times. Well, in the first place the estimated costs are not measured in milliseconds, and in the second place the estimated cost and rowcount are for execution of the plan node to completion, which is not happening here because of the Limit --- we'll stop the plan as soon as the top join node has produced 10 rows. In fact I'd say the whole problem here is that the planner is being too optimistic about the benefits of a fast-start plan. For whatever reason (most likely, an unfavorable correlation between dock.target and dockscore_plp.total), the desired rows aren't uniformly scattered in the output of the join, and so it's taking longer than expected to find 10 of them. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] suggestions on improving a query
Martijn van Oosterhout kleptog@svana.org writes: There are some more comprehensive writeups around, but hopefully this gives you an idea. You can find the official(tm) explanation at http://www.postgresql.org/docs/8.2/static/executor.html --- in fact, you might want to read all of chapter 42. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Advisory on possibly insecure security definer functions
Am Mittwoch, 14. Februar 2007 16:31 schrieb Merlin Moncure: Could you clarify what functions are going to get an explicit 'set search_path'? Will this change the behavior of any userland functions? Nothing is going to get anything. You have to fix all affected functions yourself. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PGSQL 8.2.3 Installation problem
Magnus I have NTFS only , i don't have FAT partitions at all. But the problem is not resolved. The install fail at create cluster for me. I set all permisions for user postgres. Binary manual installation .. make sense i try . It has any manual/instructions/links for that? Best regards MDC --- Magnus Hagander [EMAIL PROTECTED] escribió: The installer is not supported in a fat environment. you need to install the binaries manually for that. /Magnus --- Original message --- From: RPK [EMAIL PROTECTED] Sent: 2-14-'07, 5:14 Paul, I installed on a Fat32 partition and gave the data directory path to an NTFS partition. I have not set any file permissions. Installing as a default postgres user. Paul Lambert-2 wrote: marcelo Cortez wrote: hi there same things occurs to me. Any body install win32 version with success??? best regards MDC --- RPK [EMAIL PROTECTED] escribió: When I run the setup of PGSQL 8.2.3, it displays error while initializing database cluster. Error displayed is: Failed to execute initdb. Unable to set file system permissions. I am installing on Windows XP SP2 with administrator log in. -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8947083 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I've installed it on my WinXP Professional SP2 (32 bit) machine without error. Action start 6:33:07: SetPermissions. 1: Setting filesystem permissions... Action ended 6:33:07: SetPermissions. Return value 1. MSI (s) (F4:44) [06:33:07:312]: Doing action: RunInitdb Action 6:33:07: RunInitdb. Initializing database cluster (this may take a minute or two)... Action start 6:33:07: RunInitdb. 1: Initializing database cluster (this may take a minute or two)... Action ended 6:33:07: RunInitdb. Return value 1. I wasn't installing under administrator, did this under my own account and had the install create the 'postgres' user account. Perhaps something wrong with the default file permissions where you are installing Postgres. I assume Postgres creates directories that inherit the parent directory permissions. If you have given the parent restricted access, the 'postgres' user that PG runs under may not have access to those dirs. Only thing I can think of anyway, if not then I am not sure why you would be having a problem. Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(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 -- View this message in context: http://www.nabble.com/PGSQL-8.2.3-Installation-problem-tf3221486.html#a8964644 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(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 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 __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] suggestions on improving a query
On Wed, 2007-02-14 at 10:55 -0500, Tom Lane wrote: Rajarshi Guha [EMAIL PROTECTED] writes: Clearly a big improvement in performance. Huh? It looks like exactly the same plan as before. Any improvement you're seeing must be coming from cache effects. Well the new run was done nearly 8 hours after the initial one - I would've thought that the cache had been purged (?) It looks like theres a big mismatch on the expected and observed costs and times. In fact I'd say the whole problem here is that the planner is being too optimistic about the benefits of a fast-start plan. For whatever reason (most likely, an unfavorable correlation between dock.target and dockscore_plp.total), the desired rows aren't uniformly scattered in the output of the join, and so it's taking longer than expected to find 10 of them. Is there any way to solve this? I've increased the statistics target on dockscore_plp.total to 100 - does going higher help? From what you've said, it appears that the problem is arising due to lack of correlation between two columns in two tables. This is strange since, out of 4.6M rows in dock, ~ 960K will be selected and the corresponding 960K rows from dockscore_plp will be ordered and then the top 10 will be taken. So does the lack of correlation occur due to 'ordering' in the DB itself? And if this is the case, how does one fix the lack of correlation (if at all possible)? --- Rajarshi Guha [EMAIL PROTECTED] GPG Fingerprint: 0CCA 8EE2 2EEB 25E2 AB04 06F7 1BB9 E634 9B87 56EE --- Regular naps prevent old age especially if you take them while driving ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] backup database by cloning itself
On Tue, 2007-02-13 at 07:54, filippo wrote: Hello, my database is not very big so I want to adopt this backup strategy: I want to clone my database every 1 hour to another database 'currenttime_mydatabase' in order to have 24 backup a day, overwriting the yesterday backups by today-same-time backups. This is good for me because I have all the backups readily available to be read by my program (opening the backup read only). This is a very important for my needs. I'm writing a script run by cron each hour to do accomplish the backup task. My target is to have the backup operation not affecting the users, so I want to be able to copy a database even if the database is used by someone. Can I use CREATE DATABASE my_backup_database TEMPLATE current_database? Is there a better way to get what I need? Create database ain't gonna work, cause it needs a database with no users connected. You could do: dropdb hour_13; createdb hour_13 pg_dump masterdb | psql hour_13 with the number after hour being a var you set every hour when you run it. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] backup database by cloning itself
Scott Marlowe [EMAIL PROTECTED] writes: On Tue, 2007-02-13 at 07:54, filippo wrote: my database is not very big so I want to adopt this backup strategy: I want to clone my database every 1 hour to another database 'currenttime_mydatabase' in order to have 24 backup a day, overwriting the yesterday backups by today-same-time backups. Can I use CREATE DATABASE my_backup_database TEMPLATE current_database? Create database ain't gonna work, cause it needs a database with no users connected. There's a more serious objection, which is that storing a duplicate database under the same postmaster doesn't give you an independent copy. If something bad happens to pg_clog or pg_global, *all* your backups may be rendered useless. Now if your purpose in making the backups is only to protect against user errors, and not any sort of hardware failure or Postgres bug, maybe this isn't an issue. But it's not what I'd call a backup. regards, tom lane ---(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] Protect PL/PGSQL source
Hello, Does anyone knows if it´s possible to protect from users see a PL/PGSQL source ? I have a PL/PGSQL function and I want to make it invisible to users. Just a example, in Oracle there is a option like this: http://www.dbasupport.com/oracle/ora9i/wrap_utility.shtml Regards, Wilton Ruffato Wonrath [EMAIL PROTECTED] São Paulo - Brazil PostgreSQL 8.2 __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
Re: [GENERAL] backup database by cloning itself
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 12:41, Tom Lane wrote: Scott Marlowe [EMAIL PROTECTED] writes: On Tue, 2007-02-13 at 07:54, filippo wrote: my database is not very big so I want to adopt this backup strategy: I want to clone my database every 1 hour to another database 'currenttime_mydatabase' in order to have 24 backup a day, overwriting the yesterday backups by today-same-time backups. Can I use CREATE DATABASE my_backup_database TEMPLATE current_database? Create database ain't gonna work, cause it needs a database with no users connected. There's a more serious objection, which is that storing a duplicate database under the same postmaster doesn't give you an independent copy. If something bad happens to pg_clog or pg_global, *all* your backups may be rendered useless. Now if your purpose in making the backups is only to protect against user errors, and not any sort of hardware failure or Postgres bug, maybe this isn't an issue. But it's not what I'd call a backup. Maybe his real goal all the backups readily available to be read by my program (opening the backup read only) is to have a historical record of what certain records looked like in the past. There are other ways of doing that, though. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF01oIS9HxQb37XmcRAqYQAKDoSNb76asUadv9InNXroshleKZEQCgl6w6 SwWu3841RN4B+GBBkxoa/DQ= =bdEY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Best practice for 8.1.5 - 8.2 migration - with big database?
From what I've read about postgres 8.2, upgrading from any previous version requires a full database dump and restore. I am working with largish database (~70GB) that I would like to upgrade to 8.2. A full database dump currently takes ~3 hrs, I expect a restore require a similar timeframe if not more. So, I'm fishing to see of there are alternatives to taking our production database down for 6+ hours do this upgrade. Can slony or wal files be of help here? I realize I can do a pg_dump with the database online, but that dump will not include data added after the data dump for each table starts, correct? Is there way to get just the data after a certain point in time (say after the pg_dump started)? Also -- due to hardware limitations, we need to install 8.2.x on the same hardware 8.1.5 is currently running on. Can those two versions exist side-by-side? If something goes wrong, I want to be able to rollback to 8.1.5. thanks in advance, Mason ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] backup database by cloning itself
Maybe his real goal all the backups readily available to be read by my program (opening the backup read only) is to have a historical record of what certain records looked like in the past. There are other ways of doing that, though. If your speculation is right, perhaps the OP ought to explain a little more fully why he needs 24 snapshots a day, or indeed any at all. It seems to me that if you really want a historical record of what certain tables looked like in the past, it would be smarter and more accurate to create triggers, for each possible operation, that store the relevant details in an audit table including especially who made the edits and when. This strikes me as being much less work than developing code that processes so many backups. Cheers Ted ---(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] Best practice for 8.1.5 - 8.2 migration - with big database?
Mason Hale wrote: From what I've read about postgres 8.2, upgrading from any previous version requires a full database dump and restore. I am working with largish database (~70GB) that I would like to upgrade to 8.2. A full database dump currently takes ~3 hrs, I expect a restore require a similar timeframe if not more. So, I'm fishing to see of there are alternatives to taking our production database down for 6+ hours do this upgrade. Check out a href=http://pgfoundry.org/projects/pg-migrator/;pg_migrator/a. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Protect PL/PGSQL source
No, we have no such option. --- Wilton Wonrath wrote: Hello, Does anyone knows if it?s possible to protect from users see a PL/PGSQL source ? I have a PL/PGSQL function and I want to make it invisible to users. Just a example, in Oracle there is a option like this: http://www.dbasupport.com/oracle/ora9i/wrap_utility.shtml Regards, Wilton Ruffato Wonrath [EMAIL PROTECTED] S?o Paulo - Brazil PostgreSQL 8.2 __ Fale com seus amigos de gra?a com o novo Yahoo! Messenger http://br.messenger.yahoo.com/ -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Best practice for 8.1.5 - 8.2 migration - with big database?
Mason Hale wrote: So, I'm fishing to see of there are alternatives to taking our production database down for 6+ hours do this upgrade. Can slony or wal files be of help here? Slony can, but WAL files will not work between different versions. Also -- due to hardware limitations, we need to install 8.2.x on the same hardware 8.1.5 is currently running on. Can those two versions exist side-by-side? Sure. Just set a different port and different data directory. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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] backup database by cloning itself
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 13:40, Ted Byers wrote: Maybe his real goal all the backups readily available to be read by my program (opening the backup read only) is to have a historical record of what certain records looked like in the past. There are other ways of doing that, though. If your speculation is right, perhaps the OP ought to explain a little more fully why he needs 24 snapshots a day, or indeed any at all. It seems to me that if you really want a historical record of what certain tables looked like in the past, it would be smarter and more accurate to create triggers, for each possible operation, that store the relevant details in an audit table including especially who made the edits and when. This strikes me as being much less work than developing code that processes so many backups. I dunno about that. We use triggers to populate log tables that get extracted, truncated then loaded into a reporting db every night. Because of the night time batch cycle, there is no quiescent time to do this, so we have 2 log tables, and the triggers alternate which log table to insert into, depending on whether the day number is even or odd. That's in addition to the trigger logic to insert into history tables. It's a royal pain. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF03URS9HxQb37XmcRAq0RAJ4iAHtbst+Gq9QndTr36lErYUwSmgCg7dM/ luIRI+F9eqYqUoMz9VNNaNc= =NYOz -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Recover anything from dropped database?
With a slip of the keyboard, I just dropped a database I'd like to have back. I don't have PITR or anything turned on - if nothing else has been done to the cluster since then, is there any way to recover anything at all? Thanks. - John Burger MITRE ---(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] backup database by cloning itself
Maybe his real goal all the backups readily available to be read by my program (opening the backup read only) is to have a historical record of what certain records looked like in the past. What postgresql time travel? I have never used it, and it looks a little bit unmaintained, but it might be perfect with some tweaking: contrib/spi/README.timetravel I know that someone at SFPUG presented on using time travel last year, and she may have more info . ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to search, how to post?
Hi, I just now subscribed the mailing list, but I can't understand what I should do next. I need: 1) search forums for specific keywords 2) be able to post my question. How can I do that? I read help but it didn't have any information for helping me. Thank you, Vladimir ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to search, how to post?
Vladimir Zelinski wrote: 1) search forums for specific keywords You can do this at http://archives.postgresql.org/. 2) be able to post my question. You've just done it! :) Send an email to pgsql-general@postgresql.org and it is distributed to everyone subscribed to the list. Ray. --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recover anything from dropped database?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 14:59, John D. Burger wrote: With a slip of the keyboard, I just dropped a database I'd like to have back. I don't have PITR or anything turned on - if nothing else has And no backups? been done to the cluster since then, is there any way to recover anything at all? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF03qdS9HxQb37XmcRAjt3AKCsk20FV9UJCCz/65MTcK8pB8fyDACgk/qi gyfeMr0Ba5yff+Sx08kRoVk= =uGmg -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to search, how to post?
Vladimir Zelinski wrote: Hi, I just now subscribed the mailing list, but I can't understand what I should do next. I need: 1) search forums for specific keywords http://archives.postgresql.org/ 2) be able to post my question. You just did... Sending mail to pgsql-list name@postgresql.org is all you need to do. Where list name is substituted with the list you want to post to (i.e. in the case of this one, pgsql-general@postgresql.org) How can I do that? I read help but it didn't have any information for helping me. Thank you, Vladimir ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Paul. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] backup database by cloning itself
- Original Message - From: Ron Johnson [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Wednesday, February 14, 2007 3:46 PM Subject: Re: [GENERAL] backup database by cloning itself It seems to me that if you really want a historical record of what certain tables looked like in the past, it would be smarter and more accurate to create triggers, for each possible operation, that store the relevant details in an audit table including especially who made the edits and when. This strikes me as being much less work than developing code that processes so many backups. I dunno about that. We use triggers to populate log tables that get extracted, truncated then loaded into a reporting db every night. Because of the night time batch cycle, there is no quiescent time to do this, so we have 2 log tables, and the triggers alternate which log table to insert into, depending on whether the day number is even or odd. That's in addition to the trigger logic to insert into history tables. It's a royal pain. Sounds painful, but not as painful as trying to do the same thing with a suite of backups and client code to read the backups. Or maybe the latter task is easier than it looks at first glance. Doesn't the amount of pain depend on the amount of traffic your servers see, the capability of the hardware you can devote to the task, what you're doing with the history tables, c.? When I've worked on this kind of problem, everything was in a single DB. The traffic, though, was low enough that for daily reports a simple view of the log selecting only the previous day's data, was sufficient, but only occasionally used. The principal reason for the log was to support accountability, to know who made changes, and when, and who knew what when. Historical reconstructions were required, e.g., only when something went awry and there was a need to know if a good decision was made based on bad data or a bad decision was made based on good data, or, e.g., during an audit of the business processes and decision support systems. Cheers, Ted ---(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] backup database by cloning itself
Here is the link to Elein's presentation: http://www.varlena.com/GeneralBits/Tidbits/tt.pdf What [about] postgresql time travel? I have never used it, and it looks a little bit unmaintained, but it might be perfect with some tweaking: ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] PGSQL 8.2.3 Installation problem
marcelo Cortez wrote: Magnus I have NTFS only , i don't have FAT partitions at all. But the problem is not resolved. The install fail at create cluster for me. I set all permisions for user postgres. Binary manual installation .. make sense i try . It has any manual/instructions/links for that? Best regards MDC Have you run the setup with the 'write detailed installation log to postgresql-8.2.log in the current directory' checked? Can you paste the relevant bits of that log (which you can find in the same directory as the installation file) into a message so more educated persons can take a look? If it's a file system problem, I'd also suggest going to http://www.sysinternals.com to their file and disk utilities, download ntfilemon and run it to monitor file activity (HINT: Set the filter to include only *postgres*, otherwise you will be flooded with information relating to file system access from everything else on your server - and turn on advanced output) that may show you more information about what the error was from the file system point of view. -- Paul Lambert Database Administrator AutoLedgers ---(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] Stored Procedure examples
Hello everyone, I'm a brand new person to postgreSql, but not for databases. I came from Oracle and Sybase areas. I'm struggling to create a stored procedure. I searched on Internet for several hours trying to find a simple example, but didn't find anything. I saw dozens of questions how to create a procedure without any responses. I searched on postgreSql site and found a topic Stored Procedure Example. But actually, they showed how to write a function on postgreSql database. I understand that sometimes it's possible to use a function instead of a SP and vice versa, but it's still different database objects. Can somebody show me an example of SP for postgreSql? It doesn't have to be complex, but it should be complete. I want to be able cut paste into editor and compile it without errors. I would like to emphasize that I don't need an example for a function; I have tons of them but I don't have ANY stored procedure example. It would be great if you point me to a site with PostgreSql examples for different Stored Procedures if they actually exist as database objects on postgreSql database. Thank you, Vladimir ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cast record as text
Why do you want to reinvent the wheel? http://pgfoundry.org/projects/tablelog/ But it use a separate log-table per table. Andreas My original idea was to log changes from different tables to one audit table, and I think tablelog uses separate audit tables for each monitored table? Regards MP ---(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] [ANNOUNCE] Advisory on possibly insecure security definer functions
Peter Eisentraut wrote: The proper fix for this problem is to insert explicit SET search_path commands into each affected function to produce a known safe schema search path. Note that using the default search path, which includes a reference to the $user schema, is not safe when unqualified references are intended to be found in the public schema and $user schemas exist or can be created by other users. It is also not recommended to rely on rigorously schema-qualifying all function and operator invocations in function source texts, as such measures are likely to induce mistakes and will furthermore make the source code harder to read and maintain. I do enjoy code generators. This was a one-line fix for me. begin:vcard fn:Kenneth Downs n:Downs;Kenneth adr;dom:;;347 Main Street;East Setauket;NY;11733 email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE url:http://www.secdat.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Recover anything from dropped database?
John D. Burger wrote: With a slip of the keyboard, I just dropped a database I'd like to have back. I don't have PITR or anything turned on - if nothing else has And no backups? been done to the cluster since then, is there any way to recover anything at all? I have a backup as of last night, but I'd like to recover something more recent if I can. If I'm stuck with the backup, I can just stop the postmaster, drop the whole PG directory into place from the backup, and restart, yes? Any advice welcome. By the way, this is PG 7.4. Thanks. - John Burger ---(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] Stored Procedure examples
Vladimir Zelinski wrote: I'm struggling to create a stored procedure. I searched on Internet for several hours trying to find a simple example, but didn't find anything. I saw dozens of questions how to create a procedure without any responses. I searched on postgreSql site and found a topic Stored Procedure Example. But actually, they showed how to write a function on postgreSql database. This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. Did you try using pgAdminIII to create your procedure and see if it works any different a creating an identical function? I am sure others will chime in with reasons why you haven't found any stored procedure examples. My sad guess is that in postgresSQL they may be aliases. -- Walter ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Problem with INNER JOIN
Hello List-
I'm attempting to do an INNER JOIN on one of the views in my database,
however, when I import the schema file that has it in there, it removes the
INNER from the statement. I've also tried to add it by modifying it via
the 'Properites' - 'Definition' tab on the VIEW. But, when I put INNER in
front of JOIN, it doesn't save it: (INNER JOIN elementdata.area a USING
(elementid))
-- View: elementdata.floor_info
-- DROP VIEW elementdata.floor_info;
CREATE OR REPLACE VIEW elementdata.floor_info AS
SELECT d.elementid, d.projectname, d.floor_id, d.floor_name, d.floor_type,
d.percent_area_coverage, a.area * (d.percent_area_coverage /
100::numeric)::double precision AS area_coverage_sf, d.floor_install_date,
d.est_life_yrs, d.dimension_notes, d.floor_underlayment, d.floor_subfloor,
d.floor_warranty, d.insurance_info, d.floor_contractor, d.material_quote,
future_value((a.area * m.quote::double precision)::numeric,
m.material_inflation_rate / 100::numeric, ('now'::text::date -
m.quote_date)::numeric / 365.25) AS current_replacement_cost,
d.floor_install_date + (d.est_life_yrs * 365.25)::integer AS
est_replacement_date, future_value((a.area * m.quote::double
precision)::numeric, m.material_inflation_rate / 100::numeric,
(d.floor_install_date + (d.est_life_yrs * 365.25)::integer -
m.quote_date)::numeric / 365.25) AS est_replacement_cost, d.comments
FROM elementdata.data_floor_info d
JOIN elementdata.area a USING (elementid)
LEFT JOIN projectdata.material_quote m ON d.material_quote::text =
m.quote_id::text AND d.projectname::text = m.projectname::text;
ALTER TABLE elementdata.floor_info OWNER TO postgres;
But, if I put LEFT or RIGHT in front of the JOIN statement, it saves it
without any problems...
-- View: elementdata.floor_info
-- DROP VIEW elementdata.floor_info;
CREATE OR REPLACE VIEW elementdata.floor_info AS
SELECT d.elementid, d.projectname, d.floor_id, d.floor_name, d.floor_type,
d.percent_area_coverage, a.area * (d.percent_area_coverage /
100::numeric)::double precision AS area_coverage_sf, d.floor_install_date,
d.est_life_yrs, d.dimension_notes, d.floor_underlayment, d.floor_subfloor,
d.floor_warranty, d.insurance_info, d.floor_contractor, d.material_quote,
future_value((a.area * m.quote::double precision)::numeric,
m.material_inflation_rate / 100::numeric, ('now'::text::date -
m.quote_date)::numeric / 365.25) AS current_replacement_cost,
d.floor_install_date + (d.est_life_yrs * 365.25)::integer AS
est_replacement_date, future_value((a.area * m.quote::double
precision)::numeric, m.material_inflation_rate / 100::numeric,
(d.floor_install_date + (d.est_life_yrs * 365.25)::integer -
m.quote_date)::numeric / 365.25) AS est_replacement_cost, d.comments
FROM elementdata.data_floor_info d
LEFT JOIN elementdata.area a USING (elementid)
LEFT JOIN projectdata.material_quote m ON d.material_quote::text =
m.quote_id::text AND d.projectname::text = m.projectname::text;
ALTER TABLE elementdata.floor_info OWNER TO postgres;
By doing a LEFT JOIN, it is taking too long to return the result, so I only
want to do an INNER JOIN on this column...
Any thoughts/comments??
Thanks for your time and assistance!
-Jeanna
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [pgsql-advocacy] [GENERAL] PostgreSQL 9.0
Bruce, Having contributors bought out was always one of our three risks, the other two being patent and trademark attacks. Not sure what we can really do about them. Actually, the potential for trademark attacks is minimal to nonexistant according to the attorney's report. So I'm not worrying about it. Patent attacks are no more a risk for us than they are for every other OSS project, and the answer for these is to support the anti-patent organizations. Overall, I think we're in a good position in that there are a lot of attacks which could *hurt* PostgreSQL, but none which are a guarenteed kill, and the public knowledge of an attack could easily cause our users and enemies of the attacker, and the OSS legal community, to rally to our defense and support. This makes any attack a risky proposition for the attacker. Our #1 best defense is to make sure that as many companies as possible have invested in making PostgreSQL an integral part of their infrastructure and/or product line. -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Problem with INNER JOIN
Jeanna Geier [EMAIL PROTECTED] writes: I'm attempting to do an INNER JOIN on one of the views in my database, however, when I import the schema file that has it in there, it removes the INNER from the statement. INNER is a noise word; if it's re-listing the statement without that, there's not anything to worry about. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
On Wed, Feb 14, 2007 at 05:18:10PM -0500, Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. Way back when I learned that procedures are merely functions that don't return a value. So in that sense procedures are indeed just functions. You obviously mean something else but I'm not sure what. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org 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] Union Query Improvement
On Tue, 2007-02-13 at 05:46 -0800, Ray Bannon wrote:
Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
...
Ad infinitum for about 100 iterations.
Any way to write this more efficiently?
I assume that table/view in your OP really refers to different tables
or views; otherwise, this is a trivial query that can be collapsed to
select ... where plan_name in ('A','B','C') ... or, perhaps just
select ... where rownum=1.
As Tom said, UNION ALL may be more appropriate for your needs. See
http://www.postgresql.org/docs/8.1/interactive/queries-union.html
You should consider inherited tables and table partitioning. See
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html
Partitioning offers two administrative benefits: consistency of child
tables and an implied UNION ALL of children when one selects on the
parent.
-Reece
--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.
Re: [GENERAL] Stored Procedure examples
Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. A procedure is a function that returns null. You'll note if you create a procedure under pgAdminIII, it gets saved under functions, not under procedures. I spent a couple of minutes a few days back trying to find all the procedures I'd just created only to note that they were in fact sitting under functions. I couldn't see them under procedures, but when I tried to create them again I was told they already existed... it was hair pulling stuff there for a couple of minutes. -- Paul Lambert Database Administrator AutoLedgers ---(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] Installing on weendoze vista.
Has anyone had any success installing on weendoze vista? Any install I try gets as far as the service user details, if I ask it to create a user it fails, if I specify an existing user account it complains about the user not having enough access - even when said user account is put into the administrator group. I'm assuming PG hasn't been certified under vista yet? If this is correct, is there any plan to do so? Has anyone tried it under server longhorn? -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Stored Procedure examples
Paul Lambert wrote: Walter Vaughan wrote: This may not help, but I noticed using pgAdminIII, you can create a procedure or a function, but they seem to have the same creation interface and use the same icon. A procedure is a function that returns null. That should have said void of course. You'll note if you create a procedure under pgAdminIII, it gets saved under functions, not under procedures. I spent a couple of minutes a few days back trying to find all the procedures I'd just created only to note that they were in fact sitting under functions. I couldn't see them under procedures, but when I tried to create them again I was told they already existed... it was hair pulling stuff there for a couple of minutes. -- Paul Lambert Database Administrator AutoLedgers ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] How to create an archive for old records?
Hi all, I am looking for ways to create an archive of records older than 3 months in one of my table, and store these extracted records into a local database. Does Postgres have any command to do this? Thanks. _ Find just what you are after with the more precise, more powerful new Windows Live Search. http://search.msn.com.sg/ Try it now. ---(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
[GENERAL] pg_dump and buffer usage
Question about pg_dump and Postgres 8.1. Assuming you've let you buffers settle, and then you dump your database. Will this clobber your shared buffers like a seq scan against a large table will? -- Brad Nicholson 416-673-4106[EMAIL PROTECTED] Database Administrator, Afilias Canada Corp. ---(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] How to create an archive for old records?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 19:11, carter ck wrote: Hi all, I am looking for ways to create an archive of records older than 3 months in one of my table, and store these extracted records into a local database. Does Postgres have any command to do this? A single command that will copy data to a destination database, and then delete from the source database? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF07V5S9HxQb37XmcRAmcmAKCBRmyMFbhnfC04VUwI29pUDEVpzgCdGeDi ZqGkW48PU/99qt9bs0waftA= =V7r+ -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] How to create an archive for old records?
Ron Johnson wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 19:11, carter ck wrote: Hi all, I am looking for ways to create an archive of records older than 3 months in one of my table, and store these extracted records into a local database. Does Postgres have any command to do this? A single command that will copy data to a destination database, and then delete from the source database? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF07V5S9HxQb37XmcRAmcmAKCBRmyMFbhnfC04VUwI29pUDEVpzgCdGeDi ZqGkW48PU/99qt9bs0waftA= =V7r+ -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 I think the problem may be in determining when a record was added to the table. If there is no 'date added' column as part of your table specification that you populate when adding a row then is there any way to determine when a record was added? If there is a date added (which is a standard I put in all tables I use) then it should be a fairly straight forward task of doing an INSERT INTO followed by a DELETE FROM. As for a single command... I'm not aware of any INSERT INTO AND DELETE ORIGINAL variant so it would have to be two SQL commands, albeit it uncomplicated. -- Paul Lambert Database Administrator AutoLedgers ---(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] How to create an archive for old records?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 02/14/07 19:34, Paul Lambert wrote: Ron Johnson wrote: On 02/14/07 19:11, carter ck wrote: Hi all, I am looking for ways to create an archive of records older than 3 months in one of my table, and store these extracted records into a local database. Does Postgres have any command to do this? A single command that will copy data to a destination database, and then delete from the source database? I think the problem may be in determining when a record was added to the table. If there is no 'date added' column as part of your table specification that you populate when adding a row then is there any way to determine when a record was added? If there is a date added (which is a standard I put in all tables I use) then it should be a fairly straight forward task of doing an INSERT INTO followed by a DELETE FROM. As for a single command... I'm not aware of any INSERT INTO AND DELETE ORIGINAL variant so it would have to be two SQL commands, albeit it uncomplicated. But that falls down when you need to archive 60M records and not block any other jobs. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF07vkS9HxQb37XmcRAhN+AJ4/KIxnwof9gGa2opz9LlpcKeJjHwCghhGz oRFZDrAFhphQMpV13l0Brgw= =TOaV -END PGP SIGNATURE- ---(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] PGSQL 8.2.3 Installation problem
Paul Thanks for your time. the installer log say: fixing permissions on existing directory C:/Archivos de programa/PostgreSQL/8.2/data ... ok creating subdirectories ... initdb: could not create directory C:/Archivos de programa: File exists initdb: removing contents of data directory C:/Archivos de programa/PostgreSQL/8.2/data note path is truncated in message ??? file not exists i delete directory one more time thanks best regardsd mdc --- Paul Lambert [EMAIL PROTECTED] escribió: marcelo Cortez wrote: Magnus I have NTFS only , i don't have FAT partitions at all. But the problem is not resolved. The install fail at create cluster for me. I set all permisions for user postgres. Binary manual installation .. make sense i try . It has any manual/instructions/links for that? Best regards MDC Have you run the setup with the 'write detailed installation log to postgresql-8.2.log in the current directory' checked? Can you paste the relevant bits of that log (which you can find in the same directory as the installation file) into a message so more educated persons can take a look? If it's a file system problem, I'd also suggest going to http://www.sysinternals.com to their file and disk utilities, download ntfilemon and run it to monitor file activity (HINT: Set the filter to include only *postgres*, otherwise you will be flooded with information relating to file system access from everything else on your server - and turn on advanced output) that may show you more information about what the error was from the file system point of view. -- Paul Lambert Database Administrator AutoLedgers __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(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] Possible outer join bug with coalesce in 8.2
John McCawley [EMAIL PROTECTED] writes: My development machine is PostgreSQL 8.1.5, and my production machine is PostgreSQL 8.2. Until now I haven't run into any differences in behavior. I have a query with a relatively wacky join, and while it was working on my development machine, it wouldn't work on the production machine. The query is as follows: Is this 8.2.0? Because the query seems to match the conditions for this 8.2.1 bug fix: 2006-12-07 14:33 tgl * src/backend/optimizer/plan/: initsplan.c (REL8_2_STABLE), initsplan.c: Repair incorrect placement of WHERE clauses when there are multiple, rearrangeable outer joins and the WHERE clause is non-strict and mentions only nullable-side relations. New bug in 8.2, caused by new logic to allow rearranging outer joins. Per bug #2807 from Ross Cohen; thanks to Jeff Davis for producing a usable test case. 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] Cast record as text
am Wed, dem 14.02.2007, um 22:37:36 +0200 mailte Mikko Partio folgendes: Why do you want to reinvent the wheel? http://pgfoundry.org/projects/tablelog/ But it use a separate log-table per table. Andreas My original idea was to log changes from different tables to one audit table, and I think tablelog uses separate audit tables for each monitored table? Yes, but with tablelog it is possible to restore any changes, you can restore a table. A blog-entry from Andreas Scherbaum, the maintainer, about tablelog: http://ads.wars-nicht.de/blog/archives/100-Log-Table-Changes-in-PostgreSQL-with-tablelog.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Installing on weendoze vista.
am Thu, dem 15.02.2007, um 8:11:06 +0900 mailte Paul Lambert folgendes: Has anyone had any success installing on weendoze vista? Any install I try gets as far as the service user details, if I ask it to create a user it fails, if I specify an existing user account it complains about the user not having enough access - even when said user account is put into the administrator group. I'm not familiar with windoze, but perhaps this link may help you: http://groups.google.com/group/comp.databases.postgresql/msg/8ddde24ec12440d2 Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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
